MySQL与PHP的基础与应用专题之数据查询语句(2)-木庄网络博客

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);

?>

输出结果: