搜索

初级程序员的SQL拾遗-②（表操作）

SegmentFault

共 7534字，需浏览 16分钟

 · 2022-05-16

作者：LYX6666

来源：SegmentFault  思否社区 


前言


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


本文继 初级程序员的SQL拾遗-①（增删改查）


主要内容包括：建库、建表、字段、约束、查询等


实际开发过程中，与增删改查相比，建表、删表、约束之类的这些低频操作更加不重要，因为数据库大部分时间还是在增删改查（准确的说是查）。


所以本文的内容更不需要记，只需理解即可。


得益于强大的可视化工具Navicat（或者PHPMyAdmin），我们可以用鼠标完成建表，并转换成SQL语句。


可以说，如果会用Navicat，SQL语句就不需要记住了（因为可以用软件生成）。


一、玩转Navicat


这个软件有30试用版，也能找到和谐版，可自行搜索。


打开软件连接到MySQL服务后，左侧会显示当前MySQL的所有数据库：



新建数据库（图形化）


在左侧任意数据库上右键->新建数据库，输入信息即可。




(在此处需要科普一下，为什么要用utf8mb4？


因为MySQL内置的utf8是阉割版，用3字符表示


而真正的UTF-8是1~4可变字符，换言之MySQL的utf8并不能表示所有UTF-8字符


而utf8mb4才是真正4字符的UTF-8


这是MySQL的一个坑)


可以看到表test1已经显示出来了，


这个操作用命令行怎么实现呢？


首先需要了解一下什么叫做“查询”


查询（Query）


SQL终端输入的一组可以完成特定功能的SQL语句称为一个查询。


注意此处的Query并不是增删改查的查（SELECT），也绝不仅限于查。


可以理解成，所有SQL语句的执行过程都是查询过程。


也就是粗暴的简化成，查询 == 执行SQL语句


Query不仅包括了CRUD，还包括各种库操作、表操作。


接下来，在查询中用SQL建一个名为test2的数据库。


新建数据库 （命令行）


首先在Navicat中新建查询（也就是新建一个输入SQL语句的功能）


输入SQL代码后执行


// 建立数据库
CREATE DATABASE 数据库名 

// 示例：建立名为test2的数据库
CREATE DATABASE test2

刷新后就会出现新增的数据库。



双击新建的库后，图标变成绿色，文章后面的内容都可以通过在此数据库的Query中操作。



同理，删除数据库的语句是：


// 删除数据库
DROP DATABASE 数据库名

// 删除名为test2的数据库
DROP DATABASE test2

演示Navicat生成SQL语句


这是软件最神奇之处。


首先新建表，然后任意编辑表内字段。


编辑完成后不需要保存，此时点击SQL预览。



此时就可以发现，软件已经自动为我们生成了SQL语句：



这对于初学者学习SQL非常有帮助，有利于在大脑中快速建立某种功能和SQL代码的关联。


到此如果看明白并熟练使用Navicat，后面的内容就不用看了（不是）。


二、常见表操作


创建（CREATE）


现在我们可以拿出刚才Navicat生成的SQL代码来看看：


// 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `sex` int(1) NULL,
  `phone` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

我们可以归纳出，创建表的写法是：


// 创建数据表

CREATE TABLE `数据库名`.`数据表名`  (
    `字段1` 字段1的属性,
    `字段2` 字段2的属性,
    ......
    `字段n` 字段n的属性,
)

注意此处类似单引号的符号并不是单引号'，而是TAB上方的那个键。


助记：


  • CREATE指令适用于一切表示新增的功能，如新增数据库、数据表、索引等

  • 库的操作对象是DATABASE，而表的操作对象是TABLE

  • 新建数据表时需要写上表的字段和属性，而新建数据库不需要


用这种方法创建一个名为table1的数据表，接下来准备看如何删除它。

删除（DROP）和清空（ TRUNCATE）


SQL的写法具有很强的规律性，我们就是要把这种规律找出来以便理解和记忆。

// 删除某个数据表
DETELE TABLE 数据表名

// 举例：删除table1数据表
DETELE TABLE table1

助记：

  • 和CREATE用法相同，DROP适用于一切表示删除的功能，如删除数据库、表、索引

  • 库的操作对象是DATABASE，标的操作对象是TABLE

  • 对于数据库和数据表，删除的语句结构都是 DELETE + 操作对象 + 对象名


对于表有一种特殊的操作，可以通俗的理解成清空表，即只删除数据不删除结构：

// 情况某个数据表的数据，但不改变其结构
TRUNCATE TABLE table_name
// 举例：清空table1
TRUNCATE TABLE table1

助记：


  • 清空数据只可以对数据表使用

  • 情况语句结构和删除语句只有指令不同，其他相同


小结


此处类比数据库的建立、删除，总结了数据表的建立、删除、清空操作。


三、字段操作


前置条件：建一张名为table2的表，任意设置一些字段，类似如下：


软件自动生成了如下的SQL语句，如果读者懒得手动加字段，可以直接执行即可

获得和本文一样的环境。

// 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NULL COMMENT '姓名',
  `age` int(11) NULL COMMENT '年龄',
  `number` varchar(11) NULL COMMENT '学号',
  PRIMARY KEY (`id`)
);

所以我们又能总结出添加一个字段的写法：

// 最精简的字段写法
`字段名` 类型(长度) 是否为空约束,

// 带注释的字段写法
`字段名` 类型(长度) 是否为空约束 COMMENT '注释内容',

这里一定要睁大眼睛，字段名是TAB上方的符号，而注释是单引号！

现在已经知道字段的写法，那么字段类型共有多少种呢？

实际上很多，图片里只是一小部分。


但最常用的仍然是int、varchar、date、timestrap，

至于其他类型，需要用到的时候，先用navicat添加一个，再看看生成的SQL即可。

这就叫授人以渔（确信）。

约束


“师爷，您给翻译翻译，什么叫约束？”

“约束就是约束啊。”

约束本质是对字段取值范围的限定，

如果没有约束，字段可以取其类型中的任何值

但有时不允许字段出现NULL或者重复值等情况，这时就需要约束了。

常见约束包括：

  • NOT NULL 不允许允许字段为空

  • UNIQUE 不允许重复的值（例如学生的学号）

  • DEFAULT 默认值

  • 主键约束

  • 外键约束


非空、唯一值、默认值很好理解。

在Navicat中点击设计表，添加两个字段，分别设置非空和默认值：


自动生成了以下SQL语句：

// 自动生成的SQL语句

ALTER TABLE `test2`.`tle2` 
ADD COLUMN `test_null` varchar(255) NOT NULL AFTER `number`,
ADD COLUMN `test_default` varchar(255) NULL DEFAULT 123123123 AFTER `test_null`;

这样就可以归纳出这些约束的写法：

  • 非空约束使用NOT NULL，否则写NULL

  • 默认值使用DEFAULT 默认值

  • 唯一值Navicat没有直接设置的选项，实际上使用UNIQUE


接下来有一个问题：多种约束同时写的时候，有没有顺序要求？

来做个测试，同时设置非空和默认值，生成的语句，可以看到null在前，default在后：

// 自动生成的SQL语句

ALTER TABLE `test2`.`table2` 
ADD COLUMN `test_default` varchar(255) NOT NULL DEFAULT 123123123 AFTER `name`;

接下来调换位置，在Query中执行：

// 调换约束的顺序

ALTER TABLE `test2`.`table2` 
ADD COLUMN `test_default` varchar(255) DEFAULT 123123123 NOT NULL AFTER `name`;

是可以成功的，SQL开发者没那么傻

肯定会想到这一点。


小结


我们可以调整上文提到的一般字段的写法：

// 一般情况下字段写法，其中约束可以改变顺序
`字段名` 类型(长度) 约束1 约数2 ... COMMENT '备注内容',

接下来说很重要的主键和外键

主键（PRIMARY KEY）


对于一张表来说，主键是一个记录（一行数据）在这张表的唯一标识。

即从业务上来说，后端可以通过主键来获取一个唯一确定的对象。

SQL规定主键必须有以下要求：

  • 主键必须是唯一值（不需要写出UNIQUE，声明主键后自带UNIQUE效果）

  • 主键必须是非空（需要在字段上写出NOT NULL）


此外，在实际开发过程中，通常有以下约定：

  • 主键的列名是id

  • 主键是int类型

  • 主键是自增的（需要写出AUTO_INCREMENT）


我们又回到第一次建表时生成的SQL语句，可以归纳出主键的写法：

