目录
系列文章
Java Web开发_异步处理以及前端中Vue框架的简单使用（Day3）
Java Web开发_Maven以及SpringBootWeb入门（Day4）
前言
此博客主要记录在学习黑马程序员2023版JavaWeb开发课程的一些笔记，方便总结以及复习。
一、DQL
书接上文，继续数据库的操作——SQL语句——DQL：lData Query Language(数据查询语言)，用来查询数据库表中的记录。关键字：SELECT。
基本查询
查询多个字段：select 字段1, 字段2, 字段3 from 表名;
查询所有字段（通配符）：select * from 表名;
设置别名：select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
去除重复记录：select distinct 字段列表 from 表名;
代码实例操作：
-- 查询指定字段
select name,entrydate
from tb_emp;
-- 查询返回所有字段
-- 推荐
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp;
-- 不推荐，不直观。性能低
select *
from tb_emp;
-- 查询所有员工的name，entrydate，并起别名（姓名，入职日期）
select name as 姓名,entrydate as 入职日期
from tb_emp;
-- as可省略
select name 姓名,entrydate 入职日期
from tb_emp;
-- 查询已有的员工对应了哪几种职位（不要重复）
select distinct job
from tb_emp;条件查询
条件查询： select 字段列表 from 表名 where 条件列表 ;
在条件列表中我们通常会引用到一些运算符供给筛选：
| 比较运算符 | 功能 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between ... and ... | 在某个范围之内(含最小、最大值) |
| in(...) | 在in之后的列表中的值，多选一 |
| like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
| is null | 是null |
| 逻辑运算符 | 功能 |
| and 或 && | 并且 (多个条件同时成立) |
| or 或 || | 或者 (多个条件任意一个成立) |
| not 或 ! | 非 , 不是 |
代码实例操作：
-- 查询姓名为。。的员工
select *
from tb_emp where name = '杨逍';
-- 查询 id小于等于5 的员工信息
select *
from tb_emp where id <=5;
-- 查询没有分配职位的员工信息
select *
from tb_emp where job is null ;
-- 查询有分配职位的员工信息
select *
from tb_emp where job is not null ;
-- 查询密码不等于'123456'的员工信息
select *
from tb_emp where password != '123456';
-- 另一种写法 用<>代替!=
select *
from tb_emp where password <> '123456';
-- 查询入职日期在某个范围
select *
from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';
select *
from tb_emp where entrydate between '2000-01-01' and '2010-01-01';
-- 查询职位是2、3、4的员工信息
select *
from tb_emp where job = 2 or job = 3 or job = 4;
select *
from tb_emp where job in (2,3,4);
-- 查询姓名是两个字的员工信息
select *
from tb_emp where name like '__';
-- 查询姓名是姓“张”的员工信息
select *
from tb_emp where name like '张%';分组查询和聚合函数
分组查询：select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
其中，where和having的执行时机以及判断条件也有所不同：
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
需要注意的是：
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
- 执行顺序: where > 聚合函数 > having 。
那在这里提到的聚合函数又是什么呢？
聚合函数：将一列数据作为一个整体，进行纵向计算。
select 聚合函数(字段列表) from 表名 ;
常见的聚合函数一般有：
| 函数 | 功能 |
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
计算过程当中，null值不参与所有聚合函数运算。统计数量可以使用：count(*)，count(字段)，count(常量)，推荐使用count(*)，因为count(*)有特别的优化过。
代码实例操作：
-- 聚合函数：不对NULL值进行运算
-- 统计员工数量
-- 1、count（字段）
select count(id)
from tb_emp;
-- 2、count（常量） -- 指所有
select count(0)
from tb_emp;
-- 3、count（*） -- 推荐
select count(*)
from tb_emp;
-- 统计最早入职的员工
select min(entrydate)
from tb_emp;
-- 统计最迟入职的员工
select max(entrydate)
from tb_emp;
-- 统计id平均值
select avg(id)
from tb_emp;
-- 统计id总量
select sum(id)
from tb_emp;
-- 分组
-- 1、根据性别分组
select gender,count(*)
from tb_emp group by gender;
-- 2、查询入职时间在2015-01-04以前的员工，并对职位分组获得员工数量大于等于2的职位
select job,count(*)
from tb_emp where entrydate <= '2015-01-04' group by job having count(*) >=2;排序查询
条件查询：select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2
排序的方式分为两种：一、ASC：升序（默认值）；二、DESC：降序。
当多字段排序时，当第一个字段值相同时，才会根据第二个字段进行排序。同理，当第二个字段值相同时，才会根据第三个字段进行排序。
代码实例操作：
-- 排序查询
-- 1、根据入职时间，对员工进行升序排序 -asc(默认，即可省略)
select *
from tb_emp order by entrydate asc ;
-- 2、根据入职时间，对员工进行升序排序 -asc(默认，即可省略)
select *
from tb_emp order by entrydate desc ;
-- 3、根据入职时间 对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序
select *
from tb_emp order by entrydate , update_time desc ;分页查询
分页查询：select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
其实就是查询从起始索引开始的一共查询记录数个数。
- 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
代码实例操作：
-- 分页查询
-- 1、从起始索引0开始查询员工数据，每页展示5条数据
select *
from tb_emp limit 0,5;
-- 2、查询第一页开始查询员工数据，每页展示5条数据
select *
from tb_emp limit 0,5;
select *
from tb_emp limit 5;
-- 3、查询第2页开始查询员工数据，每页展示5条数据
select *
from tb_emp limit 5,5;
-- 3、查询第3页开始查询员工数据，每页展示5条数据
select *
from tb_emp limit 10,5;
-- 起始索引 = （页码减一）* 每页展示记录数
另外是根据需求可能会需要完成的员工性别统计和职位统计。
-- 性别信息的统计
select if(gender=1, '男性员工', '女性员工') 性别, count(*) 人数
from tb_emp group by gender;
-- 职位信息的统计
select job, count(*)
from tb_emp group by job;
select
(case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教学主管' else '未分配职位' end ) 职位,
count(*)
from tb_emp group by job;
其中提到了if和case的相关函数应用：
if(表达式, tvalue, fvalue)：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
case expr when value1 then result1 [when value2 then value2 ...] [else result] end
二、多表设计
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多(多对一)、多对多和一对一。
一对多（多对一）
这个内容很好理解，当我们阅读页面原型和需求文档的时候，经常会发现不同模块的表结构不是一一对应的关系，例如部门与员工模块的表结构设计。一个部门可以有多个员工，但是一个员工只属于一个部门。那我们在表结构设计的时候怎么去实现呢？通常来说，我们会在数据库表中多的一方，添加字段，来关联一的一方的主键。单纯的认为这两个数据关联的话只是人为的认知，删除部门数据的时候另一个表中的部分员工还是归属于那个被删除的部门当中，此时就出现了数据的不完整、不一致问题。要让数据库明白这个关系，就需要引入一个外键来保证数据的一致性和完整性的。我们可以在表的创建的时候便指定好外键，也可以在建完表后再添加外键：
-- 创建表时指定
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);
-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
这种约束方式我们称为物理外键：使用 foreign key 定义外键关联另外一张表。
缺点也很明显：
- 1、影响增、删、改的效率（需要检查外键关系）。
- 2、仅用于单节点数据库，不适用与分布式、集群场景。
- 3、容易引发数据库的死锁问题，消耗性能。
我们实际开发当中的话更推荐使用逻辑外键进行约束，即在业务层逻辑中，解决外键关联。通过逻辑外键，就可以很方便的解决上述问题。
一对一
一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。比如说我们用户的信息，一些基本信息像电话号码、学历、姓名等会更常调用到，而身份信息、住址信息等可能相对来说会少很多。那我们可以将用户信息表分成用户基本信息表和用户身份信息表，再将他们关联起来。任意一方，添加外键，关联另外一方的主键。
代码实例操作：
-- ===========================================一对一=====================================
create table tb_user(
id int unsigned primary key auto_increment comment 'ID',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1 男 2 女',
phone char(11) comment '手机号',
degree varchar(10) comment '学历'
) comment '用户信息表';
insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'),
(2,'青翼蝠王',1,'18812340002','大专'),
(3,'金毛狮王',1,'18812340003','初中'),
(4,'紫衫龙王',2,'18812340004','硕士');
create table tb_user_card(
id int unsigned primary key auto_increment comment 'ID',
nationality varchar(10) not null comment '民族',
birthday date not null comment '生日',
idcard char(18) not null comment '身份证号',
issued varchar(20) not null comment '签发机关',
expire_begin date not null comment '有效期限-开始',
expire_end date comment '有效期限-结束',
user_id int unsigned not null unique comment '用户ID',
constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户信息表';
insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),
(2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
(3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),
(4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);多对多
在两张表中，关系可以相互对应，那我没有办法去直接用一个外键关联另外一个表的结构，那我们就可以再新建一个表来关联他们，通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键。比如说学生选课的情况：一个学生可以选修多门课程，一门课程也可以供多个学生选择。这个时候我们就可以新建一个学生课程关系表，更清晰地把学生的选课情况体现出来。
代码实例操作：
-- ======================================多对多=============================
create table tb_student(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');
create table tb_course(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');
create table tb_student_course(
id int auto_increment comment '主键' primary key,
student_id int not null comment '学生ID',
course_id int not null comment '课程ID',
constraint fk_courseid foreign key (course_id) references tb_course (id),
constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生课程中间表';
insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);通过可视化也能更好地反映建表的一目明了：
小结
数据查询语言（DQL）会更倾向于我们的后端开发工程，在了解了具体的SQL语句之后我们连接数据库之后也能更好地进行增删查改等功能，学习完多表设计的内容，我认为在项目开发中，在进行数据库表结构设计时，也能更好去设计一个无冲突，整洁，完善的表结构。
2708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
