MySQL - 1（ 11000 字详解 ）

一：数据库的引入

数据库在我们以后工作中是一个非常常用的知识，数据库用来存储数据，但是有些同学可能就会疑惑了，存储数据用文件就可以了，为什么还要弄个数据库呢？

文件保存数据有以下几个缺点：

• 文件的安全性问题
• 文件不利于数据查询和管理
• 文件不利于存储海量数据
• 文件在程序中控制不方便

为了解决上述问题，专家们设计出更加利于管理数据的软件——数据库，它能更有效的管理数据。数据库可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。

数据可以存储在内存和外存两种介质中。这两种存储介质在性能、容量和可靠性等方面有着不同的特点和限制。

1. 内存存储（主存）：

• 特点：内存是计算机的主要工作区域，数据可以被快速读取和写入。它是临时存储数据的地方，当计算机关机或断电时，内存中的数据将丢失。
• 优点：内存的读写速度非常快，可以迅速访问和操作数据。
• 缺点：内存的容量较小，一般为几十GB至几百GB，因此无法长期存储大量的数据。此外，内存中的数据在断电或重新启动后会丢失。

2. 外存存储（辅助存储）：

• 特点：外存储器通常是硬盘、固态硬盘（SSD）或网络存储等，它们用于长期存储数据。外存的读写速度相对较慢。
• 优点：外存储器的容量通常较大，可以存储大量的数据并且数据不会因为断电而丢失。
• 缺点：相对于内存来说，外存储器的读写速度较慢，读写数据需要一定的时间。

二：如何启动MySQL数据库

1. 首先我们打开运行

2. 接着搜索 services.msc

3. 找到 MySQL，并右键启动：

4. 接着打开 cmd：

5. 输入命令：mysql -u root -p ( 没有分号 )

6. 接着输入密码：

7. 这就成功开启了

三：数据库基础

3.1 数据库操作

3.1.1 显示所有数据库

我们可以在 cmd 中通过

• show databases;

这个，命令来显示 mysql 中所有的数据库（是有s的），如图所示：

注意："5 rows in set"是指查询结果集中有 5 行数据。"0.00 sec"表示查询所花费的时间，以秒（ sec ）为单位。

通过这个命令我们就可以知道我们目前有5个库，其中information_schema、mysql和performance_schema是 MySQL 本身就存在的三个库。它们是系统默认创建的，用于存储和管理数据库的元数据信息、权限信息和性能统计数据。

student和studentinfosystem是我自己创建的数据库，不是MySQL系统默认存在的。

3.2 创建一个数据库

在cmd中，我们可以通过这个命令创建一个 mysql 数据库：

• create database 数据库名;

注意：mysql是不区分大小写的，所以这种写法也行：

• CREATE DATABASE 数据库名;

数据库的命名和 java 变量命名一样，但是如果你就是非要用关键字作为数据库的名字，那么你可以用反引号把数据库名字引起来，并且创建数据库的时候，不能和已有的数据库名字重复

1. 创建命令使用结果如图所示：

2. 接着我们再显示一下数据库，查看一下现存的数据库有哪些：

3. 可以看出我们已经成功创建了一个 ice 数据库，下面还有其他形式的创建方式：

 CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4;

这行代码表示如果系统没有 db_test 的数据库，则创建一个使用 utf8mb4 字符集的 db_test 数据库，如果有则不创建

IF NOT EXISTS 和 CHARACTER SET 都是可选的，我们可以把 character set 合并为 charset

对于不同的字符集，表示一个汉字所需要的字节数一般不同：

• 比如在 GBK 下，一个汉字的表示需要 2 个字节
• 在 UTF8 下，一个字节的表示需要 3 个字节

如果不指定字符集的话，会使用默认的拉丁文字符集，很可能插入中文失败，我们一般使用 UTF8 字符集

3.3 选中数据库

既然我们现在已经会创建了数据库了，那么我们该如何去使用一个数据库呢？

在 mysql 数据库中，要想对这个数据库进行操作，你需要先选中这个数据库，选中这个数据库后，才能进行后续的操作

在cmd中，我们通过这个命令来选中数据库：

• use 数据库名;

运行结果如图所示：

当你切换到一个不同的数据库时，会显示"Database changed"的提示。这意味着你已经成功切换到了指定的数据库，可以开始执行与该数据库相关的操作了。

