MySQL

参考资料

廖雪峰：https://www.liaoxuefeng.com/wiki/1177760294764384
MySQL 教程：https://www.runoob.com/mysql/mysql-install.html
视频教程：https://www.bilibili.com/video/BV12h411Q7Yq

快速创建数据库、用户、授权

create database `tcboot_scm` default character set utf8mb4 collate utf8mb4_general_ci;
CREATE USER 'tcboot_scm'@'%' IDENTIFIED BY 'tcboot_scm';
GRANT ALL ON tcboot_scm.* TO 'tcboot_scm'@'%';
GRANT PROCESS ON *.* TO 'tcboot_scm'@'%'; # 支持 mysqldump 导出数据

# 修改密码
alter user 'root'@'' identified by 'xxxx';

#支持root远程访问
use mysql;
update user set host='%' where user='root' AND host='localhost';
FLUSH PRIVILEGES;

安装

https://www.runoob.com/mysql/mysql-install.html

查看《MySQL-WindowsIntaller》

时区处理

DATE 和 TIME 类型不支持时区转换
对于 TIMESTAMP 类型，MySQL 会正确的根据 connection 时区（对于 JDBC 来说就是 JVM 时区）/ 服务端时区做转换，JDBC 程序不需要特别注意什么事情。只要保证 JVM 时区和用户所在时区保持一致即可
不要在服务器端做日期时间的字符串格式化（DATE_FORMAT()），因为返回的结果是服务端的时区，而不是 connection 的时区（对于 JDBC 来说就是 JVM 时区）
CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE() 可以安全的使用，返回的结果会转换成 connection 时区（对于 JDBC 来说就是 JVM 时区）

查询系统时区和session时区

1 2	SELECT @@global.time_zone, @@session.time_zone; SYSTEM SYSTEM

设置session时区

1	set time_zone = '+8:00';

设置全局时区

1	set global time_zone = '+8:00';

验证

select now();
select curtime();
select CURRENT_TIMESTAMP();
select CURRENT_DATE();
select CURRENT_TIME();

时区

1	serverTimezone=Asia/Shanghai

必知

查看当前 mysql 的大小写敏感配置

MySQL8 Windows 版本中默认不区分大小写，Linux 版本中「包含 Docker 中下载版本」默认区分大小写。数据库初始化时 lower_case_table_names 的值和数据库启动时的值需要一样

show global variables like '%lower_case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 0     |
+------------------------+-------+
lower_case_file_system：表示当前系统文件是否大小写敏感，只读参数，无法修改
ON  大小写不敏感 
OFF 大小写敏感 

lower_case_table_names：数据库表是否大小写敏感，0-敏感 1-不敏感
修改为不敏感：
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql --lower_case_table_names=1

MySQL本身实际上只是一个SQL接口，它的内部还包含了多种存储引擎，常用的包括
- InnoDB：支持事务，行级别锁，外键
- MyISAM：MySQL早期集成的默认数据库引擎，不支持事务

安装 MySQL 过程中，创建了默认的 4 个数据库：，创建了 root 账号

| information_schema |
| mysql              |
| performance_schema |
| test							 |

在MySQL 8下,认证加密方式默认变成了default_authentication_plugin=caching_sha2_password，包括你刚初始化的root用户也是这个认证加密方式。除非用新的协议驱动，例如 8 自带的 MySQL 客户端,否则连接不上数据库
MySQL 8.0 以上版本的数据库连接有所不同：

MySQL 8.0 以上版本驱动包版本 mysql-connector-java-8.0.16.jar
- com.mysql.jdbc.Driver 更换为 com.mysql.cj.jdbc.Driver
- MySQL 8.0 以上版本不需要建立 SSL 连接的，需要显示关闭
- allowPublicKeyRetrieval=true 允许客户端从服务器获取公钥
- 最后还需要设置 CST
加载驱动与连接数据库方式如下：
1
2
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","password");

字符集

https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html

查看字符集：SHOW VARIABLES LIKE ‘character%’;

方式一：创建数据库的时候进行配置「推荐」

这种方式比较灵活，也比较保险，它不依赖于默认的字符集 / 字符序。当你创建数据库的时候指定字符集 / 字符序，后续创建 table、column 的时候，如果不特殊指定，会继承对应数据库的字符集 / 字符序。

