一、mysql概述

MySQL是一款开源的关系型数据库管理系统，使用SQL语言进行数据管理，因其性能优异、成本低廉和社区活跃，成为LAMP/LEMP技术栈的核心组件，支撑着全球大多数网站和应用。 默认端口： 3306

  • 关系型：数据像 Excel表格，有严格的结构
  • 非关系型：数据像 JSON对象，结构灵活

二、sql基本操作

启动mysql： net start mysql 需要配置环境变量

停止mysql: net stop mysql

登录mysql： mysql -u 用户名 -p [数据库名]；

查看版本： mysql version();

退出： exit;

注意事项：

sql可以单行或多行书写，以分号结尾；

sql可以使用空格、缩进来增强语句的可读性。 注释： 单行：– 或 # 多行 /* */

mysql数据库不区分大小写，关键字建议大写。

三、DDL操作

1.库操作

(1)创建库

指令 完整语法 作用 是否检查存在 设置字符集 示例
基本创建 CREATE DATABASE 数据库名; 创建数据库 ❌ 不检查 ❌ 默认 CREATE DATABASE school;
检查存在 CREATE DATABASE IF NOT EXISTS 数据库名; 安全创建（不存在才创建） ✅ 检查 ❌ 默认 CREATE DATABASE IF NOT EXISTS school;
指定字符集 CREATE DATABASE 数据库名 CHARACTER SET 字符集; 创建并指定字符编码 ❌ 不检查 ✅ 指定 CREATE DATABASE school CHARACTER SET utf8mb4;
指定排序规则 CREATE DATABASE 数据库名 COLLATE 排序规则; 创建并指定排序规则 ❌ 不检查 ✅ 影响 CREATE DATABASE school COLLATE utf8mb4_general_ci;
完整创建 CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则; 完整配置创建 ❌ 不检查 ✅ 都指定 CREATE DATABASE school CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

(3)查看和切换库

指令 作用
show databases 查看当前所有库
select database() 查看当前库
SHOW CREATE DATABASE 数据库名; 显示数据库创建语句
user 数据库名 使用库

(4)修改和删除库

指令 完整语法 作用 适用版本 示例 注意事项
修改字符集 ALTER DATABASE 数据库名 CHARACTER SET 字符集; 修改数据库默认字符集 所有版本 ALTER DATABASE school CHARACTER SET utf8mb4; 不影响已有表，仅影响新表
修改排序规则 ALTER DATABASE 数据库名 COLLATE 排序规则; 修改数据库默认排序规则 所有版本 ALTER DATABASE school COLLATE utf8mb4_unicode_ci; 同上
修改字符集和排序规则 ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则; 同时修改字符集和排序规则 所有版本 ALTER DATABASE school CHARSET=utf8mb4 COLLATE=utf8mb4_bin; 常用组合修改
删除库 drop database 数据库名 删除
判断并删除库 drop database is exists 数据库名 删除 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#创建数据库   characrer set  设置字符    默认就是utf8mb4       collate 整理
create database if not exists ddl_dl character set utf8mb4 collate utf8mb4_0900_as_cs;

#查询数据库
show DATABASES;

#查询当前使用库
select database();

#显示数据库创建语句
show create database ddl_dl;

#使用库
use ddl_dl;

#删除库
drop database ddl_dl;

(5)备份和还原库

指令 作用
mysqldump -u用户名 -p密码 数据库名 > d:\\backup.sql 将数据库备份到d盘下的backup.sql
mysqldump -u用户名 -p密码 --all-databases > all_backup.sql 备份所有数据库
mysqldump -u用户名 -p密码 数据库名 表1 表2 > tables_backup.sql 备份数据库的表数据
mysql -u用户名 -p密码 数据库名 < 备份表 将备份的数据转到数据库

2.表操作

(1)基本格式：

1
2
3
4
5
6
CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [约束条件] [COMMENT "注释"],
    列名2 数据类型 [约束条件] [COMMENT "注释"],
    ...
    [列可选约束]
) [表可选约束][COMMENT "注释"];

(2)表增删改

操作 SQL 命令 说明
添加字段名 `ALTER TABLE 表名 ADD 列名 类型 [约束] [first after 其他字段名];`
删除字段 ALTER TABLE 表名 DROP 列名; 删除字段
修改字段类型 `ALTER TABLE 表名 MODIFY 列名 新类型 [约束] [first after 其他字段名];`
重命名字段 `ALTER TABLE 表名 CHANGE 旧列名 新列名 类型 [约束] [first after 其他字段名];`
重命名表 ALTER TABLE 旧表名 rename 新表名; 修改表名
清空表 TRUNCATE TABLE 表名; 清空表数据（不可回滚）
删除表 DROP TABLE [if exists] 表名; 删除表（包括数据）
查看结构 DESC 表名; 查看表结构
添加主键 ALTER TABLE 表名 ADD PRIMARY KEY (列名); 设置主键
删除主键 ALTER TABLE 表名 DROP PRIMARY KEY; 删除主键
添加索引 CREATE INDEX 索引名 ON 表名 (列名); 创建索引
删除索引 DROP INDEX 索引名 ON 表名; 删除索引 
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
#练习
create table if not exists employees(
emp_num int(11),
last_name varchar(50),
frist_name varchar(25),
mobile varchar(50),
`code` int,
job_title varchar(50),
birth date,
note varchar(255),
sex varchar(5)
)


#1.查看结构
desc employees;

#2.将mobile修改到code后面     after
alter table employees modify mobile varchar(25) after `code`;

