605.txt
来自「关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品」· 文本 代码 · 共 743 行 · 第 1/5 页
TXT
743 行
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 rows selected
SQL> select ename,(select job as j,sal as s from myemp where myemp.empno = e1.empno) from emp e1 ;
select ename,(select job as j,sal as s from myemp where myemp.empno = e1.empno) from emp e1
ORA-00913: 值过多
SQL> select deptno,(select job as j,sal as s from myemp where myemp.deptno = e1.deptno) from emp e1 group by deptno;
select deptno,(select job as j,sal as s from myemp where myemp.deptno = e1.deptno) from emp e1 group by deptno
ORA-00913: 值过多
SQL> create table myemp1 as select * from emp where 5=6;
Table created
SQL> select * from myemp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL> insert into myemp1 select * from emp;
14 rows inserted
SQL> select * from myemp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
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> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
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> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
50 aaaaaaaa beijing
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
70 pingxin china
6 rows selected
SQL> select * from emp,dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980-12-17 800.00 20 50 aaaaaaaa beijing
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 50 aaaaaaaa beijing
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 50 aaaaaaaa beijing
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 50 aaaaaaaa beijing
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 50 aaaaaaaa beijing
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 50 aaaaaaaa beijing
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 50 aaaaaaaa beijing
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 50 aaaaaaaa beijing
7839 KING PRESIDENT 1981-11-17 5000.00 10 50 aaaaaaaa beijing
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 50 aaaaaaaa beijing
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 50 aaaaaaaa beijing
7900 JAMES CLERK 7698 1981-12-3 950.00 30 50 aaaaaaaa beijing
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 50 aaaaaaaa beijing
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 50 aaaaaaaa beijing
7369 SMITH CLERK 7902 1980-12-17 800.00 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 10 ACCOUNTING NEW YORK
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981-11-17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 1981-12-3 950.00 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 1980-12-17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 5000.00 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-3 950.00 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 20 RESEARCH DALLAS
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980-12-17 800.00 20 30 SALES CHICAGO
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?