// 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `sex` int(1) NULL,
  `phone` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

// 归纳主键定义的写法

CREATE TABLE `数据库名`.`数据表名`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ......
  PRIMARY KEY (`id`)
);

所以，要点如下（助记）：

  • 字段名为id，需要添加非空、自增约束

  • 在所有字段的最后声明主键是id

  • 注意引号的格式


扩展：联合主键


通常来说主键只有一列，比如ID，那么这一列就是唯一的，

例如，数据表中已经有了ID为666的学生，此时插入一个ID为666的学生、或者将另一个学生的ID改为666，就会报错。这就是唯一性。

联合主键涉及到两个以上字段，只有这两个列上的数据都相同，才会报错。

比如，设置联合主键name和number，也就是说只有姓名和学号同时重复才会被判定为重复数据。

假设数据库有一个学生姓名为张三，学号123456

此时，如果插入新学生，姓名为张三，学号777777，可以插入

再插入学生，姓名为李四，学号123456，可以插入

但如果插入姓名为张三，学号为123456，数据库就会报错：主键重复。

外键


外键仅出现在多表查询时，实质上是在当前数据表中存放的关联数据表中的唯一索引，大多数情况下是主键。

简化一下，外键是在当前表中存放的，其他表的主键。

如果说，主键的目的是在单表查询中准确的根据ID获取唯一对象，

那么外键就是在多表查询中准确的获取到与之关联的唯一对象。

例如学生和班级是多对一，每个学生属于一个班级，那么就在学生表中增加一个班级ID的字段，这就是外键。

在查询学生时，就可以通过学生的班级ID字段来获取它所在的班级。

接下来是研究外键的写法，首先创建班级表”

// 创建班级
CREATE TABLE `klass`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

然后创建学生表，并加入外键klass_id指向klass 表的id字段。

先正常创建字段：


然后在外键页面，增加一个外键：


删除时和更新时有一个下拉菜单，有四个选项，分别是级联、无操作、限制、置空。

简单解释一下：

级联指的是当班级被删除时，一并删除关联的学生。

无操作会在删除班级时，学生外键不变，此时通过学生查班级会报错

限制指的是班级中有学生时，它不能被删除

置空指的是删除班级时，班级中原有的学生的klass_id字段被改为NULL

现在随意选择一个即可，查看SQL：

// 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `number` int(11) NULL,
  `sex` int(1) NULL,
  `klass_id` int(11) NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `klass_id` FOREIGN KEY (`klass_id`) REFERENCES `test2`.`klass` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

// 外键的写法
CONSTRAINT `klass_id` FOREIGN KEY (`klass_id`) REFERENCES `test2`.`klass` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

// 归纳
CONSTRAINT `外键名` FOREIGN KEY (`本表中的字段`) REFERENCES `数据库名`.

关联表名 (关联表字段) ON DELETE 删除操作模式 ON UPDATE 更新操作模式

实际开发中外键约束常用的作用是：

当使用限制模式时，某个记录被外键关联时，它不可以被删除。

例如，当班级中有学生时，这个班级是不可以被删除的。

小结


主键约束和外键约束是超高频使用的功能，理解它们的含义很重要。

四、总结


和上一篇文章不同，本文的内容几乎完全不用记，一是因为都是低频操作，二是因为已经有很智能的生成工具，我们不是DBA，不会天天和建表删表打交道。

笔者认为更重要的是讲述如何生成SQL，掌握这项操作后，无论是什么没见过语句，都可以灵活面对了。


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

- END -


浏览 8
点赞
评论
收藏
分享

手机扫一扫分享

