三，MySQL基础应用
一，MySQL内置功能
连接数据库
-u 指定登录用户
-p 输入密码
-S 指定sock连接
-h 远程连接指定IP地址
-P 指定端口默认是3306
-e 免交互方式查看数据库
< 恢复数据
例子:
1. mysql -uroot -p -S /tmp/mysql.sock
2. mysql -uroot -p -h10.0.0.51 -P3306
3. -e 免交互执行sql语句
mysql -uroot -p -e "show databases;"
4. < 恢复数据
mysql -uroot -p123 </root/world.sql
常用内置命令
help 打印mysql帮助
\c ctrl+c 结束上个命令运行
\q quit; exit; ctrl+d 退出mysql
\G 将数据竖起来显示
source 恢复备份文件
二，SQL的基础应用
2,1 SQL介绍
结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)
2,2 SQL常用种类
DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言
2,3 SQL引入数据库的逻辑结构
库:
库名字
库属性:字符集,排序规则
表:
表名
表属性:存储引擎类型,字符集,排序规则
列名
列属性:数据类型,约束,其他属性
数据行
2,4 字符集
查看：
mysql> show charset;
8.0 默认: utf8mb4
8.0 之前默认: latin1
utf8 : 最大存储3字节字符,中文占3字节,数字\字母\特殊符号占1字节
uf8mb4 : 最大存储4字节字符,中文占3字节,数字\字母\特殊符号占1字节,可以存储emoji表情
2,5 排序规则（collation）
查看：
mysql> show collation;
影响：
对于英文字符串的,大小写的敏感
utf8mb4_general_ci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音,日文)
2,6 数据类型介绍
保证数据的准确性和标准性。
常用的数字类型：
tinyint
int
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 '' 查看数据库整体状态信息
作者:会倒立的香飘飘
原文链接:https://www.jianshu.com/p/1afee8aaacc0