#3.将birth修改为 birthday
alter table employees change birth birthday date;

#4.修改sex字段,设为char(1)
alter table employees modify sex char(1);

#删除字段note
alter table employees drop note;

#增加字段favoriate_activity,数据类型为varchar(100)
alter table employees add favoriate_activity varchar(100);

#修改表名为employees_info
alter table employees rename employees_info;

四、数据类型

1.mysql也有数据类型，用于表示每个字段的类型是什么和占用多大空间.

2.整数类型

数据类型 字节 有符号范围 无符号范围 描述
TINYINT 1 -128 ~ 127 0 ~ 255 极小整数
SMALLINT 2 -32768 ~ 32767 0 ~ 65535 小整数
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215 中等整数
INT 4 -2147483648 ~ 2147483647 0 ~ 4294967295 常用整数
BIGINT 8 -2^63 ~ 2^63-1 0 ~ 2^64-1 大整数

注：在整数类型后面再加： unsigned表示无符号范围； 如： book_price int unsigned;

3.定点和浮点类型

数据类型 字节 范围 描述
FLOAT 4 ±1.175e-38 ~ ±3.402e38 单精度浮点数，约7位精度
DOUBLE 8 ±2.225e-308 ~ ±1.798e308 双精度浮点数，约15位精度
DECIMAL(M,D) 变长 取决于M和D 精确小数，M为总位数，D为小数位

4.字符串类型

数据类型 最大长度 描述 存储特点
CHAR(n) 255字符 固定长度字符串 长度不足用空格填充，检索快
VARCHAR(n) 65535字节 可变长度字符串 只存储实际长度，节省空间
TINYTEXT 255字节 短文本 最大255字符
TEXT 64KB 长文本 最大65535字符
MEDIUMTEXT 16MB 中等文本 最大约1600万字符
LONGTEXT 4GB 超长文本 最大约43亿字符

5.二进制数据

数据类型 最大长度 描述
BINARY(n) 255字节 固定长度二进制
VARBINARY(n) 65535字节 可变长度二进制
TINYBLOB 255字节 小二进制对象
BLOB 64KB 二进制大对象
MEDIUMBLOB 16MB 中等二进制对象
LONGBLOB 4GB 超大二进制对象

6.特殊类型

数据类型 描述 示例
ENUM(‘v1’,’v2’,…) 枚举类型 ENUM(‘男’,’女’)
SET(‘v1’,’v2’,…) 集合类型 SET(‘阅读’,’音乐’,’运动’)
JSON JSON数据 {"name": "John"}

7.日期类型

数据类型 格式 范围 描述
DATE YYYY-MM-DD 1000-01-01 ~ 9999-12-31 日期
TIME HH:MM:SS -838:59:59 ~ 838:59:59 时间
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 日期时间
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 时间戳，带时区
YEAR YYYY 1901 ~ 2155 年份

注：日期类型后面可以带入 default, 参数：

1
2
3
4
5
CURRENT_TIMESTAMP   如果没有给时间，自动添加当前时间;
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP   如果没有给时间并且修改了该行字段，字段修改时间

register_time datetime default CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

五、约束

约束类型 关键字 核心作用 适用场景
非空约束 NOT NULL 强制字段不能存储 NULL 值 姓名、手机号、员工编号等必填字段
主键约束 PRIMARY KEY 唯一标识表中的每行数据（非空 + 唯一），一张表只能有一个主键 员工编号、用户 ID、订单 ID 等唯一标识
自增约束 AUTO_INCREMENT 自动生成唯一递增值 如id等自动递增
唯一约束 UNIQUE 强制字段值唯一（可包含 NULL，多张唯一约束） 手机号、邮箱、身份证号等不能重复的字段
默认约束 DEFAULT 字段未赋值时，自动填充默认值 性别默认「男」、状态默认「启用」
外键约束 FOREIGN KEY 建立两张表的关联，保证子表数据依赖于父表的有效性（维护参照完整性） 员工表的部门 ID 关联部门表的 ID
检查约束 CHECK 强制字段值满足指定条件（MySQL 8.0.16+ 才真正支持） 性别只能是「男 / 女」、年龄≥18 等 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#主表
create table student(
  sid int PRIMARY KEY AUTO_INCREMENT,       -- increment  自增的意思    primary  主要的意思
  `name` varchar(20) DEFAULT "张三" NOT NULL,   
  age int NOT NULL CHECK (age > 0 and age < 120),
  sex char(1) NOT NULL,
  tel varchar(11),             -- unique   唯一、独特
  unique(tel)
)
  
  
#子表
create table score(
  cid int primary key auto_increment,
  english int NOT NULL,
  math int NOT NULL,
  sid int,
  -- 外键
  constraint score_student FOREIGN KEY (sid) REFERENCES student(sid)      -- constraint 约束    foreign   外国的     erferences  参考文献
  ON DELETE CASCADE                 -- 当字段删除后也会跟着删除 
  ON UPDATE CASCADE                 -- 当字段更新后也会跟着更新
);

六、DML操作

操作 SQL 语句 说明
插入数据 INSERT INTO 表 (列1,列2) VALUES (值1,值2); 插入单行
批量插入 INSERT INTO 表 VALUES (行1值),(行2值); 插入多行
更新数据 UPDATE 表 SET 列=值,列=值 [WHERE 条件]; 更新记录
删除数据 DELETE FROM 表 [WHERE 条件]; 删除记录
插入查询结果 INSERT INTO 表1 SELECT * FROM 表2; 从查询插入
替换数据 REPLACE INTO 表 VALUES (...); 存在则更新，否则插入
合并数据 MERGE INTO 表 USING ... 合并操作（Oracle/SQL Server） 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table if not exists books(
  book_name varchar(20) COMMENT "书名",
  book_price double(4,1) COMMENT "价格",
  book_num int COMMENT "数量"
  ) charset = utf8mb4 COMMENT "图书表"
  
 #单行插入
