插入语句
insert into 表 values (值......)
mysql> insert into employees values
-> (134, '张三', '2019-5-10', '2000-10-12', 'zhangsan@tedu.cn', '15088772354', 9),
-> (135, '李四', '2020-8-20', '1999-6-23', 'lisi@tedu.cn', '13323458734', 9);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
指定列名插入
mysql> insert into departments(dept_name,dept_id) values ('咨询部',11);
Query OK, 1 row affected (0.00 sec)
mysql> insert into departments(dept_name) values ('公关部');
Query OK, 1 row affected (0.00 sec)
支持子查询
mysql> insert into employees(name,hire_date,birth_date,email,phone_number,dept_id)(select name,hire_date,birth_date,email,phone_number,dept_id from employees where name='张三');
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
set语句
INSERT INTO 表名 SET 列名1=列值1, 列名2=列值2, ...
mysql> insert into departments set dept_name='采购部';
Query OK, 1 row affected (0.00 sec)
修改表
UPDATE 表名称 SET 列名称=新值, 列名称=新值, ... WHERE 筛选条件
mysql> update departments set dept_name='人力资源部' where dept_name='人事部';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
多表修改
UPDATE 表1 AS 表1别名
INNER | LEFT | RIGHT JOIN 表2 AS 表2别名
ON 连接条件
SET 列=值, 列=值, ...
WHERE 连接条件
mysql> update departments as d
-> inner join employees as e
-> on d.dept_id=e.dept_id
-> set d.dept_name='企划部'
-> where e.name='李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from departments;
+---------+-----------------+
| dept_id | dept_name |
+---------+-----------------+
| 1 | 人力资源部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | 企划部 |
| 11 | 咨询部 |
| 12 | 公关部 |
+---------+-----------------+
11 rows in set (0.00 sec)
删除表记录
DELETE FROM 表名 WHERE 筛选条件;
清空表
truncate table 表名
mysql> truncate table wage_grade;
Query OK, 0 rows affected (0.00 sec)