ksweb mysql 数据库6_MySQL （六）数据库的查询

继续上一篇中的问题

【常用的Select选择语句】

二、【 Select 语句之 from 子句的使用 】

1、【原理说明】

前面介绍了使用SELECT子句选择列，本小节讨论SELECT查询的对象(即数据源)的构成形式。先来看看下面的这个例子：

usexscjselect姓名,专业名,总学分from xs;

(1)实际上，这句话的逻辑是： select 子句指定了现在要进行选择操作了，但是对哪个表进行操作呢？被操作的表有from子句指定，换句话说：对于select语句来说，首先应该由from子句指定操作数据源(虽然from子句位于select之后)

(2)还有一个问题值得注意的是：from子句的执行结果实际上是一个临时表，比如上面的from xs 语句 实际上生成了一张和源xs表一样的临时表，所有的select语句中的子句的操作都会在from这张临时表上进行操作

(3)from子句的语法格式为：

FROM table_reference [, table_reference]…

其中，table_reference：

tbl_name [[AS] tbl_name_alias ] [{USE | IGNORE | FORCE} INDEX (key_list)] /*查询表*/

| join_table /*连接表*/

说明：table_reference指出了要查询的表或视图。

●  {USE | IGNORE | FORCE} INDEX：USE INDEX告知MySQL选择一个索引来查找表中的行，IGNORE INDEX告知MySQL不要使用某些特定的索引，FORCE INDEX的作用接近USE INDEX(key_list)，只有当无法使用一个给定的索引来查找表中的行时，才使用表扫描。

●  tbl_name为要查询的表名，与列别名一样，可以使用AS选项为表指定别名，tbl_name_alias为表指定的别名。表别名主要用在相关子查询及连接查询中。如果FROM子句指定了表别名，这条SELECT语句中的其他子句都必须使用表别名来代替原始的表名。当同一个表在SELECT语句中多次被提到的时候，就必须要使用表别名来加以区分。

这句话什么意思呢？

也就是说：如果我们在from子句中为一张临时表(或者一张临时的连接表中的子表)指定了别名的话，那么我们在select语句中再提到对应的表名的时候，就必须使用这个别名

为什么呢？

实际上，这是非常的明显的，就像上面说的一样，select语句的所有的操作都是在由from子句生成的临时表上进行操作的，与源表没有任何的关系，所以当from为表起了一个别名的话，select语句中当让要使用这个别名才正确呀！如下面这个例子：

假如我们已经创建了两张表

student1

student2

现在执行下面这条语句

selectstudent1.idfrom student1 as s1 ;

输出错误提示：ERROR 1054 (42S22): Unknown column 'student1.id' in 'field list'

如果我们将上面的student1.id 改为 s1的话，就正确了

2、【引用一个表】

可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：

SELECT * FROM db2.tb;

3、【多表连接】

如果要在不同表中查询数据，则必须在FROM子句中指定多个表。指定多个表时就要使用到连接。当不同列的数据组合到一个表中叫做表的连接。例如，在XSCJ数据库中需要查找选修了离散数学课程的学生的姓名和成绩，就需要将XS、KC和XS_KC三个表进行连接，才能查找到结果。

连接的方式有以下两种：全连接  和  join子句连接

(1)全连接

连接的第一种方式是将各个表用逗号分隔，这样就指定了全连接。FROM子句产生的中间结果是一个新表，新表是每个表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。

比如对上面的两张表进行全连接

select *

from student1 , student2 ;

如果我们将from子句中的两张表颠倒一下，那么结果会怎么样呢？

select *

from student2 , student1 ;

知道怎么个意思了吧！虽然颠倒了，形成的表的格式也不同了，但是两张临时表中提供的信息是一样的，并没有区别，只是临时表的格式改变罢了，想想为什么！

所以我们可以得到这样的结论：

在表的连接子句中，形成的临时表总是 “处在第二个参数上的源表 贴到 处在第一个参数上的源表上” 形成的

这样一来我们就会非常容易的读懂表连接语句到底想表达什么意思

这样连接表潜在地产生数量非常大的行，因为可能得到的行数为每个表中行数之积。在这样的情形下，通常要使用WHERE子句设定条件来将结果集减少为易于管理的大小，这样的连接即为等值连接 ， 如：

select *

