很楠不爱3

这个屌丝很懒，什么也没留下！

热门标签

三天学会MySQL（八）子查询 & 索引_子查询索引

作者：很楠不爱3 | 2024-05-04 21:50:18

踩

子查询索引

一.子查询介绍

用子查询解决问题

假如要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。可以用组合两个查询的方法解决这个问题。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。

子查询语法

子查询

子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。使用子查询可以用简单的语句构建功能强大的语句。

可以将子查询放在许多的 SQL 子句中，包括：

WHERE 子句
HAVING 子句
FROM 子句

使用子查询

使用子查询的原则

子查询放在圆括号中。（圆括号里的优先执行！）
将子查询放在比较条件的右边。
在单行子查询中用单行运算符，在多行子查询中用多行运算符。

子查询类型

示例：

查询与 Fox 同一部门的同事，并显示他们的名字与部门 ID 。

select e .LAST_NAME ,e .DEPARTMENT_ID

FROM employees e

where e .DEPARTMENT_ID =

( select e1 .DEPARTMENT_ID

from employees e1

where e1 .last_name = 'Fox' ) ;

二.单行子查询

单行子查询

单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。

示例：

查询 Fox 的同事，但是不包含他自己。

select empl .last_name

from employees empl

where empl .department_id =

( select e .department_id from employees e

where e .last_name = 'Fox' )

and empl .last_name <> 'Fox' ;

三.多行子查询

多行子查询

子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运算符。

使用ANY运算符

ANY 运算符

ANY 运算符比较一个值与一个子查询返回的每一个值。

< ANY 意思是小于最大值。
> ANY 意思是大于最小值。
= ANY 等同于 IN。

使用ALL运算符

ALL 运算符比较一个值与子查询返回的每个值。

< ALL 意思是小于最小值。
> ALL 意思是大于最大值，

NOT 运算符可以与 IN 运算符一起使用。

子查询中的空值

内查询返回的值含有空值，并因此整个查询无返回行，原因是用大于、小于或不等于比较 Null 值，都返回 null 。所以，只要空值可能是子查询结果集的一部分，就不能用 NOT IN 运算符。 NOT IN 运算符相当于 <> ALL 。

注意，空值作为一个子查询结果集的一部分，如果使用 IN 操作符的话，不是一个问题。 IN 操作符相当于 =ANY 。

SELECT emp .last_name

FROM employees emp

WHERE

emp .employee_id IN ( SELECT mgr .manager_id

FROM employees mgr ) ;

示例 :

查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID 。

select em .last_name ,em .salary ,em .department_id

from employees em

where em .salary in

( select min ( e .salary )

from employees e

group by e .department_id ) ;

四.子查询练习

1. 写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和hire date ，结果中不包括 Zlotkey 。


SELECT
e1.LAST_NAME,e1.HIRE_DATE
FROM employees e1
where e1.DEPARTMENT_ID = (select e.DEPARTMENT_ID FROM employees e
where e.LAST_NAME ='Zlotkey') AND e1.LAST_NAME <> 'Zlotkey';

2. 创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。


SELECT
e1.EMPLOYEE_ID,e1.LAST_NAME
FROM employees e1
WHERE e1.SALARY > (select AVG(e.SALARY) from employees e) ORDER BY e1.SALARY;

3. 写一个查询显示所有工作在有任一雇员的名字中包含一个 u 的部门的雇员的雇员号和名字。


SELECT
e1.EMPLOYEE_ID,e1.LAST_NAME
FROM employees e1
WHERE e1.DEPARTMENT_ID IN
(SELECT e.DEPARTMENT_ID FROM employees e
WHERE e.LAST_NAME like '%u%')

4. 显示所有部门地点号 (department location ID ) 是 1700 的雇员的 last name 、 department number 和 job ID 。


SELECT
e.LAST_NAME,e.DEPARTMENT_ID,e.JOB_ID
FROM employees e
WHERE e.DEPARTMENT_ID IN
(SELECT
d.DEPARTMENT_ID
FROM departments d
WHERE d.LOCATION_ID = 1700)

5. 显示每个向 King 报告的雇员的名字和薪水。


SELECT
e1.LAST_NAME,e1.SALARY
FROM employees e1
where e1.MANAGER_ID IN
(select e.EMPLOYEE_ID from employees e where
e.LAST_NAME ='King')

6. 显示在 Executive 部门的每个雇员的 department number 、 last name 和 job ID 。


SELECT
e.DEPARTMENT_ID,e.LAST_NAME,e.JOB_ID
FROM employees e
WHERE e.DEPARTMENT_ID = (select d.DEPARTMENT_ID FROM departments d
WHERE d.DEPARTMENT_NAME = 'Executive');

五.索引介绍

MySQL中的索引

索引介绍

索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。索引是一种特殊的文件，它们包含着对数据表里所有记录的位置信息。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

索引的作用

索引相当于图书上的目录，可以根据目录上的页码快速找到所需的内容，提高性能（查询速度）。

