6_mysql查询之from子句

专门的关系运算符

选择 σ
选择(对行进行操作) 它是将数据表符合条件的一行或多行记录提取出来
例如：查询学生表'IS'系的全体学生
投影 Π
投影(对列进行操作) 它从数据表中选出若干列组成新的关系
例如：查询学生表中都有哪些系，即查询关系student上所在系属性上的投影
补充：投影过后，会自动避免重复行
连接 ▷◁
连接join 它是从两个关系的笛卡尔积中选取属性间满足一定条件的元组
"这里元组通俗讲就是一条记录、一行数据"

关系R和关系S

等值连接

等值连接
自然连接
一般的连接操作是从行的角度进行运算，但自然连接还需要取消重复列，因而自然连接是从行和列的角度进行运算。

自然连接
外连接|左外连接|右外连接
在自然连接中被舍弃的元组称作"悬浮元组"，如果把悬浮元组保存在结果关系中，而在其它属性上填空值（Null），此连接称作外连接；如果只保留左边关系R中的悬浮元组叫做左外连接；如果只保留右边关系S中的悬浮元组叫做右外连接

外连接|左外连接|右外连接

除 ÷
一般使用象集来定义除法。
例如：查询至少选修1号课程和3号课程的学生学号。

FROM子句

引用一个表
方式一：
使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库
方式二：
指定的时候在表名前带上表所属数据库的名字
例如假设当前数据库是db1，现在要显示数据库db2里的表tb的内容
SELECT * FROM db2.tb;
多表连接
如果要在不同的表中查询数据，则必须在from子句中指定多个表，这时就要使用到连接。将不同列的数据组合到一个表中叫做表的连接。

(1) 全连接
-- 将各个表用逗号分隔，就指定了一个全连接；
-- FROM子句产生的中间结果是一个新表，新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积；
--这种连接方式潜在的会产生数量庞大的行，因为可能得到的行是每个表行数之积；
--这种情况我们通常会使用where子句设定条件将结果集减少为易于管理的大小，这样的连接即为等值连接。
举例：查找学生选修了哪些课程这里通过成绩表和课程表连接实现
select distinct course.Cno,course.Cname from score,course where score.Cno = course.Cno;

mysql> select distinct course.Cno,course.Cname from score,course where score.Cno = course.Cno;
+-----+-----------------+
| Cno | Cname           |
+-----+-----------------+
| 001 | 数学分析        |
| 003 | 微机原理        |
| 004 | 数据结构        |
| 005 | 操作系统        |
| 006 | 数据库原理      |
| 008 | 程序设计        |
+-----+-----------------+
6 rows in set (0.00 sec)

mysql> select Cno,Cname from course;
+-----+-----------------+
| Cno | Cname           |
+-----+-----------------+
| 001 | 数学分析        |
| 002 | 普通物理        |
| 003 | 微机原理        |
| 004 | 数据结构        |
| 005 | 操作系统        |
| 006 | 数据库原理      |
| 007 | 数据库设计      |
| 008 | 程序设计        |
+-----+-----------------+
8 rows in set (0.00 sec)

(2) join连接
使用join关键字进行连接，主要分为三种。

内连接 inner
-- 指定了INNER关键字的连接是内连接
-- 在上面的全连接中，FROM子句中产生的中间结果是两个表的笛卡儿积，然后再通过where子句进行限制。内连接中FROM子句产生的中间结果，是应用了ON条件后的笛卡儿积。
-- 内连接是系统默认的可以省略inner关键字，on条件主要用来连接表，其它不属于连接表的条件都可使用where子句来指定
select distinct course.Cno,course.Cname from score,course where score.Cno = course.Cno;
等同于
select distinct course.Cno,course.Cname from score inner join course on score.Cno = course.Cno;

举例：
例子一 (on连接表,where指定其它)用FROM子句的JOIN关键字表达下列查询：查找选修了206课程且成绩在80分以上的学生姓名及成绩

select 姓名,成绩
from xs join xs_kc on xs.学号 = xs_kc.学号 
where 课程号 = '206' and 成绩>=80;

例子二 (内连接用于多个表的连接) 查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩

select xs.学号, 姓名, 课程名, 成绩
from xs join xs_kc on xs.学号 = xs_kc.学号 
join kc on xs_kc.课程号 = kc.课程号
where 课程名 = '计算机基础' and 成绩>=80;

例子三 (自连接) 查找xscj数据库中课程不同、成绩相同的学生的学号、课程号和成绩

# 使用自连接的时候需要为表指定两个别名，且对所有列的引用均要用别名进行限定
select a.学号,a.课程号,b.课程号,a.成绩
from xs_kc as a join xs_kc as b    
on a.成绩=b.成绩 and a.学号=b.学号 and a.课程号!=b.课程号;

例子四 (using子句) using(column_list) column_list为两表中相同的列名
select distinct course.Cno,course.Cname from score inner join course on score.Cno = course.Cno;
等同于
select distinct course.Cno,course.Cname from score join course using (CNO);

外连接 outer
指定了OUTER关键字的连接为外连接，OUTER关键字可以省略。
注意：外连接只能对两个表进行！！！！
外连接包括：
① 左外连接（LEFT OUTER JOIN）：结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。
② 右外连接（RIGHT OUTER JOIN）：结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。
③ 自然连接（NATURAL JOIN）：自然连接还有自然左外连接（NATURAL LEFT OUTER JOIN）和自然右外连接（NATURAL RIGHT OUTER JOIN）
举例：
select score.*,course.Cname from score left join course on score.Cno = course.Cno;
说明：select语句中只选取一个用来连接表的列时，可以使用自然连接代替内连接。用这种方法，可以用自然左外连接来替换左外连接，自然右外连接替换右连接。

# 内连接
root@localhost >select * from student inner join score on student.sno = score.sno;
+-------+-----------+-------+--------+------+------+-------+------+-------+-------+
| Sno   | Sname     | Sdept | Sclass | Ssex | Sage | Sno   | Cno  | Score | Tdept |
+-------+-----------+-------+--------+------+------+-------+------+-------+-------+
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 96001 | 001  | 77.50 | NULL  |
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 96001 | 003  | 89.00 | NULL  |
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 96001 | 004  | 86.00 | NULL  |
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 96001 | 005  | 82.00 | NULL  |
| 96002 | 黎明      | CS    | 01     | 男   |   18 | 96002 | 001  | 88.00 | NULL  |
| 96002 | 黎明      | CS    | 01     | 男   |   18 | 96002 | 003  | 92.50 | NULL  |
| 96002 | 黎明      | CS    | 01     | 男   |   18 | 96002 | 006  | 90.00 | NULL  |
| 96003 | 刘东明    | MA    | 01     | 男   |   18 | 96003 | 001  | 69.00 | NULL  |
| 96003 | 刘东明    | MA    | 01     | 男   |   18 | 96003 | 003  | 91.00 | NULL  |
| 96004 | 赵志勇    | IS    | 02     | 男   |   20 | 96004 | 001  | 87.00 | NULL  |
| 97001 | 马蓉      | MA    | 02     | 女   |   19 | 97001 | 001  | 96.00 | NULL  |
| 97001 | 马蓉      | MA    | 02     | 女   |   19 | 97001 | 008  | 95.00 | NULL  |
| 97002 | 李成功    | CS    | 01     | 男   |   20 | 97002 | 003  | 91.00 | NULL  |
| 97002 | 李成功    | CS    | 01     | 男   |   20 | 97002 | 004  |  NULL | NULL  |
| 97002 | 李成功    | CS    | 01     | 男   |   20 | 97002 | 006  | 92.00 | NULL  |
| 97003 | 黎明      | IS    | 03     | 女   |   19 | 97003 | 001  | 59.00 | NULL  |
| 97003 | 黎明      | IS    | 03     | 女   |   19 | 97003 | 003  | 58.00 | NULL  |
| 97004 | 李丽      | CS    | 02     | 女   |   19 | 97004 | 005  | 90.00 | NULL  |
| 97004 | 李丽      | CS    | 02     | 女   |   19 | 97004 | 006  | 85.00 | NULL  |
| 97004 | 李丽      | CS    | 02     | 女   |   19 | 97004 | 008  | 70.00 | NULL  |
+-------+-----------+-------+--------+------+------+-------+------+-------+-------+
20 rows in set (0.00 sec)

