您的当前位置:首页正文

MySQL 教程(超详细,零基础可学、第二篇)

2024-10-31 来源:个人技术集锦

一、MySQL 删除数据表

二、使用 PHP 脚本删除数据表

三、MySQL 插入数据

四、MySQL 查询数据

 五、MySQL WHERE 子句

六、MySQL UPDATE 更新

七、MySQL DELETE 语句

八、MySQL LIKE 子句

九、MySQL ORDER BY(排序) 语句

十、MySQL GROUP BY 语句


一、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

可选。一个常量。可以是下列值中的任意一个:

  • MYSQLI_USE_RESULT(如果需要检索大量数据,请使用这个)
  • MYSQLI_STORE_RESULT(默认)

实例

以下实例使用了 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 是你要插入数据的表的名称。
  • column1column2column3, ... 是表中的列名。
  • value1value2value3, ... 是要插入的具体数值。

如果数据是字符型,必须使用单引号 ' 或者双引号 ",如: '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];

参数说明:

  • column1column2, ... 是你想要选择的列的名称,如果使用 * 表示选择所有列。
  • 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 子句中,你可以使用各种条件运算符(如 =<><=>=!=),逻辑运算符(如 ANDORNOT),以及通配符(如 %)等。

以下是一些进阶的 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;
参数说明:
  • column1column2, ... 是你要选择的列的名称,如果使用 * 表示选择所有列。
  • 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 是你要更新数据的表的名称。
  • column1column2, ... 是你要更新的列的名称。
  • value1value2, ... 是新的值,用于替换旧的值。
  • 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 子句来更新 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;

参数说明:

  • column1column2, ... 是你要选择的列的名称,如果使用 * 表示选择所有列。
  • 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], ...;

参数说明:

  • column1column2, ... 是你要选择的列的名称,如果使用 * 表示选择所有列。
  • 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)

Top