Java Web 开发学习Day2 数据库知识复习与整理（黑马程序员网课知识总结）
引言：
web开发调用流程
- SQL（Structured Query Language，简称SQL）：结构化查询语言，它是操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。
- 程序员给数据库管理系统(DBMS)发送SQL语句，再由数据库管理系统操作数据库当中的数据。
- MySQL概述
- SQL语句（DDL、DML、DQL）
- 多表设计
- 多表查询
- 事务
- 索引
1.MySQL概述
1.1安装（省略）
1.2连接
MySQL服务器启动完毕后，然后再使用如下指令，来连接MySQL服务器：
mysql -u用户名 -p密码 [-h数据库服务器的IP地址 -P端口号]
- -h 参数不加，默认连接的是本地 127.0.0.1 的MySQL服务器
- -P 参数不加，默认连接的端口号是 3306
上述MySQL服务器我们是安装在本地的，这个仅仅是在我们学习阶段，在真实的企业开发中，MySQL数据库服务器是不会在我们本地安装的，是在公司的服务器上安装的，而服务器还需要放置在专门的IDC机房中的，IDC机房呢，就需要保证恒温、恒湿、恒压，而且还要保证网络、电源的可靠性(备用电源及网络)。
如果我们要想使用服务器上的这台MySQL服务器，就需要在我们的电脑上去远程连接这台MySQL 。 而服务器上安装的MySQL数据库呢，并不是你一个人在访问，我们项目组的其他开发人员也是需要访问这台MySQL的。
（还没学，后面写)
1.3数据模型
关系型数据库（RDBMS）
概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。而所谓二维表，指的是由行和列组成的表，
二维表的优点：
- 使用表存储数据，格式统一，便于维护
- 使用SQL语言操作，标准统一，使用方便，可用于复杂查询
2.SQL语句
| 分类 | 全称 | 说明 |
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表，字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
2.1 DDL（Data Definition Language）数据库定义语言
DDL中数据库的常见操作：查询、创建、使用、删除。
2.1.1查询
查询所有数据库:
show databases;
查询当前数据库；
select database();
2.1.2创建
创建数据库
create database [ if not exists ] 数据库名 [default charset utf8mb4];
注意：在同一个数据库服务器中，不能创建两个名称相同的数据库，否则将会报错。
可以使用if not exists来避免这个问题
2.1.3使用数据库
use 数据库名 (可通过该指令切换操作的数据库）
2.1.4删除数据库
drop database [ if exists ] 数据库名 ;
说明：上述语法中的database，也可以替换成 schema
2.1.5表操作
2.1.5.6 创建
语法
create table 表名( 字段1 字段1类型 [约束] [comment 字段1注释 ], 字段2 字段2类型 [约束] [comment 字段2注释 ], ...... 字段n 字段n类型 [约束] [comment 字段n注释 ] ) [ comment 表注释 ] ;
代码块
create table tb_user (
id int comment 'ID,唯一标识', # id是一行数据的唯一标识（不能重复）
username varchar(20) comment '用户名',
name varchar(10) comment '姓名', age int comment '年龄',
gender char(1) comment '性别' ) comment '用户表';
2.1.5.7约束
- 概念：所谓约束就是作用在表中字段上的规则，用于限制存储在表中的数据。
- 作用：就是来保证数据库当中数据的正确性、有效性和完整性。（后面的学习会验证这些）
- 在MySQL数据库当中，提供了以下5种约束：
| 约束 | 描述 | 关键字 |
| 非空约束 | 限制该字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 | default |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
id字段下存储的值，如果由我们自己来维护会比较麻烦(必须保证值的唯一性)。MySQL数据库为了解决这个问题，给我们提供了一个关键字：auto_increment（自动增长）
主键自增：auto_increment
- 每次插入新的行记录时，数据库自动生成id字段(主键)下的值
- 具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
2.1.5.8数据类型
1）.数值类型
| 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
| TINYINT | 1byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2bytes | (-32768，32767) | (0，65535) | 大整数值 |
| MEDIUMINT | 3bytes | (-8388608，8388607) | (0，16777215) | 大整数值 |
| INT/INTEGER | 4bytes | (-2147483648，2147483647) | (0，4294967295) | 大整数值 |
| BIGINT | 8bytes | (-2^63，2^63-1) | (0，2^64-1) | 极大整数值 |
| FLOAT | 4bytes | (-3.402823466 E+38，3.402823466351 E+38) | 0 和 (1.175494351 E-38，3.402823466 E+38) | 单精度浮点数值 |
| DOUBLE | 8bytes | (-1.7976931348623157 E+308，1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308) | 双精度浮点数值 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
2). 字符串类型
| 类型 | 大小 | 描述 |
| CHAR | 0-255 bytes | 定长字符串(需要指定长度) |
| VARCHAR | 0-65535 bytes | 变长字符串(需要指定长度) |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
3). 日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
2.3DML语句
Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。
- 添加数据（INSERT）
- 修改数据（UPDATE）
- 删除数据（DELETE）
2.3.1添加数据
向指定字段添加数据
insert into 表名 (字段名1, 字段名2) values (值1, 值2);
insert操作的注意事项：
- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小，应该在字段的规定范围内。
2.3.2修改(update)
update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
- 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
- 在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。
2.3.4删除(delete)
delete from 表名 [where 条件]
- DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
- DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
- 当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击Execute即可。
2.4DQL语句
Data Query Language(数据查询语言)，用来查询数据库表中的记录。
查询关键字：SELECT
查询操作是所有SQL语句当中最为常见，也是最为重要的操作。在一个正常的业务系统中，查询操作的使用频次是要远高于增删改操作的。当我们打开某个网站或APP所看到的展示信息，都是通过从数据库中查询得到的，而在这个查询过程中，还会涉及到条件、排序、分页等操作。
语法结构
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
2.4.1基本查询
在基本查询的DQL语句中，不带任何的查询条件。
语法如下：
- 查询多个字段
select 字段1, 字段2, 字段3 from 表名;
- 查询所有字段（通配符）
select * from 表名;
- 设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
- 去除重复记录
select distinct 字段列表 from 表名;
2.4.2条件查询
语法：
select 字段列表 from 表名 where 条件列表 ; -- 条件列表：意味着可以有多个条件
学习条件查询就是学习条件的构建方式，而在SQL语句当中构造条件的运算符分为两类：
- 比较运算符
- 逻辑运算符
常用的比较运算符如下:
| 比较运算符 | 功能 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between ... and ... | 在某个范围之内(含最小、最大值) |
| in(...) | 在in之后的列表中的值，多选一 |
| like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
| is null | 是null |
常用的逻辑运算符如下:
| 逻辑运算符 | 功能 |
| and 或 && | 并且 (多个条件同时成立) |
| or 或 || | 或者 (多个条件任意一个成立) |
| not 或 ! | 非 , 不是 |
2.4.3聚合函数
之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）
常用聚合函数：
| 函数 | 功能 |
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
注意 : 聚合函数会忽略空值，对NULL值不作为统计。
- count ：按照列去统计有多少行数据。 在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。
- sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0
- max ：计算指定列的最大值
- min ：计算指定列的最小值
- avg ：计算指定列的平均值
2.4.4分组查询
分组： 按照某一列或者某几列，把相同的数据进行合并输出。
分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。
分组查询通常会使用聚合函数进行计算。
语法：
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
- 执行顺序：where > 聚合函数 > having
where与having区别（面试题）
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
2.4.5排查查询
排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。
语法：
select 字段列表 from 表名 [where 条件列表] [group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;
- 排序方式：
- ASC ：升序（默认值）
- DESC：降序
注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
2.4.6分页查询
分页操作在业务系统开发时，也是非常常见的一个功能，日常我们在网站中看到的各种各样的分页条，后台也都需要借助于数据库的分页操作。
分页查询语法：
select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
- 起始索引从0开始。 计算公式 ：起始索引 = （查询页码 - 1）* 每页显示记录数
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数
3.图形化工具
我使用的是DataGrip（Idea中集成了DataGrip，可以不用单独安装)
