从DQL到多表设计

MySQL

DQL

基本查询

条件查询

​编辑 

聚合函数

 分组查询

排序查询 

分页查询 

 案例

多表设计

一对多

一对一

多对多

案例

DQL

基本查询

--  =================== 基本查询 ======================
-- 1. 查询指定字段 name,entrydate 并返回
select name, entrydate
from emp;


-- 2. 查询返回所有字段
-- 方式一:  推荐 , 效率高 . 更直观
select id,
       username,
       password,
       name,
       gender,
       image,
       job,
       entrydate,
       create_time,
       update_time
from emp;


-- 方式二:
select *
from emp;


-- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期)  --- as 关键字可以省略
select name as '姓名', entrydate as '入职日期'
from emp;

select name '姓名', entrydate '入职日期'
from emp;


-- 4. 查询员工有哪几种职位(不要重复) -- distinct
select distinct job
from emp;



select *
from emp
where id = 1;

注意事项
*号代表查询所有字段，在实际开发中尽量少用(不直观、影响效率)

条件查询

--  =================== 条件查询 ======================
-- 1. 查询 姓名 为 杨逍 的员工
select *
from emp
where name = '杨逍';

-- 2. 查询在 id小于等于5 的员工信息
select *
from emp
where id <= 5;

-- 3. 查询 没有分配职位 的员工信息  -- 判断 null , 用 is null
select *
from emp
where job is null;

-- 4. 查询 有职位 的员工信息  -- 判断 不是null , 用 is not null
select *
from emp
where job is not null;

-- 5. 查询 密码不等于 '123456' 的员工信息
select *
from emp
where password <> '123456';
select *
from emp
where password != '123456';

-- 6. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select *
from emp
where entrydate between '2000-01-01' and '2010-01-01';

-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select *
from emp
where (entrydate between '2000-01-01' and '2010-01-01')
  and gender = 2;

-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select *
from emp
where job = 2
   or job = 3
   or job = 4;

select *
from emp
where job in (2, 3, 4);

-- 9. 查询姓名为两个字的员工信息
select *
from emp
where name like '__';

-- 10. 查询姓 '张' 的员工信息  ---------> 张%
select *
from emp
where name like '张%';

-- 11. 查询姓名中包含 '三' 的员工信息
select *
from emp
where name like '%三%';

聚合函数

介绍:将一列数据作为一个整体，进行纵向计算。

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

-- 聚合函数

-- 1. 统计该企业员工数量 -- count
-- A. count(字段)
select count(id)
from emp;
select count(job)
from emp;
-- null值不参与聚合函数运算

-- B. count(*)
select count(*)
from emp;

-- C. count(值)
select count(1)
from emp;

-- 2. 统计该企业员工 ID 的平均值
select avg(id)
from emp;

-- 3. 统计该企业最早入职的员工的入职日期
select min(entrydate)
from emp;

-- 4. 统计该企业最近入职的员工的入职日期
select max(entrydate)
from emp;

-- 5. 统计该企业员工的 ID 之和
select sum(id)
from emp;

注意事项
null值不参与所有聚合函数运算。
统计数量可以使用:count(*) count(字段) count(常量)，推荐使用count(*)。

 分组查询

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

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

-- 分组
-- 1. 根据性别分组 , 统计男性和女性员工的数量  -- count
select gender, count(*)
from emp
group by gender;

-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 -- count
select job, count(*)
from emp
where entrydate <= '2015-01-01'
group by job
having count(*) >= 2;

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

排序查询 

语法
条件查询:select字段列表 from 表名[where 条件列表][groupby 分组字段]order by 字段1 排序方式1,字段2 排序方式2 ...;

 排序方式
ASC:升序(默认值)
DESC:降序

--  =================== 排序查询 ======================
-- 1. 根据入职时间, 对员工进行升序排序  -- 排序条件
select *
from emp
order by entrydate asc; -- 默认升序, asc可以省略的

select *
from emp
order by entrydate;

-- 2. 根据入职时间, 对员工进行降序排序
select *
from emp
order by entrydate desc;


-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select *
from emp
order by entrydate asc, id desc;

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

分页查询 

 语法
分页查询:select 字段列表 from 表名limit 起始索引,查询记录数 ;

--  =================== 分页查询 ======================
-- 1. 查询第1页员工数据, 每页展示10条记录
select *
from emp
limit 0,10;

select *
from emp
limit 10;


-- 2. 查询第2页员工数据, 每页展示10条记录
select *
from emp
limit 10,10;

-- 公式 : 页码 ---> 起始索引  ------->  起始索引 = (页码 - 1) * 每页记录数

注意事项
1.起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
2.分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

3.如果查询的是第一页数据，起始索引可以省略，直接简写为limit10。

 案例

-- 练习 : 员工管理列表查询 , 根据最后操作时间, 进行倒序排序
-- 条件 : name , gender , entrydate

select *
from emp
where name like '%张%'
  and gender = 1
  and entrydate between '2000-01-01' and '2010-01-01'
order by update_time desc
limit 0,10;


-- 男性与女性员工的人数统计 (1 : 男性员工 , 2 : 女性员工)
-- 函数: if(条件表达式 , t , f)
select if(gender = 1, '男性员工', '女性员工') '性别',
       count(*)                       '人数'
from emp
group by gender;


-- 员工职位信息 -- count
-- 函数: case when ... then ... when ... then ... else ... end
-- 函数: case ... when ... then ... when ... then ... else ... end
select (case
            when job = 1 then '班主任'
            when job = 2 then '讲师'
            when job = 3 then '教研主管'
            when job = 4 then '学工主管'
            else '无职位' end) '职位',
       count(*)
from emp
group by job;

select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '教研主管' when 4 then '学工主管' else '无职位' end) '职位',
       count(*)
from emp
group by job;

多表设计

一对多

一对一

-- 一对一: 用户 与 身份证
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);

案例

-- 案例
-- 1. 分类表category

-- 2. 菜品表 dish

-- 3. 套餐表 setmeal

-- 4. 套餐菜品关系表 setmeal_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 '套餐菜品关系';