数据库与MySQL使用详解：概念、操作及SQL语言-优快云博客

1. 数据库概述与MySQL的使用演示

1.1 数据库概述

1.1.1 数据库相关概念

DB：数据库（Database），即存取数据的仓库，本质是一个文件系统，保存了一系列有组织的数据
DBMS：数据库管理系统（Database Management System），是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据，常见的有：Oracle、MySQL、SQL Server、DB2、Access、Sybase、Informix、PostgreSQL
SQL：结构化查询语言，专门用来与数据库通信的语言
- DDL数据定义语言：定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。主要的语句关键字包括CREATE、DROP、ALTER等。
- DML数据操作语言：用于添加、删除、更新和查询数据库记录，并检查数据完整性。主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等
- DCL数据控制语言：用于定义数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
总结：在DBMS（MySQL软件）上通过sql语言操作DB（数据库）

1.1.2 数据库与数据库管理系统的关系

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

1.1.3 RDBMS与非RDBMS

①.关系型数据库（RDBMS）

实质就是把复杂的数据结构归结为简单的二元关系（二维表格形式），以行（row）和（column）的形式存储数据，以便于用户理解。这一系列的行与列称为表（table），一组表组成了一个库（database）。表与表之间的数据记录有关系（relational）。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库。SQL就是关系型数据库的查询语言。
优势：
- 复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询
- 事物支持：使得对于安全性能很高的数据访问要求得以体现

②.非关系型数据库（非RDBMS）

基于键值对存储数据，不需要经过SQL层的解析，性能非常高。同时，通过减少不常用的功能，进一步提高性能。非关系型数据库：Redis（键值型数据库）、MongoDB（文档型数据库）

1.1.4 MySQL介绍

MySQL是一个开放源代码的关系型数据库管理系统
开源的、关系型的数据库；支持千万级数据量的存储，大型的数据库
MySQL使用标准的SQL数据语言形式。
MySQL可以允许运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP和Ruby等。

1.1.5 关系型数据库设计规则

关系型数据库的典型数据结构就是数据表，这些数据表的组成都是结构化的（Structured）
将数据放到表中，表再放到库中
一个数据库可以有多个表，每个表都有一个名字，用来标识自己。表明具有唯一性
表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中“类”的设计

①. 表、记录、字段

E-R（entity-relationship，实体-联系）模型中有三个主要概念：实体集、属性、联系集
一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据表中的一列（column），也称为一个字段（field）。
ORM思想 (Object Relational Mapping)体现：
- 数据库中的一个表 <---> Java或Python中的一个类
- 表中的一条数据 <---> 类中的一个对象（或实体）
- 表中的一个列 <---> 类中的一个字段、属性(field)

②. 表的关联关系

表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。四种：一对一关联、一对多关联、多对多关联、自我引用
一对一关联的两种建表原则：
- 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
- 外键是主键：主表的主键和从表的主键，形成主外键关系。

一对多关联的建表原则：
- 在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键
多对多关联：要表示多对多关系，必须创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。
自我引用

1.2 MySQL的使用演示

1 查看所有的数据库
show databases;
2 创建数据库
create database 数据库名;
3 使用自己的数据库
use 数据库名
4 查看某个库的所有表格
show tables;要求前面有use语句
show tables from 数据库名
5 创建新的表格
create table 表名称(
    字段名 数据类型,
    字段名 数据类型
);
#创建学生表
create table student(
    id int,
    name varchar(20) #说明名字最长不超过20个字符
);
6 查看一个表的数据
select * from 数据库表名称
7 添加一条记录
insert into 表名 values(值列表);
insert into student values(1,'张三');
insert into student values(2,'张四');
8 查看表的创建信息
show create table student\G
#结果如下
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
9 查看数据库的创建信息
show create database atguigudb\G
10 删除表格
drop table 表名称
11 删除数据库
drop database 数据库名

2. SQL之SELECT使用

2.0 SQL语言规范

