mysql

3457字约12分钟

2026-01-22

安装

将下载好的mysql.zip压缩包解压到 C:\

设置环境变量 C:\mysql\bin

在mysql目录中创建 data 文件夹以及 my.ini 文件

my.ini 配置如下（Windows路径注意 \\）

[mysqld]
basedir=C:\\mysql\\
datadir=C:\\mysql\\data
port=3306
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
lower_case_table_names=1

[client]
port=3306
default-character-set=utf8mb4

// 以下命令需要管理员权限
cd C:\mysql\bin
mysqld --initialize --console	// 记住初始化的临时密码
// 将mysql安装为Windows服务，并将该服务命名为 MySQL80
mysqld --install MySQL80 --defaults-file="C:\\mysql\\my.ini"
net start MySQL80
mysql -u root -p
// 修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

将 C:\mysql\bin 添加到系统环境变量中即可

卸载

// 以下命令需要管理员权限
net stop MySQL80  # 若已启动，先停止；若未启动，忽略此步
mysqld --remove MySQL80

删除 C:\mysql\

语法

mysql不区分大小写，关键字推荐大写

注释

-- 单行注释
/* 多行注释 */

数据类型

数值

无符号：表示该数据没有负数
tinyint unsigned
int unsigned

// 表示该小数 整数 + 小数点 总共有 4 位， 小数点后共有 1 位
// 例：100.0
double(4, 1)

字符串类型

二进制类型的字符串类型不常用

char 与 varchar 的区别
存储方式：char 是固定长度，分配固定存储空间；varchar 是可变长度，按实际数据长度存储。
存储空间：char 会浪费未使用的空间；varchar 更节省空间（额外加 1 - 2 字节记录长度）。
处理速度：char 处理快，varchar 处理稍慢。
适用场景：char 适合长度固定的数据（如手机号）；varchar 适合长度可变的数据（如姓名）。

日期类型

create table emp (
	id int comment '编号',
	workno varchar(10) comment '工号',
	name varchar(10) comment '姓名',
	gender char(1) comment '性别',
	age tinyint unsigned comment '年龄',
	idcard char(18) comment '身份证号',
	entrydate date comment '入职时间'
) comment '员工表';

SQL分类

数据库

-- 查询所有数据库 
show databases;

-- 创建数据库 
create database sql01;

-- 创建数据库（判断数据库有没有，没有则创建） 
-- 判断语句 if not exists（没有）
create database if not exists sql01;

-- 创建数据库，并且将数据库的默认字符集设置为 utf8mb4
create database text default charset utf8mb4;

-- 删除数据库 
-- if exists（有） 
drop database if exists text;

-- 使用sql01数据库 
use sql01;

-- 查询当前使用的数据库名称
select database();

表 / 列

-- 查询当前数据库所有表 
show tables;

-- 创建表
create table user1 (
	id int comment '编号',	-- comment 为注解
	name varchar(50) comment '姓名',
	age int comment '年龄',
	gender varchar(1) comment '性别'
)comment '用户表';

-- 查询表结构
desc user1;

-- 查询指定表的创建语句 
show create table user1;

-- 添加列
alter table user1 add ceshi varchar(1) comment '测试';

-- 删除列（删除emp表中的ceshi列）
alter table emp drop ceshi;

-- 修改表中的列的数据类型、长度、约束等
alter table emp modify name char(10);

-- 修改描述名（同时也支持修改数据类型、长度、约束）
alter table emp change name username varchar(10) comment '描述名';

-- 修改表名
alter table emp rename to emp01;

-- 删除表(if exists判断有没有)
drop table if exists emp01;

-- 删除表中的所有数据（保留列，删除的数据无法恢复）
-- TRUNCATE TABLE user1; 用于删除 user1 表中所有数据，并重置自增计数器（若有自增列），且操作不可回滚。
-- 与 DELETE 相比，它执行更快，但无法指定条件删除，且会清空表数据而非逐行删除
truncate table user1;

添加数据

-- user1为表名
-- 指定字段（添加顺序与字段对应，字段顺序与表对应）
insert into user1(id, name, age, gender)
values (1, 'zhangsan', 20, '男');

-- 不指定字段(添加顺序与表对应)
insert into user1 values (2, 'lisi', 23, '男');

-- 添加多条数据（字段可以不写）
insert into user1(id, name, age, gender)
values 
(3, 'zhaoliu', 27, '男'),
(4, 'wangwu', 21, '男');

修改数据

-- 更新数据
-- 修改name以及age，条件是 id = 1 的
update user1 set name="周杰伦", age=18 where id=1;

-- 没有where条件语句则修改表中所有的age值
update user1 set age=20;

删除数据

-- 删除数据，条件为 id = 3 的（删除列）
delete from user1 where id=3;

-- 删除表中的所有数据（删除的数据可恢复）
delete from user1;

-- 删除某个字段的值 （删除列中的某个值）
update user1 set age=null where id=3;

查询数据

-- 查询语句（列出name, age, gender的信息）
select name, age, gender from user1;

-- 查询语句（列出name, age, gender的信息）
-- 并且将 name, age, gender 以更直观的形式显示
select name 姓名, age 年龄, gender 性别 from user1;

-- 查询语句，并且去除重复语句，条件为 gender='男'
select distinct name 姓名, age 年龄, gender 性别 
from user1
where gender='男';

-- age在 [20, 21] 之间的值
select * from user1
where age >= 20 && age <= 21;

    -- age在 [20, 21] 之间的值
    select * from user1
    where age between 18 and 30;

-- 显示age > 20 且 gender=男 的
select * from user1
where age > 20 and gender='男';

-- 显示 age=20 或 age=21的
select * from user1
where age in(20, 21);

-- 显示name只有两个字的
select * from user1
where name like '__';

-- 显示以 e 结尾的
select * from user1
where name like '%e';

-- 显示age不为空的值
select * from user1
where age is not null;

聚合函数

数据表

id	name	age	gender
1	zhangsan	20	男
3	null	null	男
4	wangwu	21	男
3	null	null	男
4	wangwu	21	男

-- 名字不为null的有几个，显示其数量
select count(name) from user1;	-- 3

-- 计算平均值
select avg(age) from user1; 

-- 显示最大值
select max(age) from user1;

-- 显示最小值
select min(age) from user1;

-- 求和
select sum(age) from user1;

分组查询

having必须在group by后面

数据表

id	name	age	gender
1	zhangsan	20	男
3	null	null	男
4	wangwu	21	男
3	null	null	男
4	wangwu	21	男
5	zhaoyi	20	女
6	xiaobai	23	女

-- 显示 男 和 女 的平均年龄
select gender, avg(age) from user1 group by gender;

gender	avg(age)
男	20.6667
女	21.5000

-- 条件：根据 gender 分组，并且 age 要大于等于 21
-- 显示满足条件的数量，并且要求该数量要大于2
select gender, count(*) from user1 
where age >= 21 
group by gender 
having count(*) >= 2;

gender	count(*)
男	2

排序查询

-- 显示结果以 age的升序排序（默认） 来显示
select * from user1 order by age asc;

-- 降序排序
select * from user1 order by age desc;

-- 排序结果，以 age 大小的降序 来排序
-- age相同时，则以 time1大小的降序 来排序
select * from user1 order by age desc, time1 desc;

id	name	age	gender	time1
4	xiaobai	24	女	2022-10-11 00:00:00
3	zhaoying	23	女	2019-10-11 00:00:00
2	lisi	22	男	2020-10-25 00:00:00
1	zhangsan	20	男	2020-10-11 00:00:00
5	wangwu	19	男	2017-10-11 00:00:00
6	wangwu	19	男	2016-10-11 00:00:00

分页查询

