MySQL

表字段介绍

varchar

可变长字符，这个可变针对的是 MySQL 底层对该字段存储的长度是可变的

比如我们给某字段定义 varchar(100)，当我们插入数据，给该字段传入 10个字符，或者100个字符都是可以的，MySQL 会根据给定字符串来存储长度

与之对应的是 char 这是定长字符，当我们定义 char(100)，我们可以存储 10个字符，也可以存储100 个字符，但不管我们存多少， MySQL 在底层对这个字段都会给出 100 个字符长度存储

varchar 字节可存储范围为 0-65535，但实际上并不能真的存储这么长的字节数

如果字节长度不超过 255，则需要一个字节来存储长度，

如果长度超过255则额外需要两个字节来存储长度

还有一些其他的原因，导致 varchar 的最大存储长度是会小于 65535 的

int

整型，用于储存证书，四个字节，一个字节八位，所以存储的范围 -2 ^ (48 - 1) ~ 2 ^ (48 - 1) - 1，也就是 -2^31 ~ 2^31 - 1

datetime

日期时间，包括年月日时分秒的数据

text

文本字段，可以存储 65535 个字节到 64kb 的数据，记住一点，text 字段是个筐，啥都往里装。

创建表

以下是创建一张表的基本语法：

create table if not exists table_name(
 id int not null auto_increment primary key,
 name varchar(100) not null,
 num int default 0
);

在我们进入 MySQL 之后，use 一个 DATABASE 就可以运行上面的创建表的命令。

第一行有一个 if not exists，后面紧跟着将要创建的表名，表示如果不存在该表，则创建。

第二行，id int not null 是表示创建的 id 字段不允许为 null，auto_increment 参数表示是自增，primary key 则表示是主键

第三行表示 name 字段为 varchar(100) 字符串类型，且长度最大为 100个字符长度，not null 表示该字段不允许为 null

第四行表示 num 字段为整型数据，且 default 是默认值为 0

查看表信息

我们可以通过 DESC 命令来查看一张表的基本信息：

DESC table_name;

大致会显示下面的内容：

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| num   | int(11)      | YES  |     | 0       |                |
+-------+--------------+------+-----+---------+----------------+

返回的就包含了这张表所有的信息

Field 这列表示的是字段名

Type 表示的是字段类型，比如 int, varchar 等

Null 表示是否允许为 Null 值，No 表示不允许为 Null

Key 表示该字段是否有一些约束信息，比如 主键 PRI，或者 唯一键 UNI

Default 表示该字段的默认值

Extra 这一行是一些额外的信息，比如 auto_increment 自增的属性

可以看到， DESC table_name 命令这条命令之后，将我们创建表的属性都展示了出来。

创建相同表

如果是想创建一张和某表一样表结构的表，可以用下面下面的命令获得创建表的语句：

show create table table_name;

然后就可以获得创建该表的 sql 语句，把创建的表名更改为需要新建的表名运行就可以了。

重命名表

假设要将表 book 修改为 book_new，基本语法如下：

rename table book to book_new;

添加表字段

现在需要对 book_new 表添加一个字段名为 number，类型为 int 的字段，语句如下：

ALTER TABLE book_new ADD COLUMN number INT;

在上面的语句中，我把添加字段的语法的关键字都大写展示了。

我们还可以在新增字段的时候指定一个默认值：

ALTER TABLE book_new ADD COLUMN number INT DEFAULT 0;

修改字段属性

假设我们现在需要对刚刚加的 number 字段从 INT 变成 FLOAT，可以如下操作：

ALTER TABLE book_new MODIFY number FLOAT DEFAULT 0;

重命名字段名

现在需要对 book_new 这张表的 number 字段名称改为 number_float，语句如下：

ALTER TABLE book_new CHANGE number number_float FLOAT;

--需要注意，修改名称也还是需要对字段的类型进行指定，加上 DEFAULT 操作也是可以的：
ALTER TABLE book_new CHANGE number number_float FLOAT DEFAULT 1;

删除字段

假设现在我们需要删除 book_new 表中的 number_float 字段：

ALTER TABLE book_new DROP number_float;

时间格式化

对于一个时间字段，形式可能是 timestamp 时间戳格式，也可能是 '年-月-日 时:分:秒' 格式。

如果我们仅仅想查看年份、月份、或者时间，就需要对字段的数据进行格式化处理，或者在 group by 分组的时候进行统计需要对时间进行统一的处理。

MySQL 的时间格式化函数有两种，一个是 date_format，一种是 time_format。

但是 time_format 只支持对时分秒的处理，对年月日的日期不生效，而 date_format 则适用性更广，年月日时分秒都可，所以我们来介绍 date_format 的处理方法。

对 book 表添加一个时间字段，created_time：

alter table book add column created_time datetime default null;

上面的语句我们对 book 表添加了 created_time 字段，字段类型为 datetime，默认值为 null。

插入两条数据：

insert into book (book_name, author, created_time) / 
values('mysql', 'mysql_author', '2022-05-04 14:23:15'), / 
('python', 'python_author', '2022-06-11 22:13:56');

然后 MySQL 里对时间的格式化的年月日时分秒的处理是 '%Y-%m-%d %H:%i:%s'

注意： 在分钟的处理上，和 Python 的处理是不一样的。

然后我们如果要获取年份、年月日，小时的处理见下：

select date_format(created_time, '%Y'), date_format(created_time, '%Y-%m-%d'), date_format(created_time, '%H') from book; 

就可以看到相应的数据的输出：

| 2022                            | 2022-05-04                            | 14                              |
| 2022                            | 2022-06-11                            | 22                              |

插入数据

创建语句：

CREATE TABLE book(
 id int not null AUTO_INCREMENT PRIMARY KEY,
 book_name varchar(45),
 author varchar(20)
)

指定字段插入数据

以 book 表为例指定字段插入数据：

insert into book (id, book_name, author) values(1, 'mysql', 'mysql_author');

• 自增id 在创建表的时候，为 id 字段设置了自增属性，所以，插入数据的时候可以不用为其指定值：

insert into book (book_name, author) values('mysql', 'mysql_author');

• 插入多条数据 如果是想一次性插入多条数据，可以如下操作：

insert into book (book_name, author) values('mysql', 'mysql_author'), ('python', 'python_author');
--直接将插入的多条数据用逗号分隔开

• 不完全插入数据 对于一些字段，比如有自增属性的 id 字段，或者设置了 default 属性的字段，以及没有设置不允许为 null 的字段，如果插入数据的时候不指定该字段的值，那么会为其设置 null

insert into book (book_name) values('mysql');
--然后我们再去查看这张表的数据，可以看到没有被指定值的字段值为 null

不指定字段插入数据

如果我们在插入数据的时候不指定字段，也就是说，前面 id, book_name, author 这几个字段都不列出来，那么在插入的时候则需要我们填入所有的数据，示例如下：

insert into book values(100, 'mysql', 'mysql_author');
--不指定字段同样满足多条数据同时插入：

insert into book values(101, 'mysql', 'mysql_author'), (102, 'python', 'python_author');

思考:自增id的情况下,不指定字段插入可以不写id数据么？ TODO

SELECT 查看所有数据

如果是想查看 book 表所有行的所有字段的数据，我们可以使用下面的语法：

select * from book;

SELECT 是查看插入数据的语句，* 表示所有字段，即查看所有字段的数据。

在上一篇笔记中我们向 book 表中插入了数据，可以看到返回的数据。

注意：

一个是返回条数的限制，在数据量小的情况下，可以不用限制条数，但是如果数据量大且不需要查看这么多数据，可以通过 limit 来限制返回调试，或者其他的条件限制来减少数据量的返回

另一个是关于 * 这个符号，一般在知道自己需要什么字段的情况下可以直接 SELECT 相应的字段

因为获取不相关的数据毕竟会有额外的时间和内存的消耗用来返回数据，另一个原因是，通过指定字段我们可以按照顺序获取相应的字段数据

大于小于条件汇总

可以通过 WHERE 语句来进行条件过滤。

比如需要 id 值大于 2 的数据，那么就可以使用 WHERE 来操作：

SELECT book_name, author FROM book WHERE id > 2;

MySQL中的大于小于的过滤条件和其它语言的使用方法差不多，如下是对照表，摘自《MySQL必知必会》：

默认不区分大小写

我们可以往 book 表里插入一条数据：

insert into book (book_name, author) values('HTML', 'HTML_author');

接下来查询：

select * from book where book_name = 'html';

是可以查询到我们插入的大写的数据的，所以这个查询默认是不区分大小写的。

如果一定要区分大小的话，那就是如下的操作：

select * from book where binary book_name = 'html';

在字段前面加一个 binary，就可以强制查询的时候区分大小写了。

BETWEEN AND 的使用

如果我们要搜索 id 值在 2 到 9 之间的数据，就可以使用 BETWEEN AND 的语句：

select * from book where id between 2 and 9;

如果数据库中的数据是连续的话，可以看到返回的数据 id 值从2 一直到9 且包含了2和9，也就是说这是一个闭区间的取值。

NULL 的过滤

前面我们不指定字段插入数据测试字段默认值的时候，有 author 字段值为 null 的数据，如果是想要搜索 null，如下操作：

select * from book where author is null;

--如果是值不为 null，则是：
select * from book where author is not null;

逻辑语句

1. AND:与逻辑，必须同时满足
2. OR:或逻辑，满足条件之一即可
3. IN:是一个范围枚举，值在 IN 逻辑内满足的数据都可以取出来。 比如说，我们要取出 id 值为1 和 3 和100 的数据，可以用 in (1, 3, 100)。

   select * from book where id in (1, 3, 100);
   
4. NOT:非，取反逻辑。

模糊查找 LIKE

跟 like 搭配的通配符有两种，一种是 %，一种是 _

• % 是不限次数，不限字符的
• _ 是不限字符，但是仅限次数为 1 的

1. %

%这个通配符表达的含义是 不限次数、不限字符。 举个例子，如果你写的是：

WHERE name LIKE '%ython'

那么，下面这种数据你都可以匹配上：

ython
python
Python
123ython
asdasdajcaliaython

也就是说，在 ython 前包含了0个或者无数个字符的数据都属于满足要求。

如果想要在 ython 后也能有字符怎么办？在后面加上 % 即可。

也就是说想要在左右两边，哪边需要匹配字符，就往哪边加 %

如果我们尝试下面的语句：

select * from book where book_name like '%YTHO%';

可以发现小写的数据也被搜索出来，如果我们希望强制区分大小写，也就是搜索的是小写字母就返回小写字母，搜索的是大写就返回大写，可以加上 binary 参数：

select * from book where binary book_name like '%YTHON%';

2. _

_这个字符也可用于模糊搜索，但是它只能匹配一个字符，接下来我们插入几条数据：

insert into book (book_name, author) values('test', 'test_author'), ('sest', 'sest_author'), ('atest', 'atest_author');

然后这样搜索：

select * from book where book_name like '_est';

这样，book_name 为 test 和 sest 的数据就可以被搜索出来，值为 atest 的就不会，因为 _ 仅匹配一个任意字符。

多个下划线 _ 可以匹配多个任意字符

select * from book where book_name like '__st';

注意：

1. 通配符的使用会使得查找的时间变长，能用更精确的限制条件就用其他的条件
2. 不要把通配符放在搜索语句的开始处，可以把更精确的搜索条件尽量往左边放
3. 如果一定要使用通配符，_ 和% 也是可以组合使用的，在限定字符长度方面有很大帮助。

限制数据返回条数

limit

limit 限制数量，后面跟一个整数 n，表示只返回符合条件的 n 条数据

比如说返回 book 表的前 5 条数据：

select * from book limit 5;

offset

offset 是偏移量，和 limit 连用，比如说 limit 3 offset 5 表示舍弃前5条数据， 然后返回之后的 3条数据

可以理解成分页的用法，比如说每页数量为 10 条，然后我们分别获区第1、2、3、4页的数据：

select * from book limit 10 offset 0;

select * from book limit 10 offset 10;

select * from book limit 10 offset 20;

select * from book limit 10 offset 30;

注意： limit 和 offset 语句都放在查询语句的最后面。

排序

MySQL 查询语句排序的关键字是 order by。

order by 的升序、倒序

order by 的语法是 order by field_name asc/desc，asc 是升序，desc 是倒序

比如根据 book_name 字段按照升序排列返回：

select * from book order by book_name asc;

其实，asc 是 order by 的默认排序方式，所以如果是升序，asc 是可以省略的。

select * from book order by book_name;

--如果是倒序就是用 desc：
select * from book order by book_name desc;

多个字段排序

如果是多个字段分别倒序、升序，通过逗号分隔连用即可：

select * from book order by book_name desc, author asc;

按照中文排序

现在插入几条带中文的数据：

insert into book (book_name, author) values('中文测试', '作者1'), ('这是测试', '作者2'), ('测试', '作者3');

直接使用排序的逻辑会发现排序是不对的，因为 '测试' 的拼音是在 'ceshi'，但是排序却并没有在其他两条数据前面：

select * from book order by book_name;

--那么这就需要用到另一个用法：
select * from book order by convert(book_name using gbk);

其语法是 convert(field_name using gbk)，convert 函数里是字段名，然后 using gbk 表示转化成 gbk 的格式来排序。

MySQL的更新与删除

表的更新和删除操作：UPDATE 和 DELETE。

慎用！慎用！！慎用！！！

一定要对这两个语法慎用，一定要加上限制条件，除非是需要更新或者删除全表的数据。

做法一般是在 UPDATE 或者 DELETE 前先把条件放到 SELECT 后面查一遍数据，发现返回的数据是我们的目标数据，然后再把 WHERE后的条件放到 UPDATE 和DELETE后进行操作。

UPDATE

UPDATE 是更新操作，可以更新一行或多行满足条件的某个字段或多个字段的值，使用 WHERE 关键词限定范围

更新某个字段

语法模板是：

update 表名 set 字段名=值 WHERE id = 1;

比如我们想要更新 id = 2 的数据，将 book_name 的值改为 Python：

update book set book_name = 'Python' where id = 2;

更新多个字段

如果同时更新多个字段的值，语法如下：

update 表名 set 字段1=值1, 字段2=值2 where id = 1;

--比如 book_name 和 author 两个字段的值：
update book set book_name = 'Python', author = 'Python_author' where id = 1;

注意： 如果 update 语句不添加 WHERE 条件限制，更新的是全表。

DELETE

DELETE 删除一行或多行数据，根据 WHERE 条件来筛选数据。

语法如下：

delete from 表名 where id > 4;

--如果需要删除 book 表 id > 4 的数据：
delete from book where id > 4;

如果不加 WHERE 条件，删除的就是全表数据。