SQL可以写在一行或者多行，每条命令以;或\g或\G结束
字符串型和日期时间类型的数据可以使用单引号''表示
列的别名，尽量使用双引号" "，而且不建议省略as
MySQL在Windows环境下是大小写不敏感的
MySQL在Linux环境下是大小写敏感的
推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL关键字、函数名、绑定变量等都大写

2.1 基本的SELECT语句

1 语法：
SELECT 标识选择哪些列
FROM   标识从哪个表中选择
在生产环境下，不推荐直接使用SELECT * 进行查询，
虽然通配符可以节省输入查询语句的时间，但是获取不需要的列的数据通常会降低查询和使用的应用程序的效率
2 DISTINCT去除重复行，默认情况下，查询会返回全部行
DISTINCT需要放到所有列名的前面，其实是对后面所有列名的组合进行去重
SELECT DISTINCT dept_id FROM employees;
3 空值参与运算，所有运算符或列值遇到null值，运算的结果都为null
4 查询常数，在SELECT的查询结果中增加一列固定的常数列
SELECT '尚硅谷' as corporation,last_name FROM emps;
5 显示表结构：使用DESCRIBE或DESC命令，表示表结构
DESCRIBE emps;
DESC emps;
6 过滤数据：使用WHERE子句，将不满足条件的行过滤掉，WHERE子句紧随FROM子句
SELECT 字段1，字段2
FROM 表名
WHERE 过滤条件

2.2 运算符

①.算术运算符

加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL

#计算出员工的年基本工资
SELECT employee_id,salary,salary * 12 annual_sal 
FROM employees;
#筛选出employee_id是偶数的员工
SELECT * FROM employees
WHERE employee_id MOD 2 = 0;

②.比较运算符

如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。
IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL
LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。“%”：匹配0个或多个字符，“_”只能匹配一个字符
REGEXP运算符用来匹配字符串，语法格式为：expr REGEXP 匹配条件。如果expr满足匹配条件，返回1；如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。
- ‘^’：匹配以该字符后面的字符开头的字符串。
- ‘$’：匹配以该字符前面的字符结尾的字符串。
- ‘.’：匹配任何一个单字符。
- “[...]”：匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
- ‘*’：匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

③.逻辑运算符

OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

SELECT last_name, job_id
FROM   employees
WHERE  job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
AND    job_id LIKE '%MAN%';
#查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT employee_id,salary FROM employees 
WHERE NOT (salary >= 9000 AND salary <= 12000);
SELECT employee_id,salary FROM employees 
WHERE salary <9000 OR salary > 12000;
SELECT employee_id,salary FROM employees 
WHERE salary NOT BETWEEN 9000 AND 12000;

④.运算符的优先级

2.3 排序与分页

2.3.1 排序

排序规则：使用ORDER BY 子句排序，在SELECT语句的结尾
    ASC(ascend): 升序
    DESC(descend): 降序
单列排序：
SELECT employee_id, last_name, salary*12 annsal
FROM   employees
ORDER BY annsal DESC;
多列排序：
SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;
- 可以使用不在SELECT列表中的列排序。
- 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。
如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

2.3.2 分页

MySQL中使用LIMIT实现分页,LIMIT子句必须放在整个SELECT语句的最后！
格式：LIMIT [位置偏移量,] 行数
第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数
如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）
第二个参数“行数”指示返回的记录条数。
举例：
--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;
MySQL8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
分页显示公式：(当前页数-1)*每页条数,每页条数
SELECT * FROM table
LIMIT (PageNO - 1)*PageSize,PageSize;

2.4 多表查询

2.4.1 多表查询分类讲解

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
外连接：两个表在连接过程中除了返回满足条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（NULL）
- 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表
- 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表

2.4.2 SQL99语法实现多表查询

##########基本语法##########
使用JOIN ... ON ... 子句创建连接的语法结构
SELECT table1.column,table2.column,table3.column
FROM table1
    JOIN table2 ON table1和table2的连接条件
        JOIN table3 ON table2和table3的连接条件
