DML数据操作语言
对表中数据进行操作
对数据的增删改
增
# 增
insert
insert into 表名(字段1,字段2) 值('对应字段1的值','对应字段2的值')[字段不加引号，值要加引号，如果有自增或默认值那么字段和值可以不写]
-例：
mysql> insert into student(sname,sage,sbirthday,class) values('yuanli',3,NOW(),'Linux5');
mysql> insert student(sname,sage,sbirthday,class) values('yuanli',3,NOW(),'L5')(6,'wyd',2,'0','2021-11-12
00:00:00','L6');
mysql> select * from student;
+-----+--------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+--------+------+------+---------------------+-------+
| 1 | yuanli | 3 | 1 | 2023-07-25 10:10:54 | L5 |
| 2 | yuanli | 3 | 1 | 2023-07-25 10:11:51 | L5 |
| 3 | yuanli | 3 | 1 | 2023-07-25 10:13:19 | L5 |
| 4 | wyd | 2 | 1 | 2021-11-12 00:00:00 | L6 |
| 5 | yuanli | 3 | 1 | 2023-07-25 10:15:07 | L5 |
| 6 | wyd | 2 | 0 | 2021-11-12 00:00:00 | L6 |
+-----+--------+------+------+---------------------+-------+删
## 删
delete
delete from 库名.表名 where 字段1=对应值1 and 字段2=对应值2;[如果在库里就不用在表前面加上库名.了]
-#即使全部删除也要加where条件，后面加上成立的条件即可-
delete from 库名.表名 where 1=1;
-例：
-#全部删除-
delete from linux50.student where 1=1;
-#删除字段sno=7和sno=10的-
delete from linux50.student where sno=7 or sno=10;
-#删除test1用户-
mysql> delete from mysql.user where user='test1' and host='%';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| dev | % |
| dev1 | % |
| dev2 | % |
| dev3 | % |
| dev4 | % |
| dev5 | % |
| root | % |
| dev1 | localhost |
| root | localhost |
+------+-----------+
9 rows in set (0.00 sec)
-#删除dev1用户-
mysql> delete from mysql.user where user='dev1' and host='%';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| dev | % |
| dev2 | % |
| dev3 | % |
| dev4 | % |
| dev5 | % |
| root | % |
| dev1 | localhost |
| root | localhost |
+------+-----------+
8 rows in set (0.00 sec)改
# 改
update [where 后面最好根据唯一值去改]
update 库.表 set 字段='值' where x=x;
-例：
update student set ssex='0' where sno=10;
update student set ssex='1' where sname='wyd';
update student set ssex='1' where 1=1;使用update代替delete做伪删除
-##使用update代替delete做伪删除-
所有的删除都不是删除，是伪删除，是可以找回的
# 1.给表中添加状态列
mysql> alter table course add status enum('0','1') default'1';
mysql> select * from course;
+-----+--------+------+--------+
| cno | cname | tno | status |
+-----+--------+------+--------+
| 1 | 英语 | 007 | 1 |
| 2 | 语文 | 002 | 1 |
| 3 | 数学 | 003 | 1 |
+-----+--------+------+--------+
3 rows in set (0.00 sec)
# 2.使用update代替delete删除
mysql> update course set status='0' where cno=2;
mysql> select * from course;
+-----+--------+------+--------+
| cno | cname | tno | status |
+-----+--------+------+--------+
| 1 | 英语 | 007 | 1 |
| 2 | 语文 | 002 | 0 |
| 3 | 数学 | 003 | 1 |
+-----+--------+------+--------+
# 3.使用select查询时，加上条件
mysql> select * from course where status='1';
+-----+--------+------+--------+
| cno | cname | tno | status |
+-----+--------+------+--------+
| 1 | 英语 | 007 | 1 |
| 3 | 数学 | 003 | 1 |
+-----+--------+------+--------+