MySQL與PHP的基礎與應用專題之資料查詢語句

2022-02-21 19:00:12

概述

從今天開始, 小白我將帶領大家一起來補充一下資料庫的知識.

ORDER BY

在 SQL 中, 我們可以使用 ORDER BY 對查詢結果進行一列或多列排序.

SQL 語句:

SELECT 列名1, 列名2 FROM 表名1, 表名2
ORDER BY 列名, 列名[ASC|DESC]

注意:

ASC 表示按升序排列, DESC 表示按降序排列
預設情況下對列按升序排序

表記錄:

例 1

工資從低到高排序:

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句, 條件查詢
$SQL = "SELECT * FROM user 
        ORDER BY salary";

# 執行SQL語句
$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);

?>

輸出結果:

伺服器連線成功!
SQL語句執行成功!
Array
(
[id] => 3
[username] => 王富貴
[password] => 123456
[sex] => 1
[salary] => 1000.00
)
Array
(
[id] => 4
[username] => 李大爺
[password] => 123123
[sex] => 1
[salary] => 1500.00
)
Array
(
[id] => 2
[username] => 張美麗
[password] => 123456
[sex] => 2
[salary] => 5000.00
)
Array
(
[id] => 5
[username] => 王二狗
[password] => 123123
[sex] => 1
[salary] => 6000.00
)
Array
(
[id] => 1
[username] => 我是小白呀
[password] => 123456
[sex] => 2
[salary] => 30000.00
)

例 2

工資從高到低排序:

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句, 條件查詢
$SQL = "SELECT * FROM user 
        ORDER BY salary DESC";

# 執行SQL語句
$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);

?>

LIMIT

我們可以在 SELECT 語句中使用 LIMIT 來約束要返回的記錄數, 通常使用 LIMIT 實現分頁.

SQL 語句:

SELECT 列名1, 列名2 FROM 表1, 表2 LIMIT [偏移量] 行數

注意:

第一行的偏移量是 0, 而不是 1
行數為返回最大的行數

例 1

取前兩個資料:

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT * FROM user 
        LIMIT 2";

# 執行SQL語句
$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);

?>

輸出結果:

伺服器連線成功!
SQL語句執行成功!
Array
(
[id] => 1
[username] => 我是小白呀
[password] => 123456
[sex] => 2
[salary] => 30000.00
)
Array
(
[id] => 2
[username] => 張美麗
[password] => 123456
[sex] => 2
[salary] => 5000.00
)

例 2

取第 2, 3 條資料:

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT * FROM user 
        LIMIT 1, 2";

# 執行SQL語句
$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);

?>

輸出結果:

伺服器連線成功!
SQL語句執行成功!
Array
(
[id] => 2
[username] => 張美麗
[password] => 123456
[sex] => 2
[salary] => 5000.00
)
Array
(
[id] => 3
[username] => 王富貴
[password] => 123456
[sex] => 1
[salary] => 1000.00
)

GROUP BY

從字面上理解, GROUP BY 表示根據某種規則對資料進行分組. 它必須配合聚合函數進行使用, 對資料進行分組後可以進行 COUNT, SUM, AVG, MAX 和 MIN 等運算.

SQL 語句:

SELECT 列名, 聚合函數(列名)
FROM 表名
GROUP BY 列名

注意:

GROUP BY 可以對一列或多列進行分組

表記錄:

例 1

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT gender, count(*) FROM student 
        GROUP BY gender";

# 執行SQL語句
$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);

?>

輸出結果:

伺服器連線成功!
SQL語句執行成功!
Array
(
[gender] => 1
[count(*)] => 3
)
Array
(
[gender] => 2
[count(*)] => 6
)

例 2

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT grade, count(*) FROM student 
        GROUP BY grade";

# 執行SQL語句
$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);

?>

輸出結果:

伺服器連線成功!
SQL語句執行成功!
Array
(
[grade] => 高三
[count(*)] => 2
)
Array
(
[grade] => 高一
[count(*)] => 4
)
Array
(
[grade] => 高二
[count(*)] => 3
)

HAVING

HAVING 可以解決 WHERE 關鍵詞無法與聚合函數一起使用的問題. HAVING 可以對分組後的各組資料進行篩選.

SQL 語句:

