sql数据库训练1.docx

1、sql数据库训练11. 新建学生-课程数据库的三个表:学生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主码;课程表:Course(Cno,Cname,Cpno,Credeit) Cno为主码;学生选修表:SC(Sno,Cno,Grade) Sno,Cno,为主码;Student学号（Sno）姓名 Sname性别 Ssex年龄 Sage 所在系 Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISCourse:课程号Cno课程名Cname先行课Cpno学分Credit1数据库542数学23信息系统144操

2、作系统635数据结构746数据处理27Pascal语言64SC:学号Sno课程号Cno成绩Grade9500119295001285950013889500229095002380数据库生成语句：Sql代码 1. create database stu_course 2. 3. use stu_course 4. 5. create table student( 6. sno varchar(32), 7. sname varchar(32), 8. ssex varchar(32), 9. sage int, 10. sdept varchar(32) 11. ) 12. 13. creat

3、e table Course( 14. Cno varchar(32), 15. Cname varchar(32), 16. Cpno varchar(32), 17. credit int 18. ) 19. 20. create table SC( 21. Sno varchar(32), 22. Cno varchar(32), 23. Grade int 24. ) create database stu_courseuse stu_coursecreate table student( sno varchar(32), sname varchar(32), ssex varchar

4、(32), sage int, sdept varchar(32)create table Course( Cno varchar(32), Cname varchar(32), Cpno varchar(32), credit int)create table SC( Sno varchar(32), Cno varchar(32), Grade int)一:查询表中的列和行1:查询全体学生的学号与姓名select sno,sname from student2:查询全体学生的姓名、学号、所在系。select sno,sname,sdept from student3:查询全体学生的详细记录

5、select * from student4:查询全体学生的姓名及出生年份select sname,DATEPART(yy, GETDATE() - sage + 1 from student （SQLServer）5:查询全体学生的姓名,出生年份及所在系,要用小写字母表示系名select sname,DATEPART(yy, GETDATE() - sage + 1,lower(sdept) from student6:查询选修了课程的学生学号select sno,cno from sc7:查询选修了课程的学生姓名select distinct sname from student,sc w

6、here student.sno=sc.sno二:条件查询:常用的查询条件查询条件谓词比较=,=,=,!=,!,!;not+上述比较运算符确定范围Between and,Not between And,确定集合IN,not IN字符匹配Like,Not Like 空值IsNull,ISNOTNULL多重条件AND,OR1:查询计算机系全体学生的姓名select sname from student where sdept=”CS”2:查询所有年龄在20岁以下的学生姓名及其年龄select sname,sage from student where sage203:查询考试成绩有不及格的学生的学

7、号select sno from sc where grade604:查询年龄在20到23间的学生的姓名,系别及年龄select sname,sdept,sage from student where sage between 20 and 235: 查询年龄不在20到23间的学生的姓名,系别及年龄select sname,sdept,sage from student where sage not between 20 and 236:查询信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别select sname,ssex from student where sdept in

8、(IS,MA,CS) 7:查询不是信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别select sname,ssex from student where sdept not in(IS,MA,CS)8:查询学号为”95001”的学生详细情况select * from student where sno=950019:查询所有姓刘的学生的姓名,学号和性别(where name like 刘%)select sname,sno,ssex from student where sname like 刘%10:查询姓”欧阳”且命名为三个汉字的学生的姓名select sname fro

9、m student where sname like 欧阳_11:查询名字中第2个字为”阳”字的学生姓名和学号(where sname like _阳%)select sname,sno from student where sname like _阳%12:查询所有不姓刘的学生姓名select sname from student where sname not like 刘%13:查询DB_Design课程的课程号和学分(where cname like Db_Design Escape)select cno,gredit from course where cname like Db_D

10、esign Escape 14:查询以”DB_”开头,且倒数第3个字符为i的课程的详细情况(where cname like DB_%i_escape)DB_%i_escape) select cno,gredit from course where cname like Db_%i_escape15:查询缺少成绩的学生的学号和相应的课程号(where grade is not null)select sno,cno from sc where grade is null16:查询有成绩的学生学号和课程号select sno,cno from sc where grade is not nul

11、l17:查询计算机系年龄在20岁以下的学生姓名select sname from student where sdept=”CS” and sage3四:连接查询:等值与非等值的连接查询在连接查询中用来连接两个有的条件称为连接条件或连接谓词,当连接运算符号为”=”时,称为等值连接,使用如,=,=,!=连接时称非等值连接1:查询每个学生及其选修课程的情况select student.*,sc.*from student,scwhere student.sno=sc.sno自身连接连接操作在同一个表中进行连接查询2:查询每一门课的间接先修课(即先修课的先修课)select first .cno,s

12、econd.cpnofrom course first ,course secondwhere first.cpno=o五：复合条件连接1:查询选修2号课程且成绩在90分以上的所有学生。Select student,snameform student, scWhere student.sno=sc.sno And So=2 and sc.grade90六：嵌套查询1：带有谓词in的子查询查询与“刘晨”在同一个系学习的学生select sno,sname,sdeptfrom student where sdept in(select sdept from studentwhere sname=刘

13、晨)或：select s1.sname,s1.sdeptfrom student s1,student s2where s1.dept=s2.dept and s2.name=刘晨查询选修了课程名为“信息系统”的学生学号和姓名select sno,sname from studentwhere sno in( select snofrom sc where cno in(select cnofrom coursewhere cname=信息系统)或：select sno,snamefrom student,sc,coursewhere student.sno=sc.sno ando=o and

14、ame=信息系统)2:带有Any 或all谓词的子查询查询其他系中比信息系中某一学生年龄小的学生姓名和年龄select sname, sagefrom studentwhere sage any(select sagefrom studentwhere sdept=is)and sdeptis或用集函数：select sname, sagefrom studentwhere sage(select max(sage)from studentwhere sdept=is)and sdeptis 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄select sname, sagefrom st

15、udent where sageall(select sage from studentwhere sdept=is)and sdeptis3 带有Exitst谓词的子查询查询所有选修了1号课程的学生姓名select snamefrom studentwhere exists (select *from scwhere sno=student.sno and cno=1)查询没有选修1号课程的学生姓名select snameform studentwhere not exists (select *form scwhere sno=stuedent.sno and cno=1)查询选修所有全部

16、课程的学生姓名select snamefrom studentwhere not exists(select * from coursewhere not exists(select * from sc where sno=student.snoand cno=o)查询只选修了学生95002选修的全部课程的一部分的学生号码select distinct snofrom sc scxwhere not exists ( select * from sc scywhere scy.sno=95002 andnot exists( select * from sc sczwhere scz.sno=

17、scx.sno ando=o)二：题一：表数据如下：FYear FNum2006 12006 22006 32007 42007 52007 6按如下格式显示：年度 2006 2007汇总6 15方案一：create table 表名(FID varchar(10), Field1 varchar(100)goinsert into 表名 select 1,Ainsert into 表名 select 1,Binsert into 表名 select 1,Cinsert into 表名 select 2,Dinsert into 表名 select 2,Einsert into 表名 sele

18、ct 2,Fgo-创建一个合并的函数create function f_merge(name varchar(100)returns varchar(8000)asbegindeclare str varchar(8000)set str = select str = str + , + cast(Field1 as varchar(100) from 表名 where FID = nameset str = stuff(str , 1,1,)return(str)Endgo-select * from 表名-调用自定义函数得到结果：select FID ,dbo.f_merge(FID) a

19、s tel from 表名 group by FIDdrop table 表名drop function f_merge方案二：select 汇总 as 年度,2006,2007from(select fyear,fnum from T)as sourceTablepivot(sum(fnum)for fyear in (2006,2007)as pivotTable回头发现可以用SQL2005 pivot 的方法很简单题二：表A数据如下：FID Field11 A1 B1 C2 D2 E2 F要求按如下格式显示：FID Field11 A,B,C2 D,E,F 如何做到?create tab

20、le 表名(FID varchar(10), Field1 varchar(100)goinsert into 表名 select 1,Ainsert into 表名 select 1,Binsert into 表名 select 1,Cinsert into 表名 select 2,Dinsert into 表名 select 2,Einsert into 表名 select 2,Fgo-创建一个合并的函数create function f_merge(name varchar(100)returns varchar(8000)asbegindeclare str varchar(8000)set str = select str = str + , + cast(Field1 as varchar(100) from 表名 where FID = nameset str = stuff(str , 1,1,)return(str)Endgo-select * from 表名-调用自定义函数得到结果：select FID ,dbo.f_merge(FID) as tel from 表名 group by FIDdrop table 表名drop function f_merge