导图社区 数据库基本语法
- 83
- 2
- 1
- 举报
数据库基本语法
数据库基本语法思维导图，本图较详细地列出了数据库中的基本语法和一些关键例子，有助于帮助您熟悉知识要点，加强记忆。
编辑于2022-05-26 00:25:42- 数据库SQL
- 数据库基本语法
- 相似推荐
- 大纲
数据库基本语法 颜色表示可以省略 颜色表示再次省略
模式的定义与删除
定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
如果没有指定<模式名>，那么<模式名>隐含为<用户名>。
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
删除模式
DROP SCHEMA <模式名><CASCADE|RESTRICT>;
CASCADE（级联）
表示在删除模式的同时，把该模式中所有的数据库对象全部删除
RESTRICT（限制）
表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。
基本表的定义、删除与修改
定义基本表
CREATE TABLE 表名 ( 列名 数据类型 列级完整性约束条件, 列名 数据类型 列级完整性约束条件, …… 表级完整性约束条件 );
例如
CREATE TATLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY(Sno,Cno), /*在表级定义实体完整性*/ FOREIGN KEY(Sno) REFORENCES Student(Sno) /*在表级定义参照完整性*/ ON DELETE CASCADE /*级联删除SC表中相应的元组*/ ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/ FOREIGN KEY(Cno) REFORENCES Course(Cno) /*在表级定义参照完整性*/ ON DELETE NO ACTION /*当删除Course表中的元组造成了与SC表不一致时拒绝删除*/ ON UPDATE CASCADE /*当更新Course表中的Cno时，级联更新SC表中相应的元组*/ );
完整性约束命名子句
CONSTRAINT 完整性约束条件名 完整性约束条件
例如
建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是男或女。
CREATE TABLE Student (Sno NUMERIC(6) CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999) )
实体完整性
primary key
主键
参照完整性
foreign key
定义哪些列为外码
references
指明这些外码参照哪些表的主码
用户自定义完整性
not null
非空约束
unique
唯一性约束
Sname CHAR(20) UNIQUE
check
限制列中的值的范围
Ssex CHAR(2) CHECK(Ssex IN('男',女''))
元组约束
当学生的性别是男时，其名字不能以Ms.打头
CHECK(Ssex='女' OR Sname NOT LIKE 'Ms.%') /*定义了元组中Sname和Ssex两个属性值之间的约束条件*/
default
没有规定其他的值，那么会将默认值添加到所有的新记录中
学习博客
https://blog.csdn.net/u012045045/article/details/85267739
模式与表
每一个基本表都属于某一个模式，一个模式包含多个基本表。
显示当前搜索路径
SHOW search_path;
设置搜索路径
SET search_path TO "S_T",PUBLIC;
断言
数据类型
char(n),character(n)
长度为n的定长字符串
varchar(n),charactervarying(n)
最大长度为n的变长字符串
clob
字符串大对象
blob
二进制大对象
int, integer
长整数（4字节）
smallint
短整数（2字节）
bigint
大整数（8字节）
numeric(p,d)
定点数，由p位数字（不包括符号、小数点）组成，小数后面有d为数字
decimal(p,d),dec(p,d)
同numeric(p,d)
real
取决于机器精度的单精度浮点数
double precision
取决于机器精度的双精度浮点数
float(n)
可选精度的浮点数，精度至少为n位数字
boolean
逻辑布尔量
date
日期，包含年、月、日，格式为YYYY-MM-DD
time
时间，包含一日的时、分、秒，格式为HH:MM:SS
timestamp
时间戳类型
interval
时间间隔类型
修改基本表（结构）
ALTER TABLE 表名 ADD COLUMN新列名 数据类型 完整性约束 ADD 表级完整性约束 DROP COLUMN列名 CASCADE | RESTRICT DROP CONSTRAINT 完整性约束名 CASCADE | RESTRICT ALTER COLUMN 列名 数据类型；
删除基本表
DROP TABLE 表名 CASCADE | RESTRICT;
默认 RESTRICT
索引
建立索引
CREATE UNIQUE CLUSTER INDEX 索引名 ON 表名(列名 次序,列名 次序...);
次序
ASC升序
默认
DESC降序
eg
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
修改索引
ALTER INDEX 旧索引名 RENAME TO 新索引名;
eg
ALTER INDEX SCno RENAME TO SCSno;
删除索引
DROP INDEX 索引名;
数据查询
SELECT ALL|DISTINCT目标表达式1,目标表达式2... FROM 表名或者视图名1,表名或者视图名2...|(SELECT语句) AS 别名 WHERE 条件表达式 GROUP BY 列名 HAVING 条件表达式 ORDER BY 列名 ASC|DESC;
DISTINCT
过滤掉重复的记录只保留一条
ESCAPE
指定的字符后面紧挨着的第一个字符被看作是普通字符而非通配符 （转义功能）
单表查询
查询条件与谓词
比较大小
=，>，<，>=，<=，!=，<>，!>，!<
确定范围
BETWEEN AND， NOT BETWEEN AND
SELECTE Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 30;
确定集合
IN，NOT IIN
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('CS','MA','IS');
字符通配
LIKE，NOT LIKE
SELECT Sname,Sno FROM Student WHERE Sname LIKE '_阳%';
SELECT * FROM Course WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';
ESCAPE '\'表示“\”为换码字符。这样匹配串中紧跟在“\”后面的字符"_"不在具有通配符的含义，转义为普通的"_"字符。 紧跟：也就是一个"\"对应一个通配符，例如转换连续的两个: \_\% ESCAPE '\';
几个符号
*
表中的所有属性
%
一个%匹配多个字符
_
一个 _ 匹配一个字符
涉及空值
IS NULL，IS NOT NULL
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
多重条件
AND，OR，NOT
ORDER BY子句
对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，缺省值为升序。
聚集函数
COUNT(DISTINCT|ALL *)
统计元组个数
COUNT(DISTINCT|ALL列名)
统计一列中值的个数
SUM(DISTINCT|ALL列名)
计算一列中的总和
AVG(DISDINCT|ALL列名)
计算一列中的平均值
MAX(DISTINCT|ALL列名)
计算一列中的最大值
MIN(DISTINCT|ALL列名)
计算一列中的最小值
此列必须是数值型
GROUP BY子句
将查询按某一列或多列的值分组，值相等的为一组。
作用
细化聚集函数的作用对象。分组后聚集函数将作用于每一个组，即每一组都有一个函数值
注意事项
WHERE 子句和 HAVING 短语的区别在于作用对象不同。
WHERE 子句作用于基本表或视图，从中选择满足条件的元组。
HAVING 短语作用于组，从中选择满足条件的组。
eg
SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade>=90);
多表查询
等值连接
不用去相同的属性
SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno;
自然连接
去掉相同的属性
SLECT Student.*,SC.Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno;
自身连接
把同一张表取两个不同的表名，容易区分
SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno;
FIRST / SECOND都是表Couse的别名，用不用AS都一样： Course AS FIRST
外连接
SELECT Student.*,SC.Cno,Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno=SC.Sno;
复合查询
当WHERE子句中有多个连接条件时
SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno='2'AND SC.Grade>80;
嵌套查询
分类
不相关子查询
子查询的查询条件不依赖于父查询
相关子查询
子查询的查询条件依赖于父查询
带有IN的子查询
例：查询与‘刘晨’在同一个系学习的学生
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='刘晨');
子查询先把条件结果确定（所在系）查询出来成一个集合，再进行父查询。
带有比较运算符的子查询
确切知道内层查询返回的是单值时，可以用>，<，=，>=，<=，!=或<>等比较运算符
SELECT Sno,Sname Sdept FROM Student WHERE Sdept = (SELECT Sdapt FROM Student WHERE Sname='刘晨');
SELECT Sno,Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);
这个语句的一种可能的执行过程采用以下三个步骤： ①从外层查询中取出SC的一个元组x,将元组x的Sno值(201215121)传送给内层查询。  ②执行内层查询,得到值88(近似值),用该值代替内层查询,得到外层查询:  ③执行这个查询,得到  然后外层查询取出下一个元组重复做上述①至③步骤的处理,直到外层的SC元组全部处理完毕。结果为  求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来,然后求解父查询。内层查询由于与外层查询有关,因此必须反复求值。
带有ANY或ALL谓词的子查询
!=(或<>)ANY 不等于子查询结果中的某个值
!=(或<>)ALL 不等于子查询结果中的任何一个值
ANY指某个值 ALL所有的值
其他类似解释
查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄
SELECT Sname,Sage FROM Student WHERE Sage<ALL (SELETE Sage FROM Student WHERE Sdept='CS') AND Sdept<>'CS';
带有EXISTS（存在量词）谓词的子查询
带有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假 值“false”。
由 EXISTS 引出的子查询，其目标列表达式通常都用* ，因为带 EXISTS 的子查询只返回真值或假值，给出列名无实际意义
eg：查询所有选修了1号课程的学生姓名
SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1');
【与IN比较】博客
https://blog.csdn.net/qq_32800367/article/details/82467804
数据更新
插入数据
插入元组
INSERT INTO 表名(属性1,属性2,…,属性n) VALUES(属性1值,属性2值,…,属性n值);
修改数据
UPDATE 表名 SET 属性=表达式,... WHERE 条件;
删除数据
DELETE FROM 表名 WHERE 条件;
视图(虚表)
建立视图
CREATE VIEW 视图名（属性1,属性2,...） AS 子查询 WITH CHECK OPTION；
WITH CHECK OPTION ： 表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
删除视图
DROP VIEW 视图名
查询视图
更新视图
修改
插入
删除
和基本表一样 视图消解法
安全性控制
赋予权限，DRANT语句
GRANT 权限1,权限2... ON 对象类型1 对象名1,对象类型2 对象名2... TO 用户1,用户2... WITH GRANT OPTION;
WITH GRANT OPTION ： 指定的用户可以传播这种授权，否则只有使用的权限，没有授权的权限。
对象类型：表 | 视图 ...
收回权限，REVOKE子句
REVOKE 权限1,权限2... ON 对象类型1 对象名1,对象类型2 对象名2... FROM 用户1,用户2... CASCAND | RESTRICT;
CASCAND
级联回收，一般不写是默认
RESTRICT
防止级联回收，受限回收
创建数据库用户
只有超级用户才有权创建一个新的数据库用户
CREATE USER 用户名 WITH DBA | RESOURCE | CONNECT ;
默认CONNET权限
eg
每个职工只对自己的记录有SELECT权限
GRANT SELECT ON TABLE Employee WHEN USER()=NAME TO ALL;
WHEN USER()=NAME TO ALL; --这个条件就是表示每一个自己啦
数据库角色
角色的创建
CREATE ROLE 角色名;
给角色授权
GRANT 权限1,权限2... ON 对象类型1 对象名1,对象类型2 对象名2... TO 角色1,角色2... WITH GRANT OPTION;
将一个角色授予其他角色或用户
GRANT 角色1,角色2,... TO 角色3,用户1,... WITN ADMIN OPTION;
WITN ADMIN OPTION
可以级联授权
角色权限的收回
REVOKE 权限1,权限2... ON 对象类型1 对象名1,对象类型2 对象名2... FROM 角色1,角色2... CASCAND | RESTRICT;
CASCAND
级联收回
RESTRICT
防止级联收回
视图机制
eg:建立计算机系学生的视图，把对该视图的SELECT权限授予王平，把该视图的所有操作权限授予张明。
CREATE VIEW CS_Student AS SELECT * FROM Student WHERE Sdept='CS'; GRANT SELECT ON CS_Student TO 王平; GRANT ALL PRIVILEGES ON CS_Student TO 张明;
审计
AUDIT语句用来设计审计功能
例4.14 对修改SC表结构或修改SC表数据的操作进行审计
AUDIT ALTER, UPDATE ON SC;
NOAUDIT语句用来取消审计功能
例4.15 取消对SC表的一切审计
NOAUDIT ALTER, UPDATE ON SC;
数据库完成性
实体完整性
在列级定义主码