##########语法说明##########
-可以使用ON子句指定额外的连接条件
-这个连接条件是与其他条件分开的
-ON子句使语句具有更高的易读性
-关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都表示内连接
##########内连接（INNER JOIN）的实现##########
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
举例1：
SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);
举例2：
SELECT employee_id, city, department_name
FROM   employees e 
JOIN   departments d
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;
##########外连接（OUTER JOIN）的实现##########
左外连接：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
举例1：
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;
右外连接：
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
举例1：
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

2.5 单行函数

2.5.1 数值函数

| LEAST(e1,e2,e3…)    | 返回列表中的最小值 |
| GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
| MOD(x,y)            | 返回X除以Y后的余数 |
| RAND()              | 返回0~1的随机值    |
| RADIANS(x) | 将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，其中，参数x为弧度值 |
| BIN(x)        | 返回x的二进制编码        |
| HEX(x)        | 返回x的十六进制编码      |
| OCT(x)        | 返回x的八进制编码        |
| CONV(x,f1,f2) | 返回f1进制数变成f2进制数  |

2.5.2 字符串函数

MySQL中，字符串的位置是从1开始的。
| ASCII(S)                          | 返回字符串S中的第一个字符的ASCII码值                         
| CHAR_LENGTH(s)                    | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同           |
| LENGTH(s)                         | 返回字符串s的字节数，和字符集有关                            |
| CONCAT(s1,s2,......,sn)           | 连接s1,s2,......,sn为一个字符串                              |
| CONCAT_WS(x, s1,s2,......,sn)     | 同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x           |
| INSERT(str, idx, len, replacestr) | 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr |
| REPLACE(str, a, b)                | 用字符串b替换字符串str中所有出现的字符串a                    |
| UPPER(s) 或 UCASE(s)              | 将字符串s的所有字母转成大写字母                              |
| LOWER(s)  或LCASE(s)              | 将字符串s的所有字母转成小写字母                              |
| LEFT(str,n)                       | 返回字符串str最左边的n个字符                                 |
| RIGHT(str,n)                      | 返回字符串str最右边的n个字符                                 |
| LPAD(str, len, pad)               | 用字符串pad对str最左边进行填充，直到str的长度为len个字符     |
| RPAD(str ,len, pad)               | 用字符串pad对str最右边进行填充，直到str的长度为len个字符     |
| LTRIM(s)                          | 去掉字符串s左侧的空格                                        |
| RTRIM(s)                          | 去掉字符串s右侧的空格                                        |
| TRIM(s)                           | 去掉字符串s开始与结尾的空格                                  |
| TRIM(s1 FROM s)                   | 去掉字符串s开始与结尾的s1                                    |
| TRIM(LEADING s1 FROM s)           | 去掉字符串s开始处的s1                                        |
| TRIM(TRAILING s1 FROM s)          | 去掉字符串s结尾处的s1                                        |
| REPEAT(str, n)                    | 返回str重复n次的结果                                         |
| SPACE(n)                          | 返回n个空格                                                  |
| STRCMP(s1,s2)                     | 比较字符串s1,s2的ASCII码值的大小                             |
| SUBSTR(s,index,len)               | 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同 |
| LOCATE(substr,str)                | 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0 |
| ELT(m,s1,s2,…,sn)                 | 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn |
| FIELD(s,s1,s2,…,sn)               | 返回字符串s在字符串列表中第一次出现的位置                    |
| FIND_IN_SET(s1,s2)                | 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
| REVERSE(s)                        | 返回s反转后的字符串                                          |
| NULLIF(value1,value2)             | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 |

2.5.3 日期和时间函数

