MySQL传智播客--Lession25-Lession30笔记

=======================lession25=子查询(1)=============================


子查询：
场景：
查询代课天数最多的那个老师的信息。


Select t_name ,gender from teacher_class order by days desc limit 1;


逻辑没有问题：
但是出现了代课天数一致的最多的如何处理？不能是否有重复的，或者几个重复的。


换个思路：
先获得最多的代课天数是多少天，
Select max(days) from teacher_class;
再判断哪个老师的代课天数，和最大值是一样的。


mysql允许将上面的查询结果，作为一个值来使用。


Var1 = select max(days) from teacher_class;保存起来
Select t_name,gender from teacher_class where days=var1;


但是Mysql允许
Select t_name,gender from teacher_class where days=(select max(days) from teacher_class);
mysql> select t_name,gender from teacher_class where days=(select max(days) from teacher_class);
+----------+--------+
| t_name   | gender |
+----------+--------+
| 韩ä | male   | 
| 李ç | male   | 
+----------+--------+
2 rows in set (0.04 sec)


定义：
语句内部的查询语句，就是子查询语句。子查询语句需要使用括号括起来。


子查询分类：
不同的分类，会有不同的使用方式。




分类标准：
(1)子查询出现的位置
(2)子查询的返回形式


返回值分类：
单一值：
一列，
多列，
表（多行，多列）




出现位置：
where型，where后，
from型，from后，
exists型


如何使用：
标量的：
获得一个值之后，使用关系运算符，进行判断。(= ,>, <, >=, <= ,!=)


列子查询：强调是一列
获得一列：通常是，多个行的一列值。


案例：
获得所有带过0228班，讲师的信息。
所有0228讲师的姓名，再利用这行姓名，查询其所有班记录。
select t_name from teacher_class where c_name = 'php0228';
mysql> select t_name from teacher_class where c_name = 'php0228';
+--------+
| t_name |
+--------+
| 韩信 | 
| 李白 | 
+--------+


Select t_name,c_name,days from teacher_class where t_name in (select t_name from teacher_class where c_name = 'php0228');
mysql> select t_name,c_name,days from teacher_class where t_name in (select t_name from teacher_class where c_name = 'php0228');
+--------+---------+------+
| t_name | c_name  | days |
+--------+---------+------+
| 韩信 | php0115 |   21 | 
| 韩信 | php0228 |   18 | 
| 韩信 | php0331 |   22 | 
| 李白 | php0115 |   20 | 
| 李白 | php0228 |   22 | 
| 韩信 | php0331 |   15 | 
+--------+---------+------+
一定是使用集合类的操作符来完成：in | not in
还有其他的集合操作符：
Any（任何一个）=any(集合) 等于集合中的任何一个即可。等同于in。（功能比in要强大一些）
mysql> select t_name,c_name,days from teacher_class where t_name =any (select t_name from teacher_class where c_name = 'php0228');
+--------+---------+------+
| t_name | c_name  | days |
+--------+---------+------+
| 韩信 | php0115 |   21 | 
| 韩信 | php0228 |   18 | 
| 韩信 | php0331 |   22 | 
| 李白 | php0115 |   20 | 
| 李白 | php0228 |   22 | 
| 韩信 | php0331 |   15 | 
+--------+---------+------+
那如果!=any(集合呢)，只要与集合中的一个元素，不相等即可。（只要这个集合里面有两个以上的行，那么这个语法基本不能起作用）
mysql> select t_name,c_name,days from teacher_class where t_name !=any (select t_name from teacher_class where c_name = 'php0228');
+-----------+---------+------+
| t_name    | c_name  | days |
+-----------+---------+------+
| 韩信    | php0115 |   21 | 
| 韩信    | php0228 |   18 | 
| 韩信    | php0331 |   22 | 
| 李白    | php0115 |   20 | 
| 李白    | php0228 |   22 | 
| 韩非子 | php0115 |   15 | 
| 韩信    | php0331 |   15 | 
+-----------+---------+------+


All(集合)集合中的所有元素。