3.4 删除数据库

在 mysql 中，如果我们对已经不需要的数据库进行删除，那么我们该如何删除这个数据库呢？在mysql中，我们通过这个命令删除数据库：

• drop database 数据库名；

如图所示：

注意：删库的操作是非常危险的，请谨慎使用，

3.2 mysql 常用数据类型：

3.2.1 数值类型：

数据类型	大小	对应java类型
BIT[ (M) ]	M指定位数，默认为1	常用Boolean对应BIT，此时默认是1位，即只能存0和1
TINYINT	1字节	Byte
SMALLINT	2字节	Short
INT	4字节	Integer
BIGINT	8字节	Long
FLOAT(M, D)	4字节	Float
DOUBLE(M,D)	8字节	Double
DECIMAL(M,D)	M/D最大值+2	BigDecimal
NUMERIC(M,D)	M/D最大值+2	BigDecimal

在 java 中，float 和 double 存储浮点数会存在误差，decimal采用类似于字符串的存储方式保存，所以 decimal 对于浮点数的存储没有误差，但是 decimal 存储空间大，计算速度慢

decimal 和 numeric ：

• 总位数（M）：表示该数字可以包含的总位数（包括整数和小数位）。
• 小数位数（D）：表示该数字可以包含的小数位数。

举个例子，DECIMAL(5,2) 可以存储最大为 5 位数的数字，其中有 2 位为小数位。例如，123.45 就可以存储在 DECIMAL(5,2) 数据类型中。

decimal 和 numeric 是互相可替代的别名，他们在本质上是相同的，MySQL 允许使用它们来表示相同的数据类型。

3.2.2 字符串类型

数据类型	大小	说明	对应java类型
VARCHAR (SIZE)	0-65,535	可变长度字符串	String
TEXT	0-65,535	长文本数据	String
MEDIUMTEXT	0-16 777 215	中等长度文本数据	String
BLOB	0-65,535	二进制形式的长文本数据	byte[]

varchar 中的 size 指定的是最大长度，单位是字符，打个比方，varchar（10）表示最大存储 10 个字，并且这种存储是动态存储的

3.2.3 日期类型

数据类型	大小	说明	对应java类型
DATETIME	8字节	范围从1000到9999年，不会进行时区的检索及转换。	java.util.Date、java.sql.Timestamp
TIMESTAMP	4字节	范围从1970到2038年，自动检索当前时区并进行转换。	java.util.Date、java.sql.Timestamp

时间戳：以1970年1月1日0时0分为基础，计算当前时刻和基准时候的 秒数/毫秒 之差

3.3 表的操作

在学习表的操作之前，我们需要了解一下什么是表，简单来说，就是表存在于数据库中，数据库中有多个表，这些表之间相互独立且彼此隔离。

可以用一个简单的图来示例它们之间的关系：

在对表进行任何操作前，都要先选中这个表所在的数据库

3.3.1 描述表

在 mysql 中，我们可以通过这个命令来查看表的结构：

• desc 表名;

3.3.2 创建表

在 mysql 中，我们通过这个命令来创建一个表：

CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
);

下面通过一个示例讲解：

create table stu_test (
 id int,
 name varchar(20) comment '姓名',
 password varchar(50) comment '密码',
 age int,-- 年龄
 sex varchar(1), -- 性别
 birthday timestamp, -- 生日
 amout decimal(13,2), 
 resume text 
);

在 mysql 中，使用 comment 关键字来添加注释，注释的内容需要用单引号引起来，并且comment 只能在创建表的时候使用，我们还可以在 SQL 中使用“- -空格+描述”来表示注释说明，更推荐用 - -空格作为注释

下面是对这个表的描述：

3.3.3 显示所有表

我们如果要先查看一个数据库中有哪些表的话，我们可以使用这个命令进行查看：

• SHOW TABLES;

如图所示：

3.3.4 删除表

对于一个表的删除，我们可以使用下面的这个命令

--  删除 stu_test 表
drop table stu_test;

-- 如果存在 stu_test 表，则删除 stu_test 表
drop table if exists stu_test;

请注意，删表和删库的操作都是很危险的，删除操作是不可逆的，所以在执行该命令之前，要确保你真的想要删除该表

3.4 表的增删查改（CRUD）

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

