二下的数据管理技术感觉大半是 ChatGPT 帮忙写的，为了避免以后 SQL 一条写不出来的窘境，赶紧记录复习一下。

什么是 SQL

SQL（Structured Query Language）是一种用于管理关系型数据库的编程语言。它允许用户对数据库进行查询、插入、更新和删除操作，以及创建和修改表、视图、存储过程和触发器等数据库对象。

简而言之，SQL 允许我们完成对数据库的增删改查操作，并完成数据库的表操作。

表操作

表操作是数据库的基础，对表的增删改可以在给定的数据库中操作表而非表中的数据。

创建表 CREATE

创建表需要给出表名、字段名、字段类型与约束

CREATE TABLE Student
    (Sno CHAR(5) NOT NULL UNIQUE,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(1),
    Sage INT,
    Sdept VARCHAR(15),
    Primary key (Sno, Cno));
    FOREIGN KEY (column2) REFERENCES other_table(other_column)

其中比较常用的操作包括：

# 设定为非空不重复
Sname CHAR(20) NOT NULL UNIQUE,

# 设置为主键
Sno CHAR(5) primary key,
/
Sno CHAR(20) NOT NULL UNIQUE,
Primary key (Sno);

# 设置外键(引用他表主键)
Cno CHAR(5)
FOREIGN KEY (Cno) REFERENCES other_table(Cno)

删除表 DROP

# 删除整个表
DROP TABLE user;

修改表 ALTER

ALTER TABLE <表名>
    [ ADD <新列名> <数据类型> [ 完整性约束 ] ]
    [ DROP <完整性约束名>(列名) / <列名> ]
    [ MODIFY <列名> <数据类型> ]；

数据操作

插入数据 INSERT

主要有两种插入方式:

# 要求插入所有列对应数据
INSERT INTO table_name
    VALUES (value1,value2,value3,...);

# 只插入指定列数据
INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);

删除数据 DELETE

删除的条件语句可参考条件语句部分

DELETE FROM table_name
    WHERE condition;

修改数据 UPDATE

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

在 DELETE 和 UPDATE 语句中, 如果省略了 WHERE 语句, 则所有数据都会被删除!!!

条件语句 WHERE

条件语句为 SQL 提供了筛选的功能, 其中包含多种使用方法与语句.

条件表达式

SQL 提供了多种条件运算符, 和数学表达式基本相同

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

以下为一些常用实例:

# 筛选文本
SELECT * FROM Persons WHERE FirstName='Bush'
# 筛选数字
select last_name,department from employees where department_id <> 90;

逻辑表达式

一般用于连接多个条件表达式

&&    ||    !    and    or

模糊查询

主要用于实现具有某些共性的信息

# like 多用于匹配正则字段
where name like '%a%'; # 检索含有a的
where name like '刘%'; # 检索姓刘的
where name like '刘_'; #检索两个字姓刘的

# 当通配符被占用时, 使用ESCAPE标识
LIKE 'DB*_%i_ ' ESCAPE '*'

# between and 区间查询
where id between 100 and 120; # 检索id在100到120之间

# in 类似于ENUM, 筛选属于值表
where id in('IT_PROT','AD_VP','AD_PRES'); # 检索id属于值表的

# is null/is not null 是否为空
where commission_pct IS NOT NULL;

数据查询

数据查询是 SQL 的核心, 相应的内容也更多一些.

基础语句:

SELECT 属性列
    FROM 表
    WHERE 条件
    GROUP BY 属性
    ORDER BY 属性

单表查询

SELECT

A AS B: 别名
SELECT LOWER(A): 小写字母
SELECT DISTINCT B: 消除重复行(默认为 ALL 不消除),紧随 SELECT
*: 全部

集函数

COUNT,SUM, ANG, MAX, MIN
空值 NULL 不参与计算
当存在分组的时候, 针对分组计算, 没有分组的时候, 针对全部数据计算

GROUP BY

SELECT 子句的列名列表中只能出现分组属性和集函数

HAVING

只出现在 GROUP BY 的时候, 可以使用集函数

