SQL中基本SELECT语句及常见关键字的使用（内连接，左/右连接）

这里写目录标题

SQL中基本SELECT语句的使用
- SQL语法简介
- - DDL、DML、DCL
  - SEECT
- SELECT常用关键词
- - group by分组
  - having筛选
  - limit限定条数
  - UION和UION ALL合并
  - SQL执行顺序
- 联表查询
- - 多表查询示例
  - 特殊用法：笛卡尔积（交叉连接）
  - 等值连接vs非等值连接
  - 自连接vs非自连接
  - 内连接vs外连接
  - - SQL 92实现内/外连接
    - SQL 99实现内/外连接

SQL中基本SELECT语句的使用

SQL语法简介

DDL、DML、DCL

DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create 、 drop 、 alter 、create等。

DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录（增删改查），并检查数据完整性，常用的语句关键字主要包括 insert 、 delete 、 update 等。

DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant 、 deny 、 revoke 、 commit 等。

区别：

作用不同：
回滚支持不同：DML可以回滚（前提是关闭了AutoCommit属性），DDL和DML执行后立即commit（不受AutoCommit=FALSE属性影响），无法回滚

SEECT

作为开发人员，最常用的是DML，而其中最常用也最复杂的就是SELECT语句，如下是92和99标准的语句示例：

--sql 92语法:
SELECT .... ,.... ,....(存在聚合函数)
FROM ...,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件 
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDERBY .... ,...(AsC /DESC )
LIMIT ... , ....

--sql 99语法:
SELECT .... ,.... , . ...(存在聚合函数)
FROM ... 
(LEFT / RIGHT)JOIN ....ON 多表的连接条件
(LEFT / RIGHT)JOIN ....ON 多表的连接条件
WHERE 不包含聚合函数的过滤条件
GROUP BY ..., ....
HAVING 包含聚合函数的过滤条件
ORDER BY ...- ,...(AsC /DESC )
LIMIT ... ,....

SELECT常用关键词

group by分组

作用：用来给数据分组，需要指定一个或者一组字段，作为分组的依据

特点：select选中的字段要么是group by修饰的字段，要么是由各种聚合函数修饰的字段，不能有其它字段。

--查看每个部门的平均工资
select dept,AVG(salary)
from tables
group by dept;

having筛选

作用：用来过滤数据，可以用来修饰涉及到聚合函数的过滤条件

特点：必须跟在group by后面，因为必须先分组，才有聚合函数的值，然后才能对这些聚合函数的值进行过滤；当然，如果没有用groupby，只用到了聚合函数，那其实having也可用，但没有过滤的意义，因为总共只有一个组

--查找平均工资大于100的部门
select dept
from table
group by dept having AVG(salary)>100;

--查找最高工资大于100的部门
select dept
from tables
group by dept 
having AVG(salary)>100;

--练习:查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
--方式l:推荐，执行效率高于方式2.
SELECT department_id,MAX(salary)FROM employees
WHERE department_id IN( 10,20,30,40)GROUP BY department_id
HAVING MAX(salary) > 10000;
I
--方式2:
SELECT department_id,MAX (salary)FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

--结论：当过滤条件中没有聚合函数时，则此过滤条件声明在wHERE中或HAVING中都可以，但建议用where，因为执行效率更高。

wher和having对比

用法：当过滤条件中带有聚合函数时，不能用where，必须用having，否则两者都可以用；having必须跟在groupby后面
先后顺序：当同时存在where和having时，having放在后面
执行效率：做联表查询时，where的执行效率比having高，因为having必须在group by分组之后才能筛选，而where是先筛选，再group by，，先筛选的话group by的效率自然就高了。

limit限定条数

如果给定一个参数n，该参数代表返回行数（默认从从第一行数据算起，也就是0到n-1行）

如果给定两个参数，第一个参数 i 代表从表格中的第 i + 1 行数据开始检索，第二个参数 j 代表返回的行数，如LIMIT 10,20 的意思返回从11行开始，往后的20行。

 --前n行
 select * from tables limit n-1
 --第n行
 select * from tables limit n-1,1
 --第5到8行
  select * from tables limit 4，4
  
 --倒数前n行
 select * from tables order by column desc limit n-1,1
 --倒数第n行
 select * from tables order by column desc limit n-1
 --倒数5到8行
 select * from tables order by column desc limit 4,4

UION和UION ALL合并

UION代表横向合并，比如合并两个select的数据行，UION会自动去除重复行再返回

UION ALL则不会去重，完全是横向的一个拼接，效率较高

SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e. department_id IS NULL;

SQL执行顺序

联表SQL语句的执行过程:
FROM …笛卡尔积-> ON->(LEFT/RIGNTJOIN) ->WHERE->GROUP BY ->HAVING -> SELECT -> DISTINCT->#ORDERBY ->LIMIT

总结：先做笛卡尔积，然后ON筛选，然后where筛选，然后group by分组，然后having对分组结果再筛选，然后才选中数据；最后再对这些数据进行排序，然后分页。

联表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件:这些一起查询的表之间是有关系的(一对一、一对多)，它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如:员工表和部门表，这两个表依靠“部门编号”进行关联。

多表查询示例

例如有两张表，分别是员工信息表，和部门信息表
在这里插入图片描述
需求：查询所有员工的姓名及其部门名称

--sql92语法，联表查询
SELECT employee_id, department_name
FROM employees, departments
WHERE employees. department_id = departments.department_id;

使用多表查询的注意点

--sql92语法，联表查询

--如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表名。
SELECT employees.employee_id, departments.department_name ,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

--.可以给表起别名，在SELECT和WHERE中使用表的别名。
SELECT emp.employee_id, dept.department_name , emp.department_id
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id;

--注意：如果有n个表实现多表的查询，则需要至少n-1个连接条件
--练习:查询员工的employee_id,last_name , department_name , city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e. department_id,l.location_id
FROM employees e, departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id= l.location_id ;

特殊用法：笛卡尔积（交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的所有可能。组合的个数即为两个集合中元素个数的乘积数。

在这里插入图片描述

如果选中不同表的字段，如下情况会产生笛卡尔积:

没有指定多个表的连接条件(或关联条件)
连接条件(或关联条件)无效

--sql92语法，联表查询

--案例:查询员工的姓名及部门名称所有可能的组合
SELECT last_name,department_name
FROM employees, departments;

等值连接vs非等值连接

角度1:从连接条件的角度看，分为等值连接和非等值连接

--sql92语法，联表查询

--如：查询薪水介于最低薪资和最高薪资的人的姓名、薪资、级别
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
where e.salary between j.lowest_sal  and j.highest_sal;

自连接vs非自连接

角度2:从连接的表来看，自连接和非自连接

自连接：当前表和当前表进行连接

--sql92语法，联表查询

--如：查询员工id,员工姓名及其管理者的id和姓名
SELECT emp.employee_id, emp.last_name, mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.manager_id = mgr.employee_id;

内连接vs外连接

角度3:从连接方式来看，分为内连接和外连接

之前涉及到的都是内连接，因为最终只显示了满足连接条件的数据行的对应字段，但其实除此之外还有其它连接方式

内连接：最终只显示了满足连接条件的数据行
外连接：除了返回满足连接条件的行以外，还返回左表或右表中某些中不满足条件的行，没有匹配的行时,结果表中相应的列为空(NULL)。外连接具体又可分为左（外）连接，和右（外）连接。
- 左（外）连接：会将左表所有数据行都查出来，如果右边表中有数据和它匹配（满足连接条件），就正常显示；如果没有数据和左边匹配，就用NULL填充
- 右（外）连接：会将右表所有数据行都查出来，如果左边表中有数据和它匹配（满足连接条件），就正常显示；如果没有数据和右边表匹配，就用NULL填充

在这里插入图片描述

例子：查找员工和其对应部门时

内连接：只返回有部门的员工和有员工的部门，没有对应部门的员工不会返回，没有员工的部门也不会返回。
左外连接：返回所有员工，及其对应的部门，没有对应部门的员工也会包含，只不过对应部门用NULL代替
右外连接：返回所有部门，及其对应的员工，里面没有员工的部门也会包含，只不过对应员工用NULL代替

SQL 92实现内/外连接

内连接

SELECT employee_id, department_name
FROM employees e , departments d
WHERE e.department_id = d.department_id;

外连接

在连接条件中使用+来实现外连接，+号代表把NULL值也显示出来，+号在左边就是右连接，+号在右边就是左连接

--SQL92语法实现外连接
SELECT employee_id, department_name
FROM employees e , departments d
WHERE e.department_id = d.department_id(+);
--注意：MysQL不支持sQL92语法中外连接的写法!

满外链接

--满外连接: mysql不支持FULL OUTER JOINSELECT last_name , department_name
FROM employees e 
FULL OUTER JOIN departments d ON e.department_id = d.departmentlid;

MySQL要实现满外链接，需要用到uion或者uion字段去拼接两个结果集
比如将左外连接和右外连接进行uion操作

在这里插入图片描述
具体实现

--左上图的结果
SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
--去重拼接
UNION
--右上图的结果
SELECT employee_id,department_name
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.department_id;

或者将左外连接和去除掉公共部分的右外连接进行uion all操作

在这里插入图片描述

具体实现

--左上图的结果
SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id 
--拼接
UNION ALL
--右中图的结果
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e. department_id IS NULL;

SQL 99实现内/外连接

内连接

--SQL99i语法实现内连接:
SELECT last_name , department_name
FROM employees e 
JOIN departments d ON e.department_idr d.department_id;

SELECTlast _name , department_name , city
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = 1.location_id;

--注意：JOIN其实本质上是INNER JOIN，只不过省略了INNER

外连接

--练习:查询所有的员工的last_name , department_name信息
--左外连接:查出的是所有员工，及部门信息
SELECT last_name , department_name
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id;
--右外连接:查出的是所有部门，及员工信息
SELECT last_name, department_name
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.department_id;