一、MySQL内置功能
1、 连接数据库
| 参数 | 说明 |
|---|---|
| -u | 数据库用户名 |
| -p | 用户密码 |
| -S | 使用sock文件登录；例如：mysql -uroot -p -S /tmp/mysql.sock |
| -h | 指定登录IP地址；例如： mysql -uroot -p -h 192.168.1.10 |
| -P | 指定登录IP地址端口；例如：mysql -uroot -p -h 192.168.1.10 -P3306 |
| -e | 免交互执行sql语句；例如：mysql -uroot -p -e "show databases;" |
| < | 恢复数据；例如： mysql -uroot -p123456 /root/world.sql |
2、内置命令
| 命令 | 说明 |
|---|---|
| help | 打印mysql帮助 |
| \c；ctrl+c | 结束上个命令运行 |
| \q；quit; exit; ctrl+d | 退出mysql |
| \G | 将数据竖起来显示 |
| source | 恢复备份文件 |
二、SQL基础
结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)
1、SQL常用种类
| 种类 | 说明 |
|---|---|
| DDL | 数据定义语言 |
| DCL | 数据控制语言 |
| DML | 数据操作语言 |
| DQL | 数据查询语言 |
2、数据库的逻辑结构
| 库 | 表 | 列 | |
|---|---|---|---|
| 名字 | 使用小写字符；不能以数字开头；不能是数据库内部的关键字；必须设置字符集 | 表名小写字母,不能数字开头，不能是保留字符,使用和业务有关的表名 | |
| 属性 | 字符集，排序规则 | 存储引擎类型，字符集，排序规则 | 数据类型，约束，其他属性 |
3、字符集 (charset)
| 常用字符集 | |
|---|---|
| utf8 | 3个字节 |
| utf8mb4 (建议) | 4个字节,支持emoji |
1、设置MySQL默认字符集
基于session会话
set character_set_client=utf8mb4;
# 主要用来设置客户端使用的字符集。
set character_set_database=utf8mb4;
# 主要用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置。
set character_set_server=utf8mb4;
# 服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义
set character_set_connection=utf8mb4;
# 主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
set character_set_results=utf8mb4;
# 数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式。
set character_set_system=utf8mb4;
# 数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式。
set collation_connection=utf8mb4;
set collation_server=utf8mb4;
set collation_database=utf8mb4;
基于全局global
# 设置全局的数据库字符编码
set global character_set_database=utf8mb4;
set global character_ser_server=utf8mb4;
永久性改变，在配置文件中修改数据库的字符编码（需重启服务）
[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
2、字符集的查看
查看MySQL支持的字符集
show charset;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
查看MySQL已配置的默认字符设置
show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
查看库已配置的字符设置
show create database test1;
+----------+----------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------------+
查看表已配置的字符设置
show table status from 库名 like '表名';
查看表中字段已配置的字符设置
show full columns from 表名;
查看库中所有表的字符集设置
select TABLE_NAME,TABLE_COLLATION from information_schema.`TABLES`;
查看所有库所有表中的字段的字符集设置
select TABLE_SCHEMA ,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.`COLUMNS`
3、修改字符集
修改库的字符集
alter database 库名 default character set 字符集;
修改表的字符集
alter table 表名 convert to character set 字符集;
修改字段的字符集
alter table 表名 modify 字段名 字段属性 character set 字符集；
4、排序规则
| 排序规则 | 说明 |
|---|---|
| utf8mb4_general_ci | 大小写不敏感 |
| utf8mb4_bin | 大小写敏感(可以存拼音,日文) |
三、数据类型
1、数字
| 数字 | 说明 |
|---|---|
| 整数 | tinyint 、int |
| 浮点数 | 略 |
2、字符串
| 字符串 | 说明 |
|---|---|
| char(100) | 定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充 |
| varchar(100) | 变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间；会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间) |
| enum | 枚举数据类型 |
3、时间
| 时间 | 说明 |
|---|---|
| datetime | 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999 |
| timestamp | 范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999 |
四、DDL的应用
1、库的定义
1、创建数据库
mysql> create database zabbix charset utf8mb4 collate utf8mb4_bin;
2、查看库情况
mysql> show databases; //查看所有库
mysql> create database zabbix; //查看zabbix库创建详细信息
3、删除数据库(不代表生产操作)
mysql> drop database zabbix;
4、修改数据库字符集
注意：一定是从小往大了改，比如utf8--->utf8mb4
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show create database school;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database school charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show create database school;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
2、表的定义
1、列属性
| 列属性 | 说明 |
|---|---|
| PRIMARY KEY | 主键约束，表中只能有一个，非空且唯一 |
| NOT NULL | 非空约束，不允许空值 |
| UNIQUE KEY | 唯一键约束，不允许重复值 |
| DEFAULT | 默认值，一般配合 NOT NULL 一起使用 |
| UNSIGNED | 无符号,一般是配合数字列，非负数 |
| COMMENT | 注释 |
| AUTO_INCREMENT | 自增长的列 |
2、建表规范
- 表名小写字母,不能数字开头
- 不能是保留字符,使用和业务有关的表名
- 选择合适的数据类型及长度
- 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
- 每个列设置注释
- 表必须设置存储引擎和字符集
- 主键列尽量是无关列数字列,最好是自增长
- enum类型不要保存数字,只能是字符串类型
3、查询建表信息
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| blog |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec)
在school库中创建表
mysql> use school;
Database changed
mysql> CREATE TABLE stu ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号', sname VARCHAR(255) NOT NULL COMMENT '姓名', age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别', intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间' )ENGINE INNODB CHARSET utf8mb4;
Query OK, 0 rows affected (0.02 sec)
查看当前库中存在的表
mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| stu |
+------------------+
1 row in set (0.00 sec)
查看建表信息
mysql> SHOW CREATE TABLE stu;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu | CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(255) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`gender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看表结构
mysql> DESC stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
创建一个和表结构一样的表
mysql> CREATE TABLE test LIKE stu;
Query OK, 0 rows affected (0.00 sec)
mysql> DESC test;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
4、修改表信息
在stu表中添加qq列
mysql> ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
在sname后加微信列
mysql> ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
在id列前加一个新列num
mysql> ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| num | int(11) | NO | UNI | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| wechat | varchar(64) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)
修改sname数据类型的属性VARCHAR(255)为VARCHAR(64)
mysql> ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
将gender 改为 sex 数据类型改为 CHAR 类型
mysql> ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
5、删除列
删除刚才添加的列
mysql> ALTER TABLE stu DROP num;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE stu DROP qq;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE stu DROP wechat;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
五、DCL的应用
1、grant
2、revoke
权限管理: MySQL安装与基础管理
六、DML的应用
1、insert：数据插入
1、根据表结构插入数据
mysql> DESC stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| sex | char(4) | NO | | NULL | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
mysql> INSERT stu VALUES(1,'zs',18,'m',NOW());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+-------+-----+-----+---------------------+
| 1 | zs | 18 | m | 2020-09-06 15:59:36 |
+----+-------+-----+-----+---------------------+
1 row in set (0.00 sec)
2、按照规范插入数据
mysql> INSERT INTO stu(id,sname,age,sex,intime) VALUES (2,'ls',19,'f',NOW());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+-------+-----+-----+---------------------+
| 1 | zs | 18 | m | 2020-09-06 15:59:36 |
| 2 | ls | 19 | f | 2020-09-06 16:00:18 |
+----+-------+-----+-----+---------------------+
2 rows in set (0.00 sec)
3、 针对性的录入数据（其他行默认）
mysql> INSERT INTO stu(sname,age,sex) VALUES ('w5',11,'m');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+-------+-----+-----+---------------------+
| 1 | zs | 18 | m | 2020-09-06 15:59:36 |
| 2 | ls | 19 | f | 2020-09-06 16:00:18 |
| 3 | w5 | 11 | m | 2020-09-06 16:03:17 |
+----+-------+-----+-----+---------------------+
3 rows in set (0.00 sec)
4、一次性录入多行数据
mysql> INSERT INTO stu(sname,age,sex) VALUES ('aa',11,'m'),('bb',12,'f'),('cc',13,'m');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM stu; +----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+-------+-----+-----+---------------------+
| 1 | zs | 18 | m | 2020-09-06 15:59:36 |
| 2 | ls | 19 | f | 2020-09-06 16:00:18 |
| 3 | w5 | 11 | m | 2020-09-06 16:03:17 |
| 4 | aa | 11 | m | 2020-09-06 16:04:12 |
| 5 | bb | 12 | f | 2020-09-06 16:04:12 |
| 6 | cc | 13 | m | 2020-09-06 16:04:12 |
+----+-------+-----+-----+---------------------+
6 rows in set (0.00 sec)
2、update：数据更新
将stu表中所有sname更新为aaa
mysql> UPDATE stu SET sname='aaa';
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+-------+-----+-----+---------------------+
| 1 | aaa | 18 | m | 2020-09-06 15:59:36 |
| 2 | aaa | 19 | f | 2020-09-06 16:00:18 |
| 3 | aaa | 11 | m | 2020-09-06 16:03:17 |
| 4 | aaa | 11 | m | 2020-09-06 16:04:12 |
| 5 | aaa | 12 | f | 2020-09-06 16:04:12 |
| 6 | aaa | 13 | m | 2020-09-06 16:04:12 |
+----+-------+-----+-----+---------------------+
6 rows in set (0.00 sec)
将stu表中id为6的sname更新为bb
mysql> UPDATE stu SET sname='bb' WHERE id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+-------+-----+-----+---------------------+
| 1 | aaa | 18 | m | 2020-09-06 15:59:36 |
| 2 | aaa | 19 | f | 2020-09-06 16:00:18 |
| 3 | aaa | 11 | m | 2020-09-06 16:03:17 |
| 4 | aaa | 11 | m | 2020-09-06 16:04:12 |
| 5 | aaa | 12 | f | 2020-09-06 16:04:12 |
| 6 | bb | 13 | m | 2020-09-06 16:04:12 |
+----+-------+-----+-----+---------------------+
6 rows in set (0.00 sec)
3、delete：数据删除
1、删除表中所有数据
mysql> DELETE FROM stu;
2、删除表中id为9的数据
mysql> DELETE FROM stu WHERE id=9;
Query OK, 0 rows affected (0.00 sec)
4、使用update替代delete（生产中屏蔽delete功能）
mysql> ALTER TABLE stu ADD is_del TINYINT DEFAULT 0;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> UPDATE stu SET is_del=1 WHERE id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM stu WHERE is_del=0;
+----+-------+-----+-----+---------------------+--------+
| id | sname | age | sex | intime | is_del |
+----+-------+-----+-----+---------------------+--------+
| 1 | aaa | 18 | m | 2020-09-06 15:59:36 | 0 |
| 2 | aaa | 19 | f | 2020-09-06 16:00:18 | 0 |
| 4 | aaa | 11 | m | 2020-09-06 16:04:12 | 0 |
| 5 | aaa | 12 | f | 2020-09-06 16:04:12 | 0 |
| 6 | bb | 13 | m | 2020-09-06 16:04:12 | 0 |
+----+-------+-----+-----+---------------------+--------+
5 rows in set (0.00 sec)
七、DQL的应用
mysql> use world
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |城市序号
| Name | char(35) | NO | | | |城市名字
| CountryCode | char(3) | NO | MUL | | |国家代码
| District | char(20) | NO | | | |区域，省
| Population | int(11) | NO | | 0 | |人口数
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1、select 语句的应用
1、select单独使用的情况
mysql> select @@basedir; //查看当前数据库的数据存储路径
mysql> select @@port; //查看当前数据库的端口
mysql> select database(); //查看当前所在那个库
mysql> select now(); //查看当前时间
2、select 通用语法(单表)
1、SELECT 配合 FROM 子句使用
mysql> SELECT * FROM city; //查询表中所有的信息
2、SELECT 配合 WHERE 子句使用
where等值条件查询
mysql> SELECT NAME,population FROM city
WHERE countrycode='CHN'; //查询中国所有的城市名和人口数
where 配合比较判断查询(> < >= <=)
mysql> SELECT NAME,population FROM city
WHERE population<100; //世界上小于100人的城市名和人口数
where 配合逻辑连接符(and or)
mysql> SELECT NAME,population FROM city
WHERE countrycode='CHN' AND population>8000000; //查询中国人口数量大于1000w的城市名和人口
mysql> SELECT NAME,population FROM city
WHERE countrycode='CHN' OR countrycode='USA'; //查询中国或美国的城市名和人口数
mysql> SELECT NAME,population FROM city
WHERE population>5000000 AND population<6000000; //查询人口数量在500w到600w之间的城市名和人口数
mysql> SELECT NAME,population FROM city
WHERE population BETWEEN 5000000 AND 6000000; //查询人口数量在500w到600w之间的城市名和人口数
where配合like子句模糊查询
mysql> SELECT * FROM city
WHERE countrycode LIKE 'CH%'; //查询contrycode中带有CH开头,城市信息
where配合in语句
mysql> SELECT NAME,population FROM city
WHERE countrycode IN ('CHN' ,'USA'); //查询中国或美国的城市信息
3、SELECT 配合 GROUP BY + 聚合函数子句（GROUP BY：分组）
| 聚合函数名称 | 说明 |
|---|---|
| count() | 用于计算满足条件的数据项数，返回int数据类型的值 |
| sum() | 用于求和，只能用于精确或近似数字类型列(bit类型除外)，忽略null值，不允许使用聚合函数和子查询 |
| max() | 用于计算最大值，忽略null值。max函数可以使用于numeric、char、varchar、money、smallmoney、或datetime列，但不能用于bit列。不允许使用聚合函数和子查询 |
| min() | 用于计算最小值，MIN函数可以适用于numeric、char、varchar或datetime、money或smallmoney列，但不能用于bit列。不允许使用聚合函数和子查询，忽略null值 |
| avg() | 用于计算精确型或近似型数据类型的平均值，bit类型除外，忽略null值。AVG函数计算时将计算一组数的总和，然后除以为null的个数，得到平均值 |
| GROUP_CONCAT() | 将组中的字符串连接成为具有各种选项的单个字符串显示 |
mysql> SELECT countrycode,COUNT(id) FROM city
GROUP BY countrycode; //统计每个国家城市的个数
mysql> SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode; //统计每个国家的总人口数
mysql> SELECT countrycode,COUNT(DISTINCT district) FROM city
GROUP BY countrycode; //统计每个国家省的个数
mysql> SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district ; //统计中国每个省的总人口数
mysql> SELECT district,COUNT(NAME) FROM city
WHERE countrycode='CHN'
GROUP BY district ; //统计中国每个省城市的个数
mysql> SELECT district,GROUP_CONCAT(NAME) FROM city
WHERE countrycode='CHN'
GROUP BY district; //统计中国每个省所属所有城市的名称
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| district | GROUP_CONCAT(NAME) |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Anhui | Hefei,Huainan,Bengbu,Wuhu,Huaibei,Ma´anshan,Anqing,Tongling,Fuyang,Suzhou,Liu´an,Chuzhou,Chaohu,Xuangzhou,Bozhou,Huangshan
| Fujian | Fuzhou,Amoy [Xiamen],Nanping,Quanzhou,Zhangzhou,Sanming,Longyan,Yong´an,Fu´an,Fuqing,Putian,Shaowu
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
31 rows in set (0.01 sec)
4、SELECT 配合 HAVING子句（结果再处理）
mysql> SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000; //统计所有总人口数大于5000w的国家
5、SELECT 配合 ORDER BY子句（排序:从小到大；DESC:从大到小）
mysql> SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC; //统计所有总人口数大于5000w的国家并按照从大到小顺序排列
6、SELECT 配合 LIMIT子句（显示数量）
mysql> SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3; //统计所有总人口数大于5000w的国家并按照从大到小顺序排列，只显示前三名
mysql> SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3,3; //统计所有总人口数大于5000w的国家并按照从大到小顺序排列，显示4-6名
用法
LIMIT M,N :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行
7、union 和 union all （多个结果集集合并查询）
mysql> SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'; //查询中国或者美国的城市信息
union all：不做去重复
union：会做去重操作
3、多表连接查询(内连接)
语法要求：
1、找到多张表之间的关联条件列
2、列书写时必须是：表名.列
3、所有涉及到的查询列，都放在select后
4、将所有的过滤、分组、排序等条件按顺序写在on的后面
查询世界上小于100人的城市，所在的国家名，国土面积，城市名，人口数
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | decimal(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | decimal(3,1) | YES | | NULL | |
| GNP | decimal(10,2) | YES | | NULL | |
| GNPOld | decimal(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
mysql> select
country.name, //国名
country.surfacearea, //国土面积
city.name, //城市名
city.population //城市人口
from city //city表
join country //country表
on city.countrycode = country.code //city表和country表的关联列（两列数据相同）
where city.population<100; //人口<100
+----------+-------------+-----------+------------+
| name | surfacearea | name | population |
+----------+-------------+-----------+------------+
| Pitcairn | 49.00 | Adamstown | 42 |
+----------+-------------+-----------+------------+
1 row in set (0.00 sec)
4、查看数据库或表容量大小
查看所有数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
查看所有数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
查看指定数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';
查看指定数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;
八、元数据获取
1、information_schema的基本应用
mysql> use information_schema;
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> desc tables; //有删减
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_SCHEMA | varchar(64) | NO | | | |表所在的库名
| TABLE_NAME | varchar(64) | NO | | | |表名
| ENGINE | varchar(64) | YES | | NULL | |存储引擎
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |数据行
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |平均行长度
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |索引长度
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
显示所有的库和表的信息
mysql> SELECT table_schema,table_name
FROM information_schema.tables;
查询所有innodb引擎的表
mysql> SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables
WHERE ENGINE='innodb';
统计world下的city表占用空间大小（表的数据量=平均行长度×行数+索引长度）单位：kb
AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
mysql> SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema='world' AND table_name='city';
统计world库数据量总大小
mysql> SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';
统计每个库的数据量大小，并按数据量从大到小排序
mysql> SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC; //total_KB为别名
2、配合concat()函数拼接语句或命令
对所有数据库分库分表进行备份
# mysqldump -uroot -p123 world city >/bak/world_city.sql
mysql> SELECT
CONCAT("mysqldump -uroot -p123456",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;
对world库下所有表进行批量操作
mysql> ALTER TABLE world.city DISCARD TABLESPACE;
mysql> SELECT
CONCAT("ALTER TABLE",table_schema,".",table_name,"DISCARD TABLESPACE;")
FROM information_schema.tables
WHERE table_schema='world';
3、show命令
提供元数据查询基础功能
| 语句 | 说明 |
|---|---|
| show databases | 查看数据库名 |
| show tables | 查看表名 |
| show create database xx | 查看建库语句 |
| show create table xx | 查看建表语句 |
| show processlist | 查看所有用户连接情况 |
| show charset | 查看支持的字符集 |
| show collation | 查看所有支持的校对规则 |
| show grants for xx | 查看用户的权限信息 |
| show variables like '%xx%' | 查看参数信息 |
| show engines | 查看所有支持的存储引擎类型 |
| show index from xxx | 查看表的索引信息 |
| show engine innodb status\G | 查看innoDB引擎详细状态信息 |
| show binary logs | 查看二进制日志的列表信息 |
| show binlog events in '' | 查看二进制日志的事件信息 |
| show master status | 查看mysql当前使用二进制日志信息 |
| show slave status\G | 查看从库状态信息 |
| show relaylog events in '' | 查看中继日志的事件信息 |
| show status like '' | 查看数据库整体状态信息 |
show status like '%下面变量%';
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。
评论区