# 结构化查询语言SQL(Structured Query Language)
SQL包含了所有对数据库的操作，主要由4个部分组成：
1. 数据定义（DDL）：create、drop、alter
2. 数据操作（DML）：insert、update、delete
3. 数据查询（DQL）：select
4. 数据控制（DCL）：grant、revoke
## 1. 数据库和表的基本操作
1. 使用指定数据库
```mysql
use 数据库名称;
```
2. 显示操作
1. 显示所有数据库
```mysql
show databases;
```
2. 显示当前数据库中所有的表
```mysql
show tables;
```
3. 显示指定表中所有的字段
```mysql
show COLUMNS FROM 表名;
```
4. 显示服务器状态
```mysql
SHOW STATUS;
```
5. 显示用户权限
```mysql
-- 显示所有用户的安全权限
SHOW GRANTS;
-- 显示指定用户的安全权限
SHOW GRANTS for 用户名;
```
6. 显示服务器错误消息
```mysql
SHOW ERRORS;
```
7. 显示服务器警告消息
```mysql
SHOW WARNINGS;
```
3. 创建数据库
```mysql
-- 创建数据库并设置默认编码方式为utf8mb4
create database 数据库名称 default character set utf8mb4;
-- 显示字符集
show character set;
-- 创建数据库的时候设置排序方式
create database 数据库名称 default character set utf8mb4 collate utf8mb4_0900_as_cs;
-- 显示所有的排序方式
show collation;
-- 常见排序方式
utf8mb4_bin -- 按照字符编码值大小排序
utf8mb4_0900_ai_ci -- 排序时不区分重音和大小写（默认）
utf8mb4_0900_as_cs -- 区分重音和大小写
```
4. 删除数据库
```mysql
drop database 数据库名;
```
5. 创建表
```mysql
create table 表名(
字段名1 类型 约束 comment 说明,
字段名2 类型 约束 comment 说明,
字段名3 类型 约束 comment 说明,
...
PRIMARY KEY(字段名),
CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段) REFERENCES 被依赖字段所在的表(字段)
)ENGINE=引擎类型;
-- 1）comment以及后面的说明可以省略；约束也可以没有，也可有有多个
-- 2）常见约束和常见类型见后文
-- 3) 添加外键约束前表中必须已经存在对应的外键字段，并且外键字段的类型要和被依赖的字段的类型保持一致
-- 4）常见的引擎类型有：a.InnoDB(是一个可靠的事务处理引擎，它不支持全文本搜索) b.MyISAM(是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。)
-- 注意：工程化开发的时候，id字段一定要有，最好添加数据添加时间和更新时间对应的字段
create table 表名(
字段名1 类型 约束 comment 说明,
字段名2 类型 约束 comment 说明,
字段名3 类型 约束 comment 说明,
...
PRIMARY KEY(字段名),
);
```
1. 常见约束
```mysql
1)NOT NULL - 不允许为空值或者值缺失
2)AUTO_INCREMENT - 自动增长（只有字段类型是整数的时候才可以添加；每个表只允许一个AUTO_INCREMENT列，而且它必须被索引）
3)PRIMARY KEY - 主键约束（每张表必须有且只能有一个主键）
4)DEFAULT - 默认约束，设置默认值
5)Unique - 唯一约束
6)Foreign Key - 外键约束，用于在两个表之间的数据设立关联
```
2. mysql常见类型
```mysql
1）整数
a. 有符号整数：int(4字节)、bigint(8字节)、smallint(2)、tinyint(1)
b. 无符号整数：int unsigned 、 bigint unsigned 、 smallint unsigned 、 tinyint unsigned
c. 零填充: int(4) zerofill - 0001、0100、0111、0123
推荐使用：int、bigint
2）小数
a. float(单精度4字节)
b. double(双精度8字节)
c. decimal(定点数)
decimal使用方法：decimal(M,D)，其中M是数字的最大位数，范围是1~65，默认是10；D是小数点后的位数，范围是0~30并且不能大于M，默认是0。
推荐使用：decimal
3）字符串
a. char(定长字符串)：char(N)将文本数据保存成长度为N的字符串，如果数据长度超过N会报错，不足N会自动补充
b. varchar(变长字符串)： varchar(N)，字符串长度不能超过N，具体存储长度由数据长度确定。
c. text：65535字节
c. longtext：2147483647字节(4G)
推荐使用：varchar
注意事项：不推荐将大的文本或二进制数据直接放到列中，遇到这种情况建议保存文件路径或URL即可
4）时间日期：time、date、datetime、timestamp
a. time：HH:mm:ss(00:00:00到23:59:59)
b. date：YYYY-MM-DD(1901-01-01到9999-01-01)
c. datetime：YYYY-MM-DD HH:mm:ss(1901-01-01 00:00:00 到 9999-12-31 23:59:59)
d. timestamp：YYYY-MM-DD HH:mm:ss(1901-01-01 00:00:00 到 9999-12-31 23:59:59)，存储的时候存的是时间戳，可用于不同的时区
```
6. 删除表
```mysql
DROP TABLE 表名;
-- 注意：数据库中所有的删库和删表操作必须慎重再慎重！！！
```
7. 更新表(修改表)
通过`ALTER TABLE`可以对表进行更新。 更新表主要是修改表的结构，常见操作是删除某个字段，或者增加字段。但是，理想状态下，当表中存储数据以后，该表就不应该再被更新，所以开始在设计表的时候一定要多加考虑，避免后期修改。不过我们通过更新表来给表添加外键。
```mysql
1)更新表添加字段
ALTER TABLE 表名 ADD 字段名 字段类型;
2)更新表删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;
3)添加外键约束
alter table 表名 add constraint 外键连接名 foreign key (外键字段) references 被依赖表名(被依赖字段名);
-- 添加外键约束前必须保存外键字段已经存在，如果不存在需要通过ALTER去添加这个字段然后再添加外键约束
-- 添加外键约束的方式：
-- 一对一 - 外键添加在任意一方都可以
-- 一对多 - 外键添加在多的一方
-- 多对多 - 需要创建一个中间表，在中间表中建立外键约束
4)删除外键约束
alter table 表名 drop foreign key 外键连接名;
```
8. 重命名表
```mysql
-- 对一张表进行重命名
RENAME TABLE 原表名 TO 新表名;
-- 同时对多张表进行重命名
RENAME TABLE 原表名1 To 新表名1,
原表名2 To 新表名2,
...
原表名N To 新表名N;
```
9. 查询表结构
```mysql
desc 表名;
```
## 2. 数据基本操作
1. 插入数据
```mysql
1)插入完整的一行数据1
INSERT INTO 表名 VALUES (数据1, 数据2, 数据3, ...);
-- 数据的个数和表的列数保持一致，并且位置上一一对应。
-- 这种做法语法很简单，但并不安全，应该尽量避免使用。
-- 上面的SQL语句高度依赖于表中列的定义次序
2)插入一行数据
INSERT INTO 表名(字段名1, 字段名2, 字段名3,...) VALUES(数据1, 数据2, 数据3, ...);
-- 后面括号中数据的个数和顺序跟前面括号中字段的个数和顺序保持一致
-- 这种做法虽然复杂，但是安全，并且数据的添加不依赖表中字段的顺序，及时表结构发生改变也不影响数据的插入
3)同时插入多行
INSERT INTO 表名
(字段名1, 字段名2, 字段名3,...)
VALUES
(数据1, 数据2, 数据3, ...),
(数据1, 数据2, 数据3, ...),
(数据1, 数据2, 数据3, ...),
...
(数据1, 数据2, 数据3, ...);
```
2. 更新数据
```mysql
1)更新一个字段的值
UPDATE 表名 set 字段名=值 WHERE 更新条件;
-- 不要省略where语句，否则会更新所有的行
2)同时更新多个字段的值
UPDATE 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3,... WHERE 更新条件;
```
3. 删除数据
```mysql
1)删除整行数据
DELETE FROM 表名 WHERE 删除条件;
-- 不要轻易省略where语句，除非你要删除表中所有行的数据
```
## 3. 数据查询
### 3.1 基本查询操作
1. 查询单列数据
```mysql
select 查询对象 from 表名
1)获取一列数据
select 字段名 from 表名;
2)同时获取多列数据
select 字段名1, 字段名2, 字段名3, ... from 表名;
3)同时获取所有列的数据
select * from 表名;
```
2. 返回唯一值(对查询数据去重)
```mysql
SELECT DISTINCT 字段名 FROM 表名;
-- DISTINCT关键字，它必须直接放在列名的前面（所有列名的前面，不是某个列的前面）
```
3. 限制结果
SELECT语句返回所有匹配的行，为了返回第一行或前几行，可以使用LIMIT子句。
```mysql
1)获取前N行查询结果
SELECT * FROM 表名 LIMIT N;
SELECT * FROM 表名 WHERE 查询条件 LIMIT N;
2)从第M行开始获取N行查询结果
SELECT * FROM 表名 LIMIT M,N;
```
4. 查询结果排序
```mysql
1)将查询结果按照指定字段的值从小到大排序
SELECT * FROM 表名 ORDER BY 字段名;
2)将查询结果按照指定字段的值从大到小排序
SELECT * FROM 表名 ORDER BY 字段名 DESC;
-- DESC代表降序，ASC(默认)代表升序
3)查询结果先按照字段1排序，字段1相等的时候再按照字段2排序
SELECT * FROM 表名 ORDER BY 字段1, 字段2;
```
### 3.2 过滤数据(筛选数据)
数据库中一般包含大量的数据，很少需要查询表中所有行，一般需要根据条件来对数据进行过滤。
1. 使用where子句
```mysql
1)获取满足指定条件的所有行
SELECT 查询内容 FROM 表名 WHERE 条件;
-- 注意: 如果WHERE和ORDER BY同时使用的话，ORDER BY要放在WHERE的后面
```
2. where子句操作符
| 操作符 | 说明 |
| --------------- | ------------------ |
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN - AND | 在指定的两个值之间 |
```mysql
1)获取指定字段等于指定值的所有行
SELECT * FROM 表名 WHERE 字段=值;
2)获取指定字段在值1到值2之间的所有行
SELECT * FROM 表名 WHERE 字段 BETWEEN 值1 AND 值2;
3)空值检查
a.获取指定字段不为空的所有行
SELECT * FROM 表名 WHERE 字段 IS NOT NULL;
b.获取指定字段为空的所有行
SELECT * FROM tb_record WHERE score IS NULL;
```
3. 组合where子句
为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。
```mysql
1）查询同时满足多个条件的所有行
SELECT * FROM 表名 WHERE 条件1 AND 条件2 AND 条件3 AND ...;
2)查询多个条件中只要满足一个条件的所有行
SELECT * FROM 表名 WHERE 条件1 OR 条件2 OR 条件3 OR ...;
-- 注意：AND和OR可以同时使用，同时使用的时候AND的优先级更高，如果需要先判断OR连接的条件可以通过加()来改变运算顺序。
3) 如果需要查询满足多个条件中任意一个条件的时候除了使用or还可以单独查询每个条件对应的数据，然后再用 union 对结果求并集。
通用sql支持三个集合运算，但是mysql只支持其中的并集运算
union / union all - 并集运算
intersect - 交集运算
except / minus - 差集运算
```
WHERE子句中还可以使用IN来判断字段为多种不同的值的情况：
```mysql
3)查询指定字段值为值1或者值2或者值3...的所有行
SELECT * FROM 表名 WHERE 字段 in (值1, 值2, 值3,...);
```
4. NOT操作符
where子句中可以通过NOT来对条件进行否定，NOT在使用的时候非常灵活，下面举例说明：
```mysql
-- 注意：下面的例子只是表示NOT的使用规则，并不是最优查询方法
1)查询tb_record表中score字段值不大于90分的所有行
SELECT * FROM tb_record WHERE NOT score>90;
2)查询tb_record表中score值不在70到85之间的所有行
SELECT * FROM tb_record WHERE score NOT BETWEEN 70 and 85;
SELECT * FROM tb_record WHERE NOT score BETWEEN 70 and 85;
3)查询tb_record表中score值不为1111、3333和5555的所有行
SELECT * FROM tb_record WHERE cou_id NOT IN (1111, 3333, 5555);
SELECT * FROM tb_record WHERE NOT cou_id IN (1111, 3333, 5555);
```
5. 通配符的使用
过滤数据的时候，有的时候过滤条件和文本数据有关，如果数据不是已知的（比如：获取所有姓’张‘的学生信息），就需要用通配符来表示字符串中某个位置上为任意一个或者多个符号。
SQL中支持的通配符有`%`和`_`两种，如果要使用通配符，必须使用LIKE操作符。（LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。）
```mysql
1)查询tb_student表中姓'杨'的所有学生信息
SELECT * FROM tb_student WHERE stu_name LIKE '杨%';
2)查询tb_student表中名为'过'的所有学生信息
SELECT * FROM tb_student WHERE stu_name LIKE '_过';
-- 注意：a. % 表示任意符号出现任意多次；_ 表示任意符号出现一次
-- b. 在一个条件中通配符可以出现多次，例如：'%平%'
-- c. 通配符查询的处理一般要比前面讨论的其他查询所花时间更长，所以不要过度使用
```
6. 空值处理
判断数据是否为不能用 `=` 或者`<>` 来和 `null` 做比较运算，而是使用 `is` 或者 `is not`。
> 1) 如果用 = 或者 <> 和空值null进行判断的时候会出现‘三值逻辑’ 现象。
>
> 三值： true / false / unknown
> 2) trim(字符串) - 删除指定字符串左右两边指定的符号（默认是空格）
7. 分支结构
```mysql
1. 标准sql
case when 条件 then 值1 else 值2 end
2. if - mysql方言
if(条件, 值1, 值2)
```
8. ifnull - mysql方言
ifnull(字段, 值)
coalesce(数据1, 数据2, 数据3, ...) - 返回数据中的第一个非空值
```
```
### 3.3 利用正则表达式进行查询
对数据进行查询的时候有的时候会遇到利用上面的东西无法实现的更加复杂的条件，这个时候可以尝试使用正则表达式来对数据进行筛选。
如果要在MySQL使用正则表达式需要使用关键字**REGEXP**。
```mysql
-- MySQL中正则表达式的使用方法: SELECT * FROM 表名 WHERE REGEXP 正则表达式;
1)查询tb_student表中姓'杨'并且名为两个字的所有行
SELECT * FROM tb_student WHERE stu_name REGEXP '杨.{2}';
2)查询tb_student表中地址值中有数字的所有行
SELECT * FROM tb_student WHERE stu_addr REGEXP '.*[0-9].*';
'[0-9]{3}'
```
MySQL中的正则不支持**\d、\w、\s**等以反斜杠开头的特殊符号，其他符号基本都支持。除此以外还有一些特殊的匹配字符类：
| 符号 | 意义 |
| ---------- | --------------------------------- |
| [:alnum:] | 任意字母和数字（同[a-zA-Z0-9]） |
| [:alpha:] | 任意字母（同[a-zA-Z]） |
| [:blank:] | 空格和制表符 |
| [:cntrl:] | ASCII码在0~127的所有字符 |
| [:digit:] | 任意数字（同[0-9]） |
| [:lower:] | 任意小写字母（同[a-z]） |
| [:space:] | 包括空格在内的任意空白字符 |
| [:upper:] | 任意大写字母（同[A-Z]） |
| [:xdigit:] | 任意十六进制数字（同[a-fA-F0-9]） |
### 3.4 查询结果去重
查询的时候可以通过 `distinct` 来声明字段，让字段在查询的时候自动去除重复的值。
```mysql
1）查询学生选课的所有日期（去重）
select distinct sel_date from tb_record;
```
### 3.5 查询结果排序
查询结果可以通过 `order by` 按照指定的字段的大小升序或者降序排序。
```mysql
-- 查询男学生的姓名和生日按照年龄从大到小排序。
select stu_name, stu_birth from tb_student where stu_sex = 1 order by stu_birth;
-- order by 字段 - 按照指定字段大小升序排序
-- order by 字段 asc - 按照指定字段大小升序排序; asc(ascending)
-- order by 字段 desc - 按照指定字段大小降序排序；desc(descending)
-- order by 字段1 asc/desc, 字段2 asc/desc, 字段3 asc/desc, ... - 指定多个排序标签，按照前面的字段大小排序后，如果有相同的情况，在按照后面字段的大小继续排序。
```
### 3.6 日期函数
对数据完成查询操作以后，有的时候需要去处理数据。处理数据的方式多种多样，其中经常会用到和日期相关函数。
```mysql
-- 1）日期时间函数
-- now() - 当前时间日期 2023-11-2 16:46:50
-- curdate() - 当前日期 2023-11-2
-- curtime() - 当前时间 16:46:50
-- 2）计算时间差函数
-- timestampdiff(单位, 起始日期, 终止日期)
-- 单位：MONTH, YEAR, DAY, HOUR, MINUTE, SECOND
-- datediff(起始日期, 终止日期) - 计算两个日期之间相差的天数
-- 3)常见的日期操作
-- year(日期) - 获取年份
-- month(日期) - 获取月份
-- day(日期) - 获取日
-- week(日期) - 指定日期是当年的第几周
-- weekday(日期) - 获取指定日期对应的星期值，返回值是0~6表示周一~周日
-- 计算 2003-02-01 与 2003-05-01 相差多少个月
timestampdiff(MONTH, '2003-02-01', '2003-05-01');
-- 查询每个学生的名字和年龄
select stu_name
, timestampdiff(YEAR, stu_birth, curdate())
from tb_student;
```
### 3.7 聚合函数
聚合函数指的是有规约性质(把多个数据通过各种方式处理成一个数据)的函数
```mysql
max() - 求最大值
min() - 求最小值
count() - 统计个数
sum() - 求和
avg() - 求平均值
variance() - 求方差
stddev() - 求标准差
```
### 3.10 创建计算字段
计算字段可以理解成查询结果经过相关操作得到新的结果列，这个结果列就可以看成一个新的字段。
计算字段可以是通过运算符和函数来创建
```mysql
-- 取整 - floor() / ceil()
-- 四舍五入 - round()
```
```mysql
1) 查询学生的名字和地址，以'地址-姓名'的格式拼接成一个计算字段
SELECT CONCAT(stu_addr,'-',stu_name) FROM tb_student;
-- 补充：CONCAT是MySQL中的字符串拼接函数，其他数据库管理系统(DBMS)字符串拼接一般用+或者||实现
```
为了更好的使用和理解计算字段，查询的时候计算字段一般会使用别名：
```mysql
2) 查询学生的名字和地址，以'地址-姓名'的格式拼接成一个计算字段并且给计算字段重命名为'new_name'
SELECT CONCAT(stu_addr,'-',stu_name) as new_name FROM tb_student;
```
### 3.11 窗口函数
Mysql只有8.xx的版本才支持窗口函数，窗口函数的作用是给数据加标签，可以对数据进行实时分析处理。
窗口函数的用法和普通函数不同，并不是单纯的提供参数就行，下面是使用窗口函数的语法结构：
```mysql
函数名([参数]) over 子句
其中，over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下语法来设置窗口：
函数名（[参数]） over(partition by <要分列的组> order by <要排序的列> rows
between <数据范围>)
字句中数据范围往往会用到以下时间点：
-- n PRECEDING : 前n行
-- n FOLLOWING：后n行
-- CURRENT ROW ： 当前行
-- UNBOUNDED PRECEDING：窗口第一行
-- UNBOUNDED FOLLOWING：窗口的最后一行
```
一般，我们可以把窗口函数分为两种：
1. 专有窗口函数
```mysql
rank() - 获取排名
dense_rank() - 获取排名
row_number() - 获取行号
lag() - 延后
lead() - 领先
```
2. 聚合类窗口函数： max/min/sum/avg/count
## 4. DCL（数据控制语言）
1. 创建用户
```mysql
create user 用户名@主机地址 identified by 密码;
例如：
create user 'xiaoming'@'localhost' identified by 'yuting123456';
-- 1）用户名：用户自定义
-- 2）主机地址：当前常见的用户能够连接数据库的主机地址（在那台计算机上可以登录这个数据库）
-- localhost和127.0.0.1都表示本地登录
-- 主机地址可以直接写一个ip地址也可以结合%来控制某个网段内的主机可以登录
-- 3）密码： 用户自定义，实际开发的时候不能使用弱口令，必须使用强口令
```
2. 用户授权
```mysql
grant 权限名 on 数据库.表名 to 用户名;
例如：
grant select on hrs.tb_emp to 'xiaoming'@'localhost';
grant all privileges on *.* to 'xiaoming'@'localhost' with grant option;
-- 1）权限名： insert、delete、update、select - 数据增删改查权限
-- all privileges - 所有权限
-- 2）数据库.表名：指定数据库中的指定表，这个为了可以用*来表示所有
-- 3) 用户名：用户名必须是 '用户名@主机地址' 结构的用户名
```
3. 召回权限
```mysql
revoke 权限名 on 数据库.表名 from 用户名;
例如：
revoke all privileges on *.* from 'xiaoming'@'localhost';
```
4. 修改密码
```mysql
alter user 用户名 identified by 密码;
例如：
alter user 'xiaoming'@'localhost' identified by '123456';
```
5. 删除用户
```mysql
drop user 用户名;
例如：
drop user if EXISTS 'xiaoming'@'localhost';
```
## 5. TCL（事务控制语言）
事务：把多个写数据的操作视为不可分割的原子性操作，要么全部成功，要么全部失败
```mysql
-- 开始事务环境
start transaction
-- 事务提交
commit;
-- 事务回滚
rollback
```mysql数据库基础知识
本节14627字2025-04-14 13:56:46
