MySQL更新数据，删除数据综合实训

第一次制作：

Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc book;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| name        | varchar(60) | NO   |     | NULL    |       |
| price       | float       | NO   |     | NULL    |       |
| upload_time | datetime    | NO   |     | NULL    |       |
| state       | int(11)     | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table modify state default 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'state
 default 0' at line 1
mysql> alter table modify state int not null default 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'state
 int not null default 0' at line 1
mysql> alter table book modify state int not null default 0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc book;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| name        | varchar(60) | NO   |     | NULL    |       |
| price       | float       | NO   |     | NULL    |       |
| upload_time | datetime    | NO   |     | NULL    |       |
| state       | int(11)     | NO   |     | 0       |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> create table user (id int primary key auto_increment,name varchar()
    -> ^C
mysql> create table user (id int primary key auto_increment,name varchar(30) not
 null,state int not null default 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql> create table user (id int primary key auto_increment,name varchar(30) not
 null,state int not null default 0);
Query OK, 0 rows affected (0.11 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
| state | int(11)     | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> insert into book (name,price,upload_time) values ('JAVA基础入门',59.00,cu
rrent_timestamp);
ERROR 1366 (HY000): Incorrect string value: '\xE5\x9F\xBA\xE7\xA1\x80...' for co
lumn 'name' at row 1
mysql> alter table convert to character set utf8mb4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'conve
rt to character set utf8mb4' at line 1
mysql> alter table user convert to character set utf8mb4;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table book convert to character set utf8mb4;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into book (name,price,upload_time) values ('JAVA基础入门',59.00,cu
rrent_timestamp);
Query OK, 1 row affected (0.04 sec)

mysql> select * from book;
+------------------+-------+---------------------+-------+
| name             | price | upload_time         | state |
+------------------+-------+---------------------+-------+
| JAVA基础入门     |    59 | 2024-04-11 09:11:16 |     0 |
+------------------+-------+---------------------+-------+
1 row in set (0.00 sec)

mysql> insert into book (name,price,upload_time) values
    -> insert into book (name,price,upload_time) ^Clues
mysql> insert into book (name,price,upload_time,state) values
    -> ('三国演义',69,2),
    -> ('MYSQL数据库入门',40,0),
    -> ('西游记',59,2),
    -> ('红楼梦',33,2),
    -> ('水浒传',66.66,2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into book (name,price,upload_time) values
    -> ('三国演义',69,current_timestmp,2),
    -> ('MYSQL数据库入门',current_timestmp,40,0),
    -> ('西游记',^C,2),
mysql> ('西游记',59,2),^C
mysql> insert into book (name,price,upload_time,state) values
    -> ('三国演义',69,current_timestmp,2),
    -> ('MYSQL数据库入门',40,current_timestmp,0),
    -> ('西游记',59,current_timestmp,2),
    -> ('红楼梦',33,current_timestmp,2),
    -> ('水浒传',33,current_timestmp,2);
ERROR 1054 (42S22): Unknown column 'current_timestmp' in 'field list'
mysql> insert into book (name,price,upload_time,state) values
    -> ('三国演义',69,current_timesatmp,2),
    -> ('MYSQL数据库入门',40,current_timesatmp,0),
    -> ('西游记',59,current_timesatmp,2),
    -> ('红楼梦',33,current_timesatmp,2),
    -> ('水浒传',33,current_timesatmp,2);
ERROR 1054 (42S22): Unknown column 'current_timesatmp' in 'field list'
mysql> insert into book (name,price,upload_time,state) values
    -> ('三国演义',69,current_timestamp,2),
    -> ('MYSQL数据库入门',40,current_timestamp,0),
    -> ('西游记',59,current_timestamp,2),
    -> ('红楼梦',33,current_timestamp,2),
    -> ('水浒传',33,current_timestamp,2);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into book (name,price,upload_time,state) values ('JAVA开阀入门',49
,current_timestamp);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into book (name,price,upload_time,state) values ('JAVA开阀入门',49
,current_timestamp,);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
 line 1
mysql> insert into book (name,price,upload_time,state) values ('JAVA开阀入门',49
,current_timestamp,0);
Query OK, 1 row affected (0.03 sec)

mysql> select * from book;
+----------------------+-------+---------------------+-------+
| name                 | price | upload_time         | state |
+----------------------+-------+---------------------+-------+
| JAVA基础入门         |    59 | 2024-04-11 09:11:16 |     0 |
| 三国演义             |    69 | 2024-04-11 09:23:11 |     2 |
| MYSQL数据库入门      |    40 | 2024-04-11 09:23:11 |     0 |
| 西游记               |    59 | 2024-04-11 09:23:11 |     2 |
| 红楼梦               |    33 | 2024-04-11 09:23:11 |     2 |
| 水浒传               |    33 | 2024-04-11 09:23:11 |     2 |
| JAVA开阀入门         |    49 | 2024-04-11 09:24:38 |     0 |
+----------------------+-------+---------------------+-------+
7 rows in set (0.00 sec)

mysql> update book set state=0 where name='西游记';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from book;
+----------------------+-------+---------------------+-------+
| name                 | price | upload_time         | state |
+----------------------+-------+---------------------+-------+
| JAVA基础入门         |    59 | 2024-04-11 09:11:16 |     0 |
| 三国演义             |    69 | 2024-04-11 09:23:11 |     2 |
| MYSQL数据库入门      |    40 | 2024-04-11 09:23:11 |     0 |
| 西游记               |    59 | 2024-04-11 09:23:11 |     0 |
| 红楼梦               |    33 | 2024-04-11 09:23:11 |     2 |
| 水浒传               |    33 | 2024-04-11 09:23:11 |     2 |
| JAVA开阀入门         |    49 | 2024-04-11 09:24:38 |     0 |
+----------------------+-------+---------------------+-------+
7 rows in set (0.00 sec)

mysql> update book set price=66 where name='水浒传';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from book;
+----------------------+-------+---------------------+-------+
| name                 | price | upload_time         | state |
+----------------------+-------+---------------------+-------+
| JAVA基础入门         |    59 | 2024-04-11 09:11:16 |     0 |
| 三国演义             |    69 | 2024-04-11 09:23:11 |     2 |
| MYSQL数据库入门      |    40 | 2024-04-11 09:23:11 |     0 |
| 西游记               |    59 | 2024-04-11 09:23:11 |     0 |
| 红楼梦               |    33 | 2024-04-11 09:23:11 |     2 |
| 水浒传               |    66 | 2024-04-11 09:23:11 |     2 |
| JAVA开阀入门         |    49 | 2024-04-11 09:24:38 |     0 |
+----------------------+-------+---------------------+-------+
7 rows in set (0.00 sec)

mysql> update book set price=price*0.6;
Query OK, 7 rows affected (0.05 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from book;
+----------------------+-------+---------------------+-------+
| name                 | price | upload_time         | state |
+----------------------+-------+---------------------+-------+
| JAVA基础入门         |  35.4 | 2024-04-11 09:11:16 |     0 |
| 三国演义             |  41.4 | 2024-04-11 09:23:11 |     2 |
| MYSQL数据库入门      |    24 | 2024-04-11 09:23:11 |     0 |
| 西游记               |  35.4 | 2024-04-11 09:23:11 |     0 |
| 红楼梦               |  19.8 | 2024-04-11 09:23:11 |     2 |
| 水浒传               |  39.6 | 2024-04-11 09:23:11 |     2 |
| JAVA开阀入门         |  29.4 | 2024-04-11 09:24:38 |     0 |
+----------------------+-------+---------------------+-------+
7 rows in set (0.00 sec)

mysql> delete from book where='红楼梦';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '='红
楼梦'' at line 1
mysql> delete from book where name='红楼梦';
Query OK, 1 row affected (0.05 sec)

mysql> select * from book;
+----------------------+-------+---------------------+-------+
| name                 | price | upload_time         | state |
+----------------------+-------+---------------------+-------+
| JAVA基础入门         |  35.4 | 2024-04-11 09:11:16 |     0 |
| 三国演义             |  41.4 | 2024-04-11 09:23:11 |     2 |
| MYSQL数据库入门      |    24 | 2024-04-11 09:23:11 |     0 |
| 西游记               |  35.4 | 2024-04-11 09:23:11 |     0 |
| 水浒传               |  39.6 | 2024-04-11 09:23:11 |     2 |
| JAVA开阀入门         |  29.4 | 2024-04-11 09:24:38 |     0 |
+----------------------+-------+---------------------+-------+
6 rows in set (0.00 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
| state | int(11)     | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into user (name,state) values ('张三',0),('李四',0);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+--------+-------+
| id | name   | state |
+----+--------+-------+
|  1 | 张三   |     0 |
|  2 | 李四   |     0 |
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> alter table book add borrow_time datetime;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table book add borrower datetime;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc book;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| name        | varchar(60) | NO   |     | NULL    |       |
| price       | float       | NO   |     | NULL    |       |
| upload_time | datetime    | NO   |     | NULL    |       |
| state       | int(11)     | NO   |     | 0       |       |
| borrow_time | datetime    | YES  |     | NULL    |       |
| borrower    | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table book modify borrower int;
Query OK, 6 rows affected (0.28 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> update book set borrow_time=current_timestamp,borrower=1,state=1 where na
me ='MYSQL数据库入门'
    -> ；
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '；' a
t line 2
mysql> update book set borrow_time=current_timestamp,borrower=1,state=1 where na
me ='MYSQL数据库入门';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from book where name = 'MYSQL数据库入门';
+----------------------+-------+---------------------+-------+------------------
---+----------+
| name                 | price | upload_time         | state | borrow_time
   | borrower |
+----------------------+-------+---------------------+-------+------------------
---+----------+
| MYSQL数据库入门      |    24 | 2024-04-11 09:23:11 |     1 | 2024-04-11 09:35:
09 |        1 |
+----------------------+-------+---------------------+-------+------------------
---+----------+
1 row in set (0.00 sec)

mysql>

不足：

1. 没有考虑全面如借阅人问题，借阅时间

收获：

1. update 表名 set 字段名 = values[set 字段名 = values] [where 字段名 = values]
2. 如果只写update 表名 set 字段名 = values[set 字段名 = values]表示只对全表操作
3. delete form 表名 where 字段名 = values
4. select * from 表名
5. SQL函数 CURRENT_TIMESTAMP日期/时间函数，返回当前本地日期和时间。参见：https://blog.csdn.net/yaoxin521123/article/details/122739161
6. truncate和delete的区别参见：https://blog.csdn.net/weixin_48720080/article/details/129362367
7. auto_increment自动增长

ppt课件mysql第三章，数据的增删改查.pptx
https://www.alipan.com/s/xFY2AiYgqP5
提取码: f87h
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。