MySQL数据库（超详细万字长文，建议收藏）

一、MySQL简介

MySQL是一个开源免费的关系型数据库管理系统，由瑞典MySQL  AB公司开发，属于Oracle旗下产品。MySQL是流行的关系型数据库之一。

二、MySQL的特点

1. 开源性：MySQL是一个开源的关系型数据库。

2. 高性能：MySQL采用了多种优化技术，比如索引，查询缓存和高效的存储引擎，可以快速的检索数据和处理。适用于处理大量数据和高并发请求，具有高新能、高并发、高可用的特点。

3. 简单易用：MySQL易于安装和配置，并且有很好的兼容性，支持多种操作系统，比如Windows、Linux、macOS等。

4. 安全性：MySQL提供了多层次的安全措施，包括密码保护，基于角色的访问控制，SSL连接加密等，以保护数据免受未授权访问和安全威胁。

5. 可扩展性：MySQL具有良好的可扩展性，可以通过主次复制，分片和分区等技术来实现数据的扩展，从而提高系统的高可用性和高性能。

6. 跨平台性：MySQL可以在不同平台上运行，比如Windows、Linux、macOS等。

7. 支持多种存储引擎：MySQL支持多种存储引擎，包括InnoDB、MyISAM等。每种引擎都有特定的优势和用途。

三、SQL简介

1. SQL的介绍

SQL全称Structured Query Language，也就是结构化查询语言，是一种用于处理关系型数据库中数据的高级非编程化语言。用户可以通过编写SQL语句来查询、更新和管理数据库中的数据。SQL的核心部分包括数据定义、数据操作和数据控制等功能，通过这些功能用户可以定义数据库结构，操作数据，以及控制对数据的权限。

2. SQL的分类

数据定义语言（DDL）：用于创建数据和修改数据库对象（如表和索引）的结构。

数据操纵语言（DML）：包括INSERT、UPDATE、DELETE等语句，用于对数据库中的数据进行新增、修改、删除等操作。

数据控制语言（DCL）：用于控制用户对数据的访问权限，例如通过GRANT和REVOKE语句

数据查询语言（DQL）：用于对数据进行查询操作，例如通过SELECT语句

3. SQL的特点

1.高度非过程化：用户只需要描述想完成的操作，而无需具体说明操作的细节，比如数据的存储路径等。

2.语言简洁：易学易用：SQL的语法结构相对来说比较简单，易于学习和使用。

3.广泛的应用范围：几乎所有的关系型数据库管理系统都支持SQL，比如Server、MySQL、PostgreSql等。

四、 数据库的三大范式

1. 第一范式（1NF）

每一列属性（字段）不可分割，字段必须保证原子性。两列的属性值相近或者一样时，尽量合并到一列或者分表，以确保数据不冗余。

2. 第二范式（2NF）

每一行的数据只能与其中一行有关即主键一行数据只能做一件事情或者表达一个意思，只要数据出现重复，就要进行表的拆分。

3. 第三范式（3NF）

数据不能存在传递关系，每个属性都跟主键有直接关联而不是间接关联。

五、数据库的数据类型

MySQL数据库支持多种数据类型，主要分为：数值类型、日期和时间类型、字符串类型、二进制类型。

1. 数值类型

整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。这些类型方别占用不用的存储空间，提供不同的取值范围。

 浮点数类型和定点数类型：在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT）和双精度浮点数类型（DOUBLE）。而定点数类型只有一种即DECIMAL类型。

DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插入数据库后显示的结果为6.52

2. 日期和时间类型

MySQL提供的表示时间和日期的数据类型有：YEAR、DATE、TIME、DATETIME和TIMESTAMP。

2.1 YEAR类型

YEAR类型用于表示年份，在MySQL中，可以使用三种格式指定YEAR类型的值。

1.使用4为字符串或数字表示，范围是"1901"~"2155"或1901~2155。输入"2024"或2024插入到数据库中的值都是2024

2.使用两位字符表示，范围是"00"~"69"。其中，"00"~"69"范围的值会转换为2000~2069范围的YEAR值，"70"~"99"范围的值会被转换为1970~1999范围的YEAR值。例如：当取值为"01"到"69"时，表示2001到2069。

3.使用两位数字表示，范围是1~99。其中，1~99范围的值会被转换为2001~2099范围的YEAR值，66~99范围的值会被转换为1966~1999范围的YEAR值。例如：输入19插入到数据库中的值为2019。

注意：当使用YEAR类型时，一定要区别"0"和0.因为字符串格式的"0"表示的YEAR值是2000年而数字格式的0表示的YEAR的值是0000年。从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是"YYYY",没有必要写成YEAR(4)，从MySQL8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

2.2 TIME类型

TIME类型用于表示时间值，不包含日期部分，它是显示形式一般为HH:MM:SS格式，其中，HH表示小时，MM表示分钟，SS表示秒数。在MySQL中可以使用三种格式类指定TIME类型的值。

1. 以"D HH:MM:SS"、"HH:MM:SS"、"HH:MM"、"D HH:MM"、"D HH"或者"SS"格式表示。其中，D表示天，可取0—34之间的值。在插入数据时如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，小时的值计算格式为(DX24+HH)。如果使用带有冒号不带D格式的字符串插入TIME时，表示当天时间，例如，输入"2 11:30:50"插入数据库中的日期为59:30:50。不带冒号输入12:12数据库中表示为12:12:00。

2. 以"HHMMSS"字符串格式或者HHMMSS数字格式表示。例如：输入"121212"或121212插入数据库中的日期为12:12:12

3. 使用CURRENT_TIME或NOW()函数就会插入当前系统时间。

2.3 DATE类型

DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD，其中YYYY表示年份，MM表示月份，DD表示日期。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

