MySQL 复杂查询 七十题精通MySQL 第三节

MySQL复杂查询

1.索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。 

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 

注意：如果过多的使用索引将会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。

索引在 mysql 中分为普通索引，唯一索引和全文索引

1.1普通索引:

CREATE INDEX indexName ON `users` (username(length))

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length。

1.2.唯一索引：

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX indexName ON mytable(username(length))

1.3全文索引：

针对大文本 TEXT 的内容查找

CREATE FULLTEXT INDEX indexName ON `users` (username)

由于全文索引需要对该字段进行分词，对中文的支持不是很好，所以全文索引在实际开发中不建议使用。在一些大文本的内容管理系统中一般使用

使用全文索引的格式：  MATCH (columnName) AGAINST ('string') eg: SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

删除索引：

ALTER TABLE `users` DROP INDEX `indexName`

练习：

1.索引之所以能够极大加快查询速度，原因是数据库底层采用树形结构存储，而且在随着 MySQL 版本的升级树的类型也在发生变化，搜索相应博客了解在 MySQL 中使用的树型数据都有哪些优点以及各版本的优势。

2.连接查询

数据准备：下载

CREATE TABLE `client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `emp_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id_fk` (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (2,'王二','123',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (4,'麻子','234',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (5,'小明','345',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (6,'小红','456',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (7,'不知道找谁','789',NULL);

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `pwd` varchar(50) NOT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (1,'张三','123456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (2,'李四','13456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (3,'晓峰','123','2020-06-05');

两表连接分为内连接和外连接：

内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。

外连接： 分为

左外连接（left outer join）可以简写 left join

右外连接（right outer join）可以简写为 right join

与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。

from 表1 连接类型 表2 [on (连接条件)] [where (查询条件)]

内连接查询 （重点）

SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e ,`client` c WHERE e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e INNER JOIN `client` c ON e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e LEFT OUTER JOIN `client` c ON e.`id`=c.`emp_id`;

左外连接查询

使用 left outer join 关键字，在 on 子句中设定连接条件不仅包含符合连接条件的数据行，还包含左表全部数据，右表没连上的不显示。

3.子查询与联合查询

子查询也叫嵌套查询，是指在 select 子句或者 where 子句中又嵌入 select 查询语句。

SELECT * FROM `emp` WHERE id IN(SELECT emp_id FROM `client` WHERE id IN(2,4,5))

联合查询

合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用 union 关键字

SELECT * FROM `client` WHERE emp_id = 2 UNION SELECT * FROM `client` WHERE emp_id = 1;
SELECT `id`,`name` FROM `client` WHERE emp_id = 2 UNION SELECT `id`,`name` FROM `emp`;

注意：两条查询语句返回的结构和字段的数量必须相同，否则无法合并。

4.报表函数

报表函数也叫聚合函数，一般用于统计汇总数据库表信息。通常和分组函数配合使用，如果不使用分组函数则认为该表的全部数据为一个组。常用报表函数如下：

1.计数函数－COUNT，如果参数是字段名，则统计该字段不为空的记录数。

Select count(*)|count(列名) from tablename [WHERE where_definition]

案例：

SELECT COUNT(*) FROM `client`

2.求和函数－SUM

Select sum(列名){,sum(列名)…} from tablename [WHERE where_definition]

案例：

SELECT SUM(`chinese`),SUM(`english`),SUM(`math`) FROM `student` WHERE `name` LIKE '%李%'

4.平均函数－AVG

Select avg(列名){,avg(列名)…} from tablename [WHERE where_definition]

案例：

SELECT AVG(`chinese`),AVG(`english`),AVG(`math`) FROM `student` WHERE `name` LIKE '%李%'

5.边角函数－MAX/MIN

Select max(列名)　from tablename [WHERE where_definition]

5.分组查询