Mysql的CRUD

创建表格

创建表格时,需要提供以下属性:

  • 表名
  • 字段名(列名)
  • 字段的数据类型
    假设我们创建一张用户表,包含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的表格`u

创建字段

我们来看定义字段的语句:

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

仔细观察,我们可以看到它们的语法结构都是一样的,都符合:
字段名+数据类型+长度+是否为 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 是一种编码方式

符号

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

  • 所有的符号都是英文输入法输入的
  • ``这个符号用来过滤数据库关键字,比如我们会把它加在字段名和表名外面,注意和 VARCHAR 类型的''符号做区分
  • 定义字段的语句,语句间有,,最后一句后面没有
  • 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);

这句话的意思是,我们向指定表插入若干个字段,和它们对应的值。按照这个语法,可以把 INSERT 语句这么写:

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

这段语句的要点:

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

插入语句的简化

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

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

批量插入数据

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

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

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


查询

在Mysql中,我们使用SELECT SQL语句来查询数据

语法

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%的射手英雄。

CREATE TABLE timi_adc(
  id INT (10) NOT NULL,
  hero_name VARCHAR (20) NOT NULL,
  fever VARCHAR(10) NOT NULL,
  win_rate DOUBLE NOT NULL,
  appearance_rate DOUBLE NOT NULL,
  ban_rate DOUBLE NOT NULL,
  gmt_created datetime,
  gmt_modified datetime,
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

那么根据语法,我们应该这么写SQL:

SELECT * FROM timi_adc where  win_rate > 0.5;

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语句一般是配合分页使用的,比如我们在购物软件上,每一页都是有固定的商品数的

查询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行的数据。

查询第x-end行

为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:

SELECT
  *
FROM
  timi_adc
LIMIT
  3, -1;
和WHERE子句联合使用

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

SELECT
  *
FROM
  timi_adc
WHERE
  appearance_rate > 0.1
LIMIT
  5;

排序(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而言,是从过去到现在1

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条件。


更新/删除

语法

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小于10的数据
delete from `user` where id<20;
删除user表中的所有数据:
delete from user;

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

推荐阅读更多精彩内容