1.以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符日期时，其取值范围是1000-01-01~9999-12-31.YYYYMMDD格式会被转换成YYYY-MM-DD格式。

2.以YY-MM-DD格式或者YYMMDD格式表示的字符串日期时，此格式周工两位数值或者字符串满足YEAR类型的格式条件为：当年份取值为00~69时，会被转换成2000~2069；当年份取值为70~99时，会被转换成1970~1999。

3.使用CURRENT_DATE()或者NOW()函数，就会插入当前系统时间

2.4 DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为"YYYY-MM-DD HH:MM:SS"，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒数。在MySQL中，可以使用4种格式来指定DATETIME类型的值。

1. 以"YYYY-MM-DD HH:MM:SS"或者"YYYYMMDDHHMMSS"字符串格式表示的日期和时间，取值范围是"1000-01-01 00:00:00"~"9999-12-31 23:59:59"。例如：输入"2024-01-01 11:11:11"或"2024-01-01 11:11:11"插入数据库中的DAETIME值都为2024-01-01 11:11:11。

2. 以"YYYY-MM-DD HH:MM:SS"或者"YYYYMMDDHHMMSS"字符串格式表示的日期和时间，取值范围是"00"~"99"，两位数的年份规则符合YEAR类型的规则，00~69表示2000到2069，70~99表示1970到1999。

3.以YYYYMMDDHHMMSS或者YYMMDDHHMMSS的格式表示日期时间。比如插入20180505083055或者180505083055都表示插入数据库中的值都是2018-05-05 08:30:55。

4.使用CURRENT_TIME或NOW()函数就会插入当前系统时间。

3.字符串类型

在MySQL中通常用CHAR和VARCHAR来定义字符串。

CHAR与VARCHAR的区别：

CHAR是存储的是定长字符串，VARCHAR存储的是可变长度的字符串

当数据为CHAR(M)类型时，不管插入值的长度实际是多少它所占用的存储空间都是M字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1

文本类型：主要用于表示大文本数据，比如，文章内容，评论，详情等。

4.二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。】

六、数据库与数据表的基本操作

1. 数据库的基本操作

1.1 创建数据库

MySQL中创建数据库的基本SQL语法格式为：

CREATE DATABASE  database_name;

"database_name"为要创建的数据库的名称，该名称不能与已经存在的数据库重名。

1.2 查看数据库中的定义

数据库创建好之后，可以使用SHOW CREATE DATEBASE声明查看数据库的定义。比如要查看创建好的数据库test_db中的定义，可以使用以下语法：

SHOW CREATE DATEBASE test_db;

数据库创建成功后就会显示数据库的创建信息。

1.3 查看已存在的数据库

查看当前所有存在的数据库语法：

SHOW DATEBASES；

1.4 选中指定的数据库

USE DATABASE;--database是数据库名称

1.5 修改数据库中的字符集

ALTER DATEBASE database_name character set gbk;

1.6 删除数据库

删除数据库是将已经存在的数据库将磁盘空间上清除，删除的数据库中所有的数据也会被清除。删除数据库的语法为：

DROP DATABASE database_name;--database_name是数据库名称

2. 数据表的基本操作

2.1 创建数据表

在创建完数据库后，接下来就是要创建数据表。所谓的创建数据表，指的是在已经创建好的数据库中建立新表，创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性约束的过程。

2.2 创建表的语法

数据表属于数据库，在创建数据表之前，应使用USE <数据库名称>指定数据库。没有则报错“No database selected”的错误。

创建数据表的语法为create table，语法如下：

CREATE TABLE <表名称> (
    字段名称1，数据类型[列的约束条件] [默认值],
    字段名称2，数据类型[列的约束条件] [默认值],
    ......
    [表的约束条件]
);

CREATE TABLE user(
    user_id int(10) not null primary key auto_increment,--主键ID，可以不设置非空，主键默认非空
    username varchar(20) not null,--用户名 非空
    password varchar(20) not null --密码 非空
);

在建表之前我们可以先判断这个表是否存在，如果存在，就可以使用如下语法删除掉。

DROP TABLE IF EXISTS user;--user代表表名称

注意：需要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如：DORP、ALTER、INSERT等。

2.3 查看数据表

查看当前数据库中所有表的语法：

SHOW TABLES;

查看表的基本信息语法：

SHOW CREATE TABLE user;--user为表名称

查看表的字段信息语法：

DESC user;

DESC是DESCRIBE的简写都可以查看表的字段信息，其中包括字段名、字段数据类型、是否为主键、是否有默认值。

2.4 修改数据表

在表结构创建错误时可以使用 alter table修改数据表。

2.4.1 修改表名语法：

alter table <旧表名> rename to <新表名>;

alter table user rename to users;

2.4.2 修改字段的数据类型

alter table <表名> modify <字段名> <数据类型>;

alter table users modify gender char;

注意：“表名”指要修改的数据类型的字段所在表的名称，“字段名”指需要修改的字段，“数据类型”指修改后的新数据类型。

2.4.3 修改字段名

alter table <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

alter table users change username usernames varchar(20);

注意：“旧字段名”指修改前的字段名；“新字段名”指修改后的字段名；“新数据类型”指修改后的数据类型，如果不需要修改字段的数据类型，将新数据类型设置成与原来一样即可，但数据类型不能为空。change 也可以只修改数据类型，是将SQL语句中的“新字段名”和“旧字段名”设置成相同的名称，只改变“数据类型”。

2.4.4 添加字段

alter table <表名> ADD <新字段名> <数据类型> [约束条件] FIEST | AFTER 已存在的字段名];