!=All(集合)不等与集合中的所有元素。等同于not in
mysql> select t_name,c_name,days from teacher_class where t_name !=all (select t_name from teacher_class where c_name = 'php0228');
+-----------+---------+------+
| t_name    | c_name  | days |
+-----------+---------+------+
| 韩非子 | php0115 |   15 | 
| 韩非子 | php0331 |   15 | 
+-----------+---------+------+
Some(集合)集合中的一些。
总结：
=any   <==>   in
!=all   <==>   not in
Some 和any同义词
All,any和some可以使用除了=,!=之外的运算符
Update teacher_class set t_name='zhanshan' where id='1';


=======================lession26=子查询(2)=============================
返回一行
在参与比较时，使用括号可以构建一行:(filed1,field2)
比如：
mysql> select t_name,gender,c_name from teacher_class where (gender,c_name) = (select distinct gender, c_name from teacher_class where t_name='李白' and c_name='php0115');
+-----------+--------+---------+
| t_name    | gender | c_name  |
+-----------+--------+---------+
| 韩信    | male   | php0115 | 
| 李白    | male   | php0115 | 
| 韩非子 | male   | php0115 | 
| 李白    | male   | php0115 | 
+-----------+--------+---------+




mysql> select gender, c_name from teacher_class where t_name='李白' and c_name='php0115';
+--------+---------+
| gender | c_name  |
+--------+---------+
| male   | php0115 | 
| male   | php0115 | 
+--------+---------+
mysql> select distinct gender, c_name from teacher_class where t_name='李白' and c_name='php0115';
+--------+---------+
| gender | c_name  |
+--------+---------+
| male   | php0115 | 
+--------+---------+


返回一个表：
Select * from (table) where cond;
Select * from (select t_name, c_name, days from teacher_class where days > 15) as temp;
mysql> select * from (select t_name, c_name, days from teacher_class where days > 15) as temp;
+--------+---------+------+
| t_name | c_name  | days |
+--------+---------+------+
| 韩信 | php0115 |   21 | 
| 韩信 | php0228 |   18 | 
| 韩信 | php0331 |   22 | 
| 李白 | php0115 |   20 | 
| 李白 | php0228 |   22 | 
+--------+---------+------+


mysql> select * from (select t_name, c_name, days from teacher_class where days > 15) as temp where t_name like '李%';
+--------+---------+------+
| t_name | c_name  | days |
+--------+---------+------+
| 李白 | php0115 |   20 | 
| 李白 | php0228 |   22 | 
+--------+---------+------+


外部查询所使用的列名，是由子查询指定。
Select teacher from (select t_name as teacher, c_name , days from teacher_class where days > 15) as temp where teacher like '李%';


mysql> select teacher from (select t_name as teacher, c_name, days from teacher_class  where days > 15) as temp where teacher like '李%';
+---------+
| teacher |
+---------+
| 李白  | 
| 李白  | 
+---------+
2 rows in set (0.00 sec)




=======================lession27=子查询(3)=============================
Exists


Exists(subquery)
判断依据：
如果子查询的，可以返回数据，则认为exists表达示，返回真。否则返回假。
mysql> select * from teacher_class where exists (select * from teacher 
where teacher_class.id=t_id);
+----+--------+--------+---------+------+------+------------+-----------
-+
| id | t_name | gender | c_name  | room | days | begin_date | 
end_date   |
+----+--------+--------+---------+------+------+------------+-----------
-+
|  1 | 韩信 | male   | php0115 |  207 |   21 | 2013-01-15 | 2013-02-20 | 
+----+--------+--------+---------+------+------+------------+-----------
-+
1 row in set (0.03 sec)


exists能实现的其他也可以实现的
Select * from teacher_class where id in (select t_id from teacher);
mysql> select * from teacher_class where id in (select t_id from 
teacher);  
//in的话要找地方存储起来，比如teacher有十万条记录，那么要找地方存起来in的数
据，而exists则不用存，因为它是一条一条检索的
+----+--------+--------+---------+------+------+------------+-----------
-+
| id | t_name | gender | c_name  | room | days | begin_date | 
end_date   |
+----+--------+--------+---------+------+------+------------+-----------
-+
|  1 | 韩信 | male   | php0115 |  207 |   21 | 2013-01-15 | 2013-02-20 | 
|  3 | 韩信 | male   | php0331 |  102 |   22 | 2013-03-31 | 2013-05-05 | 
+----+--------+--------+---------+------+------+------------+-----------
-+
2 rows in set (0.00 sec)