1	create database `test` default character set utf8mb4 collate utf8mb4_general_ci;

方式二：mysql server 启动的时候进行配置

可以添加以下配置，这样 mysql server 启动的时候，会对 character-set-server、collation-server 进行配置。

当你通过 mysql client 创建 database/table/column，且没有显示声明字符集 / 字符序，那么就会用 character-set-server/collation-server 作为默认的字符集 / 字符序。

另外，client、server 连接时的字符集 / 字符序，还是需要通过 SET NAMES 进行设置。

1
2
3

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

方式三：从源码编译 mysql 的时候，通过编译参数进行设置

编译的时候如果指定了 -DDEFAULT_CHARSET 和 -DDEFAULT_COLLATION，那么：

创建 database、table 时，会将其作为默认的字符集 / 字符序。
client 连接 server 时，会将其作为默认的字符集 / 字符序。（不用单独 SET NAMES）

1 2	shell> cmake . -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci

已有的库和表更改为 utf8mb4

# 更改数据库编码
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 更改表编码
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

命令

查看 MySQL 服务器版本
1
select version();
当前 MySQL 提供什么存储引擎
1
show engines;

当前默认的存储引擎

1	show variables like '%storage_engine%';

查看表用了什么引擎
1
show create table students;
修改表引擎方法
1
alter table students engine=innodb;

最大连接数-查看与修改

查询MySQL的最大连接数

// 支持的最大连接数
SHOW VARIABLES LIKE 'max_connections';

// 当前连接数。表示当前打开的连接数。这个数字包括了所有到MySQL服务器的连接，不仅仅是来自你的应用程序的连接。
SHOW STATUS LIKE 'Threads_connected';

修改MySQL的最大连接数

打开MySQL配置文件my.cnf（在Linux系统中通常位于/etc/mysql/my.cnf，在Windows系统中通常位于C:\ProgramData\MySQL\MySQL Server 8.0/my.ini）。
在[mysqld]部分添加或修改以下行：

1	max_connections = 新的连接数

将新的连接数替换为您希望设置的最大连接数。例如，如果您希望将最大连接数设置为1000，则应将其设置为：

1	max_connections = 1000

保存并关闭配置文件
重启MySQL服务以使更改生效
1. 在Linux系统中，您可以使用以下命令重启MySQL服务：sudo service mysql restart
2. 在Windows系统中，您可以在“服务”管理工具中重新启动MySQL服务

内存占用分析

查看 MySQL 总消耗内存

1
2
3

select * from sys.memory_global_total;

# 不准确，与实际 mysql 进程占用内存相差较大

查看总体内存占用情况

1	select event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024 from performance_schema.memory_summary_global_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc LIMIT 20;

查看线程内存占用情况

1	select thread_id,event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024 from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;

使用触发器备份删除的表和数据

删除表数据

1 2	// 复制到备份表 CREATE TRIGGER '触发器名称' BEFORE DELETE ON '删除的主表名称' FOR EACH ROW BEGIN INSERT INTO '备份表名称' SELECT * FROM '主表名称' where id=old.id; END

数据库

连接数据库

mysql -u 用户名 -p 用户密码 -h IP地址 -P 端口

示例：
mysql -u root -p 123456 -h 192.168.0.99 -P 3306

列出所有数据库
1
SHOW DATABASES;

创建数据库

CREATE DATABASE test;

//如果test数据库不存在，就创建
CREATE DATABASE IF NOT EXISTS test;

删除数据库

1 2	//该数据库的所有表全部被删除 DROP DATABASE test;

切换到数据库

1 2	//操作数据库前先切换到数据库 USE test;

当前使用的数据库

select database();

show tables;
+----------------+
| Tables_in_test |
+----------------+
| students       |
+----------------+

状态信息

status

--------------
mysql  Ver 8.0.15 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          32
Current database:       test
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES128-GCM-SHA256
Using delimiter:        ;
Server version:         8.0.15 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 21 hours 19 min 24 sec

Threads: 2  Questions: 91  Slow queries: 0  Opens: 195  Flush tables: 2  Open tables: 169  Queries per second avg: 0.001
--------------

查看各用户认证密码方式

1	select Host,User,plugin,authentication_string from mysql.user;

