参考:https://blog.csdn.net/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)