[NOTE] SQL语言自用备查手册

---

创建

创建数据库

CREATE DATABASE <NAME>;

创建模式

CREATE SCHEMA <NAME> AUTHORIZATION <USERNAME>

不指定隐含为username.

删除模式

DROP SCHEMA <NAME><CASCADE/RESTRICT>

CASCADE:级联,即一并删除下属所有的表
RESTRICT:如果下属有表,则拒绝删除.

创建表(关系)

CREATE TABLE <NAME>
(
column_name1 data_type(size),<UNIQUE>/<PRIMARY KEY>/<NOT NULL>
column_name2 data_type(size),
column_name3 data_type(size),
....
);

data_type:

• int
• varchar=>string
• …

表间同步列的完整性约束:

FOREIGN KEY <NAME1> REFERENCES <TABLENAME><NAME2>

外码name1的约束参照NAME2.

从已有表中选取列组建新表

INSERT INTO table2
(column_name(s))--这里是table2中这些列的名字.
SELECT column_name(s)
FROM table1;--从table1中选取列并插入表2中.

SELECT INTO对MySQL不支持.
select into from 和 insert into select 都是用来复制表
两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。
See: https://www.runoob.com/sql/sql-insert-into-select.html

修改表设置

ALTER TABLE <NAME> ADD <NAME> <constraint>--加一列
ALTER TABLE <NAME> ALTER COLUMN <NAME> <NEWTYPE>--修改列的数据类型
ALTER TABLE <NAME> ADD <RESTRICT>--添加列的约束

录入数据

常规方法

INSERT INTO <table_name>
VALUES (value1,value2,value3,...);

这是不留空的录入方法.

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

这是可能存在空单元格的方法.需要指明录入的列名.

批量插入

INSERT INTO <TABLENAME>(COLUMNSNAME) 
 VALUES
(VALUES1),
(VALUES2),
(VALUES3);

这样可以避免程序和数据库建立多次连接，从而增加服务器负荷。

查询

常规操作

SELECT <DISTINCT>column_name, <aggregate_function(column_name)>#累加函数,比如SUM
FROM table_name;
WHERE <condition> AND <condition># See P112
<GROUP BY column_name>#根据column_name分类统计SUM(另一个column_name).
<ORDER BY...>#排序

从表中选择符合条件的行的某一列或者几列,返回结果.

ORDER by

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

IN 和EXIST

SELECT "栏位名"
FROM "表格名"
WHERE "栏位名" IN ('值一', '值二', ...);

而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：
select name from student where sex = ‘m’ and mark in (select 1,2,3 from grade where …)
，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。

Like

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SQL LIKE 操作符语法

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

SELECT * FROM Persons
WHERE City LIKE 'N%'

(Left)JOIN

对应左连接…

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

BETWEEN

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SQL BETWEEN 语法

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

如需使用上面的例子显示范围之外的人，请使用 NOT 操作符：

多表联合查询(非UNION)

为了实现如下逻辑:

SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO=(SELECT PNO FROM P WHERE COLOR='RED')--(3)

正规写法如下:

SELECT SNO FROM SPJ,P WHERE JNO='J1' AND SPJ.PNO=P.PNO AND COLOR='RED';

避免了PNO的查询结果序列中有元素在SPJ中查询不到的问题.
这里的SPJ.PNO=P.PNO是为了对齐(连接?)查询结果,除去另一个表没有的.

UNION

SQL UNION 操作符合并两个或多个 SELECT 语句的结果.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

聚集函数

count

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
SELECT COUNT((DISTINCT)column_name) FROM table_name
COUNT(*) 函数返回表中的记录数

group by

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name)#Count,Sum,...
FROM table_name
WHERE column_name operator value
GROUP BY column_name

修改

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

删除

DELETE FROM table_name
WHERE some_column=some_value;

视图

建立视图

CREATE VIEW <VIEWNAME> [<COLUMNS>]<OR TABLENAME>
AS <SELECT...>
<WITH CHECK OPTION>

建立一个视图,视图展示符合SELECT语句的列名.
with...:表示视图下修改视图时保证更新的行满徐视图中的SELECT条件
(防止越权修改?)

权限管理

授予权限

GRANT <OPERATION><COLUMN> ON TABLE <NAME> TO <USRNAME>/PUBLIC 
(WITH GRANT OPERATION)

授予< USR > 操作< NAME >表的< COLUMN >列< OPERATION >操作的
权限,WITH…是授予该用户向其他用户授权的权限.

• SELECT
• UPDATE
• INSERT

收回权限

REVOKE <OPERATION><COLUMN> ON TABLE <NAME> FROM <USRNAME>
(CASCADE)

从< USR >收回操作权限
CASCADE:级联,一并收回该用户授予的权限.

角色管理

角色是权限的集合,是一种权限管理的模板.

创建角色

CREATE ROLE <NAME>

管理角色

授予权限

GRANT <PERMISSION> ON <OBJNAME> TO <NAME>

权限在角色中继承

GRANT <NAME1>,... TO <NAME2>,...
[WITH ADMIN OPTION]

有ADMIN则可以将这种权限继续授予角色.

收回权限

REVOKE <PERMISSION> ON <OBJ_NAME>
FROM<NAME>

授予/收回用户

即套用模板.

GRANT <ROLE_NAME> TO <NAME>

REVOKE <ROLE_NAME> TO <NAME>