MySQL系列之——SQL介绍、常用SQL分类、数据类型、表属性、字符集、DDL应用、DCL应用、DML应用（增删改）、DQL应用(select )、元数据信息、show命令
文章目录
一 SQL介绍
结构化查询语言
有一些标准：89 92 99 03
5.7 以后符合SQL92严格模式
通过sql_mode参数来控制
# 查看sql_mode，sql_mode用来规范sql语句的书写方式
select @@sql_mode; # 查看sql_mode
ONLY_FULL_GROUP_BY, # 5.7新加入
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION
二 常用SQL分类
# help:客户端功能帮助
# help contents：服务端功能帮助
# help Data Definition
# help DROP DATABASE
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据的查询语言
2.1 客户端命令
# 客户端输入help
# \c 结束上一条命令
# \G 格式化输出
# exit，\q,control+d，quit退出会话
# notee和tee 开启日志记录，
tee/tmp/mysql.log # 开启，以后执行的sql都会被记录到日志，包括结果
select * from t2；
# source 导入sql脚本，类似于<,恢复备份
source /root/my.sql
# system 在mysql中执行linux命令
system ls
system cd /tmp && ls
三 数据类型、表属性、字符集
3.1 数据类型
3.1.1 作用
保证数据的准确性和标准性。
3.1.2 种类
数值类型
tinyint ： -128~127 1个字节，8个比特位，正负2的7次方减1
int ：-2^31~2^31-1 4个字节，32个比特位，正负2的31次方减1
bigint : -2^63~2^63-1 8个字节，64个比特位，正负2的63次方减1
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号
# 创建表指定数据类型
create tabel t1(id int,name varchar(64),age tinyint)
字符类型
image
char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长 的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
varchar类型，除了会存储字符串之外，还会额外使用1-2个字节存储字符长度
enum('bj','tj','sh')：
枚举类型，存字符串类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。
# 括号中数字指的是字符长度，所以存英文和中文，其实占得空间是不一样的
# 英文和数字，一个字符是1个字节，中文3个字节,emoji占4个字节
# 因为编码方式规定了utf8，所以不需要自行考虑此问题
# 测试：
create database db1 charset utf8mb4;
create table t2(id int,name varchar(10),sex char(10));
insert into t2 values(1,'aaaaaaaaaa','一二三四五六七八九十');
insert into t2 values(1,'aaaaaaaaaa','一二三四五六七八九十一');
insert into t2 values(1,'aaaaaaaaaa','1234567891');
ERROR 1406 (22001): Data too long for column 'sex' at row 1
desc t2；# 查看表结构
select length(sex) from t2; # 查看字符所占用的空间
# mysql 5.6 超长会存进去，自动截断
# mysql 5.6 超长会报错
varchar最多能存储65535个字节的数据，一般我们最多定义varchar(255)，超过255会被转成text类型
时间类型
DATETIME （8个字节长度）
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP （4个字节长度）
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响
二进制类型
json格式
5.6以后支持
3.2 表属性
3.2.1 列属性
约束(一般建表时添加):
# primary key ：主键约束，
唯一且非空，每个表只能有一个主键，作为聚簇索引
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
#not null ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
# unique key：唯一键
列值不能重复
# unsigned：无符号
针对数字列，非负数。
其他属性:
# key:索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
# default :默认值
列中，没有录入值时，会自动使用default的值填充
# auto_increment :自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
# comment : 注释
3.2.2 表的属性
存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8
utf8mb4
3.3 字符集和校对规则
3.3.1 字符集（charset）
# show charset;
有非常多，现在只关注如下两种
utf8
utf8mb4 # 5.6以后出现，8.0以后默认使用utf8mb4，8.0以前默认是latin1（拉丁）
# 差别：
utf8：最大存储长度，单个字符最多3个字节
utf8mb4支持的编码比utf8更多，比如emoji字符，emoji字符，一个字符占4个字节
# 查看库的字符编码
show create databaes mysql
3.3.2 校对规则（排序规则，collation）
show collation;
# 影响排序规则
a
b
A
aB
Ba
select ascii('a'); # 查看a的ascii码
如果大小写敏感排序一个样
如果大小写敏感排序另一个样
# 简单来说就是：大小写是否敏感，默认不敏感
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
四 DDL应用
4.1 数据定义语言
数据定义语言，对库和表进行操作，操作mysql的对象，即库和表，对元数据进行操作
4.2 库定义
4.2.1 创建
4.2.1 创建数据库
create database school;
create schema sch;
show charset; # 查看支持的字符集
show collation; # 查看支持的校对规则，collation
CREATE DATABASE test CHARSET utf8;
create database xyz charset utf8mb4 collate utf8mb4_bin;
建库规范：
1.库名不能有大写字母
2.建库要加字符集
3.库名不能有数字开头，不能使用保留字段（database，table）
4.库名要和业务相关
建库标准语句
mysql> create database db charset utf8mb4;
mysql> show create database xuexiao;
4.2.2 删除(生产中禁止使用)
mysql> drop database lqz;
4.2.3 修改
SHOW CREATE DATABASE school;
ALTER DATABASE school CHARSET utf8;
# 注意：
只能改库属性，不能改库名（只能改字符集）
修改字符集，修改后的字符集一定是原字符集的严格超集
从小往大改，从utf8改到utf8mb4可以，从utf8mb4改成utf8可能会乱码
4.2.4 查询库相关信息（DQL）
show databases； # 查看库
show create database lqz； # 查看具体信息
4.3 表定义
4.3.1 创建
create table stu(
列1 属性（数据类型、约束、其他属性） ，
列2 属性，
列3 属性
)
4.3.2 建表
USE school;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(255) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证',
intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';
建表规范：
1. 表名小写（多平台兼容性问题，window不区分大小写，linux严格大小写），
2. 不能是数字开头，名字不要太长15个字符以内
3. 注意字符集和存储引擎
4. 表名和业务有关，不能使用关键字
5. 选择合适的数据类型：合适，简短，足够
6. 必须有主键，每个列都要有注释
7. 每个列设置为非空，无法保证非空，用0来填充。
4.3.2 删除(生产中禁用命令)
drop table t1;
4.3.3 修改
- 在stu表中添加qq列
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE 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 COMMENT '数字' FIRST;
DESC stu;
- 把刚才添加的列都删掉(危险)
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
- 修改sname数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL ;
- 将sgender 改为 sg 数据类型改为 CHAR 类型（change需要把原来不需要修改的也带上）
ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC stu;
注意：
在mysql中，DDL语句在对表进行操作时，是要锁“元数据表”的，此时，所有修改类的命令无法运行
（元数据：记录表的各种信息，对数据锁定，才能修改，否则都去改，就会出问题）
大表加一列，业务繁忙的表，要谨慎
8.0以前版本需要借助，可以借助pt-osc(pt-online-shaema-change)，gh-ost工具进行DDL操作
4.3.4 表属性查询（DQL）
use school
show tables； # 查看该库下所有表
desc stu; # 查看表结构
show create table stu；# 查看详细建表语句
CREATE TABLE ceshi LIKE stu;
五 DCL应用 ****
grant
revoke
六 DML应用
6.1 作用
对表中的数据行进行增、删、改
6.2 insert
--- 最标准的insert语句
INSERT INTO stu(id,sname,sage,sg,sfz,intime)
VALUES
(1,'zs',18,'m','123456',NOW());
SELECT * FROM stu;
--- 省事的写法
INSERT INTO stu
VALUES
(2,'ls',18,'m','1234567',NOW());
--- 针对性的录入数据
INSERT INTO stu(sname,sfz)
VALUES ('w5','34445788');
--- 同时录入多行数据
INSERT INTO stu(sname,sfz)
VALUES
('w55','3444578d8'),
('m6','1212313'),
('aa','123213123123');
SELECT * FROM stu;
# HWM：记录自增数字，高水位线
6.3 update
DESC stu;
SELECT * FROM stu;
UPDATE stu SET sname='zhao4' WHERE id=2;
注意：update语句必须要加where。
6.4 delete（危险！！）
DELETE FROM stu WHERE id=3;
全表删除:
DELETE FROM stu;
truncate table stu;
drop table stu;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,数据库很多，速度慢，并没有在磁盘上真正删除，磁盘空间不会立即释放，自增的值，也不会释放（HWM高水位线不会降低），
truncate: DDL操作,物理层次删除，对与表段中的数据页进行清空,速度快，立即释放控件，HWM高水位线会降低
drop:将表结构（元数据）和物理层次删除
# 常规方法：
以上三者，都能通过备份+日志，恢复数据
# 灵活办法：
delete 可以通过翻转日志（binlog）
三种删除数据的情况，可以通过《延时从库》进行恢复
伪删除：
用update来替代delete，最终保证业务中查不到（select）即可
1.添加状态列
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stu;
2. UPDATE 替代 DELETE
UPDATE stu SET state=0 WHERE id=6;
3. 业务语句查询
SELECT * FROM stu WHERE state=1;
七 DQL应用(select )
7.1 单独使用
-- select @@xxx 查看系统参数，配置文件中配的都可以查看
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;
show variables; # mysql中的500多个参数
show variables like '%or%';
– select 函数();
SELECT NOW(); # 当前时间
SELECT DATABASE(); # 当前数据库
SELECT USER(); # 当前登录用户
SELECT CONCAT("hello world"); # 字符串拼接
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
select version(); # 当前数据库版本号
# 相关函数查询，官方文档或者使用help
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg
help contents
help functions
help String Functions
# 标准sql，其他关系型数据库，必须要有from
select NOW() from dual;
# 计算
select 10*100
默认执行顺序
select 列，列
from 表1，表2。。
where 过滤条件1 过滤条件2。。
group by 条件1 条件2。。
having 过滤条件1 过滤条件2。。
order by 条件列1 条件列2
limit 限制
# d
select
1 from 表1，表2。。。
2 where 过滤条件1 过滤条件2。。
3 group by 条件1 条件2。。
3.5 select_list name,age 列名列表
4 having 过滤条件1 过滤条件2。。
5 order by 条件列1 条件列2
6 limit 限制
# 完整select 执行顺序
(6) SELECT 列
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(7) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
导入数据
mysql < world.sql
库：world
表：
city
country
countrylanguage
city:城市表
DESC city;
ID : 城市ID
NAME : 城市名
CountryCode: 国家代码，比如中国CHN 美国USA
District : 区域
Population : 人口
7.2 单表子句-from
SELECT 列1,列2 FROM 表
SELECT * FROM 表
例子: – 查询city中所有的数据(不要对大表进行操作)
SELECT * FROM city ;
– 查询city表中,id和姓名
SELECT id ,name FROM city;
7.3 单表子句-where
SELECT col1,col2 FROM TABLE WHERE colN 条件;
7.3.1 where配合等值查询
例子: – 查询中国(CHN)所有城市信息
SELECT * FROM city WHERE countrycode='CHN';
– 查询北京市的信息
SELECT * FROM city WHERE NAME='peking';
– 查询甘肃省所有城市信息
SELECT * FROM city WHERE district='gansu';
7.3.2 where配合比较操作符(> < >= <= <>)
例子: – 查询世界上少于100人的城市
SELECT * FROM city WHERE population<100;
7.3.3 where配合逻辑运算符(and or )
例子: – 中国人口数量大于500w
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;
– 中国或美国城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
7.3.4 where配合模糊查询
例子: – 查询省的名字前面带guang开头的
SELECT * FROM city WHERE district LIKE 'guang%';
注意:%不能放在前面,因为不走索引.只能用字符串的列
7.3.5 where配合in语句
– 中国或美国城市信息
SELECT * FROM city WHERE countrycode ='CHN' or countrycode ='USA';
SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');
# 错误
SELECT * FROM city WHERE countrycode ='CHN' or countrycode ='USA'AND population>5000000;
# 正确
SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA') AND population>5000000;
7.3.6 where配合between and
例子: – 查询世界上人口数量大于100w小于200w的城市信息
SELECT * FROM city WHERE population >1000000 AND population <2000000;
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;
7.4 group by + 常用聚合函数
7.4.1 作用
根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列
7.4.2 常用聚合函数
**max()** ：最大值
**min()** ：最小值
**avg()** ：平均值
**sum()** ：总和
**count()** ：个数
group_concat() : 列转行
7.4.3 例子：
例子1：统计世界上每个国家的总人口数.
USE world
SELECT countrycode ,SUM(population) FROM city GROUP BY countrycode;
例子2： 统计中国各个省的总人口数量(练习)
SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district;
例子3：统计世界上每个国家的城市数量(练习)
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
例子4：统计中国，每个省总人口，城市个数，城市名列表(重点)
select district,sum(Population),count(id),name from world.city where countrycode='CHN' group by district;
# 报错：
ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# 原因：违反了sql_mode=only_full_group_by，SELECT list必须是group by的字段和聚合函数，name不属于，因为name有很多，在mysql5.6之前可以查询，但是只取name的第一个，5.7以后直接报错，这是合理的
select district,sum(Population),count(id),group_concat(name) from world.city where countrycode='CHN' group by district;
7.5 having
where|group|having
例子4：统计中国每个省的总人口数，只打印总人口数小于100w
SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;
7.6 order by + limit
7.6.1 作用
实现先排序，by后添加条件列
7.6.2 应用案例
- 查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
- 统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 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 ;
LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5
LIMIT 5 OFFSET 5;
SELECT district, SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;
7.7 distinct：去重复
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city ;
7.8 联合查询- union all
# 求并集
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city
WHERE countrycode ='CHN' or countrycode ='USA';
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'
说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
都是聚合两个结果集
UNION 去重复
UNION ALL 不去重复
7.9 join 多表连接查询
7.9.0 案例准备
按需求创建一下表结构:
use school
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');
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;
7.9.1 语法
笛卡尔积：select * from teacher，course；
内连接(inner join)：
select * from teacher join course on teacher.tno=course.tno;
select city.name,country.name,city.population from city join country on city.countrycode = country.code and city.population <100;
外连接:
left join:左表所有数据和右表满足条件的数据
select * from teacher join course on teacher.tno=course.tno;
select city.name,country.name,city.population from city left join country on city.countrycode = country.code and city.population <100;
right join：右表所有数据和左表满足条件的数据
select city.name,country.name,city.population from city right join country on city.countrycode = country.code and city.population <100;
查询张三的家庭住址
SELECT A.name,B.address FROM
A JOIN B
ON A.id=B.id
WHERE A.name='zhangsan'
7.9.2 例子：
- 查询一下世界上人口数量小于100人的城市名和国家名
SELECT b.name ,a.name ,a.population
FROM city AS a
JOIN country AS b
ON b.code=a.countrycode
WHERE a.Population<100
- 查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）
SELECT a.name,a.population,b.name ,b.SurfaceArea
FROM city AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';
7.9.3 别名
列别名,表别名
SELECT
a.Name AS an ,
b.name AS bn ,
b.SurfaceArea AS bs,
a.Population AS bp
FROM city AS a JOIN country AS b
ON a.CountryCode=b.Code
WHERE a.name ='shenyang';
7.9.4 多表SQL练习题
- 统计zhang3,学习了几门课
SELECT st.sname , COUNT(sc.cno)
FROM student AS st
JOIN
sc
ON st.sno=sc.sno
WHERE st.sname='zhang3'
- 查询zhang3,学习的课程名称有哪些?
SELECT st.sname , GROUP_CONCAT(co.cname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
WHERE st.sname='zhang3'
- 查询oldguo老师教的学生名.
SELECT te.tname ,GROUP_CONCAT(st.sname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
JOIN teacher AS te
ON co.tno=te.tno
WHERE te.tname='oldguo';
- 查询oldguo所教课程的平均分数
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
WHERE te.tname='oldguo'
4.1 每位老师所教课程的平均分,并按平均分排序
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
GROUP BY te.tname
ORDER BY AVG(sc.score) DESC ;
- 查询oldguo所教的不及格的学生姓名
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE te.tname='oldguo' AND sc.score<60;
5.1 查询所有老师所教学生不及格的信息
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE sc.score<60;
注意：
join 左边的为左表，右边的为右边
驱动表：驱动表会拿着每一行，逐一的跟另一个表匹配，匹配成功就合并
#双层for循环
for 每一行 in 驱动表：
for 每一行 in 另一个表：
匹配成功合并
# 假设驱动表是1000行，另一个表是10行，至少要1000次循环，如果拿另一个表去找，最少10次
多表关联，驱动表要选小表，降低next loop次数
对于内连接来讲，我们没法控制驱动表是谁，完全由优化器决定，如果要人为干预，要把内连接写成外连接的方式
如果使用left join 可以强制左表为驱动表
总结：
1 小表作为驱动表，降低next loop次数
2 left join 可以强制左表为驱动表
7.9.5 综合练习
1. 查询平均成绩大于60分的同学的学号和平均成绩；
2. 查询所有同学的学号、姓名、选课数、总成绩；
3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
4. 统计各位老师,所教课程的及格率
5. 查询每门课程被选修的学生数
6. 查询出只选修了一门课程的全部学生的学号和姓名
7. 查询选修课程门数超过1门的学生信息
8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
7.9.6 补充
别名
# 1 表别名
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE sc.score<60;
# 表别名可以在全局使用，只在当次查询中有用
# 2 列别名
select student.sno as '学号',student.sname as '姓名' from studnet;
# 列别名在哪些子句中可以使用
在having之前都不能使用（参照上面select执行顺序）
八 元数据信息
8.1 逻辑表有关组成部分
# 每次数据库启动，会自动在内存中生成nformation_schema，生成查询mysql部分元数据的视图
# 视图：select 语句的执行方法，不保存数据本身
## 创建视图
create view v_city as select name from city where id <10;
## 使用视图
select * from v_city;
8.2 information_schema.tables视图
use information_schema; # 进入information_schema
show tables; # 可以看到所有视图
# 我们研究tables这个视图
desc tables;
# DESC information_schema.TABLES
TABLE_SCHEMA ---->表所在库名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行数(不是特别实时)
AVG_ROW_LENGTH ---->表中行的平均行长度（字节）
INDEX_LENGTH ---->索引的占用空间大小（字节）
DATA_FREE ---->表中是否有碎片
# 所有的
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
ENGINE
VERSION
ROW_FORMAT
TABLE_ROWS
AVG_ROW_LENGTH
DATA_LENGTH
MAX_DATA_LENGTH
INDEX_LENGTH
DATA_FREE
AUTO_INCREMENT
CREATE_TIME
UPDATE_TIME
CHECK_TIME
TABLE_COLLATION
CHECKSUM
CREATE_OPTIONS
TABLE_COMMENT
# 使用场景
资产统计，自动化运维平台，多少个库，多少个表，占用空间怎么样，统计表增长
- 查询整个数据库中所有库和所对应的表信息
SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;
- 统计所有库下的表个数
SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema
- 查询所有innodb引擎的表及所在的库
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';
- 统计world数据库下每张表的磁盘空间占用
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';
- 统计所有数据库的总的磁盘空间占用
SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"
- 统计每个库，所有表的个数，表名
select table_schema,count(table_name),GROUP_CONCAT(table_name) from TABLES group by table_schema;
- 统计每个库占用空间大小
select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 from information_schema.tables group by table_schema;
select table_schema,sum(DATA_LENGTH)/1024 from information_schema.tables group by table_schema;
- 生成整个数据库下的所有表的单独备份语句
模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;
CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
- 107张表，都需要执行以下2条语句
ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';
九 show 命令
show databases; #查看所有数据库
show tables; #查看当前库的所有表
SHOW TABLES FROM #查看某个指定库下的表
show create database world #查看建库语句
show create table world.city #查看建表语句
show grants for root@'localhost' #查看用户的权限信息
show charset； #查看字符集
show collation #查看校对规则
show processlist; #查看数据库连接情况
show full processlist; #查看数据库连接情况详细信息
show privileges #查看权限信息
show index from #表的索引情况
show status #数据库状态查看
SHOW STATUS LIKE '%lock%'; #模糊查询数据库某些状态
SHOW VARIABLES #查看所有配置信息
SHOW variables LIKE '%lock%'; #查看部分配置信息
show engines #查看支持的所有的存储引擎
show engine innodb status\G #查看InnoDB引擎相关的状态信息
show binary logs #列举所有的二进制日志
show master status #查看数据库的日志位置信息
show binlog evnets in #查看二进制日志事件
show slave status \G #查看从库状态
SHOW RELAYLOG EVENTS in #查看从库relaylog事件信息
desc (show colums from city) #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html
help show 查看其它的
本文来自博客园，作者：喝茶看狗叫，转载请注明原文链接：https://www.cnblogs.com/zdwzdwzdw/p/17487927.html
