SQL语句的整理

SQL语句

注：参考书目：

1. 数据库系统概论 王珊编著
2. SQL必知必会 [美]本·福达编著

数据库的操作

创建数据库

create {database|schema} 数据库名;

使用数据库

use 数据库名;

修改数据库

alter {database|schema} [数据库名];

删除数据库

drop database 数据库名;

基本表的操作

基本表：一张基本表代表一个关系。

创建基本表

create table 表名 (列名 数据类型 [列级完整性约束]，
                 列名 数据类型 [列级完整性约束]，
                 ···
                [表级完整性约束条件]
                );

• 列级完整性约束写在对应列的后面，表级完整性约束写在最后面。

修改基本表

alter table 表名
[add [column] 新列名 数据类型 [完整性约束条件]]
[add 表级完整性约束]
[drop [column]] 列名 [cascade|restrict]
[drop constraint] 完整性约束 [cascade|restrict]
[alter column 列名 数据类型]

作用：

• 添加新的列，新的完整性约束
• 添加新的表级完整性约束
• 删除表中的列，cascade表示自动删除该列的其他对象，restrict表示如果有别人引用则拒绝删除该列。
• 删除指定的完整性约束
• 修改原有的列定义

删除基本表

drop table 表名 [cascade|restrict];

• restrict：级联：如果表下还有视图，触发器等，该表不能被删除。
• cascade：限制：删除基本表的同时，连视图等一起被删除

索引的操作

索引：索引就一种特殊的查询表，数据库的搜索引擎可以利用它提高检索效率。

创建索引

create [unique] [cluster] index 索引名
on 表名(列名 [次序],)(列名 [次序]···)；;

• 次序：asc：升序，desc：降序
• unique：每个索引值只对应一个数据记录
• cluster：建立的索引是聚簇索引

修改索引

alter index 旧索引 rename to 新索引名;

删除索引

drop index 索引名;

查询操作

select [all|distinct] 目标列表达式 [别名]，目标列表达式 [别名]···
from 表名或者视图，表名或者视图···
[where 表达式]
[group by 列名 [having 条件表达式]] 
[order by 列名 [asc|desc]]

• 别名前面可以加as或者不加
• 目标列表达式可以是*，表示该表中全部列。
• where子句从from中表或者视图中找出满足条件的元组
• group by子句将查询结果按某一列或者多列进行分组
• order by子句用来排序

where字句中的条件

查询条件	谓词
比较运算符	=，>，<，>=，<=，！!=，<>，!>，!<
确定范围	between and，not between and
确定集合	in ，not in
字符匹配	like，not like（%代表任意长度，_代表单个字符）
空值	is null，is not null
逻辑运算符	and，or，not

• 字符匹配末尾有空格会影响查询结果。

一些用法的实现：

函数

使用内置函数用来处理数据

文本处理函数

函数	说明
length()	返回字符串的长度
lower()	将字符串转换为小写
upper()	将字符串转换为大写
ltrim()	去掉字符串左边的空格
rtrim()	去掉字符串右边的空格
soundex()	返回发音相同的字符串

数值处理型函数

函数	说明
abs()	返回数值的绝对值
cos()	返回数值的余弦值
exp()	返回数值的指数值
pi()	返回圆周率的数值
sin()	返回数值的正弦值
sqrt()	返回数值的平方根
tan()	返回数值的正切值

聚集函数

聚集函数	说明
count(*)	统计元组个数
count([distinct|all] 列名)	统计一列中值的个数
sum([distinct|all] 列名)	计算一列值的总和
avg([distinct|all] 列名)	计算一列值的平均值
max([dsitinct|all] 列名)	计算一列值的最大值
min([distinct|all] 列名)	计算一列值的最小值

• 聚集函数用于汇总数据
• distinct用于取消同一列的相同值
• 💥💥聚集函数只能用于select子句和group by的having子句

联结表

内联结

也就是等值连接。

