专栏首页专注 Java 基础分享基本 SQL 之增删改查(二)

基本 SQL 之增删改查(二)

上一篇文章中,我们介绍了 SQL 中最基本的 DML 语法,包括 insert 的插入数据、update 的更新数据、delete 的删除数据以及基本的查询语法,但大多比较简单不能解决我们日常项目中复杂的需求。

那么,本篇就来看一看较为复杂繁琐的查询语法,例如多表的连接查询、嵌套的子查询以及很多预定的功能函数。

连接查询

关系型数据库的一个特点就是,多张表之间存在关系,以致于我们可以连接多张表进行查询操作,所以连接查询会是关系型数据库中最常见的操作。

连接查询主要分为三种,交叉连接、内连接和外连接,我们一个个说。

1、交叉连接

交叉连接其实连接查询的第一个阶段,它简单表现为两张表的笛卡尔积形式,具体例子:

如果你没学过数学中的笛卡尔积概念,你可以这样简单的理解这里的交叉连接:

两张表的交叉连接就是一个连接合并的过程,T1 表中的每一行会分别与 T2 表的每一行做一个组合连接并单独生成一行。例如 T1 的第一行会与 T2 的第一行合并生成一行,会与 T2 的第二行再合并生成一行,T2 的第三行合并生成一行,接着再以 T1 的第二行为基础重复上述动作。

应该不难理解,我们看实现交叉连接的语法是什么样的。

交叉连接使用关键字 CROSS JOIN 进行连接,例如:

select * from table1 cross join table2

也可以按照 ANSI SQL:1989 规范中指定的,使用逗号进行交叉连接,例如:

select * from table1,table2

通过交叉连接,我们可以两张表的数据进行一个结合,但是你会发现同时也产生了很多冗余的垃圾数据行,所以我们往往也会结合 where 子句对结果集进行一个条件筛选。

例如我们有这么两张表:

Students表:

+----+--------+------+----------+
| id | name   | age  | departId |
+----+--------+------+----------+
|  1 | 小明   |   12 |        1 |
|  2 | 胖虎   |   13 |        4 |
|  3 | 小新   |   15 |        2 |
|  4 | 曹操   | 1000 |        3 |
|  5 | 王安石 | 2000 |        5 |
|  6 | 杜甫   | 2000 |        2 |
+----+--------+------+----------+

departs 表:

+----+----------------+
| id | depart         |
+----+----------------+
|  1 | 信息工程学院   |
|  2 | 文学院         |
|  3 | 化工学院       |
|  4 | 外国语学院     |
|  5 | 数学与统计学院 |
+----+----------------+

如果我们现在需要查询一个学生的学院信息,我们就需要连接两个表,而我们的交叉连接会为我们产生太多冗余数据行,我们可以使用 where 子句对笛卡尔积后的结果集进行一个条件筛选。

select * from students,departs
where students.departId = departs.id

这样就过滤了那些冗余的垃圾数据行,得到我们需要的有效数据。

+----+--------+------+----------+----+----------------+
| id | name   | age  | departId | id | depart         |
+----+--------+------+----------+----+----------------+
|  1 | 小明   |   12 |        1 |  1 | 信息工程学院   |
|  3 | 小新   |   15 |        2 |  2 | 文学院         |
|  6 | 杜甫   | 2000 |        2 |  2 | 文学院         |
|  4 | 曹操   | 1000 |        3 |  3 | 化工学院       |
|  2 | 胖虎   |   13 |        4 |  4 | 外国语学院     |
|  5 | 王安石 | 2000 |        5 |  5 | 数学与统计学院 |
+----+--------+------+----------+----+----------------+

但是我们仍然需要说一下,虽然交叉连接允许结合 where 子句过滤冗余数据,但是笛卡尔积本身就造成了很大的资源消耗,对于这种情况,内连接会有更好效率体现,同样能实现该需求。

总归一句,笛卡尔积式的交叉连接谨慎使用。

2、内连接

内连接也可以理解为条件连接,它使用关键字 INNER JOIN 连接两张表并使用 ON 筛选器筛选组合合适的数据行。基本语法如下:

select * from table1 inner join table2 on [条件]

我们同样以上述的 students 和 departs 表举例子,看这条 SQL:

select * from students
 inner join departs
 on students.departId = departs.id;

会得到同样的结果:

