初级程序员的SQL拾遗-②（表操作）
共 7534字，需浏览 16分钟
·
2022-05-16 15:56
作者：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
新建数据表时需要写上表的字段和属性，而新建数据库不需要
删除（DROP）和清空（ TRUNCATE）
// 删除某个数据表
DETELE TABLE 数据表名
// 举例：删除table1数据表
DETELE TABLE table1
和CREATE用法相同，DROP适用于一切表示删除的功能，如删除数据库、表、索引
库的操作对象是DATABASE，标的操作对象是TABLE
对于数据库和数据表，删除的语句结构都是 DELETE + 操作对象 + 对象名
// 情况某个数据表的数据，但不改变其结构
TRUNCATE TABLE table_name
// 举例：清空table1
TRUNCATE TABLE table1
清空数据只可以对数据表使用
情况语句结构和删除语句只有指令不同，其他相同
小结
三、字段操作
// 软件生成的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 '注释内容',
实际上很多，图片里只是一小部分。
至于其他类型，需要用到的时候，先用navicat添加一个，再看看生成的SQL即可。
这就叫授人以渔（确信）。
约束
“约束就是约束啊。”
如果没有约束，字段可以取其类型中的任何值
NOT NULL 不允许允许字段为空
UNIQUE 不允许重复的值（例如学生的学号）
DEFAULT 默认值
主键约束
外键约束
在Navicat中点击设计表，添加两个字段，分别设置非空和默认值：
// 自动生成的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
// 自动生成的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）
即从业务上来说，后端可以通过主键来获取一个唯一确定的对象。
主键必须是唯一值（不需要写出UNIQUE，声明主键后自带UNIQUE效果）
主键必须是非空（需要在字段上写出NOT NULL）
主键的列名是id
主键是int类型
主键是自增的（需要写出AUTO_INCREMENT）
// 软件生成的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为666的学生，此时插入一个ID为666的学生、或者将另一个学生的ID改为666，就会报错。这就是唯一性。
比如，设置联合主键name和number，也就是说只有姓名和学号同时重复才会被判定为重复数据。
假设数据库有一个学生姓名为张三，学号123456
此时，如果插入新学生，姓名为张三，学号777777，可以插入
再插入学生，姓名为李四，学号123456，可以插入
但如果插入姓名为张三，学号为123456，数据库就会报错：主键重复。
外键
简化一下，外键是在当前表中存放的，其他表的主键。
那么外键就是在多表查询中准确的获取到与之关联的唯一对象。
在查询学生时，就可以通过学生的班级ID字段来获取它所在的班级。
// 创建班级
CREATE TABLE `klass` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
PRIMARY KEY (`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 更新操作模式
当使用限制模式时，某个记录被外键关联时，它不可以被删除。
例如，当班级中有学生时，这个班级是不可以被删除的。
小结
四、总结
笔者认为更重要的是讲述如何生成SQL，掌握这项操作后，无论是什么没见过语句，都可以灵活面对了。
