初级程序员的SQL拾遗(增删改查)

业界 作者:SegmentFault 2022-04-27 13:37:05

作者:LYX6666

来源:SegmentFault  思否社区 


前言



虽然平时开发中经常接触MySQL,但大多数的数据库操作都是通过ORM实现的(SpringDataJPA),自己并没有从底层接触SQL。


再加上笔者非专业的身份,对于原生的SQL语句几乎完全不了解。

本文是写给自己的,关于原生SQL的一篇笔记。

学习应该掌握边界,由于这部分不是编程核心,所以不需要背代码,只需要能理解会粘贴修改即可。

小Tip:

初学者如何调试SQL代码呢?可以用Navicat或PHPMyAdmin可视化练习。

先用图形界面建几张表,再练习用SQL语句操作数据表即可。

这样就避免了前期就用纯命令行难度过高。

一、增删改查(CRUD)



在REST规范中写到:查询使用GET、增加使用POST、修改使用UPDATE、删除使用DELETE。增删改查是最常见的搬砖了,那么它们的SQL代码是什么呢?


我们先以单表为例(也就是无外键情况)。

单表操作的命令具有很强的规律。包括以下要素:

  • 指令
  • 字段
  • 目标表
  • 参数
  • 分号

指令告诉SQL要执行什么操作,是增加?还是修改?还是删除?

字段告诉SQL要操作哪几个列,比如查出所有学生的姓名和年龄。

目标表告诉SQL要操作哪张表,比如删除班级表某个班级?

查——SELECT


写法是:

SELECT 字段1,字段2,... FORM 表名 WHERE 条件;

示例:

// 从学生表查找ID为1的姓名和年龄
SELECT name, age FORM student where id=1;

(辅助记忆:)
此时的指令为SELECT
目标表使用FORM 表名来确定
参数主要是增加筛选的条件,准确查到想要的数据

分页查——LIMIT参数(对于MySQL)


写法是:

SELECT 字段1,字段2,... FORM 表名 WHERE 条件 LIMIT 条数;

示例:

// 从学生表查找10条性别是女的学生的所有字段
SELECT * FORM student where sex=1 LIMIT 10;

(辅助记忆:)
LIMIT不属于原生SQL的语法,对于不同的发行版有着不同的实现。
其中MySQL使用的是LIMIT语句,作用是只取出十条。
这种查询方法常用于Web的分页查询。


增加——INSERT


写法:

INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...);

示例:

// 向学生表插入姓名为张三、性别为女、年龄20岁的学生
INSERT INTO student (name, sex, age) VALUES (zhangsan, 1, 20);

(辅助记忆:)
对于增加操作,指令就变成了INSERT。
增加操作不仅要给出字段还要给出值,所以需要VALUES。
此时作用表的前缀有一些变化,想象一下:
执行查询时,数据从数据库到用户,所以用FORM;
但插入时,数据从用户到数据库,所以是INTO。
而由于插入会直接插到最后一条数据,所以没有WHERE参数。


修改——UPDATE


写法:

UPDATE 表名 SET 字段1=值1,字段2=值2,字段3=值3,... WHERE 条件;

示例:

// 在学生表中找到id为1的学生,然后更新姓名=张三、年龄=20、性别=男
UPDATE student SET name=zhangsan,age=20,sex=0,... WHERE id=1;

(辅助记忆:)
此时的指令为UPDATE。
由于需要修改字段,需要使用SET
修改只针对一条或几条数据,必须使用WHERE条件进行限制,否则会修改整张表的数据!


删除——DELETE


DETELE FORM 表名 WHERE 条件;

(辅助记忆:)
删除的指令为DELETE
由于数据从表中被拿出来,所以是FROM
删除只针对一条或几条数据,必须使用WHERE条件进行限制,否则会删除整张表的数据!

二、WHERE参数



WHERE在SQL定义了查询条件,但有以下几个注意的地方:


WHERE搭配的操作符


常规操作符

  • = 字段等于值
  • > 字段大于值
  • < 字段小于值
  • >= 字段大于等于值
  • <= 字段小于等于值

