五.子查询与数据库操作
一.子查询
在数据查询中，可能存在以下情况
SELECT salary FROM employees WHERE last_name = 'Abel';
-- output: Abel : 11000
SELECT last_name,salary FROM employees WHERE salary > 11000;
上述的代码尝试查询哪些employee的salary大于'Abel'的。
为了简略操作，将两个语句合成为一个语句，可以使用自连接
SELECT last_name,salary FROM employees e1,employees e2 WHERE e2.`salary` > e1.`salary` AND e1.last_name = `Abel`;
语句实现了从employees表中查询salary大于'Abel'的行。
最后我们可以使用子查询来获取这个结果。
SELECT last_name,salary FROM employees WHERE salary > (
SELECT salary FROM employees
WHERE last_name = 'Abel'
);
其中，外部的SELECT语句称为外查询，内部的则称为内查询。
其中，单行运算符对应单行子查询，多行运算符对应多行子查询。
1.单行子查询
单行子查询是通过单行比较操作符与内查询结果进行比较运算得出结果的查询。
单行操作符包括：=、>、>=、<、<=、<>(即!=)。
若单行操作符对应多个数值，则会报错。
Error:1242
Subquery returns more than 1 row
2.多行子查询
为了对多行数据进行操作，适应多行操作符，我们可以采取多行子查询。
下列的多行比较操作符可以应用于多行子查询：
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的任意一个即可 |
| ANY | 子查询返回的某个值满足条件即可 |
| ALL | 子查询返回的所有值都需要满足条件 |
| SOME | ANY的别名 |
下面分别对其进行实例：
-- IN
SELECT employee_id,last_name FROM employees WHERE salary IN
(
SELECT MIN(salary) FROM employees GROUP BY department_id
);
上述代码对IN的用法进行了举例，内查询返回了所有部门的最低salary，IN则用来判断employees表中哪些人的salary与这些值相等。
-- ANY/ALL
SELECT employee_id,last_name,salary FROM employees WHERE
job_id <> 'IT_PROG'
AND salary < ANY/ALL (
SELECT salary FROM employees WHERE job_id = 'IT_PROG'
);
上述代码对ANY和ALL的用法进行了举例，用于返回其他job_id中比job_id为'IT_PROG'部门的任一/所有工资低的员工的信息。
上述多行子查询可能受到NULL值的影响，若受影响则需排除在内查询中除去NULL值，最简单的操作便是IS NOT NULL等语句。
由于MySQL中聚合函数不可嵌套，即不存在类似MIN(AVG(salary))的操作。
但我们可以通过子查询轻松实现嵌套操作。
SELECT MIN(avg_sal) FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) avg_sal_table;
3.相关子查询
当内查询的过程中都需要用到外查询的结果时，这种子查询被称为相关子查询。
例：查询员工中工资大于本部门平均工资的员工的employee_id
SELECT employee_id FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE department_id = e1.`department_id`
);
上述代码通过查询外表(e1)的department_id并将其传入内查询，相当于c语言中的for循环语句，每次带入一个salary的值并判断其是否大于对应的department_id的平均工资。
当然也可以采用在FROM语句内声明子查询
SELECT e.last_name,e.salary,e.department_id FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) inner_table
WHERE e.department_id = inner_table.department_id
AND e.salary > inner_table.avg_sal;
上述代码通过在内查询中先将各个部门的平均工资列为内表inner_table，之后将外表与内表的department_id匹配并要求salary大于平均工资。
4.EXISTS语句
EXISTS语句将判断是否存在某个字段，若存在则返回TRUE，否则返回FALSE
SELECT employee_id FROM employees e1
WHERE EXISTS (
SELECT * FROM employees e2
WHERE e1.department_id = e2.manager_id
);
同理，NOT EXISTS语句则判断某个字段是否不存在，若不存在则返回TRUE，否则返回FALSE。
二.数据库基础操作
1.创建
如何创建数据库：
CREATE DATABASE new_database;
设置数据库的字符集：
CREATE DATABASE new_database CHARACTER SET 'utf8';
如何实现os_CREATE的效果（即若不存在指定数据库则创建新的数据库）
CREATE DATABASE IF NOT EXISTS new _database CHARACTER SET 'utf8';
其中IF NOT EXISTS用于检测是否存在对应的数据库。
2.查看
首先要切换数据库：
USING new_database;
查看当前正在使用(USING)的数据库：
SELECT DATABASE() FROM DUAL;
查看当前数据库的表的列表：
SHOW TABLES;
SHOW TABLES FROM new_database;
即可直接显示正在使用(USING)的数据库的表，也可以用FROM语句指定要查询的数据库。
3.修改
修改数据库的字符集：
ALTER DATABASE new_database CHARACTER SET 'gbk';
4.删除
使用DROP语句来删除指定的数据库。
DROP DATABASE new_database;
同理，也可以在删除过程中加入判断语句。
DROP DATABASE IF EXISTS new_database;
MySQL中的数据类型
| 数据类型 | 关键字 |
|---|---|
| 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT |
| 浮点数类型 | FLOAT、DOUBLE |
| 定点数类型 | DECIMAL |
| 位类型 | BIT |
| 日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP |
| 文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
| 枚举类型 | ENUM |
| 集合类型 | SET |
| 二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
| JSON类型 | JSON对象、JSON数组 |
| 空间数据类型（单值） | GEOMETRY、POINT、LINESTRING、POLYGON |
| 空间数据类型（集合） | MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |
三.表的基本操作
1.创建
首先要制定使用的数据库。
CREATE TABLE IF NOT EXISTS new_table(
id INT,
emp_name VARCHAR(15),
hire_date DATE,
);
上述语句添加了一个表，其字段有三个，分别为INT类型、VARCHAR数组类型和DATE类型。
此外，可以通过下述语句来查询该表的字段信息。
SHOW CREATE TABLE new_table;
-- 或
DESC new_table;
可以通过查询语句的结果返回表来创建新的表：
CREATE TABLE new_table
AS
SELECT employee_id,last_name,salary FROM employees;
此时创建的新表与查询结果返回的表完全一致，包括数据。若在SELECT语句中使用了别名，则创建的新表中会沿用别名。
通过这个性质，我们可以实现表的复制：
CREATE TABLE new_table
AS
SELECT * FROM employees;
若想只复制字段内容而不复制数据，则可以通过WHERE语句来限制。
CREATE TABLE new_table
AS
SELECT * FROM employees WHERE 1 = 2;
MySQL 8.0存在的新特性：
CREATE TABLE new_table(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);
上述语句实现了一个字段c，其值总是等于a + b的数据。
2.修改
a.添加一个字段
ALTER TABLE new_table ADD salary DOUBLE(10,2);
ALTER关键字用于修改表，ADD关键字则增加新的字段，DOUBLE(10,2)表示新增加的字段数据类型为DOUBLE类型，且共10位，小数点后2位。
若不添加位置限制，则新字段默认添加到最后一个字段的位置。
为了限制字段的插入位置，有两种方法：
ALTER TABLE new_table ADD salary DOUBLE(10,2) FIRST;
ALTER TABLE new_table ADD salary DOUBLE(10,2) AFTER emp_name;
FIRST关键字会将某个字段添加至第一个位置
AFTER关键字则将新字段插入某个字段之后。
b.修改一个字段
修改一个字段的VARCHAR长度：
ALTER TABLE new_table MODIFY emp_name VARCHAR(35);
ALTER TABLE new_table MODIFY emp_name VARCHAR(35) DEFAULT 'new_data';
其中DEFAULT关键字可以提供缺省值，若未指定某个字段的内容，则查询等其他操作时将返回缺省值。
c.重命名一个字段
ALTER TABLE new_table CHANGE salary new_salary DOUBLE(10,2);
ALTER TABLE new_table CHANGE emp_name last_name VARCHAR(25);
CHANGE关键字在更改名字后要求指定其数据类型，所以可以在更改名字的过程中更改数据类型。
d.删除一个字段
ALTER TABLE new_table DROP COLUMN salary;
其中，DROP关键字用于删除指定内容，COLUMN则指定删除的内容为名为salary的那一列。
e.重命名表
RENAME TABLE new_table TO employees;
-- ---------------------------------------------------
ALTER TABLE new_table RENAME TO employees;
上述两种语句均可实现对表的重命名。
3.删除与清空
删除表
DROP TABLE new_table;
DROP TABLE IF EXISTS new_table;
推荐在删除前判断是否存在对应的表。
清空表
TRUNCATE TABLE new_table;
TRUNCATE关键字会清除表内所有数据，但保留字段信息。
4.COMMIT和ROLLBACK
COMMIT会将当前表的数据在本地进行一次备份，并覆盖原有的备份存档。
而ROLLBACK则可以将表回溯至上一次COMMIT之后的状态。
二者分别对应SAVE和LOAD。
由此我们可以对TRUNCATE和DELETE语句进行一定的分析：
TRUNCATE一旦执行，则表中数据全部清空，且不可以ROLLBACK。
而DELETE执行后，数据是可以实现回滚ROLLBACK的。
此外
一般认为，DDL操作一旦实现便不可回滚，DML操作默认不可回滚，但可以通过
SET AUTOCOMMIT = FALSE
来关闭自动COMMIT，便可实现回滚操作。
四.数据处理
首先定义一个表
CREATE TABLE new_table(
id INT,
name VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
1.插入
通过最基础的INSERT语句，后接表名和值(VALUES)的内容，来插入指定的数据。
INSERT INTO new_table VALUES (
1,
'Outer',
'2005-07-09',
5000.00
);
此外也可以自定义插入的字段的顺序：
INSERT INTO new_table(id,hire_date,name,salary) VALUES (
1,
'2005-07-09',
'Outer',
5000.00
);
若在输入时为指明某个字段的数据，则缺省值为NULL。
通过INSERT也可以实现多组数据的添加：
INSERT INTO new_table VALUES
(1,'Outer','2005-07-09',5000.00),
(2,'Cyrex','2005-07-09',6000.00);
也可以在VALUES后接已有表的查询内容，但其数据必须与表定义时的字段一一对应。
INSERT INTO new_table VALUES
SELECT id,name,hire_date,salary FROM employees;
2.更新
UPDATE后跟表名，SET后跟字段和数据来更新数据信息。
UPDATE new_table SET hire_date = CURDATE() WHERE id = 5;
3.删除
DELETE语句与FROM连用，可实现从对应表中删除对应的数据。
DELETE FROM new_table WHERE id = 1;
DELETE FROM new_table WHERE hire_date = '2005-07-09';
注：
更新和删除的过程中，会由于更改内容与表中的其他数据存在关联而导致无法删除。
