JavaWeb学习第六天-数据库-MySQL基础

---

前言

这是javaweb第六天的学习内容，主要在于复习MySQL数据库的使用，时间久了，知识遗忘的比较厉害，仅供个人学习使用，如果也对你有所帮助，小子深感荣幸。

---

一、SQL语句及其相关

带一句：MySQL数据库是一种关系型数据库，即是由多张互相连接的二维表组成的数据库。

1.SQL语句分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

tips：MySQL数据库的SQL语句不区分大小写。

2.数据定义语言DDL

这里的DDL是从一个较为宏观的角度上去进行操作的，操作对象是数据库或者表的结构，而并非其中所存储的具体的数据。

2.1 操作数据库

常见操作：查询（数据库）、创建、使用、删除

2.1.1 查询数据库

查询所有数据库：

show databases;

查询当前正在使用的数据库：

select database();

如果当前没有正在使用某一个数据库，则会返回null（如果希望使用某个数据库的话，需要通过 “use 数据库名称”命令，见下文）

2.1.2 创建数据库

create database [if not exists] 数据库名;	//[]中的if not exists为可选项，若指令中给出则会先判断数据库是否存在，如果不存在创建成功，否则创建失败。

这里我的系统中已经存在了一个名称为db01的数据库，通过控制台信息可以看到，我尝试再一次创建一个名称为db01数据库的操作，但是被驳回了。
tips：不管加不加if not exists，系统都不允许再创建一个名称已经存在的数据库，这个操作都会被驳回。添加后报的是警告，不添加报的是错误。

2.1.3 使用数据库

use 数据库名;	//或者是切换数据库，use操作指向的数据库就是select database（）命令的结果。

2.1.4 删除数据库

drop database [if exists] 数据库名;	//同理创建，判断是否存在？存在则删除，否则驳回操作。

2.2 操作表

了解，大部分可以使用图形化工具操作

2.2.1 创建表

2.2.1.1 语法

create table  表名(
	字段1  字段1类型 [约束]  [comment  字段1注释 ],	//注释部分使用单引号括起来
	字段2  字段2类型 [约束]  [comment  字段2注释 ],
	......
	字段n  字段n类型 [约束]  [comment  字段n注释 ] 	//这里注意最后一个字段的定义部分后是没有逗号的
) [ comment  表注释 ] ;

//示例----------------------------------------------------
create table tb_user(
    id int comment 'ID',
    username varchar(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
)comment '用户表'

目标表创建成功。

2.2.1.2 约束

所谓约束其实就是指在表格中某个字段上所设置的规则，用于对存储在表格中该字段的数据做出限制。

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

按照这些约束，给出使用示例（每个字段可以添加多个约束，且各个约束的定义之间无需使用符号连接）

create table tb_user (
    id int primary key comment 'ID', 	//主键，非空且唯一
    username varchar(20) not null unique comment '用户名',	//非空＋唯一
    name varchar(10) not null comment '姓名',	//非空
    age int comment '年龄',	//无约束
    gender char(1) default '男' comment '性别'	//默认值为男
) comment '用户表';

tips：关键字：auto_increment (自动增长)
具有auto_increment关键字进行表示的字段是一个从1开始自增的序列，即首条记录的该字段值为1，没增加一条记录该字段的值便会自动加一。

//对id字段的定义进行修改
id int primary key auto_increment comment 'ID'

tips：被auto_increment标记的字段，在删除记录时并不会导致其他记录的该字段值减小，不要臆测哈

2.2.1.3 数据类型

在MySQL中，数据主要可以划分为三大类：数值类型，字符串类型和日期时间类型

以下为个人总结的一些在学习过程常见的数据类型，顺序依次对照三大类（）：

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
TINYINT	1byte	(-128，127)	(0，255)	小整数值
INT/INTEGER	4bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
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(标度)的值	小数值(精确定点数)

关于DECIMAL类型使用：

decimal是MySQL中存在的精准数据类型，语法格式“DECIMAL(M,D)”。

M是数字的最大数（精度），其范围为“1～65”，默认值是10；
D是小数点右侧数字的数目（标度），其范围是“0～30”，但不得超过M。

类型说明 取值范围（MySQL < 3.23） 取值范围（MySQL >= 3.23）

DECIMAL(4,1) -9.9 到 99.9 -999.9 到 9999.9

DECIMAL(5,1) -99.9 到 999.9 -9999.9 到 99999.9

DECIMAL(6,1) -999.9 到 9999.9 -99999.9 到 999999.9

DECIMAL(6,2) -99.99 到 999.99 -9999.99 到 99999.99

DECIMAL(6,3) -9.999 到 99.999 -999.999 到 9999.999

DECIMAL类型使用部分转载自博客链接：https://blog.csdn.net/weixin_46334815/article/details/126994575

类型	大小	描述
CHAR	0-255 bytes	定长字符串(需要指定长度)
VARCHAR	0-65535 bytes	变长字符串(需要指定长度)

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值

2.2.2 查询表

查询当前数据库所有表：

show tables;

查看指定表结构：

desc 表名;

查询指定表的建表语句：

show create table 表名;

2.2.3 修改表

添加字段：

alter table 表名 add  字段名  类型(长度)  [comment 注释]  [约束];

修改数据类型：

alter table 表名 modify  字段名  新数据类型(长度);

alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];

