MySQL多表查询与常见函数

-- 根据导入的jd_base数据库中的表进行作业编写
-- 需求1：统计jd的部门数量
    select count(id)  from tb_dept;
-- 需求2：统计每个部门的员工数量，展示部门ID、员工数量值
    select job, count(*) from tb_emp group by job;
-- 需求3：查询tb_emp表，统计入职距今天5年以上的男性员工，并按照入职时间进行降序排列。
    select  count(*) from  tb_emp where gender=1 and DATEDIFF(NOW(), entrydate) >= 1825 order by entrydate desc ;
-- 需求4：设计表关系：
-- 菜品分类表 tb_category(id,分类名称--不能重复,created_time)
-- 菜品表(id,菜品名称,菜品价格,所属分类,菜品图片路径,菜品上架时间)

create table tb_category(
        id int primary key auto_increment,
        name varchar(20),
        created_time datetime
);
create table tb_menu(
        id int primary key auto_increment,
        name varchar(20),
        price decimal(5,2),
        classify varchar(20),
        img varchar(300),
        create_time datetime
);
-- 创建中间表来创建关联
create table tb_category_menu(
        id int primary key auto_increment,
        category_id int,
        menu_id int
);
-- 建完表后，添加外键。
alter table tb_category_menu add constraint  fk_munu_id foreign key (category_id) references tb_menu(id);
alter table tb_category_menu add constraint  fk_category_id foreign key (category_id) references tb_menu(id);



-- 创建完成对表格，进行删除
DROP TABLE IF EXISTS `tb_category`;
DROP TABLE IF EXISTS `menu`;
DROP TABLE IF EXISTS `tb_category_menu`;




create table tb_user(
                            id int primary key auto_increment,
                            name varchar(20),
                            created_time datetime
);
insert into tb_user values (1,'zs',now()),(2,'lisi',now());


create table tb_order(
                        id int primary key auto_increment,
                        name varchar(20),
                        price decimal(5,2),
                        user_id int
);
select * from
 tb_emp,tb_dept
where tb_emp.id=tb_dept.id;

-- select * from tb_emp inner join tb_dept  on tb_emp.id =tb_dept.id;

select * from jd_orders order by jd_orders.member_id  desc ;
select * from jd_address ;

-- 查询会员的订单数据（），并安装会员的ID降序排序。
-- 1.表是哪几个？  2.如何拼接， 3 .如何查询，  4.如何排序
select * from jd_members m,jd_orders o
        where  m.id=o.member_id
order by m.id desc ;
-- 查询所有会员用户的默认地址
select *
from jd_members m inner  join jd_address a on  a.member_id=m.id
where a.`default`=1;
-- 左外连接：查询左表所有数据与B表中的交集部分数据。
-- 右外连接：查询右表所有数据与A表中交集部分的数据。
-- select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

-- 查询所有的用户，并关联他们的订单列表（如果没有订单，则不展示订单数据。）
select *
From jd_members m left join jd_address adr on m.id= adr.member_id;

-- 查询用户所购买的商品，（没有购买就只展示用户）
select  m.id,m.nickname,o.id,o.order_no,od.sku_id,od.sku_name,k.name,k.unit_price
from jd_members m
    left join  jd_orders o on m.id=o.member_id
    left join jd_order_detail od  on o.id=od.order_id
    left join jd_sku k on od.sku_id=k.id;

--  作业：统计用户所购买的每种商品的数量！没购买则显示0即可。 (每个人购买的商品的数量)
select  sum(od.num) ,m.nickname
from jd_members m
         left join  jd_orders o on m.id=o.member_id
         left join jd_order_detail od  on o.id=od.order_id
         left join jd_sku k on od.sku_id=k.id

group by m.id,od.sku_name;

-- 查询订单列表，关联每个订单的商品名称  展示订单号和商品名称。
select  o.order_no,od.sku_name
from jd_members m
         left join  jd_orders o on m.id=o.member_id
         left join jd_order_detail od  on o.id=od.order_id
         left join jd_sku k on od.sku_id=k.id;

