MYSQL----------使用DQL命令查询数据(三)

末蓝、 2024-04-29 11:10 7阅读 0赞

## 学习目标： ##

`掌握MySQL的多表查询`

`SQL语句的综合应用`

--------------------

## 学习内容： ##

## 1.多表连接查询 ##

多表连接查询是通过各个表之间共同列的关联性（例如：外键）来查询的。

多表连接查询的分类

内连接(INNER JOIN) ，可简写为 JOIN；

左外连接(LEFT OUTER JOIN)，可简写为LEFT JOIN；

右外连接(RIGHT OUTER JOIN)，可简写为RIGHT JOIN；

自连接查询；

交叉连接(CROSS JOIN)也称为 笛卡儿乘积连接，基本上用不到。

## 2.内连接 ##

内连接：内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行。

#第一种写法
    
    SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 表1.通用字段名=表2.通用字段名
    
    #第二种写法
    SELECT 字段列表 FROM 表1,表2 WHERE    表1.通用字段名=表2.通用字段名

如果两个表中有相同的字段名，必须在列名前加表名。否则将出现 错误代码：1052 Column 'studentno' in field list is ambiguous

## 3.左外连接 ##

语法

#左外连接
    SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON  表1.通用字段名=表2.通用字段名

左外连接：左表(表1)的记录将会全部表示出来，而右表(表2)只会显示符合匹配条件的记录。

1.匹配，返回到结果集

2.无匹配，NULL值返回到结果集

## 4.右外连接 ##

语法

#右外连接
    SELECT   字段列表   FROM   表1  RIGHT JOIN  表2
    ON    表1.通用字段名=表2.通用字段名

右外连接：右表(表2)的记录将会全部表示出来，而左表(表1)只会显示符合匹配条件的记录。

1.匹配，返回到结果集

2.无匹配，NULL值返回到结果集

## 5.不同的SQL JOIN对比 ##

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>操作符名称</td> 
   <td>描述</td> 
  </tr> 
  <tr> 
   <td>INNER &nbsp; JOIN</td> 
   <td>如果表中有至少一个匹配，则返回</td> 
  </tr> 
  <tr> 
   <td>LEFT JOIN</td> 
   <td>不论右表是否有匹配，都会返回左表的所</td> 
  </tr> 
  <tr> 
   <td>RIGHT JOIN</td> 
   <td>不论左表是否有匹配，都会返回右表的所有行</td> 
  </tr> 
 </tbody> 
</table>

## 代码示例 ##

**多表关联查询**

-- 多表关联查询
    -- S R 是取的别名方便使用可以使用（原名 as 别名）取别名时这个s可以省略不写 
    -- 查询参加考试的学生的姓名，考号，成绩，考试日期，考试科目 
    SELECT
    	S.STUDENTNO,
    	S.STUDENTNAME,
    	R.SUBJECTNO,
    	R.EXAMDATE,
    	R.STUDENTRESULT 
    FROM
    	STUDENT S,
    	RESULT R 
    WHERE
    	S.STUDENTNO = R.STUDENTNO 
    	AND R.STUDENTRESULT > 85
    -- 第二种方法 INNER JOIN(内连接)的写法，INNER 可以省略不写 ON是两个表连接的条件
    -- 因为这个表此时是ON所以添加查询条件时要用WHERE关键字
    SELECT
    	S.STUDENTNO,
    	S.STUDENTNAME,
    	R.SUBJECTNO,
    	R.EXAMDATE,
    	R.STUDENTRESULT 
    FROM
    	STUDENT S
    	INNER JOIN RESULT R ON S.STUDENTNO = R.STUDENTNO 
    WHERE
    	R.STUDENTRESULT > 80;
    -- (三表关联查询)查询参加考试的学生的姓名，考号，成绩，考试日期，考试科目名称
    SELECT
    	S.STUDENTNO,
    	S.STUDENTNAME,
    	SU.SUBJECTNAME,
    	R.EXAMDATE,
    	R.STUDENTRESULT 
    FROM
    	STUDENT S,
    	SUBJECT SU,
    	RESULT R 
    WHERE
    	S.STUDENTNO = R.STUDENTNO 
    	AND R.SUBJECTNO = SU.SUBJECTNO 
    	AND R.STUDENTRESULT > 80
    	
    -- 第二种方法
    
    SELECT
    	S.STUDENTNO,
    	S.STUDENTNAME,
    	SU.SUBJECTNAME,
    	R.EXAMDATE,
    	R.STUDENTRESULT 
    FROM
    	STUDENT S
    	INNER JOIN RESULT R ON S.STUDENTNO = R.STUDENTNO
    	INNER JOIN SUBJECT SU ON SU.SUBJECTNO = R.SUBJECTNO 
    WHERE
    	R.STUDENTRESULT > 85

**左外连接**

-- 查询所有学生的成绩(外连接) 外连接要用left join 左外连接关键字
    SELECT
    	S.STUDENTNO,
    	S.STUDENTNAME,
    	R.SUBJECTNO,
    	R.EXAMDATE,
    	R.STUDENTRESULT 
    FROM
    	STUDENT S
    	LEFT JOIN RESULT R ON S.STUDENTNO = R.STUDENTNO

**自连接**

-- 自连接
    /*CREATE TABLE IF NOT EXISTS TB1_CATOGORY(
    CID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    CNAME VARCHAR(50) NOT NULL,
    PID INT(10)
    )*/
    -- 第一种方法
    SELECT 
    T1.CNAME,T2.CNAME
    FROM TB1_CATOGORY T1,TB1_CATOGORY T2
    WHERE T1.CID=T2.PID
    -- 第二种方法
    SELECT 
    T1.CNAME,T2.CNAME 
    FROM TB1_CATOGORY T1 JOIN TB1_CATOGORY T2 
    ON T1.CID=T2.PID
    
    
    SELECT 
    T1.CNAME,T2.CNAME
    FROM TB1_CATOGORY T1 LEFT JOIN TB1_CATOGORY T2
    ON T1.CID=T2.PID
    WHERE T1.PID IS NULL

综合查询

-- 综合查询
    SELECT 
    S.STUDENTNO,
    S.STUDENTNAME,
    SU.SUBJECTNAME,
    R.EXAMDATE,
    R.STUDENTRESULT
    FROM STUDENT S 
    INNER JOIN RESULT R 
    ON S.STUDENTNO = R.STUDENTNO
    JOIN SUBJECT SU 
    ON SU.SUBJECTNO = R.SUBJECTNO
    WHERE S.STUDENTNAME LIKE '张%'