删除字段：

alter table 表名 drop 字段名;

修改表名：

rename table 表名 to  新表名;

2.2.4 删除表

drop  table [ if exists ]  表名;

3.数据库操作语言DML（重点）

DML主要用于对数据库表中的记录进行增删改操作，在Web开发三层结构中的Dao层，需要使用DML对数据库进行相应的操作从而获取数据返回Service层。

3.1 添加数据-insert

//向指定字段添加数据
insert into 表名 （字段名1,字段名2,...）	values (值1,值2,...);
//向全部字段添加数据，其实就是插入一条记录
insert into 表名 values values (值1,值2,...);
//批量添加多条记录
insert into 表名 values (值1,值2,...),(值1,值2,...),...;

3.2 修改数据-update

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
//如果不加where条件，则会将表中所有记录的对应字段值全部修改

3.3 删除数据-delete

delete from 表名 [where 条件];
//没有where则会直接删除整张表中的数据

4.数据库查询语言-DQL（重点）

在实际开发中，Dao层对数据库的操作实际上基本都是和查询语句一起执行的，即先查询到目标记录，然后再对目标记录进行字段值的修改。

4.1 SQL语句的通用结构

SELECT
	字段列表
FROM
	表名列表
//条件查询
WHERE
	条件列表
//分组查询
GROUP  BY
	分组字段列表
HAVING
	分组后条件列表
//排序查询
ORDER BY
	排序字段列表
//分页查询
LIMIT
	分页参数

4.2 基本查询

//查询多个字段
select 字段1, 字段2, 字段3 from  表名;
//查询所有字段
select *  from  表名;
//设置别名	
//如果别名中含有特殊字符需要使用''或者""包涵。
//（查询结果将会以别名作为字段名进行展示）
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;
//去除查询结果中的重复数据（对表数据并不会造成影响）
select distinct 字段1,字段2,... from 表名;

4.3 条件查询

select 字段列表 from 表名 where 条件列表;

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between … and …	在某个范围之内(含最小、最大值)
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意数量个字符)
is null	是null

逻辑运算符主要和比较运算符一起使用从而形成更加复杂的查询条件

逻辑运算符	功能
and 或 &&	并且 (多个条件同时成立)
or 或 ||	或者 (多个条件任意一个成立)
not 或 !	非 , 不是

4.4 聚合函数

在此之前所提到的查询操作都是以行或者记录为单位进行查询的，但是使用聚合函数进行查询是以列或者字段作为单位来进行操作的，聚合函数会对一列的数据值进行计算，然后返回一个结果值。