################获取日期、时间###############
|CURDATE(),CURRENT_DATE() | 返回当前日期，只包含年、月、日 |
|CURTIME(),CURRENT_TIME() | 返回当前时间，只包含时、分、秒 |
|NOW()/SYSDATE()/CURRENT_TIMESTAMP()/LOCALTIME()/LOCALTIMESTAMP() | 返回当前系统日期和时间 |
|UTC_DATE() | 返回UTC（世界标准时间）日期 |
|UTC_TIME() | 返回UTC（世界标准时间）时间 |
################日期与时间戳的转换###############
|UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884 |
|UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回 |
|FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
################获取月份、星期、星期数、天数###############
|YEAR(date)/MONTH(date)/DAY(date) | 返回具体的日期值 |
|HOUR(time)/MINUTE(time)/SECOND(time) | 返回具体的时间值 |
|MONTHNAME(date) | 返回月份：January，...  |
|DAYNAME(date) | 返回星期几：MONDAY，TUESDAY.....SUNDAY |
|WEEKDAY(date) | 返回周几，注意，周1是0，周2是1，。。。周日是6 |
|QUARTER(date) | 返回日期对应的季度，范围为1～4 |
|WEEK(date)，WEEKOFYEAR(date) | 返回一年中的第几周 |
|DAYOFYEAR(date) | 返回日期是一年中的第几天 |
|DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
|DAYOFWEEK(date) | 返回周几，注意：周日是1，周一是2，。。。周六是7 |

2.5.4 流程控制函数

1. IF(value,value1,value2)：如果value的值为TRUE，返回value1，否则返回value2
SELECT IF(1 > 0,'正确','错误') 
->正确
2. IFNULL(value1, value2)：如果value1不为NULL，返回value1，否则返回value2
SELECT IFNULL(null,'Hello Word')
->Hello Word
3. CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END
相当于Java的if...else if...else...
SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0
4. CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END
相当于Java的switch...case... 
SELECT CASE 1 
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是谁'
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END  "描述"
FROM employees; 
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款' 
								   WHEN 2 THEN '已付款' 
								   WHEN 3 THEN '已发货'  
								   WHEN 4 THEN '确认收货'  
								   ELSE '无效订单' END 
FROM t_order;

2.5.5 加密与解密函数

|PASSWORD(str) | 返回字符串str的加密版本，41位长的字符串。加密结果`不可逆`，常用于用户的密码加密 |
|MD5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL|
|SHA(str) | 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。`SHA加密算法比MD5更加安全`。 |
|ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
|DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |

2.6 聚合函数

1. 可以对**数值型数据**使用AVG和SUM函数。
2. 可以对**任意数据类型**的数据使用MIN和MAX函数。
3. COUNT(*)返回表中记录总数，适用于**任意数据类型**。
4. 可以使用GROUP BY子句将表中的数据分成若干组
5. GROUP BY中使用WITH ROLLUP，使用WITH ROLLUP关键字之后，
在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
6. HAVING子句过滤分组
-行已经被分组
-使用了聚合函数
-满足HAVING子句中条件的分组将被显示
-HAVING不能被单独使用，必须要跟GROUP BY一起使用
比如：
SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;
-非法使用聚合函数：不能在where子句中使用聚合函数
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;
7. WHERE和HAVING的对比
-WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；
因为，在查询语法结构中，WHERE在GROUP BY之前，所以无法对分组结果进行筛选。
-HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件
-如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选，决定了在关联查询中，WHERE比HAVING更高效。

2.7 SELECT的执行过程

①.查询的结构

#################查询的结构###################
#方式1：
SELECT ...,...,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,...,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

②.SELECT执行顺序

关键字的顺序是不能颠倒的：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
SELECT语句的执行顺序：（在MySQL和Oracle中，SELECT的执行顺序基本相同）
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT的字段 -> DISTINCT -> ORDER BY -> LIMIT
比如这样的一个SQL语句，那么它的关键字顺序和执行顺序是下面这样的：
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player p JOIN team t ON p.team_id = t.team_id      # 顺序 1
WHERE height > 1.80                                     # 顺序 2
GROUP BY p.team_id                                      # 顺序 3
HAVING num > 2                                          # 顺序 4
ORDER BY num DESC                                       # 顺序 6
LIMIT 2                                                 # 顺序 7
在SELECT语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，
然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在SQL的执行过程中，对于我们来说是不可见的。

