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

第5章SQL与JDBC,重点：,难点：,5.1SQL概念,SQL简介,SQL全称是“结构化查询语言(StructuredQueryLanguage)”SQL语句主要完成如下功能：(1)数据定义功能:创建、修改、删除表、视图等(2)数据操纵功能:数据查询：查询、统计、分组、排序等操作；数据更新：插入、删除、修改等操作。(3)数据控制功能:安全性控制：通过对数据库用户授权、收权实现存取控制，保证数据库安全性；完整性控制：完整性约束条件。,5.2SQL语句,5.2.1增删改语句,练习1：创建“学生”表StudentCREATETABLEStudent(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)DROPTABLEStudent,5.2.2查询语句,SELECT目标列组FROM数据源WHERE元组选择条件GROUPBY分列组HAVING组选择条件ORDERBY排序列1排序要求1，n；,练习3：查询学号、姓名查询学生表的所有字段信息SELECTSno,SnameFROMStudent有两种方式：SELECTSno,Sname,Ssex,SBirth,SdeptFROMstudentSELECT*FROMStudent,练习4：求选修课号为C1且成绩在8090之间的学生学号和成绩，并将成绩乘以系数0.8输出SELECTSno,Grade*0.8FROMCSWHERECno=C1ANDGradeBETWEEN80AND90说明如下：Grade*0.8表示SELECT语句不仅支持字段输出，而且支持表达式输出；WHERE子句后面是元组选择条件，是个逻辑表达式。,练习5：根据课程表，把所有姓张的老师姓名打印出来，并要求记录不要重复SELECTDISTINCTCTeacherFROMCourseWHERECTeacherlike张%说明如下：DISTINCT关键字表示去除重复记录；like是SQL语言实现模糊匹配的保留字，%为通配符。,练习6：打印课号为C1的选课学生学号，成绩转换为ABCD等级输出SELECTSno,casewhengrade=85thenAwhengrade=75thenBwhengrade=60thenCelseDendaslevelFROMCS,练习7：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列SELECTStudent.sno,Student.Sname,CS.cno,CS.GradeFROMStudent,CSWHEREStudent.sno=CS.snoORDERBYCS.GradeDESC,练习8：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列；要求没有成绩的学生名单仍要显示，成绩一栏输出0SELECTStudent.Sno,Student.Sname,CS.cno,isNull(CS.Grade,0)GradeFROM(StudentLeftOuterJoinCSonStudent.sno=CS.sno)若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在另一个关系的其他属性位置上放上Null。,练习9：将上一练习的查询“固化”为视图CREATEVIEWCS_ViewASSELECTStudent.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)FROMCSWHEREgrade=2,HAVING子句和WHERE子句都有条件选择的功能，但两者还是有很大区别的：Where决定哪些元组被选择参加运算，作用于关系中的元组；Having决定哪些分组符合要求，作用于分组。因此聚合函数的条件关系必须用Having，Where中不应出现聚合函数。,5.2.3插入语句,练习14：新增一个学生信息07001,张三,1988-03-09,F,CSINSERTINTOStudent(Sno,Sname,Sbirth,Ssex,Sdept)VALUES(07001,张三,1988-03-09,F,CS),练习15：给CS系的学生开设必修课C05，建立选课信息INSERTINTOCSSELECTsno,cno,nullFROMStudent,CourseWHEREsdept=CSandcno=C05,5.2.4删除语句,DELETE语句的一般形式：DELETEFROM.FROMWHERE,练习16：在学生表中删除出生日期在1988-01-01之前的学生DELETEFROMStudentWHERESbirth=60andCS.Sno=Student.Sno,5.3存储过程：,存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，当执行存储过程时，只需调用execute,即可自动完成命令,优点：,1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。3.存储过程可以重复使用,4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权,语法格式,Createprocedureprocedue_nameparameterdata_typeoutputwithrecompile|encryptionassql_statementprocedue_name：为过程名称parameter：为参数名称data_type：为参数的类型output：表示此参数是可传回的withrecompile|encryptionrecompile:表示每次执行此存储过程时都重新编译一次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_Departmentparam1char(3),param2varchar(50),param3intoutputWithencryption-加密asinsertintoDepartment(Dno,Dname)Values(param1,param2)selectparam3=count(Dno)fromDepartmentgo然后执行存储过程insert_Department：declarecountDnoint-变量的声明execinsert_Department001,创新学院,countDnooutputprintcountDno,5.4触发器：,触发器是一种特殊的存储过程，当表中数据被修改时，SQLServer将自动执行触发器。使用触发器可以实施较为复杂的数据完整性约束。,触发器优点：,触发器能够实施的检查和操作比主键和外键约束、CHECK约束和规则对象等更为复杂。触发器建立在表一级，它与特定的数据修改事件相对应。,CREATETRIGGERtrigger_nameONtable_nameFORINSERT,UPDATE,DELETEWITHENCRYPTIONASsql_statements解释：trigger_name：指定触发器的名称，它必须遵守SQLServer的命名规则，而且同一个数据库中不允许出现触发器名称相同的情况。table_name：指定触发器所在的表名INSERT,UPDATE,DELETE：指定触发条件ENCRYPTION：SQLServer将触发器的定义文本保存在系统分类表syscomments中，选用此项要求SQLServer对该触发器定义文本进行加密存储，以防止第三方用户从syscomments表中读取该触发器定义文本的内容。sql_statement：定义触发器在触发事件发生时(被激活时)所要执行的操作。,5.5JDBC和数据库,JDBC：JavaDataBaseConnectivity（Java数据库连接技术），它是将Java与SQL结合且独立于特定的数据库系统的应用程序编程接口（API它是一种可用于执行SQL语句的JavaAPI，即由一组用Java语言编写的类与接口所组成）。JDBC的主要功能如下：(1)创建与数据库的连接；(2)发送SQL语句到任何关系型数据库中；(3)处理数据并查询结果。,头文件：,importjava.sql.DriverManager;importjava.sql.Statement;importjava.sql.ResultSet;importjava.sql.Connection;importjava.sql.SQLException;,连接数据库：,protectedStringdriverName=com.microsoft.jdbc.sqlserver.SQLServerDriver;protectedStringuserName=sa;/访问数据库的用户名protectedStringuserPwd=password;/访问数据库的密码protectedStringdbName=MyDB;/数据库实例名称protectedStringurl=jdbc:microsoft:sqlserver:/:1433;databaseName=+dbName;/访问数据库URLprotectedConnectionconn=null;protectedStatementsm=null;,打开数据库连接,protectedvoidConnectDB()throwsSQLException,InstantiationException,IllegalAccessException,ClassNotFoundExceptionClass.forName(driverName);conn=DriverManager.getConnection(url,userName,userPwd);sm=conn.createStatement();,简单查询：,Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver);Connectioncon=DriverManager.getConnection(j