SQL插入、添加、删除

‖蠻忝謃☆

1. 新增：
语法
1. INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
使用这个语法只能新增一条记录，如果不添加列名，则需要添加该表中所有字段的值，如果列出各字段，则只需要添加列出各字段的值。如：
INSERT INTO departments
VALUES (70, 'Public Relations', 100, 1700);
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
插入空值的使用方法：
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);
插入特殊的值
INSERT INTO employees
VALUES (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT', 11000, NULL, 100, 30);
或者：
INSERT INTO employees
VALUES (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
sysdate,
'AC_ACCOUNT', 11000, NULL, 100, 30);
还可以使用&来插入：
INSERT INTO departments
(department_id, department_name, location_id)
VALUES (&department_id, '&department_name',&location);
在对话框中录入要插入的值
以上的方式都只能插入一条记录，下面我们使用从别的表中插入记录就可以同时插入多条记录：
如：
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子句。
2. 修改：
  UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];可以同时修改多条记录。
只修改某一个字段：
UPDATE copy_emp
SET department_id = 110;
使用子查询同时修改多个字段：
UPDATE employees
SET job_id =
(SELECT job_id FROM employees WHERE employee_id = 205),
salary =
(SELECT salary FROM employees WHERE employee_id = 205)
WHERE employee_id = 114;
基于另一个表修改多个字段：
UPDATE copy_emp
SET department_id =
(SELECT department_id FROM employees WHERE employee_id = 100)
WHERE job_id =
(SELECT job_id FROM employees WHERE employee_id = 200);
3．删除：
    语法：DELETE [FROM] table
[WHERE condition];
从该表中删除指定的记录：
如：   DELETE FROM departments
WHERE department_name = 'Finance';
DELETE FROM departments
WHERE department_id IN (30, 40);
如果没有WHERE子句则将删除该表中的所有记录
基于另外一个表的删除
       DELETE FROM employees
WHERE department_id =(SELECT department_id FROM departments
WHERE department_name LIKE '%Public%');
    注意事项
with check option
  如果使用了 with check option 则你在修改创建某视图的基表中的数据时, 产生的行不允许不存在视图中。
INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);