推广

三，MySQL基础应用

iseeyu2年前 (2024-02-21)推广135

image.png

`字符串`

char
varchar
emun

区别：
char(100)        
    定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
varchar(100) 
    变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
    会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)
如何选择这两个数据类型?
    1. 少于255个字符串长度,定长的列值,选择char
    2. 多于255字符长度,变长的字符串,可以选择varchar
enum 枚举数据类型
address enum('sz','sh','bj'.....)
              1    2    3

image.png

时间类型

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。

image.png

二进制

image.png

约束

--- 建表
表名,列名,列属性,表属性
--- 列属性
PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL    : 非空约束,不允许空值
UNIQUE KEY  : 唯一键约束,不允许重复值
DEFAULT     : 一般配合 NOT NULL 一起使用.
UNSIGNED    : 无符号,一般是配合数字列,非负数
COMMENT     : 注释
AUTO_INCREMENT : 自增长的列

三，SQL语句的应用

3,1 DDL语句库和表的定义（数据定义语言）

库定义规范
1，库名使用小写字符
2，库名不能以数字开头
3，不能是数据库内部的关键字
4，必须设置字符集
创建库：

mysql> create database boy charset utf8mb4;

查看库：

mysql> show databases;
mysql> show create database boy;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| boy      | CREATE DATABASE `boy` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+

删除库：

mysql> drop database test;

修改库：

mysql> show create database wordpress;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+

mysql> alter database wordpress charset utf8mb4;

mysql> show create database wordpress;
+-----------+-----------------------------------------------------------------------+
| Database  | Create Database                                                       |
+-----------+-----------------------------------------------------------------------+
| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-----------+-----------------------------------------------------------------------+

表的定义
表定义规范

表名小写字母,不能数字开头,
不能是保留字符,使用和业务有关的表名
选择合适的数据类型及长度
每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
没个列设置注释
表必须设置存储引擎和字符集
主键列尽量是无关列数字列,最好是自增长
enum类型不要保存数字,只能是字符串类型

表的创建：

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;

查询表：

mysql> show tables;
+---------------+
| Tables_in_boy |
+---------------+
| stu           |
| student       |
+---------------+
2 rows 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;

删除表：

mysql> drop table test;

修改表：

desc stu;
在stu表中添加qq列：
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
在sname后面加上微信列：
ALTER TABLE stu ADD  wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
在id列前面加一列num：
ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST
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              |                |
+--------+---------------------+------+-----+-------------------+----------------+

删除列：

ALTER TABLE stu DROP num;

