MySQL应用篇
MySQL应用
1.SQL介绍
结构化查询（Structured Query Language）简介SQL，结构化查询语言是一种数据查询和程序设计语言，用于储存数据以及数据查询、更新和管理关系型数据库系统；
关系型数据库（RDBMS）是指建立在关系模型基础上，由多张相互连接的二维表组成的数据库。而所谓二维表，指的是由行和列组成的表，就类似于Excel表格数据，有表头、有列、有行，还可以通过一列关联另外一个表格中的某一列数据。我们耳熟能详的MySQL、SQLite、Oracle等都是基于关系模型的数据库管理系统，里面都是基于二维表存储数据的。简单说，基于二维表存储数据的数据库就成为关系型数据库。
2.MySQL中常用的命令
| 命令或快捷键 | 用法 | 作用 |
|---|---|---|
| help | mysql> help | 帮助。 |
| \G | mysql> select * from mysql.user\G | 宽表显示不下可以用它来纵向方式显示。 |
| Ctrl+D | Ctrl+D | 退出mysql会话。 |
| pager | mysql> pager less | 执行之后查询数据就可以像linux中less命令一样通过上下键滚动查看数据。也可以pager grep。（windows上安装的不支持） |
| nopager | mysql> nopager | 取消上面的pager，也可以退出会话后再进来即可关闭。 |
| tee | mysql> tee /tmp/a.log | 记录当前所有的操作记录到文件。 |
| notee | mysql> notee | 关闭上面的tee，也可以退出当前会话重新进。 |
| source | mysql> source /root/employees.sql | 导入SQL脚本。也可以再没登录前使用mysql -uroot -p 库名 </root/employees.sql命令导入。 |
| status | mysql> status | 查看状态信息。 |
prompt设置，设置之后客户端连接数据库后可以显示用户名、主机名和当前操作的数据库，需要把配置加到my.cnf的[mysql]标签下：
[mysql]
prompt=(\u@\h)[\d]>\_
不用重启数据库，退出会话再进入即可：
(root@localhost)[(none)]> use mysql;
Database changed
(root@localhost)[mysql]>
参数解析：
\D：完整的日期。
\d：当前数据库。
\h：服务器名称。
\u：当前用户。
3.MySQL服务器端命令
3.1帮助
help contents
(root@localhost)[(none)]> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management（用户、权限管理）
Administration（系统管理类语句）
Components（组件应用）
Compound Statements（过程函数复合语句）
Contents（帮助目录）
Data Definition（数据定义）
Data Manipulation（数据操作）
Data Types（数据类型）
Functions（内置函数）
Geographic Features（地理位置）
Help Metadata（帮助信息元数据）
Language Structure
Loadable Functions
Plugins（插件管理）
Prepared Statements
Replication Statements
Storage Engines
Table Maintenance（表维护）
Transactions（事务）
Utility（实用工具）
使用方法：
# 如查看用户、权限管理帮助
(root@localhost)[(none)]> help Account Management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
ALTER RESOURCE GROUP
ALTER USER
CREATE RESOURCE GROUP
CREATE ROLE
CREATE USER
DROP RESOURCE GROUP
DROP ROLE
DROP USER
GRANT
RENAME USER
REVOKE
SET DEFAULT ROLE
SET PASSWORD
SET RESOURCE GROUP
SET ROLE
# 假设要查看创建用户帮助
(root@localhost)[(none)]> help CREATE USER
常用SQL语句分类：
DDL（数据定义语言）
DQL（数据查询语言）
DML（数据操纵语言）
DCL（数据控制功能）
3.2 SQL_MODE
用途：对数据在插入或者查询的时候做严格限制。
| SQL_MODE | Comments |
|---|---|
| ONLY_FULL_GROUP_BY | 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中 |
| STRICT_TRANS_TABLES | 严格模式，进行数据的严格校验，错误数据不能插入，报error错误。如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。 |
| NO_ZERO_IN_DATE | 不接受日期和月份为零的日期。 |
| NO_ZERO_DATE | 不将'0000-00-00'作为合法日期。 |
| ERROR_FOR_DIVISION_BY_ZERO | 在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL。 |
| NO_ENGINE_SUBSTITUTION | 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。 |
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
查看当前数据库的sql_mode:
(root@localhost)[(none)]> show variables like 'sql_mode';
4. 库、表属性介绍
4.1 MySQL逻辑结构
库：库名，属性（字符集，校对规则，表空间加密）
表：表名，表属性（存储引擎，字符集，校对规则，表空间加密），列（列名，列属性），数据行
4.2 字符集
查看所有字符集：show charset;
utf8 ：字符最大长度3个字节。
utf8mb4 : 字符最大长度4个字节。较utf8支持更多特殊符号,比如表情。
记住以上两种就可以。现在用utf8mb4好一点。
4.3 校对(排序)规则
影响排序规则。
查看所有校对规则：show collation;
常用的校对规则：
utf8mb4_general_ci：使用的比较多，因为它支持大多数的unicode字符，并且对大小写不敏感，这使得它在处理大部分文本数据时表现良好。
utf8mb4_0900_ai_ci：如果你需要处理一些包含特殊字符、重音符号或者表情符号的数据，这个比较合适，因为它支持更多的字符并且对重音符号不敏感。
注意：mysql5.7不设置的话他会默认使用“latin1”的字符集，校对规则默认会使用“latin1_swedish_ci”，在mysql8.0后默认字符集使用的是“utf8mb4”，校对规则默认使用“utf8mb4_0900_ai_ci”，可以在配置文件中设置指定：
[mysql]
default-character-set=utf8mb4
[mysqld]
default-character-set=utf8mb4
collation-server=utf8mb4_general_ci
4.4 存储引擎
查看所有的存储引擎：show engines;
生产中95%以上用的都是InnoDB,默认就是它。
5.列的数据类型
6.列的约束以及属性
| 约束/属性 | 作用 |
|---|---|
| primary key | 主键约束，同时保证唯一性和非空，每个表只能有一个pk（primary key），建议与业务无关的列（数字列）。 |
| foreign key | 外键约束，用于限制两个表的关系，比如有两个关于学生的信息表，其中一个主要记录学生个人信息，另一个记录学生成绩，但是两个表里都有学生的学号，此时要删除某个学生所有信息，就可以通过这个外键指定同时删除某个学生的记录。生产不建议使用，可以通过多表连接方式实现。 |
| not null | 非空约束，保证字段的值不能为空，生产建议所有列都使用。 |
| default | 默认约束，保证字段总会有值，即使没有插入值，也会有默认值。 |
| unique | 唯一，保证唯一信但是可以为空，比如手机号。 |
| auto_increment | 自增长列 |
| unsigned | 无符号 |
| comment | 注释 |
7.客户端工具介绍
登录MySQL的方式比较多，如果你很熟练SQL语句，可以直接使用MySQL-Client直接连接，但是为了提高效率，可以使用客户端远程工具连接。比如Navicat，SQLyog，但是这些工具都需要收费，这里使用MySQL官方提供的MySQL Workbench。
https://dev.mysql.com/downloads/workbench/
8.DDL（数据定义语言）
8.1 数据库
8.1.1 创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_NAME [DEFAULT] CHARACTER SET charset_name COLLATE collate_name;
create database if not exists `guojie_db` character set 'utf8mb4' collate 'utf8mb4_0900_ai_ci';
在mysql8以后，字符集已经不支持utf8,创建的时候如果指utf8他会警告：
1 row(s) affected, 1 warning(s): 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
# 意思是：“UTF8”当前是字符集UTF8MB3的别名，但在将来的版本中将成为UTF8MB4的别名。请考虑使用UTF8MB4以明确无误。
在MySQL-Client中会提示有警告，但是它不会打印，如果查看需要使用show warnings;来查看。
8.1.2 查询数据（不属于DDL）
查询所有数据库：show databases;
查询建库语句：show create database db_name;
8.1.3 修改
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET charset_name;
ALTER {DATABASE | SCHEMA} [db_name] COLLATE collate_name;
生产中不建议对数据属性修改，比如字符集、校对规则、引擎等。修改之后它也影响不到之前已经存入的数据，只能重新创建新的数据库，然后转储。并且数据库名称是无法修改的，如下介绍怎么介绍修改字符集和校对规则。
# 修改aaa库的字符集为utf8mb4
alter database `aaa` character set 'utf8mb4';
# 修改aaa库校对规则为utf8mb4_0900_as_ci
alter database `aaa` collate 'utf8mb4_0900_as_ci';
8.1.4 删除
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
# 删除数据库aaa
drop database if exists `aaa`;
注意：生产中一般不会让你删除数据库，需要移除账号的drop权限，除非有特殊需求。
# 回收guojie@'172.16.1.1'用户的drop权限
(root@localhost)[(none)]> revoke drop on *.* from guojie@'172.16.1.1';
(root@localhost)[(none)]> flush privileges;
# 客户端再退出后下次就会无法删除
8.1.5 规范
1、库名不要以数字开头，不要超过18个字符、与业务有关，不能是关键字。
2、必须设置字符集。
3、禁止删除。
8.3 数据表
8.3.1 创建表
创建表定义，可以通过客户端工具点点点实现，效率比较高，也可以使用语句创建。需要注意第6节对列的约束和限制。
也可以使用SQL语句建表：
create table if not exists `students` (
stu_id int unsigned not null primary key auto_increment comment '学号',
stu_name varchar(64) not null comment '姓名',
stu_age tinyint unsigned not null default 18 comment '年龄',
stu_gender char(1) not null default 'M' comment '性别:M|F',
stu_addre enum('bj', 'sh', 'tj', 'sz', 'tw') not null comment '省份',
stu_el char(11) not null unique key comment '手机号',
stu_qq char(11) not null unique key comment 'qq号',
intime datetime not null default now() comment '入学时间'
) engine=InnoDB character set=utf8mb4 comment '学生表';
8.3.2 查看表定义
查看所有表：show tables;
查看表结构：desc 表名;orshow create table 表名;orSHOW COLUMNS FROM table_name;
(root@localhost)[guojie_db]> desc `students`;
+------------+--------------------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------------+------+-----+-------------------+-------------------+
| stu_id | int unsigned | NO | PRI | NULL | auto_increment |
| stu_name | varchar(64) | NO | | NULL | |
| stu_age | tinyint unsigned | NO | | 18 | |
| stu_gender | char(1) | NO | | M | |
| stu_addre | enum('bj','sh','tj','sz','tw') | NO | | NULL | |
| stu_el | char(11) | NO | UNI | NULL | |
| stu_qq | char(11) | NO | UNI | NULL | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------------------------+------+-----+-------------------+-------------------+
8 rows in set (0.00 sec)
(root@localhost)[guojie_db]> show create table `students`\G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`stu_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
`stu_name` varchar(64) NOT NULL COMMENT '姓名',
`stu_age` tinyint unsigned NOT NULL DEFAULT '18' COMMENT '年龄',
`stu_gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别:M|F',
`stu_addre` enum('bj','sh','tj','sz','tw') NOT NULL COMMENT '省份',
`stu_el` char(11) NOT NULL COMMENT '手机号',
`stu_qq` char(11) NOT NULL COMMENT 'qq号',
`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`stu_id`),
UNIQUE KEY `stu_el` (`stu_el`),
UNIQUE KEY `stu_qq` (`stu_qq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'
1 row in set (0.00 sec)
ERROR:
No query specified
8.3.3 修改表定义
8.3.3.1修改表名
rename table 旧表名 to 新表名
rename table `students` to `stu1`;
8.3.3.2 修改表引擎
生产中基本不需要修改引擎，用默认innodb就可以，一般只有其它类型改成innodb。
alter table 表名 engine=myisam;
alter table stu1 engine=innodb;
8.3.3.4 表中增加列
alter table 表名 add 列名 属性1 属性2;
alter table `stu1` add is_deleted tinyint not null default 0 comment '状态列:0代表正常，0代表删了';
以上操作5.6，5.7版本中可能数据量多的时候会锁表，8.0以后不会。以上会在表末尾添加列，如果需要指定列的位置需要使用以下方法。
alter table 表明 add 新增列名 属性1 属性2 after 在哪个列后;
# 在表stu1中stu_name列后添加一列aaa。
alter table stu1 add aaa varchar(20) not null after stu_name;
8.3.3.5 删除列
还是一样，生产中不建议删除。
alter table 表名 drop 列名;
alter table `stu1` drop aaa;
8.3.3.6 修改列属性
注意：修改列属性等于重新定义改列的属性，要修改的同时把其它属性也补上，同时还要注意修改长度时如果改短需要注意已存在的数据是否满足条件。
alter table 表名 modify 列名 属性1 属性2;
# 先看以下建表语句对应要修改列的属性
| stu1 | CREATE TABLE `stu1` (
`stu_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
`stu_name` varchar(64) NOT NULL COMMENT '姓名',
`stu_age` tinyint unsigned NOT NULL DEFAULT '18' COMMENT '年龄',
`stu_gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别:M|F',
`stu_addre` enum('bj','sh','tj','sz','tw') NOT NULL COMMENT '省份',
`stu_el` char(11) NOT NULL COMMENT '手机号',
`stu_qq` char(11) NOT NULL COMMENT 'qq号',
`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
`is_deleted` tinyint NOT NULL DEFAULT '0' COMMENT '状态列:0代表正常，0代表删了',
PRIMARY KEY (`stu_id`),
UNIQUE KEY `stu_el` (`stu_el`),
UNIQUE KEY `stu_qq` (`stu_qq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表' |
# 比如这里修改列stu_name的长度为100
alter table `stu1` modify stu_name varchar(100) NOT NULL COMMENT '姓名';
8.3.3.7 索引
8.3.3.7.1 添加索引
alter table 表名 add index 索引名(列名);
# 比如把表stu1的stu_name列设置为索引，索引名为index_stu_name
alter table stu1 add index index_stu_name(stu_name);
添加之后可以使用desc查看(也可以使用show indexes from table_name;查看)，在Key那一列有MUL表明设置成功。
(root@localhost)[guojie_db]> desc stu1;
+------------+--------------------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------------+------+-----+-------------------+-------------------+
| stu_id | int unsigned | NO | PRI | NULL | auto_increment |
| stu_name | varchar(100) | NO | MUL | NULL | |
| stu_age | tinyint unsigned | NO | | 18 | |
| stu_gender | char(1) | NO | | M | |
| stu_addre | enum('bj','sh','tj','sz','tw') | NO | | NULL | |
| stu_el | char(11) | NO | UNI | NULL | |
| stu_qq | char(11) | NO | UNI | NULL | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| is_deleted | tinyint | NO | | 0 | |
+------------+--------------------------------+------+-----+-------------------+-------------------+
9 rows in set (0.00 sec)
8.3.3.7.2 删除索引
alter table 表名 drop index 索引名;
# 比如把表stu1名为index_stu_name的索引删除
alter table stu1 drop index index_stu_name;
再查看：
(root@localhost)[guojie_db]> desc stu1;
+------------+--------------------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------------+------+-----+-------------------+-------------------+
| stu_id | int unsigned | NO | PRI | NULL | auto_increment |
| stu_name | varchar(100) | NO | | NULL | |
| stu_age | tinyint unsigned | NO | | 18 | |
| stu_gender | char(1) | NO | | M | |
| stu_addre | enum('bj','sh','tj','sz','tw') | NO | | NULL | |
| stu_el | char(11) | NO | UNI | NULL | |
| stu_qq | char(11) | NO | UNI | NULL | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| is_deleted | tinyint | NO | | 0 | |
+------------+--------------------------------+------+-----+-------------------+-------------------+
9 rows in set (0.00 sec)
8.3.3.7.3 判断某个列有没有索引
使用：desc 表名; key那一列有值就是有索引
(root@localhost)[guojie_db]> desc stu1;
+------------+--------------------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------------+------+-----+-------------------+-------------------+
| stu_id | int unsigned | NO | PRI | NULL | auto_increment |
| stu_name | varchar(100) | NO | MUL | NULL | |
| stu_age | tinyint unsigned | NO | | 18 | |
| stu_gender | char(1) | NO | | M | |
| stu_addre | enum('bj','sh','tj','sz','tw') | NO | | NULL | |
| stu_el | char(11) | NO | UNI | NULL | |
| stu_qq | char(11) | NO | UNI | NULL | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| is_deleted | tinyint | NO | | 0 | |
+------------+--------------------------------+------+-----+-------------------+-------------------+
9 rows in set (0.00 sec)
8.3.4 删除表
8.3.4.1 删除表
drop table 表名;
# 删除表stu1
drop table `stu1`;
8.3.4.2 清空表
truncate table 表名;
# 把表stu1的数据清空
truncate table `stu1`;
9 Online DDL（ALGORITHEM）
9.1 介绍
在5.6版本之后，对于部分alter table ，加入新的执行算法，可以进行DDL时，“并行”有业务(DML操作)。
可以通过ALter table 时添加 ALGORITHM参数控制使用算法。
目前可以支持的算法有三种:
COPY
INPLACE
INSTANT
DDL操作，在执行时，不管何种算法，都会经历三个阶段:准备阶段、执行阶段、提交阶段。不同之处是在三个阶段中分别作了不同的处理。
9.2 Copy 算法原理
指DDL时，会生成《临时》新表，将原表数据逐行拷贝到新表中，在此期间会阳塞DML
准备阶段:
1、对表加元数据共享锁，读取frm元数据《此时DDL不能不行)
2、共享锁升级为排他锁，《此时DDL、DML都不能并行)
3、在Server层通过Create like语句，创建时表，Engine层也生成对应ibd、frm文件
执行提交阶段:
1、修改临时表元数据
2、拷贝原表数据到临时表
3、重命令临时表及文件
4、删除原表及文件
5、提交事务，释放锁
9.3 INPLACE 算法原理
无需拷贝全表数据到新表，但可能还是需要IN-PLACE方式《原地，无需生成新的临时表)重建整表。这种情况下，在DDL的初始准备和最后结束两个阶段时通常需要加排他MDL锁(metadata 1ock，元数据锁》，除此外，DDL期间不会阻塞DML
准备阶段:
1、对表加元数据共享升级锁，并升级为排他锁，《此时OML不能并行》
2、判断语句rebuild table,no-rebuild.norebuild在原表所在的路径下创建.frm和.ibd临时中转文件;
++++
no-rebui1d除创建二级索引外只创建,frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中
++++
3、申请row log空间，用于存放DDL执行阶段产生的DML操作。(no-rebuild不需要)
执行阶段:
1、释放排他锁，保留元数据共享升级锁;《此时DML可以并行)
2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中，
3、将所有对原表的OML操作记录在日志文件row 1og中。
提交阶段:
1、升级元数据共享升级锁，产生排他锁锁表，(此时DML不能并行)
2、重做row 1og中的内容，(no-rebuild不需要》
3、重命名原表文件，将临时文件改名为原表文件名，删除原表文件;
4、提交事务，变更完成。
+++
在DDL期间产生的数据，会按照正常操作一样，写入原表，记redolog、undolog、binlog，并同步到从库去执行，只是额外会记录在row 1og中，并且写入row 1og的操作本身也会记录redolog，而在提交阶段才进行row 1og重做，此阶段会锁表，此时主库(新表空间+row 1og)和从库(表空间)数据是一致的，在库DDL操作执行完成并提交，这个DDL才会写入binlog传到从库执行，在从库执行该DDL时，这个DDL对于从库本地来进仍然是online的，也就是在从库本地直接写入数据是不会阳塞的，也会像主库一样产生row g。但是对于主库同步过来DML，此时会被阳塞，是off1ine的，DDL是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作(主要是在复制线程并行时会排他，同一时间只有他自己在执行》。所以大表的DDL操作，会造成同步延迟。
+++
9.4 INSTANT :
只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他MDL锁，原表数据也不受影响。整个DDL
过程几乎是瞬间完成的，也不会阻塞DML。
这个新特性是8.0.12引入的，再次感谢腾讯互娱DBA团队的质献。执行DDL操作时，ALGORITHM选项可以不指定，这时候MysQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效
果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。
当采用COPY模式时，这时表里任何的修改数据操作，DDL都会被阻塞。COPY模式下会生成临时新表，操作完成后原表会被删除，新表被重命名为原表名。当DDL开始后，原表上仅能只读，其他的DML操作也都会被阳塞。COPY过程中，唯一会阳算只读的时机是在清理旧表结构和表定义缓存时。
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
当前，InnoDB支持用于这些操作的INSTANT算法:
添加列《追加)
更改索引选项
重命名表(以ALTER方式)
设置/删除默认值
修改栏
添加/删除虚拟列
您可以使用ALGORITHM = INSTANT在单个语句中指定多个操作。
9.5 建议
个人建议:
一般DDL操作最好都采用pt-osc或gh-ost这样的工具来实施，并且实施之前务必要先检查当前目标表上是否有事务或大查询未结束，避免严重的MDL锁等待。
除了8.0以上版本，除了追加式新增列、表改名、新增虚拟列这三种支持INSTANT的操作可以直接跑DDL，其余的都充统采用pt-osc/gh-osc工具，相对更不容易出问题。
执行ALTER TABLEDDL时，不要指定ALGORITHM=?，LOCK=?选项，因为MySQL会自行判断该采用那种方式。本来可以INPLACE的，可能不小心给指定成COPY就悲剧了。
10 数据操作语言（DML）
可以使用help查看，主要包括INSERT、UPDATE、DELETE、SELECT,其中SELECT是使用最多的。
(root@localhost)[(none)]> help Data Manipulation
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
CALL
DELETE
DO
DUAL
HANDLER
IMPORT TABLE
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
PARENTHESIZED QUERY EXPRESSIONS
REPLACE
SELECT
TABLE
UNION
UPDATE
VALUES STATEMENT
10.1 INSERT
单行插入：insert into 表名(列1,列2,列3,...) values (值1,值2,值3,...);
注意：
1、插入的值要和列名一一对应，除了数字类型，其他的要用单引号或双引号引起来。
2、插入时间时如果需要使用当前时间，可以直接使用now()函数，直接写成now()即可。
3、如果每列都插入，那列名可以不写。
insert into stu1(stu_id,stu_name,stu_age,stu_gender,stu_addre,stu_el,stu_qq,intime,is_deleted) values
(001, '陆国杰', 23, 'M', 'bj', '15798852926', '2310478001', '2023-10-15 15:04:30', 0);
多行插入：
(insert into 表名(列1,列2,列3,...) values (值1,值2,值3,...),(值1,值2,值3,...),...;
insert into stu1 values
(002, '张三', 18, 'M', 'bj', '15798852927', '2310478007', '2023-10-15 15:04:30', 0),
(003, '李四', 38, 'M', 'bj', '15798852928', '2310478008', now(), 0),
(004, '王五', 28, 'M', 'bj', '15798852929', '2310478009', now(), 0);
自增或者有默认值的列可以跳过让它自动填充：
insert into stu1(stu_name,stu_addre,stu_el,stu_qq) values ('马六','sh','17787099583','2310479002');
10.2 UPDATE
update 表名 set 列名=新值 where 列名='值';
# 原数据
(root@localhost)[guojie_db]> select stu_id,stu_name from stu1;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | 陆国杰 |
| 2 | 张三 |
| 3 | 李四 |
| 4 | 王五 |
| 5 | 马六 |
| 10 | 张飞 |
| 43 | 刘备 |
+--------+----------+
7 rows in set (0.00 sec)
# 把张飞列的stu_id改成6
(root@localhost)[guojie_db]> update stu1 set stu_id=6 where stu_name='张飞';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 再次查看
(root@localhost)[guojie_db]> select stu_id,stu_name from stu1;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | 陆国杰 |
| 2 | 张三 |
| 3 | 李四 |
| 4 | 王五 |
| 5 | 马六 |
| 6 | 张飞 |
| 43 | 刘备 |
+--------+----------+
7 rows in set (0.00 sec)
注意：数据库默认不添加过滤也可以修改，比较危险，比如执行update stu1 set stu_name='陆国杰';它也是可以执行的，他会把所有记录的stu_name全部改为'陆国杰'，比较危险,建议把安全更新打开，参数是“sql_safe_updates”，它会要求你添加过滤条件，并且要求过滤条件是有索引的列
# 可以看到是关闭状态
(root@localhost)[guojie_db]> show variables like '%update%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | OFF |
| log_replica_updates | ON |
| log_slave_updates | ON |
| low_priority_updates | OFF |
| sql_safe_updates | OFF |
+-----------------------------------------+-------+
5 rows in set, 9 warnings (0.00 sec)
# 打开安全更新
(root@localhost)[guojie_db]> set global sql_safe_updates=ON;
Query OK, 0 rows affected (0.00 sec)
# 退出登录再重新登录执行修改语句测试它就会报错，尽管你设置过滤条件也会失败
(root@localhost)[guojie_db]> update stu1 set stu_name='关羽';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
(root@localhost)[guojie_db]> update stu1 set stu_name='关羽' where stu_name='陆国杰';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
# 当你把那个列设置上索引之后就可以执行
(root@localhost)[guojie_db]> alter table stu1 add index index_stu_name(stu_name);
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost)[guojie_db]> update stu1 set stu_name='关羽' where stu_name='陆国杰';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 可以看见之间stu_id为'陆国杰'的已经被改成'关羽'
(root@localhost)[guojie_db]> select stu_id,stu_name from stu1;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | 关羽 |
| 43 | 刘备 |
| 2 | 张三 |
| 6 | 张飞 |
| 3 | 李四 |
| 4 | 王五 |
| 5 | 马六 |
+--------+----------+
7 rows in set (0.00 sec)
提示：这个操作好像有后遗症，观察可以发现stu_id原本设置了自增，但是在stu_name上添加索引后它并没有按照自增排序，但是它也只是影响update语句，你新insert数据它也还是会自动正常排序，这个可能有点小后遗症。
10.3 DELETE
生产中不建议删除数据，表中已经有了一个状态列，修改状态列即可（使用update代替delete,查询的时候过滤状态列为0的即可）。这里也把删除方法说一下。
delete from 表名 where 列名=值;
(root@localhost)[guojie_db]> delete from stu1 where stu_id=44;
Query OK, 1 row affected (0.46 sec)
拓展：如果不添加where过滤，它也会清空表里的所有数据，加上之前我们已经学过三种删除表的方法了，下面做一下比较：
| 删除方式 | 原理 | 执行速度 |
|---|---|---|
| delete from 表名; | 不是真的删除，逐行打标记。 | 最慢 |
| drop table 表名; | 删除表定义，再清空表空间（操作系统rm） | 中间 |
| truncate table 表名; | 保留表定义，清空表空间 | 最快 |
10.4 SELECT
10.4.1 独立使用（查询系统参数）
| 语句 | 查询内容 |
|---|---|
| select @@port; | 查看端口 |
| select @@basedir; | 查看安装路径 |
| select @@datadir; | 查看数据存放路径 |
| select @@socket; | 查看socket位置 |
| select 1+1; | 计算器 |
以上查询只是基本的，还有较多配置有时候太长记不住，建议使用show variables来看,可以模糊搜索，如：
(root@localhost)[guojie_db]> show variables like '%port%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| admin_port | 33062 |
| large_files_support | ON |
| mysqlx_port | 33060 |
| mysqlx_port_open_timeout | 0 |
| port | 3306 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| require_secure_transport | OFF |
+--------------------------+-------+
10 rows in set, 9 warnings (0.00 sec)
(root@localhost)[guojie_db]> show variables like '%basedir%';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| basedir | D:\Services\mysql\ |
+---------------+--------------------+
1 row in set, 9 warnings (0.00 sec)
(root@localhost)[guojie_db]> show variables like '%datadir%';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| datadir | D:\Services\mysql\data\ |
+---------------+-------------------------+
1 row in set, 9 warnings (0.00 sec)
(root@localhost)[guojie_db]> show variables like '%log_err%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | D:\Services\mysql\data\ |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
5 rows in set, 9 warnings (0.00 sec)
10.4.2 调用内置函数
# 查看版本号
(root@localhost)[guojie_db]> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
#查看当前时间
(root@localhost)[guojie_db]> select now();
+---------------------+
| now() |
+---------------------+
| 2023-10-15 21:08:01 |
+---------------------+
1 row in set (0.00 sec)
10.4.3 配合其它字句查询(单表)
多子句的执行顺序:
select
from 从哪来
where 过滤条件
group by 分组条件
select_list 列条件
having 后过滤条件
order by 排序条件
limit 分页
10.4.3.1 from
查询整个表：select * from 表名;
(root@localhost)[guojie_db]> select * from stu1;
+--------+----------+---------+------------+-----------+-------------+-------------+---------------------+------------+
| stu_id | stu_name | stu_age | stu_gender | stu_addre | stu_el | stu_qq | intime | is_deleted |
+--------+----------+---------+------------+-----------+-------------+-------------+---------------------+------------+
| 1 | 关羽 | 23 | M | bj | 15798852926 | 2310478001 | 2023-10-15 15:04:30 | 0 |
| 2 | 张三 | 18 | M | bj | 15798852927 | 2310478007 | 2023-10-15 15:04:30 | 0 |
| 3 | 李四 | 38 | M | bj | 15798852928 | 2310478008 | 2023-10-15 15:18:30 | 0 |
| 4 | 王五 | 28 | M | bj | 15798852929 | 2310478009 | 2023-10-15 15:18:30 | 0 |
| 5 | 马六 | 18 | M | sh | 17787099583 | 2310479002 | 2023-10-15 15:29:52 | 0 |
| 6 | 张飞 | 18 | M | tj | 12345678911 | 43462342123 | 2023-10-15 16:35:06 | 0 |
| 43 | 刘备 | 18 | M | tj | 12345678901 | 43442342123 | 2023-10-15 16:34:23 | 0 |
+--------+----------+---------+------------+-----------+-------------+-------------+---------------------+------------+
指定要查询哪些列：select 列1,列2,... from 表名;
(root@localhost)[guojie_db]> select stu_id,stu_name,stu_addre from stu1;
+--------+----------+-----------+
| stu_id | stu_name | stu_addre |
+--------+----------+-----------+
| 1 | 关羽 | bj |
| 2 | 张三 | bj |
| 3 | 李四 | bj |
| 4 | 王五 | bj |
| 5 | 马六 | sh |
| 6 | 张飞 | tj |
| 43 | 刘备 | tj |
+--------+----------+-----------+
7 rows in set (0.00 sec)
注意：这些是灾难新使用，它会全表扫描，有时候数据太多非常消耗性能。
10.4.3.2 where
过滤使用，过滤条件有：
| 条件 | 作用 |
|---|---|
| = | 等于。 |
| > | 大于。 |
| < | 小于。 |
| >= | 大于等于。 |
| <= | 小于等于。 |
| != | 不等于。 |
| between and | 在xxx到xxx之间，也可以使用在时间上。 |
| in | 在其中，满足定义的值。 |
| not in | 不在其中，满足定义的值之外的值。 |
| and | 满足多个条件。 |
| or | 满足其中一个条件。 |
| like | 模糊匹配，一般用在字符串列上，用在数值上没有意义。 |
使用上面的表举例子：
select * from stu1 where stu_id=5
select * from stu1 where stu_id between 2 and 5;
select * from stu1 where stu_name in ('关羽', '张飞', '刘备');
select * from stu1 where stu_name not in ('关羽', '张飞', '刘备');
select * from stu1 where stu_gender='M' and stu_age > 20;
select * from stu1 where stu_addre='bj' or stu_addre='sh';
select * from stu1 where stu_name like '%三%';
10.4.3.3 group by
group by 主要配合分组函数使用（如：count、sum、min、max、avg、group_concat）。
select 查询内容 from 表名 group by 分组条件;
如上表:
需求1：分组统计各洲国家的数量。
语句： select continent, count(name) from country group by continent;
语句解读：查询 Continent, 统计数量(Name) 从 Country 分组 通过 Continent;
guojie@localhost:guojie_db>select continent, count(name) from country group by continent;
+---------------+-------------+
| continent | count(name) |
+---------------+-------------+
| North America | 37 |
| Asia | 51 |
| Africa | 58 |
| Europe | 46 |
| South America | 14 |
| Oceania | 28 |
| Antarctica | 5 |
+---------------+-------------+
7 rows in set (0.00 sec)
需求2：统计每个洲的人口总数。
语句： select continent, sum(population) from country group by continent;
语句解读：查询 Continent, 求和(population) 从 Country 分组 通过 Continent;
guojie@localhost:guojie_db>select continent,sum(population) from country group by continent;
+---------------+-----------------+
| continent | sum(population) |
+---------------+-----------------+
| North America | 482993000 |
| Asia | 3705025700 |
| Africa | 784475000 |
| Europe | 730074600 |
| South America | 345780000 |
| Oceania | 30401150 |
| Antarctica | 0 |
+---------------+-----------------+
7 rows in set (0.00 sec)
需求3：统计每个洲的人口数的同时列出所有改洲的所有国家。
此时使用‘select continent,name,sum(population) from country group by continent;’发现报错，这是因为三个列查出来的内容数量无法对应，而在name列上使用group_concat()函数之后发现就可以了,它把这个列的在同一个洲的拼在了一起，这样就可以正常显示了，后面会说到原理。
语句的执行流程：
10.4.3.4 count()
count函数主要用于统计数据的条数，可以对全表的数据条数统计，也可以统计单个列的条数，统计单列的数据条数的时候它只会统计字段值为not null(非空)的。可以配合group by使用，也可以单独使用。
统计全表的数据条数：select count(*) from 表名;
统计单个列的数据条数：select count(列名) from 表名;
guojie@localhost:guojie_db>select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
guojie@localhost:guojie_db>select count(name) from city;
+-------------+
| count(name) |
+-------------+
| 4079 |
+-------------+
1 row in set (0.00 sec)
10.4.3.5 having
select 列名,count(*) from 表名 group by 列名 having 过滤条件;
having类似于where，但是它要在group by后面使用。它可以再处理group by后的数据。
如下的查询,比如想要count(*)列滤出大于100的，就需要添加having过滤(需要注意的是having走不了索引，它是group by查询出来之后再处理，所以肯定用不了索引)：
使用having过滤出count(*)大于100的：
guojie@localhost:guojie_db>select CountryCode,count(*) from city group by CountryCode having count(*)>100;
+-------------+----------+
| CountryCode | count(*) |
+-------------+----------+
| BRA | 250 |
| CHN | 363 |
| IND | 341 |
| JPN | 248 |
| MEX | 173 |
| PHL | 136 |
| RUS | 189 |
| USA | 274 |
+-------------+----------+
8 rows in set (0.00 sec)
10.4.3.6 order by
大到小：select * from 表名 order by 列名 desc;
小到大：select * from 表名 order by 列名;
order by可以单独使用，也可以于用于处理goup by后得到的数据，它主要用来按某个列排序。比如如下表：
如果我们想让这个表通过population列从大到小或者从小到大排列：
小到大：
大到小：
配合group by使用：
10.4.3.7 limit
用法：xx xx xx limit N:显示前N条数据：
xx xx xx limit N offset M:跳过前M行，显示第M+1行到M+N行。
例如： limit 3 offset 5即标识跳过前5行，显示第6到第8行。也可以写成limit 5,3也表示跳过5行，显示6到8行。
limit 一般配合order使用，先前这个函数我们也用了好多次了，它主要用来限制显示的结果条目。
需求1：查询city表的前10条数据：
需求2：查询city表中Population列最大的前10条数据：
跳过前5行，显示后三行：
10.3.4 多表连接
数据样例：
如下有两张表，其中两张表通过student_id关联。
(root@localhost)[guojie_db]> select * from students;
+------------+--------------+--------+------+
| student_id | student_name | gender | age |
+------------+--------------+--------+------+
| 1 | 张三 | 男 | 20 |
| 2 | 李四 | 女 | 19 |
| 3 | 王五 | 男 | 21 |
| 4 | 马六 | 男 | 22 |
+------------+--------------+--------+------+
4 rows in set (0.00 sec)
(root@localhost)[guojie_db]> select * from family_addresses;
+------------+------------+-----------------------+
| address_id | student_id | address |
+------------+------------+-----------------------+
| 1 | 1 | 北京市朝阳区 |
| 2 | 2 | 上海市浦东新区 |
| 3 | 3 | 广州市天河区 |
| 4 | 4 | 云南昆明 |
+------------+------------+-----------------------+
4 rows in set (0.00 sec)
10.3.4.1 笛卡尔连接
select * from 表1,表2;
MySQL连接查询不适用任何条件使用的的是笛卡尔乘积，笛卡尔乘积是一个数学运算，假设我有两个集合 X 和 Y ，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X ，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
在数学中的定义：假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
如查询以上的数据样例：
(root@localhost)[guojie_db]> select * from students,family_addresses;
+------------+--------------+--------+------+------------+------------+-----------------------+
| student_id | student_name | gender | age | address_id | student_id | address |
+------------+--------------+--------+------+------------+------------+-----------------------+
| 4 | 马六 | 男 | 22 | 1 | 1 | 北京市朝阳区 |
| 3 | 王五 | 男 | 21 | 1 | 1 | 北京市朝阳区 |
| 2 | 李四 | 女 | 19 | 1 | 1 | 北京市朝阳区 |
| 1 | 张三 | 男 | 20 | 1 | 1 | 北京市朝阳区 |
| 4 | 马六 | 男 | 22 | 2 | 2 | 上海市浦东新区 |
| 3 | 王五 | 男 | 21 | 2 | 2 | 上海市浦东新区 |
| 2 | 李四 | 女 | 19 | 2 | 2 | 上海市浦东新区 |
| 1 | 张三 | 男 | 20 | 2 | 2 | 上海市浦东新区 |
| 4 | 马六 | 男 | 22 | 3 | 3 | 广州市天河区 |
| 3 | 王五 | 男 | 21 | 3 | 3 | 广州市天河区 |
| 2 | 李四 | 女 | 19 | 3 | 3 | 广州市天河区 |
| 1 | 张三 | 男 | 20 | 3 | 3 | 广州市天河区 |
| 4 | 马六 | 男 | 22 | 4 | 4 | 云南昆明 |
| 3 | 王五 | 男 | 21 | 4 | 4 | 云南昆明 |
| 2 | 李四 | 女 | 19 | 4 | 4 | 云南昆明 |
| 1 | 张三 | 男 | 20 | 4 | 4 | 云南昆明 |
+------------+--------------+--------+------+------------+------------+-----------------------+
16 rows in set (0.00 sec)
10.3.4.2 内连接
早期写法：select * from 表1,表2 where 表1.关联列=表2.关联列;
现在：select * from 表1 join 表2 on 表1.关联列=表2.关联列;（效果都一样，建议使用这种）
内连接是根据两个表之间的相关字段进行匹配，并返回满足条件的行。内连接只返回那些在两个表中都存在的匹配行。
例：
(root@localhost)[guojie_db]> select * from students,family_addresses where students.student_id=family_addresses.student_id;
+------------+--------------+--------+------+------------+------------+-----------------------+
| student_id | student_name | gender | age | address_id | student_id | address |
+------------+--------------+--------+------+------------+------------+-----------------------+
| 1 | 张三 | 男 | 20 | 1 | 1 | 北京市朝阳区 |
| 2 | 李四 | 女 | 19 | 2 | 2 | 上海市浦东新区 |
| 3 | 王五 | 男 | 21 | 3 | 3 | 广州市天河区 |
| 4 | 马六 | 男 | 22 | 4 | 4 | 云南昆明 |
+------------+--------------+--------+------+------------+------------+-----------------------+
4 rows in set (0.00 sec)
(root@localhost)[guojie_db]> select * from students join family_addresses on students.student_id=family_addresses.student_id;
+------------+--------------+--------+------+------------+------------+-----------------------+
| student_id | student_name | gender | age | address_id | student_id | address |
+------------+--------------+--------+------+------------+------------+-----------------------+
| 1 | 张三 | 男 | 20 | 1 | 1 | 北京市朝阳区 |
| 2 | 李四 | 女 | 19 | 2 | 2 | 上海市浦东新区 |
| 3 | 王五 | 男 | 21 | 3 | 3 | 广州市天河区 |
| 4 | 马六 | 男 | 22 | 4 | 4 | 云南昆明 |
+------------+--------------+--------+------+------------+------------+-----------------------+
4 rows in set (0.00 sec)
10.3.4.3 左外连接
select 列 from 表1 left join 表2 on 表1.关联列 = 表2.关联列;
左外连接返回左表中的所有行（以左表为驱动表），以及右表中与左表匹配的行。如果右表中没有匹配的行，则返回NULL值。这种查询一般要左表小一点比较快，因为它会查询左边表所有的内容去匹配右表。
(root@localhost)[guojie_db]> select * from students left join family_addresses on students.student_id=family_addresses.student_id;
+------------+--------------+--------+------+------------+------------+-----------------------+
| student_id | student_name | gender | age | address_id | student_id | address |
+------------+--------------+--------+------+------------+------------+-----------------------+
| 1 | 张三 | 男 | 20 | 1 | 1 | 北京市朝阳区 |
| 2 | 李四 | 女 | 19 | 2 | 2 | 上海市浦东新区 |
| 3 | 王五 | 男 | 21 | 3 | 3 | 广州市天河区 |
| 4 | 马六 | 男 | 22 | 4 | 4 | 云南昆明 |
+------------+--------------+--------+------+------------+------------+-----------------------+
4 rows in set (0.00 sec)
10.3.4.4 右外连接
select 列 from 表1 right join 表2 on 表1.关联列 = 表2.关联列;
右外连接返回右表中的所有行（以右表为驱动表），以及左表中与右表匹配的行。如果左表中没有匹配的行，则返回NULL值。这种查询一般要右表小一点比较快，因为它会查询右边表所有的内容去匹配左表。
(root@localhost)[guojie_db]> select * from students right join family_addresses on students.student_id=family_addresses.student_id;
+------------+--------------+--------+------+------------+------------+-----------------------+
| student_id | student_name | gender | age | address_id | student_id | address |
+------------+--------------+--------+------+------------+------------+-----------------------+
| 1 | 张三 | 男 | 20 | 1 | 1 | 北京市朝阳区 |
| 2 | 李四 | 女 | 19 | 2 | 2 | 上海市浦东新区 |
| 3 | 王五 | 男 | 21 | 3 | 3 | 广州市天河区 |
| 4 | 马六 | 男 | 22 | 4 | 4 | 云南昆明 |
+------------+--------------+--------+------+------------+------------+-----------------------+
4 rows in set (0.00 sec)
10.3.4.5 小结
通过使用内连接、左外连接和右外连接，我们可以根据不同的需求从多个表中获取所需的数据。这些连接操作使我们能够更好地处理复杂的关系型数据库查询。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了