MySQL基础
2023-12-13 17:00:15
MySQL基础
数据库概论
数据管理演变
- 人工档案管理
- 文件系统管理
- 数据库管理
数据库管理系统
- 层次数据库
- 网状型数据库
- 关系型数据库
- 网格型数据库
- 对象型数据库
- 非关系型数据库
关系型数据库
数据库是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合.
在数据库中的数据我们可以用二维表格的形式理解,例如
表格中一列的标题我们称之为字段
表格中一行数据我们称之为一条记录
| id | account | password | nickname | uuid |
|---|---|---|---|---|
| 1 | root | root | 超级管理员 | 2e6342dd-c475-410f-9948-e4cc1948ef0f |
| 2 | admin | admin | guanli | e30e8060-9320-4b72-8722-10328348a272 |
| 3 | phoenix | 123456 | 李昊哲 | 3ddc2b0e-fdba-4f61-b0a8-2585ffdce940 |
数据库由3部分组成
数据库：用于存储数据
数据库管理系统：用户管理数据库的软件
数据库应用程序：为了提高数据库系统的管理能力使用的数据库管理的补充
对数据库进行查询、修改操作的语言我们称之为SQL
结构化查询语言(Structured Query Language)简称SQL(发音：/?es kju? ?el/ "S-Q-L")，
是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，
用于存取数据以及查询、更新和管理关系数据库系统；
同时也是数据库脚本文件的后缀名。
标准：SQL-92、SQL-99、SQL-2003
非标准：方言
常用关系型数据库
Oracle、MySQL、SQLServer、...
对象型数据库
PostgresQL、...
常用的非关系数据库（在中国）
Redis、MongoDB、HBase、..
按照SQL-92标准SQL包含4部分
1、数据库定义语言(DDL)：create、drop、alter、...
2、数据库操作语言(DML)：insert、update、delete、...
3、数据库查询语言(DQL)：select
4、数据库控制语言(DCL)：grant、revoke、.
5、数据库事务控制语言(TCL)：commit、rollback
安装
- 安装包
- 绿色版
- 集成环境
数据库常用操作
连接数据库
mysql
-h 服务器IP
-u 账户
-p 密码
mysql -h localhost -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 199
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
显示目前已有数据库
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
显示当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
创建数据库
# 创建数据库 格式：create database 数据库名称;
create database `chap01`;
# 显示已存在的数据库 检查数据库创建是否成功
show databases;
查看数据库创建详细过程
mysql> show create database `chap01`;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| chap01 | CREATE DATABASE `chap01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
创建数据库同时指定字符集
# 创建数据库并指定字符集 推荐字符集使用 utf8 或者 utf8mb4
create database `chap001` default character set gbk;
show databases;
show create database `chap001`;
删除数据库
# 删除数据库 drop database 数据库名称
drop database `chap001`;
# 显示已存在的数据库 检查数据库删除是否成功
show databases;
切换数据库
使用哪一个数据库
# 切换数据库 使用哪个数据库
use chap01;
# 显示当前使用的数据库 检查数据库切换是否成功
select database();
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> use chap01;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| chap01 |
+------------+
1 row in set (0.00 sec)
数据表常用操作
创建基本数据表
# 创建数据库
create databae chap01;
# 使用刚刚创建的数据库
use chap01;
# 创建数据表 格式 create table 表名称
# [HY000][4028] A table must have at least one visible column.
# create table `tb_user`;
# 基本格式 create table 表名称 (字段1 数据类型,字段2 数据类型,字段...);
create table `tb_user` (`user_id` int,`user_name` varchar(50));
查看数据表创建详细过程
show create table `tb_user`;
根据数据表创建详细过程 引申出 创建数据表的格式
创建数据表的同时 指定使用的引擎和字符集
create table `tb_user` (
`user_id` int,
`user_name` varchar(50)
) engine=innodb default charset=utf8mb4;
描述数据表结构
describe `tb_user`;
desc `tb_user`;
显示当前数据库所有数据表
show tables;
删除数据表
drop table `tb_user`;
存储引擎
数据库存储引擎是数据库地产软件组织
MySQL 提供了多给不同的存储引擎，包括事务安全型和非事务安全型
show engines;
数据类型
- 数值型 整数、浮点数和定点数
- 日期与时间
- 字符串
整数型
| 数据类型 | 说明 | 存储 | 有符号范围 | 无符号范围 |
|---|---|---|---|---|
| tinyint | 很小的整数 | 1字节 | -128~127 | 0~255 |
| smallint | 小的整数 | 2字节 | -32768~32767 | 0~65535 |
| mediumint | 中等大小的整数 | 3字节 | -8388608~8388607 | 0~16777215 |
| int(integer) | 普通大小的整数 | 4字节 | -2147483648~2147483647 | 0~4294967295 |
| bigint | 大整数 | 8字节 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
浮点型
| 数据类型 | 说明 | 存储 | 有符号范围 | 无符号范围 |
|---|---|---|---|---|
| float | 单精度浮点数 | 4个字 | ||
| double | 双精度浮点数 | 8个字节 |
定点型
| 数据类型 | 说明 | 存储 | 有符号范围 | 无符号范围 |
|---|---|---|---|---|
| decimal(M,D) | 严格定点数 | M+2个字节 |
日期与时间
| 数据类型 | 日期格式 | 存储 | 日期范围 |
|---|---|---|---|
| year | YYYY | 1字节 | 1901~2155 |
| date | YYYY-MM-DD | 3字节 | 1000-01-01~9999-12-31 |
| time | HH:MMSS | 3字节 | -838:59:59~838:59:59 |
| datetime | YYYY-MM-DD HH:MM:SS | 8字节 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| timestamp | YYYY-MM-DD HH:MM:SS | 4字节 | 1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC |
字符串
-
文本字符串
数据类型 说明 存储 char(M) 固定长度 M字节，1 <= M <=255 varchar(M) 非固定长度 可变长 L字节，L <= M 和1 <= M <=255 tinytext 非常小的文本 2^8 text 小文本 2^16 mediumtext 中等大小的文本 2^24 longtext 大的文本 2^32 -
二进制字符串
数据类型 说明 存储 bit binary varbinary tinyblob mediumblob longblob
DML
准备
create table tb_user (
user_id int,
user_name varchar(50)
) engine=innodb default charset=utf8mb4;
insert
格式 插入一条数据 insert into 数据表名 (字段1名,字段2名,…) value (字段1值,字段2值,…)
格式 插入一条数据 为所有字段赋值 insert into 数据表名 value (字段1值,字段2值,…)
insert into tb_user (user_id,user_name) value (1,'user01');
insert into tb_user value (2,'user02');
格式 插入多条数据 insert into 数据表名 (字段1名,字段2名,…) values (字段1值,字段2值,…),(字段1值,字段2值,…)
insert into tb_user values (3,'user03'),(4,'user04');
查询数据表中的数据
select * from tb_user;
update
update 数据表名 set 字段1名 = 值,字段2名 = 值,… where 筛选字段(值必须能够帝国为到当前行)
# update tb_user set user_name = '李昊哲';
update tb_user set user_name = '李昊哲' where user_id = 3;
select * from tb_user;
delete truncate
格式 delete from 数据表名 where 筛选字段(值必须能够帝国为到当前行)
# delete from tb_user;
delete from tb_user where user_id = 3;
select * from tb_user;
清空数据表
# delete from tb_user;
truncate tb_user;
重点面试题 重点中的重点 必问
delete 与 truncate 区别
一般人的回答 delete 仅仅是删除数据效率比较低 truncate 相对于删除表后在重新建表 效率比较高
高手的回答 delete 效率比较低 为每行数据打一个标记 水位线线不变
高手的回答 truncate 效率比较高 相对于删除表后在重新建表 重置水位线update 与 delete 哪个速度更快
update = delete + insert
约束
非空约束
默认约束
唯一约束
检查约束
主键约束
外键约束
非空约束
create table user01 (
id int comment '用户编号',
account varchar(50) not null comment '用户账号',
password varchar(200) not null comment '用户密码',
real_name varchar(50) comment '用户姓名'
);
mysql> describe user01;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int unsigned | YES | | NULL | |
| account | varchar(50) | NO | | NULL | |
| password | varchar(200) | NO | | NULL | |
| real_name | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
insert into user01 value (1,'admin01','pass01','user01');
insert into user01 (id, account, password) value (2,'admin02','pass02');
select * from user01;
# [HY000][1364] Field 'account' doesn't have a default value
insert into user01 (id) value (2);
默认约束
create table user02 (
id int unsigned comment '用户编号',
account varchar(50) not null comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
real_name varchar(50) comment '用户姓名'
);
mysql> describe user02;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int unsigned | YES | | NULL | |
| account | varchar(50) | NO | | NULL | |
| password | varchar(200) | NO | | 123456 | |
| real_name | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
insert into user02 value (1,'admin01','pass01','user01');
insert into user02 (id, account, password) value (2,'admin02','pass02');
insert into user02 (id, account) value (3,'admin03');
select * from user02;
唯一性约束
注意：值虽然不能重复但是可以为空
create table user03 (
id int unsigned comment '用户编号',
account varchar(50) not null comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
id_card varchar(18) unique comment '身份证',
real_name varchar(50) comment '用户姓名'
);
# 或者
create table user03 (
id int unsigned comment '用户编号',
account varchar(50) not null comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
id_card varchar(18) comment '身份证',
real_name varchar(50) comment '用户姓名',
unique key id_card (id_card)
);
mysql> describe user03;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int unsigned | YES | | NULL | |
| account | varchar(50) | NO | | NULL | |
| password | varchar(200) | NO | | 123456 | |
| id_card | varchar(18) | YES | UNI | NULL | |
| real_name | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
insert into user03 (id, account, password,id_card, real_name) VALUE (1,'admin01','pass01','220422','user01');
# [23000][1062] Duplicate entry '220422' for key 'user03.id_card'
insert into user03 (id, account, password,id_card, real_name) VALUE (2,'admin02','pass02','220422','user02');
insert into user03 (id, account, password, real_name) VALUE (3,'admin03','pass03','user03');
insert into user03 (id, account, password, real_name) VALUE (4,'admin04','pass04','user04');
select * from user03;
检查性约束
注意：值虽然必须符合检查规则但是可以为空
create table user04 (
id int unsigned comment '用户编号',
account varchar(50) not null comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
id_card varchar(18) unique comment '身份证',
age int unsigned check ( age >= 18 ) comment '用户年龄 年龄在18岁以上包含18岁',
real_name varchar(50) comment '用户姓名'
);
mysql> describe user04;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int unsigned | YES | | NULL | |
| account | varchar(50) | NO | | NULL | |
| password | varchar(200) | NO | | 123456 | |
| id_card | varchar(18) | YES | UNI | NULL | |
| age | int unsigned | YES | | NULL | |
| real_name | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
insert into user04 (id, account,age) VALUE (1,'admin01', 18);
# [HY000][3819] Check constraint 'user04_chk_1' is violated.
insert into user04 (id, account,age) VALUE (2,'admin02', 17);
insert into user04 (id, account) VALUE (3,'admin03');
select * from user04;
主键约束
值不能为空且唯一约束
主键约束 约等于 非空约束 + 唯一性约束
create table user050 (
id int unsigned comment '用户编号',
account varchar(50) not null unique comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
real_name varchar(50) comment '用户姓名'
);
mysql> describe user050;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int unsigned | YES | | NULL | |
| account | varchar(50) | NO | PRI | NULL | |
| password | varchar(200) | NO | | 123456 | |
| real_name | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table user050;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user050 | CREATE TABLE `user050` (
`id` int unsigned DEFAULT NULL COMMENT '用户编号',
`account` varchar(50) NOT NULL COMMENT '用户账号',
`password` varchar(200) NOT NULL DEFAULT '123456' COMMENT '用户密码',
`real_name` varchar(50) DEFAULT NULL COMMENT '用户姓名',
UNIQUE KEY `account` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
单主键约束
create table user051 (
id int unsigned primary key comment '用户编号',
account varchar(50) not null unique comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
real_name varchar(50) comment '用户姓名'
);
# 或者
create table user051 (
id int unsigned comment '用户编号',
account varchar(50) not null unique comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
real_name varchar(50) comment '用户姓名',
primary key (id)
);
mysql> describe user051;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int unsigned | NO | PRI | NULL | |
| account | varchar(50) | NO | UNI | NULL | |
| password | varchar(200) | NO | | 123456 | |
| real_name | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table user051;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user051 | CREATE TABLE `user051` (
`id` int unsigned NOT NULL COMMENT '用户编号',
`account` varchar(50) NOT NULL COMMENT '用户账号',
`password` varchar(200) NOT NULL DEFAULT '123456' COMMENT '用户密码',
`real_name` varchar(50) DEFAULT NULL COMMENT '用户姓名',
PRIMARY KEY (`id`),
UNIQUE KEY `account` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
insert into user051 value (1,'admin01','pass01','user01');
# [23000][1062] Duplicate entry '1' for key 'user051.PRIMARY'
insert into user051 value (1,'admin02','pass02','user02');
# [HY000][1364] Field 'id' doesn't have a default value
insert into user051 (account, password, real_name) value ('admin03','pass03','user03');
select * from user051;
主键自增长
auto_increment
create table user052 (
id int unsigned auto_increment comment '用户编号',
account varchar(50) not null unique comment '用户账号',
password varchar(200) not null default '123456' comment '用户密码',
real_name varchar(50) comment '用户姓名',
primary key (id)
);
mysql> describe user052;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| account | varchar(50) | NO | UNI | NULL | |
| password | varchar(200) | NO | | 123456 | |
| real_name | varchar(50) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
insert into user052 (account, real_name) values ('admin01','user01'),('admin02','user02'),('admin03','user03');
select * from user052;
联合主键
create table tb_order (
order_id int comment '订单号',
item_id int comment '流水号',
commodity_id int comment '商品编号',
quantity int comment '购买数量',
price int comment '单价',
primary key (order_id,item_id)
);
mysql> describe tb_order;
+--------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+-------+
| order_id | int | NO | PRI | NULL | |
| item_id | int | NO | PRI | NULL | |
| commodity_id | int | YES | | NULL | |
| quantity | int | YES | | NULL | |
| price | int | YES | | NULL | |
+--------------+------+------+-----+---------+-------+
5 rows in set (0.00 sec)
insert into tb_order values (10000,1,1001,1,10),(10000,2,1002,2,20),(10000,3,1003,3,003),(10001,1,1001,1,10),(10001,2,1002,2,20),(10001,3,1003,3,003);
select * from tb_order;
外键约束
# 创建主表
create table course (
c_id int auto_increment comment '专业编号',
c_name varchar(20) comment '专业名称',
primary key (c_id)
);
insert into course (c_name) values ('大数据'),('人工智能');
select * from course;
# 创建从表
create table student (
stu_id int auto_increment comment '学生编号',
stu_name varchar(20) comment '学生姓名',
c_id int comment '专业名称',
primary key (stu_id),
constraint fk_course_student foreign key (c_id) references course (c_id)
);
mysql> describe student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| stu_id | int | NO | PRI | NULL | auto_increment |
| stu_name | varchar(20) | YES | | NULL | |
| c_id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
insert into student (stu_name, c_id) values ('张三',1),('李四',2),('王五',1),('赵六',2);
select * from student;
# [23000][1452] Cannot add or update a child row: a foreign key constraint fails (`chap01`.`student`, CONSTRAINT `fk_course_student` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`))
insert into student (stu_name, c_id) values ('显眼包',3);
# [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`chap01`.`student`, CONSTRAINT `fk_course_student` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`))
delete from course where c_id = 2;
# [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`chap01`.`student`, CONSTRAINT `fk_course_student` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`))
delete from course where c_id = 2;
# 删除主表中被用于的数据 解决方案
# 1、如果子表中存在匹配的记录 删除从表外键
# 2、如果子表中存在匹配的记录 将从表数据级联删除
# 3、如果子表中存在匹配的记录 将子表的中的外键字段数据设置为null
# 4、如果子表中存在匹配的记录在不允许主表进行 update 和 delete 操作
drop table student;
cascade
# cascade 级联删除 当主表发生 update | delete 时，从表 同步 update | delete
drop table student;
create table student (
stu_id int auto_increment comment '学生编号',
stu_name varchar(20) comment '学生姓名',
c_id int comment '专业名称',
primary key (stu_id),
constraint fk_course_student foreign key (c_id) references course (c_id) on delete cascade on update cascade
);
insert into student (stu_name, c_id) values ('张三',1),('李四',2),('王五',1),('赵六',2);
select * from student;
update course set c_id = 3 where c_id = 2;
select * from course;
select * from student;
delete from course where c_id = 3;
select * from course;
select * from student;
no action
# no action 当从表中有主表中匹配的记录 则不运行主表进行 update | delete 操作
drop table student;
drop table course;
create table course (
c_id int auto_increment comment '专业编号',
c_name varchar(20) comment '专业名称',
primary key (c_id)
);
insert into course (c_name) values ('大数据'),('人工智能');
create table student (
stu_id int auto_increment comment '学生编号',
stu_name varchar(20) comment '学生姓名',
c_id int comment '专业名称',
primary key (stu_id),
constraint fk_course_student foreign key (c_id) references course (c_id) on delete no action on update no action
);
insert into student (stu_name, c_id) values ('张三',1),('李四',2),('王五',1),('赵六',2);
select * from student;
# [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`chap01`.`student`, CONSTRAINT `fk_course_student` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`))
update course set c_id = 3 where c_id = 2;
# [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`chap01`.`student`, CONSTRAINT `fk_course_student` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`))
delete from course where c_id = 2;
restrict
restrict 同 no actio ,都是立即检查外键约束
MySQL 默认使用方案
set null
# 当主表 update | delete 时，从表匹配的记录字段 设置为 null 注意 外键不能设置 not null
drop table student;
drop table course;
create table course (
c_id int auto_increment comment '专业编号',
c_name varchar(20) comment '专业名称',
primary key (c_id)
);
insert into course (c_name) values ('大数据'),('人工智能');
create table student (
stu_id int auto_increment comment '学生编号',
stu_name varchar(20) comment '学生姓名',
c_id int comment '专业名称',
primary key (stu_id),
constraint fk_course_student foreign key (c_id) references course (c_id) on delete set null on update set null
);
insert into student (stu_name, c_id) values ('张三',1),('李四',2),('王五',1),('赵六',2);
select * from student;
update course set c_id = 3 where c_id = 2;
select * from course;
select * from student;
delete from course where c_id = 1;
select * from course;
select * from student;
default
default 主表有变更时, 从表将外键列设置成一个默认的值但Innodb不能识别
总结：
主表更新从表同步，主表删除从表set null
drop table student;
drop table course;
create table course (
c_id int auto_increment comment '专业编号',
c_name varchar(20) comment '专业名称',
primary key (c_id)
);
insert into course (c_name) values ('大数据'),('人工智能');
create table student (
stu_id int auto_increment comment '学生编号',
stu_name varchar(20) comment '学生姓名',
c_id int comment '专业名称',
primary key (stu_id),
constraint fk_course_student foreign key (c_id) references course (c_id) on delete set null on update cascade
);
insert into student (stu_name, c_id) values ('张三',1),('李四',2),('王五',1),('赵六',2);
select * from student;
修改表
修改表名称
格式 alter table 旧表名称 rename to 新表名称
alter table tb_user rename to user;
show tables;
修改字段数据类型
格式 alter table 表名称 modify 字段名称 新的数据类型
desc user;
alter table user modify user_name char(20);
desc user;
修改字段数据约束
格式 alter table 表名称 modify 字段名称 新数据类型 新约束
desc user;
alter table user modify user_name varchar(50) not null comment '姓名';
desc user;
修改字段名称
格式 alter table 表名称 change 旧字段名称 新字段名称 新数据类型 新约束
desc user;
alter table user change user_name name char(20) null comment '姓名';
desc user;
添加字段
格式 alter table 表名称 add 字段名称 数据类型 约束
desc user;
alter table user add gender enum('F','M') default 'M' comment '性别 F代表女 M代表男 默认值M';
alter table user add date_of_birth date comment '出生日期' first;
alter table user add id_card char(18) comment '出生日期' after user_id;
desc user;
删除字段
格式 alter table 表名称 drop 字段名称
desc user;
alter table user drop date_of_birth;
desc user;
调整字段位置
格式 alter table 表名称 modify 字段名称 数据类型 约束
desc user;
alter table user modify id_card char(18) not null comment '出生日期' first;
alter table user modify user_id int not null comment '用户编号' after name;
desc user;
修改存储引擎
格式 alter table 表名称 engine = 引擎名称
show create table user;
alter table user engine = myisam;
show create table user;
修改字符集
格式 alter table 表名称 character set 字符集名称
show create table user;
alter table user character set gbk;
show create table user;
数据备份与还原
数据备份
使用
mysqldump格式：
msyqldump需要备份的数据库名称 -h 服务器地址 -u 账号 -p · 备份文件存储路径
C:\Users\Administrator>mysqldump chap01 -h localhost -uroot -p > d:\chap01.sql
Enter password: ******
C:\Users\Administrator>
数据还原
使用
mysql格式mysql需要被还原的库 -h 服务器地址 -u 账号 -p < sql文件存储路径使用
source格式sourcesql文件存储路径注意：
source需要在mysql命令行下在需要被环境的数据库执行
C:\Users\Administrator>mysql chap01 -h localhost -uroot -p < d:\chap01.sql
Enter password: ******
C:\Users\Administrator>
C:\Users\Administrator>mysql -h localhost -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use chap01;
Database changed
mysql> source d:/chap01.sql;
mysql>
注意：windows 路劲分隔符
d:/chap01.sql;
d:
\\chap01.sql;
数据库查询语言 DQL
准备数据
C:\Users\Administrator> mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
create database chap02;
use chap02;
source d:/chap02_data.sql;
单表查询
查询所有
select * from fruit;
按字段查询
select f_name,f_price from fruit;
# 2.2.3.1 查询香蕉的价格
select f_name,f_price from fruit where f_name = 'banana';
# 2.2.3.2 查询价格为10.30的水果
select f_name,f_price from fruit where f_price = 10.30;
# 2.2.3.4 查询价格 高于5.2 低于10.30 的水果
select f_name,f_price from fruit where f_price > 5.2 && f_price < 10.30;
select f_name,f_price from fruit where f_price > 5.2 and f_price < 10.30;
select f_name,f_price from fruit where f_price >= 5.2 && f_price <= 10.30;
select f_name,f_price from fruit where f_price >= 5.2 and f_price <= 10.30;
# 2.2.3.5 查询价格 高于5.2 低于10.30 的水果
# select f_name,f_price from fruit where f_price >= 5.2 and f_price <= 10.30;
select f_name,f_price from fruit where f_price between 5.2 and 10.30;
# 2.2.3.6 查询供应商 s_id 为 101 和 102 供应的水果
select s_id,f_name,f_price from fruit where s_id = 101 || s_id = 102;
select s_id,f_name,f_price from fruit where s_id = 101 or s_id = 102;
# 查询价格在 5.2 与 10.2 之间 供应商 s_id 为 101 和 102 供应的水果
select s_id,f_name,f_price
from
fruit
where
(f_price between 5.2 and 10.30)
and
(s_id = 101 or s_id = 102);
select s_id,f_name,f_price
from
fruit
where
(s_id = 101 or s_id = 102)
and
(f_price between 5.2 and 10.30);
in 查询
# 2.2.4.1 查询供应商 s_id 为 101 和 102 供应的水果
select s_id,f_name,f_price from fruit where s_id = 101 or s_id = 102;
select s_id,f_name,f_price from fruit where s_id in ( 101 , 102);
# 2.2.4.2 查询供应商 s_id 不是 101 和 102 供应的水果
select s_id,f_name,f_price from fruit where s_id != 101 and s_id != 102;
select s_id,f_name,f_price from fruit where s_id not in ( 101 , 102);
null 查询
# 2.2.5.1 # 查询没有绑定邮箱的消费者
select * from customer where c_email is null;
# 2.2.5.2 # 查询没有绑定邮箱的消费者
select * from customer where c_email is not null;
模糊查询
# 2.2.6.1 查询所有名称 b 开头的水果
# % 代表零个或多个字符
select * from fruit where f_name like 'b%';
# 2.2.6.2 查询所有名称 含有 g 的水果
select * from fruit where f_name like '%g%';
# 2.2.6.3 查询所有名称 b开头 y结尾 的水果
select * from fruit where f_name like 'b%y';
# 2.2.6.4 查询所有名称 y结尾 y的前面有5个字符 的水果
# 一个下划线代表一个字符
select * from fruit where f_name like '_____y';
排序
# 2.2.7.1 按照水果价格升序显示
select * from fruit order by f_price;
select * from fruit order by f_price asc;
# 2.2.7.2 按照水果价格降序显示
select * from fruit order by f_price desc;
# 2.2.7.3 按照供应商升序 如果供应商相同的情况下按照价格降序
select * from fruit order by s_id,f_price desc ;
聚合函数
# 2.2.8.1.1 统计水果种类数量
select count(*) as 'total' from fruit;
select count(1) as 'total' from fruit;
select count(f_id) as 'total' from fruit;
# 2.2.8.1.2 统计消费者人数
# count(*) 只有该行有记录就统计
# count(字段名) 先判断 该字段的值是否为空 如果为空不在统计范围内
select count(1) as 'total' from customer;
select count(c_email) as 'total' from customer;
# 2.2.8.2 统计单价最高
select max(f_price) as 'max_price' from fruit;
# [42000][1140] In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'chap02.fruit.f_name'; this is incompatible with sql_mode=only_full_group_by
# 解决方法1：修改配置文件 取消严格模式
# 解决方法2：使用 group by 查询
select f_name,max(f_price) as 'max_price' from fruit;
# 2.2.8.3 统计单价最低
select min(f_price) as 'min_price' from fruit;
# 2.2.8.4 统计单价总和
select sum(f_price) as 'sum_price' from fruit;
# 2.2.8.5 统计平均价格
select avg(f_price) as 'avg_price' from fruit;
去重
# 2.2.9.1 在水果表查询所有供应商 id
select distinct s_id from fruit;
# 2.2.9.2 在水果表中统计所有供应商数量
select count(distinct s_id) as 'total_s_id' from fruit;
分组
# 2.2.10.1 每个供应商供应水果种类数量
select s_id,count(*) as 'total' from fruit group by s_id;
# 2.2.10.2 每个供应商供应水果种类数量 按照供应水果种类数量降序
select s_id,count(*) as 'total' from fruit group by s_id order by total desc ;
# 2.2.10.3 每个供应商供应水果种类数量 按照供应水果种类数量降序 如果供应水果种类数量相同则按照供应商编号降序
select s_id,count(*) as 'total' from fruit group by s_id order by total desc ,s_id desc ;
# 2.2.10.4 每笔消费的总金额
select * from orderitem;
# 2.2.10.4.1 计算每笔流水的小计 小计 = 数量 × 单价
# 2.2.10.4.2 计算每笔流水的的小计 小计 = 数量 × 单价 分组
# 2.2.10.4.3 计算每笔流水的总金额 小计的和
# 2.2.10.5 每笔消费的总金额 降序
# 2.2.10.6 每笔消费的总金额 高于200 降序
分页查询
-- LIMIT 起始位置 , 查询数量
-- (查询页面号 - 1) * 每页记录数 = 起始位置
-- 总页数 = if(总记录数 % 每页记录数 == 0) ? 总记录数 / 每页记录数 : (总记录数 / 每页记录数) + 1
select * from fruit limit 0 , 5;
select * from fruit limit 5, 5;
select * from fruit limit 50, 5;
总结
| 名称 | 符号 |
|---|---|
| 比较查询 | >，<，<=，>=，=，!= |
| 范围查询 | between and， in，limit |
| 模糊查询 | like |
| null查询 | is null，is not null |
| 逻辑查询 | and，or，!= |
- sql语句编写顺序 select 字段 from 表名 where 条件 group by having order by limit
- sql语句执行顺序 from表 -> where -> group by -> having -> order by -> limit
多表查询
子查询
# 2.3.1 子查询 查找水果名为苹果的供应商名 子查询方式查找
# 子查询 （嵌套结构）
# 子查询嵌入在主查询中
# 子查询辅助主查询，要么充当条件，要么充当数据源（数据表）
# 子查询可以是独立的语句，自己就是一条完整的 select 语句
# 编写步骤 先写子查询再写主查询最后在将子查询与主查询拼接
select s.s_id,s.s_name from supplier s where s.s_id = 102;
select f.s_id,f.f_name from fruit f where f_name = 'apple';
select s.s_id,s.s_name from supplier s where s.s_id = (select f.s_id from fruit f where f_name = 'apple');
# 练习： 每笔消费的总金额 高于200 的订单的消费者信息
select * from customer where c_id in (
select c_id from `order` where o_num in (
select o_num from orderitem group by o_num having sum((quantity * item_price)) > 200
)
);
交叉查询 cross join
产生笛卡儿积
SELECT
`s_name`,
`s_city`,
`s_zip`,
`s_call`,
`s`.`s_id`,
`f`.`s_id`,
`f_id`,
`f_name`,
`f_price`
FROM
`supplier` AS s,
`fruit` AS f
SELECT
`s_name`,
`s_city`,
`s_zip`,
`s_call`,
`s`.`s_id`,
`f`.`s_id`,
`f_id`,
`f_name`,
`f_price`
FROM
`supplier` AS s,
`fruit` AS f
where s.s_id = f.s_id;
SELECT
`s_name`,
`s_city`,
`s_zip`,
`s_call`,
`s`.`s_id`,
`f`.`s_id`,
`f_id`,
`f_name`,
`f_price`
FROM
`supplier` AS s cross join `fruit` AS f
where s.s_id = f.s_id;
内连接 inner join
- 关键字 INNER JOIN
- 连接查询条件 ON
select * from `order` o inner join customer c on o.c_id = c.c_id;
左外连接 left outer join
以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
select * from `order` o left outer join customer c on o.c_id = c.c_id;
右外连接 right outer join
以右表为主根据条件查询右表数据，如果根据条件查询左表数据不存在使用null值填充
select * from `order` o right outer join customer c on o.c_id = c.c_id;
全连接 full join
# FULL JOIN的作用
# 在关系型数据库中，我们经常需要将两个或多个表格中的数据进行合并。
# FULL JOIN是一种将两个表格中的所有数据进行合并的操作，包括左表格、右表格以及两个表格中都存在的数据。
# FULL JOIN可以帮助我们更好地理解数据之间的关系，从而更加高效地进行数据分析和处理。
# MySQL中没有FULL JOIN的原因
# 尽管FULL JOIN在关系型数据库中非常有用，但是MySQL并不支持FULL JOIN操作。
# 这是因为MySQL采用的是基于左表格和右表格的JOIN操作，而没有提供FULL JOIN的实现方式。
# 因此，如果我们需要使用FULL JOIN操作，就需要通过其他方式来实现。
# 虽然MySQL不支持FULL JOIN操作，但是我们可以通过使用UNION和LEFT JOIN操作来实现FULL JOIN的效果。具体方法如下：
# （1）使用UNION操作将左表格和右表格中的数据进行合并。
# （2）使用LEFT JOIN操作将左表格和右表格中的数据进行连接，将左表格和右表格中都存在的数据进行合并。
# （3）将UNION操作和LEFT JOIN操作的结果进行合并，即可得到FULL JOIN的结果。
# 需要注意的是，在使用UNION操作和LEFT JOIN操作时，我们需要保证两个表格中的数据结构相同，否则可能会出现错误。
union all
# 两张表的数据结构要一致 不会去除重复数据
# 查询水果表中 供应商id 是 101 和 102 水果信息
select * from fruit where s_id = 101 or s_id = 102;
select * from fruit where s_id in (101, 102);
select * from fruit where s_id = 101
union all
select * from fruit where s_id = 102;
select * from fruit where s_id = 101
union all
select * from fruit where s_id = 101;
union
# 过滤重复数据
select * from fruit where s_id = 101
union
select * from fruit where s_id = 101;
some any all
# 2.3.4.1 some
select * from tbl1 where num1 > some(select * from tbl2);
# 2.3.4.2 any
select * from tbl1 where num1 > any(select * from tbl2);
# 2.3.4.3 all
select * from tbl1 where num1 > all(select * from tbl2);
exits
# 2.3.5 exists
# 查找供应商 名字 是否存在
select exists (SELECT `s_id` FROM `supplier` WHERE `s_name` = 'ACME' ) as isExists;
select exists (SELECT `s_id` FROM `supplier` WHERE `s_name` = '李昊哲' ) as isExists;
# Can't create database 'chap02'; database exists
create database if not exists chap02;
drop database if exists chap03;
create table if not exists login (
id int auto_increment comment '主键',
phone_number varchar(11) unique not null comment '手机号',
primary key (id)
);
insert into login (phone_number) value ('15311484568');
insert into login (phone_number)
select '15311484568'
where not exists
(select phone_number from login where phone_number = '15311484568');
insert into login (phone_number)
select '18515697037'
where not exists
(select phone_number from login where phone_number = '18515697037');
select * from login;
自关联查询
desc region_address;
select * from region_address where region_name = '河南';
select * from region_address where parent_code = 41;
select * from region_address where parent_code = 4107;
练习
select * from tb_category;
select count(*) from tb_category;
# 子查询作为查询条件
# 一级导航
select * from tb_category where parent_id = 0;
# 二级导航
select * from tb_category where parent_id in (select id from tb_category where parent_id = 0);
# 二级导航
select * from tb_category where parent_id in (select id from tb_category where parent_id in (select id from tb_category where parent_id = 0))
# 子查询作为数据源 也就是 当中一张数据表看待
# 一级导航
select * from tb_category where parent_id = 0;
# 二级导航
select t1.id,name,goods_num,is_menu,is_menu,seq,parent_id,template_id from tb_category t1
inner join
(select id from tb_category where parent_id = 0) t2
on t1.parent_id = t2.id;
# 将一级导航和该导航下的二级导航数据封装一次性送到前端
select t1.id,name,goods_num,is_menu,is_menu,seq,parent_id,template_id from tb_category t1
inner join
(select id from tb_category where parent_id = 0) t2
on t1.parent_id = t2.id;
select parent_id,group_concat(id) from tb_category where parent_id = 0 group by parent_id;
select t4.id,name,goods_num,is_menu,is_menu,seq,t4.parent_id,template_id,t3.ids,t3.names from tb_category t4 inner join
(
select parent_id,group_concat(t1.id) ids,group_concat(t1.name) names from tb_category t1
inner join
(select id from tb_category where parent_id = 0) t2
on t1.parent_id = t2.id
group by parent_id
) t3
on t3.parent_id = t4.id;
select t4.id,name,goods_num,is_menu,is_menu,seq,t4.parent_id,template_id,t3.ids,t3.names from tb_category t4 inner join
(
select parent_id,json_array(group_concat(t1.id)) ids,json_array(group_concat(t1.name)) names from tb_category t1
inner join
(select id from tb_category where parent_id = 0) t2
on t1.parent_id = t2.id
group by parent_id
) t3
on t3.parent_id = t4.id;
select t4.id,name,goods_num,is_menu,is_menu,seq,t4.parent_id,template_id,json_array(t3.ids) id_list,json_array(t3.names) name_list from tb_category t4 inner join
(
select parent_id,group_concat(t1.id) ids,group_concat(t1.name) names from tb_category t1
inner join
(select id from tb_category where parent_id = 0) t2
on t1.parent_id = t2.id
group by parent_id
) t3
on t3.parent_id = t4.id;
函数
# 1、函数
# 数学函数、字符串函数、日期时间函数、条件判断、循环函数、系统信息函数、加密解密函数、开窗函数
# 1.1 准备数据
# mysql -uroot -p
create database chap03;
use chap03;
source d:/chap03_data.sql;
show tables;
# dual
select '勿忘国耻，振兴中华' as 'slogan' from dual;
聚合函数
数学函数
# 1.3 数学函数
# 1.3.1 绝对值
select abs(3),abs(-3),abs(-3.3) from dual;
# 1.3.2 圆周率
select pi() as 'pi' from dual;
# 1.3.3 平方根
select sqrt(9),sqrt(20),sqrt(-9) from dual;
# 1.3.4 取模运算
select mod(9,2),mod(6,2),mod(-6,2),mod(-9,2) from dual;
# 1.3.5 向上取整
select ceil(3.3),ceil(-3.9) from dual;
# 1.3.6 向上取整
select floor(3.9),floor(-3.3) from dual;
# 1.3.7 四舍五入 (x,y) x 是原始值 y 保留小数后面几位
select round(3.9),round(3.3),round(-3.9),round(-3.3) from dual;
select round(3.1415926,2) from dual;
select round(3.145,2) from dual;
# 1.3.8 随机数 范围 0~1之间的小数
select rand(),rand(),rand() from dual;
select round(rand() ,2),round(rand() ,2),round(rand() ,2) from dual;
# 1.3.9 随机数 参数x 最接近参数x的随机值
select rand(10),rand(-10) from dual;
# 1.3.10 符号函数 参数值为整数、零、负数返回结果依次为1、0、-1
select sign(3), sign(0) ,sign(-3)from dual;
# 1.3.11 幂运算函数
# 1.3.11.1 幂运算函数 (x,y)
select pow(2,2),pow(3,3), pow(2,-2),pow(-2,2),pow(-2,3) from dual;
# 1.3.11.2 幂运算函数 (x,y)
select power(2,2),power(3,3), power(2,-2),power(-2,2),power(-2,3) from dual;
# 1.3.11.3 幂运算函数 e 的乘方
select exp(2),exp(3) from dual;
# 1.3.12 对数运算函数
select log(3),log(0),log(-3) from dual;
# 1.3.13 角度与弧度互转函数
# 1.3.13.1 角度转弧度
select radians(90),radians(180) from dual;
# 1.3.13.2 弧度转角度
select degrees(pi()),degrees(pi()/2) from dual;
# 1.3.14 正弦函数
select sin(0),sin(1),sin(pi()) from dual;
# 1.3.15 正余弦函数
select cos(0),cos(1),cos(pi()) from dual;
# 1.3.16 余弦函数
select acos(0),acos(1),acos(pi()) from dual;
# 1.3.17 正切函数
select tan(0.3) from dual;
# 1.3.18 反正切函数
select atan(0.3) from dual;
# 1.3.19 反正切函数
select cot(3.23) from dual;
字符串函数
# 1.4 字符串函数
# 1.4.1 char_length 计算字符串字符数量
select char_length('我爱你中国') from dual;
# 1.4.2 length 计算字符串长度 单位字节
select length('我爱你中国') from dual;
select char_length('I love China') from dual;
select length('I love China') from dual;
# 1.4.3 concat 字符串拼接
select concat('I',' ', 'love', ' ', 'China') from dual;
select concat('1983','-', '11', '-', '22') from dual;
# 注意：在oracle中concat只能传两个参数
select concat('1983',concat('-',concat('11',concat('-', '22')))) from dual;
# oracle 语法 select '1983'||'-'||'11'||'-'||'22' from dual;
# 1.4.4 concat_ws 带连接符号的字符串拼接 第一个参数为连接符
select concat_ws('-','1983', '11','22') from dual;
select concat_ws(' ','I', 'love', 'China') from dual;
# 1.4.5 insert 字符串替换
# 第一个参数是源字符串，第二个参数是该字符串的第几个字符,第三次参数是长度，第四个参数是替换后的内容
select insert ('桃李不言下自成蹊',2,4,'what') from dual;
# 1.4.6 lower 字符串中字母转小写
select lower('I Love CHINA') from dual;
# 1.4.7 upper 字符串中字母转大写
select upper('i love china') from dual;
# 1.4.8 left 返回字符串中左边的字符
select left('桃李不言下自成蹊',2) from dual;
# 1.4.9 right 返回字符串中有边的字符
select right('桃李不言下自成蹊',2) from dual;
# 1.4.10 lpad 字符串左边填充
# 第一个参数是原字符串 第二个参数填充后的字符串长度 第三个参数是填充值
select lpad('桃李不言下自成蹊',3,'李昊哲') from dual;
select lpad('桃李不言下自成蹊',char_length('桃李不言下自成蹊') + 3,'李昊哲') from dual;
# 注意：填充后的长度 如果超过了 源字符串长度+填充内容长度 填充内容会重复填充
select lpad('桃李不言下自成蹊',char_length('桃李不言下自成蹊') + 6,'李昊哲') from dual;
# 1.4.11 rpad 字符串右边填充
select rpad('桃李不言下自成蹊',3,'李昊哲') from dual;
select rpad('桃李不言下自成蹊',char_length('桃李不言下自成蹊') + 3,'李昊哲') from dual;
# 注意：填充后的长度 如果超过了 源字符串长度+填充内容长度 填充内容会重复填充
select rpad('桃李不言下自成蹊',char_length('桃李不言下自成蹊') + 6,'李昊哲') from dual;
# 1.4.12 ltrim 删除字符串左边空格
select ' 桃 李 不 言 下 自 成 蹊 ' from dual;
select ltrim(' 桃 李 不 言 下 自 成 蹊 ') from dual;
# 1.4.13 rtrim 删除字符串右边空格
select char_length(' 桃 李 不 言 下 自 成 蹊 ') from dual;
select rtrim(' 桃 李 不 言 下 自 成 蹊 ') from dual;
select char_length(rtrim(' 桃 李 不 言 下 自 成 蹊 ')) from dual;
# 1.4.14 trim 删除字符串左右边空格
select trim(' 桃 李 不 言 下 自 成 蹊 ') from dual;
select char_length(' 桃 李 不 言 下 自 成 蹊 ') from dual;
select char_length(trim(' 桃 李 不 言 下 自 成 蹊 ')) from dual;
# 1.4.15 trim(s1 from s2) 删除左右两端指定字符串函数
select trim('xyz' from 'xyzabcxyzabcxyzabcxyz') from dual;
# 1.4.16 repeat 重复生成相同的字符串
select repeat('爱我中华',3) from dual;
# 1.4.17 space 生成由空格组成的字符串
select concat_ws(space(6),'爱','我','中','华') from dual;
# 1.4.18 replace 字符串替换
select '我爱你你爱我，我们一起甜蜜蜜' from dual;
select insert('我爱你你爱我，我们一起甜蜜蜜',4,3,'李昊哲') from dual;
select replace('我爱你你爱我，我们一起甜蜜蜜','你爱我','李昊哲') from dual;
select replace('你爱我你爱我，我们一起甜蜜蜜','你爱我','李昊哲') from dual;
select replace(' 桃 李 不 言 下 自 成 蹊 ',' ','') from dual;
# 1.4.19 ascii 将 ascii 字符转为 ascii 码 字符串替换
select ascii('a') from dual;
select ascii('b') from dual;
# 1.4.20 strcmp 比较字符大小 返回值为正数 为第一个参数比第二个参数大 反之第二个参数比第一个参数大 返回值为零两个参数一样大
select strcmp('a','b') from dual;
select strcmp('李','王') from dual;
# 1.4.21 substring 字符串截取 第一个参数为原字符串 第二个参数为截取位置 位置从1开始 第三个参数为截取长度
select substring('桃李不言下自成蹊',2,3) from dual;
select substr('桃李不言下自成蹊',2,3) from dual;
# 1.4.22 mid 字符串截取 第一个参数为原字符串 第二个参数为截取位置 位置从1开始 第三个参数为截取长度
select mid('桃李不言下自成蹊',2,3) from dual;
# 1.4.23 locate 查找字符串中指定字符串第一次出现的位置 第一个参数是字符串中出现的字符串 返回值为零代表没有出现过
select locate('你爱我','我爱你你爱我我爱你你爱我') from dual;
select locate('蜜雪冰城','我爱你你爱我我爱你你爱我') from dual;
# 1.4.24 reverse 逆序
select reverse('我爱你') from dual;
# 1.4.25 elt 返回指定位置的字符串 第一个参数是指定位置值
select elt(3,'我','真','的','很','爱','你') from dual;
日期时间函数
# 1.5 日期时间函数
# 1.5.1 获取当前日期
select curdate(),current_date() from dual;
# 1.5.2 获取当前时间
select curtime(),current_time() from dual;
# 1.5.3 获取当前日期和时间
select sysdate(),now(),localtime(),current_timestamp() from dual;
# 1.5.4 获取当前时间戳
select unix_timestamp(), unix_timestamp(now()),unix_timestamp('1983-11-22 20:30:00'),from_unixtime(438352200) from dual;
# 1.5.5 获取UTC时间
select utc_date(),utc_time(),utc_timestamp() from dual;
# 1.5.6 单独获取年、月、日、小时、分钟、秒、星期
select year(now()), month(now()),day(now()),dayofmonth(now()),hour(now()),minute(now()),second(now()) from dual;
# weekday 从零开始
select week(now()), weekday(now()),weekofyear(now()) from dual;
# 1.5.7 提取获取年、月、日、小时、分钟、秒、星期
select year('1983-11-22 20:30:00') from dual;
select extract(year from '1983-11-22 20:30:00') from dual;
# 1.5.8 时间加减操作
select now(),date_add(now(), interval 1 year ) from dual;
select now(),date_add(now(), interval 1 month ) from dual;
select now(),date_add(now(), interval 1 day ) from dual;
select now(),date_add(now(), interval 1 hour ) from dual;
select now(),date_add(now(), interval 1 minute ) from dual;
select now(),date_add(now(), interval 1 second ) from dual;
select now(),subdate(now(), interval 1 year ) from dual;
select now(),subdate(now(), interval 1 month ) from dual;
select now(),subdate(now(), interval 1 day ) from dual;
select now(),subdate(now(), interval 1 hour ) from dual;
select now(),subdate(now(), interval 1 minute ) from dual;
select now(),subdate(now(), interval 1 second ) from dual;
# 1.5.9 计算时间间隔天数
select datediff('2023-09-28 15:01:01','2023-09-27 16:03:03') from dual;
# 1.5.10 日期格式化输出
# %Y 4位年份
# %y 2位年份
# %M 月份全名
# %b 月份缩写
# %m 2位月份
# %D 当月第几天 数字th
# %d 2位日期
# %H 24小时
# %h 12小时
# %i 2位分钟
# %s 2位秒
# %W 星期全名
# %w 星期缩写
select now(),date_format(now(),'%Y/%m/%d %H:%i:%s') from dual;
# 1.5.11 时间格式化输出
select now(),time_format(now(),'%Y/%m/%d %H:%i:%s') from dual;
选择函数
# 1.6 选择判断函数
# 1.6.1 第一个参数是条件表达式 条件表达式返回值为true返回第二个参数 条件表达式返回值为false返回第三个参数
select if(true,'成立','不成立') from dual;
select if(false,'成立','不成立') from dual;
select if(1,'成立','不成立') from dual;
select if(0,'成立','不成立') from dual;
select if(-1,'成立','不成立') from dual;
# 1.6.2
# use chap04;
# select score from scores;
# # 90以上A 80~90B 70~80C 60~70D 60以下E
# select s_id,c_id,
# case
# when score >= 90 then 'A'
# when score >= 80 then 'B'
# when score >= 70 then 'C'
# when score >= 60 then 'D'
# else 'E'
# end as 'level'
# from scores;
开窗函数
能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给该行
Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
-- 其中Function(arg1,..., argn) 可以是下面分类中的任意一个
-- 聚合函数：比如sum max min avg count等
-- 排序函数：比如row_number rank dense_rank等
-- 分析函数：比如lead lag first_value last_value等
-- OVER [PARTITION BY <...>] 类似于group by 用于指定分组 每个分组你可以把它叫做窗口
-- 如果没有PARTITION BY 那么整张表的所有行就是一组
-- [ORDER BY <....>] 用于指定每个分组内的数据排序规则 支持ASC、DESC
-- [<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行
# 1.7.1 查询各科成绩前三名的学生
-- row_number() over () 连续序号
-- over()里头的分组以及排序的执行晚于 where 、group by、order by 的执行。
select c_id ,score, row_number() over () as 'num' from scores;
# partition by 作用相当于 group by
select c_id ,score, row_number() over (partition by c_id) as 'num' from scores;
# order by 写在 开窗函数内 局部排序
select c_id ,score, row_number() over (partition by c_id order by score desc ) as 'num' from scores;
-- row_number() over () 连续序号
-- over()里头的分组以及排序的执行晚于 where 、group by、order by 的执行。
select * from
(
select s_id,c_id ,score,
row_number() over (partition by c_id order by score desc ) as 'num'
from scores
) t where t.num < 4;
-- rank() over () 排名 跳跃排序 序号不是连续的
select * from
(
select s_id,c_id ,score,
rank() over (partition by c_id order by score desc ) as 'num'
from scores
) t where t.num < 4;
-- dense_rank() over () 排名 连续排序
select * from
(
select s_id,c_id ,score,
dense_rank() over (partition by c_id order by score desc ) as 'num'
from scores
) t where t.num < 4;
行列转换
列转行
create table test (
stu_name varchar(50),
course_name varchar(50),
score int
);
insert into test values ('张三','语文','80'),('张三','数学','90'), ('李四','语文','85'),('李四','数学','95');
select * from test;
# 1.9.1 列转行
create table test (
stu_name varchar(50),
course_name varchar(50),
score int
);
insert into test values ('张三','语文','80'),('张三','数学','90'), ('李四','语文','85'),('李四','数学','95');
select * from test;
select stu_name,
case when course_name = '语文' then score end as 'yuwen',
case when course_name = '数学' then score end as 'shuxue'
from test;
-- null 在 比较的时候 是最小值
select stu_name,
max(case when course_name = '语文' then score end) as 'yuwen',
max(case when course_name = '数学' then score end) as 'shuxue'
from test
group by stu_name;
行转列
# 1.9.2 行转列
create table sales (
emp_name varchar(50),
january int,
february int,
March int
);
insert into sales values ('张三',1000,2000,3000),('李四',1500,2500,3500);
select * from sales;
select emp_name, 'january' as 'yue', january from sales
union all
select emp_name,'february' as 'yue', february from sales
union all
select emp_name,'March' as 'yue', March from sales
order by emp_name;
select emp_name,yue_list,
substring_index(substring_index(yue_list,',',1),',',-1) as 'january',
substring_index(substring_index(yue_list,',',2),',',-1) as 'february',
substring_index(substring_index(yue_list,',',3),',',-1) as 'March'
from
(select emp_name ,group_concat(t.yue) as yue_list from
(select emp_name,january as 'yue' from sales
union all
select emp_name,february as 'yue' from sales
union all
select emp_name,March as 'yue' from sales
order by emp_name) t
group by t.emp_name) t1;
身份证
# 1.10 身份证
# 第1位和第2位数字代表出生省份地区码
# 第3位和第4位数字代表出生省份所辖城市地区码
# 第5位和第6位数字代表出生省份所辖城市所辖区县地区码
# 第7、8、9、10位数字代表出生出生年份
# 第11、12位数字代表出生出生月份
# 第13、14位数字代表出生出生日期
# 第15、16位顺序码
# 第17位数字代表性别 奇数代表男性 偶数代表女性
# 第18位数字代表校验码m，用于验证身份证号码的合法性
# 1.10.1 计算性别
select * from person;
select * ,substring(id_card,17,1) as 'gender' from person;
select * ,mod(substring(id_card,17,1),2) as 'gender' from person;
select * ,if(mod(substring(id_card,17,1),2),'男','女') as 'gender' from person;
select
if(mod(substring(id_card,17,1),2),'男','女') as 'gender' ,
count(*)
from person group by gender;
# 1.10.2 按照出生日期排序
select * ,substring(id_card,7,8) as 'date_of_birth' from person order by date_of_birth desc;
# 1.10.3 计算年龄
# 方式一：子查询
select id, id_card, first_name, last_name, mobile, auth_text, date_of_birth,
# 当前月份 - 出生月份 > 0 说明已经过完生日了 直接返回 之前计算的年纪
if(month(curdate()) - month(date_of_birth) > 0,age,
# 当前月份 - 出生月份 < 0 说明还没有过生日 直接返回 年纪 = 之前计算的年纪 - 1
if(month(curdate()) - month(date_of_birth) < 0,age - 1,
# 当前月份 - 出生月份 = 0
# 当前日期 - 出生日期 > 0 说明已经过完生日了 直接返回 之前计算的年纪
# 当前日期 - 出生日期 <= 0 说明还没有过生日 直接返回 年纪 = 之前计算的年纪 - 1
if(day(curdate()) - day(date_of_birth) > 0,age,age - 1)
)
) as 'age'
from
(select id, id_card, first_name, last_name, mobile, auth_text, date_of_birth,
# 当前年份 - 出生日期 = 年纪
(year(curdate()) - year(date_of_birth)) as 'age'
from
# 从身份证截取出生日期
(select * ,substring(id_card,7,8) as 'date_of_birth' from person ) t1) t2;
# 方式二：反复调用时间函数
select id, id_card, first_name, last_name, mobile, auth_text,
# 当前月份 - 出生月份 > 0 说明已经过完生日了 直接返回 之前计算的年纪
if(month(curdate()) - month(substring(id_card,7,8)) > 0,
month(curdate()) - month(substring(id_card,7,8)),
# 当前月份 - 出生月份 < 0 说明还没有过生日 直接返回 年纪 = 之前计算的年纪 - 1
if(month(curdate()) - month(substring(id_card,7,8)) < 0,
month(curdate()) - month(substring(id_card,7,8)) - 1,
# 当前月份 - 出生月份 = 0
# 当前日期 - 出生日期 > 0 说明已经过完生日了 直接返回 之前计算的年纪
# 当前日期 - 出生日期 <= 0 说明还没有过生日 直接返回 年纪 = 之前计算的年纪 - 1
if(day(curdate()) - day(substring(id_card,7,8)) > 0,
month(curdate()) - month(substring(id_card,7,8)),
month(curdate()) - month(substring(id_card,7,8)) - 1)
)
) as 'age'
from person;
json
select '{"name":"李昊哲","firends":["温柔","知性","美丽","大方"]}' from dual;
select json_extract('{"name":"李昊哲","firends":["温柔","知性","美丽","大方"]}','$.name') as name from dual;
select json_extract('{"name":"李昊哲","firends":["温柔","知性","美丽","大方"]}','$.firends') as firends from dual;
select json_extract('{"name":"李昊哲","firends":["温柔","知性","美丽","大方"]}','$.firends[1]') as firend from dual;
视图
create view person_view as select * from person;
select * from person_view;
update person set mobile = '13427468302' where id = 1 ;
update person_view set mobile = '13427468303' where id = 1 ;
drop view person_view;
create view person_view as
select id, id_card, first_name, last_name, mobile, auth_text, date_of_birth,
# 当前月份 - 出生月份 > 0 说明已经过完生日了 直接返回 之前计算的年纪
if(month(curdate()) - month(date_of_birth) > 0,age,
# 当前月份 - 出生月份 < 0 说明还没有过生日 直接返回 年纪 = 之前计算的年纪 - 1
if(month(curdate()) - month(date_of_birth) < 0,age - 1,
# 当前月份 - 出生月份 = 0
# 当前日期 - 出生日期 > 0 说明已经过完生日了 直接返回 之前计算的年纪
# 当前日期 - 出生日期 <= 0 说明还没有过生日 直接返回 年纪 = 之前计算的年纪 - 1
if(day(curdate()) - day(date_of_birth) > 0,age,age - 1)
)
) as 'age',mod(substring(id_card,17,1),2) as 'gender'
from
(select id, id_card, first_name, last_name, mobile, auth_text, date_of_birth,
# 当前年份 - 出生日期 = 年纪
(year(curdate()) - year(date_of_birth)) as 'age'
from
# 从身份证截取出生日期
(select * ,substring(id_card,7,8) as 'date_of_birth' from person ) t1) t2;
create table person_new
(
id int auto_increment comment '主键'
primary key,
id_card varchar(18) not null,
first_name varchar(15) not null,
last_name varchar(15) not null,
mobile varchar(11) not null,
auth_text varchar(255) null
);
insert into person_new select * from person;
select * from person_new;
事务
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
1 row in set (0.00 sec)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED |
+--------------------------------+
1 row in set (0.00 sec)
mysql>
| 隔离级别 | 脏读 | 不可重复读 | 幻影读 |
|---|---|---|---|
| READ-UNCOMMITTED | √ | √ | √ |
| READ-COMMITTED | × | √ | √ |
| REPEATABLE-READ | × | × | √ |
| SERIALIZABLE | × | × | × |
表分区
表分区介绍
MySQL 数据库中的数据是以文件的形势存在磁盘上的，
默认放在
/var/lib/mysql/目录下面，我们可以通过
show variables like '%datadir%';命令来查看：
show variables like '%datadir%';
我们进入到这个目录下，就可以看到我们定义的所有数据库了，
一个数据库就是一个目录，一个库中，有其对应的表的信息，如下：
cd /var/lib/mysql
ls
优点：
- 查询性能提升：分区可以将大表划分为更小的部分，查询时只需扫描特定的分区，而不是整个表，从而提高查询性能。特别是在处理大量数据或高并发负载时，分区可以显著减少查询的响应时间。
- 管理和维护的简化：使用分区可以更轻松地管理和维护数据。可以针对特定的分区执行维护操作，如备份、恢复、优化和数据清理，而不必处理整个表。这简化了维护任务并减少了操作的复杂性。
- 数据管理灵活性：通过分区，可以根据业务需求轻松地添加或删除分区，而无需影响整个表。这使得数据的增长和变化更具弹性，可以根据需求进行动态调整。
- 改善数据安全性和可用性：可以将不同分区的数据分布在不同的存储设备上，从而提高数据的安全性和可用性。例如，可以将热数据放在高速存储设备上，而将冷数据放在廉价存储设备上，以实现更高的性能和成本效益。
缺点：
- 复杂性增加：分区引入了额外的复杂性，包括分区策略的选择、表结构的设计和维护、查询逻辑的调整等。正确地设置和管理分区需要一定的经验和专业知识。
- 索引效率下降：对于某些查询，特别是涉及跨分区的查询，可能会导致索引效率下降。由于查询需要在多个分区之间进行扫描，可能无法充分利用索引优势，从而影响查询性能。
- 存储空间需求增加：使用分区会导致一定程度的存储空间浪费。每个分区都需要占用一定的存储空间，包括分区元数据和一些额外的开销。因此，对于分区键的选择和分区粒度的设置需要权衡存储空间和性能之间的关系。
- 功能限制：在某些情况下，分区可能会限制某些MySQL的功能和特性的使用。例如，某些类型的索引可能无法在分区表上使用，或者某些DDL操作可能需要更复杂的处理。
在考虑使用分区时，需要综合考虑业务需求、查询模式、数据规模和硬件资源等因素，
并权衡分区带来的优势和缺点。对于特定的应用和数据场景，
分区可能是一个有效的解决方案，但并不适用于所有情况。
同时分区表也存在一些限制，如下：
限制：
- 在mysql5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。
- 分区表无法使用外键约束。
- NULL值会使分区过滤无效。
- 所有分区必须使用相同的存储引擎。
分区适用场景
分区表在以下情况下可以发挥其优势，适用于以下几种使用场景：
- 大型表处理：当面对非常大的表时，分区表可以提高查询性能。通过将表分割为更小的分区，查询操作只需要处理特定的分区，从而减少扫描的数据量，提高查询效率。这在处理日志数据、历史数据或其他需要大量存储和高性能查询的场景中非常有用。
- 时间范围查询：对于按时间排序的数据，分区表可以按照时间范围进行分区，每个分区包含特定时间段内的数据。这使得按时间范围进行查询变得更高效，例如在某个时间段内检索数据、生成报表或执行时间段的聚合操作。
- 数据归档和数据保留：分区表可用于数据归档和数据保留的需求。旧数据可以归档到单独的分区中，并将其存储在低成本的存储介质上。同时，可以保留较新数据在高性能的存储介质上，以便快速查询和操作。
- 并行查询和负载均衡：通过哈希分区或键分区，可以将数据均匀地分布在多个分区中，从而实现并行查询和负载均衡。查询可以同时在多个分区上进行，并在最终合并结果，提高查询性能和系统吞吐量。
- 数据删除和维护：使用分区表，可以更轻松地删除或清理不再需要的数据。通过删除整个分区，可以更快速地删除大量数据，而不会影响整个表的操作。此外，可以针对特定分区执行维护任务，如重新构建索引、备份和优化，以减少对整个表的影响。
分区表并非适用于所有情况。在选择使用分区表时，需要综合考虑数据量、查询模式、存储资源和硬件能力等因素，并评估分区对性能和管理的影响。
分区策略
- RANGE分区
RANGE分区
RANGE分区是MySQL中的一种分区策略，根据某一列的范围值将数据分布到不同的分区。
每个分区包含特定的范围。下面是RANGE分区的定义方式、特点以及代码示例。
定义方式：
- 指定分区键：选择作为分区依据的列作为分区键，通常是日期、数值等具有范围特性的列。
- 分区函数：通过
PARTITION BY RANGE指定使用RANGE分区策略。 - 定义分区范围：使用
VALUES LESS THAN子句定义每个分区的范围。
RANGE分区的特点：
- 范围划分：根据指定列的范围进行分区，适用于需要按范围进行查询和管理的情况。
- 灵活的范围定义：可以定义任意数量的分区，并且每个分区可以具有不同的范围。
- 高效查询：根据查询条件的范围，MySQL能够快速定位到特定的分区，提高查询效率。
- 动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。
以下是一个使用RANGE分区的代码示例：
create table sale(
sale_id int,
sale_date date,
amount decimal(10,2)
) partition by range(year(sale_date))(
partition p0 values less than (2020),
partition p1 values less than (2021),
partition p2 values less than (2022),
partition p3 values less than (2023),
partition p4 values less than (2024),
partition p5 values less than (2025)
);
insert into sale values (1,'2020-11-11',9999.00);
insert into sale values (2,'2020-12-12',6666.00);
insert into sale values (3,'2021-11-11',12000.00);
insert into sale values (4,'2021-12-12',13000.00);
insert into sale values (5,'2022-11-11',13000.00);
insert into sale values (6,'2022-12-12',12500.00);
insert into sale values (7,'2023-11-11',12500.00);
insert into sale values (8,'2023-12-12',13000.00);
select * from sale partition(p0);
select * from sale partition(p1);
LIST分区
LIST分区是根据某一列的离散值将数据分布到不同的分区。每个分区包含特定的列值列表。下面是LIST分区的定义方式、特点以及代码示例。
定义方式：
- 指定分区键：选择作为分区依据的列作为分区键，通常是具有离散值的列，如地区、类别等。
- 分区函数：通过
PARTITION BY LIST指定使用LIST分区策略。 - 定义分区列表：使用
VALUES IN子句定义每个分区包含的列值列表。
LIST分区的特点：
- 列值离散：根据指定列的具体取值进行分区，适用于具有离散值的列。
- 灵活的分区定义：可以定义任意数量的分区，并且每个分区可以具有不同的列值列表。
- 高效查询：根据查询条件的列值直接定位到特定分区，提高查询效率。
- 动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。
以下是一个使用LIST分区的代码示例：
create table people(
id_card varchar(18),
name varchar(50),
province int
) partition by list(province)(
partition huabei values in (11,12,13,14,15),
partition dongbei values in (21,22,23),
partition huazhong values in (31,32,33,34,35,36,37),
partition huadong values in (41,42,43,44,45,46),
partition xinan values in (50,51,52,53,55),
partition xibei values in (61,62,63,64,65),
partition gang_ao_tai values in (71,81,91)
);
insert into people values ('410182198903224674','邱赣',41);
insert into people values ('411429199211019071','何天',41);
insert into people values ('310182198903224674','方加牡',31);
insert into people values ('210182198903224674','贺巧',21);
insert into people values ('110182198903224674','闾丘饱乾',11);
insert into people values ('510182198903224674','丁经',51);
insert into people values ('610182198903224674','韦散',61);
insert into people values ('710182198903224674','东方让',71);
insert into people values ('810182198903224674','赖队瞻',81);
insert into people values ('910182198903224674','郭叹',91);
insert into people values ('120182198903224674','慕容芋岛',12);
insert into people values ('220182198903224674','孙劣',22);
insert into people values ('320182198903224674','王桃',32);
insert into people values ('420182198903224674','郝郑惭',42);
insert into people values ('520182198903224674','余烂',52);
insert into people values ('620182198903224674','宇文酚',62);
select * from people partition(huabei);
select * from people partition(dongbei);
select * from people partition(huazhong);
select * from people partition(huadong);
select * from people partition(xibei);
select * from people partition(gang_ao_tai);
HASH分区
HASH分区是使用哈希算法将数据均匀地分布到多个分区中。下面是HASH分区的定义方式、特点以及代码示例。
定义方式：
- 指定分区键：选择作为分区依据的列作为分区键。
- 分区函数：通过
PARTITION BY HASH指定使用HASH分区策略。 - 定义分区数量：使用
PARTITIONS关键字指定分区的数量。
HASH分区的特点：
- 数据均匀分布：HASH分区使用哈希算法将数据均匀地分布到不同的分区中，确保数据在各个分区之间平衡。
- 并行查询性能：通过将数据分散到多个分区，HASH分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。
- 简化管理：HASH分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。
以下是一个使用HASH分区的代码示例：
create table user (
id int auto_increment,
name varchar(50),
primary key (id)
)
partition by hash (id)
partitions 4;
insert into user (name) values ('邱赣');
insert into user (name) values ('何天');
insert into user (name) values ('方加牡');
insert into user (name) values ('贺巧');
insert into user (name) values ('闾丘饱乾');
insert into user (name) values ('丁经');
insert into user (name) values ('韦散');
insert into user (name) values ('东方让');
insert into user (name) values ('赖队瞻');
insert into user (name) values ('郭叹');
insert into user (name) values ('慕容芋岛');
insert into user (name) values ('孙劣');
insert into user (name) values ('王桃');
insert into user (name) values ('郝郑惭');
insert into user (name) values ('余烂');
insert into user (name) values ('宇文酚');
select * from user;
select * from user partition(p0);
select * from user partition(p1);
select * from user partition(p2);
select * from user partition(p3);
KEY分区
KEY分区是根据某一列的哈希值将数据分布到不同的分区。不同于HASH分区，KEY分区使用的是列值的哈希值而不是哈希函数。下面是KEY分区的定义方式、特点以及代码示例。
定义方式：
- 指定分区键：选择作为分区依据的列作为分区键。
- 分区函数：通过
PARTITION BY KEY指定使用KEY分区策略。 - 定义分区数量：使用
PARTITIONS关键字指定分区的数量。
KEY分区的特点：
- 哈希分布：KEY分区使用列值的哈希值将数据分布到不同的分区中，与哈希函数不同，它使用的是列值的哈希值。
- 高度自定义：KEY分区允许根据业务需求自定义分区逻辑，可以灵活地选择分区键和分区数量。
- 并行查询性能：通过将数据分散到多个分区，KEY分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。
- 简化管理：KEY分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。
以下是一个使用KEY分区的代码示例：
create table person (
id int auto_increment,
name varchar(50),
primary key (id)
)
partition by key(id)
partitions 4;
insert into person (name) values ('邱赣');
insert into person (name) values ('何天');
insert into person (name) values ('方加牡');
insert into person (name) values ('贺巧');
insert into person (name) values ('闾丘饱乾');
insert into person (name) values ('丁经');
insert into person (name) values ('韦散');
insert into person (name) values ('东方让');
insert into person (name) values ('赖队瞻');
insert into person (name) values ('郭叹');
insert into person (name) values ('慕容芋岛');
insert into person (name) values ('孙劣');
insert into person (name) values ('王桃');
insert into person (name) values ('郝郑惭');
insert into person (name) values ('余烂');
insert into person (name) values ('宇文酚');
select * from person;
select * from person partition(p0);
select * from person partition(p1);
select * from person partition(p2);
select * from person partition(p3);
COLUMNS 分区
MySQL在5.5版本引入了COLUMNS分区类型，其中包括RANGE COLUMNS分区和LIST COLUMNS分区。以下是对这两种COLUMNS分区的详细说明：
-
RANGE COLUMNS分区： RANGE COLUMNS分区是根据列的范围值将数据分布到不同的分区的分区策略。它类似于RANGE分区，但是根据多个列的范围值进行分区，而不是只根据一个列。这使得范围的定义更加灵活，可以基于多个列的组合来进行分区。
下面是一个RANGE COLUMNS分区的代码示例：create table product( product_id int, product_name varchar(50), product_location int, category_id int ) partition by range columns(category_id,product_location)( partition p11 values less than (1,1), partition p12 values less than (1,2), partition p21 values less than (2,1), partition p22 values less than (2,2) );每个分区根据这两列的范围值进行划分。
-
LIST COLUMNS分区： LIST COLUMNS分区是根据列的离散值将数据分布到不同的分区的分区策略。它类似于LIST分区，但是根据多个列的离散值进行分区，而不是只根据一个列。这使得离散值的定义更加灵活，可以基于多个列的组合来进行分区。
下面是一个LIST COLUMNS分区的代码示例：create table product( product_id int, product_name varchar(50), product_location int, category_id int ) partition by list columns(category_id,product_location)( partition p12 values in ((1,1),(1,2)), partition p22 values in ((2,1),(2,2)) );
常见分区命令
向分区表添加新的分区
alter table sale add partition (partition p6 values less than (2026));
删除指定的分区
alter table sale drop partition p6;
重新组织分区
alter table sale REORGANIZE PARTITION p0, p1, p2,p3, p4, p5 INTO (
partition p0 values less than (2021),
partition p1 values less than (2023),
partition p2 values less than (2025)
);
合并相邻的分区
分析指定分区的统计信息
alter table sale analyze partition p0;
主从复制
服务器 openEuler
一主一从
| 角色 | 主机名 | IP地址 |
|---|---|---|
| 主库 | master | 192.168.1.201 |
| 从库 | slave | 192.168.1.202 |
分别安装MySQL
下载MySQL
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.29-1.el8.x86_64.rpm-bundle.tar
创建安装包目录
mkdir mysql
释放安装包到安装包目录
tar -xvf mysql-8.0.29-1.el8.x86_64.rpm-bundle.tar -C mysql
切换到MySQL安装包目录
cd mysql
安装MySQL
rpm -ivh *.rpm --force --nodeps
启动MySQL
systemctl start mysqld
设置MySQL开机自启动
systemctl enable mysqld
在服务器初始启动时，假设服务器的数据目录为空，会发生以下情况：
-
服务器已初始化。
-
SSL 证书和密钥文件在数据目录中生成。
-
validate_password已安装并启用。 -
创建了一个超级用户帐户
'root'@'localhost。超级用户的密码已设置并存储在错误日志文件中。要显示它，请使用以下命令：sudo grep 'temporary password' /var/log/mysqld.log通过使用生成的临时密码登录并为超级用户帐户设置自定义密码，尽快更改 root 密码：
mysql -uroot -p
修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@1122';
FLUSH PRIVILEGES;
设置远程访问地址
update mysql.user set host = '%',plugin='mysql_native_password' where user='root';
FLUSH PRIVILEGES;
退出MySQL
exit;
重启MySQL
systemctl restart mysqld
防火墙开放端口
firewall-cmd --zone=public --add-port=3306/tcp --add-port=33060/tcp --permanent
重启防火墙
firewall-cmd --reload
主库配置
在主从库中分别建一个你需要同步的测试库，比如我这里创建的库叫lihaozhe，然后在这两个库中分别建一个测试表，都是空表即可。
修改配置文件
vim /etc/my.cnf
追加以下内容：
实验配置
server-id=201
log-bin=mysql-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
生产环境配置
还可以配置一些其他参数：
log_bin_index（bin日志文件索引位置），
expire_logs_days 日志多少天过期，binlog_format 等。
server-id=201 #服务器 id，随意，但要唯一
log-bin=mysql-bin #二进制文件存放路径
binlog-do-db=lihaozhe #待同步的数据库日志
binlog-ignore-db=mysql #不同步的数据库日志
创建账号用于主从复制
#创建用户 我这里用户名为copyuser，注意这里的ip是从库服务器的ip
CREATE USER 'lihaozhe'@'%' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@6633';
#给主从复制账号授权
grant replication slave on *.* to 'lihaozhe'@'%';
重启MySQL
systemctl restart mysqld
查看主库状态
登录数据库后查看
show master status;
显示如下：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | lihaozhe | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
注意：File 和 Position的值从库同步的时候需要
从库配置
在主从库中分别建一个你需要同步的测试库，比如我这里创建的库叫lihaozhe，然后在这两个库中分别建一个测试表，都是空表即可。
修改配置文件
vim /etc/my.cnf
追加以下内容：
server-id=202 #服务器 id，随意，但要唯一
log-bin=mysql-bin #二进制文件存放路径
replicate-do-db=lihaozhe #待同步的数据库
replicate-ignore-db=mysql,information_schema,performance_schema #不同步的数据库
以上面主库配置相比，就是除了server-id不一样，这里还需要配置replicate-do-db和replicate-ignore-db，注意不是日志。
重启MySQL
systemctl restart mysqld
实现主从同步
登录数据库后
-- 关闭从库
stop slave;
-- 设置同步，注意这里是主库ip，日志名称和位置是我们之前上图中看到的名称和位置
change master to master_host='192.168.1.201',master_user='lihaozhe',master_password='Lihaozhe!!@@6633',master_log_file='mysql-bin.000001',master_log_pos=157;
-- 开启从库
start slave;
-- 检查服务器状态
show slave status \G;
显示如下：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.201
Master_User: lihaozhe
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 472
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: lihaozhe
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 472
Relay_Log_Space: 536
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 201
Master_UUID: ee21ecf9-01be-11ed-916c-000c29c9a6f5
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql>
测试
主表操作，从表观察
cate-do-db和replicate-ignore-db，注意不是日志。
重启MySQL
systemctl restart mysqld
实现主从同步
登录数据库后
-- 关闭从库
stop slave;
-- 设置同步，注意这里是主库ip，日志名称和位置是我们之前上图中看到的名称和位置
change master to master_host='192.168.1.201',master_user='lihaozhe',master_password='Lihaozhe!!@@6633',master_log_file='mysql-bin.000001',master_log_pos=157;
-- 开启从库
start slave;
-- 检查服务器状态
show slave status \G;
显示如下：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.201
Master_User: lihaozhe
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 472
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: lihaozhe
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 472
Relay_Log_Space: 536
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 201
Master_UUID: ee21ecf9-01be-11ed-916c-000c29c9a6f5
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql>
测试
主表操作，从表观察
文章来源:https://blog.csdn.net/qq_24330181/article/details/134944059
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！