索引优点：

1 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；
2 可以加快数据的检索速度；
3 可以加速表与表之间的连接；
4 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

索引缺点

1 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
2 索引需要占用物理空间，数据量越大，占用空间越大；
3 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

什么时候需要创建索引

1 频繁作为查询条件的字段应该创建索引；
2 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；
3 查询中统计或者分组的字段；

什么时候不需要创建索引

频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；
2 where条件里用不到的字段，不创建索引；
3 表记录太少，不需要创建索引；

经常增删改的表；
5 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引；

MySQL 中的索引类型

普通索引：最基本的索引，它没有任何限制。
唯一索引：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。
联合索引：在多个字段上建立索引，能够加速查询到速度。

六.普通索引

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。 length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定

length 。

创建索引时需要注意：

如果指定单列索引长度， length 必须小于这个字段所允许的最大字符个数。

查询索引

SHOW INDEX FROM table_name;

直接创建索引

CREATE INDEX index_name ON table ( column ( length )) ;

示例：为 emp3 表中的 name 创建一个索引，索引名为emp3_name_index；

create index emp3_name_index ON emp3 ( name ) ;

修改表添加索引

ALTER TABLE table_name ADD INDEX index_name ( column ( length )) ;

示例：

修改 emp3 表，为 addrees 列添加索引，索引名为emp3_address_index ；

alter table emp3 add index emp3_address_index ( address ) ;

创建表时指定索引列

CREATE TABLE `table` (

COLUMN TYPE ,

PRIMARY KEY ( `id` ) ,

INDEX index_name ( column ( length ))

) ;

示例：

创建 emp4 表，包含 emp_id,name,address 列，同时为 name 列创建索引，索引名为 emp4_name_index 。

create table emp4 ( emp_id int primary key auto_increment ,name varchar ( 30 ) ,address

varchar ( 50 ) , index emp4_name_index ( name )) ;

删除索引

DROP INDEX indexname ON tablename;

示例：

删除 mep3 表中索引名为 emp3_address_index 的索引。

drop index emp3_address_index on emp3;

七.唯一索引

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

创建唯一索引

CREATE UNIQUE INDEX indexName ON table ( column ( length )) ;

被创建为唯一索引的列中不能有重复值！

示例：

为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index 。

create unique index emp_name_index on emp ( name ) ;

修改表添加唯一索引

ALTER TABLE table_name ADD UNIQUE indexName ( column ( length )) ;

示例：

修改 emp 表，为 salary 列添加唯一索引，索引名为emp_salary_index 。

alter table emp add unique emp_salary_index ( salary ) ;

创建表时指定唯一索引

CREATE TABLE `table` (

COLUMN TYPE ,

PRIMARY KEY ( `id` ) ,

UNIQUE index_name ( column ( length ))

) ;

示例：

创建 emp5 表，包含 emp_id,name,address 列，同时为 name 列创建唯一索引。索引名为 emp5_name_index 。

create table emp5 ( emp_id int primary key ,name varchar ( 30 ) ,address varchar ( 30 ) , unique

emp5_name_index ( name )) ;

八.主键索引

添加了主键约束就有了主键索引。

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

修改表添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 ) ;

示例：

修改 emp 表为 employee_id 添加主键索引。

alter table emp add primary key ( employee_id ) ;

创建表时指定主键索引

CREATE TABLE `table` (

COLUMN TYPE ,

PRIMARY KEY ( column )

) ;

示例：

创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id列创建主键索引。

create table emp6 ( employee_id int primary key auto_increment ,name varchar ( 20 ) ,address

varchar ( 50 )) ;

九.组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用 ( 最左前缀原则 ) 。

最左前缀原则

就是最左优先。

如：我们使用表中的 name ， address ， salary 创建组合索引，那么想要组合索引生效，我们只能使用如下组合：

name/address/salary

name/address

name/

如果使用 addrees/salary 或者是 salary 则索引不会生效。

添加组合索引

ALTER TABLE table_name ADD INDEX index_name ( column ( length ) , column ( length )) ;

示例：修改 emp6 表，为 name ， address 列创建组合索引。

alter table emp6 add index emp6_index_n_a ( name,address ) ;

创建表时创建组合索引

CREATE TABLE `table` (

COLUMN TYPE ,

INDEX index_name

( column ( length ) , column ( length ))

) ;

示例：

创建 emp7 表，包含 emp_id,name,address 列，同时为name,address 列创建组合索引。

create table emp7 ( emp_id int primary key auto_increment ,name varchar ( 20 ) ,address varchar ( 30 ) , index emp7_index_n_a ( name,address )) ;

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/很楠不爱3/article/detail/536215

三天学会MySQL（八）子查询 & 索引_子查询 索引

一.子查询介绍

二.单行子查询

三.多行子查询

四.子查询练习

五.索引介绍

六.普通索引

七.唯一索引

八.主键索引

九.组合索引

三天学会MySQL（八）子查询 & 索引_子查询索引