修改列：
修改varchar(255)为varchar(64）

ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';

同时修改列名和数据类型：

ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';

3,2 DCL应用

grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;
revoke delete on wordpress.*  from 'wordpress'@'10.0.0.%';

3.3 DML的应用

对数据行进行增删改查

insert语句

INSERT INTO stu VALUES(1,'zs',18,'m',NOW());
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
+----+-------+-----+-----+---------------------+
最规范写法：
INSERT INTO stu(id,sname,age,sex,intime)
VALUES(2,'ls',19,'f',NOW())
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
|  2 | ls    |  19 | f   | 2021-05-12 17:19:34 |
+----+-------+-----+-----+---------------------+
针对性录入：
INSERT INTO stu(sname,age,sex)
VALUES('w5',20,'m');
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
|  2 | ls    |  19 | f   | 2021-05-12 17:19:34 |
|  3 | w5    |  20 | m   | 2021-05-12 17:22:18 |
+----+-------+-----+-----+---------------------+
一次性录入多行：
INSERT INTO stu(sname,age,sex)
VALUES
('aa',12,'m'),
('bb',13,'n'),
('cc',14,'f');
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
|  2 | ls    |  19 | f   | 2021-05-12 17:19:34 |
|  3 | w5    |  20 | m   | 2021-05-12 17:22:18 |
|  4 | aa    |  12 | m   | 2021-05-12 17:26:01 |
|  5 | bb    |  13 | n   | 2021-05-12 17:26:01 |
|  6 | cc    |  14 | f   | 2021-05-12 17:26:01 |
+----+-------+-----+-----+---------------------+

update(一定要加where条件)

UPDATE stu SET sname='aaa';
SELECT * FROM stu;
UPDATE stu SET sname='bb' WHERE id=6;

delete语句(一定要加where条件)

DELETE FROM stu;
DELETE FROM stu WHERE id=9;

生产中屏蔽delete功能
使用update替代delete 
ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ;
UPDATE stu SET is_del=1 WHERE id=7;
SELECT * FROM stu WHERE is_del=0;

3.4DQL查询语句

select应用

select单独使用

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

mysql> select @@basedir;
+--------------+
| @@basedir    |
+--------------+
| /data/mysql/ |
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-05-13 09:50:37 |
+---------------------+
mysql> select database();
+------------+
| database() |
+------------+
| boy        |
+------------+

select通用语法(单表)

select  列   
from  表   
where 条件  
group by  条件 
having   条件 
order by 条件
limit

环境说明（world数据库）

链接：https://pan.baidu.com/s/1GJFvzDaPvOyr-HHyZScLPQ 
提取码：2qh4 

查看表结构
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
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       |                |
+-------------+----------+------+-----+---------+----------------+

select配合from子句使用

查询表中所有信息（生产中几乎没有这样的要求）

use world;
SELECT * FROM city;
或
SELECT id,NAME,countrycode,district,population FROM city;

查询表中name，population的值

SELECT  NAME ,population   FROM  city;

select配合where子句使用
where等值查询
例子：
1，查询中国所有城市名和人口数

select name,population from city
where countrycode='CHN';

where配合比较判断查询（> < >= <=）
例子：
1，世界上小于100人的城市名和人口数

select name,population from city
where population<100;

where配合逻辑连接符(AND OR)
例子：

查询中国人口数量大于800w的城市名和人口

select name,population from city
where countrycode='CHN' and population>8000000;

查询中国或美国的城市名和人口数

select name,population from city
where countrycode='CHN' OR countrycode='USA';

查询人口数量在500w到600w之间的城市名和人口数

select name,population from city
where population>5000000 and population<6000000;
或者
SELECT NAME,population FROM city
where population between 5000000 and 6000000;

where配合like子句模糊查询
例子：

查询一下contrycode中带有CH开头,城市信息

select * from city
where  countrycode like 'CH%';

注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差

where配合in语句
例子：

查询中国或美国的城市信息.

seleCt name,population from city
where countrycode='CHN' or countrycode='USA'
或者
select name,population from city
where countrycode in ('CHN','USA');

select 配合 GROUP BY +聚合函数使用
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作
例子：

1，常用聚合函数

max() 最大值
min() 最小值
avg() 平均值
count() 计数
sum() 求和
group_concat()

例子:

统计每个国家,城市的个数

select countrycode,count(id) from city
group by countrycode;

统计每个国家的总人口数.

select countrycode,sum(population) from city
group by countrycode;

统计每个国家省的个数

select countrycode,count(distinct district) from city
group by countrycode;

统计中国每个省的总人口数

SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district;

统计中国每个省城市的个数

select district,count(name) from city
where countrycode='CHN'
group by district;

统计中国每个省城市的名字列表GROUP_CONCAT()

select district,group_concat(name) from city
where countrycode='CHN'
group by district;

7.小扩展

SELECT CONCAT(district,":",GROUP_CONCAT(NAME)) FROM city
WHERE countrycode='CHN'
GROUP BY district;

select配合having子句应用
having子句是做完分组聚合后的判断，在group by后面在做判断的一个功能,having和where除了位置不一样功能上都是一样的
例子：

统计所有国家的总人口数量，将总人口数大于1亿的过滤出来

select countrycode,sum(population) from city
group by countrycode
having sum(population)>100000000;

select配合order by子句应用
给最后的结果集进行排序，可以单独使用，
例子：

统计所有国家的总人口数量，将总人口数大于5000w的过滤出来，并按照从大到小的顺序排列

select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) desc;

select配合limit应用
limit可以理解为是分页显示
例子：

统计所有国家的总人口数量，将总人口数大于5000w的过滤出来，并按照从大到小的顺序排列，只显示前三名

用法1:显示前三行
select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) desc
limit 3;
用法2：显示4-6行
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3,3;

LIMIT M,N            跳过m行显示一共n行
LIMIT Y OFFSET X     跳过x行显示一共y行

小练习

统计中国每个省的总人口数，只打印总人口数小于100w的

SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)<1000000;

