结构化查询语言

1、第 2 章 结构化查询语言学习目标了解： SQL 语言的组成、功能和特点；理解：表、索引、视图、存储过程等数据库对象的概念和作用；掌握：数据库定义、更新语言、数据查询语言等的语句格式和使用方法。SQL 有两种使用方法:交互式SQL:一种与用户交互的方式，适合非计算机专业人员。宿主型SQL:一种作为子语言嵌入到其它程序设计语言中，适合于程序设计人员用高级语言编写应用程序并与数据库打交道, 嵌入到主语言中使用。SQL 语言概述SQL 具有四个主要的功能数据定义（DDL）：主要用于创建、修改、删除数据库及其中的对象。如：表、视图、索引等；数据操纵（DML）：主要用于对关系中的数据进行增、删、改和更新

2、等操作；数据查询：主要用于对数据进行检索查询等；数据控制（DCL）：主要用于对数据库的访问权限的授予或撤消、完整性规则描述、事务控制等操作。二、语言的特点非过程化语言: 一次处理一条记录, 对数据提供自动导航。统一的语言: 对所有用户都适用。提供的命令有：查询数据；在表中插入、修改和删除记录；建立、修改和删除数据对象；控制对数据和数据对象的存取；保证数据库一致性和完整性等。是所有关系数据库的公共语言。基本数据类型主要数据类型: 数值型、字符型、日期型、二进制型、位型和双字节型。数值数据类型: 用于存储数值型数据整数型：包括Bitint 、 Int 、 Smallint 、 Tinyint, 一

3、般使用Int 型。精确数值：用于存储带小数位的数据，包括Decimal 、 Numeric，一般使用后者。浮点数值：（Float,Real） 存储非常大带小数的数据。货币型：带四位小数的浮点数。字符型：用于存储字符型数据Char 型：长度固定；Varchar 型：长度不固定；Text 型： 存储字符型数据非常大，长度超过8000 个字符时使用，最大可存储2G 的数据。日期时间型：存储日期和时间的组合数据。Datetime: 1753 年 1 月 1 日 至 9999 年 12月 31 日，占 8 个字符的存储空间。Small Datetime: 1900 年 1 月 1 日 至 2079 年

4、6月 6 日，占 4 个字符的存储空间。二进制：存储图形图像、WORD文档等。包括 :Image、 Binary 、 var binary 。其中，Image 用于存储照片、音频和视频等文件。位型： Bit 逻辑数据，用于存储1 或 0 ，只有一位字节。特殊数据类型：用于存储特殊用途的数据。数据定义语言一、数据库：数据库的概念：对象的集合，主要包括表、索引、视图、存储过程等。数据库在磁盘上以文件形式存储，由数据文件和日志文件组成。先建立数据库，然后才能建立对象 如：表、索引等。数据库的定义：CREATE DATABASE 功能：建立一个空数据库。该语句执行后，可以使用“USE” 命令把已建立的

5、一个数据库置为当前数据库。此后所有操作，包括建立、修改、删除基本表等操作均对当前数据库进行操作。例： Create DATABASE 教学管理库删除数据库: DROP DATABASE 功能：删除（撤消）数据库；同时将删除数据库中所有对象。例：DROP DATABASE教学管理库二、表表的概念：表是数据库的操作对象之一，是数据库最基本、最重要的对象。其他对象，如查询、窗体和报表都是在表的基础上建立和使用的。表由行（记录）和列（字段）组成。学号课程号成绩001K0160001K0270002K0190002K0280数据表具有的属性：表名：表的唯一标识；记录：表中的每行为一条记录； 字段： 表的

6、列为字段，每个字段需定义字段名称、字段类型、宽度等；主关键字：能够唯一地标识表的每条记录的字段或字段组合。如：学生表的主关键字为“学号”，选课表的“学号”和“课程号”的组合为关键字。域： 每个字段的取值范围。创建表结构命令格式：CREATETABLER （ ， , ）功能：在当前或给定的数据库中定义一个表结构（关系模式）列定义包括：属性名，数据类型，完整性约束。常用的表中数据类型有以下几种：char（n）:定长度字符型，如：姓名char（10） ；varchar ：变长度字符型；Int ：整型，占4 个字节；Float ：浮点数（实数）占4 或 8 个字节；Date ：日期型yyyy-mm-d

