MySQL数据库

关系型数据库基础

MySQL：是一个小型关系型数据库

Oracle、sqlServer、DB2

一、概念

关系模型：把世界看作是由实体和关系组成。实体是指现实世界中客观存在的事物，事物的特性称为属性。关系就是指实体间的联系。

关系模型数据库：是一种以表做为实体，以主键和外键关系做为联系的一种数据库结构

相类似的实体被存入表中

主键：在关系型数据库表中，用一个唯一的标识符来标识每一行，这个标识符就是主键(Primary Key)；

​ 两个特点：不能重复；不能为空

外键：在关系型数据库中，外键(Foreign Key)是用来表达表和表之间关联关系的列

1、三种关系：

1-1、一对一

一条主表记录对应一条从表记录，同时一条从表记录对应一条主表记录

1-2、一对多

一条主表记录对应多条从表记录，同时一条从表记录对应一条主表记录。“多边”的表称为从表，“一边”的表称为主表，外键加在从表中

1-3、多对多

一条主表记录对应多条从表记录，同时一条从表记录对应多条主表记录。在关系型数据库中，利用第三方的中间表描述主表和从表的多对多关系

创建数据库、创建表、及删除数据库、删除表

二、数据库管理系统(DBMS)

数据库本身只是一个用来放数据的容器，放在容器中的数据如何摆放不归容器管理；需要使用数据库管理系统(DBMS)来对数据库中的数据进行管理

1、分类

1-1、本地数据库管理系统

又称桌面型数据库管理系统。在这种模式下，RDBMS与数据库应用程序运行在同一客户端的进程中

1-2、数据库服务器管理系统

RDBMS和数据库应用程序运行在不同的进程中

2、数据库引擎

是数据库镀层软件组织，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据

//查询数据库引擎
show engines

2-1、InnoDB存储引擎

InnoDB是MySQL数据库默认的引擎，事务型数据库的首选引擎，提供提交、回滚、崩溃恢复、并发控制的能力。

2-2、MyISAM存储引擎

MyISAM拥有较高的插入、查询速度，但不支持事务

2-3、MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。主要用于数据量不大的临时数据

三、结构化查询语言SQL

SQL是结构化查询语言的英文缩写，是一种用于管理关系型数据库，并与数据库中的数据进行通讯的计算机语言

SQL方言：SQL提供所有基本的数据操作，但是不同的RDBMS之间存在一些差别，每个RDBMS实现标准的方法可能有细微的区别

SQL的非过程性质：

分类：

1⃣️数据定义语言(DDL)

创建、修改、删除数据库的内部数据结构

create database 数据库名称; —— 创建数据库
drop database 数据库名称; —— 删除数据库

-- 创建表的语法
CREATE TABLE<表名>
 (<列名><列的数据类型>[列的约束]);
 -- 创建用户表
CREATE TABLE user_table(
userName varchar(255),
userPassword varchar(255),
userMoney DECIMAL(11,2)
);
-- 删除表
drop table 表名;
 
 -- 添加新列
 -- alter table 表名 add 新列名 新列的数据类型 新列的约束
 alter table user_table add user_info varchar(255);
 
 -- 修改列
 -- alter table 表名 change 旧列名 新列名 新列的数据类型 新列的约束
 alter table user_table change user_info user_gender varchar(100);
 
 -- 删除列
 -- alter table 表名 drop COLUMN 列名;
 alter table user_table drop COLUMN user_gender;

*数据类型：

数据类型是数据的一种分类，是按照数据结构来分类的

1、整数类型

类型	大小	范围(有符号)	范围(无符号)	用途
TINYINT	1字节	（-128，127）	（0，255）	小整数值
SMALLINT	2字节	（-32 768，32767）	（0，65 535）	大整数值
MEDIUMINT	3字节			大整数值
INT	4字节			大整数值
BIGINT	8字节			极大整数

2、浮点类型

类型	大小	精度	用途
FLOAT	4字节	7位小数	单精度浮点数
DOUBLE	8字节	15位小数	双精度浮点数
DECIMAL(M,D)	17字节	30位小数	小数值

3、字符串类型

类型	范围	用途
CHAR(n)	0-255	定长字符串
VARCHAR(n)	0-65 535	变长字符串
TINYTEXT	0-255	短文本字符串
TEXT	0-65 535	长文本数据
MEDIUMTEXT	0-16 777 215	中等长度文本数据
LONGTEXT	0-4 294 967 295	极大文本数据

