mysql外键等

外键

        # 外键前戏
            创建一张员工表
                id name age	dep_name dep_desc
        """
        缺陷：
            1.表的重点不清晰>>>到底是员工表还是部门表
                     可以忽略
            2.表中相关的字段一直重复储存>>>浪费储存空间
                     可以忽略
            3.表的扩展性极差，牵一发而动全身
                     不可忽略
        """
        解决方式：
            将上述一张表进行拆分，分为员工表emp和部门表dep，即可解决
        但是即将带来一个问题：表与表之间的数据没有对应关系了

        外键字段>>>:部门编号
            就是用来标识表与表之间的数据关系
            # 简单的理解为该字段可以让你去到其他表中查找数据

表与表之间建关系

        表关系总共就四种
            '一对多'  '多对多'  '一对一'  '没关系'
        """用换位思考的方式判断表与表之间的关系"""
        1.一对多
            以员工和部门表为例
                先站在员工表的基础之上
                    问:一个员工信息能否对应多个部门信息
                    答:不可以
                再站在部门表的基础之上
                    问:一个部门信息能否对应多个员工信息
                    答:可以
                结论:一个可以一个不可以 那么表关系就是"一对多"
                    员工表是多 部门表是一
                """
                针对一对多的表关系 外键字段建在多的一方
                """
                # 表关系没有'多对一'一说 都是'一对多'
        """
        使用SQL语句建立真正意义上的表关系 可以先创建不含外键字段的基本表
        之后再添加外键字段
            create table emp(
                id int primary key auto_increment,
                name varchar(32),
                age int,
                dep_id int,
                foreign key(dep_id) references dep(id)
            );
            create table dep(
                id int primary key auto_increment,
                dep_name varchar(32),
                dep_desc varchar(254)
            );
        """


        2.多对多关系
            以书籍表与作者表为例
                先站在书籍表的基础之上
                    问:一个书籍信息能否对应多个作者信息
                    答:可以
                再站在作者表的基础之上
                    问:一个作者信息能否对应多个书籍信息
                    答:可以
                结论:两个都可以 那么表关系就是"多对多"
                # 多对多表关系 需要单独开设第三张表存储(并且第三张表可以不绑定)
        """
        create table book(
            id int primary key auto_increment,
            title varchar(32),
            price float(6,2)
        );
        create table author(
            id int primary key auto_increment,
            name varchar(32),
            age int
        );
        create table book2author(
            id int primary key auto_increment,
            author_id int,
            book_id int,
            foreign key(author_id) references author(id)
            on update cascade  # 级联更新
            on delete cascade,  # 级联删除
            foreign key(book_id) references book(id)
            on update cascade  # 级联更新
            on delete cascade  # 级联删除
        );
        """	


        3.一对一表关系
            作者表与作者详情表
                先站在作者表的基础之上
                    问:一个作者信息能否对应多个作者详情信息
                    答:不可以
                再站在作者详情表的基础之上
                    问:一个作者详情信息能否对应多个作者信息
                    答:不可以
                结论:两个都不可以 
                    那么表关系可能是"一对一"或者"没有关系"
                    # 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
        """
        create table author(
            id int primary key auto_increment,
            name varchar(32),
            age int,
            author_id int unique,
            foreign key(author_id) references author_detail(id)
            on update cascade  # 级联更新
            on delete cascade  # 级联删除
        );
        create table author_detail(
            id int primary key auto_increment,
            phone varchar(32),
            address varchar(32)
        );
        """

外键约束

        1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
        2.在插入新数据的时候 应该先确保被关联表中有数据
        3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
        4.在修改和删除被关联表中的数据的时候 无法直接操作
            如果想要数据之间自动修改和删除需要添加额外的配置
            create table emp1(
                id int primary key auto_increment,
                name varchar(32),
                age int,
                dep_id int,
                foreign key(dep_id) references dep1(id) 
                 on update cascade  # 级联更新
                 on delete cascade  # 级联删除
            );
            create table dep1(
                id int primary key auto_increment,
                dep_name varchar(32),
                dep_desc varchar(254)
            );
        """
        由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度
            所以在实际开发项目中 有时候并不会使用外键创建表关系
            而是通过SQL语句层面 建立逻辑意义上的表关系
                eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql
        """

操作表的SQL语句补充

        show tables;
        desc 表名;
        create table t1(id int);
        alter table t1 change id nid int;
        drop table t1;

        语法：
        1. 修改表名  
              alter table 表名 
                                  rename 新表名;
        2. 增加字段
              alter table 表名
                                  add 字段名  数据类型 [完整性约束条件…],
              alter table 表名
                                  add 字段名  数据类型 [完整性约束条件…]  first;
              alter table 表名
                                  add 字段名  数据类型 [完整性约束条件…]  after 字段名;                       
        3. 删除字段
              alter table 表名 
                                  drop 字段名;
        4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
              alter table 表名 
                                  modify  字段名 数据类型 [完整性约束条件…];
              alter table 表名 
                                  change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