解决思路是不一样的：
Exists:先获得每一条teacher_class的数据，然后获得id字段，去teacher表内查找对
应值，找到，说明符合条件。


In:先获得所有的id的可能性。再去检索teacher_class数据时，判断当前的id是否在i
d集合内。


=======================lession28=连接查询=============================
Join


每一个实体，一个表一个业务逻辑，使用多个实体的数据，多张表应该在一起使用，将
多个表的记录连接起来。


总体思路：
将所有的数据，按照某种条件，连接起来，再进行筛选处理。


连接的分类：
根据连接的条件不同，分类如下：
内连接
外连接
自然连接


Create table join_teacher(
Id int primary key auto_increment,
T_name varchar(10),
Gender enum('male', 'female', 'secret')
) engine innodb character set utf8;


Insert into join_teacher values
(1,'韩信','male'),
(2, '李白', 'female'),
(3, '韩非子', 'secret');
insert into join_teacher values (4, '孙武','male');


Create table join_class(
Id int primary key auto_increment,
C_name char(7),
Room char(3)
) engine innodb character set utf8;


Insert into join_class values
(1,'php0115','207'),
(2,'php0228','104'),
(3,'php0331','102');
insert into join_class values(4,'php0505','202');


create table join_teacher_class(
id int primary key auto_increment,
t_id int,
c_id int,
days tinyint,
begin_date date,
end_date date
) engine innodb character set utf8;


insert into join_teacher_class values
(1, 1, 1, 15, '2013-01-15', '2013-02-20'),
(2, 1, 2, 18, '2013-02-28', '2013-03-30'),
(3, 1, 3, 22, '2013-03-31', '2013-05-05'),
(4, 2, 1, 20, '2013-02-22', '2013-03-25'),
(5, 2, 2, 22, '2013-03-31', '2013-04-29'),
(6, 3, 1, 15, '2013-03-27', '2013-04-18'),
(7, 1, 1, 15, '2013-04-19', '2013-05-01'),
(8, 3, 3, 15, '2013-05-28', '2013-06-15'),
(9, 2, 1, 5, '2013-05-04', '2013-05-15');


更改列的列名：
alter table tbl_name change column old_name new_name new_列的类型;
mysql> select * from join_teacher;
+----+-----------+--------+
| Id | T_name    | Gender |
+----+-----------+--------+
|  1 | 韩信    | male   | 
|  2 | 李白    | female | 
|  3 | 韩非子 | secret | 
|  4 | 孙武    | male   | 
+----+-----------+--------+
4 rows in set (0.00 sec)
ysql> alter table join_teacher change column Id id int;
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from join_teacher;
+----+-----------+--------+
| id | T_name    | Gender |
+----+-----------+--------+
|  1 | 韩信    | male   | 
|  2 | 李白    | female | 
|  3 | 韩非子 | secret | 
|  4 | 孙武    | male   | 
+----+-----------+--------+
4 rows in set (0.00 sec)






内连接：
数据内部的连接，要求，连接的多个数据都必须存在才能进行连接。
不包含连接不到的数据。


tbl_left inner join tbl_right on 连接条件


select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher inner join 
join_teacher_class on join_teacher.id = join_teacher_class.t_id;


ql> select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher inner join 
join_teacher_class on join_teacher.id = join_teacher_class.t_id;
+-----------+------------+------+
| t_name    | begin_date | days |
+-----------+------------+------+
| 韩信    | 2013-01-15 |   15 | 
| 韩信    | 2013-02-28 |   18 | 
| 韩信    | 2013-03-31 |   22 | 
| 韩信    | 2013-04-19 |   15 | 
| 李白    | 2013-02-22 |   20 | 
| 李白    | 2013-03-31 |   22 | 
| 李白    | 2013-05-04 |    5 | 
| 韩非子 | 2013-03-27 |   15 | 
| 韩非子 | 2013-05-28 |   15 | 
+-----------+------------+------+
9 rows in set (0.10 sec)


1.连接
(1,'韩信','male',1, 1, 1, 15, '2013-01-15','2013-02-20')


2,判断条件 on
将连接结果保留。


3.继续连接，重复第一步
(1,'韩信','male',2,1,2, 18, '2013-02-28','2013-03-30'),保留


(1, '韩信', 'male',5, 2, 2, 22, '2013-03-31', '2013-04-29'),不满足
不满足，条件，连接结果不保留




