SQL基础 – Tech 学习之路

一、概述

1. 数据库分类

RDBMS
NoSQL
NewSQL

数据库产品世界排名

2.SQL分类

SQL：结构化查询语言，是RDBMS中通用的语言；

DDL-数据定义语言
DCL-数据控制语言
DML-数据操作语言
DQL-数据查询语言

二、DDL应用

1. 数据库

增(创建库)

CREATE DATABASE 库名 CHARSET 字符集 COLLATION 排序规则; #创建数据库并指定字符集
CREATE SCHEMA 库名 CHARSET 字符集 COLLATION 排序规则; #创建数据库并指定字符集

DATABASE==SCHEMA;
字符集建议设为：utf8mb4;
COLLATION参数，无特殊需求时可以不设置

mysql> CREATE DATABASE Linux CHARSET utf8mb4 ;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

删(危险操作)

DROP DATABASE 数据库名; #删除指定数据库

mysql> DROP DATABASE Linux ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
5 rows in set (0.00 sec)

改

ALTER DATABASE 数据库名 CHARSET 字符集; #更改数据库字符集

mysql> ALTER DATABASE Linux CHARSET utf8mb4;
Query OK, 1 row affected (0.00 sec)

查

SHOW CREATE DATABASE 数据库名; #查看数据库属性
SHOW DATABASES; #查看所有数据库信息

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql> SHOW CREATE DATABASE Linux;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| Linux    | CREATE DATABASE `Linux` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

2.表

[表的构成]
列：字段
行：元组(记录)

[表的属性]

字符集(默认继承所属库的字符集)
存储引擎
列的属性

数据类型

常见数据类型
数字类型：int(整数，32位) tinyint(整数，8位)
字符串类型：char(字符串长度)-定长字符串最大长度255 varchar(字符串长度)-变长字符串长度65535 enum(字符串长度) enum(字符串1,字符串2,字符串3,......)

enum不能用作数字列的枚举类型；

时间类型
DATATIME-普通时间类型

时间范围：1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP-UNIX时间戳类型(会受到时区的影响)

时间范围：1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。

二进制类型
JSON类型

数据类型一览表

类	类型	说明
数值型
整数	TINYINT	极小整数型数据类型(0-255)
整数	SMALLINT	较小整数数据类型(-2^15 ~ 2^15-1)
整数	MEDIUMINT	中型整数数据类型
整数	INT	常规(平均)整数数据类型(-2^31 ~ 2^31-1)
整数	BIGINT	大整数数据类型(-2^63 ~ 2^63-1)
浮点数	FLOAT	小型单精度浮点数(四个字节)
浮点数	DOUBLE	常规双精度浮点数(八个字节)
浮点数	DECIMAL	包含整数部分、小数部分或同时包括二者的精确值数值
BIT	BIT	位字段值
字符型
文本	CHAR	定长字符串，最多255个字符
文本	VARCHAR	变长字符串，最多65,535个字符
文本	TINYTEXT	变长字符串，最多255个字符
文本	TEXT	变长字符串，最多65,535个字符
文本	MEDIUMTEXT	变长字符串，最多16,777,215个字符
文本	LONGTEXT	变长字符串，最多4,294,967,295个字符
整数	ENUM	由一组固定的合法值组成的枚举
整数	SET	由一组固定的合法值组成的枚集
时间型
时间	DATE	YYYY-MM-DD
时间	TIME	hh:mm:ss[.uuuuuu]
时间	DATETIME	YYYY-MM-DD hh:mm:ss[.uuuuuu]
时间	TIMESTAMP	YYYY-MM-DD hh:mm:ss[.uuuuuu]
时间	YEAR	YYYY
二进制类型
二进制	BINARY	类似于CHAR(定长)类型，但存储的是二进制字节字符串，而不是非二进制字符串
二进制	VARBINARY	类似于VARCHAR(变长)类型，但存储的是二进制字节字符串，而不是非二进制字符串
BLOB	TINYBLOB	最大长度为255个字节的BLOB列
BLOB	BLOB	最大长度为65,535个字节的BLOB列
BLOB	MEDIUMBLOB	最大长度为16,777,215个字节的BLOB列
BLOB	LONGBLOB	最大长度为4,294,967,295个字节的BLOB列

约束

PRIMARY KEY #主键(非空且唯一,每张表只能有一个)
NOT NULL #非空约束
UNIQUE KEY #唯一约束

其他属性

unsigned #非负数(对于数字列)
default #设置默认值
auto_increment #数值自增长
comment #注释(表,列)

字符集：utf8mb4(推荐)
存储引擎：InnoDB(推荐)

MySQL数据库建议使用InnoDB(MySQL 5.5以后默认使用InnoDB存储引擎)
数据库存储引擎的概念类似于操作系统中文件系统的概念；

增

CREATE TABLE 表名 (
列名1 该列数据类型约束条件其他属性,
列名2 该列数据类型约束条件其他属性
)ENGINE=存储引擎 CHARSET=字符集 COMMENT '备注名'; #创建一张表

