-- 将查询结果插入到表中INSERTINTO employees (id, emp_name, salary)SELECT
employee_id,
last_name,
salary
FROM
atguigudb.employees
WHERE
department_id IN(20,30,40);
UPDATE:更新表记录
格式UPDATE … SET … WHERE …
更新一条记录或批量更新记录
UPDATE employees SET hire_date=NOW()WHERE id=3;UPDATE employees SET hire_date=STR_TO_DATE('2008-11-05','%Y-%m-%d'),salary=3000WHERE id=5;UPDATE employees SET salary=salary+500WHERE emp_name LIKE'%a%';
由于约束影响,导致更新失败
UPDATE atguigudb.employees
SET department_id =10000WHERE
employee_id =102;/*
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))
*/
DELETE:删除表记录
删除一条记录或批量删除记录
DELETEFROM employees WHERE id=202;
清空表数据
DELETEFROM employees;
由于约束影响,导致更新失败
DELETEFROM
atguigudb.departments
WHERE
department_id =50;