外连接：
如果负责连接的一个或多个数据不真实存在，则称之为外连接
（孙武老师虽然没有连接上，但也出现在结果内）


select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher left outer join 
join_teacher_class on join_teacher.id = join_teacher_class.t_id;


ysql> select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher left outer join 
join_teacher_class on join_teacher.id = join_teacher_class.t_id;
+-----------+------------+------+
| t_name    | begin_date | days |
+-----------+------------+------+
| 韩信    | 2013-01-15 |   15 | 
| 韩信    | 2013-02-28 |   18 | 
| 韩信    | 2013-03-31 |   22 | 
| 韩信    | 2013-04-19 |   15 | 
| 李白    | 2013-02-22 |   20 | 
| 李白    | 2013-03-31 |   22 | 
| 李白    | 2013-05-04 |    5 | 
| 韩非子 | 2013-03-27 |   15 | 
| 韩非子 | 2013-05-28 |   15 | 
| 孙武    | NULL       | NULL | 
+-----------+------------+------+
10 rows in set (0.00 sec)


内链接的处理：
内链接，在连接时，是可以省略连接条件的。意味着，所有的左表的数据，都要与
右表的记录做一个连接。
共存在M*N个连接
这中连接，就称之为，交叉连接，或者笛卡尔积。
select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher inner join join_teacher_class;


select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher cross join join_teacher_class;


select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher join join_teacher_class;






可以通过多表查询，不使用where,做到笛卡尔积。
select join_teacher.t_name, join_teacher_class.begin_date, 
join_teacher_class.days from join_teacher ,join_teacher_class;


注意：mysql中cross join 与 inner join 相同，但在数据库的定义上，交叉
连接就是笛卡尔积。是没有条件的inner join.


mysql inner join 是默认的连接方案，可以省略inner.


有条件的内连接：
会在连接时过滤非法的连接。


where:数据过滤条件
on:数据连接条件
where写法：数据过滤，理解上，数据按照交叉连接连接完成后，再做数据过滤。
on的写法： 在连接时，就对数据进行判断。
using的写法:using要求，负责连接的两个实体之间的字段名称，一致。


建议是，在有同名字段时，使用using,而在通用条件时，使用on.在数据过滤时（不
是指的连接过滤）使用where.


select id, days, t_name from join_teacher inner join 
join_teacher_class using (id);


select * from join_teacher join join_teacher_class;


注意：查询条件，与外连接通用（外连接，不能使用where作为连接条件）；


=======================lession29=外连接==============================


注意： 无论是连接条件，还是连接查询多字段列表，都没有必要一定要写表名.字段
的语法。是否写，取决于是否发生冲突，冲突需要写，不冲突，无所谓。


建议：如果可以写上，保证代码的可读性。


别名：
表别名，保证简介和清晰
sql> select t.t_name, tc.begin_date, tc.days from join_teacher as t 
inner join join_teacher_class as tc where t.id=tc.t_id and tc.days > 15;
t_name | begin_date | days |
+--------+------------+------+
| 韩信 | 2013-02-28 |   18 | 
| 韩信 | 2013-03-31 |   22 | 
| 李白 | 2013-02-22 |   20 | 
| 李白 | 2013-03-31 |   22 | 
+--------+------------+------+
4 rows in set (0.00 sec)


列别名：
select t.id, tc.id, t.t_name, tc.begin_date, tc.days from join_teacher 
as t inner join join_teacher_class as tc where t.id=tc.t_id and 
tc.days > 15;


mysql> select t.id, tc.id, t.t_name, tc.begin_date, tc.days from 
join_teacher as t inner join join_teacher_class as tc where 
t.id=tc.t_id and tc.days > 15;
+----+----+--------+------------+------+
| id | id | t_name | begin_date | days |
+----+----+--------+------------+------+
|  1 |  2 | 韩信 | 2013-02-28 |   18 | 
|  1 |  3 | 韩信 | 2013-03-31 |   22 | 
|  2 |  4 | 李白 | 2013-02-22 |   20 | 
|  2 |  5 | 李白 | 2013-03-31 |   22 | 
+----+----+--------+------------+------+
4 rows in set (0.00 sec)


