数据库

软考-数据库系统工程师-SQL章节

SQL语法规则

一、创建表语句
- 1. 主键
- 2. 外键
二、修改表语句
三、删除表
四、创建删除索引
- 1. 创建索引
- 2. 删除索引
五、创建删除视图
六、数据操作
七、数据库授权
- 1. 完整性约束
- 2.授权与销权
  - 2.1. 授权(GRANT)
  - 2.2. 销权(REVOKE)
八、触发器
九、嵌入式SQL
十、存储过程
十一、事务

一、创建表语句

CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束条件],
                  [<列名><数据类型>[列级完整性约束条件]]···
                  [,<表级完整性约束条件>]);

1. 主键

PRIMARY KEY(C_BH)   -- 加在字段后面或结尾（分号前）
PRIMARY KEY(C_NAME, C_ITEM, ……)  -- 多个属性的主键仅可加在结尾
NOT NULL UNIQUE  -- 等同于主键，仅可加在字段后面，NNU和PK仅一个即可

2. 外键

REFERENCES T_TABLE(C_BH)   -- 加在字段后面
FOREIGN KEY(C_BH_JZ) REFERCNCES T_TABLE(C_BH)  -- 加在结尾（分号前）

二、修改表语句

ALTER TABLE <表名>[ADD<新列名><数据类型>[完整性约束条件]]
[DROP<完整性约束名>]
[MODIFY<列名><数据类型>];

示例

ALTER TABLE T_TABLE ADD C_NEW_COLUMN VARCHAR(300);
ALTER TABLE T_TABLE MODIFY C_NEW_COLUNM INT;

三、删除表

DROP TABLE <表名>;

四、创建删除索引

1. 创建索引

CREATE [UNIQUE][CLUSTER] INDEX <索引名> 
ON <表名>(<列名>[<次序>][,<列名>[<次序>]]···);
-- 次序：ASC/DESC, 即升序或降序，默认ASC升序
-- UNIQUE 唯一索引，表明此索引的每一个索引值只对应唯一数据记录
-- CLUSTER 聚簇索引，表明索引项的顺序是与表中记录的物理顺序一致

示例

CREATE UNIQUE INDEX S-SNO ON S(Sno);
CREATE UNIQUE INDEX SPJ-NO ON SPJ(Sno ASC, Pno DESC, Jno ASC);

2. 删除索引

DROP INDEX <索引名>;

五、创建删除视图

1.创建视图

CREATE VIEW 视图名(列名) AS SELECT 查询子句 [WITH CHECK OPTION];
-- WITH CHECK OPTION: 表示对UPDATE、INSERT、DELETE操作时保证更新插入删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

2.删除视图

DROP VIEW 视图名;

3.视图操作

视图操作规则

从多个基本表通过连结操作到处的视图不允许更新。
对使用了分组、集函数操作的视图不允许进行更新操作。
如果视图是从单个基本表通过投影、选取操作导出的则允许进行更新操作，且语法同基本表。

六、数据操作

1. 查询

SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]··· 
FROM <表名或视图名>[,<表名或视图名>]···
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]···]

2. 子查询

示例

SELECT Sno, Sname FROM S WHERE Sno IN (SELECT Sno FROM SC);

2.1. 谓词 ALL/ANY

示例

SELECT Sname, Sage FROM S WHERE Sage < ALL(SELECT Sage FROM S);
即等价于：
SELECT Sname, Sage FROM S WHERE Sage < (SELECT MIN(Sage) FROM S);

等价转换

谓词	语义	等价转换关系
>ANY	大于子查询结果中的某个值	>MIN
<ANY	小于子查询结果中的某个值	<MAX
=ANY	等于子查询结果中的某个值	IN
>ALL	大于子查询结果中的所有值	>MAX
<ALL	小于子查询结果中的所有值	<MIN
<>ALL	不等于子查询结果中的所有值	NOT IN

2.2.分组查询

GROUP BY子句、HAVING子句

示例

SELECT Jno, AVG(Qty) FROM SPJ 
GROUP BY Jno HAVING COUNT(DISTINCT(Sno)) > 2 
ORDER BY Jno DESC

3. 更名/别名

AS

示例

SELECT C_BH AS 编号, C_XM AS 姓名 FROM T_TABLE;

4. 字符串操作（模糊查询）

LIKE 关键字

特殊字符	匹配方式
%	任意字符串
_	任意一个字符

示例

SELECT * FROM T_TABLE WHERE C_NAME LIKE '%秀%';   -- 名字包含秀的
SELECT * FROM T_TABLE WHERE C_NAME LIKE '_建国';   -- 名字叫x建国的
SELECT * FROM T_TABLE WHERE C_NAME LIKE '___';    -- 名字是3个字符的
SELECT * FROM T_TABLE WHERE C_NAME LIKE '__%';    -- 名字至少包含2个字符的