查看中国所有的城市，并按人口数进行排序(从大到小)

SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC

统计中国各个省的总人口数量，按照总人口从大到小排序

SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC;

统计中国,每个省的总人口,找出总人口大于500w的,
并按总人口从大到小排序,只显示前三名

SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3;

union和union all
作用：多个结果集合并查询的功能,一般会将出现在or或者in的语句进行改写，会提高很多性能，

例子：
1.查询中国或者美国的城市信息

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

改写为：

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL 
SELECT * FROM city WHERE countrycode='USA';

union 和union all区别

union all     不做去重复
union         做去重复

select多表连接查询（内连接）

作用：单表数据不能满足查询需求时
环境准备：

student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

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

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;

例子：

统计zhang3学习了几门课程，

SELECT student.sname,COUNT(sc.cno) 
FROM student JOIN sc
ON student.sno=sc.sno
WHERE student.sname='zhang3';

2.统计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,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;

查询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';

每位老师所教课程的平均分数并按平均分排序

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 course.cname
ORDER BY AVG(sc.score);

6.查询oldguo所交的不及格的学生姓名

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 teacher.tname='oldguo' AND sc.score<60;

查询所有老师所教学生不及格的信息

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 teacher.tname;

别名应用

表别名：是全局调用的

SELECT t.tname,GROUP_CONCAT(st.sname,":",sc.score) 
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

列别名：可以被having和order by调用

SELECT t.tname AS 讲师名 ,GROUP_CONCAT(st.sname,":",sc.score) AS 不及格
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

3.5元数据获取

元数据介绍及获取介绍

元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的试图
show 命令是封装好功能，提供元数据查询基础功能

information_schema的基本应用

创建一个视图
USE school

CREATE VIEW  aa AS SELECT t.tname AS 讲师名 ,GROUP_CONCAT(st.sname,":",sc.score) AS 不及格
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

SELECT * FROM aa;
适用于常用的sql语句平凡使用，

创建一个视图

USE school

CREATE VIEW  aa AS SELECT t.tname AS 讲师名 ,GROUP_CONCAT(st.sname,":",sc.score) AS 不及格
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

SELECT * FROM aa;

USE informetion_schema
SHOW TABLES;
DESC TABLES;

tables视图中的应用

TABLE_SCHEMA        表所在的库名
TABLE_NAME          表名
ENGINE              存储引擎
TABLE_ROWS          数据行
AVG_ROW_LENGTH      平均行长度
INDEX_LENGTH        索引长度

例子：
1.显示数据库下所有库和表的信息，

SELECT table_schema,table_name FROM information_schema.tables;

2.查询一下innodb引擎的表，

SELECT table_schema,table_name FROM information_schema.tables
WHERE ENGINE='innodb';

3.统计world库下的city表占用空间的大小

表数据量=平均长度*行数+索引长度
AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

SELECT table_name,AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
FROM information_schema.tables
WHERE table_schema='world' AND table_name='city';

统计world库数据量的大小

SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';

5.统计每个库的数据量大小，并按照从大到小排序

SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS aa
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY aa DESC;

配合concat()函数拼接语句或命令

例子：

1.对数据库进行分库分表备份

mysqldump -uroot -p123 world city >/bak/world_city.sql

SELECT CONCAT("mysqldump -uroot -p123 "
,table_schema," ",table_name," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;

2.模仿一下语句，进行批量生成对world库下所有表进行操作

ALTER TABLE world.city DISCARD TABLESPACE;

SELECT
CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;")
FROM information_schema.TABLES
WHERE table_schema='world';

show语句的介绍


SHOW DATABASES;                   查看所有数据库名
SHOW TABLES;                      查看表名
SHOW CREATE DATABASE '库名'       查看建库语句
SHOW CREATE TABLE    '表名'       查看建表语句
SHOW PROCESSLIST                  查看所有用户连接情况
SHOW CHARSET                      查看支持的字符集
SHOW COLLATION                    查看所有支持的校对规则
SHOW GRANTS FOR LIKE "用户"       查看用户的权限信息
SHOW VARIABLES LIKE  "xxx"        查看参数信息
SHOW ENGINES                      查看所有支持的存储引擎类型
SHOW INDEX FROM      '表名'       查看表的索引信息
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 ''               查看数据库整体状态信息