什么是SQL？

关系型数据库当中通用的查询语言。全名：结构化查询语言。

SQL 标准 （ANSI/ISO）

SQL-89
SQL-92
SQL-99
SQL-03

SQL 常用分类

DDL ： 数据定义语言
DCL ： 数据控制语言
DML ： 数据操作语言

SQL_MODE ?

5.7+ 之后采用的是严格模式。
作用：
为了让我们SQL在执行时更加严谨、有意义，符合常识、逻辑、符合科学等。。。
例子：
1. 日期格式： 2020-07-01 0000-00-00
2. 除法运算： 除数不能为0

查看SQL_MODE:
mysql> select @@sql_mode;
±------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+

设置SQL_MODE:
mysql> set sql_mode=’’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
±-----------+
| @@sql_mode |
±-----------+
| |
±-----------+
1 row in set (0.00 sec)

set sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
±------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±------------------------------------------------------------------------------------------------------------------------------------------+

字符集和校对规则

字符集

mysql> show charset;
utf8
utf8mb4

mysql> show variables like ‘%char%’;
±-------------------------±--------------------------------------------------------------+
| Variable_name | Value |
±-------------------------±--------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /data/app/mysql-5.7.28-linux-glibc2.12-x86_64/share/charsets/ |
±-------------------------±--------------------------------------------------------------+

utf8 和 utf8mb4 区别？
例如：
utf8不完整，emoji表情字符是不支持，utf8mb4是支持的。
根本原因是，utf8 字符最大长度为3字节，utf8mb4是4字节。

校对规则（排序规则）

mysql> show collation;
作用： 影响到了字符串的排序。

数据类型

作用： 约束存储的数据更加有意义，符合对于这个列的定义。

数字类型

字节量
范围

tinyint 1 0~255 -127~128
int 4 0~2^32-1 -2^31 ~2^31-1

字符串类型

char(10)
定长类型的字符串类型。最多存储10个字符。如果存了5个,剩余空间用空格填充。

varchar(10)
变长类型的字符串类型。最多存储10个字符。如果存了5个,按需分配存储空间，另外需要1-2字节，存储字符长度。

怎么选择：一般情况下 变长字符串就用varchar，固定长度一般采用char类型

enum() ： 枚举类型
应用场景： 列中的数据，有限个数的值的时候，并且是有规律。
enum(‘bj’,‘sh’,‘tj’,…)
1 2

时间日期

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。
timestamp会受到时区的影响

二进制

json

约束和其他表属性

Primary key ： 主键约束 ，要求设置为主键的列，储值时，非空且唯一。每张表只有一个主键。
not null : 非空约束，必须录入值
unique key ： 唯一约束，不能重复值
unsigned ： 数字类型约束，无符号。

default ： 设置默认值，一般配合not null 使用
auto_increment ： 针对数字列，自动增长，一般配合主键
comment ： 列或者表进行注释

DDL 应用

作用： 数据定义语言 。

库定义

#创建库
CREATE DATABASE test CHARSET utf8mb4;
CREATE DATABASE wordpress;

#查询库
SHOW DATABASES;
SHOW CREATE DATABASE test;
SHOW CREATE DATABASE wordpress;
#修改库
ALTER DATABASE wordpress CHARSET utf8mb4;

#删除库(生产禁用！！！！)
DROP DATABASE wordpress;

表定义

#建表
USE test;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(64) 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 COMMENT ‘入学时间’
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT ‘学生表’;

#查表定义
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;

#修改表定义
– 添加和删除字段
– 1. 在表中添加telnum char(11) not null unique key comment ‘手机号’
ALTER TABLE stu ADD COLUMN telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT ‘手机号’;

– 2. 在sname后添加a列
ALTER TABLE stu ADD COLUMN a INT NOT NULL COMMENT ‘测试列’ AFTER sname;

– 3. 在第一列前添加b列
ALTER TABLE stu ADD COLUMN b INT NOT NULL COMMENT ‘测试列’ FIRST ;

– 4. 删除添加的a,b列
ALTER TABLE stu DROP COLUMN a;
ALTER TABLE stu DROP COLUMN b;

