前言 Java后端技术点 StringUtils常用API 学习时笔记(Linux) 学习时笔记(Git) 学习时笔记(Docker) 学习时笔记(Vue2+3) 面试宝典学习时笔记(JavaScript) 学习时笔记(JavaWeb) 1.初识前端 2.JavaScript-vue 3.Vue&Element 4.Maven-SpringBootWeb 5.SpringBootWeb请求响应 6.MySQL 7.MySQL查询 8.MySQL多表查询-MyBatis入门 9.MyBatis 10.springBoot-案例 11.springBoot-案例 12.springBootWeb 登录认证 13.SpringBoot AOP 14.SpringBoot原理 15.Maven进阶学习时笔记(Java) 学习时笔记(Python)

1.数据库操作-DQL

DQL全称为 Data Query Language（数据查询语言），用来查询数据库表中的记录
关键字：Select

2.1、基本查询

查询多个字段
```
select 字段1,字段2... from 表名;
```
查询所有字段（通配符）
```
select * from 表名
```

设置别名

select 字段1 as 别名1,字段2 as 别名2 from 表名;

去除重复记录

select distinct 字段列表 from 表名

练习

# 1.查询字段 name,entrydate
select name,entrydate from tb_emp;
# 2.查询返回所有字段
select * from tb_emp;
# 3.查询所有员工的name,entrydate，并起别名（姓名、入职日期）
select name as 姓名,entrydate as 入职日期 from tb_emp;
# 4.查询已有的员工关联了哪几种职位
select distinct job from tb_emp;

通配符代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）

2.2、条件查询

语法

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

运算符

练习题

# 1.查询 姓名为杨逍的员工
select * from tb_emp where name = '杨逍';
#2.查询id小于5的员工信息
select * from tb_emp where id <= 5;
#3.查询没有分配职位的员工信息
select * from tb_emp where job is null;
#4.查询有职位的员工信息
select * from tb_emp where job is not null;
#5.查询密码不等于123456的员工
select * from tb_emp where password != '123456';

select * from tb_emp where password <> '123456';
#6.查询入职日期在'2000-01-01'包含 到'2010-01-01'（包含）之间的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

select * from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';
#7. 查询入职日期在 '2000-01-01'（包含） 到 '2010-01-01'（包含）之间且性别为女的员工信息
select * from tb_emp where gender = 2 and entrydate between '2000-01-01' and '2010-01-01';
#8.查询职位是2，3，4的员工信息
select * from tb_emp where job in (2,3,4);

select * from tb_emp where job = 2 or job = 3 or job = 4;
#9.查询姓名为两个字的员工
select * from tb_emp where name like '__';
#10.查询姓'张'的员工信息
select * from tb_emp where name like '张%';

2.3、分组查询

聚合函数
- 将一列数据作为一个整体，进行纵向计算

语法

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

函数

练习

#1.统计该企业员工数量
#A count（字段）
select count(id) from tb_emp;
#B.count （常量）
select count(2) from tb_emp;
#C.count(*)
select count(*) from tb_emp;
#2.统计该企业最早如期的员工
select min(entrydate) from tb_emp;
#3.统计该企业最迟入职的员工
select max(entrydate) from tb_emp;
#4.统计该企业员工ID平均值
select avg(id) from tb_emp;
#5.统计员工ID之和
select sum(id) from tb_emp;

【注意事项】
- null值不参与所有聚合函数运算
- 统计数量可以使用：count(*)、count(字段)、count(常量)，推荐通配符

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where 与 having区别
- 执行时机不同，where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤
- 判断条件不同，where不能对聚合函数进行判断，而having可以
练习

#1.根据性别分组，统计男性和女性员工的数量
select gender,count(gender) from tb_emp group by gender;
#2.先查询入职时间在'2015-01-01'（包含）以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*)>=2;

【注意事项】
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
- 执行顺序：where > 聚合函数 > having

2.4、排序查询

排序

select 字段列表 from 表名 [where 条件] [group by 分组字段] order by 字段1 排序方式1,字段2 排序方式2...;

排序方式
- ASC：升序（默认值）不写则为升序
- DESC：降序
练习

#1.根据入职时间，对员工进行升序排序
select * from tb_emp order by entrydate ASC;  #升序为默认排序方式 asc可以省略
#2.根据入职日期，对员工进行降序排序
select * from tb_emp order by  entrydate DESC;
#3.根据入职时间对公司的员工进行升序，入职时间相同，再按照更新时间进行降序
select * from tb_emp order by entrydate ASC,update_time DESC;

【注意事项】
- 如果是多字段排序，当第一个字段值相同时，才会根据第二字段进行排序

2.5、分页查询

语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

练习

#1.从其实索引0开始查询员工数据，每页展示5条
select * from tb_emp limit 0,5;
#2.查询第一页员工数据，每页展示五条
select * from tb_emp limit 0,5;
#3.查询第二页员工数据，每页展示5条
select * from tb_emp limit 5,5;
#4.查询第三页员工数据，每页展示5条
select * from tb_emp limit 10,5;

select * from tb_emp limit 5*（页数——1),5

