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

-- database
CREATE DATABASE db_name;
ALTER DATABASE db_name RENAME TO new_db_name;
DROP DATABASE db_name;

Table

-- 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 类型

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 后面指定的表）必须已经存在。
• 被引用的字段必须是父表的主键或具有唯一性约束。
• 数据类型必须兼容，即外键字段和被引用字段的类型必须相同或可转换。

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 中不支持，

'''
管理约束
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）：将关系表按照指定列的取值顺序组织元组数据的数据结构，加快查询，占用额外存储空间、开销较大

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

-- insert
INSERT INTO  <表名|视图名>[<列名表>]  VALUES （列值表）;
INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程',
'liuyin@163.com')
-- update
UPDATE
    <表名|视图名>
SET
    <列名1>=<表达式1> [，<列名2>=<表达式2>...]
[WHERE <条件表达式>]；

UPDATE
    Student
SET
    Email='zhaodong@163.com'
WHERE
    StudentName='赵东';
-- delete
DELETE FROM
    <表名|视图名>
[WHERE <条件表达式>]

DELETE FROM
    Student
WHERE
    StudentName='赵东';

DQL

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;

多表关联查询

子查询

SELECT  TeacherID, TeacherName, TeacherTitle
FROM  Teacher
WHERE  CollegeID  IN
        (SELECT  CollegeID
         FROM  College
         WHERE  CollegeName='计算机学院');

连接查询

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;