MySQL|基础

安装

• Linux环境请点博客跳转
• Windows安装mysql详细步骤（通俗易懂，简单上手）-CSDN博客用下别人的轮子

数据库的基础操作

1
2
3
4
5
6

# 查看所有数据库
show databases;
# 创建数据库
create database database_name;
# 删除数据库
drop database database_name;

⚠️使用drop database命令时，MySQL并不会给出任何提醒、确认信息。该命令执行后，所有数据表和数据也将删除，且操作不可逆。请慎重使用。

数据表的基本操作

🌟想要操作数据表，需要使用use database_name指定操作在哪个数据库中执行。如果没有选择数据库，就会抛出No database selected的错误。

创建表

1
2
3
4
5
6
7

# 创建数据表
create table <table_name>(
    column_1 data_type,
    ...
);
# 查看数据表
show tables;

🌟约束

⭐主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。

1
2
3
4
5

# 单字段主键
字段名 数据类 primary key [默认值]
# 多字段主键
[constraint <约束名>] primary key [字段名1,字段名2...]

外键约束

外键，用来在两个表的数据之间的建立连接，可以是一列或者是多列，以确保引用数据的完整性。定义外键之后，不允许删除在另一张表中具有关联关系的行。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

优点：保证数据的完整性和一致性，级联操作方便，数据的一致性交给数据库，代码量小。

1

constraint <外键约束名> foreign key(外键名) references <主表> 主键列1 [,主键列2...]

提示

子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时，就会出现错误“ERROR 1005(HY000): Can't create table 'database.tablename'(errno: 150)”。

⚠️ 【阿里规范-强制】不得使用外键与级联，一切外键概念在应用层解决。

原因：外键约束每次做delete或者update都必须考虑外键约束，在开发测试时极为不便。

非空约束

非空约束，指字段的值不能为空。用户添加数据未指定值，数据库系统报错。

1

字段名 数据类型 not null

唯一性约束

唯一性约束，要求该列唯一，允许为空，但只能出现一个空值。

1
2
3
4

# 方式一
字段名 数据类型 unique
# 方式二
[constraint <约束名>] unique(<字段名>)

默认值约束

默认值约束，指给某列设定默认值。当插入一条记录没有这个字段值时，系统自动赋值。

1

字段名 数据类型 default 默认值

自增

在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值。可以通过为表主键添加auto_increment关键字来实现。默认的，在MySQL中auto_increment的初始值是1，每新增一条记录，字段值自动加1。一个表只能有一个字段使用auto_increment约束，且该字段必须为主键的一部分。auto_increment约束的字段可以是任何整数类型（tinyint、smallint、int、bigint等）。

1

字段名 数据类型 auto_increment

⚠️自增持久化：

在MySQL 8.0之前，自增主键auto_increment的值自增主键auto_increment的值如果大于max(primary key)+1，在MySQL重启后，会重置auto_increment=max(primary key)+1。

原理是，在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会通过下面这种方式初始化。

到了MySQL 8.0之后，将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。为了尽量减小对系统性能的影响，计数器写入到重做日志时并不会马上刷新数据库系统。

查看表结构

desc / describe语句可以查看表的字段信息，其中包括字段名、字段数据类型、是否为主键、是否有默认值等

1
2
3

desc (describe) 表名;
# 查看表详细结构；\G 参数会使结果更加直观。
show create table <表名\G>

其中，各个字段的含义分别解释如下：

• NULL：表示该列是否可以存储NULL值
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，有的话指定值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

🌟修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 修改表名
alter table <旧表名> rename [to] <新表名>;
# 修改字段的数据类型
alter table <表名> modify <字段名> <数据类型>;
# 修改字段排列 first添加在最前面，after表示某字段之后，没有配置位置，默认添加在最后。
alter table <表名> modify <字段名> <数据类型> <first | after 已有字段>;
# 修改字段名
alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
# 添加字段
alter table <表名> add <新字段名> <新数据类型> [约束条件] [first | after <已存在字段名>];
# 删除字段
alter table <表名> drop <字段名>;
# 删除表的外键约束
alter table <表名> drop foreign key <外键约束名>;
# 更换存储引擎
alter table <表名> engine=<更换后的存储引擎名称>;

删除表

1
2

drop table [if exists]表1[,表2,...];
# 存在外键约束要先解除外键，才能删除表。

数据类型

整数类型

⚠️显示宽度只用于显示，并不能限制取值范围和占用空间。例如：int(3)会占用4字节的存储空间，并且允许的最大值不会是999，而是INT整型所允许的最大值。

