第 5 章 SQL 与 JDBC

第5章 SQL与JDBC 孙焘

重点： 1 2 3 4 select语句更新操作语句 JDBC接口 SQL语句概念

1 2 3 4 group by 语句级联更新语句 JDBC中类的运行机制连接查询语句难点：

5.1 SQL概念

SQL简介 • SQL全称是“结构化查询语言(Structured Query Language)” • SQL语句主要完成如下功能： (1)数据定义功能:创建、修改、删除表、视图等 (2)数据操纵功能:数据查询：查询、统计、分组、排序等操作；数据更新：插入、删除、修改等操作。 (3)数据控制功能: ①安全性控制：通过对数据库用户授权、收权实现存取控制，保证数据库安全性； ②完整性控制：完整性约束条件。

5.2 SQL语句

5.2.1 增删改语句 • 练习1：创建“学生”表Student • CREATE TABLE Student ( Sno char(8) NOT NULL, Sname varchar(10), Ssex char(1), Sbirth datetime, Sdept char(3), CONSTRAINT c1 PRIMARY KEY(Sno) )

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

5.2.2 查询语句 SELECT〈目标列组〉 FROM〈数据源〉 [WHERE〈元组选择条件〉] [GROUP BY〈分列组〉[HAVING 〈组选择条件〉]] [ORDER BY〈排序列1〉〈排序要求1〉 [，…n]]；

练习3：查询学号、姓名 查询学生表的所有字段信息 • SELECT Sno, Sname FROM Student • 有两种方式： SELECT Sno, Sname, Ssex, SBirth, Sdept FROM student SELECT * FROM Student

练习4：求选修课号为C1且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出练习4：求选修课号为C1且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出 • SELECT Sno, Grade*0.8 FROM CS WHERE Cno = 'C1' AND Grade BETWEEN 80 AND 90 说明如下： Grade*0.8表示SELECT语句不仅支持字段输出，而且支持表达式输出； WHERE子句后面是元组选择条件，是个逻辑表达式。

练习5：根据课程表，把所有姓张的老师姓名打印出来，并要求记录不要重复练习5：根据课程表，把所有姓张的老师姓名打印出来，并要求记录不要重复 SELECT DISTINCT CTeacher FROM Course WHERE CTeacher like '张%' 说明如下： DISTINCT关键字表示去除重复记录； like是SQL语言实现模糊匹配的保留字，%为通配符。

练习6：打印课号为C1的选课学生学号，成绩转换为ABCD等级输出练习6：打印课号为C1的选课学生学号，成绩转换为ABCD等级输出 SELECT Sno, case when grade>=85 then 'A' when grade<85 and grade>=75 then 'B' when grade<75 and grade>=60 then 'C' else 'D' end as level FROM CS

练习7：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列练习7：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列 • SELECT Student.sno, Student.Sname, CS.cno,CS.Grade FROM Student,CS WHERE Student.sno = CS.sno ORDER BY CS.Grade DESC

练习8：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列；要求没有成绩的学生名单仍要显示，成绩一栏输出0练习8：列出所有学生的学号、姓名、课号、成绩,并按成绩降序排列；要求没有成绩的学生名单仍要显示，成绩一栏输出0 • SELECT Student.Sno, Student.Sname, CS.cno,isNull(CS.Grade,0) Grade FROM ( Student Left Outer Join CS on Student.sno = CS.sno) • 若一个关系中的元组在另一个关系中没有相匹配的元组，则这些元组会在连接结果中出现，并在另一个关系的其他属性位置上放上Null。

练习9：将上一练习的查询“固化”为视图 • CREATE VIEW CS_View AS • SELECT Student.Sno, Student.Sname, CS.cno,isNull(CS.Grade,0) Grade FROM ( Student Left Outer Join CS on Student.sno = CS.sno)

练习10：列出得过100分的学生的学号、姓名 • SELECT sno,sname FROM Student WHERE Exists ( SELECT 1 FROM CS WHERE CS.sno = Student.sno AND grade = 100)

练习11：列出没有选C01课程的学生的学号、姓名练习11：列出没有选C01课程的学生的学号、姓名 • SELECT sno,sname FROM Student WHERE Not Exists ( SELECT 1 FROM CS WHERE CS.sno = Student.sno And cno = 'C01')

练习12：统计各系人数 • SELECT sdept，COUNT(*) as stu_count FROM Student GROUP BY sdept

GROUP BY 语句实现将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现。需要注意的是，只有出现在GROUP BY子句中的属性，才可出现在Select子句中。COUNT为聚集函数，聚集函数共5种： (1) 平均值：AVG (2) 总和： SUM (3) 最小值：MIN (4) 最大值：MAX 计数： COUNT

练习13：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。练习13：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。 • SELECT sno，COUNT(sno) FROM CS WHERE grade < 60 GROUP BY sno HAVING COUNT(sno) >= 2

HAVING子句和WHERE子句都有条件选择的功能，但两者还是有很大区别的：Where 决定哪些元组被选择参加运算，作用于关系中的元组；Having 决定哪些分组符合要求，作用于分组。因此聚合函数的条件关系必须用Having，Where中不应出现聚合函数。

5.2.3 插入语句 • 练习14：新增一个学生信息 07001, 张三, 1988-03-09, F, CS • INSERT INTO Student(Sno,Sname,Sbirth,Ssex,Sdept) VALUES ('07001', '张三','1988-03-09', 'F', 'CS')

练习15：给CS系的学生开设必修课C05，建立选课信息练习15：给CS系的学生开设必修课C05，建立选课信息 • INSERT INTO CS SELECT sno, cno, null FROM Student, Course WHERE sdept = ‘CS’and cno =‘C05’

