首页 > 代码库 > SQL_由创建表引出
SQL_由创建表引出
***********************************************声明***********************************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。
深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/39933069
****************************************************************************************************************************
SQL_由创建表引出
目标：
1、hyl用户下创建表cool；
2、将scott用户下emp表数据抽取到hyl用户下的cool表内；
3、更改cool表内数据，把scott的emp按hyl用户下的cool表进行更改。
操作：
SQL> create table cool ("1" number(4),"2" varchar2(10),"3" varchar2(9),"4" number(4),"5" date,"6" number(7,2),"7" number(7,2),"8" number(2));
Table created
--创建表
SQL> select * from cool;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
SQL> desc cool
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
1 NUMBER(4) Y
2 VARCHAR2(10) Y
3 VARCHAR2(9) Y
4 NUMBER(4) Y
5 DATE Y
6 NUMBER(7,2) Y
7 NUMBER(7,2) Y
8 NUMBER(2) Y
使用查询语句，计划抽取的数据，如下：
SQL> select t.empno as "1",t.ename as "2",t.job as "3",t.mgr as "4",t.hiredate as "5",t.sal as "6",t.comm as "7",t.deptno as "8" from scott.emp t;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> insert into cool("1","2","3","4","5","6","7","8") select t.empno as "1",t.ename as "2",t.job as "3",t.mgr as "4",t.hiredate as "5",t.sal as "6",t.comm as "7",t.deptno as "8" from scott.emp t;
--将数据按序号字段，由emp表抽取到cool表内
14 rows inserted
SQL> commit;
Commit complete
SQL> select * from cool;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
小结：
A表数据抽取到B表
SQL>insert into select B表（”B表列1”,”B表列2”,”B表列3”） select t.”A表列1” as “B表列1”，”t.A表列2” as “B表列2”，”t.A表列3” as “B表列3” fromA表 t;
--hyl用户下，如下所示：
SQL> update cool set "3"=‘DBA‘ where "8"=30;
6 rows updated
SQL> commit;
Commit complete
SQL> select * from cool;
1 2 3 4 5 6 7 8
----- ---------- --------- ----- ----------- --------- --------- ---
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN DBA 7698 1981/2/20 1600.00 300.00 30
7521 WARD DBA 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN DBA 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE DBA 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER DBA 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES DBA 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
--scott用户下，执行操作，如下：
SQL> update emp t set (job)=(select "3" from hyl.cool p where t.empno=p."1");
--对hyl的cool表进行更新
14 rows updated
补充：通过另外一个用户的表向本用户下表插入数据时，应具有访问该表的权限
如该例，需使用sys用户向scott用户赋予查询hyl表的权限
SQL> grant select on hyl.cool to scott;
Grant succeeded.
补充完毕
SQL> select * from emp;
--可以看到，此时scott用户下的emp表数据job列已经更新为同hyl用户下的cool表相同了
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN DBA 7698 1981/2/20 1600.00 300.00 30
7521 WARD DBA 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN DBA 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE DBA 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER DBA 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES DBA 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> commit;
Commit complete
小结：
把一个A表的某列更改为另一个B表的列值，使用某一列进行关联。
SQL> update A表 t set(列)=（select 列 from B表 p where t.列=p.列）；
例：SQL> update emp t set (job)=(select "3" from hyl.cool p where t.empno=p."1");
***********************************************声明***********************************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。
深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/39933069
****************************************************************************************************************************
SQL_由创建表引出