2.DML

最新推荐文章于 2024-09-04 14:23:09 发布

小恐龙观铃

最新推荐文章于 2024-09-04 14:23:09 发布

阅读量373

点赞数 7

分类专栏： Mysql 基础文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_69844818/article/details/134731751

版权

Mysql 同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

基础

16 篇文章 0 订阅

订阅专栏

添加数据(Insert)

修改数据(Update)

删除数据(Delete)

DML-添加数据

mysql> insert into employee(id,workno,name,gender,age,idcard,entrydate) values(1,'1','itcast','男',10,'123456789012345678','2000-01-01');
ERROR 1054 (42S22): Unknown column 'gender' in 'field list'
mysql> insert into employee(id,workno,name,gendef,age,idcard,entrydate) values(1,'1','itcast','男',10,'123456789012345678','2000-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from 
Display all 760 possibilities? (y or n) 
mysql> select * from employee;
+------+--------+--------+--------+------+--------------------+------------+
| id   | workno | name   | gendef | age  | idcard             | entrydate  |
+------+--------+--------+--------+------+--------------------+------------+
|    1 | 1      | itcast | 男     |   10 | 123456789012345678 | 2000-01-01 |
+------+--------+--------+--------+------+--------------------+------------+
1 row in set (0.00 sec)




mysql> insert into employee value (3,'3','张无忌','男',18,'123456789123456789','2025-10-15');
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+------+--------+-----------+--------+------+--------------------+------------+
| id   | workno | name      | gendef | age  | idcard             | entrydate  |
+------+--------+-----------+--------+------+--------------------+------------+
|    1 | 1      | itcast    | 男     |   10 | 123456789012345678 | 2000-01-01 |
|    2 | 2      | itcast2   | 男     |   21 | 123456789012345678 | 2000-01-01 |
|    3 | 3      | 张无忌    | 男     |   18 | 123456789123456789 | 2025-10-15 |
+------+--------+-----------+--------+------+--------------------+------------+
3 rows in set (0.00 sec)

多字段同时插入

DML-修改-删除

更新记录

mysql> update employee set name = 'itheima' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+------+--------+-----------+--------+------+--------------------+------------+
| id   | workno | name      | gendef | age  | idcard             | entrydate  |
+------+--------+-----------+--------+------+--------------------+------------+
|    1 | 1      | itheima   | 男     |   10 | 123456789012345678 | 2000-01-01 |
|    2 | 2      | itcast2   | 男     |   21 | 123456789012345678 | 2000-01-01 |
|    3 | 3      | 张无忌    | 男     |   18 | 123456789123456789 | 2025-10-15 |
+------+--------+-----------+--------+------+--------------------+------------+
3 rows in set (0.00 sec)


mysql> select * from employee;
+------+--------+-----------+--------+------+--------------------+------------+
| id   | workno | name      | gendef | age  | idcard             | entrydate  |
+------+--------+-----------+--------+------+--------------------+------------+
|    1 | 1      | itheima   | 男     |   10 | 123456789012345678 | 2000-01-01 |
|    2 | 2      | itcast2   | 男     |   21 | 123456789012345678 | 2000-01-01 |
|    3 | 3      | 张无忌    | 男     |   18 | 123456789123456789 | 2025-10-15 |
+------+--------+-----------+--------+------+--------------------+------------+
3 rows in set (0.00 sec)

mysql> update employee set name='小糟',gendef='女' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+------+--------+-----------+--------+------+--------------------+------------+
| id   | workno | name      | gendef | age  | idcard             | entrydate  |
+------+--------+-----------+--------+------+--------------------+------------+
|    1 | 1      | 小糟      | 女     |   10 | 123456789012345678 | 2000-01-01 |
|    2 | 2      | itcast2   | 男     |   21 | 123456789012345678 | 2000-01-01 |
|    3 | 3      | 张无忌    | 男     |   18 | 123456789123456789 | 2025-10-15 |
+------+--------+-----------+--------+------+--------------------+------------+
3 rows in set (0.00 sec)

 不带where条件 会更新表中所有记录 
mysql> update employee set entrydate ='2008-01-01';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from employee;
+------+--------+-----------+--------+------+--------------------+------------+
| id   | workno | name      | gendef | age  | idcard             | entrydate  |
+------+--------+-----------+--------+------+--------------------+------------+
|    1 | 1      | 小糟      | 女     |   10 | 123456789012345678 | 2008-01-01 |
|    2 | 2      | itcast2   | 男     |   21 | 123456789012345678 | 2008-01-01 |
|    3 | 3      | 张无忌    | 男     |   18 | 123456789123456789 | 2008-01-01 |
+------+--------+-----------+--------+------+--------------------+------------+
3 rows in set (0.00 sec)

DML 删除数据

mysql> delete from employee where gendef='女';
Query OK, 1 row affected (0.00 sec)

mysql> delete from employee;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from employee;
Empty set (0.00 sec)