ChⅢ-数据库操作SQL语言
SQL 语句
- 数据定义语言（DDL）：创建修改删除数据库对象；create/drop/alter database/table/index
- 数据操纵语言（DML）：增删改数据；insert/update/delete
- 数据查询语言（DQL）：数据查询；
- 数据控制语言（DCL）：数据库对象访问控制；grant/deny/revoke
- 事物处理语言（TPL）：事物处理；begin transaction/commit/rollback
- 游标控制语言（CCL）：游标操作；declare cursor/fetch into/close curso
DDL
数据定义语言（Data Definition Language，DDL）是 SQL 语言中用于创建、修改或删除数据库对象的语句。
Database
1
2
3
4
-- database
CREATE DATABASE db_name;
ALTER DATABASE db_name RENAME TO new_db_name;
DROP DATABASE db_name;Table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- table
CREATE TABLE table_name (
Sname varchar(10) NOT NULL,
Age int NOT NULL,
Sid char(10) PRIMARY KEY,
);
'''
完整性约束包括：
PRIMARY KEY 主键
NOT NULL 非空
NULL 空值
UNIQUE 值唯一
CHECK 有效性检查
DEFAULT 缺省
'''
'''假定创建一个表，City字段只能取值上海、北京，age 默认为 10'''
CREATE TABLE TEST(
City varchar(10) CHECK(City IN('上海','北京')),
Age int NOT NULL DEFAULT 10
);
ALTER TABLE <表名> ADD <新列名称><数据类型>|[完整性约束]
ALTER TABLE<表名> DROP COLUMN <列名>；
ALTER TABLE<表名> DROP CONSTRAINT<完整性约束名>；
ALTER TABLE <表名> RENAME TO <新表名>；
ALTER TABLE <表名> RENAME <原列名> TO <新列名>；
ALTER TABLE <表名> ALTER COLUMN <列名> TYPE<新的数据类型>；
DROP TABLE <表名>;Primary/Foreign Key Constraint
Primary Key Constraint syntax
- 定义单列主键
Age int PRIMARY KEY - 定义多列主键
CONSTRAINT <constraint_name> PRIMARY KEY <col1,col2...> - 定义代理键时，注意主键应为 serial 类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE TEST(
City varchar(10) PRIMARY KEY,
Age int PRIMARY KEY
);
CREATE TABLE TEST(
Name varchar(10),
Age int,
CONSTRAINT PK_TEST PRIMARY KEY(Name,Age)
);
CREATE TABLE TEST(
Idx serial PRIMARY KEY,
Name varchar(10),
Age int
);Foreign Key Constraint
为了成功创建外键，以下条件必须满足：
- 引用的表（在 REFERENCES 后面指定的表）必须已经存在。
- 被引用的字段必须是父表的主键或具有唯一性约束。
- 数据类型必须兼容，即外键字段和被引用字段的类型必须相同或可转换。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE parent_table (
id SERIAL PRIMARY KEY,
name VARCHAR(10)
)
CREATE TABLE child_table (
id SERIAL PRIMARY KEY,
name VARCHAR(10),
parent_id INTEGER,
FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);
-- ON DELETE CASCADE
ALTER TABLE
child_table
ADD CONSTRAINT
fk_parent_id FOREIGN KEY (parent_id)
REFERENCES parent_table(id)
ON DELETE CASCADE;在 SQL 中，特别是像 MySQL 和 PostgreSQL 这样的关系型数据库管理系统中，外键不仅提供了数据完整性，还可以配置不同的行为，例如 ON DELETE 和 ON UPDATE 规则，来决定当父表中的记录被删除或更新时，子表中的关联记录应该如何处理，常见的 ON DELETE 和 ON UPDATE 规则包括：
- RESTRICT 或 NO ACTION
这是默认行为。如果尝试删除或更新主表中的记录，而子表中存在依赖这个记录的外键，那么操作会被阻止。这确保了参照完整性，防止了意外的数据丢失。 - CASCADE:
当主表中的记录被删除或更新时，所有关联的子表记录也将被相应地删除或更新。这样，两个表之间的数据保持一致。 - SET NULL:
如果主表中的记录被删除或更新，子表中对应的外键字段将被设置为 NULL。这要求外键字段在定义时允许为 NULL。 - SET DEFAULT:
设置外键在主键被删除或更新时，将子表中的外键字段设为其默认值。
请注意，SET DEFAULT 在 MySQL 中不支持，
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
'''
管理约束
constraint_type指明了约束的类型，比如FOREIGN KEY、UNIQUE、PRIMARY KEY等。
'''
ALTER TABLE
table_name
ADD CONSTRAINT
constraint_name constraint_type(column_name, ...);
ALTER TABLE
child_table
ADD CONSTRAINT IF NOT EXISTS
fk_parent_id FOREIGN KEY (parent_id)
REFERENCES
parent_table(id)
ON DELETE CASCADE;
ALTER TABLE child_table DROP CONSTRAINT fk_parent_id;Index
索引（index）：将关系表按照指定列的取值顺序组织元组数据的数据结构，加快查询，占用额外存储空间、开销较大
1
2
3
CREATE INDEX Birthday_Idx ON STUDENT(Birthday);
ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;
DROP INDEX bday_idx;DML
DML：数据操作语言，用于对数据进行 CRUD(Create, Retrieve, Update, Delete)
Ref: [[DataBase01#DML]]
INSERT, UPDATE, DELETE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- insert
INSERT INTO <表名|视图名>[<列名表>] VALUES （列值表）;
INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程',
'[email protected]')
-- update
UPDATE
<表名|视图名>
SET
<列名1>=<表达式1> [，<列名2>=<表达式2>...]
[WHERE <条件表达式>]；
UPDATE
Student
SET
Email='[email protected]'
WHERE
StudentName='赵东';
-- delete
DELETE FROM
<表名|视图名>
[WHERE <条件表达式>]
DELETE FROM
Student
WHERE
StudentName='赵东';DQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SELECT [ALL|DISTINCT] <目标列>[，<目标列>…]
[ INTO <新表> ]
FROM <表名|视图名>[，<表名|视图名>…]
[ WHERE <条件表达式> ]
[ GROUP BY <列名> [HAVING <条件表达式> ]]
[ ORDER BY <列名> [ ASC | DESC ] ];
-- BETWEEN AND 限制列值范围
SELECT *
FROM STUDENT
WHERE BirthDay BETWEEN ‘2000-01-01’ AND ‘2000-12-30’;
-- LIKE 通配符'_'代表一个字符'%'代表一个或多个字符
SELECT *
FROM STUDENT
WHERE Email LIKE '%@163.com';
-- AND\OR\NOT 逻辑运算符
SELECT StudentID, StudentName, StudentGender, Major
FROM STUDENT
WHERE Major='软件工程' AND StudentGender='男';
-- IN 限定范围
SELECT StudentID, StudentName, StudentGender, Major
FROM STUDENT
WHERE Major IN ('CS','SE');
-- ORDER BY <> ASC/DESC 默认升序ASC
-- 多个列排序时，只有当前列相同时才会比较下一列
SELECT *
FROM STUDENT
ORDER BY Birthday DESC , StudentName ASC;
-- 内置函数
SELECT COUNT（*） AS 学生人数
FROM Student;
SELECT Min（Birthday） AS 最大年龄，Max（Birthday） AS 最小年龄
FROM Student;
-- GROUP BY <> HAVING
-- 专业统计STUDENT表中男生人数，但限定只显示人数大于2的人数
SELECT Major AS 专业, COUNT（StudentID） AS 学生人数
FROM Student
WHERE StudentGender=’男’
GROUP BY Major
HAVING COUNT(*)>2;多表关联查询
子查询
1
2
3
4
5
6
SELECT TeacherID, TeacherName, TeacherTitle
FROM Teacher
WHERE CollegeID IN
(SELECT CollegeID
FROM College
WHERE CollegeName='计算机学院');连接查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SELECT
B.CollegeName AS 学院名称, A.TeacherID AS 编号, A.TeacherName AS 姓名, A.TeacherGender AS 性别, A. TeacherTitle AS 职称
FROM
Teacher AS A，College AS B
WHERE
A.CollegeID=B.CollegeID
ORDER BY
B.CollegeName, A.TeacherID;
-- JOIN ON 内连接
SELECT
B.CollegeName AS 学院名称, A.TeacherID AS 编号, A.TeacherName AS 姓名, A.TeacherGender AS 性别, A. TeacherTitle AS 职称
FROM
TEACHER AS A JOIN COLLEGE AS B
ON
A.CollegeID=B.CollegeID
ORDER BY
B.CollegeName, A.TeacherID;
-- LEFT JOIN/RIGHT JOIN/FULL JOIN 外连接
SELECT
C.CourseName AS 课程名称, T.TeacherName AS 教师,COUNT (R.CoursePlanID) AS 选课人数
FROM
COURSE AS C
JOIN
PLAN AS P
ON C.CourseID=P.CourseID
JOIN
TEACHER AS T
ON P.TeacherID=T.TeacherID
LEFT JOIN
REGISTER AS R
ON P.CoursePlanID=R.CoursePlanID
GROUP BY
C.CourseName, T.TeacherName;ChⅢ-数据库操作SQL语言