MySQL中数据处理之增删改的那些事（超级详细，附带代码）

数据处理之增删改

准备工作

CREATE TABLE IF NOT EXISTS emp1(
	id INT,
	NAME VARCHAR(15),
	hire_date DATE,
	salary DOUBLE(10,2)
);

添加数据

方式1：一条一条的插入数据

# ①
INSERT INTO emp1 VALUES(1,'TOM','2001-12-21',3400); # 注意：一定要按照声明的字段的先后顺序添加
 # ② 
 INSERT INTO emp1 (id, NAME, hire_date, salary) VALUES(2, 'Jerry', '2002-10-2', 4000);
# ③
INSERT INTO emp1 (id, NAME, salary) VALUES(3, 'Jerry', 4500); 
 # ④
 INSERT INTO emp1 (id, NAME, hire_date, salary) VALUES(4, 'Jim', '2002-10-2', 4660),(5, 'Bob', '2002-10-2', 4650);

方式2：将查询结果插入表中

INSERT INTO emp1(id, NAME, salary,hire_date) SELECT employee_id,last_name,salary,hire_date FROM employees WHERE department_id IN (60,70);

更新数据

# 更新数据
UPDATE emp1 SET hire_date = CURDATE() WHERE id = 3;
# 同时修改一条数据的多个字段
UPDATE emp1 SET hire_date = CURDATE(), salary = 6000 WHERE id = 4;

删除数据

# 删除数据
DELETE FROM emp1 WHERE id = 1;