SQL归纳总结（2）

1.子查询

1.1 含义

子查询指一个查询语句嵌套在另一个查询语句内部的查询

1.2 子查询的基本语法结构

select 		select_list
from			table		
where		expr 	operator(
											select  			select_list
											from				table);

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。

注意事项
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询

1.3 单行子查询

示例：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

1.4 case中的子查询

在CASE表达式中使用单列子查询

示例：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800
的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id, last_name,
(CASE department_id
WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

1.5 多行子查询

也称为集合比较子查询
内查询返回多行
使用多行比较操作符

1.5.1 多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

1.5.2 示例

查询平均工资最低的部门id

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) dept_avg_sal
)

1.6 EXISTS与NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

如果在子查询中不存在满足条件的行：
条件返回 FALSE
继续在子查询中查找

如果在子查询中存在满足条件的行：
不在子查询中继续查找
条件返回 TRUE

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

2.创建和管理表

2.1 MySQL中的数据类型

数据类型	概述
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOBJSON类型 JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

常用的数据类型如下：

数据类型	描述
INT	从-231到231-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式’YYYY-MM-DD’
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

2.2 管理和创建数据库

2.2.1创建数据库

CREATE DATABASE 数据库名;

判断数据库是否存在，不存在则创建数据库（推荐）

CREATE DATABASE IF NOT EXISTS 数据库名;

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

### 2.2.2 使用数据库
查看当前所有的数据库
```sql
show database;

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

select database();

查看指定库下所有的表

select tables from 数据库名：

查看数据库的创建信息

show create database 数据库名;

2.3 创建表

语法格式：

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

 );

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

举例：

-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);

2.4 修改表

使用 ALTER TABLE 语句可以实现：
向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列

2.4.1 追加一个列

格式：

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

2.4.2 修改一个列

语法格式：

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

2.4.3 重命名一个列

语法格式：

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

2.4.4 删除一个列

语法格式：

ALTER TABLE 表名 DROP 【COLUMN】字段名

2.4.5 删除表

在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除

语法格式：

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

2.4.6 清空表

TRUNCATE TABLE语句：
删除表中所有的数据
释放表的存储空间

注意：TRUNCATE不能回滚*