MySQL入门

#1 数据库概述

1. 数据库与数据库管理系统

• DB:数据库(Database)；
• DBMS:数据库管理系统(Database Management System)；
• SQL:结构化查询语言(Structured Query Language)；

2. RDBMS与非RDBMS

2.1 关系型数据库(RDBMS)

• 关系型数据库以行(row)和列(column)的形式存储数据。一系列的行和列被称为表(table)，一组表组成了一个库(database)
• 优势： 复杂查询，事务支持

2.2 非关系型数据库(非RDBMS)

• 可以看成传统关系型数据库的功能阉割版本，基于键值对存储数据，不需要经过SQL层的解析，性能非常高
• 键值型数据库：Redis
• 文档型数据库：MongoDB
• 搜索引擎数据库：Elasticsearch
• 列式数据库：HBase
• 图形数据库

3. 关系型数据库设计规则

• 关系型数据库的典型数据结构是数据表

3.1 表、记录、字段

• E-R(entity-relationship，实体-联系)模型中三个主要概念是：实体集、属性、联系集

ORM思想(Object Relational Mapping)
数据库中的一个表 --- Java或Python中的一个类
表中的一条数据、记录 --- 类中的一个对象
表中的一个列 --- 类中的一个字段、属性(field) 

3.2 表的关联关系

• 表与表之间的数据记录有关系
• 四种关系：一对一关联、一对多关联、多对多关联、自我引用

#2 基本的SELECT语句

1. SQL分类

• DDL(Data Definition Languages数据定义语言):

  CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE

• DML(Data Manipulation Languages数据操作语言):

  INSERT \ DELETE \ UPDATE \ SELECT(重中之重)

• DCL(Data Control Languages数据控制语言):

  COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

2. 导入现有的数据表、表的数据

• 方式一：source 文件的全路径名
• 方式二：基于具体的图形化界面的工具可以导入数据

3. 基本的SELECT语句

3.1 SELECT…FROM

• SELECT 字段1，字段2，… FROM 表名

• SELECT ... FROM DUAL;DUAL表示伪表

• *：表中所有的字段(列)

3.2 列的别名

• AS(alias)：别名；可以省略

  SELECT employee_id AS id->employee_id id

• 列的别名还可以用””引用起来。不要使用单引号

SELECT employee_id emp_id, last_name AS lname, salary * 12 "annual sal"
FROM employees;

3.3 去除重复行

• 使用DISTINCT

SELECT DISTINCT department_id
FROM employees;

3.4 空值参与运算

• 空值:NULL 不等同于0, ‘ ‘, ‘null’

• 空值参与运算：结果一定为空

• 实际问题解决方案：引入IFNULL

SELECT employee_id, salary "月工资", salary * (1 + IFNULL(commission_pct, 0)) * 12 "年工资"
FROM employees;

3.5 着重号``

• 如果字段与保留字、数据库系统等冲突，使用着重号引起来，避免冲突

SELECT * 
FROM `order`;

3.6 查询常数

• SELECT查询还可以对常数进行查询，在查询结果中增加一列固定的常数列。

SELECT '中国', 123, employee_id, last_name
FROM employees;

3.7显示表结构

• DESCRIBE 表名：显示了表中字段的详细信息
• 或者DESC 表名

DESCRIBE employees;
DESC departments;

3.8 过滤数据

• 过滤条件用WHERE来表示
• 声明在FROM结构的后面

SELECT *
FROM employees
WHERE department_id = 90;

#3. 运算符

1. 算术运算符

2. 比较运算符

• <>(!=):不等于

• 字符串存在隐式转换，如果转换不成功，则看作0

• 只要有NULL参与判断，结果就为NULL

2.1 安全等于运算符(<=>)

• 作用与等于运算符(=)相似。唯一的区别是’<=>’可以用来对NULL进行判断。当两个都为NULL时，返回1，一个为NULL时，返回0

2.2 IS NULL \ IS NOT NULL \ ISNULL

• 以下三种写法表达的意思相同

SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL;

SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct <=> NULL;

SELECT last_name, salary, commission_pct
FROM employees
WHERE ISNULL(commission_pct);

2.3 LEAST() \ GREATEST

SELECT LEAST('g', 'a', 't'), GREATEST('g', 'b', 't')
FROM DUAL;

2.4 BETWEEN…AND

WHERE salary BETWEEN 5000 AND 9000;

WHERE salary >= 5000 && salary <= 9000;

WHERE salary NOT BETWEEN 5000 AND 9000;

2.5 IN (SET) \ NOT IN (SET)

SELECT last_name, department_id
FROM employees
WHERE department_id IN (10, 20, 30);

2.6 LIKE:模糊查询

SELECT last_name
FROM employees
WHERE last_name LIKE ('%a%');

SELECT last_name
FROM employees
WHERE last_name LIKE ('a%');

#查询第2个字符是_且第3个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE ('_\_a%');

• %:代表不确定个数的字符，0个，1个或多个

• _:代表一个不确定的字符

• \:转义字符

  如果想让其他字符具有转义的意思，使用ESCAPE

2.7 REGEXP \ RLIKE:正则表达式

3. 逻辑运算符

• OR ||
• AND &&
• NOT !
• XOR
• OR可以和AND一起使用，并且AND的优先级高于OR

4. 位运算符

5. 运算符的优先级

#4. 排序与分页

1. 排序

• 使用ORDER BY对查询到的数据进行排序操作；默认升序

• 升序：ASC(ascend):

• 降序：DESC(descend):

SELECT employee_id, last_name, salary 
FROM employees
ORDER BY salary DESC;

• 我们可以使用列的别名，进行排序;

  注意：列的别名只能在ORDER BY中使用，不能在WHERE中使用

• WHERE需要声明在FROM后，ORDER BY之前

1.1 多列排序

#显示员工信息，按照department_id的降序排序，salary的升序排序
SELECT employee_id, last_name, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC;

2. 分页

• LIMIT: 位置偏移量，条目数
• 公式：LIMIT (pageNo - 1) * pageSize, pageSize;

SELECT employee_id, last_name
FROM employees
LIMIT 0, 20;
#第二页
SELECT employee_id, last_name
FROM employees
LIMIT 20, 20;

2.1 WHERE…ORDER BY…LIMIT声明顺序

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
LIMIT 10;

2.2 MySQL8.0新特性：LIMIT…OFFSET…

• 第一个值表示条目数，第二个表示偏移量

#表里有107条数据，我们只想要显示第32、33条数据
SELECT employee_id, last_name
FROM employees
LIMIT 31, 2;

#LIMIT...OFFSET...
SELECT employee_id, last_name
FROM employees
LIMIT 2 OFFSET 31;

#5. 多表查询

1. 笛卡尔积的错误

• 出现笛卡尔积的错误

SELECT employee_id, department_name
FROM employees, departments;

• 笛卡尔积也称为交叉连接，CROSS JOIN

• 错误原因：缺少了多表的连接条件

2. 多表查询的正确方式：需要有连接条件

SELECT employees.employee_id, departments.department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

• 如果查询语句中出现多个表中都存在的字段，则必须指明此字段所在的表
• 建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表
• 可以给表起别名，在SELECT和WHERE中使用表的别名

SELECT emp.employee_id, dept.department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id;

• 如果给表起了别名，那就必须使用表的别名，不能使用表的原名
• 如果有n个表实现多表的查询，则至少需要n - 1个连接条件

3. 多表查询的分类

3.1 等值连接 vs 非等值连接

• 非等值连接

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

3.2 自连接 vs 非自连接

• 自连接:自己连接自己

SELECT e.employee_id, e.last_name, m.manager_id, m.last_name
FROM employees e, employees m
WHERE e.employee_id = m.employee_id;

3.3 内连接 vs 外连接

• 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
• 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了不匹配的行

```

#### 3.3.1 外连接的分类：左外连接、右外连接、满外连接

* SQL92语法实现内连接

```mysql
SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

• SQL92语法实现外连接：使用 +
• MySQL不支持SQL92语法中外连接的写法

SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

• SQL99语法中使用JOIN…ON的方式实现多表的查询，这种方式也能解决外连接的问题
• MySQL支持这种语法
• SQL99语法实现内连接：

SELECT last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

• SQL99语法实现外连接：
• 左外连接：

SELECT e.last_name, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

SELECT e.last_name, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

• 右外连接：

SELECT e.last_name, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

• 满外连接：MySQL不支持FULL OUTER JOIN

4. SQL99实现七种JOIN操作

1. UNION和UNION ALL的使用

• UNION操作符返回两个查询的结果集的并集，去除重复记录

• UNION ALL操作符返回两个查询的结果集的并集，对于两个结果集的重复部分，不去重

  能使用UNION ALL尽量使用UNION ALL

2. 七种JOIN的实现

• 内连接

  SELECT employee_id, department_name
  FROM employees e JOIN departments d
  ON e.department_id = d.department_id;

• 左外连接

SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

• 右外连接

SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

• 左外连接（除去内连接的部分）

SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

• 右外连接（除去内连接的部分）

SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

• 满外连接
• 方式一：左外连接 UNION ALL 右外连接（除去内连接的部分）

SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

• 中空连接
• 方式一：左外连接（除内连接） UNION ALL 右外连接（除内连接）

SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

5. SQL99语法的新特性1：自然连接

• NATURAL JOIN:相当于SQL92中的等值连接，会自动查询两张连接表中所有相同的字段，然后进行等值连接

SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;

6. SQL99语法的新特性2：USING

• USING:指定数据表里的同名字段进行等值连接，但是只能配合JOIN一起使用

SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
USING (department_id);

#6. 单行函数

• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套

1. 数值函数

2. 字符串函数

• 字符串的索引是从1开始的

3. 日期和时间函数

4. 流程控制函数

5. 加密与解密函数

6. MySQL信息函数

#7. 聚合函数

1. 常见的几个聚合函数

1.1 AVG / SUM

• 只适用于数值类型的字段

1.2 MAX / MIN

• 适用于数值类型、字符串类型、日期时间类型的字段

1.3 COUNT

• 作用：计算指定字段在查询结构中出现的个数
• 注意：计算指定字段出现的个数时，是不计算NULL值的

SELECT COUNT(employee_id), COUNT(1), COUNT(2), COUNT(*)
FROM employees;

• 如果需要统计表中的记录数，适用COUNT(*)、COUNT(1)、COUNT(具体字段)

  其中，如果使用MyISAM存储引擎，则三者效率相同

  如果使用InnoDB存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(字段)

2. GROUP BY的使用

• SELECT中出现的非组函数的字段必须声明在GROUP BY 中；GROUP BY中声明的字段可以不出现在SELECT中
• GROUP BY声明在FROM后面、WHERE后面、ORDER BY前面

SELECT department_id, AVG(salary), SUM(salary)
FROM employees
GROUP BY department_id;

• 当使用ROLLUP时，不能同时使用ORDER BY 子句进行结果排序，即ROLLUP和ORDER BY是相互排斥的

3. HAVING的使用

• 用来过滤数据的

• 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE

  并且HAVING必须声明在GROUP BY 后面

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10, 20, 30, 40);
//后者效率高于前者
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10, 20, 30, 40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

• 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中

  当过滤条件中没有聚合函数时，则过滤条件建议声明在WHERE中

4. SQL 底层的执行原理

• sql92语法：

  SELECT ...,...,...(存在聚合函数)
  FROM ...,...,...
  WHERE 多表的连接条件 AND不包含聚合函数的过滤条件
  GROUP BY ..,..
  HAVING 包含聚合函数的过滤条件
  ORDER BY ...,...(ASC / DESC)
  LIMIT ...,...

• sql99语法：

SELECT ...,...,...(存在聚合函数)
FROM ...(LEFT / RIGHT) JOIN ... ON 多表的连接条件
WHERE 不包含聚合函数的过滤条件
GROUP BY ..,..
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC / DESC)
LIMIT ...,...

4.1 SQL语句的执行过程

1. FROM … , … - -> ON —>(LEFT / RIGHT JOIN) —> WHERE —> GROUP BY —> HAVING —>
2. SELECT —> DISTINCT — >
3. ORDER BY —> LIMIT

• 由SQL语句的执行过程也可以看出为什么过滤条件要尽可能的声明在WHERE中；以及WHERE中不能使用别名的原因也可以知道了

#8. 子查询

• 称谓的规范：外查询（或主查询）、内查询（子查询）
• 子查询要放在比较条件的右侧
• 子查询要放在括号内

#查询公司中哪些人的工资比"Abel"高
#法一：自连接法
SELECT e2.last_name, e2.salary
FROM employees e1, employees e2
WHERE e2.salary > e1.`salary`
AND e1.last_name = 'Abel';
#法二：子查询
SELECT last_name, salary
FROM employees
WHERE salary >  (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		);

1. 子查询的分类

• 从内查询返回的结果的条目数

  单行子查询 vs 多行子查询

• 内查询是否被执行多次

  相关子查询 vs 不相关子查询

1.1 单行子查询

1.2 多行子查询

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

1.3 相关子查询

• 查询员工中工资大于本部门平均工资的员工的薪水

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

• 在S ELECT中，除了GROUP BY 和LIMIT之外，其他位置都可以声明子查询

• EXISTS 和 NOT EXISTS关键字

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS(
	SELECT *
	FROM employees e2
	WHERE e1.employee_id = e2.`employee_id`
	);
	
	
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.`department_id` = e.`department_id`
		);

• 既可以使用子查询也可以使用自连接，优先使用自连接

#9. 创建和管理表

1. 如何创建数据库

CREATE DATABASE mytest1;

CREATE DATABASE mytest2 CHARACTER SET '';
#推荐
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET '';

2. 管理数据库

2.1 查看当前连接中的数据库都有哪些

SHOW DATABASES;

2.2 切换数据库

USE mytest2;

2.3 查看当前数据库中保存的数据表

SHOW TABLES FROM mysql;

2.4 查看当前使用的数据库

SELECT DATABASE() FROM DUAL;

2.5 修改数据库

ALTER DATABASE 数据库名 CHARACTER SET 'utf8'

2.5 删除数据库

DROP DATABASE mytest1;
#推荐
DROP DATABASE IF EXISTS mytest1;

3. 如何创建数据表

• 方式一：

CREATE TABLE IF NOT EXISTS myemp1(
				id INT,
				emp_name VARCHAR(15),
				hire_data DATE
				);
				
#查看表结构
DESC myemp1;
#查看创建表的语句结构
SHOW CREATE TABLE myemp1;

• 方式二：基于现有的表(查询语句中字段的别名，可以作为新创建的表的字段的名称)

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

4. 修改表

4.1 添加一个字段

#默认添加到表中最后一个字段的位置
ALTER TABLE myemp1
ADD salary DOUBLE(10, 2);
#添加到第一个字段的位置
ALTER TABLE myemp1
ADD phone_number varchar(20) first;
#添加到某字段后面
ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

4.2 修改一个字段：数据类型、长度、默认值

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) DEFAULT 'aaa';

4.3 重命名一个字段

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE (10, 2);

4.4 删除一个字段

ALTER TABLE myemp1
DROP COLUMN email;

5. 重命名表

#方式一：
RENAME TABLE myemp1
TO myemp11;

#方式二：
ALTER TABLE myemp2
RENAME TO myemp12;

6. 删除表

#不光将表结构删除，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS memp12;

7. 清空表

#清空表中的所有数据，但是表结构保留
TRUNCATE TABLE employees_copy;

8. DCL 中 COMMIT 和 ROLLBACK

• COMMIT: 提交数据，一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚

• ROLLBACK: 回滚数据。一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后

9. TRUNCATE TABLE 和 DELETE FROM

• TRUNCATE TABLE : 一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的

• DELETE FROM : 一旦执行此操作，表数据可以全部清除。同时，数据是可以实现回滚的

• 开发中不建议使用TRUNCATE TABLE

10. DDL 和 DML

• DDL的操作一旦执行，就不可回滚

  因为在执行完DDL操作之后，一定会执行一次COMMIT。

• DML的操作默认情况下，一旦执行，也是不可以回滚的， 但是如果在执行DML之前，执行了

  SET autocommit = FALSE，则执行的DML操作就可以实现回滚

11. MySQL8.0的新特性：DDL的原子化

#10. 数据处理之增删改

1. 添加数据

• 方式一：一条一条的添加数据

1. ````mysql
   INSERT INTO emp1
   VALUES (1, ‘Tom’, ‘2000-12-21’, 3400);

   2. ```mysql
      #指明要添加的字段
      INSERT INTO emp1(id, hire_date, salary, `name`)
      VALUES (2, '1999-9-9', 4000, 'Jerry');
      ```
   
   3. ```mysql
      #同时添加多条数据
      INSERT INTO emp1(id, hire_date, `name`)
      VALUES
      (3, '2010-10-21', '石雨晨'),
      (4, '2191-1-3', '尹恒');
      ```
   
   * 方式二：将查询结果插入到表中
   
   ```mysql
   INSERT INTO emp1(id, `name`, salary, hire_date)
   #查询语句(查询的字段一定要与添加到表的字段一一对应)
   SELECT employee_id, last_name, salary, hire_date
   FROM employees
   WHERE department_id IN (70, 60);
   ```
   
   ## 2. 更新数据
   
    ```mysql
    #不加WHERE可以实现批量修改
    UPDATE emp1
    SET hire_date = CURDATE(), salary = 6000
    WHERE id = 4;
    ```
   
   ## 3. 删除数据
   
   ```mysql
   DELETE FROM emp1
   WHERE id = 1;
   ```
   
   * DML操作默认情况下，执行完都会自动提交数据
   
     但是如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚
   
   # 4. MySQL8 新特性：计算列
   
   ```mysql
   CREATE TABLE test1(
   a INT,
   b INT,
   c INT GENERATED ALWAYS AS (a + b) VIRTUAL
   #字段c即为计算列
   );
   ```
   
   # #11. 数据类型
   
   * 定点数类型：DECIMAL
   
   * 指明字符集
   
   ```mysql
   CHARACTER SET 'utf8'
   ```
   
   ## 1. 整型
   
   * TINYINT
   * SMALLINT
   * MEDIUMINT
   * INT/INTEGER
   * BIGINT
   
   ### 1.1 可选属性
   
   * M：表示显示宽度，取值范围为(0, 255)。需要配合"ZEROFILL"使用
   
     从MySQL8.0 开始，整数数据类型不推荐使用显示宽度属性
   
   * UNSIGNED：无符号的
   
   ## 2. 浮点类型
   
   * FLOAT
   * DOUBLE 
   
   ### 2.1 数据精度说明
   
   * FLOAT(M, D)/DOUBLE(M, D):M称为精度，D称为标度；M = 整数位+小数位，D = 小数位 
   
   * 因为浮点数是不精准的，所以要避免使用 = 来判断两个数是否相等。也因此，使用定点数来代替浮点数
   
   ## 3. 定点数类型
   
   * DECIMAL(M, D):M称为精度，D称为标度。0 <= M <= 65, 0 <= D <= 30
   
   * 定点数在底层是以字符串的形式进行存储
   
   ## 4. 位类型：BIT
   
   * 使用SELECT命令查询位字段时，可以使用BIN()或HEX()函数进行读取
   
   ## 5. 日期与时间类型
   
   * YEAR：以4位字符串表示YEAR类型，格式为YYYY，最小值为1901，最大值为2155
   
     以2位字符串表示YEAR类型，最小为00，最大为99
   
     当取值为01到69时，表示2001到2069年
   
     当取值为70到99时，表示1970到1999年
   
   * TIME：格式为HH::MM:SS
   
   * DATE：格式为YYYY-MM-DD
   
     表示当前的日期，使用CURDATE()和NOW()
   
   * DATETIME：需要8个字节的存储空间，格式为YYYY-MM-DD HH:MM:SS
   
     表示当前的DATETIME，可以使用NOW()和 CURRENT_TIMESTAMP()
   
   * TIMESTAMP：格式与DATATIME相同，只需要4个字节的存储空间，存储的时间范围也小得多，只能存储1970到2038之间的时间
   
   * 修改时区：SET time_zone = '+8:00';
   * 使用TIMESTAMP存储的同一个时间值，在不同的时区查询会显示不同的时间
   
   * 开发中，一般使用DATETIME；
   
     一般存注册时间、商品发布时间，建议使用时间戳
   
   ## 6.文本字符串类型
   
   * CHAR：固定长度；浪费存储空间；效率高；
   
   * VARCHAR：可变长度；节省存储空间；效率低；
   
   * TEXT：由于实际存储的长度不确定，所以不允许TEXT类型的字段做主键；
   
     频繁使用的表不建议使用包含TEXT类型的字段，建议单独分出去，单独使用一个表
   
   * TINYTEXT
   
   * MEDIUMTEXT
   
   * LONGTEXT
   
   * ENUM：设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值
   
   * SET：表示一个字符串对象，可以包含多个成员，但成员的上限为64
   
     插入重复的SET类型成员时，会自动删除重复的成员
   
   ## 7. 二进制字符串类型
   
   * BINARY和VARBINARY:类似于CHAR和VARCHAR
   
   * TINYBLOB
   * BLOB:二进制大对象，比如图片、音频、视频
   * MEDIUMBLOB
   * LONGBLOB
   
   * TEXT和BLOB的使用注意事项：
   
     ①由于执行删除后会在数据表中留下很大的空洞，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理
   
     ②如果需要对大文本字段进行模糊查询，可以使用前缀索引。并且仍然需要避免对大文本字段进行检索
   
     ③把BLOB或TEXT列分离到单独的表中。
   
   ## 8. JSON类型
   
   ## 9.空间类型
   
   ## 10. 总结
   
   * 在定义数据类型的时候，整数，就用INT；小数，就用DECIMAL；如果是日期与时间，就用DATETIME
   
   # #12. 约束(constrant)
   
   ## 1. 为什么
   
   * 为了保证数据完整性
   * 实体完整性
   * 域完整性
   * 引用完整性
   * 用户自定义完整性
   
   ## 2. 约束的分类
   
   * 约束的字段的个数
   
     单列约束 vs 多列约束
   
   * 约束的作用范围
   
     列级约束：将此约束声明在对应字段的后面
   
     表级约束：将此约束声明在所有字段的后面声明出来
   
   ## 3. 约束的作用
   
   ### 3.1 NOT NULL(非空约束)
   
   * 只能某个列单独限定非空，不能组合非空
   
   ```mysql
   CREATE TABLE test1(
   id INT NOT NULL,
   last_name VARCHAR(15) NOT NULL,
   email VARCHAR(25),
   salary DECIMAL(10, 2)
   );
   
   ALTER TABLE test1
   MODIFY email VARCHAR(25) NOT NULL;
   ```
   
   ### 3.2 UNIQUE(唯一性约束)
   
   * 唯一约束允许列值为空
   * 在创建唯一约束的时候，如果不给约束命名，就默认和列明相同
   
   ```mysql
   CREATE TABLE test2(
   id INT UNIQUE,
   last_name VARCHAR(15),
   email VARCHAR(25),
   salary DECIMAL(10, 2),
   
   #表级约束
   CONSTRAINT uni_test2_email UNIQUE(email)
   );
   
   ALTER TABLE test2
   ADD UNIQUE(salary);
   
   ALTER TABLE test2
   MODIFY last_name VARCHAR(15) UNIQUE;
   ```
   
   * 删除唯一性约束
   
     删除唯一约束只能通过删除唯一索引的方式删除，唯一索引名和唯一约束名一样
   
     如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合名，就默认和组合中第一个列名相同
   
   ```mysql
   ALTER TABLE test2
   DROP INDEX last_name;
   ```
   
   ### 3.3 PRIMARY KEY(主键约束)
   
   * 主键约束相当于唯一约束+非空约束的组合，既不允许重复，也不允许出现空值
   * 一个表最多只能有一个主键约束
   * MySQL的主键名总是PRIMARY
   * 当创建主键约束时，系统默认会在所在的列或列组合上创建对应的主键索引 
   * 在实际开发中，不会去删除主键约束的
   
   #### 3.31 自增列：AUTO_INCREMENT
   
   * 作用：某个字段的值自增
   * 一个表最多只能有一个自增长列
   
   ```mysql
   CREATE TABLE test4(
   id INT PRIMARY KEY AUTO_INCREMENT,
   last_name VARCHAR(15)
   );
   ```
   
   * MySQL8.0 新特性：自增变量的持久化
   
     8.0 将自增主键的计数器持久化到重做日志中，如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值
   
   ### 3.4 FOREIGN KEY(外键约束)
   
   * 关联的两个表，从表中添加数据的值必须是主表中已经存在的值
   
   * 作用：限定某个表的某个字段的引用完整性
   
   * 特点：①从表的外键列，必须引用主表的主键或唯一约束的列
   
     ②删除时，先删除从表再删除主表
   
   ````mysql
   #创建主表
   CREATE TABLE dept1(
   dept_id INT PRIMARY KEY,
   dept_name VARCHAR(15)
   );
   #创建从表
   CREATE TABLE emp1(
   emp_id INT PRIMARY KEY AUTO_INCREMENT,
   emp_name VARCHAR(15),
   department_id INT,
   
   #表级约束
   CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
   );
   
   SELECT * FROM information_schema.table_constraints
   WHERE table_name = 'emp1';
   
   #演示外键的效果
   INSERT INTO dept1
   VALUES(10, 'IT');
   
   INSERT INTO emp1
   VALUES(1001, 'Tom', 10);

• 删除外键约束流程：先删除外键约束，再删除索引

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

SHOW INDEX FROM 表名称;

ALTER TABLE 从表名 DROP INDEX 索引名;

• 开发中：不建议使用外键，而是从应用层面的附加逻辑，来实现外键约束的功能

3.4.1 约束等级

• Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
• Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null
• No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
• Restrict方式：同no action
• Set default方式：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
• 如果没有指定等级，相当于Restrict方式

对于外键约束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT 的方式

3.5 CHECK(检查约束)

• 作用：检查某个字段的值是否符合xx要求，一般指的是值的范围

3.6 DEFAULT(默认值约束)

4. 如何添加约束

• CREATE TABLE 时添加约束

• ALTER TABLE 时增加约束、删除约束

• 如何查看表中的约束

SELECT *
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE table_name = 'employees';

#13. 视图

• 视图是一种虚拟表，本身是不具有数据的

• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表

• 可以将视图理解为存储起来的SELECT语句

1. 创建视图

CREATE VIEW 视图名称
AS 查询语句

#确定视图中字段名的方式一
CREATE VIEW vu_emp2
AS
SELECT employee_id emp_id, last_name lname, salary
FROM emps;

#确定视图中字段名的方式二
CREATE VIEW vu_emp3(emp_id, NAME, monthly_sal)
AS
SELECT employee_id, last_name, salary
FROM emps;

#视图中的字段在基表中没有对应的字段
CREATE VIEW vu_emps_sal
AS
SELECT department_id, AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;

#利用视图对数据进行格式化
CREATE VIEW vu_emp_dept1
AS 
SELECT CONCAT(e.last_name, '(', d.department_name, ')') emp_info
FROM emps e JOIN depts d
ON e.department_id = d.department_id;

2. 查看视图

#1.查看数据库的表对象、视图对象
SHOW TABLES;

#2.查看视图的结构
DESCRIBE vu_emp1;

#3.查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp1';

#4.查看视图的详细定义信息
SHOW CREATE VIEW vu_emp1;

3. 更新视图中的数据

• 更新视图的数据，会修改基表中的数据，反之同理