select t.id as t_id, tc.id as tc_id, t.t_name, tc.begin_date, tc.days 
from join_teacher as t inner join join_teacher_class as tc where 
t.id=tc.t_id and tc.days > 15;


mysql> select t.id as t_id, tc.id as tc_id, t.t_name, tc.begin_date, 
tc.days from join_teacher as t inner join join_teacher_class as tc 
where t.id=tc.t_id and tc.days > 15;
+------+-------+--------+------------+------+
| t_id | tc_id | t_name | begin_date | days |
+------+-------+--------+------------+------+
|    1 |     2 | 韩信 | 2013-02-28 |   18 | 
|    1 |     3 | 韩信 | 2013-03-31 |   22 | 
|    2 |     4 | 李白 | 2013-02-22 |   20 | 
|    2 |     5 | 李白 | 2013-03-31 |   22 | 
+------+-------+--------+------------+------+
4 rows in set (0.00 sec)


外连接：
分类：
左外连接
右外连接
全外连接 （mysql暂不支持）


左连接：
在连接时，如果出现左边表，数据连接不到右边表的情况，则左边表的数据在最终结
果内被保留。而如果出现右表数据连接不到左表的情况，右表数据被丢弃。
select t.id as t_id, tc.id as tc_id, t.t_name, tc.begin_date, 
tc.days from join_teacher as t left  join join_teacher_class as tc 
on t.id=tc.t_id;
mysql> select t.id as t_id, tc.id as tc_id, t.t_name, tc.begin_date, 
    -> tc.days from join_teacher as t left  join join_teacher_class as 
tc 
    -> on t.id=tc.t_id;
+------+-------+-----------+------------+------+
| t_id | tc_id | t_name    | begin_date | days |
+------+-------+-----------+------------+------+
|    1 |     1 | 韩信    | 2013-01-15 |   15 | 
|    1 |     2 | 韩信    | 2013-02-28 |   18 | 
|    1 |     3 | 韩信    | 2013-03-31 |   22 | 
|    1 |     7 | 韩信    | 2013-04-19 |   15 | 
|    2 |     4 | 李白    | 2013-02-22 |   20 | 
|    2 |     5 | 李白    | 2013-03-31 |   22 | 
|    2 |     9 | 李白    | 2013-05-04 |    5 | 
|    3 |     6 | 韩非子 | 2013-03-27 |   15 | 
|    3 |     8 | 韩非子 | 2013-05-28 |   15 | 
|    4 |  NULL | 孙武    | NULL       | NULL | 
+------+-------+-----------+------------+------+
10 rows in set (0.00 sec)


drop table if exists one;
create table one(
one_id int,
one_data char(1),
public_field int
);
insert into one values
(1,'a',10),
(2,'b',20),
(3,'c',30);


drop table if exists two;
create table  two(
two_id int,
two_data char(1) not null default 't',
public_field int
);
insert into two values
(2,'B',20),
(3,'C',30),
(4,'D',40);


左连接：
select * from one left join two on one_id=two_id;
mysql> select * from one left join two on one_id=two_id;
+--------+----------+--------------+--------+----------+--------------+
| one_id | one_data | public_field | two_id | two_data | public_field |
+--------+----------+--------------+--------+----------+--------------+
|      1 | a        |           10 |   NULL | NULL     |         NULL | 
|      2 | b        |           20 |      2 | B        |           20 | 
|      3 | c        |           30 |      3 | C        |           30 | 
+--------+----------+--------------+--------+----------+--------------+
3 rows in set (0.00 sec)


右连接：
select * from one right join two on one_id=two_id;
mysql> select * from one right join two on one_id=two_id;
+--------+----------+--------------+--------+----------+--------------+
| one_id | one_data | public_field | two_id | two_data | public_field |
+--------+----------+--------------+--------+----------+--------------+
|      2 | b        |           20 |      2 | B        |           20 | 
|      3 | c        |           30 |      3 | C        |           30 | 
|   NULL | NULL     |         NULL |      4 | D        |           40 | 
+--------+----------+--------------+--------+----------+--------------+
3 rows in set (0.00 sec)


