目录
一、MySQL 删除数据表
MySQL中删除数据表是非常容易操作的,但是你在进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失。
语法
以下为删除 MySQL 数据表的通用语法:
DROP TABLE table_name ; -- 直接删除表,不检查是否存在
或
DROP TABLE [IF EXISTS] table_name;
参数说明:
table_name
是要删除的表的名称。IF EXISTS
是一个可选的子句,表示如果表存在才执行删除操作,避免因为表不存在而引发错误。
-- 删除表,如果存在的话
DROP TABLE IF EXISTS mytable;
-- 直接删除表,不检查是否存在
DROP TABLE mytable;
请替换 mytable 为你要删除的表的名称。
在命令行中,你也可以使用 mysqladmin 工具来删除表。
以下是使用 mysqladmin 删除表的命令:
mysqladmin -u your_username -p drop your_table
- your_username 是 MySQL 用户名。
- your_table 是要删除的表的名称。
执行此命令后,系统会提示输入密码,输入密码后按 Enter 键即可删除表。
注意: 在执行删除表操作时,请确保你确实想要删除表及其所有数据,因为该操作是不可逆的。为了避免误操作,通常建议在执行删除之前备份表。
实例
以下实例删除了数据表 runoob_tbl:
实例
root@host# mysql -u root -p
Enter password:*******
mysql> USE RUNOOB;
DATABASE changed
mysql> DROP TABLE runoob_tbl;
Query OK, 0 ROWS affected (0.8 sec)
mysql>
二、使用 PHP 脚本删除数据表
PHP 使用 mysqli_query 函数来删除 MySQL 数据表。
该函数有两个参数,在执行成功时返回 TRUE,否则返回 FALSE。
语法
mysqli_query(connection,query,resultmode);
参数 | 描述 |
---|---|
connection | 必需。规定要使用的 MySQL 连接。 |
query | 必需,规定查询字符串。 |
resultmode | 可选。一个常量。可以是下列值中的任意一个:
|
实例
以下实例使用了 PHP 脚本删除数据表 runoob_tbl:
删除数据库
<?php
$dbhost = 'localhost'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = "DROP TABLE runoob_tbl";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('数据表删除失败: ' . mysqli_error($conn));
}
echo "数据表删除成功\n";
mysqli_close($conn);
?>
执行成功后,我们使用以下命令,就看不到 runoob_tbl 表了:
mysql> show tables; Empty set (0.01 sec)
三、MySQL 插入数据
MySQL 表中使用 INSERT INTO 语句来插入数据。
你可以通过 mysql> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据。
语法
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
参数说明:
table_name
是你要插入数据的表的名称。column1
,column2
,column3
, ... 是表中的列名。value1
,value2
,value3
, ... 是要插入的具体数值。
如果数据是字符型,必须使用单引号 ' 或者双引号 ",如: 'value1', "value1"。
一个简单的实例,插入了一行数据到名为 users 的表中:
INSERT INTO users (username, email, birthdate, is_active)
VALUES ('test', 'test@runoob.com', '1990-01-01', true);
username
: 用户名,字符串类型。email
: 邮箱地址,字符串类型。birthdate
: 用户生日, 日期类型。is_active
: 是否已激活,布尔类型。
如果你要插入所有列的数据,可以省略列名:
INSERT INTO users
VALUES (NULL,'test', 'test@runoob.com', '1990-01-01', true);
这里,NULL 是用于自增长列的占位符,表示系统将为 id 列生成一个唯一的值。
如果你要插入多行数据,可以在 VALUES 子句中指定多组数值:
INSERT INTO users (username, email, birthdate, is_active)
VALUES
('test1', 'test1@runoob.com', '1985-07-10', true),
('test2', 'test2@runoob.com', '1988-11-25', false),
('test3', 'test3@runoob.com', '1993-05-03', true);
以上代码将在 users 表中插入三行数据。
通过命令提示窗口插入数据
以下我们将使用 INSERT INTO 语句向 MySQL 数据表 runoob_tbl 插入数据
实例
以下实例中我们将向 runoob_tbl 表插入三条数据:
实例
root@host# mysql -u root -p password;
Enter password:*******
mysql> USE RUNOOB;
DATABASE changed
mysql> INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 ROWS affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 ROWS affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 ROWS affected (0.00 sec)
mysql>
注意: 使用箭头标记 -> 不是 SQL 语句的一部分,它仅仅表示一个新行,如果一条 SQL 语句太长,我们可以通过回车键来创建一个新行来编写 SQL 语句,SQL 语句的命令结束符为分号 ;。
在以上实例中,我们并没有提供 runoob_id 的数据,因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以,该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数,该函数返回日期和时间。
四、MySQL 查询数据
MySQL 数据库使用 SELECT 语句来查询数据。
你可以通过 mysql> 命令提示窗口中在数据库中查询数据,或者通过 PHP 脚本来查询数据。
语法
以下为在 MySQL 数据库中查询数据通用的 SELECT 语法:
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];
参数说明:
column1
,column2
, ... 是你想要选择的列的名称,如果使用*
表示选择所有列。table_name
是你要从中查询数据的表的名称。WHERE condition
是一个可选的子句,用于指定过滤条件,只返回符合条件的行。ORDER BY column_name [ASC | DESC]
是一个可选的子句,用于指定结果集的排序顺序,默认是升序(ASC)。LIMIT number
是一个可选的子句,用于限制返回的行数。
MySQL SELECT 语句简单的应用实例:
实例
-- 选择所有列的所有行
SELECT * FROM users;
-- 选择特定列的所有行
SELECT username, email FROM users;
-- 添加 WHERE 子句,选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;
-- 添加 ORDER BY 子句,按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;
-- 添加 ORDER BY 子句,按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;
-- 添加 LIMIT 子句,限制返回的行数
SELECT * FROM users LIMIT 10;
SELECT 语句可以是灵活的,我们可以根据实际需求组合和使用这些子句,比如同时使用 WHERE 和 ORDER BY 子句,或者使用 LIMIT 控制返回的行数。
在 WHERE
子句中,你可以使用各种条件运算符(如 =
, <
, >
, <=
, >=
, !=
),逻辑运算符(如 AND
, OR
, NOT
),以及通配符(如 %
)等。
以下是一些进阶的 SELECT 语句实例:
实例
-- 使用 AND 运算符和通配符
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;
-- 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';
-- 使用 IN 子句
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');
五、MySQL WHERE 子句
我们知道从 MySQL 表中使用 SELECT 语句来读取数据。
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。
WHERE 子句用于在 MySQL 中过滤查询结果,只返回满足特定条件的行。
语法
以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:
SELECT column1, column2, ... FROM table_name WHERE condition; 参数说明:
column1
,column2
, ... 是你要选择的列的名称,如果使用*
表示选择所有列。table_name
是你要从中查询数据的表的名称。WHERE condition
是用于指定过滤条件的子句。
更多说明:
- 查询语句中你可以使用一个或者多个表,表之间使用逗号, 分割,并使用WHERE语句来设定查询条件。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
- WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。
以下为操作符列表,可用于 WHERE 子句中。
下表中实例假定 A 为 10, B 为 20
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
简单实例
1. 等于条件:
SELECT * FROM users WHERE username = 'test';
2. 不等于条件:
SELECT * FROM users WHERE username != 'runoob';
3. 大于条件:
SELECT * FROM products WHERE price > 50.00;
4. 小于条件:
SELECT * FROM orders WHERE order_date < '2023-01-01';
5. 大于等于条件:
SELECT * FROM employees WHERE salary >= 50000;
6. 小于等于条件:
SELECT * FROM students WHERE age <= 21;
7. 组合条件(AND、OR):
SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;
SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;
8. 模糊匹配条件(LIKE):
SELECT * FROM customers WHERE first_name LIKE 'J%';
9. IN 条件:
SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');
10. NOT 条件:
SELECT * FROM products WHERE NOT category = 'Clothing';
11. BETWEEN 条件:
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
12. IS NULL 条件
SELECT * FROM employees WHERE department IS NULL;
13. IS NOT NULL 条件:
SELECT * FROM customers WHERE email IS NOT NULL;
如果我们想在 MySQL 数据表中读取指定的数据,WHERE 子句是非常有用的。
使用主键来作为 WHERE 子句的条件查询是非常快速的。
如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。
六、MySQL UPDATE 更新
如果我们需要修改或更新 MySQL 中的数据,我们可以使用 UPDATE 命令来操作。
语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
参数说明:
table_name
是你要更新数据的表的名称。column1
,column2
, ... 是你要更新的列的名称。value1
,value2
, ... 是新的值,用于替换旧的值。WHERE condition
是一个可选的子句,用于指定更新的行。如果省略WHERE
子句,将更新表中的所有行。
更多说明:
- 你可以同时更新一个或多个字段。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。
实例
以下实例演示了如何使用 UPDATE 语句。
1. 更新单个列的值:
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;
2. 更新多个列的值:
UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;
3. 使用表达式更新值:
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';
以上 SQL 语句将每个属于 'Electronics' 类别的产品的价格都增加了 10%。
4. 更新符合条件的所有行:
UPDATE students
SET status = 'Graduated';
以上 SQL 语句将所有学生的状态更新为 'Graduated'。
5. 更新使用子查询的值:
UPDATE customers
SET total_purchases = (
SELECT SUM(amount)
FROM orders
WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';
以上 SQL 语句通过子查询计算每个 'Premium' 类型客户的总购买金额,并将该值更新到 total_purchases 列中。
注意: 在使用 UPDATE 语句时,请确保你提供了足够的条件来确保只有你想要更新的行被修改。如果不提供 WHERE 子句,将更新表中的所有行,可能导致不可预测的结果。
通过命令提示符更新数据
以下我们将在 UPDATE 命令使用 WHERE 子句来更新 runoob_tbl 表中指定的数据。
以下实例将更新数据表中 runoob_id 为 3 的 runoob_title 字段值:
SQL UPDATE 语句:
mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
Query OK, 1 rows affected (0.01 sec)
mysql> SELECT * from runoob_tbl WHERE runoob_id=3;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3 | 学习 C++ | RUNOOB.COM | 2016-05-06 |
+-----------+--------------+---------------+-----------------+
1 rows in set (0.01 sec)
从结果上看,runoob_id 为 3 的 runoob_title 已被修改。
七、MySQL DELETE 语句
你可以使用 DELETE FROM 命令来删除 MySQL 数据表中的记录。
你可以在 mysql> 命令提示符或 PHP 脚本中执行该命令。
语法
以下是 DELETE 语句从 MySQL 数据表中删除数据的通用语法:
DELETE FROM table_name
WHERE condition;
参数说明:
table_name
是你要删除数据的表的名称。WHERE condition
是一个可选的子句,用于指定删除的行。如果省略WHERE
子句,将删除表中的所有行。
更多说明:
- 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
- 你可以在 WHERE 子句中指定任何条件
- 您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
实例
以下实例演示了如何使用 DELETE 语句。
1. 删除符合条件的行:
DELETE FROM students
WHERE graduation_year = 2021;
以上 SQL 语句删除了 students 表中所有 graduation_year 为 2021 的学生的记录。
2. 删除所有行:
DELETE FROM orders;
以上 SQL 语句删除了 orders 表中的所有记录,但表结构保持不变。
3. 使用子查询删除符合条件的行:
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date < '2023-01-01'
);
以上 SQL 语句通过子查询删除了 orders 表中在 '2023-01-01' 之前下的订单对应的客户。
注意: 在使用 DELETE 语句时,请确保你提供了足够的条件来确保只有你想要删除的行被删除。如果不提供 WHERE 子句,将删除表中的所有行,可能导致不可预测的结果。
从命令行中删除数据
这里我们将在 DELETE 命令中使用 WHERE 子句来删除 MySQL 数据表 runoob_tbl 所选的数据。
实例
以下实例将删除 runoob_tbl 表中 runoob_id 为3 的记录:
DELETE 语句:
mysql> use RUNOOB;
Database changed
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
Query OK, 1 row affected (0.23 sec)
八、MySQL LIKE 子句
我们知道在 MySQL 中使用 SELECT 命令来读取数据, 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 = 来设定获取数据的条件,如 "runoob_author = 'RUNOOB.COM'"。
但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录,这时我们就需要在 WHERE 子句中使用 LIKE 子句。
LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一起使用,用于搜索符合某种模式的字符串。
LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
语法
以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
参数说明:
column1
,column2
, ... 是你要选择的列的名称,如果使用*
表示选择所有列。table_name
是你要从中查询数据的表的名称。column_name
是你要应用LIKE
子句的列的名称。pattern
是用于匹配的模式,可以包含通配符。
更多说明:
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在 WHERE 子句中使用LIKE子句。
- 你可以使用LIKE子句代替等号 =。
- LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。
实例
以下是一些 LIKE 子句的使用实例。
1. 百分号通配符 %:
% 通配符表示零个或多个字符。例如,'a%' 匹配以字母 'a' 开头的任何字符串。
SELECT * FROM customers WHERE last_name LIKE 'S%';
以上 SQL 语句将选择所有姓氏以 'S' 开头的客户。
2. 下划线通配符 _:
_ 通配符表示一个字符。例如,'_r%' 匹配第二个字母为 'r' 的任何字符串。
SELECT * FROM products WHERE product_name LIKE '_a%';
以上 SQL 语句将选择产品名称的第二个字符为 'a' 的所有产品。
3. 组合使用 % 和 _:
SELECT * FROM users WHERE username LIKE 'a%o_';
以上 SQL 语句将匹配以字母 'a' 开头,然后是零个或多个字符,接着是 'o',最后是一个任意字符的字符串,如 'aaron'、'apol'。
4. 不区分大小写的匹配:
SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;
以上 SQL 语句将选择姓氏以 'smi' 开头的所有员工,不区分大小写。
LIKE 子句提供了强大的模糊搜索能力,可以根据不同的模式和需求进行定制。在使用时,请确保理解通配符的含义,并根据实际情况进行匹配。
在命令提示符中使用 LIKE 子句
以下我们将在 SELECT 命令中使用 WHERE...LIKE 子句来从 MySQL 数据表 runoob_tbl 中读取数据。
实例
以下是我们将 runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录:
SQL LIKE 语句:
mysql> use RUNOOB;
Database changed
mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)
九、MySQL ORDER BY(排序) 语句
我们知道从 MySQL 表中使用 SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序,我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。
MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序(ASC)或降序(DESC)排序。
语法
以下是 SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
参数说明:
column1
,column2
, ... 是你要选择的列的名称,如果使用*
表示选择所有列。table_name
是你要从中查询数据的表的名称。ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...
是用于指定排序顺序的子句。ASC
表示升序(默认),DESC
表示降序。
更多说明:
- 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
- 你可以设定多个字段来排序。
- 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
- 你可以添加 WHERE...LIKE 子句来设置条件。
实例
以下是一些 ORDER BY 子句的使用实例。
1. 单列排序:
SELECT * FROM products
ORDER BY product_name ASC;
以上 SQL 语句将选择产品表 products 中的所有产品,并按产品名称升序 ASC 排序。
2. 多列排序:
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
以上 SQL 语句将选择员工表 employees 中的所有员工,并先按部门 ID 升序 ASC 排序,然后在相同部门中按雇佣日期降序 DESC 排序。
3. 使用数字表示列的位置:
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;
以上 SQL 语句将选择员工表 employees 中的名字和工资列,并按第三列(salary)降序 DESC 排序,然后按第一列(first_name)升序 ASC 排序。
4. 使用表达式排序:
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;
以上 SQL 语句将选择产品表 products 中的产品名称和根据折扣率计算的折扣后价格,并按折扣后价格降序 DESC 排序。
5.从 MySQL 8.0.16 版本开始,可以使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值:
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;
以上 SQL 语句将选择产品表 products 中的产品名称和价格,并按价格降序 DESC 排序,将 NULL 值排在最后。
相反,如果你想让 NULL 值排在前面,可以这样写:
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS FIRST;
ORDER BY 子句是一个强大的工具,可以根据不同的业务需求对查询结果进行排序。在实际应用中,注意选择适当的列和排序顺序,以获得符合期望的排序效果。
在命令提示符中使用 ORDER BY 子句
以下将在 SELECT 语句中使用 ORDER BY 子句来读取 MySQL 数据表 runoob_tbl 中的数据:
实例
尝试以下实例,结果将按升序及降序排列。
SQL 排序
mysql> use RUNOOB;
Database changed
mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
| 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
| 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
mysql> SELECT * from runoob_tbl ORDER BY submission_date DESC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
| 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
读取 runoob_tbl 表中所有数据并按 submission_date 字段的升序排列。
十、MySQL GROUP BY 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具,尤其在处理大量数据时,它能够提供有用的汇总信息。
GROUP BY 语法
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
column1
:指定分组的列。aggregate_function(column2)
:对分组后的每个组执行的聚合函数。table_name
:要查询的表名。condition
:可选,用于筛选结果的条件。
假设有一个名为 orders 的表,包含以下列:order_id、customer_id、order_date 和 order_amount。
我们想要按照 customer_id 进行分组,并计算每个客户的订单总金额,SQL 语句如下:
实例
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
以上实例中,我们使用 GROUP BY customer_id 将结果按 customer_id 列分组,然后使用 SUM(order_amount) 计算每个组中 order_amount 列的总和。
AS total_amount 是为了给计算结果取一个别名,使查询结果更易读。
注意事项:
GROUP BY
子句通常与聚合函数一起使用,因为分组后需要对每个组进行聚合操作。SELECT
子句中的列通常要么是分组列,要么是聚合函数的参数。- 可以使用多个列进行分组,只需在
GROUP BY
子句中用逗号分隔列名即可。 - 实例
SELECT column1, column2, aggregate_function(column3)
FROM TABLE_NAME
WHERE condition
GROUP BY column1, column2;
实例演示
本章节实例使用到了以下表结构及数据,使用前我们可以先将以下数据导入数据库中。
实例
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` INT(11) NOT NULL,
`name` CHAR(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
导入成功后,执行以下 SQL 语句:
mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | signin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)
使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
mysql> SELECT name, SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | signin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
4 rows in set (0.00 sec)
其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
select coalesce(a,b,c);
参数说明:如果 a==null,则选择 b;如果 b==null,则选择 c;如果 a!=null,则选择 a;如果 a b c 都为 null ,则返回为 null(没意义)。
以下实例中如果名字为空我们使用总数代替:
mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)