2.8 子查询

子查询（内查询）在主查询之前一次执行完成
子查询的结果被主查询（外查询）使用
按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询
按内查询是否被执行多次，将子查询划分为相关（或关联）子查询和不相关（非关联）子查询
注意事项：
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询

2.8.1 单行子查询

①.单行比较操作符

②.代码示例

1. 查询工资大于149号员工工资的员工信息
SELECT last_name
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE employee_id = 149
);
2. 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
) AND salary > (
    SELECT salary
    FROM employees
    WHERE employee_id = 143
);
3. 返回公司工资最少的员工last_name，job_id，和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees;
);
4. 查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
方式1：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id IN (
    SELECT manager_id
    FROM employees
    WHERE employee_id IN (141,174)
)AND department_id IN (
    SELECT department_id
    FROM employees
    WHERE employee_id IN (141,174)
)AND employee_id NOT IN(174,141);
方式2：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) IN (
    SELECT manager_id,department_id
    FROM employees
    WHERE employee_id IN (141,174)
)AND employee_id NOT IN(174,141);

③.HAVING中子查询

首先执行子查询
向子查询中的HAVING子句返回结果
查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT  department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);

④.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 employees 
               WHERE location_id = 1800)
         THEN
               'Canada'
         ELSE
               'USA'
         END) location
)
FROM employees;

⑤.子查询中的空值问题

SELECT last_name,job_id
FROM employees
WHERE job_id = (
        SELECT job_id
        FROM employees
        WHERE last_name = 'Haas'
);
no rows selected
子查询不返回任何行

⑥.非法使用子查询

SELECT employee_id,last_name
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
    GROUP BY department_id
);
多行子查询使用单行比较符

2.8.2 多行子查询

①.多行比较符

②.代码示例

题目1：返回其他job_id中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、Job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
    SELECT salary
    FROM employee
    WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
题目2：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
    SELECT salary
    FROM employee
    WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';
题目3：查询平均工资最低的部门id
方式1：
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
);
方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
        SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
)

③.空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
    SELECT manager_id
    FROM employees
);

2.8.3 相关子查询

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

①.代码示例

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
方式1：相关子查询
SELECT last_name,salary,department_id
FROM employees outer
WHERE salary > (
        SELECT AVG(salary)
        FROM employees
        WHERE department_id = outer.department_id
)
方式2：在FROM中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

3. SQL之DDL、DML、DCL使用

3.0 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、LONGBLOB |
| JSON类型 | JSON对象、JSON数组 |
| 空间数据类型 | 单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |
######################常用的几类类型###########################
| INT           | 从-2^31到2^31-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 |

3.1 创建和管理表

3.1.1 创建和管理数据库

#######################创建数据库###############################
方式1：创建数据库
CREATE DATABASE 数据库名;
方式2：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）
CREATE DATABASE IF NOT EXISTS 数据库名;
#######################使用数据库###############################
查看当前所有的数据库：SHOW DATABASES;
查看当前正在使用的数据库：SELECT DATABASE();#使用的一个mysql中的全局函数
查看指定库下的所有表：SHOW TABLES FROM 数据库名;
查看数据库的创建信息：SHOW CREATE DATABASE 数据库名;
使用、切换数据库：USE 数据库名;
#######################修改数据库###############################
更改数据库字符集：
ALTER DATABASE 数据库名 CHARACTER SET 字符集;#比如：gbk、utf8等
#######################删除数据库###############################
方式1：删除指定的数据库
DROP DATABASE 数据库名;
方式2：删除指定的数据库
DROP DATABASE IF EXISTS 数据库名;

3.1.2 创建表

语法格式：
CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型 [约束条件] [默认值],
	字段2 数据类型 [约束条件] [默认值],
	字段3 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);
