关系数据库标准语言SQL
SQL:结构化查询语言是关系数据库的标准语言。
3.1SQL概述
SQL的特点:综合统一;高度非过程化;面向集合的操作方式;以同一种语法结构提供多种使用方式;语言简洁,易学易用。
数据定义语言DDL,数据操纵语言DML,数据控制语言DCL。
SQL功能:数据查询select;数据定义create,drop,alter;数据操纵insert,update,delete;数据控制grant,revoke。
3.2数据定义
数据定义:包括模式、表、视图、索引定义。
模式的创建删除,表的创建删除修改,视图的创建删除,索引的创建删除修改。
一个关系数据库管理系统的实例中可以建立多个数据库,一个数据库可以建立多个模式,一个模式下通常包括多个表、视图、索引等数据库对象。
3.2.1模式的定义与删除
定义模式:create schema 模式名 authorization 用户名;或create schema 模式名 authorization 用户名 [表定义子句|视图定义子句|授权定义子句];
如果没有指定模式名,模式名隐含为用户名;创建模式时调用该命令的用户必须拥有数据库管理员权限,或者获得数据库管理员授予的create schema的权限。
删除模式:drop schema 模式名 cascade|restrict;
Cascade:级联,删除模式的同时把该模式中所有的数据库对象全部删除;
Restrict:限制,如果该模式中已经定义了下属的数据库对象,则拒绝该删除语句的执行。
(创建了一个模式就建立了一个数据库的命名空间,一个框架)
3.2.2基本表的定义、删除、修改
定义基本表:create table 表名 (列名 数据类型 [列级完整性约束条件]
[,列名 数据类型 [列级完整性约束条件]]
…
[,表级完整性约束条件]);
(如果完整性约束条件涉及该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级)
数据类型:char(n)长度为n的定长字符串;varchar(n)最大长度为n的变长字符串;int长整数(4字节),smallint短整数(2字节),bigint大整数(8字节);float(n)浮点数;date日期,time时间。一个属性选用哪种数据类型要根据实际情况来决定,(取值范围,做什么运算)。
模式与表:每一个基本表都属于某一个模式,一个模式包含多个基本表。
在表名中明显地给出模式名;在创建模式语句中同时创建表;
设置所属的模式:show search_path;显示当前的搜索路径;
set search_path to 模式名,public;
然后定义基本表create table 表名(…);
修改基本表:alter table 表名 [add [column] 新列名 数据类型 [完整性约束]]
[add 表级完整性约束]
[drop [column] 列名 [cascade|restrict]]
[drop constraint 完整性约束名 [restrict|cascade]]
[alter column 列名 数据类型];
add子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件;
drop column 子句用于删除表中的列(若restrict,列被引用,RDBMS拒绝删除列);
drop constraint子句用于删除指定的完整性约束条件
alter column子句用于修改原有的列定义,包括修改列名和数据类型。
删除基本表:
drop table 表名 [restrict|cascade];(默认情况是restrict)
3.2.3索引的建立与删除
在数据库中为提高查询速度而设置的逻辑排序手段称为索引。顺序文件上的索引;B+树索引;散列索引;位图索引。
顺序文件上的索引:是针对按指定属性值升序或降序存储的关系,在该属性上建立一个顺序索引文件,索引文件由属性值和相应的元组指针组成。
B+树索引:将索引属性组织成B+树形式,B+树的叶结点为属性值和相应的元组指针,具有动态平衡的优点。
散列索引:建立若干个桶,将索引属性按照其散列函数值映射到相应桶中,桶中存放索引属性值和相应的元组指针,查找速度快。
位图索引:用位向量记录索引属性中可能出现的值,每个位向量对应一个可能值。
用户不能显式的选择索引。索引是关系数据库管理系统的内部实现技术,属于内模式的范畴。
1.建立索引:
create [unique] [cluster] index 索引名
on 表名 (列名 [次序],……);
列名后面可以用次序指定索引值的排列次序,asc升序(默认) desc降序;Unique:表明此索引的每一个索引值只对应唯一的数据记录;Cluster:表示要建立的索引为聚簇索引。
2.修改索引:alter index 旧索引名 rename to 新索引名;
3.删除索引:drop index 索引名;
3.2.4数据字典
数据字典:是关系数据库管理系统内部的一组系统表,它记录了数据库中所有定义的信息,包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。
3.3数据查询
Select [all|distinct] <目标列表达式> [,<目标列表达式>] ……
From 表名或视图名 [,表名或视图名…]|(select语句) as 别名
[Where 条件表达式]
[Group by 列名 [having 条件表达式]]
[Order by 列名 [asc|desc]];
3.3.1单表查询
选择表中的若干列:查询指定列,查询全部列*,查询经过计算的值(lower()小写字母)。*
选择表中的若干元组:消除取值重复的行distinct,查询满足条件的元组where子句。
1.Where子句:
比较大小:比较运算符。
确定范围:between and ,not between a and b。(a≤ x ≤b)
确定集合:in,not in。
字符匹配:like ‘匹配串’[escape ‘换码字符’];%代表任意长度,_代表任意单个字符。Escape ‘\’:紧跟在\后的字符不做通配符。
涉及空值的查询:is null,is not null。
多重条件查询:and,or(and优先级高于or)。
2.聚集函数:
Count():统计元组个数。
Count(distinct|all 列名):统计一列中值的个数。
Sum(distinct|all 列名):计算一列值的总和(此列必须是数值型)。
Avg(distinct|all 列名):计算一列值的平均值(此列必须是数值型)。
Max(distinct|all 列名):求一列值中的最大值。
Min(distinct|all 列名):求一列值中的最小值。
当聚集函数遇到空值时,除count()外都跳过空值而只处理非空值。
Where子句中是不能用聚集函数作为表达式的,聚集函数只能用于select子句和group by子句中的having子句。
3.Group by子句:将查询结果按某一列或多列的值分组,值相等的为一组。(where子句与having短语的区别在于作用对象不同,where子句作用于基本表或视图,从中选择满足条件的元组,having短语用于组,从中选择满足条件的组)
4.Order by子句:对最终查询结果按照一个或多个属性列的升序或降序排列,默认升序。(按升序排时,含空值的元组最后显示;按降序排时,空值的元组最先显示)(Order by子句所产生的输出只是逻辑排序效果,并没有影响表的实际内容)
3.3.2连接查询
等值与非等值连接:where子句中用来连接两个表的条件称为连接条件或连接谓词,连接谓词中的列名称为连接字段,连接条件中的连接字段类型必须是可比的,但名字不必相同。一条SQL语句可以同时完成选择和连接查询,这时where子句是由连接谓词和选择谓词组成的复合条件。
等值连接中把目标列中的重复属性列去掉则为自然连接(绝大多数都是自然连接)。
自身连接:一个表与其自己进行连接,称为表的自身连接。
外连接:left outer join,right outer join,full outer join
From 表1 full outer join 表2
On 。。=。。
多表连接:student.sno=sc.sno and sc.sno=course.sno。
3.3.3嵌套查询
将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询。上层查询块称外层查询或父查询,下层查询块称内层查询或子查询。子查询的select语句中不能使用order by子句,order by子句只能对最终查询结果排序。
带有in谓词的子查询:子查询的查询条件不依赖于父查询,称为不相关子查询;如果子查询的查询条件依赖于父查询,这类子查询称为相关子查询,整个查询语句称为相关嵌套查询语句。
带有比较运算符的子查询:当用户能确切知道内层查询返回的是单个值时可以使用比较运算符。
带有any some或all谓词的子查询:子查询使用单值时可以用比较运算符,但返回多值时要用any,some,all谓词修饰符。使用any或all谓词时则必须同时使用比较运算符。any大于子查询结果中的某个值,all所有值。
带有exists谓词的子查询:带有exists谓词的子查询不返回任何数据,只产生逻辑真值或逻辑假值。Not exists用于“所有的”,“全部的”。
3.3.4集合查询
并操作union,交操作intersect,差操作except。参加集合操作的各查询结果的列数必须相同,对应项的数据类型也必须相同。
3.3.5基于派生表的查询
子查询不仅可以出现在where子句中,还可以出现在from子句中,这时子查询生成的临时派生表成为主查询的查询对象。通过from子句生成派生表时,as关键字可以省略,但必须为派生表指定一个别名。
3.4数据更新
3.4.1插入数据
插入数据:插入元组或插入子查询结果
插入元组:insert into 表名(属性列,…,属性列) values(值1,…,值);(values括号里的值字符串常数需要单引号括起来)
插入子查询结果:insert into 表名(属性列,…,属性列)子查询;
3.4.2修改数据
修改数据:update 表名 set 列名=表达式,列名=表达式… where 条件;
3.4.3删除数据
删除数据:delete from 表名 where 条件;
从指定表中删除满足where子句条件的所有元组。删除的是表中的数据而不是关于表的定义。
3.5空值的处理
空值与另一个值的算术运算的结果为空值,空值与另一个值的比较运算的结果为unknown。有了unknown后,传统的逻辑运算中二值逻辑就扩展成了三值逻辑。
3.6视图
视图是从一个或几个基本表(或试图)导出的表。是一个虚表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中。
3.6.1定义视图
建立视图:create view 视图名(列名,…,列名) as 子查询 [with check option];
with check option:表示对视图进行update,insert,delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。[组成视图的属性列名或者全部省略或者全部指定,没有第三种选择(如果省略了视图的各个属性列名,则隐含该视图由子查询中select子句目标列中的诸字段组成)下列三种情况必须明确指定组成视图的所有列名:某个目标列不是单纯的属性名,而是聚集函数或列表达式;多表连接时选出了几个同名列作为视图的字段;需要在视图中为某个列启用新的更适合的名字。]
关系数据库管理系统执行create view语句的结果只是把视图的定义存入数据字典,并不执行其中的select语句,只是在对试图查询时,才按视图的定义从基本表中将数据查出。
若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,称这类视图为行列子集视图。
带虚拟列的视图也称为带表达式的视图:由于视图中的数据并不实际存储,所以定义视图时可以根据应用的需要设置一些派生属性列,这些派生属性由于在基本表中并不实际存在,也称它们为虚拟列。
分组视图:用带有聚集函数和group by子句的查询来定义视图
删除视图:drop view 视图名 [cascade];
基本表删除后,由该基本表导出的所有视图均无法使用,但是视图的定义没有从字典中清除。删除这些视图定义需要显式地使用drop view语句。
3.6.2查询视图
关系数据库管理系统执行对视图的查询时,首先进行有效性检查,检查查询中涉及的表、视图等是否存在,如果存在,则从数据字典中取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询,这一转换过程称为视图消解。
定义视图并查询视图与基于派生表的查询是有区别的,视图一旦定义,其定义将永久保存在数据字典中,之后的所有查询都可以直接引用该视图,而派生表只是在语句执行时临时定义,语句执行后该定义即被删除。
3.6.3更新视图
由于视图是不实际存储数据的虚表,对视图的更新最终要转换为对基本表的更新。
在关系数据库中,并不是所有的视图都是可更新的,因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。一般地,行列子集视图是可更新的。
若视图是由两个以上基本表导出的,则此视图不允许更新;
若视图的字段来自字段表达式或常数,则不允许对此视图执行insert和update操作;
若视图的字段来自聚集函数,则此视图不允许更新;
若视图定义中含有group by子句,则此视图不允许更新;
若视图定义中含有distinct短语,则此视图不允许更新;
若视图定义中有嵌套查询,并且内层查询的from子句中涉及的表也是导出该视图的基本表,则此视图不允许更新;
一个不允许更新的视图上定义的视图也不允许更新。
(不可更新的视图与不允许更新的视图是两个不同的概念,前者指理论上已证明其是不可更新的视图,后者指实际系统中不支持其更新,但它本身有可能是可更新的视图)
3.6.4视图的作用
视图的作用:能够简化用户的操作;使用户能以多种角度看待同一数据;对重构数据库提供了一定程度的逻辑独立性;能够对机密数据提供安全保护;可以更清晰地表达查询。
视图机制使系统具有数据安全性、逻辑独立性、操作简单性。