7、d,（yyyy/mm/dd） 占 4 个或 8 个字节。列级完整性约束: 有六种DEFAULT ：默认值约束。NULL/NOT NULL 空值 /非空值：对于非主属性，若不注明此约束，则隐含为允许空值约束。PRIMARY KEY：主码约束。UNIQUE：单值约束，惟一值约束。REFERNCES （）: 外码约束。CHECK（）: 检查约束，取值限制。举例：创建学生基本表CREATE TABLE学生（学生号 char（7） primary key ，姓名char（6） ，性别char（2） ，出生年月datetime,专业char（10）,年级int ）表级完整性约束: 有四种PRIMARY K

8、EY（ , ）主码约束: 注明一个或多个列为主码。UNIQUE（ , ）单值约束：注明一个或同时若干个列为单值。FOREING KEY , REFRENCES（） 外码约束：注明一个或同时若干个列为外码。CHECK（） 检查约束：注明每行中一个或若干个列的取值限制。除默认值约束、空值/ 非空值约束外，对于其它约束，若只涉及到一个列时，则可以作列级完整性约束，又可以作表级完整性约束。若涉及到多个列时，则只能作为表级完整性约束。举例：创建选课基本表CREATE TABLE选课（学生号char（7） ，课程号char（4）,成绩 int check（ 成绩 =0 and 成绩 =100）, prim

9、ary key （ 学生号 , 课程号 ）, Foreign key （学生号） refrences学生（学生号），Foreign key （课程号） refrences课程（ 课程号）3表的删除DROP TABLE 三、索引索引的概念: 索引提供一种快速访问数据的途径, 当数据库很大时，在表中建立若干个索引，会加快查询。索引有以下特点：1 ） 索引是在表中的某列上建立的数据库对象，它和表分开存储，它的建立或撤消对数据的内容没有影响。2）索引一经创建，就完全由系统自动选择和维护。3）表中的记录数量越多，越有必要建立索引。4）索引是为加快查询速度，但其影响表中数据的插入、删除和修改。创建原则1

10、）表中的记录数量越多，越有必要建立索引。2）索引是为加快查询速度，但其影响表中数据的插入、删除和修改。3） 索引是建立在基本表列上的对象，通常需要在经常出现在WHER、EORDERB Y、 GROUPBY 子句中的列建立索引。不易创建索引的情况包含太多重复值的列、查询中很少被引用的列、值特别长的列、具有很多NULL 值的列、需要经常更新的列、记录较少的表等。索引的类型单列索引：对基本表的某一单独列进行索引，通常是对主关键字建立。 唯一索引：建立唯一索引后，则不允许在该列上插入相同的值。复合索引：对表的两个或多个列上建立索引。聚集索引：改变表中记录的物理存储顺序，即表中记录的物理排列顺序不再按插

11、入 的顺序排列。非聚集索引：不改变表中记录的物理存储顺序，即表中记录的物理顺序和索引顺序不同。聚集索引和非聚集索引对比:存取速度: 聚集索引查询速度快, 但降低更新速度。索引的数据: 一个表只能建立一个聚集索引, 但可以建立多个非聚集索引。所用空间: 非聚集索引需要较多的空间。创建索引: CREATE UNIQUE CLUSTERED INDEX 索引名 ON 表名 （ 列名 1 次序 。UNIQUE：创建唯一索引；CLUSTERED：创建聚集索引；次序：表示排列次序，ASC 表示升序；DESC 表示降序。如： CREATE INDEX学号索引ON 学生表（学号）删除索引：DROP INDEX

12、 索引名，一次可以删除一个或多个索引。四、视图视图是建立在基本表上的一个虚表，它的结构和内容都来自基本表，它依据基本表存在而存在。视图的列可以来自一个基本表或多个基本表。视图的建立和删除只影响视图本身，不影响对应的基本表，而对视图的更新（插入、删除和修改）直接影响基本表。对视图做的最多的是查询操作。当删除表时同时删除在表建立的视图。视图能够增强安全性，使数据库中一些保密的数据对无关人员成为不可见的，从而不能随意查询。创建视图：CREATE VIEW 视图名（ 列名，） AS SELECT 子句；为当前数据库根据SELECT 子句查询结果建立视图。举例： 把计算机系学生的学号和姓名创建为视图：C

