SQL

SQL语句

结构化查询语言（Structured Query Language）简称SQL，这是一种特殊的语言，它专门用于数据库的操作。每一种数据库都支持SQL，但是他们之间会存在一些细微的差异，因此不同的数据库都存在自己的“方言”。

SQL语句不区分大小写（关键字推荐使用大写），它支持多行，并且需要使用;进行结尾！

SQL也支持注释，通过使用--或是#来编写注释内容，也可以使用/*来进行多行注释。

我们要学习的就是以下四种类型的SQL语言：

• 数据查询语言（Data Query Language, DQL）基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。
• 数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令。
• 数据库定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。
• DCL（Data Control Language）是数据库控制语言。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。

我们平时所说的CRUD其实就是增删改查（Create/Retrieve/Update/Delete）

数据库定义语言（DDL）

数据库操作

我们可以通过create database来创建一个数据库：

create database 数据库名

为了能够支持中文，我们在创建时可以设定编码格式：

CREATE DATABASE IF NOT EXISTS study4 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

如果我们创建错误了，我们可以将此数据库删除，通过使用drop database来删除一个数据库：

drop database 数据库名

创建表

数据库创建完成后，我们一般通过create table语句来创建一张表：

create table 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件]);

SQL数据类型

以下的数据类型用于字符串存储：

• char(n)可以存储任意字符串，但是是固定长度为n，如果插入的长度小于定义长度时，则用空格填充。
• varchar(n)也可以存储任意数量字符串，长度不固定，但不能超过n，不会用空格填充。

以下数据类型用于存储数字：

• smallint用于存储小的整数，范围在 (-32768，32767)
• int用于存储一般的整数，范围在 (-2147483648，2147483647)
• bigint用于存储大型整数，范围在 (-9,223,372,036,854,775,808，9,223,372,036,854,775,807)
• float用于存储单精度小数
• double用于存储双精度的小数

以下数据类型用于存储时间：

• date存储日期
• time存储时间
• year存储年份
• datetime用于混合存储日期+时间

列级约束条件

列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 check （MySQL不支持）、默认default 、非空/空值 not null/ null

表级约束条件

表级约束有四种：主键、外键、唯一、检查

现在我们通过SQL语句来创建我们之前提到的三张表。

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

CREATE TABLE `study`.`teach`  (
  `tid` int NOT NULL,
  `sid` int NOT NULL,
  CONSTRAINT `f_tid` FOREIGN KEY (`tid`) REFERENCES `study`.`teacher` (`tid`),
  CONSTRAINT `f_sid` FOREIGN KEY (`sid`) REFERENCES `study`.`student` (`sid`)
);

修改表

如果我们想修改表结构，我们可以通过alter table来进行修改：

ALTER TABLE 表名[ADD 新列名 数据类型[列级约束条件]]
                             [DROP COLUMN 列名[restrict|cascade]]
                             [ALTER COLUMN 列名 新数据类型]

我们可以通过ADD来添加一个新的列，通过DROP来删除一个列，不过我们可以添加restrict或cascade，默认是restrict，表示如果此列作为其他表的约束或视图引用到此列时，将无法删除，而cascade会强制连带引用此列的约束、视图一起删除。还可以通过ALTER来修改此列的属性。

删除表

我们可以通过drop table来删除一个表：

DROP TABLE 表名[restrict|cascade]

其中restrict和cascade上面的效果一致。

---

数据库操纵语言（DML）

前面我们已经学习了如何使用SQL语句来创建、修改、删除数据库以及表，而如何向数据库中插入、删除、更新数据，将是本版块讨论的重点。

插入数据

通过使用insert into语句来向数据库中插入一条数据（一条记录）：

INSERT INTO 表名 VALUES(值1, 值2, 值3)

如果插入的数据与列一一对应，那么可以省略列名，但是如果希望向指定列上插入数据，就需要给出列名：

INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2)

我们也可以一次性向数据库中插入多条数据：

INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2)

我们来试试看向我们刚刚创建的表中添加三条数据。

修改数据

我们可以通过update语句来更新表中的数据：

UPDATE 表名 SET 列名=值,... WHERE 条件

注意，SQL语句中的等于判断是=

