create table user2
(
    id       int primary key auto_increment comment '主键',
    username varchar(20) not null unique comment '登录账号',
    name     varchar(10) not null ,
    age      int,
    gender   char(1) default '男'
);
drop table user;
alter table emp add address varchar(50);  #加地址列
alter table emp drop name;
alter table emp modify username varchar(21);   #改数据类型(长度,约束)
alter table emp change image img varchar(100);  #改字段名并指定新的字段长度
rename table user to user3;
show tables;
desc emp;
show create table emp;

create table emp2
(
    id     int auto_increment
        primary key
        unique,
    emp_id int not null,
    constraint emp2_emp_id_fk
        foreign key (emp_id) references emp (id)
);
alter table dish
    add constraint dish_category_id_fk
        foreign key (category_id) references category (id);

-- 插入单条数据片段
insert into user (name, username, gender)
values ('李四', 'lisi', 1);
-- 插入单条数据
insert into user (id, username, name, ages, gender)
values (3, 'wangwu', '王五', 20, '男');
insert into user
values (4, 'zhaoliu', '赵六', 33, '1');
-- 插入多条数据
insert into user
values (5, 'hu', 'hu', 33, '1'),
       (6, 'hu', 'hu', 33, '1');
-- 修改数据
update user
set username = 'zhangsan'
where id = '5';
-- 删除数据
delete
from user
where id = 6;
delete
from user
where id in (4, 5);

select
  字段列表
from
  表名列表
where
  条件列表
group by
  分组字段列表
having
  分组后条件列表
order by
  排序字段列表
limit
  分页参数

select *
from emp
where name like '%张%'
  and gender = 1
  and entrydate between '2000-01-01' and '2010-01-01'
order by entrydate desc
limit 10;
select if(gender = 1, '男性员工', '女性员工'), count(gender)
from emp
group by gender;
select case job
           when 1 then '班主任'
           when 2 then '讲师'
           when 3 then '教研主管'
           when 4 then '学工主管'
           else '无'
           end,
       count(job)
from emp
group by job;

-- ============================= 内连接 ==========================
-- A.查询员工的姓名, 及所属的部门名称 (隐式内连接实现)
select tb_emp.name, tb_dept.name
from tb_emp,
     tb_dept
where tb_emp.dept_id = tb_dept.id;
-- B.查询员工的姓名, 及所属的部门名称 (显式内连接实现)
select tb_emp.name, tb_dept.name
from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;
         
select tb_emp.name, tb_dept.name
from tb_emp
         inner join tb_dept on tb_emp.dept_id = tb_dept.id;
-- =============================== 外连接 ============================
-- A.查询员工表所有员工的姓名, 和对应的部门名称 (左外连接)
select tb_emp.name , tb_dept.name 
from tb_emp
         left outer join tb_dept on tb_emp.dept_id = tb_dept.id;
         
select tb_emp.name , tb_dept.name 
from tb_emp
         left join tb_dept on tb_emp.dept_id = tb_dept.id;
-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select tb_dept.name as 部门, tb_emp.name as 员工
from tb_dept
         right join tb_emp on tb_dept.id = tb_emp.dept_id;
-- ========================= 子查询 ================================
-- 标量子查询
-- A.查询 "教研部" 的所有员工信息
select *
from tb_emp
where dept_id = (select id
                 from tb_dept
                 where name = '教研部');
-- B.查询在 "方东白" 入职之后的员工信息
select *
from tb_emp
where entrydate > (select entrydate
                   from tb_emp
                   where name = '方东白');
-- 列子查询
-- A.查询 "教研部" 和 "咨询部" 的所有员工信息
select *
from tb_emp
where dept_id in (select id
                  from tb_dept
                  where name in ('教研部', '咨询部'));
-- 行子查询
-- A.查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息;
select *
from tb_emp
where (entrydate, job) = (select entrydate, job
                          from tb_emp
                          where name = '韦一笑');
-- 表子查询
-- A.查询入职日期是 "2006-01-01" 之后的员工姓名 , 及其部门信息
select tb_emp.name, tb_dept.name
from tb_emp,
     tb_dept
where entrydate > '2006-01-01'
and tb_emp.dept_id = tb_dept.id;
select tb_emp2.name,tb_dept.name
from (select * from tb_emp where entrydate > '2006-01-01') as tb_emp2
left join tb_dept on tb_emp2.dept_id = tb_dept.id;

-- 1.查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称
select d.name, d.price, c.name
from dish d,
     category c
where price < 10 && d.category_id = c.id;
-- 2.查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来)
select dish.name, dish.price, c.name
from dish,
     category c
where price >= 10 && price <= 50
  and dish.category_id = c.id
  and dish.status = 1;
-- 3.查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格
select c.name, max(d.price)
from dish d,
     category c
where d.category_id = c.id
group by c.name;
select c.name, d2.max
from (select category_id, max(price) max
      from dish
      group by category_id) d2,
     category c
where d2.category_id = c.id;
-- 4.查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3的 分类名称
select c.name, count(d.status)
from category c,
     dish d
where d.category_id = c.id && d.status = 1
group by c.name
having count(d.status) >= 3;
select category_id, count(*)
from dish
where status = 1
group by category_id
having count(*) >= 3;
select c.name ,d.count
from (select category_id, count(*) count
      from dish
      where status = 1
      group by category_id
      having count >= 3) d,
     category c
where d.category_id = c.id;
-- 5.查询出 "商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格, 包含的菜品名称、价格、份数)
select s.name, s.price, d.name
from setmeal s,
     dish d,
     setmeal_dish sd
where s.name = '商务套餐A'
  and sd.setmeal_id = s.id
  and sd.dish_id = d.id;
-- 6.查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)
select dish.name, price
from dish
where price < (select avg(price) from dish);