select sno from SC
        group by sno
        having count(*)>=3

ORDER BY:

默认升序, 出现在 SELECT 语句的最后
升序：ASC；降序：DESC

多表查询

自连接 JOIN

FROM 多个表, 通过 WHERE 控制连接

SELECT FIRST.Cno，SECOND.Cpno
    FROM Course AS FIRST，Course AS SECOND
    WHERE FIRST.Cpno = SECOND.Cno；

外连接(默认 OUTER JOIN)

MySQL 中支持 LEFT JOIN 和 RIGHT JOIN
左右内外判断方法: 指向谁, 谁不会被删除

# 查询所有学生的选课情况
SELECT Student.Sno，Sname，Ssex， Sage，Sdept，Cno，Grade
    FROM Student LEFT Join SC on Student.Sno = SC.Sno；

子查询:

多数情况可以插入到 WHERE, FROM, HAVING 中
相关子查询: 取外层查询中的元组判断是否满足内层查询条件
不相关子查询: 子查询得到的临时表/值表用于父查询

IN 子查询:

先得到查询顺序, 将第一步查询作为第二步查询的子条件
确切知道内层查询返回单值时, 可用比较运算符

SELECT Sno，Sname，Sdept
    FROM Student
    WHERE Sdept IN
        (SELECT Sdept
            FROM Student
            WHERE Sname = '刘晨')；

有 ANY/ALL 子查询
any 为存在, all 为任意

EXIST

主要有以下应用:

全选: 不存在一门课程没有被选择

select S,SNAME -- 在 S 表里选 S，SNAME
from S
where not exists -- 不存在
    (select * -- 课程
    from C
       where not exists -- 没有
        (select * -- 被该生选择的课程
        from SC
        where SC.S=S.S and SC.C=C.C)) -- 相关查询，三个表进行连接

至少选了一个: 存在一门课有被选择

select S,SNAME -- 在 S 表里选 S，SNAME
from S
where exists -- 存在
    (select * -- 课程
    from C
    where exists -- 有
        (select * -- 被该生选择的课程
        from SC
        where SC.S=S.S and SC.C=C.C)) -- 相关查询，三个表进行连接

没有全选: 存在一门课没有被选择

select S,SNAME -- 在 S 表里选 S，SNAME
from S
where exists -- 存在
    (select * -- 课程
    from C
    where not exists -- 没有
        (select * -- 被该生选择的课程
        from SC
        where SC.S=S.S and SC.C=C.C)) -- 相关查询，三个表进行连接

全部没选: 不存在一门课被选择

select S,SNAME -- 在 S 表里选 S，SNAME
from S
where not exists -- 不存在
    (select * -- 课程
    from C
    where exists -- 有
        (select * -- 被该生选择的课程
        from SC
        where SC.S=S.S and SC.C=C.C)) -- 相关查询，三个表进行连接

选择了全部的 A: 全选: 非+非
包含了 A 选择的 B: 在全选基础上, 加条件

SELECT DISTINCT a.name
    FROM Actor a, Play p, Role r
    WHERE p.author = ‘Great Writer’ AND p.play_id = r.play_id AND r.actor_id = a.actor_id
    AND a.actor_id NOT IN
        (SELECT r2.actor_id
            FROM Role r2, Play p2
            WHERE p2.author = ‘Nobody’ AND p2.play_id = r2.play_id)

Select name, title
    from play p inner join
        (actor a left outer join role r on a.actor_id= r.actor_id)
        on p.play_id=r.play_id

其他

视图

CREATE VIEW Student(Sno，Sname，Ssex，Sage，Sdept)
    AS
    SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept
        FROM SX，SY
        WHERE SX.Sno = SY.Sno；
    WITH CHECK OPTION

存储过程

触发器

后续再做更新, 大部分时候根据模板实现即可

参考资料

SQL WHERE 子句 | 菜鸟教程 (runoob.com)

(29 条消息) SQL 语句之条件查询–WHERE(where)sql where远方的旅行者的博客-CSDN 博客