这篇文章主要为大家展示了“MySQL与PHP中的数据查询语句是什么”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL与PHP中的数据查询语句是什么”这篇文章吧。
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);?>输出结果:
数据库链接成功SQL 语句执行成功!Array
(
[gender] => 1
)
Array
(
[gender] => 2
)
以上是“MySQL与PHP中的数据查询语句是什么”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注编程网行业资讯频道！