浮点数和定点数类型

⚠️用户指定的精度超出精度范围，则会四舍五入。

💬在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据等）使用decimal的类型比较好，另外两个浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

日期和时间类型

⚠️

• time类型的输入格式为d hh:mm:ss，最终可以转换到hh:mm:ss格式。注意，使用d hh格式输入时，小时一定要是双位，小于10的在前面加0

• date允许不严格语法：任何标点符号都可以用做日期部分之间的间隔符。

• datetime在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；

  timestamp值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。查询时，不同时区显示的时间值是不同的。

文本字符串类型

二进制字符串类型

运算符

运算符是告诉MySQL执行特定算术或逻辑操作的符号。MySQL中的运算符逻辑与C、Java等语言的运算符逻辑相通的内容，就不过多赘述了。以下比较运算符是MySQL特有的。

regexp:

• ^ 开头
• $结尾
• .任意一个
• *匹配任意个

函数

数学函数

字符串函数

日期时间函数

条件判断函数

• if(expr,v1,v2)

  如果表达式expr是true(expr <> 0 and expr <> null)，则返回值为v1；否则返回值为v2。if()的返回值为数字值或字符串值，具体情况视其所在语境而定。

  如果v1或v2中只有一个明确是NULL，则if()函数的结果类型为非null表达式的结果类型。

• ifnull(v1,v2)

  假如v1不为null，则ifnull()的返回值为v1；否则其返回值为v2。ifnull()的返回值是数字或者字符串，具体情况取决于其所在

• case

  case expr when v1 then r1 [when v2then r2]…[else rn+1] end：如果expr值等于某个vn，则返回对应位置then后面的结果；如果与所有值都不相等，则返回else后面的rn+1

系统信息函数

加密函数

其他

🌟数据的操作

查询语句

• {* | <字段列表>}包含星号通配符和字段列表，表示查询的字段。其中，字段列表至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不加逗号。

  在字段列前添加distinct去重

• FROM <表1>,<表2>...，表1和表2表示查询数据的来源，可以是单个或者多个。

• WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。

• GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。

  [HAVING <条件表达式>]用来筛选分组之后的数据展示

• [ORDER BY <字段>]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）、降序（DESC）。

• [LIMIT [<offset>,] <row count>]，该子句告诉MySQL每次显示查询出来的数据条数。

• AS 用于表、列的重命名

聚合函数

连接查询

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE `a` (
  `a_id` varchar(100) DEFAULT NULL,
  `b_id` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO `a` VALUES ('1','1','Duo'),('2','1','Tony'),('3','2','Lili'),('4','4','Seri');

CREATE TABLE `b` (
  `b_id` varchar(100) DEFAULT NULL,
  `class_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO `b` VALUES ('1','体育'),('2','美术'),('3','舞蹈');

1
2
3
4
5
6
7
8

# 1. 使用where进行连接
select a.name,b.class_name from a,b where a.b_id = b.b_id;
# 2. 使用[inner] join进行连接，等价于使用where连接
select a.name,b.class_name from a [inner] join b on a.b_id = b.b_id;
# 3. left join
select a.name,b.class_name from a left join b on a.b_id = b.b_id;
# 4. right join
select a.name,b.class_name from a right join b on a.b_id = b.b_id;

子查询

1
2
3
4
5
6
7
8
9
10
11

# 1. any(任意一个) / some(多个，包括一个) / all 所有
select a_id from a where a_id > any (select b_id from b);
select a_id from a where a_id > some (select b_id from b);
select a_id from a where a_id > all (select b_id from b);
# 2. exists / not exists [系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么exists返回的结果是false，此时外层语句将不进行查询。not exists 与之相反]
select * from a where [not] exists (select b_id from b where b_id > 12);
# 3. in 限制某字段范围在子查询查询结果中；in 也可以替换成比较运算符
select * from a where b_id in (select b_id from b where class_name != '体育');
# 4. union / union all 合并查询 [两个查询结果列数不等不能合并]
### union all 保留重复值
select * from a union [all] select * from b

插入数据

1
2

insert into table_name [(column_list)] values (value_list);
insert into table_name values (value_list1),(value_list2),...;

更新数据

1

update table_name set column_name1 = value1,... [where 条件];

删除数据

1

delete from table_name [where 条件]

添加计算列

1
2
3
4
5

create table tb1(
  a int(9),
  b int(9),
  c int(9) generated always as ((a+b)) virtual
);