SQL基础与高级应用-CSDN博客

本文链接：https://blog.csdn.net/wxiaomimang/article/details/107437380

SQL基础应用

1、什么是SQL？
2、SQL 标准（ANSI/ISO)
3、SQL常用分类
4、SQL_MODE ?
5、字符集和校对规则
- 5.1 字符集
- 5.2 校对规则（排序规则）
6、数据类型
7、约束和其他表信息
8、DDL 应用【数据控制语言】
9、DCL应用【管理控制语言】
- 9.1 grant授权
- 9.2revoke回收权限
10、DML语句【数据操作语言】
11、DQL语句【数据查询语言】

1、什么是SQL？

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

2、SQL 标准（ANSI/ISO)

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

3、SQL常用分类

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

4、SQL_MODE ?

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

#升级的时候可以用到，因为之前版本不严谨会出现格式不匹，比如除数为0，这时候我们可以把SQL_MODE关闭，设置如下：

#查看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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+

5、字符集和校对规则

5.1 字符集

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字节。

5.2 校对规则（排序规则）

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

大小写是否敏感#查看
select ascii('A');
select ascii('a');

6、数据类型

作用： 约束存储的数据更加有意义，符合对于这个列的定义。保证数据的准确性和标准性

6.1数字类型

		    字节量                  范围
tinyint       1              0~255      -127~128
int           4              0~2^32-1   -2^31 ~2^31-1

区别：长度不一样一个字节八位，数小的选tinyint占字节小
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

6.2字符串类型

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

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

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

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

6.4二进制
6.5josn类型

7、约束和其他表信息

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

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

8、DDL 应用【数据控制语言】

作用：  数据定义语言 。

8.1库定义

# 创建库 

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;

8.2表定义

# 建表
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;			#查看表的列信息

# 修改表定义
#添加
-- 添加和删除字段add
-- 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列drop
ALTER TABLE stu DROP COLUMN a;
ALTER TABLE stu DROP COLUMN b;

#修改
-- 5. 修改数据类型 modify
#命令ALTER TABLE stu modify+修改的列名+修改的数据类型+约束+注释
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. 修改列名及数据类型change
#命令ALTER TABLE stu CHANGE+原来的列名+修改的列名+修改的数据类型+约束+注释
ALTER TABLE stu CHANGE telnum tel VARCHAR(64) NOT NULL UNIQUE KEY COMMENT '手机号';


# 删除表
drop table stu;

8.3 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 。

9、DCL应用【管理控制语言】

#权限管理控制语言
grant	#授权 
revoke  #回收权限

mysql> show privileges;  ---》 查看MySQL中的权限列表

9.1 grant授权

#查看权限
show grants for root@'localhost';

#语法
GRANT 权限  ON 作用范围  TO 用户  IDENTIFIED BY '密码';

作用范围？
*.*         : 全局范围          chmod -R  /
wordpress.* : 单库范围          chmod -R  /wordpress
wordpress.t1：单表范围          chmod -R  /wordpress/a.txt

#管理员用户授权： 
	mysql> grant all on *.* to oldboy@'10.0.0.%' identified by '123';
#普通用户授权： 
	mysql> grant select,update,delete,insert   on test.* to test@'10.0.0.%' idenfied by '123';

9.2revoke回收权限

注意： 不能通过重复授权修改权限，因为授权都是相加的关系。
mysql> revoke delete on test.*  from 'test'@'10.0.0.%';

10、DML语句【数据操作语言】

10.1、作用

针对表的数据行增删改查。

10.2、种类

insert   #增
update   #修
delete   #删
select   #查 DQL重点讲解

10.3、insert（增）语句应用

#进入test库的student表
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;          #查看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');

# 部分列录入，写上你要录入的列；NOW()时间变量
INSERT INTO 
student(NAME,intime)
VALUES('ma6',NOW());
SELECT * FROM student;

10.4、update（改）应用

#字符要加引号，要加上where条件不然会全改
UPDATE student SET NAME='马六' WHERE id=8;

10.5、delete（删）应用

DELETE FROM student WHERE id=8 ;

彩蛋1:伪删除的实现,使用update替代delete。

# 1. 添加一个状态列 state；值为1，修改你要删除的行值为0，查询的时候匹配状态列为1的行，就看不到修改为0的行了
ALTER TABLE student ADD state TINYINT NOT NULL DEFAULT 1 ;

# 2. update 替代 delete 将id为9的行的state的值改为0
UPDATE student SET state=0 WHERE id=9;

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

彩蛋2：以下三条语句的功能及区别？

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

11、DQL语句【数据查询语言】

select
show

1、select

11.1.1、作用

获取用户表中的数据行。

11.1.2、select独立使用（MySQL独立用法）

# 查询系统变量（参数）
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
#替代方案： 
SHOW VARIABLES;				  #查看所有参数
SHOW VARIABLES LIKE '%trx%';  #模糊匹配like

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

