📄 example.txt
字号:
在进行多表查询的时候 为了防止笛卡儿集的发生 条件的个数至少应该是表的个数-1
在进行分组查询的时候 select和order by 后面 只能跟函数或者分组后的列
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott
SQL> select * from student;
ID NAME SEX AGE LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
1 andy 男 18 2
2 jacky 女 40 2
3 lucy 女 23 4
4 好 男 23 4
5 lucy 女 23 3
SQL> select * from student where id in (select lid from student);
ID NAME SEX AGE LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
2 jacky 女 40 2
3 lucy 女 23 4
4 好 男 23 4
SQL> select e.name from student e;
NAME
--------------------
andy
jacky
lucy
好
lucy
SQL> select s1.name from student s1,student s2 s1.id = s2.lid;
select s1.name from student s1,student s2 s1.id = s2.lid
ORA-00933: SQL 命令未正确结束
SQL> select s1.name from student s1,student s2 where s1.id = s2.lid;
NAME
--------------------
jacky
jacky
好
好
lucy
SQL> select distinct s1.name from student s1,student s2 where s1.id = s2.lid;
NAME
--------------------
jacky
lucy
好
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
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
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 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
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
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
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
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 30 SALES CHICAGO
7839 KING PRESIDENT 1981-11-17 5000.00 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 1981-12-3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 30 SALES CHICAGO
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 30 SALES CHICAGO
7369 SMITH CLERK 7902 1980-12-17 800.00 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 1981-4-2 2975.00 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 1981-11-17 5000.00 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 1981-12-3 950.00 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 1981-12-3 3000.00 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 1982-1-23 1300.00 10 40 OPERATIONS BOSTON
56 rows selected
SQL> select * from emp where deptno = (select deptno from dept where dname='SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
6 rows selected
SQL> select s1.name from student s1,student s2 where s1.id = s2.lid;
NAME
--------------------
jacky
jacky
好
好
lucy
SQL> select distinct s1.name from student s1,student s2 where s1.id = s2.lid;
NAME
--------------------
jacky
lucy
好
SQL> select s1.name from student s1,student s2 ;
NAME
--------------------
andy
andy
andy
andy
andy
jacky
jacky
jacky
jacky
jacky
lucy
lucy
lucy
lucy
lucy
好
好
好
好
好
NAME
--------------------
lucy
lucy
lucy
lucy
lucy
25 rows selected
SQL> select distinct s1.name from student s1,(select * from student) s2 where s1.id = s2.lid;
NAME
--------------------
jacky
lucy
好
SQL> select distinct s1.name from student s1,(select lid from student) s2 where s1.id = s2.lid;
NAME
--------------------
jacky
lucy
好
SQL> select distinct s1.name from student s1,(select lid lingdao from student) s2 where s1.id = s2.lid;
select distinct s1.name from student s1,(select lid lingdao from student) s2 where s1.id = s2.lid
ORA-00904: "S2"."LID": 标识符无效
SQL> select distinct s1.name from student s1,(select lid lingdao from student) s2 where s1.id = s2.lingdao;
NAME
--------------------
jacky
lucy
好
SQL> select distinct s1.name from student s1,(select lid as lingdao from student) s2 where s1.id = s2.lingdao;
NAME
--------------------
jacky
lucy
好
SQL> select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.lingdao;
select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.lingdao
ORA-00933: SQL 命令未正确结束
SQL> select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.ld;
select distinct s1.name from student s1,(select lid as lingdao from student) s2(ld) where s1.id = s2.ld
ORA-00933: SQL 命令未正确结束
SQL> select distinct s1.name from student s1,(select lid from student) s2(ld) where s1.id = s2.ld;
select distinct s1.name from student s1,(select lid from student) s2(ld) where s1.id = s2.ld
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -