MySQL笔记 -- SQL语句

基础知识

  • SQL介绍
  1. 结构化查询语言
  2. 关系型数据库通用的命令
  3. 遵循SQL92的标准(SQL_MODE)
  • SQL常用种类
  1. DDL 数据定义语言
  2. DCL 数据控制语言
  3. DML 数据操作语言
  4. DQL 数据查询语言
  • SQL引入

  1. 库属性:字符集,排序规则

  2. 表属性:存储引擎类型,字符集,排序规则
    列属性:数据类型,约束,其它属性
  • 字符集
    mysql> show charset;(utf8mb4 – utf8超集,支持emoji)
    相当于MySQL的密码本(编码表)

  • 排序规则
    mysql> show collation;

排序规则功用
utf8mb4_general_ci英文大小写不敏感(默认)
utf8mb4_bin大小写敏感
  • 数据类型
类型说明
数字tinyint8bit
数字smallint16bit
数字int32bit
数字bigint64bit
字符串char定长字符串类型,立即分配空间,未使用的空格填满
字符串varchar变长字符串类型,按需分配空间单独申请存储空间
字符串enum枚举数据类型
时间dateYYYY-MM-DD
时间timehh:mm:ss[.uuuuuu]
时间datetime范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
时间timestamp1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。时间会跟时区自动变化。

DDL的应用

  1. 创建数据库:mysql> CREATE DATABASE [name] CHARSET [charset] COLLATE [collation];
mysql> create database wordpress;
Query OK, 1 row affected (0.00 sec)

mysql> show create database wordpress;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                                                                     |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database zabbix charset utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show create database zabbix;
+----------+---------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                     |
+----------+---------------------------------------------------------------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
  1. 删除数据库:mysql> DROP DATABASE [name];
  2. 修改数据库:
mysql> alter database zabbix charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show create database zabbix;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database zabbix collate utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)

mysql> show create database zabbix;
+----------+---------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                           |
+----------+---------------------------------------------------------------------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
  1. 查看数据库:myql> SHOW DATABASES;
  • 库定义规范
  1. 库名用大写
  2. 库名不能以数字开头
  3. 不能是数据库内部的关键字

  • 表定义
类别属性说明
表属性COMMENT注释
表属性ENGINE存储引擎
表属性CHARACTER SET字符集
表属性COLLATE排序方式
列属性PRIMARY KEY主键约束,非空
列属性NOT NULL非空约束
列属性UNIQUE KEY唯一键约束
列属性DEFAULT一般配合NOT NULL一起使用
列属性UNSIGNED无符号,配合数字
列属性AUTO_INCREMENT数字自动增长
列属性COMMENT注释
列属性CHARACTER SET字符集
列属性COLLATE排序方式
  1. 建表
mysql> CREATE TABLE `zabbix`.`student`  (
    ->   `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '序号',
    ->   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
    ->   `age` tinyint NOT NULL DEFAULT 99,
    ->   `gender` enum('m','f','n') NULL DEFAULT 'n',
    ->   `intime` datetime NOT NULL DEFAULT now(),
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)

mysql> 
  1. 查表
mysql> show create table zabbix.student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL DEFAULT '99',
  `gender` enum('m','f','n') COLLATE utf8mb4_bin DEFAULT 'n',
  `intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> 
  1. 删表:DROP TABLE [name];

  2. 修改表
    4.1 增加列
    4.2 在某列后新增一列
    4.3 删除列
    4.4 修改某列数据类型的属性
    4.5 修改某列的列名及数据类型
    4.6 删除某列的UNIQUE属性
    4.7 联合主键

mysql> ALTER TABLE zabbix.student ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC zabbix.student;
+--------+-------------------+------+-----+-------------------+-------------------+
| Field  | Type              | Null | Key | Default           | Extra             |
+--------+-------------------+------+-----+-------------------+-------------------+
| id     | int unsigned      | NO   | PRI | NULL              | auto_increment    |
| name   | varchar(255)      | NO   |     | NULL              |                   |
| age    | tinyint           | NO   |     | 99                |                   |
| gender | enum('m','f','n') | YES  |     | n                 |                   |
| intime | datetime          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| qq     | varchar(20)       | NO   |     | NULL              |                   |
+--------+-------------------+------+-----+-------------------+-------------------+
6 rows in set (0.01 sec)

mysql> 
mysql> ALTER TABLE zabbix.student ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC zabbix.student;
+--------+-------------------+------+-----+-------------------+-------------------+
| Field  | Type              | Null | Key | Default           | Extra             |
+--------+-------------------+------+-----+-------------------+-------------------+
| id     | int unsigned      | NO   | PRI | NULL              | auto_increment    |
| name   | varchar(255)      | NO   |     | NULL              |                   |
| wechat | varchar(64)       | NO   | UNI | NULL              |                   |
| age    | tinyint           | NO   |     | 99                |                   |
| gender | enum('m','f','n') | YES  |     | n                 |                   |
| intime | datetime          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| qq     | varchar(20)       | NO   |     | NULL              |                   |
+--------+-------------------+------+-----+-------------------+-------------------+
7 rows in set (0.00 sec)

mysql> 
mysql> ALTER TABLE zabbix.student DROP qq;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC zabbix.student;
+--------+-------------------+------+-----+-------------------+-------------------+
| Field  | Type              | Null | Key | Default           | Extra             |
+--------+-------------------+------+-----+-------------------+-------------------+
| id     | int unsigned      | NO   | PRI | NULL              | auto_increment    |
| name   | varchar(255)      | NO   |     | NULL              |                   |
| wechat | varchar(64)       | NO   | UNI | NULL              |                   |
| age    | tinyint           | NO   |     | 99                |                   |
| gender | enum('m','f','n') | YES  |     | n                 |                   |
| intime | datetime          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------+-------------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> ALTER TABLE zabbix.student MODIFY name VARCHAR(100) NOT NULL COMMENT '姓名';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC zabbix.student;
+--------+-------------------+------+-----+-------------------+-------------------+
| Field  | Type              | Null | Key | Default           | Extra             |
+--------+-------------------+------+-----+-------------------+-------------------+
| id     | int unsigned      | NO   | PRI | NULL              | auto_increment    |
| name   | varchar(100)      | NO   |     | NULL              |                   |
| wechat | varchar(64)       | NO   | UNI | NULL              |                   |
| age    | tinyint           | NO   |     | 99                |                   |
| gender | enum('m','f','n') | YES  |     | n                 |                   |
| intime | datetime          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------+-------------------+------+-----+-------------------+-------------------+
6 rows in set (0.01 sec)

mysql> 
mysql> ALTER TABLE zabbix.student CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC zabbix.student;
+--------+--------------+------+-----+-------------------+-------------------+
| Field  | Type         | Null | Key | Default           | Extra             |
+--------+--------------+------+-----+-------------------+-------------------+
| id     | int unsigned | NO   | PRI | NULL              | auto_increment    |
| name   | varchar(100) | NO   |     | NULL              |                   |
| wechat | varchar(64)  | NO   | UNI | NULL              |                   |
| age    | tinyint      | NO   |     | 99                |                   |
| sex    | char(4)      | NO   |     | NULL              |                   |
| intime | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------+--------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> show create table zabbix.student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(100) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL DEFAULT '99',
  `sex` char(4) COLLATE utf8mb4_bin NOT NULL COMMENT '性别',
  `intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `qq` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'qq号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `qq` (`qq`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> alter table zabbix.student drop index qq;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table zabbix.student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(100) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL DEFAULT '99',
  `sex` char(4) COLLATE utf8mb4_bin NOT NULL COMMENT '性别',
  `intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `qq` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'qq号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> 
mysql> create table zabbix.t1 (id int not null auto_increment, name varchar(20) not null, primary key (id,name));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table zabbix.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> 
  • 建表规范
  1. 表名小写
  2. 不能是数字开头
  3. 注意字符集和存储引擎
  4. 表名和业务有关
  5. 选择合适的数据类型
  6. 每个列都要有注释
  7. 每个列设置为非空,无法保证非空,用0来填充。

DCL的应用

用户管理

  • 形式([user]@’[white-list]’)
用户白名单
user@’%’全开放
user@‘localhost’本地
user@‘127.0.0.1’本地
user@‘10.0.0.%’24位掩码
user@‘10.0.0.5%’[50-59]
user@‘10.0.0.0/255.255.254.0’23位掩码
user@‘10.0.%’16位掩码
  • 创建用户及查询:
mysql> create user wordpress@'13.13.%' identified by 'abcd1234..';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| wordpress        | 13.13.%   |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> 
注意:8.0以后不可以通过授权时直接去创建不存在的用户了(grant all on wordpress.* to wordpress@'13.13.%' identified by 'abcd1234..';
  • 修改
    alter user wordpress@‘13.13.%’ identified by ‘…4321dcba’;

  • 删除
    drop user wordpress@‘13.13.%’;

  • 修改密码:

  1. mysqladmin password -u[user] -p 回车先输旧密码再设置新密码
  2. mysql> alter user [user]@’[host]’ identified by ‘[new-password]’;
  3. 忘记密码的情况
[root@mysql01 ~]# systemctl stop mysqld
[root@mysql01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 6736
[root@mysql01 ~]# 2020-09-20T08:39:41.185821Z mysqld_safe Logging to '/data/mysql/data/mysql01.err'.
2020-09-20T08:39:41.209970Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

[root@mysql01 ~]# mysql
....
mysql> update mysql.user set authentication_string='' where user='root' and host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> quit
Bye
[root@mysql01 ~]# ps -ef | grep mysql
root        6736    6618  0 16:39 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking
mysql       6919    6736  0 16:39 pts/2    00:00:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=mysql01.err --pid-file=mysql01.pid --socket=/tmp/mysql.sock --port=3306
root        7215    6618  0 16:48 pts/2    00:00:00 grep --color=auto mysql
[root@mysql01 ~]# kill -9 6736 6919
[root@mysql01 ~]# systemctl start mysqld
[root@mysql01 ~]# mysqladmin password -uroot -p
Enter password: 
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@mysql01 ~]# 

权限管理

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
  • 常用归类
  1. ALL : 以上所有权限,一般是普通管理员拥有的
  2. with grant option:超级管理员才具备的,给别的用户授权的功能
  3. 应用用户:SELECT,INSERT, UPDATE, DELETE
  • 授权
    grant 权限 on 对象 to 用户 identified by ‘密码’;
  1. 需求:windows机器的navicat登录到linux中的MySQL,管理员用户。
    1.1 grant all on *.* to root@‘10.0.0.%’ identified by ‘123’;
  2. 需求:创建一个应用用户app用户,能从windows上登录mysql,并能操作app库。
    2.2 grant select ,update,insert,delete on app.* to app@‘10.0.0.%’ identified by ‘123’;
  • 查看授权:
    show grants for app@‘10.0.0.%’;

  • 回收权限:
    revoke delete on app.* from app@‘10.0.0.%’;

DML的应用

  • INSERT
mysql> DESC zabbix.student;
+--------+--------------+------+-----+-------------------+-------------------+
| Field  | Type         | Null | Key | Default           | Extra             |
+--------+--------------+------+-----+-------------------+-------------------+
| id     | int unsigned | NO   | PRI | NULL              | auto_increment    |
| name   | varchar(100) | NO   |     | NULL              |                   |
| age    | tinyint      | NO   |     | 99                |                   |
| sex    | char(4)      | NO   |     | NULL              |                   |
| intime | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

mysql> INSERT zabbix.student(id,name,age,sex,intime) VALUES('1','zs','22','m',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from zabbix.student;
+----+------+-----+-----+---------------------+
| id | name | age | sex | intime              |
+----+------+-----+-----+---------------------+
|  1 | zs   |  22 | m   | 2020-09-21 11:41:13 |
+----+------+-----+-----+---------------------+
1 row in set (0.00 sec)

mysql> INSERT zabbix.student(name,age,sex) VALUES('ls','24','m');
Query OK, 1 row affected (0.00 sec)

mysql> select * from zabbix.student;
+----+------+-----+-----+---------------------+
| id | name | age | sex | intime              |
+----+------+-----+-----+---------------------+
|  1 | zs   |  22 | m   | 2020-09-21 11:41:13 |
|  2 | ls   |  24 | m   | 2020-09-21 11:43:22 |
+----+------+-----+-----+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT zabbix.student(name,age,sex) VALUES 
    -> ('ww',25,'m'),
    -> ('mz',37,'f');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from zabbix.student;
+----+------+-----+-----+---------------------+
| id | name | age | sex | intime              |
+----+------+-----+-----+---------------------+
|  1 | zs   |  22 | m   | 2020-09-21 11:41:13 |
|  2 | ls   |  24 | m   | 2020-09-21 11:43:22 |
|  3 | ww   |  25 | m   | 2020-09-21 11:45:44 |
|  4 | mz   |  37 | f   | 2020-09-21 11:45:44 |
+----+------+-----+-----+---------------------+
4 rows in set (0.00 sec)

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

mysql> select * from student;
+----+------+-----+-----+---------------------+------+
| id | name | age | sex | intime              | qq   |
+----+------+-----+-----+---------------------+------+
|  1 | zs   |  22 | m   | 2020-09-21 11:41:13 | NULL |
|  2 | ls   |  24 | m   | 2020-09-21 11:43:22 | NULL |
|  3 | ww   |  25 | m   | 2020-09-21 11:45:44 | NULL |
+----+------+-----+-----+---------------------+------+
3 rows in set (0.00 sec)

mysql> 
  • UPDATE
mysql> UPDATE student SET sex='f' WHERE name='ww';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------+-----+-----+---------------------+------+
| id | name | age | sex | intime              | qq   |
+----+------+-----+-----+---------------------+------+
|  1 | zs   |  22 | m   | 2020-09-21 11:41:13 | NULL |
|  2 | ls   |  24 | m   | 2020-09-21 11:43:22 | NULL |
|  3 | ww   |  25 | f   | 2020-09-21 11:45:44 | NULL |
+----+------+-----+-----+---------------------+------+
3 rows in set (0.00 sec)

mysql> 

DQL的应用

示例数据库的SQL脚本下载:https://blog.csdn.net/horses/article/details/106795844

  • DQL的介绍
  1. select
  2. show

单表查询

  • SELECT
  1. 通用语法:select [column] from [table] where [] group by [] having [] order by [] limit [];
  2. SELECT单独使用的情况:
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> select @@basedir;
+-------------------------------------------------+
| @@basedir                                       |
+-------------------------------------------------+
| /usr/local/mysql-8.0.20-linux-glibc2.12-x86_64/ |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use zabbix;
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> select database();
+------------+
| database() |
+------------+
| zabbix     |
+------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-21 15:58:46 |
+---------------------+
1 row in set (0.00 sec)

mysql> 
  1. SELECT配合FROM使用:
mysql> select * from city;
....
| 4078 | Nablus                             | PSE         | Nablus                 |     100231 |
| 4079 | Rafah                              | PSE         | Rafah                  |      92020 |
+------+------------------------------------+-------------+------------------------+------------+
4079 rows in set (0.00 sec)

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select id,name from city;
....
| 4078 | Nablus                             |
| 4079 | Rafah                              |
+------+------------------------------------+
4079 rows in set (0.01 sec)

mysql> 
  1. SELECT配合WHERE子句使用
    2.1 查询中国所有城市和人口
    2.2 查询小于100人的城市和人口
    2.3 查询中国且人口大于500万的城市和人口数
    2.4 查询世界上人口数大于1000万或小于1000人的城市
    2.5 查询世界上人口数在500到600之间的城市
mysql> SELECT name,population FROM city WHERE countrycode='CHN';
+---------------------+------------+
| name                | population |
+---------------------+------------+
| Shanghai            |    9696300 |
| Peking              |    7472000 |
.....
mysql> SELECT name,population FROM city WHERE population<100;
+-----------+------------+
| name      | population |
+-----------+------------+
| Adamstown |         42 |
+-----------+------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT name,population FROM city WHERE countrycode='CHN' AND population>5000000;
+-----------+------------+
| name      | population |
+-----------+------------+
| Shanghai  |    9696300 |
| Peking    |    7472000 |
| Chongqing |    6351600 |
| Tianjin   |    5286800 |
+-----------+------------+
4 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM city WHERE population>10000000 OR population<100;
+------+-----------------+-------------+-------------+------------+
| ID   | Name            | CountryCode | District    | Population |
+------+-----------------+-------------+-------------+------------+
| 1024 | Mumbai (Bombay) | IND         | Maharashtra |   10500000 |
| 2912 | Adamstown       | PCN         ||         42 |
+------+-----------------+-------------+-------------+------------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM city WHERE population BETWEEN 500 AND 600;
+------+------------+-------------+-------------+------------+
| ID   | Name       | CountryCode | District    | Population |
+------+------------+-------------+-------------+------------+
|   62 | The Valley | AIA         ||        595 |
| 2316 | Bantam     | CCK         | Home Island |        503 |
| 2728 | Yaren      | NRU         ||        559 |
+------+------------+-------------+-------------+------------+
3 rows in set (0.00 sec)

mysql> 
  1. SELECT配合LIKE使用
    3.1 查询countycode以KW开头的城市信息
mysql> SELECT * FROM city WHERE countrycode LIKE 'KW%';
+------+------------------+-------------+----------+------------+
| ID   | Name             | CountryCode | District | Population |
+------+------------------+-------------+----------+------------+
| 2427 | al-Salimiya      | KWT         | Hawalli  |     130215 |
| 2428 | Jalib al-Shuyukh | KWT         | Hawalli  |     102178 |
| 2429 | Kuwait           | KWT         | al-Asima |      28859 |
+------+------------------+-------------+----------+------------+
3 rows in set (0.00 sec)

mysql> 

注意不要出现类似’%CN%'前后都有百分号的语句,因为不走索引,效率极差。考虑使用ES数据库来代替。

  1. WHERE配合IN语句
    4.1 查询中国或美国的城市信息
mysql> SELECT * FROM city WHERE countrycode IN ('CHN','USA');
+------+-------------------------+-------------+----------------------+------------+
| ID   | Name                    | CountryCode | District             | Population |
+------+-------------------------+-------------+----------------------+------------+
| 1890 | Shanghai                | CHN         | Shanghai             |    9696300 |
....
| 4066 | Charleston              | USA         | South Carolina       |      89063 |
+------+-------------------------+-------------+----------------------+------------+
637 rows in set (0.00 sec)

mysql> 
  1. SELECT配合GROUP BY使用
    5.1 常用聚合函数:MAX() MIN() AVG() COUNT() SUM() GROUP_CONCAT() CONCAT()
    5.2 统计每个国家的城市个数
    5.3 统计每个国家的人口数
    5.4 统计每个国家省的个数
    5.5 统计中国各省人口数
    5.6 统计中国各省城市数
    5.7 统计中国各省市的名字列表
    5.8 CONCAT
mysql> SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
+-------------+-----------+
| countrycode | COUNT(id) |
+-------------+-----------+
| ABW         |         1 |
| AFG         |         4 |
....
mysql> SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| ABW         |           29034 |
| AFG         |         2332100 |
....
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city GROUP BY countrycode;
+-------------+--------------------------+
| countrycode | COUNT(DISTINCT district) |
+-------------+--------------------------+
| ABW         |                        1 |
| AFG         |                        4 |
....
mysql> SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
+----------------+-----------------+
| district       | SUM(population) |
+----------------+-----------------+
| Shanghai       |         9696300 |
| Peking         |         7569168 |
....
mysql> SELECT district,COUNT(id) FROM city WHERE countrycode='CHN' GROUP BY district;
+----------------+-----------+
| district       | COUNT(id) |
+----------------+-----------+
| Shanghai       |         1 |
| Peking         |         2 |
....
mysql> SELECT district,GROUP_CONCAT(name) FROM city WHERE countrycode='CHN' GROUP BY district\G
*************************** 1. row ***************************
          district: Anhui
GROUP_CONCAT(name): Huangshan,Hefei,Huainan,Fuyang,Suzhou,Chuzhou,Chaohu,Bengbu,Wuhu,Huaibei,Bozhou,Ma´anshan,Liu´an,Tongling,Anqing,Xuangzhou
*************************** 2. row ***************************
          district: Chongqing
GROUP_CONCAT(name): Chongqing
....
mysql> SELECT CONCAT(district,":",GROUP_CONCAT(name)) FROM city WHERE countrycode='CHN' GROUP BY district\G
*************************** 1. row ***************************
CONCAT(district,":",GROUP_CONCAT(name)): Anhui:Huangshan,Hefei,Huainan,Fuyang,Suzhou,Chuzhou,Chaohu,Bengbu,Wuhu,Huaibei,Bozhou,Ma´anshan,Liu´an,Tongling,Anqing,Xuangzhou
*************************** 2. row ***************************
CONCAT(district,":",GROUP_CONCAT(name)): Chongqing:Chongqing
....

说明:在带有GROUP BY子句的SELECT中,SELECT后的条件列(非主键列),要么在GROUP BY后,要么需要在函数中包含。

mysql> select user,host from user group by user;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.user.Host' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select user,group_concat(host) from user group by user;
+------------------+--------------------+
| user             | group_concat(host) |
+------------------+--------------------+
| mysql.infoschema | localhost          |
| mysql.session    | localhost          |
| mysql.sys        | localhost          |
| root             | 13.13.%,localhost  |
+------------------+--------------------+
4 rows in set (0.00 sec)

mysql> 
  1. SELECT 配合 HAVING
    6.1 过滤总人口数大于1亿的国家
mysql> SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>100000000;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| CHN         |       175953614 |
| IND         |       123298526 |
+-------------+-----------------+
2 rows in set (0.01 sec)

mysql> 
  1. SELECT 配合 ORDER BY
    7.1 将人口数大于5000万的国家从大到小排序
mysql> SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| CHN         |       175953614 |
| IND         |       123298526 |
....
  1. SELECT 配合LIMIT
    8.1 取世界人口数量最多的三个国家
    8.2 取世界人口数量做多的6-8名国家
mysql> SELECT countrycode,SUM(population) FROM city GROUP BY coUntrycode ORDER BY SUM(population) DESC LIMIT 3;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| CHN         |       175953614 |
| IND         |       123298526 |
| BRA         |        85876862 |
+-------------+-----------------+
3 rows in set (0.01 sec)

mysql> 
mysql> SELECT countrycode,SUM(population) FROM city GROUP BY coUntrycode ORDER BY SUM(population) DESC LIMIT 3 OFFSET 5;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| RUS         |        69150700 |
| MEX         |        59752521 |
| KOR         |        38999893 |
+-------------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT countrycode,SUM(population) FROM city GROUP BY coUntrycode ORDER BY SUM(population) DESC LIMIT 5,3;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| RUS         |        69150700 |
| MEX         |        59752521 |
| KOR         |        38999893 |
+-------------+-----------------+
3 rows in set (0.01 sec)

mysql> 
  1. 综合练习
    9.1 打印中国人口数少于100万的省
    9.2 将中国城市人口由大到小排序
    9.3 将中国省人口由大到小排序
    9.4 取中国人口大于500万的前三名省
mysql> SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)<1000000;
+----------+-----------------+
| district | SUM(population) |
+----------+-----------------+
| Qinghai  |          700200 |
| Ningxia  |          802362 |
| Hainan   |          557120 |
| Tibet    |          120000 |
+----------+-----------------+
4 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
+------+---------------------+-------------+----------------+------------+
| ID   | Name                | CountryCode | District       | Population |
+------+---------------------+-------------+----------------+------------+
| 1890 | Shanghai            | CHN         | Shanghai       |    9696300 |
| 1891 | Peking              | CHN         | Peking         |    7472000 |
....
mysql> SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC;
+----------------+-----------------+
| district       | SUM(population) |
+----------------+-----------------+
| Liaoning       |        15079174 |
| Shandong       |        12114416 |
....
mysql> SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC limit 3;
+--------------+-----------------+
| district     | SUM(population) |
+--------------+-----------------+
| Liaoning     |        15079174 |
| Shandong     |        12114416 |
| Heilongjiang |        11628057 |
+--------------+-----------------+
3 rows in set (0.00 sec)

mysql> 
  1. 小结
mysql> SELECT [column] FROM [table]
    -> WHERE []
    -> GROUP BY []
    -> HAVING []
    -> ORDER BY []
    -> LIMIT [];
  1. UNION 和 UNION ALL
    11.1 查询中国和美国的城市信息

UNION ALL 会做去重的工作,UNION不会

mysql> SELECT * FROM city WHERE countrycode='CHN'
    -> UNION ALL
    -> SELECT * FROM city WHERE countrycode='USA';
+------+-------------------------+-------------+----------------------+------------+
| ID   | Name                    | CountryCode | District             | Population |
+------+-------------------------+-------------+----------------------+------------+
| 1890 | Shanghai                | CHN         | Shanghai             |    9696300 |
....
| 4066 | Charleston              | USA         | South Carolina       |      89063 |
+------+-------------------------+-------------+----------------------+------------+
637 rows in set (0.00 sec)

mysql> 

多表查询

  • 多表连接查询(内连接)
  1. 作用:单表数据不能满足查询需求时
    例子:查询人口小于100的城市所在的国家国土面积
mysql> SELECT countrycode,name,population FROM city WHERE population<100;
+-------------+-----------+------------+
| countrycode | name      | population |
+-------------+-----------+------------+
| PCN         | Adamstown |         42 |
+-------------+-----------+------------+
1 row in set (0.00 sec)

mysql> SELECT name,surfacearea FROM country WHERE code='PCN';
+----------+-------------+
| name     | surfacearea |
+----------+-------------+
| Pitcairn |       49.00 |
+----------+-------------+
1 row in set (0.00 sec)

mysql> 
  1. 基本语法
    2.1最核心的是,找到多张表之间的关联条件列
    2.2 列书写,必须是:表明.列
    2.3 所有涉及到的查询列,写在SELECT后
    2.4 所有过滤、分组和排序等条件按顺序写在ON的后面
    2.5 A JOIN B ON A.x=B.y JOIN C ON B.m=C.n

注意:多表连接查询时,驱动表选择数据行较少的表,后续所有表的关联列尽量是主键或者是唯一键,有助于提高性能。

mysql> SELECT country.name,country.SurfaceArea,city.name,city.population FROM city JOIN country ON city.countrycode=country.code WHERE city.population<100;
+----------+-------------+-----------+------------+
| name     | SurfaceArea | name      | population |
+----------+-------------+-----------+------------+
| Pitcairn |       49.00 | Adamstown |         42 |
+----------+-------------+-----------+------------+
1 row in set (0.00 sec)

mysql> 
  1. 案例准备

脚本参考:https://www.jianshu.com/p/08c4b78402ff

drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

在这里插入图片描述

  1. 例子
    4.1 统计zhang3,学了几门课
    4.2 查询zhang3,学习哪些课
    4.3 查询oldguo老师教的学生名和个数
    4.4 查询olguo所教课程的平均分
    4.5 每位老师所教课程的平均分由大到小排序
    4.6 查询不及格的学生信息
    4.7 查询oldguo所教的不及格的学生信息
mysql> SELECT student.sname,COUNT(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname='zhang3';
+--------+---------------+
| sname  | COUNT(sc.cno) |
+--------+---------------+
| zhang3 |             2 |
+--------+---------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT student.sname,GROUP_CONCAT(course.cname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3' GROUP BY student.sname;
+--------+----------------------------+
| sname  | GROUP_CONCAT(course.cname) |
+--------+----------------------------+
| zhang3 | linux,python               |
+--------+----------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname=''oldguo' GROUP BY teacher.tname;
+--------+---------------------------------------------+----------------------+
| tname  | GROUP_CONCAT(student.sname)                 | COUNT(student.sname) |
+--------+---------------------------------------------+----------------------+
| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |                    8 |
+--------+---------------------------------------------+----------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT teacher.tname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno WHERE teacher.tname='oldguo' GROUP BY sc.cno;
+--------+---------------+
| tname  | AVG(sc.score) |
+--------+---------------+
| oldguo |       76.7500 |
+--------+---------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT teacher.tname,course.cname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno GROUP BY teacher.tname,course.cname ORDER BY AVG(sc.score) DESC;
+--------+--------+---------------+
| tname  | cname  | AVG(sc.score) |
+--------+--------+---------------+
| oldboy | linux  |       80.6667 |
| oldguo | mysql  |       76.7500 |
| hesw   | python |       70.0000 |
+--------+--------+---------------+
3 rows in set (0.00 sec)

mysql>  
mysql> SELECT teacher.tname,student.sname,sc.score FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE sc.score<60;
+--------+--------+-------+
| tname  | sname  | score |
+--------+--------+-------+
| hesw   | zhang3 |    59 |
| oldguo | li4    |    40 |
| oldguo | zh4    |    40 |
+--------+--------+-------+
3 rows in set (0.00 sec)

mysql> SELECT teacher.tname,GROUP_CONCAT(student.sname,sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE sc.score<60 GROUP BY teachrer.tno;
+--------+--------------------------------------+
| tname  | GROUP_CONCAT(student.sname,sc.score) |
+--------+--------------------------------------+
| hesw   | zhang359                             |
| oldguo | li440,zh440                          |
+--------+--------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,"-",sc.score)) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE sc.score<60 GROUP BY teacher.tno;
+--------+--------------------------------------------------+
| tname  | GROUP_CONCAT(CONCAT(student.sname,"-",sc.score)) |
+--------+--------------------------------------------------+
| hesw   | zhang3-59                                        |
| oldguo | li4-40,zh4-40                                    |
+--------+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT DISTINCT teacher.tname,student.sname,course.cname,sc.score FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON course.tno=teacher.tno WHERE teacher.tname='oldguo' AND sc.score<60;
+--------+-------+-------+-------+
| tname  | sname | cname | score |
+--------+-------+-------+-------+
| oldguo | li4   | mysql |    40 |
| oldguo | zh4   | mysql |    40 |
+--------+-------+-------+-------+
2 rows in set (0.00 sec)

mysql> 
  1. 别名
    5.1 表别名:可以被全局调用
    5.2 列别名:可以被HAVING和ORDER BY调用
mysql> SELECT DISTINCT th.tname,st.sname,cs.cname,sc.score FROM student st JOIN sc ON st.sno=sc.sno JOIN course cs ON sc.cno=cs.cno JOIN teacher th ON cs.tno=th.tno WHERE th.tname='oldguo' AND sc.score<60;
+--------+-------+-------+-------+
| tname  | sname | cname | score |
+--------+-------+-------+-------+
| oldguo | li4   | mysql |    40 |
| oldguo | zh4   | mysql |    40 |
+--------+-------+-------+-------+
2 rows in set (0.01 sec)

mysql> 
mysql> SELECT DISTINCT th.tname '教师',st.sname '学生',cs.cname '课程',sc.score '分数' FROM student st JOIN sc ON st.sno=sc.sno JOIN course cs ON sc.cno=cs.cno JOIN teacher th ON cs.tno=th.tno WHERE th.tname='oldgguo' AND sc.score<60;
+--------+--------+--------+--------+
| 教师   | 学生   | 课程   | 分数   |
+--------+--------+--------+--------+
| oldguo | li4    | mysql  |     40 |
| oldguo | zh4    | mysql  |     40 |
+--------+--------+--------+--------+
2 rows in set (0.00 sec)

mysql> 

扩展应用

导出查询结果至文件

  1. 修改安全限制
[root@mysql01 ~]# sed '/log_queries/a secure_file_priv=' /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
autocommit=0
log_error=./mysql01.err
log_bin=/data/mysql/binlog/mysql-bin
gtid_mode=on
enforce_gtid_consistency=true
slow_query_log=1
long_query_time=0.3
log_queries_not_using_indexes
secure_file_priv=
[mysql]
socket=/tmp/mysql.sock
[root@mysql01 ~]# sed -i '/log_queries/a secure_file_priv=' /etc/my.cnf
[root@mysql01 ~]# systemctl restart mysqld
[root@mysql01 ~]# 
  1. 导出至文件
mysql> select concat("alter table mysql.", table_name, " discard tablespace;") from information_schema.tables where table_schema='mysql' into outfile '/tmp/alter.sql';
Query OK, 33 rows affected (0.00 sec)

mysql> 
  1. 查看导出文件
[root@mysql01 ~]# head /tmp/alter.sql 
alter table mysql.columns_priv discard tablespace;
alter table mysql.component discard tablespace;
alter table mysql.db discard tablespace;
alter table mysql.default_roles discard tablespace;
alter table mysql.engine_cost discard tablespace;
alter table mysql.func discard tablespace;
alter table mysql.general_log discard tablespace;
alter table mysql.global_grants discard tablespace;
alter table mysql.gtid_executed discard tablespace;
alter table mysql.help_category discard tablespace;
[root@mysql01 ~]# 
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页