+----+--------+------+----------+----+----------------+
| id | name   | age  | departId | id | depart         |
+----+--------+------+----------+----+----------------+
|  1 | 小明   |   12 |        1 |  1 | 信息工程学院   |
|  3 | 小新   |   15 |        2 |  2 | 文学院         |
|  6 | 杜甫   | 2000 |        2 |  2 | 文学院         |
|  4 | 曹操   | 1000 |        3 |  3 | 化工学院       |
|  2 | 胖虎   |   13 |        4 |  4 | 外国语学院     |
|  5 | 王安石 | 2000 |        5 |  5 | 数学与统计学院 |
+----+--------+------+----------+----+----------------+

我们也说了,虽然交叉连接也实现同样的效果,但实现原理是不同的,效率也是不一样的,交叉连接通过笛卡尔积返回结果集再结合 where 子句剔除冗余数据行,而内连接的 ON 筛选器工作在笛卡尔积过程中,只有符合条件才能合并生成新的数据行。

这两者的效率是不同的,内连接的效率显然是大于等于交叉连接的,所以我们也建议了尽量使用内连接取代交叉连接的使用。

3、外连接

我们说内连接主要是两个步骤的结合,笛卡尔积加 ON 筛选器,一般情况下也是内连接使用的最为频繁。外连接其实是基于内连接的两个步骤,额外新增了另一个步骤,进一步苛刻化查询操作,我们依然以上述的学生表和学院表营造这么一个场景:

我们的学生表中保存了学生的个人信息以及所属的学院外键编号,假设其中有部分学生的学院还未分配,即为NULL。

那么我现在需要查询出所有的学生及其所属部门信息,包括那些未知学院信息的学生,请问你怎么做?

这个问题的核心点在于,我不仅要满足连接条件成功合并的数据行,还要那些未成功匹配的行,也就是说学生表的所有行都得出现。

不卖关子了,使用左外连接即可实现:

我们该一下 students 表中数据,departs 表中数据不变:

+----+------+------+----------+
| id | name | age  | departId |
+----+------+------+----------+
|  1 | 小明 |   12 |        1 |
|  2 | 胖虎 |   13 |     NULL |
|  3 | 小新 |   15 |        2 |
|  4 | 李白 |  200 |     NULL |
+----+------+------+----------+

我们执行 SQL :

select * from
 students left outer join departs
 on students.departId = departs.id;

得到结果:

+----+------+------+----------+------+--------------+
| id | name | age  | departId | id   | depart       |
+----+------+------+----------+------+--------------+
|  1 | 小明 |   12 |        1 |    1 | 信息工程学院 |
|  2 | 胖虎 |   13 |     NULL | NULL | NULL         |
|  3 | 小新 |   15 |        2 |    2 | 文学院       |
|  4 | 李白 |  200 |     NULL | NULL | NULL         |
+----+------+------+----------+------+--------------+

你看,左连接相当于以左表为基准,成功连接匹配的就列出其对应的学院信息,不能匹配的就填充为 NULL。

当然,如果你想以右表为基准,你可以使用右连接,关键词 right outer join/on。

除此之外,还有一种全外连接,这种模式下没有以谁为基准,两边表的所有行都得出现。我们举个例子:

两边的表都在看,自己哪些行成功的条件匹配了,哪些没有,没有成功匹配的行会在最后强制出现,未匹配的字段赋值为 NULL。

这就是外连接的本质,希望你理解了。

子查询

子查询,顾名思义就是嵌套的别的查询语句中的查询,因为很多时候查询不是一蹴而就的,往往是需要一个中间结果集作一个过渡的,而我们的子查询就是用于这种中间结果集过渡。

1、出现在选择列表中

这种情况并不多见,但也是一种使用场景,可以应用在 insert,update,delete 和 select 语句中,我们分别来看。

insert:

insert into students
 values(5,'yang',(select avg(id) from departs),1);

我们向 students 表中插入一条数据,age 字段的值为 departs 表 id 字段的平均值,这个例子本身没多大意义,但是我为你演示的是语法。

update:

update students set age=(select avg(id) from departs)
  where students.id = 1;

记住,子查询一定要使用小括号括起来,强调一种优先级,不然会与外查询产生冲突报错。

delete:

delete from students
 where id = (select avg(id) from departs)

select 语句的子查询类似,不再赘述了。再次强调下,这种模式下使用子查询并不多见,不要刻意为了装逼写成这样,你会被打的!

2、含有in和exists的子查询