判断当前到数据库的连接是否有效
1
SELECT 1;

用户

创建用户

创建 test 用户，只允许本地 localhost 访问

1	CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

创建 demo 用户，只允许指定的 ip 主机访问

1	CREATE USER 'demo'@'192.168.0.109' IDENTIFIED BY '123456';

创建 admin 用户，允许任何ip主机访问「localhost 除外」
1
CREATE USER 'admin'@'%' IDENTIFIED BY '123456';

允许从任何 ip 主机访问

1
2
3

use mysql;
update user set host='%' where user='root' AND host='localhost';
FLUSH PRIVILEGES;

用户授权
- 允许用户 test 拥有数据库 mydb 的所有权限
  1
  GRANT ALL ON mydb.* TO 'test'@'localhost';
- 创建用户并分配适当的权限后，请确保重新加载权限
  1
  FLUSH PRIVILEGES;

撤消用户权限

1 2	REVOKE DELETE ON mydb.* TO 'test'@'localhost'; //撤销 delete 权限 FLUSH PRIVILEGES;

删除用户

1 2	DROP USER 'test'@'localhost'; FLUSH PRIVILEGES;

修改用户密码

1
2
3

从「数据库-8」查看各用户认证密码方式

alter user 'test'@'%' identified by 'test';

更改用户名

mysql -u root -p
use mysql;
update user set user="test1" where user="test";
flush privileges;

增加admin并提升为超级管理员

1 2	grant all on . to 'admin'@'localhost' identified by '123456' with grant option; FLUSH PRIVILEGES;

删除所有用户,包括root超级用户「禁用」
1
delete from mysql.user;

权限

ALL [PRIVILEGES] -授予用户所有权限
CREATE -授予用户创建新数据库和表的权限
DROP -授予用户删除（drop）数据库和表的权限
DELETE -授予用户删除表中的行的权限
ALTER -授予用户修改表结构的权限
INSERT -授予用户在表中插入（add）行的权限
SELECT -授予用户运行select命令以从表中读取数据的权限
UPDATE -授予用户更新表中的数据的权限
EXECUTE -授予用户执行存储的例程的权限
FILE -授予用户访问服务器主机上的文件的权限
GRANT OPTION -授予用户授予或删除其他用户权限的权限
允许对数据库mydb上的‘test‘@’localhost’进行CREATE，DELETE，INSERT，UPDATE访问
1
2
GRANT CREATE,DELETE,INSERT,UPDATE ON mydb.* TO 'test'@'localhost';
FLUSH PRIVILEGES;

表管理

列出当前数据库的所有表
1
SHOW TABLES;

创建表

