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

前言

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

本文继初级程序员的SQL拾遗-①（增删改查）
主要内容包括：建库、建表、字段、约束、查询等

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

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

得益于强大的可视化工具Navicat（或者PHPMyAdmin），我们可以用鼠标完成建表，并转换成SQL语句。
可以说，如果会用Navicat，SQL语句就不需要记住了（因为可以用软件生成）。

一、玩转Navicat

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

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

新建数据库（图形化）

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

Pasted image 20220509122538.png
Pasted image 20220509122622.png

(在此处需要科普一下，为什么要用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

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

Pasted image 20220509123759.png

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

Pasted image 20220509124034.png

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

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

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

演示Navicat生成SQL语句

这是软件最神奇之处。

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

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

Pasted image 20220509124245.png

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

Pasted image 20220509124356.png

这对于初学者学习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的表，任意设置一些字段，类似如下：

Pasted image 20220509172955.png

软件自动生成了如下的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上方的符号，而注释是单引号！

现在已经知道字段的写法，那么字段类型共有多少种呢？
实际上很多，图片里只是一小部分。

Pasted image 20220509173648.png

但最常用的仍然是int、varchar、date、timestrap，
至于其他类型，需要用到的时候，先用navicat添加一个，再看看生成的SQL即可。
这就叫授人以渔（确信）。

约束

“师爷，您给翻译翻译，什么叫约束？”
“约束就是约束啊。”

约束本质是对字段取值范围的限定，
如果没有约束，字段可以取其类型中的任何值

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

常见约束包括：

NOT NULL 不允许允许字段为空
UNIQUE 不允许重复的值（例如学生的学号）
DEFAULT 默认值
主键约束
外键约束

非空、唯一值、默认值很好理解。
在Navicat中点击设计表，添加两个字段，分别设置非空和默认值：
Pasted image 20220509180457.png

自动生成了以下SQL语句：

// 自动生成的SQL语句

ALTER TABLE `test2`.`table2` 
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开发者没那么傻，肯定会想到这一点。
Pasted image 20220509182808.png

小结

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

// 一般情况下字段写法，其中约束可以改变顺序
`字段名` 类型(长度) 约束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字段。
先正常创建字段：
Pasted image 20220509201006.png
然后在外键页面，增加一个外键：
Pasted image 20220509201041.png
删除时和更新时有一个下拉菜单，有四个选项，分别是级联、无操作、限制、置空。
简单解释一下：
级联指的是当班级被删除时，一并删除关联的学生。
无操作会在删除班级时，学生外键不变，此时通过学生查班级会报错
限制指的是班级中有学生时，它不能被删除
置空指的是删除班级时，班级中原有的学生的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，掌握这项操作后，无论是什么没见过语句，都可以灵活面对了。

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

前言

一、玩转Navicat

新建数据库（图形化）

查询（Query）

新建数据库（命令行）

演示Navicat生成SQL语句

二、常见表操作

创建（CREATE）

删除（DROP）和清空（ TRUNCATE）

小结

三、字段操作

约束

小结

主键（PRIMARY KEY）

扩展：联合主键

外键

小结

四、总结

LYX6666

引用和评论

解决lodash由于依赖错误导致无法启动的问题

SQL优化还不会？速来掌握这15个小技巧

sqlmap注入语句学习，帮助你排查SQL注入漏洞

java-db 进行 SQL 统计

如何高效使用索引——10个索引优化最佳指南

关于建表字段是否该使用not null这个问题你怎么看?

2024年9月中国数据库排行榜：openGauss系多点开花，根社区优势明显

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

前言

一、玩转Navicat

新建数据库（图形化）

查询（Query）

新建数据库 （命令行）

演示Navicat生成SQL语句

二、常见表操作

创建（CREATE）

删除（DROP）和清空（ TRUNCATE）

小结

三、字段操作

约束

小结

主键（PRIMARY KEY）

扩展： 联合主键

外键

小结

四、总结

LYX6666

引用和评论

解决lodash由于依赖错误导致无法启动的问题

SQL优化还不会？速来掌握这15个小技巧

sqlmap注入语句学习，帮助你排查SQL注入漏洞

java-db 进行 SQL 统计

如何高效使用索引——10个索引优化最佳指南

关于建表字段是否该使用not null这个问题你怎么看?

2024年9月中国数据库排行榜：openGauss系多点开花，根社区优势明显

新建数据库（命令行）

扩展：联合主键