insert into books (book_name,book_price,book_num) values ("西游记",128,200);

#单行或多行插入
insert into books values ("水浒传",198,98),("红楼梦",208,144);

#更新数据  书量和书价格				where表示条件，可有可无
update books set book_num=192,book_price=168 where book_name = "西游记";
#原列计算更新数据			book_prive+10   也可以 - / * % div(整除)！！！！！！
update books set book_num=192,book_price=book_prive+10 where book_name = "西游记";

#删除数据
delete from books where book_name = "红楼梦";

七、DQL操作

1.条件语句(where)

条件类型 运算符 示例 说明
比较条件 =, <>, !=, >, <, >=, <= WHERE age >= 18 比较大小
逻辑条件 `AND &&, OR , NOT !, XOR`
范围条件 BETWEEN ... AND ... WHERE age BETWEEN 18 AND 60 范围查询
列表条件 IN (...) NO IN(...) WHERE id IN (1, 2, 3) 在列表中
模糊匹配 LIKE %匹配0-n个字符 _匹配1个字符 WHERE name LIKE '张%' 模式匹配
空值判断 IS NULL, IS NOT NULL WHERE email IS NOT NULL 空值判断 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#条件语句
#where   ----->   and or not  = < > >=
select * from employees_info where `code` > 10003 and `code` < 10014 or job_title = "前端开发";
select * from employees_info where job_title = "产品经理";
#where   ----->   between ...  and ...  在这之间    in()  在这里面
select * from employees_info where `code` between 10001 and 10010; 
select * from employees_info where `code` in(10001,10004,10019);
#where   ----->   like  模糊查询
select * from employees_info where job_title like "后端%";
select * from employees_info where job_title like "%工程师";
select * from employees_info where job_title like "前端工程_";    #后面跟一个字符
select * from employees_info where job_title like "前端工__";    #后面跟二个字符
#where   ----->   IS NULL 字段为空列出,  IS NOT NULL  不为空列出
select * from employees_info where sex IS NULL;
select * from employees_info where sex IS NOT NULL;

2.基础查询

查询功能 SQL 命令示例 说明
查询所有列 SELECT * FROM 表名; 返回所有字段
查询指定列 SELECT 列1, 列2 FROM 表名; 返回指定字段
使用别名 SELECT 列名 AS 别名 FROM 表名; 给列起别名
去重查询 SELECT distinct 列名 FROM 表名; 去除重复值
条件查询 SELECT * FROM 表名 WHERE 条件; 按条件筛选
常数查询 SELECT *,"字符串" as 自定义列名 from 表名; 仅此次查询增加一列 
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
create table if not exists employees(
    emp_num int(11),
    last_name varchar(50),
    frist_name varchar(25),
    mobile varchar(50),
    `code` int,
    job_title varchar(50),	
    birth date,
    note varchar(255),
    sex varchar(5)
)
  
#查询表
select * from employees_info;

#查询返回单列或多列
select last_name,job_title from employees_info;

#使用别名查询     注：其他语句都可以使用as语句，也可以省略不写
select frist_name as f from employees_info;
select frist_name f from employees_info;

#去重查询
select distinct frist_name, job_title from employees_info;

#条件查询
select * from employees_info where `code` < 10008;

#常数			此时会增加 port 列
select * , "篮球" as `port` from employees_info;

3.分组查询

查询类型 SQL 语句 说明 结果示例
基础分组 SELECT 列, COUNT(*) FROM 表 GROUP BY 列; 按单列分组统计 各部门人数
多列分组 SELECT 列1,列2,COUNT(*) FROM 表 GROUP BY 列1,列2; 多列组合分组 各部门各职位人数

注： 分组查询最基本条件是group by 后面的列 必须在查询列有，并且必须使用至少一个聚合函数！！！！

1
2
3
4
5
6
7
#分组查询
select sex,count(*) from employees_info group by sex;     #根据姓别分组
select sex,job_title,count(*) from employees_info group by sex,job_title;     #根据性别和工作部门分组
#where查询会在分组查询之前操作
select sex,job_title,count(*) from employees_info where `code` > 10003 group by sex,job_title;     
#having查询会在分组完后操作，having必须跟group by，不能单独存在，  having 一般都是聚合函数再次筛选
select fenlei,count(*),round(avg(book_price),0) as av from books group by fenlei having av > 50;

4.排序查询

排序类型 SQL 语句 说明 示例结果
单列升序/降序 `ORDER BY 列 ASC DESC 默认ASC` 从小到大排序
多列排序 ORDER BY 列1, 列2 DESC 先按列1排，相同再按列2 (A,100), (A,90), (B,100)
按表达式排序 ORDER BY 列1+列2 按计算结果排序 按总分、总价等

5.分页查询

语法格式 说明 示例 结果
LIMIT n 只限制数量，不偏移 LIMIT 10 前10条
LIMIT m, n 偏移m条，取n条 LIMIT 20, 10 第21-30条 
1
2
#分页查询     查询从第四条数据开始后四条数据
select * from books limit 4,4;
1
执行顺序：  FORM  >   WHERE   >   GROUP BY...HAVING     >  ORDER BY    >  LIMIT

6.多表查询

