数据库内容复习03-MySql的查询

Mar 4, 2019 00:00 · 4416 words · 9 minute read 数据库 SQL MySql 数据库查询

复习数据库方面的内容, 以下为笔记内容, 参考了:
廖雪峰的MySQL教程
 mysql查询语句select-子查询
 MySQL连接查询:子查询
 美团优化实例
…

预备

CREATE TABLE `classes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `students` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `score` DECIMAL NOT NULL,
    `gender` CHAR(1) DEFAULT 'M',
    `class_id` INT(11),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `students` ADD CONSTRAINT `FK_students_classes` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;

INSERT INTO `classes`(`name`) values('一班'), ('二班'), ('三班'), ('四班');
INSERT INTO `students`(`name`, `score`, `gender`, `class_id`) values
        ('Tom', 98.5, 'M', 1), ('Abigail', 86.0, 'F', 1), ('Cara', 96.0, 'F', 1), ('Caroline', 60.0, 'M', 1), ('Beryl', 89.5, 'F', 1), ('Brook', 86.0, 'M', 2), ('Ellen', 66.0, 'F', 2), ('Emma', 80.0, 'F', 2),
        ('Fanny', 50.0, 'F', 3), ('Gail', 80.0, 'M', 3), ('Jodie', 90.0, 'F', 3), ('Nobody', 0.0, 'M', null);

条件查询

SELECT * FROM <表名> WHERE <条件表达式>

常用的条件表达式:

条件	说明	表达式举例
=	判断相等	score = 80 name = ‘abc’ 字符串需要用单引号括起来
>	判断大于	score > 80 name > ‘abc’ 字符串比较根据ASCII码，中文字符比较根据数据库设置
>=	判断大于或相等	score >= 80 name >= ‘abc’
<	判断小于	score < 80 name <= ‘abc’
<=	判断小于或相等	score <= 80 name <= ‘abc’
<> 也可以用 !=	判断不相等(!= 在MySQL中可以使用不过不是标准的SQL)	score <> 80 name <> ‘abc’
LIKE	判断相似	name LIKE ‘ab%’ name LIKE ‘%bc%’ %表示任意字符，例如’ab%‘将匹配’ab’，’abc’，’abcd’

基本实例:

-- 查询分数在60分(含)～90分(含)之间的学生可以使用的WHERE语句：
SELECT * FROM students WHERE score >= 60 OR score <= 90;
-- 或者
SELECT * FROM students WHERE score BETWEEN 60 and 90;

-- 指定查询列
SELECT id, name, score from students WHERE SCORE < 60;

-- 排序
select * from students ORDER BY score;  -- 升序 "ASC"
select * from students ORDER BY score DESC; -- 倒序
select * from students ORDER BY score DESC, gender; -- 先按score倒序排, 如果有相同的score 按 gender 升序排

-- 分页
select * from students ORDER BY score LIMIT 10 OFFSET 0;  -- 每页10条数据, 查询第一页
select * from students ORDER BY score LIMIT 10 OFFSET 10;  -- 每页10条数据, 查询第二页
select * from students ORDER BY score LIMIT 10 OFFSET 20;  -- 每页10条数据, 查询第三页
-- 备注: 在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15

聚合查询

聚合函数:

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值 (如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。)
MIN	计算某一列的最小值 (如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。)

-- 查询students表一共有多少条记录为例
select COUNT(*) from students;
-- +----------+
-- | count(*) |
-- +----------+
-- | 3        |
-- +----------+

-- 使用别名
select COUNT(*) as total from students;
-- +-------+
-- | total |
-- +-------+
-- | 3     |
-- +-------+

-- 计算平均值
select AVG(score) as average from students;

-- 获得总页数(每页10条数据)
select CEILING(COUNT(*)/10) from students;

分组查询(Group By)

-- 统计每一班学生的总数
SELECT class_id, COUNT(*) as total FROM students GROUP BY class_id;

-- 统计每一班平均分数
select class_id, AVG(score) as average from students GROUP BY class_id;

-- 按性别统计每一班学生数
SELECT class_id, gender, COUNT(*) as total FROM students GROUP BY class_id, gender;

多表查询

-- 笛卡尔查询(结果为数量M*N)
select * from students, classes;    -- 结果条数=count(students) * count(classes);

-- 查询结果:
+----+----------+-------+--------+----------+----+------+
| id | name     | score | gender | class_id | id | name |
+----+----------+-------+--------+----------+----+------+
| 1  | Tom      | 99    | M      | 1        | 1  | 一班 |
| 1  | Tom      | 99    | M      | 1        | 2  | 二班 |
| 1  | Tom      | 99    | M      | 1        | 3  | 三班 |
| 1  | Tom      | 99    | M      | 1        | 4  | 四班 |
| 2  | Abigail  | 86    | F      | 1        | 1  | 一班 |
| 2  | Abigail  | 86    | F      | 1        | 2  | 二班 |
| 2  | Abigail  | 86    | F      | 1        | 3  | 三班 |
| 2  | Abigail  | 86    | F      | 1        | 4  | 四班 |
| 3  | Cara     | 96    | F      | 1        | 1  | 一班 |
| 3  | Cara     | 96    | F      | 1        | 2  | 二班 |
| 3  | Cara     | 96    | F      | 1        | 3  | 三班 |
| 3  | Cara     | 96    | F      | 1        | 4  | 四班 |
...
...

内联查询(Inner Join)

innerjoin

select s.name, s.score, c.name from students as s inner join classes as c on (s.class_id=c.id);

-- 查询结果
+----------+-------+------+
| name     | score | name |
+----------+-------+------+
| Tom      | 99    | 一班 |
| Abigail  | 86    | 一班 |
| Cara     | 96    | 一班 |
| Caroline | 60    | 一班 |
| Beryl    | 90    | 一班 |
| Brook    | 86    | 二班 |
| Ellen    | 66    | 二班 |
| Emma     | 80    | 二班 |
| Fanny    | 50    | 三班 |
| Gail     | 80    | 三班 |
| Jodie    | 90    | 三班 |
+----------+-------+------+

左关联查询(Left Join)

left join

select s.name, s.score, c.name from students as s left join classes as c on (s.class_id=c.id);

-- 查询结果
+----------+-------+--------+
| name     | score | name   |
+----------+-------+--------+
| Tom      | 99    | 一班   |
| Abigail  | 86    | 一班   |
| Cara     | 96    | 一班   |
| Caroline | 60    | 一班   |
| Beryl    | 90    | 一班   |
| Brook    | 86    | 二班   |
| Ellen    | 66    | 二班   |
| Emma     | 80    | 二班   |
| Fanny    | 50    | 三班   |
| Gail     | 80    | 三班   |
| Jodie    | 90    | 三班   |
| Nobody   | 0     | <null> |
+----------+-------+--------+

右关联查询(Right Join)

right join

select s.name, s.score, c.name from students as s right join classes as c on (s.class_id=c.id);

-- 查询结果
+----------+--------+------+
| name     | score  | name |
+----------+--------+------+
| Tom      | 99     | 一班 |
| Abigail  | 86     | 一班 |
| Cara     | 96     | 一班 |
| Caroline | 60     | 一班 |
| Beryl    | 90     | 一班 |
| Brook    | 86     | 二班 |
| Ellen    | 66     | 二班 |
| Emma     | 80     | 二班 |
| Fanny    | 50     | 三班 |
| Gail     | 80     | 三班 |
| Jodie    | 90     | 三班 |
| <null>   | <null> | 四班 |
+----------+--------+------+

全关联查询(Full Join)

full join

注意: mysql 没有 full outer join 可以使用 UNION

-- 在mysql 下执行下面语句会报错
select s.name, s.score, c.name from students as s FULL JOIN classes as c on (s.class_id=c.id);

-- 使用UNION
select s.name, s.score, c.name from students as s LEFT join classes as c on (s.class_id=c.id)
UNION
select s.name, s.score, c.name from students as s RIGHT join classes as c on (s.class_id=c.id);

-- 查询结果
+----------+--------+--------+
| name     | score  | name   |
+----------+--------+--------+
| Tom      | 99     | 一班   |
| Abigail  | 86     | 一班   |
| Cara     | 96     | 一班   |
| Caroline | 60     | 一班   |
| Beryl    | 90     | 一班   |
| Brook    | 86     | 二班   |
| Ellen    | 66     | 二班   |
| Emma     | 80     | 二班   |
| Fanny    | 50     | 三班   |
| Gail     | 80     | 三班   |
| Jodie    | 90     | 三班   |
| Nobody   | 0      | <null> |
| <null>   | <null> | 四班   |
+----------+--------+--------+

练习

-- 统计各班学生数
select c.name, count(s.id) as total from classes as c left join students as s on (c.id=s.class_id) group by c.id;
+------+-------+
| name | total |
+------+-------+
| 一班 | 5     |
| 二班 | 3     |
| 三班 | 3     |
| 四班 | 0     |
+------+-------+

子查询

-- 获取 与 "Emma" 同分数的学生
select id, name from students where score = (select score from students where name="Emma") and name<>"Emma";
+----+------+
| id | name |
+----+------+
| 10 | Gail |
+----+------+

-- exists 查询
-- 统计哪个班至少有一个不及格(<60)的学生
select c.name from classes as c where exists (select * from students as s where s.score < 60 and c.id=s.class_id);
+------+
| name |
+------+
| 三班 |
+------+

查看执行计划(Explain/Desc)

查看执行计划, 是为了确定sql查询的新能(比如是否使用索引, 可能读取的数据数量…) 帮助我们对SQL查询进行优化或者是对表进行优化

分析表

explain students;

+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int(11)       | NO   | PRI | <null>  | auto_increment |
| name     | varchar(100)  | NO   |     | <null>  |                |
| score    | decimal(10,0) | NO   |     | <null>  |                |
| gender   | char(1)       | YES  |     | M       |                |
| class_id | int(11)       | YES  | MUL | <null>  |                |
+----------+---------------+------+-----+---------+----------------+
5 rows in set
Time: 0.013s

字段名称	含义
Field	字段表示的是列名
Type	字段表示的是列的数据类型
Null	字段表示这个列是否能取空值
Key	在mysql中key 和index 是一样的意思，这个Key列可能会看到有如下的值：PRI(主键)、MUL(普通的b-tree索引)、UNI(唯一索引)
Default	列的默认值
Extra	其它信息

分析select查询语句

explain select * from students where name="Emma"\G;

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | students
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 12
filtered      | 10.0
Extra         | Using where

1 row in set
Time: 0.008s

字段名称	含义
table	查询的数据表
type	这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和all。
possible_keys	显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句
key	实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引
key_len	使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref	显示索引的哪一列被使用了，如果可能的话，是一个常数
rows	这里是执行计划中估算的扫描行数，不是精确值
filtered	存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
extra	关于mysql如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是using temporary和using filesort，意思mysql根本不能使用索引，结果是检索会很慢。

type:

all: 全表扫描, mysql遍历全表以找到匹配的行 (如上面的实例: mysql一个遍历了12行(row: 12) 才找结果)
index: 索引全表查找, 把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询
index_merge: 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
range: 索引范围扫描, 常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_subquery: 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重
unique_subquery: 用于where中的in形式子查询，子查询返回不重复值唯一值
ref_or_null: 与ref方法类似，只是增加了null值的比较。实际用的不多
fulltext: 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref: 不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现
eq_ref: 出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
const: 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描, 代表可以做一个类似常量级别的查找, 是sql查询中最好的情况

select_type:

simple: 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个
primary: 一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个
union: union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
dependent union: 与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
union result: 包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
subquery: 除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
dependent subquery: 与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
derived: from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

实例:

-- 使用主键查询
explain select * from students where id=7\G;

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | students
partitions    | <null>
type          | const
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 1
filtered      | 100.0
Extra         | <null>
1 row in set
Time: 0.008s

-- 范围查询
explain select * from students where id >= 6 and id < 9\G;

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | students
partitions    | <null>
type          | range
possible_keys | PRIMARY
key           | PRIMARY
key_len       | 4
ref           | <null>
rows          | 3
filtered      | 100.0
Extra         | Using where

1 row in set
Time: 0.008s


-- 子查询查询
explain select c.name from classes as c where exists (
    select * from students as s where s.score < 60 and c.id=s.class_id
);

***************************[ 1. row ]***************************
id            | 1
select_type   | PRIMARY
table         | c
partitions    | <null>
type          | ALL
possible_keys | <null>
key           | <null>
key_len       | <null>
ref           | <null>
rows          | 4
filtered      | 100.0
Extra         | Using where
***************************[ 2. row ]***************************
id            | 2
select_type   | DEPENDENT SUBQUERY
table         | s
partitions    | <null>
type          | ref
possible_keys | FK_students_classes
key           | FK_students_classes
key_len       | 5
ref           | my_test.c.id
rows          | 3
filtered      |  33.33
Extra         | Using where

2 rows in set
Time: 0.008s

优化实例:

参考: 美团优化实例

预备