一、数据库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;