多表查询类型 核心语法 作用说明 极简实操案例（基于 student/score/classes 表） 适用场景
内连接（INNER JOIN） SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段=表2.字段; （可省略INNER，直接写JOIN） 只返回两张表中「关联条件匹配」的行（最常用） SELECT s.sname, sc.score FROM student s JOIN score sc ON s.sid = sc.sid;（查询有成绩的学生姓名+分数） 需同时获取多张表的关联数据
多表联查（3张及以上 SELECT * FROM 表1 JOIN 表2 ON 条件1 JOIN 表3 ON 条件2; 依次关联多张表，关联条件需匹配表之间的外键关系 SELECT s.sname, c.cname, sc.score FROM student s JOIN classes c ON s.cid = c.cid JOIN score sc ON s.sid = sc.sid;（查询学生姓名+班级名称+分数） 需跨多张表获取关联数据
左外连接（LEFT JOIN SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段=表2.字段; 返回「左表所有行」+「右表关联匹配的行」，右表无匹配则补NULL SELECT s.sname, sc.score FROM student s LEFT JOIN score sc ON s.sid = sc.sid;（查询所有学生，无成绩则score为NULL） 需保留左表全部数据
右外连接（RIGHT JOIN） SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段=表2.字段; 返回「右表所有行」+「左表关联匹配的行」，左表无匹配则补NULL SELECT s.sname, sc.score FROM student s RIGHT JOIN score sc ON s.sid = sc.sid;（查询所有成绩，无对应学生则sname为NULL） 需保留右表全部数据
全外连接（FULL JOIN） MySQL无原生FULL JOIN，需用LEFT JOIN UNION RIGHT JOIN模拟 返回「左表+右表所有行」，无匹配则补NULL SELECT s.sname, sc.score FROM student s LEFT JOIN score sc ON s.sid=sc.sid UNION SELECT s.sname, sc.score FROM student s RIGHT JOIN score sc ON s.sid=sc.sid; 需保留两张表的全部数据
自连接（SELF JOIN） SELECT * FROM 表1 t1 JOIN 表1 t2 ON t1.字段=t2.字段; 同一张表当作两张表关联查询（给表起不同别名） SELECT t1.sname 学生, t2.sname 同班同学 FROM student t1 JOIN student t2 ON t1.cid = t2.cid AND t1.sid != t2.sid WHERE t1.sname = '张三';（查询张三的同班同学） 表内数据有层级/关联关系
子查询（嵌套查询） SELECT * FROM 表1 WHERE 字段 IN (SELECT 字段 FROM 表2 WHERE 条件); 把一个查询结果作为另一个查询的条件/数据源 SELECT sname FROM student WHERE sid IN (SELECT sid FROM score WHERE score > 90);（查询分数>90的学生姓名） 关联条件复杂、需先筛选再查询
联合查询（UNION/UNION ALL） SELECT 字段 FROM 表1 UNION SELECT 字段 FROM 表2;（去重） SELECT 字段 FROM 表1 UNION ALL SELECT 字段 FROM 表2;（不去重） 合并多个查询结果，垂直拼接数据 SELECT sid, sname FROM student WHERE cid=1 UNION SELECT sid, sname FROM student WHERE cid=2;（合并1班和2班学生） 合并结构相同的查询结果 
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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
#多表查询
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL CHECK (age >= 0 AND age <= 120),
    class_id INT NOT NULL
);

CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(20) NOT NULL UNIQUE,
    teacher VARCHAR(20) NOT NULL
);

CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject VARCHAR(20) NOT NULL,
    score INT NOT NULL CHECK (score >= 0 AND score <= 100)
);

CREATE TABLE north_sales (
    id INT PRIMARY KEY,
    product VARCHAR(20),
    amount DECIMAL(10,2),
    sale_date DATE
);

CREATE TABLE south_sales (
    id INT PRIMARY KEY,
    item VARCHAR(20),
    revenue DECIMAL(10,2),
    date DATE
);

CREATE TABLE old_products (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    price DECIMAL(10,2),
    status VARCHAR(10)
);

CREATE TABLE new_products (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    price DECIMAL(10,2),
    category VARCHAR(20)
);


#联合查询
#查询old_products表和new_products表的所有数据
select * from old_products UNION ALL select * from new_products;      #不会去除两张表有重复的！！！
select * from old_products UNION select * from new_products;          #会去重，需要保证字段完全相同，一个不同都不会生效


#内连接      可以省略inner join   逗号代替
select * from students inner join scores where students.id = scores.id;
select * from students,scores where students.id = scores.id;      #这两个相同,不过不推荐
#内连接三张表以上查询       select * from 表1 join 表2 on 字段对应 join 表3  on 字段对应 ...;

select * from students stu join classes cs on stu.id = cs.id join scores on stu.id = scores.student_id;
select stu.name,scores.math,cs.class_name from students stu,classes cs,scores where stu.id = scores.student_id and stu.id = cs.id;

#左外连接   如果第二个表没有对应主表的数据，则显示 null！！！！
select * from students left join classes on students.id = classes.id;         #此时左边的为主表

#右外连接   如果第一个表没有对应第二个表的数据，则显示 null！！！！
select * from students right join classes on students.id = classes.id;        #此时右边的为主表

#外连接三张表以上表查询     select * from 表1 left join 表2 on 字段对应 left join 表3  on 字段对应 ...;
select stu.name,scs.math,scs.english,cs.class_name from students stu left join scores scs on stu.id = scs.student_id left join classes cs on stu.id = cs.id;

