关系型数据库
1前言：
数据库 (DateBase) ，简称DB
数据库管理系统（DateBase Management System），简称DBMS
结构化查询语言（Structured Query Language），简称SQL，操作数据库的一门编程语言
1.1为什么使用数据库？
文件流缺陷：效率太低
数据库优势：
1、降低数据的冗余度
2、提高数据的一致性
3、存储数据可以共享
4、实现数据的安全性
5、便于维护数据的完整性
6、建立数据库所遵循的标准
1.2数据在数据库中的存储形式
层次模型
网状模型
关系模型
1.3关系型数据库
关系模型把世界看做是由实体和联系组成的，所谓实体就是指在现实世界中客观存在并可相互区别的事物，实体具有的某一特性就是属性。
关系型模型数据库是一种以表作为实体，以主键和外键关系作为联系的一种数据库结构。
在关系型数据库中，向类似的实体被存入表中，表table是关系型数据库的核心单元。
1.3.1主键：
在关系型数据库中，用一个唯一的标识符来标识每一行，这个标识就是主键（primary Key） 主键有两个特点：不可以重复，不能为空
表头可以看成是类名
每一行就是一个对象数据
每一列是该对象的属性
1.3.2外键：
在关系型数据库中，外键(Foreign key)是用来表达表和表之间关联关系的列。通常外键是另外一个表的主键。
1.3.3关系型数据库的三种关系
1、一对一关系
一条主表记录对应一条从表记录。同时一条从表记录对应一条主表记录。
有外键的表称为从表
有主键的表称为主表
从表或者主表是一个相对的概念，看立足点，基于谁去分析。
2、一对多关系
一条主表记录对应多条从表记录。同时一条从表记录对应一条主表记录。
多边的表称为从表； 一边的表称为主表
外键加在从表当中
3、多对多关系
一条主表记录对应多条从表记录。同时一条从表记录对应多条主表记录。
在关系型数据库中，利用第三方的中间表，描述主表和从表之间的多对多关系。
2关系型数据库管理系统
只是一个保存数据的容器。DBMS来管理数据库中的数据。
2.1数据库管理系统分为：
1、本地数据库管理系统
又称为桌面型数据库管理系统，在这种系统模式下，RDBMS关系型数据库管理系统与数据应用程序在运行在同一客户端的进程中。
2、数据库服务器管理系统
RDBMS和数据库应用程序运行在不同的进程，通常在不同的机器上，一般数据库
2.2数据库引擎
数据存储引擎是数据库底层软件组织，数据库管理系统DRMS使用数据引擎进行创建、查询、更新和删除数据。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的引擎，还可以获得特定的功能。
1、innoDB存储引擎
是事务型数据库的首选引擎，提供提交、回滚、崩溃恢复能力的事务安全能力，并可以实现并发控制。是默认的MySQL引擎。
2、MySAM存储引擎
拥有较高的插入、查询速度、但不支持事务。
3、MEMORY存储引擎
将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。主要用于数据量不大的临时数据。
灵活选择使用哪种引擎。
1、
– 查询存储引擎：
show engines;
2、
– 在建表时设置引擎
– 在建表语句后加上engine=引擎名
create table t_test(
user_name varchar(200)
)engine=MyISAM;
3、
– 修改已经创建表的存储引擎
alter table t_test engine=MyISAM;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v8nHhMva-1683115983186)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230425141617099.png)]
3结构化查询语言SQL
是一种用于管理关系型数据库，并与数据库中的数据进行通讯的计算机语言。
SQL与RDBMS协同工作，来定义数据库的结构、存储数据、操纵数据、获取数据、控制对数据的
SQL的非过程性质
在本质上就是非过程式的，主要关心的是操作的结果。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eBl0J3f4-1683115983188)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230425230745936.png)]
3.1SQL语言的分类
分为四类：
数据定义语言DDL （data define language）：创建、修改、删除数据库的内部数据结构
数据查询语言DQL：用于数据库中数据的查询
数据操作语言DML：用于数据库中数据的修改、包括添加、删除、修改等
数据控制语言DCL：控制数据库访问权限
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H5UvOLQ2-1683115983188)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230425230946516.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ay9FUQQn-1683115983189)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230425231332453.png)]
3.2 DDL数据定义语言
3.2.1操作数据库
1.创建/删除数据库
DDL语言：
创建/删除数据库：数据库名称要唯一，且符合标识符规则
**create database 数据库名称;---->创建数据库**
**drop database 数据库名称;---->删除数据库**
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BxDeYdXE-1683115983189)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502103346413.png)]
3.2.2操作表
1.查询表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aM3WIfMN-1683115983190)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502103903844.png)]
2.创建表
DDL语言：
-- create table 表格名称(
-- -- 表格列的信息
-- -- 列名1 列的数据类型 列的约束,
-- -- 列名2 列的数据类型 列的约束
-- );
create table t_friend(
friend_name varchar(255),
friend_phone varchar(11),
friend_address varchar(255),
friend_age varchar(255)
);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yqn7VQGm-1683115983191)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230425231608403.png)]3.修改/删除表
- 创建表后添加新列
语法:
alter table 表名 add 新列名 新列数据类型 新列约束;
示例：
alter table t_test_user add u_info varchar(255);
- 创建表后 修改列
语法：
alter table 表名 change 旧列名 新列名 新列数据类型 新列约束;
示例：
alter table t_test_user change u_info u_test varchar(100);
- 创建表后 删除列
语法：
alter table 表名 drop column 列名;
示例：
alter table t_test_user drop column t_count;
- 删除表
-- drop table 表名
drop table t_friend;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ObLDkQRx-1683115983191)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230425233300918.png)]
3.2.3数据类型
数据类型是数据的一种分类，是按照数据结构来分类的。数据类型的作用是为了把数据分成所需大小的不同数据来进行存储。
一共分为五种：
- 整数类型
| 类型 | 名称 | 大小 |
|---|---|---|
| tinyint | 小整数值 | 1字节 |
| smallint | 大整数值 | 2字节 |
| mediumint | 大整数值 | 3字节 |
| int | 大整数值 | 4字节 |
| bigint | 极大整数值 | 8字节 |
- 浮点类型
| 类型 | 名称 | 大小 | 范围 |
|---|---|---|---|
| float | 单精度浮点数 | 4字节 | 7位小数 |
| double | 双精度浮点数 | 8字节 | 15位小数 |
| decimal（m,d）m为总位数 ;d为小数位 ;m必须大于d | 17字节 | 30位小数 |
- 字符串类型
| char（n） | 定长字符串 存储性能高，但浪费空间 |
| varchar（n） | **变长字符串 ** 存储性能低，但节约空间 |
| tinytext | 短文本字符串 |
| text | 长文本数据 |
| mediumtext | 中等长度文本数据 |
| longtext | 极大文本数据 |
- 日期类型
| 格式 | 格式 | ||
|---|---|---|---|
| date | 3字节 | YYYY-MM-DD（2000-10-10） | 日期值 |
| time | 3字节 | HH:MM:SS(12:13:14) | 时间值或持续时间 |
| year | 1字节 | YYYY | 年份值 |
| datetime | 8字节 | YYYY-MM-DD HH:MM:SS | 混合日期和时间 |
| timestamp | 4字节 | YYYMMDD HHMMSS | 混合日期和时间，时间戳 |
- 二进制类型
| 类型 | 用途 |
|---|---|
| tinyblob | 不超过255个字节的二进制字符串 |
| blob | 二进制形式的长文本数据 |
| mediumblob | 二进制形式的中等长度文本数据 |
| longblob | 二进制形式的极大文本数据 |
- ENUM枚举型
是一个字符串对象，其值是从列创建时定义的允许值列表中选择的，提供预设值。
使用语法：
create table t_ employee(
employee varchar(50),
edu enum('高中','大专','本科')
)
create table t_test_enum(
t_gender enum('男','女','中性'),
t_edu enum('高中','大专','本科','硕士')
);
3.3DML数据操作语言
DML数据的操作语言： 添加 删除 修改
1.添加记录
- 单行添加
语法：
insert into 表名（列名列表）value（列数据）;
示例：
insert into t_test_user(t_username,t_password,u_test)
value('张三','111111','1');
insert into t_test_user(t_username,t_password)
value('李四','111144411');
- 多行添加 ：给所有列添加数据，数据列名列表可省略，但不建议
语法：
insert into 表名（列名列表）values（列数据）; //此写法只适用于MySQL数据库
示例：
insert into t_test_user(t_username,t_password)
values('李四','111141'),
('杨三','124567'),
('李八','115411');
2.删除数据
语法：
delete from 表名 where 过滤条件 //where语句可以不写 ，表示删除全部表格数据
示例：
delete from t_test_user where t_username ='李八';
3.修改数据
语法：
update 表名 set 列1=新值，列2=新值 where 过滤条件； //where语句可以不写 ，表示修改全部表格数据
示例：
update t_test_user set t_password='123456' where t_username='a哈哈哈';
4数据完整性—约束
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VYxsTt0y-1683115983192)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502122041890.png)]
数据完整性的概念：
可靠性+准确性=数据完整性
为了防止垃圾数据的产生，从而影响数据库的执行效率
数据库完整性的分类：
①实体完整性、②域完整性、③引用完整性、④自定义完整性
4.1实体完整性
概念：保证每行所代表的的实体能够相互区别，不能存在两条一模一样的记录。
实体完整性的实现方式：
主键约束：
主键是表中的列，主键列不能为空，也不能重复。一个表只能有一个主键
唯一的约束：
唯一约束是指给定列的所有值必须唯一，该列在表中每一行的值必须唯一。
它和主键约束的区别在于该列可以为空，并且可以在一张表中给多个列设置唯一约束。
设置主键约束：
设置主键约束可以在创建表时指定。
-- primary key设置主键约束 auto_increment设置自增长(自动编号) unique设置唯一约束
create table student_info(
pk_ID int primary key auto_increment,
name_info varchar(255),
birthday_info date,
class_info varchar(255),
score_info int unique,
);
-- 建表后添加删除
主键约束：
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key
自动增长：
添加:alter table table_name modify 列名 int auto_increment
删除:alter table table_name modify 列名 int
唯一约束：
添加:alter table table_name add unique 约束名（字段）
删除:alter table table_name drop key 约束名
适合做主键的必须满足的四个条件：
①值不能为空 ②值必须唯一 ③不能有业务含义 ④值不能发生变动
4.2域完整性
概念：保证指定的列的数据的有效性
实现方式：
not null非空约束
default(默认值)设置默认值
check检查约束（MySQL不支持检查约束）
-- not null非空约束 default(默认值)设置默认值
create table student_info(
pk_ID int primary key auto_increment,
name_info varchar(255),
birthday_info date not null,
class_info varchar(255) default'j194',
score_info int unique
);
-- 建表后添加删除
非空约束：
添加:alter table table_name modify 列名 数据类型 not null
删除:alter table table_name modify 列名 数据类型 null
默认约束：
添加:alter table table_name alter 列名 set default '值'
删除:alter table table_name alter 列名 drop default
4.3引用完整性
概念：从表外键中出现的数据，必须在主表的主键列中出现
实现方式：
外键约束
外键与外键约束的区别：
外键约束：保证引用完整性，强制外键列引用的数据是正确的，如果违反该约束，则不允许该条数据的修改。
外键：列表示表与表之间的关系，是表中的一个列。
注意：没有建立外键约束不等于没有外键。
外键约束的添加
-- 员工表
create table employee_info(
pk_ID int primary key auto_increment,
name_info varchar(255),
money_info double,
indate_info date,
deptid_info int,
foreign key(deptid_info) references deptid_info(pk_deptid)
);
-- 部门表
create table deptid_info(
pk_deptid int primary key auto_increment,
name_info varchar(255)
);
-- 添加外键一 在建表之后添加外键
alter table 添加外键约束的表 add constraint 外键列 foreign key (外键列名) references 主键表（主键名）
alter table employee_info add constraint deptid_info foreign key (deptid_info) references deptid_info(pk_deptid);
-- 添加外键二 在建表的同时添加外键 要先把关联的表建好
foreign key(外键列) references 主表(主键列);
foreign key(deptid_info) references deptid_info(pk_deptid);
-- 删除表
drop table employee_info;
drop table deptid_info;
insert into deptid_info(name_info)
values('宣传部'),('销售部'),('技术部');
insert into employee_info(name_info,money_info,indate_info,deptid_info)
values
('张三','5000','2001-12-31','1'),
('李四','5500','2021-12-12','2'),
('王五','6700','2022-09-24','1');
外键约束的删除
-- 删除
delete from dept
删除外键约束下的部门约束
方式一：先删除和部门相关的所有员工信息，再删除部门信息
delete from employee_info where deptid_info='2';
delete from deptid_info where pk_deptid='2';
方式二：外键置空 先将和部门相关的所有员工外键设置为null 再删除部门信息
update employee_info set deptid_info=null where deptid_info='3'
delete from deptid_info where pk_deptid='3';
4.4自定义完整性
指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
5DQL数据查询语句
5.1SQL查询的基本结构
数据库的查询是使用select语句来实现查询的。SQL中的查询语句只有一个:select，它可与其他语句配合完成所有的查询功能。
select语句的完整语法，可以有6个句子：完整的语法如下：
select 目标表的列名或列表表达式集合
from 基本表或（和）视图集合
[where 条件表达式]
[group by 列名集合]
[having 组条件表达式]
[order by 列名[集合]]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OHka0sVN-1683115983192)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502111124779.png)]
5.2基础查询
5.2.1基础查询
-- 标准语法
-- select 投影列 from 表名 where 过滤条件;
select name_info,birthday_info from student_info;
-- *表示通配符，表示查询所有的列
select * from student_info;
5.2.2表前缀、列别名、表别名
表前缀（表名.列名）、列别名（as或空格）、表别名（as或空格）：
在多个表有相同的列名的时候，使用别名更容易区分
select 列列名 from 表名
给列加上表前缀： 表名.列名
select student_info.name_info from student_info;
列加上表前缀、表名、列名
select student_info.name_info as stuName from student_info;
别名 列名/表名 as 列/表别名
别名 列名/表名 空格 列/表别名 --->as可以用空格替代
select s.name_info as stuName from student_info as s;
select s.name_info stuName from student_info s;
5.2.3计算列
返回计算列意味着数据不存在数据库中，但是可以从数据库中的数据计算得来。
-- 计算列
-- 数据库结构中，不存在该列数据，但可通过计算得到该列数据
select s.score_info*2 double_score from student_info s;
-- CONCAT(str1,str2)字符串拼接函数
select CONCAT(s.score_info*2,'分') stuName from student_info s;
5.2.4去重查询distinct
select distinct 列名 from 表名;
-- 去重 去重后 重复数据只显示一条
-- 查看员工所在的所有城市
select distinct f_city from t_employee;
5.3条件查询
where 子句应用搜索条件于select查询获得的数据，搜索条件可以采用不同的形式，但是条件的值都是布尔值。
5.3.1单条件查询：
-- 条件查询的运算符
-- =, ！= , > , >= , < , <=
-- 标准结构：
-- select 列1,列2....from 表名 where 列=值；
查询生日为1998-03-15的学生的姓名、班级信息
select name_info,class_info from student_info where birthday_info = '1998-03-15';
查询分数小于70的所有学生信息
select * from student_info where score_info < '70';
-- *通配符，表示所有列
5.3.2多条件查询：and /or
– and并且 or或者
-- and并且 or或者
查询就j194班级分数大于70分的学生信息
select * from student_info where class_info = 'j194' and score_info > '70';
查询j194班级或者分数大于70分的学生信息
select * from student_info where class_info = 'j194' or score_info > '70';
查询生日在1998-01-01——2003-12-31号范围的学生信息
select * from student_info where birthday_info > '1998-01-01' and birthday_info < '2003-12-31';
5.3.3定义集合关系查询in或者not in
定义一个值集合关系（in 或 not in），在指定的某几个值中进行搜索。此时不能使用=，！=，>=,<=。
-- select * from 表名 where 查询列名 in("查询添加1","查询添加2",..."查询条件n");
-- 查询 技术部或销售部的员工
select * from t_employee where f_deptName in('技术部','销售部');
-- select * from 表名 where 查询列名 not in("查询添加1","查询添加2",..."查询条件n");
-- 查询 不在技术部或销售部的员工
select * from t_employee where f_deptName not in('技术部','销售部');
5.3.4范围查询 between and
between 下限 and 上限
-- 范围查询
-- between 下限 and 上限
查询分数60-80的学生信息
select * from student_info where score_info between '60' and '80';
5.3.5模糊查询like
模糊查询是一种比较实用的过滤方式，用于比较相似的值，利用通配符来实现模糊查询。通配符：
‘_’：匹配任意单个字符；
‘%’：匹配单个或多个字符；
-- 标准结构
-- select 列1,列2...from 表名 where 列3 like 模式
查询姓名姓张的学生信息
select * from student_info where name_info like '张%';
查询姓名中带“张”字的同学
select * from student_info where name_info like '%张%';
查询姓名为null的学生信息
-- 判断空不能使用 = 或 ！=，要使用is/is not null
select * from student_info where name_info is null;
not like 不是某个值/不姓王
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hKAQFWk4-1683115983193)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502112845757.png)]
5.3.6条件判断语句if（双分支）
-- select if(判断条件,条件true返回的值，条件为false返回的值) from 表名 where --->类比三目运算符
if search_condition then
statement_list
else
statement_list
end if;
5.3.7条件判断语句case when（多分支）
-- case
-- when 条件1 then 数据1
-- when 条件2 then 数据2
-- when 条件3 then 数据3
-- ....
-- when 条件n then 数据n
-- else 数据4
-- end--->类比if多分支语句
5.3排序查询order by
根据指定的列进行整表排序。
升序排列默认排序asc，可省略
desc降序排列
-- select * from 表名 order by 排序列名1 desc|asc,排序列名2 desc|asc....排序列名n desc|asc;
多列排序结果是根据order by子句后面列名的顺序确定优先级。
查询结果首先以1的顺序进行排序，而只有当列1出现相同的信息时，这些相同的信息再按列2的进行排序，以此类推。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xONqwWVI-1683115983193)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502113558392.png)]
5.4分组查询Group by
5.4.1聚合函数：用于统计汇总
| 函数 | 描述 | 支持计算规范 |
|---|---|---|
| COUNT(col) | 返回指定列中非NULL值的个数 | */ all 列名/distinct列名 |
| AVG(col) | 返回指定列的平均值 | all 列名/distinct 列名 |
| SUM(col) | 返回指定列的所有值之和 | all 列名/distinct 列名 |
| MAX(列名) | 返回指定列的最大值 | all 列名/distinct 列名 |
| MIN(列名) | 返回指定列的最小值 | all 列名/distinct 列名 |
| GROUP_CONCAT(col) | 返回由属于一组的列值连接组合而成的结果 | - |
count（*）统计所有行的数量，包含null行
count（all 列名） 等价于 count（列名）统计非null行数量
count（distinct 列名） 统计非null行且不重复行的数量
计算规范
- *：匹配所有的行，包括null
- all 列名：默认选项（count(列名)） 匹配所有的行，不包括null
- distinct 列名：匹配所有的行，不包括null，并且去重处理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VfOJQ7YR-1683115983194)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502114426413.png)]
5.4.2分组查询
按照指定的规则进行分组，分组后就相当于将表格划分为小表格，聚合函数应用在多个小表格上，多列分组时，只有多条数据规则相同时才会被分到一个组，例如：按照部门和性别进行分组，销售部和女，销售部和男
-- 标准语法
-- select 列名, 聚合函数 from where 过滤条件 group by 列名A,列名2...列名n
例：统计班级有多少个学生
select count(id) from stu;
查询男同学和女同学各自的数学平均分
select sex,avg（math）from stu group by sex;
查询查询男同学和女同学各自的数学平均分，以及各自人数
select sex,avg（math）,count(*) from stu group by sex;
查询查询男同学和女同学各自的数学平均分，以及各自人数,要求：分数低于70分不参与分组
select sex,avg（math）,count(*) from stu where math>70 group by sex;
查询查询男同学和女同学各自的数学平均分，以及各自人数,要求：分数低于70分不参与分组，分组之后人数大于2个
select sex,avg（math）,count(*) from stu where math>70 group by sex having count(*) > 2;
分组语句中投影列的限制
Group by分组后只能出现的列
- 分组列
- 聚合函数列
having子句
对分组后的数据进行过滤
having子句在Group by之后执行
-- select 列名, 聚合函数 from where 过滤条件 group by 列名A,列名2...列名n having 过滤条件
where和having 的区别
- where运行在分组前，因此不能执行任何聚合函数
- having是运行在分组后，只能做聚合函数或者分组列的过滤
5.4.3SQL语句的代码执行顺序
后面执行的子句，可以使用前执行的语句计算结果和定义的别名等
- from 执行
- where 条件过滤
- group by 进行分组
- select 投影列
- having 添加过滤
- order by 排序
- limit 返回限定行
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ySFq1VqZ-1683115983194)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502115414658.png)]
5.5分页查询limit
limit序号是从0开始的。
limit是限于MySQL的。
多用于分页：页码+每页显示条数。
起始索引：=（当前页码-1）*每页显示的条数
select * from 表名 limit 开始的序号,返回几条;
-- 开始序列号从0开始,只给一个数表示默认从0开始
-- 返回行数：如果表格剩余行数不足，就只会返回剩余的函数
# 简写 如果从第1行开始返回
select * from 表名 limit 0,n;
# 简写为
select * from 表名 limit n;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pVc705ex-1683115983195)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502115846705.png)]
5.7时间函数
| 方法 | 描述 |
|---|---|
| now() | 返回当前时间(年月日 时分秒) |
| curdate() | 返回当前时间(年月日) |
| curtime | 返回当前的时间 |
| date_add(@dt interval 100 day)—>@dt指定日期 | 返回指定时间多少天后的参数 如指定“2023-04-28” 5 year —>2028-04-28 |
| year(@dt) —>@dt指定日期 | 返回日期date的年份(1000~9999) 如指定“2010-03-04”—>2010 |
| timestampdiff(day，@dt1，@dt2) | 返回两个日期的间隔 |
6子查询
子查询：在一个查询语句中再嵌套一个查询语句。
子查询是嵌套在查询语句里面的查询语句，就像语句块里嵌套语句块类似。
select * from t_employee where money >‘张三的工资’;
select money from t_employee where t_name = ‘张三’;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8L6HAtEL-1683115983195)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502141151251.png)]
-- 子查询
select 列1 ，列2，(子查询) as 列别名 from ;
查询员工信息的同时查询对应的部门
select e.name_info, -- 名字 员工表中员工部门对应的数字=部门表中对应的数字
(select d.name_info
from deptid_info as d where e.deptid_info = d.pk_deptid)as deptName -- 部门
from employee_info as e;
-- 嵌套在select语句中SQL语句只能是单行单列
-- 在from里面加入子查询
-- 就是把子查询的结果当作是一个临时表
-- 外部查询就是基于临时表进行查询
-- from里的子查询可以是任意查询语句，然后将其结果作为外部查询的表。
-- 嵌套在from查询里面的子句，必须加别名
-- select * from （select 学号 姓名 from t_student）s;
--
select name_info,money_info from employee_info; -- 作为子查询
select * from ( select name_info,money_info from employee_info) as demo where demo.money_info>8000;
-- 在where中嵌入子查询
select 列1，列2 from 表 where 列 = 子查询
-- 在where中嵌套的子查询三类情况：
-- 1、子查询结果是单行单列，使用比较运算符 = ！= > >= < <=
例：查询和张三同一部门的员工信息
①先查询出张三所在的部门
select deptid_info from employee_info as e where e.name_info = '张三'; -- 3
②再根据张三部门进行查询
select e2.* from employee_info as e2 where e2.deptid_info = ( -- =比较部门序号相同的员工
select deptid_info from employee_info as e where e.name_info = '张三');
-- 2、子查询结果是多行单列，使用in 或者not in运算符
例：查询张三和李四所在部门的员工
①查找到张三和李四所在部门的编号
select deptid_info from employee_info as e where e.name_info in ('张三','李五') -- 1,3
②再根据张三和李四所在部门的编号查找员工
select e2.* from employee_info as e2 where e2.deptid_info in( -- in比较与张三和李四部门序号相同的员工
select deptid_info from employee_info as e where e.name_info in ('张三','李五')
);
-- 3、all any exists
-- all 运算符： 和子查询的结果逐一比较，必须全部满足时表达式的值才为真
例：查询比所有张姓员工工资都还要高的员工信息
①查出所有张姓员工的工资
select money_info from employee_info where name_info like '张%'
②再通过all比较所有张姓员工工资
select * from employee_info where money_info > all ( -- all比较
select money_info from employee_info where name_info like '张%');
-- any 运算符：和子查询逐一比较，其中一条记录满足条件则表达式的值为真
例：查询比任意张姓员工工资都还要高的员工信息
①查出所有张姓员工的工资
select money_info from employee_info where name_info like '张%'
②再通过all比较所有张姓员工工资
select * from employee_info where money_info > any ( -- any比较
select money_info from employee_info where name_info like '张%')
and name_info not like '张%';
-- exists 运算符： 判断子查询是否存在数据，如果存在则表达式为真，反之为假；not exists
例：查询显示员工表 显示与张姓员工相同工资的员工信息
①查询出张姓员工的工资
select e.money_info from employee_info as e where e.name_info like '张%'
②判断其他员工有无和张姓员工同工资的
select e2.* from employee_info as e2 where exists ( -- exists比较
select e.money_info from employee_info as e where e.name_info like '张%'
and e.money_info = e2.money_info)
and e2.name_info not like '张%';
相关子查询：在主查询中，每查询一条记录，需要重新做一次子查询，称为相关子查询
缺陷：效率低下
非相关子查询：在主查询中，子查询只需执行一次，结果不再变化，子查询结果供主查询使用，称为非相关子查询
7联表查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AnuHctvf-1683115983196)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502140931310.png)]
联表查询：
如果数据来自多个表，可以采用联表查询来实现
把多个表联合在一起组成一个表，基于这个表实现查询效果。
笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积。
-- join关键字：将多表相连
select 列1，列2 from 表1 join 表2;
例：联接员工表和部门表
select * from employee_info e join deptid_info d;
-- on关键字：去掉冗余、废弃的数据
select 列1，列2 from 表1 join 表2 on 表1.外键 = 表2.主键;
select * from employee_info e join deptid_info d on e.deptid_info = d.pk_deptid
where e.deptid_info > 3;
练习：查询广告部的员工信息
select * from employee_info e join deptid_info d;
select * from employee_info e join deptid_info d on e.deptid_info = d.pk_deptid
where d.name_info = '广告部'
and e.name_info = '狗%';
表联接分类;
内联接：
是从结果表中删除与其他被联接表中没有匹配行的所有行，所以内联接可能会丢失信息。（两个表的交集数据）
inner join 关键字 inner可省略
外联接：
指不管有没有匹配，被定义了的外联接的表数据都要出现在结果中
left join 左外联接/right join右外联接
select e.*,d.* from employee_info e left join deptid_info d on e.deptid_info = d.pk_deptid;
多对多的联表
查询学生张三以及其老师的信息
从学生表 联接 中间表 联接 老师表
select s.*,t.* from t_student s
left join t_center st on s.pk_stuId = st.fk_stuid
left join t_teacher t on st.pk_id = t.t_teachid;
自联接
查询员工姓名以及直属领导的姓名
每位员工都要直属领导的编号
可以想象成有张领导表
这一列就是指向领导表的外键
select e1.f_name,e2.f_name as leadername
from t_employee el
left join t_employee e2
on e1.leaderid = e2.pk_emId;
视图
视图可以看成是SQL语句的封装，可以看做是临时表，视图查询的结果会随着真实表数据的变化而变化。
视图只提供查询功能，不提供数据修改的功能
创建视图
create view 视图名 as 查询SQL语句
查看视图
select * from VIEW
删除视图
drop view 视图名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AGUXS2Dj-1683115983196)(C:\Users\mx\AppData\Roaming\Typora\typora-user-images\image-20230502135804566.png)]
练习：查询广告部的员工信息
select * from employee_info e join deptid_info d;
select * from employee_info e join deptid_info d on e.deptid_info = d.pk_deptid
where d.name_info = ‘广告部’
and e.name_info = ‘狗%’;
表联接分类;
内联接：
是从结果表中删除与其他被联接表中没有匹配行的所有行，所以内联接可能会丢失信息。（两个表的交集数据）
inner join 关键字 inner可省略
外联接：
指不管有没有匹配，被定义了的外联接的表数据都要出现在结果中
left join 左外联接/right join右外联接
select e.,d. from employee_info e left join deptid_info d on e.deptid_info = d.pk_deptid;
多对多的联表
查询学生张三以及其老师的信息
从学生表 联接 中间表 联接 老师表
select s.,t. from t_student s
left join t_center st on s.pk_stuId = st.fk_stuid
left join t_teacher t on st.pk_id = t.t_teachid;
自联接
查询员工姓名以及直属领导的姓名
每位员工都要直属领导的编号
可以想象成有张领导表
这一列就是指向领导表的外键
select e1.f_name,e2.f_name as leadername
from t_employee el
left join t_employee e2
on e1.leaderid = e2.pk_emId;
视图
视图可以看成是SQL语句的封装，可以看做是临时表，视图查询的结果会随着真实表数据的变化而变化。
视图只提供查询功能，不提供数据修改的功能
创建视图
create view 视图名 as 查询SQL语句
查看视图
select * from VIEW
删除视图
drop view 视图名
[外链图片转存中...(img-AGUXS2Dj-1683115983196)]
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