举报
评论
图片
表情
推荐
【第129期】程序员的新宠：三款终端工具，让你告别Xshell！
概述 WindTerm：跨平台的SSH利器 首先介绍的是WindTerm，这是一款使用C语言开发的跨平台SSH客户端。它不仅完全免费，而且没有商业使用的限制。WindTerm支持SSH v2、Telnet、Raw Tcp等协议，而且性能出色，甚至超过了FinalShell和Electerm。功能
前端微服务
0
上班的时候，有一群摸鱼搭子非常重要...
上班的时候，有一群摸鱼搭子非常重要！一到上班时间，他们就从四面八方涌进群里冒泡...从八卦聊到股市、从职场聊到乌X兰局势，偶尔还会复读、相亲、battle...然后，下午6点钟准时消失不见...所以你要不要加入我们一起摸鱼？我们有北京、上海、深圳、广州、杭州、武汉、成都、南京等8个城市的摸鱼群，还有
产品经理日记
0
周四002 瑞超：同样落寞的境遇——北雪平vs埃尔夫斯堡
上赛季最终排名联赛第9的北雪平本赛季伊始表现不佳，4轮战罢他们仅以1胜1平2负的战绩排在倒数第三，这支历史上曾夺得13次联赛冠军、6次杯赛冠军老牌劲旅，正如英格兰赛场上的一众百年俱乐部，在低谷中不断探索着出路。球队主教练安德烈亚斯·阿尔姆曾是AIK索尔纳及赫根队的主教练，他于今年年初刚刚拿起球队教鞭
产品与体验
0
知乎高问：程序员有必要​知道为什么做某个功能吗？
将Python客栈设为“星标⭐”第一时间收到最新资讯前言知乎上有一个提问：程序员有必要知道为什么做某个功能吗？↓↓↓今天，我们就这个话题一起来做个讨论。不知道程序员的你，在接到产品经理提的一个需求后，是习惯马上动手开始撸代码呢？还是会先暂停一下，认真思考一会如下一些问题，比如这个需求产生的背景是什么
Python客栈
0
“渣女”怎样抵抗收入暴跌，去杭州找大厂程序员“接盘”
一位好友给我分享了一张图片，看过之后，我直呼毁三观。不知是哪位大厂员工蒙在鼓里多年，被“渣女”耍的团团转。这也难怪外面传“要嫁就嫁程序员，钱多话少死得早。”敢情成为了某些“心机女”的接盘侠。我不由得想起了曾经轰动一时的“程序员苏享茂事件”，被前妻翟欣欣索要千万跳楼自杀。我在网上详细查了这个案件资料。
Python涨薪研究所
0
日本影山优佳最新杂志照，展现充满透明感的美丽
今天的图文分享的是影山优佳的杂志写真。元日向坂46的影山优佳，登上了写真杂志《周刊FLASH》5/7和‬5/14合并号的封面。影山优佳是日本艺人、女演员、前偶像。身高155厘米。2001年‬5月‬8日‬出生‬于‬东京都。2023年7月从组合日向坂46毕业，之后作为演员活跃的影山优佳，在《周刊FLAS
python教程
0
盘点一个使用超级鹰识别验证码并自动登录的案例
点击上方“Python共享之家”，进行关注回复“资源”即可获赠Python学习资料今日鸡汤江上几人在，天涯孤棹还。大家好，我是皮皮。一、前言前几天在Python钻石交流群【静惜】问了一个Python实现识别验证码并自动登录的问题，提问截图如下：验证码的截图如下所示：二、实现过程这里大家激烈的探讨，【
IT共享之家
0
朋友，你也不想一个人孤孤单单的上班吧？
上班的时候，有一群摸鱼搭子非常重要！一到上班时间，他们就从四面八方涌进群里冒泡...从八卦聊到股市、从职场聊到乌X兰局势，偶尔还会复读、相亲、battle...然后，下午6点钟准时消失不见...所以你要不要加入我们一起摸鱼？我们有北京、上海、深圳、广州、杭州、武汉、成都、南京等8个城市的摸鱼群，还有
产品经理日记
0
美团社招一面，比预想的简单。
面试题大全：www.javacn.site面试这件事就很玄学，有时候你觉得他可能很难，但面完之后竟然出奇的顺利，问的问题你都会；有些你觉得这次面试应该很简单，但去了之后就被问懵了，所以面试这件事有很多一部分运气的成分。所以说，在没有 Offer 之前就是多准备、楞怂面，主打一个大力出奇迹。这不，逛牛
Java中文社群
0
Eiten 一个构建投资组合的好帮手
Eiten是Tradytics的一个开源工具包，它实现了各种统计和算法投资策略，如Eigen组合、最小方差组合、最大夏普比率组合和基于遗传算法的组合。Eiten允许你用自己的股票组合建立自己的投资组合。Eiten中自带的严格测试框架使你能够对你的投资组合更有自信。1.准备开始之前，你要确保Pytho
Python实用宝典
0
点赞
评论
收藏
分享

手机扫一扫分享

举报