4、日期类型

类型	大小	格式	用途
DATE	3字节	YYYY-MM-DD	日期值
TIME	3字节	HH:MM:SS	时间值或持续时间
YEAR	1字节	YYYY	年份值
DATETIME	8字节	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4字节	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

5、二进制类型

一般情况下不会将二进制文件的数据放在数据库中，而是会有一个专门存放文件的文件服务器，在数据库中只需要保存文件在文件服务器中的路径

类型	大小	用途
TINYBLOB	0-255	不超过255个字符的二进制字符串
BLOB	0-65535	二进制形式的长文本数据
MEDIUMBLOB	0-16 777 215	二进制形式的中等长度文本数据
LONGBLOB	0-4 294 967 295	二进制形式的极大文本数据

6、枚举类型(ENUM)

在MySQL中，ENUM是一个字符串对象，其值是从列创建时定义的允许值列表中选择的；ENUM枚举类型使用语法：

CREATE TABLE t_employee(
	employeeName VARCHAR(50),
	edu ENUM('高中','大专','本科')
);

2⃣️*数据查询语言(DQL) *

用于数据库中数据的查询

SQL语句特点、执行顺序

重要：
查询语句的书写顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limi

后执行的子句，可以使用前执行子句中的计算结果、定义的别名等
SQL的执行顺序为：

1. 执行FROM
2. where条件过滤
3. GROUP BY分组
4. 执行SELECT投影列
5. having条件过滤
6. 执行ORDER BY排序
7. 执行limit语句，返回限定行

1、SQL查询

基础查询

SQL中的查询语句只有一个：SELECT，它可与其它语句配合完成所有的查询功能。SELECT语句的完整语法，可以有6个子句，语法如下：

SELECT 目标表的列名或列表达式集合
		FROM 基本表或(和)视图集合
		[WHERE 条件表达式]
		[GROUP BY 列名集合]
		[HAVING 组条件表达式]
		[ORDER BY 列名[集合]]
		[LIMIT]

投影操作：是查询语句里必须有的字句，关键字为 SELECT 它将选择对表中那些列进行操作，这些列将出现在结果中

-- 将需要查询的列的列名写在 投影列位置，查询多列用 , 分隔
SELECT 投影列1,投影列2,投影列3 FROM 表名 where 过滤条件;
-- *通配符：表示查询表中所有的列
SELECT * FROM 表名 where 过滤条件;

-- SELECT 表名.列名
select employees_table.e_name from employees_table;
-- 别名简写
select e.e_name as eName,
			e.e_money
from employees_table as e;

计算列

在数据库管理系统中，我们也可以执行返回计算列的SELECT查询

数据结构中不存在该列数据，但可通过计算得到该数据

代码详见 navicat —> j0426 —> SQL查询

-- 查看到金额乘2后的值
select e.e_money*2 money
from employees_table as e; 

-- CONCAT用于字符串拼接
select CONCAT(e.e_money*2,"元") money
from employees_table as e;

2、条件查询

WHERE子句应用搜索条件与SELECT查询获得的数据，搜索条件可以采用不同的形式，但是条件的值都是布尔值

2-1、单条件查询

运算符：= < > <= >= !=

-- select 列1,列2,... from 表名 where 列 = 值;

2-2、多条件查询

运算符：= < > <= >= !=

-- select 列1,列2,... from 表名 where 列 = 值 && 列>值;

3、范围查询

-- select 列1,列2,... from 表名 where 列名 between 值1 and 值2;

4、模糊查询

用于比较相似的值，利用通配符来实现模糊查询

SELECT 列A,列B FROm 表名 WHERE 要查询的列名 LIKE 模式

通配符
		"_" 匹配任何单个字符
		"%" 匹配零个或任意多个字符
-- 例：查询姓王的员工
select * from 表名 where 列名 like "王%";

-- 若要查询不姓王的员工，在like前加not
select * from 表名 where 列名 not like "王%";

5、处理空数据

-- 查询为空的信息 is null(是空) 或 is not null(不是空)
SELECT *
FROM student_table
WHERE studentName IS NULL;

6、去重查询

-- distinct数据查询去重
SELECT distinct 列名 FROM 表名;

7、返回限定行limit

-- select 列1,列2,列3 from 表名 limit 开始序号,返回行数
-- 开始序列号：从0开始； limit：仅限mysql使用(分页查询常用)
-- 如果从第一行开始返回 0,n 可简写为 limit n
SELECT * FROM t_employee limit 5,8; -- 表示从t_employee表中查询返回从第6行到第8行的三条数据

8、定义集合关系 in()

-- select 列1,列2,列3 from 表名 where 限定的列名 in ();
SELECT * FROM t_employee WHERE f_deptName in ("技术部","销售部");
-- 表示从t_employee表中查询f_deptName列的 技术部和销售部的员工信息

-- not in 取消集合关系

9、排序 order by

-- (默认是升序ASC) SELECT * FROM 表名 order by 列名
SELECT * FROM t_employee ORDER BY f_money; -- 表示从t_employee表中按f_money列查询信息，并按f_money的值升序排列

-- 降序 在最后加上 desc
SELECT * FROM 表名 order by 列名 desc

-- 当排序多列时会先按第一列数据进行排序，完成后，当第一列有相同数据时再按第二列进行排序
-- 例如下行代码：会先按工资降序排列，排序完成后当工资有相同的数据时会再按入职时间进行升序排列
SELECT* FROM t_employee ORDER BY f_money DESC,f_inDate;

10、分组查询 Group by

可以让表中的行进行分组，然后在每个组上应用聚合函数

分组：就是将制定规则中相同的数据进行“小区域”的划分，然后针对若干个“小区域”进行数据处理

语法：

-- 例如：统计每个部门学历是本科的员工数量
select deptName,count(*) enNum from t_employee where edu="本科" group by deptName;	

多列分组

只有多列数据都相同时才会被分到一个组

分组语句对投影列的限制：
1、分组列(group by后的列名)
2、聚合函数列

-- 例：按部门和性别分组；只有部门和性别相同才被分到一组
SELECT f_deptName,f_gender,count(*) as emNum FROM t_employee where f_money>=8000 group by f_deptName,f_gender;

-- 聚合函数是在分组后执行的，分组又是在过滤条件(where)后执行，所以通过where来对聚合函数进行过滤都是不行的 
-- 如果想对聚合后的数据进行过滤，使用having子句
-- 例：平均工资在8000以上的部门
SELECT f_deptName,avg(f_money) FROM t_employee GROUP BY f_deptName HAVING avg(f_money)>8000;

-- where 和 HAVING 的区别：
-- 1、where运行在分组前，因此不能执行任何聚合函数
-- 2、having时运行在分组后，只能用作聚合函数的过滤

3⃣️数据操作语言(DML)

用于数据库中数据的修改，包括添加、删除、修改等

1、添加

向表格添加符合每列数据类型要求的数据

-- insert into 表名(列名列表)value(列数据); （只能添加单行）
insert into user_table(userName,userPassword,userMoney)value
("易国柱","111111",1897.32);

-- insert into 表名(列名列表)values(列数据); （使用 values 可添加多行数据）
insert into user_table(userName,userPassword,userMoney)values
("易国柱","111111",1897.32),
("易国柱","111111",1897.32),
("易国柱","111111",1897.32),
("易国柱","111111",1897.32);

2、删除

删除满足过滤条件的所有

-- delete from 表名 where 过滤条件
delete from user_table where userPassword="ygz970518";

3、修改

将满足过滤条件的单行数据做单独修

-- update 表名 set 列1=新值1,列2=新值2 where 过滤条件
UPDATE user_table set userPassword="111111",userMoney=49458.00
where userName="易国柱";

4⃣️数据控制语言(DCL)

控制数据库访问权限

四、数据完整性

可靠性+准确性=数据完整性

1、实体完整性

保证每行所代表的实体能互相区别，不能存在两条一摸一样的记录

1-1、实现方式：

1-1.1、主键约束(Primary Key)：

主键是表中的列，主键列不能为空，也不能重复，一个表中只能有一个主键，做主键必需满足4个条件：

• 值不能为空
• 值必须为一
• 不能有业务含义
• 值不能发生变动

设置主键约束可以在创建表时指定

-- 建表时添加
create table t_table(
  -- 主键自增长、主键自动增长
	id INT PRIMARY KEY AUTO_INCREMENT;
)

-- 建表后添加、删除
主键约束
添加:alter table 表名 add primary key (字段);
删除:alter table 表名 drop primary key;

自动增长
添加:alter table 表名 modify 列名 int  auto_increment;
删除:alter table 表名 modify 列名 int;
-- 注意：若主键设置了自动增长，在删除时必须先删除自动增长，再删除主键约束

1-1.2、唯一约束(Unique)

唯一约束是指给定列的所有值必须唯一，该列在表中每一行的值必须唯一。它和主键约束的区别在于该列可以为空，并且可以在一张表中给多个列设置唯一约束

-- 建表时添加
create table t_table(
	id INT PRIMARY KEY AUTO_INCREMENT,
  money DOUBLE UNIQUE -- 唯一约束，同一列值不能重复
);

-- 建表后添加、删除
添加:alter table 表名 add unique 约束名 (字段);
删除:alter table 表名 drop key 约束名;

2、域完整性

2-1、实现方式：

保证指定列的数据有效性

2-1.1、非空约束(not null)

-- 建表时添加
CREATE TABLE user_table(
userId INT PRIMARY KEY AUTO_INCREMENT, -- 增加主键约束，且自动生成数据
userName varchar(255) NOT NULL, -- 非空约束
userBirthday DATE,
userMoney DECIMAL(9,2),
);

-- 建表后添加、删除
添加:alter table 表名 modify 列名 数据类型 not null;
删除:alter table 表名 modify 列名 数据类型 null;

2-1.2、默认约束(Default)

-- 建表时添加
CREATE TABLE user_table(
userId INT PRIMARY KEY AUTO_INCREMENT, -- 增加主键约束，且自动生成数据
userName varchar(255) NOT NULL, -- 非空约束
userBirthday DATE,
userMoney DECIMAL(9,2),
userJob varchar(255) DEFAULT "教师" -- 默认约束
);

-- 建表后添加、删除
添加:alter table 表名 alter 列名 set default '值';
删除:alter table 表名 alter 列名 drop default;

2-1.3、检查约束(Check)

MySQL不支持

CREATE TABLE user_table (
	money DECIMAL(9,2) CHECK(money>1000 and money<1500) -- 检查约束：规定值的范围
);

3、引用完整性

从表外键中出现的数据，必须在主表的主键列中出现

3-1、实现方式

3-1.1、外键约束

外键是用来表示表于表之间联系的，是表中的一个列

外键约束是指在外键列上加上一个约束，强制外键列引用的数据是正确的。如果违反约束，则不允许该数据的修改；没有建立外键约束不等于没有外键

-- 创建员工表、部门表
-- 员工表(从表)
CREATE TABLE employees_table(
e_id INT PRIMARY KEY AUTO_INCREMENT,
e_name varchar(255),
e_money DOUBLE,
e_inDate DATE,
e_depeId INT,
foreign key(e_depeId) references department(d_depeId) -- 在创建表时添加外键约束(注意：此时关联的主表应先创建好)
);

-- 部门表(主表)
CREATE TABLE department(
d_depeId INT PRIMARY KEY AUTO_INCREMENT,
d_name varchar(255)
);
INSERT INTO department(d_name) VALUES
("开发部"),("财务部"),("销售部"),("技术部"),("管理部");

-- ALTER TABLE 添加外键约束的表 ADD CONSTRAINT 外键列 FOREIGN KEY(从表外键列列名) REFERENCES 主表表名(主表主键列名)
ALTER TABLE employees_table ADD CONSTRAINT e_depeId FOREIGN KEY (e_depeId) REFERENCES department(d_depeId);
-- 在表创建好后添加外键约束

INSERT INTO employees_table(e_name,e_money,e_inDate,e_depeId)VALUES
("张三",6000,"2000-01-01",5);

-- 删除外键约束下的部门数据
-- 方式一：先删除和部门相关的所有员工信息，再删除部门信息
delete from employees_table where e_depeId=5;
delete from department where d_depeId=5;
-- 方式二：外键置空：先将和部门相关的所有员工外键设置为null，再删除部门信息
update employees_table set e_depeId=null where e_depeId=5;
delete from department where d_depeId=5;

删除:
第一步:删除外键
alter table 表名 drop foreign key 约束名
第二步:删除索引
alter table 表名 drop index 约束名
PS : 约束名和索引名一样

五、条件判断语句

1、if()双分支

-- if(条件,结果为真的数据,结果为假的数据)
SELECT *,if(f_money>6000,"高工资","低工资") FROM t_employee;
-- 表示在t_employee表中工资大于6000的返回高工资，否则返回低工资

2、case-when多分支

-- case when 多分支
-- case 
--  WHEN 条件1 then 数据1
--  WHEN 条件2 then 数据2
--  WHEN 条件3 then 数据3
--  ......
--  ELSE 数据n
--  END 
-- 例：
UPDATE t_employee set 
f_money=f_money+case
WHEN f_deptName="技术部" THEN 500
WHEN f_deptName="管理部" THEN 600
WHEN f_deptName="财务部" THEN 300
ELSE 400
END;
SELECT f_money from t_employee;
-- 代码表示在t_employee表中，技术部工资加500，管理部加600，财务部加300，其余部门加400

六、MySQL函数

1、常用函数

1-1、时间函数

方法	作用
now()	得到当前时间(年月日 时分秒)
curdate()	得到当前时间(年月日)
year()	得到指定日期的年份
date_add()	得到之后的时间
timestampdiff()	得到两个日期之间的间隔，小日期在前

-- now() 得到当前时间的 年月日 时分秒
SELECT now();

-- curdata() 得到当前时间的 年月日
SELECT curdate();

-- year() 得到指定日期的年份
SELECT year("2011-01-01");

-- date_add()
SELECT DATE_ADD(CURDATE(),INTERVAL 10 day); -- 当前时间的10天后
SELECT DATE_ADD(CURDATE(),INTERVAL 10 month); -- 当前时间的10个月后
SELECT DATE_ADD(CURDATE(),INTERVAL 10 year); -- 当前时间的10年后

-- timestampdiff() 得到两个日期之间的间隔，小日期在前
SELECT timestampdiff(day,"1997-05-18",NOW()); -- 得到 1997-05-18 到当前时间的天数
SELECT timestampdiff(month,"1997-05-18",NOW()); -- 得到 1997-05-18 到当前时间的月数
SELECT timestampdiff(year,"1997-05-18",NOW()); -- 得到 1997-05-18 到当前时间的年数

1-2、数据函数

方法	作用
abs()	得到绝对值
ceiling()	向上取整
floor()	向下取整
rand()	随机数,返回一个0-1之间的随机数
sign()	符号函数: 负数返回-1,正数返回1,0返回0

-- abs()
SELECT ABS(-8); -- 8  /*绝对值*/

-- ceiling()
SELECT CEILING(9.4); -- 10 /*向上取整*/
 
-- floor()
SELECT FLOOR(9.4); -- 9 /*向下取整*/

-- rand()
SELECT RAND();

-- sign()
SELECT SIGN(-9); -- -1

1-3、字符串函数

方法	作用
char_length(str)	返回字符串包含的字符数
concat(str, str,int)	用于字符串拼接
insert(str1,int,int,str2)	替换字符串，从指定位置开始替换指定长度
lower()	大写转小写
upper()	小写转大写
left(str,int)	从左边截取
right(str,int)	从右边截取
replace(str1,str2,str3)	替换指定字符串(str2为str1的子串)
substr(str,int,int)	截取字符串,开始和长度
reverse(str)	字符串反转

-- char_length(str)
SELECT CHAR_LENGTH("我爱编程helloworld"); -- 14

-- concat(str, str,int,...)
SELECT CONCAT("我爱编程helloworld",520,"ok"); -- 我爱编程helloworld520ok

-- insert(str1,int,int,str2)
SELECT insert("我爱编程helloworld",1,2,'超级热爱'); -- 超级热爱编程helloworld 从第一个字符开始，到第二个字符替换为 "超级热爱"

-- lower() 小写转大写
SELECT LOWER("ABCdEfG"); -- abcdefg

-- upper() 大写转小写
SELECT UPPER("aBcDEfg"); -- ABCDEFG

-- left(str,int)
SELECT LEFT("我爱编程helloworld",5); -- "我爱编程," 从左边截取5个字符

-- right(str,int)
SELECT RIGHT("我爱编程,helloworld",5); -- "world" 从右边截取5个字符

-- replace(str1,str2)
SELECT REPLACE("我爱编程helloworld","编程helloworld","java"); -- 我爱java

