Mysql -- 更新与删除

最新推荐文章于 2024-04-03 15:58:48 发布

转载最新推荐文章于 2024-04-03 15:58:48 发布 · 221 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://www.yiibai.com/mysql/limit.html

MySQL 专栏收录该内容

19 篇文章

订阅专栏

本文详细介绍了如何在MySQL中使用UPDATE和DELETE语句进行数据更新和删除操作，并通过实例展示了LIMIT子句与ORDER BY结合使用的方法，以高效获取最高或最低值的数据记录。

参考：https://blog.youkuaiyun.com/qq_35246620/article/details/73722704

update my_class set name="x" where grade ="XXX";

mysql> update my_class set name="x" where grade ="XXX";
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_class;
+-------+------+------+
| grade | room | name |
+-------+------+------+
| XXX   | C315 | x    |
| YYY   | A215 | NULL |
| ZZZ   | B315 | NULL |
+-------+------+------+
3 rows in set (0.00 sec)

LIMIT 使用

https://www.yiibai.com/mysql/limit.html

使用MySQL LIMIT获得最高和最低的值

LIMIT子句经常与ORDER BY子句一起使用。首先，使用ORDER BY子句根据特定条件对结果集进行排序，然后使用LIMIT子句来查找最小或最大值。

注意：ORDER BY子句按指定字段排序的使用。

一张customers表，其表结构如下所示 -

mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber         | int(11)       | NO   | PRI | NULL    |       |
| customerName           | varchar(50)   | NO   |     | NULL    |       |
| contactLastName        | varchar(50)   | NO   |     | NULL    |       |
| contactFirstName       | varchar(50)   | NO   |     | NULL    |       |
| phone                  | varchar(50)   | NO   |     | NULL    |       |
| addressLine1           | varchar(50)   | NO   |     | NULL    |       |
| addressLine2           | varchar(50)   | YES  |     | NULL    |       |
| city                   | varchar(50)   | NO   |     | NULL    |       |
| state                  | varchar(50)   | YES  |     | NULL    |       |
| postalCode             | varchar(15)   | YES  |     | NULL    |       |
| country                | varchar(50)   | NO   |     | NULL    |       |
| salesRepEmployeeNumber | int(11)       | YES  | MUL | NULL    |       |
| creditLimit            | decimal(10,2) | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set

例如，要查询信用额度最高的前五名客户，请使用以下查询：

SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;

执行上面查询语句，得到以下结果 -

mysql> SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;
+----------------+------------------------------+-------------+
| customernumber | customername                 | creditlimit |
+----------------+------------------------------+-------------+
|            141 | Euro+ Shopping Channel       | 227600      |
|            124 | Mini Gifts Distributors Ltd. | 210500      |
|            298 | Vida Sport, Ltd              | 141300      |
|            151 | Muscle Machine Inc           | 138500      |
|            187 | AV Stores, Co.               | 136800      |
+----------------+------------------------------+-------------+
5 rows in set

以下查询将返回信用限额最低的五位客户：

SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY
 creditlimit ASC
LIMIT 5;

删除操作

删除name="a"的3条数据：

delete from my_copy where name = "a" limit 3;

mysql> select * from my_copy;
+------+
| name |
+------+
| a    |
| a    |
| a    |
| a    |
| b    |
| b    |
| c    |
| c    |
| c    |
| c    |
+------+
10 rows in set (0.00 sec)

mysql> delete from my_copy where name = "a" limit 3;
Query OK, 3 rows affected (0.04 sec)

mysql> select * from my_copy;
+------+
| name |
+------+
| a    |
| b    |
| b    |
| c    |
| c    |
| c    |
| c    |
+------+
7 rows in set (0.00 sec)