#自连接    将一张表分成两张查询    比如： 姓名里面有组长和员工，每个员工都有组长， 要查询每个员工的组长此时使用自连接即可
select worker.ename,boos.ename from emp worker left join emp boos on worker.mgr = boos.ename # select -- from 表1 join 表1 on 员工id = 组长id；


#子查询    使另一个查询作为 数据源h
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
select * from emp where (gender,did) in (select gender,did from emp where ename = "白露");    #双列返回值作为条件
select * from from emp left join (select did,avg(salary) from emp group by did) as empTwo on emp.did = empTwo.id;   #将子查询作为虚拟表

八、函数

1.聚合函数

函数 语法 说明 示例
COUNT COUNT(expr) 统计行数 SELECT COUNT(*) FROM users;
SUM SUM(expr) 求和 SELECT SUM(salary) FROM employees;
AVG AVG(expr) 平均值 SELECT AVG(score) FROM students;
MAX MAX(expr) 最大值 SELECT MAX(price) FROM products;
MIN MIN(expr) 最小值 SELECT MIN(age) FROM users;
GROUP_CONCAT GROUP_CONCAT(expr) 合并字符串 SELECT GROUP_CONCAT(name) FROM users;

2.字符串函数

函数 语法 说明 示例
CONCAT CONCAT(str1, str2, ...) 连接字符串 SELECT CONCAT(first, ' ', last);
SUBSTRING SUBSTR(str, start, len) 截取子串 SELECT SUBSTR('Hello', 2, 3); → ‘ell’
LENGTH LENGTH(str) 字符串长度 SELECT LENGTH('Hello'); → 5
UPPER/LOWER UPPER(str), LOWER(str) 大小写转换 SELECT UPPER(name);
TRIM TRIM(str) 去除首尾空格 SELECT TRIM(' hello ');
REPLACE REPLACE(str, from, to) 替换字符串 REPLACE('A-B-C', '-', '/') → ‘A/B/C’
LEFT/RIGHT LEFT(str, n), RIGHT(str, n) 左右截取 SELECT LEFT('2024-01-21', 4); → ‘2024’
INSTR INSTR(str, substr) 查找位置 SELECT INSTR('hello', 'e'); → 2
LPAD/RPAD LPAD(str, len, padstr) 左右填充 SELECT LPAD('5', 3, '0'); → ‘005’

3.数值函数

函数 语法 说明 示例
ROUND ROUND(num, decimals) 四舍五入 SELECT ROUND(3.14159, 2); → 3.14
CEIL/FLOOR CEIL(num), FLOOR(num) 向上/向下取整 SELECT CEIL(3.2); → 4
ABS ABS(num) 绝对值 SELECT ABS(-10); → 10
MOD MOD(dividend, divisor) 取余数 SELECT MOD(10, 3); → 1
POW POW(base, exponent) 幂运算 SELECT POW(2, 3); → 8
SQRT SQRT(num) 平方根 SELECT SQRT(16); → 4
RAND RAND() 随机数(0-1) SELECT RAND();
FORMAT FORMAT(num, decimals) 格式化数字 SELECT FORMAT(1234567.89, 2); → ‘1,234,567.89’

4.日期函数

函数 语法 说明 示例
NOW/CURDATE/CURTIME NOW(), CURDATE(), CURTIME() 当前日期时间 SELECT NOW(); → ‘2024-01-21 10:30:00’
DATE_FORMAT DATE_FORMAT(date, format) 格式化日期 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
DATE_ADD/DATE_SUB DATE_ADD(date, INTERVAL expr unit) 日期加减 SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
DATEDIFF DATEDIFF(date1, date2) 日期差(天) SELECT DATEDIFF('2024-01-31', '2024-01-01'); → 30
YEAR/MONTH/DAY YEAR(date), MONTH(date), DAY(date) 提取年月日 SELECT YEAR('2024-01-21'); → 2024
HOUR/MINUTE/SECOND HOUR(time), MINUTE(time), SECOND(time) 提取时分秒 SELECT HOUR('10:30:45'); → 10
WEEK/DAYOFWEEK WEEK(date), DAYOFWEEK(date) 周数/星期几 SELECT DAYOFWEEK('2024-01-21'); → 1(周日)
LAST_DAY LAST_DAY(date) 月最后一天 SELECT LAST_DAY('2024-02-15'); → ‘2024-02-29’

5.条件判断函数

