查看“MySQL”的源代码

MySQL，一个开放源码的关系[[数据库]]管理系统，读作“My S-Q-L”。MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库。但被甲骨文公司收购后，大幅调涨MySQL商业版的售价，且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划[[MariaDB]]。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库。

==简介==
===时间轴===
*2008年1月16日，Sun（太阳微系统）正式收购MySQL。
*2009年4月20日，甲骨文公司宣布以每股9.50美元，74亿美元的总额收购Sun电脑公司。
*2013年6月18日，甲骨文公司修改MySQL授权协议，移除了GPL。但随后有消息称这是一个bug。

===安装===
====使用Docker安装====
安装[[Docker]]，然后直接从官网镜像启动一个mysql容器，并设置root用户密码为123456。
 docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql

{{了解更多
|[https://hub.docker.com/_/mysql Docker Hub：MySQL]
}}

====Ubuntu上安装====
<syntaxhighlight lang="bash">
#更新软件源
sudo apt-get update 

#
sudo apt-get install mysql-server  
</syntaxhighlight>

{{了解更多|[https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ MySQL文档：使用APT仓库安装]}}
====可能遇到问题====
*主机只有1G内存，mysql却占用很大内存。
在终端输入top查看各程序cup和内存等占用率，然后输入M，使结果按内存大小排序，可以看到mysqld的占用率高。直接修改mysql服务器的配置文件my.cnf，如果使用mysql容器，先进入容器终端。
<syntaxhighlight lang="bash">
#进入某个容器
docker exec -it some-mysql bash
#在容器内安装vim
apt-get update
apt-get install vim

#打开并修改my.cnf文件
vim /etc/mysql/my.cnf
#在文件里加入如下配置
performance_schema_max_table_instances=400  
table_definition_cache=400  
table_open_cache=256
performance_schema = off

#重启mysql
service mysql stop
service mysql start
#输入exit，退出容器，然后重启该容器
docker restart some-mysql 
#用top命令查看内存占用率
</syntaxhighlight>

* 错误：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

== 基础知识 ==
=== MySQL架构 ===
{{#drawio:MySQL架构}}

{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html MySQL 文档：MySQL 存储引擎架构概述]
|[https://www.oreilly.com/library/view/high-performance-mysql/9781449332471/ch01.html Oreilly：高性能MySQL - 第1章 MySQL架构和历史]
}}
===客户端===
{| class="wikitable"  style="width: 100%;
! 名称
! 界面
! 描述
|-
| MySQL客户端程序
| 命令
| 免费。<br \>MySQL客户端程序包含：<br \><code>mysql</code> 命令行客户端 <br \><code>mysqldump</code> 数据库备份程序 <br \><code>mysqladmin</code> 服务器管理程序 <br \>...
|- 
| [[Navicat]]
| 图形
| 收费，简单易用，可用于多种数据库管理
|- 
| SQLyog
| 图形
| 收费，简单易用
|- 
| DBeaver
| 图形
| 免费，开源
|- 
| MySQL Workbench
| 图形
| 免费，开源，MySQL官方工具
|- 
| [[VS Code]]插件MySQL
| 图形
| 免费，开源。一款[[VS Code]]中的插件，作者cweijan。支持多种数据库，使用简单方便。
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/programs-client.html MySQL 文档：MySQL程序 - 客户端程序]
}}
==数据类型==
=== 概览 ===

===类型===
{| class="wikitable"  style="width: 100%;
! 类别
! 类型
! 描述
! 示例
|-
| rowspan="8"|[https://dev.mysql.com/doc/refman/8.0/en/string-types.html 字符串]
| CHAR(n)
| 固定长度字符串。
| CHAR(10)
|- 
| VARCHAR(n)
| 可变长度字符
| VARCHAR(150)
|- 
| TINYTEXT
| 字符串大对象
| 
|- 
| TEXT
| 字符串大对象
| 
|- 
| MEDIUMTEXT
| 字符串大对象
| 
|- 
| LONGTEXT
| 字符串大对象
| 
|- 
| ENUM
| 枚举
| 
|- 
| SET
| 集合
| 
|- 
| rowspan="6"|二进制字符串
| BINARY(n)
| 二进制
| BINARY(10)
|-
| VARBINARY(n)
| 可变长度二进制
| VARBINARY(200)
|-
| TINYBLOB
| 二进制大对象
|
|-
| BLOB
| 二进制大对象
| BLOB
|-
| MEDIUMBLOB
| 二进制大对象
| 
|-
| LONGBLOB
| 二进制大对象
|
|-
| rowspan="7"|[https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html 准确数值]
| TINYINT
| 微型整数，非标准SQL。占1字节，范围：无符号0~255，有符号-128~127。MySQL没有定义布尔类型（BOOL,BOOLEAN），使用TINYINT(1)表示，0表示false，非零表示true。
| 
|-
| SMALLINT
| 小型整数，非标准SQL。占2字节，
| SMALLINT
|-
| MEDIUMINT
| 中型整数，非标准SQL。占3字节，
| MEDIUMINT
|-
| INTEGER, INT
| 整数，占4字节，范围：无符号0~4294967295，有符号-2147483648~2147483647
| INTEGER
|-
| BIGINT
| 大型整数，非标准SQL。占8字节，范围：无符号0~	2^64-1，有符号-2^63~2^63
| BIGINT
|-
| NUMERIC(p,s)
| 带固定精度和小数位数的数值。p（precision）表示整数和小数的总位数，s（scale）表示小数位数，p和s都可以省略。
| NUMERIC(5,2)
|-
| DECIMAL(p,s)
| 带固定精度和小数位数的数值，p（precision）表示整数和小数的总位数，s（scale）表示小数位数，p和s都可以省略。
| DECIMAL(5,2)
|-
| rowspan="3"|[https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html 近似数值]
| FLOAT(p)
| 浮点数
| FLOAT(3)
|-
| REAL
| 单精度浮点数
| REAL
|-
| DOUBLE
| 双精度
| DOUBLE
|-
| rowspan="5"|[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html 日期时间]
| DATE[(p)]
| 日期。默认显示YYYY-MM-DD，范围1000-01-01~9999-12-31
| DATE
|-
| TIME(p)
| 时间
| TIME
|-
| DATETIME[(p)]
| 日期和时间。支持的范围是 '1000-01-01 00:00:00.000000'到 '9999-12-31 23:59:59.999999'。
| DATE
|-
| TIMESTAMP[(p)]
| 时间戳
| TIMESTAMP(9)
|-
| YEAR[(p)] 
| 年。默认显示YYYY
| YEAR
|-
| rowspan="1"|JSON
| JSON
| JSON
| 
|-
| rowspan="1"|空间数据
| geom
| 空间数据
| 
|}


{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/data-types.html MySQL 文档：数据类型]
|[https://dev.mysql.com/doc/refman/8.0/en/other-vendor-data-types.html MySQL 文档：使用来自其他数据库引擎的数据类型]
}}

