数据库的CRUD之delete

对查询的结果进行列值更新

语句

UPDATE table_name SET column=expr[,column=expr]
	[WHERE...][ORDER BY...][LIMIT...]

操作数据

mysql> CREATE TABLE exam_score(
    -> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL COMMENT '同学姓名',
    -> yuwen float DEFAULT 0.0 COMMENT '语⽂成绩',
    -> shuxue float DEFAULT 0.0 COMMENT '数学成绩',
    -> yingyu float DEFAULT 0.0 COMMENT '英语成绩'
    -> );
Query OK, 0 rows affected (0.45 sec)

--插入数据：
 insert into exam_score(name,yuwen,shuxue,yingyu) values
    -> ('蔡文姬',68,95,57),
    -> ('李白',84,76,74),
    -> ('虞姬',88,85,62),
    -> ('韩信',85,97,93),
    -> ('诸葛亮',54,87,46),
    -> ('貂蝉',77,75,78),
    -> ('小乔',76,66,36);

实例

1.删除李白同学的考试成绩

delete from exam_score where name='李白';

2.删除整张表的结构。慎用！
为删除准备的数据：

mysql> create table for_delete(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.59 sec)

mysql> insert into for_delete (name) values ('a'),('b'),('c');
Query OK, 3 rows affected (0.35 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

操作：

mysql> delete from for_delete;
Query OK, 3 rows affected (0.07 sec)

mysql> select * from for_delete;
Empty set (0.00 sec)
---删除成功

mysql> insert into for_delete (name) values ('d');
Query OK, 1 row affected (0.03 sec)

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | d    |
+----+------+
1 row in set (0.00 sec)
---在删整表后插入一条数据，自增id在原值上增长。

查看表结构，有AUTO_INCREMENT=n 项

mysql> show create table for_delete\G
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

截断表

慎用
1.只能对整表操作，不能像delete一样针对部分数据操作。
2.实际上MySQL不对数据操作，所以比delete快。(delete影响行数是n行)
3.会重置AUTO_INCREMEN项。

语法

TRUNCATE [TABLE] table_name;

实例

CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插⼊测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 查看测试数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)

操作

-- 截断整表数据，注意影响⾏数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)

-- 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)

-- 再插⼊⼀条数据，⾃增 id 在重新增⻓
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)

-- 查看数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)

-- 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)