警告：如果忘记添加WHERE字句来限定条件，将使得整个表中此列的所有数据都被修改！

删除数据

我们可以通过使用delete来删除表中的数据：

DELETE FROM 表名

通过这种方式，将删除表中全部数据，我们也可以使用where来添加条件，只删除指定的数据：

DELETE FROM 表名 WHERE 条件

---

数据库查询语言（DQL）

数据库的查询是我们整个数据库学习中的重点内容，面对数据库中庞大的数据，该如何去寻找我们想要的数据，就是我们主要讨论的问题。

单表查询

单表查询是最简单的一种查询，我们只需要在一张表中去查找数据即可，通过使用select语句来进行单表查询：

-- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名
-- 会以别名显示此列
SELECT 列名 别名 FROM 表名
-- 查询所有的列数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名

我们也可以添加where字句来限定查询目标：

SELECT * FROM 表名 WHERE 条件

常用查询条件

• 一般的比较运算符，包括=、>、<、>=、<=、!=等。
• 是否在集合中：in、not in
• 字符模糊匹配：like，not like
• 多重条件连接查询：and、or、not

我们来尝试使用一下上面这几种条件。

排序查询

我们可以通过order by来将查询结果进行排序：

SELECT * FROM 表名 WHERE 条件 ORDER BY 列名 ASC|DESC

使用ASC表示升序排序，使用DESC表示降序排序，默认为升序。

我们也可以可以同时添加多个排序：

SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC

这样会先按照列名1进行排序，每组列名1相同的数据再按照列名2排序。

聚集函数

聚集函数一般用作统计，包括：

• count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
• count([distinct]列名)统计某列的值总和
• sum([distinct]列名)求一列的和（注意必须是数字类型的）
• avg([distinct]列名)求一列的平均值（注意必须是数字类型）
• max([distinct]列名)求一列的最大值
• min([distinct]列名)求一列的最小值

一般聚集函数是这样使用的：

SELECT count(distinct 列名) FROM 表名 WHERE 条件 

分组和分页查询

通过使用group by来对查询结果进行分组，它需要结合聚合函数一起使用：

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名

我们还可以添加having来限制分组条件：

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件

我们可以通过limit来限制查询的数量，只取前n个结果：

SELECT * FROM 表名 LIMIT 数量

我们也可以进行分页：

SELECT * FROM 表名 LIMIT 起始位置,数量

多表查询

多表查询是同时查询的两个或两个以上的表，多表查询会提通过连接转换为单表查询。

SELECT * FROM 表1, 表2

直接这样查询会得到两张表的笛卡尔积，也就是每一项数据和另一张表的每一项数据都结合一次，会产生庞大的数据。

SELECT * FROM 表1, 表2 WHERE 条件

这样，只会从笛卡尔积的结果中得到满足条件的数据。

注意：如果两个表中都带有此属性吗，需要添加表名前缀来指明是哪一个表的数据。

自身连接查询

自身连接，就是将表本身和表进行笛卡尔积计算，得到结果，但是由于表名相同，因此要先起一个别名：

SELECT * FROM 表名 别名1, 表名 别名2

其实自身连接查询和前面的是一样的，只是连接对象变成自己和自己了。

外连接查询

外连接就是专门用于联合查询情景的，比如现在有一个存储所有用户的表，还有一张用户详细信息的表，我希望将这两张表结合到一起来查看完整的数据，我们就可以通过使用外连接来进行查询，外连接有三种方式：

• 通过使用inner join进行内连接，只会返回两个表满足条件的交集部分：

• 通过使用left join进行左连接，不仅会返回两个表满足条件的交集部分，也会返回左边表中的全部数据，而在右表中缺失的数据会使用null来代替（右连接right join同理，只是反过来而已，这里就不再介绍了）：

嵌套查询

我们可以将查询的结果作为另一个查询的条件，比如：

SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件)

我们来再次尝试编写一下在最开始我们查找某教师所有学生的SQL语句。

这部分需要多加练习, 不然容易绕.

---

数据库控制语言（DCL）

庞大的数据库不可能由一个人来管理，我们需要更多的用户来一起管理整个数据库。

创建用户

