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;
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 表的名字
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 |
|
| table | table 字段表示正在访问的表的名称或派生表的别名。 |
| partitions | partitions 字段显示查询匹配的分区信息。如果表是分区表，该字段显示哪些分区被扫描。 |
| type |
|
| possible_keys |
|
| key |
|
| key_len |
|
| ref |
|
| rows |
|
| filters |
|
| Extra |
|
可以对比一下下面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)
本文只发表于博客园和tonglin0325的博客，作者：tonglin0325，转载请注明原文链接：https://www.cnblogs.com/tonglin0325/p/4646396.html
