MySql（15）数据库DML:增、删、改

Mysql专栏入口

链接：https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w
提取码：ttst
提取码：ttst
专栏中有mysql的导入导出，里面有教如何导入

添加数据表

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

一、增(insert)

1.1 正常插入

# 不指定顺序，按照存入表的默认顺序
INSERT INTO emp1
VALUES (1,'TOM','2000-12-21',3400)

# 指定字段插入(推荐！)
INSERT INTO emp1 (id,hire_date,salary,name)
VALUES (1,'2000-12-21',3400,'TOM')

# 多行插入
INSERT INTO emp1 (id,hire_date,salary,name)
VALUES 
(1,'2000-12-21',3400,'TOM'),
(2,'2000-12-21',3400,'TOM'),
(3,'2000-12-21',3400,'TOM')

1.2 查询结果插入到表中

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

二、改(update)

将id为2的记录日期修改为当前时间

UPDATE emp1
SET hire_date = NOW()
WHERE id = 2

修改id为2记录的工资和日期同时做出修改

UPDATE emp1
SET hire_date = NOW(),salary = 6000
WHERE id = 2

将id为2的记录薪资提高百分之二十

UPDATE emp1
SET salary = salary * 1.2
WHERE id = 2

三、删(delete)

删除id为1的记录

DELETE FROM emp1
WHERE id = 1