第九章——编辑数据

9.1更新数据

9.1.1update更新数据

使用update 来更新列中的值
语法

update 表名 set 更新的字段 where 更新的条件

注意：如果不加where则将会更新一列的值
扩展：防止update 和delete
在启动mysql监听器的时候加上-safe-updates选项，如果update 和delete语句上没有where的话那么就不能执行啦

C:\Users\ASUS>mysql -u root -p  --safe-updates
Enter password: ****
mysql> update tb set remark ='有';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

9.1.2update配合order by使用

update语句中也可以配合order by来使用<br /> 示列：给tb表中销售额最低的3名添加marker为加油

mysql> update tb set remark='加油' order by sales limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from tb order by sales;
+-------+-------+-------+------------+
| empid | sales | month | remark     |
+-------+-------+-------+------------+
| A103  |    12 |     6 | 加油       |
| A103  |    17 |     5 | 加油       |
| A107  |    87 |     6 | 加油       |
| A104  |    93 |     5 | 无特殊记录 |
| A103  |   101 |     4 | 优秀       |
| A103  |   101 |     4 | 优秀       |
| A104  |   181 |     5 | 优秀       |
| A101  |   184 |     4 | 优秀       |
| A102  |   205 |     6 | 优秀       |
| A101  |   300 |     5 | 优秀       |
+-------+-------+-------+------------+
10 rows in set (0.00 sec)

在加上offset还可以从第几条记录到第几条记录更新

9.2复制符合条件的记录

9.2.1只复制符合条件记录

  **示列**：创建表A101_table并复制tb表中A101员工的记录

mysql> create table A101_table select * from tb where empid ='A101';
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select* from A101_table;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   184 |     4 |
| A101  |   300 |     5 |
+-------+-------+-------+
2 rows in set (0.00 sec)

9.2.2排序后复制

示列：提前销售表tb中销售额从高到底中第2名到第五名的记录

mysql> create table tb2_top5 select * from tb order by sales desc limit 5 offset 1;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select* from tb2_top5;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A102  |   205 |     6 |
| A101  |   184 |     4 |
| A104  |   181 |     5 |
| A103  |   101 |     4 |
| A103  |   101 |     4 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql>

9.3删除数据

9.3.1删除数据

 **   语法：**

delete from 表名 where 条件

   如果不加条件会删除整个表中的内容

9.3.2排序后删除

       先使用order by 排序然后使用limit指定要删除的记录数<br />**示列**：删除表tb_copy中销售额最大的前四名(tb_copy是复制的tb表)

mysql> delete from tb_copy order by sales desc limit 4;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from tb_copy;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A103  |   101 |     4 |
| A103  |    17 |     5 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
+-------+-------+-------+
6 rows in set (0.00 sec)

mysql>