alter table users add address varchar(50);

 注意：first为可选参数，其作用是将新添加的字段设置为表的第一个字段；after为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”后面。如果SQL语句中没有first和after这两个参数，则默认将新添加的字段设置为数据表的最后一列。

2.4.5 删除字段

alter table <表名> drop <字段名>;

alter table users drop gender;

2.4.6 修改字段的排列位置

alter table <表名> modify <字段1> <数据类型> first|after <字段2>;

alter table users modify gender char after password;

注意：字段1为需要修改的排列位置的字段，数据类型是字段1的数据类型。first为可选参数，指将字段1修改字段的排列位置为第一个字段，after 字段2指将字段1插入到字段2后面。

2.4.7 修改表的存储引擎

MySQL中的存储引擎有很多主要有InnoDB、MyISAM、MEMORY(HEAP)、BDB等。

可以使用 SHOW ENGINE 来查看系统支持1存储引擎。

更改表的存储引擎的语法：

alter table <表名> engine = <更改后的存储引擎>

alter table users engine = <MyISAM>

2.4.8 删除数据表

drop table <表名>;

drop table users;

七、数据表的约束

为了防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则。这些规则被称为表的约束。

约束条件用于针对表中的字段进行限制从而保证数据表中的数据的正确性和唯一性。

1. 主键约束

主键：PRIMARY KEY用与唯一的标识该字段里面的数据，不能重复，不能为空，一张表中只能有一个主键。

主键约束的语法：

<字段名> <数据类型> primary key;

第一种方式设置主键约束：

create table users (
users_id int primary key,
users_name varchar(20)
);

第二种方式设置主键约束：

create table users (
user_id int,
user_name varchar(20),
password varchar(20),
primary key(user_id)
);

2. 非空约束

非空约束：NOT NULL指的是字段的值不能为空

非空约束语法：

<字段> <数据类型> NOT NULL;

create table users (
    user_name varchar(20) not null,
);

3. 默认值约束

默认值约束：DEFAULT，用于给数据表中的字段设置默认值。即当在表中插入一条新纪录时若没给给字段赋值，那么，数据库会自动为这个字段插入默认值。

默认主键约束的语法：

<字段名> <数据类型> default 默认值;

create table users (
    user_id int,
    user_name varchar(20),
    password varchar(20) default '123456'
);

4. 唯一性约束

唯一性约束：UNIQUE 用于保证数据表中字段的唯一性，即表中字段的值不能重复，

唯一性约束语法：

<字段名> <数据类型> unique;

create table users (
    user_id int,
    user_name varchar(20),
    identity_card varchar(18) unique
);

5. 外键约束

外键约束：FOREIGN KEY，用于维护表之间关系的完整性，确保只能插入或更新符合关联条件的数据。外键约束可以在创建表时定义，或者在已存在的表上使用 ALTER TABLE 语句添加。

外键约束语法：

-- 在创建数据表时语法如下：
CONSTRAINT <外键名> FOREIGN KEY <从表外键字段> REFERENCES <主表> (关联列)
 
--在创建数据表时语法：
CREATE TABLE <表名> (
    <列名> <数据类型>,
    ...
    FOREIGN KEY (外键列) REFERENCES <主表> (关联列)
);
 
 
-- 在数据表创建好后的语法如下：
ALTER TABLE <从表名> ADD CONSTRAINT <外键名> FOREIGN KEY <从表外键字段> REEFRENCES <主表> (关联列)

创建一个学生表：

create table student (
    id int primary key,
    name varchar(10)
);

创建一个班级表：

create table class (
    class_id int primary key,
    student_id int,
    class_name varchar(20)
);

学生表作为主表，班级表作为副表设置外键约束：

alter table class add constraint fk_class_student_id foreign key(student_id) references student(id);

5.1 删除外键：

alter table <从表名> dorp foreign key <外键名>;

alter table class dorp foreign key fk_class_student_id;

5.2 外键的优缺点

外键的优点：

1. 保持数据完整性：外键可以用来保证数据的完整性，确保关联表中的数据一致性和有效性。通过定义外键约束，可以防止无效或不一致的数据进入数据库。

2. 实现表之间的关联查询：外键可以用来连接两个或多个表，建立关联查询。通过关联查询，可以从多个表中获取关联数据，实现数据的查询、分析和报表等功能。

3. 提高数据库性能：外键可以加速查询操作，提高数据库的性能。通过外键关联，可以减少数据冗余和重复存储，提高数据的一致性和规范性。

4. 维护数据一致性：外键可以用来维护数据的一致性。当修改或删除主表的数据时，外键约束可以自动更新或删除关联表中的数据，确保数据的一致性。

外键的缺点：

1. 增加数据库的复杂性：外键的使用增加了数据库的复杂性，对于数据库的设计和维护需要更高的技术要求。错误的外键设计可能导致数据一致性问题或性能下降。

2. 影响数据操作的速度：外键的使用会对数据操作的速度产生一定的影响。在进行数据插入、更新和删除操作时，需要检查外键约束的有效性，可能会引起操作的延迟和性能下降。

3. 数据库迁移和备份的复杂性：外键在数据库迁移和备份过程中可能会带来一些问题。在进行数据迁移或备份时，需要处理外键约束的关系，可能需要更复杂的操作和策略。

4. 对于大规模数据的处理效率较低：在处理大规模数据时，外键的效率可能较低。由于外键约束的检查和维护需要额外的计算和存储开销，对于大规模数据的处理可能影响性能。

建立外键需要注意以下几点：

1. 外键必须引用另一个表的主键或唯一键。被引用的列必须具有唯一性。

2. 确保被引用的表已经存在，并且被引用的列已经创建了主键或唯一索引。

3. 外键引用必须满足引用完整性约束。这意味着在插入或更新数据时，外键列的值必须存在于被引用表的主键列中。

4. 从表里的外键通常为主表的主键。

八、数据表插入数据

在MySQL中通常使用INSERT INTO语句来插入数据。

1.向表中插入数据

--语法
INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...);

其中，table_name是目标数据表的名称，column1，column2，column3等是要插入的列名。VALUES关键字后面是要插入的值，按照相应的列顺序进行匹配。

例如，假设有一个名为users的数据表，包含user_id，user_name和password这几个列，可以使用以下语句向表中插入一条数据：

INSERT INTO users(user_id, user_name, password) 
VALUES (1, 'zhangsan', '12456');

2.向表中插入多条数据

如果要插入多条数据，可以使用INSERT INTO语句的多重VALUES子句，在VALUES关键字后面添加多个值组：

INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;

INSERT INTO users (user_id, user_name, password) 
VALUES (1, 'zhansan', '123456'), (2, 'lisi', '234567'), (3, 'wanwu', '345678');

注意：每个字段与其值都需要一一对应。每个值、值的顺序、值的类型必须与对应的字段相匹配。但是，各字段也无须与其在表周工定义的顺序一样，它们只需要与values中的值的顺序一直即可。

九、数据表更新数据

在更新MySQL数据库中数据表中的数据时，可以使用UPDATE语句来更新数据表中的数据。

1. 更新数据的基本语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

其中，table_name是要更新数据的目标数据表的名称。SET关键字后面是要更新的列和对应的新值。WHERE关键字用于指定更新的条件。

现对users的数据表，包含user_id，user_name和password这几个列，要将name为zhansan的用户的password更新为000123，可以使用以下语句：

UPDATE users
SET password = 000123, 
WHERE username = zhansan;

2. 更新部分数据

如果要更新多个列，可以在SET子句中添加多个列和对应的新值。

UPDATE users
SET password = 000123, gender= '未知',
WHERE user_name = zhansan;

3. 更新符合特定条件的多个记录

可以使用WHERE子句来指定条件。

UPDATE users
SET user_age = 22
WHERE user_age > 20;

十、删除数据

在MySQL中可以通过DELETE语句删除数据表中的数据。

1. DELETE基本语法

DELETE FORM <表名> [WHERE条件表达式]

其中，表名是要删除数据的表的名称，条件是指定要删除的数据的条件。如果不指定条件，则会删除表中的所有数据。

2. DELETE删除表中部分数据

delete form users where uesr_age = 16;

3. 删除全部数据

DELETE FORM users;

十一、 MySQL数据表查询

MySQL数据表的查询是指通过使用SQL语句来检索数据表中的数据。以下使用students学生表的SQL查询语句示例

--创建students学生表
create table students (
    stu_id int primary key,
    stu_name varchar(20),
    stu_age int,
    stu_score int,
    gender char(2)
);
 
 
--向students学生表中插入学生信息
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1001, '张三丰', 17, 88 , 'male');
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1002, '李四', 19, 55 , 'female'); 
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1003, '王五', 19, 68 , 'male');
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1004, '老六', 19, 60 , 'female'); 
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1005, '七七', 18, 71 , 'male');
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1006, '老八', 19, 76 , 'female');
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1007, '韭菜', 20, 90 , 'male');  
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1008, '拾荒', 20, 82 , 'female');
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1009, '十一一', 17, 49 , 'male');
INSERT INTO students (stu_id, stu_name, stu_age, stu_score, gender) VALUES (1010, '十二二', 18, 66 , 'female'); 

1. 基础查询

1.1 直接查询

查询数据表中所有的列

语法：select * form 表名;
 
-- 查询 students表中所有的内容
select * form students;

 1.2 查询数据表中指定的列

select <字段> form 表名;
 
--查询students表中的stu_name列与stu_age列
select stu_name stu_age form students;

1.3 使用AS 给字段起别名

语法：select 字段 as 别名 from 表名;(as可以省略)
--查询students表中的ustu_name和stu_age列
select stu_name 学生姓名, stu_age 学生年龄 from students;
 
select stu_name sn, stu_age sa from students;

1.4 使用AS给数据表起别名

语法：select 字段 as 别名 from 表名;(as可以省略)
--查询students表中的stu_name和stu_age列
select stu_name 学生姓名, stu_age 学生年龄 from students学生表;
 
select stu_name sn, stu_age sa from studentas stu;

1.5 去重查询

DISTINCT 用于从表中获取不重复的数据
语法：SELECT DISTINCT <列名> FORM <表名>;
--查询students表中所有不同的学生年龄
SELECT DISTINCT stu_age FORM students;

1.6 条件查询

语法：SELECT <字段> FROM <表名> WHERE <条件>;
 
-- 从students表中查询学生名称为张三的学生
select stu_name from students where stu_name = '张三';

1.7 关系运算符查询

-- 从 students表中查询 stu_age >=20 的所有记录
select * from students where stu_age >= 20;

1.8 逻辑运算符查询

语法：and（且）, or（或）, not（非） sql 会首先执行 and 条件，再执行 or 语句。除非加括号
-- 从 studnts 表中查询 stu_age >=18 并且 stu_score > 60 分的所有记录
select * from student where stu_age >= 18 and stu_score > 60;
-- 从 students 表中查询 stu_age = 19 或 score = 80 的所有记录
select * from student where stu_age = 19 or score = 80;

1.9 正则表达式查询

MySQL中的正则表达式用regexp表示
语法：select 字段 from 表名 where 字段 regexp '正则表达式';
 
--从 students 表中查询 stu_name 含有李的所有记录
select * from students where stu_name regexp '李';
 
--从 student 表中查询 stu_name 含有李或三的所有记录
select * from students where name regexp '李|三';
 
