1. 简单概述
MySQL安装后默认有4个库不可以删除，存储的是服务运行时加载的不同功能的程序和数据
- information_schema：是MySQL数据库提供的一个虚拟的数据库，存储了MySQL数据库中的相关信息，比如数据库、表、列、索引、权限、角色等信息。它并不存储实际的数据，而是提供了一些视图和存储过程，用于查询和管理数据库的元数据信息。
- mysql：存储了MySQL服务器的系统配置、用户、账号和权限信息等。它是MySQL数据库最基本的库，存储了MySQL服务器的核心信息。
- performance_schema：存储了MySQL数据库的性能指标、事件和统计信息等数据，可以用于性能分析和优化。
- sys：是MySQL 8.0引入的一个新库，它基于information_schema和performance_schema视图，提供了更方便、更直观的方式来查询和管理MySQL数据库的元数据和性能数据。
MySQL图形工具
- phpMyAdmin
- DBeaver
- MySOL-WorkBench
1.1 相关信息&配置文件
| 说明 | MySQL信息 |
|---|---|
| 主配置文件 | /etc/my.cnf.d/mysql-server.cnf |
| 数据库目录 | /var/lib/mysql |
| 端口号 | 3306 |
| 进程号 | mysql |
| 传输协议 | TCP |
| 进程所有者 | mysql |
| 进程所属组 | mysql |
| 错误日志文件 | /var/log/mysql/mysql.log |
主配置文件默认配置
| 配置项 | 功能说明 |
|---|---|
| datadir | 定义数据库目录位置 |
| socket | 定义socket文件名称及存放位置 |
| log-error | 定义错误日志文件名称及存放位置 |
| pid-file | 定义存放数据库服务进程pid号文件名及存放位置 |
# 主配置文件
[root@mysql50 ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
2. 增删改查
2.1 insert 插入表记录
2.1.1 不指定列名插入
- 不指定列名插入记录（必须给所有列赋值）
- insert into 库名.表名 values(值列表)； //插入一行
- insert into 库名.表名 values(值列表),(值列表)； //插入多行
mysql> insert into tarena.user values
-> (101,'user1','x',3001,3001,'test','/home/user1','/bin/sh'),
-> (101,'user2','x',3002,3002,'test','/home/user1','/bin/false');
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0
6.1.2 指定列名插入
- 指定列名插入记录（仅须给指定列赋值）
- insert into 库名.表名(列名列表) values(值列表)； //插入一行
- insert into 库名.表名(列名列表) values(值列表),(值列表);//插多行
mysql> insert into tarena.user(name,uid,shell) values
-> ('user3','3003','/sbin/nologin'),
-> ('user4',3004,'/bin/bash');
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
6.1.3 使用select查询结果赋值
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
4 rows in set (0.00 sec)
mysql> insert into tarena.user(name) (select user from mysql.user);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
# 查看插入后的数据
mysql> select * from tarena.user where name like "mysql%" or name="root";
+----+------------------+----------+------+------+--------------+----------------+------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------------------+----------+------+------+--------------+----------------+------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 26 | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false |
| 44 | mysql.infoschema | NULL | NULL | NULL | NULL | NULL | NULL |
| 45 | mysql.session | NULL | NULL | NULL | NULL | NULL | NULL |
| 46 | mysql.sys | NULL | NULL | NULL | NULL | NULL | NULL |
| 47 | root | NULL | NULL | NULL | NULL | NULL | NULL |
+----+------------------+----------+------+------+--------------+----------------+------------+
6 rows in set (0.00 sec)
6.1.4 使用set命令赋值
# 实际情况下用set命令不常见
mysql> insert into tarena.user set name="yaya" , uid=99 , gid=99 ;
Query OK, 1 row affected (0.06 sec)
mysql> select * from tarena.user where name="yaya";
+----+------+----------+------+------+---------+---------+-------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-------+
| 28 | yaya | NULL | 99 | 99 | NULL | NULL | NULL |
+----+------+----------+------+------+---------+---------+-------+
1 row in set (0.00 sec)
2.2 delete 删除表记录
delete from 库名.表名 where 条件
# 删除的时候务必看好，一定要加where条件，要不然你会后悔的
mysql> delete from tarena.user where name is null;
Query OK, 2 rows affected (0.08 sec)
2.3 truncate 删除表记录
DELETE：用于删除表中的部分或全部数据。
可以使用
WHERE子句指定删除条件，如果不加条件，则删除表中的所有数据。示例：
DELETE FROM table_name WHERE condition; -- 删除满足条件的行 DELETE FROM table_name; -- 删除表中所有数据
TRUNCATE：仅用于删除表中的全部数据，不能指定条件。
相当于“清空”表，但保留表的结构。
示例：
TRUNCATE TABLE table_name;
2.4 update 修改表记录
update 库名.表名 set 字段1=值,字段2=值 where 条件
# 改的时候一定要加上where条件，要不然会影响整个表
mysql> update tarena.user
-> set homedir='/home/user5',shell='/bin/bash'
-> where name='user5';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2.5 select 查询
2.5.1 select语法
语法格式一：select 字段列表 from 库名.表名;
语法格式二：select 字段列表 from 库名.表名 where 筛选条件;
2.5.2 条件筛选
数值比较
< <= > >= = !=
字符串比较
- =
- !=
范围匹配
select 字段名 from 库名.表名 where 字段名 not in ("值1","值2");
in （值列表） //在…里
not in （值列表） //不在…里
between 数字1 and 数字2 //在…之间
like 模糊匹配
select 字段名 from 库名.表名 where 字段名 like "通配符";
- _ 表示 1个字符
- % 表示零个或多个字符
regexp 正则匹配
select 字段名 from 库名.表名 where 字段名 regexp '正则表达式';
- ^ 匹配行首
- $ 匹配行尾
- [] 匹配范围内任意一个
- 前边的表达式出现零次或多次
- | 或者
- . 任意一个字符
逻辑运算
逻辑与 and （&&） 多个判断条件必须同时成立
逻辑或 or （||） 多个判断条件其中某个条件成立即可
在这里插入图片描述
逻辑非 not （!） 取反 ， 放在表达式前面
逻辑与and 优先级高于逻辑或 or
（）提高优先级
示例：
# 没加（） 的查询结果 mysql> select name , uid from tarena.user where name = "root" or name = "bin" and uid = 1 ; +------+------+ | name | uid | +------+------+ | root | 0 | | bin | 1 | +------+------+ 2 rows in set (0.00 sec) # 加（）的查询结果 mysql> select name , uid from tarena.user where (name = "root" or name = "bin") and uid = 1 ; +------+------+ | name | uid | +------+------+ | bin | 1 | +------+------+ 1 row in set (0.00 sec) mysql>
空与非空
select 字段名 from 库名.表名 where 字段名 is null;
- is null表头下没有数据 ----> 空
- is not null表头下有数据 ----> 非空
查询结果的显示结果
3. 常用函数
格式一：select 函数(表头名) from 库名.表名;
格式一：select 函数(表头名) from 库名.表名 where 条件;
3.1 字符函数
作用：处理字符或字符类型的表头
- length(str) 返回字符串长度，以字节为单位 （一个汉字3个字节）
- char_length(str) 返回字符串长度，以字符单位
- upper(str)和ucase(str) 将str中的字母全部转换成大写
- lower(str)和lcase(str) 将str中的字母全部转换成小写
- substr(‘hello’,‘2’,‘3’) 从hello的第2个字符e开始取出3个字符长度，结果为ell
- instr(‘hello’,‘l’) 返回’l’参数在’hello’参数内的位置，结果为3
- trim(s) 返回字符串s删除了两边空格之后的字符
3.2 数字函数
作用：处理数字或数值类型的表头
- abs(x) 返回x的绝对值
- pi() 返回圆周率π，默认显示6位小数
- mod(x,y) 返回x被y除后的余数
- ceil(x)、ceiling(x) 向上取整
- floor(x) 向下取整
- round(9.5) 四舍五入取整为10
- round(9.58585,3) 代表取小数点后三位为9.586
mysql> select ceil(9.6),ceil(9.2),floor(9.2),floor(9.6);
+-----------+-----------+------------+------------+
| ceil(9.6) | ceil(9.2) | floor(9.2) | floor(9.6) |
+-----------+-----------+------------+------------+
| 10 | 10 | 9 | 9 |
+-----------+-----------+------------+------------+
1 row in set (0.00 sec)
mysql> select round(9.2),round(9.6),round(pi(),3);
+------------+------------+---------------+
| round(9.2) | round(9.6) | round(pi(),3) |
+------------+------------+---------------+
| 9 | 10 | 3.142 |
+------------+------------+---------------+
1 row in set (0.01 sec)
3.3 日期函数
mysql> select now(),curdate(),curtime();
+---------------------+------------+-----------+
| now() | curdate() | curtime() |
+---------------------+------------+-----------+
| 2024-12-12 10:28:07 | 2024-12-12 | 10:28:07 |
+---------------------+------------+-----------+
1 row in set (0.00 sec)
mysql> select date(now()),time(now());
+-------------+-------------+
| date(now()) | time(now()) |
+-------------+-------------+
| 2024-12-12 | 10:33:19 |
+-------------+-------------+
1 row in set (0.00 sec)
mysql> select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());
+-------------+--------------+------------+-------------+---------------+---------------+
| year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) |
+-------------+--------------+------------+-------------+---------------+---------------+
| 2024 | 12 | 12 | 10 | 34 | 25 |
+-------------+--------------+------------+-------------+---------------+---------------+
1 row in set (0.00 sec)
mysql> select week(now()),weekday(now()),monthname(now()),dayname(now());
+-------------+----------------+------------------+----------------+
| week(now()) | weekday(now()) | monthname(now()) | dayname(now()) |
+-------------+----------------+------------------+----------------+
| 49 | 3 | December | Thursday |
+-------------+----------------+------------------+----------------+
1 row in set (0.00 sec)
mysql> select quarter(now()),dayofyear(now()),dayofmonth(now());
+----------------+------------------+-------------------+
| quarter(now()) | dayofyear(now()) | dayofmonth(now()) |
+----------------+------------------+-------------------+
| 4 | 347 | 12 |
+----------------+------------------+-------------------+
1 row in set (0.00 sec)
3.4 聚集函数
作用：数据统计
聚合函数还是挺重要的
- avg(表头名) //计算平均值
- sum(表头名) //求和
- min(表头名) //获取最小值
- max(表头名) //获取最大值
- count(表头名) //统计字段值个数
3.5 数学计算
作用：对行中的列做计算
| 符号 | 用途 | 例子 |
|---|---|---|
| + | 加法 | uid + gid |
| - | 减法 | uid - gid |
| * | 乘法 | uid * gid |
| / | 除法 | uid / gid |
| % | 取余数(求模) | uid % gid |
| () | 提高优先级 | (uid + gid) / 2 |
mysql> select employee_id,date,basic,bonus from tarena.salary where employee_id=8 and year(date)=2019;
+-------------+------------+-------+-------+
| employee_id | date | basic | bonus |
+-------------+------------+-------+-------+
| 8 | 2019-01-10 | 23093 | 1000 |
| 8 | 2019-02-10 | 23093 | 4000 |
| 8 | 2019-03-10 | 23093 | 10000 |
| 8 | 2019-04-10 | 23093 | 1000 |
| 8 | 2019-05-10 | 23093 | 4000 |
| 8 | 2019-06-10 | 23093 | 7000 |
| 8 | 2019-07-10 | 23093 | 6000 |
| 8 | 2019-08-10 | 23093 | 1000 |
| 8 | 2019-09-10 | 23093 | 1000 |
| 8 | 2019-10-10 | 23093 | 8000 |
| 8 | 2019-11-10 | 23093 | 10000 |
| 8 | 2019-12-10 | 24247 | 10000 |
+-------------+------------+-------+-------+
12 rows in set (0.00 sec)
mysql> select employee_id,date,basic+bonus 月薪 from tarena.salary where employee_id=8 and year(date)=2019;
+-------------+------------+--------+
| employee_id | date | 月薪 |
+-------------+------------+--------+
| 8 | 2019-01-10 | 24093 |
| 8 | 2019-02-10 | 27093 |
| 8 | 2019-03-10 | 33093 |
| 8 | 2019-04-10 | 24093 |
| 8 | 2019-05-10 | 27093 |
| 8 | 2019-06-10 | 30093 |
| 8 | 2019-07-10 | 29093 |
| 8 | 2019-08-10 | 24093 |
| 8 | 2019-09-10 | 24093 |
| 8 | 2019-10-10 | 31093 |
| 8 | 2019-11-10 | 33093 |
| 8 | 2019-12-10 | 34247 |
+-------------+------------+--------+
12 rows in set (0.00 sec)
mysql> select employee_id,sum(basic+bonus) 年薪 from tarena.salary where employee_id=8 and year(date)=2019;
+-------------+--------+
| employee_id | 年薪 |
+-------------+--------+
| 8 | 341270 |
+-------------+--------+
1 row in set (0.00 sec)
mysql> select * from tarena.employees limit 1;
+-------------+--------+------------+------------+------------------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+--------+------------+------------+------------------+--------------+---------+
| 1 | 梁伟 | 2018-06-21 | 1971-08-19 | liangwei@tedu.cn | 13591491431 | 1 |
+-------------+--------+------------+------------+------------------+--------------+---------+
1 row in set (0.00 sec)
mysql> select name,year(now()) - year(hire_date) as 工龄,
-> year(now()) - year(birth_date) as 年龄
-> from tarena.employees
-> where employee_id=1;
+--------+----------+------+
| name | 工龄 | 年龄 |
+--------+----------+------+
| 梁伟 | 6 | 53 |
+--------+----------+------+
1 row in set (0.00 sec)
3.6 if 函数
# if(条件，v1，v2)如果条件是TRUE则返回v1，否则返回v2
mysql> select if(1 = 2 , "a","b");
+---------------------+
| if(1 = 2 , "a","b") |
+---------------------+
| b |
+---------------------+
1 row in set (0.00 sec)
mysql> select if( 1 = 1 , "a","b");
+---------------------+
| if(1 = 1 , "a","b") |
+---------------------+
| a |
+---------------------+
1 row in set (0.00 sec)
# ifnull(v1,v2)如果v1不为NULL，则返回v1，否则返回v2
mysql> select ifnull("abc","xxx");
+---------------------+
| ifnull("abc","xxx") |
+---------------------+
| abc |
+---------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,"xxx");
+--------------------+
| ifnull(null,"xxx") |
+--------------------+
| xxx |
+--------------------+
1 row in set (0.00 sec)
# 根据UID判断用户类型
mysql> select name as 姓名 , uid as UID号 ,
if(uid < 1000 , "系统用户","创建用户") as 用户类型 from tarena.user;
+-----------------+-------+--------------+
| 姓名 | UID号 | 用户类型 |
+-----------------+-------+--------------+
| root | 0 | 系统用户 |
| bin | 1 | 系统用户 |
| daemon | 2 | 系统用户 |
| adm | 3 | 系统用户 |
| lp | 4 | 系统用户 |
| sync | 5 | 系统用户 |
| shutdown | 6 | 系统用户 |
| halt | 7 | 系统用户 |
| mail | 8 | 系统用户 |
| operator | 11 | 系统用户 |
| games | 12 | 系统用户 |
| ftp | 14 | 系统用户 |
| nobody | 99 | 系统用户 |
| systemd-network | 192 | 系统用户 |
| dbus | 81 | 系统用户 |
| polkitd | 999 | 系统用户 |
| sshd | 74 | 系统用户 |
| postfix | 89 | 系统用户 |
| chrony | 998 | 系统用户 |
| rpc | 32 | 系统用户 |
| rpcuser | 29 | 系统用户 |
| nfsnobody | 65534 | 创建用户 |
| haproxy | 188 | 系统用户 |
| plj | 1000 | 创建用户 |
| apache | 48 | 系统用户 |
| mysql | 27 | 系统用户 |
| bob | NULL | 创建用户 |
+-----------------+-------+--------------+
27 rows in set (0.00 sec)
3.7 case 函数
命令格式:
CASE 表头名
WHEN 值1 THEN 输出结果
WHEN 值2 THEN 输出结果
WHEN 值3 THEN 输出结果
ELSE 输出结果
END
或
CASE
WHEN 判断条件1 THEN 输出结果
WHEN 判断条件2 THEN 输出结果
WHEN 判断条件3 THEN 输出结果
ELSE 输出结果
END
或
CASE
WHEN 表头名 in ("值1","值2","值3") THEN 输出结果
ELSE 输出结果
END
# 如果表头名等于某个值，则返回对应位置then后面的值并结束判断，
# 如果与所有值都不相等，则返回else后面的结果并结束判断
示例：
mysql> select * from tarena.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
+---------+-----------+
8 rows in set (0.03 sec)
# 第一种方法
mysql> select dept_id,dept_name,
-> case dept_name
-> when '运维部' then '技术部门'
-> when '开发部' then '技术部门'
-> when '测试部' then '技术部门'
-> else '非技术部门'
-> end as 部门类型 from tarena.departments;
+---------+-----------+-----------------+
| dept_id | dept_name | 部门类型 |
+---------+-----------+-----------------+
| 1 | 人事部 | 非技术部门 |
| 2 | 财务部 | 非技术部门 |
| 3 | 运维部 | 技术部门 |
| 4 | 开发部 | 技术部门 |
| 5 | 测试部 | 技术部门 |
| 6 | 市场部 | 非技术部门 |
| 7 | 销售部 | 非技术部门 |
| 8 | 法务部 | 非技术部门 |
+---------+-----------+-----------------+
8 rows in set (0.00 sec)
# 第二种方法
mysql> select dept_id,dept_name,
-> case
-> when dept_name="运维部" then "技术部"
-> when dept_name="开发部" then "技术部"
-> when dept_name="测试部" then "技术部"
-> else "非技术部"
-> end as 部门类型 from tarena.departments;
+---------+-----------+--------------+
| dept_id | dept_name | 部门类型 |
+---------+-----------+--------------+
| 1 | 人事部 | 非技术部 |
| 2 | 财务部 | 非技术部 |
| 3 | 运维部 | 技术部 |
| 4 | 开发部 | 技术部 |
| 5 | 测试部 | 技术部 |
| 6 | 市场部 | 非技术部 |
| 7 | 销售部 | 非技术部 |
| 8 | 法务部 | 非技术部 |
+---------+-----------+--------------+
8 rows in set (0.00 sec)
# 第三种方法
mysql> select dept_id,dept_name,
-> case
-> when dept_name in ('运维部','开发部','测试部') then '技术部门'
-> else '非技术部门'
-> end as 部门类型 from tarena.departments;
+---------+-----------+-----------------+
| dept_id | dept_name | 部门类型 |
+---------+-----------+-----------------+
| 1 | 人事部 | 非技术部门 |
| 2 | 财务部 | 非技术部门 |
| 3 | 运维部 | 技术部门 |
| 4 | 开发部 | 技术部门 |
| 5 | 测试部 | 技术部门 |
| 6 | 市场部 | 非技术部门 |
| 7 | 销售部 | 非技术部门 |
| 8 | 法务部 | 非技术部门 |
+---------+-----------+-----------------+
8 rows in set (0.00 sec)
4. select 查询结果处理
4.1 SQL语句执行顺序
- FROM子句:确定数据来源，包括JOIN的表
- ON:执行JOIN条件
- JOIN:如果有JOIN，根据JOIN类型(如INNER、LEFT)，将相关行组合起来
- WHERE子句:过滤记录
- GROUP BY子句:根据指定的列分组记录，通常与聚合函数一起使用。
- 聚合函数:分组后对分组外其他字段进行聚合处理
- HAVING子句:过滤分组
- SELECT子句:选取特定的列
- DISTINCT子句:去除重复数据
- ORDERBY子句:最后对结果进行排序
- LIMIT子句:限制输出行数
4.2 group by 分组
语法：select 表头名，...... from 库名.表名 where 条件 group by 表头名；
注意事项：在使用 GROUP BY 时，SELECT 列表中的列要么是聚合函数的结果，要么必须出现在 GROUP BY 子句中。否则，MySQL 会报错（在严格模式下）。
mysql> select shell,count(name)
-> from tarena.user
-> where shell is not null
-> group by shell;
+----------------+-------------+
| shell | count(name) |
+----------------+-------------+
| /bin/bash | 2 |
| /sbin/nologin | 20 |
| /bin/sync | 1 |
| /sbin/shutdown | 1 |
| /sbin/halt | 1 |
| /bin/false | 1 |
+----------------+-------------+
6 rows in set (0.00 sec)
4.3 having 过滤
语法：select 表头名 from 库.表 where 筛选条件 having 筛选条件;
关于where与having区别:
对于表中真实的字段筛选用where
where 子句用于在分组之前筛选数据
having 子句用于在分组之后筛选分组结果
group by 后边只能用 having
mysql> select dept_id,count(name)
-> from tarena.employees
-> group by dept_id;
+---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
+---------+-------------+
8 rows in set (0.00 sec)
# 找出部门总人数少于10人的，并把总人数降序显示
mysql> select dept_id 部门编号,count(name) 总人数
-> from tarena.employees
-> group by 部门编号
-> having 总人数 < 10
-> order by 总人数 desc;
+--------------+-----------+
| 部门编号 | 总人数 |
+--------------+-----------+
| 6 | 9 |
| 1 | 8 |
| 3 | 6 |
| 2 | 5 |
| 8 | 3 |
+--------------+-----------+
5 rows in set (0.00 sec)
4.4 order by 排序
语法：select 表头名 from 库.表 where 筛选条件 order by 表头名 asc|desc
- asc：升序排序（默认）
- desc：降序排序
mysql> select name,uid,shell
-> from tarena.user
-> where id between 8 and 15
-> order by uid desc;
+-----------------+------+---------------+
| name | uid | shell |
+-----------------+------+---------------+
| systemd-network | 192 | /sbin/nologin |
| nobody | 99 | /sbin/nologin |
| dbus | 81 | /sbin/nologin |
| ftp | 14 | /sbin/nologin |
| games | 12 | /sbin/nologin |
| operator | 11 | /sbin/nologin |
| mail | 8 | /sbin/nologin |
| halt | 7 | /sbin/halt |
+-----------------+------+---------------+
8 rows in set (0.00 sec)
示例：
mysql> select * from tarena.salary limit 5;
+----+------------+-------------+-------+-------+
| id | date | employee_id | basic | bonus |
+----+------------+-------------+-------+-------+
| 2 | 2015-01-10 | 2 | 17000 | 10000 |
| 3 | 2015-01-10 | 3 | 8000 | 2000 |
| 4 | 2015-01-10 | 4 | 14000 | 9000 |
| 6 | 2015-01-10 | 6 | 14000 | 10000 |
| 7 | 2015-01-10 | 7 | 19000 | 10000 |
+----+------------+-------------+-------+-------+
5 rows in set (0.00 sec)
mysql> select employee_id 员工编号,sum(basic+bonus) 年薪
-> from tarena.salary
-> where year(date)=2018
-> group by 员工编号
-> having 年薪 > 350000
-> order by 年薪 desc;
+--------------+--------+
| 员工编号 | 年薪 |
+--------------+--------+
| 31 | 374923 |
| 117 | 374923 |
| 37 | 362981 |
| 68 | 360923 |
| 48 | 359923 |
| 61 | 357981 |
| 25 | 353027 |
+--------------+--------+
7 rows in set (0.01 sec)
4.5 limit 分页
作用：限制查询结果显示行数（默认显示全部的查询结果）
# SELECT语句 LIMIT 数字; //显示查询结果前多少条记录
# SELECT语句 LIMIT 数字1,数字2; //显示指定范围内的查询记录
# 数字1 表示起始行 （0表示第1行） 数字2表示总行数
mysql> select id,name from tarena.user limit 5;
+----+--------+
| id | name |
+----+--------+
| 1 | root |
| 2 | bin |
| 3 | daemon |
| 4 | adm |
| 5 | lp |
+----+--------+
5 rows in set (0.00 sec)
mysql> select id,name from tarena.user limit 0,2;
+----+------+
| id | name |
+----+------+
| 1 | root |
| 2 | bin |
+----+------+
2 rows in set (0.00 sec)
mysql> select id,name from tarena.user limit 1,2;
+----+--------+
| id | name |
+----+--------+
| 2 | bin |
| 3 | daemon |
+----+--------+
2 rows in set (0.00 sec)
5. select 连接查询
5.1 连接查询概述
连接查询也叫多表查询，常用于查询表头来自于多张表，通过不同连接方式把多张表重新组成一张新的临时表，再对临时表做数据处理
- 内连接（INNER JOIN）：只返回匹配的记录。
- 左连接（LEFT JOIN）：返回左表的所有记录，即使右表中没有匹配的记录。
- 右连接（RIGHT JOIN）：返回右表的所有记录，即使左表中没有匹配的记录。
- 全连接（FULL JOIN）：返回两个表的所有记录，无论是否匹配（MySQL不支持全连接，但可以通过
UNION实现）
[!CAUTION]
在实际生产环境中，通常建议连表查询的表数量不超过5张。例如，阿里巴巴的数据库规范中明确禁止超过3张表的
JOIN操作。这种限制主要是基于以下考虑：
- 性能问题：多表连接会显著增加查询的复杂性，导致查询效率下降。每增加一个表，查询的笛卡尔积会呈指数级增长。
- 资源消耗：多表连接会占用更多的内存和CPU资源，尤其是当
join_buffer_size设置不合理时，可能导致服务器内存溢出。- 可维护性：过多的表连接会使SQL语句难以维护和优化。
下面的示例主要围绕着如下的表来操作：
5.2 内连接
特点
匹配行：内连接只返回两个表中连接条件相匹配的行。
性能：通常比外连接（LEFT JOIN 或 RIGHT JOIN）更高效，因为它只需要处理匹配的行。
5.2.1 等值连接
- 使用相等判断做连接条件，表里必须有存储相同数据的表头，等值连接是最常见的。
select 表头名列表 from 表1 as 别名 inner join 表2 as 别名 on 连接条件 [where筛选条件] | [group by 分组] | [having分组后筛选] | [order by 排序列表]
# 给表名定义别名后，必须使用别名表示表名
# 查询工号小于6的员工的所有信息及所在的部门名称
mysql> select e.* , d.dept_name from employees e
-> inner join departments d on e.dept_id=d.dept_id
-> where e.employee_id < 6;
+-------------+-----------+------------+------------+----------------------+--------------+---------+-----------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id | dept_name |
+-------------+-----------+------------+------------+----------------------+--------------+---------+-----------+
| 1 | 梁伟 | 2018-06-21 | 1971-08-19 | liangwei@tedu.cn | 13591491431 | 1 | 人事部 |
| 2 | 郭岩 | 2010-03-21 | 1974-05-13 | guoyan@tedu.cn | 13845285867 | 1 | 人事部 |
| 3 | 李玉英 | 2012-01-19 | 1974-01-25 | liyuying@tedu.cn | 15628557234 | 1 | 人事部 |
| 4 | 张健 | 2008-09-17 | 1972-06-07 | zhangjian@tarena.com | 13835990213 | 1 | 人事部 |
| 5 | 郑静 | 2018-02-03 | 1997-02-14 | zhengjing@tedu.cn | 14508936730 | 1 | 人事部 |
+-------------+-----------+------------+------------+----------------------+--------------+---------+-----------+
5 rows in set (0.00 sec)
# 查询2018年总工资大于35万的员工名字，按2018年总工资降序排列
mysql> select e.name 姓名,year(date) 年份,sum(basic+bonus) 年薪
-> from employees e inner join salary s
-> on e.employee_id=s.employee_id where year(date)=2018
-> group by e.name,year(date)
-> having 年薪>350000 order by 年薪 desc;
+-----------+--------+--------+
| 姓名 | 年份 | 年薪 |
+-----------+--------+--------+
| 刘海燕 | 2018 | 374923 |
| 和林 | 2018 | 374923 |
| 朱淑兰 | 2018 | 362981 |
| 柴冬梅 | 2018 | 360923 |
| 范秀英 | 2018 | 359923 |
| 沈秀梅 | 2018 | 357981 |
| 邵佳 | 2018 | 353027 |
+-----------+--------+--------+
7 rows in set (0.01 sec)
5.2.2 非等直连接
- 不使用相等判断做连接条件，适合表里没有存储相同数据的表头时使用
select 表头名列表 from 表1 as 别名 inner join 表2 as 别名 on 连接条件 [where筛选条件] | [group by 分组] | [having分组后筛选] | [order by 排序列表]
# 查询工资等级表
mysql> select * from wage_grade;
+------+-------+-------+--------+
| id | grade | low | high |
+------+-------+-------+--------+
| 1 | A | 5000 | 8000 |
| 2 | B | 8001 | 10000 |
| 3 | C | 10001 | 15000 |
| 4 | D | 15001 | 20000 |
| 5 | E | 20001 | 100000 |
+------+-------+-------+--------+
5 rows in set (0.00 sec)
# 查询2018年12月员工基本工资级别
mysql> select s.employee_id, s.date, s.basic, g.grade
-> from salary as s inner join wage_grade as g
-> on s.basic between g.low and g.high
-> where year(s.date)=2018 and month(s.date)=12
-> having s.employee_id <10;
+-------------+------------+-------+-------+
| employee_id | date | basic | grade |
+-------------+------------+-------+-------+
| 1 | 2018-12-10 | 17016 | D |
| 2 | 2018-12-10 | 20662 | E |
| 3 | 2018-12-10 | 9724 | B |
| 4 | 2018-12-10 | 17016 | D |
| 5 | 2018-12-10 | 17016 | D |
| 6 | 2018-12-10 | 17016 | D |
| 7 | 2018-12-10 | 23093 | E |
| 8 | 2018-12-10 | 23093 | E |
| 9 | 2018-12-10 | 13369 | C |
+-------------+------------+-------+-------+
9 rows in set (0.00 sec)
# 查询2018年12月员工各基本工资级别的人数
mysql> select grade 工资级别,count(s.employee_id) 总人数
-> from salary s inner join wage_grade g
-> on s.basic between g.low and g.high
-> where year(date)=2018 and month(date)=12
-> group by 工资级别;
+--------------+-----------+
| 工资级别 | 总人数 |
+--------------+-----------+
| D | 32 |
| E | 33 |
| B | 12 |
| C | 30 |
| A | 13 |
+--------------+-----------+
5 rows in set (0.00 sec)
3连表连接示例：
# 查询2018年12月员工基本工资级别，员工需要显示姓名
mysql> select s.date 日期,e.name 姓名, basic 基本工资,grade 工资级别
-> from salary s inner join wage_grade g
-> on s.basic between g.low and g.high
-> inner join employees e on e.employee_id=s.employee_id
-> where year(date)=2018 and month(date)=12
-> limit 8;
+------------+-----------+--------------+--------------+
| 日期 | 姓名 | 基本工资 | 工资级别 |
+------------+-----------+--------------+--------------+
| 2018-12-10 | 梁伟 | 17016 | D |
| 2018-12-10 | 郭岩 | 20662 | E |
| 2018-12-10 | 李玉英 | 9724 | B |
| 2018-12-10 | 张健 | 17016 | D |
| 2018-12-10 | 郑静 | 17016 | D |
| 2018-12-10 | 牛建军 | 17016 | D |
| 2018-12-10 | 刘斌 | 23093 | E |
| 2018-12-10 | 汪云 | 23093 | E |
+------------+-----------+--------------+--------------+
8 rows in set (0.00 sec)
4连表示例：
[生产环境下禁止超过3张表JOIN操作,这里只是举个示例](###5.1 连接查询概述)
# 查询2018年12月发薪日期时的员工的姓名、部门、基本工资、工资级别
mysql> select e.name 姓名,dept_name 部门,
-> s.date 发薪日期,s.basic 基本工资,
-> g.grade 工资级别 from salary as s
-> inner join employees e
-> on s.employee_id=e.employee_id
-> inner join departments d
-> on e.dept_id=d.dept_id
-> inner join wage_grade as g
-> on s.basic between g.low and g.high
-> where year(s.date)=2018 and month(s.date)=12
-> and e.employee_id < 10;
+-----------+-----------+--------------+--------------+--------------+
| 姓名 | 部门 | 发薪日期 | 基本工资 | 工资级别 |
+-----------+-----------+--------------+--------------+--------------+
| 梁伟 | 人事部 | 2018-12-10 | 17016 | D |
| 郭岩 | 人事部 | 2018-12-10 | 20662 | E |
| 李玉英 | 人事部 | 2018-12-10 | 9724 | B |
| 张健 | 人事部 | 2018-12-10 | 17016 | D |
| 郑静 | 人事部 | 2018-12-10 | 17016 | D |
| 牛建军 | 人事部 | 2018-12-10 | 17016 | D |
| 刘斌 | 人事部 | 2018-12-10 | 23093 | E |
| 汪云 | 人事部 | 2018-12-10 | 23093 | E |
| 张建平 | 财务部 | 2018-12-10 | 13369 | C |
+-----------+-----------+--------------+--------------+--------------+
9 rows in set (0.00 sec)
5.3 外连接
分类
左外连接 left join
右外连接 right join
5.3.1 左外连接
select tb1.字段......,tb2.字段 from table1 as tb1 left join table2 as tb2 on tb1.字段=tb2.字段;
查询输出结果：返回左表的所有记录以及右表中匹配的记录，如果右表中没有匹配的记录，则结果集中对应的字段为NULL。
mysql> select * from tarena.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | 小卖部 |
| 10 | 行政部 |
| 11 | 公关部 |
+---------+-----------+
11 rows in set (0.00 sec)
mysql> select d.dept_id,d.dept_name,e.name
-> from departments d
-> left join employees e
-> on d.dept_id=e.dept_id
-> where e.name is null;
+---------+-----------+------+
| dept_id | dept_name | name |
+---------+-----------+------+
| 9 | 小卖部 | NULL |
| 10 | 行政部 | NULL |
| 11 | 公关部 | NULL |
+---------+-----------+------+
3 rows in set (0.01 sec)
5.3.2 右外连接
select tb1.字段......,tb2.字段 from table1 as tb1 right join table2 as tb2 on tb1.字段=tb2.字段;
查询输出结果：返回右表的所有记录以及左表中匹配的记录，如果左表中没有匹配的记录，则结果集中对应的字段为NULL。
mysql> select * from tarena.employees
-> where dept_id is null;
+-------------+------+-----------+------------+-------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+------+-----------+------------+-------+--------------+---------+
| 134 | bob | NULL | NULL | NULL | NULL | NULL |
| 135 | tom | NULL | NULL | NULL | NULL | NULL |
| 136 | lily | NULL | NULL | NULL | NULL | NULL |
+-------------+------+-----------+------------+-------+--------------+---------+
3 rows in set (0.00 sec)
mysql> select d.dept_id,d.dept_name,e.name
-> from departments d right join employees e
-> on d.dept_id=e.dept_id
-> where d.dept_name is null;
+---------+-----------+------+
| dept_id | dept_name | name |
+---------+-----------+------+
| NULL | NULL | bob |
| NULL | NULL | tom |
| NULL | NULL | lily |
+---------+-----------+------+
3 rows in set (0.00 sec)
5.3.3 全外连接
特点：
- 返回所有记录：全外连接会返回左表和右表的所有记录，即使它们之间没有匹配的行。
- 匹配与不匹配的记录：对于匹配的记录，会显示两个表中的字段值；对于不匹配的记录，不匹配的字段会显示为
NULL。 - 用途：全外连接常用于需要同时查看两个表中所有数据的场景，尤其是当不确定两个表之间是否存在匹配关系时。
语法格式：
(select语句) union (select语句); //去重
(select语句) union all (select语句); //不去重
# union 去掉查询结果中重复的行
mysql> (select employee_id,name from employees where employee_id <=5) union
-> (select employee_id,name from employees where employee_id <=6);
+-------------+-----------+
| employee_id | name |
+-------------+-----------+
| 1 | 梁伟 |
| 2 | 郭岩 |
| 3 | 李玉英 |
| 4 | 张健 |
| 5 | 郑静 |
| 6 | 牛建军 |
+-------------+-----------+
6 rows in set (0.01 sec)
#union all 不去重显示查询结果
mysql> (select employee_id,name from employees where employee_id <=5) union all
-> (select employee_id,name from employees where employee_id <=6);
+-------------+-----------+
| employee_id | name |
+-------------+-----------+
| 1 | 梁伟 |
| 2 | 郭岩 |
| 3 | 李玉英 |
| 4 | 张健 |
| 5 | 郑静 |
| 1 | 梁伟 |
| 2 | 郭岩 |
| 3 | 李玉英 |
| 4 | 张健 |
| 5 | 郑静 |
| 6 | 牛建军 |
+-------------+-----------+
11 rows in set (0.00 sec)
# 使用union将左右连接两个查询结果合并
mysql> select d.dept_id,d.dept_name,e.name
-> from departments d
-> left join employees e
-> on d.dept_id=e.dept_id
-> where e.name is null
-> union
-> select d.dept_id,d.dept_name,e.name
-> from departments d right join employees e
-> on d.dept_id=e.dept_id
-> where d.dept_name is null;
+---------+-----------+------+
| dept_id | dept_name | name |
+---------+-----------+------+
| 9 | 小卖部 | NULL |
| 10 | 行政部 | NULL |
| 11 | 公关部 | NULL |
| NULL | NULL | bob |
| NULL | NULL | tom |
| NULL | NULL | lily |
+---------+-----------+------+
6 rows in set (0.00 sec)
6. 嵌套查询
是指在一个完整的查询语句之中，包含若干个不同功能的小查询从而一起完成复杂查询的一种编写形式。
包含的查询放在（）里
包含的查询出现的位置
select之后
from之后
where
having之后
6.1 where 后
# 查看人事部的部门id
mysql> select dept_id from departments where dept_name='人事部';
+---------+
| dept_id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
# 查找employees表里 人事部的员工id
mysql> select employee_id from employees
-> where dept_id=(select dept_id from
-> departments where dept_name='人事部');
+-------------+
| employee_id |
+-------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+-------------+
8 rows in set (0.00 sec)
# 查找2018年12月人事部的员工的工资情况
mysql> select * from salary
-> where year(date)=2018 and month(date)=12
-> and employee_id in (
-> select employee_id from employees
-> where dept_id=(
-> select dept_id from departments
-> where dept_name='人事部'
-> )
-> );
+------+------------+-------------+-------+-------+
| id | date | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6252 | 2018-12-10 | 1 | 17016 | 7000 |
| 6253 | 2018-12-10 | 2 | 20662 | 9000 |
| 6254 | 2018-12-10 | 3 | 9724 | 8000 |
| 6255 | 2018-12-10 | 4 | 17016 | 2000 |
| 6256 | 2018-12-10 | 5 | 17016 | 3000 |
| 6257 | 2018-12-10 | 6 | 17016 | 1000 |
| 6258 | 2018-12-10 | 7 | 23093 | 4000 |
| 6259 | 2018-12-10 | 8 | 23093 | 2000 |
+------+------------+-------------+-------+-------+
8 rows in set (0.00 sec)
6.2 having 后
# 统计开发部员工总人数
mysql> select count(name) from employees where dept_id = (select
-> dept_id from departments where dept_name="开发部");
+-------------+
| count(name) |
+-------------+
| 55 |
+-------------+
1 row in set (0.00 sec)
# 统计每个部门总人数
mysql> select dept_id , count(name)
-> from employees group by dept_id;
+---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
+---------+-------------+
8 rows in set (0.00 sec)
# 输出总人数比开发部总人数少的部门名及总人数
mysql> select e.dept_id ,d.dept_name,count(name) total
-> from employees e
-> inner join departments d on e.dept_id=d.dept_id
-> group by e.dept_id having total < (
-> select count(name) from employees where dept_id=(
-> select dept_id from departments
-> where dept_name='开发部')
-> );
+---------+-----------+-------+
| dept_id | dept_name | total |
+---------+-----------+-------+
| 1 | 人事部 | 8 |
| 2 | 财务部 | 5 |
| 3 | 运维部 | 6 |
| 5 | 测试部 | 12 |
| 6 | 市场部 | 9 |
| 7 | 销售部 | 35 |
| 8 | 法务部 | 3 |
+---------+-----------+-------+
7 rows in set (0.00 sec)
6.3 from 后
# 在from之后做嵌套，很少用
# 查询3号部门及其部门内员工的编号，名字和生日
mysql> select dept_id,dept_name,name,birth_date from
-> (select d.dept_name,e.* from departments d
-> inner join employees e
-> on d.dept_id=e.dept_id) as tmp #必须要起别名，否则报错
-> where dept_id=3;
+---------+-----------+-----------+------------+
| dept_id | dept_name | name | birth_date |
+---------+-----------+-----------+------------+
| 3 | 运维部 | 廖娜 | 1982-06-22 |
| 3 | 运维部 | 窦红梅 | 1971-09-09 |
| 3 | 运维部 | 聂想 | 1999-06-05 |
| 3 | 运维部 | 陈阳 | 1991-04-10 |
| 3 | 运维部 | 戴璐 | 1975-05-16 |
| 3 | 运维部 | 陈斌 | 2000-01-22 |
+---------+-----------+-----------+------------+
6 rows in set (0.00 sec)
6.4 select 后
# 查询每个部门的人数
mysql> select d.*,
-> (select count(name) from employees e
-> where e.dept_id=d.dept_id) as 部门人数
-> from departments d;
+---------+-----------+--------------+
| dept_id | dept_name | 部门人数 |
+---------+-----------+--------------+
| 1 | 人事部 | 8 |
| 2 | 财务部 | 5 |
| 3 | 运维部 | 6 |
| 4 | 开发部 | 55 |
| 5 | 测试部 | 12 |
| 6 | 市场部 | 9 |
| 7 | 销售部 | 35 |
| 8 | 法务部 | 3 |
| 9 | 小卖部 | 0 |
| 10 | 行政部 | 0 |
| 11 | 公关部 | 0 |
+---------+-----------+--------------+
11 rows in set (0.00 sec)
7. 数据类型
7.1 字符类型
一般用来存储姓名，收货地址，工作单位，家庭住址
| 类型 | 名称 | 说明 |
|---|---|---|
| char(字符个数) | 定长类型 | 最多255个字符 |
| varchar(字符个数) | 变长类型 | 最多65532个字符 |
- char类型说明：不能指定字符个数时在左右边用空格补全字符个数，超出时无法写入数据。
- varchar类型说明：按数据实际大小分配存储空间，字符个数超出时无法写入数据。
7.2 数型类型
仅存储数值的整数部分
| 类型 | 名称 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| tinyint | 微小整数 | -128~127 | 0~255 |
| smallint | 小整数 | -32768~32767 | 0~65535 |
| mediumint | 中整数 | -223~223-1 | 0~224-1 |
| int | 大整数 | -231~231-1 | 0~232-1 |
| bingint | 极大整数 | -263~263-1 | 0~264-1 |
| unsigned | 使用无符号存储范围 |
7.3 浮点类型
存储有小数点的数
| 类型 | 名称 | 存储空间 |
|---|---|---|
| float | 单精度 | 4字节 |
| double | 双精度 | 8字节 |
7.4 枚举类型
- enum类型 单选
| 类型 | 说明 |
|---|---|
| enum(值列表) | 字段值仅能在范围内选择1个值 |
- set类型 多选
| 类型 | 说明 |
|---|---|
| set(值列表) | 字段值能在范围内选择1个或多个值 |
mysql> create table studb.t8(
-> 姓名 char(10),
-> 性别 enum("男","女","保密"),
-> 爱好 set("帅哥","金钱","吃","睡")
-> );
Query OK, 0 rows affected (0.29 sec)
mysql> desc studb.t8 ;
+--------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 性别 | enum('男','女','保密') | YES | | NULL | |
| 爱好 | set('帅哥','金钱','吃','睡') | YES | | NULL | |
+--------+------------------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into studb.t8 values ("乔碧罗","女","帅哥,吃");
Query OK, 1 row affected (0.09 sec)
mysql> select * from studb.t8;
+--------+--------+------------+
| 姓名 | 性别 | 爱好 |
+--------+--------+------------+
| 乔碧罗 | 女 | 帅哥,吃 |
+--------+--------+------------+
1 row in set (0.00 sec)
7.5 日期类型
存储如生日，注册时间，出生年份，入职日期
| 类型 | 名称 | 范围 | 赋值格式 |
|---|---|---|---|
| year | 年 | 1901~2155 | YYYY |
| date | 日期 | 0001-01-01~9999-12-31 | YYYYMMDD |
| time | 时间 | 01:00:00-23:59:59 | HHMMSS |
| datetime | 日期时间 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYYMMDDHHMMSS |
| timestamp | 1970-01-01 00:00:00~2038-01-19 00:00:00 |
mysql> create table studb.t6(
-> 姓名 char(10),
-> 生日 date,
-> 出生年份 year,
-> 家庭聚会 datetime ,
-> 聚会地点 varchar(15),
-> 上班时间 time);
Query OK, 0 rows affected (0.65 sec)
mysql> desc studb.t6;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 生日 | date | YES | | NULL | |
| 出生年份 | year | YES | | NULL | |
| 家庭聚会 | datetime | YES | | NULL | |
| 聚会地点 | varchar(15) | YES | | NULL | |
| 上班时间 | time | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> insert into studb.t6 values
-> ("坤坤",19980228,1998,20250412090000,"郑州金水",100000);
Query OK, 1 row affected (0.11 sec)
mysql> select * from studb.t6;
+--------+------------+--------------+---------------------+--------------+--------------+
| 姓名 | 生日 | 出生年份 | 家庭聚会 | 聚会地点 | 上班时间 |
+--------+------------+--------------+---------------------+--------------+--------------+
| 坤坤 | 1998-02-28 | 1998 | 2025-04-12 09:00:00 | 郑州金水 | 10:00:00 |
+--------+------------+--------------+---------------------+--------------+--------------+
1 row in set (0.00 sec)
8. 库操作/表操作
8.1 建删库
建库
# if not exists 避免建库重名报错，也可以不加
mysql> create database if not exists 数据库名
删库
# if exists 避免删除的库名不存在而报错,也可以不加
mysql> drop database if exists 数据库名
8.2 建删表
建表语法
create table 库名.表名
(
表头名1 数据类型,
表头名2 数据类型,
表头名3 数据类型,
......
);
建表示例：
# 注意：表必须存放在库里
mysql> create database studb; //建库
Query OK, 1 row affected (0.11 sec)
mysql> create table studb.stu( //建表
-> name char(10),
-> class char(9),
-> gender char(4),
-> age int
-> );
Query OK, 0 rows affected (1.17 sec)
# 查看表头
mysql> desc studb.stu;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| class | char(9) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除表
mysql> drop table studb.stu;
# 再删除库
mysql> drop database studb;
8.3 alter 修改表
| 操作命令 | 说明 |
|---|---|
| add | 添加新字段，一起添加多个字段使用，以,分隔 add命令（first是把表头添加到首位，after是添加在指定表头名的下方） |
| modify | 修改字段类型，也可以修改字段的位置 |
| change | 修改字段名，也可以同时修改字段类型 |
| rename | 修改表名 |
| drop | 删除字段，删除多个字段使用以,分隔drop命令 |
# 查看表头
mysql> desc studb.stu;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| class | char(9) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 修改表名
mysql> alter table studb.stu rename studb.stuinfo;
Query OK, 0 rows affected (0.28 sec)
mysql> alter table studb.stuinfo
-> drop age, # 删除age字段
-> add email char(30), # 添加emali字段（默认添加最下面）
-> add school char(10) after name, # 在name字段下面添加school字段
-> add number char (10) first; # 在首位添加number字段
Query OK, 0 rows affected (1.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc studb.stuinfo;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(10) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| school | char(10) | YES | | NULL | |
| class | char(9) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| email | char(30) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table studb.stuinfo
-> modify email varchar(50), # 修改emali字段的数据类型
-> change class 班级 char(8); # 修改class字段的字段名和数据类型
Query OK, 0 rows affected (1.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc studb.stuinfo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(9) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| school | char(10) | YES | | NULL | |
| 班级 | char(8) | YES | | NULL | |
| gender | char(4) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 将gender字段放在number字段的下面，并修改数据类型
mysql> alter table studb.stuinfo
-> modify gender varchar (4) after number;
Query OK, 0 rows affected (1.72 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc studb.stuinfo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(9) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| school | char(10) | YES | | NULL | |
| 班级 | char(8) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
8.4 复制表
- 复制表结构及数据
create table 库.表 select 列名 from 库.表 [where 条件]
[!CAUTION]
原表的key 不会复制给新表，新表数据与select语句决定
- 仅复制表结构
create table 库.表 like 库.表
[!CAUTION]
仅仅复制表头，源表的key 会被复制
mysql> create table studb.salary select * from tarena.salary;
Query OK, 8055 rows affected (3.51 sec)
Records: 8055 Duplicates: 0 Warnings: 0
# 数据复制了，但是key不会被复制
mysql> desc studb.salary;
+-------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| id | int | NO | | 0 | |
| date | date | YES | | NULL | |
| employee_id | int | YES | | NULL | |
| basic | int | YES | | NULL | |
| bonus | int | YES | | NULL | |
+-------------+------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 仅仅复制表头，源表的key会被复制
mysql> create table studb.salary2 like tarena.salary;
Query OK, 0 rows affected (0.56 sec)
mysql> desc studb.salary2;
+-------------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| date | date | YES | | NULL | |
| employee_id | int | YES | MUL | NULL | |
| basic | int | YES | | NULL | |
| bonus | int | YES | | NULL | |
+-------------+------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
# 数据没被复制
mysql> select count(*) from studb.salary2;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
9. 数据导入/导出
9.1 数据导入
操作步骤：
拷贝文件到检索目录 ---> 建表 --->> 导入数据
# 查看默认检索目录
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
mysql> exit
[root@mysql50 ~]# ls -ld /var/lib/mysql-files/
drwxr-x--- 2 mysql mysql 6 Sep 22 2021 /var/lib/mysql-files/
[root@mysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
secure_file_priv=/myload 添加此行
:wq
[root@mysql50 ~]# mkdir /myload
[root@mysql50 ~]# chown mysql /myload
# 关闭selinux
[root@mysql50 ~]# setenforce 0 //禁用selinux
[root@mysql50 ~]# systemctl restart mysqld
# 进入mysql查看检索目录
mysql> show variables like "secure_file_priv";
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| secure_file_priv | /myload/ |
+------------------+----------+
1 row in set (0.01 sec)
# 建表（ 根据导入的文件内容 创建表头）,这里是拿/etc/passwd举例
mysql> create table db1.user3(
-> name varchar(30),
-> password char(1),
-> uid int ,
-> gid int ,
-> comment varchar(200),
-> homedir varchar(50),
-> shell varchar(30)
-> );
Query OK, 0 rows affected (0.41 sec)
# 将/etc/passwd拷贝到检索目录
mysql> system cp /etc/passwd /myload/
mysql> system ls /myload/ 查看文件
passwd
mysql> load data infile '/检索目录名/文件名' into table 库名.表名
-> fields terminated by '分隔符'
-> lines terminated by '\';
9.2 数据导出
语法格式1：
mysql> select * from 库名.表名 into outfile '/检索目录名/文件名';
语法格式2：
mysql> select * from 库名.表名
-> where 条件 into outfile '/检索目录名/文件名'
-> fields terminated by ':'
-> lines terminated by '/n';
10. 约束分类
约束是一种限制，设置在表头上，用来控制表头的赋值，包括以下几种：
NOT NULL ：非空，用于保证该字段的值不能为空。
DEFAULT：默认值，用于保证该字段有默认值。
UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。
10.1 not null 非空
用于保证该字段的值不能为空
# 创建带非空的字段的表
mysql> create table db1.t33(
-> name char(10) not null,
-> class char(7));
Query OK, 0 rows affected (0.65 sec)
# name字段为空，报错
mysql> insert into db1.t33 values(null,"linux");
ERROR 1048 (23000): Column 'name' cannot be null
# name字段赋值，成功插入
mysql> insert into db1.t33 values("hehe","linux");
Query OK, 1 row affected (0.08 sec)
mysql> select * from db1.t33;
+------+-------+
| name | class |
+------+-------+
| hehe | linux |
+------+-------+
1 row in set (0.01 sec)
10.2 default 默认值
用于保证该字段有默认值
mysql> create table db1.t31(
-> name char(10) not null,
-> class char(7) default "Go",
-> likes set ("吃","喝","辣","撒") not null default "吃,喝");
Query OK, 0 rows affected (0.55 sec)
mysql> desc db1.t31;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(10) | NO | | NULL | |
| class | char(7) | YES | | Go | |
| likes | set('吃','喝','辣','撒') | NO | | 吃,喝 | |
+-------+------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into db1.t31 values(null,null,null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into db1.t31 values("hehe",null,null);
ERROR 1048 (23000): Column 'likes' cannot be null
mysql> insert into db1.t31(name) values("jim");
Query OK, 1 row affected (0.19 sec)
mysql> insert into db1.t31 values("lucy","linux","撒");
Query OK, 1 row affected (0.24 sec)
mysql> select * from db1.t31;
+------+-------+---------+
| name | class | likes |
+------+-------+---------+
| jim | Go | 吃,喝 |
| lucy | linux | 撒 |
+------+-------+---------+
2 rows in set (0.00 sec)
10.3 unique 唯一索引
用于保证该字段的值具有唯一性，可以为空
mysql> create table db1.t43 (
-> 姓名 char(10),
-> 护照 char(18) unique);
Query OK, 0 rows affected (1.26 sec)
mysql> desc db1.t43;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 护照 | char(18) | YES | UNI | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 可以赋空值
mysql> insert into db1.t43 values("bob",null);
Query OK, 1 row affected (0.08 sec)
mysql> insert into db1.t43 values("tom",888);
Query OK, 1 row affected (0.20 sec)
# 不可以赋重复的值
mysql> insert into db1.t43 values("haha",888);
ERROR 1062 (23000): Duplicate entry '888' for key 't43.护照'
mysql> insert into db1.t43 values("haha",666);
Query OK, 1 row affected (0.09 sec)
mysql> select * from db1.t43;
+--------+--------+
| 姓名 | 护照 |
+--------+--------+
| bob | NULL |
| tom | 888 |
| haha | 666 |
+--------+--------+
3 rows in set (0.00 sec)
10.4 primary key 主键
用于保证该字段的值具有唯一性并且非空
主键使用规则：
表头值不允许重复，不允许赋NULL值
一个表中只能有一个primary key 表头
多个表头做主键，称为复合主键，必须一起创建和删除
主键标志PRI
主键通常与auto_increment连用
通常把表中唯一标识记录的表头设置为主键[行号表头]
创建主键的格式
create table 库.表 (
字段名 数据类型 primary key,
字段名 数据类型,
......);
或
create table 库.表 (
字段名 数据类型,
字段名 数据类型,
......
primary key (字段名)
);
删除主键命令格式 alter table 库.表 drop primary key;
添加主键命令格式 alter table 库.表 add primary key (字段名);
复合主键
多个字段一起做主键，相当于多个字段共享一个主键
复合主键的值不允许同时重复
创建复合主键语法格式：
create table 库.表(
字段名 数据类型,
字段名 数据类型,
......
primary key(字段名1,字段名2)
);
删除复合主键：alter table 库名.表名 drop primary key;
添加复合主键：alter table 库.表 add primary key (字段名1,字段名2);
与 auto_increment 连用
作用：自增长，通过表头自加1计算结果赋值
通常与数据类型整型类型连用，如统计行号
想要让表头有自增长，表头必须有主键设置才可以
当给自增长表头赋值后，后面的自增长表头会以最后一条记录表头的值+1
caret table 库名.表名(
字段名 int primary key auto_increment,
字段名 数据类型,
字段名 数据类型,
......
);
10.5 foreign key 外键
用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。
作用：插入记录时，表头值在另一个表的表头值范围内选择
使用规则
表存储引擎必须是innodb
字段类型要一致
被参照字段必须要是索引类型的一种(primary key)
创建外键语法格式：
create table 库.表(
字段名 数据类型,
......
foreign key(字段名) #指定外键
references 库.表(字段名) #指定参考的字段名
on update cascade #同步更新
on delete cascade #同步删除
)engine=innodb; #指定存储引擎
创建外键示例
# 创建员工表
mysql> create table db1.yg(
-> yg_id int primary key auto_increment,
-> name char(20));
Query OK, 0 rows affected (0.49 sec)
# 创建工资表，给已有的员工发工资
mysql> create table db1.gz(
-> gz_id int, pay float,
-> foreign key(gz_id) references db1.yg(yg_id)
-> on update cascade on delete cascade);
Query OK, 0 rows affected (1.02 sec)
删除与添加
# 查看外键
mysql> show create table db1.gz \G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float DEFAULT NULL,
KEY `gz_id` (`gz_id`),
CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# 删除外键
mysql> alter table db1.gz drop FOREIGN KEY gz_ibfk_1;
# 删除后，查看不到外键
mysql> show create table db1.gz \G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float DEFAULT NULL,
KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# 添加外键
mysql> alter table db1.gz
add foreign key(gz_id) references db1.yg(yg_id)
on update cascade on delete cascade ;
# 添加后就又有外键了
mysql> show create table db1.gz \G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float DEFAULT NULL,
KEY `gz_id` (`gz_id`),
CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
验证外键功能
- 外键字段的值必须在参考表字段值范围内
- 验证同步更新（on update cascade)
- 验证同步删除（on delete cascade)
外键字段的值必须在参考表字段值范围内
# 在员工表插入记录
mysql> insert into db1.yg(name) values("haha"),("tom");
mysql> select * from db1.yg;
+-------+------+
| yg_id | name |
+-------+------+
| 1 | haha |
| 2 | tom |
+-------+------+
2 rows in set (0.00 sec)
# 工资表插入记录
mysql> insert into db1.gz values(1,20000);
mysql> insert into db1.gz values(2,30000);
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay |
+-------+-------+
| 1 | 20000 |
| 2 | 30000 |
+-------+-------+
2 rows in set (0.00 sec)
# 没有的3号员工 工资表插入记录报错
mysql> insert into db1.gz values(3,40000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
# 员工表插入编号3的员工
mysql> insert into db1.yg (name) values ("Lucy");
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | haha |
| 2 | tom |
| 3 | lufei |
+-------+-------+
3 rows in set (0.00 sec)
# 这样就执行成功了
mysql> insert into db1.gz values(3,40000);
验证同步更新
# 更新员工表的id
mysql> update db1.yg set yg_id=9 where yg_id=3;
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | haha |
| 2 | tom |
| 9 | lufei |
+-------+-------+
3 rows in set (0.00 sec)
# 查看gz表的id也变化了，这就是同步更新了
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay |
+-------+-------+
| 1 | 20000 |
| 2 | 30000 |
| 9 | 40000 |
+-------+-------+
3 rows in set (0.00 sec)
验证同步删除
mysql> delete from db1.yg where yg_id=2;
Query OK, 1 row affected (0.11 sec)
# 查看工资表的id2也同时删除了
mysql> select * from db1.gz;
+-------+-------+
| gz_id | pay |
+-------+-------+
| 1 | 20000 |
| 9 | 40000 |
+-------+-------+
2 rows in set (0.00 sec)
外键使用注意事项
# 被参考的表不能删除
mysql> drop table db1.yg;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
# 给工资表的gz_id添加主键，以防重复
mysql> alter table db1.gz add primary key(gz_id); 添加主键
11. Mysql 索引
11.1 什么是索引 (index)
索引类似于书籍的目录，它为数据库中的数据提供了一种快速查找的方式。在没有索引的情况下，数据库需要扫描整个表来查找满足条件的记录，这种操作称为全表扫描（Full Table Scan），效率非常低。而索引通过创建数据的有序结构，使得数据库可以快速定位到目标数据，从而大大提高了查询速度。
11.2 索引的原理
索引通过预先对数据进行排序和组织，使得数据库在查询时可以快速定位目标数据，从而避免了全表扫描。B+ 树是 MySQL 中最常用的索引结构，它通过多级索引和有序性，大大减少了磁盘 I/O 操作，提高了查询效率。索引虽然强大，但也需要合理使用，避免过多或不恰当的索引带来的额外开销。
11.3 索引分类
普通索引（Normal Index）
普通索引是最基本的索引类型，它没有唯一性约束。可以为表中的一个或多个列创建普通索引。例如：
sql复制
CREATE INDEX idx_name ON table_name (column_name);
唯一索引（Unique Index）
唯一索引要求索引列的值必须是唯一的，但允许有空值。它不仅可以加快查询速度，还可以防止数据重复。例如：
CREATE UNIQUE INDEX idx_unique_name ON table_name (column_name);
主键索引（Primary Key Index）
主键索引是一种特殊的唯一索引，它要求索引列的值必须唯一且非空。每个表只能有一个主键索引。主键索引通常用于唯一标识表中的每一行记录。例如：
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
复合索引（Composite Index）
复合索引是为表中的多个列创建的索引。在查询时，如果查询条件中包含了复合索引的前导列，MySQL 可以利用该索引来加速查询。例如：
CREATE INDEX idx_composite ON table_name (column1, column2);
全文索引（Full-Text Index）
全文索引用于对文本数据进行全文搜索，适用于 CHAR、VARCHAR 和 TEXT 类型的列。它可以帮助快速查找文本内容中的关键字。例如：
CREATE FULLTEXT INDEX idx_fulltext ON table_name (column_name);
11.4 索引优点
可以大大提高MySQL的检索速度
索引大大减小了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
11.5 索引缺点
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效。
11.6 使用规则
一个表中可以有多个index
任何数据类型的表头都可以建索引
字段的值可以重复，可以赋NULL值
通常在where条件中的字段上配置index
index索引标志MUL
11.7 创建索引
creare table 库.表(
字段名 数据类型,
字段名 数据类型,
......
index(字段名),
index(字段名),
);
create index 索引名 on 库.表(字段名); # 添加索引
drop index 索引名 on 库.表; # 删除索引
explain select 查询语句; # 验证查询是否使用索引
mysql> create database home;
mysql> use home;
# 创建索引
mysql> create table t4(
-> id char(6),
-> name varchar(6),
-> age int(3),
-> gender enum("boy","girl") default "boy",
-> index(id),index(name)
-> );
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> desc home.t4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | YES | MUL | NULL | |
| name | varchar(6) | YES | MUL | NULL | |
| age | int | YES | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
# 查看索引详细信息
mysql> show index from home.t4\G
*************************** 1. row ***************************
Table: t4 # 表名
Non_unique: 1
Key_name: id # 索引名 （默认索引名和表头名相同，删除索引时，使用的索引名）
Seq_in_index: 1
Column_name: id # 表头名
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE # 索引类型
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t4
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.01 sec)
# 删除索引
mysql> drop index id on home.t4;
mysql> desc home.t4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | YES | | NULL | |
| name | varchar(6) | YES | MUL | NULL | |
| age | int | YES | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show index from home.t4\G
*************************** 1. row ***************************
Table: t4
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.01 sec)
# 已有表添加索引命令
mysql> create index nl on home.t4(age);
mysql> desc home.t4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | YES | | NULL | |
| name | varchar(6) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show index from home.t4\G
*************************** 1. row ***************************
Table: t4
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t4
Non_unique: 1
Key_name: nl # 设置的索引名
Seq_in_index: 1
Column_name: age # 表头名
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.00 sec)
11.8 验证索引
# 验证是否使用索引
mysql> explain select * from tarena.user where name="sshd" \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user # 表名
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL # 使用的索引名
key_len: NULL
ref: NULL
rows: 27 # 查找的总行数
filtered: 10.00
Extra: Using where # 额外说明
1 row in set, 1 warning (0.00 sec)
# 查看表的总行数，查找sshd用做的是全表扫描
mysql> select count(*) from tarena.user;
+----------+
| count(*) |
+----------+
| 27 |
+----------+
1 row in set (0.01 sec)
# 给name表头设置索引
msyql> create index name on tarena.user(name);
# 验证索引
mysql> explain select * from tarena.user where name="sshd" \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user #表名
partitions: NULL
type: ref
possible_keys: name
key: name #使用的索引名
key_len: 21
ref: const
rows: 1 #查找的总行数
filtered: 100.00
Extra: NULL #额外说明
1 row in set, 1 warning (0.00 sec)
12. 用户管理
12.1 创建用户
用户名：授权时自定义，要有标识性，存储在mysql库的user表里
客户端地址
% //所有主机
192.168.88.0/24 //网段内的所有主机
192.168.88.52 //固定1台主机
localhost //数据库服务器本机
create user 用户名@"客户端地址" identified by "密码";
#创建用户如下
mysql> create user tom@'localhost' identified by '123';
Query OK, 0 rows affected (0.39 sec)
#修改用户密码如下
mysql> alter user tom@'localhost' identified by 'tarena';
Query OK, 0 rows affected (0.08 sec)
#修改用户名如下
mysql> rename user tom@'localhost' to jerry@'localhost';
Query OK, 0 rows affected (0.07 sec)
#删除用户如下
mysql> drop user jerry@localhost;
Query OK, 0 rows affected (0.11 sec)
12.2 用户授权
授权是在数据库服务器里添加用户并设置权限及密码
重复执行grant命令时如果库名和用户名不变时，是追加权限
语法：
grant 权限列表 on 库名 to 用户名@"客户端地址";
权限表示方法如下:
- all //所有权限
- usage //登陆权限或无权限
- select,update,insert //个别权限
- select,update(字段1,字段n) //指定字段
库名用法如下:
- *.* //所有库所有表
- 库名.* //一个库
- 库名.表名 //一张表
mysql> grant select on *.* to tom@'localhost';
Query OK, 0 rows affected (0.06 sec)
mysql> grant delete on tarena.* to tom@'localhost';
Query OK, 0 rows affected (0.13 sec)
mysql> grant insert on tarena.departments to tom@'localhost';
Query OK, 0 rows affected (0.08 sec)
mysql> grant update(name) on tarena.user to tom@'localhost';
Query OK, 0 rows affected (0.07 sec)
mysql> show grants for tom@'localhost';
+---------------------------------------------------------------+
| Grants for tom@localhost |
+---------------------------------------------------------------+
| GRANT SELECT ON *.* TO `tom`@`localhost` |
| GRANT DELETE ON `tarena`.* TO `tom`@`localhost` |
| GRANT INSERT ON `tarena`.`departments` TO `tom`@`localhost` |
| GRANT UPDATE (`name`) ON `tarena`.`user` TO `tom`@`localhost` |
+---------------------------------------------------------------+
4 rows in set (0.00 sec)
12.3 授权库
mysql库,存储用户权限信息,主要表如下:
user表 //保存已有的授权用户及用户对所有库的权限
db表 //保存已有授权用户对某一个库的访问权限
tables_priv表 //记录已有授权用户对某一张表的访问权限
columns_priv表 //记录已有授权用户对某一个字段的访问权限
12.4 撤销权限
删除已有授权用户的权限
库名必须和授权时的表示方式一样
语法：revoke 权限列表 on 库名 from 用户名@"客户端地址";
mysql> revoke drop,delete on *.* from tom@'localhost';
Query OK, 0 rows affected (0.34 sec)
12.5 用户管理相关命令
| 命令 | 作用 |
|---|---|
| select user(); | 显示登录用户名和客户端地址 |
| show grants; | 用户显示自身访问权限 |
| show grants for 用户名@“客户端地址”; | 管理员查看已有授权用户权限 |
| set password for 用户名@“客户端地址”=“密码”; | 管理员重置授权用户连接密码 |
| drop user 用户名@“客户端地址”; | 删除授权用户(必须有管理员权限) |
13. 数据备份/ 恢复
13.1 备份方式及策略
按照数据库服务状态分为：
冷备份：在在备份时停止数据库服务，将整个数据库复制到备份设备中。
热备份：在备份时数据库是运行的。
按照备份的数据分为：
物理备份：备份数据库服务器上存储的原始数据和文件，可以直接拷贝和恢复。
逻辑备份：备份的是 SQL语句，可用于不同版本和不同类型的MySQL数据库之间的数据迁移。
备份策略
13.2 物理备份及恢复
定义: 备份数据库的物理文件，如数据文件和日志文件。
优点: 恢复速度快。
缺点: 依赖于数据库系统，可读性差。
示例：
# 物理备份时要停掉mysql服务
[root@mysql50 ~]# systemctl stop mysqld
[root@mysql50 ~]# mkdir /bakdir #创建备份目录
# 这里使用的是拷贝的方式,也可以使用tar备份的方式
[root@mysql50 ~]# cp -r /var/lib/mysql /bakdir/mysql.bak #拷贝数据库目录
[root@mysql50 ~]# rsync -av /bakdir/mysql.bak root@192.168.88.51:/root/
#在192.168.88.51主机实现恢复操作
[root@mysql51 ~]# systemctl stop mysqld #停止服务
[root@mysql51 ~]# rm -rf /var/lib/mysql/* #清空数据库目录
# 如果是tar备份的方式,就使用tar -xf的命令,解压到/var/lib/mysql/
[root@mysql51 ~]# cp -r /bakdir/mysql.bak/* /var/lib/mysql/
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql #修改所有者和组用户
[root@mysql51 ~]# systemctl start mysqld #启动服务
#连接服务查看数据,会发现数据就被恢复了
[root@mysql51 ~]# mysql -uroot -p'123'
13.3 逻辑备份及恢复
定义: 备份数据库的逻辑数据，如表、视图、存储过程等。
优点: 独立于数据库系统，可读性好。
缺点: 恢复速度慢
库名表示方法
库名 表名列表 //备份1张表或多张表所有数据
-B 库名 //备份1个库或多个库所有数据
–all-database或-A //备份1台服务器所有数据
示例：
#备份1张表或多张表如下
[root@mysql50 ~]# mysqldump -u用户名 -p密码 库名 表名列表 > /bakdir/xxx.sql
#备份1个库或多个库如下
[root@mysql50 ~]# mysqldump -u用户名 -p密码 -B 库名1 库名2 > /bakdir/xxx.sql
#备份所有库如下
[root@mysql50 ~]# mysqldump -u用户名 -p密码 -A > /bakdir/xxx.sql
[root@mysql50 ~]# rsync -av /bakdir/*.sql root@192.168.88.51:/root/
#恢复库数据如下
[root@mysql51 ~]# mysql -uroot -p密码 < /root/xxx.sql
#恢复表数据如下
[root@mysql51 ~]# mysql -uroot -p密码 库名 < /root/xxx.sql
13.4 增量备份及恢复
xtrabackup 是一个开源的、基于MySQL数据库文件级别的备份工具，由Percona公司开发。它主要用于热备份MySQL数据库，即在数据库运行时进行备份，而不需要关闭数据库服务。xtrabackup 利用了MySQL的InnoDB存储引擎特性，能够实现一致性的备份，并且备份过程对数据库性能的影响较小。
对数据做增量备份前，必须先有一次备份，也就是首次备份，通常首次备份备份所有数据；比如每周周一完全备份，每周周二到周日增量备份。
首次完成备份
xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --datadir=数据库目录 --backup --target-dir=备份目录
# 周一完全备份（备份所有数据）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password --databir=/var/lib/mysql --backup --target-dir=/fullbak
增量备份
xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --datadir=数据库目录 --backup --incremental-basedir=参考目录 --target-dir=备份目录
# 周二增量备份（备份周一备份后新产生的数据）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/fullbak --target-dir=/new2
# 周三增量备份（备份周二备份后新产生的数据）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new2 --target-dir=/new3
# 周四增量备份（备份周三备份后新产生的数据）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new3 --target-dir=/new4
# 周五增量备份（备份周四备份后新产生的数据）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new4 --target-dir=/new5
# 周六增量备份（备份周五备份后新产生的数据）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new5 --target-dir=/new6
# 周日增量备份（备份周六备份后新产生的数据）
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/new6 --target-dir=/new7
数据增量恢复
增量恢复步骤:
- 准备恢复数据
- 合并数据
- 清空数据库目录
- 拷贝数据
- 修改数据库目录所有者/组用户为mysql
- 重启数据库服务
xtrabackup --prepare --apply-log-only --target-dir=/完全备份目录
# 拷贝完全备份文件
[root@mysql50 ~]# rsync -av /fullbak root@192.168.88.51:/opt/
# 拷贝增量备份文件
[root@mysql50 ~]# rsync -av /new* root@192.168.88.51:/opt/
[root@mysql51 ~]# xtrabackup --prepare --apply-log-noly --target-dir=/opt/fullbak
合并数据
xtrabackup --prepare --apply-log-noly --target-dir=/完全备份目录 --incremental-dir=/新数据目录
# 将周二的增量数据拷贝到周一备份目录里，合并后周一的目录里存放的是周一 + 周二 的数据
[root@mysql51 ~]# xtrabackup --prepare --apply-log-noly --target-dir=/opt/fullbak --incremental-dir=/opt/new2
# 将周三的增量数据拷贝到周一备份目录里，合并后周一的目录里存放的是周一 + 周二 + 周三 的数据
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/fullbak --incremental-dir=/opt/new3
......#依此类推
# 将周日的增量数据拷贝到周一备份目录里，合并后周一的目录里存放的是周一 + 周二 + 周三 + 周四 + 周五 + 周六 + 周日 的数据
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/opt/fullbak --incremental-dir=/opt/new7
拷贝数据
xtrabackup --copy-back --target-dir=/完全备份目录
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/fullbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# systemctl restart mysqld
# 进入数据库,会发现从周一到周日的数据都有
[root@mysql51 ~]# mysql -u用户 -p密码
13.5 差异备份与恢复
首次完成备份
xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --datadir=数据库目录 --backup --target-dir=备份目录
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --target-dir=/allbak
差异备份
xtrabackup --host=127.0.0.1 --user=用户名 --password=密码 --datadir=数据库目录 --backup --incremental-basedir=完全备份目录 --target-dir=备份目录
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/allbak --target-dir=/dir2
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/allbak --target-dir=/dir3
[root@mysql50 ~]# xtrabackup --host=127.0.0.1 --user=root --password=123 --datadir=/var/lib/mysql --backup --incremental-basedir=/allbak --target-dir=/dir4
差异数据恢复
差异恢复数据步骤
准备恢复数据
合并数据
清空数据库目录
拷贝数据
修改数据库目录所有者/组用户为mysql
重启数据库服务
xtrabackup --prepare --apply-log-only --target-dir=完全备份目录
[root@mysql50 ~]# rsync -av /allbak root@192.168.88.51:/root/ 第一次完全备份
[root@mysql50 ~]# rsync -av /dir4 root@192.168.88.51:/root/ 最后一次差异备份
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/allbak
合并数据
xtrabackup --prepare --apply-log-only --target-dir=/完全备份目录 --incremental-dir=/最后一次差异备份
[root@mysql51 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/allbak --incremental-dir=/root/dir4
拷贝数据
xtrabackup --copy-back --target-dir=/完全备份目录
[root@mysql51 ~]# rm -rf /var/lib/mysql/*
[root@mysql51 ~]# xtrabackup --copy-back --target-dir=/root/allbak
[root@mysql51 ~]# chown -R mysql:mysql /var/lib/mysql
[root@mysql51 ~]# systemctl restart mysqld
# 数据会被成功恢复
[root@mysql51 ~]# mysql -u用户 -p密码
13.6 数据实时备份与恢复
binlog日志管理
binlog日志也称做二进制日志
MySQL服务日志文件的一种
保存出查询之外的所有sql命令
可用于数据的备份和恢复
配置mysql主从同步的必要条件
相关命令
mysql> show master status; # 查看正在使用的日志
mysql> show binary logs; # 查看已有的日志文件
mysql> purge master logs to "日志名"; # 删除编号之前的日志
mysql> flush logs; # 刷新日志
mysql> reset master; # 删除所有日志，重新创建日志
mysql> show binlog events in "日志文件名";# 查看日志文件内容
自定义日志
| 配置项 | 用途 |
|---|---|
| server_id=数字 | 指定id值(1-255) |
| log_bin=/目录/日志名 | 启用binlog日志 |
| max_binlog_size=数值m | 指定日志文件容量，默认1G |
[root@mysql52 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
log-bin=/mylog/mysql52 //定义日志目录和日志文件名(手动添加)
:wq
[root@mysql52 ~]# mkdir /mylog 创建目录
[root@mysql52 ~]# chown mysql /mylog 修改目录所有者mysql用户
[root@mysql52 ~]# setenforce 0 关闭selinux
[root@mysql52 ~]# systemctl restart mysqld 重启服务
[root@mysql52 ~]# ls /mylog/ 查看日志目录
mysql52.000001 mysql52.index
恢复命令
把查看到的文件内容管道给连接mysql服务的命令执行
mysqlbinlog /目录/文件名 | mysql -u用户名 -p密码
# 把日志文件拷贝给恢复数据的服务器,比如 mysql50
[root@mysql52 ~]# rsync -av /mylog/mysql52.000001 root@192.168.88.50:/root/
# 执行日志恢复数据
[root@mysql50 ~]# mysqlbinlog /root/mysql52.000001 | mysql -uroot -p123
# 进入数据库,会发现数据通过binlog日志恢复了
[root@mysql50 ~]# mysql -u用户 -p密码
13.7 推荐备份策略
选择哪种备份策略取决于组织的具体需求、资源限制和恢复目标。以下是一些考虑因素
如果数据量不大或者初期备份时，可以使用完整备份，因为它最直观也最全面
如果数据量较大且变化频繁，增量备份是一个节省空间的选择，但需要注意恢复过程的复杂性
如果需要快速恢复且有足够的存储空间，差异备份是一个较好的选择，因为它结合了全备份和增量备份的优点
对于需要实时备份和数据同步的场景，Binlog备份是非常合适的
14. MySQL 主从同步
14.1 原理&结构概括
数据库复制技术
可以将一个MySQL数据库的变更操作同步到另外一个或多个MySQL数据库中，分为2种角色：
主（master）服务器：接受客户端访问连接
从 (slave）服务器：自动同步主服务器数据
主从同步原理
通过复现主服务器binlog日志中SQL语句的方式实现数据实时同步,在标准的 MySQL 主从同步（Replication）配置中，从服务器（Slave）是只读的，不允许进行写操作。所有的写操作都应该在主服务器（Master）上进行，然后通过二进制日志（Binary Log）同步到从服务器。
数据同步的步骤
主库将数据的事务操作（DML）记录到binlog日志文件里。
从库监听主库binlog日志变化，当发生变化后，IO线程去请求主库的binlog日志，并将日志变化写入到relaylog日志中，此时主库生产logdump线程，给从库IO线程传输binlog日志文件内容
从库更新relaylog文件，SQL线程将relaylog文件中的更新执行一遍，达到了与主库数据一致的目的。
Master:启用binlog日志，记录所有的数据库更新和修改操作
Slave:
- Slave_IO：复制master主机 binlog日志文件里的SQL命令到本机的 relay-log文件里。
- Slave_SQL：执行本机relay-log文件里的SQL语句，实现与Master数 据一致。
结构模式（宏观模式）
区分数据库服务器的角色
一主一从：搭建简单，数据副本数少
一主多从：数据多副本且无关联，主节点负载高
级联复制：相对主节点负载低，数据多副本，但有关联
互为主从：多节点写，容易出现死锁
复制模式 (微观模式) 保证MySQL主从同步性能
异步复制:主服务器处理完SQL直接返回结果
半同步复制:主服务器处理完SQL等待至少1个从完成数据同步后返回结果
全同步复制:主服务器处理完SQL等待所有从完成数据同步后返回结果
14.2 构建主从同步
主要步骤如下:
主服务器上的配置:
1. 安装MySQL并启动服务
2. 配置binlog日志和server_id
[root@mysql53 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=53 # 设置唯一的server-id
log-bin=mysql53 # 开启binlog，指定日志文件前缀
:wq
[root@mysql53 ~]# systemctl restart mysqld
3. 授权主从同步用户
[root@mysql53 ~]# mysql
mysql> create user repluser@"%" identified by "123qqq...A";
mysql> grant replication slave on *.* to repluser@"%";
4. 备份已有数据
[root@mysql53 ~]# mysqldump -uroot -p -A > all.sql
[root@mysql53 ~]# rsync -av all.sql 192.168.88.54:/root
从服务器上的配置: //如果想要实现一主多从的场景,就可以把以下对从服务器的配置在目标服务器上执行,目标服务器就是从服务器了,要注意日志文件名和偏移量
1. 安装MySQL并启动服务
2. 还原主服务器备份数据
[root@mysql54 ~]# mysql -u用户 -p < all.sql
3. 配置server_id
[root@mysql54 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=54 # 设置唯一的server-id
:wq
[root@mysql54 ~]# systemctl restart mysqld
4.[root@mysql54 ~]# mysql
mysql> change master to //设置主服务器信息
-> master_host=主服务器地址
-> master_port=主服务器端口
-> master_user=主服务器授权用户
-> master_password=主服务器授权用户密码
-> master_log_file=主服务器正在使用的binlog日志
-> master_log_pos=主服务器当前偏移量
5.mysql> start slave //启动io/sql线程
mysql> show slave status\G //查看状态信息
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.88.53 #主服务器ip地址
Master_User: repluser #主服务器授权用户
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql53.000001 #主服务器正在使用的binlog日志
Read_Master_Log_Pos: 667 #主服务器当前偏移量
Relay_Log_File: mysql54-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql53.000001
Slave_IO_Running: Yes #IO线程已运行
Slave_SQL_Running: Yes #SQL线程已运行
错误处理
stop slave; //停止io/sql 线程
reset slave; //重置io/sql 线程
change master to 语句 //设置主服务器信息
start slave; //启动io/sql 线程
show slave status\G //查看状态信息
15. MySQL 读写分离
15.1 读写分离概述
是指将MySQL数据库的读操作和写操作分别分配到不同的服务器上，通过这种方式可以提高数据库的并发处理能力和性能、降低系统失败的风险。要保证负责读访问主机与负责写访问主机的数据一致
解决MySQL主从同步中主服务器负载高的解决方案
实现方法
客户端分离：开发者手工分离
服务端分离：加设MySQL主从复制组的代理服务器
解决方案：插入中间件软件
运行在应用程序和数据库之间的软件，通过拦截和处理数据库请求来实现读写分离、负载均衡、故障切换等功能。工作原理就是识别SQL类型按照主写读从的规则进行分发。
| 软件 | 介绍 |
|---|---|
| MySQL Proxy | 基于Lua脚本语言编写的代理服务器 |
| MaxScale | 开源的MySQL数据库代理服务器 |
| Cobar | 阿里巴巴开发的一款开源的MySQL数据库中间件 |
| TcaplusDB | 腾讯云开发的一款分布式NoSQL数据库 |
| Mycat2 | 阿里巴巴旗下的一个项目 |
15.2 构建读写分离思路
以下是基于mycat2实现读写分离的步骤：
1. 部署MySQL主从同步
2. 部署MyCat服务：装包，改配置文件，配置数据库服务，启动服务
3. 进入mycat2数据库，创建添加数据源和集群（添加master角色主机、添加slave角色主机）
4. 修改master角色主机仅负责写访问，修改slave角色主机仅负责读访问
5. 修改自己创建的集群的读策略
6. 指定库存储数据使用的集群，然后重启mycat2服务，验证读写分离
###############mycat2热配置模板####################
# 用户相关配置
/*+ mycat: showusers{} */;
/*+ mycat: createuser{"name":"xx","password":"xx"...} */;
/*+ mycat: dropuser{"name":"xx"} */;
# 数据源相关配置
/*+ mycat: showdatasources{} */;
/*+ mycat: createdatasource{"name":"xx"...} */;
/*+ mycat: dropdatasource{"name":"xx"} */;
示例：
mysql> /*+ mycat:createdatasource{
-> "name":"whost56",
-> "url":"jdbc:mysql://192.168.88.56:3306",
-> "user":"plja","password":"123456"
-> }*/;
mysql> /*+ mycat:createdatasource{
-> "name":"rhost57",
-> "url":"jdbc:mysql://192.168.88.57:3306",
-> "user":"plja","password":"123456"
-> }*/;
mysql> /*+ mycat: showdatasources{} */\G
# 集群相关配置
/*+ mycat: showclusters{} */;
/*! mycat: createcluster{"name":"xx"...} */;
/*! mycat: dropcluster{"name":"xx"} */;
示例：
mysql> /*! mycat:createcluster{
-> "name":"rwcluster",
-> "masters":["whost56"],
-> "replicas":["rhost57"]
-> }*/;
mysql> /*! mycat:showclusters{}*/\G
# 逻辑库相关配置
/*+ mycat: showschemas{} */;
/*+ mycat: createschema{"name":"xx"...} */;
/*+ mycat: dropschema{"name":"xx"} */;
16. MySQL 分库分表
16.1 什么是分库分表
将存放在一台数据库服务器中的数据，按照特定方式进行拆分，分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果
16.2 相关概念
分库：指在表数量不变的情况下对库进行切分
分表：指在库数量不变的情况下对表进行切分
分库分表：指库和表都切分，数据量都发生改变
垂直分割
垂直分库：指的是将单个库中的表分到多个库，每个库包含的表不一样
垂直分表：指的是将存在一张表中的表头切分到多张，切分后每张表的结构不一样、数据不一样，所有表的表头和一起是原表的表头
水平分割
水平分库：将表水平切分后分散到不同库里，每个库有相同的表，但表里的数据不同
水平分表：表的结构不变，数据分散存储到不同表中，每个表的结构一样、数据不一样，所有表的数据合并是表的总数据
企业常见的场景是：垂直分库+水平分表
解决方案：使用mycat中间件
16.3 Mycat是什么
Mycat是一个开源的基于MySQL协议的分布式数据库中间件
能够将多个MySQL节点组合成一个逻辑上的数据库集群，并提供读写分离、分库分表、自动切换、自动负载均衡等功能。其主要目的是为了解决MySQL单点故障、性能瓶颈、数据扩容等问题，从而提升MySQL的可用性和性能
Mycat使用Java语言开发，运行于JVM之上，支持高并发的SQL处理，同时也具有一定的自动化管理能力，可以根据配置文件自动化地完成各种数据库操作
16.4 分库分表的思路
1. 先搭建两一主一从的主从复制，并创建mycat连接主从mysql的用户
2. 部署MyCat服务：装包，改配置文件，配置数据库服务，启动服务
3. 再MyCat里执行以下模板命令：
/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.59:3306", # 主库
"user":"plj", # 连接mysql数据库的用户
"password":"123456", # 连接mysql数据库的密码
"name":"dw0" # 数据源的名字
}*/;
/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.60:3306", # 从库
"user":"plj", # 连接mysql数据库的用户
"password":"123456", # 连接mysql数据库的密码
"name":"dr0" # 数据源的名字
}*/;
/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.61:3306", # 主库
"name":"dw1",
"user":"plj",
"password":"123456"
}*/;
/*+ mycat:createdatasource{
"url":"jdbc:mysql://192.168.88.62:3306", # 从库
"name":"dr1",
"user":"plj",
"password":"123456"
}*/;
# 以下是创建集群
mysql> /*!mycat:createcluster{
-> "name":"c0", # 做分库分表必须是c开头，从0开始
-> "masters":["dw0"],
-> "replicas":["dr0"]
-> }*/;
Query OK, 0 rows affected (0.02 sec)
mysql> /*!mycat:createcluster{
-> "name":"c1", # 做分库分表必须是c开头
-> "masters":["dw1"],
-> "replicas":["dr1"]
-> }*/;
Query OK, 0 rows affected (0.01 sec)
mysql> /*!mycat:showcluster{}*/\G
4. 就是验证测试了，看下面16.5的内容
16.5 测试配置
分片表
根据分片规则计算结果存储数据
dbpartition by //数据分区，表示将表的数据分为多个分区，每个分区存储在不同的数据库节点中。
tbpartition by //表分区，表示在每个数据分区中，再将数据分为多个表分区。
mod_hash() //取模哈希分片规则
tbpartition //表分片数量
dbpatition //库分片数量
# 客户端进入mycat，创建表来进行实验
mysql> create table tarena.employees(
-> employee_id int primary key,
-> name char(10),
-> dept_id int,
-> mail varchar(30)
-> )
-> dbpartition by mod_hash(employee_id)
-> tbpartition by mod_hash(employee_id)
-> dbpartitions 2 tbpartitions 1;
Query OK, 0 rows affected (3.35 sec)
# 来到第一个主库来查看库和表
[root@mysql59 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tarena |
| tarena_0 |
+--------------------+
[root@mysql59 ~]# mysql -e 'use tarena_0 ; show tables'
+--------------------+
| Tables_in_tarena_0 |
+--------------------+
| employees_0 |
+--------------------+
# 来到第二个主库来查看库和表
[root@mysql61 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tarena |
| tarena_1 |
+--------------------+
[root@mysql61 ~]# mysql -e 'use tarena_1;show tables'
+--------------------+
| Tables_in_tarena_1 |
+--------------------+
| employees_1 |
+--------------------+
# 客户端再次进入mycat，来存储数据
mysql> insert into tarena.employees values (9,"jim","1","jim@163.com");
mysql> insert into tarena.employees values (8,"tom","3","tom@QQ.com");
mysql> insert into tarena.employees values (7,"lucy","2","lucy@QQ.com");
mysql> insert into tarena.employees values (6,"john","2","john@QQ.com");
mysql> select * from tarena.employees;
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 6 | jim | 2 | jim@QQ.com |
| 8 | tom | 3 | tom@QQ.com |
| 7 | lucy | 2 | lucy@QQ.com |
| 9 | john | 1 | john@163.com |
+-------------+------+---------+-----------+
4 rows in set (2.07 sec)
# 因为上面创建表的时候使用了mod_hash(employee_id)，意思就是employee_id字段对集群的数量取余，该案例有两个集群，也就是employee_id对2取余，结果不是0就是1，所以当employee_id的值为偶数时，结果铁定是0，正好对应了上面定义的c0集群，所以被分到了c0集群中，当employee_id的值为奇数时，结果铁定是1，正好对应了上面定义的c1集群，所以被分到了c1集群中
# 查看c0集群中 数据库服务器存储的数据
[root@mysql59 ~]# mysql -e 'select * from tarena_0.employees_0'
+-------------+------+---------+----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+----------+
| 6 | jim | 2 | jim@QQ.com |
| 8 | tom | 3 | tom@QQ.com |
+-------------+------+---------+----------+
# 查看c1集群中 数据库服务器存储的数据
[root@mysql61 ~]# mysql -e 'select * from tarena_1.employees_1'
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 7 | lucy | 2 | lucy@QQ.com |
| 9 | john | 1 | john@163.com |
+-------------+------+---------+-----------+
ER表
称为关联表，表示数据逻辑上有关联性的两个或多个表，例如工资表和员工表。对于关联表，通常希望他们能够有相同的分片规则，这样在进行关联查询时，能够快速定位到同一个数据分片中。MyCat2中对于关联表，不需要有过多的声明，他可以根据分片规则自行判断。
# 客户端连接mycat，创建表
mysql> create table tarena.salary(
-> employee_id int primary key,
-> p_date date,basic int,bonux int
-> )dbpartition by mod_hash(employee_id)
-> tbpartition by mod_hash(employee_id)
-> tbpartitions 1;
Query OK, 1 row affected (1.63 sec)
# 在MyCat2终端查看关联表关系
mysql> /*+ mycat:showErGroup{}*/;
+---------+------------+-----------+
| groupId | schemaName | tableName |
+---------+------------+-----------+
| 0 | tarena | employees |
| 0 | tarena | salary |
+---------+------------+-----------+
2 rows in set (0.01 sec)
# 在c0集群master服务器查看表
[root@mysql59 ~]# mysql -e 'use tarena_0 ; show tables'
+--------------------+
| Tables_in_tarena_0 |
+--------------------+
| employees_0 |
| salary_0 |
+--------------------+
# 在c1集群master服务器查看表
[root@mysql61 ~]# mysql -e 'use tarena_1;show tables'
+--------------------+
| Tables_in_tarena_1 |
+--------------------+
| employees_1 |
| salary_1 |
+--------------------+
# 客户端连接mycat服务并插入数据
mysql> insert into tarena.salary values(6,20230110,20000,2000);
mysql> insert into tarena.salary values(7,20230210,25000,2500);
mysql> insert into tarena.salary values(8,20230310,30000,3000);
mysql> insert into tarena.salary values(9,20230410,35000,3500);
# 在c0集群master服务器查看表
[root@mysql59 ~]# mysql -e 'select * from tarena_0.employees_0'
+-------------+------+---------+----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+----------+
| 6 | C | 2 | c@QQ.com |
| 8 | B | 3 | B@QQ.com |
+-------------+------+---------+----------+
# 在c1集群master服务器查看表
[root@mysql61 ~]# mysql -e 'select * from tarena_1.employees_1'
+-------------+------+---------+-----------+
| employee_id | name | dept_id | mail |
+-------------+------+---------+-----------+
| 7 | C | 2 | c@QQ.com |
| 9 | a | 1 | a@163.com |
+-------------+------+---------+-----------+
全局表
所有库都有表的全部数据
# 客户端连接mycat，创建表
mysql> create table tarena.dept(
-> dept_id int primary key,
-> dept_name char(10)
-> )default charset utf8mb4
-> broadcast;
Query OK, 4 rows affected (2.45 sec)
mysql> insert into tarena.dept values(1,"开发部"),(2,"运维部"),(3,"测试部");
# 在4台数据库服务器查看数据
[root@mysql59 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 开发部 |
| 2 | 运维部 |
| 3 | 测试部 |
+---------+-----------+
[root@mysql60 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 开发部 |
| 2 | 运维部 |
| 3 | 测试部 |
+---------+-----------+
[root@mysql61 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 开发部 |
| 2 | 运维部 |
| 3 | 测试部 |
+---------+-----------+
[root@mysql62 ~]# mysql -e 'select * from tarena.dept'
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 开发部 |
| 2 | 运维部 |
| 3 | 测试部 |
+---------+-----------+