《数据库原理及应用 教学课件 ppt 作者 范剑波 1_ 第4章 关系数据库的结构化查询语言（1）》由会员分享，可在线阅读，更多相关《数据库原理及应用 教学课件 ppt 作者 范剑波 1_ 第4章 关系数据库的结构化查询语言（1）（48页珍藏版）》请在金锄头文库上搜索。
1、第4章 关系数据库的结构化查询语言 Chapter 4 Structured Query Language of Relational Database,本章重点 本章主要介绍了T-SQL语言的数据定义、数据操纵和数据控制功能，介绍了视图、存储过程和触发器，介绍批、脚本、局部变量、全局变量的概念，重点介绍了流程控制语句和游标的使用方法。本章重点要求掌握T-SQL语言的查询和更新功能，灵活掌握视图的作用和用法，掌握流程控制语句和游标的使用方法。,4.1 SQL概述 SQL是一种介于关系代数和关系演算之间的一种结 构化查询语言，其主要功能包括数据定义语言(DDL）、 数据操纵语言（DML）和数据控
2、制语言（DCL），它是一 种通用的、功能很强的关系数据库语言。 4.1.1标准SQL的特点 1一体化特点 2高度非过程化 3语言简洁，易学易用 4统一的语法结构对待不同的工作方式 5. 面向集合的操作方式 6. SQL语言可以对两种基本数据结构进行操作，一种是 “表”，另一种是“视图(View)”。,4.1.2 关系数据库的三级模式结构 图4.l SQL对关系数据库模式结构的支持 1.基本表是本身独立存在的表，一个（或多个）基 本表对应一个存储文件，一个表可以带若干索引，索引 也存放在存储文件中。存储文件的逻辑结构组成了关系 数据库的内模式。 2.视图是从一个或几个基本表导出的表，它本身不 独
3、立存储在数据库中，即数据库中只存储视图的定义而 不存储对应的数据，因此视图是一个虚表。,4.1.3 SQL Server中的T-SQL对标准SQL的扩充 1.对标准SQL的扩充; 2.提供控制流语言作为SQL语句或批处理的一部分; 3.对于SELECT语句中的COMPUTE子句，当它与集函数 （SUM，MAX，MIN，AVG，COUNT）结合使用时，能产生小 计和总计报告; 4.支持存储过程,存储过程是可按名称调用的、已编译过的一组T-SQL语句，它能显著提高系统性能; 5.提供先进的数据完整性机制; 6、SELECT语句的FOR BROWSE扩充，允许DB-LIBRARY应用以浏览模式进行查
4、询。 7、为监视和优化系统性能提供多种全局变量、数据库选项和查询处理选项。,4.2 T-SQL的数据定义 4.2.1 SQL Server中的数据库 1.系统数据库 （1）Master数据库：它用来存储有关其它数据库的各种 信息，这些信息全都记录在一组事先定义的系统表中， 用来管理和控制整个DBMS的运行。 （2）Model数据库：它为创建其他用户数据库提供一个 原型。每当建立一个新的用户数据库时，都先建立一份 Model数据库的拷贝，然后再对其尺寸进行适当的扩充。 （3）Tempdb数据库：用于储存临时表和其它临时建立的 对象。它为该服务器上的所有数据库用户所共享。 （4）Msdb数据库：它
5、主要被SQL Server Agent用来进行 复制、作业调度以及管理报警等活动，也可通过调度任 务来排除故障。 2.用户数据库,4.2.2 SQL Server中的数据库表和索引 1.定义基本表 使用T-SQL中的CREATE TABLE语句可以创建基本 表，其语句格式为： CREATE TABLE . ( NOT NULLNULL , NOT NULLNULL) SQL Server中支持二种数据类型：系统数据类型和用户定义数 据类型。 （1）系统数据类型 字符数据： char(n),varchar(n),text 二进制数据：binary(n), varbinary(n),image 整
6、数数据：int,smallint,tinyint,bit 浮点数据： float,real 货币数据： money,smallmoney 日期时间数据： datetime,smalldatetime,（2）用户定义数据类型 格式：sp_addtype ,系统类型 , 例如： use pubs exec sp_addtype phone_type, ”varchar(24)”, ”not null” exec sp_addtype fax_type, ”varchar(24)”, ”null” 使用sp_droptype 可删除用户自定义数据 类型。,例1下面将使用CREATE TABLE语句来
7、创建teaching数 据库的三个基本表：student、student_course和 course。 1）创建student表 CREATE TABLE student (sno char (7) NOT NULL PRIMARY KEY , sname varchar (20) NOT NULL , dept varchar (20) NOT NULL , birthday datetime NULL , tel varchar (20) NULL , email varchar (20) NULL UNIQUE) 上面语句创建了一个名为student的学生表，表 中字段含义分别为：学号
8、、姓名、系名、生日、电话 号码和电子邮件地址。其中sno字段非空且设置为主 键；email字段可以为空，但必须不能取重复值；其 它字段含义自明，在此略去。,2）创建course表 CREATE TABLE course (cno char (5) NOT NULL PRIMARY KEY , cname varchar (20) NOT NULL , chour tinyint NULL ) 上面语句创建了一个名为course的课程表，表中字 段含义分别为：课程号、课程名和学时数。其中cno字 段非空且设置为主键；cname字段不能为空；chour字段 可以取空值。,3) 创建student_
9、course表 CREATE TABLE student_course( id int IDENTITY (1, 1) NOT NULL PRIMARY KEY , sno char (7) NOT NULL FOREIGN KEY(sno) REFERENCES student(sno) ON DELETE CASCADE , cno char (5) NOT NULL FOREIGN KEY(cno) REFERENCES course(cno) ON DELETE NO ACTION , grade decimal(5,0) NULL CHECK (grade=0 and grade=1
10、00) 在student_course表，id字段非空且设置为主键； sno字段不能为空，设置成相对于student表来说为外 键，并且当在student表中删除某个学生记录时，会根据 学号自动在student_course表中删除此学生的选课记 录；cno字段不能为空，设置成相对于course表来说 为外键，只有当在student_course表中无某门课的选课 记录时才能在course表中删除这门课程的记录；grade字 段可以为空，但取值必须在0到100之间。,2. 扩充修改基本表 用户使用数据库时，随着应用要求的改变，往往需 要对原有的表格结构进行修改，此时使用 ALTER TABLE
11、 命令。语句格式如下： （1）扩充基本表 ALTER TABLE 表名 ADD 列名 类型 NULL，列名 类型 NULL)； （2）修改基本表 ALTER TABLE 表名 ALTER COLUMN 列名 类型NULL|NOT NULL，列名 类型 NULL | NOT NULL)； 功能：对存在的表进行修改，但不影响原有表的数据， ADD后面指出新增列的名字及它们的数据类型等信息； ALTER COLUMN后指出要修改列定义的第一个名字及它们 的数据类型等信息。注意一个列要改变成非空值(NOT NULL)，要求该列当前不含有空值，如果不选择该项,其 默认的值保持原有的值；若一个列要改变数据
12、类型，则 该列数据必须全为空值,否则不能改变。,3. 撤销基本表 语句格式为： DROP TABLE ； 功能：删除指定表及其数据，释放相应的存储空间，同 时系统也自动地删除在此表上建立的各种索引，也删除 了在该表上授予的操作权限。虽然删除表时并未删除定 义在该表上的视图，但这些视图已无效，不能再使用 了。 例2 删除已存在的表table1。 use teaching DROP TABLE table1 只有当没有其它表引用它时，这个表才能被删除， 否则删除操作就会失败。 需要注意的是DROP TABLE语句不能用来删除系 统表。,4. 索引的建立和删除 SQL Server中提供了二种形式的
13、索引：簇集索引 和非簇集索引。簇集索引根据键的值对行进行排序，所 以每个表只能有一个簇集索引。非簇集索引不根据键值 排序，索引数据结构与数据行是分开的。索引建立和删 除的语句格式为： （1）CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX ON (,) （2）DROP INDEX . ,. 例3 在课程表上建立和删除索引。 Create unique clustered index kh_ind on course(cno); Drop index course.kh_ind;,4.3 T-SQL数据查询功能 最常用的SELECT语句的语法： SELECT
14、FROM WHERE GROUP BY HAVING ORDER BY ASCDESC 整个语句的含义为：根据 WHERE子句中的条件表达，从基本表(或视图)中找出满足条件的元组按SELECT子句中的目标列，选出元组中的分量形成结果表。如果有ORDER子句，则结果表要根据指定的表达式按升序(ASC)或降序(DESC)排序。如果有GROUP子句，则将结果按列名分组，根据 HAVING指出的条件，选取满足该条件的组予以输出。,4.3.1 数据基本查询 例4 在student中查询学生姓名、系名和年龄，要求结果 集中的列指定中文别名，同时消除结果集中重复的行。 SELECT DISTINCT sna
15、me as 姓名,dept as 系名, year(getdate()-year(birthday) as 年龄 FROM student 关于函数year()和getdate()的详细内容请读者自己 查看在线手册（Books Online）。 例5 在student表中查询1984年出生的电子系和计算机系 的学生，要求这些学生的电子邮件包含字 符串。 SELECT * FROM student Where birthday Between 1984-01-01 and 1984-12-31 and dept IN (计算机系,电子系) and email like %,其中SQL Serve
16、r的通配符有以下几个： %：代表任意多个字符。 _（下划线）：代表单个字符。 ：代表指定范围内的单个字符， 中可以是单个字 符（如nbgcxy），也可以是字符范围（如a-h）。 ：代表不在指定范围内的单个字符， 中可以是单个字符（如 nbgcxy），也可以是字符范围（如 a-h）。 例6 在student_course表中查询成绩非空的选课记录， 结果集要求按成绩的升序排列。 SELECT * FROM student_course WHERE grade IS NOT NULL ORDER BY grade ASC,4.3.2 数据分组查询 SQL Server提供以下集合函数： 1、MIN() 求(字符、日期、数值列)的最小值； 2、MAX() 求(字符、日期、数值列)的最大值