# 查询函数
-- select 函数();
SELECT VERSION();				#系统版本
SELECT USER();					#当前用户
SELECT NOW();					#当前时间
SELECT CONCAT("hello world")	#字符窜拼接concat	
SELECT CONCAT(USER ,"@",HOST) FROM mysql.user;		#concat字符拼接应用

11.1.2、select 通用使用方法

单表查询的语法结构

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

导入world练习库

-- https://dev.mysql.com/doc/index-other.html      #下载地址
-- [root@db01 ~]# mysql -uroot -p123 < world.sql   #导入

了解业务

拿到一张表的时候先了解一下表信息

-- 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 应用【from到那个表取数据】

1. 查询全表数据(不代表生产操作)  相当于Linux的 cat /etc/passwd
select * from city;     #查看所有数据
2. 查询部分列数据   ---> awk $2 $5
select NAME,population from city;     #查看name和population列数据

select + from + where 应用【where匹配】

------1.where配合等值查询
-- 例子： 查询中国(CHN)所有的城市信息
select * from city 
where countrycode='chn';

-- 例子： 查询美国(USA)所有的城市名和人口数  #name城市，population人口
select name,population from city 
where countrycode='usa';

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

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


#or，in    in相同条件可以引用
select * from city 
where countrycode='CHN' or countrycode='USA';
 
--- 等价写法
select * from city 
where countrycode in ('CHN','USA');

#and，between and；   between and相同条件可以引用
select * from city 
where population >=1000000 and population<=1100000;

--- 等价写法
select * from city 
where population between 1000000 and 1100000;

where 配合 like应用【配合like进行模糊查询】

-- 查询countrycode是 “CH” 开头的城市信息
SELECT * FROM city
WHERE countrycode LIKE 'CH%';

#注意:%不能放在前面,因为不走索引.

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

group by【将相同的排序到一起

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

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

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

*/

#表结构
id         : 主键列，自增长1-N。
name       : 城市名字
countrycode: 城市所在国家编码(3字母，CHN、USA)
District   ：城市所在区域（省、州、县）
Population ：城市人口

例子:

-- 例子1 ： 统计city表，每个国家的城市个数 
 
SELECT countrycode,COUNT(*)  #countrycode匹配国家的列，count统计个数
FROM city 				#取city表的数据
GROUP BY countrycode;   #group by 对国家进行分组

-- 例子2 ： 统计city表，中国 每个省的 城市个数 
SELECT district,COUNT(*)  		#匹配省份，进行统计个数
FROM city 
WHERE countrycode='CHN'			#匹配中国
GROUP BY district;				#对城市进行分组

-- 例子3 ： 统计city表，每个国家的总人口数
SELECT countrycode,SUM(population)  #匹配国家，sum求和
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 应用

【having只能在group by后进行二次条件判断】

-- 例子：  统计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

【order by 排序可以匹配列条件进行排序，加上desc从大到小排序】

-- 例子：  统计city表，中国 每个省的总人口数，显示总人口数超过500w的信息,并按照总人口排序输出。
#从小到大排序
SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>5000000
order by SUM(population);

#desc从大到下排序
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

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 ;      #offset表示从第几行开始匹配offset也可以用，号代替

-- 只显示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 |
+-----+--------+------+--------+-----+
16 rows in set (0.00 sec)

– 内连接（取交集，相同的列）

SQL 标准 （ANSI/ISO）不同标准语法不同
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 |
+-----+--------+------+--------+-----+

– 外连接（左、右）

#left左连接
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 |
+-----+--------+------+--------+------+

#right右连接
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 |
+------+--------+------+--------+-----+
4 rows in set (0.00 sec)

3. 多表连接语法格式

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.罗列其他的查询条件

4. 多表连接例子：

---  统计学员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 ;

5.别名使用

– 表别名

#用as设置from读取的表为别名，teacher表别名为a，course表别名为b，sc表别名为c 
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 平均分;		#ORDER BY排序这里输出可以调用别名

2.【DQL】show

11.2元数据的获取

11.2.1 元数据包含什么？

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

11.2.2 查询源数据方法

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 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               #查看从库relaylog事件信息
desc  (show colums from city)               #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html

information_schema 视图库

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

– TABLES 使用

– 1. 结构介绍

作用：存储了整个MySQL中所有表相关属性信息
desc tables;    #查看tables
TABLE_SCHEMA     :  库名
TABLE_NAME       ： 表名
ENGINE           ： 存储引擎
TABLE_ROWS       ： 数据行
AVG_ROW_LENGTH   ： 平均行长度（字节）
INDEX_LENGTH     ： 索引的长度（字节）
DATA_FREE        ： 碎片的情况

–2.应用案例

-- 例子1： 统计MySQL所有业务库：库名、表个数、表名  #not in 不包含
select table_schema,count(*),group_concat(table_name) 
from information_schema.tables #对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';

#到出数据要在配置文件加人导出数据了路径,5.7版本以后加入了安全
vim /etc/my.cnf
secure-filr-priv=/tmp	#加入到配置文件即可

#需求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  #导入数据

#查看非innodb表,会发现已经没有了
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)