学习MySQL基础笔记4

下一次笔记就是MySQL的进阶了

#作业
create table employee(
id int unsigned primary key auto_increment comment '编号',
job_id int unsigned not null comment '工种编号',
name varchar(30) not null comment '名称',
department_id int(3) zerofill not null comment '部门编号',
salary decimal(10,2) not null default 0 comment '薪水',
bonus decimal(10,2) not null default 0 comment '奖金'
)

insert into employee(job_id,name,department_id,salary,bonus) values 
(1,'tom',1,6000,1000),
(1,'jerry',1,7000,1000),
(2,'alice',1,6700,1000),
(3,'tina',1,8000,1000),
(1,'zhangs',2,9000,1000),
(2,'lisi',2,9000,0),
(3,'zhaowu',2,9000,0); 
#(1)  使用alter， 添加  se(性别 tinyint 值 0,1 ) age（年龄  int ）  字段并且放在 name 后面
  alter table employee
  add se tinyint unsigned not null after name,
  add age int unsigned not null after name;
  #(2)  使用alter，修改 se 字段名为   sex
  alter table employee change se sex tinyint unsigned not null;
#(3)  条件查询
    #a.查找 employee  中   年龄大于等于 20并且 薪水大于5000的记录
	select * from employee where age>=20 and salary>5000;

    #b.  查找 employee  中 编号在2-9  也可以  sex 是 1的记录
	select * from employee where id between 2 and 9 or sex=1;
	
    #c.  查找 employee  中 编号是偶数并且年龄大于22的记录
	select * from employee where id%2=0 and age>22;
	
    #d. 条件编号在2-9 的记录，更新age 字段的年龄都加9
	update employee set age=age+9  where id between 2 and 9 ;
#(4) 分组
      #a. 查询每个部门,显示 部门编号， 人数 ，最大薪水，最小薪水，薪水和
	  select department_id,
	  count(*) as 人数,
	  max(salary) as msalary,
	  sum(salary),
	  min(salary)
	  from employee group by department_id;
      #b. 查询每个工种,显示 工种编号， 人数 ，最大年龄，最小年龄，平均年龄
	  select job_id,
	  count(*) as 人数,
	  max(age) as mage,
	  avg(age),
	  min(age)
	  from employee group by job_id;
	  
# [having  条件] 二次过滤: 结合group by 使用
# eg1 查询部门人数大于2 的部门编号和人数的记录
select department_id,count(*) as c
from employee 
group by department_id
having c >2;
select department_id,count(*) as c
from employee
where id between 2 and 6
group by department_id
having c >2;
# [order by 字段]  排序
#  默认升序 asc，降序 desc
# eg2 查询 employee 表 2-6的记录显示 编号，用户名称，薪水并且对薪水和编号降序排序
select id,name,salary from employee
where id between  2 and 6
order by salary desc,id desc;

# [limit 偏移量,长度] 截取 -- slice
#  a.偏移量: 起始索引，索引从0开始
#  b. 长度： 显示条数(记录数)
#  c. 分页   偏移量 = (当前页 -1 )*长度
# eg3 查询前三条记录
select * from employee where id <=3; # 不准确
select * from employee limit 0,3;

# eg4 分页 （新闻内容）
# 第一页 每页显示3条，降序排序  偏移量 = (当前页 -1 )*长度= (1-1)*3=0
select * from employee order by id desc limit 0,3;
# 第二页 每页显示3条，降序排序  偏移量 = (当前页 -1 )*长度= (2-1)*3=3
select * from employee order by id desc limit 3,3;
# 第三页 每页显示3条，降序排序  偏移量 = (当前页 -1 )*长度= (3-1)*3=6
select * from employee order by id desc limit 6,3;


# 多表联合查询
# job 表
create table job(
id int unsigned primary key auto_increment comment '编号',
jname varchar(30) not null comment '工种名称'
);
insert into job(jname) values 
('电工'),
('汽修'),
('瓦工'),
('水泥工');
# depart 表
create table depart(
id int(3) zerofill primary key auto_increment comment '编号',
dname varchar(30) not null comment '部门名称'
);
insert into depart(dname) values 
('装修'),
('销售'),
('会计');
/*
SELECT 表.字段名称,表.字段名称...
       FROM 表1
              连接类型 表2 ON 条件   
              连接类型 表3 ON 条件...
              连接类型： 
        (1)内连接: [INNER] JOIN  查找两个表都符合条件的信息
*/
# eg5 查询employee 表中 员工编号，姓名(employee)，部门名（depart）的记录
select  e.id,e.name,d.dname
from employee as e
inner join depart as d on e.department_id = d.id;

# 了解(sql92)
select e.id,e.name,d.dname
from employee as e,depart as d where e.department_id = d.id;

# eg6 查询employee 表中 员工编号，姓名(employee)，部门名（depart）,工种名称（job）的记录
select  e.id,e.name,d.dname,j.jname
from employee as e
inner join depart as d on e.department_id = d.id
inner join job as j on e.job_id = j.id;

# eg7 查询部门人数大于2的部门名（depart）和人数的记录并且对人数降序排序
select d.dname,count(*) as c
from employee as e
inner join depart as d on e.department_id = d.id
group by e.department_id
having c > 2
order by c desc;

# eg8 查询部门名（depart）是'装修'和'会计'的员工信息(employee)的记录
select e.*,d.dname
from employee as e
inner join depart as d on e.department_id = d.id
where d.dname in ('装修','会计');

/*
 (2)外连接：
		外连接的查询结果为主表中的所有记录如果从表中有和它匹配的，则显示匹配的值如果从表中没有和它匹配的，则显示null
	    外连接查询结果=内连接结果+主表中有而从表没有的记录.左外连接，left join左边的是主表,右外连接，right join右边的是主表
        全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的 full join
*/
# eg9 查询所有部门的部门名称及对应员工信息的记录
#  left join 左外连接 (左边是主表)
select d.dname,e.*
from depart as d
left join employee as e on d.id = e.department_id;

#  right join 右外连接(右边是主表)
select d.dname,e.*
from employee as e
right join depart as d on d.id = e.department_id;

# 内连接-自连接(菜单级联)
#递归用法
# 设计 一个菜单表 menu (子菜单pid 与上一级菜单id 相等)
# id  name  pid
#  1   家电   0
#  2   手机   0
#  3   电脑   0
#  4   电视   1
#  5   空调   1
#  6   洗衣机  1
#  7   长虹   4 
#  8   TCL    4
#  9   格力   5
#  10  美的   5

create table menu(
id int unsigned primary key auto_increment comment '编号',
name varchar(30) not null comment '菜单名称',
pid int unsigned not null comment '上一级菜单id'
);
insert into menu(name,pid) values 
('家电',0),
('手机',0),
('电脑',0),
('电视',1),
('空调',1),
('洗衣机',1),
('长虹',4),
('TCL',4),
('格力',5),
('美的',5);

# eg10: 查询子菜单名称及对应父菜单的名称的记录
select s.name as 子菜单 ,p.name as 父菜单
from menu as p 
inner join menu as s on s.pid = p.id;