学习MySQL基础笔记4

旧城等待， 2024-03-26 15:43 21阅读 0赞

`下一次笔记就是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;