这些例子都是我平时积累总结的
- set statistics io on
- --查询选修了003号课程并且分数在80分以上的所有学生信息
- --第一句开销小,第二句开销大
- select * from student where sno=(select sno from sc where cno=003 and grade>80)
- select * from student where 80<=(select grade from sc where student.sno=sc.sno and cno=003)
- --查询至少选修了2门课程的学生所有信息,从系统开销来看还是第一个的效率高
- select * from student where 2<=(select count(cno) from sc where student.sno=sc.sno)
- select * from student where sno in (select sno from sc group by sno having count(cno)>=2)
- --在学生表student和学生选课表sc中找出所有学生选修的课程其平均成绩大于75分的学生所有信息
- select * from student where 75<(select avg(grade) from sc where student.sno=sc.sno)
- select * from student where sno in(select sno from sc group by sno having avg(grade)>75)
- --在学生表student和学生选课表sc中找出所有学生选修了课程001的学生学号,姓名,性别,年龄,和所属系部信息
- select sno,sname,sgentle,sage,sdept from student where sno in(select sno from sc where cno=001)
- select sno,sname,sgentle,sage,sdept from student where 001 in(select cno from sc where student.sno=sc.sno)
- --查询学生表student中平均年龄小于该系其中某一个学生的年龄
- select sdept,avg(sage) from student a group by a.sdept having avg(sage)< any(select sage from student b where a.sdept=b.sdept)
- --选取计算机系学生选修了"数据结构"课程的学生基本信息,并按年龄降序排列
- --同样的,第一个效率高
- select * from student where sno in (
- select sno from sc where cno in(
- select cno from course where cname='数据结构'))and sdept='计算机' order by sage desc
- select student.* from student join sc
- on student.sno=sc.sno join course
- on course.cno=sc.cno
- where student.sdept='计算机' and
- course.cname='数据结构'
- order by sage desc
- --查询姓名'张忠和'的学生所选修课程'软件工程'的成绩
- select grade from sc where cno in(select cno from course where cname='软件工程') and sno=(select sno from student where sname='张忠和')
- create table studenttest(
- sno varchar(10) primary key,
- sname varchar(10) not null,
- sgentle varchar(2) not null,
- sage int,
- sbirth smalldatetime,
- sdept varchar(20) not null
- )
- sp_help student
- --大数据的复制
- bulk insert stored.dbo.studenttest from 'c:\test.txt'
- --xp_cmdshell是要调用系统中的cmd,在执行这个时要先配置path环境变量
- EXEC xp_cmdshell 'bcp stored.dbo.student out c:\test1.txt -c -T'
- --将数据导入到数据库中,导入的时候要注意时间日期的问题,导入的格式要与数据库中的设定格式要一致
- exec xp_cmdshell 'bcp stored.dbo.studenttest in c:\test1.txt -c -T'
转载于:https://blog.51cto.com/sucre/420274