学习记录分享（MySQL）

一、引言

1.1现有的数据存储方法有哪些？

java中存储数据（变量，对象，数组，集合），数据都是保存在内存中，属于瞬时状态数据
文件(File)存储数据，保存在硬盘上，数据持久化状态存储

1.2以上存储方式存在哪些缺点？

程序停止，数据就没了
文件存储的数据，没有数据类型的区分
没有访问安全限制
没有备份，恢复机制

二、数据库

2.1概念

数据库是按照数据结构来组织，存储，管理数据的仓库。是一个可以长期存储在计算机内的，有组织的，有共享的，可以统一管理的数据集合。

2.2数据库的分类

网状结构数据库：以节点形式存储数据和访问数据。
层次结构数据库：IBM【IMS】，定向有序的树状结构实现存储和访问。
关系结构数据库：Oracle，MySQL,DB2，SQL Server，以表格(Table)形式存储，多表之间建立关联关系，通过分类，合并，连接，选取等方式实现访问。
非关系型数据库：MongDB，Redis，使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向的特定数据
EladtecSearch（四阶段学习）

三、数据库管理系统

3.1概念

数据库管理系统：指的是一种操作和管理数据库的大型软件，用于建立，使用，维护数据库，对数据库进行统一的管理和控制，保证数据库的安全性和完整性。
用户通过数据库管理系统访问数据库中的数据

3.2常见的数据库管理系统

Oracle：可以运行在UNIX,Window等主流操作系统，支持所有的工业标准，并获得了最高级别的ISO标准安全性认证。
DB2：IBM公司的，满足中大公司的需要。
SQL Server：微软推出的。
SQLLite：手机端的数据库。
Mysql：免费，适合中小型企业。

四，MySQL

4.1简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发的。属于Oracle旗下的产品。
MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，是最好的应用软件之一。

4.2访问与下载
官网：www.mysql.com
下载地址：https://dev.mysql.com/downloads/mysql/

4.3配置环境变量
Windows：
创建MYSQL_HOME:C:\Program Files\MySQL\MySQL Server 5.7
追加Path：%MySQL_HOME%\bin

4.4MySQL的目录结构

核心文件介绍

文件夹名称	内容
bin	命令相关文件
include	库文件
lib	头文件
Share	字符集，语言等信息

4.5MySQL配置文件

在MySQL安装目录中找到my.ini的文件，MySQL的一些配置参数

参数	描述
default-character-set	客户端的默认字符集
default-character-server	服务端默认字符集
port	客户端和服务端的端口号
default-storage-engine	MySQL的默认存储引擎INNODB

五、SQL

5.1概念

SQL：结构化查询语言，用于存取数据，更新，查询和管理关系数据库系统的程序设计语言。
经验：通常执行对数据库的“增，删，改，查”，简称C（Create），R（Read），U（Update），D（Delete）

5.2MySQL应用

对于数据库的操作，需要在连接MySQL的环境下进行指令输入，并在一行指令的末尾使用；结束。

5.3基本命令

查看MySQL中所有数据库
连接到MySQL
mysql> SHOW DATABASES;显示当前MySQL中所有的数据库
±-------------------+
| Database | 数据库（默认的）
±-------------------+
| information_schema | 信息数据库，保存的是关于数据的数据（库名，表名， 列的数据类型以及访问权限等）
| mysql | 核心数据库，存储数据库的用户
| performance_schema | 性能优化
| sakila |
| sys | 系统库
| world | 里边存储的跟语言相关的内容
±-------------------+
创建自定义数据库 CREATE DATABASE
mysql>CREATE DATABASE nz2002; 创建了名称为nz2003的数据库
创建成功后返回：Query OK, 1 row affected (0.00 sec)

mysql>CREATE DATABASE nz2002 CHARACTER SET gbk; 创建数据库并设置其默认字符集为GBK。
mysql>CREATE DATABASE nz2002 CHARACTER SET GBK COLLATE gbk_chinese_ci; 支持简体中文和繁体中文。
mysql>CREATE DATABASE IF NOT EXISTS nz2002 如果nz2002不存在则创建，反之不创建。

删除数据库 DROP DATABASE
mysql>DROP DATABASE nz2002; 删除数据库

查看数据库创建信息 SHOW CREATE DATABASE
mysql>SHOW CREATE DATABASE NZ2002; 查看创建数据库时的基本信息。
返回：
±---------±---------------------------------------------------------------+
| Database | Create Database |
±---------±---------------------------------------------------------------+
| nz2002 | CREATE DATABASE nz2002 /*!40100 DEFAULT CHARACTER SET gbk */ |
±---------±---------------------------------------------------------------+
表示默认字符集是gbk

修改数据库 ALTER DATABASE
mysql>ALTER DATABASE NZ2002 CHARACTOR SET UTF8; 修改数据库nz2002的字符集为utf8。

使用数据库 USE
mysql>USE NZ2002 当前环境下，操作nz2002数据库。

查看当前实用的数据库 SELECT DATABASE();
mysql>SELECT DATABASE(); 查看当前使用的数据库。

六、客户端工具

6.1Navicat
是一套快速，可靠并且价格便宜的数据库管理工具，专为简化数据库管理及降低系统管理成本而设。

