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

news/2024/9/30 10:31:04 标签: sql, oracle, 数据库

这里写目录标题

  • 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">--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修饰的字段，要么是由各种聚合函数修饰的字段，不能有其它字段。

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

having筛选

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

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

sql">--查找平均工资大于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行。

sql"> --前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则不会去重，完全是横向的一个拼接，效率较高

sql">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对分组结果再筛选，然后才选中数据；最后再对这些数据进行排序，然后分页。

联表查询

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

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

多表查询示例

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

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

使用多表查询的注意点

sql">--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的所有可能。组合的个数即为两个集合中元素个数的乘积数。

在这里插入图片描述

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

  • 没有指定多个表的连接条件(或关联条件)
  • 连接条件(或关联条件)无效
sql">--sql92语法，联表查询

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

等值连接vs非等值连接

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

sql">--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:从连接的表来看，自连接和非自连接

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

sql">--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实现内/外连接

内连接

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

外连接

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

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

满外链接

sql">--满外连接: 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操作

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

sql">--左上图的结果
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操作

在这里插入图片描述

具体实现

sql">--左上图的结果
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实现内/外连接

内连接

sql">--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

外连接

sql">--练习:查询所有的员工的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;

注意：

  • Oracle对SQL92支持较好，可以使用SQL92的加号+语法去实现外连接。
  • MySQL不支持SQL92语法中使用+去完成外连接，所以MySQL里面要实现外连接，只能用jion on的方式。

http://www.niftyadmin.cn/n/5684902.html

相关文章

Visual Studio-X64汇编编写

Visual Studio-X64汇编编写

纯64位汇编： includelib ucrt.lib includelib legacy_stdio_definitions.lib includelib user32.libextern printf:proc extern MessageBoxA:proc.data szFormat db "%s",0 szHello db "HelloWorld",0 szRk db "123",0.code start p…
阅读更多...
Spring Security中自定义cors配置

Spring Security中自定义cors配置

一、为什么要自定义cors配置 在使用Spring框架时，Spring Security组件提供了简便的cors配置方案，使程序开发者可以快速的实现“同源安全策略”。关于cors，可以参数之前的一篇文章--关于Spring Security的CORS_springsecurity cors-CSDN博客 由…
阅读更多...
信息技术与商业变革：机遇与挑战

信息技术与商业变革：机遇与挑战

信息技术与商业变革：机遇与挑战 目录 引言信息技术推动商业变革的主要因素 数字化转型的加速客户需求的个性化创新技术的应用 信息技术在企业中的应用场景 供应链管理的智能化营销与客户关系管理财务与资源管理的自动化远程工作和协作 信息技术带来的挑战 网络安全…
阅读更多...
小型气象站在线分析

小型气象站在线分析

小型气象站在线分析 气象站是进行气象观测的场所。 一、气象站的主要功能 气象数据监测 气象站通过各种仪器设备对气温、气压、湿度、风向、风速、降水量等气象要素进行实时监测。这些数据对于天气预报、气候研究、灾害预警等。 例如，准确的气温和湿度数据可以帮助…
阅读更多...
./mnt/container_run_medium.sh

./mnt/container_run_medium.sh

#!/bin/bash# 清理旧的日志文件 rm -f *.log rm -f nohup.out rm -f cssd.dat# 启动 pwbox_simu 和 MediumBoxBase nohup /mnt/simutools/pwbox_simu /mnt/simutools/pw_box.conf > /dev/null 2>&1 & nohup /mnt/mediumSimu/MediumBoxBase /mnt/mediumSimu/hynn_…
阅读更多...
每一个云手机的ip是独立的吗

每一个云手机的ip是独立的吗

每一个云手机的IP地址通常是独立的。以下是关于云手机IP地址的一些详细信息： 1. 云手机的概念 云手机是将手机操作系统（如Android）虚拟化后托管在云服务器上的一种服务。用户可以通过互联网访问和控制这些云手机，进行应用使用、…
阅读更多...
水波荡漾效果+渲染顺序+简单UI绘制

水波荡漾效果+渲染顺序+简单UI绘制

创建场景及布置 创建新场景Main,在Main场景中创建一个plane物体，命名为WaterWavePla,具体数值及层级面板排布如下： 编写脚本 创建一个文件夹，用于存放脚本，命名Scripts,创建一个子文件夹Effect,存放特效相关脚本，创建…
阅读更多...
Hive数仓操作（二）

Hive数仓操作（二）

Hive 数据类型与连接 Hive 是一个用于处理大规模数据集的工具，支持多种数据类型以满足不同的需求。本文将详细介绍 Hive 的基本数据类型和集合数据类型。 一、Hive 基本数据类型 Hive 提供了多种基本数据类型，适用于不同的数据存储和处理需求&#xf…
阅读更多...
最新文章

Copyright @ 2022~2023