in 这个关键字相信大家也不陌生,我们之前介绍 select 基本查询的时候介绍过,它强调的是一种集合的概念,可以视作一种逻辑运算符。

比如:

select * from students
  where id in(2,3,4,5,6)

我们换成子查询就变成:

select * from students
  where id in(select id from departs)

这时的子查询返回的是一个集合,而不再是一个常量。

exists 带来的子查询主要思路:将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留

只要子查询有返回行,即返回 true,否则返回 false。

基本语法如下:

select [select_list] from [table]
where [not] exists [子查询]

看起来挺复杂,我们举个例子你就明白了:

以我们的学生表来说,每个学生都存储了一个学院字段信息,通过这个字段的值可以具体查到学院的名字,那么假如现学生表中有大量陈腐数据,无效或为空的学院 id 对应的数据都是有些有问题的数据,现需要查出这些数据

departs 表如下:

+----+----------------+
| id | depart         |
+----+----------------+
|  1 | 信息工程学院   |
|  2 | 文学院         |
|  3 | 化工学院       |
|  4 | 外国语学院     |
|  5 | 数学与统计学院 |
+----+----------------+

students 表如下:

+----+------+------+----------+
| id | name | age  | departId |
+----+------+------+----------+
|  1 | 小明 |   12 |        1 |
|  2 | 小红 |   13 |        2 |
|  3 | 王菲 |   14 |       10 |
|  4 | 张三 |   23 |     NULL |
+----+------+------+----------+

显然,学生表中的第三第四行数据有问题,他们的学院 id 非法,我们需要使用 SQL 找到这两条数据。

select * from students
where not exists
(select * from departs where students.departId = departs.id)

执行 SQL,看看结果:

+----+------+------+----------+
| id | name | age  | departId |
+----+------+------+----------+
|  3 | 王菲 |   14 |       10 |
|  4 | 张三 |   23 |     NULL |
+----+------+------+----------+

首先外查询拿到 students 表的所有数据,where 子句会遍历每一行,执行子查询过滤筛选,如果整个 where 子句返回为 true,代表该行记录有效应该被查询出来,否则将抛弃该行,继续遍历。

关于子查询暂时句介绍到这,基本核心的内容都已经附带例子的介绍了,什么嵌套子查询不过是多套了一层而已,相信你能够理解,这里不再赘述了。

常用函数

SQL 规范中定义了很多的函数方法,它们按照应用场景不同可以划分为以下类别,聚合函数、日期时间函数、字符串函数,逻辑函数等等,我们抽取几个较为频繁使用的方法介绍下,其余的留待大家自行研究。

聚合函数:

  • AVG(column_name):求取结果集某一列的平均值,如果某条记录行该字段NULL,将不参与平均值计算
  • COUNT(column_name):统计结果集中数据记录行数,即统计有多少条数据
  • MAX(column_name):返回结果集中某列的最大值
  • SUM(colunmn_name):求和结果集某列
  • MIN(column_name):返回结果集中某列的最小值
  • 除此之外,还有一些求标准差,方差

聚合函数的一个特点是,它往往是对整个结果集进行了某种数学运算并返回一个常量数值而非集合。

日期时间函数:

由于日期函数在不同的数据实现来说,很多相同功能的函数具有不同的函数名称,我们这里仅以 MySql 来介绍这些函数,给你一个宏观印象,不同的数据库之间只不过语法差异,搜索引擎就可以解决。

  • NOW():返回当前日期时间,精确到时分秒
  • CURDATE():返回当前的日期
  • CURTIME():返回当前的时间
  • DATA(日期时间/日期表达式):该函数用于提取参数中的日期部分,参数可为一个时分秒的日期对象,或字符串表达式
  • EXTRACT(unit FROM date):取出日期时间中指定部分,unit 可取值年、月、日、时分秒等等
  • DATE_ADD(date,INTERVAL expr type):为指定参数的时间日期添加一个时间间隔
  • DATEDIFF(date1,date2):返回两个日期时间之间相隔的天数
  • DATE_FORMAT(date,format):按照指定格式输出指定的日期时间

我们详细说一下最后一个函数,这个函数用于格式化输出一个日期时间对象,format 为指定的格式,取值如下:

举个例子:

select date_format(now(),'年:%Y-月:%m-日:%d');

执行 SQL,将输出:

年:2019-月:02-日:25

虽然很奇怪的输出格式,但我要说明的是这种可定制化的日期格式输出函数。