5. 集合操作

关键字： UNION、INTERSECT、EXCEPT

关键字	含义
UNION [ALL]	并集（UNION去重，UNION ALL不去重）
INTERSECT	交集
EXCEPT	差集 - 有顺序

示例

（SELECT C_BH, C_NAME FROM T_TABLE1）
UNION/INTERSECT/EXCEPT                    -- EXCEPT 指在T_TABLE1中但不在T_TABLE2中
(SELECT C_BH, C_NAME FROM T_TABLE2)

七、数据库授权

1. 完整性约束

完整性约束分为：实体完整性约束、参照完整性约束、用户自定义完整性约束

1.1. 实体完整性约束（主键约束）

<列名> <数据类型> [PRIMARY KEY]    -- CREATE 语句中
或
PEIMARY KEY(<列名>[,<列名>]···)    -- CREATE 语句结尾

1.2. 参照完整性约束（外键约束）

REFERENCES <表名><列名> [ON DELETE CASECODE|SET NULL]  -- 加在字段后面
FOREIGN KEY(<列名>) REFERCNCES <表名><列名> [ON DELETE CASECODE|SET NULL] -- 加在结尾（分号前）
-- 说明：
ON DELETE CASECODE  删除被参照关系的元组时，同时删除参照关系中的元组
ON DELETE SET NULL  删除被参照关系的元组时，同时将参照关系中元组所属字段置为null

1.3. 用户自定义完整性约束

关键字	含义	示例
NOT NULL	非空	C_NAME VARCHAR(300) NOT NULL
UNIQUE	唯一	C_BH_USER VARCHAR(300) UNIQUE
CHECK	校验	CHECK(C_AGE > 18 )

2.授权与销权

2.1. 授权(GRANT)

GRANT <权限>[,<权限>]··· 
ON <对象类型><对象名> 
TO <用户>[,<用户>]···
[WITH GRANT OPTION]   -- 获得该权限的用户还可以将该权限赋给其他用户

对象	对象类型	操作权限
属性列	TABLE	SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES(所有权限)
视图	VIEW	SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES(所有权限)
基本表	TABLE	SELECT、INSERT、UPDATE、DELETE、ALTER、INDEX、ALL PRIVILEGES(所有权限)
数据库	DATABASE	CREATETAB(建表权限)

说明：TO PUBLIC 将权限赋给全部用户

示例

GRANT ALL PRIVILEGES ON TABLE T_TABLE1, T_TABLE2 TO USER1, USER2;
GRANT INSERT ON TABLE T_TABLE TO USER1 WITH GRANT OPTION;
GRANT CREATETAB ON DATABASE DB_JR TO USER1;

2.2. 销权(REVOKE)

REVOKE <权限>[,<权限>]··· 
ON <对象类型><对象名> 
FROM <用户>[,<用户>]···
[RESTRICT|CASECODE]  -- RESTRICT 只收回指定用户权限(默认)  CASECODE 级联收回用户权限

示例

REVOKE ALL PRIVILEGES ON TABLE T_TABLE1, T_TABLE2 FROM USER1, USER2;
REVOKE SELECT ON TABLE T_TABLE FROM PUBLIC;
REVOKE UPDATE(Sno) ON TABLE T_TABLE FROM USER1 CASECODE;

八、触发器

1. 新建触发器

CREATE TRIGGER<触发器名> [{BEFORE|AFTER}]    -- 在执行处罚语句之前/之后激发触发器
	{[DELETE|INSERT|UPDATE OF [列名清单]]}   -- 触发器类型删除/新增/修改
	ON 表名
	[REFERINCING<临时视图名>]                 -- 执行临时视图别名，存放新值和旧值
	[WHEN<触发条件>]                          -- 触发条件必须包含临时视图名
	BEGIN
		<触发动作>
	END [触发器名]

示例

CREATE TRIGGER overdraft_trigger AFTER UPDATE ON t_acount
	REFERENCING NEW ROW AS NROW
	FOR EACH ROW                             -- 行级触发器
	WHEN NROW.n_balance < 0
	BEGIN ATOMIC                             -- ATOMIC指原子性即事务
		INSERT INTO ……;
		INSERT INTO ……;
		UPDATE t_acount SET ……
	END

CREATE TRIGGER reorder_trigger AFTER UPDATE of n_amount ON t_inventory
	REFERENCING OLD ROW AS OROW, NEW ROW AS NROW
	FOR EACH ROW                             -- 行级触发器
	WHEN NROW.c_level <= 50 and OROW.c_level > 0
	BEGIN
		INSERT INTO ……
	END