5.2.4 删除语句 • DELETE语句的一般形式： DELETE [FROM] [<数据库名>.<所有者名>.]<目的表名> FROM <原表名>][WHERE <逻辑表达式>]

练习16：在学生表中删除出生日期在1988-01-01之前的学生练习16：在学生表中删除出生日期在1988-01-01之前的学生 • DELETE FROM Student WHERE Sbirth<’1988-01-01’

练习17：在学生表中删除课程C01没有成绩的学生练习17：在学生表中删除课程C01没有成绩的学生 • DELETE Student FROM Student, CS WHERE CS.Cno ='C01' and isnull(CS.Grade,0)=0 and CS.Sno=Student.Sno

5.2.5 更新语句 • UPDATE语句的一般形式如下： UPDATE [<数据库名>.<所有者名>.]<视图名> SET <列名>＝<表达式>,… [FROM <原表名>,…][WHERE <逻辑表达式>] 从SQLSERVER2000的UPDATE语法形式上看，UPDATE可以接FROM子句（但Oracle等就不可以）。

练习19：将课程C01成绩大于等于95的学生转入PH系练习19：将课程C01成绩大于等于95的学生转入PH系 • UPDATE Student SET Student.Sdept='PH2' FROM Student, CS WHERE CS.Cno='C01' and CS.Grade>='60' and CS.Sno=Student.Sno

5.3 存储过程： • 存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，当执行存储过程时，只需调用execute , 即可自动完成命令

优点： • 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译， • 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 • 3.存储过程可以重复使用,4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

语法格式 Create procedure procedue_name [@parameter data_type][output] [with]{recompile|encryption} as sql_statement procedue_name：为过程名称 @parameter：为参数名称 data_type：为参数的类型 output：表示此参数是可传回的 with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次 encryption:所创建的存储过程的内容会被加密

练习20：一个简单的存储过程，查询学生表中的第一条记录练习20：一个简单的存储过程，查询学生表中的第一条记录 • CREATE PROCEDURE Student_s AS SELECT TOP 1 Sno, Sname, Ssex, Sbirth, Sdept from Student GO

练习21：查询姓名为张三，课程名为计算机的存储过程练习21：查询姓名为张三，课程名为计算机的存储过程 • CREATE PROCEDURE Student_score( @param1 varchar(10), @param2 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.Cno GO 然后执行存储过程Student_score： Exec Student_score '张三','计算机'

练习22：加入一条记录到表Department,并查询以及输出此表中院系总数练习22：加入一条记录到表Department,并查询以及输出此表中院系总数 • Create proc insert_Department @param1 char(3), @param2 varchar(50), @param3 int output With encryption ---------加密 as insert into Department(Dno,Dname) Values(@param1,@param2) select @param3=count(Dno) from Department go 然后执行存储过程insert_Department： declare @countDno int -- 变量的声明 exec insert_Department '001','创新学院',@countDno output print @countDno

5.4 触发器： • 触发器是一种特殊的存储过程，当表中数据被修改时，SQL Server将自动执行触发器。使用触发器可以实施较为复杂的数据完整性约束。

触发器优点： • 触发器能够实施的检查和操作比主键和外键约束、CHECK约束和规则对象等更为复杂。 • 触发器建立在表一级，它与特定的数据修改事件相对应。

CREATE TRIGGER trigger_name ON table_name FOR {INSERT,UPDATE,DELETE} [WITH ENCRYPTION] AS sql_statements • 解释： • trigger_name：指定触发器的名称，它必须遵守SQL Server的命名规则，而且同一个数据库中不允许出现触发器名称相同的情况。 • table_name：指定触发器所在的表名 • INSERT,UPDATE,DELETE：指定触发条件 • ENCRYPTION：SQL Server将触发器的定义文本保存在系统分类表syscomments中，选用此项要求SQL Server对该触发器定义文本进行加密存储，以防止第三方用户从syscomments表中读取该触发器定义文本的内容。 • sql_statement：定义触发器在触发事件发生时(被激活时)所要执行的操作。

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

头文件： • import java.sql.DriverManager; • import java.sql.Statement; • import java.sql.ResultSet; • import java.sql.Connection; • import java.sql.SQLException;

连接数据库： • protected String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; • protected String userName = "sa"; //访问数据库的用户名 • protected String userPwd = "password"; //访问数据库的密码 • protected String dbName = "MyDB"; //数据库实例名称 • protected String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;databaseName=" + dbName; //访问数据库URL • protected Connection conn = null; • protected Statement sm = null;

打开数据库连接 • protected void ConnectDB() throws SQLException,InstantiationException, IllegalAccessException, ClassNotFoundException { Class.forName(driverName); conn = DriverManager.getConnection(url, userName, userPwd); sm = conn.createStatement(); }

简单查询： • Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); Connection con = DriverManager.getConnection ("jdbc:microsoft:sqlserver://127.0.0.1:1433;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实现对数据库的一般查询的过程如下 • 创建Statement对象（要想执行一个SQL查询语句，必须首先创建出Statement对象，它封装代表要执行的SQL语句）并执行SQL语句以返回一个ResultSet对象，这可以通过Connection类中的createStatement()方法来实现。 • (2) 执行一个SQL查询语句，以查询数据库中的数据。Statement接口提供了三种执行SQL语句的方法：executeQuery()、executeUpdate() 和execute()。具体使用哪一个方法由SQL语句本身来决定。 • (3) Statement类中的executeQuery()方法, 输入参数是一个标准的SQL查询语句，其返回值是一个ResultSet类的对象。

结束

第 5 章 SQL 与 JDBC