How to use table connection in MySQL and PHP

Table connection

Use connection conditions to associate multiple tables. This is table connection (JOIN). Data between multiple tables can be obtained through table connection

Table connection syntax:

SELECT table name 1. column name, table name 2. column name FROM table 1, table 2 WHERE condition

Several ways to connect tables:

Inner join: join or inner join

Self-join: connection within the same table

Outer join:

• Left outer join, left join

• Right outer join, right join

• Full outer join, full join

Data(table1):

Data(table2):

Inner join

Inner join is to delete all rows from the result table that do not match other joined tables. (Intersection)

Format:

SELECT A .c1, B.c2 FROM A JOIN B ON A.c3 = B.c3

Example:

<?php

$conn = mysqli_connect("localhost", "root","admin","study");

if ($conn) {
    echo "数据库链接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "SELECT table1.name, table1.age, table2.class
        FROM table1 JOIN table2
        ON table1.name = table2.name";

# 执行
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL 语句执行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 调试输出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 关闭连接
mysqli_close($conn);

?>

Output result:

Database link Success
SQL statement executed successfully!
Array
(
[name] => Yang Fugui
[age] => 18
[class] => Mathematics Class
)
Array
(
[name] => Yang Meili
[age] => 19
[class] => Chinese class
)
Array
(
[name] => Yang Jianguo
[age] => 20
[class] => English class
)

left The connection

contains all rows in the table (regardless of whether there are matching rows in the right table), and all matching rows in the right table.

Format:

SELECT A.c1, B.c2 FROM A LEFT JOIN B ON A.c3 = B.c3

Example:

<?php

$conn = mysqli_connect("localhost", "root","admin","study");

if ($conn) {
    echo "数据库链接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "SELECT table1.name, table1.age, table2.class
        FROM table1 LEFT JOIN table2
        ON table1.name = table2.name";

# 执行
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL 语句执行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 调试输出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 关闭连接
mysqli_close($conn);

?>

Output result:

Database link successful
SQL statement executed successfully!
Array
(
[name] => Yang Fugui
[age] => 18
[class] = > Mathematics class
)
Array
(
[name] => Yang Meili
[age] => 19
[class] => Chinese class
)
Array
(
[name] => Yang Jianguo
[age] => 20
[class] => English class
)
Array
(
[name] => Yang Tiezhu
[age] => 21
[class] =>
)

Right connection

Includes all rows in the right table (regardless of whether there are matching rows in the left table), and all matching rows in the left table.

Format:

SELECT A .c1, B.c2 FROM A RIGHT JOIN B ON A.c3 = B.c3

Example:

<?php

$conn = mysqli_connect("localhost", "root","admin","study");

if ($conn) {
    echo "数据库链接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "SELECT table1.name, table1.age, table2.class
        FROM table1 RIGHT JOIN table2
        ON table1.name = table2.name";

# 执行
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL 语句执行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 调试输出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 关闭连接
mysqli_close($conn);

?>

Output result:

Database Link successful
SQL statement executed successfully!
Array
(
[name] => Yang Fugui
[age] => 18
[class] => Mathematics class
)
Array
(
[name] => Yang Meili
[age] => 19
[class] => Chinese class
)
Array
(
[name] => Yang Jianguo
[age] => 20
[class] => English class
)
Array
(
[name] =>
[age] =>
[class] => History Lesson
)

Full Connection

Contains All rows in the left and right tables (regardless of whether there are matching rows in the other table).

Format:

SELECT A.c1, B.c2 FROM A LEFT JOIN B ON A.c3 = B.c3
UNION
SELECT A.c1, B.c2 FROM A RIGHT JOIN B ON A.c3 = B.c3

Example:

<?php

$conn = mysqli_connect("localhost", "root","admin","study");

if ($conn) {
    echo "数据库链接成功\n";
} else {
    echo mysqli_connect_error();
}

# SQL语句
$SQL = "SELECT table1.name, table1.age, table2.class
        FROM table1 LEFT JOIN table2 ON table1.name = table2.name
        UNION 
        SELECT table1.name, table1.age, table2.class
        FROM table1 RIGHT JOIN table2 ON table1.name = table2.name";

# 执行
$result = mysqli_query($conn, $SQL);

# 查看是否执行成功
if ($result) {
    echo "SQL 语句执行成功!\n";
}else {
    echo mysqli_error($conn);
}

# 调试输出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 关闭连接
mysqli_close($conn);

?>

Output result:

Database link successful
SQL statement executed successfully!
Array
(
[name] => Yang Fugui
[age] => 18
[class] => Mathematics class
)
Array
(
[name] => Yang Meili
[age] = > 19
[class] => Chinese class
)
Array
(
[name] => Yang Jianguo
[age] => 20
[ class] => English class
)
Array
(
[name] => Yang Tiezhu
[age] => 21
[class] =>
)
Array
(
[name] =>
[age] =>
[class] => History Lesson
)

The above is the detailed content of How to use table connection in MySQL and PHP. For more information, please follow other related articles on the PHP Chinese website!