2. 修改触发器

ALTER TRIGGER <触发器名> [{BEFORE|AFTER}]    -- 在执行处罚语句之前/之后激发触发器
	{[DELETE|INSERT|UPDATE OF [列名清单]]}   -- 触发器类型删除/新增/修改
	ON 表名|视图名
	AS
	BEGIN
		要执行的SQL语句
	END

3. 删除触发器

DROP TRIGGER <触发器名>[,<触发器名>]···

九、嵌入式SQL

嵌入到其他语言的SQL

EXEC SQL <SQL语句>
或
EXEC SQL <SQL语句> END-EXEC

1. SQL通信区

SQL通信区即：SQLCA，向诸语言传递SQL语句执行的状态信息。

2. 主变量

DECLARE语句

说明主变量示例

EXEC SQL BEGIN DECLARE SECTION;               -- 说明主变量
	char Msno[4],Mcno[3],givensno[5];
	int Mgrade;
	char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

示例

EXEC SQL SELECT sname,age,sex
	INTO :Msno, :Mcno, :givensno
	FROM students
	WHERE sno = :Msno;

EXEC SQL INSERT
	INTO SC(Sno, Cno, Grade)
	VALUES(:Hsno, :Hcno, :Hgrade);

3. 游标

3.1. 定义游标

说明性语句，不立即执行。

EXEC SQL DELCLARE <游标名> CURSOR FOR
	<SELECT 语句>
END_EXEC

3.2. 打开游标

执行SELECT语句，游标处于活动状态，游标是个指针，此时指向结果第一行之前。

EXEC SQL OPEN <游标名> END_EXEC

3.3. 推进游标

游标推进一行，并把游标指向的行中值取出，送到共享变量中。

变量表即共享变量列表，例如：：Hsno, :Hcno, ···

EXEC SQL FETCH FROM <游标名> INTO <变量表> END_EXEC

3.4. 关闭游标

EXEC SQL CLOSE <游标名> END_EXEC

游标示例

略

十、存储过程

CREATE PROCEDURE 存储过程名(IN|OUT|IN OUT 参数1 数据类型, IN|OUT|IN OUT 参数2 数据类型···)
[AS]
BEGIN
	<SQL语句>
END

说明：

IN: 为默认值，表示该参数为输入型参数，在过程体中值一般不变。
OUT: 表示该参数为输入参数，可以作为存储过程的输出结果，供外部调用者使用。
IN OUT: 既可以作为输入参数，也可以作为输出参数。

十一、事务

BEGIN TRANSACTION            -- 开始事务
END TRANSACTION              -- 结束事务
COMMIT                       -- 提交事务
ROLLBACK                     -- 回滚事务

示例

BEGIN TRANSACTION 
	read(A);                          -- 读A账户余额
	A = A - x;
	IF(A < 0) THEN
		print("金额不足，不能转账");
		ROLLBACK;                     -- 撤销该事务 
	ELSE
		write(A);                     -- 写入账户A余额
		read(B);
		B = B + 1;
		write(B);
		COMMIT;                       -- 提交事务
	ENDIF;
END TRANSACTION

如果觉得文章对你有用，请随意赞赏

SQL

软考-数据库系统工程师-SQL章节

https://blog.haojianxiang.top/?p=65

作者

一枪一个小脑袋

发布于

2023-11-30

更新于

2023-12-12

许可协议

CC BY 4.0

软考-数据库系统工程师-SQL章节

一、创建表语句

1. 主键

2. 外键

二、修改表语句

三、删除表

四、创建删除索引

1. 创建索引

2. 删除索引

五、创建删除视图

1.创建视图

2.删除视图

3.视图操作

六、数据操作

1. 查询

2. 子查询

2.1. 谓词 ALL/ANY

2.2.分组查询

3. 更名/别名

4. 字符串操作（模糊查询）

5. 集合操作

七、数据库授权

1. 完整性约束

1.1. 实体完整性约束（主键约束）

1.2. 参照完整性约束（外键约束）

1.3. 用户自定义完整性约束

2.授权与销权

2.1. 授权(GRANT)

2.2. 销权(REVOKE)

八、触发器

1. 新建触发器

2. 修改触发器

3. 删除触发器

九、嵌入式SQL

1. SQL通信区

2. 主变量

3. 游标

3.1. 定义游标

3.2. 打开游标

3.3. 推进游标

3.4. 关闭游标

十、存储过程

十一、事务

作者

发布于

更新于

许可协议

评论