mysql> USE world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE student (
    -> id INT PRIMARY KEY AUTO_INCREMENT COMMENT'学号',
    -> name varchar(100) NOT NULL COMMENT'姓名',
    -> age TINYINT UNSIGNED DEFAULT 99 NOT NULL COMMENT'年龄',
    -> gender ENUM('M','F','U') DEFAULT 'U' NOT NULL COMMENT '性别',
    -> time TIMESTAMP DEFAULT NOW() NOT NULL COMMENT '入学时间'
    -> )ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
Query OK, 0 rows affected (0.14 sec)  

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| student         |
+-----------------+
4 rows in set (0.00 sec)

建表开发规范
1. 表名必须由小写字组组成，不能以数字开头，必须与业务有关；
2. 每个表必须要存在主键列，可以定制无关列为主键列；
3. 每个列必须必须NOT NULL约束，可以定义默认值(数字列用0填充，字符串列用NULL填充)；
4. 每个列必须要有注释；
5. 选择合适的数据类型；
6. 表必须设置字符集和存储引擎；

删(危险操作)

DROP TABLE 表名; #删除表结构及数据;
TRUNCATE TABLE 表名; #清除表数据;

mysql> SELECT * FROM student;
+----+-------+-----+--------+---------------------+
| id | name  | age | gender | time                |
+----+-------+-----+--------+---------------------+
|  1 | aspen |  27 | F      | 2019-08-16 15:47:30 |
|  2 | Alice |  27 | M      | 2019-08-16 15:48:07 |
+----+-------+-----+--------+---------------------+
2 rows in set (0.00 sec)

mysql> TRUNCATE TABLE student;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM student;
Empty set (0.00 sec)

mysql> DROP TABLE student;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

改

ALTER TABLE 表名 ADD 列名数据类型约束条件其他属性; #向表中增加列
ALTER TABLE 表名 ADD 列名数据类型约束条件其他属性 AFTER 列名; #在表中指定列后增加列
ALTER TABLE 表名 ADD 列名数据类型约束条件其他属性 FIRST; #将该列添加为表的首列
ALTER TABLE 表名 DROP 列名 #从表中删除列
ALTER TABLE 表名 CHANGE 原列名新列名新数据类型新约束条件新其他属性; #修改表中指定列
ALTER TABLE 表名 CHARSET 字符集; #修改表的字符集
ALTER TABLE 表名 ENGINE=存储引擎; #修改表的存储引擎

mysql> ALTER TABLE student ADD telenum CHAR(11) NOT NULL UNIQUE KEY COMMENT '联系方式' AFTER gender;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE student ADD status CHAR(1) NOT NULL COMMENT '状态列' FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC student;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| status  | char(1)             | NO   |     | NULL              |                |
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| name    | varchar(100)        | NO   |     | NULL              |                |
| age     | tinyint(3) unsigned | NO   |     | 99                |                |
| gender  | enum('M','F','U')   | NO   |     | U                 |                |
| telenum | char(11)            | NO   | UNI | NULL              |                |
| time    | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+---------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE student DROP status;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE student CHANGE gender sex CHAR(1) NOT NULL DEFAULT 'u' COMMENT '性别';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE student CHARSET utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE student ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(100) CHARACTER SET latin1 NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年龄',
  `sex` char(1) CHARACTER SET latin1 NOT NULL DEFAULT 'u' COMMENT '性别',
  `telenum` char(11) CHARACTER SET latin1 NOT NULL COMMENT '联系方式',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `telenum` (`telenum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表'
1 row in set (0.00 sec)

ERROR: 
No query specified

DESC 表名; #查看表结构
SHOW CREATE TABLE 表名; #查看表属性
SHOW TABLES #查看库下表信息

mysql> DESC student;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| name    | varchar(100)        | NO   |     | NULL              |                |
| age     | tinyint(3) unsigned | NO   |     | 99                |                |
| sex     | char(1)             | NO   |     | u                 |                |
| telenum | char(11)            | NO   | UNI | NULL              |                |
| time    | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+---------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(100) CHARACTER SET latin1 NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年龄',
  `sex` char(1) CHARACTER SET latin1 NOT NULL DEFAULT 'u' COMMENT '性别',
  `telenum` char(11) CHARACTER SET latin1 NOT NULL COMMENT '联系方式',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `telenum` (`telenum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| student         |
+-----------------+
4 rows in set (0.00 sec)

三、DCL应用

1.授权

MySQL 8.0版本及以后，必须先创建用户，再为用户授权；

grant 权限 on 数据库.表 to 用户@'白名单' identified by 密码; #为指定用户授权相关权限

MySQL多次授权是叠加的关系，后一次授权无法覆盖前一次授权

权限：
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL 代表所有权限，一般是普通管理员拥有
with grant option 为其他用户授权权限，一般是超级管理员的权限

mysql> GRANT ALL ON *.* TO app@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.回收权限

revoke 权限 on 数据库.表 from 用户@'白名单'; #回收指定用户的相关权限

mysql> REVOKE SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE on *.* FROM app@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

3.查看用户权限

show grants for 用户@'白名单'; #查看指定用户的权限

mysql> SHOW GRANTS FOR app@'10.0.0.%'\G;
*************************** 1. row ***************************
Grants for app@10.0.0.%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES ON *.* TO 'app'@'10.0.0.%'
1 row in set (0.00 sec)

ERROR: 
No query specified

四、DML应用

1. 增-INSERRT

INSERT INTO 表名(列名1,列名2,列名3,列名4)
VALUES ('值1',值2,'值3',值4); #为表中插入一组记录

mysql> INSERT INTO student(name,age,sex,telenum)           #同时插入多列记录写法；
    -> VALUES ('Alice_Yang',24,'f',17731779177),('Marry_Li',30,'f',19924998688),('Jerermy_Mu',28,'m',19988188818);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> INSERT INTO student(name,age,sex,telenum)
    -> VALUES ('Aspen_Han',18,'m',13889886688);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student                                  #简易写法；
    -> VALUES (5,'Maria_Liu',27,'f',13339333366,NOW());
Query OK, 1 row affected (0.00 sec)

2. 删-DELETE

DELETE语句属于逻辑删除，被删除的数据空间不会立即被释放

DELETE FROM 表名; #清空表数据(逻辑删除)
DELETE FROM 表名 WHERE 列名='列值'; #删除与指定内容相关的记录

mysql> DELETE FROM student WHERE name='Aspen_Han';
Query OK, 1 row affected (0.00 sec)

3. 改-UPDATE

UPDATE 表名 SET 列名=列值 WHERE 列名=列值; #将指定记录的相关列值进行修改

mysql> UPDATE student SET age=27 WHERE name='Marry_Li';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[伪删除]
设置状态列

ALTER TABLE 表名 ADD STATUS CHAR(1) NOT NULL DEFAULT '1' COMMENT '状态列' FIRST;

改写SQL语句

源语句
DELETE FROM 表名 WHERE 列名=列值;
改写语句
UPDATE 表名 SET STATUS=0 WHERE 列名=列值;

业务查询变更

源语句
SELECT * FROM 表名
变更语句
SELECT * FROM 表名 WHERE STATUS=1;

mysql> ALTER TABLE student ADD status CHAR(1) NOT NULL DEFAULT '1' COMMENT '状态列';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DELETE FROM student WHERE name='Jerermy_Mu';
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE student SET status=0 WHERE name='Alice_Yang';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM student;
+----+------------+-----+-----+-------------+---------------------+--------+
| id | name       | age | sex | telenum     | time                | status |
+----+------------+-----+-----+-------------+---------------------+--------+
|  1 | Alice_Yang |  24 | f   | 17731779177 | 2019-08-16 16:30:07 | 0      |
|  2 | Marry_Li   |  27 | f   | 19924998688 | 2019-08-16 16:30:07 | 1      |
|  5 | Maria_Liu  |  27 | f   | 13339333366 | 2019-08-16 16:33:22 | 1      |
+----+------------+-----+-----+-------------+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM student WHERE status=1;
+----+-----------+-----+-----+-------------+---------------------+--------+
| id | name      | age | sex | telenum     | time                | status |
+----+-----------+-----+-----+-------------+---------------------+--------+
|  2 | Marry_Li  |  27 | f   | 19924998688 | 2019-08-16 16:30:07 | 1      |
|  5 | Maria_Liu |  27 | f   | 13339333366 | 2019-08-16 16:33:22 | 1      |
+----+-----------+-----+-----+-------------+---------------------+--------+
2 rows in set (0.00 sec)

4. 查(DQL)

SELECT * FROM 表名
该指令禁止用于生产环境

mysql> SELECT * FROM student;
+----+------------+-----+-----+-------------+---------------------+--------+
| id | name       | age | sex | telenum     | time                | status |
+----+------------+-----+-----+-------------+---------------------+--------+
|  1 | Alice_Yang |  24 | f   | 17731779177 | 2019-08-16 16:30:07 | 0      |
|  2 | Marry_Li   |  27 | f   | 19924998688 | 2019-08-16 16:30:07 | 1      |
|  5 | Maria_Liu  |  27 | f   | 13339333366 | 2019-08-16 16:33:22 | 1      |
+----+------------+-----+-----+-------------+---------------------+--------+
3 rows in set (0.00 sec)

五、DQL应用

1.SELECT语句

SELECT单独使用(仅限MySQL使用)

SELECT @@内置变量 #查看MySQL内置变量信息
SELECT 内置函数 #查看函数结果

mysql> SELECT @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-08-16 19:31:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 3+4;
+-----+
| 3+4 |
+-----+
|   7 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| world      |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT("HELLO");
+-----------------+
| CONCAT("HELLO") |
+-----------------+
| HELLO           |
+-----------------+
1 row in set (0.00 sec)

CONCAT()函数-拼接函数

mysql> SELECT CONCAT(user,"@",host) FROM mysql.user;
+------------------------------+
| CONCAT(user,"@",host)        |
+------------------------------+
| app@10.0.0.%                 |
| aspen@10.0.0.0/255.255.255.0 |
| mysql.session@localhost      |
| mysql.sys@localhost          |
| root@localhost               |
+------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT user,host FROM mysql.user;
+---------------+------------------------+
| user          | host                   |
+---------------+------------------------+
| app           | 10.0.0.%               |
| aspen         | 10.0.0.0/255.255.255.0 |
| mysql.session | localhost              |
| mysql.sys     | localhost              |
| root          | localhost              |
+---------------+------------------------+
5 rows in set (0.00 sec)

GROUP_CONCAT()函数-列内容转为行内容

mysql> SELECT GROUP_CONCAT(user,"@",host) FROM mysql.user;
+------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(user,"@",host)                                                                          |
+------------------------------------------------------------------------------------------------------+
| app@10.0.0.%,aspen@10.0.0.0/255.255.255.0,mysql.session@localhost,mysql.sys@localhost,root@localhost |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT配合子句使用

SELECT子句执行逻辑
FROM > WHERE > GROUP BY > SELECT 列 > HAVING > ORDERBY > LIMIT > UNION ALL

FROM子句

SELECT 列1,列2,... FROM 表; #查看表指定列的全部记录

mysql> SELECT id,name,age,sex FROM student;
+----+------------+-----+-----+
| id | name       | age | sex |
+----+------------+-----+-----+
|  1 | Alice_Yang |  24 | f   |
|  2 | Marry_Li   |  27 | f   |
|  5 | Maria_Liu  |  27 | f   |
+----+------------+-----+-----+
3 rows in set (0.00 sec)

WHERE子句

SELECT 列1,列2,... FROM 表 WHERE 筛选条件; #查看满足条件的相关列记录

等值查询

SELECT 列1,列2,... FROM 表 WHERE 列=值;

mysql> SELECT id,name,district,population FROM city WHERE district='Liaoning';
+------+------------+----------+------------+
| id   | name       | district | population |
+------+------------+----------+------------+
| 1896 | Shenyang   | Liaoning |    4265200 |
| 1902 | Dalian     | Liaoning |    2697000 |
| 1918 | Anshan     | Liaoning |    1200000 |
| 1919 | Fushun     | Liaoning |    1200000 |
| 1933 | Benxi      | Liaoning |     770000 |
| 1940 | Fuxin      | Liaoning |     640000 |
| 1944 | Jinzhou    | Liaoning |     570000 |
| 1949 | Dandong    | Liaoning |     520000 |
| 1953 | Liaoyang   | Liaoning |     492559 |
| 1966 | Yingkou    | Liaoning |     421589 |
| 1985 | Panjin     | Liaoning |     362773 |
| 1987 | Jinxi      | Liaoning |     357052 |
| 2029 | Tieling    | Liaoning |     254842 |
| 2030 | Wafangdian | Liaoning |     251733 |
| 2047 | Chaoyang   | Liaoning |     222394 |
| 2062 | Haicheng   | Liaoning |     205560 |
| 2073 | Beipiao    | Liaoning |     194301 |
| 2170 | Tiefa      | Liaoning |     131807 |
| 2181 | Kaiyuan    | Liaoning |     124219 |
| 2227 | Xingcheng  | Liaoning |     102384 |
| 2238 | Jinzhou    | Liaoning |      95761 |
+------+------------+----------+------------+
21 rows in set (0.00 sec)

不等值查询(>,<,>=,<=,<>,!=)
<>和!=都表示不等于，不等于查询无法利用索引，效率较低，实际生产环境中很少用到

SELECT 列1,列2,... FROM 表 WHERE 列<值;

mysql> SELECT id,name,countrycode,population FROM city WHERE population<100;
+------+-----------+-------------+------------+
| id   | name      | countrycode | population |
+------+-----------+-------------+------------+
| 2912 | Adamstown | PCN         |         42 |
+------+-----------+-------------+------------+
1 row in set (0.00 sec)

模糊查询

SELECT 列1,列2,... FROM 表 WHERE 列 LIKE 值; #模糊查询表中筛选条件相关记录
%一般不要出现在字符串前面，如'%字符串'，因为该种形式的查询语句，无法使用索引，查询性能较差

mysql> SELECT id,name,countrycode,population FROM city WHERE countrycode LIKE 'SA%';
+------+----------------+-------------+------------+
| id   | name           | countrycode | population |
+------+----------------+-------------+------------+
| 3173 | Riyadh         | SAU         |    3324000 |
| 3174 | Jedda          | SAU         |    2046300 |
| 3175 | Mekka          | SAU         |     965700 |
| 3176 | Medina         | SAU         |     608300 |
| 3177 | al-Dammam      | SAU         |     482300 |
| 3178 | al-Taif        | SAU         |     416100 |
| 3179 | Tabuk          | SAU         |     292600 |
| 3180 | Burayda        | SAU         |     248600 |
| 3181 | al-Hufuf       | SAU         |     225800 |
| 3182 | al-Mubarraz    | SAU         |     219100 |
| 3183 | Khamis Mushayt | SAU         |     217900 |
| 3184 | Hail           | SAU         |     176800 |
| 3185 | al-Kharj       | SAU         |     152100 |
| 3186 | al-Khubar      | SAU         |     141700 |
| 3187 | Jubayl         | SAU         |     140800 |
| 3188 | Hafar al-Batin | SAU         |     137800 |
| 3189 | al-Tuqba       | SAU         |     125700 |
| 3190 | Yanbu          | SAU         |     119800 |
| 3191 | Abha           | SAU         |     112300 |
| 3192 | Ara´ar         | SAU         |     108100 |
| 3193 | al-Qatif       | SAU         |      98900 |
| 3194 | al-Hawiya      | SAU         |      93900 |
| 3195 | Unayza         | SAU         |      91100 |
| 3196 | Najran         | SAU         |      91000 |
+------+----------------+-------------+------------+
24 rows in set (0.00 sec)

多条件查询(AND,OR,XOR)

AND 与
SELECT 列1,列2,... FROM 表 WHERE 筛选条件1 AND 筛选条件2; #查询既满足条件1又满足条件2的记录

mysql> SELECT id,name,countrycode,population FROM city WHERE countrycode='CHN' AND population>4000000;
+------+--------------------+-------------+------------+
| id   | name               | countrycode | population |
+------+--------------------+-------------+------------+
| 1890 | Shanghai           | CHN         |    9696300 |
| 1891 | Peking             | CHN         |    7472000 |
| 1892 | Chongqing          | CHN         |    6351600 |
| 1893 | Tianjin            | CHN         |    5286800 |
| 1894 | Wuhan              | CHN         |    4344600 |
| 1895 | Harbin             | CHN         |    4289800 |
| 1896 | Shenyang           | CHN         |    4265200 |
| 1897 | Kanton [Guangzhou] | CHN         |    4256300 |
+------+--------------------+-------------+------------+
8 rows in set (0.00 sec)

OR 或
SELECT 列1,列2,... FROM 表 WHERE 筛选条件1 OR 筛选条件2; #查询既满足条件1或满足条件2的记录

mysql> SELECT id,name,countrycode,population FROM city WHERE countrycode='PCN' OR countrycode='CXR';
+------+------------------+-------------+------------+
| id   | name             | countrycode | population |
+------+------------------+-------------+------------+
| 1791 | Flying Fish Cove | CXR         |        700 |
| 2912 | Adamstown        | PCN         |         42 |
+------+------------------+-------------+------------+
2 rows in set (0.00 sec)

XOR 非

与BETWEEN AND语句应用

BETWEEN AND表示一个闭区间
SELECT 列1,列2,... FROM 表 WHERE 列 BETWEEN 列值1 AND 列值2; #查询列值在条件1和条件2区间的记录

mysql> SELECT id,name,countrycode,population FROM city WHERE population BETWEEN 100 AND 300;
+------+-------------+-------------+------------+
| id   | name        | countrycode | population |
+------+-------------+-------------+------------+
| 2317 | West Island | CCK         |        167 |
| 3333 | Fakaofo     | TKL         |        300 |
+------+-------------+-------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT id,name,countrycode,population FROM city WHERE population>=100 AND population<=300;
+------+-------------+-------------+------------+
| id   | name        | countrycode | population |
+------+-------------+-------------+------------+
| 2317 | West Island | CCK         |        167 |
| 3333 | Fakaofo     | TKL         |        300 |
+------+-------------+-------------+------------+
2 rows in set (0.00 sec)

与IN语句应用

SELECT 列1,列2,... FROM 表 WHERE 列 IN (列值1,列值2,...); #查询列值满足条件的所有记录

mysql> SELECT id,name,countrycode,population FROM city WHERE countrycode IN ('PCN','CXR');
+------+------------------+-------------+------------+
| id   | name             | countrycode | population |
+------+------------------+-------------+------------+
| 1791 | Flying Fish Cove | CXR         |        700 |
| 2912 | Adamstown        | PCN         |         42 |
+------+------------------+-------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT id,name,countrycode,population FROM city WHERE countrycode='PCN' OR countrycode='CXR';
+------+------------------+-------------+------------+
| id   | name             | countrycode | population |
+------+------------------+-------------+------------+
| 1791 | Flying Fish Cove | CXR         |        700 |
| 2912 | Adamstown        | PCN         |         42 |
+------+------------------+-------------+------------+
2 rows in set (0.00 sec)

GROUP BY子句(一般配合聚合函数使用)

SELECT 列1,列2,... FROM 表 GROUP BY 分组列; #将全表数据按指定列进行分组显示；
SELECT 列1,列2,... FROM 表 WHERE 筛选条件 GROUP BY 分组列; #将指定数据按指定列进行分组显示；

#统计中国、美国和日本的城市数量、人口数量
mysql> SELECT countrycode,COUNT(name),SUM(population) FROM city WHERE countrycode IN ('CHN','JPN','USA')GROUP BY countrycode;
+-------------+-------------+-----------------+
| countrycode | COUNT(name) | SUM(population) |
+-------------+-------------+-----------------+
| CHN         |         363 |       175953614 |
| JPN         |         248 |        77965107 |
| USA         |         274 |        78625774 |
+-------------+-------------+-----------------+
3 rows in set (0.01 sec)

#统计中国每个省的城市数量以及以及人口数量
mysql> SELECT district,COUNT(name),countrycode,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
+----------------+-------------+-------------+-----------------+
| district       | COUNT(name) | countrycode | SUM(population) |
+----------------+-------------+-------------+-----------------+
| Anhui          |          16 | CHN         |         5141136 |
| Chongqing      |           1 | CHN         |         6351600 |
| Fujian         |          12 | CHN         |         3575650 |
| Gansu          |           7 | CHN         |         2462631 |
| Guangdong      |          20 | CHN         |         9510263 |
| Guangxi        |           9 | CHN         |         2925142 |
| Guizhou        |           6 | CHN         |         2512087 |
| Hainan         |           2 | CHN         |          557120 |
| Hebei          |          12 | CHN         |         6458553 |
| Heilongjiang   |          21 | CHN         |        11628057 |
| Henan          |          18 | CHN         |         6899010 |
| Hubei          |          22 | CHN         |         8547585 |
| Hunan          |          18 | CHN         |         5439275 |
| Inner Mongolia |          13 | CHN         |         4121479 |
| Jiangsu        |          25 | CHN         |         9719860 |
| Jiangxi        |          11 | CHN         |         3831558 |
| Jilin          |          20 | CHN         |         7826824 |
| Liaoning       |          21 | CHN         |        15079174 |
| Ningxia        |           2 | CHN         |          802362 |
| Peking         |           2 | CHN         |         7569168 |
| Qinghai        |           1 | CHN         |          700200 |
| Shaanxi        |           8 | CHN         |         4297493 |
| Shandong       |          32 | CHN         |        12114416 |
| Shanghai       |           1 | CHN         |         9696300 |
| Shanxi         |           9 | CHN         |         4169899 |
| Sichuan        |          21 | CHN         |         7456867 |
| Tianjin        |           1 | CHN         |         5286800 |
| Tibet          |           1 | CHN         |          120000 |
| Xinxiang       |          10 | CHN         |         2894705 |
| Yunnan         |           5 | CHN         |         2451016 |
| Zhejiang       |          16 | CHN         |         5807384 |
+----------------+-------------+-------------+-----------------+
31 rows in set (0.01 sec)

#统计中国、日本、美国各有多少个省
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city WHERE countrycode IN ('CHN','JPN','USA') GROUP BY countrycode;
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| CHN         |                       31 |
| JPN         |                       47 |
| USA         |                       45 |
+-------------+--------------------------+
3 rows in set (0.00 sec)

#统计中国东北三省每个省的城市数量以及城市名称
mysql> SELECT district,COUNT(name),GROUP_CONCAT(name) FROM world.city WHERE CountryCode='CHN'AND district IN ('liaoning','jilin','heilongjiang') GROUP BY District\G;
*************************** 1. row ***************************
          district: Heilongjiang
       COUNT(name): 21
GROUP_CONCAT(name): Suihua,Shangzi,Qitaihe,Tieli,Bei´an,Acheng,Shuangyashan,Zhaodong,Jiamusi,Hegang,Mudanjiang,Shuangcheng,Harbin,Daqing,Jixi,Yichun,Fujin,Anda,Qiqihar,Hailun,Mishan
*************************** 2. row ***************************
          district: Jilin
       COUNT(name): 20
GROUP_CONCAT(name): Gongziling,Baicheng,Tumen,Yushu,Meihekou,Fuyu,Jiutai,Jiaohe,Huadian,Da´an,Taonan,Longjing,Jilin,Hunjiang,Liaoyuan,Tonghua,Changchun,Siping,Dunhua,Yanji
*************************** 3. row ***************************
          district: Liaoning
       COUNT(name): 21
GROUP_CONCAT(name): Xingcheng,Jinzhou,Kaiyuan,Tiefa,Dalian,Anshan,Shenyang,Fushun,Benxi,Fuxin,Jinzhou,Dandong,Liaoyang,Yingkou,Panjin,Jinxi,Beipiao,Haicheng,Tieling,Wafangdian,Chaoyang
3 rows in set (0.00 sec)

ERROR: 
No query specified

常用聚合函数
COUNT() #统计
SUM() #求和
MAX() #求最大值
MIN() #求最小值
AVG() #求平均值
GROUP_CONCAT() #列行转换

mysql> HELP Functions and Modifiers for Use with GROUP BY;
You asked for help about help category: "Functions and Modifiers for Use with GROUP BY"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AVG
   BIT_AND
   BIT_OR
   BIT_XOR
   COUNT
   COUNT DISTINCT
   GROUP_CONCAT
   JSON_ARRAYAGG
   JSON_OBJECTAGG
   MAX
   MIN
   STD
   STDDEV
   STDDEV_POP
   STDDEV_SAMP
   SUM
   VARIANCE
   VAR_POP
   VAR_SAMP

HAVING子句

HAVING子句不能利用索引提高查询效率
SELECT 列1,列2,... FROM city GROUP BY 分组列 HAVING 筛选条件; #查询满足筛选条件的记录
SELECT 列1,列2,... FROM city WHERE 筛选条件 GROUP BY 分组列 HAVING 筛选条件; # 查询满足筛选条件的记录(两次筛选)

#查询拥有30个以上的省的国家
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city GROUP BY countrycode HAVING COUNT(DISTINCT district) > 30;
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| CHN         |                       31 |
| JPN         |                       47 |
| MEX         |                       33 |
| RUS         |                       77 |
| TUR         |                       50 |
| USA         |                       45 |
+-------------+--------------------------+
6 rows in set (0.01 sec)

ORDERBY子句

SELECT 列1,列2,... FROM 表名 GROUP BY 分组列 HAVING 筛选条件 ORDER BY 排序条件; # 将满足筛选条件分组记录进行升序排序
SELECT 列1,列2,... FROM 表名 GROUP BY 分组列 HAVING 筛选条件 ORDER BY 排序条件 DESC; #将满足筛选条件分组记录进行降序排序

#升序排列
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city
    -> GROUP BY countrycode
    -> HAVING COUNT(DISTINCT district) > 30
    -> ORDER BY COUNT(DISTINCT district);
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| CHN         |                       31 |
| MEX         |                       33 |
| USA         |                       45 |
| JPN         |                       47 |
| TUR         |                       50 |
| RUS         |                       77 |
+-------------+--------------------------+
6 rows in set (0.00 sec)

#降序排列
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city
    -> GROUP BY countrycode
    -> HAVING COUNT(DISTINCT district) > 30
    -> ORDER BY COUNT(DISTINCT district) DESC;
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| RUS         |                       77 |
| TUR         |                       50 |
| JPN         |                       47 |
| USA         |                       45 |
| MEX         |                       33 |
| CHN         |                       31 |
+-------------+--------------------------+
6 rows in set (0.00 sec)

LIMIT子句

SELECT 列1,列2,... FROM city WHERE 筛选条件 GROUP BY 分组列 HAVING 筛选条件 ORDER BY 排序条件 LIMIT n; 显示查询记录的前n行
SELECT 列1,列2,... FROM city WHERE 筛选条件 GROUP BY 分组列 HAVING 筛选条件 ORDER BY 排序条件 LIMIT m,n; 显示查询记录的m+1行到m+n行(跳过查询记录的前m行，显示之后的n行)
SELECT 列1,列2,... FROM city WHERE 筛选条件 GROUP BY 分组列 HAVING 筛选条件 ORDER BY 排序条件 LIMIT n OFFSET m; 显示查询记录的m+1行到m+n行(将查询记录偏移m行后，显示前n行)

#显示查询记录的前5行
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city
    -> GROUP BY countrycode
    -> HAVING COUNT(DISTINCT district) > 20
    -> ORDER BY COUNT(DISTINCT district) LIMIT 5;
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| VNM         |                       21 |
| EGY         |                       22 |
| COL         |                       23 |
| UKR         |                       25 |
| IDN         |                       26 |
+-------------+--------------------------+
5 rows in set (0.01 sec)

#显示查询记录的是10-14行，方式一
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city
    -> GROUP BY countrycode
    -> HAVING COUNT(DISTINCT district) > 20
    -> ORDER BY COUNT(DISTINCT district) LIMIT 9,5;
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| CHN         |                       31 |
| MEX         |                       33 |
| USA         |                       45 |
| JPN         |                       47 |
| TUR         |                       50 |
+-------------+--------------------------+
5 rows in set (0.00 sec)

#显示查询记录的是10-14行，方式二
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city
    -> GROUP BY countrycode
    -> HAVING COUNT(DISTINCT district) > 20
    -> ORDER BY COUNT(DISTINCT district) LIMIT 5 OFFSET 9;
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| CHN         |                       31 |
| MEX         |                       33 |
| USA         |                       45 |
| JPN         |                       47 |
| TUR         |                       50 |
+-------------+--------------------------+
5 rows in set (0.01 sec)

UNION ALL子句

语句1 UNION ALL 语句2; #将两条语句的结果合并起来
语句1 UNION 语句2; #将两条语句的结果合并起来

UNION与UNION ALL区别

UNION子句：将结果去重后显示；

UNION ALL子句显示结果；

UNION ALL和UNION相比性能更好，在实际环境中用的更多；

#显示查询记录的第10行与第15行
mysql> (SELECT countrycode,COUNT(DISTINCT district) FROM city
    -> GROUP BY countrycode
    -> HAVING COUNT(DISTINCT district) > 20
    -> ORDER BY COUNT(DISTINCT district) LIMIT 1 OFFSET 9)
    -> UNION ALL
    -> (SELECT countrycode,COUNT(DISTINCT district) FROM city
    -> GROUP BY countrycode
    -> HAVING COUNT(DISTINCT district) > 20
    -> ORDER BY COUNT(DISTINCT district) LIMIT 14,1);
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| CHN         |                       31 |
| RUS         |                       77 |
+-------------+--------------------------+
2 rows in set (0.01 sec)

SELEC多表查询

SELECT 表.列1,表.列2,表.列3,... FROM 表1 JOIN 表2 ON 表1.列=表2.列 SELECT子句 #将两张表连接后显示指定查询结果；
SELECT 表.列1,表.列2,表.列3,... FROM 表1 JOIN 表2 ON ON 表1.列=表2.列 JOIN 表3 ON 表2.列=表3.列 SELECT子句 #将多张表连接后显示指定查询结果；

多表连接查询技巧
1.根据需求找到所有关联表
2.找到多表之间的关联条件
3.两张没有关系的表可以通过第三张表建立联系

#查找人数数量少于100人的城市名，国家名，人口，国土面积以及国家独立时间
mysql> SELECT country.name AS country_name, city.name AS city_name, city.population, country.governmentform,   country.indepyear,   country.governmentform,   countrylanguage.language FROM city JOIN country ON city.countrycode = country.code JOIN countrylanguage ON country.code = countrylanguage.countrycode WHERE city.population<100;
+--------------+-----------+------------+-------------------------------+-----------+-------------------------------+-------------+
| country_name | city_name | population | governmentform                | indepyear | governmentform                | language    |
+--------------+-----------+------------+-------------------------------+-----------+-------------------------------+-------------+
| Pitcairn     | Adamstown |         42 | Dependent Territory of the UK |      NULL | Dependent Territory of the UK | Pitcairnese |
+--------------+-----------+------------+-------------------------------+-----------+-------------------------------+-------------+
1 row in set (0.00 sec)

#查找人数数量少于100人的城市名，国家名，人口，国土面积，国家独立时间，政府形式以及国家语言
mysql> mysql> SELECT country.name AS country_name, city.name AS city_name, city.population, country.government country.indepyear,   country.governmentform,   countrylanguage.language FROM city JOIN country ON city.countrycode = country.code JOIN countrylanguage ON country.code = countrylanguage.countrycode WHERE city.population<100;
+--------------+-----------+------------+-------------------------------+-----------+-------------------------------+-------------+
| country_name | city_name | population | governmentform                | indepyear | governmentform                | language    |
+--------------+-----------+------------+-------------------------------+-----------+-------------------------------+-------------+
| Pitcairn     | Adamstown |         42 | Dependent Territory of the UK |      NULL | Dependent Territory of the UK | Pitcairnese |
+--------------+-----------+------------+-------------------------------+-----------+-------------------------------+-------------+
1 row in set (0.00 sec)

2.SHOW语句

MySQL官网SHOW语句帮助文档

SHOW DATABASES; #查看所有数据库
SHOW TABLES; #查看当前库的所有表
SHOW TABLES FROM 库名; #查看某个指定库下的表
SHOW CREATE DATABASE 库名; #查看建库语句
SHOW CREATE TABLE 库名.表名; #查看建表语句
SHOW GRANTS FOR 用户名; #查看用户的权限信息
SHOW CHARSET; #查看字符集;
SHOW COLLATION; #查看校对规则
SHOW PROCESSLIST; #查看数据库连接情况
SHOW INDEX FROM表名; #表的索引情况
SHOW STATUS; #数据库状态查看
SHOW STATUS LIKE '%字符串%'; #模糊查询数据库某些状态
SHOW VARIABLES; #查看所有配置信息
SHOW VARIABLES LIKE '%字符串%'; #查看部分配置信息
SHOW ENGINES; #查看支持的所有的存储引擎
SHOW ENGINE INNODB STATUS\G; #查看INNODB引擎相关的状态信息
SHOW BINARY LOGS; #列举所有的二进制日志
SHOW MASTER STATUS; #查看数据库的日志位置信息
SHOW BINLOG EVNETS IN; #查看二进制日志事件
SHOW SLAVE STATUS \G; #查看从库状态
SHOW RELAYLOG EVENTS; #查看从库RELAYLOG事件信息
SHOW COLUMS FROM 表名;(DESC 表名;) #查看表的列定义信息

mysql> HELP SHOW;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.7/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/show.html

应用举例

mysql> SHOW VARIABLES\G;
*************************** 1. row ***************************
Variable_name: auto_increment_increment
        Value: 1
*************************** 2. row ***************************
Variable_name: auto_increment_offset
        Value: 1
*************************** 3. row ***************************
Variable_name: autocommit
        Value: ON
*************************** 4. row ***************************
Variable_name: automatic_sp_privileges
        Value: ON
......
*************************** 513. row ***************************
Variable_name: warning_count
        Value: 0
513 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> SHOW VARIABLES LIKE  '%trx%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_api_trx_level           | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

六、information_schema 虚拟库(MySQL专有)

information_schema用于统计和搜集整个MySQL数据库源数据信息；

常用视图及作用

TABLES

作用：保存整个MySQL数据库中所有的表信息；
常用列：
TABLE_SCHEMA 表所在库
TABLE_NAME 表名
ENGINE 表所用的引擎
TABLE_ROWS 表的总行数
AVG_ROW_LENGTH 表的平均行长度
INDEX_LENGTH 索引占用长度

表所占空间的计算公司:TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH

TABLES表结构

mysql> DESC information_schema.TABLES; 
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

应用

#统计每个库下表个数最多的5个库的库名以及表的个数并按个数排序
mysql> SELECT table_schema,COUNT(table_name) FROM tables GROUP BY table_schema ORDER BY COUNT(table_name) DESC LIMIT 5;
+--------------------+-------------------+
| table_schema       | COUNT(table_name) |
+--------------------+-------------------+
| sys                |               101 |
| performance_schema |                87 |
| information_schema |                61 |
| mysql              |                31 |
| world              |                 6 |
+--------------------+-------------------+
5 rows in set (0.15 sec)

#统计每个库的总数据量
mysql> SELECT table_schema,SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024 FROM tables GROUP BY table_schema; 
+--------------------+--------------------------------------------------+
| table_schema       | SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024 |
+--------------------+--------------------------------------------------+
| information_schema |                                             NULL |
| mysql              |                                        2308.7832 |
| performance_schema |                                           0.0000 |
| sys                |                                          15.9961 |
| world              |                                         843.7744 |
+--------------------+--------------------------------------------------+
5 rows in set (0.65 sec)

#显示world库下所有的表信息
mysql> SELECT table_schema,table_name FROM tables WHERE table_schema='world';
+--------------+-----------------+
| table_schema | table_name      |
+--------------+-----------------+
| world        | city            |
| world        | country         |
| world        | countrylanguage |
| world        | student         |
| world        | t1              |
| world        | t2              |
+--------------+-----------------+
6 rows in set (0.00 sec)