===转换===
MySQL可以自动转换其他数据库的某些数据类型到MySQL的数据类型。可以在表定义时使用这些类型，如：
<syntaxhighlight lang="SQL">
-- 定义一个表t1
CREATE TABLE t1 (id int3, is_ok BOOL);

-- 查看数据表定义可知，已经转换为MySQL类型
desc t1;
</syntaxhighlight>

{| class="wikitable" 
|-
! 其他数据库类型
! MySQL 类型
|-
| BOOL
| TINYINT
|-
| BOOLEAN
| TINYINT
|-
| CHARACTER VARYING(M)
| VARCHAR(M)
|-
| FIXED
| DECIMAL
|-
| FLOAT4
| FLOAT
|-
| FLOAT8
| DOUBLE
|-
| INT1
| TINYINT
|-
| INT2
| SMALLINT
|-
| INT3
| MEDIUMINT
|-
| INT4
| INT
|-
| INT8
| BIGINT
|-
| LONG VARBINARY
| MEDIUMBLOB
|-
| LONG VARCHAR
| MEDIUMTEXT
|-
| LONG
| MEDIUMTEXT
|-
| MIDDLEINT
| MEDIUMINT
|-
| NUMERIC
| DECIMAL
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/other-vendor-data-types.html MySQL 文档：使用来自其他数据库引擎的数据类型]
}}

== SQL语句 ==
=== 基础语法 ===
Mysql的SQL语句与标准的[[SQL]]语句类似：
*不区分大小写，除固定的字符串值。但一般SQL关键词大写，其他列名和表名等小写，这样易于阅读。
*语句以分号 <code>;</code> 结束。
*注释
<syntaxhighlight lang="sql">
-- 单行注释
SELECT * FROM 表名;

/* 
多行注释
多行注释
*/
SELECT * FROM 表名;
</syntaxhighlight>
=== 数据定义 DDL ===
==== 数据库 ====
{| class="wikitable" 
|-
! 名称
! 常用语句
! 描述
! 示例
|-
| 创建数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/create-database.html create database]
|create database 数据库名； <br \><br \>create database if not exists 数据库名； <br \><br \>create database 数据库名 character set 字符集；
| 创建指定名称的数据库。<code>character set</code> 用于指定字符集，<code>SHOW CHARACTER SET</code>语句显示所有可用的字符集。 <br \><br \>创建一个数据库，MySQL会在数据目录下创建一个同名的目录。MySQL 对数据库的数量没有限制。
| <code>CREATE DATABASE db1; </code> 创建名称为db1的数据库。  <br \><code>CREATE DATABASE db2 CHARACTER SET utf8;</code> 创建数据库db2，并指定utf8字符集。
|-
| 查看所有数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/show-databases.html show databases]
| show databases;  <br \><br \><nowiki>show databases like '模式';</nowiki>
| 显示MySQL中的数据库。
| <code>show databases; </code> 显示所有数据库。 <br \><code>show databases like 'my%';</code> 显示所有以my开头的数据库。
|-
| 使用数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/use.html use]
| use 数据库;
| 当前语句执行的默认数据库。<br \><br \>如<code>USE db1;</code>接下来所有操作默认在db1数据库中，<code>SELECT * FROM table1;</code>是从<code>db1.table1</code>表查询，要使用其他数据库的表需要指定数据库名如<code>db2.table1</code>。
| <code>USE db2;</code> 切换到db2数据库。
|-
| 查看当前数据库
| select database();
| 查看当前语句执行的默认数据库。
| select database();
|-
| 查看数据库定义
| show create database 数据库；
| 查看一个数据库的定义信息。
| SHOW CREATE DATABASE db1;
|-
| 修改数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/alter-database.html alter database]
| alter database 数据库 character set 字符集； <br \><br \><nowiki>alter database 数据库 read only = {DEFAULT | 0 | 1}</nowiki>
| 修改数据库。<br \><br \><code>character set</code> 修改字符集<br \><code>read only</code> 修改是否只读。8.0.22版本新增。
| <code>ALTER DATABASE db1 CHARACTER SET utf8;</code> 修改数据库db1的字符集为utf8。 <br \><code>ALTER DATABASE db1 READ ONLY = 1</code> 修改数据库db1为只读。
|-
| 删除数据库 <br \>[https://dev.mysql.com/doc/refman/8.0/en/drop-database.html drop database]
| drop database 数据库; <br \>drop database if exists 数据库; 
| 删除数据库。需要非常小心，所有该数据库及其所有表都将删除。if exists 可防止数据库不存在而报错。
| DROP DATABASE db1;
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html MySQL 文档：数据定义语句]
|[https://dev.mysql.com/doc/refman/8.0/en/database-use.html MySQL 文档：教程 - 创建和使用数据库]
}}

