学习MySQL进阶笔记1

墨蓝 2024-03-26 16:45 69阅读 0赞

进阶笔记 多表联合查询

#(1) 查询员工号，姓名，工资以及工资提高百分20%后的结果。
    select id,name,salary,1.2*salary as newS from employee;
    #(2) 查询员工最高工资和最低工资的差距 (提示写运算得一个结果，别名diff)
    select max(salary) -min(salary) as diff from employee;
    #(3） 选择工资不在5000-10000的员工的姓名和工资， 按工资降序
    select name,salary from employee where salary<5000 or salary >10000
    order by salary desc;
    #(4) 查询员工名中第二个字符为_的员工名
    select name from employee where name like '_#_%' escape '#';
    #(5)  查询没有奖金的员工名和奖金值。
    select name,bonus from employee where bonus=0;
    #(6) 查询哪个部门的员工人数大于2
    select  department_id,count(*) as c
    from employee
    group by department_id
    having c >2;
    #(7）每个工种(有奖金的员工的)(最高工资>5000的)工种编号和最高工资，按最高工资升序
    select  job_id,max(salary) as msalary
    from employee
    where bonus !=0
    group by job_id
    having msalary >5000
    order by msalary asc;
    
    #(8) 多表联合查询
    #  a. 查询名字中包含a的员工名和工种名(job)(添加条件)
    select e.name,j.jname
    from employee as e
    inner join job as j on e.job_id = j.id
    where e.name like '%a%';
    #  b. 查询哪个部门(员工大于2的)部门名(depart)和员工个数
    select count(*) as c,d.dname
    from employee as e
    inner join depart as d on e.department_id = d.id
    group by e.department_id
    having c >2;
    #  C 查询部门名（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 ('销售','会计');
    
    
    # 约束： 按约束字段个数分
    #a.列约束:在字段后面实现约束NOT NULL ,DEFAULT必须用列约束实现 
    #b.表约束:对两个字段或两个字段以上实现的约束PRIMARY KEY ，UNIQUE KEY，FOREIGN KEY可以实现表约束
    
    # eg1 列约束
    CREATE TABLE user(
        id SMALLINT UNSIGNED PRIMARY KEY,
        name VARCHAR(30) NOT NULL UNIQUE KEY
    );
    # eg2 表约束
    CREATE TABLE user2(
        id SMALLINT UNSIGNED,
        name VARCHAR(30) NOT NULL,
    	primary key(id),
    	unique(name)
    );
    
    show create table user2;  # 查询创建表命令
    
    # eg3 多个字段同时为主键
    create table key1( # 错误的 两个主键了
    n1 int primary key,
    n2 int primary key
    )
    
    create table key1( #只能写表约束,一个表只能有一个主键
    n1 int,
    n2 int,
    primary key(n1,n2)
    )
    insert into key1(n1,n2) values 
    (1,1),
    (1,2),
    (2,1); # 再写 (1,1) (1,2) 主键重复了
    
    /* 
    (4)FOREIGN KEY (外键) 
    
     1.实现对两个表的字段完整性和一致性约束
     2.
         FOREIGN KEY(外键列名称)
         REFERENCES 参考表名称(字段名称)
        说明： 5.7以前 MyISAM 不支持 外键，事务，锁
           a.MYSQL引擎为 InnoBD alter table employee engine=InnoDB;
           b.数据类型必须一致，如果是整型时大小,UNSIGNED 必须一致，如果字符型长度可以不同，但编码必须一致
           c.外键列如果不是索引，MYSQL引擎会自动将外键列定义为索引
           d.外键列(子表)添加信息，参考表必须有相应信息，参考表不能删除/更新在子表中已经使用的外键值。
     
     */
    # a. 先创建父表（参考表）
    # 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 
    ('装修'),
    ('销售'),
    ('会计');
    
    # b.子表(外键表)
    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 '奖金',
    foreign key(job_id) references job(id),
    foreign key(department_id) references depart(id)
    );
    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);
    
    # eg1 外键列(子表)添加信息，参考表必须有相应信息，
    insert into employee(job_id,name,department_id,salary,bonus) values
    (5,'zhaowu5',2,9000,0); #不能添加
    # eg2参考表不能删除/更新在子表中已经使用的外键值。
    
    delete from job where id = 1; #不能删除
    update job set id = 8 where id = 2; #不能更新
    
    # eg3 不能先删除参考表（父表），在上外键列表（子表）
    
    /* 
      3  完整格式：
         FOREIGN KEY (外键列名称)
         REFERENCES 参考表名称(字段名称)
         [ON UPDATE RESTRICT|CASCADE|SET NULL|NO ACTION
         ON DELTED RESTRICT|CASCADE|SET NULL|NO ACTION]
             说明：
           a.RESTRICT:默认值 ,等同NO ACTION
                     参考表(父表)不能更新/删除外键表(子表)中被应用的字段值
           b.CASCADE(级联)：参考表(父表)
                     更新/删除外键表(子表)也更新或删除相应的信息
           c.SET NULL:参考表(父表)
                     更新/删除将外键表(子表)中更新或删除相应信息设置为NULL
                     外键表(子表)的字段必须允许为NULL即不能为NOT NULL
     
     */
    
    # 数据库表三范式
    /* 
     #1.第一范式：要求有主键，并且要求每一个字段原子性不可再分 (员工编号id,员工名称name,员工情况X)
     
     #2.第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
     #3.第三范式：所有非主键字段和主键字段之间不能产生传递依赖
     (employee  id  job_id工种编号  job表 jname工种名称X)
     */
    # 表设计种类
    # 一对一: （article表  id title author content(内容特别大),created）拆成一对一的两个表
       #a 两个表共享一个主键 article1(id titel author created) article2(id content) 提高查询效率
       #b 两个表做外键
    # 一对多: 给多的一个添加外键，这个外键字段引入参考表中的主键字段
     #  employee(多) job(一) :一个工种编号对应多个员工，一个员工属于一个工种
     #  employee (多)depart(一) :一个部门对于多个员工，一个员工属于一个部门
     #  商品表goods(多)和分类表cates（一）:一个分类有多个商品，一个商品属于一个分类
    # 多对多：一般3个表
       收藏 :goods user :一个商品可以被多个用户收藏，一个用户可以收藏多个商品
       goods (id,title,price)
       user(id,name,sex)
       favi (id,goods_id,user_id)
              1     1       1
    		  2     1       2
    		  3     1       3
    		  4     2       1
    		  5     2       2
    		  
    	成绩表 score,学生表 student ,课程表 course:一门课可以被多个学生学习,一个学生可以学多门课
    	student(sid,sname,sage,ssex) 
    	course(cid,cname,tid) 课程表
    	sC(id,sid,cid,score) 成绩表
    	    1  1  1     90
    		2  1  2     89
    		3  1  3     86
    		4  2  1     68
    		5  2  2     88
    		6  2  3     79
    		
    # 一对一
    create table article1(
    id int unsigned primary key auto_increment comment '编号',
    title varchar(30) not null comment '文章标题',
    author varchar(30) not null default '' comment '文章标题',
    created datetime not null
    );
    insert article1(id,title,created) values
    (1,'一只蝴蝶',now()),
    (2,'一只小鸟',now()),
    (3,'一只老虎',now());
    create table article2(
    id int unsigned primary key auto_increment comment '编号',
    content text not null comment '内容'
    );
    insert article2(id,content) values
    (1,'一只蝴蝶内容'),
    (2,'一只小鸟内容'),
    (3,'一只老虎内容');
    	
    # 一对多
    create table cates(
    id int unsigned primary key auto_increment comment '编号',
    name varchar(30) not null comment '分类名'
    );
    insert cates(name) values
    ('女装'),
    ('男装'),
    ('童装');
    create table goods(
    id int unsigned primary key auto_increment comment '编号',
    title varchar(30) not null comment '商品名',
    price decimal(10,2) not null default 0 comment '价格',
    cate_id int unsigned not null comment '分类编号',
    foreign key(cate_id) references cates(id)
    );
    insert goods(title,price,cate_id) values
    ('裙子',300,1),
    ('大衣',190,1),
    ('童装衬衫',166,3);
    
    # 多对多
    /* student(sid,sname,sage,ssex) 
    course(cid,cname,tid) 课程表
    teacher(tid,tname) 教师表
    score(id,sid,cid,score) 成绩表 */
    create table student(
    sid int unsigned primary key auto_increment comment '编号',
    sname varchar(30) not null comment '学生名',
    sage  tinyint unsigned not null default 18 comment '年龄',
    ssex tinyint unsigned not null default 0 comment '性别'
    );
    insert student(sname) values
    ('tom'),
    ('alice'),
    ('tina');
    
    create table course(
    cid int unsigned primary key auto_increment comment '编号',
    cname varchar(30) not null comment '课程名',
    tid int unsigned  not null comment '教师编号'
    );
    insert course(cname,tid) values
    ('数学',1),
    ('语文',2),
    ('英语',3);
    
    create table sorce(
    id int unsigned primary key auto_increment comment '编号',
    sid int unsigned  not null comment '学员编号',
    cid int unsigned  not null comment '课程编号',
    score int unsigned not null default 0 comment '成绩',
    foreign key(sid) references student(sid),
    foreign key(cid) references course(cid)
    );
    insert sorce(sid,cid,score) values
    (1,1,90),
    (1,2,88),
    (1,3,90),
    (2,1,95),
    (2,2,91),
    (2,3,98),
    (3,1,75),
    (3,2,81),
    (3,3,78);
    
    create table teacher(
    tid int unsigned primary key auto_increment comment '编号',
    tname varchar(30) not null comment '课程名'
    );
    insert teacher(tname) values
    ('张红'),
    ('刘丽'),
    ('陈浩');