欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-07 14:28:48
...

数据库系统概念》第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();

语句和连接都要被关闭。

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

 

预备语句

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

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

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

 

元数据特性

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

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

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

 

 

ODBC

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

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

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

(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语句>;

 

连接到数据库

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

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

 

声明游标

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

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

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

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

open语句对游标执行查询:

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

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

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

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

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

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

 

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

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

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

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

 

 

函数和过程

声明和调用SQL函数和过程

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

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

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

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

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

 

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

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

 

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

(1)变量通过declare语句进行声明,使用set语句进行赋值

(2)复合语句用begin...end的形式,即在begin和end之间会包含复杂的SQL语句

(3)while和repeat语句,以及for循环

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

(4)if-then-else语句

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

(5)异常

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

 


触发器

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

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

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

(2)指明触发器执行时的动作

 

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

 

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

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

 

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

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

下面是一个完整的例子

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

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

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

 

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

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


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