==== 数据表 ====
{| class="wikitable" 
|-
! 名称
! 语句
! 描述
! 示例
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-table.html 创建表]
| <syntaxhighlight lang="sql">
create table 表名(
列名1 数据类型， 
列名2 数据类型 
)； </syntaxhighlight><syntaxhighlight lang="sql">
create table  [if not exists] 表名(
  列定义,
  ...,
  表约束,
  ...,
)
表选项,
...,
分区选项,
...;
</syntaxhighlight>
| 默认情况下，使用InnoDB存储引擎在当前默认数据库中创建表，MySQL对表的数量没有限制。 <br \><br \>常用[https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-types-attributes 列定义]：<br \>1.列名 <br \>2.数据类型，如<code>CHAR(20)</code>。<br \>3.null或 not null，能不能为空值，默认可以<code>NULL</code>。 <br \>4.default 默认值，如<code>DEFAULT 0</code>， 8.0.13+默认值可以常量或用括号包裹(表达式) <br \>5.AUTO_INCREMENT，自增长，每个表只能有一个自增长列且该列添加索引。6.COMMENT 注释，给列添加注释，最长1024 个字符。使用SHOW CREATE TABLE和SHOW FULL COLUMNS语句时会显示。<br \>7.primary key，主键，一个表只有一个。该列必须约束有UNIQUE和NOT NULL，没有声明时MySQL会自动声明。<br \>8.index 或 key，普通索引。<br \>9.unique，唯一索引。<br \>10.fulltext，全文索引。<br \>11.foreign key，外键。<br \>12.check，检查约束。 <br \><br \>常用[https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-options 表选项]：<br \>1.engine，存储引擎，默认InnoDB，设置其他如<code>ENGINE MEMORY</code><br \>2.CHARACTER SET，指定表的字符集 <br \>3.COMMENT，表的注释。 <br \><br \>常用[https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-partitioning 分区选项]<br \>1.
| <syntaxhighlight lang="sql">
CREATE TABLE book (
book_id INT UNSIGNED,
book_name VARCHAR(50)
); </syntaxhighlight>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html 快速创建空表]
| create table 新表名 like 旧表名;
| 根据一个表的定义创建一个空表。只能用于创建普通表，不能用于视图。
| <code>CREATE TABLE t2 LIKE t1; </code> <br \><code>CREATE TABLE IF NOT EXISTS t2 LIKE t1; </code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html 使用SELECT创建表]
| <nowiki>create table 新表名 [as] select语句 旧表名;</nowiki>
| 在CREATE TABLE语句的末尾添加SELECT语句，创建一个表。
|<syntaxhighlight lang="sql">
CREATE TABLE t2 SELECT * FROM t1;
</syntaxhighlight>
<syntaxhighlight lang="sql">
CREATE TABLE t2 (
a INT, 
c INT )
SELECT * FROM FROM t1;
</syntaxhighlight>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html 创建临时表]
| create temporary table 表名 ...; 
| 临时表只在当前会话中可见，当会话关闭时自动删除。
| <code>CREATE TEMPORARY TABLE t2 SELECT * FROM t1;</code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-tables.html 查看所有表]
| show tables;
| 查看当前数据库中的所有表名。
| <code>show tables;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html 查看所有表详细信息]
| show table status;
| 与show tables类似，但提供了大量与表相关的信息，而不只是表名，如存储引擎、行数、创建时间和更新时间等。
| <code>show table status;</code> <br \><code>show table status like 't%';</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/describe.html 查看表中列的信息]
| desc 表名；<br \>describe 表名;
| 查看表中列的信息。
| <code>DESC t1;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-columns.html 查看表中列的信息]
| show columns from 表名; <br \>show [extended] [full] columns from 表名;
| 查看表中列的信息，比desc语句显示更多信息。extended，可以输出MySQL内部使用的隐藏信息。full，可以输出列排序规则，注释，当前用户权限。
| <code>SHOW COLUMNS FROM t1;</code> <br \><code>SHOW COLUMNS FROM db2.t1;</code> <br \> <code>SHOW FULL COLUMNS FROM t1 FROM db2;</code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html 查看建表语句]
| show create table 表名;
| 查看表的create table语句,
| <code>SHOW CREATE TABLE t1;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/rename-table.html 修改表名]
| rename table 旧表名 to 新表名；
| 修改表名
| <code>RENAME TABLE t1 TO new_t1;</code> <br \><code>RENAME TABLE old_t1 TO new_t1, old_t2 to new_t2;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-add-drop-column 增加列和删除列]
| <syntaxhighlight lang="sql">
alter table 表名 add [column] 列名 数据类型；
</syntaxhighlight><syntaxhighlight lang="sql">
alter table 表名 drop [column] 列名；
</syntaxhighlight>
| 添加列add，删除列drop，column关键字可以省略。
| <code>ALTER TABLE t1 DROP name VARCHAR(20);</code> 向t1表增加name列 <br \><code>ALTER TABLE t1 DROP COLUMN a, DROP COLUMN b;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column 修改列名称或定义]
| alter table 表名 change 旧列名 新列名 列定义;
| 可以同时修改列名或列定义
| <code>ALTER TABLE t1 CHANGE a c INT NOT NULL;</code> 修改a列名称为c，并修改列定义。
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column 修改列定义]
| alter table 表名 modify 旧列名 列定义;
| 只修改列定义
| <code>ALTER TABLE t1 MODIFY a INT NOT NULL;</code> 
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column 修改列名]
| alter table 表名 rename column 旧列名 to 新列名;
| 只修改列名称
| <code>ALTER TABLE t1 RENAME COLUMN a TO c;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-character-set 修改字符集]
|<syntaxhighlight lang="sql">
alter table 表名 convert to character set 字符集；
</syntaxhighlight> <syntaxhighlight lang="sql">
alter table 表名 modify 列名 数据类型 character set 字符集；
</syntaxhighlight> <syntaxhighlight lang="sql">
alter table 表名 [default] character set 字符集；
</syntaxhighlight>
| convert to用于修改所有列字符集。<br \>modify或change修改单列字符集。<br \>default仅修改默认字符集，对后面添加列有效。
|<code>ALTER TABLE t1 MODIFY a TEXT CHARACTER SET utf8;</code>
|-
|[https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html 快速清空表]
|<code>truncate [table] 表名</code>
|快速清楚一个表，并创建一个相同空表。
| <code>TRUNCATE TABLE t1</code>
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html MySQL 文档：数据定义语句]
|[https://dev.mysql.com/doc/refman/8.0/en/database-use.html MySQL 文档：教程 - 创建和使用数据库]
}}

