十分钟速通 MySQL —— CRUD

表格的结构

在之前的课程中我们已经学习了关系型数据库的表格，我们再来回顾-下表格由哪些元素构成

• 表由表名、行、列、列名构成
• 表名是表的名称
• 列名表示列的名字，列名不可以重复
• 表格实质上是一个二维数组，行和列都是从0开始数的(数组的特性)

数据库和表

在上篇中，我们在我们的云服务上创建了一个数据库(database)，在云服务器上我们可以创建很多DB，它们共用一个云服务，我们配置链接的是其中一个数据库。之后我们涉及的所有表都在我们配置的数据库下：

表名

表名就是表格名，在MySQL中我们一般使用 英文小写字母 来约定表名，比如用户表我们命名为 user

一般来说英文名可以使用翻译软件，尽量不要使用拼音

如果遇到多个单词，则用_分隔，比如支付记录表格，我们可以命名为 pay_record ,这种命名方式我们称为驼峰命名，一般在数据库中我们将表名、字段名都小写，将数据库操作语句都大写,

字段

在数据库表中，每一列都是一个字段，第一行是字段名，下面都是字段的值，我们读取数据时，从第二行开始读取，对应的数组下标是0：

字段必须是唯一的，即不能出现同名的字段。字段用来约定行的值或者和其他的表格产生联系。字段的值可以为 NULL。

关于 NULL我们下面会涉及，在现在规范的数据库设计中，允许为NULL的情况需要尽量避免，即保证所有的数据都有值

主键

每一张数据库表都可以有一个主键(primary key)，主键最大的作用就是用来标识数据，数据就像我们人，名字可能会重复，生日可能会一样，但是身份证号是肯定每个人都不一样的，身份证号就是社会体系辨别我们每个人谁是谁的主键。理论上表格可以没有主键，但是现在的规范表结构设计中，必须要有主键，这就像一个人他可以在深山野林生孩子，不给孩子登记身份证号，他的孩子就会是一个黑户各种权利都会受到限制。

主键拥有以下的特点：

• 主键是一个特殊字段
• 表格可以没有主键，但是最多只能拥有一个主键
• 主键的值不能为 NULL，必须有对应的值
• 主键的值必须是绝对唯一的，即不能出现两个相同的主键值，比如名字就不能作为主键，因为会出现重名的情况。
• 我们一般使用主键和其他表进行关联

主键也可以用多个字段联合组成一个主键，但用的很少，初级学习阶段只需要了解

SQL常用数据类型

字段的值拥有数据类型，在MySQL中我们常用的数据类型有

类型	含义
VARCHAR	可变的长字符串，可以类比于java的String
INT	整型，int
DOUBLE	浮点型，double
DATETIME	时间类型，长度为0，格式为YYYY-MM-DD HH:MM:SS
BIGINT	长整型，可以类比java的long

数据库中常用的类型就是以上这些，其他类型我们可以在之后的学习中不断的接触。NULL表示这个字段的值没有填写，可以对值为NULL的字段重新赋值

数据

我们通常把表格中的一行称为一条数据，大家都学习过HashMap，想必对key-value这种键-值对的储存形式并不陌生，数据库的一条数据就像是value，而主键就像是它的key，我们通过主键来快速查找数据，也可以通过其他字段来操作数据。

CRUD

在计算机程序语言中，创建(Create)、读取(Read)、更新(Update)、删除(Delete)，也就是CRUD; 这是一连串常见的动作行为，而其行为通常是为了针对某个特定资源所作出的举动(例如:创建数据、读取数据等)。这四个行为最常见的用途能够在使用SQL数据库与网站的API端口时发现。这个词语(CRUD)最早被记载于James Martin所撰写的Managing the Data-base Environment书中。
在SQL中，我们拥有对应的专业术语

英文	中文	SQL	HTTP
CREATE	创建	INSERT	POST
READ	读取	SELECT	GET
UPDATE	更新	UPDATE	POST
DELETE	删除	DELETE	DELETE

创建表格

在创建表格时我们需要提供以下的属性

• 表名
• 字段名
• 字段的数据类型

小王公司需要记录用户的信息，要创建一张用户表，用户包含用户id，昵称、手机号这三个字段，同时我们需要记录创建时间和修改时间。我们来看一下创建表格的代码

CREATE TABLE `user`(
`id` INT(10)NOT NULL,
`mobile` VARCHAR(11) NOT NULL,
`nickname` VARCHAR(40) NOT NULL,
`gmt_created` datetime ,
`gmt_modified` datetime NOT NULL,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

看起来很复杂是吧，别怕，我们一点点拆解它

创建表格

首先是第一行：

CREATE TABLE `user`()

这一行的意思是，我们创建一张叫做 user 的表格

创建字段

我们来看定义字段的语句

`id` INT(10)NOT NULL,
`mobile` VARCHAR(11) NOT NULL,
`nickname` VARCHAR(40) NOT NULL,
`gmt_created` datetime ,
`gmt_modified` datetime NOT NULL,

仔细观察，我们可以看到它们的语法结构都是一样的，都符合:

**`字段名`+数据类型(长度)+值+,**我们以第一句为例:

• id 是字段名，我们用``这个符号把它包含起来
• INT 是数据类型，表示id 这个字段是 INT 值
• (10)表示id 最长为 10 位
• datetime 类型没有长度，所以不用定义长度
• NOT NULL 表示这个字段不能为空，也就是必须要输入值，否则就会报错，如果这个值可以为不输入值，那么不加 NOT NULL

一般来说字段的长度足够用就可以，同时要注意值域，比如INT的最大值为 2147483647，长度为 10 位，所以上面的语句中，id最大只能这么大，如果超过这个数，我们可以使用 BIGINT

约定主键

PRIMARY KEY ( `id` )

这句话的意思是，这张表格的主键(primary key)是 id 这个字段

主键拥有以下特点

• 主键必须是已经约定的字段
• 主键不能为空
• 主键的值不能重复
• 主键最大的作用是标识，所以它最好是由计算机生成，人工不干预主键生成后的值

有时候我们会把 INT 类型的主键语句改为

`id` INT UNSIGNED AUTO_INCREMENT

这句话的意思是，id 会从1开始自增，第二个为 2，第三个为 3，依此类推。

UNSIGNED是指无符号的，也就是说排除了负数，但是数据库默认是从1开始累计，所以这个关键词可以省略。

在企业级开发中，往往会使用BIGINT作为主键，因为我们知道INT的值域只有2147483647这么大，也就是20亿多，有些公司用户数达到百万甚至千万，那么记录用户行为的数据，比如说支付宝账单，就很轻松会突破INT的值域，因此使用BIGINT会避免数据溢出的问题

设置储存引擎和编码方式

ENGINE=InnoDB DEFAULT CHARSET=utf8

这句话的意思是储存引擎为 InnoDB，默认编码方式为 utf-8。

InnoDB 是 MySQL 的默认储存引擎，utf-8 是一种编码方式

这句话死记硬背就可以啦

符号

我们已经学习了创建表格的语法，在这段语句中出现了很多符号，我们来总结一下:

• 所有的符号都是英文输入法输入的
• ``这个符号叫作 反引号，是用来过滤数据库关键字，比如我们会把它加在字段名和表名外面，避免字段名和表名跟 MySQL 系统的关键字冲突。

注意和 VARCHAR 类型的单引号"符号做区分

反引号 ` 是键盘 ESC 键下面的按键哦

• 定义字段的语句，语句间有,，最后一句后面没有
• SQL语句以;结尾

关键词和保留字

在数据中有很多关键词和保留字，就像 Java 中的 public class 这些单词一样，关键词和保留字不需要特别去记忆，用的多了就会逐渐熟悉。我们只需要注意，遇到关键字和保留字的时候，用``这个符号去过滤掉它，这样它就会被视为普通单词。

那么问题来了，我们如何知道哪些词是关键词或者保留字呢?

在我们的编译器里，当你看到单词变成了这个颜色，说明它是关键：

删除表格

drop table `table_name`;

或者

DROP TABLE IF EXISTS `table_name`;

table_name 是指你要删除的表格名 IF EXISTS 的意思就是如果存在 因为删除表格是不可逆的，所以大家在删除表格时，务必慎
重

插入语句

在 MySQL 中，我们使用 INSERT INTO SQL 语句来插入数据

语法

我们来看一下语法：

INSERT INTO table_name(field1,field2,...fieldN)
VALUES
(value1,value2,...valueN);

这句话的意思是，我们向指定表插入若干个字段，和它们对应的值还记得我们之前那张 user 表么，按照这个语法，我们可以把 INSERT语句这么写

INSERT INTO
  `user` (`id`, `mobile`, `nickname`, `gmt_created`)
VALUES
  (1, '13426069530', '叶冰', now());

我们来看一下这段语句的要点：

• user是表名
• id，mobile等是字段名
• id的值是数字，可以直接写，比如1
• mobile的值是VARCHAR类型，所以要用``包含
• gmt_created是datetime类型，我们一般使用now()这个函数来获取服务器当前时间，并将它作为值插入

插入语句的简化

1. 如果主键设置为自增，则可以不插入主键和对应的数据
2. 如果插入的是所有的字段，可以省略字段名，直接插入值,但是类型必须全部一致,比如:

INSERT INTO table_name
VALUES
(value1,value2,...valueN);

这两种情况不可以同时简化。

批量插入数据

如果要一次性对一张表格插入大量数据，我们可以使用下面的语法

INSERT INTO table_name
VALUES
(value1,value2,...valueN),
(value1,value2,...valueN);

插入一条数据对应表格中的一行，当规定为 NOT NULL 的字段没有给到值时，插入语句会报错。

查询(SELECT)

在MySQL中，我们使用SELECTSQL语句来查询数据

语法

我们来看一下语法:

SELECT field1,field2,.... FROM table_name;

这句话的意思是，我们从指定表中查询指定列的信息。参考我们的user表，语句可以这么写：

SELECT
  id,
  hero_name
FROM
  timi_adc;

如果我们要查询所有的字段，那么SQL语句可以这么写：

SELECT * FROM timi_adc;

这里用*表示所有的字段

WHERE子句

在实际工作的查询中，我们很少直接限定字段查找数据，更多的时候，我们希望查询符合某一种条件的数据，这时候我们就需要使用到限定语句。 在MySQL中我们使用WHERE语句来限定条件，它的作用类似程序语言中的if语句。我们可以将WHERE子句加入到查询语句，来加强应用场景。

语法

我们来看一下语法：

SELECT * FROM table_name WHERE condition;

condition 是指条件，它和if语句一样，可以做简单的逻辑判断，我们查询到的数据是true

应用

在面，我们创建了timi_adc表，现在我们想查询胜率超过50%的射手英雄。

那么根据语法，我们应该这么写SQL：

SELECT
  *
FROM
  timi_adc
where
  win_rate > 0.5;

思考如果把 * 换成 win_rate，查询得到的结果会发生什么变化，have a try！

Limit子句

在实际工作的数据查询中，我们有时候需要返回指定行，比如我想查询符合某个条件的前十个数据，这时候就需要使用LIMIT子句来加强查询功能。

语法

SELECT * FROM table_name LIMIT parameter;

parameter是LIMIT语句的参数，我们来看以下几种情况：

查询第x-y行

SELECT
  *
FROM
  timi_adc
LIMIT
  5, 6;

这句SQL的意思是查询timi_adc表的第6-11行，第一个参数5表示从第6行开始查，第二个参数6，表示一共查询6条记录。

之前说过数据库的表格类似数组，是从第0行开始数的，所以5表示第6行。

LIMIT语句一般是配合分页使用的，比如我们在购物软件上，每一页都是有固定的商品数的，在本门课程我们主要是练习LIMIT语句的使用，后续的课程中会涉及到实战中的使用。

查询0-x行

SELECT
  *
FROM
  timi_adc
LIMIT
  5;

这句SQL的意思是查询user表的第0-5行，也就是说这句话等价于：

SELECT * FROM timi_adc LIMIT 0,5;

查询第x行

SELECT
  *
FROM
  timi_adc
LIMIT
  4, 1;

这句SQL的意思是查询timi_adc表第5行的数据。

和WHERE子句联合使用 在实际的应用场景中，LIMIT子句往往和WHERE等其他子句联合使用，LIMIT语句会放在WHERE语句后面，比如说我要查下timi_adc表中登场率大于10%的前五条数据，那么SQL应该这么写：

SELECT
  *
FROM
  timi_adc
WHERE
  appearance_rate > 0.1
LIMIT
  5;

大家可以尝试一下如果把LIMIT子句放在WHERE子句前，执行会发生什么

排序（ORDER BY子句）

在实际的应用场景中，我们会需要对查询结果进行排序，这时候我们就需要使用 ORDER BY 子句。

语法

我们来看一下语法：

SELECT * FROM table_name ORDER BY field_name;

终于到了拿胜率说话的时候了，老王想知道所有射手的胜率排序，那么我们可以这么写查询语句：

SELECT
  *
FROM
  timi_adc
ORDER BY
  win_rate;

排序默认按照升序排序，对于int,double而言，是从小到大，对于varchar而言，是从字母A到Z，对于datetime而言，是从过去到现在

DESC关键词

ORDER BY 语句的排序默认是正序排序，关键词为ASC（一般不写），我们可以通过加上关键词DESC，使得排序变为逆序，比如上面的演示中，我们的想查看胜率从高到低排序：

SELECT
  *
FROM
  timi_adc
ORDER BY
  win_rate DESC;

和其他子句连用

和LIMIT子句一样，ORDER BY子句一般和其他语句联合使用，比如我想查询胜率最高的三个射手，那么我们的查询语句可以这么写：

SELECT
  *
FROM
  timi_adc
ORDER BY
  win_rate DESC
LIMIT
  3;

一般我们会先排序，再加LIMIT条件。

更新/删除

我们在登录形形色色的网站时，往往需要我们的密码，而忘记密码是一个基本操作，大家基本都有找回过密码的行为，找回密码时往往会要求我们创建一个新的密码，这个创建新密码并且生效的过程，在SQL语句中，就是UPDATE语句。

语法

我们来看一下UPDATE语句的语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE语句我们必须加入WHERE限定条件，否则的话UPDATE语句就会对整列起作用。

比如我们要将timi_adc表格中的艾琳这个英雄的ban率改为1%，那么我们应该这么写SQL语句：

UPDATE
  timi_adc
SET
  ban_rate = 0.01
WHERE
  hero_name = '艾琳';

一定要注意加上WHERE限定语句，否则所有英雄的ban率都会被改成0.01 UPDATE语句不关心原有的值

删除语句（DELETE）

在数据库中的使用中，有时候我们需要删除一些数据，这时候就需要使用DELETE语句。 我们来看一下DELETE语句的语法：

DELETE FROM table_name [WHERE Clause]

由于删除语句是不可恢复的，所以我们务必要增加WHERE语句，否则将会删除整张表格的数据

我们来看一下DELETE语句的几种应用场景：

删除user表中id为4的行：

delete from user where id=4;

删除user表中所有id小于20的数据：

delete from `user` where id<20;

删除user表中的所有数据：

delete from user;

DELETE 语句只删除表中的数据，如果要删除表格，我们用之前学过的 DROP TABLE + 表名的语句