数据库（一 ）SQL基本语言

一、SQL的基本语言类型

按功能，将数据库语言划分为以下四类。

1. DDL（Data Definition Language）：数据定义语言，用于定义、撤销和修改关系模式，如表、视图、索引等。
2. QL（Query Language）：数据查询语言，用于查询数据库中的数据。
3. DML（Data Manipulation Language）：数据操纵语言，用于数据访问权限的控制。
4. DCL（Data Control Language）：数据控制语言，用于数据访问权限的控制。

二、数据定义语言

关系是关系数据库中的基本构成单位，也称为表。在SQL中，分为以下两种。
5. 基表。实表，数据显示存储的位置。
6. 视图。虚表，数据仍在基表中，基表数据发生变化，视图数据也会变化。普通视图可根据其定义由其他基表导出。

2.1 创建基表

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

eg：

-- 创建学生表
CREATE TABLE STUDENT 
	(SNO CHAR(10),
	SNAME VARCHAR(10),
	SEX CHAR(2),
	PRIMARY KEY(SNO)
)
-- 创建课程表
CREATE TABLE COURSE 
	(CNO CHAR(6),
	CNAME VARCHAR(20),
	CREDIT INT,
	PRIMARY KEY(CNO)
	)
-- 创建学生学分表
CREATE TABLE STUDENTSCORE
	(SNO CHAR(10) NOT NULL,
	CNO CHAR(6) NOT NULL, 
	NAME VARCHAR(10) NOT NULL,
	SCORE DEC(2, 1) DEFAULT NULL,
	PRIMARY KEY (SNO, CNO),
	FOREIGN KEY(SNO)
		REFERENCES STUDENT(SNO)
		ON DELETE CASCADE,
	FOREIGN KEY(CNO)
		REFERENCES COURSE(CNO)
		ON DELETE RESTRICT
)

ON DELETE 是引用完整性的任选项，
ON DELETE CASCADE 表示当主表删除了某一主键，基表中引用此键的行也随之删除。
ON DELETE RESTRICT 表示凡是被基表引用的主键，不得被删除。

属性级完整性约束条件：列属性是否为空，默认值；
表级完整性约束条件：引用完整性及唯一性检查

一般完整性约束有：

约束	表示	备注
主键约束	PRIMARY KEY	定义该属性列为主键
唯一性约束	UNIQUE	不能取相同值但允许多个空值
非空约束	NOT NULL	不能为空
引用（参照）完整性约束

一般 SQL 的数据类型有

数据类型	符号	备注
整数	INT	32位
短整数	SMALLINT	16位
十进制	DEC(m, n)	m为小数点前，n为小数点后
浮点数	FLOAT	双精度浮点数， 64 位
定长字符串	CHAR(n)	长度固定为 n, 少则补空格，多则报错
变长字符串	VARCHAR(n)	按实际输入字符存储，多则报错
日期	DATE	yyyymmdd，年月日
时间	TIME	hhmmss, 时分秒
时长	TIMESTAMP	yyyymmddhhmmssnnnnnn，nnnnnn为微秒

2.2 基表模式修改

-- 1.增加列
ALTER TABLE <表名>
	ADD <属性列> <类型>
	
-- 2.删除表，若要删除列时，应当定义另一新表，新表去掉该列，删除原表，把新表名替换为原表名
DROP TABLE <表名>

-- 3.补充定义主键
ALTER TABLE <表名>
 	ADD PRIMARY KEY (<属性列表>)
 	
-- 4.撤销主键定义
ALTER TABLE <表名>
	DROP PRIMARY KEY

-- 5.补充定义外键，
-- 		RESTRICT：凡被基表引用的主键，不能被删除
-- 		CASCADE: 主键删除，基表引用此主键的元组也被删除
-- 		SET NULL：该列应无NOT NULL说明
ALTER TABLE <表名 1>
	ADD AFOREIGN KEY [<外键名>](<属性列表>)
	REFERENCES <表名 2>
	ON DELETE {RESTRICT | CASCADE | SET NULL}

-- 6.撤销外键定义
ALTER TABLE <表名>
	DROP <外键名>

-- 7.定义和撤销别名
CREATE SYNONYM <标识符>
	FOR <表名>|<视图名> 
DROP SYNONYM <标识符>

2.3 索引的建立与撤销

-- 1. 建立索引
--		UNIQUE:单独索引
--		CLUSTER:聚簇索引
--		次序：DESC，ASC
CREATE [UNIQUE]|[CLUSTER] INDEX <索引名> ON <表名>(<属性列>[次序],...)
CREATE UNIQUE INDEX STU_IDX ON STUDENT(SNO ASC, SCORE DESC)

-- 2. 撤销索引
DROP INDEX <表名>.<索引名>

三、数据查询语言

3.1 基本格式

SELECT [ALL | DISTINCT] <目标表达式> AS <别名>
FROM <表名>
[WHERE <条件表达式>]
[GROUP BY <属性> HAVING <条件>]
[ORDER BY <属性> DESC | ASC]

3.2 复杂查询的例子

（待补充）

四、数据操纵语言

4.1 插入数据

INSERT INTO <表名>[属性列 1][属性列 2]
VALUES (<常量 1>, <常量 2>)

4.2 修改数据

UPDATE <表名>
SET <属性列名> = <表达式>
[WHERE <条件>]

4.3 删除数据

DELETE FROM <表名>
WHERE <条件>

五、数据控制语言

数据控制包括事务管理和数据保护，即数据库的恢复和并发控制，数据库的安全性和完整性控制。
不同类型的操作对象具有不同的操作权限，权限表如下：

对象	类型	权限
属性列	TABLE	SELECT, INSERT, UPDATE, DELETE, ALL PRIVIEGES
视图	TABLE	SELECT, INSERT, UPDATE, DELETE, ALL PRIVIEGES
基表	TABLE	SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, ALL PRIVIEGES
数据库	DATABASE	CREATETAB建立表的权限，可由 DBA 授予普通用户

访问控制在数据库之间相互独立，一个用户在一个数据库获得的访问权限不能用于其他数据库。
数据库按用户访问权力的大小，分为三种：
（1）一般数据库用户
（2）具有支配部分数据库资源特权的用户
（3）具有DBA特权的用户

5.1 授权

-- GRANT 表示授权
-- ON 后面指定对象类型和对象名
-- TO 接受权限的用户，可以是一个 / 多个用户，也可以是 PUBLIC 用户
-- WITH GRANT OPTION 表示用户可以将权限授予其他用户
GRANT <权限> ON <对象类型><对象名>
TO <用户> [WITH GRANT OPTION]

Eg：

GRANT SELECT/UPDATE(SNO)/INSERT
ON TABLE SC
TO U1 
WITH GRANT OPTION

5.2 收回权限

REVOKE <权限>
ON <对象类型><对象名>
FROM <用户>

Eg:

REVOKE SELECT 
ON SC
TO U1