数学函数:

  • ABS(column_name):求绝对值
  • PI():返回常量 3.141593
  • 还有一些正弦、余弦,正切,平方根等函数

除此之外,还有很多函数,例如一些操作字符串、文本等等函数,这里不一一赘述了,用到的时候尝试性的搜一搜,看有没有预定义的函数能够解决你的当下需求。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

推荐阅读

  • 「网安夜校」开课啦!多门网络安全课程开启限时优惠报名

    众志成城,共抗疫情。腾讯安全联合腾讯云大学、腾讯课堂启动「网安夜校」,为大家提供限时优惠的网络安全课程。欢迎网络安全从业者和信息安全专业学生报名参加学习,快速充电提升自我。

    腾讯安全
    安全培训腾讯云大学
  • Flink源码走读(一):Flink工程目录

    导语 | Flink已经成为未来流计算趋势,目前在很多大厂已经有了大规模的使用。最近在学习Flink源码,就想把自己学习的过程分享出来,希望能帮助到志同道合的朋友。开始阅读源码,说明读者已经对flink的基本概念有一些了解,这里就不再重复介绍Flink了。本文作为学习过程的第一章,首先对Flink的工程目录做一个解读,了解了工程下各个模块的作用,才能在遇到问题时准确定位到代码,进一步学习。

    2011aad
    大数据解决方案
  • Flink源码走读(二):Flink+Kafka实现端到端Exactly Once语义

    Flink通过Checkpoint机制实现了消息对状态影响的Exactly Once语义,即每条消息只会影响Flink内部状态有且只有一次。但无法保证输出到Sink中的数据不重复。以图一所示为例,Flink APP收到Source中的A消息,将其转化为B消息输出到Sink,APP在处理完A1后做了一次Checkpoint,假设APP在处理到A4时发生错误重启,APP将会重新从A2开始消费并处理数据,就会导致B2和B3重复输出到Sink中两次。

    2011aad
    大数据解决方案Kafka
  • kubernetes系列教程(十九)使用metric-server让HPA弹性伸缩愉快运行

    kubernetes监控指标大体可以分为两类:核心监控指标和自定义指标,核心监控指标是kubernetes内置稳定可靠监控指标,早期由heapster完成,现由metric-server实现;自定义指标用于实现核心指标的扩展,能够提供更丰富的指标支持,如应用状态指标,自定义指标需要通过Aggregator和k8s api集成,当前主流通过promethues实现。

    HappyLau谈云计算
    Kubernetes容器微服务微服务架构腾讯微服务平台 TFS
  • 三分钟入坑指北 🔜 Docsify + Serverless Framework 快速创建个人博客系统

    之前由于学摄影的关系,为了提高自己的审美,顺便锻炼下自己的英文能力,翻译了不少国外艺术类的 文章。最近一直想搭一个个人博客来存放这些内容,又懒得折腾建站,遂一直搁置。

    Aceyclee
    ServerlessHTML网站GitGitHub
  • NVM作为主存上对数据库管理系统的影响

    implications of non-volatile memory as primary storage for database management systems

    yzsDBA
    存储缓存数据库数据结构SQL
  • DevOps平台架构演进

    附最新架构图https://www.processon.com/view/5cbd897de4b0bab90962c435

    我思故我在
    DevOps 解决方案微服务架构架构设计
  • 【腾讯云AI小程序大赛】中山大学作品《小耳朵天使》

    ----------------------------------------------------------------------------------

    陈华山
    小程序 · 云开发小程序语音识别文字识别对话机器人
  • Kona JDK 在腾讯大数据领域内的实践与发展

    经常听人谈到 OpenJDK,那它到底是什么呢?相信大家都听说过 Java SE、ME、EE等规范, 通常意义上对 Open JDK 的定义指:Java SE规范的一个免费和开源参考实现。

    腾小云
    JDKJavaJVM大数据Oracle
  • 公告丨腾讯安全产品更名通知

    为了更好地为政企客户的安全保驾护航,腾讯安全即日起更新旗下身份安全、网络安全、终端安全、应用安全、数据安全、业务安全、安全管理、安全服务等八类安全产品的命名,致力于打造全栈安全产品“货架”,让客户选购安全产品/服务更加便捷,更快地找到合适的安全产品,从而对自身的安全建设“对症下药”。

    腾讯安全
    DDoS 防护应用安全 MS验证码(业务安全)应用安全(移动安全)漏洞扫描服务

扫码关注云+社区

领取腾讯云代金券