CREATE TABLE students(
id bigint(20) NOT NULL AUTO_INCREMENT,
class_id bigint(20) NOT NULL, 
name varchar(100) NOT NULL,
gender varchar(1) NOT NULL,
score int(11) NOT NULL,
PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建表是先判断表是否存在

create table if not exists students(id bigint(20) NOT NULL AUTO_INCREMENT,
class_id bigint(20) NOT NULL, 
name varchar(100) NOT NULL,
gender varchar(1) NOT NULL,
score int(11) NOT NULL,
PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查看一个表的结构
1
DESC students;
查看创建表的 SQL 语句
1
SHOW CREATE TABLE students;
修改表名
1
alter table students rename to stu;

删除表

DROP TABLE students;

//存在即删除
DROP TABLE IF EXISTS students;

给students表新增一列birth

1	ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

把birth列名改为birthday，类型改为VARCHAR(20)

1	ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

删除列birthday

1	ALTER TABLE students DROP COLUMN birthday;

表和字段注释

创建带注释的表

CREATE TABLE students( 
  sid INT PRIMARY KEY AUTO_INCREMENT COMMENT '设置主键自增',
  sname VARCHAR(200) COMMENT '列注释'
 ) COMMENT='表注释';

修改现有表，加上注释

1	ALTER TABLE students COMMENT='学生表';

修改现有列，加上注释

1	alter table students modify column sname VARCHAR(200) comment 'xxxx';

查看表注释

1	SELECT table_name,table_comment FROM information_schema.tables WHERE table_schema = 'test' AND table_name ='students';

查看列的注释

1	SELECT column_name, column_comment FROM information_schema.columns WHERE table_schema ='test' AND table_name = 'students';

MySQL 备份和恢复机制

https://juejin.im/entry/5a0aa2026fb9a045132a369f

函数

IFNULL

如果为空，返回指定值

1
2
3

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;

SELECT * FROM A WHERE IFNULL(B1, '')  != 1

group_concat

将指定字段数据通过分隔符拼接
GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep);

1	select group_concat(db_field_name SEPARATOR ',') from onl_cgform_field

FIND_IN_SET

FIND_IN_SET(string, string_list)
返回在集合中的位置
If string is not found in string_list, this function returns 0
If string or string_list is NULL, this function returns NULL
If string_list is an empty string (“”), this function returns 0

SELECT 
    name, belts
FROM
    divisions
WHERE
    FIND_IN_SET('red', belts);
//更多请阅读：https://www.yiibai.com/mysql/find_in_set.html

NOT FIND_IN_SET

SELECT 
    name, belts
FROM
    divisions
WHERE
    NOT FIND_IN_SET('black', belts);

//更多请阅读：https://www.yiibai.com/mysql/find_in_set.html

LEFT

LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分

下面是LEFT()函数的语法 -

1	LEFT(str,length);

LEFT()函数接受两个参数：

str是要提取子字符串的字符串。
length是一个正整数，指定将从左边返回的字符数。

LEFT()函数返回str字符串中最左边的长度字符。如果str或length参数为NULL，则返回NULL值。

如果length为0或为负，则LEFT函数返回一个空字符串。如果length大于str字符串的长度，则LEFT函数返回整个str字符串。

请注意，SUBSTRING(或SUBSTR)函数也提供与LEFT函数相同的功能。

1
2
3

SELECT LEFT('MySQL LEFT', 5); // MySQL

select LEFT(id, 5) from act_z_apply

RIGHT

RIGHT(string, number_of_chars)
与 LEFT类似，但提取字符串或字段右边指定长度的内容

1	SELECT RIGHT("SQL Tutorial is cool", 4) AS ExtractString;

UPPER

转大写
相同作用的函数还有 UCASE

1	SELECT UPPER("SQL Tutorial is FUN!");

LOWER

转小写
相同作用的函数还有 LCASE

1	SELECT LOWER("SQL Tutorial is FUN!");

TRIM

移除开头和结尾的空格字符
LTRIM 移除开头的空格字符
RTRIM 移除结尾的空格字符

1	SELECT TRIM(' SQL Tutorial ') AS TrimmedString; //SQL Tutorial

SUBSTR

SUBSTR(string, start_position, length)
与 SUBSTRING 、MID 一样

1	SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString;

SUBSTRING_INDEX

返回指定分隔符第N次出现位置前的字符串

1 2	SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 1); // www SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 2); // www.w3schools

REVERSE

反转字符串

REPLACE

REPLACE(string, from_string, new_string)

1	SELECT REPLACE("XYZ FGH XYZ", "X", "m"); // mYZ FGH mYZ

CONCAT

连接
CONCAT(expression1, expression2, expression3,…)

1	SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address FROM Customers;

CONCAT_WS

通过分隔符连接
CONCAT_WS(separator, expression1, expression2, expression3,…)

1	SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address FROM Customers;

STRCMP

比较2个字符串
If string1 = string2, this function returns 0
If string1 < string2, this function returns -1
If string1 > string2, this function returns 1

1	SELECT STRCMP("SQL Tutorial", "HTML Tutorial");

SPACE

返回指定数量空格的字符串
SPACE(number)

RPAD

使用另一个字符串向右填充字符串，直到达到一定长度
RPAD(string, length, rpad_string)

1	SELECT RPAD("SQL Tutorial", 20, "ABC"); // SQL TutorialABCABCAB

REPEAT

重复指定次数
REPEAT(string, number)

1	SELECT REPEAT("SQL Tutorial", 3);

INSTR

返回字符串首次出现的位置，从 1 开始
不存在，返回0

1	SELECT INSTR("W3Schools.com", "3") AS MatchPosition; // 1

POSITION

返回指定字符在字符串中位置，从 1 开始
POSITION(substring IN string)
与 LOCATE 功能一样：

LOCATE(substring, string, start)

The starting position for the search. Position 1 is default

1
2
3

