外键变种与单、多表查询

一、外键变种
    三种关系:
        ---多对一
            站在左表的角度:
                (1)一个员工能否对应多个部门;
                (2)多个员工 能否在一个部门
                只要一个条件成立: 多对一或者一对多
                如果两个条件都成立:多对多
        ---- 多对多
            create table book(
               id int primary key auto_increment,
               name varchar(20)
            );
            create table author(
                id int primary key auto_increment,
                name varchar(20)
            );
            create table author2book(
                id int primary key auto_increment,
                author_id int not null,
                book_id  int not null,

                constraint fk_author foreign key(author_id) references author(id)
                on delete cascade
               on update cascade,

               constraint fk_book foreign key(book_id) references book(id)
               on delete cascade
               on update cascade
            );
        ---一对一
        #两张表: 用户表(user)和博客表(blog)
        #创建用户表
        create table user(
            id int primary key auto_increment,
            name varchar(20)
            );
        #创建博客表
        create table blog(
            id int primary key auto_increment,
            url varchar(100),
            user_id int unique,
            constraint fk_user foreign key(user_id) references user(id)
            on delete cascade
            on update cascade
        );
        #插入用户表中的记录
            insert into user(name) values
            ('alex'),
            ('wusir'),
            ('egon'),
            ('xiaoma')
            ;
        # 插入博客表的记录
         insert into blog(url,user_id) values
         ('http://www.cnblog/alex',1),
         ('http://www.cnblog/wusir',2),
         ('http://www.cnblog/egon',3),
         ('http://www.cnblog/xiaoma',4)
         ;
        as  #给字段起别名
         #子查询
         select url as user_url from blog where user_id = (select id from user where name = 'wusir');
二、单表查询
    关键字的执行优先级
        from
        where
        group by
        having
        select
        distinct
        order by
        limit
        1.找到表:form
        2.拿着where制定的约束条件,去文件/表中取出一条条记录
        3.将取出的一条条记录进行分组group by,如果没有group by,则整体视为一组;分组的目的是将表中具有
          相同属性的值筛选出来便于处理
        4.将分组的结果进行having过滤
        5.执行select
        6.使用distinct去重
        7.将结果按条件排序: order by
        8.限制结果的显示条数:limit

    查询岗位名以及岗位包含的所有员工名字 group_concat(name)
    select post,group_concat(name) from employee group by post;
(1)where 约束

　　where子句中可以使用
　　1.比较运算符：>、<、>=、<=、<>、!=
　　2.between 80 and 100 ：值在80到100之间
　　3.in(80,90,100)值是10或20或30
　　4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符
　　5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
（2）group by 分组查询

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

#4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
继续验证通过group by分组之后，只能查看当前字段,如果想查看组内信息，需要借助于聚合函数。mysql> select * from emp group by post;# 报错ERROR 1054 (42S22): Unknown column 'post' in 'group statement'

mysql>  select post from employee group by post;
+-----------------------------------------+
| post                                    |
+-----------------------------------------+
| operation                               |
| sale                                    |
| teacher                                 |
| 老男孩驻沙河办事处外交大使              |
+-----------------------------------------+
4 rows in set (0.00 sec)

（3）聚合函数

max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数 

count()与sum()的区别:前者是求总个数,后者是求和。

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
# 每个部门有多少个员工
select post,count(id) from employee group by post;
# 每个部门的最高薪水
select post,max(salary) from employee group by post;
# 每个部门的最低薪水
select post,min(salary) from employee group by post;
# 每个部门的平均薪水
select post,avg(salary) from employee group by post;
# 每个部门的所有薪水
select post,sum(age) from employee group by post;

（4）HAVING过滤

HAVING与WHERE不一样的地方在于

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

（5）order by 查询排序

HAVING与WHERE不一样的地方在于

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
（5）order by 查询排序

按单列排序
    SELECT * FROM employee ORDER BY age;
    SELECT * FROM employee ORDER BY age ASC;
    SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序，如果年纪相同，则按照id降序
    SELECT * from employee
        ORDER BY age ASC,
        id DESC;
（5）limit  限制查询的记录数:

示例：
    SELECT * FROM employee ORDER BY salary DESC 
     LIMIT 3;                    #默认初始位置为0 

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

三、多表查询:
    根据之前的学习将两张表间进行连接,不建立外键约束
        select * from employee where dep_id=(select id from department);

        select * from employee,department;
        #会形成一个笛卡尔积
        select * from employee,department where employee.dep_id = department.id;
    -内连接:只连接匹配的行
        select * from employee inner join department on employee.dep_id=department.id;

    -左连接:优先匹配左表的记录
        select * from employee left join department on employee.dep_id=department.id;
    -右连接:优先匹配右表的记录
        select * from employee right join department on employee.dep_id=department.id;

    -子查询:
        #1：子查询是将一个查询语句嵌套在另一个查询语句中;
        #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件;
        #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
      #4：还可以包含比较运算符：= 、 !=、> 、<等