首页 文章详情

初级程序员的SQL拾遗-②(表操作)

SegmentFault | 79 2022-05-16 15:56 0 0 0
UniSMS (合一短信)

作者: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 -


good-icon 0
favorite-icon 0
收藏
回复数量: 0
    暂无评论~~
    Ctrl+Enter