--从 students 表中查询 stu_name 为李开头的所有记录
select * from students where stu_name regexp '^李';
 
--从 students 表中查询 stu_name 为五结尾的所有记录
select * from students where stu_name regexp '五$';
 
-- 字符.用来替代字符串中的任意一个字符
--从 students 表中查询 stu_name 含'十'和'一'两个字符且中间只隔了一个字符的所有记录
select * from students where stu_name regexp '十.一';
 
-- 字符*和+都可以匹配多个该符号之前的字符。不同的是，+表示至少一个字符，而*可以表示 0 个字符。
-- 从 students 表中查询 stu_name 含丰字的所有记录（三*表示丰字前面可以有0-无数个三，因此至少含有丰）
select * from students where stu_name regexp '三*丰';
-- 从 students 表中查询 stu_name 含丰且前面只少有一个三的所有记录（三+表示丰字前面至少一个三，因此至少含有三丰）
select * from students where stu_name regexp '三+丰';
 
 
-- 从 students 表中查询 stu_name 包含李、三、 十 3 个字符中任意一个的记录
select * from students where stu_name regexp '[李三十]';
-- 方括号[ ]还可以指定集合的区间。例如，“[a-z]”表示从 a~z 的所有字母；“[0-9]”表示从 0~9 的所有数字；“[a-z0-9]”表示包含所有的小写字母和数字；“[a-zA-Z]”表示匹配所有字符;“[\\u4e00-\\u9fa5]”表示中文汉字
 
-- [^字符集合]用来匹配不在指定集合中的任何字符。
-- 查询 stu_name 字段值包含字母 a~t 以外的字符的所有记录
select * from students where stu_name regexp'[^a-t]';
 
-- 字符串{n,}表示字符串连续出现 n 次；字符串{n,m}表示字符串连续出现至少 n 次，最多 m 次。
-- a{2,} 表示字母 a 连续出现至少 2 次，也可以大于 2 次；a{2,4} 表示字母 a 连续出现最少 2 次，最多不能超过 4 次。
-- 查询 stu_name 字段值出现字母‘e’ 至少 2 次的记录
select * from students where stu_name regexp'e{2,}';

1.10 正则表达式操作符中常用的匹配方式

1.11 模糊查询

MySQL中使用like语句进行模糊查询，模糊查询常用的符号如下：

语法：select 字段 from 表名 where 字段 like '%数据%';
-- 从students 表中查询stu_name中含有张的所有记录
select * from students where stu_name like '%张%';
-- 从students 表中查询stu_name中含有十的所有记录
select * from students where stu_name like '%十%';
 
语法：select 字段 from 表名 where 字段 like '_数据';
-- 如果想匹配多个字符 就需要连续使用多个下划线_
-- 从students 表中查询stu_name中以韭开头并只有一个汉字的所有记录
select * from students where stu_name like '韭_';

1.12范围查询

in 和 not in | in是查询定义的范围以内的数据 not in 是查询除开定义的范围内的数据 between ... and ... 是定义的范围以内的数据; 
 
语法：select 字段 from 表名 where 字段 in(列表)或not in(列表); 
-- 从 students 表中查询 stu_age 为（18,19,20）之间的所有记录
select * from students where stu_age in(18, 19, 20);
-- 从 students 表中查询 除了age 为 (18, 19, 20) 之间的所有记录
select * from student where age not in(18, 19, 20);
 
语法：select 字段 from 表名 where 字段 between 值1 and 值2;
-- 从 students 表中查询 age 为 (18, 19, 20) 之间的所有记录
select * from students where stu_age between 18 and 20;

1.13 是否非空判断查询

null（为空）  not null （非空） 判断是否为空要用is
语法：select 字段 from 表名 where 字段 is null(not null);
--从 students 表中查询 stu_name 未填写（为空 null）的所有记录
select * from students where stu_name is not null;

1.14 排序查询

语法：select 字段 from 表名 order by 字段 排序方式: ASC 是按照升序进行排序的，是默认的排序方式。
DESC 是按降序方式进行排序。
-- 从 students 表中查询所有记录并按照 stu_age升序排序
select * from students order by age stu_asc;
 
进阶 select 字段 from 表名 order by 字段 排序方式，字段 排序方式;
（当第一个字段相同时，按第二个字段排序顺序来）
-- 从 students 表中查询所有记录并按照 stu_age 升序，当 stu_age 相同时，按stu_score降序排序
select * from students order by stu_age asc,stu_score desc;

1.15 分页查询

语法：limit可以限制制定查询结果的记录条数
    注意  0 表示第一行记录，也是从 0 开始
    select 字段 from 表名 limit n; 查询前n行的记录
	select 字段 from 表名 limit n, m; 查询第n+1行到第m+n行的记录（也就是从第n+1行开始查询m行记录）
 
-- 从 students 表中查询第三行到第六行的记录，也就是第三行开始查询4条记录 因为从0开始
select * from students limit 2, 4;

1.16 随机查询

-- 随机显示两个学生信息
select * from students order by rand() limit 2;

1.17 分组查询

语法：select 字段 from 表名 group by 字段 ;
-- 从 student 表中查询stu_age值和每个stu_age都有多少人
select age ,count(*) from students group by stu_age ;

注意： GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前

1.18 使用HAVING语句

having语句用于筛选分组的数据

语法：select 字段 from 表名 group by 字段 having 条件;
-- 从 students 表中查询stu_age>19值和每个stu_age>19都有多少人
select stu_age ,count(*) from students group by stu_age having stu_age > 19;

1.19 使用EXISTS语句

用来测试内查询是否存在来判断查询条件，类似布尔值是否为真
如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。

语法：SELECT 字段1 FROM 表名1 WHERE EXISTS (SELECT 字段2 FROM 表名2 WHERE 条件);
 
