MySQL学习笔记——基本语法

SQL——结构化查询语言（Structured Query Language）

1. 字符集和大小写

SQL语言不区分大小写，建议关键字用大写，但是字符串常量区分大小写

字符集

character_set_client：服务器将系统变量character_set_client作为客户端发送语句时使用的字符集。
character_set_connection：用于没有字符集介绍器指定的字面量和用于数字到字符串转换的字符集。
character_set_database：默认数据库使用的字符集。每当默认数据库更改时，服务器都会设置此变量。
character_set_filesystem：文件系统字符集。
character_set_results：用于向客户端返回查询结果的字符集。这包括结果数据(如列值)、结果元数据(如列名)和错误消息。
character_set_server：服务器的默认字符集。如果你设置了这个变量，你还应该设置collation_server来指定字符集的排序规则。
character_set_system：服务器用于存储标识符的字符集。
character_sets_dir：安装字符集的目录。

MySQL5.7的默认字符集character和排序字符集collation

mysql> show variables like '%character%';
+--------------------------+--------------------------------------------------------------+
| Variable_name            | Value                                                        |
+--------------------------+--------------------------------------------------------------+
| character_set_client     | latin1                                                       |
| character_set_connection | latin1                                                       |
| character_set_database   | latin1                                                       |
| character_set_filesystem | binary                                                       |
| character_set_results    | latin1                                                       |
| character_set_server     | latin1                                                       |
| character_set_system     | utf8                                                         |
| character_sets_dir       | /opt/rh/rh-mysql57/root/usr/share/rh-mysql57-mysql/charsets/ |
+--------------------------+--------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

MySQL8.0的默认字符集

mysql> show variables like '%character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | latin1_swedish_ci  |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

查看mysql table字段的字符集

MySQL5.7

mysql> create table user
(
    id bigint unsigned auto_increment comment '主键' primary key,
    username varchar(128) not null comment '用户名',
    email varchar(128) not null comment '邮箱'
)
comment '用户表' charset=utf8mb4;

mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'default' and TABLE_NAME = 'user';
+-------------+--------------------+--------------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+-------------+--------------------+--------------------+
| id          | NULL               | NULL               |
| username    | utf8mb4            | utf8mb4_general_ci |
| email       | utf8mb4            | utf8mb4_general_ci |
+-------------+--------------------+--------------------+
3 rows in set (0.00 sec)

或者