6.2SQLyog
也拥有图形化界面，拥有广泛的预定义工具和查询，友好的视觉界面。类似Excel的查询结果编辑界面。

6.3DataGrip（Idea开发工具集成）
捷克公司的产品。需要付费。如果买了idea，DataGrip通用

七、执行SQL脚本
创建一个companyDB的数据库，然后在对象浏览器区，右键->执行SQL脚本->找到文件，打开->点击执行

八、数据查询【重点】

8.1数据表的基本结构
关系结构的数据库是以表格（Table）进行数据存储，表格由行和列组成
经验：执行查询语句返回的结果集是一张虚拟表

8.2基本查询
语法：SELECT列名 FROM表名

关键字	描述
SELECT	指定要查询的列
FROM	指定要查询的表

8.2.1查询所有列
查询t_employees表中所有员工的所有信息
SELECT * FROM t_employees;
SELECT 所有的列名 FROM t_employees;
经验：生产环境下，优先使用列名查询。*的方式虽然看起来便捷，但实际上需要转换成全列名，效率低，可读性差。

8.2.2查询部分列
查询表中的所有员工的编号，姓氏，邮箱
SELECT EMPLOYEE_ID,FIRST_NAME,EMAIL FROM t_employees;
查询表中所有员工的编号，部门编号
SELECT EMPLOYEE_ID,DEPARTMENT_ID FROM t_employees;

8.2.3对列中的数据进行运算
查询员工表中所有员工的编号，姓名，年薪
SELECT EMPLOYEE_ID,FIRST_NAME,last_NAME,SALARY * 12 FROM t_employees;

算术运算符	描述
+	列与列之间做加法运算
-	列与列之间做减法运算
*	列与列之间做乘法运算
/	列与列之间做除法运算

注意：%在数据库中，代表的是占位符，而并非取余运算符

8.2.4列的别名
列 AS ‘列名’
#查询员工表中所有员工的编号，姓名，年薪(列的运算/22),列名均为中文
SELECT EMPLOYEE_ID AS ‘编号’,FIRST_NAME AS ‘姓’,last_NAME AS ‘名’,SALARY / 22 AS ‘日薪’ FROM t_employees;
起别名，没有对原表的列名发生影响

8.2.5查询结果去重
distinct 列名
查询员工表中，所有经理的ID编号
SELECT DISTINCT MANAGER_ID AS ‘经理编号’ FROM t_employees;
查询员工表中，所有的工资（去掉重复的数据）
SELECT DISTINCT SALARY FROM t_employees;

8.3排序查询
语法：SELECT 列名 FROM 表名 ORDER BY 排序列名【排序规则】

排序规则	描述
ASC	做升序排序
DESC	做降序排序

8.3.1依据单列进行排序
查询员工的编号，名字，薪资，按照工资进行升序排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY ASC;
查询员工的编号，名字，薪资，按照姓名进行升序排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY FIRST_NAME ASC;
查询员工的编号，名字，薪资，按照工资进行降序排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC;
经验：当进行升序排序时，排序规则可以不显示声明。默认为升序排序规则。

8.3.2依据多列进行排序
查询员工编号，名字，薪资，按照工资进行升序排序，如果工资相等，按照编号降序排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
ORDER BY SALARY ASC,EMPLOYEE_ID DESC;
查询员工编号，名字，薪资，按照工资进行升序排序，如果工资相等，按照姓名降序排序
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
ORDER BY SALARY ASC,FIRST_NAME DESC;

8.4条件查询
语法：SELECT 列名 FROM 表名 WHERE 条件

关键字	描述
WHERE	在查询结果中，筛选符合条件的查询结果。条件为布尔表达式

8.4.1等值判断（=）
查询工资为2500的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE SALARY = 2500;
查询姓为Steven的
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE FIRST_NAME = ‘Steven’;
注意：与java不同（==），MySQL中等值判断用=

8.4.2不等值判断（>,<,>=,<=,!=,<>）
查询员工工资大于6000的员工的信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY >= 6000;
查询员工工资不等于2500的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY <> 2500;#(!=同理)

8.4.3逻辑判断（and，or。not）
查询员工工资在6000~10000的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY >= 6000 AND SALARY <= 10000;
查询员工工资是10000或者是9000的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY = 10000 OR SALARY = 9000;
查询除了工资是10000的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE NOT SALARY = 10000;

8.4.4区间判断（between and）
#区间判断 包含区间边界的两个值
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY BETWEEN 6000 AND 10000;
注意:between and要遵循 between 小值 and 大值

8.4.5NULL值判断（IS NULL,IS NOT NULL）
IS NULL
列名 IS NULL;
IS NOT NULL
列名 IS NOT NULL;
查询出 没有经理编号的员工 IS NULL
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NULL;
查询出 没有经理编号以外的员工
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NOT NULL;
查询出 没有经理编号以外的员工（此处NOT为取反，两个结果）
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE NOT MANAGER_ID IS NULL;

8.4.6枚举查询
枚举查询 IN(值1，值2，值n。。。)
查询部门编号为70，80，90的员工
SELECT EMPLOYEE_ID,FIRST_NAME,last_NAME,DEPARTMENT_ID FROM t_employees
WHERE DEPARTMENT_ID IN(70,80,90);
#枚举查询 经理编号为 124 和 100的员工的信息
SELECT EMPLOYEE_ID,FIRST_NAME,last_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IN(124,100);