-- substr(str,int,int)
SELECT SUBSTR("我爱编程helloworld",3,7); -- 编程hello 从字符串第3个位置开始，截取七个字符

-- reverse(str) 反转
SELECT REVERSE("abcdefg"); -- gfedcba

1-4、系统信息函数

SELECT VERSION();  /*版本*/
SELECT USER();     /*用户*/

2、聚合函数

2-1、方法：

方法	作用
count()	统计单个列的行数量
sum()	获取单个列的合计值
avg()	获取单个列的平均值
max()	获取单个列的最大值
min()	获取单个列的最小值

2-2、计算规范：

符号、关键字	作用
*	计算所有选择的行，包括null值
all 列名(默认 等价于只写列名)	统计所有非null行数
distinct 列名	统计非null行且不重复行数

-- count() 统计行数
SELECT COUNT(*) from 表名; -- 统计表中所有行数量
SELECT COUNT(all 列名A) from 表名; -- 统计表中，A列非null行数量
SELECT COUNT(distinct 列名A) from 表名; -- 统计表中，A列非null且不重复的行数量

-- sum() 统计单列的和
SELECT sum(f_money) FROM t_employee; -- all 累加非空行
SELECT sum(distinct f_money) FROM t_employee; -- distinct 累加非空不重复行

-- avg() 平均值
SELECT avg(f_money) FROM t_employee; -- 非空行的平均值
SELECT sum(f_money)/count(*) FROM t_employee; -- 加上了非空行的平均值

-- max() 最大值, min() 最小值
SELECT max(f_money),min(f_money) FROM t_employee;

七、子查询

在一个查询语句中再嵌套一个查询语句，称为子查询；子查询是潜逃在查询语句里面的查询语句

代码详情见navicat j0428查询

1、select中嵌入子查询

当作列来使用，子查询查出来的结果必须是单行单列

标准结构：

select 列1,列2,(子查询) as 列别名 from 表名;
-- 部门表
CREATE TABLE t_department(
`pk_deptId` INT PRIMARY KEY AUTO_INCREMENT,
`deptName` varchar(255)
);
INSERT INTO t_department(`deptName`)VALUES
("财务部"),("销售部"),("广告部"),("管理部");

INSERT INTO t_employee(`name`,`e_deptId`)VALUES
("张三",1),("李四",4),("王五",2),
("赵六",3),("刘七",1),("甘八",3);

SELECT e.`name`,
(SELECT d.`deptName` FROM t_department as d WHERE e.`e_deptId`=d.`pk_deptId`) 
FROM t_employee as e;

2、from中嵌入子查询

把子查询的结果当作临时表来使用，外部查询就是基于临时表进行查询

标准结构：

-- select 列1,列2 from(子查询) as 别名;
SELECT demo.*
FROM (SELECT `name`,e_money
FROM t_employee) as demo
WHERE demo.e_money>=4000

3、where中嵌入子查询

三类情况：

3-1、比较运算符

在where中嵌套的子查询，如果子查询返回单行单列，才能使用

-- 查询和张三同一部门的员工：
-- 1、先查询出张三所在的部门
SELECT e.`e_deptId`
FROM t_employee as e
WHERE e.`name`="张三";
-- 2、再根据张三的部门信息查询出该部门的员工
SELECT e2.*
FROM t_employee as e2
WHERE e2.`e_deptId`=(SELECT e.`e_deptId`
FROM t_employee as e
WHERE e.`name`="张三");

3-2、in 和 not in

子查询返回多行单列，才能使用

-- 查询张三和李四所在部门的员工：
-- 1、先查询出张三和李四所在的部门id集合
SELECT `e_deptId`
FROM t_employee 
WHERE `name` in ("张三","李四");

-- 2、根据张三和李四所在的部门id查找员工
SELECT * FROM t_employee WHERE `e_deptId` in 
(SELECT `e_deptId`
FROM t_employee 
WHERE `name` in ("张三","李四"));

3-3、子查询运算符

1、all 运算符

和子查询的结果逐一比较，必须全部满足时表达式的值才为真

