案例来源:黑马javaweb课程
单表查询
1、查询符合条件的员工
表–tb_emp
name like ‘%张%’ 模糊匹配,只要含有’张’
between and 范围查询
order by 对查询结果排序,默认升序,desc是降序
limit 0,10 分页查询,0是起始索引,10是每页展示的记录数
Select *
from tb_emp
where name like '%张%'
and gender=1
and enterdate between '2000-01-01' and '2015-12-31'
order by updatetime desc
limit 0,10;
查询第二页,将limit的起始索引改为10
Select *
from tb_emp
where name like '%张%'
and gender=1
and enterdate between '2000-01-01' and '2015-12-31'
order by updatetime desc
limit 10,10;
2、从表中查询到数据返回给前端就行
分组查询:分组查询之后,查询的字段一般为分组字段和聚合函数。
要对gender做处理再返回,gender取值为1或2,用if(条件表达式,true取值,false取值)
,后面跟着性别,意思是取别名。
select if(gender = 1, '男性员工' , '女性员工') 性别,count(*) form tb_emp group by gender;
员工有四种职位:班主任,讲师,学工主管,教研主管
流程控制语句:case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ...
end
select
(case job when 1 then '班主任' when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) 职位
count(*)
form tb_emp
group by job;
多表查询
案例来源:黑马javaweb课程
(1)
select d.name,d.price,c.name
from dish d,category c
where d.category_id = c.id and d.price<10;
(2)括号里是即使菜品没有分类,也需要将菜品查询出来,用左外连接,左边是dish表,on后面跟的是连接条件
select d.name,d.price,c.name
from dish d
left join category c
on d.category_id = c.id
where d.price between 10 and 50 and d.status=1
(3)要求每个分类下价格最贵的菜品,就需要对数据按照菜品的分类名进行分组group by
select c.name,max(d.price)
from dish d,category c
where d.category_id = c.id
group by c.name;
(4)
select c.name,count(*)
from dish d,category c
where d.category_id = c.id and d.status=1
group by c.name
having count(*) >= 3;
(5)这里涉及到的套餐表和菜品表是多对多的关系,所以需要一张中间表来连接
select s.name,s.price,d.name,d.price,sd.copies
from setmeal s,setmeal_dish,sd,dish d
where s.id = sd.setmeal_id and d.id = sd.dish_id and s.name='商务套餐A';
(6)用的是子查询,先查出菜品价格的平均值,再查出低于平均价格的菜品
select *
from dish
where price<(select avg(price) from dish);
附:四张表的建立
-- 分类表
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 '套餐菜品中间表';