特殊操作符

  • LIKE 模糊查询(字段包含值)
  • IN 属于(字段的值属于给定的集合)
  • BETWEEN 区间(字段的值属于给定的区间)

模糊查询指的是,被查询的字段不需要严格等于某字符串,只需要包含字符串即可。

例如,使用LIKE '%zhang%'作为条件,'zhangsan'包括了'zhang',就会被查出来。

模糊查询又分为三种:

  • 前缀匹配 开头必须相同:zhangsan%
  • 中缀匹配 中间相同即可:%zhangsan%
  • 后缀匹配 结尾必须相同:%zhangsan


WHERE的逻辑运算


在编程语言中一定会用到逻辑用语(与、或、非),而SQL中也有与、或。

逻辑运算符连接两个条件。
与运算使用AND,表示两个条件都满足才有效。
或运算使用OR,表示任一个条件满足则有效。
实际使用场景可以多个逻辑符连续使用,并用括号定义优先级。

写法:

SELECT 字段 FROM 表名 WHERE 条件1 AND/OR 条件2;

举例:

// 在学生表取出年龄大于18岁的女生的所有字段
SELECT * FROM student WHERE sex=1 AND age > 18;

三、多表查询(连接/JOIN)



软件开发中常见的实体关系包括:


  • 一对一(0…1 : 0…1)
  • 一对多/多对一(0…1 : 0…n)
  • 多对多(0…n : 0…n)

其中,一对一、一对多、多对一使用外键关联,而多对多使用中间表关联。

对于外键关联的三种情况,在查询时,往往需要通过table1把与之关联的table2一并查出来。

这时候可以使用JOIN,把两张表拼成一张表。

关联表就像两个集合,分别是表一和表二,
他们的交集就是两张表中有外键关联的那部分记录。


班级有多个学生,每个学生属于一个班级。所以学生和班级是多对一。
如果想在查询学生的时候,一并显示他们所在班级的信息:

SELECT student.id, student.name, klass.name, klass.count  
FROM Students  
INNER JOIN Klass  
ON Klass.id=Student.klass_id;

尽管菜鸟教程已经讲的很清楚了,我们这里可以再精简一些:

  • 我们把使用FORM字段的表定义为左表
  • 把使用JOIN字段的表定义为右表

同样对于四种连接也可以概况:

  • INNER JOIN 一般连接:只查外键关联的数据,也就是取交集
  • LEFT JOIN 左连接:左表全查,右表随缘,空数据置NULL,也就是取集合A
  • RIGHT JOIN 右连接:右表全查,左表随缘,空数据置NULL,也就是取集合B
  • FULL JOIN 全连接:左右表都全查,两侧空数据都置NULL,也就是取并集。


四、总结



借助编程语言强大的ORM(实体关系映射),增删改查的操作几乎不再需要手写SQL来完成,但我们不能对ORM形成依赖而忘记了原生SQL怎么写。

这也是一门必修课,至少能看懂增删改查的基本写法,才能在一些特殊需求中知道如何变通,也能在JPA出问题时通过查看生成的SQL语句来顺利的Debug。

文章中提到的内容基本覆盖日常增删改查的需要,而对于如何定义主键、外键,如何建表、删表,如何添加、编辑字段,这些非高频操作,以后再说。

参考资料



菜鸟教程:https://www.runoob.com/sql/sql-tutorial.html



点击左下角阅读原文,到 SegmentFault 思否社区 和文章作者展开更多互动和交流,扫描下方”二维码“或在“公众号后台回复“ 入群 ”即可加入我们的技术交流群,收获更多的技术文章~

- END -


关注公众号:拾黑(shiheibook)了解更多

赞助链接:

关注数据与安全,洞悉企业级服务市场:https://www.ijiandao.com/
四季很好,只要有你,文娱排行榜:https://www.yaopaiming.com/
让资讯触达的更精准有趣:https://www.0xu.cn/

公众号 关注网络尖刀微信公众号
随时掌握互联网精彩
赞助链接