DBA-70-day04

接昨天----->>>

9. DCL

grant

revoke

10. DML语句

10.1 作用：

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

10.2 种类

insert

update

delete

select

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

-- 彩蛋1：伪删除的实现,使用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;

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

drop table t1 ; ---> 表定义+表数据（物理），全删除，磁盘空间立即删除

truncate table t1 ; ---> 清空表数据（物理），立即释放磁盘空间。

delete from t1; ---> 逐行删除表数据（逻辑，delete mark）。不会立即释放磁盘空间，会有碎片。

11. DQL 语句

11.1 select

11.1.1 作用：

获取用户表中的数据行。

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

-- 11.1.3 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 应用

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

+-----+--------+------+--------+------+

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 |

+------+--------+------+--------+-----+

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

--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 平均分;

11.2 元数据的获取

11.2.1 元数据包含什么？

数据字典信息（表属性、列、列属性）、状态、系统参数、权限等。

ibdata1 、 frm 、 mysql库（权限表、状态表、统计信息）、 P_S、SYS表

11.2.2 查询换数据方法

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