8.4.7模糊查询
LIKE
LIKE_(单个任意字符)
列名 LIKE 'S_;
LIKE (任意长度的任意字符 0~n个)
列名 LIKE ‘S%’;
#模糊查询，查询姓氏以S开头且长度为6的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE FIRST_NAME LIKE ‘S_____’;
#模糊查询，查询姓氏以S开头任意长度的所有员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE FIRST_NAME LIKE ‘S%’;

8.4.8分支结构查询
查询员工信息（编号，名字，薪资，薪资级别<条件表达式>）
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件3 THEN 结果3
WHEN 条件4 THEN 结果4
ELSE 结果
END
注意：通过CASE END进行条件判断，每条数据对应生成一个值
经验：类似java中的分支结构
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,
CASE
WHEN SALARY >= 10000 THEN ‘A’
WHEN SALARY >= 8000 AND SALARY < 10000 THEN ‘B’
WHEN SALARY >= 6000 AND SALARY < 8000 THEN ‘C’
WHEN SALARY >= 4000 AND SALARY < 6000 THEN ‘D’
ELSE ‘E’
END AS ‘薪资级别’
FROM t_employees;
注意：case分支结构产生一个新的列

8.5时间查询
语法：SELECT 时间函数 参数列表
经验：执行时间函数查询，会生成一张虚拟表（一行一列）

时间函数	描述
SYSDATE()	当前系统时间（年，月，日，时，分，秒）
DURDATE()	获得当前时间
WEEK(DATE)	获得指定日期是一年中第几周
YEAR(DATE)	获得指定日期的年份
MONTH(DATE)	获得指定日期的月份
DAY(DATE)	获得指定日期的天
HOUR(DATE)	获得指定日期的小时值
MINUTE(DATE)	获得指定日期的分钟值
SECOND(DATE)	获得指定日期的秒值
DATEDIFF(DATE1,DATE2)	获得DATE1和DATE2之间相隔的天数
ADDDATE(DATE,N)	在指定日期加上N天后的日期

8.5.1获取时间
#时间查询。。。
1.当前系统时间
SELECT SYSDATE();
2.获得当前日期
SELECT CURDATE();
3.获得当前时间
SELECT CURTIME();
4.获得当前日期在一年中为第几周
SELECT WEEK(CURDATE());
5.获取指定日期中的年份
SELECT YEAR(CURDATE());
6.获取指定日期中的月份
SELECT MONTH(CURDATE());
7.获取指定日期中的日
SELECT DAY(CURDATE());
8.获取指定日期中的时
SELECT HOUR(CURDATE());
9.获取指定日期中的分
SELECT MINUTE(CURDATE());
10.获取指定日期中的秒
SELECT SECOND(CURDATE());
11.获取Datel和Date2之间相隔的天数
SELECT DATEDIFF(SYSDATE(),‘2019-3-26’);
12.在指定日期之上加n天后的日期
SELECT ADDDATE(SYSDATE(),6);

8.6字符串查询
语法：SELECT 字符串函数(参数列表);
经验：执行字符串函数，也是产生一张虚拟表（一行一列）

字符串函数	说明
CONCAT(str1，str2，str3…)	将多个字符串进行拼接
INSERT(str，pos，len，newStr)	将str中指定pos位置开始len长度的内容替换为newStr
LOWER(str)	将指定字符串转换为小写
UPPER(str)	将指定字符串转换为大写
SUBSTRING(str，pos，len)	将str字符串pos位置开始截取len个内容

#1.连接将多个字符串连接在一起
SELECT CONCAT(‘My’,‘S’,‘QL’);
#2.插入替换（下标从1开始）
SELECT INSERT(‘这是MySQL数据库’,3,5,‘Oracle’);
#3.转小写
SELECT LOWER(‘MYSQL’);
#4.转大写
SELECT UPPER(‘mysql’);
#5.截取
SELECT SUBSTRING(‘欢迎你，你好世界，我在这里’,3,5);

8.7聚合函数