我们可以通过create user来创建用户：

CREATE USER 用户名 identified by 密码;

也可以不带密码：

CREATE USER 用户名;

我们可以通过@来限制用户登录的登录IP地址，%表示匹配所有的IP地址，默认使用的就是任意IP地址。

通过root用户来创建

@localhost 表示它只能在这个ip地址上进行登陆

%表示可以在任意的ip地址进行登陆.

登陆用户

首先需要添加一个环境变量，然后我们通过cmd去登陆mysql：

login -u 用户名 -p

输入密码后即可登陆此用户，我们输入以下命令来看看能否访问所有数据库：

show databases;

我们发现，虽然此用户能够成功登录，但是并不能查看完整的数据库列表，这是因为此用户还没有权限！

用户授权

我们可以通过使用grant来为一个数据库用户进行授权：

grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option]

其中all代表授予所有权限，当数据库和表为*，代表为所有的数据库和表都授权。如果在最后添加了with grant option，那么被授权的用户还能将已获得的授权继续授权给其他用户。

我们可以使用revoke来收回一个权限：

revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户

要记得保存, 重新登陆test用户

---

视图

视图本质就是一个查询的结果，不过我们每次都可以通过打开视图来按照我们想要的样子查看数据。既然视图本质就是一个查询的结果，那么它本身就是一个虚表，并不是真实存在的，数据实际上还是存放在原来的表中。

我们可以通过create view来创建视图;

CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];

WITH CHECK OPTION是指当创建后，如果更新视图中的数据，是否要满足子查询中的条件表达式，不满足将无法插入，创建后，我们就可以使用select语句来直接查询视图上的数据了，因此，还能在视图的基础上，导出其他的视图。

1. 若视图是由两个以上基本表导出的，则此视图不允许更新。
2. 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
3. 若视图的字段来自集函数，则此视图不允许更新。
4. 若视图定义中含有GROUP BY子句，则此视图不允许更新。
5. 若视图定义中含有DISTINCT短语，则此视图不允许更新。
6. 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC： CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); 　　导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。
7. 一个不允许更新的视图上定义的视图也不允许更新

通过drop来删除一个视图：

drop view apptest

索引

在数据量变得非常庞大时，通过创建索引，能够大大提高我们的查询效率，就像Hash表一样，它能够快速地定位元素存放的位置，我们可以通过下面的命令创建索引：

-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 查看表中的索引
show INDEX FROM student

我们也可以通过下面的命令删除一个索引：

drop index 索引名称 on 表名

虽然添加索引后会使得查询效率更高，但是我们不能过度使用索引，索引为我们带来高速查询效率的同时，也会在数据更新时产生额外建立索引的开销，同时也会占用磁盘资源。

---

触发器

触发器就像其名字一样，在某种条件下会自动触发，在select/update/delete时，会自动执行我们预先设定的内容，触发器通常用于检查内容的安全性，相比直接添加约束，触发器显得更加灵活。

触发器所依附的表称为基本表，当触发器表上发生select/update/delete等操作时，会自动生成两个临时的表（new表和old表，只能由触发器使用）

比如在insert操作时，新的内容会被插入到new表中；在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中。

CREATE TRIGGER 触发器名称 [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON 表名/视图名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno

FOR EACH ROW表示针对每一行都会生效，无论哪行进行指定操作都会执行触发器！

通过下面的命令来查看触发器：

SHOW TRIGGERS

如果不需要，我们就可以删除此触发器：

DROP TRIGGER 触发器名称

---

事务

当我们要进行的操作非常多时，比如要依次删除很多个表的数据，我们就需要执行大量的SQL语句来完成，这些数据库操作语句就可以构成一个事务！只有Innodb引擎支持事务，我们可以这样来查看支持的引擎：

SHOW ENGINES;

MySQL默认采用的是Innodb引擎，我们也可以去修改为其他的引擎。

事务具有以下特性：

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

我们通过以下例子来探究以下事务：

begin;   #开始事务
...
rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点
rollback to 回滚点; #回滚到指定回滚点
...
commit; #提交事务
-- 一旦提交，就无法再进行回滚了！

要commit之后才会显示.

=> 深入细节 原理 CS7330