多表查询

 **多表查询**
多表连接查询
复合条件连接查询
子查询

**一、准备两张测试表**
**表company.employee6**
mysql> create table employee6(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int);

mysql> desc employee6;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| emp_id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| dept_id | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+

mysql> insert into employee6(emp_name,age,dept_id) values
('tianyun',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);

mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | tianyun | 19 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 201 |
| 4 | alice | 24 | 202 |
| 5 | robin | 40 | 200 |
| 6 | natasha | 28 | 204 |
+--------+----------+------+---------+

**表company.department6**
mysql> create table department6(
dept_id int,
dept_name varchar(100)
);

mysql> desc department6;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+

mysql> insert into department6 values
(200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');

mysql> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 200 | hr |
| 201 | it |
| 202 | sale |
| 203 | fd |
+---------+-----------+
注：
Financial department:财务部门 fd

二、多表的连接查询
交叉连接： 生成笛卡尔积，它不使用任何匹配条件
内连接： 只连接匹配的行

外连接
左连接： 会显示左边表内所有的值，不论在右边表内匹不匹配
右连接： 会显示右边表内所有的值，不论在左边表内匹不匹配

全外连接： 包含左、右两个表的全部行

=================交叉连接（了解）=======================
[图片上传失败...(image-47b12f-1525778188132)]

有用：可以优化很多操作速度快
没用：在记录条目太多的时候，会死

=================内连接=======================
两种方式：
方式1：使用where条件
方式2：使用inner join
**只找出有部门的员工 (部门表中没有natasha所在的部门)**
mysql> select employee6.emp_id,employee6.emp_name,employee6.age,departmant6.dept_name
from employee6,departmant6
where employee6.dept_id = departmant6.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+

使用别名：
> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,departmant6 b where a.dept_id = b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+

使用inner join
> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a inner join departmant6 b on a.dept_id = b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+

======================================
**外连接语法：**
SELECT 字段列表
FROM 表1 LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

=================外连接(左连接 left join)=======================
mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
找出所有员工及所属的部门，包括没有部门的员工
[图片上传失败...(image-992f0b-1525778188132)]

=================外连接(右连接right join)=======================
mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;
找出所有部门包含的员工，包括空部门
[图片上传失败...(image-da8914-1525778188129)]

=================全外连接(了解)=======================
> select * from employee6 full join departmant6;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | tianyun | 19 | 200 | 200 | hr |
| 1 | tianyun | 19 | 200 | 201 | it |
| 1 | tianyun | 19 | 200 | 202 | sale |
| 1 | tianyun | 19 | 200 | 203 | fd |
| 2 | tom | 26 | 201 | 200 | hr |
| 2 | tom | 26 | 201 | 201 | it |
| 2 | tom | 26 | 201 | 202 | sale |
| 2 | tom | 26 | 201 | 203 | fd |
| 3 | jack | 30 | 201 | 200 | hr |
| 3 | jack | 30 | 201 | 201 | it |
| 3 | jack | 30 | 201 | 202 | sale |
| 3 | jack | 30 | 201 | 203 | fd |
| 4 | alice | 24 | 202 | 200 | hr |
| 4 | alice | 24 | 202 | 201 | it |
| 4 | alice | 24 | 202 | 202 | sale |
| 4 | alice | 24 | 202 | 203 | fd |
| 5 | robin | 40 | 200 | 200 | hr |
| 5 | robin | 40 | 200 | 201 | it |
| 5 | robin | 40 | 200 | 202 | sale |
| 5 | robin | 40 | 200 | 203 | fd |
| 6 | natasha | 28 | 204 | 200 | hr |
| 6 | natasha | 28 | 204 | 201 | it |
| 6 | natasha | 28 | 204 | 202 | sale |
| 6 | natasha | 28 | 204 | 203 | fd |
+--------+----------+------+---------+---------+-----------+

三、复合条件连接查询
示例1：以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工
[图片上传失败...(image-84a5a1-1525778188129)]

示例2：以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示
[图片上传失败...(image-32637f-1525778188129)]

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

1\. 带IN关键字的子查询
查询employee表，但dept_id必须在department表中出现过
select * from employee6 where dept_id in (select dept_id from department6);
2\. 带比较运算符的子查询
=、!=、>、>=、<、<=、<>
查询年龄大于等于25岁员工所在部门（查询老龄化的部门）
select dept_id ,dept_name from department6 where dept_id in (select distinct dept_id from employee6 where age >= 25);
3\. 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

department表中存在dept_id=203，Ture
select  * from employee6 where exists (select * from department6 where dept_id=203);
department表中存在dept_id=300，False
select * from employee6 where exits (select * from department6 where dept_id=300)

DTL=======================================================

一、DQL(查询)(默写)
    查询
        查询表中所有
            select * from user; 
            【注】查询的时候也要加条件，真实开发中不要直接这么查
        指定字段查询
            select name,sex from user;
        条件查询
            select name,sex from user where id = 1;
    查询去重    distinct    (默写)
        查询表中多条数据并且 重复的数据只会出来一个
            select distinct * from user;
二、where条件
    关系：
        >   <   <=  >=  !=/<>   =   
    逻辑：
        or  或  把满足两者条件任意一种的都显示出来
        and 并且  把同时满足两者条件的显示出来
    区间：
        between and
        查询id在2-5区间内的数据 包括2和5
            select * from user where id between 2 and 5;
    集合：
        in/not in   
        查询id在 1,3,4,5 中的数据
            select * from user where id in (1,3,4,5);   
    模糊查询：
        like
            _   代表任意一个字符
                _张      两位数并且以张结尾
            %   代表任意所有字符
                %张%     所有带张的
                %张      以张结尾的
                张%      以张开头的
        查询表中所有名字中带张的
            select * from star where name like '%张%';
三、结果集排序
    order by
        desc:倒序
        asc：正序(默认)
        单字段排序(按照某个字段对查询的结果进行排序)
            select name,sex from user where id > 1 order by id desc
        多字段排序(当单字段排序有相同值时可以使用多字段，可以无限加)
            select name,sex from user where id > 1 order by id desc,money desc;
            【注】多字段排序时用逗号分隔开
四、结果集限制
    limit
        查询所有数据并显示出五条
            select * from user limit 0,5;
        查询user表所有数据并且从第三条开始显示5条
            select * from user limit 2,5;
        查最有钱的五个人
            select name from star order by money desc limit 5;
    说明：
        1、2,5       2代表从第三个开始  5代表显示5条
        2、分页        每页显示10条
            1       0,10
            2       10,10
            3       20,10
            n       (n-1)*10,10
五、常用统计函数
    sum count   max min avg(平均值)
六、结果集分组
    group by
        查询star表中的数据 并且按照name分组
            select * from star group by name;
    having      分组过滤
        查询star表中的数据 并且按照name分组 显示出id>3的
            select * from star group by name having id > 3;
        【注】group by 之后不要用where
    新版本大坑说明：
        1、如果你在执行分组的时候按照我上面的写法出错了 请你去修改mysql->mysql setting->sql-mode->none
        2、最好是 select 的 字段 必须出现在 group by 后面 除了统计函数
        3、group by 之后最好不要用别名
七、起别名
    as
        查询star表中的name 显示出来结果是shaoye
            select name as shaoye from star; 
        找到个数第二多的省份及个数
            select count(province) as shaoye ,province from star group by province order by shaoye desc limit 1,1;  
八、多表联合查询
        内连接
            隐式内连接
                select goods.name, user.username from user,goods where goods.gid = user.uid;
            显示内连接
                select goods.name,user.username from user inner join goods on goods.gid = user.uid;
                【注】有join关键字出现 后面接的条件必须是on inner 可以省略
        外链接
            左连接
                select * from user left join goods on goods.gid = user.uid;
            右连接
                select * from user right join goods on goods.gid = user.uid;
            说明
                1、左连接 左边的表叫做主表 右边的表叫副表
                2、右连接 右边的表叫做主表 左边的表叫副表
                3、主表内容会全部显示，副表显示符合条件的 空位用null填充
        练习
            1、查询那些商品没有背购买过
                select * from user right join goods on goods.gid = user.uid where username is null;
                【注】后面条件一定要用 is
            2、查询销量冠军
                select name,count(name) from user right join goods on goods.gid = user.uid group by name order by count(name) desc limit 0,1;
            3、查询销量冠军的价格
                select name,price,count(name) from user right join goods on goods.gid = user.uid group by name order by count(name) desc limit 0,1;
九、子（嵌套）查询
        查询买过商品的大哥
            select * from user where uid in(select gid from goods);
十、记录联合（了解）
    1、使用union将左连接和右连接查询出来的数据联合到一起
        select * from user left join goods on goods.gid = user.uid union select * from user right join goods on goods.gid = user.uid;
    2、使用union all 将左连接和右连接查询出来的数据联合到一起
        select * from user left join goods on goods.gid = user.uid union all select * from user right join goods on goods.gid = user.uid;
    说明：
        1、union 比 union all 多进行了一次去重
        2、数据联合时左右联合的字段必须要对应
十一、多表联合更新
    将user表中uid=4的并且user.uid=goods.gid的数据里的username和name改成邹玉和奔驰
        update user,goods set user.username = '邹玉', goods.name = '奔驰' where user.uid = goods.gid and user.id = 4;
        【注】多表联合更新时一定要找对条件关系,否则后果自负
十二、清空表数据
        truncate table 表名
            清空user表的数据，并且让自增的id从1开始自增
        与delete from 表名的区别
            delete  是清空数据然后id是从记录的开始自增
十三、DCL
    创建用户
        创建一个叫xiaoming的用户 密码是123123 从本机登录
        create user 'xiaoming'@'localhost' identified by '123123';
    删除用户
        把上面刚给一个叫xiaoming用户干掉
         drop user 'xiaoming'@'localhost';
    授予权限
        给一个叫做xiaoming的用户权限 权限是对 zhatian数据库下的所有表 有全部权限
         grant all on zhatian.* to 'xiaoming'@'localhost';
    剥夺权限
        把上面刚给一个叫xiaoming用户的权限收回来
        revoke all on zhatian.* from 'xiaoming'@'localhost';
十四、导入导出数据库
    1、通过phpmyadmin
    2、命令行
        导出
            导出数据库
                mysqldump -uroot -p 数据库名 > C:\123.sql
            导出表 
                mysqldump -uroot -p 数据库名 表名 > C:\123.sql
        导入
            mysql -uroot -p sss < C:\123.sql
        说明：
            1、导入导出都要在非登录咋状态下完成
            2、带入的时候要有一个空的数据库
十五、修改密码
        1、输入命令mysqladmin -uroot -p password
        2、按回车时候输入原密码 如果原密码为空 直接回车
        3、输入新密码
        4、确认新密码
        5、执行 flush privileges;
        说明：
            修改密码要在非登录状态下完成
十六、DTL(了解)
    1、set autocommit = 0  或者 begin
    2、执行sql
    3、判断是否同意执行
    4、如果同意 执行 commit  不同意执行 rollback（回滚）
    说明：
        1、使用事务的表存储引擎必须是InnoDB   

//左连接和右连接对比图    （良心推荐）      
    mysql> select * from user left join goods on goods.gid = user.uid;
    +----+-----+----------+--------+------+---------+---------+----------+
    | id | uid | username | pass   | gid  | name    | price   | category |
    +----+-----+----------+--------+------+---------+---------+----------+
    |  1 |   5 | 邹玉     | asd    |    5 | auto    |   20000 | car      |
    |  2 |   7 | 刘伟     | qweqwe |    7 | 路虎    | 1000000 | car      |
    |  3 |   6 | 韦福东   | asdasd |    6 | 奔驰    | 1000000 | car      |
    |  4 |   7 | 周威     | asdsad |    7 | 路虎    | 1000000 | car      |
    |  5 |   1 | 何芍叶   | asdasd |    1 | ipone 7 |    7100 | phone    |
    |  6 |   0 | 东策     | asdasd | NULL | NULL    |    NULL | NULL     |
    +----+-----+----------+--------+------+---------+---------+----------+
    6 rows in set (0.00 sec)

    mysql> select * from user right join goods on goods.gid = user.uid;
    +------+------+----------+--------+-----+----------+---------+----------+
    | id   | uid  | username | pass   | gid | name     | price   | category |
    +------+------+----------+--------+-----+----------+---------+----------+
    |    1 |    5 | 邹玉     | asd    |   5 | auto     |   20000 | car      |
    |    2 |    7 | 刘伟     | qweqwe |   7 | 路虎     | 1000000 | car      |
    |    3 |    6 | 韦福东   | asdasd |   6 | 奔驰     | 1000000 | car      |
    |    4 |    7 | 周威     | asdsad |   7 | 路虎     | 1000000 | car      |
    |    5 |    1 | 何芍叶   | asdasd |   1 | ipone 7  |    7100 | phone    |
    | NULL | NULL | NULL     | NULL   |   2 | mate9    |    4000 | phone    |
    | NULL | NULL | NULL     | NULL   |   3 | oppo R11 |    3000 | phone    |
    | NULL | NULL | NULL     | NULL   |   4 | vivo x9  |    3000 | phone    |
    +------+------+----------+--------+-----+----------+---------+----------+
    8 rows in set (0.00 sec)