selext student.*,sc.* from student,sc where student.sno=sc.sno;

自身联结

也就是自己连接自己。

外联结

将悬空数组仍保存在关系中。

select student.sno,sno cno,grade from student left outer join sc on (student.sno=sc.sno);

多表联结

多个表联结在一起

···student.sno=sc.sno and sc.cno=course.cno;

嵌套查询

不相关子查询

子查询的查询条件不依赖于父查询。

相关子查询

子查询的查询条件依赖于父查询。

select sno,cno 
from sc x 
where grade>=(
    selext avg(grade) 
    from sc y 
    where x.sno=y.sno);

any和all的子查询

any是任意，all是全部，>any,<any,>all,<all这些都非常容易理解，这就不一一列举了。

exists子查询

这个非常难。

它只返回逻辑值true和false，不返回任何数据。

例：查询至少选修了学生201215122选修的全部课程的学生号码。

select distinct sno
from sc x
where not exists(select *
                from sc y
                where y.sno='201215122' and
                not exists(select *
                          from sc z
                          where z.sno=x.sno and
                          z.cno=y.cno));

集合查询

union（并操作），intersect（交操作）和except（差操作）。

数据更新操作

插入数据

insert into 表名 [(属性列···)] values (常量···)

• into子句没有列出属性，values子句必须每个属性列都有值（空值要写null）

修改数据

update 表名 set 列名=表达式··· [where 条件]

删除数据

delete from 表名 [where 条件]

视图的操作

视图：视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

建立视图

create view 视图名 [列名···] as 子查询 [with check option];

• with check option表示在对视图进行操作时要满足视图定义的谓词条件（也就是子查询中的where子句）

删除视图

drop view 视图名 [cascade]

查询视图

视图消解：从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。

更新视图

视图更新的规定：

1. 若视图是由两个以上基本表导出的，则此视图不允许更新。
2. )若视图的字段来自聚集函数，则此视图不允许更新。
3. 若视图定义中含有GROUP BY子句，则此视图不允许更新。
4. 若视图定义中含有DISTINCT短语，则此视图不允许更新。
5. 若视图的字段来自字段表达式或常数，则不允许对此视图执行insert和update操作，但允许执行delete操作。

授权操作

自主存取控制：

• 用户对不同的数据对象有不同的存取权限。
• 不同的用户对同一对象也有不同的权限。
• 用户还可以将其拥有的存取权限转授给其他用户。

授予

grant 权限··· on 对象类型 对象名··· to 用户··· [with grant option];

• with grant option 表示获得某种权限的对象也可以将此权限授予其他对象，但不允许循环授权。

收回

revoke 权限··· on 对象类型 对象名··· from 用户 [cascade|restrict];

创建数据库的权限

grant和revoke是对用户授予和收回对数据的操作权限，对创建数据库模式一类的数据库对象的授权则由数据库管理员在创建用户时实现。

create user uername [with][dba|resourse|connect];

• dba：拥有dba权限的用户是系统中的超级用户，可以创建新的用户创建模式创建基本表和视图等。
• resourse：拥有resource权限的用户可以创建基本表和视图，成为所创建对象的一种，但不能创建模式，不能创建新的用户。
• connect（默认）：拥有connect权限的用户，不能创建新用户，不能创建模式，也不能创建基本表，只能登录数据库。

用户定义完整性

属性约束条件

• not null：列值非空
• unique：列值唯一
• check语句：检查列值是否满足一个条件表达式

ssex char(2) check(ssex in('男','女'))

元组约束约束条件

check (sex='男' or sname not like 'Ms.%');

• 写在最后

完整性约束命名句

constraint 完整性约束条件名 完整性约束条件

• 完整性约束条件命名，可以灵活的增加和删除一个完整性约束。

sname char(20) constraint c1 not null;

修改完整性约束

alter table 表名 [drop|add] constraint 完整性约束条件名 完整性约束条件