# 在这里 自然连接 等同于 内连接
root@localhost >select * from student natural join score;
+-------+-----------+-------+--------+------+------+------+-------+-------+
| Sno   | Sname     | Sdept | Sclass | Ssex | Sage | Cno  | Score | Tdept |
+-------+-----------+-------+--------+------+------+------+-------+-------+
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 001  | 77.50 | NULL  |
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 003  | 89.00 | NULL  |
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 004  | 86.00 | NULL  |
| 96001 | 马小燕    | CS    | 01     | 女   |   21 | 005  | 82.00 | NULL  |
| 96002 | 黎明      | CS    | 01     | 男   |   18 | 001  | 88.00 | NULL  |
| 96002 | 黎明      | CS    | 01     | 男   |   18 | 003  | 92.50 | NULL  |
| 96002 | 黎明      | CS    | 01     | 男   |   18 | 006  | 90.00 | NULL  |
| 96003 | 刘东明    | MA    | 01     | 男   |   18 | 001  | 69.00 | NULL  |
| 96003 | 刘东明    | MA    | 01     | 男   |   18 | 003  | 91.00 | NULL  |
| 96004 | 赵志勇    | IS    | 02     | 男   |   20 | 001  | 87.00 | NULL  |
| 97001 | 马蓉      | MA    | 02     | 女   |   19 | 001  | 96.00 | NULL  |
| 97001 | 马蓉      | MA    | 02     | 女   |   19 | 008  | 95.00 | NULL  |
| 97002 | 李成功    | CS    | 01     | 男   |   20 | 003  | 91.00 | NULL  |
| 97002 | 李成功    | CS    | 01     | 男   |   20 | 004  |  NULL | NULL  |
| 97002 | 李成功    | CS    | 01     | 男   |   20 | 006  | 92.00 | NULL  |
| 97003 | 黎明      | IS    | 03     | 女   |   19 | 001  | 59.00 | NULL  |
| 97003 | 黎明      | IS    | 03     | 女   |   19 | 003  | 58.00 | NULL  |
| 97004 | 李丽      | CS    | 02     | 女   |   19 | 005  | 90.00 | NULL  |
| 97004 | 李丽      | CS    | 02     | 女   |   19 | 006  | 85.00 | NULL  |
| 97004 | 李丽      | CS    | 02     | 女   |   19 | 008  | 70.00 | NULL  |
+-------+-----------+-------+--------+------+------+------+-------+-------+
20 rows in set (0.00 sec)

# 在这里 自然左外连接 等同于 左外连接 
root@localhost >select * from student natural left outer join score;
+-------+--------------+-------+--------+------+------+------+-------+-------+
| Sno   | Sname        | Sdept | Sclass | Ssex | Sage | Cno  | Score | Tdept |
+-------+--------------+-------+--------+------+------+------+-------+-------+
| 20001 | 赵薇         | IS    | 02     | 女   |   19 | NULL |  NULL | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 001  | 77.50 | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 003  | 89.00 | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 004  | 86.00 | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 005  | 82.00 | NULL  |
| 96002 | 黎明         | CS    | 01     | 男   |   18 | 001  | 88.00 | NULL  |
| 96002 | 黎明         | CS    | 01     | 男   |   18 | 003  | 92.50 | NULL  |
| 96002 | 黎明         | CS    | 01     | 男   |   18 | 006  | 90.00 | NULL  |
| 96003 | 刘东明       | MA    | 01     | 男   |   18 | 001  | 69.00 | NULL  |
| 96003 | 刘东明       | MA    | 01     | 男   |   18 | 003  | 91.00 | NULL  |
| 96004 | 赵志勇       | IS    | 02     | 男   |   20 | 001  | 87.00 | NULL  |
| 97001 | 马蓉         | MA    | 02     | 女   |   19 | 001  | 96.00 | NULL  |
| 97001 | 马蓉         | MA    | 02     | 女   |   19 | 008  | 95.00 | NULL  |
| 97002 | 李成功       | CS    | 01     | 男   |   20 | 003  | 91.00 | NULL  |
| 97002 | 李成功       | CS    | 01     | 男   |   20 | 004  |  NULL | NULL  |
| 97002 | 李成功       | CS    | 01     | 男   |   20 | 006  | 92.00 | NULL  |
| 97003 | 黎明         | IS    | 03     | 女   |   19 | 001  | 59.00 | NULL  |
| 97003 | 黎明         | IS    | 03     | 女   |   19 | 003  | 58.00 | NULL  |
| 97004 | 李丽         | CS    | 02     | 女   |   19 | 005  | 90.00 | NULL  |
| 97004 | 李丽         | CS    | 02     | 女   |   19 | 006  | 85.00 | NULL  |
| 97004 | 李丽         | CS    | 02     | 女   |   19 | 008  | 70.00 | NULL  |
| 97005 | 司马志明     | CS    | 02     | 男   |   18 | NULL |  NULL | NULL  |
+-------+--------------+-------+--------+------+------+------+-------+-------+
22 rows in set (0.01 sec)