SELECT POSITION("a" IN CustomerName) FROM Customers;

SELECT LOCATE("com", "W3Schools.com", 3) AS MatchPosition; // 11

LENGTH

返回字符串长度

1	SELECT LENGTH("SQL Tutorial") AS LengthOfString;

INSERT

指定位置和长度替换为指定字符串
INSERT(string, position, number, string2)
If position is outside the length of string, this function returns string
If number is higher than the length of the rest of the string, this function replaces string from position until the end of string

1	SELECT INSERT("W3Schools.com", 1, 9, "Example"); // Example.com

表查询

条件

多个条件中有 not、and、or 等，则尽量使用小括号 ()

1	SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M'

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级

投影

使用SELECT *查询表的所有列，使用SELECT 列1, 列2, 列3返回指定列，这种操作称为投影
SELECT语句可以对结果集的列进行重命名

排序

使用ORDER BY可以对结果集进行排序
默认的排序规则是ASC：“升序”，即从小到大
可以对多列进行升序、倒序排序

将null强制放在最前（默认就是这样）

1	SELECT * FROM BGM_LIST ORDER BY if(isnull(SORT),0,1),SORT ASC //null, null, 1,2,3,4

将null强制放在最后

1
2
3

SELECT * FROM BGM_LIST ORDER BY if(isnull(SORT),1,0),SORT ASC //1,2,3,4,null,null

SELECT * FROM BGM_LIST ORDER BY if(isnull(SORT),1,0),SORT DESC // 4,3,2,1,null,null

分页

通过LIMIT <M> OFFSET <N>实现

1	SELECT * FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;

LIMIT和OFFSET应该设定的值
1. LIMIT总是设定为 pageSize
2. OFFSET计算公式为pageSize * (pageIndex - 1)
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0
LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15

聚合

函数	说明
COUNT	计算结果集行数
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

COUNT(*)
1
SELECT COUNT(*) num FROM students;
如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

分组

聚合查询返回列中，只能放入分组的列

1	SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

使用多个列进行分组

1	SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

非分组列拼接后返回

1	SELECT class_id, group_concat(name), COUNT(*) num FROM students GROUP BY class_id;

分组排序

根据 create_by 分组，create_time、id 字段倒排序，取各分组第一条数据

SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段) AS rn
FROM 表名
) t
WHERE t.rn = 1;

多表

查询多张表的语法是：SELECT * FROM <表1>, <表2>
1. 使用多表查询可以获取M x N行记录
2. 多表查询的结果集可能非常巨大，要小心使用
1
SELECT * FROM students, classes;

连接

INNER JOIN「交集」

只返回同时存在于两张表的行数据
LEFT OUTER JOIN

以左表为基准，匹配返回，右表匹配不到的字段返回null
RIGHT OUTER JOIN

以右表为基准，匹配返回，左表匹配不到的字段返回null
FULL OUTER JOIN「并集」

把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

CRUD

INSERT

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

//多条
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...),(值1, 值2, ...);

UPDATE
- 在执行UPDATE语句前，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新
  1
  2
  3
  SELECT * FROM students WHERE id=1;
  
  UPDATE students SET name='大牛', score=66 WHERE id=1;
- UPDATE语句会返回更新的行数以及WHERE条件匹配的行数，匹配不到时，返回0
DELETE
- 语法
  1
  DELETE FROM <表名> WHERE ...;
- 如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除
- 在执行delete语句前，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用delete删除

索引

基础

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义
可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢
对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一
索引对于用户和应用程序来说都是透明的

操作

对score列创建索引

1	ALTER TABLE students ADD INDEX idx_score (score);

对name、score 创建复合索引

1	ALTER TABLE students ADD INDEX idx_name_score (name, score);

唯一索引
- 通过创建唯一索引，可以保证某一列的值具有唯一性
  1
  ALTER TABLE students ADD UNIQUE INDEX uni_name (name);

对某一列添加一个唯一约束而不创建唯一索引

1	ALTER TABLE students ADD CONSTRAINT uni_name UNIQUE (name);

反向模糊查询

平时开发中，常用一些模糊查询，例如查询表格user中uname包含“塔”的数据，可以使用如下三种方式：

select * from user where uname like '%塔%';

select * from user where INSTR(uname,'塔');

select * from user where LOCATE('塔', uname);