说明：
-必须指定表名，列名(或字段名)，数据类型，长度
-可选条件：约束条件、默认值
举例1：
--创建表
CREATE TABLE emp(
    -- int类型
    emp_id INT,
    -- 最多保存20个中英文字符
    emp_name VARCHAR(20),
    -- 总位数不超过15位
    salary DOUBLE,
    -- 日期类型
    birthday DATE
);
举例2：
CREATE TABLE dept(
    -- int类型，自增
    dept_no INT(2) AUTO_INCREMENT,
    dept_name VARCHAR(14),
    loc VARCHAR(13),
    -- 主键
    PRIMARY KEY (dept_no)
);
查看数据表结构：不仅可以查看创建表的详细语句，还可以查看存储引擎和字符编码
SHOW CREATE TABLE 表名;

3.1.3 修改表：指的是修改数据库中已经存在的数据表的结构

①.向已有的表中添加列
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
举例：
ALTER TABLE dept
ADD job_id varchar(15);
②.修改现有表中的列
可以修改列的数据类型、长度、默认值和值
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】 【FIRST|AFTER 字段名】;
举例：
ALTER TABLE dept
MODIFY last_name VARCHAR(30);
ALTER TABLE dept
MODIFY salary double(9,2) default 1000;
③.重命名现有表中的列
使用 CHANGE old_column  new_column  dataType子句重命名列
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
举例：
ALTER TABLE dept
CHANGE department_name dept_name varchar(15); 
④.删除现有表中的列
ALTER TABLE 表名 DROP 【COLUMN】 字段名
举例：
ALTER TABLE dept
DROP COLUMN job_id;

3.1.4 重命名表

方式一：使用RENAME
RENAME TABLE emp
TO myemp;
方式2：
ALTER table dept
RENAME [TO] detail_dept;  -- [TO]可以省略

3.1.5 删除表：DROP TABLE 语句不能回滚

DROP TABLE [IF EXISTS] 数据表1 [，数据表2，...，数据表n]
DROP TABLE dept;
DROP TABLE 语句不能回滚

3.1.6 清空表

- TRUNCATE TABLE语句：
  - 删除表中所有的数据
  - 释放表的存储空间
  - TRUNCATE语句不能回滚，而使用DELETE语句删除数据，可以回滚
- 举例：
TRUNCATE TABLE detail_dept;

3.2 数据处理之增删改

①.插入数据

VALUES的方式添加,使用这种语法一次只能向表中插入一条数据,字符和日期型数据应包含在单引号中
情况1：为表的所有字段按默认顺序插入数据
INSERT INTO 表名
VALUES(value1,value2,...);
情况2：为表的指定字段插入数据
INSERT INTO 表名(column1 [, column2, ... , columnn])
VALUES(value1 [, value2, ... , valuen]);
情况3：同时插入多条记录，用','隔开
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还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入
只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。
基本语法格式如下：
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT (src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]
举例：
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';

②.更新数据

使用UPDATE语句更新数据：
 -可以一次更新多条数据
 -如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
 -使用WHERE子句指定需要更新的数据。
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]
举例：
UPDATE employees
SET department_id = 70
WHERE employee_id = 113

③.删除数据

使用DELETE语句从表中删除数据
DELETE FROM departments
WHERE  department_name = 'Finance';

3.3 约束

3.3.1 NOT NULL：非空约束，规定某个字段不能为空

1. 建表时：
CREATE TABLE 表名称(
	字段名  数据类型,
    字段名  数据类型 NOT NULL,  
    字段名  数据类型 NOT NULL
);
举例：
CREATE TABLE student(
	sid int,
    sname varchar(20) not null,
    tel char(11) ,
    cardid char(18) not null
);
insert into student values(1,'张三','13710011002','110222198912032545'); #成功
insert into student values(2,'李四','13710011002',null);#身份证号为空
ERROR 1048 (23000): Column 'cardid' cannot be null
2. 建表后
alter table 表名称 modify 字段名 数据类型 not null
举例：
ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;
3. 删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null，相当于修改某个非注解字段，该字段允许为空
alter table 表名称 modify 字段名 数据类型;#去掉not null，相当于修改某个非注解字段，该字段允许为空
举例：
ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