=== 数据操作 DML ===
==== 插入数据 ====
{| class="wikitable" 
|-
! 名称
! 语句
! 描述
! 示例
|-
| 插入数据
| <syntaxhighlight lang="sql">
insert into 表名 (列名列表) values (值列表);
</syntaxhighlight><syntaxhighlight lang="sql">
insert into 表名 (列名列表) values (值列表), (值列表), ...;
</syntaxhighlight>
| 可以插入一行或多行数据。列名列表要和值列表对应，列名列表可以是表的部分列，其他未列出填充默认值。
|<code>INSERT INTO t1 (a) VALUES (1);</code> <br \><code>INSERT INTO t1 (a,b,c) VALUES (2,"hi",10)</code>
|-
|完整列插入数据
| <syntaxhighlight lang="sql">
insert into 表名 values (值列表);
</syntaxhighlight><syntaxhighlight lang="sql">
insert into 表名 values (值列表),(值列表),...;
</syntaxhighlight>
| 省略列名列表，但值列表要与表结构一致，不能省略。
|<code>INSERT INTO t1 VALUES (3,"hello",20)</code>
|-
|SELECT语句插入数据
|<syntaxhighlight lang="sql">
insert into 表名 [(列名列表)] select语句;
</syntaxhighlight>
| select语句结果插入指定表。
| <code>INSERT INTO t2 (a) SELECT t1.a from t1 where t1.a > 60;</code>
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/insert.html MySQL 文档：INSERT 语句] 
|[https://dev.mysql.com/doc/refman/8.0/en/insert-select.html MySQL 文档：INSERT ... SELECT 语句]
}}