3.4.1 增加表中的数据

首先我们先创建一个表

DROP TABLE IF EXISTS student;
CREATE TABLE student (
 id INT,
 sn INT comment '学号',
 name VARCHAR(20) comment '姓名',
 qq_mail VARCHAR(20) comment 'QQ邮箱'
);

接着我们就可以在这个表中增加数据了：

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

这是两个 MySQL 的 单行数据全列插入语句，用于向名为 student 的表中插入数据。每个 INSERT 语句插入了一行数据。

第一个 INSERT 语句插入了以下数据：

• 学生编号: 100
• 学生学号: 10000
• 学生姓名: 唐三藏
• 学生密码: NULL（空值）

第二个 INSERT 语句插入了以下数据：

• 学生编号: 101
• 学生学号: 10001
• 学生姓名: 孙悟空
• 学生密码: ‘11111’

当然，我们还可以把这两个语句合并成一个语句：

INSERT INTO student VALUES (100, 10000, '唐三藏', NULL), (101, 10001, '孙悟空', '11111');

我们还可以将 insert into 合并为 into，即

INTO student VALUES (100, 10000, '唐三藏', NULL), (101, 10001, '孙悟空', '11111');

3.4.1.1 多行数据 + 指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
INSERT INTO student (id, sn, name) VALUES (102, 20001, '曹孟德'),(103, 20002, '孙仲谋');

这条插入语句的意思是往 student 表中插入两条记录，分别是

1. id: 102, sn: 20001, name: ‘曹孟德’，
2. id: 103, sn: 20002, name: ‘孙仲谋’。

我们还可以只指定 id 和 name 两列进行插入

3.4.2 在表中查询数据

首先我们创建一个表，并在表中新增一些数据：

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
	id INT,
	name VARCHAR(20),
	chinese DECIMAL(3,1),
	math DECIMAL(3,1),
	english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);

3.4.2.1 全列查询

-- 通常情况下不建议使用 * 进行全列查询，因为
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。（索引待后面课程讲解）
SELECT * FROM exam_result;

SELECT * FROM exam_result; 是一条 SQL 查询语句，用于从 exam_result 表中检索所有的数据记录。* 是通配符，表示选择所有的列。而 FROM exam_result 表示从名为 exam_result 的表中进行数据检索。

如图所示：

3.4.2.2 指定列查询

-- 指定列的顺序不需要按定义表的顺序来
SELECT id, name, english FROM exam_result;

通过这个命令，我们就可以看想要看见的列

3.4.2.3 查询字段为表达式

下面是示例语句

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result; 
-- 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;
-- 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

下面我们对这三个语句进行解释：

SELECT id, name, 10 FROM exam_result;

这是一个简单的 SQL 查询语句，用于从 exam_result 表中选择 id、name 和 10 这三个列的数据。这个查询会返回 exam_result 表中每一行的 id 和 name 列的值，并且 10 这个常量值会被作为第三列返回。

如图所示：

 SELECT id, name, english + 10 FROM exam_result;

这个 SQL 查询语句是用来从 exam_result 表中选择 id、name 和 english 列的值，并且english 列的值将在原有值的基础上增加 10

注意，english 中的数据并没有发生改变

如图所示：

SELECT id, name, chinese + math + english FROM exam_result;

这个 SQL 查询语句的意思是从 exam_result 表中选择 id、name 和 chinese + math + english 的总分，并显示在结果集中。

如图所示：

3.4.2.4 别名

在 mysql 中，我们可以对字段进行重命名，在 mysql 中也叫别名。

字段 as 别名 

这是别名的的一般形式，将字段重命名为别名

下面是一个示例语句：

SELECT id, name, chinese + math + english AS 总分 FROM exam_result;
-- as可以省略，所以也可以写成
SELECT id, name, chinese + math + english 总分 FROM exam_result;

这两个 SQL 查询语句是从 “exam_result” 表中选择 “id”、“name” 和 "chinese + math + english " 列，并给 “chinese + math + english” 列的总和起了一个别名 “总分”。

虽然 as 可以省略，但是并不建议省略 as，因为省略了 as，语句的可读性会差一点，

结果如图所示：

如果你还需要再对 id 重命名的话，你可以：

SELECT id AS aftid, name, chinese + math + english AS 总分 FROM exam_result;

3.4.2.5 去重