-- LIMIT 0, 3
-- 限制返回结果的行数，是 MySQL 中用于分页或限制结果集的关键字。
-- 第一个参数 `0`：表示 “起始行索引”（从 0 开始计数，即从第 1 行开始）。
-- 第二个参数 `3`：表示 “返回的最大行数”（最多返回 3 行数据）。
select * from user1 limit  0, 3;

id	name	age	gender	time1
1	zhangsan	20	男	2020-10-11 00:00:00
2	lisi	22	男	2020-10-25 00:00:00
3	zhaoying	23	女	2019-10-11 00:00:00

select * from user1 limit  3, 3;

id	name	age	gender	time1
4	xiaobai	24	女	2022-10-11 00:00:00
5	wangwu	19	男	2017-10-11 00:00:00
6	wangwu	19	男	2016-10-11 00:00:00

执行顺序

用户管理

-- 切换到mysql的系统数据库  
use mysql;
select * from user;

-- 创建用户 ceshi
create user 'ceshi'@'localhost' identified by '密码';

-- 修改密码（修改用户ceshi）
alter user 'ceshi'@'localhost' identified with mysql_native_password by '新密码';

-- 删除用户（删除用户ceshi）
drop user 'ceshi'@'localhost';

权限控制

权限列表

-- 查询用户权限
show grants for 'ceshi'@'localhost';

-- 授予用户权限
-- 允许用户 'ceshi' 从本地登录，拥有 'sql01' 数据库的所有权限
grant all on sql01.* to 'ceshi'@'localhost';

-- 撤销用户 'ceshi' 对 'sql01' 数据库的所有权限
revoke all on sql01.* from 'ceshi'@'localhost';

函数

字符串函数

-- 拼接两个字符串
select concat('zhangsan', 'lisi');
-- 输出：zhangsanlisi

-- 左填充
select lpad('01', 5, '-'); 
-- 输出：---01

-- 返回字符串（从第1个字符开始到第5个字符结束）
-- 下标是从1开始的
select substring('hello', 1, 5); 
-- 输出：hello

-- 将id值转成5位数的值 例：10 --> 00010
update user1 set id = lpad(id, 5, 0);

数值函数

-- 向上取整
select ceil(2.2);
-- 3

-- 向下取整
select floor(2.2); 
-- 2

-- 取余数
select mod(3, 2);
-- 1

-- 随机数
select rand();

-- 四舍五入，保留2位小数
select round(2.345, 2);
-- 2.35

-- 随机生成 6 位数的验证码
select lpad( round( (rand() * 1000000) , 0) , 6, 0);

日期函数

-- 获取时间中的年份
select year('2020-10-23');
-- 2020

-- 返回 70 天之后的日期
select date_add(now(), interval 70 day); 
-- 2021-01-01

-- 返回 结束时间 - 起始时间 的天数
select datediff('2021-06-01', '2021-01-01');
-- 151

-- 入职日期到当前日期的天数，倒序
select datediff(curdate(), time1) as newTime1 from user1 
order by newTime1 desc;

流程函数（if-else-）

-- 括号中第一个值为true时，返回 ok，为false时，为no
select if(true, 'ok', 'no');

-- 括号中第一个值不为null时，则显示第一个值
-- 括号中第一个值为null时，则返回第二个值
select ifnull('ok', 'default');
-- ok

-- 条件控制语句
-- 判断gender值，当值为 男 的时候输出 man，否则输出woman
-- when条件可以有多个（when '男' then 'man'）
select 
case gender when '男' then 'man' else 'woman' end
from user1;

-- 根据年龄输出对应的值
select 
case when age <= 22 then '小年轻' when age > 22 then '大年轻' else '年轻人' end
from user1;

约束

起别名

# 别名
# 当给表起了别名，引用数据时，是用别名引用，而不是表名
select u.id id, u.name name, b.name bumen, x.id_card shengfen
from user1 u
         inner join bumen b on u.bumen_id = b.id
         inner join xinxi x on u.id = x.id;

多表设计

一对一

一对多

部门和员工关系，改图中没有实现两个表的关系

要实现删除某个部门从而实现删除该部门中的员工信息，则需要使用外键（逻辑外键）