语法：SELECT [聚合函数(列名)FORM 表名；
经验：聚合函数是对多条数据的单列进行统计，返回统计后的一行结果

聚合函数	说明
COUNT()	求总行数
SUM()	求单列中所有行的总和
AVG()	求单列中所有行的平均值
MAX()	求单列中所有行的最大值
MIN()

8.7.1求总行数
1.查询员工一共多少人
SELECT COUNT(EMPLOYEE_ID) AS ‘员工总数’ FROM t_employees;
SELECT COUNT(MANAGER_ID) AS ‘经理总数’ FROM t_employees;
SELECT COUNT(*)FROM t_employees;
注意：聚合函数中，自动忽略null值。不进行统计

8.7.2单列总和
2.查询员工每个月工资的总和
SELECT SUM(SALARY) FROM t_employees;

8.7.3单列平均值
3.查询员工每个月工资的平均工资
SELECT AVG(SALARY) FROM t_employees;

8.7.4单列最大值
4.查询月薪最高的
SELECT MAX(SALARY) FROM t_employees;

8.7.5单列最小值
5.查询月薪最低的
SELECT MIN(SALARY) FROM t_employees;

8.8分组查询
语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（列名）

关键字	说明
GROUP BY	分组依据。如果有WHERE，在WHERE之后生效

8.8.1查询各部门的总人数
#思路：
#1.先按照部门编号分组（分组依据是：department_id）
#2.在针对各部门的人数进行统计（count）
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID;
8.8.2查询各部门的平均工资
#思路：
#1.先按照部门编号分组（分组依据是，department_id）
#2.再针对各部门的工资进行平均计算（AVG()）
SELECT DEPARTMENT_ID,AVG(SALARY) AS ‘平均工资’,COUNT(EMPLOYEE_ID) AS ‘人数’
FROM t_employees
GROUP BY DEPARTMENT_ID;
8.8.3查询各个部门，各个岗位的人数
#思路：
#1.按照部门编号进行分组（departmen_id）
#2.按照岗位名称进行分组（job_id）
#3.针对每个部门中各个岗位的人数进行统计
SELECT DEPARTMENT_ID AS ‘部门’,JOB_ID AS ‘岗位’,COUNT(EMPLOYEE_ID) AS ‘人数’
FROM t_employees
GROUP BY DEPARTMENT_ID,JOB_ID;

8.8.4常见问题
#查询各个部门的id，总人数，first_name
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID),FIRST_NAME
FROM t_employees
GROUP BY DEPARTMENT_ID;
注意：分组查询中，select显示的列只能是分组依据的列或者是聚合函数列，不能出现其他列。

8.9分组过滤查询
语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（列名） HAVING 过滤规则

关键字	说明
HAVING	过滤规则是对分组后的数据进行过滤

8.9.1统计部门中的最高工资
#统计部门编号为60，70，80的部门最高工资
#思路：
#1.确定分组依据 department_id
#2.对分组后的数据进行过滤 过滤规则为60，70，80部门编号
#3.分组过滤后的数据，做MAX()函数处理
SELECT DEPARTMENT_ID,MAX(SALARY)
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN (60,70,80);
#HAVING是在分组之后的数据进行过滤

8.10限定查询
语法：SELECT 列名 FROM 表名 LIMT 起始行，查询行

关键字	描述
LIMIT offset_start,row_counnt	限定查询结果的起始行和总行数

8.10.1查询前五行记录
#查询表中前五名员工的信息
SELECT * FROM t_employeesLIMIT 0,5;
注意：起始行是从0开始，代表了第一行。第二个参数代表的是从指定行开始查询几行。
8.10.2查询范围记录
#查询表中的第二页数据和第三页数据
#查询表中前五名员工的信息(第二页)
SELECT * FROM t_employees LIMIT 5,5;
#查询表中前五名员工的信息（第三页）
SELECT * FROM t_employees LIMIT 10,5;
经验：在分页的应用场景中，起始行是跟随页数变化的，但是一页显示的条数是不变的。

8.11查询总结
8.11.1SQL语句编写顺序
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY排序列 LIMIT 起始行，总条数
8.11.2SQL语句执行顺序
#1.执行 FROM ：指定数据来源表
#2.WHERE : 对查询的数据做第一次过滤
#3.执行GROUP BY ，分组
#4.执行HAVING : 队分组后的数据做第二次过滤
#5.执行SELECT ：查询各个字段的值
#6.执行ORDER BY : 排序
#7.执行LIMIT : 限定查询结果

8.12子查询（作为条件判断）
语法：SELECT 列名 FROM 表名 WHERE 条件（子查询结果）
8.12.1查询工资大于Bruce的员工信息
#思路：
#1.想查询到Bruce的工资
SELECT SALARY FROM t_employees WHERE FIRST_NAME = ‘Bruce’;
#2.查询大于Bruce工资的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
#3.将1，2整合为一条语句
SELECT * FROM t_employees WHERE SALARY > (SELECT SALARY FROM t_employees WHERE FIRST_NAME = ‘Bruce’);
注意：将子查询“一行一列”的结果作为外部查询的条件。做第二次查询
子查询得到的是一行一列的结果才能作为外部条件的等值或不等值判断条件。

