SQL堂上作业七
新行添加
传统添加
我们可以用INSERT命令进行行的添加，这样只能添加一行
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES (1070, 'Public Relations', 100, 1700);
输出为：已创建 1 行。
添加带空值的行
我们可以添加带空值的行
INSERT INTO departments (department_id,
department_name )
VALUES (1030, 'Purchasing');
我们查询对应的值，输出如下：
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------------------------------------ ---------- -----------
1030 Purchasing
我们也可以这样添加行
INSERT INTO departments
VALUES (1100, 'Finance', NULL, NULL);
效果与上文类似
添加日期相关的行
我们可以自动添加当前的日期
INSERT INTO employees (employee_id,
first_name, last_name,
email, phone_number,
hire_date, job_id, salary,
commission_pct, manager_id,
department_id)
VALUES (10113,
'Louis', 'Popp',
'LPOPPxfz', '515.124.4567',
SYSDATE, 'AC_ACCOUNT', 6900,
NULL, 205, 100);
我们也可以用TO_DATE函数构造日期
INSERT INTO employees
VALUES (10114,
'Den', 'Raphealy',
'DRAPHEALxfz', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT', 11000, NULL, 100, 30);
我们输出下构造出的数值
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------- ------------ -------------------- ---------- -------------- ---------- -------------
10114 Den Raphealy DRAPHEALxfz 515.127.4561
03-FEB-99 AC_ACCOUNT 11000 100 30
行的复制
我们可以用SELECT子句来复制行，再用INSERT插入
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
由于不存在表sales_reps，我们需要把这个表建出来
CREATE TABLE sales_reps
( id NUMBER(6)
, name VARCHAR2(25)
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, PRIMARY KEY (id)
) ;
然后再运行下面的代码，输出如下：
已创建 33 行。
我们可以select出来看下
ID NAME SALARY COMMISSION_PCT
---------- -------------------------------------------------- ---------- --------------
150 Tucker 10000 .3
151 Bernstein 9500 .25
152 Hall 9000 .25
153 Olsen 8000 .2
154 Cambrault 7500 .2
155 Tuvault 7000 .15
156 King 10000 .35
157 Sully 9500 .35
158 McEwen 9000 .35
159 Smith 8000 .3
160 Doran 7500 .3
161 Sewall 7000 .25
162 Vishney 10500 .25
163 Greene 9500 .15
164 Marvins 7200 .1
165 Lee 6800 .1
166 Ande 6400 .1
167 Banda 6200 .1
168 Ozer 11500 .25
169 Bloom 10000 .2
170 Fox 9600 .2
171 Smith 7400 .15
172 Bates 7300 .15
173 Kumar 6100 .1
174 Abel 11000 .3
175 Hutton 8800 .25
176 Taylor 8600 .2
177 Livingston 8400 .2
178 Grant 7000 .15
179 Johnson 6200 .1
202 Fay 6000
203 Mavris 6500
204 Baer 10000
已选择33行。
修改数值
我们可以用UPDATE命令对数值进行修改
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
我们的选定和输出如下:
已更新 1 行。
SQL> SELECT * FROM employees WHERE department_id = 113;
未选定行
SQL> SELECT * FROM employees WHERE department_id = 70;
EMPLOYEE_ID FIRST_NAME LAST_NAME
EMAIL PHONE_NUMBER
----------- ---------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------
HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- -------------------- ---------- -------------- ---------- -------------
113 Luis Popp
LPOPP 515.124.4567
07-12月-99 FI_ACCOUNT 6900 108 70
204 Hermann Baer
HBAER 515.123.8888
07-6月 -94 PR_REP 10000 101 70
注意：如果你不增加WHERE限制，所有的数据都会被修改
UPDATE copy_emp
SET department_id = 110;
同时改两行
将employee_id=114的人的job_id和salary都改为和205的一样
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;
完成修改后，我们看下效果
SQL> SELECT job_id,salary FROM employees WHERE employee_id IN (114,205);
JOB_ID SALARY
-------------------- ----------
AC_MGR 12000
AC_MGR 12000
用基于其他表的查询子句修改该表
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);
一些错误的排查
我们输入下面的修改信息时，会出现一些错误
UPDATE employees
SET department_id = 55
WHERE department_id = 110;
会有以下报错信息
UPDATE employees
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SYS.EMP_DEPT_FK) - 未找到父项关键字
这是因为，55并不是department_id的合法值，把55改为60其实就可以了
行的删除
和UPDATE语句很相似
DELETE FROM departments
WHERE department_name = 'Finance';
结果是有一行被删除了
基于其他表的数据删除
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');
默认语句
我们可以用DEFAULT来插入数据
INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
也可以更新数据
UPDATE departments
SET manager_id = DEFAULT WHERE department_id = 10;
表的合并
我们可以用MERGE命令来将两张表进行合并
注意：此处的“合并”，指的是将表A中的数据，以某种形式融入到表B中，但表A并不会消失。
此处有点难理解，我给个示例代码
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE
SET col1 = col1_val1,
col2 = col2_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
这里的含义是：向表table_name （别名alias1）中融入数据，融入的数据由（表table经过某个子查询产生，别名alias2）提供
join condition表示的是表合并的规则（比如说比较两者的id或者名字）
当比较条件相同是（WHEN MATCHED THEN），执行UPDATE SET下的一系列语句
当比较条件不同时（WHEN NOT NATCHED THEN），执行INSERT下的一系列语句。
下方提供一个例子
上图为表MERGE_SOURCE
上图为表MERGE_TARGET
建表和插入的命令如下：
CREATE TABLE MERGE_SOURCE(
id NUMBER(6) ,
name VARCHAR2(20),
age NUMBER(6),
PRIMARY KEY(id)
);
INSERT INTO MERGE_SOURCE VALUES (1,'Amy',23);
INSERT INTO MERGE_SOURCE VALUES (2,'Coco',26);
INSERT INTO MERGE_SOURCE VALUES (3,'Helen',30);
CREATE TABLE MERGE_TARGET(
id NUMBER(6),
name VARCHAR2(20),
age NUMBER(6),
PRIMARY KEY(id)
);
INSERT INTO MERGE_TARGET VALUES (1,'Amy',23);
INSERT INTO MERGE_TARGET VALUES (3,'Helen',30);
INSERT INTO MERGE_TARGET VALUES (4,'Emma',25);
INSERT INTO MERGE_TARGET VALUES (5,'Jane',21);
MERGE的命令如下：
MERGE into MERGE_TARGET TARGET
USING(SELECT * FROM MERGE_SOURCE) SOURCE
ON(TARGET.id=SOURCE.id)
WHEN MATCHED THEN
UPDATE
SET TARGET.NAME = SOURCE.NAME,
TARGET.AGE = SOURCE.AGE
WHEN NOT MATCHED THEN
INSERT VALUES (SOURCE.ID,SOURCE.NAME,SOURCE.AGE);
输出如下：
SQL> SELECT * FROM MERGE_TARGET;
ID NAME AGE
---------- ---------------------------------------- ----------
1 Amy 23
3 Helen 30
4 Emma 25
5 Jane 21
2 Coco 26
表的标记和回滚
我们可以给数据库标记一些保存点
做完一些修改后，如果我们觉得这些修改不妥，我们可以直接进行回滚。
拍摄保存点的命令如下：
SAVEPOINT update_done;
中间我们可以执行一系列的操作，回滚的命令如下：
ROLLBACK TO update_done;
其中update_done是一个保存点的名字，可以随意命名。
当然，我们可以拍一些不带名字的点，比如说COMMIT和ROLLBACK。