3.3.2 UNIQUE：唯一约束，规定某个字段在整个表中是唯一的

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL会给唯一约束的列上默认创建一个唯一索引。

1. 建表时
create table 表名称(
	字段名  数据类型,
    字段名  数据类型  unique,  
    字段名  数据类型  unique key,
    字段名  数据类型
);
create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    [constraint 约束名] unique key(字段名)
);
举例：
create table student(
	sid int,
    sname varchar(20),
    tel char(11) unique,
    cardid char(18) unique key
);
CREATE TABLE USER(
    id INT NOT NULL,
    NAME VARCHAR(25),
    PASSWORD VARCHAR(16),
    -- 使用表级约束语法，表示用户名和密码组合不能重复
    CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
2. 建表后指定唯一键约束
#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式1：
alter table 表名称 add unique key(字段列表); 
#方式2：
alter table 表名称 modify 字段名 字段类型 unique;
举例：
create table student(
	sid int primary key,
    sname varchar(20),
    tel char(11) ,
    cardid char(18) 
);
alter table student add unique key(tel);
alter table student add unique key(cardid);
3. 关于复合唯一约束
#学生表
create table student(
	sid int,	#学号
    sname varchar(20),			#姓名
    tel char(11) unique key,  #电话
    cardid char(18) unique key #身份证号
);
#课程表
create table course(
	cid int,  #课程编号
    cname varchar(20)     #课程名称
);
#选课表
create table student_course(
    id int,
	sid int,
    cid int,
    score int,
    unique key(sid,cid)  #复合唯一
);
4. 删除唯一约束
- 添加唯一性约束的列上也会自动创建唯一索引。
- 删除唯一约束只能通过删除唯一索引的方式删除。
- 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
- 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
SELECT * 
FROM information_schema.table_constraints 
WHERE table_name = '表名'; #查看都有哪些约束
ALTER TABLE USER 
DROP INDEX uk_name_pwd;
注意：可以通过 `show index from 表名称; `查看表的索引

3.3.3 PRIMARY KEY：主键(非空且唯一)约束，用来唯一标识表中的一行记录

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

①.添加主键约束

1. 建表时指定主键约束
create table 表名称(
	字段名  数据类型  primary key, #列级模式
    字段名  数据类型,  
    字段名  数据类型  
);
create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    [constraint 约束名] primary key(字段名) #表级模式
);
举例：
create table temp(
	id int primary key,
    name varchar(20)
);
insert into temp values(1,'张三');#成功
insert into temp values(2,'李四');#成功
insert into temp values(1,'王五');#失败
ERROR 1062 (23000): Duplicate（重复） entry（键入，输入） '1' for key 'PRIMARY'
###################################列级约束
CREATE TABLE emp4(
    id INT PRIMARY KEY AUTO_INCREMENT ,
    NAME VARCHAR(20)
);
###################################表级约束
CREATE TABLE emp5(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(20),
    pwd VARCHAR(15),
    CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);
2. 建表后增加主键约束
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键
ALTER TABLE student ADD PRIMARY KEY (sid);
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

②.关于复合主键

create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    primary key(字段名1,字段名2)  #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);
#学生表
create table student(
	sid int primary key,  #学号
    sname varchar(20)     #学生姓名
);
#课程表
create table course(
	cid int primary key,  #课程编号
    cname varchar(20)     #课程名称
);
#选课表
create table student_course(
	sid int,
    cid int,
    score int,
    primary key(sid,cid)  #复合主键
);

③.删除主键约束

alter table 表名称 drop primary key;
ALTER TABLE student DROP PRIMARY KEY;
说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