==== 更新数据 ====
{| class="wikitable" 
|-
! 名称
! 语句
! 描述
! 示例
|-
|[https://dev.mysql.com/doc/refman/8.0/en/update.html 更新整列数据]
|<syntaxhighlight lang="sql">
update 表名 set 列名 = 值;
</syntaxhighlight><syntaxhighlight lang="sql">
update 表名 set 列名 = 值 order by 子句;
</syntaxhighlight>
|不带where语句，更新一列数据。可以使用oder by子句排序后更新，如想要主键的列+1，使用order降序，先更新最大的值就不会报重复值错误。
|<code>UPDATE t1 SET a = a + 1;</code> 表t1的a列值+1 <br \><code>UPDATE t1 SET b = 'hi', c = c + 1;</code> 表t1的b列改为'hi',c列+1 <br \><code>UPDATE t3 SET id = id + 1 order by id desc;</code> 表t3的id列值+1
|-
|[https://dev.mysql.com/doc/refman/8.0/en/update.html 条件更新数据]
|<syntaxhighlight lang="sql">
update 表名 set 列名 = 值 where 语句;
</syntaxhighlight>
| 只修改列中符合条件的行。
|<code>UPDATE t1 SET a = a + 1 WHERE a > 10;</code> 表t1的a列中大于10的都+1
|-
| [https://dev.mysql.com/doc/refman/8.0/en/update.html 查询更新数据]
|<syntaxhighlight lang="sql">
update 表名，
  (select 子句) as 子句表名 
set 表名.列名 = 值 
where 表名.列名 = 子句表名.列名;
</syntaxhighlight> 
|
|
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/update.html MySQL 文档：UPDATE 语句] 
}}

==== 删除数据 ====
{| class="wikitable" 
|-
! 名称
! 语句
! 描述
! 示例
|-
|[https://dev.mysql.com/doc/refman/8.0/en/delete.html 删除所有数据]
| delete from 表名;
| 删除指定表所有行。快速删除使用truncate table语句，该语句是删除整个表，再创建一个同样的空表。
| <code>delete from t1;</code> 删除t1表的所有数据。
|-
|[https://dev.mysql.com/doc/refman/8.0/en/delete.html 条件删除数据]
|<syntaxhighlight lang="sql">
delete from 表名 where 语句;
</syntaxhighlight>
|按条件删除行
|<code>delete from t1 where a > 10;</code> 删除t1表中a>10的行。
|-
|[https://dev.mysql.com/doc/refman/8.0/en/delete.html 多表删除数据]
|
|
|
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/delete.html MySQL 文档：delete 语句] 
}}

=== 数据查询 DQL ===
==== 书写和执行顺序 ==== 
SQL中最常见的操作是查询，它是通过SELECT语句执行的。SELECT语句从一个或多个的表或视图中检索数据。 常用SELECT语句书写顺序如下，其中括号里的数字表示执行顺序：
<syntaxhighlight lang="sql">
(7)  SELECT    (8) DISTINCT <显示内容>   
(1)  FROM <左表名>       
(3)  <连接类型> JOIN <右表名>
(2)            ON <连接条件>
(4)  WHERE  <筛选条件>     
(5)  GROUP BY  <分组>
(6)  HAVING <分组后筛选条件>
(9)  ORDER BY <排序>
(10) LIMIT <限制与偏移量>;
</syntaxhighlight>


{{了解更多
| [https://dev.mysql.com/doc/refman/8.0/en/select.html MySQL 文档：select 语句]
|《MySQL技术内幕：SQL编程》：第三章 查询处理 - 3.1 逻辑查询处理
}}

==== 基本查询 ====
{| class="wikitable"  style="width: 100%;
! 查询数据
! 语句
! 描述
! 示例
|-
| [https://dev.mysql.com/doc/refman/8.0/en/selecting-all.html 查询所有列]
|<syntaxhighlight lang="sql">
select * from 表名;
</syntaxhighlight>
| 通配符<code>*</code>，表示表中所有列。
| SELECT * FROM book;
|-
| [https://dev.mysql.com/doc/refman/8.0/en/selecting-columns.html 查询单列]
| <syntaxhighlight lang="sql">
select 列名 from 表名;
</syntaxhighlight>
| 
|<code>SELECT id FROM book;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/selecting-columns.html 查询多列]
| <syntaxhighlight lang="sql">
select 列名1, 列名2, 列名3 from 表名;
</syntaxhighlight>
| 列名用逗号隔开，最后一个列名不加逗号。
|<code>SELECT name, birth FROM person;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/select.html 设置别名]
| <syntaxhighlight lang="sql">
select 列名 as 别名 from 表名;
</syntaxhighlight> <syntaxhighlight lang="sql">
select 列名1 别名1, 列名2 别名2 from 表名;
</syntaxhighlight>
| 有时为列名和表名设置别名，可以显示更清楚，方便引用。<br \>显式设置别名的关键字<code>AS</code>可以省略，但不建议，使用AS增加了可读性。
| <code>SELECT name AS n FROM book ORDER BY n;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/select.html 去除重复值]
| <syntaxhighlight lang="sql">
select distinct 列名 from 表名;
</syntaxhighlight><syntaxhighlight lang="sql">
select distinct 列名1, 列名2, ... from 表名;
</syntaxhighlight>
| 可以查询一列不同的值，或多列组合不同的值。distinct关键词要置于列名的开头。
|<code>SELECT DISTINCT name FROM book;</code> <br \><code>SELECT DISTINCT id, name FROM book;</code> <br \><code>SELECT count( DISTINCT id, name ) FROM book;</code>
|-
| [https://dev.mysql.com/doc/refman/8.0/en/select.html 限制返回行数]
|<syntaxhighlight lang="sql">
select * from 表名 limit 行数;
</syntaxhighlight><syntaxhighlight lang="sql">
select * from 表名 limit 偏移量,行数;
</syntaxhighlight><syntaxhighlight lang="sql">
select * from 表名 limit 行数 offset 偏移量;
</syntaxhighlight>
|LIMIT子句用于限制SELECT语句返回的行数。默认偏移量为0，表示结果集开头开始返回，即<code>limit 行数</code>和<code>limit 0, 行数</code>一样。<code>limit 1, 5</code>表从结果集的第2行开始，选取5行。为了与 [[PostgreSQL]] 兼容，MySQL 也支持 <code>LIMIT 行数 OFFSET 偏移量</code> 语法。 
|
|-
|
|
|
|
|-
|
|
|
|
|}
==== 排序 ====
ORDER BY子句用于对一列或多列进行排序。
{| class="wikitable"  style="width: 100%;
! 名称
! 语句
! 描述
! 示例
|- 
| 单列排序
| <syntaxhighlight lang="sql">
select * from 表名
order by 列名;
</syntaxhighlight> <syntaxhighlight lang="sql">
select * from 表名
order by 列名 desc;
</syntaxhighlight>
| 升序，使用<code>ASC</code>关键字，默认方式，关键字可以省略 <br \>降序，使用<code>DESC</code>关键字
| 
|- 
| 多列排序
| <syntaxhighlight lang="sql">
select * from 表名
order by 列名1 desc, 列名2;
</syntaxhighlight>
| 先按第一列排序，当第一列相同时再按第二列排序。
| 
|}

==== 条件筛选 ====
WHERE子句用于条件筛选查询。
{| class="wikitable"  style="width: 100%;
! 名称
! 语句
! 描述
! 示例
|-
| 比较函数和比较运算符
| <syntaxhighlight lang="sql">
select * from 表名 
where 列名 = 值;
</syntaxhighlight>
| 常见[https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html 比较函数和比较运算符]：<br \><code>=</code> 等于 <br \><code><>, != </code> 不等于 <br \><code>>, >=</code> 大于，大于等于 <br \><code><, <=</code> 小于，小于等于 <br \><code>BETWEEN 最小值 AND 最大值</code> 最小值和最大值之间，包含边界。<br \><code>IN(集合)</code> 是否在一组值内 <br \><code>IS TRUE, FALSE, or UNKNOWN</code> 是否为某个布尔值 <br \><code>IS NOT TRUE, FALSE, or UNKNOWN</code> 是否不为某个布尔值 <br \><code>IS NULL</code> 是否为空值 <br \><code>IS NOT NULL</code>是否不为空值 <br \><code>ISNULL(表达式)</code> <br \><code>LIKE</code> 简单模式匹配，可以使用2个通配符，<code>%</code>匹配0个或多个字符，<code>_</code>匹配一个字符。
|
|- 
| 逻辑运算符
| <syntaxhighlight lang="sql">
select * from 表名 
where 列名1 = 值1 
  and 列名2 = 值2;
</syntaxhighlight>
|逻辑运算符可以将多个查询条件组合。<br \><br \>Mysql中的[https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html 逻辑运算符]：<br \><code>AND, &&</code> 逻辑与 <br \><code>NOT, !</code> 逻辑非 <br \><code><nowiki>OR, ||</nowiki></code> 逻辑或 <br \><code>XOR</code> 逻辑异或 
|
|- 
| 
| 
| 
|}

{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html  MySQL 文档：比较函数和比较运算符]
}}

==== 分组 ====
GROUP BY子句可以对一列或多列进行分组，常用聚合函数一起使用实现分组统计。可以使用HAVING子句进行分组统计后条件筛选。

常用[https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html 聚合函数]：
{| class="wikitable"  style="width: 100%;
! 聚合函数
! 描述
! 示例
|-
| AVG() 
| 平均值
| <syntaxhighlight lang="sql">
select 列名1, avg(列名2) from 表名
group by 列名1
</syntaxhighlight>
|- 
| MAX()
| 最大值
| 
|- 
| MIN()
| 最小值
| 
|- 
| SUM()
| 总和
| 
|- 
| STD()
| 总体标准差
| 
|}

{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions-and-modifiers.html MySQL 文档： 聚合函数]
}}

==== 多表查询 ====
多表查询，一般通过[https://dev.mysql.com/doc/refman/8.0/en/join.html JOIN]关键字将表建立连接，再查询。
{| class="wikitable" 
|-
! 分类
! 命令
! 描述
! 示例
|-
| 交叉连接查询
| <syntaxhighlight lang="sql">
SELECT 列名 FROM 左表, 右表;
</syntaxhighlight>
| 交叉连接（Cross Join），两个表之间产生笛卡尔积，即左表的每一行与右表的每一行连接，返回大量行（两表行数的乘积）。
| 
|-
| 内连接查询
| <syntaxhighlight lang="sql">
SELECT 列名 
FROM 左表
[INNER] JOIN 右表 
  ON 条件;
</syntaxhighlight>
| 按条件取两表交集，使用inner join ...on 语句，inner可省略。
|
|-
| 隐式内链接查询
| <syntaxhighlight lang="sql">
SELECT 
列名 
FROM 左表, 右表 
WHERE 连接条件;
</syntaxhighlight>
| 结果与内连接查询一样。
| 
|-
| 左外连接
|<syntaxhighlight lang="sql">
SELECT 列名 
FROM 左表  
LEFT [OUTER] JOIN 右表 
  ON 条件;
</syntaxhighlight> 
| 以左表为基准，左边所有数据，以及右表与左表有交集部分。对于左表有而右表没有的数据，右边显示NULL。OUTER可省略。
| 
|-
| 右外连接
|<syntaxhighlight lang="sql">
SELECT 列名 
FROM 左表  
RIGHT [OUTER] JOIN 右表 
  ON 条件;
</syntaxhighlight> 
| 以右表为基准，右边所有数据，以及左表与右表有交集部分。对于右表有而左表没有的数据，左边显示NULL。OUTER可省略。与左外连接相似，一般使用左外连接即可。
| 
|}

{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/join.html MySQL 文档：JOIN 子句]
}}

==== 查询合并 ====
合并多条SELECT语句的结果集使用UNION关键字。两个SELECT语句的结果列数需要相同，数据类型相似。合并结果集的列名同第一个SELECT语句，数据类型长度同最长的SELECT语句。

{| class="wikitable"  style="width: 100%;
! 名称
! 语句
! 描述
! 示例
|-
| 合并，删除重复行 <br \>UNION
| <syntaxhighlight lang="sql">
SELECT ...
UNION [DISTINCT]
SELECT ...
</syntaxhighlight>
| UNION默认删除重复行，可以省略显示指定的关键字<code>DISTINCT</code>。
| 
|- 
| 合并，不删除重复行 <br \>UNION ALL
| <syntaxhighlight lang="sql">
SELECT ...
UNION ALL
SELECT ...
</syntaxhighlight>
| 使用关键字<code>UNION ALL</code>，保留所有重复行。因为不用删除重复行，<code>UNION ALL</code>速度会比<code>UNION</code>速度快。
|
|}

{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/union.html MySQL 文档：UNION 子句]
}}

==== 子查询 ====
子查询是SELECT语句中嵌套的SELECT语句，子查询中也可以嵌套子查询。子查询必须在括号内，子查询的返回可以是单个值、单行、单列和表。子查询比<code>JOIN</code>和<code>UNION</code>更易读和调试，对于复杂语句，子查询可以逐步测试SELECT语句。




{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/subqueries.html MySQL 文档：子查询]
}}

=== 数据控制 DCL ===

== 函数和运算符 ==
=== 函数概览 ===
使用函数可以方便进行各种计算、转换等数据处理。MySQL内置了很多函数。

默认情况下，函数和它后面括号之间不能有空格，函数的参数可以有空格。

{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/functions.html MySQL 文档：函数和运算符]
}}

