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

《第5章SQL与JDBC创新教育基础与实践大连理工大学》由会员分享，可在线阅读，更多相关《第5章SQL与JDBC创新教育基础与实践大连理工大学（45页珍藏版）》请在装配图网上搜索。

1、LOGO第5章 SQL与JDBC孙焘孙焘重点：重点：SQL语句概念1select语句2更新操作语句3JDBC接口4难点：难点：连接查询语句1group by 语句2级联更新语句3JDBC中类的运行机制45.1 SQL概念SQL简介简介v SQL全称是全称是“结构化查询语言结构化查询语言(Structured Query Language)”v SQL语句主要完成如下功能：语句主要完成如下功能：(1)数据定义功能数据定义功能:创建、修改、删除表、视图等创建、修改、删除表、视图等(2)数据操纵功能数据操纵功能:数据查询：查询、统计、分组、排序数据查询：查询、统计、分组、排序等操作；数据更新：插入、

2、删除、修改等操作。等操作；数据更新：插入、删除、修改等操作。(3)数据控制功能数据控制功能:安全性控制：通过对数据库用户授权、收权实现存取安全性控制：通过对数据库用户授权、收权实现存取控制，保证数据库安全性；控制，保证数据库安全性；完整性控制：完整性约束条件。完整性控制：完整性约束条件。5.2 SQL语句5.2.1 增删改语句增删改语句v练习练习1：创建：创建“学生学生”表表StudentvCREATE TABLE Student(Sno char(8)NOT NULL,Sname varchar(10),Ssex char(1),Sbirth datetime,Sdept char(3),C

3、ONSTRAINT c1 PRIMARY KEY(Sno)v练习练习2：v(1)向向Student表增加表增加“入学时间入学时间”列列Scome，其数据类型为日期型，其数据类型为日期型；v(2)将姓名字段将姓名字段Sname长度改为长度改为20；v(3)删除删除Sbirth列；列；v(4)删除学生表；删除学生表；v(1)ALTER TABLE Student ADD Scome DATETIMEv(2)ALTER TABLE Student ALTER COLUMN Sname varchar(20)v(3)ALTER TABLE Student DROP COLUMN Sbirthv(4)D

4、ROP TABLE Student5.2.2 查询语句查询语句SELECT目标列组目标列组FROM数据源数据源WHERE元组选择条件元组选择条件GROUP BY分列组分列组HAVING 组选择组选择条件条件ORDER BY排序列排序列1排序要求排序要求1 ，n；v练习练习3：查询学号、姓名：查询学号、姓名 查询学生表的所有字段信息查询学生表的所有字段信息vSELECT Sno,Sname FROM Studentv有两种方式：有两种方式：SELECT Sno,Sname,Ssex,SBirth,Sdept FROM studentSELECT*FROM Studentv练习练习4：求选修课号为

5、：求选修课号为C1且成绩在且成绩在8090之间的之间的学生学号和成绩，并将成绩乘以系数学生学号和成绩，并将成绩乘以系数0.8输出输出vSELECT Sno,Grade*0.8FROM CSWHERE Cno=C1ANDGrade BETWEEN 80 AND 90说明如下：说明如下：Grade*0.8表示表示SELECT语句不仅支持字段输出，而且支持语句不仅支持字段输出，而且支持表达式输出；表达式输出；WHERE子句后面是元组选择条件，是个逻辑表达式。子句后面是元组选择条件，是个逻辑表达式。v练习练习5：根据课程表，把所有姓张的老师姓：根据课程表，把所有姓张的老师姓名打印出来，并要求记录不要重

6、复名打印出来，并要求记录不要重复SELECT DISTINCT CTeacherFROM CourseWHERE CTeacher like 张张%说明如下：说明如下：DISTINCT关键字表示去除重复记录；关键字表示去除重复记录；like是是SQL语言实现模糊匹配的保留字，语言实现模糊匹配的保留字，%为通配符。为通配符。v练习练习6：打印课号为：打印课号为C1的选课学生学号，成绩转换的选课学生学号，成绩转换为为ABCD等级输出等级输出SELECT Sno,case when grade=85 then Awhen grade=75 then Bwhen grade=60 then C els

7、e Dend as levelFROM CSv练习练习7：列出所有学生的学号、姓名、课号：列出所有学生的学号、姓名、课号、成绩、成绩,并按成绩降序排列并按成绩降序排列vSELECT Student.sno,Student.Sname,CS.cno,CS.GradeFROM Student,CSWHERE Student.sno=CS.snoORDER BY CS.Grade DESCv练习练习8：列出所有学生的学号、姓名、课号：列出所有学生的学号、姓名、课号、成绩、成绩,并按成绩降序排列；要求没有成绩的学生并按成绩降序排列；要求没有成绩的学生名单仍要显示，成绩一栏输出名单仍要显示，成绩一栏输出

8、0vSELECT Student.Sno,Student.Sname,CS.cno,isNull(CS.Grade,0)GradeFROM (Student Left Outer Join CSon Student.sno=CS.sno)v若一个关系中的元组在另一个关系中没有相匹配的若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在另一个关系元组，则这些元组会在连接结果中出现，并在另一个关系的其他属性位置上放上的其他属性位置上放上NullNull。v练习练习9：将上一练习的查询：将上一练习的查询“固化固化”为视图为视图vCREATE VIEW CS_View

9、ASvSELECT Student.Sno,Student.Sname,CS.cno,isNull(CS.Grade,0)GradeFROM (Student Left Outer Join CSon Student.sno=CS.sno)v练习练习10：列出得过：列出得过100分的学生的学号、姓名分的学生的学号、姓名vSELECT sno,sname FROM StudentWHERE Exists(SELECT 1 FROM CSWHERE CS.sno=Student.sno ANDgrade=100)v练习练习11：列出没有选：列出没有选C01课程的学生的学号课程的学生的学号、姓名、姓

10、名vSELECT sno,sname FROM StudentWHERE Not Exists(SELECT 1 FROM CSWHERE CS.sno=Student.sno Andcno=C01)v练习练习12：统计各系人数：统计各系人数vSELECT sdept，COUNT(*)as stu_countFROM StudentGROUP BY sdeptvGROUP BY 语句实现将查询结果集按某一语句实现将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现。需要注意的是，只有出以一个元组的形式出现。需要注意的是，只有

11、出现在现在GROUP BY子句中的属性，才可出现在子句中的属性，才可出现在Select子句中。子句中。COUNT为聚集函数，聚集函数共为聚集函数，聚集函数共5种：种：(1)平均值：平均值：AVG(2)总和：总和：SUM(3)最小值：最小值：MIN(4)最大值：最大值：MAX计数：计数：COUNTv练习练习1313：列出具有两门（含）以上不及格的：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。学生的学号、不及格的课目数。vSELECT sno，COUNT(sno)FROM CSWHERE grade=2vHAVING子句和子句和WHERE子句都有条件选择子句都有条件选择的功能，但两者

12、还是有很大区别的：的功能，但两者还是有很大区别的：Where 决定决定哪些元组被选择参加运算，作用于关系中的元组哪些元组被选择参加运算，作用于关系中的元组；Having 决定哪些分组符合要求，作用于分组决定哪些分组符合要求，作用于分组。因此聚合函数的条件关系必须用。因此聚合函数的条件关系必须用Having，Where中不应出现聚合函数。中不应出现聚合函数。5.2.3 插入语句插入语句v练习练习1414：新增一个学生信息：新增一个学生信息 07001,07001,张三张三,1988-03-09,F,CS1988-03-09,F,CSvINSERT INTO Student(Sno,Sname,S

13、birth,Ssex,Sdept)VALUES(07001,张三张三,1988-03-09,F,CS)v练习练习1515：给：给CSCS系的学生开设必修课系的学生开设必修课C05C05，建立，建立选课信息选课信息vINSERT INTO CSSELECT sno,cno,null FROM Student,Course WHERE sdept=CSand cno=C05 5.2.4 删除语句删除语句vDELETE语句的一般形式：语句的一般形式：DELETE FROM.FROM WHERE v练习练习1616：在学生表中删除出生日期在：在学生表中删除出生日期在1988-1988-01-0101-

14、01之前的学生之前的学生vDELETE FROM Student WHERE Sbirth1988-01-01v练习练习1717：在学生表中删除课程：在学生表中删除课程C01C01没有成绩的没有成绩的学生学生vDELETE Student FROM Student,CS WHERE CS.Cno=C01 and isnull(CS.Grade,0)=0 and CS.Sno=Student.Sno5.2.5 更新语句更新语句vUPDATE语句的一般形式如下：语句的一般形式如下：UPDATE.SET,FROM,WHERE 从从SQLSERVER2000的的UPDATE语法形式上看，语法形式上看，

15、UPDATE可以接可以接FROM子句（但子句（但Oracle等就不可以）。等就不可以）。v练习练习19：将课程：将课程C01成绩大于等于成绩大于等于95的学生的学生转入转入PH系系vUPDATE StudentSET Student.Sdept=PH2FROM Student,CSWHERE CS.Cno=C01 and CS.Grade=60 and CS.Sno=Student.Sno5.3 存储过程：存储过程：v存储过程就是将常用的或很复杂的工作存储过程就是将常用的或很复杂的工作，预先用，预先用SQL语句写好并用一个指定的名语句写好并用一个指定的名称存储起来，当执行存储过程时，只需调称存

16、储起来，当执行存储过程时，只需调用用execute,即可自动完成命令即可自动完成命令优点：优点：v1.存储过程只在创造时进行编译，以后每次存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，执行存储过程都不需再重新编译，v2.当对数据库进行复杂操作时当对数据库进行复杂操作时(如对多个表如对多个表进行进行Update,Insert,Query,Delete时），可时），可将此复杂操作用存储过程封装起来与数据库提供将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。的事务处理结合一起使用。v3.存储过程可以重复使用存储过程可以重复使用,4.安全性高安全性高,可设可设定只

17、有某此用户才具有对指定存储过程的使用权定只有某此用户才具有对指定存储过程的使用权语法格式语法格式Create procedure procedue_nameparameter data_typeoutputwithrecompile|encryptionassql_statement procedue_name：为过程名称：为过程名称parameter：为参数名称：为参数名称 data_type：为参数的类型：为参数的类型output：表示此参数是可传回的：表示此参数是可传回的with recompile|encryptionrecompile:表示每次执行此存储过程时都重新编译一次表示每次执

18、行此存储过程时都重新编译一次encryption:所创建的存储过程的内容会被加密所创建的存储过程的内容会被加密v练习练习20：一个简单的存储过程，查询学生表：一个简单的存储过程，查询学生表中的第一条记录中的第一条记录vCREATE PROCEDURE Student_sAS SELECT TOP 1 Sno,Sname,Ssex,Sbirth,Sdeptfrom StudentGOv练习练习2121：查询姓名为张三，课程名为计算机：查询姓名为张三，课程名为计算机的存储过程的存储过程vCREATE PROCEDURE Student_score(param1 varchar(10),param2

19、 varchar(50)AS Select Student.Sname,Course.Cname,CS.Grade from Student,Course,CS where Sname=param1 and Cname=param2 and Student.Sno=CS.Sno and CS.Cno=Course.CnoGO然后执行存储过程然后执行存储过程Student_score：Exec Student_score 张三张三,计算机计算机v练习练习2222：加入一条记录到表：加入一条记录到表Department,Department,并查询以及输并查询以及输出此表中院系总数出此表中院系总数

20、vCreate proc insert_Departmentparam1 char(3),param2 varchar(50),param3 int outputWith encryption -加密加密as insert into Department(Dno,Dname)Values(param1,param2)select param3=count(Dno)from Departmentgo然后执行存储过程然后执行存储过程insert_Department：declare countDno int-变量的声明变量的声明exec insert_Department 001,创新学院创新学院

21、,countDno outputprint countDno5.4 触发器：触发器：v 触发器是一种特殊的存储过程，当触发器是一种特殊的存储过程，当表中数据被修改时，表中数据被修改时，SQL Server将将自动执行触发器。使用触发器可以实自动执行触发器。使用触发器可以实施较为复杂的数据完整性约束。施较为复杂的数据完整性约束。触发器优点：触发器优点：v触发器能够实施的检查和操作比主键和外键触发器能够实施的检查和操作比主键和外键约束、约束、CHECK约束和规则对象等更为复杂。约束和规则对象等更为复杂。v触发器建立在表一级，它与特定的数据修改触发器建立在表一级，它与特定的数据修改事件相对应。事件相

22、对应。vCREATE TRIGGER trigger_nameON table_nameFOR INSERT,UPDATE,DELETEWITH ENCRYPTIONAS sql_statementsv解释：解释：v trigger_name：指定触发器的名称，它必须遵守：指定触发器的名称，它必须遵守SQL Server的命名规的命名规则，而且同一个数据库中不允许出现触发器名称相同的情况。则，而且同一个数据库中不允许出现触发器名称相同的情况。vtable_name：指定触发器所在的表名：指定触发器所在的表名vINSERT,UPDATE,DELETE：指定触发条件：指定触发条件vENCRYPTI

23、ON：SQL Server将触发器的定义文本保存在系统分类表将触发器的定义文本保存在系统分类表 syscomments中，选用此项要求中，选用此项要求SQL Server对该触发器定义文本进行加密对该触发器定义文本进行加密存储，以防止第三方用户从存储，以防止第三方用户从syscomments表中读取该触发器定义文本的内表中读取该触发器定义文本的内容。容。vsql_statement：定义触发器在触发事件发生时：定义触发器在触发事件发生时(被激活时被激活时)所要执行的操所要执行的操作。作。5.5 JDBC和数据库和数据库vJDBC：Java DataBase Connectivity（Java

24、数据库连接技术），它是数据库连接技术），它是 将将Java与与SQL结合结合且独立于特定的数据库系统的应用程序编程接口（且独立于特定的数据库系统的应用程序编程接口（API它是一种可用于执行它是一种可用于执行SQL语句的语句的Java API，即由一组用即由一组用Java语言编写的类与接口所组语言编写的类与接口所组 成）。成）。vJDBC的主要功能如下：的主要功能如下：(1)创建与数据库的连接；创建与数据库的连接；(2)发送发送SQL语句到任何关系型数据库中；语句到任何关系型数据库中；(3)处理数据并查询结果。处理数据并查询结果。头文件：头文件：vimport java.sql.DriverMa

25、nager;vimport java.sql.Statement;vimport java.sql.ResultSet;vimport java.sql.Connection;vimport java.sql.SQLException;连接数据库：连接数据库：vprotected String driverName=com.microsoft.jdbc.sqlserver.SQLServerDriver;vprotected String userName =sa;/访问数据库的用户名访问数据库的用户名vprotected String userPwd =password;/访问数据库的密码访

26、问数据库的密码vprotected String dbName =MyDB;/数据库实例名称数据库实例名称vprotected String url =jdbc:microsoft:sqlserver:/127.0.0.1:1433;databaseName=+dbName;/访问数据库访问数据库URLvprotected Connection conn =null;vprotected Statement sm =null;打开数据库连接vprotected void ConnectDB()throws SQLException,InstantiationException,IllegalA

27、ccessException,ClassNotFoundException Class.forName(driverName);conn=DriverManager.getConnection(url,userName,userPwd);sm =conn.createStatement();简单查询：简单查询：vClass.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver);Connection con=DriverManager.getConnection(jdbc:microsoft:sqlserver:/127.0.0.1:1433

28、;databaseName=MyDB,sa,password);Statement stmt=con.createStatement();ResultSet rs=stmt.executeQuery(select Sno,Sname from Student);while(rs.next()System.out.println(rs.getString(1)+rs.getString(2);rs.close();stmt.close();con.close();JDBC实现对数据库的一般查询的过程如下实现对数据库的一般查询的过程如下v创建创建Statement对象（要想执行一个对象（要想执行一

29、个SQL查询语句，查询语句，必须首先创建出必须首先创建出Statement对象，它封装代表要执行的对象，它封装代表要执行的SQL语句）并执行语句）并执行SQL语句以返回一个语句以返回一个ResultSet对象，这可以对象，这可以通过通过Connection类中的类中的createStatement()方法来实现方法来实现。v(2)执行一个执行一个SQL查询语句，以查询数据库中的数据。查询语句，以查询数据库中的数据。Statement接口提供了三种执行接口提供了三种执行SQL语句的方法：语句的方法：executeQuery()、executeUpdate()和和execute()。具体使用哪一个方法由具体使用哪一个方法由SQL语句本身来决定。语句本身来决定。v(3)Statement类中的类中的executeQuery()方法方法,输入输入参数是一个标准的参数是一个标准的SQL查询语句，其返回值是一个查询语句，其返回值是一个ResultSet类的对象。类的对象。LOGO