内链接：
select * from one inner join two on one_id=two_id;
mysql> select * from one inner join two on one_id=two_id;
+--------+----------+--------------+--------+----------+--------------+
| one_id | one_data | public_field | two_id | two_data | public_field |
+--------+----------+--------------+--------+----------+--------------+
|      2 | b        |           20 |      2 | B        |           20 | 
|      3 | c        |           30 |      3 | C        |           30 | 
+--------+----------+--------------+--------+----------+--------------+
2 rows in set (0.00 sec)
左连接+右连接 = 全外连接
mysql> (select * from one left join two on one_id=two_id)
    -> union
    -> (select * from one right join two on one_id=two_id);
+--------+----------+--------------+--------+----------+--------------+
| one_id | one_data | public_field | two_id | two_data | public_field |
+--------+----------+--------------+--------+----------+--------------+
|      1 | a        |           10 |   NULL | NULL     |         NULL | 
|      2 | b        |           20 |      2 | B        |           20 | 
|      3 | c        |           30 |      3 | C        |           30 | 
|   NULL | NULL     |         NULL |      4 | D        |           40 | 
+--------+----------+--------------+--------+----------+--------------+
4 rows in set (0.00 sec)


using会去掉重复字段，并且会放在最前一列，建议使用，因为去掉重复对人友好
select * from one left join two using (public_field);
mysql> select * from one left join two using (public_field);
+--------------+--------+----------+--------+----------+
| public_field | one_id | one_data | two_id | two_data |
+--------------+--------+----------+--------+----------+
|           10 |      1 | a        |   NULL | NULL     | 
|           20 |      2 | b        |      2 | B        | 
|           30 |      3 | c        |      3 | C        | 
+--------------+--------+----------+--------+----------+
3 rows in set (0.00 sec)


select * from one left join two on one.public_field=two.public_field;
mysql> select * from one left join two on 
one.public_field=two.public_field;
+--------+----------+--------------+--------+----------+--------------+
| one_id | one_data | public_field | two_id | two_data | public_field |
+--------+----------+--------------+--------+----------+--------------+
|      1 | a        |           10 |   NULL | NULL     |         NULL | 
|      2 | b        |           20 |      2 | B        |           20 | 
|      3 | c        |           30 |      3 | C        |           30 | 
+--------+----------+--------------+--------+----------+--------------+
3 rows in set (0.00 sec)


=======================lession30=自然连接==============================
自然连接：
通过mysql自己的判断完成连接过程：
不需要指定连接条件
mysql会使用多表内的，相同的字段，作为连接条件。


select * from one natural join two; 相当于
select * from one inner join  two using(public_field);
mysql> select * from one natural join two;
+--------------+--------+----------+--------+----------+
| public_field | one_id | one_data | two_id | two_data |
+--------------+--------+----------+--------+----------+
|           20 |      2 | b        |      2 | B        | 
|           30 |      3 | c        |      3 | C        | 
+--------------+--------+----------+--------+----------+
2 rows in set (0.01 sec)
mysql> select * from one inner join  two using(public_field);
+--------------+--------+----------+--------+----------+
| public_field | one_id | one_data | two_id | two_data |
+--------------+--------+----------+--------+----------+
|           20 |      2 | b        |      2 | B        | 
|           30 |      3 | c        |      3 | C        | 
+--------------+--------+----------+--------+----------+
2 rows in set (0.00 sec)




自然连接分成内外之分：
内，natural join
外，左外，右外   (左右是相对与join来说的)
natural letf join
natural right join


select * from one natural left join two;相当于
select * from one left join two using (public_field);
mysql> select * from one natural left join two;
+--------------+--------+----------+--------+----------+
| public_field | one_id | one_data | two_id | two_data |
+--------------+--------+----------+--------+----------+
|           10 |      1 | a        |   NULL | NULL     | 
|           20 |      2 | b        |      2 | B        | 
|           30 |      3 | c        |      3 | C        | 
+--------------+--------+----------+--------+----------+
3 rows in set (0.00 sec)
mysql> select * from one left join two using (public_field);
+--------------+--------+----------+--------+----------+
| public_field | one_id | one_data | two_id | two_data |
+--------------+--------+----------+--------+----------+
|           10 |      1 | a        |   NULL | NULL     | 
|           20 |      2 | b        |      2 | B        | 
|           30 |      3 | c        |      3 | C        | 
+--------------+--------+----------+--------+----------+
3 rows in set (0.00 sec)