– 5. 修改数据类型
ALTER TABLE stu MODIFY telnum VARCHAR(20) NOT NULL UNIQUE KEY COMMENT ‘手机号’;
ALTER TABLE stu MODIFY telnum VARCHAR(30) NOT NULL UNIQUE KEY COMMENT ‘手机号’;

– 6. 修改列名及数据类型
ALTER TABLE stu CHANGE telnum tel VARCHAR(64) NOT NULL UNIQUE KEY COMMENT ‘手机号’;

#删除表
drop table stu;

DDL语句开发规范

库： CREATE DATABASE test CHARSET utf8mb4;
1. 库名要与业务有关
2. 库名不使用大写字母、数字开头。
3. 不要使用内置关键字
4. 建库要指定字符集。
5. 生产中禁止使用删库操作。

表：
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(64) 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 COMMENT ‘入学时间’
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT ‘学生表’;

1. 表名：与业务有关,例如：wp_users，不使用大写字母、数字开头，不要太长（16以下）
2. 设置存储引擎、字符集、表注释
3. 表名、列名要使用内置关键字
4. 列名要有意义，长度（16以下）
5. 数据类型：合适的、足够的、简短
6. 每个表要有主键，一般是自增长、无关列数字列。
7. 每个列尽量是not null ，可以配合default
8. 每个列要有注释
9. 修改定义的操作，要在业务不繁忙期间去做。如果紧急可以使用pt-osc 。

DCL

grant
revoke

DML语句

作用

针对表的数据行增删改查。
##种类
insert
update
delete
select

insert 应用

USE test;
DESC student;

标准数据插入方式

INSERT student(id,NAME,age,gender,intime)
VALUES(1,‘zhangs’,18,‘M’,‘2020-07-02 08:30:00’);

SELECT * FROM student;

INSERT INTO
student(id,NAME,age,gender,intime)
VALUES
(2,‘zhang1’,18,‘M’,‘2020-07-01 08:30:00’),
(3,‘zhang2’,19,‘F’,‘2020-07-03 08:30:00’),
(4,‘zhang3’,17,‘M’,‘2020-07-05 08:30:00’),
(5,‘zhang4’,16,‘F’,‘2020-07-06 08:30:00’),
(6,‘zhang5’,15,‘M’,‘2020-07-07 08:30:00’);

#省略写法
INSERT INTO
student
VALUES
(7,‘zhang6’,19,‘M’,‘2020-06-07 08:30:00’);

#部分列录入
INSERT INTO
student(NAME,intime)
VALUES(‘ma6’,NOW());
SELECT * FROM student;

– 10.4 update 应用
UPDATE student SET NAME=‘马六’ WHERE id=8;

– 10.5 delete 应用
DELETE FROM student WHERE id=8 ;

伪删除的实现,使用update替代delete。

1. 添加一个状态列 state
   ALTER TABLE student ADD state TINYINT NOT NULL DEFAULT 1 ;

2. update 替代 delete
   UPDATE student SET state=0 WHERE id=9;

3. 查询语句修改为
   SELECT * FROM student WHERE state=1;

以下三条语句的功能及区别？

drop table t1 ; —> 表定义+表数据（物理），全删除，磁盘空间立即删除
truncate table t1 ; —> 清空表数据（物理），立即释放磁盘空间。
delete from t1; —> 逐行删除表数据（逻辑，delete mark）。不会立即释放磁盘空间，会有碎片。

DQL 语句

select

作用：

获取用户表中的数据行。

select 独立使用

#查询系统变量（参数）
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
#替代方案：
SHOW VARIABLES;
SHOW VARIABLES LIKE ‘%trx%’;

#查询用户变量
SET @oldguo=100;
SELECT @oldguo;

#查询函数
SELECT VERSION();
SELECT USER();
SELECT NOW();
SELECT CONCAT(“hello world”)
SELECT CONCAT(USER ,"@",HOST) FROM mysql.user;

select 通用使用方法

#单表查询的语法结构
/*
select 列
from 表
where 条件
group by 条件
having 条件
order by 列
limit 条件;
*/