select 聚合函数(字段列表) from 表名;

tips： 聚合函数会忽略空值，对null值不做统计。

常用聚合函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

4.5 分组查询

分组：按照某一列或者某几列，把相同的数据进行合并输出。

1. 分组其实就是按列进行分类（指定列下相同的数据归为一类），然后可以对分类完的数据进行合并计算。
2. 分组查询通常会使用聚合函数进行计算。

select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];
//执行顺序：where > group by > having
//tips:分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义(因为分组字段各个组件是相同的)

4.6 排序查询

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;	//排序方式分为：ASC：升序（默认），DESC：降序
//order by给入给入字段的排序优先级由由前向后依次降低。

4.7 分页查询

select  字段列表  from   表名  limit  起始索引, 查询记录数 ;
//从其实索引开始，向后展示对应条数索引

在SQL语句中可以加入if或者case语句来进行操作，操作形式需要注意，和java有所区别。

-- if(条件表达式, true取值 , false取值)
select if(gender=1,'男性员工','女性员工') AS 性别, count(*) AS 人数
from tb_emp
group by gender;

-------------------------------------------------------------------

-- case 表达式 when 值1 then 结果1  when 值2  then  结果2 ...  else  result  end
select (case job
             when 1 then '班主任'	//按照job字段进行分组，如果字段值为1则显示班主任
             when 2 then '讲师'
             when 3 then '学工主管'
             when 4 then '教研主管'
             else '未分配职位'
        end) AS 职位 ,
       count(*) AS 人数
from tb_emp
group by job;

二、数据库的多表设计

在实际的业务开发中，各个业务之间项目关联，所以各个表结构之间也会存在关联，主要分为三种

1. 一对多
2. 多对多
3. 一对一

1.一对多

所谓一对多的表间关系，其实是指A表中的多条记录中的某个字段对应B表中的主键字段。例如，A表是学生表（表中含有一个班级字段），B表是班级表（主键是班级字段）。每个学生记录中都会包含一个班级字段值，而班级表中的每一个班级字段值在学生表中都可能会对应多条记录。

对于“一对多”中的“一”所在表我们称为父表，“多”所在表我们称为子表。一般对于这种关系会经常使用外键（Foreign Key）的方式来进行设置，在子表中设置外键（“多”的一方），这样就实现了父表和子表之间的一种关联关系，例如我希望删除父表中的某条记录，会首先查询子表中是否存在相应的关联记录，如果存在，则父表中的删除操作会被驳回。

//sql语句可以简单了解一下，一般会使用图形化界面进行操作

-- 创建表时指定
create table 表名(
	字段名    数据类型,
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (主表列名)	
);


-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key(外键字段名) references 主表(主表列名);

上述这种实实在在存在于数据库层面的外键关系，我们称为物理外键。

物理外键和逻辑外键：

• 物理外键

  • 概念：使用foreign key定义外键关联另外一张表。
  • 缺点：
    • 影响增、删、改的效率（需要检查外键关系）。
    • 仅用于单节点数据库，不适用与分布式、集群场景。
    • 容易引发数据库的死锁问题，消耗性能。

• 逻辑外键

  • 概念：在业务层逻辑中，解决外键关联。
  • 通过逻辑外键，就可以很方便的解决上述问题。

在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。 甚至在一些数据库开发规范中，会明确指出禁止使用物理外键 foreign key

2.一对一

可以看作为一种特殊的一对多，所以在处理方法上只需选中一张表然后添加上与另一张表的外键联系即可。但由于是一对一关系，所以父表中的主键字段应该还添加上unique约束，避免该字段出现多个重复值。

3.多对多

解决方法一般是创建一张中间表。中间表至少包涵两个外键，分别关联两方主键。（当然，多对多关系和一对一关系中的这种外键也应该尽可能的使用逻辑外键来进行实现）

总结

本文主要记录了MySQL入门的基础操作等知识，主要用于个人学习，如果存在错误还请大佬指正，小子拜谢。共勉。