-- 假设有两个表：Customers和Orders。我们想要查询存在未完成订单的客户列表。
-- 使用EXISTS查询来判断是否存在满足条件的订单，如果存在，则返回相应的客户名。
SELECT DISTINCT c.customer_name FROM customers c WHERE EXISTS (
    SELECT * FROM orders o WHERE o.customer_id = c.customer_id 
    AND o.status <> 'completed'
);

注意：在使用EXISTS时，子查询的返回结果并不会被使用，只关心子查询是否返回了结果。 

十二、表之间的关联关系 

1. 表的关联关系

是指在数据库中不同表之间存在各种关系。常见的表之间的关联关系有三种：

1. 一对一关系：一个表的每一行与另一个表的一行对应。在这种关系中，每个表的主键和外键只能唯一地关联一对数据。

2. 一对多关系：一个表的每一行与另一个表的多行对应。在这种关系中，一个表的主键被另一个表的外键引用，从而建立起两个表之间的关联。

3. 多对多关系：一个表的多行与另一个表的多行对应。在这种关系中，需要使用一个关联表来连接两个表，关联表包含两个表的主键作为外键，从而建立起两个表之间的关联。

通过建立表之间的关联关系，可以实现数据的一致性和完整性，并且可以方便地进行数据的查询和分析。

2. 关联查询

关联查询是一种查询方式，用于在数据库中将两个或多个表中的数据相关联起来。这种查询可以通过共享相同的值或关联键将表连接在一起。关联查询通常使用关联条件来指定连接的方式。

列如：关联查询火箭班中所有的学生数据

select * from students where id = (select id from class where class_name = '火箭' );

十三、高级查询

1. 嵌套查询

嵌套查询是指在一个查询语句中嵌入另一个查询语句。嵌套查询可以通过对内部查询的结果进行进一步的过滤和处理来实现复杂的查询需求。嵌套查询通常使用子查询的形式，子查询可以作为条件、表达式或子查询表的一部分来辅助查询。

嵌套查询语法：SELECT 列名 FROM 表名 WHERE 列名 值 运算符 (SELECT 列名 FROM 表名 WHERE 条件)
 
-- 先查询学生平均年龄，再查询大于平均年龄的学生
select * from students where stu_age > (select avg(stu_age) from students);

 其中，子查询可以出现在主查询的 SELECT 语句、FROM 语句、WHERE 语句、HAVING 语句、GROUP BY 语句等部分。

注意：嵌套查询的性能可能会较低，因此在使用嵌套查询时，应尽量优化查询语句，并考虑是否有更好的替代方案。

2. 数据表连接查询

表连接查询是将多个表中的数据联接起来的查询方式。它通过使用表之间的关联键进行匹配，将符合条件的记录组合起来生成结果集。

常用的表连接查询有三种：inner join 内连接 left join 左连接 right join 右连接

2.1 内连接

内连接（INNER JOIN）：只返回满足联接条件的记录。

-- 内连接语法：
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

其中，table1和table2是要连接的两个表，column是它们之间的关联键，通常使用ON子句来指定关联键。

select * from students stu inner join class cs on stu.stu_id = cs.id;

2.2 左外连接

左连接（LEFT JOIN）：返回满足联接条件的记录以及左表（左侧表）中所有的记录。如果右表（右侧表）中没有匹配记录，则返回NULL值。

-- 左连接语法：
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

 其中，table1和table2是要连接的两个表，table1是左表，table2是右表。column是它们之间的关联键，on字句通常用来指定关联键

select * from students stu left join class cs on stu.stu_id = cs.id;
 
-- 与内连接形式相同，但左表为主表，如有指定字段则都会显示，右表为从表，无内容会显示 null

2.3 右外连接

右连接（RIGHT JOIN）：返回满足联接条件的记录以及右表中所有的记录。如果左表中没有匹配记录，则返回NULL值。

--右连接语法：
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

其中，table1和table2是要连接的两个表，table1是左表，table2是右表。column是它们之间的关联键，on字句通常用来指定关联键。

select * from students stu right join class cs on stu.stu_id = cs.id;
 
-- 与内连接形式相同，但右表为主表，如有指定字段则都会显示，左表为从表，无内容会显示 null

左连接和右连接相似，只是左表和右表的角色翻转了

3. 聚合函数查询 

聚合函数是在数据库中用于对数据进行统计和汇总的函数。

聚合函数使用规则：

只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。

3.1 COUNT

用于计算某列或某个表的行数。

-- count(字段) 统计个数
语法：selects count(字段) from 表名;
-- 从 students 中查询 stu_name 的记录个数
select count(stu_name) from students;

3.2 SUM

用于计算某个列的总和。

-- sum() 求和
语法：select sum(字段) from 表名;
-- 从 students 中统计所有 score 的和
select sum(score) from students;

3.3 AVG

用于计算某个列的平均值。

-- avg() 求平均值
语法：select avg(字段) from 表名;
-- 从 students 中对所有的 score 求平均值
select avg(score) from students;

3.4 MAX

用于找出某个列的最大值。

-- max() 求最大值
语法：select max(字段) from 表名;
-- 从 students 中查询最大的 stu_age
select max(stu_age) from students;

3.5 MIN

用于找出某个列的最小值。

-- min() 求最小值
语法：select min(字段) from 表名;
-- 从 students 中查询最小的 stu_age
select min(stu_age) from students;

4. 函数查询

MySQL中有多种类型的函数，可以根据功能和用途进行分类。以下是MySQL中常见的几种函数类型：

4.1. 数值函数：

MySQL数值函数是一组用于处理数值数据的函数。

