2021.12.9

子查询

相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

典型的运用场景：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

SELECT last_name,salary,department_id
FROM employees e1
WHERE salary >(
	SELECT AVG(salary)
	FROM employees e2
	WHERE e2.department_id = e1.`department_id`
);

由例子的自己的理解：

相关子查询的关键在于内查询需要用到外面的表中的数据。这里的处理也可以向上面有种情况一样，将平均值分部门的结果作为一张表来实现子查询。

由此可以简单的结合前面的顺序总结一下子查询的位置安排

exist和not exist

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：
  • 条件返回 FALSE
  • 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  • 不在子查询中继续查找
  • 条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

例：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT d.department_id,d.department_name
FROM departments d
WHERE NOT EXISTS 
(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

理解这个关键字就理解成子查询中的一种特殊的使用场景就可以了，其实通过其他方式实现也是很好理解的。

思考：

问题：谁的工资比Abel的高？

解答：

#方式1：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式2：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		);

问题：以上两种方式有好坏之分吗？

解答：自连接方式好！

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

略去了这一章的习题练习，

但从逻辑层面来说这里的练习题应该是最有难道的，如果有需求，则在这里来刷题和锻炼思维。

创建和管理表

存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。

那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

我们要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。

数据库名的规范要求：

• 数据库名、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

创建和管理数据库

创建数据库

• 方式1：创建数据库

CREATE DATABASE 数据库名; 

• 方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

• 方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）

CREATE DATABASE IF NOT EXISTS 数据库名; 

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

示例：

CREATE DATABASE IF NOT EXISTS mytest1;
SHOW DATABASES;

数据库的使用：

• 查看当前所有的数据库

SHOW DATABASES; #有一个S，代表多个数据库

• 查看当前正在使用的数据库

SELECT DATABASE();  #使用的一个 mysql 中的全局函数

• 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

• 使用/切换数据库

USE 数据库名;

数据库的删除：

• 方式1：删除指定的数据库

DROP DATABASE 数据库名;

• 方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

要删除的不存在则默默推出，不会报错。

创建表

常见的变量类型

具体的介绍有需要再去仔细了解。

创建数据表：

• 必须具备：
  • CREATE TABLE权限
  • 存储空间
• 语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

• 必须指定：
  • 表名
  • 列名(或字段名)，数据类型，长度
• 可选指定：
  • 约束条件
  • 默认值

示例：

CREATE TABLE IF NOT EXISTS mytest
(
	id INT,
	m_name VARCHAR(15),
	h_date DATE
);

这里使用varchar定义字符串的时候必须指明其长度。

创建方法2:

• 使用 AS subquery 选项，将创建表和插入数据结合起来

  

• 指定的列和子查询中的列要一一对应

• 通过列名和默认值定义列

这种创建方式的更多的细节：

• 使用这种方法创建表后，原始的数据也是跟着保留的。
• 查询语句中字段的别名，可以作为新创建的表的字段的名称。
• 创建空表的话就可以在where筛选条件中写一个不可能条件，比如说1=2即可。

具体示例：

CREATE TABLE myemp1
AS
SELECT employee_id,last_name,salary
FROM employees;

管理表

修改表

添加一个字段

• 向已有的表中添加列

语法格式如下：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

修改一个字段

• 可以修改列的数据类型，长度、默认值和位置
• 但一般也不会去改变数据类型。更多的是去改长度。
• 修改字段数据类型、长度、默认值、位置的语法格式如下：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

示例：

ALTER TABLE	dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE	dept80
MODIFY salary double(9,2) default 1000;

• 对默认值的修改只影响今后对表的修改

重命名一个字段

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15); 

删除一个字段

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例：

ALTER TABLE  dept80
DROP COLUMN  job_id; 

重命名表

• 使用RENAME

RENAME TABLE emp
TO myemp;

删除表

• 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

• 数据和结构都被删除

• 所有正在运行的相关事务被提交

• 所有相关索引被删除

• 语法格式：

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

IF EXISTS的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

• 举例：

DROP TABLE dept80;

• DROP TABLE 语句不能回滚

清空表

• TRUNCATE TABLE语句：

  • 删除表中所有的数据
  • 释放表的存储空间
  • 但是清空过后表的结构还是存在的。

• 举例：

TRUNCATE TABLE detail_dept;

清空数据和删除数据的区别

• TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
• TRUNCATE是DDL的代表。DDL的操作一旦执行就无法回滚。因为在执行完DDL操作后，一定会执行commit操作。DML的操作默认情况下一旦执行也是没法回滚的，但是可以修改参数，则此时的操作可以回滚。
• 对比：

SET autocommit = FALSE;
  
DELETE FROM emp2; 
#TRUNCATE TABLE emp2;
  
SELECT * FROM emp2;
  
ROLLBACK;
  
SELECT * FROM emp2;

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

DML之增删改

插入数据

方式1：VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

情况1：为表的所有字段按默认顺序插入数据

INSERT INTO 表名
VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

举例：

INSERT INTO emp1
VALUES (70, 'Pub', '2000-10-11', 1700); 

此时即没有指明添加的字段，必须按照声明的字段按顺序的去添加。

情况2：为表的指定字段插入数据

INSERT INTO emp1(id,hire_date,salary,`name`) 
VALUES(2,'1999-08-08',6545,'yiyi');

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

此时如果上面的字段多于下面的值，那么当下面的值个数少的时候，即没有赋值的则直接赋值为NULL。

情况3：同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或者

INSERT INTO table_name(column1 [, column2, …, columnn]) 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

举例：

INSERT INTO emp(emp_id,emp_name)
 VALUES (1001,'shkstart'),
(1002,'atguigu'),
(1003,'Tom');

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：
●　Records：表明插入的记录条数。
●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
●　Warnings：表明有问题的数据值，例如发生数据类型转换。

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

小结：

• VALUES也可以写成VALUE，但是VALUES是标准写法。

• 字符和日期型数据应包含在单引号中。

方式2：将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式如下：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

• 在 INSERT 语句中加入子查询。
• 不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对应。且必须一一对应。
• 且被添加的表中的字段长度不能小于复制对象的表中的字段的长度。

举例：

INSERT INTO emp1(id,`name`,salary,hire_date)
SELECT employee_id,last_name,salary,hire_date
FROM employees
WHERE department_id IN (60,70);