mysql一道面試題

mysql> #一道面試題
mysql> #把一張表num 的值[20-30]之間的數全改為20
mysql> #並且把[30-40]之間的數全改為30
mysql> create table mianshi (
    -> num int
    -> );
Query OK, 0 rows affected (1.94 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| class          |
| m              |
| mianshi        |
| msg            |
| snsmember      |
| test1          |
+----------------+
6 rows in set (0.52 sec)

mysql> insert into mianshi
    -> values(3),(8),(12),(15),(21),(25),(26),(32),(33),(39),(43),(52);
Query OK, 12 rows affected (0.22 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> desc mianshi;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.20 sec)

mysql> select * from mianshi;
+------+
| num  |
+------+
|    3 |
|    8 |
|   12 |
|   15 |
|   21 |
|   25 |
|   26 |
|   32 |
|   33 |
|   39 |
|   43 |
|   52 |
+------+
12 rows in set (0.01 sec)

mysql> #把num當成變數看，因此num/10取整，再乘以10
mysql> update mianshi set num=floor(num/10)*10
    -> where num>=20 and num<=39;
Query OK, 6 rows affected (0.59 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from mianshi;
+------+
| num  |
+------+
|    3 |
|    8 |
|   12 |
|   15 |
|   20 |
|   20 |
|   20 |
|   30 |
|   30 |
|   30 |
|   43 |
|   52 |
+------+
12 rows in set (0.00 sec)

mysql> exit