select * from one natural right join two;相当于
select * from one right join two using(public_field);
mysql> select * from one natural right join two;
+--------------+--------+----------+--------+----------+
| public_field | two_id | two_data | one_id | one_data |
+--------------+--------+----------+--------+----------+
|           20 |      2 | B        |      2 | b        | 
|           30 |      3 | C        |      3 | c        | 
|           40 |      4 | D        |   NULL | NULL     | 
+--------------+--------+----------+--------+----------+
3 rows in set (0.00 sec)
mysql> select * from one right join two using(public_field);
+--------------+--------+----------+--------+----------+
| public_field | two_id | two_data | one_id | one_data |
+--------------+--------+----------+--------+----------+
|           20 |      2 | B        |      2 | b        | 
|           30 |      3 | C        |      3 | c        | 
|           40 |      4 | D        |   NULL | NULL     | 
+--------------+--------+----------+--------+----------+
3 rows in set (0.00 sec)


info_class; id, class_name.
info_student; id,student_name, class_id.
info_student_info: id, student_detail.


作业：
连接时支持多表连接：
select s.*, si.* from info_class as c left join info_student as s on
c.id = s.class_id left join info_student_info as si on s.id = si.id 
where c.class_name='php0331';


作业2：许多著名公司的面试题
参考图片：

L30自然连接后的作业2.JPG

提示：
Match left join class on match.host_id=class.id left join class on
match.guest_id=class.id


mysql> drop table if exists class;


mysql> create table class(
    -> id tinyint primary key auto_increment,
    -> class_name char(7)
    -> )character set utf8;
Query OK, 0 rows affected (0.10 sec)


mysql> insert into class values
    -> (2,'php0331'),
    -> (5,'php0228'),
    -> (1,'php0115'),
    -> (7,'php0505');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from class;
+----+------------+
| id | class_name |
+----+------------+
|  2 | php0331    | 
|  5 | php0228    | 
|  1 | php0115    | 
|  7 | php0505    | 
+----+------------+
4 rows in set (0.12 sec)


// match应该是mysql的关键字创建的时候老出现问题
mysql> create table match( id tinyint primary key auto_increment, 
host_id tinyint, guest_id tinyint, match_time date, match_result 
char(7) )character set utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near 'match( id tinyint primary key auto_increment, 
host_id tinyint, guest_id tinyint,' at line 1




mysql> create table game( 
id tinyint primary key auto_increment,
 host_id tinyint, 
 guest_id tinyint,
 match_time date,
 match_result char(7) )character set utf8;
Query OK, 0 rows affected (0.01 sec)


mysql> insert into game values 
(null,2,1,'2013-04-09','34:28'), 
(null,2,7,'2013-03-09','35:45');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from game;
+----+---------+----------+------------+--------------+
| id | host_id | guest_id | match_time | match_result |
+----+---------+----------+------------+--------------+
|  1 |       2 |        1 | 2013-04-09 | 34:28        | 
|  2 |       2 |        1 | 2013-04-09 | 34:28        | 
|  3 |       2 |        7 | 2013-03-09 | 35:45        | 
+----+---------+----------+------------+--------------+
3 rows in set (0.00 sec)
l> delete from game where id = 1;
Query OK, 1 row affected (0.05 sec)
mysql> select * from game;
+----+---------+----------+------------+--------------+
| id | host_id | guest_id | match_time | match_result |
+----+---------+----------+------------+--------------+
|  2 |       2 |        1 | 2013-04-09 | 34:28        | 
|  3 |       2 |        7 | 2013-03-09 | 35:45        | 
+----+---------+----------+------------+--------------+
2 rows in set (0.00 sec)


mysql> select c.class_name,c2.class_name,g.match_time,g.match_result 
from
    -> game as g left join class as c on g.host_id=c.id
    -> left join class as c2 on g.guest_id=c2.id;
+------------+------------+------------+--------------+
| class_name | class_name | match_time | match_result |
+------------+------------+------------+--------------+
| php0331    | php0115    | 2013-04-09 | 34:28        | 
| php0331    | php0505    | 2013-03-09 | 35:45        | 
+------------+------------+------------+--------------+
2 rows in set (0.00 sec)