1. 导入world练习库
   – https://dev.mysql.com/doc/index-other.html
   – [root@db01 ~]# mysql -uroot -p123 < world.sql

2. 了解业务
   – 1. 查看列的信息
   USE world;
   DESC city;
   SHOW CREATE TABLE city;
   /*
   id : 主键列，自增长1-N。
   name : 城市名字
   countrycode: 城市所在国家编码(3字母，CHN、USA)
   District ：城市所在区域（省、州、县）
   Population ：城市人口
   */
   – 2. 查询表中部分数据
   SELECT * FROM city LIMIT 10;

– 3. 找开发沟通获取信息

select + from 应用

– 1. 查询全表数据(不代表生产操作) —> cat /etc/passwd
SELECT * FROM city;
– 2. 查询部分列数据 —> awk $2 $5
SELECT NAME,population FROM city;

select + from + where 应用

– 1. where 配合等值查询
– 例子： 查询中国(CHN)所有的城市信息
SELECT * FROM city
WHERE countrycode=‘CHN’;
– 例子： 查询美国(USA)所有的城市名和人口数
SELECT NAME,population FROM city
WHERE countrycode=‘USA’;

– 2. where 配合不等值查询 （> < >= <= !=）
– 例子： 查询世界上人口数据小于100人的城市信息
SELECT * FROM city
WHERE population<100;

– 3. where 配合 and or ,between and ,in 使用
– 例子：查询中国，并且人口大于500w的城市信息
SELECT * FROM city
WHERE countrycode=‘CHN’ AND population>5000000;

– 例子： 查询中国或美国的城市信息
SELECT * FROM city
WHERE countrycode=‘CHN’ OR countrycode=‘USA’;
— 等价写法
SELECT * FROM city
WHERE countrycode IN (‘CHN’,‘USA’);

– 例子： 查询人口数量在 100w-110w之间
SELECT * FROM city
WHERE population >=1000000 AND population<=1100000;
— 等价写法
SELECT * FROM city
WHERE population BETWEEN 1000000 AND 1100000;

– 4. where 配合 like应用
– 查询countrycode是 “CH” 开头的城市信息
SELECT * FROM city
WHERE countrycode LIKE ‘CH%’;

select + from + where + group by + 聚合函数 应用

– 1. 需求 ：1000人在一个广场上，要求快速统计每个省的学生数量？
– 1. 站队。分组
– 2. 数数

– 2. group by + 聚合函数的执行逻辑？
– 1. 按照group by的列进行排序+去重复
– 2. 讲其他的查询列进行聚合操作
– 3. 1+2 显示给用户

– 聚合函数种类：
/*
count() : 统计个数
sum() : 求和
avg() ：平均值
max() ：最大值
min() ：最小值
group_concat()：列转行：

*/

– 例子1 ： 统计city表，每个国家的城市个数

SELECT countrycode,COUNT(*)
FROM city
GROUP BY countrycode;

– 例子2 ： 统计city表，中国 每个省的 城市个数
SELECT district,COUNT(*)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district;

– 例子3 ： 统计city表，每个国家的总人口数
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode;

– 例子4 ： 统计city表，中国 每个省的总人口数
SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district;

– 例子5 ： 统计city表，中国 每个省的 城市个数 ，所有城市名
SELECT district,COUNT(*) ,GROUP_CONCAT(NAME)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district;

select + from + where + group by + 聚合函数 + having 应用

– 例子： 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息
SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
having SUM(population)>5000000;

select + from + where + group by + 聚合函数 + having +order by

– 例子： 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口排序输出。

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
order by SUM(population);

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) desc ;

select + from + where + group by + 聚合函数 + having +order by + limit

– 例子： 统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口从大倒小排序输出
– 只显示前5名。

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) desc
limit 5 offset 0 ;

– 只显示6-10名。
SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

SELECT district,SUM(population)
FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5 offset 5;

多表连接查询

– 1. 预备工作
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’),
(2,‘zhang4’,18,‘m’),
(3,‘li4’,18,‘m’),
(4,‘wang5’,19,‘f’),
(5,‘zh4’,18,‘m’),
(6,‘zhao4’,18,‘m’),
(7,‘ma6’,19,‘f’),
(8,‘oldboy’,20,‘m’),
(9,‘oldgirl’,20,‘f’),
(10,‘oldp’,25,‘m’);