函数 语法 说明 示例
IF IF(expr, true_value, false_value) 简单条件判断 SELECT IF(score>=60, '及格', '不及格');
CASE WHEN CASE WHEN ... THEN ... END 多条件判断 见详细示例
IFNULL IFNULL(expr, value) NULL替换 SELECT IFNULL(name, '未知');
COALESCE COALESCE(expr1, expr2, ...) 返回第一个非NULL值 SELECT COALESCE(middle, first, '无名'); 
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
#函数
 #聚合函数   count(*) 统计行数   sum()求和  avg()平均值  nax()最大值  min()最小值
 select count(*) from employees_info;
 select sum(`code`) from employees_info;
 select avg(`code`) from employees_info;
 
 #字符串函数
 select concat("我爱","~","java");
 select substring("hello",2,3);   # ell  [2,3]
 select length("hello");   #5
 select upper("syy");  #syy  lower
 select trim("   hdsfguhg   ");   #去除周围空格
 select replace("A-B-C","-","_");   #将出现的-,全部替换为_
 select left("2024-12-15",4);    #截取前四个  2024    right()从右边开始截取
 select instr("hello","e");    #从1开始，  2
 select reverse("abcdefg");    #翻转字符串
 
 #数值函数
 select round(1232.3454,2);  #四舍五入，后面参数是小数点位数
 select ceil(4.1);  #向上取整  5
 select floor(5.9);  #向下取整  5
 select abs(-23);    #绝对值  23
 select pow(2,3);   #幂运算  8
 select sqrt(4);   #平方根计算  2
 select rand();    #随机数 0-1
 select floor(rand() * 100 + 1);   #练习 生成 1-100之间的数
 
 #时间日期函数
 select now();   #返回当前时间 年月日时分秒
 select curdate();    #返回当前 年月份
 select curtime();     #返回当前 时分秒
 select date_add(curdate(),interval 7 day);   #添加时间，在当前时间添加7天
 select date_sub(curdate(),interval 7 day);   #减少时间，在当前时间减少7天\
 select datediff(curdate(),'2026-1-5');     #时间相差天数   17
 select year("2025-12-13");          #提取年份     其他相关：  month() day() hour()  minute() second() week() 	dayofweek() 星期几
 select last_day("2024-12-4");       #获取本月最后一天的时间  年月份
 select date_format(now(),"%m-%d");    #返回月日 01-22      %Y  %y  %m  %d  %H  %i(分钟)  %s
 
 #判断函数
 select *,if(book_num > 0,"有货","无货") as stock from books;    #if(条件,满足,不满足)
 select *,book_price * IFNULL(book_num,0) as money from books;   #ifnull(原值,默认值)   如果值为空，使用默认值
 select last_name,			# case when 表达式 then 结果 when 表达式 then 结果 ... else 默认结果 end as 新字段名
 	   case
       when `code` > 10018 then "高新"
       when `code` > 10010 then "中鑫"
       when `code` > 10001 then "低薪"
       else "黑奴"
      end as salary from employees_info;

6.加密函数

函数 算法 输出长度 是否可逆 用途
MD5() MD5 32字符 ❌ 不可逆 简单哈希，已不推荐安全用途
SHA2() SHA-256/512 64/128字符 ❌ 不可逆 目前推荐的安全哈希
AES_ENCRYPT() AES 变长 ✅ 可逆 对称加密，需要密钥
AES_DECRYPT() AES 变长 ✅ 可逆 AES解密

7.窗口函数

函数 作用 案例
`over() (partition by 字段)分组 (order by)`
row_number() 将返回数据进行标序号 select name,dept,salary,row_number() over(order by salary) from employee; 和over搭配使用 
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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
#单表查询综合实战
 -- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS book_db;
USE book_db;

-- 创建 books 表
CREATE TABLE IF NOT EXISTS books (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    authors VARCHAR(100),
    price FLOAT,
    pubdate YEAR,
    note VARCHAR(100),
    num INT
);

-- 插入示例数据
INSERT INTO books (id, name, authors, price, pubdate, note, num) VALUES
(1, 'Tal of AAA', 'Dickes', 23, 1995, 'novel', 11),
(2, 'EmmaT', 'Jane lura', 35, 1993, 'joke', 22),
(3, 'Story of Jane', 'Jane Tim', 40, 2001, 'novel', 0),
(4, 'Lovey Day', 'George Byron', 20, 2005, 'novel', 30),
(5, 'Old land', 'Honore Blade', 30, 2010, 'law', 0),
(6, 'The Battle', 'Upton Sara', 30, 1999, 'medicine', 40),
(7, 'Rose Hood', 'Richard haggard', 28, 2008, 'cartoon', 28);


#将小说类型为novel价格提升5元
update books set price = price + 5 where note = "novel";

#将名称为EmmaT价格改为40,名称改为drama
update books set price = 40 , `name` = "drama" where `name` = "EmmaT";

#删除库存为0的记录

#统计书名包含a的书
select count(*) from books where `name` like "%a%";

#统计书名包含a的书的数量和库存总量
select count(*),sum(num) from books  where `name` like "%a%";

#找出 novel 类型的书，以价格降序排序
select * from books where note = "novel" order by price DESC;

#查询图书信息，按库存降序排序，库存相同按note排序
select * from books order by num DESC,note;

#按照 note 分类统计书的数量
select `note`,sum(num) from books group by `note`;

#按照 note 分类统计库存量，显示库存超过30本的书
select `note`,sum(num) as stock from books group by `note` having stock >= 30;

#查询图书，每页5本，显示第二页
select * from books limit 5,5;

#按照 note 分组显示库存最多的
select `note`,sum(num) as stock from books group by `note` limit 1;

#查询书名长度为10的，不包含空格
select * from books where length(trim(`name`)) >= 10;

#
SELECT 
    CASE
        WHEN note = 'novel' THEN '小说'
        WHEN note = 'law' THEN '法律'
        WHEN note = 'medicine' THEN '药学'
        WHEN note = 'cartoon' THEN '卡通'
        WHEN note = 'joke' THEN '笑话'
        ELSE note
    END AS '类型',
    COUNT(*) AS '书籍数量',
    note
FROM books
GROUP BY note;

#查询库存。大于0 显示畅销，等于0显示 无货， 大于10显示太多
SELECT
    `name`,
    num,
    CASE
      WHEN num > 10 THEN "货满"
      WHEN num > 0 and num < 10 THEN "畅销"
      WHEN num = 0 THEN "无货"
    END as "库存"
FROM books;

#统计每种note的库存量，并合计
select note,sum(num) as "库存" from books  group by note;

#统计每种note的数量，并合计
select note,count(num) as "数量" from books  group by note;

#统计库存前三个
select `name`,num from books order by num DESC limit 3;

