MYSQL-增、删、改

INSERT：向表中插入记录

不指明列

INSERT INTO employees VALUES(1,'refine','2020-12-14',20000);

指明列

• 推荐使用指明列的方式插入记录

INSERT INTO employees (id,emp_name,hire_date) VALUES(2,'lucy','2006-05-06');

添加多条记录

INSERT INTO employees (id, emp_name, salary)
VALUES
	(3, 'tom', 8000),
	(4, 'jim', 28000),
	(5, 'wang', 80000);

将查询结果插入表中

-- 将查询结果插入到表中
INSERT INTO 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 …

1. 更新一条记录或批量更新记录

UPDATE employees SET hire_date=NOW() WHERE id=3;
UPDATE employees SET hire_date=STR_TO_DATE('2008-11-05','%Y-%m-%d'),salary=3000 WHERE id=5;
UPDATE employees SET salary=salary+500 WHERE emp_name LIKE '%a%';

2. 由于约束影响，导致更新失败

UPDATE atguigudb.employees
SET department_id = 10000
WHERE
	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：删除表记录

1. 删除一条记录或批量删除记录

DELETE FROM employees WHERE id=202;

2. 清空表数据

DELETE FROM employees;

3. 由于约束影响，导致更新失败

DELETE
FROM
	atguigudb.departments
WHERE
	department_id = 50;

计算列

• 概念：某一列的值是通过别的列计算得来
• MySQL不支持MySQL 5.7之前的计算列

-- 计算列
CREATE TABLE employees1(
id INT,
emp_name VARCHAR(15),
salary DOUBLE(10,2),
coefficient DOUBLE(10,2),
bonus DOUBLE(10,2) GENERATED ALWAYS AS (salary*bonus) VIRTUAL
);