SQL操作复习题及复习资料

1数据库操作1．创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为，初始大小为1MB，最大尺寸为5MB，增长速度为10%。参考答案：createdatabaseTestONprimary( name=test_data, ='d:\test\test_data.mdf', size=5MB, maxsize=unlimited, =1MB)LOGON( name=test_log, ='d:\test\test_log.ldf', size=1MB, maxsize=5MB, =10%)GO2．查看数据库属性：操作1.2：使用T-SQL语句查看数据库test属性参考答案：EXECsp_helpdbtest3．删除数据库：操作1.3：使用T-SQL语句删除数据库test参考答案：dropdatabaseTest2表操作1．创建表：操作2.1：创建学生表：表名：student说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnVarChar9NotNullPK学生学号st_nmnVarChar8NotNull学生姓名st_sexnVarChar2Null学生性别st_birthdatetimeNull出生日期st_scoreintNull入学成绩st_datedatetimeNull入学日期st_fromnChar20Null学生来源st_dpidnVarChar2Null所在系编号st_mnttinyintNull学生职务参考答案：USEtestGOCREATETABLEstudent( st_id nVarChar(9) primarykeyNOTNULL, st_nm nVarChar(8) NOTNULL, st_sex nVarChar(2) NULL, st_birth datetime NULL, st_score int NULL, st_date datetime NULL, st_from nVarChar(20) NULL, st_dpid nVarChar(2) NULL, st_mnt tinyint NULL)GO操作2.2：创建课程信息表：表名：couse说明：课程信息表属性列数据类型长度空值列约束说明cs_idnVarChar4NotNullPK课程编号cs_nmnVarChar20NotNull课程名称cs_tmintNull课程学时cs_scintNull课程学分参考答案：USEtestGOCREATETABLEcouse( cs_id nVarChar(4) primarykeyNOTNULL, cs_nm nVarChar(20) NOTNULL, cs_tm int NULL, cs_sc int NULL)GO操作2.3：创建选课表：表名：slt_couse说明：选课表属性列数据类型长度空值列约束说明cs_idnVarChar4NotNullFK课程编号st_idnVarChar9NotNullFK学生编号scoreintNull课程成绩sltdatedatetimeNull选课日期参考答案：USEtestGOCREATETABLEcouse( cs_id nVarChar(4) NOTNULL, st_id nVarChar(9) NOTNULL, score int NULL, sltdate datetime NULL)GO操作2.4：创建院系信息表：表名：dept说明：院系信息表属性列数据类型长度空值列约束说明dp_idnVarChar2NotNull系编号dp_nmnVarChar20NotNull院系名称dp_drtnVarChar8Null院系主任dt_telnVarChar12Null联系电话参考答案：USEtestGOCREATETABLEdept( dp_id nVarChar(2) NOTNULL, dp_nm nVarChar(20) NOTNULL, dp_drt nVarChar(8) NULL, dp_tel nVarChar(12) NULL)GO2．修改表结构：（1）向表中添加列：操作2.5：为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）参考答案：ALTERTABLEdeptADDdp_countnvarchar(3)NULL（2）修改列数据类型：操作2.6：修改“dept”表的“dp_count”列数据类型为int参考答案：ALTERTABLEdeptALTERCOLUMNdp_countintNULL（3）删除表中指定列：操作2.7：删除“dept”表的“dp_count”列参考答案：ALTERTABLEdeptDROPCOLUMNdp_count3．删除表操作2.8：删除“dept”表参考答案：DROPTABLEstudent4．向表中输入数据记录操作2.9：分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录3数据完整性1．空值约束(NULL)操作3.1：将student表中的st_sex列属性更改为NOTNULL参考答案：ALTERTABLEstudentALTERCOLUMEst_nmnVarChar(8)NOTNULL2．默认值约束(DEFAULT)操作3.2：将student表中的st_from列默认值设置为“陕西省”参考答案：ALTERTABLEstudentADDDEFAULT'陕西省'FORst_from3．默认值对象操作3.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。参考答案：CREATEDEFAULTdf_todayASGetdate()GOEXECsp_bindefaultdf_today,'slt_couse.sltdate'GOEXECsp_unbindefault'slt_couse.sltdate'GODROPDEFAULTdf_todayGO4．检查约束(CHECK)操作3.4：将slt_couse表中的score列的检查约束设置为>=0且<=100参考答案：ALTERTABLEslt_couseADDCHECK(score>=0ANDscore<=100)5．规则约束对象操作3.5：创建规则约束对象rl_sex，用于检查性别的取值仅限于“男”与“女”，并将其绑定到student表中的st_sex列，然后取消绑定，最后删除规则约束对象rl_sex。参考答案：CREATERULErl_sexAS@chksex’男’OR@chksex=’女’或CREATERULErl_sexAS@chksexIN(’男’,’女’)GOEXECsp_bindrulerl_sexex'GOex'GODROPRULErl_sexGO6．主键操作3.6：将dept表中的dp_id列设置为主键参考答案：ALTERTABLEdeptADDPRIMARYKEY(dp_id)7．唯一性约束(UNIQUE)操作3.7：将dept表中的dp_nm列设置为唯一性约束参考答案：ALTERTABLEdeptADDUNIQUE(dp_nm)8．标识列操作3.8：向slt_couse表中添加标识列id，第1行默认值为1，相邻两个标识列间的增量为1参考答案：ALTERTABLEslt_couseADDidINTIDENTITY(1,1)NOTNULL9．外键（FOREIGNKEY）操作3.9：被参照表为dept，参照表为student参考答案：ALTERTABLEstudentADDFOREIGNKEY(st_dpid)REFERENCESdept(dp_id)4数据更新1．表中插入数据操作4.1：向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567INSERTINTOdeptVALUES('11','自动控制系','李其余','')操作4.2：向student表插入一条记录，学号070201001，姓名为王小五，性别为男，出生日期为1990年9月9日，系号为11，其余字段为NULL或默认值INSERTINTOstudent(st_id,st_nm,st_sex,st_birth,st_dpid)VALUES('070201001','王小五','男','1990.9.9','11')操作4.3：向couse表插入一条记录，课程号1234，课程名为操作系统，其余字段为NULL或默认值INSERTINTOcouse(cs_id,cs_nm)VALUES('1234','操作系统')操作4.4：向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值INSERTINTOslt_couse(cs_id,st_id)VALUES('1234','070201001')2．修改表中数据操作4.5：修改student表记录，将王小五的入学成绩改为88UPDATEstudentSETst_score=88WHEREst_nm='王小五'操作4.6：修改couse表记录，将所有记录的学分改为4，学时改为64UPDATEcouseSETcs_tm=64,cs_sc=4操作4.7：修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77UPDATEslt_couseSETscore=77WHEREcs_id='1234'ANDst_id='070201001'3．删除表中数据操作4.8：删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除DELETEFROMslt_couseWHEREcs_id='1234'ANDst_id='070201001'操作4.9：删除couse表记录，将课程号为1234的记录删除DELETEFROMcouseWHEREcs_id='1234'5数据查询（1）——简单查询（1）查询表中所有的列：查询所有系的信息SELECT*FROMdept（2）查询表中指定列的信息：查询所有的课程号及课程名称SELECTcs_id,cs_nmFROMcouse（3）在查询列表中使用列表达式：在查询student表时使用列表达式：入学成绩+400SELECTst_id,st_nm,st_score,st_score+400ASnew_scoreFROMstudent（4）重新命名查询结果：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话SELECTdp_idAS系号,dp_nmAS系名,dp_drtAS系主任,dp_telAS联系电话FROMdept：使用"="号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分SELECT课程号=cs_id,课程名=cs_nm,学分=cs_sc,学时=cs_sc*16FROMcouse（5）增加说明列：查询dept表的系号、系名与系主任，向查询结果中插入说明列：系号、系名与系主任SELECT'系号:',st_id,'系名:',st_nm,'系主任:',st_drtFROMdept（6）查询列表中使用系统函数：显示所有学生的学号、姓名、性别与入学年份SELECTst_id,st_nm,st_sex,DATEPART(yy,st_birth)AS入学年份FROMstudent：显示所有学生学号、姓名、性别与班级(学号前6位)SELECTst_id,st_nm,st_sex,LEFT(st_id,6)AS班级FROMstudent（7）消除查询结果中的重复项：显示所有学生班级SELECTDISTINCTLEFT(st_id,6)AS班级FROMstudent（8）取得查询结果的部分行集：显示前5条学生记录信息SELECTTOP5*FROMstudent：显示前25%条学生记录信息SELECTTOP25PERCENT*FROMstudent：显示前n条学生记录信息，n为局部变量DECLARE@nINTSET@n=4SELECTTOP@n*FROMstudent6数据查询（2）——条件查询1．使用关系表达式表示查询条件操作6.1：查询dept表中系号为11的院系信息SELECT*FROMdeptWHEREdp_id='11'：查询student表中11系的学生学号、姓名、性别与所在系编号SELECTst_id,st_nm,st_sex,st_dpidFROMstudentWHEREst_dpid='11'：查询student表中2008年及以后入学的学生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)>=2008：在查询student表080808班学生的学号、姓名、性别与入学成绩SELECTst_id,st_nm,st_sex,st_scoreFROMstudentWHERELeft(st_id,6)='080808'2．使用逻辑表达式表示查询条件：查询student表中非11系的学生信息SELECT*FROMstudentWHERENOT(st_dpid='11')：查询选修了1002号课程且成绩在60以下的学生学号SELECTst_idFROMslt_couseWHERE(cs_id='1002')AND(score<60)：查询2007年入学的11系所有男生信息SELECT*FROMstudentWHEREDATEPART(yy,st_date)=2007ANDst_dpid='11'ANDst_sex='男'：查询11系与12系的学生信息SELECT*FROMstudentWHEREst_dpid='11'ORst_dpid='12'：查询11系与12系所有2007年入学的学生信息SELECT*FROMstudentWHERE(st_dpid='11'ORst_dpid='12')ANDDATEPART(yy,st_date)=20073．使用LIKE关键字进行模糊查询操作6.10：查询所有“计算机”开头的课程信息SELECT*FROMcouseWHEREcs_nmLIKE'计算机%'操作6.11：查询所有由三个字组成的“王”姓学生信息SELECT*FROMstudentWHEREst_nmLIKE'王__'操作6.12：查询所有课程名中包含“信息”的课程信息SELECT*FROMcouseWHEREcs_nmLIKE'%信息%'操作6.13：查询学生姓名介于王姓到张姓的信息SELECT*FROMstudentWHEREst_nmLIKE'[王-张]%'4．使用Between…And关键字进行查询操作6.14：查询在1989.7.1到1990之间出生的学生信息SELECTst_id,st_nm,st_sex,st_birthFROMstudentWHEREst_birthBETWEEN'1981.7.1'AND'1999.6.30'操作6.15：查询选修了1001号课程且成绩在60到80之间的学生选课信息SELECT*FROMslt_couseWHEREcs_id='1001'AND(scoreBETWEEN60AND80)5．使用IN关键字进行查询操作6.16：查询11系、12系、13系的学生信息SELECT*FROMstudentWHEREst_dpidIN('11','12','13')操作6.17：查询所有张，王，李，赵姓的学生的学号、姓名、性别SELECTst_id,st_nm,st_sexFROMstudentWHERELeft(st_nm,1)IN('张','王','李','赵')6．使用[NOT]NULL关键字进行查询操作6.18：查询所有生源为非空的学生信息SELECT*FROMstudentWHEREst_fromISNOTNULL操作6.19：查询选修了1001号课程且成绩为空的学生选课信息SELECT*FROMslt_couseWHEREcs_id='1001'ANDscoreISNULL7数据查询（3）——查询排序及查询结果存储操作7.1：查询课程信息，按课程名称降序排序SELECT*FROMcouseORDERBYcs_nmDESC操作7.2：查询选修了1001号课程成绩非空的学生学号与成绩，并按成绩降序排序SELECTst_id,scoreFROMslt_corseWHEREcs_id='1001'ANDscoreISNOTNULLORDERBYscoreDESC操作7.3：查询11系学生学号、姓名与年龄，按年龄升序排序SELECTst_id,st_nm,DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)ASageFROMstudentORDERBYageASC：查询学生信息，按姓名升序排序，再按系号降序排序SELECT*FROMstudentORDERBYst_nm,st_dpidDESC：创建学生表副本student01，仅保留学生学号、姓名与性别SELECTst_id,st_nm,st_sexINTOstudent01FROMstudent：查询陕西籍学生，将结果保存在新表st_shanxiSELECT*INTOst_shanxiFROMstudentWHEREst_from='陕西省'：查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001SELECT*INTOslt1001FROMslt_corseWHEREcs_id='1001'ORDERBYst_id：用局部变量@stage保存学生张三的年龄DECLARE@stageintSELECT@stage=DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)FROMstudentWHEREst_nm='张三'：用局部变量@name与@stscore保存070101班按学号排序后最后一个学生的姓名与入学成绩DECLARE@namenVarChar(8),@stscoreintSELECT@name=st_nm,@stscore=st_scoreFROMstudentWHERELEFT(st_id,6)='070101'ORDERBYst_id8数据查询（4）——查询统计及汇总：查询课程总数SELECTCOUNT(*)FROMcouse：查询选修1001号课程的学生人数SELECTCOUNT(st_id)FROMslt_couseWherecs_id='1001'操作8.3：查询被选修课程的数量SELECTCOUNT(DISTINCTcs_id)FROMslt_couse：查询选修070101班学生的平均入学成绩SELECTAVG(st_score)FROMstudentWHERELEFT(st_id,6)='070101'操作8.5：查询070101001号学生选修课程的数量、总分以及平均分SELECTCOUNT(cs_id)AS课程数量,SUM(score)AS总分,AVG(score)AS平均分FROMslt_couseWHEREst_id='070101001'：查询选修1001号课程的学生人数、最高分、最低分与平均分SELECT COUNT(*)AS学生人数, MAX(score)AS最高分, MIN(score)AS最低分, AVG(score)AS平均分FROMslt_couseWHEREcs_id='1001'：求各个课程号与相应的选课人数SELECTcs_id,COUNT(st_id)FROMslt_couseGROUPBYcs_id操作：统计各班人数SELECTLEFT(st_id,6)AS班级,COUNT(st_id)AS人数FROMstudentGROUPBYLEFT(st_id,6)：依次按班级、系号对学生进行分类统计人数、入学平均分SELECTst_dpidAS系号,LEFT(st_id,6)AS班级,COUNT(st_nm)AS人数,AVG(st_score)AS均分FROMstudentGROUPBYLEFT(st_id,6),st_dpid：查询选修了均分在75以上的课程号及均分SELECTcs_idAS课程编号,AVG(score)AS均分FROMslt_couseGROUPBYcs_idHAVINGAVG(score)>75：查询选修了2门以上课程的学生学号SELECTst_idFROMslt_couseGROUPBYst_idHAVINGCOUNT(*)>2：明细汇总年龄<20的学生，并汇总学生数量、平均年龄SELECTst_nm,DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)AS年龄FROMstudentWHEREDATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20COMPUTECOUNT(st_nm),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth))：按班级明细汇总成绩<85分的学生，汇总学生数、均分SELECTst_nm,LEFT(st_id,6)AS班级,st_scoreFROMstudentWHEREst_score<85ORDERBY班级COMPUTECOUNT(st_nm),AVG(st_score)BY班级9数据查询（5）——连接查询操作9.1：用SQLServer形式连接查询学生学号、姓名、性别及其所选课程编号SELECTa.st_id,st_nm,st_sex,cs_idFROMstudenta,slt_cousebWHEREa.st_id=idORDERBYa.st_id操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号SELECTa.st_id,st_nm,st_sex,cs_idFROMstudentaINNERJOINslt_cousebONa.st_id=_idORDERBYa.st_id操作9.3：用SQLServer形式连接查询学生学号、姓名及其所选课程名称及成绩SELECTa.st_id,st_nm,cs_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=idORDERBYa.st_id操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩SELECTa.st_id,st_nm,cs_nm,scoreFROMslt_couseaINNERJOINstudentbONa.st_id=_idINNERJOINcousecON_id=_idORDERBYb.st_id操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩SELECTa.st_id,st_nm,scoreFROMstudenta，slt_cousebWHEREa.st_id=idANDb.cs_id='1002'ORDERBYb.st_id操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩SELECTa.st_id,st_nm,scoreFROMstudenta,slt_couseb,cousecWHEREa.st_id=idANDb.cs_id=c.cs_idANDc.cs_nm='数据结构'ORDERBYa.st_id操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名SELECTa.st_id,st_nm,scoreFROMstudentaLEFTOUTERJOINslt_cousebONa.st_id=idWHEREb.cs_idISNULLORDERBYb.st_id操作9.8：用右外连接查询选修各个课程的学生学号10数据查询（6）——子查询：用子查询对各班人数进行查询（新增列）SELECTDISTINCTLEFT(a.st_id,6)AS班级,人数=(SELECTCOUNT(st_id)FROMstudentb WHERELEFT(a.st_id,6)=LEFT(b.st_id,6))FROMstudentaORDERBYLEFT(a.st_id,6)ASC：用子查询对各课程的选课人数进行查询（新增列）SELECTDISTINCTa.cs_id,人数=(SELECTCOUNT(st_id)FROMslt_couseb WHEREa.cs_id=b.cs_id)FROMslt_couseaORDERBYa.cs_idASC：查询选修了1002课程成绩不及格的学生的学号、姓名与性别，并按姓名升序排序通过子查询实现：——使用IN关键字SELECTst_id,st_nm,st_sexFROMstudentWHEREst_idIN( SELECTst_idFROMslt_couse WHEREcs_id='1002'ANDscore<60 )ORDERBYst_nm通过子查询实现：——使用比较运算符SELECTst_id,st_nm,st_sexFROMstudentaWHERE( SELECTscoreFROMslt_couseb WHEREa.st_id=b.st_idANDcs_id='1002')<60ORDERBYst_nm：查询“东方红”同学所在班的学生信息，并按姓名降序排序通过子查询实现：——IN运算符SELECTst_id,st_nm,st_sexFROMstudentWHERELEFT(st_dpid,6)IN( SELECTLeft(st_dpid,6)FROMstudentWHEREst_nm='东方红' )ORDERBYst_nmDESC通过自连接查询实现：——JOINSELECTs1.st_id,s1.st_nm,s1.st_sexFROMstudents1JOINstudents2ONLEFT(s1.st_dpid,6)=LEFT(s2.st_dpid,6)WHEREs2.st_nm='东方红'ORDERBYs2.st_nm：查询其它班比070511班某一学生的1002号课程成绩高的学生信息（ANY/ALL）SELECT*FROMslt_couseWHEREscore>ANY( SELECTscoreFROMslt_couse WHEREcs_id='1002'ANDLEFT(st_id,6)='070511' )ANDLEFT(st_id,6)<>'070511'ANDcs_id='1002'操作10.