INSERT INTO teacher(tno,tname) VALUES
(101,‘oldboy’),
(102,‘hesw’),
(103,‘oldguo’)
(104,‘alex’);

INSERT INTO course(cno,cname,tno)
VALUES
(1001,‘linux’,101),
(1002,‘python’,102),
(1003,‘mysql’,103)
(1004,‘go’,105);

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);

– 2. 多表连接的类型
– 笛卡尔乘积
mysql> select * from teacher , course;

±----±-------±-----±-------±----+
| tno | tname | cno | cname | tno |
±----±-------±-----±-------±----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1001 | linux | 101 |
| 103 | oldguo | 1001 | linux | 101 |
| 104 | alex | 1001 | linux | 101 |
| 101 | oldboy | 1002 | python | 102 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1002 | python | 102 |
| 104 | alex | 1002 | python | 102 |
| 101 | oldboy | 1003 | mysql | 103 |
| 102 | hesw | 1003 | mysql | 103 |
| 103 | oldguo | 1003 | mysql | 103 |
| 104 | alex | 1003 | mysql | 103 |
| 101 | oldboy | 1004 | go | 105 |
| 102 | hesw | 1004 | go | 105 |
| 103 | oldguo | 1004 | go | 105 |
| 104 | alex | 1004 | go | 105 |
±----±-------±-----±-------±----+

– 内连接 （取交集）
SQL92:
mysql> select *from teacher , course where teacher.tno=course.tno;
SQL99:
mysql> select *from teacher join course on teacher.tno=course.tno;
±----±-------±-----±-------±----+
| tno | tname | cno | cname | tno |
±----±-------±-----±-------±----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
±----±-------±-----±-------±----+

– 外连接 （左、右）

mysql>
mysql> select * from teacher left join course on teacher.tno = course.tno;
±----±-------±-----±-------±-----+
| tno | tname | cno | cname | tno |
±----±-------±-----±-------±-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
| 104 | alex | NULL | NULL | NULL |
±----±-------±-----±-------±-----+
4 rows in set (0.00 sec)

mysql> select * from teacher right join course on teacher.tno = course.tno;
±-----±-------±-----±-------±----+
| tno | tname | cno | cname | tno |
±-----±-------±-----±-------±----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
| NULL | NULL | 1004 | go | 105 |
±-----±-------±-----±-------±----+

多表连接语法格式

select xxx
from a join b
on a.x=b.y
where
group by
having
order by
limit

select xxx
from a join b
on a.x=b.y
join c
on b.z=c.zz

核心思路：
1.找到所有相关表
2.找到所有表之间的关联关系
3.罗列其他的查询条件

多表连接例子：

— 统计学员zhang3,学习了几门课
SELECT student.sname,COUNT(*)
FROM student JOIN sc
ON student.sno =sc.sno
WHERE student.sname=‘zhang3’;

— 所有学员学习的课程门数
SELECT student.sname,COUNT(*)
FROM student JOIN sc
ON student.sno =sc.sno
GROUP BY student.sname;

— 查询zhang3,学习的课程名称有哪些?
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;

– 课堂练习：
— 查询oldguo所教课程的平均分数
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 teacher.tname;

— 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname ,AVG(sc.score) FROM
teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tname
ORDER BY AVG(sc.score);

— 查询oldguo所教的不及格的学生姓名