#找出最早的书籍
select * from books order by pubdate ASC limit 1;

#找出 novel 最贵的书
select * from books order by price DESC limit 1;

#找出名字最多的书
select * from books order by length(`name`) DESC limit 1;

九、事务

1.ACID 原则：

特性 说明 示例
Atomicity（原子性） 事务是不可分割的最小单位 转账：A扣款和B加款必须同时成功或失败
Consistency（一致性） 事务前后数据状态一致 转账前后总金额不变
Isolation（隔离性） 并发事务互不干扰 两个转账操作互不影响
Durability（持久性） 事务提交后永久保存 转账成功后数据不会丢失

2.开始事务方式1：

指令 作用
select @@autocommit 当为1时表示自动提交(默认),当为0的时候手动提交
`set @@autocommit = 0 1`
commit 手动提交当前执行的所有指令
rollback 回滚操作，不执行所有指令 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#事务
create table account(
  id int primary key auto_increment,
  `name` varchar(20) NOT NULL,
  money int
)

#事务方式1： 
select @@autocommit;    -- 查询当前提交方式   1表示自动提交(默认)，0表示手动提交
set @@autocommit = 0;   -- 设置为手动提交

update account set money = money - 100 where `name` = "张三";
update account set money = money + 100 where `name` = '李四';

commit;   -- 必须执行这个上面的语句才会成功

rollback;   -- 如果上面语句有出错，执行这个指令进行回滚

2.开始事务方式2：

指令 作用
方式1：START TRANSACTION; 方式2：BEGIN;（简写） 显式开启手动事务，临时将当前会话的 AUTOCOMMIT 设为 0（自动提交关闭）
COMMIT; 将事务内所有 SQL 操作的修改持久化到数据库，结束事务
ROLLBACK; 撤销事务内所有未提交的修改，恢复到事务开启前的状态，结束事务
SAVEPOINT 保存点名; （如 SAVEPOINT sp1;） 在事务内创建「局部回滚标记」，可回滚到该标记处，而非全部回滚
ROLLBACK TO 保存点名; （如 ROLLBACK TO sp1;） 仅撤销「保存点之后」的操作，保留「保存点之前」的操作
RELEASE SAVEPOINT 保存点名; （如 RELEASE SAVEPOINT sp1;） 删除事务内指定的保存点，释放资源 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
begin;      -- 或者 start transaction

select `money` from account where `name` = '张三';
savepoint one;      -- 设置保存点，执行 rollback to one;
update account set money = money - 100 where `name` = "张三";
update account set money = money + 100 where `name` = '李四';

commit;   -- 必须执行这个上面的语句才会成功

rollback;   -- 如果上面语句有出错，执行这个指令进行回滚

rollback to one;    -- 回滚到保存点

release savepoint one;    -- 删除保存点，释放资源

3.事务隔离级别

隔离级别名称 关键字 核心定义 能解决的并发问题 存在的并发问题 性能排序 典型适用场景
读未提交 (Read Uncommitted) READ UNCOMMITTED 一个事务能读取到其他事务未提交的修改（最低级别） 无（仅解决了最基础的操作原子性） 脏读、不可重复读、幻读 最高 极少使用（仅临时测试 / 对数据一致性无要求的场景）
读已提交 (Read Committed) READ COMMITTED 一个事务只能读取到其他事务已提交的修改 脏读 不可重复读、幻读 次高 大多数互联网业务（如电商、社交，平衡一致性和性能）
可重复读 (Repeatable Read) REPEATABLE READ MySQL 默认级别，一个事务内多次读取同一数据，结果始终一致（不受其他事务提交影响） 脏读、不可重复读 幻读（MySQL 已通过 MVCC 解决） 次低 金融 / 财务类业务（需保证事务内数据一致性）
串行化 (Serializable) SERIALIZABLE 最高级别，事务串行执行（加锁），完全隔离，无并发问题 脏读、不可重复读、幻读 无 最低 对数据一致性要求极高的场景（如银行核心交易）
操作类型 语法/命令 作用描述 生效范围 权限要求 注意事项
1. 查看全局隔离级别 SELECT @@GLOBAL.transaction_isolation; (8.0+) 查看全局 全局设置 SELECT权限 MySQL 8.0+ 使用新变量名
2 查看当前隔离级别 SELECT @@transaction_isolation; (8.0+) 查看当前事务 当前会话 SELECT权限 简化写法，返回会话级别
4. SHOW VARIABLES查看 SHOW VARIABLES LIKE 'transaction_isolation'; (8.0+) 显示隔离级别系统变量值 系统变量 SELECT权限 显示变量名和值
5. 设置当前会话 SET TRANSACTION = "级别"; 设置当前会话的隔离级别 当前会话后续所有事务 无特殊权限 立即生效，不影响其他会话 
1
2
3
脏读：  一个事务读取到另一个事务未提交的数据；  错误
不可重复读：   一个事务读取到另一个事务修改后的数据；	不符合ACID原则(非错误)
幻读：  一个事务读取另一个事务插入或删除的数据；	不符合ACID原则(非错误)

十、用户管理

