【数据库系统概念】第5章 高级SQL 知识总结

《数据库系统概念》第5章知识点总结

高级SQL

前面第3章和第4章介绍的是SQL的基本操作

本章介绍的则是如何使用通用程序设计语言来访问SQL以及触发器等。

 

JDBC

JDBC标准定义了Java程序连接数据库服务器的应用程序接口（API）（JDBC原本是Java数据库连接（Java Database Connectivity）的缩写，但其全称现在已经不用了）

JDBC接口的四个基本操作：打开数据库连接，执行语句，处理结果，关闭连接

（Java程序需引用java.sql.*，它包含了JDBC所提供功能的接口定义）

以下我们通过一个书上的例子详细讲解

public static void JDBCexample(String userid,String passwd)
{
	try
	{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn=DriverManager.getConnection(
				"jdbc:oracle:thin:@db.yale.edu:1521:univdb",
				userid,passwd);
		Statement stmt=conn.createStatement();
		try{
			stmt.executeUpdate(
					"insert into instructor vales('77987','Kim','Physics',98000)");
		}catch(SQLException sqle)
		{
			System.out.println("Could not insert tuple."+sqle);
		}
		ResultSet rset=stmt.executeQuery(
				"select dept_name,avg(salary)"+
				"from instructor "+
				"group by dept_name");
		while(rset.next()){
			System.out.println(rset.getString("dept_name")+" "+
		             rset.getFloat(2));
		}
		stmt.close();
		conn.close();
	}
	catch(Exception sqle)
	{
		System.out.println("Exception:"+sqle);
	}
}

加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

要实现Java对数据库的访问必须动态加载相应的JDBC驱动程序。此操作必须在连接数据库之前完成。

在例子中，第一行调用的Class.forName函数完成了驱动程序的加载，加载了oracle的驱动程序，oracle.jdbc.driver.OracleDriver

该驱动程序包含在一个 .jar文件里，可以从提供商的网站下载，然后放到Java的类路径（classpath）里，用于Java编译器访问

 

连接到数据库

Connection conn=DriverManager.getConnection(
				"jdbc:oracle:thin:@db.yale.edu:1521:univdb",
				userid,passwd);

要在Java程序中访问数据库，首先要打开一个数据库连接，这一步需要选择要使用哪个数据库。这里我们通过调用DriverManager类的getConnection方法来打开一个数据库连接。

（1）getConnection的第一个参数"jdbc:oracle:thin:@db.yale.edu:1521:univdb"，是以字符串类型表示的URL，指明服务器所在的主机名称遗迹可能包含的其他信息。

     主机名称：db.yale.edu

     与数据库通信所用的协议：jdbc:oracle:thin:

     数据库系统用来通信的端口号：1521

     服务器端使用的特定数据库：univdb

（2）getConnection的第二个参数：userid，用于指定一个数据库用户标识，是字符串类型

（3）getConnection的第三个参数：passwd，是密码，也是字符串类型

 

执行语句

Statement stmt=conn.createStatement();
		try{
			stmt.executeUpdate(
					"insert into instructor vales('77987','Kim','Physics',98000)");
		}catch(SQLException sqle)
		{
			System.out.println("Could not insert tuple."+sqle);
		}

通过Statement类的一个实例来利用连接向数据库发送SQL语句用于执行。

executeQuery函数：执行查询语句，返回一个结果集

executeUpdate函数：执行更新、插入、删除、创建表的非查询语句

 

 

处理结果

ResultSet rset=stmt.executeQuery(
	"select dept_name,avg(salary)"+
	"from instructor "+
	"group by dept_name");
while(rset.next()){
	System.out.println(rset.getString("dept_name")+" "+
        rset.getFloat(2));
}

示例程序用stmt.executeQuery来执行一次查询。它可以把结果中的元组集合提取到ResultSet对象变量rset中并每次取出一个进行处理。结果集的next方法用来查看在集合中是否还存在至少一个尚未取回的元组，如果存在的话就取出。

该示例也给出了两种在元组中提取属性值的办法：利用属性名提取（dept_name）或者利用属性位置提取（2，代表第二个属性）

 

关闭连接

stmt.close();
conn.close();

语句和连接都要被关闭。

之所以要关闭，是因为数据库连接的个数是有限制；未关闭的连接可能导致超过这一限制。如果发生这种情况，应用将不能打开任何数据库连接。

 

预备语句

通过以“？”来代表以后再给出的实际值，而创建一个预备语句

 

执行代码如上图所示。从图中我们可以看出，在同一查询编译一次然后设置不同的参数值执行多次的情况下，预备语句使得执行更加高效。

 

元数据特性

