多表连接查询

  • 内连接查询
    连接结果默认是迪卡尔积, 根据连接条件连接满足条件的记录 
  1. select t1.feild,...t2.field,... from tableName1 as t1 inner join tableName2 as t2 on condition;
  • 左外连接查询
    连接结果以左表为准, 如果在右表中没有没有满足条件的记录,则左表记录后连接 NULL 值 
  1. select t1.feild,...t2.field,... from tableName1 as t1 left join tableName2 as t2 on condition;
  • 右外连接查询
    连接结果以右表为准, 如果在左表中没有没有满足条件的记录,则右表记录后连接 NULL 值
    **可以通过 
  1. select t1.feild,...t2.field,... from tableName1 as t1 right join tableName2 as t2 on condition;

两表以上多表连接查询

  1. select t1.feild,...t2.field,t3.field,... from tableName1 as t1 xxx join tableName2 as t2 on condition xxx join tableName3 as t3 on condition....,....;

select t1.feild,…t2.field,t3.field,… from tableName1 as t1 xxx join tableName2 as t2 on condition xxx join tableName3 as t3 on condition….,….;

  • 多表连接查询三-第五题(专题3-5) 

    1. SELECT class.caption, student.`name`,course.`name` AS subject,score.number 
    2. FROM class 
    3. LEFT JOIN student ON class.id = student.classId  -- 找到学生信息
    4. LEFT JOIN classandcourse ON classandcourse.classId = class.id  -- 找到课程编号
    5. LEFT JOIN course ON classandcourse.courseId = course.id -- 找到课程信息
    6. LEFT JOIN score ON score.studentId = student.id AND score.courseId = course.id 
    7. WHERE class.caption = '一年一班'

  • 使用表别名 

    1. select t1.caption,t2.name, t4.name,t5.number from class t1 
    2. left join student t2 on t1.id = t2.classId
    3. left join classandcourse t3 on t2.classId = t3.classID
    4. left join course  t4 on t3.courseId = t4.id
    5. left join score t5 on t2.id = t5.studentId and t4.id = t5.courseId
    6. where t1.caption='一年一班';

  • 扩展操作 

    1. select t1.caption,t2.name, t4.name,t5.number from class t1 
    2. left join student t2 on t1.id = t2.classId
    3. left join classandcourse t3 on t2.classId = t3.classID
    4. left join course  t4 on t3.courseId = t4.id
    5. left join score t5 on t2.id = t5.studentId and t4.id = t5.courseId
    6. where t1.caption='一年一班'
    7. order by t5.number desc 
    8. limit 5