在表级定义主码

只能在表级定义主码

(1)检查主码值是否唯一,如果不唯一则拒绝插入或修改 (2)检查主码的各个属性是否为空,只要有一个为空就拒绝插入或修改
参照完整性
在 CREATE TABLE中用 FOREIGN KEY短语定义哪些列为外码,用 REFERENCES短语指明这些外码参照哪些表的主码。
eg

eg

自定义完整性
列值非空（NOT NULL）

列值唯一（UNIQUE）

检查列值知否满足一个条件表达式（CHECK短语）

完整性约束命名子句
定义
CONSTRAINT 完整性约束条件名 完整性约束条件
eg:建立教师表 TEACHER,要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项 Deduct之和。
CREATE TABLE TEACHER (Eno NUMERIC(10) PRIMARY KEY, /*在列级定义主码*/ Ename CHAR(10), Job CHAR(8), Sal NUMERIC(7,2), Deduct NUMERIC(2), CONSTRAINT TEACHERKey FOREIGN KEY(Deptno) REFERENCES DEPT(Deptno), CONSTRAINT C1 CHECK (Sal + Deduct >= 3000) );
修改
[例5.13] 修改表 Student中的约束条件,要求学号改为在900000-999999之间,年龄由小于30改为小于40（可以先删除原来的约束条件，再增加新的约束条件）(例5.10书上p165)
ALTER TABLE Student DROP CONSTRAINT C1; ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999); ALTER TABLE Student DROP CONSTRAINT C3; ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage < 40);
域中的完整性约束限制
域
是一组具有相同数据类型的值的集合。
优点
是数据库中不同的属性可以来自同一个域,当域上的完整性约束条件改变时只要修改域的定 义即可,而不必一一修改域上的各个属性。
建立一个域

建立域，并对其中的限制命名

删除域的限制条件

在域上添加限制条件

断言
目的
指定更具一般性的约束。断言创建以后,任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查,任何使断言不为真值的操作都会被拒绝执行。
创建断言的格式
CREATE ASSERTION 断言名 CHECK子句
[例5.18] 限制数据库课程最多60名学生选修。

[例5.19] 限制每一门课程最多60名学生选修。

[例5.20] 限制每个学期每一门课程最多60名学生选修。

删除断言
DROP ASSERTION 断言名;
触发器
建立触发器
CREATE TRIGGER 触发器名 /*每当触发事件发生时，该触发器被激活*/ BEFORE | AFTER 触发事件 ON 表名 /*指明触发器激活的时间是在执行触发事件前或后*/ REFERENCING NEW | OLD ROW AS 变量 /*REFERENCING指明引用的变量*/ FOR EACH ROW | STATEMENT /*定义触发器的类型，指明动作体执行的效率*/ WHEN 触发条件触发体动作 /*仅当触发条件为真时才执行触发动作体*/
[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

删除触发器
DROP TRIGGER 触发器名 ON 表名;