3.3.4 自增列：AUTO_INCREMENT

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时，可设置自增长
自增长列约束的列必须是键列（主键列，唯一键列）
自增约束的列的数据类型必须是整数类型
如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

1. 建表时
create table 表名称(
	字段名  数据类型  primary key auto_increment,
    字段名  数据类型  unique key not null,  
    字段名  数据类型  unique key,
    字段名  数据类型  not null default 默认值, 
);
create table 表名称(
	字段名  数据类型 default 默认值 ,
    字段名  数据类型 unique key auto_increment,  
    字段名  数据类型 not null default 默认值,,
    primary key(字段名)
);
create table employee(
	eid int primary key auto_increment,
    ename varchar(20)
);
2. 建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;
create table employee(
	eid int primary key ,
    ename varchar(20)
);
alter table employee modify eid int auto_increment;
3. 如何删除自增主键
#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
alter table employee modify eid int;

3.3.5 FOREIGN KEY：外键约束

①.主表和从表、父表和子表

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表
例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

②.特点

从表的外键列，必须引用/参考主表的主键或唯一约束的列。因为被依赖/被参考的值必须是唯一的
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名
创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
删表时，先删从表（或先删除外键约束），再删除主表
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
在“从表”中指定外键约束，并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
删除外键约束后，必须手动删除对应的索引

③.添加外键约束

1. 建表时
create table 主表名称(
	字段1  数据类型  primary key,
    字段2  数据类型
);
create table 从表名称(
	字段1  数据类型  primary key,
    字段2  数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列
举例：
create table dept( #主表
	did int primary key,		#部门编号
    dname varchar(50)			#部门名称
);
create table emp(#从表
	eid int primary key,    #员工编号
    ename varchar(5),       #员工姓名
    deptid int,				#员工所在的部门
    foreign key (deptid) references dept(did)   #在从表中指定外键约束
    #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept
2. 建表后
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];
举例：
create table dept(
	did int primary key,		#部门编号
    dname varchar(50)			#部门名称
);
create table emp(
	eid int primary key,    #员工编号
    ename varchar(5),       #员工姓名
    deptid int				#员工所在的部门
);
#这两个表创建时，没有指定外键的话，那么创建顺序是随意
alter table emp add foreign key (deptid) references dept(did);

④.删除外键约束

(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

4. 其他数据库对象

4.0 常见的数据库对象

4.1 视图

4.1.1 为什么使用视图

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

4.1.2 视图的理解

视图是一种虚拟表，本身不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念
试图建立在已有的表的基础上，视图赖以建立的这些表称为基表
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的SELECT语句
视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便

4.1.3 创建视图

在CREATE VIEW语句中嵌入子查询
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
精简版：
CREATE VIEW 视图名称
AS 查询语句

①.创建单表视图

CREATE VIEW empvu80
AS 
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
查询视图：
SELECT *
FROM empvu80;

②.创建多表联合视图

举例1：
CREATE VIEW empview 
AS 
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
举例2：
CREATE VIEW emp_dept
AS 
SELECT ename,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;
举例3：
CREATE VIEW	dept_sum_vu
(name, minsal, maxsal, avgsal)
AS 
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id 
GROUP BY  d.department_name;

③.基于视图创建视图

举例：联合“emp_dept”视图和“emp_year_salary”视图查询员工姓名、部门名称、年薪信息创建 “emp_dept_ysalary”视图。
CREATE VIEW emp_dept_ysalary
AS 
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

4.1.4 查看视图

语法1：查看数据库的表对象、视图对象
SHOW TABLES;
语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
语法3：查看视图的属性信息
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

4.1.5 修改、删除视图

修改视图：
方式1：使用CREATE OR REPLACE VIEW 子句修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS 
SELECT  employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;
方式2：ALTER VIEW
ALTER VIEW 视图名称 
AS
查询语句
删除视图：删除视图只是删除视图的定义，并不会删除基表的数据。
DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;
基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。