案例练习 (班级信息管理)

  1. <?php
  2.     // 连接数据库
  3.     $url = "mysql:host=mysql;dbname=database_lesson_30511_14_11214";
  4.     $user = "lesson_30511_14_11214";
  5.     $passwd = "b58c4ebfdcced037d1178d91a452015d";
  6.     $pdo = new PDO($url,$user,$passwd);
  8.     // 相关变量
  9.     $page = 1; // 当前页码
  10.     $pageSize = 3;  // 当前每页显示记录数
  11.     $start = 0; // 获取分页数据时的起始位置 
  12.     $allNum = 0; // 表中所有的记录数
  13.     $allPage = 0; // 表中所有的页数
  15.     // 获取所有的记录数
  16.     $sql = "select class.name, count(*) from class inner join student on class.id=student.classId group by class.name;";
  17.     $st = $pdo->query($sql);
  18.     $res = $st->fetchAll(PDO::FETCH_ASSOC);
  20.     // 保存所有的记录数
  21.     $allNum = count($res);
  22.     // 计算所有的页数
  23.     $allPage = ceil($allNum/$pageSize);
  25.     if(isset($_GET['p'])){
  26.         $page = $_GET['p'];
  27.     }
  29.     // 通过页码云计算起始查询位置,进进行获取数据
  30.     $start = ($page-1)*$pageSize;
  31.     $sql = "select class.name, count(*) as num from class inner join student on class.id=student.classId group by class.name limit {$start}, {$pageSize}";
  32.     $st = $pdo->query($sql);
  33.     $res = $st->fetchAll(PDO::FETCH_ASSOC);
  34.     // var_dump($res);
  36. ?>
  38. <!DOCTYPE html>
  39. <html lang="en">
  40. <head>
  41.     <meta charset="UTF-8">
  42.     <meta http-equiv="X-UA-Compatible" content="IE=edge">
  43.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  44.     <title>Document</title>
  45.     <style>
  46.         #content{
  47.             width:800px;
  48.             text-align: center;
  49.             margin:0 auto;
  50.             background: #ccc;
  51.             font-size: 20px;
  52.         }
  53.         #table{
  54.             width:800px;
  55.         }
  56.         table{
  57.             border-collapse:collapse;
  58.             margin: 0 auto;
  59.         }
  60.         table,td,th{
  61.             border: 1px solid red;
  62.         }
  63.         tr:nth-child(1){
  64.             background: cyan;
  65.         }
  66.         td{
  67.             text-align: center;
  68.             width:400px;
  69.         }
  70.         #ctl{
  71.             width:800px;
  72.             margin:10px 0;
  73.         }
  75.         #btn{
  76.             width:700px;
  77.             list-style: none;
  78.             margin: 0 auto;
  79.             display: flex;
  80.             padding: 0;
  81.             justify-content: space-between;
  83.         }
  84.         li{
  85.             width:100px;
  86.             height: 25px;
  87.             border-radius:5px;
  88.             border:1px solid blue;
  89.             margin:0 ;
  91.         }
  92.         .num{
  93.             width:25px; 
  94.             height: 25px;
  95.             border:1px solid blue;
  96.             border-radius:5px;
  97.             margin: 0 -25px;
  98.         }
  99.         a{
  100.             text-decoration: none;
  101.         }
  102.         #info{
  103.             text-align: center;
  104.             font-size: 18px;
  105.         }
  106.         span{
  107.             margin-left:20px;
  108.         }
  111.     </style>
  112. </head>
  113. <body>
  114.     <div id="content">
  115.         <div id='title'>
  116.             <h1>班级信息管理</h1>
  117.         </div>
  118.         <div id='table'>
  119.             <table  width="600" >
  120.                 <tr background="#ccc">
  121.                     <th>班级名称</th>
  122.                     <th>人数</th>
  124.                 </tr>
  125.                 <!--对查询数据进行显示-->
  126.                 <?php foreach($res as $val){ ?>
  127.                     <tr>
  128.                         <td> <?php echo $val["name"]; ?></td>
  129.                         <td> <?php echo $val["num"]."人"; ?></td>
  130.                     </tr>
  131.                     <?php } ?>
  132.             </table>
  133.         </div>
  134.         <!--控制按钮-->
  135.         <div id='ctl'>
  136.             <ul id='btn'>
  137.                 <li>
  138.                     <!--如果是到首页,固定页码为1-->
  139.                     <a href="<?php echo "index.php?p=1"; ?>"> 
  140.                     首页
  141.                     </a>
  142.                 </li>
  143.                 <li>
  144.                     <!--如果当前页面不是第一页,则可以进行页面减一,跳到上一次,否则一直为第一页-->
  145.                     <a href="<?php 
  146.                     if($page != 1){
  147.                         echo "index.php?p=" . ($page - 1);
  148.                     }else{
  149.                         echo "index.php?p=1";
  150.                     }
  151.                     ?>"> 
  152.                     上一页
  153.                     </a>
  154.                 </li>
  156.                 <?php 
  157.                     for($i=1;$i<=$allPage;$i++){ ?>
  158.                          <li class="num" <?php 
  159.                                 if($page == $i){ ?>
  160.                                     style = "background: red;";
  161.                              <?php } ?> 
  162.                              >
  163.                             <a href="<?php echo "index.php?p=$i"; ?>">
  164.                                 <?php echo $i; ?>
  165.                             </a>
  166.                         </li>
  167.                 <?php } ?>
  168.                 </li>
  169.                 <li>
  170.                     <!--如果当前页面不是第一页,则可以进行页面加一,跳到下一次,否则一直为末页-->
  171.                    <a href="<?php 
  172.                     if($page != $allPage){
  173.                         echo "index.php?p=" . ($page + 1);
  174.                     }else{
  175.                         echo "index.php?p=" . $allPage;
  176.                     }
  177.                     ?>"> 
  178.                     下一页
  179.                     </a>
  180.                 </li>
  181.                 <li>
  182.                      <!--固定页码为最后一页-->
  183.                     <a href="<?php echo "index.php?p=" . $allPage;?>"> 
  184.                     末页
  185.                     </a>
  186.                 </li>
  188.             </ul>
  189.         </div>
  190.         <!--显示记录信息-->
  191.         <div id="info">
  192.             <?php echo "<span>当前页: {$page}</span> <span>总页数: {$allPage}</span> <span>总数据量:{$allNum}</span>"; ?>
  193.         </div>
  194.     </div>
  196. </body>
  197. </html>