Sql 函数 约束 多表查询 新手02

Mysql常见函数使用(二)

1.思维导图

Mysql常见约束使用(三)

1.普通约束探究

CREATE TABLE user(
    id int AUTO_INCREMENT PRIMARY KEY  COMMENT  'ID唯一标识',
    name varchar(10) NOT NULL UNIQUE  COMMENT  '姓名' ,
    age int check (age > 0 && age <= 120)  COMMENT  '年龄' ,
    status char(1) default  '1'  COMMENT  '状态',
    test int  COMMENT  '无约束对比测试字段'
 );

2.外键约束探究

逻辑探究

它们只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联。也就说emp表中的dept_id值可以为任意数值，即是dept表中不存在。而dept表中字段被emp使用了也可以随意删除。 因此无法保证数据的一致性和完整性的。这时候就需要我们严格的进行手动维护或者使用外键约束。

父子关系探究

父表（也称为主表）是提供主键（或唯一键）的表

数据依赖：父表中的主键数据必须先存在，子表中的外键才能引用这些数据。这意味着父表不依赖子表，而子表依赖父表。

# 必须先创建需要关联的父表 (dept)
create table emp(
 id  int auto_increment comment 'ID' primary key,
 name varchar(50) not null comment '姓名',
 age  int comment '年龄',
 job varchar(20) comment '职位',
 salary int comment '薪资',
 entrydate date comment '入职时间',
 managerid int comment '直属领导ID',
 dept_id int comment '部门ID',
 CONSTRAINT fk_dept_id foreign key (dept_id) references dept(id)
 )comment '员工表';

Mysql常见多表查询(四)

1.多表查询探究

多表查询就是指一次性从多张表中查询数据。

原来我们查询单表数据，执行的SQL形式为：select 字段列表 from 表名;

现在我们想要执行多表查询，就只需要使用逗号分隔多张表即可，如： select 字段列表 from 表名1, 表名2;

但是我们这样使用却发现存在问题：的确同时查到了多张表的数据，但是数据形式和我们想要的并不一样,它排列组合了两张表中的所有数据项！

例如我们查询员工、部门表，本来我们预期是每个员工对应其所在的部门，但事实确实，每个员工都对应了所有部门。这种现象也称之为 笛卡尔积 。

因此，在多表查询中，我们需要根据业务情况进行连接查询，消除无效的笛卡尔积，只保留两张表关联部分的有效数据。

例如在上述示例，我们通过表名.字段名指定员工表的外键等于部门表的主键即可获得预期数据~

2.内链接探究

内连接是将两个或多个表根据指定条件连接在一起，只返回在所有连接表中都有匹配的记录。

内连接查询的是两张表交集部分的数据(也就是绿色部分的数据)。语法分为两种: 隐式内连接、显式内连接。

隐式内连接

# 查询每一个员工的姓名 , 及关联的部门的名称 

select e.name,d.name from emp e,dept d where e.dept_id = d.id;

显式内连接

# 查询每一个员工的姓名 , 及关联的部门的名称 
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

3.外链接

外连接分为：左外连接 和 右外连接。左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

左外

# 查询emp表的所有数据, 和对应的部门信息

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

先出左表数据-右表数据拼接

右外链接

# 查询emp表的所有数据, 和对应的部门信息 
select e.*, d.name from emp e right outer join dept d on e.dept_id = d.id;

数据在右边-然后链接左表-包括null-没对应上的也会显示这就是查询范围

4.内外区别探究

在MySQL中，多表查询常常使用内连接（INNER JOIN）和外连接（OUTER JOIN）来实现。两者的区别在于查询结果集中是否包含没有匹配记录的行。以下是它们的区别及例子说明：

1.内连接只返回两个表中满足条件的记录。如果某一行在其中一个表中没有对应的匹配行，则该行不会出现在结果集中。

2.左外连接（LEFT JOIN）： 返回左表中的所有记录，即使在右表中没有匹配的记录，右表中没有匹配的地方会显示NULL。

3.右外连接（RIGHT JOIN）： 返回右表中的所有记录，即使在左表中没有匹配的记录，左表中没有匹配的地方会显示NULL。

5.自链接

# 查询员工 及其 所属领导的名字,如果员工没有领导, 也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = 
b.id;

6.联合查询

• 注意事项
  • 对于联合查询的多张表的字段列表必须保持一致，字段类型也需要保持一致，如果不一致将会报错。
  • union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

select * from emp where salary < 5000
union all
select * from emp where age > 50;

union all查询出来的结果，仅仅只对数据集进行简单的合并，查询结果中可能会存在重复数据项，使用union即可去除重复数据项。

6.子查询

子查询是指在一个 SQL 查询中嵌套的另一个查询。子查询可以用来返回单个值、多个值或一组记录。它们通常用于提供数据给外部查询的条件。

嵌套性：子查询可以嵌入在 SELECT、INSERT、UPDATE 或 DELETE 语句中，也可以作为条件使用。

独立性：子查询可以独立执行，通常会返回一个结果集，该结果集可以被外部查询使用。

可以返回多种结果：子查询可以返回单个值（标量子查询）、单列多行（列子查询）或多列多行（表子查询）。

执行顺序：子查询在外部查询执行之前先执行，外部查询使用子查询的结果。

可用于过滤条件：子查询常用在 WHERE、HAVING 和 FROM 子句中，用于动态生成条件。

1.标量子查询

• 使用示例：查询 “销售部” 的所有员工信息
• 返回单个值（例如，单个数值或字符串）。

# 拆分1： 查询 "销售部" 部门ID，返回单个id值
select id from dept where name = '销售部';

# 拆分2：根据部门ID, 查询员工信息
select id from dept where name = xxx;

# 完整版
 select * from emp where dept_id = (select id from dept where name = '销售部');

2.列子查询

子查询返回的结果是一列（可以是多行）。常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

• 使用示例：查询比 财务部 所有人工资都高的员工信息

SELECT * 
FROM emp 
WHERE dept_id IN (SELECT id FROM dept);

3.行子查询

返回多行的结果集。

• 这个查询将返回所有薪资高于部门 ID 为 1 的任意员工薪资的员工。

SELECT * 
FROM emp 
WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = 1);

4. 表子查询

• 返回多个列和多行的结果集。

SELECT e.name, e.salary 
FROM emp e 
JOIN (SELECT id, name FROM dept WHERE location = '北京') d 
ON e.dept_id = d.id;

(SELECT id, name FROM dept WHERE location = '北京') 返回一个临时表，包含位于 ‘北京’ 的部门的 ID 和名称。外部查询则连接这个临时表与员工表 emp，以获取相应的员工信息。

多表查询业务能实现相关需求的SQL往往会很多, 写法也多种多样，总之，能满足我们的需求，查询出符合条件的记录即可~

5.常用关键字

1.in

IN 用于检查某个值是否在一个给定的集合中，可以是多个值的列表或一个子查询的结果。

SELECT * 
FROM emp 
WHERE dept_id IN (SELECT id FROM dept WHERE location = '北京');
这个查询会返回所有在部门位于 '北京' 的员工。

2.any

ANY 用于与比较操作符（如 =、>、< 等）结合使用，检查一个值是否与子查询结果中的任意值满足条件。

SELECT * 
FROM emp 
WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = 1);
这个查询会返回所有薪资高于部门 ID 为 1 的任意员工薪资的员工。

3.all

ALL 也用于与比较操作符结合使用，检查一个值是否与子查询结果中的所有值满足条件。

SELECT * 
FROM emp 
WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = 2);
这个查询会返回所有薪资高于部门 ID 为 2 的所有员工薪资的员工。

**IN**：用于检查某个值是否在集合中，适用于多个值的比较。

**ANY**：检查一个值是否与子查询结果中的任意值满足条件。

**ALL**：检查一个值是否与子查询结果中的所有值满足条件。