查询关键字

        # 数据准备
        create table emp(
          id int primary key auto_increment,
          name varchar(20) not null,
          sex enum('male','female') not null default 'male', #大部分是男的
          age int(3) unsigned not null default 28,
          hire_date date not null,
          post varchar(50),
          post_comment varchar(100),
          salary double(15,2),
          office int, #一个部门一个屋子
          depart_id int
        );

        #插入记录
        #三个部门：教学，销售，运营
        insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
        ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1),  #以下是教学部
        ('tom','male',78,'20150302','teacher',1000000.31,401,1),
        ('kevin','male',81,'20130305','teacher',8300,401,1),
        ('tony','male',73,'20140701','teacher',3500,401,1),
        ('owen','male',28,'20121101','teacher',2100,401,1),
        ('jack','female',18,'20110211','teacher',9000,401,1),
        ('jenny','male',18,'19000301','teacher',30000,401,1),
        ('sank','male',48,'20101111','teacher',10000,401,1),
        ('哈哈','female',48,'20150311','sale',3000.13,402,2),  #以下是销售部门
        ('呵呵','female',38,'20101101','sale',2000.35,402,2),
        ('西西','female',18,'20110312','sale',1000.37,402,2),
        ('乐乐','female',18,'20160513','sale',3000.29,402,2),
        ('拉拉','female',28,'20170127','sale',4000.33,402,2),
        ('僧龙','male',28,'20160311','operation',10000.13,403,3),  #以下是运营部门
        ('程咬金','male',18,'19970312','operation',20000,403,3),
        ('程咬银','female',18,'20130311','operation',19000,403,3),
        ('程咬铜','male',18,'20150411','operation',18000,403,3),
        ('程咬铁','female',18,'20140512','operation',17000,403,3);

查询关键字之select与from

        from控制的是查询哪张表
        select控制的是查询表里面的哪些字段
            select * from emp;
            select id,name from emp;

查询关键字之where筛选

        where筛选功能 

        """
        模糊查询:没有明确的筛选条件
            关键字:like
            关键符号:
                %:匹配任意个数任意字符
                _:匹配单个个数任意字符
        show variables like '%mode%se';
        """
        # 1.查询id大于等于3小于等于6的数据
        select id,name from emp where id >= 3 and id <= 6;
        select *  from emp where id between 3 and 6;  

        # 2.查询薪资是20000或者18000或者17000的数据
        select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
        select * from emp where salary in (20000,18000,17000);  # 简写

        # 3.查询员工姓名中包含o字母的员工姓名和薪资
        # 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
        """
        先是查哪张表 from emp
        再是根据什么条件去查 where name like ‘%o%’
        再是对查询出来的数据筛选展示部分 select name,salary
        """
        select name,salary from emp where name like '%o%';

        # 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
        select name,salary from emp where name like '____';
        select name,salary from emp where char_length(name) = 4;

        # 5.查询id小于3或者大于6的数据
        select *  from emp where id not between 3 and 6;

        # 6.查询薪资不是20000，18000，17000的数据
        select * from emp where salary not in (20000,18000,17000);

        # 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
        select name,post from emp where post_comment = NULL;  # 查询为空！
        select name,post from emp where post_comment is NULL;
        select name,post from emp where post_comment is not NULL;

查询关键字之group by分组

        分组
        """
        按照某个指定的条件将单个单个的个体分成一个个整体
            eg:  按照男女将人分组
                按照肤色分组
                按照年龄分组
        """
        # 分组之后默认只能够直接过去到分组的依据 其他数据都不能直接获取
            针对5.6需要自己设置sql_mode
                set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';


        # 聚合函数
            聚合函数主要就是配合分组一起使用
            max min sum count avg

        # 数据分组应用场景：每个部门的平均薪资，男女比例等

        # 1.按部门分组
        select * from emp group by post;  # 分组后取出的是每个组的第一条数据
        select id,name,sex from emp group by post;  # 验证
        """
        设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
        不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
        """
        set global sql_mode="strict_trans_tables,only_full_group_by";
        # 重新链接客户端
        select * from emp group by post;  # 报错
        select id,name,sex from emp group by post;  # 报错
        select post from emp group by post;  # 获取部门信息
        # 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名


        # 2.获取每个部门的最高工资  
        # 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
        # 每个部门的最高工资
        select post,max(salary) from emp group by post;
        补充:在显示的时候还可以给字段取别名
        select post as '部门',max(salary) as '最高工资' from emp group by post;
        as也可以省略 但是不推荐省 因为寓意不明确
        # 每个部门的最低工资
        select post,min(salary) from emp group by post;
        # 每个部门的平均工资
        select post,avg(salary) from emp group by post;
        # 每个部门的工资总和
        select post,sum(salary) from emp group by post;
        # 每个部门的人数
        select post,count(id) from emp group by post;
        统计的时候只要是非空字段 效果都是一致的 
        这里显示age，salary，id最后演示特殊情况post_comment

补充说明

        # group_concat  分组之后使用
        如果真的需要获取分组意外的数据字段 可以使用group_concat()
        # 每个部门的员工姓名
        select post,group_concat(name) from emp group by post;

        select post,group_concat(name,'|',sex) from emp group by post;

        # concat  不分组使用
        select concat(name,sex) from emp;
        select concat(name,'|',sex) from emp;

关键字之having过滤

        """
        where与having都是筛选功能 但是有区别
            where在分组之前对数据进行筛选
            having在分组之后对数据进行筛选

        我们一定要有一个简单的认识 一条SQL语句的结果也可以看成是一张全新的表
        """
        select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;

关键字之distinct去重

        # 对有重复的展示数据进行去重操作 一定要是重复的数据
        select distinct id,age from emp;
        select distinct post from emp;

关键字之order by排序

        select * from emp order by salary asc; #默认升序排
        select * from emp order by salary desc; #降序排

        #先按照age降序排，在年轻相同的情况下再按照薪资升序排
        select * from emp order by age desc,salary asc; 

        # 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
        select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;

关键字之limit分页

        # 限制展示条数
        select * from emp limit 3;
        # 查询工资最高的人的详细信息
        select * from emp order by salary desc limit 1;

        # 分页显示
        select * from emp limit 0,5;  # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
        select * from emp limit 5,5;

关键字之regexp正则

        select * from emp where name regexp '^j.*(n|y)$';

__EOF__

本文作者：祈安
本文链接：https://www.cnblogs.com/jyc666/p/15929215.html
关于博主：没有收拾残局的能力，就别放纵善变的情绪
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！