SELECT teacher.tname,GROUP_CONCAT(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’ AND sc.score<60
GROUP BY teacher.tname ;

— 查询所有老师所教学生不及格的信息
SELECT teacher.tname,GROUP_CONCAT(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 sc.score<60
GROUP BY teacher.tname ;

别名的使用

– 表别名
SELECT a.tname,GROUP_CONCAT(d.sname) FROM
teacher AS a
JOIN course AS b
ON a.tno=b.tno
JOIN sc AS c
ON b.cno=c.cno
JOIN student AS d
ON c.sno=d.sno
WHERE c.score<60
GROUP BY a.tname ;

– 列别名
SELECT a.tname AS aa ,GROUP_CONCAT(d.sname) AS bb FROM
teacher AS a
JOIN course AS b
ON a.tno=b.tno
JOIN sc AS c
ON b.cno=c.cno
JOIN student AS d
ON c.sno=d.sno
WHERE c.score<60
GROUP BY a.tname ;

SELECT teacher.tname AS ‘老师名’ ,AVG(sc.score) AS ‘平均分’ FROM
teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tname
ORDER BY 平均分;

元数据的获取

元数据包含什么？

数据字典信息（表属性、列、列属性）、状态、系统参数、权限等。
ibdata1 、 frm 、 mysql库（权限表、状态表、统计信息） 、 P_S、SYS表

查询元数据方法

show 语句

help show
show databases;
show tables [from DB];
show create database world;
show craete table world.city;
show full processlist;
show engines;
show charset;
show collation;
show variables [like ‘%%’]
show status [like ‘%%’]
show grants for
SHOW OPEN TABLES
SHOW INDEX FROM tbl_name
SHOW MASTER STATUS
SHOW BINLOG EVENTS
SHOW RELAYLOG EVENTS
SHOW SLAVE STATUS
SHOW SLAVE HOSTS

#information_schema 视图库
每次数据库启动，自动在内存中生成的“虚拟表”（视图）。
保存了各种常用元数据查询方法的视图，只能查询不能修改和删除。

– TABLES 使用
– 1. 结构介绍
作用：存储了整个MySQL中所有表相关属性信息
desc tables;
TABLE_SCHEMA : 所在库
TABLE_NAME ： 表名
ENGINE ： 存储引擎
TABLE_ROWS ： 数据行
AVG_ROW_LENGTH ： 平均行长度
INDEX_LENGTH ： 索引的长度
DATA_FREE ： 碎片的情况

– 2. 应用案例
– 例子1： 统计MySQL所有业务库：库名、表个数、表名
select table_schema,count(*),group_concat(table_name)
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’)
group by table_schema ;

– 例子2： 统计MySQL所有业务库：库名、数据总量(单张表：TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)

select table_schema,sum(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 as sum_mb
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’)
group by table_schema ;

– 例子3:
生产案例：客户MySQL系统 经历的很多个版本 5.1 --》 5.5 —》 5.6。。。
系统中有2000-3000张表，其中有myisam、innodb两种存储引擎类型。
需求1： 查找业务库中，所有非InnoDB表

select table_schema,table_name,engine
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’;

需求2： 将所有这些表备份走
mysqldump -uroot -p123 test t1 >/data/test_t1.sql

select concat(“mysqldump -uroot -p123 “,table_schema,” “,table_name,” >/data/”,table_schema,"_",table_name,".sql")
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’
into outfile ‘/tmp/dump.sh’;

需求3: 将以上表替换为InnoDB引擎
alter table test.t1 engine=innodb;

select concat(“alter table “,table_schema,”.”,table_name," engine=innodb;")
from information_schema.tables
where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’
into outfile ‘/tmp/alter.sql’;

[root@db01 ~]# sh /tmp/dump.sh
[root@db01 ~]# cd /data/
[root@db01 data]# ll
-rw-r–r-- 1 root root 1741 Jul 2 18:30 test_t1.sql
-rw-r–r-- 1 root root 1741 Jul 2 18:30 world_t2.sql
[root@db01 data]# mysql -uroot -p123 </tmp/alter.sql

mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in (‘mysql’,‘sys’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’;
Empty set (0.00 sec)

最后

以上就是机智母鸡为你收集整理的SQL 基础应用什么是SQL？SQL 标准 （ANSI/ISO）SQL 常用分类SQL_MODE ?字符集和校对规则数据类型约束和其他表属性DDL 应用DCLDML语句DQL 语句多表连接语法格式别名的使用的全部内容，希望文章能够帮你解决SQL 基础应用什么是SQL？SQL 标准 （ANSI/ISO）SQL 常用分类SQL_MODE ?字符集和校对规则数据类型约束和其他表属性DDL 应用DCLDML语句DQL 语句多表连接语法格式别名的使用所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。