在 mysql 中，我们使用 distinct 关键字对某列数据进行去重，示例语句如下：

SELECT DISTINCT math FROM exam_result;

这个 SQL 查询语句的意思是从 “exam_result” 表中选择不重复的数学成绩（“math” 列）。它会返回该列中所有不重复的数学成绩值。

注意：distinct 保留第一个出现的值，去除掉第一个重复出现的值

3.4.2.6 排序

在 mysql 中，我们通过 ORDER BY 进行排序（采用的是并归排序），下面是示例语句：

-- 查询同学姓名和 qq_mail，按 qq_mail 排序显示
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT name, qq_mail FROM student ORDER BY qq_mail;
SELECT name, qq_mail FROM student  ORDER BY qq_mail DESC;

通过这两个查询语句，我们可以获取 “student” 表中 “name” 和 “qq_mail” 列的数据，并按照 “qq_mail” 列的升序和降序进行排序。

注意：NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

我们还可以使用表达式及别名排序：

-- 查询同学及总分，由高到低
SELECT name, chinese + english + math FROM exam_result ORDER BY chinese + english + math DESC;
-- 等价于
SELECT name, chinese + english + math as total FROM exam_result ORDER BY total DESC;

我们还可以对多个字段进行排序，排序优先级随书写顺序：

-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;

如果 math 的值相同了，那么接着按照英语的排序规则再根据值进行排序，如果英语的值也一样，那么按照语文的值再进行比较

3.4.2.7 条件查询

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

注意：LIKE 运算符是区分大小写的。

下面是关于比较运算符一些要注意的地方：

• null 和其他数值进行运算，结果还是 null
• null 结果在条件中的话，那就相当于 false 了

比如 null == null 的结果还是 null 而不是 true

条件运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)，优先级高于OR
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

我们通过 where 子句和条件表达式，就可以完成条件查询了，比如说：

select * from eaxm_result where english < 60;

通过这个我们就可以查询到在 eaxm_result 这个表中，英语分数小于 60 的记录了。

注意：

1. WHERE 条件可以使用表达式，但不能使用别名。
2. AND 的优先级高于 OR，在同时使用时，需要使用小括号()包裹优先执行的部分

以下是一些搭配where和条件表达式使用的语句：

-- 查询英语不及格的同学及英语成绩 ( < 60 )
SELECT name, english FROM exam_result WHERE english < 60;

-- 查询语文成绩好于英语成绩的同学
SELECT name, chinese, english FROM exam_result WHERE chinese > english;

-- 查询总分在 200 分以下的同学
SELECT name, chinese + math + english 总分 FROM exam_result WHERE chinese + math + english < 200;

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;

-- 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;

-- 注意加括号，and的优先级比or高
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

-- 使用 OR 也可以实现
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;

-- % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权

-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权

-- 查询 qq_mail 已知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;

-- 查询 qq_mail 未知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

3.4.2.8 分页查询

语法：

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

案例：按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;

-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 3;

-- 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;

3.4.3 修改表中的数据

语法：

UPDATE table_name SET column = expr [, column = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]

案例：

-- 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';

-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';

-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;

-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

在修改中，如果不写 where 条件，那么修改就是针对所有行进行的

3.4.4 删除表中的数据

语法：

DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

案例：

-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';

注意：在 mysql 中，如果 delete 后面不跟 where，那么删除的是整张表的数据，这和删表无异，表中的数据都没了，只不过这张表还在而已

四：重点内容总结

新增：

-- 单行插入
insert into 表(字段1, ..., 字段N) values (value1, ..., value N);

-- 多行插入
insert into 表(字段1, ..., 字段N) values
(value1, ...),
(value2, ...),
(value3, ...);

查询：

-- 全列查询
select * from 表

-- 指定列查询
select 字段1,字段2... from 表

-- 查询表达式字段
select 字段1+100,字段2+字段3 from 表

-- 别名
select 字段1 别名1, 字段2 别名2 from 表

-- 去重DISTINCT
select distinct 字段 from 表

-- 排序ORDER BY
select * from 表 order by 排序字段

-- 条件查询WHERE：
-- (1)比较运算符 (2)BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR (8)NOT
select * from 表 where 条件

修改：

update 表 set 字段1=value1, 字段2=value2... where 条件

删除：

delete from 表 where 条件