MySQL DML操作详解

一、数据库DML操作(增删改)

1.创建一个员工表

-- 创建一张员工表
create table t_employee (
 id bigint primary key auto_increment,
 name varchar(20) not null,
 gender varchar(10),
 birthday date,
 entry_date date,
 job varchar(30),
 salary double,
 resume text
 );

mysql> desc t_employee;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | NO   |     | NULL    |                |
| gender     | varchar(10) | YES  |     | NULL    |                |
| birthday   | date        | YES  |     | NULL    |                |
| entry_date | date        | YES  |     | NULL    |                |
| job        | varchar(30) | YES  |     | NULL    |                |
| salary     | double      | YES  |     | NULL    |                |
| resume     | text        | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

2.插入语句 ---insert

mysql> insert into t_employee (name,gender,job,salary,resume) values ('王宝强','
男','演员','1000000','北漂族');
Query OK, 1 row affected (0.08 sec)

mysql> select * from t_employee;
+----+--------+--------+----------+------------+------+---------+--------+
| id | name   | gender | birthday | entry_date | job  | salary  | resume |
+----+--------+--------+----------+------------+------+---------+--------+
|  1 | 王宝强  | 男     | NULL     | NULL       | 演员 | 1000000  | 北漂族 |
+----+--------+--------+----------+------------+------+---------+--------+
1 row in set (0.01 sec)

3.更新语句 ---update

--  1、将修改id=2的name='王铁柱',birthday='1975-10-10'

mysql> update t_employee set name='王铁柱',birthday='1975-10-10' where id=2 ;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_employee;
+----+--------+--------+------------+------------+------+---------+--------+
| id | name   | gender | birthday   | entry_date | job  | salary  | resume |
+----+--------+--------+------------+------------+------+---------+--------+
|  1 | 王宝强 | 男     | NULL       | NULL       | 演员 | 1000000 | 北漂族 |
|  2 | 王铁柱 | 男     | 1975-10-10 | NULL       | 演员 | 1000000 | 北漂族 |
|  3 | 王宝强 | 男     | NULL       | NULL       | 演员 | 1000000 | 北漂族 |
|  4 | 王宝强 | 男     | NULL       | NULL       | 演员 | 1000000 | 北漂族 |
|  5 | 王宝强 | 男     | NULL       | NULL       | 演员 | 1000000 | 北漂族 |
+----+--------+--------+------------+------------+------+---------+--------+
5 rows in set (0.02 sec)

   
4.删除语句 ---delete

--  1、删除表中ID=3的记录。
delete from t_employee where id=3;

Query OK, 1 row affected (0.08 sec)

mysql> select * from t_employee;
+----+--------+--------+------------+------------+------+---------+--------+
| id | name   | gender | birthday   | entry_date | job  | salary  | resume |
+----+--------+--------+------------+------------+------+---------+--------+
|  1 | 王宝强 | 男     | NULL       | NULL       | 演员 | 1000000 | 北漂族 |
|  2 | 王铁柱 | 男     | 1975-10-10 | NULL       | 演员 | 1000000 | 北漂族 |
|  4 | 王宝强 | 男     | NULL       | NULL       | 演员 | 1000000 | 北漂族 |
|  5 | 王宝强 | 男     | NULL       | NULL       | 演员 | 1000000 | 北漂族 |
+----+--------+--------+------------+------------+------+---------+--------+
4 rows in set (0.00 sec)

--    2、删除表中所有记录。
 delete from t_employee;