接口ResultSet有一个getMetaData()方法，它返回一个包含结果集元数据的ResultSetMetaData对象。ResultSetMetaData进一步又包含查找元数据信息的方法，例如结果的列数、某个特定列的名称，或者某个特定列的数据类型。这样，即使不知道结果的模式，我们也可以方便地执行查询。

 

图片中，getColumnCount方法返回结果关系的元素（属性个数）；getColumnName和getColumnType-Data方法分别的到它的名称和数据类型。

 

 

ODBC

开放数据库互连（Open DataBase Connectivity，ODBC）标准定义了一个API，应用程序用它来打开一个数据库连接、发送查询和更新，以及获取返回结果等。

以下是一个使用ODBC API的C语言代码示例

 

（1）和JDBC类似，利用ODBC和服务器通信的第一步是建立一个和服务器的连接。

（2）建立连接之后，利用SQLConnect打开和数据库的连接，几个参数意义如下：数据库的连接句柄、要连接的服务器、用户的身份和密码。常数SQL_NTS表示前面参数是一个以null结尾的字符串。

（3）通过SQLExceDirect语句把命令发送到数据库

（4）因为C语言变量可以和查询结果的属性绑定，所以当一个元组被SQLFetch语句取回的时候，可以通过SQLBindCol把相应的属性的值放到对应的C变量里。

     SQLBindCol的第二个参数代表选择属性中哪一个位置的值

                 第三个参数代表SQL应该把属性转化成什么类型的C变量

                 第四个参数给出了存放变量的地址

                 第五个参数给出变量的最大长度

                 第六个参数给出一个位置来存放元组取回时的实际长度。

（5）结束时，程序释放语句的句柄，断开与数据库的连接，同时释放连接和SQL环境句柄

 

 

嵌入式SQL

SQL标准定义了嵌入SQL到许多不同的语言中，SQL查询所嵌入的语言被称为诉诸语言，宿主语言中使用的SQL结构被称为嵌入式SQL。使用宿主语言写出的程序可以通过嵌入式SQL的语法访问和修改数据库中的数据。

识别嵌入式SQL请求的语句格式：EXEC SQL <嵌入式SQL语句>；

 

连接到数据库

 

这里，server表示将要建立连接的服务器

 

声明游标

为了表示关系查询，使用声明游标语句：declare cursor。

例如：找出学分高于credit_amount的所有学生的名字

 

上述表达式中的变量c被称为该查询的游标（cursor）

open语句对游标执行查询：

 

fetch语句要求结果关系的每一个属性有一个宿主变量相对应

 

在这个查询例子中，需要两个变量分别存储ID和name的值，所以设si和sn

close语句告诉数据库系统删除用于保存查询结果的临时关系

 

 

通过游标来更新数据库关系

例如要为音乐系的每个老师的salsry属性都增加100

可以先声明这样一个游标      然后利用游标上的fetch操作对元组进行迭代

 

 

 

函数和过程

声明和调用SQL函数和过程

表函数：返回关系作为结果的函数

 

上述函数也可以写成一个过程

 

关键字in和out分别表示待幅值的参数和为返回结果而在过程中设置值的参数

 

SQL允许多个过程同名，只要同名过程的参数个数不同。

SQL也允许多个函数同名，只要这些同名的不同函数的参数个数不同，或者对于那些有相同参数个数的函数，至少有一个参数的类型不同

 

支持过程和函数的语言构造

（1）变量通过declare语句进行声明，使用set语句进行赋值

（2）复合语句用begin...end的形式，即在begin和end之间会包含复杂的SQL语句

（3）while和repeat语句，以及for循环

 

（4）if-then-else语句

 

（5）异常

 

 

触发器

触发器（trigger）是一条语句，当对数据库作修改时，它自动被系统执行。

设置触发器机制的两个要求

（1）指明什么条件下执行触发器：一个引起触发器被检测的事件和一个触发器执行必须满足的条件

（2）指明触发器执行时的动作

 

为什么需要触发器：用来实现未被SQL约束机制指定的某些完整性约束

 

referencing new row as语句建立一个变量nrow，用来在插入完成后存储所插入行的值

referencing old row as语句建立一个变量用来存储已经更新或删除行的旧值

 

创建一个触发器，更新关系takes的属性grade时执行触发器

 

下面是一个完整的例子

 

when语句指定一个条件。仅对于满足条件的元组系统彩绘执行触发器的其余部分。begin atomic...end语句用来将多行SQL语句集合成为一个复合语句。

for each statement子句替代for each row子句时，可以对引起插入、删除或更新的SQL语句执行单一动作，而不是对每个被影响的行执行一个动作。

 

alter trigger trigger_name disable可以将触发器设为无效

drop trigger trigger_name 触发器被丢弃，即将其永久移除

注：图片均来自《数据库系统概念》第6版——机械工业出版社