-- 左外连接：
-- 查询所有的部门以及每个部门的员工内容：展示 部门名称，员工名。

select de.id,de.name,em.name
from tb_emp em  left join  tb_dept de on de.id=em.dept_id;

-- 统计每个部门的人数，如果没有则填写0  (用分组)
select de.name,count(*)  --
from tb_emp em  left join  tb_dept de on de.id=em.dept_id
group by de.name;
-- ↓ 用下面这个。
select de.name,count(em.id) -- 聚合函数不计算null值。 en.id是null的会是0
from tb_emp em  left join  tb_dept de on de.id=em.dept_id
group by de.name;


-- 子查询
-- from 后的子查询
select * from
(select de.id,de.name
from tb_emp em  left join  tb_dept de on de.id=em.dept_id) tmp;


-- 1）查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
--   拆解步骤一：统计员工数据
--   拆解步骤二：根据from子查询结果进行多表关联查询（当然只用多表关联或者in等where子查询也能实现--实现方式有很多，掌握思想后灵活运用即可）
select * from
(select *
from tb_emp em where entrydate>'2006-01-01') em left join tb_dept de on de.id=em.dept_id;

-- 2）统计下单日期在2023-01-01之后的订单，并展示其用户信息
select * from
    (select id,order_no,member_id
     from jd_orders where create_time>'2023-01-01') em
  left join jd_members m on em.member_id=m.id;

-- 3）订单派发需要默认地址，所有，现在统计有默认地址的用户的订单数据。
select * from (select * from jd_address ad left join jd_orders o on o.member_id= ad.member_id)  em
    left join jd_order_detail de on em.id=de.order_id;



-- 3.2 where后（常用）

-- 统计在 "方东白" 入职之后的员工信息
select * from tb_emp where entrydate>(select  entrydate from tb_emp where name='方东白');

-- 查询"教研部"的所有员工信息

select * from tb_emp where tb_emp.dept_id=(select id from tb_dept where name='教研部');

-- 查询"教研部"和'学工部'的所有员工信息

select * from tb_emp where tb_emp.dept_id=(select id from tb_dept where name='教研部')
or(tb_emp.dept_id=(select id from tb_dept where name='学工部'));

-- 方法二：
select  id from tb_dept where name in('教研部','学工部');
select * from tb_emp where dept_id in(select  id from tb_dept where name in('教研部','学工部')) order by dept_id;


-- 子查询的结果为一行多列
--

-- 查询与"韦一笑"的入职日期及职位都相同的员工信息
-- select * from tb_emp where tb_emp.entrydate=(select entrydate from tb_emp where name='韦一笑');

select *
from tb_emp
where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');


select if(job is null ,0,job) from tb_emp;
select ifnull(job,0) from tb_emp;

select name,
       case job
        when 1 then '班主任'
        when 2 then '讲师'
        when 3 then '学工主管'
        when 4 then '教研主任'
        when 5 then '咨询师'
        else 'BOSS'
        end
from tb_emp;

-- 入职时间<2001:"老员工"
-- >=2015  骨干员工
-- 否则新员工。
select  name,
        case
            when entrydate<'2001-01-01' then '老员工'
            when entrydate<'2015-01-01' then '骨干员工'
            else '新员工'
         end
from tb_emp;



SQL语句



use jd_base; -- 使用数据库，可以切换数据库。
show databases ;  --  查询所有数据库
select database(); -- 查询当前数据库
create database if not exists database_name ;  -- 创建名字为database_name数据库
drop database  if exists database_name;   -- 删除数据库

show tables ;   -- 查询当前数据库所有表
desc jd_address;   -- 查询表结构
show create table jd_address;  --  查询建表语句

-- CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串
-- CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
select concat_ws(':',username,name)
from tb_emp