这些函数可以在SELECT语句中用于计算数值表达式或对结果进行数值计算和处理。下面是一些常用的MySQL数值函数示例：

-- ABS(x)：返回x的绝对值。
SELECT ABS(-10) AS absolute_value; -- 结果为 10
 
-- CEILING(x)：返回不小于x的最小整数。
SELECT CEILING(3.14159) AS ceiling_value; -- 结果为 4
 
-- FLOOR(x)：返回不大于x的最大整数。
SELECT FLOOR(3.14159) AS floor_value; -- 结果为 3
 
-- ROUND(x, d)：对x进行四舍五入，保留d位小数。如果d被省略，则默认为0。
SELECT ROUND(3.14159, 2) AS rounded_value; -- 结果为 3.14
 
-- MOD(x, y)：返回x除以y的余数。
SELECT MOD(10, 3) AS modulus_value; -- 结果为 1
 
-- SQRT(x)：返回x的平方根。
SELECT SQRT(16) AS square_root; -- 结果为 4
 
-- POWER(x, y)：返回x的y次方。
SELECT POWER(2, 3) AS power_value; -- 结果为 8

4.2 字符串函数

MySQL字符串函数是一组用于处理字符串数据的函数。

这些函数可以在SELECT语句中用于处理字符串数据，可以进行字符串连接、截取、替换、转换等操作。下面是字符串函数的示例

-- CONCAT(str1, str2, ...)	将多个字符串连接为一个字符串
SELECT CONCAT('Hello', ' ', 'World'); 
 
--LENGTH(str)	返回字符串的长度
LENGTH('Hello World'); 
 
--UPPER(str)	将字符串转换为大写
SELECT UPPER('hello'); 
 
--LOWER(str)	将字符串转换为小写
SELECT LOWER('WORLD'); 
 
--SUBSTRING(str, start, length)	返回字符串的子串 
-- str：要截取子串的原始字符串。
-- start：指定要开始截取子串的位置。位置从1开始计数。
-- length：指定要截取的子串的长度。
SELECT SUBSTRING('Hello World', 7, 5);
 
-- REPLACE(str, search, replace)将字符串中的指定子字符串替换为新的字符串
SELECT REPLACE('Hello World', 'World', 'MySQL');
 
-- TRIM([BOTH | LEADING | TRAILING] trimstr FROM str)去除字符串两端或指定位置的指定字符或空格
-- BOTH：去除字符串两端的指定字符。
-- LEADING：只去除字符串开头的指定字符。
-- TRAILING：只去除字符串结尾的指定字符。
SELECT TRIM(' abc ') AS trimmed_str;  -- 结果为 'abc'
SELECT TRIM(LEADING '0' FROM '000123') AS trimmed_str;  -- 结果为 '123'
SELECT TRIM(TRAILING '@' FROM 'email@example.com@') AS trimmed_str;  -- 结果为 'email@example.com'
 
-- LPAD(str, length, padstr)在字符串的左侧填充指定字符，使字符串长度达到指定长度
-- str是要填充的字符串 length是填充后字符串的长度 padstr是填充的字符
SELECT LPAD('abc', 6, '0') AS padded_str;  -- 结果为 '000abc'
SELECT LPAD('123', 5, '*') AS padded_str;  -- 结果为 '**123'
 
--RPAD(str, length, padstr)在字符串的右侧填充指定字符，使字符串长度达到指定长度
-- str是要填充的字符串 length是填充后字符串的长度 padstr是填充的字符
SELECT RPAD('abc', 6, '0') AS padded_str;  -- 结果为 'abc000'
SELECT RPAD('123', 5, '*') AS padded_str;  -- 结果为 '123**'
 
-- INSTR(str, substr)	返回子字符串在字符串中的位置
-- str是要搜索的字符串 substr是要搜索的子字符串
SELECT INSTR('hello world', 'lo') AS position;  -- 结果为 4
SELECT INSTR('hello world', 'abc') AS position;  -- 结果为 0
 
-- CONCAT_WS(separator, str1, str2, ...)	使用指定的分隔符连接字符串
SELECT CONCAT_WS('-', '2021', '01', '01'); 
 
-- SUBSTRING_INDEX 返回指定分隔符之前或之后的子字符串
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

4.3 日期和时间函数

用于处理和操作日期和时间数据

-- NOW(): 返回当前日期和时间
SELECT NOW();
 
-- CURDATE(): 返回当前日期
SELECT CURDATE();
 
-- CURTIME(); 返回当前时间
SELECT CURTIME();
 
-- DATE(str): 将字符串转换为日期格式 如果参数格式不正常就会返回null
SELECT DATE('2022-01-01') AS date; -- 结果为 2022-01-01
SELECT DATE('22-01-01') AS date; -- 结果为 2022-01-01
SELECT DATE('20220101') AS date; -- 结果为 2022-01-01
SELECT DATE('220101') AS date; -- 结果为 2022-01-01
SELECT DATE('2022/01/01') AS date; -- 结果为 2022-01-01
SELECT DATE('invalid date') AS date; -- 结果为 NULL
 
-- TIME(str): 将字符串转换为时间格式 如果参数格式不正确就会返回null
SELECT TIME('12:34:56') AS time; -- 结果为 12:34:56
SELECT TIME('12:34') AS time; -- 结果为 12:34:00
SELECT TIME('123456') AS time; -- 结果为 12:34:56
SELECT TIME('1234') AS time; -- 结果为 12:34:00
SELECT TIME('invalid time') AS time; -- 结果为 NULL
 
-- YEAR(date): 返回日期的年份。
SELECT YEAR('2022-01-01');
 
-- MONTH(date): 返回日期的月份。
SELECT MONTH('2022-01-01');
 
