|
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); |
|