mysql> select c.class_name,c2.class_name, g.match_time, g.match_result 
from game as g left join class as c on g.host_id=c.id left join class 
as c2 on g.guest_id=c2.id limit 1;
+------------+------------+------------+--------------+
| class_name | class_name | match_time | match_result |
+------------+------------+------------+--------------+
| php0331    | php0115    | 2013-04-09 | 34:28        | 
+------------+------------+------------+--------------+
1 row in set (0.00 sec)






作业3：

参考图片：L30自然连接后的作业3.JPG

1.获得所有的顶级部门（即parent_id = 0）
2.获得教学部内所有的子部门。(法1，用subquery；法2 用(2)join)


mysql> create table dept(
    -> id tinyint primary key auto_increment,
    -> dept_name char(9),
    -> parent_id tinyint
    -> )character set utf8;
Query OK, 0 rows affected (0.00 sec)


1.获得所有顶级部门
mysql> select * from dept where parent_id=0;
+----+-----------+-----------+
| id | dept_name | parent_id |
+----+-----------+-----------+
|  1 | 行政部 |         0 | 
|  2 | 教学部 |         0 | 
+----+-----------+-----------+
2 rows in set (0.04 sec)


2.获得教学部内所有的子部门 
法一：subquery
mysql> select * from dept where parent_id=
    -> (select id from dept where dept_name='教学部');
+----+-----------+-----------+
| id | dept_name | parent_id |
+----+-----------+-----------+
|  3 | php       |         2 | 
|  4 | java      |         2 | 
|  5 | .net      |         2 | 
|  6 | 平面    |         2 | 
+----+-----------+-----------+
4 rows in set (0.00 sec)


法二：join
mysql> select * from dept as son inner join dept as pa on 
(son.parent_id=pa.id and pa.dept_name='教学部');
+----+-----------+-----------+----+-----------+-----------+
| id | dept_name | parent_id | id | dept_name | parent_id |
+----+-----------+-----------+----+-----------+-----------+
|  3 | php       |         2 |  2 | 教学部 |         0 | 
|  4 | java      |         2 |  2 | 教学部 |         0 | 
|  5 | .net      |         2 |  2 | 教学部 |         0 | 
|  6 | 平面    |         2 |  2 | 教学部 |         0 | 
+----+-----------+-----------+----+-----------+-----------+
4 rows in set (0.02 sec)
优化：
mysql> select son.id,son.dept_name,pa.dept_name from dept as son inner 
join dept as pa on (son.parent_id=pa.id and pa.dept_name='教学部');
+----+-----------+-----------+
| id | dept_name | dept_name |
+----+-----------+-----------+
|  3 | php       | 教学部 | 
|  4 | java      | 教学部 | 
|  5 | .net      | 教学部 | 
|  6 | 平面    | 教学部 | 
+----+-----------+-----------+
在优化：（改了教学部dept_name为parent）
mysql> select son.id,son.dept_name,pa.dept_name as parent from dept as 
son inner join dept as pa on (son.parent_id=pa.id and 
pa.dept_name='教学部');
+----+-----------+-----------+
| id | dept_name | parent    |
+----+-----------+-----------+
|  3 | php       | 教学部 | 
|  4 | java      | 教学部 | 
|  5 | .net      | 教学部 | 
|  6 | 平面    | 教学部 | 
+----+-----------+-----------+
4 rows in set (0.00 sec)


4 rows in set (0.00 sec)
注意：如果不是inner join的话，会保留所有哦比如
mysql> select * from dept as son left join dept as pa on
    -> (son.parent_id=pa.id and pa.dept_name='教学部');
+----+-----------+-----------+------+-----------+-----------+
| id | dept_name | parent_id | id   | dept_name | parent_id |
+----+-----------+-----------+------+-----------+-----------+
|  1 | 行政部 |         0 | NULL | NULL      |      NULL | 
|  2 | 教学部 |         0 | NULL | NULL      |      NULL | 
|  3 | php       |         2 |    2 | 教学部 |         0 | 
|  4 | java      |         2 |    2 | 教学部 |         0 | 
|  5 | .net      |         2 |    2 | 教学部 |         0 | 
|  6 | 平面    |         2 |    2 | 教学部 |         0 | 
|  7 | 咨询    |         1 | NULL | NULL      |      NULL | 
|  8 | 财务    |         1 | NULL | NULL      |      NULL | 
+----+-----------+-----------+------+-----------+-----------+
8 rows in set (0.00 sec)