操作类别_______________________________ 操作命令 语法格式 作用说明_______________________________________________________________________________ 权限要求__________________________
1. 查看用户 查看所有用户 SELECT User, Host FROM mysql.user; 查看MySQL所有用户及允许登录的主机 SELECT权限
查看当前用户 SELECT CURRENT_USER(); SELECT USER(); 查看当前登录用户信息 无
2. 创建用户 创建新用户 `CREATE USER ‘用户名‘@’主机’ [IDENTIFIED BY ‘密码’] 主机：localhost ip % ;`
3. 删除用户 删除用户 DROP USER '用户名'@'主机', ...; 删除指定用户,或多个用户 CREATE USER权限
4. 修改用户 修改用户名 RENAME USER '旧用户'@'主机' TO '新用户'@'主机'; 修改用户名 CREATE USER权限
修改主机限制 先删除再创建或使用RENAME 修改用户允许登录的主机 CREATE USER权限
5. 修改密码 修改自己密码 ALTER USER USER() IDENTIFIED BY '新密码'; 修改当前登录用户密码 无
修改他人密码 ALTER USER '用户'@'主机' IDENTIFIED BY '新密码'; 修改指定用户密码 CREATE USER权限
6. 用户授权 授予权限 GRANT 权限列表 ON 数据库.表 TO '用户'@'主机'; 授予用户特定权限 GRANT权限
授予所有权限 GRANT ALL PRIVILEGES ON *.* TO '用户'@'主机'; 授予用户所有权限（超级用户） 超级用户权限
授予GRANT权限 GRANT 权限列表 ON 数据库.表 TO '用户'@'主机' WITH GRANT OPTION; 允许用户将权限授予他人 超级用户权限
7. 查看权限 查看用户权限 SHOW GRANTS FOR '用户'@'主机'; 查看指定用户权限 SELECT权限
查看自己权限 SHOW GRANTS; 查看当前用户权限 无
8. 撤销权限 撤销权限 REVOKE 权限列表 ON 数据库.表 FROM '用户'@'主机'; 撤销用户特定权限 GRANT权限
撤销所有权限 REVOKE ALL PRIVILEGES ON *.* FROM '用户'@'主机'; 撤销用户所有权限 超级用户权限
撤销GRANT权限 REVOKE GRANT OPTION ON 数据库.表 FROM '用户'@'主机'; 撤销用户授权给别人的权限 超级用户权限
9. 刷新权限 刷新权限 FLUSH peivileges; 刷新权限缓存，使修改立即生效 RELOAD权限
10. 锁定/解锁 锁定用户 ALTER USER '用户'@'主机' ACCOUNT LOCK; 锁定用户禁止登录 CREATE USER权限
解锁用户 ALTER USER '用户'@'主机' ACCOUNT UNLOCK; 解锁允许用户登录 CREATE USER权限

权限大全：

权限类别 权限名称 关键字 核心作用
数据权限 查询权限 SELECT 读取表中的数据
插入权限 INSERT 向表中插入新数据
更新权限 UPDATE 修改表中的数据
删除权限 DELETE 删除表中的数据
结构权限 创建权限 CREATE 创建数据库、表、视图等
删除权限 DROP 删除数据库、表等
修改权限 ALTER 修改表结构（添加/删除列等）
索引权限 INDEX 创建和删除索引
视图权限 CREATE VIEW 创建视图
触发器权限 TRIGGER 创建/删除触发器
临时表权限 CREATE TEMPORARY TABLES 创建临时表
存储过程权限 CREATE ROUTINE 创建存储过程/函数
修改存储过程 ALTER ROUTINE 修改存储过程/函数
执行存储过程 EXECUTE 执行存储过程/函数
事件权限 EVENT 创建/修改/删除事件
管理权限 全部权限 ALL PRIVILEGES 所有权限（最高权限）
授权权限 GRANT OPTION 允许用户将自己的权限授予他人
超级权限 SUPER 执行管理命令，绕过权限限制
进程权限 PROCESS 查看所有进程（SHOW PROCESSLIST）
文件权限 FILE 读写服务器文件（危险）
关闭权限 SHUTDOWN 关闭MySQL服务器
刷新权限 RELOAD 执行FLUSH操作（如刷新权限）
显示数据库 SHOW DATABASES 查看所有数据库列表
锁表权限 LOCK TABLES 锁定表
复制客户端 REPLICATION CLIENT 查看主从复制状态
复制从库 REPLICATION SLAVE 作为从库复制数据
创建用户 CREATE USER 创建新用户
其他权限 引用权限 REFERENCES 创建外键约束（基本无用）
代理权限 PROXY 代理其他用户（企业版）
使用权限 USAGE 无实际权限，仅用于连接 
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
#查看用户和当前用户
select user();    #当前用户  user() 或 current_user 或 current_user()
select current_user();
select User,host from mysql.user;   #查看所有用户

#创建用户
create user 'test'@'localhost' identified by '123456';     #创建用户和密码        identified  已识别 的意思    %表示全部主机都可

#删除用户
drop user 'test'@'localhost';

#修改名称
rename user 'test'@'localhost' to 'testNew'@'localhost';    

#修改密码
alter user 'testNew'@'localhost' identified by '1234';      #如果是自己可以使用 user() 函数代表自己\

#查看权限
show grants for 'testNew'@'localhost';

#授予权限
grant select on book_db.account to 'testNew'@'localhost';   #授予查询book_db数据库里面的 account表，此时这个用户只能查询到和查询这张表;
grant insert on book_db.* to 'testNew'@'localhost';         #授予修改book_db数据库里面的 所有表
flush privileges;             #刷新权限      privileges  特权
grant delete,update on book_db.* to 'testNew'@'localhost';      #授予多个权限
grant ALL PRIVILEGES on *.* to 'testNew'@'localhost';       #授予全部权限和全部数据库和表

#撤销权限
revoke insert on book_db.account from 'testNew'@'localhost';      #撤销 book_db.account表的插入权限;