fromstudent1 , student2where student1.id=student2.id ;

又例如： 查找XSCJ数据库中所有学生选过的课程名和课程号，使用等值连接如下语句：

select distinctKC.课程名, XS_KC.课程号fromKC, XS_KCwhere KC.课程号=XS_KC.课程号;

(2)Join 连接

所谓的join连接就是在from子句中使用join关键字的连接 。Join 连接分为三种方式：内连接、外连接、交叉连接

由于很多的连接最终的结果都是一样的，所以我们就将重复的连接语句省略了，以后遇到了新的连接语句在补充

①内连接

指定了INNER关键字的连接是内连接。

对于上一个例子中的连接，FROM子句中产生的中间结果是两个表的笛卡儿积。内连接中FROM子句产生的中间结果是应用了ON条件后的笛卡儿积。

先看下面这个例子，再来解释这句话，我们还是实现上一个例子：查找XSCJ数据库中所有学生选过的课程名和课程号

select distinct课程名, XS_KC.课程号from KC inner joinXS_KCon (KC.课程号=XS_KC.课程号);

结果上面的例子中的结果是一样的，我们分析一下和上面例子中的语句有什么分别：

1.在from子句中使用了 inner join 关键字之后，就指定了现在使用的连接方式是内连接

2.使用了 inner join 之后 ，和不使用没有什么分别，形成的临时表还是将 XS_KC 贴到 KC表上

3.但是指定了内连接之后，表连接条件就可以使用 on 子句来指定了

4.可见内连接和使用where连接条件的全连接没有什么分别，只是说使用内连接之后，就可以用 on 子句代替where来指定表连接条件了(当然在内连接中也可以省略on条件，一依然使用where指定条件)，那么内连接是不是多此一举呢？

5.当然不是，因为使用内连接之后，就会使表的连接描述更加的清晰：

使用内连接后，FROM子句中ON条件主要用来连接表，其他并不属于连接表的条件可以使用WHERE子句来指定。

这种模式的优越性不言而喻

6.在举个例子：

如：用FROM子句的JOIN关键字表达下列查询：查找选修了206课程且成绩在80分以上的学生姓名及成绩。

SELECT姓名,成绩FROM XS JOIN XS_KC ON XS.学号 =XS_KC.学号WHERE 课程号 = '206' AND 成绩>=80;

7.内连接还可以用于多个表的连接。

如：用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

SELECTXS.学号, 姓名, 课程名, 成绩FROM XS JOIN XS_KC ON XS.学号 =XS_KC.学号JOIN KC ON XS_KC.课程号 =KC.课程号WHERE 课程名 = '计算机基础' AND 成绩>=80 ;

如果我们不适用内连接而是使用where全连接的话，语句为

selectxs.学号, 姓名,课程名,成绩fromxs , xs_kc , kcwhere xs.学号 =xs_kc.学号and xs_kc.课程号 =kc.课程号and 课程名 = '计算机基础'

and 成绩 >= 80 ;

是不是没有内连接好

8.作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

如：查找XSCJ数据库中课程不同、成绩相同的学生的学号、课程号和成绩。

SELECTs1.学号,s1.课程号,s2.课程号,s1.成绩FROM XS_KC AS s1 JOIN XS_KC ASs2ON s1.成绩=s2.成绩 AND s1.学号=s2.学号 AND s1.课程号!=s2.课程号;

9.如果要连接的表中有列名相同，并且连接的条件就是列名相等，那么ON条件也可以换成USING子句。USING(column_list)子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中column_list为两表中相同的列名。

如： 查找KC表中所有学生选过的课程名。

SELECT课程名FROM KC INNER JOINXS_KC

USING (课程号);

② 左外连接(left outer join)

结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。

如：查找所有学生情况以及他们选修的课程名，如果学生没有选修某课程，那么该学生的该课程显示为null

selectxs.学号 , xs.姓名 , xs.专业名 , 课程号from xs left outer joinxs_kcon xs.学号 = xs_kc.学号 ;

部分结果为

③ 右外连接(right outer join)

结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。

如： 查找被选修了的课程的选修情况和所有开设的课程名。

SELECT XS_KC.*, 课程名FROM XS_KC RIGHT JOIN KC ON XS_KC.课程号= KC.课程号;

说明：本例执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩字段值均为NULL。

部分结果为