但是如果有一个需求，我要查询user表格中uname值包含在“北境史塔克家族”中的数据，尝试了下，直接反过来：

select * from user where '北境史塔克家族' like CONCAT('%',uname,'%');

select * from user where INSTR('北境史塔克家族', uname);

select * from user where LOCATE(uname,'北境史塔克家族');

实用SQL语句

插入或替换

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录

不存在则插入，存在则先删除后插入
1
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
插入或更新

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定

不存在则插入，存在则更新指定字段数据「非指定字段数据不变」
1
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
插入或忽略

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

即不存在则插入，存在则不执行任何操作
1
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

表快照

新创建的表结构和SELECT使用的表结构完全一致

1	CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

查询结果集写入表中

确保INSERT语句的列和SELECT语句的列能对应，数量和类型一致
1
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
查询所有库

mysql 查询所有表中字段类型为指定类型的表、字段

1
2
3

SELECT TABLE_SCHEMA AS '库名', TABLE_NAME AS '表名', COLUMN_NAME AS '字段名', DATA_TYPE AS '字段类型'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE='geometry';

事务

数据库事务具有ACID这4个特性
- A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
- C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
- I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
- D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。
对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务
多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务
1
2
3
4
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

事务隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题

SQL标准定义了4种事件之间的隔离级别，分别对应可能出现的数据不一致的情况

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）
在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据并且提交，那么，在第一个事务中，两次读取的数据就可能不一致
在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了
Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read

修改服务端口

找到MYSQL安装目录的my.ini
打开my.ini文件，修改port端口。直接Ctrl+F，搜索port，如图所示 3306为mysql默认端口号，将它修改为你想要的端口号，注意两个地方都要修改；修改完成后保存关闭
重启mysql服务

客户端

MySQL WorkBench
DataGrip
Navicat Premium
Navicat for MySQL

MySQL WorkBench

图形化操作，导出导入格式支持 SQL

关键字！！！

MySQL关键字（保留字）列表 - jack_ou - 博客园 (cnblogs.com)

mysql关键字有以下这些，表名、字段名不能使用关键字

ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	ASENSITIVE	BEFORE
BETWEEN	BIGINT	BINARY
BLOB	BOTH	BY
CALL	CASCADE	CASE
CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN
CONDITION	CONNECTION	CONSTRAINT
CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
DATABASE	DATABASES	DAY_HOUR
DAY_MICROSECOND	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DETERMINISTIC
DISTINCT	DISTINCTROW	DIV
DOUBLE	DROP	DUAL
EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS
EXIT	EXPLAIN	FALSE
FETCH	FLOAT	FLOAT4
FLOAT8	FOR	FORCE
FOREIGN	FROM	FULLTEXT
GOTO	GRANT	GROUP
HAVING	HIGH_PRIORITY	HOUR_MICROSECOND
HOUR_MINUTE	HOUR_SECOND	IF
IGNORE	IN	INDEX
INFILE	INNER	INOUT
INSENSITIVE	INSERT	INT
INT1	INT2	INT3
INT4	INT8	INTEGER
INTERVAL	INTO	IS
ITERATE	JOIN	KEY
KEYS	KILL	LABEL
LEADING	LEAVE	LEFT
LIKE	LIMIT	LINEAR
LINES	LOAD	LOCALTIME
LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP
LOW_PRIORITY	MATCH	MEDIUMBLOB
MEDIUMINT	MEDIUMTEXT	MIDDLEINT
MINUTE_MICROSECOND	MINUTE_SECOND	MOD
MODIFIES	NATURAL	NOT
NO_WRITE_TO_BINLOG	NULL	NUMERIC
ON	OPTIMIZE	OPTION
OPTIONALLY	OR	ORDER
OUT	OUTER	OUTFILE
PRECISION	PRIMARY	PROCEDURE
PURGE	RAID0	RANGE
READ	READS	REAL
REFERENCES	REGEXP	RELEASE
RENAME	REPEAT	REPLACE
REQUIRE	RESTRICT	RETURN
REVOKE	RIGHT	RLIKE
SCHEMA	SCHEMAS	SECOND_MICROSECOND
SELECT	SENSITIVE	SEPARATOR
SET	SHOW	SMALLINT
SPATIAL	SPECIFIC	SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING
SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN
TABLE	TERMINATED	THEN
TINYBLOB	TINYINT	TINYTEXT
TO	TRAILING	TRIGGER
TRUE	UNDO	UNION
UNIQUE	UNLOCK	UNSIGNED
UPDATE	USAGE	USE
USING	UTC_DATE	UTC_TIME
UTC_TIMESTAMP	VALUES	VARBINARY
VARCHAR	VARCHARACTER	VARYING
WHEN	WHERE	WHILE
WITH	WRITE	X509
XOR	YEAR_MONTH	ZEROFILL

