语法
SELECT 列名
FROM 数据表1 RIGHT JOIN 数据表2 ON 连接条件 [AND 筛选条件]
实例
<?php
$conf = require_once('conf.php');
$sql_connect = mysqli_connect($conf['host'],$conf['username'],$conf['password'],$conf['dbname']);
if(mysqli_connect_error($sql_connect)){
die('数据库连接失败'.mysqli_connect_error($sql_connect));
}
// books 表
$books = array(
array("book_id" => 1, "title" => "The Great Gatsby", "author_id" => 1),
array("book_id" => 2, "title" => "To Kill a Mockingbird", "author_id" => 2),
array("book_id" => 3, "title" => "1984", "author_id" => 3),
array("book_id" => 4, "title" => "Pride and Prejudice", "author_id" => 1),
array("book_id" => 5, "title" => "The Catcher in the Rye", "author_id" => 4)
);
// authors 表
$authors = array(
array("author_id" => 1, "author_name" => "F. Scott Fitzgerald"),
array("author_id" => 2, "author_name" => "Harper Lee"),
array("author_id" => 3, "author_name" => "George Orwell"),
array("author_id" => 4, "author_name" => "J.D. Salinger"),
array("author_id" => 5, "author_name" => "Jane Austen")
);
//清除数据表中的数据,防止数据混淆
$drop_table = "DROP TABLE books,authors";
$drop_query = mysqli_query($sql_connect,$drop_table);
if(!$drop_query){
die('数据表删除失败'.mysqli_error($sql_connect));
}
//创建第一个数据表
$create_books = "CREATE TABLE IF NOT EXISTS books(
book_id INT(6),
title VARCHAR(60),
author_id INT(6)
)";
$create_book_query = mysqli_query($sql_connect,$create_books);
if(!$create_book_query){
die('数据表1创建失败'.mysqli_error($sql_connect));
}
//创建第二个数据表
$create_authors = "CREATE TABLE IF NOT EXISTS authors(
author_id INT(6),
author_name VARCHAR(60)
)";
$carete_authors_query = mysqli_query($sql_connect,$create_authors);
if(!$carete_authors_query){
die('数据表2创建失败'.mysqli_error($sql_connect));
}
//第一个数据表插入数据
foreach($books as $arr){
$insert_book = " INSERT INTO books VALUES ({$arr['book_id']}, '{$arr['title']}', {$arr['author_id']}) ";
$insert_book_query = mysqli_query($sql_connect,$insert_book);
}
if(!$insert_book_query){
die('数据表1插入数据失败'.mysqli_error($sql_connect));
}
//第二个数据表插入数据
foreach($authors as $arr){
$insert_authors = "INSERT INTO authors VALUES ({$arr['author_id']},'{$arr['author_name']}')";
$insert_authors_query = mysqli_query($sql_connect,$insert_authors);
}
if(!$insert_authors_query){
die('数据表2插入数据失败'.mysqli_error($sql_connect));
}
// 使用右连接,从 "authors" 表中选取所有作者的姓名以及他们所著书籍的标题,如果作者没有著书,则显示为 "No books."
$right_join = "SELECT author_name,COALESCE(title,'No books') AS titles
FROM books RIGHT JOIN authors ON books.author_id = authors.author_id
";
$right_join_query = mysqli_query($sql_connect,$right_join);
if(!$right_join_query){
die('左连接失败'.mysqli_error($sql_connect));
}
echo '<table border = "1">';
echo '<tr><td>作者</td><td>书籍</td></tr>';
while($arr = mysqli_fetch_array($right_join_query)){
echo "<tr>";
echo "<td>{$arr['author_name']}</td>";
echo "<td>{$arr['titles']}</td>";
echo "</tr>";
}
echo '</table>';
mysqli_close($sql_connect);运行结果





还没有评论,来说两句吧...