-- DAY(date): 返回日期的天数。
SELECT DAY('2022-01-01');
 
-- HOUR(time): 返回时间的小时数。
SELECT HOUR('12:30:00');
 
-- MINUTE(time): 返回时间的分钟数。
SELECT MINUTE('12:30:00');
 
-- SECOND(time): 返回时间的秒数。
SELECT SECOND('12:30:00');
 
-- DATE_FORMAT(date, format): 将日期格式化为指定的格式。
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
 
-- DATE_ADD(date, INTERVAL value unit): 给日期添加指定的时间间隔。
SELECT DATE_ADD('2022-01-01', INTERVAL 1 DAY);
 
-- DATE_SUB(date, INTERVAL value unit): 从日期中减去指定的时间间隔。
SELECT DATE_SUB('2022-01-01', INTERVAL 1 DAY);
 
-- DATEDIFF(date1, date2): 计算两个日期之间的天数差。
SELECT DATEDIFF('2022-12-31', '2022-01-01');
 
-- TIMESTAMPDIFF(unit, timestamp1, timestamp2): 返回两个时间戳之间的差距，单位可以是秒、分钟、小时、天等。
SELECT TIMESTAMPDIFF(MINUTE, '2022-01-01 12:00:00', '2022-01-01 12:30:00');

这些函数可以在SELECT语句中用于处理日期和时间数据，进行日期的提取、格式化、计算、比较等操作。

4.4 条件函数

条件函数是一组用于处理条件判断的函数

以下是一些常用的条件函数的示例：

-- IF(expr, value1, value1): 如果expr条件成立，则返回value1；否则返回value2。
SELECT IF(5 > 3, '大于', '小于');
 
-- CASE WHEN condition THEN result END: 根据条件返回结果。
SELECT CASE WHEN 5 > 3 THEN '大于' ELSE '小于' END;
 
-- COALESCE(value1, value2, ...): 返回参数列表中第一个非空值。
SELECT COALESCE(NULL, '默认值');
 
-- NULLIF(value1, value2): 如果value1和value2相等，则返回NULL；否则返回value1。
SELECT NULLIF(10, 10);
 
 
-- NULLIFNULL(value1, value2): 如果value1为NULL，则返回value1；否则返回value2。
SELECT NULLIFNULL(NULL, '默认值');
 
-- IFNULL(value1, value2): 如果value1为非NULL值，则返回value1；否则返回value2。
SELECT IFNULL(NULL, '默认值');
 
-- CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE result END: 根据表达式匹配相应的结果。
SELECT CASE 5 WHEN 1 THEN '一' WHEN 2 THEN '二' ELSE '其他' END;

这些函数可以在SELECT语句、WHERE子句、ORDER BY子句等位置使用，用于根据条件进行不同的操作和返回不同的结果。

4.5 数学函数

用于执行数学计算

 以下只是简单的数学函数示例：

-- ABS(x): 返回x的绝对值。
SELECT ABS(-5);
 
-- CEIL(x): 返回不小于x的最小整数。
SELECT CEIL(4.2);
 
-- FLOOR(x): 返回不大于x的最大整数。
SELECT FLOOR(4.9);
 
-- ROUND(x): 返回最接近x的整数。
SELECT ROUND(4.6);
 
-- RAND(): 返回一个0到1之间的随机数。
SELECT RAND();
 
-- SQRT(x): 返回x的平方根。
SELECT SQRT(16);
 
-- POW(x, y): 返回x的y次方。
SELECT POW(2, 3);
 
-- MOD(x, y): 返回x除以y的余数。
SELECT MOD(10, 3);

4.6 系统函数

系统函数：用于获取和处理数据库服务器的相关信息。

这些系统函数可以在SELECT语句中使用，或者用作查询条件，以获得关于数据库服务器状态和连接信息的相关数据。以下只是一些简单示例：

-- NOW(): 返回当前日期和时间。
SELECT NOW();
 
-- CURRENT_DATE(): 返回当前日期。
SELECT CURRENT_DATE();
 
-- CURRENT_TIME(): 返回当前时间。
SELECT CURRENT_TIME();
 
-- CURDATE(): 返回当前日期。
SELECT CURDATE();
 
-- CURTIME(): 返回当前时间。
SELECT CURTIME();
 
-- UNIX_TIMESTAMP(): 返回当前日期和时间的UNIX时间戳。
SELECT UNIX_TIMESTAMP();
 
-- SYSDATE(): 返回当前日期和时间。
SELECT SYSDATE();
 
-- VERSION(): 返回当前MySQL服务器的版本号。
SELECT VERSION();
 
-- DATABASE(): 返回当前正在使用的数据库名。
SELECT DATABASE();
 
-- USER(): 返回当前用户的用户名。
SELECT USER();

4.7 转换函数

转换函数，用于在查询中对数据类型进行转换。

--使用CAST函数将一个字符串转换为整数
SELECT CAST('10' AS INT);
 
--使用CONVERT函数将一个字符串转换为日期
SELECT CONVERT('2021-01-01', DATE);
 
--使用DATE函数从一个日期时间类型的列中获取日期部分
SELECT DATE(datetime_column);
 
--使用TIME函数从一个日期时间类型的列中获取时间部分
SELECT TIME(datetime_column);

这些转换函数可用于SELECT语句中，以便在查询结果中对数据类型进行转换，以满足特定的需求。

十四、MySQL数据库字句说明

1. 字句说明

2. SELECT字句的执行顺序

2.1 书写顺序

SELECT ---> FROM --->WHERE ---> GROUP BY ---> HAVING ---> ORDER BY---> LIMIT

2.2 执行顺序

FROM ---> WHERE ---> GROUP BY ---> HAVING ---> SELECT ---> ORDER BY ---> LIMT