#数据处理之增删改操作
#1. 添加数据
CREATE TABLE emp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
SELECT *
FROM emp1;
DESC emp1;
#方式一:
#注意:添加的字段值要与emp1表中字段声明的顺序一致
INSERT INTO emp1
VALUES(1,'Tom',CURDATE(),5600);
#错误的
INSERT INTO emp1
VALUES(2,CURDATE(),'王子健',5600);
#错误的
INSERT INTO emp1
VALUES(2,CURDATE(),5600);
#常见的方式
INSERT INTO emp1(id,hire_date,salary,emp_name)
VALUES(2,CURDATE(),5600,'王子健');
#未声明赋值的字段,默认赋值为null
INSERT INTO emp1(id,hire_date,emp_name)
VALUES(3,CURDATE(),'张晓栋');
#方式二:基于现有的表
INSERT INTO emp1(id,emp_name,salary)
SELECT employee_id,last_name,salary
FROM employees
WHERE department_id IN (50,60,70);
SELECT *
FROM emp1;
DESC emp1;
DESC employees;
#2. 删除操作:delete from ... where ....
SELECT *
FROM emp1;
DELETE FROM emp1
#where id = 3;
WHERE salary < 4000;
#3. 修改操作:update ... set ...,....,... where ...
UPDATE emp1
SET salary = 6600
WHERE id = 1;
UPDATE emp1
SET salary = 7600,hire_date = '2020-02-01'
WHERE emp_name = 'Tom';
#总结:由于约束的影响,可能会导致增、删、改操作失败。