-- 查询比所有张姓员工工资都还要高的员工信息
-- 1、先查询所有张姓员工的工资
SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%"
-- 2、再通过all比较所有张姓员工工资
SELECT *
FROM t_employee
WHERE e_money>all 
(SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%")

2、any运算符

子查询的结果逐一比较，只要满足其中一个表达式的值就为真

-- 查询比所有张姓员工工资高的员工信息
-- 1、先查询所有张姓员工的工资
SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%"
-- 2、再通过any比较所有张姓员工工资 (只要比其中一个姓张员工工资高就为真)
SELECT *
FROM t_employee
WHERE e_money>ANY 
(SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%") AND `name` NOT LIKE "张%"

3、exists运算符

exists判断子查询中是否存在数据，如果存在，表达式为真，反之为假。not exists相反

-- 查询员工表，显示与张姓员工相同工资的员工信息
-- 1、先查询所有张姓员工的工资
SELECT e.e_money
FROM t_employee e
WHERE e.`name` LIKE "张%"
-- 2、再判断其它员工有无和张姓员工同工资的
SELECT e2.*
FROM t_employee e2
WHERE EXISTS
(SELECT e.e_money
FROM t_employee e
WHERE e.`name` LIKE "张%" and e.e_money=e2.e_money) and `name` NOT LIKE "张%"

3、组合查询

使用union运算符，将两个查询信息组合返回显示

-- 查询张姓员工工资前二的信息
-- 查询李姓员工工资前二的信息
(SELECT *
FROM t_employee
WHERE `name` like "张%"
ORDER BY e_money DESC LIMIT 2)
union -- 将两个查询信息组合
(SELECT *
FROM t_employee
WHERE `name` like "李%"
ORDER BY e_money DESC LIMIT 2);

4、相关子查询

在主查询中，每查询一条记录，需要重新做一次子查询，这种称为相关子查询。相关子查询的执行，依赖于外部查询的数据，外部查询返回一行，子查询就执行一次。

-- 代码示例
SELECT emName, (SELECT d.deptName FROM t_dept d 
WHERE e.deptId=d.deptId) deptName FROM  t_employee e;

5、非相关子查询

在主查询中，子查询只需要执行一次，子查询结果不再变化。子查询结果供主查询使用，这种查询方式称为非相关子查询

-- 例：查询学生成绩表，显示班上数学成绩最高的同学的信息
select * from t_studentScore
where math=(select max(math) from t_studentScore)

6、联表查询

如果数据来自多个表，那么可以采用联表查询的方式来实现；表连接就是指将多个表联合在一起实现查询结果；表联接采用的是笛卡尔乘积，称之为横向联接

笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积

6-1、内联接

是从结果表中删除与其它被连接表中没有匹配行的所有行，所以内联接可能会丢失信息

在数据库中将多表相连需要使用 INNER JOIN，INNER可以省略

select 列1,列2 from 表1 join 表2;
-- 查询广告部的员工信息
SELECT e.*,d.*
FROM t_employee e join t_department d
on e.e_deptId=d.pk_deptId
WHERE d.deptName="广告部"

6-2、外联接

不管有没有匹配，被定义了外联接的表数据都要出现在结果中

6-2.1、左外联接

在LEFT JOIN左边的表就被定义为外联接，那么此表中所有数据都会出现在查询结果中

select e.*,d.* from 表1 as e left join 表2 as d on e.外键列名=d.主键列名;
-- 左边表(e)的数据会全部显示

6-2.2、右外联接

在RIGHT JOIN右边的表就被定义为外联接，那么此表中所有数据都会出现在查询结果中

select e.*,d.* from 表1 as e right join 表2 as d on e.外键列名=d.主键列名;
-- 右边表(d)的数据会全部显示

6-3、自联接

自联接其实就是内联接或外联接的一种特例，同样可以使用INNER JOIN 或 OUTER JOIN；自联接所联接的表是来自于同一张表

-- 查询每位员工姓名及他们直属领导的姓名
SELECT e1.`name`,e2.`name` as leaderName
FROM t_employee e1
LEFT JOIN t_employee e2
ON e1.leaderId=e2.pk_id

八、视图

视图可以看作是SQL语句的封装，可以看作是临时表，将前面创建的数据保存起来，便于后续使用

-- 创建视图   CREATE view 视图名 as 查询SQL语句
CREATE view demo as 
SELECT e1.`name`,e2.`name` as leaderName
FROM t_employee e1
LEFT JOIN t_employee e2
ON e1.leaderId=e2.pk_id

-- 删除视图
create view 视图名;