Mac

// 查看包信息，比如目前的版本，依赖，安装后注意事项等
brew info mysql

// 搜索包
brew search mysql

// 安装包
brew install mysql

// 初始化配置
mysql_secure_installation

// my.cnf位置为：/usr/local/etc/my.cnf

// 启动
sudo mysql.server start
// 关闭
sudo mysql.server stop
// 重启
sudo mysql.server restart
// 启动并设置开机启动
brew services start mysql

// 连接
mysql -u root -p

其它

https://zhuanlan.zhihu.com/p/27960044

下载、安装
启动、停止

连接
1
2
mysql -u root -p
输入 123456

问题

mysql too many connections 解决方法

# 修改最大连接数，默认最大连接数是 100
show variables like '%max_connections%'; # 查看最大连接数
show status like 'Max_used_connections'; # 同一时刻并行连接数的最大值

# 设置最大连接数为 1000
set global max_connections = 1000; # 服务器重启后失效
# 修改 /etc/my.cnf.添加 max_connections = 1000 永久有效。重启后生效

# sleep 连接最大存活时间，默认是 28800 s ,8 小时，连接不会被清理
show global variables like '%wait_timeout'; 

# navicat 的超时请求机制配置了 240s，这里设置为 250s
set global wait_timeout=250; 【重点】

对实体 “useSSL” 的引用必须以 ‘;’ 分隔符结尾

& 替换为 &amp;

<bean id="dataSource_mid"
		  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=utf8&amp;useSSL=true" />
		<property name="username" value="root" />
		<property name="password" value="123456" />
</bean>

配置 mysql 允许远程连接的方法

修改 /etc/mysql/my.conf「Linux」或 my.ini「Windows」

把
bind-address = 127.0.0.1
改成
bind-address = 0.0.0.0
重启 MySQL 服务

// 注释使用 # 开头

为需要远程登录的用户赋予权限

grant all on *.* to 'root'@'%' identified by '890okm*()OKM' with grant option;
flush privileges;

允许任何 ip 地址 (% 表示允许任何 ip 地址) 的电脑用 admin 帐户和密码 (123456) 来访问这个 mysql server。
注意 admin 账户不一定要存在

支持 root 用户允许远程连接 mysql 数据库

方法一「优选」

1
2
3

use mysql;
update user set host='%' where user='root' AND host='localhost';
FLUSH PRIVILEGES;

方法二

1 2	grant all privileges on . to 'root'@'%' [identified by '123456'] with grant option; flush privileges;

查看系统用户

1 2	use mysql; select user, host from user;

DataGrip 连接 mysql 报错 Server returns invalid timezone. Go to ‘Advanced’ tab and set’serverTimezone’ property

1	配置界面-Advanced 选项中搜索到 Name 为 serverTimezone 其 Value设置为 Asia/Shanghai

IDEA 连接错误com.mysql.jdbc. exceptions.jdbc4.MySOLNonTransientConnectionException: Could not create connection to database server.

1	MySQL 版本使用新版本，例如 8.0.9-rc

2059- Authentication plugin ‘caching_sha2 password’ cannot be loaded:

1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

注意：执行完后，如果还有问题，则尝试重启服务器

方法一「推荐」

1
2
3

ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; #修改指定用户的加密规则 
ALTER USER 'test'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; #更新一下用户的密码且永不过期
FLUSH PRIVILEGES; #刷新权限

方法二「不建议」

1
2
3

修改 my.ini 配置文件
[mysqld]
default_authentication_plugin=mysql_native_password

check the manual that corresponds to your MySQL server version for the right syntax to use near ‘order FROM

答案：表名或者字段名是不是使用了Mysql的关键字，比如 join、on、order、from