简单查询: 一、投影 select * from 表名 select 列1,列2... from 表名 select distinct 列名 from 表名 二、筛选 select top 数字 列|* from 表名 (一)等值与不等值 select * from 表名 where 列名=值 select * from 表名 where 列名!=值 select * from 表名 where 列名>值 select * from 表名 where 列名<值 select * from 表名 where 列名>=值 select * from 表名 where 列名<=值 (二)多条件与范围 select * from 表名 where 条件1 and|or 条件2 ... select * from 表名 where between ... and ... select * from 表名 where 列 in (值列表) (三)模糊查询 like % _ select * from 表名 where 列 like '%_....' 三、排序 select * from 表名 where 条件 order by 列名 ASC|DESC,列名 ASC|DESC 四、分组: 统计函数(聚合函数) count(), max(), min(), sum(), avg() count()统计总行数 count(*)得到所有的行数 count(列)得到该列中所有非null个数。 select COUNT(*) from car where Brand='b003' max(列) 这一列的最大,min(列)这一列的最小 select min(price) from car sum(列)这一列的和,avg(列)这一列的平均 select AVG(price) from car group by ...having... 1.group by后面跟的是列名。 2.一旦使用group by分组了,则select和from中间就不能用*,只能包含两类东西一类是:group by 后面的列名,另一类是统计函数 select Oil,avg(price) from Car group by oil 对于统计函数生成的列,默认是无列名,可以通过下面的方法指定列名。 select Oil as 油耗,COUNT(*) as 数量,avg(price) 均价 from Car group by oil having后面一般跟得是统计函数。它用来对分组后的数据进一步筛选。 复杂查询: 一、连接查询 把多个表的列合在一个界面视图中。 思想:1.生成笛卡尔积。2.对笛卡尔积进行筛选。3.选择列进行显示。 select 表1.列1,表1.列2,表2.列1,表2.列2…… from 表1,表2 where 表1.列=表2.列 select * from 表1 join 表2 on 表1.列=表2.列 join 表3 on 表2.列=表3.列 左连(left join),右连(right join),全连(full join)
二、联合查询 把多个表的行合在一个界面视图中。 用union把两个查询组合在一起。要求是这两个查询的列要一一对应。 三、子查询(嵌套查询) (一)无关子查询: 至少是两层查询,在外层查询的里面再写查询。 里层查询为外层查询提供查询的中间内容。
create database test go use test go create table student ( --学号 sno varchar(3) not null primary key, --姓名 sname varchar(4) not null, --性别 ssex varchar(2) not null, --出生年月 sbirthday datetime, --所在班级 class varchar(5) ) go create table teacher ( --教工编号 tno varchar(3) not null primary key, --教工姓名 tname varchar(4) not null, --教工性别 tsex varchar(2) not null, --教工出生日期 tbirthday datetime, --职称 prof varchar(6), --所在部门 depart varchar(10) ) go create table course ( --课程号 cno varchar(5) not null primary key, --课程名称 cname varchar(10) not null, --教工编号 tno varchar(3) references teacher(tno) ) go create table score ( --学号 sno varchar(3) not null references student(sno), --课程号 cno varchar(5) not null references course(cno), --成绩 degree decimal(4,1) ) go insert into student values('108','曾华','男','1977-09-01','95033') insert into student values('105','匡明','男','1975-10-02','95031') insert into student values('107','王丽','女','1976-01-23','95033') insert into student values('101','李军','男','1976-02-20','95033') insert into student values('109','王芳','女','1975-02-10','95031') insert into student values('103','陆君','男','1974-06-03','95031') insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系') insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系') insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系') insert into teacher values('831','刘冰','女','1958-08-14','助教','电子工程系') insert into course values('3-105','计算机导论','825') insert into course values('3-245','操作系统','804') insert into course values('6-166','数字电路','856') insert into course values('9-888','高等数学','831') insert into score values('103','3-245','86') insert into score values('105','3-245','75') insert into score values('109','3-245','68') insert into score values('103','3-105','92') insert into score values('105','3-105','88') insert into score values('109','3-105','76') insert into score values('101','3-105','64') insert into score values('107','3-105','91') insert into score values('108','3-105','78') insert into score values('101','6-166','85') insert into score values('107','6-166','79') insert into score values('108','6-166','81') select * from student select * from teacher select * from course select * from score --1、 查询Student表中的所有记录的Sname、Ssex和Class列。 select sname,ssex,class from student --2、 查询教师所有的单位即不重复的Depart列。 select distinct depart from teacher --3、 查询Student表的所有记录。 select * from student --4、 查询Score表中成绩在60到80之间的所有记录。 select * from score where degree between 60 and 80 --5、 查询Score表中成绩为85,86或88的记录。 select * from score where degree in (85,86,88) --6、 查询Student表中“95031”班或性别为“女”的同学记录。 select * from student where class='95031' or ssex='女' --7、 以Class降序查询Student表的所有记录。 select* from student order by class desc --8、 以Cno升序、Degree降序查询Score表的所有记录。 select * from score order by cno asc,degree desc --9、 查询“95031”班的学生人数。 select count(*) 数量 from student where class=95031 --10、查询Score表中的最高分的学生学号和课程号。 select sno,cno from score where degree=(select MAX(degree)分数 from score) --11、查询‘3-105’号课程的平均分。 select AVG(degree) from score where cno='3-105' --12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select AVG(degree) from score where (cno = (select cno from score group by cno having count(*)>=5)) and cno like '3%' select cno,count(*),AVG(degree) from score where cno like '3%' group by cno having COUNT(*)>=5 --13、查询最低分大于70,最高分小于90的Sno列 select sno,MAX(degree),MIN(degree) from score group by sno having MAX(degree)<90 and MIN(degree)>70 --14、查询所有学生的Sname、Cno和Degree列。 select sname,cno,degree from student join score on student.sno=score.sno --15、查询所有学生的Sno、Cname和Degree列。\p select sno ,cname,degree from score join course on score.cno=course.cno --16、查询所有学生的Sname、Cname和Degree列。 select sname,cname,degree from student join score on student.sno=score.sno join course on course.cno=score.cno --17、查询“95033”班所选课程的平均分。 select avg(degree) from score where sno in (select sno from student where class='95033') --18、假设使用如下命令建立了一个grade表: --create table grade(low int,upp int,rank varchar(1)) --insert into grade values(90,100,'A') --insert into grade values(80,89,'B') --insert into grade values(70,79,'C') --insert into grade values(60,69,'D') --insert into grade values(0,59,'E') --现查询所有同学的Sno、Cno和rank列。 select sno,cno,rank from score join grade on score.degree>grade.low and score.degree < grade.upp --19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 select degree from score where cno='3-105' and sno='109'--76 select * from score where cno='3-105' and degree>(select degree from score where cno='3-105' and sno='109') --20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。 select * from score select sno from score group by sno having count(*)>1--有歧义 --21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。 select * from score where degree>(select degree from score where sno=109 and cno='3-105') --22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 select year(sbirthday) from student where sno=108 select sno,sname, sbirthday from student where year(sbirthday) =(select year(sbirthday) from student where sno=108) --23、查询“张旭“教师任课的学生成绩。 select tno from teacher where tname='张旭' select cno from course where tno=(select tno from teacher where tname='张旭') select degree from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭')) --24、查询选修某 课程的同学人数多于5人的教师姓名。 select cno from score group by cno having COUNT(*)>5 select tno from course where cno=(select cno from score group by cno having COUNT(*)>5) select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having COUNT(*)>5)) --25、查询95033班和95031班全体学生的记录。 select * from student where class=95033 union select *from student where class=95031 --26、查询存在有85分以上成绩的课程Cno. select cno from score where degree >85 select cname from course where cno in (select cno from score where degree >85) --27、查询出“计算机系“教师所教课程的成绩表。 select tno from teacher where depart ='计算机系' select cno from course where tno in (select tno from teacher where depart ='计算机系') select degree from score where cno in ( select cno from course where tno in (select tno from teacher where depart ='计算机系') ) --28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。 select prof from teacher where (depart='计算机系' or depart='电子工程系') select prof from teacher group by prof having COUNT(*)=1 select tname ,prof from teacher where prof in (select prof from teacher group by prof having COUNT(*)=1)and prof in (select prof from teacher where (depart='计算机系' or depart='电子工程系')) --的Cno、Sno和Degree,并按Degree从高到低次序排序。 select degree from score where cno='3-105' select MAX(degree) from score where cno='3-245' select degree from score where degree in (select degree from score where cno='3-105') and degree> (select MAX(degree) from score where cno='3-245') select * from score where degree in (select degree from score where cno='3-105') and degree> (select MAX(degree) from score where cno='3-245') order by degree desc --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree. --31、查询所有教师和同学的name、sex和birthday. select * from student select * from teacher select * from course select * from score select sname 姓名, ssex 性别,sbirthday 生日 from student union select tname ,tsex,tbirthday from teacher --32、查询所有“女”教师和“女”同学的name、sex和birthday. select sname, ssex ,sbirthday from student where ssex='女' union select tname ,tsex,tbirthday from teacher where tsex='女' --33、查询成绩比该课程平均成绩低的同学的成绩表。//相关子查询 select * from score select avg(degree) from score group by cno select degree from score group by cno --insert into teacher values('888','aaa','v','2005.1.1','a','a') select cno from course where cno not in (select cno from score ) select * from teacher select cno from score select tname ,depart from teacher join course on teacher.tno=course.tno left join score on course.cno=score .cno where course . cno=(select cno from course where cno not in (select cno from score )) --36、查询至少有2名男生的班号。 --insert into student values('110','aaa','a','2005.1.1','95000') select class from student group by class having count('男')>=2 --37、查询Student表中不姓“王”的同学记录。 select sname from student where sname like '王%' select * from student where sname not in (select sname from student where sname like '王%') --38、查询Student表中每个学生的姓名和年龄。 select sname 姓名,YEAR(getdate())-year(sbirthday) 年龄 from student --39、查询Student表中最大和最小的Sbirthday日期值。 select MAX(sbirthday) 最大生日,MIN(sbirthday) 最小生日 from student select * from student --40、以班号和年龄从大到小的顺序查询Student表中的全部记录。 select * from student order by class desc, sbirthday desc --41、查询“男”教师及其所上的课程。 select * from teacher where tsex='男' select tname, cname from course join teacher on course .tno=teacher.tno where tsex='男' --42、查询最高分同学的Sno、Cno和Degree列。 select MAX(degree) from score select * from score where degree =(select MAX(degree) from score) --43、查询和“李军”同性别的所有同学的Sname. select ssex from student where sname='李军' select sname from student where ssex=(select ssex from student where sname='李军') --44、查询和“李军”同性别并同班的同学Sname. select * from student select class from student where sname='李军' select sname from student where ssex=(select ssex from student where sname='李军')and class=(select class from student where sname='李军') --45、查询所有选修“计算机导论”课程的“男”同学的成绩表。 select * from student select * from course select * from score select degree from student join score on student .sno=score.sno join course on score .cno=course.cno where cname='计算机导论' and ssex='男'