# 左外连接
root@localhost >select * from student left outer join score on student.sno = score.sno;
+-------+--------------+-------+--------+------+------+-------+------+-------+-------+
| Sno   | Sname        | Sdept | Sclass | Ssex | Sage | Sno   | Cno  | Score | Tdept |
+-------+--------------+-------+--------+------+------+-------+------+-------+-------+
| 20001 | 赵薇         | IS    | 02     | 女   |   19 | NULL  | NULL |  NULL | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 96001 | 001  | 77.50 | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 96001 | 003  | 89.00 | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 96001 | 004  | 86.00 | NULL  |
| 96001 | 马小燕       | CS    | 01     | 女   |   21 | 96001 | 005  | 82.00 | NULL  |
| 96002 | 黎明         | CS    | 01     | 男   |   18 | 96002 | 001  | 88.00 | NULL  |
| 96002 | 黎明         | CS    | 01     | 男   |   18 | 96002 | 003  | 92.50 | NULL  |
| 96002 | 黎明         | CS    | 01     | 男   |   18 | 96002 | 006  | 90.00 | NULL  |
| 96003 | 刘东明       | MA    | 01     | 男   |   18 | 96003 | 001  | 69.00 | NULL  |
| 96003 | 刘东明       | MA    | 01     | 男   |   18 | 96003 | 003  | 91.00 | NULL  |
| 96004 | 赵志勇       | IS    | 02     | 男   |   20 | 96004 | 001  | 87.00 | NULL  |
| 97001 | 马蓉         | MA    | 02     | 女   |   19 | 97001 | 001  | 96.00 | NULL  |
| 97001 | 马蓉         | MA    | 02     | 女   |   19 | 97001 | 008  | 95.00 | NULL  |
| 97002 | 李成功       | CS    | 01     | 男   |   20 | 97002 | 003  | 91.00 | NULL  |
| 97002 | 李成功       | CS    | 01     | 男   |   20 | 97002 | 004  |  NULL | NULL  |
| 97002 | 李成功       | CS    | 01     | 男   |   20 | 97002 | 006  | 92.00 | NULL  |
| 97003 | 黎明         | IS    | 03     | 女   |   19 | 97003 | 001  | 59.00 | NULL  |
| 97003 | 黎明         | IS    | 03     | 女   |   19 | 97003 | 003  | 58.00 | NULL  |
| 97004 | 李丽         | CS    | 02     | 女   |   19 | 97004 | 005  | 90.00 | NULL  |
| 97004 | 李丽         | CS    | 02     | 女   |   19 | 97004 | 006  | 85.00 | NULL  |
| 97004 | 李丽         | CS    | 02     | 女   |   19 | 97004 | 008  | 70.00 | NULL  |
| 97005 | 司马志明     | CS    | 02     | 男   |   18 | NULL  | NULL |  NULL | NULL  |
+-------+--------------+-------+--------+------+------+-------+------+-------+-------+
22 rows in set (0.00 sec)

交叉连接 cross join
指定了CROSS JOIN关键字的连接是交叉连接
在mysql中，cross join从语法上与inner join相同
另外 straight_join连接用法和inner join连接相同，不同的是straight_join后不可以使用using子句代替on条件。