【注意事项】
- 其实索引从0开始，其实索引 = (查询页码 - 1) * 每页记录数
- 分组查询是数据库的方言，不同的数据库有不同的实现
- 如果查询的是第一页数据，起始索引可以省略

函数

if (表达式,true,false)

case 字段名 when 值1 then result [when 值2 then result2...] [else result] end;

案例

-- 案例：按需求完成员工管理的条件分页查询，  根据输入条件，查询第一页数据，每页展示10条记录
-- 输入条件：姓名 张，性别 男，入职日期 2000-01-01 - 2015-12-31
select * from  tb_emp where name like '%张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31'
order by update_time DESC limit 0,10;

案例

-- 统计出男性和女性 分别有多少人，并且添加别名
-- 数据库的性别为1 和 2 来标识性别，使用if 函数
select if(gender=1,'男','女') 性别,count(*) from tb_emp group by gender;

案例

-- 统计出员工职位，并统计在该职位有多少人
-- 数据库的职位为1 班主任，2 讲师，3 学工主管，4 教研主管 使用case when 函数
select
    case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '没有分配职位' end
    as 职位,
    count(*) from tb_emp group by job;

2.多表设计

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互练习，所以各个表格之间也存在着各种练习，基本上分为三种：
- 一对多（多对一）
- 多对多
- 一对一

2.1、一对多

需求
- 根据页面原型及需求文档，完成部门及员工模块的表结构设计
员工

create table tb_emp (
  id int unsigned primary key auto_increment comment 'ID',
  username varchar(20) not null unique comment '用户名',
  password varchar(32) default '123456' comment '密码',
  name varchar(10) not null comment '姓名',
  gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
  image varchar(300) comment '图像',
  job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
  entrydate date comment '入职时间',
  dept_id int unsigned comment '部门ID',
  create_time datetime not null comment '创建时间',
  update_time datetime not null comment '修改时间'
) comment '员工表';

部门

-- 部门管理
create table tb_dept(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '部门表';

一对多关系实现：在数据库中多的以防，添加字段，来关联一的一方主键

现象
- 部门数据可以直接删除，然而还有部分员工归属于该部门下，此时出现了数据不完整、不一致问题
分析
- 上述两张表，在数据库层面，并未建立关联，所以无法保证数据的一致性和完整性——外键

外键约束

语法

创建表时指定
create table 表名(
    字段名 数据类型,
    ...,
    [constraint] [外键名称] foreign key(外键字段名) reference 主表(字段名)
)
建表后，添加外键
alert table 表名 add contraint 外键名称 foreign key(外键字段名) reference 主表(字段名);

图形设置外键

物理外键
- 使用 foreign key 定义外键关联另一张表
- 缺点
  - 影响增、删、改的效率（需要检查外键关系）
  - 仅用于单节点数据库，不适用于分布式，集群场景
  - 容易引发数据库的思索问题，消耗性能
逻辑外键【推荐方式】
- 在业务层逻辑中，解决外键关联
- 通过逻辑外键，就可以很方便解决杉树问题

2.2、一对一

案例：用户与身份证信息的关系
关系
- 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

2.3、多对多

案例：学生与课程的关系
关系
- 一个学生可以选多门课，一个课程也可以供多个学生选择

2.4、案例

参考资料 “苍穹外卖管理后台”，设计分类管理、菜品管理、套餐管理模块的表结构

-- 瑞吉点餐页面原型 , 设计表结构
-- 分类表
create table category(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(20) not null unique comment '分类名称',
    type tinyint unsigned not null comment '类型 1 菜品分类 2 套餐分类',
    sort tinyint unsigned not null comment '顺序',
    status tinyint unsigned not null default 0 comment '状态 0 禁用，1 启用',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
) comment '菜品及套餐分类' ;

-- 菜品表
create table dish(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(20) not null unique comment '菜品名称',
    category_id int unsigned not null comment '菜品分类ID',
    price decimal(8, 2) not null comment '菜品价格',
    image varchar(300) not null comment '菜品图片',
    description varchar(200) comment '描述信息',
    status tinyint unsigned not null default 0 comment '状态, 0 停售 1 起售',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
) comment '菜品';

-- 套餐表
create table setmeal(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(20) not null unique comment '套餐名称',
    category_id int unsigned not null comment '分类id',
    price decimal(8, 2) not null comment '套餐价格',
    image varchar(300) not null comment '图片',
    description varchar(200) comment '描述信息',
    status tinyint unsigned not null default 0 comment '状态 0:停用 1:启用',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
)comment '套餐' ;


-- 套餐菜品关联表
create table setmeal_dish(
    id int unsigned primary key auto_increment comment '主键ID',
    setmeal_id int unsigned not null comment '套餐id ',
    dish_id int unsigned not null comment '菜品id',
    copies tinyint unsigned not null comment '份数'
)comment '套餐菜品关系';

马浩楠的技术博客

1.数据库操作-DQL

2.1、基本查询

2.2、条件查询

2.3、分组查询

2.4、排序查询

2.5、分页查询

2.多表设计

2.1、一对多

2.2、一对一

2.3、多对多

2.4、案例

results matching ""

No results matching ""