数据处理之增删改

插入数据

字符和日期型数据应包含在单引号中。

INSERT INTO
employees(employee_id,last_name,email,hire_date,job_id)
VALUES (300,’Tom’,’tom@126.com’,to_date(‘2012-3-
21’,’yyyy-mm-dd’),’SA_RAP’);

从其它表中拷贝数据

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

• 不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对
应

更新数据

使用 WHERE 子句指定需要更新的数据。

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

如果省略 WHERE 子句，则表中的所有数据都将被更新

UPDATE copy_emp
SET department_id = 110;

更新中的数据完整性错误

UPDATE employees
SET department_id = 55
WHERE department_id = 110

1452 - Cannot add or update a child row: a foreign key constraint fails (myemployees.employees, CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments (department_id))
时间: 0.004s

删除数据

使用 WHERE 子句删除指定的记录。

DELETE FROM departments
WHERE department_name = 'Finance';

如果省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM copy_emp;

删除中的数据完整性错误

DELETE FROM departments
WHERE department_id = 60;

1451 - Cannot delete or update a parent row: a foreign key constraint fails (myemployees.employees, CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments (department_id))
时间: 0.002s