多对多

多表查询

部门表：
id name
1 人事部
2 市场部
3 研发部
员工表：
id name age gender bumen_id
1 zhangsan 20 男 2
2 lisi 22 男 3
3 wangwu 25 男 null
身份表：
id id_card
1 123123
2 123456
3 654321

id	name
1	人事部
2	市场部
3	研发部

id	id_card
1	123123
2	123456
3	654321

内连接

# 隐式内连接
select user1.id id, user1.name name, bumen.name bumen, xinxi.id_card shengfen
from user1, bumen, xinxi
where user1.bumen_id = bumen.id && user1.id = xinxi.id;

# 显式内连接
select user1.id id, user1.name name, bumen.name bumen, xinxi.id_card shengfen
from user1
inner join bumen on user1.bumen_id = bumen.id
inner join xinxi on user1.id = xinxi.id;

查询结果：
id name bumen shengfen
1 zhangsan 市场部 123123
2 lisi 研发部 123456

id	name	bumen	shengfen
1	zhangsan	市场部	123123
2	lisi	研发部	123456

外连接

# 用的多一点
# 左外连接(数据中会包括左表（user1）中的所有数据)
select user1.id id, user1.name name, bumen.name bumen
from user1 left join bumen on user1.bumen_id = bumen.id;

id	name	bumen
1	zhangsan	市场部
2	lisi	研发部
3	wangwu	null

# 右外连接(数据中会包括右表（bumen）中的所有数据)
select user1.id id, user1.name name, bumen.name bumen
from user1 right join bumen on user1.bumen_id = bumen.id;

id	name	bumen
null	null	人事部
1	zhangsan	市场部
2	lisi	研发部

子查询

标量子查询：子查询返回结果为单个值

# 标量子查询
# 查询研发部的员工
select id from bumen where name = '研发部';    # 3

select * from user1
where user1.bumen_id = (select id from bumen where name = '研发部');

id	name	age	gender	bumen_id
2	lisi	22	男	3

列子查询：子查询返回结果为一列

# 列子查询
select id from bumen where name = '研发部' or name = '市场部';	# 2 3

select * from user1
where user1.bumen_id in (select id from bumen where name = '研发部' or name = '市场部');

id	name	age	gender	bumen_id
1	zhangsan	20	男	2
2	lisi	22	男	3

行子查询：子查询返回结果为一行

# 行子查询
select user1.age, user1.bumen_id from user1 where user1.name = 'zhangsan';  # 20 2

select * from user1
where (age, bumen_id) = (select age, bumen_id from user1 where name = 'zhangsan');

id	name	age	gender	bumen_id
1	zhangsan	20	男	2

表子查询：子查询返回结果为多行多列

# 表子查询
select * from user1 where age >= 22;

select u.*, b.name from
(select * from user1 where age >= 22) u, bumen b
where u.bumen_id = b.id;

id	name	age	gender	bumen_id	name
2	lisi	22	男	3	研发部

事务

# 开启事务
# 开启事务之后，执行的多条sql语句为一个事务
start transaction;

# 执行sql语句
delete from bumen where id = 2;
delete from user1 where user1.bumen_id = 2;

# 提交事务
# sql语句执行成功，没有报错，则提交事务，使其生效
commit;

# 回滚事务
# sql语句执行失败，则回滚事务，回到执行sql语句之前的操作
rollback;

开启事务，提交和回滚事务二选一

索引

结构

语法

# 创建索引
# 创建 user1表 中的 name列 索引
create index idx_user1_name on user1(name);
# idx_user1_name 为索引的名称
# 命名规范为 idx_表名_索引列

# 查看索引
# 查看 user1 表中的索引
show index from user1;

# 删除索引
# 删除 user1 表中的 idx_user1_name 索引
drop index idx_user1_name on user1;

细节：
主键字段，在建表时，会自动创建主键索引。（性能最高）添加唯一约束时，数据库实际上会添加唯一索引。