mysql > CREATE TABLE IF NOT EXISTS `t_user` (`username` varchar(64) NOT NULL,`password` varchar(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql > SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'default' and TABLE_NAME = 't_user';
+-------------+--------------------+-----------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME  |
+-------------+--------------------+-----------------+
| username    | utf8               | utf8_general_ci |
| password    | utf8               | utf8_general_ci |
+-------------+--------------------+-----------------+
2 rows in set (0.00 sec)

MySQL8.0

mysql> CREATE TABLE IF NOT EXISTS `t_user123` (`username` varchar(64) NOT NULL,`password` varchar(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 't_user123';
+-------------+--------------------+-----------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME  |
+-------------+--------------------+-----------------+
| password    | utf8mb3            | utf8_general_ci |
| username    | utf8mb3            | utf8_general_ci |
+-------------+--------------------+-----------------+

utf8（别名utf8mb3）utf8 是 MySQL 早期版本使用的字符集，它实际上是 UTF-8 的一种实现，只支持最多 3 字节来存储一个字符。utf8 是 utf8mb3 的别名。从 MySQL 8.0.28 版本开始，utf8 已经被明确标识为 utf8mb3，但在此之前，它们是同义的。utf8适用于大多数情况，但不能用于需要存储 4 字节 UTF-8 字符的场景（如表情符号）。

utf8mb4 是 MySQL 完整实现的 UTF-8 字符集，支持最多 4 字节来存储一个字符。支持所有 Unicode 字符，包括所有表情符号和扩展字符。

utf8mb4_general_ci 的 ci 表示 case-insensitive，即不区分大小写。

utf8mb4_bin 的 bin 表示 binary，这种排序规则是区分大小写的。

将字段类型修改成可区别大小写

CREATE TABLE your_table_name (column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

在创建表的时候指定可区别大小写

CREATE TABLE user (
    id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    username VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
    email VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '邮箱'
) 
COMMENT '用户表' CHARSET=utf8mb4;

如果想区分case insensitive的字段，也可以使用binary函数将其转换成大小写敏感的，如下

select * from user where binary username = 'mlinnockax'

2. Database

1.列出MySQL服务器主机上的数据库

SHOW DATABASES[LIKE wild];

2. 用给定的名字创建一个数据库

语法：CREATE DATABASE[IF NO EXISTS] 数据库名字　　创建之后要刷新才能在列表中看见新建立的数据库

create database spring_user default character set utf8 collate utf8_general_ci;

3. 删除数据库中的所有表和数据

DROP DATABASE[IF NO EXISTS] 数据库名字

4. 指定数据库

把指定数据库作为默认（当前）数据库使用，用于后续语句

USE 数据库名字 

5. 数据库对象的命名规则

1.必须以字母开头
2.可包括数字和三个特殊字符（# _ $）　
3.不要使用MySQL的保留字
4.同一个schema下的对象不能同名

3. Table

1. 建表语句

CREATE TABLE [schema] 表的名字 (column datatype[DEFAULT expr],...) ENGINE = 存储机制

数据表的每行称为一条记录(record)，每一列称为一个字段(field)，主键列：唯一能够识别每条记录的列 

CREATE TABLE IF NOT EXISTS `t_user` (`username` varchar(64) NOT NULL,`password` varchar(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

或者

CREATE TABLE 表的名字（
　　　　　　　　列名 列类型，
　　　　　　　　列名 列类型
　　　　）；

CREATE TABLE tb_dept( #创建一个部门表叫做tb_dept
　id INT PRIMARY KEY AUTO INCREMENT, #部门编号id,整型，primary key 主键
　NAME VARCHAR(18), #部门名称 name,字符，最多只能储存18个字符
　description VARCHAR(100) #描述description,字符，最多只能存储100个字符
);

2. MySQL支持的列类型

i>数值类型
ii>日期/时间类型
iii>字符串（字符）类型　
整数：int或者integer
浮点：double
字符：char、varchar、blob、text

3. 显示当前数据库中已有的数据表的信息

SHOW TABLES [FROM 数据库名字][LIKE wild]

4. 查看数据表中各列的信息

description或者DESC 表名[列名]

mysql> desc t_user;

5. 用ALTER TABLE语句修改表的结构

修改列类型　　ALTER TABLE 表名 MODIFY 列名 列类型　　注意：不是任何情况都可以修改，例如名字是char不能改成int

增加列　　ALTER TABLE 表名 ADD 列名 列类型　　　　注意：使用ADD子句增加字段，新的字段只能被加到整个表的最后

ALTER TABLE employees ADD gender CHAR(1);

删除列　　ALTER TABLE 表名 DROP 列名 列类型

ALTER TABLE employees DROP gender CHAR(1);　　#mysql特有

ALTER TABLE employees DROP COLUMN gender CHAR(1);　#oracle

列改名　　ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型

更改表名　　ALTER TABLE 表名 RENAME 新表名

RENAME TABLE 表名 TO 新表名

6. 删除表

DROP TABLE 表的名字

7. 不存在插入，存在时更新

可以使用replace into语法或者on duplicate key update语法来实现

replace into语法

replace into xx_table (f1, f2, f3) values(v1, v2, v3),(v4, v5, v6);

on duplicate key update语法

insert into xx_table (id, f1, f2) values(1, 'test', 123) on duplicate key update f1 = 'test1', f2 = 456;

区别：当主键或者唯一索引不重复的时候，两者都是直接insert；当主键或者唯一索引重复的时候，replace into会先删除数据再insert，on duplicate key update是执行update语句

4. 视图

1.创建视图

视图的好处：可以限制对数据的访问、可以是复杂的查询变得简单、提供了数据的独立性、提供了对相同数据的不同显式

# 创建视图
CREATE VIEW emo_v_10 AS
SELECT NAME AS '名字',sex '性别',age '年龄'
FROM tb_emp
WHERE dept_id=2;

# 使用视图
SELECT * FROM emo_v_10

5. 其他

1. SQL注释

/**/多行注释
--单行注释
MySQL注释：#

2.查看MySQL版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

3.查看当前连接MySQL的客户端数量

mysql> show processlist;
+----+-----------------+------------------+------+---------+--------+------------------------+------------------+
| Id | User            | Host             | db   | Command | Time   | State                  | Info             |
+----+-----------------+------------------+------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost        | NULL | Daemon  | 292527 | Waiting on empty queue | NULL             |
| 22 | root            | localhost        | NULL | Query   |      0 | init                   | show processlist |
| 23 | root            | 172.17.0.1:60036 | test | Sleep   |     14 |                        | NULL             |
+----+-----------------+------------------+------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)

Command中的Daemon表示这是一个守护进程，Sleep表示当前连接处于空闲状态，Query表示当前连接正在执行一个查询

连接默认的最大空闲时间由 wait_timeout 参数控制，默认为8小时（28800秒）

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

可以使用kill命令手动杀死这个连接

mysql> kill connection +23;
Query OK, 0 rows affected (0.00 sec)

一个连接被服务端主动断开的时候，客户端下次连接的时候会先抛出下面的错误，然后再次尝试连接

ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    23
Current database: test

MySQL的最大连接数量由 max_connections 参数控制

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

4. 修改MySQL的密码

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

5. MySQL怎么取消错误的命令

1.没办法的办法，ctrl+c，完全退出
2.可以输入\c 废弃本次语句，若输入后没效果是因为未保持当前输入语句完整匹配

6. 清空表并将自增id归1

TRUNCATE TABLE baike_pages;

7. 使用timestamp，并在修改的时候自动更新

将默认值设置为CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，并为NOT NULL

8. MySQL数据导出，使用管道命令

mysql -uroot -pXXXX -e"select * from music.tencent_music" > /home/mi/下载/dump

也可以使用mysqldump

mysqldump -hlocalhost -uroot -p xxx_table > ./xxx_table_2020-04-20.bak

9.MySQL导入大量测试数据

生成测试数据可以使用如下网站：https://mockaroo.com/，生成后保存成csv文件

然后使用MySQL的load命令将数据导入到MySQL表中

mysql> LOAD DATA INFILE '/var/opt/rh/rh-mysql57/lib/mysql-files/MOCK_DATA.csv'
    -> INTO TABLE `default`.user
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

如果遇到 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 的报错，可以使用如下命令查看允许导入数据的目录

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| secure_file_priv | /var/opt/rh/rh-mysql57/lib/mysql-files/ |
+------------------+-----------------------------------------+
1 row in set (0.01 sec)

10.MySQL查看表数据和索引所占的存储大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
    concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
    from information_schema.tables where
    table_schema='xx_db'
    and table_name = 'xx_table';

结果

11.使用explain查看SQL的执行计划

查看查询SQL的执行计划，其中username字段添加了索引

explain select * from user where username = 'test';

如果数据量很少的话，其执行计划如下

mysql> explain select * from user where username = 'test';
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | user_username_index | NULL | NULL    | NULL |   18 |    88.89 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

添加了1000条随机数据后，其执行计划如下

mysql> explain select * from user where username = 'test';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | user_username_index | user_username_index | 514     | const |   16 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

explain输出信息的含义，参考：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

字段	含义
id	id 字段是查询中每个 SELECT 语句的标识符。在一个查询中，每个 SELECT 语句会有一个唯一的 id。
select_type	select_type 字段显示 SELECT 语句的类型。 SIMPLE：简单查询，不包含子查询或联合查询。 PRIMARY：最外层的 SELECT 查询。 UNION：联合查询中的第二个或后续 SELECT。 DEPENDENT UNION：依赖于外部查询的 UNION。 UNION RESULT：UNION 的结果。 SUBQUERY：子查询。 DEPENDENT SUBQUERY：依赖于外部查询的子查询。 DERIVED：派生表（子查询中使用的临时表）。
table	table 字段表示正在访问的表的名称或派生表的别名。
partitions	partitions 字段显示查询匹配的分区信息。如果表是分区表，该字段显示哪些分区被扫描。
type	type 字段表示 MySQL 选择的查询类型或访问类型，指示表的访问方式。常见值（按效率排序）： system：表只有一行（等于 const 表）。 const：表最多只有一个匹配行（常量索引）。 eq_ref：对于每个来自前一个表的行组合，最多有一个匹配行。 ref：对于每个来自前一个表的行组合，有可能有多个匹配行。 fulltext：使用全文索引。 ref_or_null：与 ref 类似，但会查找带有 NULL 的行。 index_merge：使用索引合并优化方法。 unique_subquery：用于优化在 IN 子查询中使用的唯一索引。 index_subquery：用于优化 IN 子查询，而不使用唯一索引。 range：仅检索给定范围的行，使用一个索引来选择行。常见于 '<', '<=', '>', '>=', 'between' 等操作符已经like 'xx'或者like 'xx%'的模式匹配查询。 index：全表扫描，但是扫描的只是索引，比如覆盖索引或order by排序查询。 ALL：全表扫描。
possible_keys	possible_keys 字段显示 MySQL 考虑在查询中使用的索引。显示优化器在查询分析阶段的可能索引。可以帮助判断是否存在未被使用的索引。
key	key 字段表示 MySQL 实际使用的索引名称。显示查询优化器选择的实际索引。若为空，表示未使用索引。
key_len	key_len 字段表示 MySQL 使用的索引的字节长度。指示优化器在查询过程中使用的索引部分的长度（单位为字节）。有助于理解索引的使用范围。
ref	ref 字段显示使用的列或常量，来与索引列进行比较。指示查询中与索引列进行比较的列或常量。显示的是匹配条件，如 const，表示索引列与常量比较。
rows	rows 字段是 MySQL 估计的要读取和检查的行数。提供有关查询性能的估计值，显示需要访问的行数来执行查询。
filters	filtered 字段显示查询条件过滤掉的行的百分比。较高的过滤率（接近 100%）表示大多数行都匹配了 WHERE 条件，低过滤率可能表明查询性能不佳。
Extra	Extra 字段提供了查询执行过程中的其他详细信息。常见值： Using index：查询中使用了覆盖索引（只从索引树中读取数据）。 Using where：查询过程中使用了 WHERE 子句来过滤行。 Using temporary：MySQL 需要使用临时表来存储结果。 Using filesort：MySQL 需要额外的排序操作，而不是从索引中读取数据。

可以对比一下下面2个SQL中where和having的效率

mysql> explain select username, count(1) as cnt from `default`.user group by username having cnt > 1;
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | user_username_index | user_username_index | 514     | NULL | 1018 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from (select username, count(1) as cnt from `default`.user group by username) t1 where t1.cnt > 1;
+----+-------------+------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL                | NULL                | NULL    | NULL | 1018 |    33.33 | Using where |
|  2 | DERIVED     | user       | NULL       | index | user_username_index | user_username_index | 514     | NULL | 1018 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

可以对比一下下面2个SQL中给username字段建立索引和email字段没有索引的效率

mysql> explain select username as cnt from `default`.user where username = 'test';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | user_username_index | user_username_index | 514     | const |   16 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select email as cnt from `default`.user where email = 'test@test';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1018 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select email as cnt from `default`.user where username = 'test' and email = 'test@test';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | user_username_index | user_username_index | 514     | const |   16 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

12.count性能排序

COUNT(*) ≈ COUNT(1) > COUNT(主键字段) > COUNT(非主键字段)

参考：https://xiaolincoding.com/mysql/index/count.html