T-SQL 查询语句 环境准备 use master go if db_id('accp') is not null drop database accp go --create physics memory for new database exec xp_cmdshell 'if exist e:/project (rd /s /q e:/project)',no_output exec xp_cmdshell 'md e:/project',no_output go --create new database accp create database accp on primary ( name=accp01, filename='e:/project/accp01.mdf', size=5MB, filegrowth=10%, maxsize=50MB ) log on ( name=accp02, filename='e:/project/accp02.ldf', size=1MB, filegrowth=1MB, maxsize=10MB ) go --use database accp use accp go --prepare table teacher create table teacher ( tid int not null identity primary key , --serial number tname varchar(10) not null, --teacher name tsex varchar(2) not null, --teacher sex tage int not null, --teacher age tsalary int , --teacher salary cid int not null, --course id dname varchar(50) not null --department name ) go --insert some datas insert into teacher values('张之全','男',30,1500,3,'计算机') insert into teacher values('马强','男',28,1800,4,'生物工程') insert into teacher values('王美丽','女',25,null,5,'物理') insert into teacher values('倩影','女',20,1600,6,'机械工程') insert into teacher values('赵之强','男',26,1500,8,'数学') insert into teacher values('叶秀','女',18,1200,9,'生物工程') insert into teacher values('吴欣','男',20,1500,7,'电子工程') insert into teacher values('李秀丽','女',20,1800,2,'计算机') insert into teacher values('李秀丽','女',20,1800,1,'计算机') insert into teacher values('李秀丽','女',20,1800,10,'计算机') go create table course ( cid int not null , --course id cname varchar(50) not null, --course name ctime int not null, --course time scount int not null, --credit hour ctest datetime not null --test time ) go insert into course values(4 , '应用数学基础',48 ,120 , '2006-7-10') insert into course values( 5, '生物工程概论',32 ,80 , '2006-7-8') insert into course values( 1, '计算机软件基础',32 ,70 , '2006-7-8') insert into course values( 2, '计算机硬件基础',24 ,80 , '2006-6-28') insert into course values( 8, '模拟电路设计',28 ,90 , '2006-7-10') insert into course values( 7, '机械设计实践',48 ,68 , '2006-7-14') insert into course values( 3, '生物化学',32 ,40 , '2006-7-2') insert into course values(9 , '数据库设计',16 ,80 , '2006-7-1') insert into course values(6, '设计理论',28 ,45 , '2006-6-30') insert into course values(10 , '计算机入门',24 ,150 , '2006-6-29') insert into course values(11, '数字电路设计基础',30 ,125 , '2006-6-20') insert into course values(12, 'HTML%',30 ,125 , '2006-6-20') insert into course values(13, 'DIV_CSS',30 ,125 , '2006-6-20') insert into course values(14, 'Jsp2.0[en]',30 ,125 , '2006-6-20') go create table student ( sid char(6) not null, --student number sname char(15) not null, --student name dname char(16) not null, --department name ssex char(2), --student sex cid int, --course id mark decimal(3,1), --student grade type char(4) --subject type ) go insert into student values ('9701', '刘建国', '管理工程', '男',4 ,82.5 , '必修') insert into student values ('9701', '刘建国', '管理工程', '男',10 , 70, '选修') insert into student values ('9701', '刘建国', '管理工程', '男',1 ,78.5 , '选修') insert into student values ('9702', '李春', '环境工程', '女',5 ,63 , '必修') insert into student values ('9702', '李春', '环境工程', '女',10 ,58 , '选修') insert into student values ('9703', '王天', '生物', '男',5 , 48.5, '必修') insert into student values ('9703', '王天', '生物', '男',2 ,86 , '选修') insert into student values ('9704', '李华', '计算机', '女', 4,76 , '必修') insert into student values ('9704', '李华', '计算机', '女',1 ,92 , '必修') insert into student values ('9704', '李华', '计算机', '女',2 ,89 , '必修') insert into student values ('9704', '李华', '计算机', '女',9 ,80 , '必修') insert into student values ('9704', '李华', '计算机', '女', 8, 70, '选修') insert into student values ('9705', '孙庆', '电子工程', '男', 8,79 , '必修') insert into student values ('9705', '孙庆', '电子工程', '男', 1, 59 ,'必修') insert into student values ('9705', '孙庆', '电子工程', '男',11 ,52 , '必修') insert into student values ('9705', '孙庆', '电子工程', '男',6 ,68 , '必修') insert into student values ('9706', '高伟', '机械工程', '男',13 , 93, '必修') -----------------------------简单查询语句------------------ select * from teacher select * from course select * from student 1、查询修过计算机入门的学生信息 select '计算机入门' as cname,sname,dname,ssex,sid from student where cid=(select cid from course where cname='计算机入门') order by sname go select '计算机入门' as cname,sname,dname,ssex,sid from student as s inner join course as c on s.cid=c.cid where c.cname='计算机入门' order by sname go 2、查询所有年龄高于平均年龄的教师信息 select tname as 姓名,tsex as 性别,tage as 年龄from teacher where tage>(select avg(tage) from teacher) order by tname go 3、查询各系中平均年龄大于所有教师平均年龄的教师信息 select dname as 系名, tname as 姓名,tsex as 性别,tage as 年龄 from teacher as t where (select avg(tage) from teacher where dname=t.dname)>(select avg(tage) from teacher) order by dname,tname go 4、查询所有教师的姓名、工号、性别、课程号以及修过此课程的人数 select tid as 工号,tname as 姓名,tsex as 性别,cid as 课程号, (select count(cid) from student where cid=teacher.cid) as 修过人数 from teacher order by 修过人数desc go 5、查询所有不及格学生的姓名、所在系、课程及成绩信息 select sname as 姓名,dname as 系名,cid as 课程号,mark as 成绩 from student where mark<60 order by sname,mark go 6、查询所有不及格学生的姓名、所在系、所有课程及成绩信息 select s1.sname as 姓名,s1.dname as 系名,s1.cid as 课程号,s1.mark as 成绩 from student as s1 inner join student as s2 on s1.cid=s2.cid and s1.mark<60 order by s1.sname,s1.mark go select s1.sname as 姓名,s1.dname as 系名,s1.cid as 课程号,s1.mark as 成绩 from student as s1 ,student as s2 where s1.cid=s2.cid and s2.mark<60 order by s1.sname,s1.mark go select sname as 姓名,dname as 系名,cid as 课程号,mark as 成绩 __________from student where cid in (select cid from student where mark<60) order by sname go 7、查询修过了号或号课程学生的学号、姓名以及所在系信息 select sid as 学号,sname as 姓名,dname as 系名,cid as 课程号from student where cid in (1,10) order by cid,sname go 8、查询修过了号但是没有修过号课程学生的学号、姓名以及所在系信息 select sid as 学号,sname as 姓名,dname as 系名,cid as 课程号from student where cid=1 and cid not in(10) order by cid,sname go 9、查询教师信息,要求该教师开设的课程有人修过 select tid as 工号,tname as 姓名,tsex as 性别,tage as 年龄,cid as 课程号 from teacher as t where exists (select cid from student where cid=t.cid) order by tid,tname go 10、查询未被学生选修过的课程信息 select cid as 课程号,cname as 课程名,ctime as 课时 from course as c where not exists (select cid from student where cid=c.cid) order by cid,cname go 11、查询女教师开设的、月份考试的课程号、开设教师的姓名以及教师所在的系 select t.tname as 姓名,t.dname as 系名,t.cid as 课程号 from teacher as t inner join course as c on t.cid=c.cid and month(c.ctest)=6 where t.tsex='女' order by t.tname go select tname as 姓名,dname as 系名,cid as 课程号 from teacher where tsex='女' and exists (select * from course where teacher.cid=course.cid and month(ctest)=6) order by tname go 12、查询其它系中比计算机系中所有教师年龄都小的教师的教工号、姓名、所在系以及年 龄信息 select tid as 工号,tname as 姓名,dname as 系名,tage as 年龄 from teacher where tage<(select min(tage) from teacher where dname='计算机') and dname<>'计算机' order by dname,tage go select tid as 工号,tname as 姓名,dname as 系名,tage as 年龄 from teacher where tage<all(select tage from teacher where dname='计算机') and dname<>'计算机' order by dname,tage go select dname,tage from teacher group by dname,tage go 13、查询只被一个学生修过的课程的课程号、课程名、学时和考试时间信息 select cid as 课程号,cname as 课程名,ctime as 学时,ctest as 考试时间 from course where (select count(*) from student where course.cid=student.cid)=1 order by cid,cname go select cid as 课程号,cname as 课程名,ctime as 学时,ctest as 考试时间 --error from course where unique(select cid from student where course.cid=student.cid) order by cid,cname go 14、李华同学修过的课程号、课程名称、学时和考试时间信息 select '李华' as 姓名,cid as 课程号,cname as 课程名称,ctime as 学时,ctest as 考试时间 from course where '李华' in (select sname from student where student.cid=course.cid) order by cid,cname go 15、查询学生的姓名、所在系、课程号以及成绩,要求查询的课程的考试时间在七月日之 前 select s.sname as 姓名,s.dname as 系名,s.cid as 课程号,s.mark as 成绩 from student as s where (select ctest from course as c where c.cid=s.cid)<cast ('2006-7-2' as smalldatetime) order by s.dname, s.sname go select s.sname as 姓名,s.dname as 系名,s.cid as 课程号,s.mark as 成绩 from student as s,course as c where s.cid=c.cid and c.ctest<cast('2006-7-2' as smalldatetime) order by s.dname,s.sname go 16、查询系名,要求各系包含的教师数目要少于student表中修过该系的教师开设的课程的 学生的人次 select t.dname from teacher as t group by t.dname having count(*)<(select count(*) from student where cid in (select cid from teacher as t2 where t2.dname=t.dname)) order by t.dname go 17、查询计算机系教师开设的课程信息,并且要求在student表中所有修过这门课程的同学 的成绩及格 select * from course where cid in (select cid from teacher where dname='计算机' and not exists (select * from student where cid=teacher.cid and mark <60)) order by cid go 18、查询每个系中高于平均年龄的老师的姓名、姓别、年龄 select dname,tname,tsex,tage from teacher as t where tage>(select avg(tage) from teacher where dname=t.dname) order by dname,tname go select dname,tname,tsex,tage from teacher as t where tage>(select avg(tage) from teacher) order by dname,tname go ---------------------End----------------- ------------------------复杂搜索条件查询----------------------- select * from teacher go select * from course go 1、查询计算机系年龄在岁以上并且是女性的老师 select tname,tage from teacher where tsex='女' and tage>18 and dname='计算机' go 2、查询计算机系或生物工程系教师的姓名,薪水情况 select dname,tname,tsalary from teacher where dname='计算机' or dname='生物工程' order by dname,tsalary go 3、查询薪水至少在或者性别为女的老师的系、姓名、年龄、薪水 select dname,tname,tsex,tsalary from teacher where tsalary>=1800 or tsex='男' order by dname,tsalary go 4、查询生物工程和计算机系并且是女教师的教师姓名 select dname,tname,tsex from teacher where dname='生物工程' or dname='计算机' and tsex='女' order by dname go select dname,tname,tsex from teacher where (dname='生物工程' or dname='计算机') and tsex='女' order by dname go 5、查询生物工程和计算机系教师信息 select * from teacher where dname in ('生物工程','计算机') order by dname go 6、查询不是生物工程或计算机系的教师信息 select dname,tname,tsex,tage,tsalary from teacher where dname not in ('生物工程','计算机') order by dname go 7、查询不是计算机系的教师信息 select * from teacher where not dname='计算机' order by tid go 8、查询工资不是元的教师信息 select * from teacher where not tsalary=1800 order by tsalary go 9、查询以基础结尾并且只有六个字的课程 select * from course select * from course where cname like '____基础' order by cid go 10、查询只有六个字的课程 select * from course where cname like '______' order by cname go 11、查询课程名中第二个字为算或者物的课程 select * from course where cname like '_[算物]%' order by cname go 否定形式为: select * from course where cname like '_[^算物]%' order by cname go 12、如果我查找的字符串中就包含%、_、[]时使用转义符即可escape 语法:escape '字符'即将‘字符’后的通配符当作字符而不是通配符 select cname from course where cname like '%_%' escape '_' order by cname go select cname from course where cname like '%*[%]%' escape '*' order by cname go select cname from course where cname like '%▲_%' escape '▲' order by cname go select cname from course where cname like '%★_%[a]' escape '★' order by cname go ----------------------------------End---------------------------------------- --------------------------聚合分析和分组--------------------------- select * from teacher go select * from course go 1、sum select sum(tsalary) as men_sal_total from teacher where tsex='男' go 2、count select count(*) as totalrecords from teacher go select count(distinct tname) as namerecords from teacher go select count(tid) as id_num ,count(distinct tname) as name_num , count(cast(tid as varchar(10))+tname) as new_name_num from teacher go 3、max select max(tage) from teacher go select tname,tsalary,tsex from teacher where tage=(select max(tage) from teacher) go select max(tname) as name from teacher where tsex='女' go select min(ctest) as min_time , max(ctest) as max_time from course go 4、avg select avg(tsalary) as avg1 , sum(tsalary)/count(*) as avg2 from teacher go --Notice:Null is filtered case1:查询每个系中的平均年龄 select dname,avg(tage) from teacher group by dname go case2:查询男老师和女老师的平均年龄 select tsex,avg(tage) from teacher group by tsex go case3:查询表中工资的所有记录数 select count(all tsalary) from teacher go case4:查询各个系中男教师和女老师的人数 select dname,tsex,count(*) from teacher group by dname,tsex order by dname go case5:查询工资的种数和每种工资的人数 select tsalary,count(*) from teacher group by tsalary order by tsalary go --注意:Null值单独列为一组 case6:查询至少有两位教师的系和该系的人数 select dname as 系名,count(*) as 人数from teacher group by dname having count(*)>1 go case7:查询拥有女教师的系和该系的女教师数量 select dname,count(tsex) as count from teacher group by dname,tsex having tsex='女' go select dname,count(tsex) as count from teacher where tsex='女' group by dname go case8:查询至少有两个女教师的系和该系的女教师数量 select dname,count(tsex) as count from teacher group by dname,tsex having tsex='女' and count(*)>1 go select dname,count(tsex) as count from teacher where tsex='女' group by dname having count(*) > 1 go case9:从teacher表中查询至少有两名女教师的系和拥有女教师的数量,并且按照女教师的 数量按照升序排列结果 select dname,count(tsex) as count from teacher where tsex='女' group by dname having count(*) >1 order by count go ------------------------------------end--------------------------------------- --------------------------------多表查询-------------------------------- select * from teacher select * from course select * from student 1、简单的二表连接,相当于内连接 select tname,dname,cname from teacher,course where teacher.cid=course.cid go select t.tname,t.dname,c.cname from teacher as t inner join course as c on t.cid=c.cid go 2、不指明条件的二表连接,直接两表相乘 ,相当于交叉连接 select tname,dname,cname from teacher,course go select count(*) from teacher --10 select count(*) from course --14 3、简单多表连接查询 查询学生高伟所有的系名、课程名、授课教师、分数 select s.sname,s.dname,c.cname,t.tname,s.mark from student as s,course as c ,teacher as t where s.cid=c.cid and s.cid=t.cid and s.sname='高伟' order by s.mark go select s.sname,s.dname,c.cname,t.tname,s.mark from student as s inner join course as c on s.cid=c.cid inner join teacher as t on s.cid=t.cid where s.sname='高伟' order by s.mark go 4、查询学生姓名、所在系、所修订的所有课程的课程号以及开课的姓名 select s.sname as 姓名,s.dname as 系名,s.cid as 课程号,c.cname as 课程名 from student as s inner join course as c on s.cid=c.cid order by s.sname,s.dname,c.cname go 5、查询学生李华的非本系教师开设的课程信息 6、查找所有教师信息,包括课程名 select t.* ,c.cname from teacher as t left outer join course as c on t.cid=c.cid order by t.tname go 7、查询所有课程的信息,包括授课教师姓名 select c.* , t.tname from teacher as t right outer join course as c on t.cid=c.cid order by c.cid go select c.* ,t.tname from course as c left outer join teacher as t on c.cid=t.cid order by c.cid go select c.* ,t.tname --非ANSI外部联接运算符 from teacher as t,course as c where t.cid=* c.cid order by c.cid go --Notice:左外连接和右外连接就是一个顺序的差别 --左连接:显示左表所有内容,不管右表中相关内容是否存在 --右连接:同样道理,显示右表所有内容,而不管左表中相关内容是否存在 8、查询教师马强和叶秀两人的相关信息 select * from teacher where tname='马强' union select * from teacher where tname='叶秀' go select * from teacher where tname='马强' or tname='叶秀' go select * from teacher where tname='马强' union all select * from teacher where tname='李秀丽' go 9、查询修了或号课程的学生姓名、所在系、课程编号信息 以及号或号课程的教师的姓名、所在系、课程编号 select sname ,dname,cid from student where cid=1 or cid=10 union select tname,dname,cid from teacher where cid=1 or cid=10 go 10、查询所有teacher表中的课程代码、以及在student表中修过这门课程的学生的数目 select t.cid,count(s.cid) as num from teacher as t inner join student as s on t.cid=s.cid group by t.cid order by num go 11、查询所有教师的姓名、所在系、开设的课程以及选修这门课程的学生姓名以及成绩。 并要求显示的教师信息必须是他开设的课程在student表中有同学选修 select t.tname,t.dname,c.cname,s.sname,s.mark from teacher as t inner join course as c on t.cid=c.cid inner join student as s on t.cid=s.cid order by t.tname,s.sname go select t.tname,t.dname,c.cname,s.sname,s.mark from teacher as t left outer join course as c on t.cid=c.cid inner join student as s on t.cid=s.cid order by t.tname,s.sname go 12、查询至少修过‘计算机软件基础’或‘数据库设计’两门课程中的一门的学生学号、姓名 以及所修课程名和课程成绩 select s.sid,s.sname,c.cname,s.mark from student as s inner join course as c on s.cid=c.cid where c.cname='计算机软件基础' or c.cname='数据库设计' order by s.sid go select sid, sname, cname, mark from student as s inner join course as c on s.cid=c.cid where cname='计算机软件基础' union select sid, sname, cname, mark from student as s inner join course as c on s.cid=c.cid where cname='数据库设计' order by sid go -------------------------------------End---------------------------------------- -------------------------------子查询---------------------------------- select * from teacher select * from course select * from student 1、查询修过计算机入门的学生信息 select '计算机入门' as cname,sname,dname,ssex,sid from student where cid=(select cid from course where cname='计算机入门') order by sname go select '计算机入门' as cname,sname,dname,ssex,sid from student as s inner join course as c on s.cid=c.cid where c.cname='计算机入门' order by sname go 2、查询所有年龄高于平均年龄的教师信息 select tname as 姓名,tsex as 性别,tage as 年龄from teacher where tage>(select avg(tage) from teacher) order by tname go 3、查询各系中平均年龄大于所有教师平均年龄的教师信息 select dname as 系名, tname as 姓名,tsex as 性别,tage as 年龄 from teacher as t where (select avg(tage) from teacher where dname=t.dname)>(select avg(tage) from teacher) order by dname,tname go 4、查询所有教师的姓名、工号、性别、课程号以及修过此课程的人数 select tid as 工号,tname as 姓名,tsex as 性别,cid as 课程号, (select count(cid) from student where cid=teacher.cid) as 修过人数 from teacher order by 修过人数desc go 5、查询所有不及格学生的姓名、所在系、课程及成绩信息 select sname as 姓名,dname as 系名,cid as 课程号,mark as 成绩 from student where mark<60 order by sname,mark go 6、查询所有不及格学生的姓名、所在系、所有课程及成绩信息 select s1.sname as 姓名,s1.dname as 系名,s1.cid as 课程号,s1.mark as 成绩 from student as s1 inner join student as s2 on s1.cid=s2.cid and s1.mark<60 order by s1.sname,s1.mark go select s1.sname as 姓名,s1.dname as 系名,s1.cid as 课程号,s1.mark as 成绩 from student as s1 ,student as s2 where s1.cid=s2.cid and s2.mark<60 order by s1.sname,s1.mark go select sname as 姓名,dname as 系名,cid as 课程号,mark as 成绩 from student where cid in (select cid from student where mark<60) order by sname go 7、查询修过了号或号课程学生的学号、姓名以及所在系信息 select sid as 学号,sname as 姓名,dname as 系名,cid as 课程号from student where cid in (1,10) order by cid,sname go 8、查询修过了号但是没有修过号课程学生的学号、姓名以及所在系信息 select sid as 学号,sname as 姓名,dname as 系名,cid as 课程号from student where cid=1 and cid not in(10) order by cid,sname go 9、查询教师信息,要求该教师开设的课程有人__________修过 select tid as 工号,tname as 姓名,tsex as 性别,tage as 年龄,cid as 课程号 from teacher as t where exists (select cid from student where cid=t.cid) order by tid,tname go 10、查询未被学生选修过的课程信息 select cid as 课程号,cname as 课程名,ctime as 课时 from course as c where not exists (select cid from student where cid=c.cid) order by cid,cname go 11、查询女教师开设的、月份考试的课程号、开设教师的姓名以及教师所在的系 select t.tname as 姓名,t.dname as 系名,t.cid as 课程号 from teacher as t inner join course as c on t.cid=c.cid and month(c.ctest)=6 where t.tsex='女' order by t.tname go select tname as 姓名,dname as 系名,cid as 课程号 from teacher where tsex='女' and exists (select * from course where teacher.cid=course.cid and month(ctest)=6) order by tname go 12、查询其它系中比计算机系中所有教师年龄都小的教师的教工号、姓名、所在系以及年 龄信息 select tid as 工号,tname as 姓名,dname as 系名,tage as 年龄 from teacher where tage<(select min(tage) from teacher where dname='计算机') and dname<>'计算机' order by dname,tage go select tid as 工号,tname as 姓名,dname as 系名,tage as 年龄 from teacher where tage<all(select tage from teacher where dname='计算机') and dname<>'计算机' order by dname,tage go select dname,tage from teacher group by dname,tage 13、查询只被一个学生修过的课程的课程号、课程名、学时和考试时间信息 select cid as 课程号,cname as 课程名,ctime as 学时,ctest as 考试时间 from course where (select count(*) from student where course.cid=student.cid)=1 order by cid,cname go select cid as 课程号,cname as 课程名,ctime as 学时,ctest as 考试时间 --error from course where unique(select cid from student where course.cid=student.cid) order by cid,cname go 14、李华同学修过的课程号、课程名称、学时和考试时间信息 select '李华' as 姓名,cid as 课程号,cname as 课程名称,ctime as 学时,ctest as 考试时间 from course where '李华' in (select sname from student where student.cid=course.cid) order by cid,cnamego 15、查询学生的姓名、所在系、课程号以及成绩,要求查询的课程的考试时间在七月日之 前 select s.sname as 姓名,s.dname as 系名,s.cid as 课程号,s.mark as 成绩 from student as s where (select ctest from course as c where c.cid=s.cid)<cast ('2006-7-2' as smalldatetime) order by s.dname, s.sname go select s.sname as 姓名,s.dname as 系名,s.cid as 课程号,s.mark as 成绩 from student as s,course as c where s.cid=c.cid and c.ctest<cast('2006-7-2' as smalldatetime) order by s.dname,s.sname go 16、查询系名,要求各系包含的教师数目要少于student表中修过该系的教师开设的课程的 学生的人次 select t.dname from teacher as t group by t.dname having count(*)<(select count(*) from student where cid in (select cid from teacher as t2 where t2.dname=t.dname)) order by t.dname go 17、查询计算机系教师开设的课程信息,并且要求在__________student表中所有修过这门课程的同学 的成绩及格 select * from course where cid in (select cid from teacher where dname='计算机' and not exists (select * from student where cid=teacher.cid and mark <60)) order by cid go 18、查询每个系中高于平均年龄的老师的姓名、姓别、年龄 select dname,tname,tsex,tage from teacher as t where tage>(select avg(tage) from teacher where dname=t.dname) order by dname,tname go select dname,tname,tsex,tage from teacher as t where tage>(select avg(tage) from teacher) order by dname,tname go -------------------------------------End--------------------------------------__