SELECT 列名, 聚合函數(列名)
FROM 表名
GROUP BY 列名
HAVING 聚合函數(列名) 條件

例 1

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT grade, count(*) FROM student 
        GROUP BY grade
        HAVING count(*) > 2";

# 執行SQL語句
$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);

?>

輸出結果:

伺服器連線成功!
SQL語句執行成功!
Array
(
[grade] => 高一
[count(*)] => 4
)
Array
(
[grade] => 高二
[count(*)] => 3
)

例 2

<?php

# 建立連線
$conn = mysqli_connect("localhost", "root", "admin", "study");

# 檢視是否連線成功
if ($conn) {
    echo "伺服器連線成功!n";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT gender, count(*) FROM student 
        GROUP BY gender
        HAVING count(*) > 3";

# 執行SQL語句
$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);

?>

輸出結果:

伺服器連線成功!
SQL語句執行成功!
Array
(
[gender] => 2
[count(*)] => 6
)

GROUP_CONCAT

當我們使用GROUP BY可以獲取分組, 但是無法看到具體資訊. 這時我們就可以使用GROUP_CONCAT來獲取具體資訊. GROUP_CONCAT配合GROUP BY一起使用, 可以將一列的值按照指定分隔符進行拼接 (預設為「,」).

SQL 語句:

GROUP_CONCAT(列名[排序列 ASC/DESC] [分隔符])

例 1

<?php

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

if ($conn) {
    echo "資料庫連結成功";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT grade, count(*), GROUP_CONCAT(student_name)
        FROM student
        GROUP BY grade";

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

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!";
}else {
    echo mysqli_error($conn);
}

# 偵錯輸出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 關閉連線
mysqli_close($conn);

?>

輸出結果:

資料庫連結成功SQL 語句執行成功!Array
(
[grade] => 高一
[count(*)] => 4
[GROUP_CONCAT(student_name)] => 王富貴,楊美麗,楊美麗,楊美麗
)
Array
(
[grade] => 高三
[count(*)] => 2
[GROUP_CONCAT(student_name)] => 楊美麗,楊美麗
)
Array
(
[grade] => 高二
[count(*)] => 3
[GROUP_CONCAT(student_name)] => 楊美麗,楊美麗,趙有才
)

例 2

<?php

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

if ($conn) {
    echo "資料庫連結成功";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT gender, count(*), GROUP_CONCAT(student_name)
        FROM student
        GROUP BY gender";

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

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!";
}else {
    echo mysqli_error($conn);
}

# 偵錯輸出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 關閉連線
mysqli_close($conn);

?>

輸出結果:

資料庫連結成功SQL 語句執行成功!Array
(
[gender] => 1
[count(*)] => 3
[GROUP_CONCAT(student_name)] => 楊美麗,楊美麗,楊美麗
)
Array
(
[gender] => 2
[count(*)] => 6
[GROUP_CONCAT(student_name)] => 王富貴,楊美麗,楊美麗,楊美麗,楊美麗,趙有才
)

DISTINCT

DISTINCT用於在查詢中返回父唯一不同值, 支援單列或多列. 在實際的應用中, 表中的某一列含有重複值是很常見的. 如果在查詢資料時, 希望得到某列的所有不同值, 可以使用 DISTINCT.

SQL 語句:

SELECT DISTINCT 列名1, 列名2 FROM 表名

例 1

<?php

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

if ($conn) {
    echo "資料庫連結成功";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT DISTINCT grade FROM student";

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

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!";
}else {
    echo mysqli_error($conn);
}

# 偵錯輸出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 關閉連線
mysqli_close($conn);

?>

輸出結果:

資料庫連結成功SQL 語句執行成功!Array
(
[grade] => 高三
)
Array
(
[grade] => 高一
)
Array
(
[grade] => 高二
)

例 2

<?php

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

if ($conn) {
    echo "資料庫連結成功";
} else {
    echo mysqli_connect_error();
}

# SQL語句
$SQL = "SELECT DISTINCT gender FROM student";

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

# 檢視是否執行成功
if ($result) {
    echo "SQL 語句執行成功!";
}else {
    echo mysqli_error($conn);
}

# 偵錯輸出
while ($line = mysqli_fetch_assoc($result)) {
    print_r($line);
}

# 關閉連線
mysqli_close($conn);

?>

輸出結果: