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。