数据库(mysql)-连接嵌套查询-2

子查询

MySQL中的子查询（Subquery）是嵌套在其他SQL查询中的查询。子查询可以出现在SELECT、FROM或WHERE子句中，并用于返回将被用于外部查询的数据。子查询的结果可以是一个单一的值、一行、一列或多行多列的数据集。

单行单列查询 

 实例

#查询比齐雷老师工资高的有哪些 
SELECT name FROM teacher WHERE salary>(SELECT salary FROM teacher WHERE name='齐雷');
//注意需要写(查询语句) 查询齐雷工资的就是子查询

注意子查询需要用()括起来

聚合函数 查询结果集只有一个值

实例

#查询哪些老师工资高于平均水平
SELECT name,salary FROM teacher WHREE salary> (SELECT AVG(salary) FROM teacher );

多行单列查询

子查询的查询的结果集有多个值

进行等值判断是要配合: IN 或 NOT IN 使用

如果 进行关系的计算

 ANY 用于在查询中筛选符合特定条件的任意一个数据

 ALL 它通常用于指定某个操作应适用于所有相关的行或值。

>ANY 即只要大于多行数据任意一个就行 (>最小值)

<ANY 即只要小于多行数据任意一个就行 (<最大值)

>ALL 即必须全部大于所有值 (>最大值)

<ALL 即必须全部小于所有值(<最小值)

 实例

#查看与'祝雷'和李费水同班的同学有哪些
# 这边是符合其中之一就行 ANY
SELECT name,age,gender,class_id FROM student WHERE class_id IN (SELECT class_id FROM student WHERE name IN ('祝雷','李费水'))
#查看比教科目2和教科目4老师工资都高的老师都有谁
多行单子列进行关系运算
SELECT name,salary FROM teacher WHERE salary>ALL(SELECT MAX(salary) FROM teacher WHERE subject_id IN(2,4));

更改数据

#将同李伟老师的科目的老师工资加500
UPDATE teacher SET salary=salary+500 WHERE subject_id=(SELECT subject_id FROM teacher WHERE name='李伟');
#将李伟的工资改成和李建明一样
UPDATE teacher SET salary =(SELECT salary FROM teacher WHERE name='李建明') WHERE name ='李伟';

 删除数据

#删除同李明老师教同一科目的所有老师
DELETE FROM teacher WHERE subject_id =(SELECT subject_id FROM teacher WHERE name='李明')

联表查询

联表查询在关系型数据库中非常常见，用于从多个表中检索相关数据

内连接

在SQL中， JOIN关键字默认执行的是内连接。内连接返回两个表中满足连接条件的记录。如果不明确指定连接类型， JOIN 就相当于  INNER JOIN。

SELECT   
    -- 选择要显示的列，可以是来自一个表的列，也可以是两个表通过JOIN操作关联后的列  
    table1.column1,   
    table1.column2,   
    table2.columnA,   
    table2.columnB  
FROM   
    -- 第一个表名  
    table1  
INNER JOIN   
    -- 第二个表名  
    table2   
ON   
    -- 指定JOIN条件，即两个表中用于匹配的列  
    table1.matching_column = table2.matching_column  
WHERE   
    -- 可选的WHERE子句，用于进一步过滤结果  
    some_condition;

实例 

#查询教语文老师的名字(这边teacher表有老师姓名和subject表要科目名称)
SELECT teacher.name,subject.name FROM teacher JOIN subject ON teacher.subject_id = subject.id  WHERE subject.name='语文';
SELECT teacher.name,subject.name FROM subject JOIN teacher ON  subject.id = teacher.subject_id WHERE subject.name='语文';

注意如果两个表中都要字段需要加对应的表名

否则 会报字段名模糊

外连接

外连接（Outer Join）是一种连接两个或多个表的方式，它返回包括匹配行和非匹配行的结果集。与内连接（Inner Join）不同，内连接只返回匹配的行，而外连接会返回一侧表中的所有行，即使另一侧表中没有匹配的行。

左外连接（Left Outer Join）

左外连接返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则结果集中对应的列将包含NULL值。

SELECT A.*, B.*FROM TableA a

LEFT OUTER JOIN TableB b ON a.key = b.key;

#查询所有班的老师,班级全部列出,班级中没有老师用null填补
SELECT * FROM class c LEFT JOIN teacher t ON c.teacher_id = t.id;

右外连接（Right Outer Join）

右外连接返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则结果集中对应的列将包含NULL值。

SELECT A.*, B.*FROM TableA a

RIGHT OUTER JOIN TableB b ON a.key = b.key;

#查询所有老师教的班,老师全部列出,老师没任教的班用null填补
SELECT * FROM class c RIGHT JOIN teacher t ON c.teacher_id = t.id;

全外连接（Full Outer Join）

全外连接返回左表和右表中的所有行。如果某侧表中没有匹配的行，则结果集中对应的列将包含NULL值。

SELECT A.*, B.*FROM TableA a

FULL OUTER JOIN TableB b ON a.key = b.key;

 在某些数据库系统中，可能需要使用UNION操作符来模拟全外连接的行为。

SELECT A.*, B.*FROM TableA a

LEFT OUTER JOIN TableB b ON a.key = b.key;

UNION

SELECT A.*, B.*FROM TableA a

RIGHT OUTER JOIN TableB b ON a.key = b.key;

 实例

#查看姓张和姓李的学生各有多少人
SELECT Count(*) '人数','姓张' AS '姓氏'  FROM student s
WHERE  name  LIKE '张%'
UNION
SELECT Count(*) '人数','姓李' AS'姓氏' FROM student s
WHERE   name  LIKE '李%';

自连接

自连接（Self-Join）是一种特殊的连接操作，它涉及到一个表与其自身进行连接。自连接通常用于比较表内的行，找出满足特定条件的行之间的关系。自连接通常通过使用别名来区分同一个表在查询中的不同实例

 模版

SELECT A.*, B.*FROM TableA a,TableA b

WHERE a.key = b.key

#查询教师表中的领导
SELECT t.name ,t1.name FROM teacher t1,teacher t
WHERE t1.id = t.manager