MySQL select join on 连表查询和自连接查询

野性酷女 2021-09-21 11:26 439阅读 0赞

# 连表查询 #

> JOIN ON

<table> 
 <thead> 
  <tr> 
   <th>操作</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>inner join</td> 
   <td>只返回匹配的值</td> 
  </tr> 
  <tr> 
   <td>right join</td> 
   <td>会从右表中返回所有的值, 即使左表中没有匹配</td> 
  </tr> 
  <tr> 
   <td>left join</td> 
   <td>会从左表中返回所有的值, 即使右表中没有匹配</td> 
  </tr> 
 </tbody> 
</table>

-- ========== 连表查询 join ============
    -- 查询参加了考试的同学 学号, 姓名, 分数
    SELECT * FROM student
    SELECT * FROM result
    
    /* 查询两个表, 这两个表的交叉点 */
    -- join 连接的表 on 判断条件 -- 连接查询
    -- where -- 等值查询
    -- inner join
    SELECT result.gradeid,`grade`,`name` FROM student INNER JOIN result
    WHERE result.gradeid = student.gradeid
    
    -- right join
    SELECT s.`gradeid`,`grade`,`name` FROM student AS s RIGHT JOIN result AS r
    ON s.gradeid = r.gradeid
    
    -- left join
    SELECT s.`gradeid`,`grade`,`name` FROM student s LEFT JOIN result r
    ON s.gradeid = r.gradeid
    
    -- 查询没有分数的同学
    SELECT s.`gradeid`,`grade`,`name` FROM student s LEFT JOIN result r
    ON s.gradeid = r.gradeid
    WHERE `grade` IS NULL
    
    
    
    /* 查询参加了考试的同学的: 学号, 姓名, 科目, 分数 确定交叉点 */
    SELECT r.`gradeid`,`name`,`subject` AS 科目,`grade`
    FROM `student` s RIGHT JOIN `result` r
    ON s.gradeid = r.gradeid
    INNER JOIN `subject` sub
    ON r.gradeid = sub.gradeid

# 自连接 #

*  自己的表和自己的表连接, 核心：**一张表拆为两张一样的表**

父类

<table> 
 <thead> 
  <tr> 
   <th>categoryid</th> 
   <th>categoryName</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>信息技术</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>软件开发</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>美术设计</td> 
  </tr> 
 </tbody> 
</table>

子类

<table> 
 <thead> 
  <tr> 
   <th>pid</th> 
   <th>categoryid</th> 
   <th>categoryName</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>4</td> 
   <td>数据库</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>6</td> 
   <td>web开发</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>8</td> 
   <td>办公信息</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>7</td> 
   <td>ps技术</td> 
  </tr> 
 </tbody> 
</table>

-- 查询父子信息, 把一张表看做两个一模一样的表
    SELECT a.`categoryName` AS '父栏目', b.`categoryName` AS '子栏目'
    FROM `category` AS a, `category` AS b
    WHERE a.`categoryid` = b.`pid`