--新建数据库create database admin--新建表同时建字段create table student(ID int,namee nvarchar(5),age nchar(1),sex int)--删除表drop table student--查询select * from student--查询指定的字段select ID,age from student--小名或者别名select namee 姓名,age 年龄from student--添加记录insert into student values(1,‘王建国‘,‘男‘,61)insert into student values(2,‘李刚‘,‘男‘,55)insert into student values(4,‘张家辉‘,‘男‘,64)--添加部分字段（）里的是需要添加的insert into student (ID,namee,sex) values(3,‘刘能‘,69)--删除全部记录delete from student--删除一条记录delete from student where ID=1delete from student where sex<60delete from student where namee=‘刘能‘--按条件查询select ID,namee,sex from student where sex<60--修改记录update student set sex=56 where sex=40update student set sex=sex+1 where sex<60---------------------------------------------------------------------------数据类型char给多少就有多少字符串varchar可变长字符串，指定长度nchar和nvarchar是在前者的基础上采用了Unicode编码char  varchar   含英文的nchar nvarchar  含中文的Unicode编码的特点是字节和汉字占用的空间一样 （要用到中午就在前面加一个n）int        bigint        numeric(6.2)-（更加精准，好控制）datetime    timestamp  --时间getdate()  得到当前时间--测试时间create table timee(timee datetime)insert into timee values(getdate())select *from timee------------------------------------------------------------------------------主键 （唯一标示）不能重复不为空create table teacher( ID int primary key,--primary key表示主键namee nvarchar(5),age int)delete from teacher where ID=4insert into teacher values(1,‘王建国‘,61)insert into teacher values(2,‘李刚‘,55)insert into teacher (ID,namee) values(3,‘刘能‘)--插入部分字段主键必须插入insert into teacher values(4,‘李刚‘,55)select * from teacherupdate teacher set age=69 where age is nulldelete from teacher where age=61 and namee=‘李刚‘delete from teacher where age=61 or namee=‘李刚‘select ID,namee,age from teacher where namee=‘李刚‘or age=61---------------------- 外键-----------------------外键只能指向主键并且类型要一致create database Supermarketdrop table bumencreate table bumen(ID int primary key,--主键cname nvarchar(10),address nvarchar(10))create table person(number int primary key,pname nvarchar(10),work nvarchar(10),--职务shangji int ,ptime datetime,--入职时间Wage numeric(6,1),--工资buzhu numeric(5,1),ID int foreign key references bumen(ID)--外键)select * from bumenselect * from person--修改员工的上级update person set ptime=‘2014-05-14‘ where pname=‘王翔‘insert into bumen values(1,‘永辉喷水池店‘,‘喷水池‘)insert into bumen values(2,‘永辉观山湖店‘,‘观山湖‘)insert into bumen values(3,‘永辉世纪城店‘,‘世纪城‘)insert into bumen values(4,‘永辉花果园店‘,‘花果园‘)insert into bumen values(5,‘永辉火车站店‘,‘火车站‘)select number 编号,pname 姓名,work 职务,shangji 上级,ptime 入职时间,Wage 工资,buzhu 补助,ID ID from persondelete from person where work=‘员工‘insert into person (number,pname,work,ptime,Wage,buzhu,ID) values(101,‘张轩松‘,‘董事长‘,‘1998-01-02‘,50000,8000,1)insert into person values(102,‘李明星‘,‘总经理‘,101,‘2010-05-23‘,20000,5000,1)insert into person values(103,‘旺小涵‘,‘区域经理‘,102,‘2010-05-23‘,15000,5500,1)insert into person values(104,‘陈明尔‘,‘区域经理‘,102,‘2010-05-23‘,15000,5500,2)insert into person values(105,‘王星宇‘,‘部门经理‘,104,‘2010-05-23‘,10000,1000,3)insert into person(number,pname,work,shangji,ptime,Wage,ID)values(106,‘张雨生‘,‘员工‘,104,‘2011-08-15‘,2800,3)insert into person(number,pname,work,shangji,ptime,Wage,ID)values(107,‘王秘书‘,‘员工‘,104,‘2012-01-13‘,2500,3)insert into person(number,pname,work,shangji,ptime,Wage,ID)values(108,‘石阿姨‘,‘员工‘,104,‘2013-12-02‘,2000,3)insert into person(number,pname,work,shangji,ptime,Wage,ID)values(109,‘蒋敏平‘,‘员工‘,104,‘2013-12-02‘,2200,3)insert into person(number,pname,work,shangji,ptime,Wage,ID)values(110,‘李安静‘,‘员工‘,104,‘2013-12-02‘,2000,4)insert into person(number,pname,work,shangji,ptime,Wage,ID)values(111,‘王翔‘,‘员工‘,104,‘2013-12-02‘,2200,5)--查询王星宇的编号，姓名，职务，工资select number,pname,work,Wage from person where pname=‘王星宇‘--查询一共多少部门 distinct  只显示重复记录的一个select distinct ID from person--查询区域经理的姓名和年收入select pname 姓名,Wage*12 年薪from person where work=‘区域经理‘--计算年总收入 和空值做四则运算select pname 姓名,wage*12+isnull(buzhu,0)*12 年薪from  person--查询-01-01以后入职的员工select pname,ptime from person where ptime>‘2012-1-1‘and ptime<‘2014-1-1‘--查询工资在到的select pname,wage from person where wage>2000 and wage<=10000--模糊查询select pname from person where pname like‘王%‘select pname from person where pname like‘__宇%‘--批量查询select pname,number from person where number in(101,105,109)select pname,work from person where shangji is null--排序默认升序 desc降序select number,pname,wage from person order by wage descselect number,pname,wage from person order by pnameselect ID,pname,wage from person order by ID desc,wage descselect pname‘姓名‘,wage*12+isnull(buzhu,0)*12‘年薪‘ from  person order by ‘年薪‘ desc --别名方法显示排序--显示总工资和平均工资select sum(wage)‘总工资‘,avg(wage)‘平均工资‘ from personselect pname,wage from person where wage=(select max(wage) from person)select pname,wage from person where wage=(select min(wage) from person)select pname,wage,(select avg(wage)from person) from person where wage>(select avg(wage) from person)--统计有多少记录select count(*) from person--统计每个部门的平均工资和总工资group by后面的字段不需显示字段中select ID,avg(wage)‘平均工资‘,sum(wage)‘总工资‘ from person group by ID--显示每个部门的每个职务的平均工资和最低工资select ID,avg(wage)‘平均工资‘,min(wage)‘最低工资‘,work from person group by ID,work order by ID--平均工资低于的部门编号和平均工资(having)再次查询select id,avg(wage) from person group by id having avg(wage)>10000-------------多表查询--------------------------select * from bumen,personselect * from bumen,person where bumen.address=‘喷水池‘select * from bumen,person where bumen.address=‘喷水池‘ and person.ID=bumen.idselect * from bumen,person where person.ID=bumen.id--显示姓名和所在部门以及部门编号select pname,cname,person.ID from bumen,person where person.ID=bumen.IDselect pname,cname,person.ID from bumen,person where (person.ID=bumen.ID)and(bumen.id=3)select pname,cname,person.ID from bumen,person where person.ID=bumen.ID order by bumen.ID--查询张玉生的上级select shangji from person where pname=‘张雨生‘select pname from person where number=(select shangji from person where pname=‘张雨生‘)--查询所有员工及其上级的姓名select a.pname,b.pname from person a,person b where a.shangji=b.number--查询高于部门平均工资的员工和工资，部门编号以及部门的平均工资首先得到每个部门的平均工资select avg(wage),ID from person group by idselect pname,wage,avgwage,person.ID from person,(select avg(wage) avgwage,ID from person group by id) lsbwhere (person.ID=lsb.ID) and (person.wage>lsb.avgwage) --分页查询--查询第个到第个入职的职工select top 5 pname,work,ptime from person order by ptime----查询第个到第个入职的职工select top 7 number,pname,work,ptime from person where number not in (select top 3 number from person order by ptime) order by ptime----------------删除重复的记录----------------------select * from studentselect distinct * from studentselect distinct *into lsStudent from studentdelete from studentinsert into student select * from lsStudentdrop table lsStudent---------外连接---select a.pname,b.pname 上级from person a left join person b on a.shangji=b.number---张轩松的上级为空