13、REATE VIEW CSStudent ASSELECT StudentNo, StudentNameFROM StudentWHERE Dept=计算机系 ；可把 CSStudent 看作是一个虚拟的表。删除（撤消）视图：DROP VIEW 视图名；举例： DROP VIEW CSStudent 撤消视图CSStudent 。五、存储过程存储过程：存储过程是一组预先编译好的Transact SQL 代码，是一种可执行的永久存储在数据的SQL 代码中。可以作为一个独立的数据库对象被用户使用。存储过程的作用: 它可以接收参数, 并以输出参数的形式返回多个参数给调用它的过程或批处理，使用存储过程

14、优点：（ 1 ） 执行速度快：存储过程在创建时就经过了语法检查和性能优化，因此在执行时不必再重复这些步骤。存储过程在经过第一次调用后，就驻留在内存中，不必再经过编译和优化，所以执行速度快。模块化的程序设计：可以被多次调用，用户可以独立于应用程序而对存储过程进行修改，增加了数据库应用程序的易修改特征。减少网络流量: 存储过程可以包含大量的Transact SQL 语句 , 但在进行调用时,客户端只需要使用一个语句就可以实现了, 大大减少了网络上数据的传输量。保证系统的安全性: 存储过程存放在数据库服务器端, 对于某些关键数据, 可以设置成只通过存储过程来访问, 不允许用户直接使用Transact

15、 SQL 语句或企业管理器对数据进行访问。创建存储过程：CREATE PROC存储过程名AS SQL 语句SQL 语句是利用Transact SQL 编写的程序。执行存储过程: 如果对存储过程的调用是批处理的第一条语句，则可直接使用存储过程的名字调用。如果不是第一条语句，调用的语法：EXEC 存储过程名。举例 : 创建USER_PROC1存储过程。CREATE PROC USER_PROC1ASSELECT * FROM 学生表GO六、 触发器：触发器是一个特殊的存储过程, 它基于一个表的创建, 但可以针对多个表进行操作 , 主要用来保证数据的完整性。一张表可以创建多年触发器, 用户可以针对U

16、PDATE、DELETE、 INSERT 语句分别设置触发器，当用户进行UPDATE、 DELETE、 INSERT 等操作后，这些事先定义好的触发器对象就会被触发，并按事先定义好的规则自动执行。创建触发器：CREATE TRIGGER触发器名ON 表名 /视图名OF DELETE， INSERT，UPDATE AS SQL 语句主要参数：DELETE、 INSERT、 UPDATE指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。举例： 当对学生表执行DELETE 语句时 , 同时删除成绩表中学号相同的记录。CREATE TRIGGER删除学号ON 学生表FO

17、R DELETEASDECLARE Myno char（4）Select Myno= 学号 FROM DELETEDDELETE FROM 成绩表WHERE学号=MynoRETURN数据操纵语言插入记录命令格式：INSERT INTO （ , ） VALUES （ 列值, ）向表中插入完整的记录时, 可省略列名列表。例如：学生关系（学生号，姓名，性别，专业）INSERT into 学生（学生号，姓名，性别，专业）VALUES（001 ，李宏 ，女 ，计算机 ）与下面语句功能相同INSERT into 学生 VALUES （001 ，李宏 ，女 ，计算机 ）。修改记录命令格式：UPDATE SE

18、T = , WHERE ；功能：按条件修改表中的一些列值。SET：要修改的列值；WHER：修改记录条件，省略参数将修改表中所有记录。E例：将所有计算机系学生的年龄增加1 岁，并且把系名改为“计算机科学系”UPDATE Student SET Age=Age+1, Dept= 计算机科学系 WHERE Dept= 计算机科 学系 删除记录命令格式：DELETE FROM 表名 WHERE ；例 : 删除学号为9900203 的学生记录DELETE FROM Student WHERE 学生号= 9900203数据查询语言SQL 中最简单的查询就是从某个关系中查找满足某种条件的元组，类似于关系代数