=== 数值函数和运算符 ===
{| class="wikitable" 
|-
! 名称
! 描述
! 示例
|-
| +, -, *, /
| 加，减，乘，除运算符
| <code>select 1+1;</code> 返回2
|-
| <nowiki>-</nowiki>
| 更改符号
| <code>select -2;</code> 返回-2
|-
| %, MOD
| 返回余数运算符
| <code>select 7 % 4;</code> 7除以4的余数，即3 <br \><code>select 7 mod 4;</code>
|-
| MOD()
| 返回余数函数
| 
|-
| DIV
| 整除，返回整数丢弃小数。
| <code>select 3 div 2;</code> 返回1
|-
| ABS()
| 返回绝对值
| 
|-
| CEIL(), CEILING()
| 向上取整，返回不小于参数的最小整数值
| <code>select ceil(2.5);</code> 返回3
|-
| FLOOR()
| 向下取整，返回不大于参数的最大整数值
| <code>select floor(2.5);</code> 返回2
|-
| ROUND(X), ROUND(X,D)
| 四舍五入，参数D指定小数位数，默认为0。
| 
|-
| RAND(), RAND(N)
| 返回一个0~1的随机数，左闭右开。可以指定一个整数参数作为种子值，将每次返回一组相同的随机值。
| <code>select rand();</code> <br \><code>SELECT FLOOR(2 + (RAND() * 3));</code>返回>=2且<5的随机整数 <br \><code>select rand(3);</code>
|-
| PI()
| 返回 pi 的值
| 
|-
| SIN()、COS()、COT()、TAN()
| 返回参数的正弦、余弦、余切、正切
| 
|-
| ASIN()、ACOS()、ATAN()
| 返回反正弦、反余弦、反正切
| 
|-
| ATAN2(),ATAN()
| 返回两个参数的反正切
| 
|-
| CONV()
| 在不同数字基数之间转换数字
| 
| 
|-
| CRC32()
| 计算循环冗余校验值
| 
|-
| DEGREES()
| 将弧度转换为度
| 
|-
| EXP()
| 提升到权力
| 
|-
|LN()
|自然数e为底的对数
|
|-
| LOG()
| 返回第一个参数的自然对数
| 
|-
| LOG2()、LOG10()
| 返回参数的以 2或10 为底的对数
| 
|-
| POW()
| 将引发的参数返回到指定的幂
| 
|-
| POWER()
| 将引发的参数返回到指定的幂
| 
|-
| RADIANS()
| 返回参数转换为弧度
| 
|-
| SIGN()
| 返回参数的符号
| 
|-
| SQRT()
| 返回参数的平方根
| 
|-
| TRUNCATE()
| 截断到指定的小数位数
| 
|}
{{了解更多
|[https://dev.mysql.com/doc/refman/8.0/en/numeric-functions.html  MySQL 文档：数值函数和运算符]
}}

=== 字符串函数 ===
{| class="wikitable" 
|-
! 名称
! 描述
! 示例
|-
| CONCAT(s1,s2,...)
| 拼接字符串
| 
|-
| CONCAT_WS(separator,s1,s2,...)
| 带分隔符拼接字符串
| 
|-
| LEFT(s,n)
| 返回字符串s最左边的n个字符。
| 
|-
| RIGHT(s,n)
| 返回字符串s最右边的n个字符。
| 
|-
| MID(s,p,n), SUBSTRING(),SUBSTR()
| 返回字符串s从第p个位置开始的n个字符。3个函数都是一样。
| 
|-
| TRIM()
| 删除字符串开头和结尾的空格
| 
|-
| LTRIM()、RTRIM()
| 删除字符串开头或结尾的空格
| 
|-
| REVERSE(s)
| 反转字符串中的字符
| <code>SELECT REVERSE('abc');</code> 返回'cba'
|-
| REPEAT(s, n)
| 重复一个字符串指定的次数
| <code>SELECT REPEAT('ab', 3);</code> 返回'ababab'
|-
| LOWER(), LCASE()
| 转换为大写
| 
|-
| UPPER(), UCASE()
| 转换为大写
| 
|-
| LENGTH()
| 返回字符串的长度（以字节为单位）
| 
|-
| REPLACE(s, from_s, to_s)
| 字符串替换
| 
|-
| ASCII()
| 返回最左边字符的数值
| 
|-
| BIN()
| 返回一个包含数字二进制表示的字符串
| 
|-
| BIT_LENGTH()
| 返回参数的长度（以位为单位）
| 
|-
| CHAR()
| 返回传递的每个整数的字符
| 
|-
| CHAR_LENGTH()
| 返回参数中的字符数
| 
|-
| CHARACTER_LENGTH()
| CHAR_LENGTH() 的同义词
| 
|-
| ELT()
| 返回索引号处的字符串
| 
|-
| EXPORT_SET()
| 返回一个字符串，这样对于值位中设置的每个位，您都会得到一个 on 字符串，对于每个未设置的位，您都会得到一个 off 字符串
| 
|-
| FIELD()
| 后续参数中第一个参数的索引（位置）
| 
|-
| FIND_IN_SET()
| 第二个参数中第一个参数的索引（位置）
| 
|-
| FORMAT()
| 返回格式化为指定小数位数的数字
| 
|-
| FROM_BASE64()
| 解码base64编码字符串并返回结果
| 
|-
| HEX()
| 十进制或字符串值的十六进制表示
| 
|-
| INSERT()
| 在指定位置插入子字符串，最多指定字符数
| 
|-
| INSTR()
| 返回子字符串第一次出现的索引
| 
|-
| LIKE
| 简单的模式匹配
| 
|-
| LOAD_FILE()
| 加载命名文件
| 
|-
| LOCATE()
| 返回子字符串第一次出现的位置
| 
|-
| LPAD()
| 返回字符串参数，左填充指定的字符串
| 
|-
| MAKE_SET()
| 返回一组逗号分隔的字符串，这些字符串在 bits 集中具有相应的位
| 
|-
| MATCH()
| 执行全文搜索
| 
|-
| NOT LIKE
| 简单模式匹配的否定
| 
|-
| NOT REGEXP
| 否定正则表达式
| 
|-
| OCT()
| 返回包含数字的八进制表示的字符串
| 
|-
| ORD()
| 返回参数最左边字符的字符代码
| 
|-
| POSITION()
| LOCATE() 的同义词
| 
|-
| QUOTE()
| 转义参数以在 SQL 语句中使用
| 
|-
| REGEXP
| 字符串是否匹配正则表达式
| 
|-
| REGEXP_INSTR()
| 子串匹配正则表达式的起始索引
| 
|-
| REGEXP_LIKE()
| 字符串是否匹配正则表达式
| 
|-
| REGEXP_REPLACE()
| 替换匹配正则表达式的子字符串
| 
|-
| REGEXP_SUBSTR()
| 返回匹配正则表达式的子字符串
| 
|-
| RLIKE
| 字符串是否匹配正则表达式
| 
|-
| RPAD()
| 附加字符串指定次数
| 
|-
| SOUNDEX()
| 返回一个 soundex 字符串
| 
|-
| SOUNDS LIKE
| 比较声音
| 
|-
| SPACE()
| 返回指定空格数的字符串
| 
|-
| STRCMP()
| 比较两个字符串
| 
|-
| SUBSTRING_INDEX()
| 从指定出现次数的分隔符之前的字符串中返回子字符串
| 
|-
| TO_BASE64()
| 返回转换为 base-64 字符串的参数
| 
|-
| UNHEX()
| 返回包含数字的十六进制表示的字符串
| 
|-
| WEIGHT_STRING()
| 返回字符串的权重字符串
| 
|}

== 索引 ==

== 优化 ==
=== 常用工具 ===

== 导入导出 ==
=== 导入 ===
{| class="wikitable"  style="width: 100%;
! 导入工具
! 支持格式
! 描述
|-
| <code>LOAD DATA</code> 语句
| CSV等
|  SQL语句 <code>LOAD DATA</code>用于加载数据文本类型数据。示例：<br \><br \>当需要导入大量本地数据到mysql数据库时，可使用<code>LOAD DATA LOCAL INFILE</code>命令。但</code>local_infile</code>选项默认关闭状态，需要打开。以下以导入5千万行CSV文件为例。
<syntaxhighlight lang="SQL">
-- mysql8.0以上版本，命令行登录数据库时，使用如下命令
-- mysql -u root -p --local-infile

-- 查看'local_infile'选项，并设置为ON
show variables like 'local_infile';
set global local_infile=on;

-- 建test表
CREATE TABLE test( 
  user_id INT, 
  name VARCHAR);

-- 导入本地csv文件，如果第一行有标题使用IGNORE 1 LINES忽略
LOAD DATA LOCAL INFILE 'C:\\Users\\TESTWORK\\Desktop\\test_data.csv'
INTO TABLE test
FIELDS TERMINATED BY ','
IGNORE 1 LINES;
</syntaxhighlight>
|- 
| [https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html mysqlimport] 
| CSV等
| mysqlimport工具是LOAD DATA 语句的命令行接口
|- 
| [https://dev.mysql.com/doc/refman/8.0/en/using-mysqldump.html mysqldump]
| SQL语句
| mysqldump工具用于备份，可以导出和导入sql语句。
|}
=== 导出 ===


=== 常见问题 ===
 使用LOAD DATA INFILE加载CSV文件时，空值（NULL）会自动变为0。
空值和0不同，如在计算均值时，空值不会纳入计算，0值会增加个数。两种方法：
*方法1：导入时使用<code>NULLIF()</code>函数进行预处理
<syntaxhighlight lang="SQL">
-- 示例，对name列的空值进行预处理
-- 也可以全部例进行预处理，但最后一列好像不起作用?

LOAD DATA INFILE '/data/testdata.csv'
INTO TABLE book
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, @name, author) 
SET name = NULLIF(@name,'')
;
</syntaxhighlight>

*方法2：对csv文件进行预处理，空值处插入转义字符<code>\N</code>会自动转为NULL，如某行<code>15, ,10</code>，改为<code>15,\N,10</code>

{{了解更多
|[https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data  stackoverflow：MySQL从CSV数据加载NULL值]
|[https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html MySQL 文档：空值的问题]
|[https://dev.mysql.com/doc/refman/8.0/en/load-data.html MySQL 文档：Load Data 语句]
}}

 LOAD DATA INFILE时报错。 
 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
SHOW VARIABLES LIKE "secure_file_priv"; 
*方法1：查询secure_file_priv值 <code>SHOW VARIABLES LIKE "secure_file_priv";</code> ，如果存在目录，将文件移动到该安全目录即可导入。

*方法2：配置secure_file_priv值，如ubuntu中可以编辑<code>/etc/mysql/my.cnf</code>配置文件，设置<code>secure-file-priv= /data</code>，重启mysql服务即可，docker可以直接重启容器。将文件放入/data目录即可导入。

{{了解更多
|[https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option stackoverflow：MySQL ERROR 1290 (HY000) --secure-file-priv option]
}}

==资源==
===官网===
*[https://www.mysql.com/ MySQL 官网]
*[https://www.mysql.com/cn/ MySQL 中文官网]
*[https://dev.mysql.com/doc/ MySQL 文档]

===书籍===

===教程===
*[https://www.runoob.com/mysql/mysql-tutorial.html 菜鸟教程：MySQL]

===网页===
*[https://zh.wikipedia.org/wiki/MySQL 维基百科：MySQL]

[[分类:数据库]]