SQL语言 归纳速查
一.SQL语言概述
1.SQL语言的发展
几乎所有的关系数据库管理系统(RDBMS)都支持SQL标准
2.SQL语言的功能
定义、查询、更新、控制
3.SQL语句格式
4.SQL语言使用方式
-联机交互
-嵌入执行
可在用C# JAVA PYTHON等高级程序语言所写的程序中嵌入SQL语言,实现与数据库的连接,并对数据库中的数据进行操作,此时,SQL语言被称为数据库子语言,高级程序设计语言被称为宿主语言
二.基本表的定义
基本表的定义存入DBMS的数据字典中,由DBMS根据数据字典中的信息自动检查用户操作是否符合完整性约束条件,实现关系的实体完整性、参照完整性和用户定义完整性
1.基本表的创建
创建数据库
打开数据库
基本表的创建
数据类型和含义
当输入的字符不足n个字符时,CHAR类型会在字符串后面自动补足空格,使其达到n个字符,而对VAECHAR类型不会补足,属性值长度就是输入字符数。
注意:以上每种数据类型,都可能包含一个被称作空值(NULL)的特殊值,空值表示一个缺失的值,该值可能存在但并不为用户所知,或者可能根本不存在,不同的数据库管理系统支持的数据类型不完全相同,数据类型的表达方式也有所区别,在实际操作中要注意
定义主键约束
定义外键约束
用户定义完整性定义
2.基本表的修改
增加列或表的约束规则
修改原有列的类型
删除原有的列或约束规则
系统默认是执行RESTRICT限制操作
选项CASCADE表示在删除基本表的同时,在此表上建立的试图、索引等关联对象也将自动被删除
选项RESTRICT表示该表删除是有限制的,如果有依赖该表的数据对象,则不能被删除。
3.基本表的删除
三.查询结果显示
一个完整的数据查询语句的格式
1.投影属性列
2.去除重复元组
3.查询结果的计算
需要强调的是对于属性列中的空值,除了COUNT(*)外,都不做处理,COUNT函数只是统计元组的个数,并不关心元组中的属性值。
4.目标列命名
5.查询结果的排序
默认升序,故ASC可省略
可把列名用列序号替代
四.查询满足条件的元组
一个完整的数据库查询语句的格式
1.运算符
2.谓词
BETWEEN谓词
LIKE谓词
要查询的字符串本身含有通配符"",所以需要对通配符进行转义,在"“的前面加上”";在字符串表达式后用ESCAPE来定义转义符
IN谓词
NULL谓词
运算顺序
五.分组聚集查询
一个完整的数据查询语句的格式
1.语句格式
2.分组聚集查询
六.连接查询
一个完整的数据查询语句格式
1.多表连接查询
2.外连接查询
内连接既可以写成FROM A,B WHERE…也可以写成FROM A JOIN B ON…WHERE…
3.自身连接查询
七.嵌套查询
1.嵌套查询的概念
2.嵌套查询的方式
谓词IN实现嵌套
若子查询的结果是一个集合,则可用谓词IN来实现嵌套查询
对于该查询,也可用连接查询来实现
那么,这两个查询那个更好?
从DBMS对SQL语句的查询处理方面看,嵌套查询更又利于生成优化的查询执行计划。
课后自行思考一下
比较操作符实现嵌套
当一个子查询的结果是一个值时,可使用比较操作符,将一个子查询嵌套在条件表达式中,来实现嵌套查询
量词ANY或ALL实现嵌套
有些DBMS中,不支持量词ANY或ALL操作,则可用谓词IN或聚集函数与比较符配合来使用,功能的等价性参见表格
当子查询的结果是一个集合而不是一个值时,比较运算符前的值,肯定会不等于查询结果中的某个值,那么比较运算符不等于ANY结果永真。作为父查询的条件没有意义,更不能用谓词NOT IN来替代,也不可能存在比较运算符前的值等于查询结果中的所有值的情况,所以比较运算符等于ALL结果永假。作为父查询的条件更不能用谓词IN来替代。
谓词EXISTS实现嵌套
从语法角度来看,本查询对于父查询中的每一个学生元组,若输出该学生的姓名,应满足第一个谓词EXISTS后,第一层嵌套为空,而第一层子查询为空,则其WHERE子句的条件应为假。那么,第二个谓词EXISTS的结果则为真,第二层嵌套子查询应存在查询结果,即在表SC中,表S中的当前元组,有选任一课程的选课记录
将两个子查询中的SC表至少为其中一个改名,因为第二个子查询是一个相关子查询。
小结:
八.集合查询
和关系代数中的要求一样,要求参与集合运算的两个查询结果必须是相容的。也就是说,两个SELECT语句的查询结果必须具有相同数目的属性列,且对应属性列具有相同的数据类型
1.并运算
查询结果会自动去除重复的元组,若要保留重复元组,在关键词UNION后添加选项ALL
不用集合查询也可以实现:
2.差运算
也可用嵌套查询实现:
3.交运算
也可以嵌套查询实现:
注意:
九.数据更新
以这个为例,进行讲解:
1.插入元组
给出了所有属性值,可以省略()部分
在插入语句中嵌入查询语句,此时可以向多个关系表中一次插入多个元组,格式为在INSERT语句中,将VALUES子句替换成一个子查询,该语句先执行子查询,再将子查询结果分别赋予指定表中每一个新元组的对应属性,
2.修改属性值
如果省略WHERE子句,则要对表中所有元组的相关属性进行修改
UPDATE只能修改一个表,不能同时修改多个表
3.删除元组
如果省略WHERE子句,则要对表中所有元组的相关属性进行删除
更新操作的完整性检查
拒绝执行是大多数DBMS的默认策略
十.视图
1.视图的概念
2.视图的创建
有些DBMS中不支持子查询中包含与查询结果的显示有关的ORDER BY语句和DISTINCT选项等
视图创建时,若加上WITH CHECK OPTION选项,则要求在对视图进行更新操作时,检查待更新的元组是否满足子查询中的元组选择条件
3.视图的删除
不同DBMS对CASCADE选项支持程度不同,对视图上还创建有视图的删除操作限制也不同
4.视图的查询
DBMS执行对视图的查询时,首先要进行有效性检查,检查涉及的基本表、视图等是否存在。如果存在,则从数据字典中取出视图的定义,把定义的子查询同用户的查询结合起来,转换成对相应基本表的查询
转换后的查询语句实际是对学生表S和选课表SC进行连接查询,对连接结果进行分组,查询显示每组的学生学号和响应的平均成绩
5.视图的更新
5.1插入
该元组会插入到基本表中,但无法在视图中看到该学生的相关信息。DBMS并没有将对数学系学生视图的插入,转换为向基本表插入一个数学系的学生操作。
WITH CHECK OPTION,在定义视图时设置WITH CHECK OPTION,就表示对视图进行更新操作时,DBMS会检查更新元组是否满足创建视图时子查询中的元组选择条件。
5.2修改和删除
S_AVE是派生属性,对视图的修改无法转换为基本表SC的修改,即无法把平均成绩的修改反映到该学生的各科的成绩上
5.3更新的限制
6.视图的作用
视图定义在基本表之上,对视图的操作最终要转换为对基本表的操作,并且对于非行列子集视图进行更新时,还可能出现问题。既然如此,为什么还需要创建视图?
在数据库使用过程中,随着应用需求的变化,可能需要对数据库的逻辑结构进行改变,比如把S重构为两个表SX和SY,对于表SX和SY的连接查询,创建视图S,任何以前对基本表S的操作,现在可针对视图S进行,使得数据库的重构对用户和用户程序没有影响,从而实现了数据的逻辑独立性。
十一.索引
索引是SQL语言定义的一种数据对象,是大多数DBMS为数据库基本表创建的一种辅助存取结构,用于提高相应特定查询条件的查询处理速度。
1.索引的概念
2.索引的作用
2.索引的分类
一个关系表只能创建一个聚集索引(主索引),多个非聚集索引(辅助/次索引)
3.索引的创建
方式一
方式二
在默认情况下,CREATE INDEX语句创建的是非聚集索引,其他参数是与索引文件的物理存储相关的参数,可以不指定,由DBMS决定
这里的NONCLUSTERED是可以省略的
4.索引的删除
4.索引的创建原则
小结:
十二.授权管理
SQL语言的控制功能,体现在DBMS的授权机制上,目的是实现数据库的安全访问,确保只有授权用户才能访问数据库,所有未被授权的用户无法获取数据
1.自主访问控制
用户对某一数据对象的操作权力成为操作权限。在DBMS中,用户对数据库的访问权限由两个要素组成,数据库对象和操作权限。数据库对象可以是数据库、基本表、表中的元组 属性、视图、索引等。操作权限根据数据库对象有所不同,一般包括对象定义,如创建、修改、删除、对象查询和更新,有的DBMS用ALL PRIVILEGES表示所有操作权限,数据库用户又分为如下四类:SA、DBA、数据库对象用户、数据库访问用户。
-SA:DBMS在安装时,至少有一个SA,SA在DBMS上拥有一切权限,负责整个系统的管理。
-DBA,该用户在自己所创建的数据库上,拥有一切权限。
-数据库对象用户:该用户可以建立数据库对象,如表、视图等等,在这些数据库对象上,拥有全部的操作权限。
-数据库访问用户:这类用户可以对被授权的数据库对象进行操作,如查询数据、修改数据等
这四类用户从上往下权限逐渐降低,一般较低级别用户的权限,是由较高级别用户授予的,比如系统管理员用户授予某个用户创建数据库的权限。
DBMS通过SQL提供的GRANT和REVOKE语句,实现用户间权限的授予,使得不同用户对不同的数据库对象,可有不同的存取权限。不同用户对于同一数据库对象也可有不同的权限。
2.授权GRANT
3.收权REVOKE
4.自主访问控制示例
可见在SQL SERVER中,RESTRICT选项是默认选项。
小结:
在有的DBMS中,可为一组具有相同权限的用户创建一个角色,通过角色授权来简化为用户授权的过程,其授权操作同给用户授权一样。