8.13子查询（作为枚举查询的条件）
语法：SELECT 列名 FROM 表名 WHERE 列名 IN (子查询结果)
8.13.1查询与King同一部门的员工信息
#思路：
#1.查询King所在的部门编号（多行单列）
SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = ‘King’;
#2.将80，90作为枚举查询的条件
select EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE DEPARTMENT_ID IN (80,90);
#3.整合
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE DEPARTMENT_ID
IN (SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = ‘King’);
经验：将子查询得到的“多行一列”的结果作为外部查询的枚举查询条件，做第二次查询。
8.13.2查询工资高于60部门的所有人的信息
#1.查询部门编号为60的工资
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60;
#2.查询工资高于60部门的所有人的信息(高于所有人！)
select EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
where SALARY > ALL
(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
#3.查询工资高于60部门的所有人的信息(高于部分人！)
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
WHERE SALARY > any
(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
注意：当子查询结果集为多行单列时，也可以使用ALL匹配所有或者ANY匹配部分。
8.14子查询（作为一张表）
语法：SELECT 列名 FROM （子查询结果集）WHERE 条件；
8.14.1查询员工表中部分列的信息，获得工资大于15000的
#思路：
#1.先查询部分列的信息作为一张临时表
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC;
#2.将子查询得到的临时表作为外部查询的表
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM(SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC)AS temp
WHERE SALARY > 15000;
经验：将子查询得到的“多行多列”的结果作为外部查询的一张临时表，做第二次查询。
8.15合并查询（了解即可）
语法：
SELECT 列名 FROM 表名 UNION SELECT 列名 FROM 表名2
SELECT 列名 FROM 表名 UNION ALL SELECT 列名 FROM 表名2
8.15.1合并两张表的结果（去除重复记录）
#合并t1和t2两张表的结果。纵向合并，去除重复的记录
SELECT * FROM t1
UNIT
SELECT * FROM t2
8.15.2合并两张表的结果（保留重复记录）
#合并t1和t2两张表的结果。不去除重复记录
SELECT * FROM t1
UNIT ALL
SELECT * FROM t2
注意：合并的两个结果集，列表必须相同，列类型，列名可以不同。

8.16表连接查询
语法：SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件;
8.16.1内连接查询（关键字时INNER JOIN ON）

#查询所有有部门的员工信息，显示本门名称（不包括没有部门的员工）SQL标准
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME FROM t_employees
INNER JOIN t_departments
ON t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

#MYSQL标准
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME FROM
t_employees,t_departments WHERE t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

#1.两张表连接查询，要有关联条件。但是关联条件的列重复了。需要明确查询的是哪个表的列
#2.表名比较长，表名多次重复出现。容易混淆，可以给别名
SELECT EMPLOYEE_ID,FIRST_NAME,d.DEPARTMENT_ID FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
经验：在MySQL中，可以使用第二种方式，不符合SQL标准
第一种，属于SQL标准，与其他关系型数据库通用

8.16.2内连接查询
#查询所有岗位的员工信息，显示岗位名称
SELECT EMPLOYEE_ID,FIRST_NAME,JOB_TITLE FROM t_employees AS a
INNER JOIN t_jobs AS b
ON a.JOB_ID = b.JOB_ID;

8.16.3三表连接查询

#查询所有员工工号，名字，部门名称，部门所在城市的名称
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
INNER JOIN t_locations AS l
ON d.LOCATION_ID = l.LOCATION_ID;

8.16.4多表连接查询
#查询所有员工工号，名字，部门名称，部门所在城市的名称，所在城市的国家
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY,COUNTRY_NAME
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
INNER JOIN t_locations AS l
ON d.LOCATION_ID = l.LOCATION_ID
INNER JOIN t_countries AS c
ON l.COUNTRY_ID = c.COUNTRY_ID;
经验：多表查询时，要明确哪一张表和连接的表有关系。

8.16.5左外连接查询（LEFT JOIN ON）
#查询所有员工信息，以及对应的部门名称（没有部门的员工，也在查询结果中，但是部门名称以NULL填充）
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME
FROM t_employees AS e
LEFT JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
注意：左外连接，是以左表为主表，依次向右表匹配，匹配到，则返回正确结果
匹配不到，则返回NULL值，填充显示。

8.16.6右外连接查询（RIGHT JOIN ON）
#查询所有员工信息，以及部门中的员工信息（
#没有员工的部门，也在查询结果中，员工信息以NULL填充）
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME
FROM t_employees AS e
RIGHT JOIN t_departments AS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回正确结果
匹配不到，则返回NULL填充

九、DML操作（增、删、改）
9.1新增（INSERT）
语法：INSERT INTO 表名（列1，列2，列3）VALUE(值1，值2，值3)
#添加一条员工信息
INSERT INTO t_employees
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES(‘209’,‘Ya’,‘Suo’,‘YaSuo@happy.com’,‘515.123.6666’,‘2010-3-18’,‘Center’,900,NULL,‘123’,‘50’)
#多行添加，在值列表外边追加，再写一个值列表
,(‘210’,‘Ya’,‘Suo’,‘YaSuo@happy.com’,‘515.123.6666’,‘2010-3-18’,‘Center’,900,NULL,‘123’,‘50’);
#添加一条城市信息
INSERT INTO t_countries(COUNTRY_ID,COUNTRY_NAME)VALUES(‘AL’,‘阿尔巴尼亚’);
#添加一条部门信息
INSERT INTO t_departments(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
VALUES(‘280’,‘Teach’,‘111’,‘1500’);
注意：表名后的列名列表以及VALUES里的值列表要一一对应（个数，顺序，类型）

9.2修改（UPDATE）
UPDATE 表名 SET 列名1 = 新值1，列名2=新值2…WHERE 条件
#修改员工编号为208的员工名字为Tom Jackson
SELECT * FROM t_employees;
UPDATE t_employees
SET FIRST_NAME = ‘Tom’,LAST_NAME = ‘Jackson’
WHERE EMPLOYEE_ID = ‘208’;
注意：SET后跟着多个列=值，绝大多数情况下，都要加WHERE条件，指定修改的目标，否则为整表更新。

9.3删除
DELETE FROM表名 WHERE 条件
9.3.1删除一条信息
#删除一条员工，编号哦为’207’
DELETE FROM t_employees
WHERE EMPLOYEE_ID = ‘207’;
注意：删除时，如果不加WHERE条件，删除的是整张表的数据。结构不变
9.4清空（TRUNCATE）
TRUNCATE TABLE 表名；
9.4.1清空整张表
#清空t2整张表
TRUNCATE TABLE t2;
注意：TRUNCATE与DELETE不加WHERE删除整张表数据不同：
DELETE仅仅删除数据，结构不变。
TRUNCATE是把整张表销毁，再按照原表的格式，结构创建一张新表。

十、库表操作
10.1数据库创建（CREATE）
CREATE DATABASE 库名
10.1.1创建数据库
#创建默认字符集的数据库
CREATE DATABASE MYDB1;
#创建指定字符集的数据库
CREATE DATAVASE MYDB1 CHARACTER SET UTF8;

10.2修改数据库
ALTER DATABASE 库名 操作

10.2.1修改数据库的字符集
#修改mydb1的字符集为gbk
ALTER DATABASE MYDB1 CHARACTER SET GBK;

10.3删除数据库
DROP DATABASE 库名
#删除mydb1数据库
DROP DATABASE MYDB1;

10.4数据类型
MySQL大致可以分为三类；数值，日期/时间，字符串（字符）类型。对于我们建表，约束列的类型有很大的帮助。
10.4.1数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4字节	（-2147483648，2147483647）	（0，4294967295）	整数值
DOUBLE	8字节	（-1.797E+308，-2.22E-308）	（0，2.22E-308,1.797E+308）	双精度浮点值
DOUBLE(M,D)	8字节，M表示长度，D表示小数位数	同上，受M和D的约束。DOUBLE(5,2) -999.99-999.99	同上，受M和D的约束	双精度浮点值
DECIMAL(M,D)	保存精确值	依赖M和D	依赖M和D	小数值

10.4.2日期类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值
YEAT	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期时间值

10.4.3字符串类型

类型	大小	用途
CHAR	0-255字符	定长字符串CHAR(10)10个字符
VARCHAR	0-65535	可变长字符串VARCHAR(10)10个字符
BLOB(binary large object)	0-65535	二进制形式的长文本数据
TEXT	0-65535	长文本

10.5数据表的创建（CREATE）
CREATE TABLE 表名（
列名 数据类型[约束]，
列名 数据类型[约束]，
…
列名 数据类型[约束] //最后一列的创建，末尾不需要加逗号
）[charset=utf8]；//根据需要指定表的字符编码集

10.5.1创建表
#创建科目表
#科目编号，科目名称，科目学时
#Subject
CREATE TABLE Subject(
subjectID INT,
subjectName VARCHAR(20),
subjectHours INT
)CHARSET = utf8;
INSERT INTO subject(subjectID,subjectName,subjectHours)
VALUES (1,‘Java’,‘10’);
INSERT INTO subject(subjectID,subjectName,subjectHours)
VALUES (2,‘Html5’,‘20’);
INSERT INTO subject(subjectID,subjectName,subjectHours)
VALUES (3,‘Bigdata’,‘5’);
问题：在已创建的表中新增数据时，可不可以新增两个相同列值的数据？可以
有什么弊端？表中的数据不唯一，导致返回的结果也不唯一

十一、约束
11.1实体完整性约束
表中一行数据代表一个实体（entity），实体完整性约束是标识每一行数据不重复，实体唯一。
11.1.1主键约束
PRIMARY KEY唯一，标识其中一行数据，此列的值不可重估，且不能为NULL
#创建表中，选择合适做主键的列，添加主键约束
CREATE TABLE Student(
stuid INT PRIMARY KEY,#标识每一个学生的编号是唯一的，不能为NULL
stuName VARCHAR(20),
phone VARCHAR(11)
)CHARSET utf8;

11.1.2唯一约束
UNIQUE 唯一，标识表中的一行数据，不可重复，但是可以为NULL
CREATE TABLE Student(
stuid INT PRIMARY KEY,#标识每一个学生的编号是唯一的，不能为NULL
stuName VARCHAR(20),
phone VARCHAR(11) UNIQUE
)CHARSET utf8;

11.1.3自动增长列
AUTO_INCREMENT 自动增长，给主键数值列添加自动增长。默认从1开始，每次加1。不能单独使用，和主键搭配。
#为表中的主键列添加自动增长，避免ID重复，也容易忘记
CREATE TABLE Student(
stuid INT PRIMARY KEY AUTO_INCREMENT,#标识每一个学生的编号是唯一的，不能为NULL/会从1开始，根据添加数据的顺序依次+1
stuName VARCHAR(20),
phone VARCHAR(11) UNIQUE
)CHARSET utf8;

11.2域完整性约束
限制列的每一个单元格的数据正确性
11.2.1非空约束
NOT NULL非空，约束此列的每一个单元格不允许有NULL
#加了NOT NULL的约束列，必须有值
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(20) NOT NULL,#约束名字这一列必须有值
address VARCHAR(50) NOT NULL
)CHARSET utf8;
INSERT INTO emp(empName,address) VALUES(UNLL,‘北京市海淀区’);#error，课程名称必须有值

11.2.2默认值约束
DEFAULT 为列赋予默认值，当新增的数据不指定值时，可以书写DEFAULT，以定义好的默认值进行填充
默认值约束，如果没有指定值，填充DEFAULT，默认值。
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
empName VARCHAR(20) NOT NULL,#约束名字这一列必须有值
address VARCHAR(50) NOT NULL,
sex CHAR(1) DEFAULT ‘女’
)CHARSET utf8;

11.2.3引用完整性约束
语法：CONSTRAINT 引用名 FOREIGN KEY(列名) REFERENCES 被引用表名（列名）;
详解：FOREIGN KEY 引用外部分表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值
#专业表
CREATE TABLE Speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
SpecialName VARCHAR(20) UNIQUE NOT NULL#唯一，且不能为空
)CHARSET utf8;
#课程表
CREATE TABLE Subject(
subjectid INT PRIMARY KEY AUTO_INCREMENT,
subjectname VARCHAR(20) UNIQUE NOT NULL,
subjecthours INT DEFAULT 20,
speciaid INT NOT NULL,
CONSTRAINT fk_Subject_specialid
FOREIGN KEY(specialid)
REFERENCES Speciality(id)
)CHARSET utf8
#存在引用关系的表。要先添加被引用的表数据（主键表），再添加引用表的数据（外键表）
SELECT * FROM Speciality(SpecialName) VALUES(‘Java’);
SELECT * FROM Speciality(SpecialName) VALUES(‘Html5’);

INSERT INTO Subject(subjectname,subjecthours,speciaid)
VALUES(‘JavaSE’,10,1);
INSERT INTO Subject(subjectname,subjecthours,speciaid)
VALUES(‘JavaScript’,20,2);
INSERT INTO Subject(subjectname,subjecthours,speciaid)
VALUES(‘BigData’,20,3);#error约束：主键表不存在3，所以外键表不能插入3
注意：两张表存在引用关系时，执行删除操作需要注意，先删除从表（引用表、外键表），再删除主表（被引用表、主键表）

11.3约束创建集合
创建带有约束的表
11.3.1创建Grade表

列名	数据类型	约束	说明
Gradeid	INT	主键，自动增长	班级编号
GradeName	VARCHAR(20)	唯一，非空	班级名称

#创建Grade表
CREATE TABLE Grade(
GradeId INT PRIMARY KEY AUTO_INCREMENT,
GradeName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
SELECT * FROM Grade;
INSERT INTO Grade(GradeName)VALUES(‘NZ2001’);
INSERT INTO Grade(GradeName)VALUES(‘NZ2002’);
INSERT INTO Grade(GradeName)VALUES(‘NZ2003’);

11.3.2创建Student表

列名	数据类型	约束	说明
sutdent_id	VARCHAR(50)	主键	学号
student_name	VARCHAR(50)	非空	姓名
sex	CHAR(2)	默认值，男	性别
borndate	DATE	非空	生日
phone	VARCHAR(11)	无	电话
Gradeid	INT	非空，外键约束：引用班级表的Gradeid	班级编号

#创建Student表
CREATE TABLE Student(
student_id VARCHAR(50) PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
sex CHAR(2) DEFAULT ‘男’,
borndate DATE NOT NULL,
phone VARCHAR(11),
GradeId INT NOT NULL,
CONSTRAINT kf_Student_gradeId
FOREIGN KEY (GradeId)
REFERENCES Grade(GradeId)
)CHARSET=utf8;
SELECT * FROM Student;
INSERT INTO Student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES(‘S1001’,‘张三’,DEFAULT,‘2001-06-01’,NULL,2);
INSERT INTO Student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES(‘S1002’,‘李四’,DEFAULT,‘1999-06-01’,NULL,3);
注意：在创建有关系关联表时，要先创建主表（主键），再创建从表（外键表）

11.4数据表的修改（ALTER）
语法：ALTER TABLE 表名 修改操作；

11.4.1向现有表中添加列
ALTER TABLE Student ADD image BLOB;
#ADD 新列名 数据类型 [约束]

11.4.2修改表中的列
ALTER TABLE Student MODIFY phone VARCHAR(14) NOT NULL;
注意：修改表中的某列时，需要写全列的名字、数据类型、约束

11.4.3删除表中的列
ALTER TABLE Student DROP image;

11.4.4改变列名
ALTER TABLE Student CHANGE borndate birthday DATE NOT NULL;
注意：改变列名时，在给定新列名的同时，要指定列的数据类型和约束。

11.4.5修改表名
ALTER TABLE Student RENAME stu;

11.5删除表（DROP）
DROP TABLE 表名

11.5.1删除学生表
DROP TABLE stu;

十二、事务
12.1模拟转账
生活中转账是转账方扣钱，收钱方账户加钱。用数据库操作来模拟现实转账。
12.1.1模拟账户转钱
#1账号转钱给2账户1000元
#1账户扣钱
UPDATE accout SET money = money - 1000 WHERE id = 1;
#2账户加钱
UPDATE account SET money = money + 1000 WHERE id = 2;
12.1.2模拟转账错误
#1账号转钱给2账户1000元
#1账户扣钱
UPDATE accout SET money = money - 1000 WHERE id = 1;
#断电、异常、出错
#2账户加钱
UPDATE account SET money = money + 1000 WHERE id = 2;
上述代码在扣钱操作过程中出现了异常或语句出错，会发现，扣钱仍旧成功，而加钱失败了。
每条SQL语句都是独立的操作！任何一个操作执行完对数据库是永久性的影响。
12.2事务的概念
事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务中，所有的SQL语句都成功执行时，整个事务成功！有一个SQL语句执行失败，整个事务都执行失败！

12.3事务的边界
开始：连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即事务的开始。
结束：
1.提交
显示提交：COMMIT;
隐式提交：一条DML语句。正常退出（客户端退出链接）
2.回滚：
显示回滚：ROLLBACK;
隐式回滚：非正常退出（断电，死机），执行了创建，删除的语句，但是失败了！会为这个无效的SQL语句执行回滚。
12.4事务的原理
数据库会为每一个客户端都维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有的SQL语句均正常结束（COMMIT），才会将回滚段中的数据同步到数据库。否则无论因为任何原因失败了，则整个事务回滚（ROLLBACK）。
12.5事务的特性
Atomicity（原子性）
表示的是一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。
Consistency（一致性）
表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态。
Isolation（隔离性）
事务查看数据操作时数据所处的状态，要么是另一个并发事务修改数据之前的状态，要么是另一个并发事务修改它之后的状态。事务不会查看中间状态的数据。
Durability（持久性）
事务完成之后，对于数据库的影响时永久性的。
12.6事务的应用
应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），可通过程序逻辑手动控制事务的提交或回滚
12.6.1事务完成转账
#开启事务
START TRANSACTION #SET autoCommit = 0;#方式2 设置自动提交为0 关闭自动提交 | 开启自动提交
#1账户扣钱
UPDATE accout SET money = money - 1000 WHERE id = 1;
#2账户加钱
UPDATE account SET money = money + 1000 WHERE id = 2;
#执行提交—成功
COMMIT;
#执行回滚—失败
ROLLBACK;
注意：开启事务后，在当前事务内执行的语句均属于当前事务，成功再执行COMMIT，失败要进行ROLLBACK。

十三、权限管理
13.1创建用户
CREATE USER 用户名 IDENTIFIED BY 密码
13.1.1创建一个用户
CREATE USER zhangsan IDENTIFIED BY 123;
13.2授权
GRANT ALL ON 数据库.表名 TO 用户名;
13.2.1用户授权
#将companydb数据里的grade表授权给zhangsan
GRANT ALL ON companydb.t1TO zhangsan;

13.3撤销授权
REVOKE ALL ON 数据库.表名 FROM 用户名
13.3.1撤销用户权限
REVOKE ALL ON companydb.t1 FROM zhangsan;
13.4删除用户
DROP USER 用户名;
DROP USER zhangsan;

十四、视图
14.1概念
视图，虚拟表，从一个表中或多个表中查询出来的结果表，作用和真实的表是一样的，包含一系列的带有行和列的数据。视图中，可以使用SELECT语句查询数据，也可以使用INSERT、UPDATE修改记录，视图可以使用户操作方便，并保障了数据库系统安全。
14.2视图特点
优点：
简单化，数据所见即所得
安全性，只能查询或修改视图中所能见到的数据
逻辑独立性，可以屏蔽真实表结构变化带来的影响。
缺点：
性能相对较差，简单的查询会稍微复杂
修改不方便，当视图的数据是复杂的集合视图时，无法修改。
14.3视图的创建
语法：CREATE VIEW 视图名 AS 查询数据源表的语句；
14.3.1创建视图
#创建一个t_empinfo视图，该视图的数据是员工姓名，邮箱，手机号码
CREATE VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;

14.3.2使用视图
#使用视图
SELECT * FROM t_empinfo WHERE FIRST_NAME=‘Steven’ AND LAST_NAME=‘King’;
#修改，只能修改看得到的
UPDATE t_empinfo SET email = ‘Kings’ WHERE FIRST_NAME=‘Steven’ AND LAST_NAME=‘King’;

14.4视图的修改
方式一：CREATE OR REPLACE VIEW 视图名 AS 查询源表的语句；
方式二：ALTER VIEW 视图名 AS 查询源表的语句；

14.4.1修改视图
#方式1
#修改视图 存在就替换数据，不存在就新建
CREATE OR REPLACE VIEW t_empinfo
AS
SELECT SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
#方式2
ALTER VIEW t_empinfo
AS
SELECT SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;

14.5视图的删除
DROP VIEW 视图名
14.5.1删除视图
DROP VIEW t_empinfo;
注意：删除视图不会影响原表的数据

14.6视图的注意事项
注意：
视图不会独立存储数据，原表发生改变，视图的数据也发生改变。没有优化查询的性能。
如果视图包含以下结构中的一种，则视图不可更新：
聚合函数的结果
GROUP BY分组后的结果
HAVING筛选过滤后的结果
UNION,UNION ALL联合后的结果

十五、SQL语言分类
1.数据查询语言DQL（Data Query Launguage）：SELECT,WHERE,ORDER BY,GROUP BY,HAVING
2.数据定义语言DDL（Data Definition Language）：CREATE,ALTER,DROP
3.数据操作语言DML（Data Manipulation Launguage）：INSERT,UPDATE,DELETE
4.事务处理语言TPL（Transaction Process Language）：COMMIT,ROLLBACK
5.数据控制语言DCL（Data Control Language）：GRANT,REVOKE