第5章SQL与JDBC=创新教育基础与实践=大连理工大学

LOGO第5章SQL与JDBC孙焘重点：SQL语句概念1select语句2更新操作语句3JDBC接口4难点：连接查询语句1groupby语句2级联更新语句3JDBC中类的运行机制45.1SQL概念SQL简介SQL全称是“结构化查询语言(StructuredQueryLanguage)”SQL语句主要完成如下功能：(1)数据定义功能:创建、修改、删除表、视图等(2)数据操纵功能:数据查询：查询、统计、分组、排序等操作；数据更新：插入、删除、修改等操作。(3)数据控制功能:①安全性控制：通过对数据库用户授权、收权实现存取控制，保证数据库安全性；②完整性控制：完整性约束条件。5.2SQL语句5.2.1增删改语句练习1：创建“学生”表StudentCREATETABLEStudent(Snochar(8)NOTNULL,Snamevarchar(10),Ssexchar(1),Sbirthdatetime,Sdeptchar(3),CONSTRAINTc1PRIMARYKEY(Sno))练习2：(1)向Student表增加“入学时间”列Scome，其数据类型为日期型；(2)将姓名字段Sname长度改为20；(3)删除Sbirth列；(4)删除学生表；(1)ALTERTABLEStudentADDScomeDATETIME(2)ALTERTABLEStudentALTERCOLUMNSnamevarchar(20)(3)ALTERTABLEStudentDROPCOLUMNSbirth(4)DROPTABLEStudent5.2.2查询语句SELECT〈目标列组〉FROM〈数据源〉[WHERE〈元组选择条件〉][GROUPBY〈分列组〉[HAVING〈组选择条件〉]][ORDERBY〈排序列1〉〈排序要求1〉[，…n]]；练习3：查询学号、姓名查询学生表的所有字段信息SELECTSno,SnameFROMStudent有两种方式：SELECTSno,Sname,Ssex,SBirth,SdeptFROMstudentSELECT*FROMStudent练习4：求选修课号为C1且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出SELECTSno,Grade*0.8FROMCSWHERECno='C1'ANDGradeBETWEEN80AND90说明如下：Grade*0.8表示SELECT语句不仅支持字段输出，而且支持表达式输出；WHERE子句后面是元组选择条件，是个逻辑表达式。练习5：根据课程表，把所有姓张的老师姓名打印出来，并要求记录不要重复SELECTDISTINCTCTeacherFROMCourseWHERECTeacherlike'张%'说明如下：DISTINCT关键字表示去除重复记录；like是SQL语言实现模糊匹配的保留字，%为通配符。练习6：打印课号为C1的选课学生学号，成绩转换为ABCD等级输出SELECTSno,casewhengrade=85then'A'whengrade85andgrade=75then'B'whengrade75andgrade=60then'C'else'D'endaslevelFROMCS练习7：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列SELECTStudent.sno,Student.Sname,CS.cno,CS.GradeFROMStudent,CSWHEREStudent.sno=CS.snoORDERBYCS.GradeDESC练习8：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列；要求没有成绩的学生名单仍要显示，成绩一栏输出0SELECTStudent.Sno,Student.Sname,CS.cno,isNull(CS.Grade,0)GradeFROM(StudentLeftOuterJoinCSonStudent.sno=CS.sno)若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在另一个关系的其他属性位置上放上Null。练习9：将上一练习的查询“固化”为视图CREATEVIEWCS_ViewASSELECTStudent.Sno,Student.Sname,CS.cno,isNull(CS.Grade,0)GradeFROM(StudentLeftOuterJoinCSonStudent.sno=CS.sno)练习10：列出得过100分的学生的学号、姓名SELECTsno,snameFROMStudentWHEREExists(SELECT1FROMCSWHERECS.sno=Student.snoANDgrade=100)练习11：列出没有选C01课程的学生的学号、姓名SELECTsno,snameFROMStudentWHERENotExists(SELECT1FROMCSWHERECS.sno=Student.snoAndcno='C01')练习12：统计各系人数SELECTsdept，COUNT(*)asstu_countFROMStudentGROUPBYsdeptGROUPBY语句实现将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现。需要注意的是，只有出现在GROUPBY子句中的属性，才可出现在Select子句中。COUNT为聚集函数，聚集函数共5种：(1)平均值：AVG(2)总和：SUM(3)最小值：MIN(4)最大值：MAX计数：COUNT练习13：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。SELECTsno，COUNT(sno)FROMCSWHEREgrade60GROUPBYsnoHAVINGCOUNT(sno)=2HAVING子句和WHERE子句都有条件选择的功能，但两者还是有很大区别的：Where决定哪些元组被选择参加运算，作用于关系中的元组；Having决定哪些分组符合要求，作用于分组。因此聚合函数的条件关系必须用Having，Where中不应出现聚合函数。5.2.3插入语句练习14：新增一个学生信息07001,张三,1988-03-09,F,CSINSERTINTOStudent(Sno,Sname,Sbirth,Ssex,Sdept)VALUES('07001','张三','1988-03-09','F','CS')练习15：给CS系的学生开设必修课C05，建立选课信息INSERTINTOCSSELECTsno,cno,nullFROMStudent,CourseWHEREsdept=‘CS’andcno=‘C05’5.2.4删除语句DELETE语句的一般形式：DELETE[FROM][数据库名.所有者名.]目的表名FROM原表名][WHERE逻辑表达式]练习16：在学生表中删除出生日期在1988-01-01之前的学生DELETEFROMStudentWHERESbirth’1988-01-01’练习17：在学生表中删除课程C01没有成绩的学生DELETEStudentFROMStudent,CSWHERECS.Cno='C01'andisnull(CS.Grade,0)=0andCS.Sno=Student.Sno5.2.5更新语句UPDATE语句的一般形式如下：UPDATE[数据库名.所有者名.]视图名SET列名＝表达式,…[FROM原表名,…][WHERE逻辑表达式]从SQLSERVER2000的UPDATE语法形式上看，UPDATE可以接FROM子句（但Oracle等就不可以）。练习19：将课程C01成绩大于等于95的学生转入PH系UPDATEStudentSETStudent.Sdept='PH2'FROMStudent,CSWHERECS.Cno='C01'andCS.Grade='60'andCS.Sno=Student.Sno5.3存储过程：存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，当执行存储过程时，只需调用execute,即可自动完成命令优点：1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。3.存储过程可以重复使用,4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权语法格式Createprocedureprocedue_name[@parameterdata_type][output][with]{recompile|encryption}assql_statementprocedue_name：为过程名称@parameter：为参数名称data_type：为参数的类型output：表示此参数是可传回的with{recompile|encryption}recompile:表示每次执行此存储过程时都重新编译一次encryption:所创建的存储过程的内容会被加密练习20：一个简单的存储过程，查询学生表中的第一条记录CREATEPROCEDUREStudent_sASSELECTTOP1Sno,Sname,Ssex,Sbirth,SdeptfromStudentGO练习21：查询姓名为张三，课程名为计算机的存储过程CREATEPROCEDUREStudent_score(@param1varchar(10),@param2varchar(50))ASSelectStudent.Sname,Course.Cname,CS.GradefromStudent,Course,CSwhereSname=@param1andCname=@param2andStudent.Sno=CS.SnoandCS.Cno=Course.CnoGO然后执行存储过程Student_score：ExecStudent_score'张三','计算机'练习22：加入一条记录到表Department,并查询以及输出此表中院系总数Createprocinsert_Department@param1char(3),@param2varchar(50),@param3intoutputWithencryption---------加密asinsertintoDepartment(Dno,Dname)Values(@param1,@param2)select@param3=count(Dno)fromDepartmentgo然后执行存储过程insert_Department：declare@countDnoint--变量的声明execinsert_Department'001','创新学院',@countDnooutputprint@countDno5.4触发器：触发器是一种特殊的存储过程，当表中数据被修改时，SQLServer将自动执行触发器。使用触发器可以实施较为复杂的数据完整性约束。触发器优点：触发器能够实施的检查和操作比主键和外键约束、CHECK约束和规则对象等更为复杂。触发器建立在表一级，它与特定的数据修改事件相对应。CREATETRIGGERtrigger_nameONtable_nameFOR{INSERT,UPDATE,DELETE}[WITHENCRYPTION]ASsql_statements解释：trigger_name：指定触发器的名称，它必须遵守SQLServer的命名规则，而且同一个数据库中不允许出现触发器名称相同的情况。table_name：指定触发器所在的表名INSERT,UPDATE,DELETE：指定触发条件ENCRYPTION：SQLServer将触发器的