19、中的选择运算。一、查询语言格式Select 字段列表IntoFrom Where GROP by Having Order by ASC DESC二、简单查询假设教学数据库包括：学生、课程和成绩三个基本表。查询指定的字段：用“，”隔开需要查询的字段。例：查询学生表所有学生的学号和姓名。Select 学号 , 姓名FROM 学生通配符“* ” : 查询所有字段例：查询学生表所有学生的所有字段。Select *FROM 学生基于字段的表达式: 使用字段构成需要查询的表达式，如：例：查询所有学生的学号和出生年月。Select 学号,STR（YEAR（出生日期）,4）+ 年+ STR（MONTH（ 出

20、生日期）,2）+ 月AS 出生年月FROM 学生说明： AS 选项定义表达式的列名，若省略AS 选项，则字段名作为列名使用。ALL/DISTINCT ：默认值为ALL, 表示允许查询结果中出现内容重复的行；若选择DISTINCT 则表示不允许出现内容重复的行。例：查询学生表中专业数。Select DISTINCT 专业FROM 学生WHERE 选项：定义基本表之间的连接条件和对记录的筛选条件。连接条件通常为等值连接 , 筛选条件是从源表中选择出满足条件的行，筛选条件可以是一般比较式，也可以是通过逻辑运算符连接的逻辑表达式。1） 使用BETWEEN短语查询。短语格式：BETWEEN AND ，例

21、：查询年龄在19 岁 -21 岁的学生。select * from 学生 where 年龄 19 and212）使用NULL 查询：例：查询成绩不为空值的所有记录。select * from 成绩 where NOT （成绩 ISNULL ）3）使用日期进行查询：例：查询1985 年 1 月 1 日 后出生的学生。select * from 学生 出生日期 1985-01-01使用 IN 查询： IN（ 常量表 ）例：查询年龄为19 岁或 21 岁的记录。select * from 学生 where 年龄IN（19,21）使用 LIKE 查询： 列名 LIKE 表达式 （ 用 %、构成表达式）

22、例：查询姓“王”的所有学生。select * from 学生 where 姓名 LIKE王 %6.ORDERB Y ， ：将查询结果按排序列进行排序，查询结果首先按排序列名1 的值排序，若该列的值相同，再按排序列名2 的值排序。排序方式有两种，关键字 DESC 表示降序，ASC 表示升序，若省略表示升序。例：从教学库中查询出所有学生的学号和姓名，查询结果按学生号升序排列。select 学生号 , 姓名from 学生order by 学生号. 统计函数的使用：COUNT（ ALL/DISTINCT|* ）：统计指定列的值个数；MAX（ ）求出对应的数值、字符或日期列的最大值；MIN （ ）求出对

23、应的数值、字符或日期列的最小值；AVG（ ）求出对应的数值列的平均值；SUM（ ）求出对应的数值列的总和。例 : 统计学生人数。Select COUNT （*） AS 学生人数FROM学生例：统计每个专业的学生人数。Select COUNT （DISTINCT 专业 ） AS 专业数FROM学生.GROUP BY ： 按分组列名对查询结果中的所有元组进行分组，使得分组列值相同的为一组，形成结果表中的一个元组。select 选项中只能包括分组列名和统计函数。例：查询每个专业的学生数。Select 专业 as 专业名，count（ 专业 ） 学生数From 学生Group by 专业三、复杂查询：

24、涉及多个表的查询连接查询：两表按公共字段等值连接查询。例： 查询学生的学号、姓名、课程号和成绩。查询需要用到两张表（学生，成绩）SELECT 学号、姓名、课程号，成绩FROM学生表，成绩表WHERE学生表. 学号=成绩表. 学号例： 查询出所有计算机专业学生的学号, 姓名 , 课程号 , 课程名和成绩。查询条件包括连接条件和选择条件。SELECT 学号、姓名、课程号，成绩FROM学生表，成绩表WHERE学生表. 学号=成绩表. 学号 AND 专业=计算机嵌套查询: 一个 SELECT 查询嵌套在另一个查询语句中。例 1：查询所有被女生选出的课程号和课程名。SELECT 课程号, 课程名 FROM 课程表WHERE课程号IN (SELECT 课程号 FROM 成绩单WHERE学号IN (SELECT 学号 FROM 学生表WHERE性别=女 )第一步：最内层的查询功能查询出所有女生的学号； 第二步：第二层查询功能是从成绩单表中查询出女生选修课程的课程号； 第三步：最外层查询功能是从课程表中查询出被女生选修的所有课程的课程名和课 程号。例 2： 求成绩小于总平均成绩的学生学号、姓名、专