MySQL学习笔记
文章目录
- 1 DML语言概述
- 2 insert
- —2.1 语法
- —2.2 操作演示
- 3 update
- 4 delete
- 5 select查询参数值
- 6 select查询函数
- 7 select 单表查询
- —7.1 多子句的执行顺序
- —7.2 select 配合 from 使用
- —7.3 select...from...where 比较判断及过滤
- —7.4 select...group by聚合操作
- —7.5 select...group by...having 过滤使用
- —7.6 select...group by...having...order by 排序
- —7.7 select...group by...having...order by...limit选择显示
- 8 多表查询
- —8.1 内连接
- —8.2 左外连接
- —8.3 右外连接
- —8.4 全连接(笛卡尔乘积)
- —8.5 多表连接练习使用
- —8.6 别名
- —8.7 子查询
- —8.8 union 和union all
1 DML语言概述
mysql> help contents;
mysql> help Data Manipulation；
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
CALL
DELETE
DO
DUAL
HANDLER
IMPORT TABLE
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
PARENTHESIZED QUERY EXPRESSIONS
REPLACE
SELECT
TABLE
UNION
UPDATE
VALUES STATEMENT
最常用的是insert update delete select
2 insert
—2.1 语法
按照对应好的列，对每个列插入值。
语法：
insert into table_name(colname1,colname2,...) values(col1_value,col2_value,...);
—2.2 操作演示
举例1：
use test;
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`sid` CHAR(10) NOT NULL COMMENT '学号',
`sname` VARCHAR(128) NOT NULL COMMENT '姓名',
`sage` TINYINT(3) UNSIGNED NOT NULL DEFAULT '18' COMMENT '年龄',
`sgender` TINYINT(3) UNSIGNED NOT NULL DEFAULT '2' COMMENT '性别',
`scardid` CHAR(18) NOT NULL COMMENT '身份证号',
`saddr` ENUM('北京市','上海市','天津市','重庆市') NOT NULL COMMENT '省份',
`shobby` SET('足球','篮球','羽毛','排球','保龄','乒乓') DEFAULT NULL COMMENT '爱好',
`smoney` DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '学费',
`sdate` DATETIME NOT NULL COMMENT '入学时间',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
desc student;
insert into student(sid,sname,scardid,saddr,shobby,smoney,sdate)
values(1001,'zs','1234567','北京市','足球,排球',10000.11,now());
show tables;
desc student;
insert into student(sid,sname,scardid,saddr,shobby,smoney,sdate)
values(1001,'zs','1234567','北京市','足球,排球',10000.11,now());
mysql> select * from student;
+----+------+-------+------+---------+---------+-----------+---------------+----
------+---------------------+
| id | sid | sname | sage | sgender | scardid | saddr | shobby |
smoney | sdate |
+----+------+-------+------+---------+---------+-----------+---------------+----
------+---------------------+
| 1 | 1001 | zs | 18 | 2 | 1234567 | 北京市 | 足球,排球 |
10000.11 | 2020-01-11 10:14:55 |
+----+------+-------+------+---------+---------+-----------+---------------+----
------+---------------------+
1 row in set (0.00 sec)
举例2：
use test;
desc stu;
insert into stu(sid,sname,sage,sgender,saddr,stel,sqq,intime)
values(1,'zs',18,'M','bj','110','12345',now());
多行插入
insert into stu(sid,sname,sage,sgender,saddr,stel,sqq,intime)
values
(2,'zs',18,'M','bj','119','1234125',now()),
(3,'w5',18,'M','bj','120','12345545',now()),
(4,'m6',18,'M','bj','130','1237745',now());
insert into stu
values
(12,'zs1',18,'M','bj','1191','12341252',now()),
(23,'w52',18,'M','bj','1202','123455453',now()),
(34,'m63',18,'M','bj','1303','12377455',now());
insert into stu(sname,saddr,stel,sqq)
values('ss','sh','12123','1235123452');
select * from stu;
举例3:
mysql> create table t1 (id int ,name varchar(64));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1,'zs');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(id,name) values(1,'zs');
Query OK, 1 row affected (0.01 sec)
mysql> insert ignore into t2(id,name) values(2,'ls'),(3,'w5');
Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | zs |
| 2 | ls |
| 3 | w5 |
+----+------+
mysql> insert ignore into t2 set id=4,name='ss' ; 如果有重复值插入，如果有就跳过。
mysql> insert into t1 select * from t2;
mysql> replace into 表名 values ... ；如果有重复值插入，如果有就替换。
3 update
update TABLE_NAME SET xxx=xxx where...;
select * from stu;
update stu set sname='m64' where sid=34;
mysql> show variables like '%update%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | OFF |
| log_slave_updates | ON |
| low_priority_updates | OFF |
| sql_safe_updates | OFF |
+-----------------------------------------+-------+
set global sql_safe_updates=1;
退出重连
show variables like '%update%';
mysql> update stu set sname='m66';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
注意点： 更新列的个数，做where条件时的列必须有索引，或者加limit走主键索引。
4 delete
## delete
delete from stu where sid=4;
select * from stu;
1.面试题 ： 1亿数据，drop table truncate table delete from table 有什么区别？快慢？
### 速度： delete < drop < truncate
### delete 是逐行打标记
### drop : 表定义删除, 删除ibd(操作系统rm)
### trucate : 保留表定义，清空表空间
2.面试题 ：使用update进行替代delete，伪删除（状态列的使用is_deleted）
### 1. 添加状态列 ，设定默认值
alter table stu add column is_deleted tinyint not null default 0;
desc stu;
select * from stu;
### 2. update 替代delete
delete from stu where id=1; 替代为 update stu set is_deleted=1 where sid=1;
commit;
### 3. 业务查询语句修改
select * from stu; 替换为 select * from stu where is_deleted=0;
5 select查询参数值
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+------------------------+
| @@datadir |
+------------------------+
| /data/mysql/data_3306/ |
+------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
mysql> show variables like '%trx%' \G
*************************** 1. row ***************************
Variable_name: innodb_api_trx_level
Value: 0
*************************** 2. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 1
2 rows in set (0.00 sec)
mysql>
6 select查询函数
select current_user()
mysql> select pi()*2*10;
+-----------+
| pi()*2*10 |
+-----------+
| 62.831853 |
+-----------+
1 row in set (0.00 sec)
mysql> select pi()*2*10 from dual;
7 select 单表查询
—7.1 多子句的执行顺序
select
from 从哪来
where 过滤条件
group by 分组条件
select_list 列条件
having 后过滤条件
order by 排序条件
limit 分页
-- from子句应用
—7.2 select 配合 from 使用
### （生产中谨慎使用）
a. 查询整表数据
use world;
desc city;
###获取世界上所有城市信息
SELECT * FROM city;
SELECT id,NAME,coutrycode,District ,population FROM city;
b. 查询整表部分列数据
###获取世界上所有城市名和人口数
SELECT NAME,population FROM city;
—7.3 select...from...where 比较判断及过滤
where = > < >= <= between and != in not in 比较判断使用，一般使用在数字列或者时间列上较多
### 查询中国所有的城市
select * from city where countrycode='CHN';
select * from city where population<100;
select * from city where population between 1000000 and 2000000;
SELECT * FROM city WHERE countrycode != 'CHN' ;
SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
SELECT * FROM city WHERE Population>=1000000 AND Population<=2000000;
SELECT * FROM city WHERE Population BETWEEN 1000000 AND 2000000;
and or in
### 查询中国广东省的城市信息
select * from city where countrycode='CHN' and district='guangdong';
### 查询中国或美国的城市
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' ;
UNION和UNION ALL 差别，union 是要去重复的。
说明： union all 语句在索引合理的时候，比in性能好一些 如果说，在索引不合理的时候，需要多次查同一表时，性能可能不如in。
### WHERE + LIKE 使用（字符串列）
### 查询国家代号，是CH开头的城市信息
select * from city where countrycode like 'CH%‘;
—7.4 select...group by聚合操作
### group by + 聚合函数 （count()、sum()、avg()、max()、min()、group_concat()）
### 统计每个国家的城市个数
select countrycode,count(*) from city group by countrycode;
### 统计每个国家的人口总数
select countrycode,sum(population) from city group by countrycode;
### 统计一下中国每个省的总人口
SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
### 统计每个国家的城市个数 ，城市名列表
select countrycode,count(*), group_concat(name) from city group by countrycode;
执行逻辑：
1. 按照group by条件进行排序
2. 去重复
3. 聚合列
### 5.7 版本之后，sql_mode=only_full_group，要求select_list中的列，要么在group by，要么在聚合函数中。
Error Code: 1055. Expression #3 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
—7.5 select...group by...having 过滤使用
having 使用，类似于where，需要在group by 聚合函数后过滤
### 统计每个国家的城市个数，大于100个的国家过滤出来
select countrycode,count(*) from city
group by countrycode
having count(*)>100;
having条件不走索引。
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000;
—7.6 select...group by...having...order by 排序
### 查询全球城市人口从小到大排序列表
select * from city order by population desc ;
### 统计每个国家的城市个数超过100的总人口从小到大排序
select countrycode,count(*) from city
group by countrycode
having count(*)>100
order by count(*);
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来，总人口从大到小排序
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC ;
### 查询中国所有城市人口数，按从大到小排序
SELECT NAME ,population FROM city
WHERE countrycode='CHN'
ORDER BY population DESC;
—7.7 select...group by...having...order by...limit选择显示
## limit ,一般是配合order by 一起使用才有意义
select * from city order by population desc limit 10 offset 0;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 5 OFFSET 1;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 1,5;
两种写法： ## limit N offset M
limit 3 offset 5 只显示3行跳过5行（6-8行） ## limit M,N
limit 5,3 跳过5行只显示3行（6-8行）
8.0 新姿势： 倒序索引
select * from t1
order by a asc , b desc;
idx_a_b(a,b) ===== > idx(a asc,b desc)
8 多表查询
—8.1 内连接
mysql> select * from a;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from b;
+------+------+--------+
| bid | addr | telnum |
+------+------+--------+
| 1 | bj | 110 |
| 2 | sh | 119 |
| 3 | sz | 120 |
| 4 | cq | 122 |
| 5 | bj | 190 |
+------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from a join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
+------+------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select a.* from a join b on a.id=b.bid;走索引优化查询
mysql> select * from a where id not in (select bid from b );
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
mysql> select a.id,a.name from a left join b on a.id=b.bid where b.bid is
null;
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
-- 多表连接工作逻辑
1. 优化器，在内连接中,选择驱动表
1) 多张表时，从左到右，查看表中索引情况，有索引选为驱动表
2）如果第一张没有，直到找到第一只张表有索引的表作为驱动表
3）如果都没有，则选择行数少的表
2. 多表连接算法
SNLJ
BNLJ
BKAJ
3. 优化重点
强制“小表”为驱动表（left）
建立合适的索引
开启优化算法
set global optimizer_switch='batched_key_access=ON,mrr_cost_based=off';
—8.2 左外连接
mysql> select * from a left join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| 11 | aa | NULL | NULL | NULL |
| 22 | bb | NULL | NULL | NULL |
| 32 | cc | NULL | NULL | NULL |
+------+------+------+------+--------+
6 rows in set (0.01 sec)
—8.3 右外连接
mysql> select * from a right join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| NULL | NULL | 4 | cq | 122 |
| NULL | NULL | 5 | bj | 190 |
+------+------+------+------+--------+
5 rows in set (0.00 sec)
—8.4 全连接(笛卡尔乘积)
mysql> select * from a join b ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 5 | bj | 190 |
| 1 | a | 4 | cq | 122 |
| 1 | a | 3 | sz | 120 |
| 1 | a | 2 | sh | 119 |
| 1 | a | 1 | bj | 110 |
| 2 | b | 5 | bj | 190 |
| 2 | b | 4 | cq | 122 |
| 2 | b | 3 | sz | 120 |
| 2 | b | 2 | sh | 119 |
| 2 | b | 1 | bj | 110 |
| 3 | c | 5 | bj | 190 |
| 3 | c | 4 | cq | 122 |
| 3 | c | 3 | sz | 120 |
| 3 | c | 2 | sh | 119 |
| 3 | c | 1 | bj | 110 |
| 11 | aa | 5 | bj | 190 |
| 11 | aa | 4 | cq | 122 |
| 11 | aa | 3 | sz | 120 |
| 11 | aa | 2 | sh | 119 |
| 11 | aa | 1 | bj | 110 |
| 22 | bb | 5 | bj | 190 |
| 22 | bb | 4 | cq | 122 |
| 22 | bb | 3 | sz | 120 |
| 22 | bb | 2 | sh | 119 |
| 22 | bb | 1 | bj | 110 |
| 32 | cc | 5 | bj | 190 |
| 32 | cc | 4 | cq | 122 |
| 32 | cc | 3 | sz | 120 |
| 32 | cc | 2 | sh | 119 |
| 32 | cc | 1 | bj | 110 |
+------+------+------+------+--------+
30 rows in set (0.00 sec)
—8.5 多表连接练习使用
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);
1. 查询每个老师教了哪些课程
一共有四张表teacher course student sc
#a. 分析题目，找到相关表
## teacher course
#b . 找关联条件
## teacher.tno = course.tno
#c. 组合select 语句
## select teacher.tname ,course.cname from teacher join course on teacher.tno = course.tno;
2. 查询zhang3,学习的课程名。
select student.sname,course.cname
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
3. 统计每个学生学习课程的门数
select student.sname,count(*)
from student
join sc
on student.sno=sc.sno
group by student.sno
4. 统计每个学生学习课程的名称
select student.sname,group_concat(course.cname)
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
group by student.sno
—8.6 别名
列别名： 在having和order by子句中可以调用
select user as '用户' ,host as '白名单' from mysql.user;
use world;
select countrycode CC,count(*) CU from city group by countrycode having CU>100 order by CU;
表别名： 在任意子句中调用表名时，都可以使用别名替代
select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno
改为：
select a.sname,group_concat(c.cname)
from student as a
join sc as b
on a.sno=b.sno
join course as c
on b.cno=c.cno
group by a.sno;
—8.7 子查询
3种类型：
select from ()
select from join ()
select from where [=/in/exists/not in] ()
子查询查询方式，是先执行小括号内部再执行括号外的查询。
a.查询人口数量少于100人的城市： 城市名，人口数，国家名，国土面积
use world;
先查出城市编号
select countrycode from city where population<100;
再嵌套,下面语句不走索引
select code,name,SurfaceArea from country
where code=(select countrycode from city where population<100);
优化走索引
select country.code,country.name,country.SurfaceArea
from country join city
on country.code = city.countrycode
where city.population < 100;
其它案例：
子查询示例1：
select xxx from (subquery) # 杜绝
SELECT a,sum_po FROM
( SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district ) as t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
拆分：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district;
SELECT a,sum_po FROM
t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
组合（性能未做优化，只是简写）：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN'
GROUP BY district
having sum_po>5000000
order by sum_po;
子查询示例2：
select xxx from where id = > in exist ... (subquery) # join替换
SELECT t2.name, t2.`SurfaceArea` FROM country AS t2
WHERE t2.code = (SELECT a.countrycode AS c3 FROM city AS a
WHERE a.population<100) ;
改写：
select t2.name, t2.`SurfaceArea` from country as t2
right join city as t2
where t2.population<100;
select name from hosts
where hostid =
(
select hostid from items as a,
(select itemid,min(value)
from history_uint
where itemid in (1,2,.5) as b
where a.itemid = b.itemid
);
再此改写：
select name from hosts where hostid = (select hostid from items as a,(select
itemid,min(value) from history_uint where itemid in (select itemid from items
where name = 'Available memory') and from_unixtime(clock) like '{}%') as b where
a.itemid = b.itemid)
改写为：
select c.name
from hosts as c
join history_uint b
on c.hostid=b.itemid
join itemid c
on b.itemid=c.itemid
where from_unixtime(b.clock) like '{}%'
DELETE FROM tb_member_product_extend WHERE member_id IN
( SELECT member_id FROM
( SELECT * FROM `tb_member_product_extend` WHERE id not IN
( SELECT min(id) FROM `tb_member_product_extend` GROUP BY `member_id`)
) t
)
—8.8 union 和union all
查询中国或美国的城市信息
select * from city where countrycode in ('CHN','USA');
改写：
# in or ===> 转换为union all ,来达到优化的目的
# 执行计划，in 是range 扫描 ， = 使用的是等值匹配 ref
# 对于查询条件重复值少的时候，优化效果比较明显；重复值多，优化效果可能会变差。
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
union 和 union all 的区别？
#union 会去重复 ，需要使用临时表，进行排序去重复。
压测看效果：
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city where countrycode in ('CHN','USA');" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.121 seconds
Minimum number of seconds to run all queries: 1.121 seconds
Maximum number of seconds to run all queries: 1.121 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query=" select * from city where countrycode='CHN' union all select * from city where countrycode='USA';" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.139 seconds
Minimum number of seconds to run all queries: 1.139 seconds
Maximum number of seconds to run all queries: 1.139 seconds
Number of clients running queries: 100
Average number of queries per client: 20
可以看出改写后的优化效果不明显，还需要根据系统及业务做优化。
—2.1 语法
按照对应好的列，对每个列插入值。 语法： insert into table_name(colname1,colname2,...) values(col1_value,col2_value,...);
—2.2 操作演示
举例1：
use test;
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`sid` CHAR(10) NOT NULL COMMENT '学号',
`sname` VARCHAR(128) NOT NULL COMMENT '姓名',
`sage` TINYINT(3) UNSIGNED NOT NULL DEFAULT '18' COMMENT '年龄',
`sgender` TINYINT(3) UNSIGNED NOT NULL DEFAULT '2' COMMENT '性别',
`scardid` CHAR(18) NOT NULL COMMENT '身份证号',
`saddr` ENUM('北京市','上海市','天津市','重庆市') NOT NULL COMMENT '省份',
`shobby` SET('足球','篮球','羽毛','排球','保龄','乒乓') DEFAULT NULL COMMENT '爱好',
`smoney` DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '学费',
`sdate` DATETIME NOT NULL COMMENT '入学时间',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
desc student;
insert into student(sid,sname,scardid,saddr,shobby,smoney,sdate)
values(1001,'zs','1234567','北京市','足球,排球',10000.11,now());
show tables;
desc student;
insert into student(sid,sname,scardid,saddr,shobby,smoney,sdate)
values(1001,'zs','1234567','北京市','足球,排球',10000.11,now());
mysql> select * from student;
+----+------+-------+------+---------+---------+-----------+---------------+----
------+---------------------+
| id | sid | sname | sage | sgender | scardid | saddr | shobby |
smoney | sdate |
+----+------+-------+------+---------+---------+-----------+---------------+----
------+---------------------+
| 1 | 1001 | zs | 18 | 2 | 1234567 | 北京市 | 足球,排球 |
10000.11 | 2020-01-11 10:14:55 |
+----+------+-------+------+---------+---------+-----------+---------------+----
------+---------------------+
1 row in set (0.00 sec)
举例2：
use test;
desc stu;
insert into stu(sid,sname,sage,sgender,saddr,stel,sqq,intime)
values(1,'zs',18,'M','bj','110','12345',now());
多行插入
insert into stu(sid,sname,sage,sgender,saddr,stel,sqq,intime)
values
(2,'zs',18,'M','bj','119','1234125',now()),
(3,'w5',18,'M','bj','120','12345545',now()),
(4,'m6',18,'M','bj','130','1237745',now());
insert into stu
values
(12,'zs1',18,'M','bj','1191','12341252',now()),
(23,'w52',18,'M','bj','1202','123455453',now()),
(34,'m63',18,'M','bj','1303','12377455',now());
insert into stu(sname,saddr,stel,sqq)
values('ss','sh','12123','1235123452');
select * from stu;
举例3:
mysql> create table t1 (id int ,name varchar(64));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1,'zs');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(id,name) values(1,'zs');
Query OK, 1 row affected (0.01 sec)
mysql> insert ignore into t2(id,name) values(2,'ls'),(3,'w5');
Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | zs |
| 2 | ls |
| 3 | w5 |
+----+------+
mysql> insert ignore into t2 set id=4,name='ss' ; 如果有重复值插入，如果有就跳过。
mysql> insert into t1 select * from t2;
mysql> replace into 表名 values ... ；如果有重复值插入，如果有就替换。
3 update
update TABLE_NAME SET xxx=xxx where...;
select * from stu;
update stu set sname='m64' where sid=34;
mysql> show variables like '%update%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | OFF |
| log_slave_updates | ON |
| low_priority_updates | OFF |
| sql_safe_updates | OFF |
+-----------------------------------------+-------+
set global sql_safe_updates=1;
退出重连
show variables like '%update%';
mysql> update stu set sname='m66';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
注意点： 更新列的个数，做where条件时的列必须有索引，或者加limit走主键索引。
4 delete
## delete
delete from stu where sid=4;
select * from stu;
1.面试题 ： 1亿数据，drop table truncate table delete from table 有什么区别？快慢？
### 速度： delete < drop < truncate
### delete 是逐行打标记
### drop : 表定义删除, 删除ibd(操作系统rm)
### trucate : 保留表定义，清空表空间
2.面试题 ：使用update进行替代delete，伪删除（状态列的使用is_deleted）
### 1. 添加状态列 ，设定默认值
alter table stu add column is_deleted tinyint not null default 0;
desc stu;
select * from stu;
### 2. update 替代delete
delete from stu where id=1; 替代为 update stu set is_deleted=1 where sid=1;
commit;
### 3. 业务查询语句修改
select * from stu; 替换为 select * from stu where is_deleted=0;
5 select查询参数值
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+------------------------+
| @@datadir |
+------------------------+
| /data/mysql/data_3306/ |
+------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
mysql> show variables like '%trx%' \G
*************************** 1. row ***************************
Variable_name: innodb_api_trx_level
Value: 0
*************************** 2. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 1
2 rows in set (0.00 sec)
mysql>
6 select查询函数
select current_user()
mysql> select pi()*2*10;
+-----------+
| pi()*2*10 |
+-----------+
| 62.831853 |
+-----------+
1 row in set (0.00 sec)
mysql> select pi()*2*10 from dual;
7 select 单表查询
—7.1 多子句的执行顺序
select
from 从哪来
where 过滤条件
group by 分组条件
select_list 列条件
having 后过滤条件
order by 排序条件
limit 分页
-- from子句应用
—7.2 select 配合 from 使用
### （生产中谨慎使用）
a. 查询整表数据
use world;
desc city;
###获取世界上所有城市信息
SELECT * FROM city;
SELECT id,NAME,coutrycode,District ,population FROM city;
b. 查询整表部分列数据
###获取世界上所有城市名和人口数
SELECT NAME,population FROM city;
—7.3 select...from...where 比较判断及过滤
where = > < >= <= between and != in not in 比较判断使用，一般使用在数字列或者时间列上较多
### 查询中国所有的城市
select * from city where countrycode='CHN';
select * from city where population<100;
select * from city where population between 1000000 and 2000000;
SELECT * FROM city WHERE countrycode != 'CHN' ;
SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
SELECT * FROM city WHERE Population>=1000000 AND Population<=2000000;
SELECT * FROM city WHERE Population BETWEEN 1000000 AND 2000000;
and or in
### 查询中国广东省的城市信息
select * from city where countrycode='CHN' and district='guangdong';
### 查询中国或美国的城市
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' ;
UNION和UNION ALL 差别，union 是要去重复的。
说明： union all 语句在索引合理的时候，比in性能好一些 如果说，在索引不合理的时候，需要多次查同一表时，性能可能不如in。
### WHERE + LIKE 使用（字符串列）
### 查询国家代号，是CH开头的城市信息
select * from city where countrycode like 'CH%‘;
—7.4 select...group by聚合操作
### group by + 聚合函数 （count()、sum()、avg()、max()、min()、group_concat()）
### 统计每个国家的城市个数
select countrycode,count(*) from city group by countrycode;
### 统计每个国家的人口总数
select countrycode,sum(population) from city group by countrycode;
### 统计一下中国每个省的总人口
SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
### 统计每个国家的城市个数 ，城市名列表
select countrycode,count(*), group_concat(name) from city group by countrycode;
执行逻辑：
1. 按照group by条件进行排序
2. 去重复
3. 聚合列
### 5.7 版本之后，sql_mode=only_full_group，要求select_list中的列，要么在group by，要么在聚合函数中。
Error Code: 1055. Expression #3 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
—7.5 select...group by...having 过滤使用
having 使用，类似于where，需要在group by 聚合函数后过滤
### 统计每个国家的城市个数，大于100个的国家过滤出来
select countrycode,count(*) from city
group by countrycode
having count(*)>100;
having条件不走索引。
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000;
—7.6 select...group by...having...order by 排序
### 查询全球城市人口从小到大排序列表
select * from city order by population desc ;
### 统计每个国家的城市个数超过100的总人口从小到大排序
select countrycode,count(*) from city
group by countrycode
having count(*)>100
order by count(*);
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来，总人口从大到小排序
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC ;
### 查询中国所有城市人口数，按从大到小排序
SELECT NAME ,population FROM city
WHERE countrycode='CHN'
ORDER BY population DESC;
—7.7 select...group by...having...order by...limit选择显示
## limit ,一般是配合order by 一起使用才有意义
select * from city order by population desc limit 10 offset 0;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 5 OFFSET 1;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 1,5;
两种写法： ## limit N offset M
limit 3 offset 5 只显示3行跳过5行（6-8行） ## limit M,N
limit 5,3 跳过5行只显示3行（6-8行）
8.0 新姿势： 倒序索引
select * from t1
order by a asc , b desc;
idx_a_b(a,b) ===== > idx(a asc,b desc)
8 多表查询
—8.1 内连接
mysql> select * from a;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from b;
+------+------+--------+
| bid | addr | telnum |
+------+------+--------+
| 1 | bj | 110 |
| 2 | sh | 119 |
| 3 | sz | 120 |
| 4 | cq | 122 |
| 5 | bj | 190 |
+------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from a join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
+------+------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select a.* from a join b on a.id=b.bid;走索引优化查询
mysql> select * from a where id not in (select bid from b );
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
mysql> select a.id,a.name from a left join b on a.id=b.bid where b.bid is
null;
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
-- 多表连接工作逻辑
1. 优化器，在内连接中,选择驱动表
1) 多张表时，从左到右，查看表中索引情况，有索引选为驱动表
2）如果第一张没有，直到找到第一只张表有索引的表作为驱动表
3）如果都没有，则选择行数少的表
2. 多表连接算法
SNLJ
BNLJ
BKAJ
3. 优化重点
强制“小表”为驱动表（left）
建立合适的索引
开启优化算法
set global optimizer_switch='batched_key_access=ON,mrr_cost_based=off';
—8.2 左外连接
mysql> select * from a left join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| 11 | aa | NULL | NULL | NULL |
| 22 | bb | NULL | NULL | NULL |
| 32 | cc | NULL | NULL | NULL |
+------+------+------+------+--------+
6 rows in set (0.01 sec)
—8.3 右外连接
mysql> select * from a right join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| NULL | NULL | 4 | cq | 122 |
| NULL | NULL | 5 | bj | 190 |
+------+------+------+------+--------+
5 rows in set (0.00 sec)
—8.4 全连接(笛卡尔乘积)
mysql> select * from a join b ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 5 | bj | 190 |
| 1 | a | 4 | cq | 122 |
| 1 | a | 3 | sz | 120 |
| 1 | a | 2 | sh | 119 |
| 1 | a | 1 | bj | 110 |
| 2 | b | 5 | bj | 190 |
| 2 | b | 4 | cq | 122 |
| 2 | b | 3 | sz | 120 |
| 2 | b | 2 | sh | 119 |
| 2 | b | 1 | bj | 110 |
| 3 | c | 5 | bj | 190 |
| 3 | c | 4 | cq | 122 |
| 3 | c | 3 | sz | 120 |
| 3 | c | 2 | sh | 119 |
| 3 | c | 1 | bj | 110 |
| 11 | aa | 5 | bj | 190 |
| 11 | aa | 4 | cq | 122 |
| 11 | aa | 3 | sz | 120 |
| 11 | aa | 2 | sh | 119 |
| 11 | aa | 1 | bj | 110 |
| 22 | bb | 5 | bj | 190 |
| 22 | bb | 4 | cq | 122 |
| 22 | bb | 3 | sz | 120 |
| 22 | bb | 2 | sh | 119 |
| 22 | bb | 1 | bj | 110 |
| 32 | cc | 5 | bj | 190 |
| 32 | cc | 4 | cq | 122 |
| 32 | cc | 3 | sz | 120 |
| 32 | cc | 2 | sh | 119 |
| 32 | cc | 1 | bj | 110 |
+------+------+------+------+--------+
30 rows in set (0.00 sec)
—8.5 多表连接练习使用
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);
1. 查询每个老师教了哪些课程
一共有四张表teacher course student sc
#a. 分析题目，找到相关表
## teacher course
#b . 找关联条件
## teacher.tno = course.tno
#c. 组合select 语句
## select teacher.tname ,course.cname from teacher join course on teacher.tno = course.tno;
2. 查询zhang3,学习的课程名。
select student.sname,course.cname
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
3. 统计每个学生学习课程的门数
select student.sname,count(*)
from student
join sc
on student.sno=sc.sno
group by student.sno
4. 统计每个学生学习课程的名称
select student.sname,group_concat(course.cname)
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
group by student.sno
—8.6 别名
列别名： 在having和order by子句中可以调用
select user as '用户' ,host as '白名单' from mysql.user;
use world;
select countrycode CC,count(*) CU from city group by countrycode having CU>100 order by CU;
表别名： 在任意子句中调用表名时，都可以使用别名替代
select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno
改为：
select a.sname,group_concat(c.cname)
from student as a
join sc as b
on a.sno=b.sno
join course as c
on b.cno=c.cno
group by a.sno;
—8.7 子查询
3种类型：
select from ()
select from join ()
select from where [=/in/exists/not in] ()
子查询查询方式，是先执行小括号内部再执行括号外的查询。
a.查询人口数量少于100人的城市： 城市名，人口数，国家名，国土面积
use world;
先查出城市编号
select countrycode from city where population<100;
再嵌套,下面语句不走索引
select code,name,SurfaceArea from country
where code=(select countrycode from city where population<100);
优化走索引
select country.code,country.name,country.SurfaceArea
from country join city
on country.code = city.countrycode
where city.population < 100;
其它案例：
子查询示例1：
select xxx from (subquery) # 杜绝
SELECT a,sum_po FROM
( SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district ) as t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
拆分：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district;
SELECT a,sum_po FROM
t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
组合（性能未做优化，只是简写）：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN'
GROUP BY district
having sum_po>5000000
order by sum_po;
子查询示例2：
select xxx from where id = > in exist ... (subquery) # join替换
SELECT t2.name, t2.`SurfaceArea` FROM country AS t2
WHERE t2.code = (SELECT a.countrycode AS c3 FROM city AS a
WHERE a.population<100) ;
改写：
select t2.name, t2.`SurfaceArea` from country as t2
right join city as t2
where t2.population<100;
select name from hosts
where hostid =
(
select hostid from items as a,
(select itemid,min(value)
from history_uint
where itemid in (1,2,.5) as b
where a.itemid = b.itemid
);
再此改写：
select name from hosts where hostid = (select hostid from items as a,(select
itemid,min(value) from history_uint where itemid in (select itemid from items
where name = 'Available memory') and from_unixtime(clock) like '{}%') as b where
a.itemid = b.itemid)
改写为：
select c.name
from hosts as c
join history_uint b
on c.hostid=b.itemid
join itemid c
on b.itemid=c.itemid
where from_unixtime(b.clock) like '{}%'
DELETE FROM tb_member_product_extend WHERE member_id IN
( SELECT member_id FROM
( SELECT * FROM `tb_member_product_extend` WHERE id not IN
( SELECT min(id) FROM `tb_member_product_extend` GROUP BY `member_id`)
) t
)
—8.8 union 和union all
查询中国或美国的城市信息
select * from city where countrycode in ('CHN','USA');
改写：
# in or ===> 转换为union all ,来达到优化的目的
# 执行计划，in 是range 扫描 ， = 使用的是等值匹配 ref
# 对于查询条件重复值少的时候，优化效果比较明显；重复值多，优化效果可能会变差。
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
union 和 union all 的区别？
#union 会去重复 ，需要使用临时表，进行排序去重复。
压测看效果：
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city where countrycode in ('CHN','USA');" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.121 seconds
Minimum number of seconds to run all queries: 1.121 seconds
Maximum number of seconds to run all queries: 1.121 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query=" select * from city where countrycode='CHN' union all select * from city where countrycode='USA';" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.139 seconds
Minimum number of seconds to run all queries: 1.139 seconds
Maximum number of seconds to run all queries: 1.139 seconds
Number of clients running queries: 100
Average number of queries per client: 20
可以看出改写后的优化效果不明显，还需要根据系统及业务做优化。
## delete delete from stu where sid=4; select * from stu;
1.面试题 ： 1亿数据，drop table truncate table delete from table 有什么区别？快慢？ ### 速度： delete < drop < truncate ### delete 是逐行打标记 ### drop : 表定义删除, 删除ibd(操作系统rm) ### trucate : 保留表定义，清空表空间 2.面试题 ：使用update进行替代delete，伪删除（状态列的使用is_deleted） ### 1. 添加状态列 ，设定默认值 alter table stu add column is_deleted tinyint not null default 0; desc stu; select * from stu; ### 2. update 替代delete delete from stu where id=1; 替代为 update stu set is_deleted=1 where sid=1; commit; ### 3. 业务查询语句修改 select * from stu; 替换为 select * from stu where is_deleted=0;
5 select查询参数值
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+------------------------+
| @@datadir |
+------------------------+
| /data/mysql/data_3306/ |
+------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
mysql> show variables like '%trx%' \G
*************************** 1. row ***************************
Variable_name: innodb_api_trx_level
Value: 0
*************************** 2. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 1
2 rows in set (0.00 sec)
mysql>
6 select查询函数
select current_user()
mysql> select pi()*2*10;
+-----------+
| pi()*2*10 |
+-----------+
| 62.831853 |
+-----------+
1 row in set (0.00 sec)
mysql> select pi()*2*10 from dual;
7 select 单表查询
—7.1 多子句的执行顺序
select
from 从哪来
where 过滤条件
group by 分组条件
select_list 列条件
having 后过滤条件
order by 排序条件
limit 分页
-- from子句应用
—7.2 select 配合 from 使用
### （生产中谨慎使用）
a. 查询整表数据
use world;
desc city;
###获取世界上所有城市信息
SELECT * FROM city;
SELECT id,NAME,coutrycode,District ,population FROM city;
b. 查询整表部分列数据
###获取世界上所有城市名和人口数
SELECT NAME,population FROM city;
—7.3 select...from...where 比较判断及过滤
where = > < >= <= between and != in not in 比较判断使用，一般使用在数字列或者时间列上较多
### 查询中国所有的城市
select * from city where countrycode='CHN';
select * from city where population<100;
select * from city where population between 1000000 and 2000000;
SELECT * FROM city WHERE countrycode != 'CHN' ;
SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
SELECT * FROM city WHERE Population>=1000000 AND Population<=2000000;
SELECT * FROM city WHERE Population BETWEEN 1000000 AND 2000000;
and or in
### 查询中国广东省的城市信息
select * from city where countrycode='CHN' and district='guangdong';
### 查询中国或美国的城市
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' ;
UNION和UNION ALL 差别，union 是要去重复的。
说明： union all 语句在索引合理的时候，比in性能好一些 如果说，在索引不合理的时候，需要多次查同一表时，性能可能不如in。
### WHERE + LIKE 使用（字符串列）
### 查询国家代号，是CH开头的城市信息
select * from city where countrycode like 'CH%‘;
—7.4 select...group by聚合操作
### group by + 聚合函数 （count()、sum()、avg()、max()、min()、group_concat()）
### 统计每个国家的城市个数
select countrycode,count(*) from city group by countrycode;
### 统计每个国家的人口总数
select countrycode,sum(population) from city group by countrycode;
### 统计一下中国每个省的总人口
SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
### 统计每个国家的城市个数 ，城市名列表
select countrycode,count(*), group_concat(name) from city group by countrycode;
执行逻辑：
1. 按照group by条件进行排序
2. 去重复
3. 聚合列
### 5.7 版本之后，sql_mode=only_full_group，要求select_list中的列，要么在group by，要么在聚合函数中。
Error Code: 1055. Expression #3 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
—7.5 select...group by...having 过滤使用
having 使用，类似于where，需要在group by 聚合函数后过滤
### 统计每个国家的城市个数，大于100个的国家过滤出来
select countrycode,count(*) from city
group by countrycode
having count(*)>100;
having条件不走索引。
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000;
—7.6 select...group by...having...order by 排序
### 查询全球城市人口从小到大排序列表
select * from city order by population desc ;
### 统计每个国家的城市个数超过100的总人口从小到大排序
select countrycode,count(*) from city
group by countrycode
having count(*)>100
order by count(*);
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来，总人口从大到小排序
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC ;
### 查询中国所有城市人口数，按从大到小排序
SELECT NAME ,population FROM city
WHERE countrycode='CHN'
ORDER BY population DESC;
—7.7 select...group by...having...order by...limit选择显示
## limit ,一般是配合order by 一起使用才有意义
select * from city order by population desc limit 10 offset 0;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 5 OFFSET 1;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 1,5;
两种写法： ## limit N offset M
limit 3 offset 5 只显示3行跳过5行（6-8行） ## limit M,N
limit 5,3 跳过5行只显示3行（6-8行）
8.0 新姿势： 倒序索引
select * from t1
order by a asc , b desc;
idx_a_b(a,b) ===== > idx(a asc,b desc)
8 多表查询
—8.1 内连接
mysql> select * from a;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from b;
+------+------+--------+
| bid | addr | telnum |
+------+------+--------+
| 1 | bj | 110 |
| 2 | sh | 119 |
| 3 | sz | 120 |
| 4 | cq | 122 |
| 5 | bj | 190 |
+------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from a join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
+------+------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select a.* from a join b on a.id=b.bid;走索引优化查询
mysql> select * from a where id not in (select bid from b );
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
mysql> select a.id,a.name from a left join b on a.id=b.bid where b.bid is
null;
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
-- 多表连接工作逻辑
1. 优化器，在内连接中,选择驱动表
1) 多张表时，从左到右，查看表中索引情况，有索引选为驱动表
2）如果第一张没有，直到找到第一只张表有索引的表作为驱动表
3）如果都没有，则选择行数少的表
2. 多表连接算法
SNLJ
BNLJ
BKAJ
3. 优化重点
强制“小表”为驱动表（left）
建立合适的索引
开启优化算法
set global optimizer_switch='batched_key_access=ON,mrr_cost_based=off';
—8.2 左外连接
mysql> select * from a left join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| 11 | aa | NULL | NULL | NULL |
| 22 | bb | NULL | NULL | NULL |
| 32 | cc | NULL | NULL | NULL |
+------+------+------+------+--------+
6 rows in set (0.01 sec)
—8.3 右外连接
mysql> select * from a right join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| NULL | NULL | 4 | cq | 122 |
| NULL | NULL | 5 | bj | 190 |
+------+------+------+------+--------+
5 rows in set (0.00 sec)
—8.4 全连接(笛卡尔乘积)
mysql> select * from a join b ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 5 | bj | 190 |
| 1 | a | 4 | cq | 122 |
| 1 | a | 3 | sz | 120 |
| 1 | a | 2 | sh | 119 |
| 1 | a | 1 | bj | 110 |
| 2 | b | 5 | bj | 190 |
| 2 | b | 4 | cq | 122 |
| 2 | b | 3 | sz | 120 |
| 2 | b | 2 | sh | 119 |
| 2 | b | 1 | bj | 110 |
| 3 | c | 5 | bj | 190 |
| 3 | c | 4 | cq | 122 |
| 3 | c | 3 | sz | 120 |
| 3 | c | 2 | sh | 119 |
| 3 | c | 1 | bj | 110 |
| 11 | aa | 5 | bj | 190 |
| 11 | aa | 4 | cq | 122 |
| 11 | aa | 3 | sz | 120 |
| 11 | aa | 2 | sh | 119 |
| 11 | aa | 1 | bj | 110 |
| 22 | bb | 5 | bj | 190 |
| 22 | bb | 4 | cq | 122 |
| 22 | bb | 3 | sz | 120 |
| 22 | bb | 2 | sh | 119 |
| 22 | bb | 1 | bj | 110 |
| 32 | cc | 5 | bj | 190 |
| 32 | cc | 4 | cq | 122 |
| 32 | cc | 3 | sz | 120 |
| 32 | cc | 2 | sh | 119 |
| 32 | cc | 1 | bj | 110 |
+------+------+------+------+--------+
30 rows in set (0.00 sec)
—8.5 多表连接练习使用
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);
1. 查询每个老师教了哪些课程
一共有四张表teacher course student sc
#a. 分析题目，找到相关表
## teacher course
#b . 找关联条件
## teacher.tno = course.tno
#c. 组合select 语句
## select teacher.tname ,course.cname from teacher join course on teacher.tno = course.tno;
2. 查询zhang3,学习的课程名。
select student.sname,course.cname
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
3. 统计每个学生学习课程的门数
select student.sname,count(*)
from student
join sc
on student.sno=sc.sno
group by student.sno
4. 统计每个学生学习课程的名称
select student.sname,group_concat(course.cname)
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
group by student.sno
—8.6 别名
列别名： 在having和order by子句中可以调用
select user as '用户' ,host as '白名单' from mysql.user;
use world;
select countrycode CC,count(*) CU from city group by countrycode having CU>100 order by CU;
表别名： 在任意子句中调用表名时，都可以使用别名替代
select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno
改为：
select a.sname,group_concat(c.cname)
from student as a
join sc as b
on a.sno=b.sno
join course as c
on b.cno=c.cno
group by a.sno;
—8.7 子查询
3种类型：
select from ()
select from join ()
select from where [=/in/exists/not in] ()
子查询查询方式，是先执行小括号内部再执行括号外的查询。
a.查询人口数量少于100人的城市： 城市名，人口数，国家名，国土面积
use world;
先查出城市编号
select countrycode from city where population<100;
再嵌套,下面语句不走索引
select code,name,SurfaceArea from country
where code=(select countrycode from city where population<100);
优化走索引
select country.code,country.name,country.SurfaceArea
from country join city
on country.code = city.countrycode
where city.population < 100;
其它案例：
子查询示例1：
select xxx from (subquery) # 杜绝
SELECT a,sum_po FROM
( SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district ) as t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
拆分：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district;
SELECT a,sum_po FROM
t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
组合（性能未做优化，只是简写）：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN'
GROUP BY district
having sum_po>5000000
order by sum_po;
子查询示例2：
select xxx from where id = > in exist ... (subquery) # join替换
SELECT t2.name, t2.`SurfaceArea` FROM country AS t2
WHERE t2.code = (SELECT a.countrycode AS c3 FROM city AS a
WHERE a.population<100) ;
改写：
select t2.name, t2.`SurfaceArea` from country as t2
right join city as t2
where t2.population<100;
select name from hosts
where hostid =
(
select hostid from items as a,
(select itemid,min(value)
from history_uint
where itemid in (1,2,.5) as b
where a.itemid = b.itemid
);
再此改写：
select name from hosts where hostid = (select hostid from items as a,(select
itemid,min(value) from history_uint where itemid in (select itemid from items
where name = 'Available memory') and from_unixtime(clock) like '{}%') as b where
a.itemid = b.itemid)
改写为：
select c.name
from hosts as c
join history_uint b
on c.hostid=b.itemid
join itemid c
on b.itemid=c.itemid
where from_unixtime(b.clock) like '{}%'
DELETE FROM tb_member_product_extend WHERE member_id IN
( SELECT member_id FROM
( SELECT * FROM `tb_member_product_extend` WHERE id not IN
( SELECT min(id) FROM `tb_member_product_extend` GROUP BY `member_id`)
) t
)
—8.8 union 和union all
查询中国或美国的城市信息
select * from city where countrycode in ('CHN','USA');
改写：
# in or ===> 转换为union all ,来达到优化的目的
# 执行计划，in 是range 扫描 ， = 使用的是等值匹配 ref
# 对于查询条件重复值少的时候，优化效果比较明显；重复值多，优化效果可能会变差。
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
union 和 union all 的区别？
#union 会去重复 ，需要使用临时表，进行排序去重复。
压测看效果：
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city where countrycode in ('CHN','USA');" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.121 seconds
Minimum number of seconds to run all queries: 1.121 seconds
Maximum number of seconds to run all queries: 1.121 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query=" select * from city where countrycode='CHN' union all select * from city where countrycode='USA';" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.139 seconds
Minimum number of seconds to run all queries: 1.139 seconds
Maximum number of seconds to run all queries: 1.139 seconds
Number of clients running queries: 100
Average number of queries per client: 20
可以看出改写后的优化效果不明显，还需要根据系统及业务做优化。
select current_user() mysql> select pi()*2*10; +-----------+ | pi()*2*10 | +-----------+ | 62.831853 | +-----------+ 1 row in set (0.00 sec) mysql> select pi()*2*10 from dual;
7 select 单表查询
—7.1 多子句的执行顺序
select
from 从哪来
where 过滤条件
group by 分组条件
select_list 列条件
having 后过滤条件
order by 排序条件
limit 分页
-- from子句应用
—7.2 select 配合 from 使用
### （生产中谨慎使用）
a. 查询整表数据
use world;
desc city;
###获取世界上所有城市信息
SELECT * FROM city;
SELECT id,NAME,coutrycode,District ,population FROM city;
b. 查询整表部分列数据
###获取世界上所有城市名和人口数
SELECT NAME,population FROM city;
—7.3 select...from...where 比较判断及过滤
where = > < >= <= between and != in not in 比较判断使用，一般使用在数字列或者时间列上较多
### 查询中国所有的城市
select * from city where countrycode='CHN';
select * from city where population<100;
select * from city where population between 1000000 and 2000000;
SELECT * FROM city WHERE countrycode != 'CHN' ;
SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
SELECT * FROM city WHERE Population>=1000000 AND Population<=2000000;
SELECT * FROM city WHERE Population BETWEEN 1000000 AND 2000000;
and or in
### 查询中国广东省的城市信息
select * from city where countrycode='CHN' and district='guangdong';
### 查询中国或美国的城市
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' ;
UNION和UNION ALL 差别，union 是要去重复的。
说明： union all 语句在索引合理的时候，比in性能好一些 如果说，在索引不合理的时候，需要多次查同一表时，性能可能不如in。
### WHERE + LIKE 使用（字符串列）
### 查询国家代号，是CH开头的城市信息
select * from city where countrycode like 'CH%‘;
—7.4 select...group by聚合操作
### group by + 聚合函数 （count()、sum()、avg()、max()、min()、group_concat()）
### 统计每个国家的城市个数
select countrycode,count(*) from city group by countrycode;
### 统计每个国家的人口总数
select countrycode,sum(population) from city group by countrycode;
### 统计一下中国每个省的总人口
SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
### 统计每个国家的城市个数 ，城市名列表
select countrycode,count(*), group_concat(name) from city group by countrycode;
执行逻辑：
1. 按照group by条件进行排序
2. 去重复
3. 聚合列
### 5.7 版本之后，sql_mode=only_full_group，要求select_list中的列，要么在group by，要么在聚合函数中。
Error Code: 1055. Expression #3 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
—7.5 select...group by...having 过滤使用
having 使用，类似于where，需要在group by 聚合函数后过滤
### 统计每个国家的城市个数，大于100个的国家过滤出来
select countrycode,count(*) from city
group by countrycode
having count(*)>100;
having条件不走索引。
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000;
—7.6 select...group by...having...order by 排序
### 查询全球城市人口从小到大排序列表
select * from city order by population desc ;
### 统计每个国家的城市个数超过100的总人口从小到大排序
select countrycode,count(*) from city
group by countrycode
having count(*)>100
order by count(*);
### 统计一下中国每个省的总人口，将总人口超过500w的显示出来，总人口从大到小排序
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC ;
### 查询中国所有城市人口数，按从大到小排序
SELECT NAME ,population FROM city
WHERE countrycode='CHN'
ORDER BY population DESC;
—7.7 select...group by...having...order by...limit选择显示
## limit ,一般是配合order by 一起使用才有意义
select * from city order by population desc limit 10 offset 0;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 5 OFFSET 1;
SELECT district ,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >5000000
ORDER BY SUM(population) DESC
LIMIT 1,5;
两种写法： ## limit N offset M
limit 3 offset 5 只显示3行跳过5行（6-8行） ## limit M,N
limit 5,3 跳过5行只显示3行（6-8行）
8.0 新姿势： 倒序索引
select * from t1
order by a asc , b desc;
idx_a_b(a,b) ===== > idx(a asc,b desc)
8 多表查询
—8.1 内连接
mysql> select * from a;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from b;
+------+------+--------+
| bid | addr | telnum |
+------+------+--------+
| 1 | bj | 110 |
| 2 | sh | 119 |
| 3 | sz | 120 |
| 4 | cq | 122 |
| 5 | bj | 190 |
+------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from a join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
+------+------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select a.* from a join b on a.id=b.bid;走索引优化查询
mysql> select * from a where id not in (select bid from b );
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
mysql> select a.id,a.name from a left join b on a.id=b.bid where b.bid is
null;
+------+------+
| id | name |
+------+------+
| 11 | aa |
| 22 | bb |
| 32 | cc |
+------+------+
-- 多表连接工作逻辑
1. 优化器，在内连接中,选择驱动表
1) 多张表时，从左到右，查看表中索引情况，有索引选为驱动表
2）如果第一张没有，直到找到第一只张表有索引的表作为驱动表
3）如果都没有，则选择行数少的表
2. 多表连接算法
SNLJ
BNLJ
BKAJ
3. 优化重点
强制“小表”为驱动表（left）
建立合适的索引
开启优化算法
set global optimizer_switch='batched_key_access=ON,mrr_cost_based=off';
—8.2 左外连接
mysql> select * from a left join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| 11 | aa | NULL | NULL | NULL |
| 22 | bb | NULL | NULL | NULL |
| 32 | cc | NULL | NULL | NULL |
+------+------+------+------+--------+
6 rows in set (0.01 sec)
—8.3 右外连接
mysql> select * from a right join b on a.id=b.bid ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 1 | bj | 110 |
| 2 | b | 2 | sh | 119 |
| 3 | c | 3 | sz | 120 |
| NULL | NULL | 4 | cq | 122 |
| NULL | NULL | 5 | bj | 190 |
+------+------+------+------+--------+
5 rows in set (0.00 sec)
—8.4 全连接(笛卡尔乘积)
mysql> select * from a join b ;
+------+------+------+------+--------+
| id | name | bid | addr | telnum |
+------+------+------+------+--------+
| 1 | a | 5 | bj | 190 |
| 1 | a | 4 | cq | 122 |
| 1 | a | 3 | sz | 120 |
| 1 | a | 2 | sh | 119 |
| 1 | a | 1 | bj | 110 |
| 2 | b | 5 | bj | 190 |
| 2 | b | 4 | cq | 122 |
| 2 | b | 3 | sz | 120 |
| 2 | b | 2 | sh | 119 |
| 2 | b | 1 | bj | 110 |
| 3 | c | 5 | bj | 190 |
| 3 | c | 4 | cq | 122 |
| 3 | c | 3 | sz | 120 |
| 3 | c | 2 | sh | 119 |
| 3 | c | 1 | bj | 110 |
| 11 | aa | 5 | bj | 190 |
| 11 | aa | 4 | cq | 122 |
| 11 | aa | 3 | sz | 120 |
| 11 | aa | 2 | sh | 119 |
| 11 | aa | 1 | bj | 110 |
| 22 | bb | 5 | bj | 190 |
| 22 | bb | 4 | cq | 122 |
| 22 | bb | 3 | sz | 120 |
| 22 | bb | 2 | sh | 119 |
| 22 | bb | 1 | bj | 110 |
| 32 | cc | 5 | bj | 190 |
| 32 | cc | 4 | cq | 122 |
| 32 | cc | 3 | sz | 120 |
| 32 | cc | 2 | sh | 119 |
| 32 | cc | 1 | bj | 110 |
+------+------+------+------+--------+
30 rows in set (0.00 sec)
—8.5 多表连接练习使用
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);
1. 查询每个老师教了哪些课程
一共有四张表teacher course student sc
#a. 分析题目，找到相关表
## teacher course
#b . 找关联条件
## teacher.tno = course.tno
#c. 组合select 语句
## select teacher.tname ,course.cname from teacher join course on teacher.tno = course.tno;
2. 查询zhang3,学习的课程名。
select student.sname,course.cname
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
3. 统计每个学生学习课程的门数
select student.sname,count(*)
from student
join sc
on student.sno=sc.sno
group by student.sno
4. 统计每个学生学习课程的名称
select student.sname,group_concat(course.cname)
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
group by student.sno
—8.6 别名
列别名： 在having和order by子句中可以调用
select user as '用户' ,host as '白名单' from mysql.user;
use world;
select countrycode CC,count(*) CU from city group by countrycode having CU>100 order by CU;
表别名： 在任意子句中调用表名时，都可以使用别名替代
select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno
改为：
select a.sname,group_concat(c.cname)
from student as a
join sc as b
on a.sno=b.sno
join course as c
on b.cno=c.cno
group by a.sno;
—8.7 子查询
3种类型：
select from ()
select from join ()
select from where [=/in/exists/not in] ()
子查询查询方式，是先执行小括号内部再执行括号外的查询。
a.查询人口数量少于100人的城市： 城市名，人口数，国家名，国土面积
use world;
先查出城市编号
select countrycode from city where population<100;
再嵌套,下面语句不走索引
select code,name,SurfaceArea from country
where code=(select countrycode from city where population<100);
优化走索引
select country.code,country.name,country.SurfaceArea
from country join city
on country.code = city.countrycode
where city.population < 100;
其它案例：
子查询示例1：
select xxx from (subquery) # 杜绝
SELECT a,sum_po FROM
( SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district ) as t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
拆分：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district;
SELECT a,sum_po FROM
t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
组合（性能未做优化，只是简写）：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN'
GROUP BY district
having sum_po>5000000
order by sum_po;
子查询示例2：
select xxx from where id = > in exist ... (subquery) # join替换
SELECT t2.name, t2.`SurfaceArea` FROM country AS t2
WHERE t2.code = (SELECT a.countrycode AS c3 FROM city AS a
WHERE a.population<100) ;
改写：
select t2.name, t2.`SurfaceArea` from country as t2
right join city as t2
where t2.population<100;
select name from hosts
where hostid =
(
select hostid from items as a,
(select itemid,min(value)
from history_uint
where itemid in (1,2,.5) as b
where a.itemid = b.itemid
);
再此改写：
select name from hosts where hostid = (select hostid from items as a,(select
itemid,min(value) from history_uint where itemid in (select itemid from items
where name = 'Available memory') and from_unixtime(clock) like '{}%') as b where
a.itemid = b.itemid)
改写为：
select c.name
from hosts as c
join history_uint b
on c.hostid=b.itemid
join itemid c
on b.itemid=c.itemid
where from_unixtime(b.clock) like '{}%'
DELETE FROM tb_member_product_extend WHERE member_id IN
( SELECT member_id FROM
( SELECT * FROM `tb_member_product_extend` WHERE id not IN
( SELECT min(id) FROM `tb_member_product_extend` GROUP BY `member_id`)
) t
)
—8.8 union 和union all
查询中国或美国的城市信息
select * from city where countrycode in ('CHN','USA');
改写：
# in or ===> 转换为union all ,来达到优化的目的
# 执行计划，in 是range 扫描 ， = 使用的是等值匹配 ref
# 对于查询条件重复值少的时候，优化效果比较明显；重复值多，优化效果可能会变差。
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
union 和 union all 的区别？
#union 会去重复 ，需要使用临时表，进行排序去重复。
压测看效果：
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city where countrycode in ('CHN','USA');" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.121 seconds
Minimum number of seconds to run all queries: 1.121 seconds
Maximum number of seconds to run all queries: 1.121 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query=" select * from city where countrycode='CHN' union all select * from city where countrycode='USA';" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.139 seconds
Minimum number of seconds to run all queries: 1.139 seconds
Maximum number of seconds to run all queries: 1.139 seconds
Number of clients running queries: 100
Average number of queries per client: 20
可以看出改写后的优化效果不明显，还需要根据系统及业务做优化。
—8.1 内连接
mysql> select * from a; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 11 | aa | | 22 | bb | | 32 | cc | +------+------+ 6 rows in set (0.00 sec) mysql> select * from b; +------+------+--------+ | bid | addr | telnum | +------+------+--------+ | 1 | bj | 110 | | 2 | sh | 119 | | 3 | sz | 120 | | 4 | cq | 122 | | 5 | bj | 190 | +------+------+--------+ 5 rows in set (0.00 sec)
mysql> select * from a join b on a.id=b.bid ; +------+------+------+------+--------+ | id | name | bid | addr | telnum | +------+------+------+------+--------+ | 1 | a | 1 | bj | 110 | | 2 | b | 2 | sh | 119 | | 3 | c | 3 | sz | 120 | +------+------+------+------+--------+ 3 rows in set (0.00 sec) mysql> select a.* from a join b on a.id=b.bid;走索引优化查询 mysql> select * from a where id not in (select bid from b ); +------+------+ | id | name | +------+------+ | 11 | aa | | 22 | bb | | 32 | cc | +------+------+ mysql> select a.id,a.name from a left join b on a.id=b.bid where b.bid is null; +------+------+ | id | name | +------+------+ | 11 | aa | | 22 | bb | | 32 | cc | +------+------+ -- 多表连接工作逻辑 1. 优化器，在内连接中,选择驱动表 1) 多张表时，从左到右，查看表中索引情况，有索引选为驱动表 2）如果第一张没有，直到找到第一只张表有索引的表作为驱动表 3）如果都没有，则选择行数少的表 2. 多表连接算法 SNLJ BNLJ BKAJ 3. 优化重点 强制“小表”为驱动表（left） 建立合适的索引 开启优化算法 set global optimizer_switch='batched_key_access=ON,mrr_cost_based=off';
—8.2 左外连接
mysql> select * from a left join b on a.id=b.bid ; +------+------+------+------+--------+ | id | name | bid | addr | telnum | +------+------+------+------+--------+ | 1 | a | 1 | bj | 110 | | 2 | b | 2 | sh | 119 | | 3 | c | 3 | sz | 120 | | 11 | aa | NULL | NULL | NULL | | 22 | bb | NULL | NULL | NULL | | 32 | cc | NULL | NULL | NULL | +------+------+------+------+--------+ 6 rows in set (0.01 sec)
—8.3 右外连接
mysql> select * from a right join b on a.id=b.bid ; +------+------+------+------+--------+ | id | name | bid | addr | telnum | +------+------+------+------+--------+ | 1 | a | 1 | bj | 110 | | 2 | b | 2 | sh | 119 | | 3 | c | 3 | sz | 120 | | NULL | NULL | 4 | cq | 122 | | NULL | NULL | 5 | bj | 190 | +------+------+------+------+--------+ 5 rows in set (0.00 sec)
—8.4 全连接(笛卡尔乘积)
mysql> select * from a join b ; +------+------+------+------+--------+ | id | name | bid | addr | telnum | +------+------+------+------+--------+ | 1 | a | 5 | bj | 190 | | 1 | a | 4 | cq | 122 | | 1 | a | 3 | sz | 120 | | 1 | a | 2 | sh | 119 | | 1 | a | 1 | bj | 110 | | 2 | b | 5 | bj | 190 | | 2 | b | 4 | cq | 122 | | 2 | b | 3 | sz | 120 | | 2 | b | 2 | sh | 119 | | 2 | b | 1 | bj | 110 | | 3 | c | 5 | bj | 190 | | 3 | c | 4 | cq | 122 | | 3 | c | 3 | sz | 120 | | 3 | c | 2 | sh | 119 | | 3 | c | 1 | bj | 110 | | 11 | aa | 5 | bj | 190 | | 11 | aa | 4 | cq | 122 | | 11 | aa | 3 | sz | 120 | | 11 | aa | 2 | sh | 119 | | 11 | aa | 1 | bj | 110 | | 22 | bb | 5 | bj | 190 | | 22 | bb | 4 | cq | 122 | | 22 | bb | 3 | sz | 120 | | 22 | bb | 2 | sh | 119 | | 22 | bb | 1 | bj | 110 | | 32 | cc | 5 | bj | 190 | | 32 | cc | 4 | cq | 122 | | 32 | cc | 3 | sz | 120 | | 32 | cc | 2 | sh | 119 | | 32 | cc | 1 | bj | 110 | +------+------+------+------+--------+ 30 rows in set (0.00 sec)
—8.5 多表连接练习使用
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);
1. 查询每个老师教了哪些课程 一共有四张表teacher course student sc #a. 分析题目，找到相关表 ## teacher course #b . 找关联条件 ## teacher.tno = course.tno #c. 组合select 语句 ## select teacher.tname ,course.cname from teacher join course on teacher.tno = course.tno; 2. 查询zhang3,学习的课程名。 select student.sname,course.cname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3'; 3. 统计每个学生学习课程的门数 select student.sname,count(*) from student join sc on student.sno=sc.sno group by student.sno 4. 统计每个学生学习课程的名称 select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno
—8.6 别名
列别名： 在having和order by子句中可以调用 select user as '用户' ,host as '白名单' from mysql.user; use world; select countrycode CC,count(*) CU from city group by countrycode having CU>100 order by CU; 表别名： 在任意子句中调用表名时，都可以使用别名替代 select student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno 改为： select a.sname,group_concat(c.cname) from student as a join sc as b on a.sno=b.sno join course as c on b.cno=c.cno group by a.sno;
—8.7 子查询
3种类型：
select from ()
select from join ()
select from where [=/in/exists/not in] ()
子查询查询方式，是先执行小括号内部再执行括号外的查询。
a.查询人口数量少于100人的城市： 城市名，人口数，国家名，国土面积
use world;
先查出城市编号
select countrycode from city where population<100;
再嵌套,下面语句不走索引
select code,name,SurfaceArea from country
where code=(select countrycode from city where population<100);
优化走索引
select country.code,country.name,country.SurfaceArea
from country join city
on country.code = city.countrycode
where city.population < 100;
其它案例：
子查询示例1：
select xxx from (subquery) # 杜绝
SELECT a,sum_po FROM
( SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district ) as t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
拆分：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN' GROUP BY district;
SELECT a,sum_po FROM
t1
WHERE t1.sum_po>5000000
ORDER BY sum_po;
组合（性能未做优化，只是简写）：
SELECT district AS a ,SUM(population) AS sum_po
FROM city
WHERE countrycode='CHN'
GROUP BY district
having sum_po>5000000
order by sum_po;
子查询示例2：
select xxx from where id = > in exist ... (subquery) # join替换
SELECT t2.name, t2.`SurfaceArea` FROM country AS t2
WHERE t2.code = (SELECT a.countrycode AS c3 FROM city AS a
WHERE a.population<100) ;
改写：
select t2.name, t2.`SurfaceArea` from country as t2
right join city as t2
where t2.population<100;
select name from hosts
where hostid =
(
select hostid from items as a,
(select itemid,min(value)
from history_uint
where itemid in (1,2,.5) as b
where a.itemid = b.itemid
);
再此改写：
select name from hosts where hostid = (select hostid from items as a,(select
itemid,min(value) from history_uint where itemid in (select itemid from items
where name = 'Available memory') and from_unixtime(clock) like '{}%') as b where
a.itemid = b.itemid)
改写为：
select c.name
from hosts as c
join history_uint b
on c.hostid=b.itemid
join itemid c
on b.itemid=c.itemid
where from_unixtime(b.clock) like '{}%'
DELETE FROM tb_member_product_extend WHERE member_id IN
( SELECT member_id FROM
( SELECT * FROM `tb_member_product_extend` WHERE id not IN
( SELECT min(id) FROM `tb_member_product_extend` GROUP BY `member_id`)
) t
)
—8.8 union 和union all
查询中国或美国的城市信息
select * from city where countrycode in ('CHN','USA');
改写：
# in or ===> 转换为union all ,来达到优化的目的
# 执行计划，in 是range 扫描 ， = 使用的是等值匹配 ref
# 对于查询条件重复值少的时候，优化效果比较明显；重复值多，优化效果可能会变差。
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
union 和 union all 的区别？
#union 会去重复 ，需要使用临时表，进行排序去重复。
压测看效果：
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city where countrycode in ('CHN','USA');" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.121 seconds
Minimum number of seconds to run all queries: 1.121 seconds
Maximum number of seconds to run all queries: 1.121 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query=" select * from city where countrycode='CHN' union all select * from city where countrycode='USA';" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.139 seconds
Minimum number of seconds to run all queries: 1.139 seconds
Maximum number of seconds to run all queries: 1.139 seconds
Number of clients running queries: 100
Average number of queries per client: 20
可以看出改写后的优化效果不明显，还需要根据系统及业务做优化。