📄 example.txt
字号:
56
SQL> select * from emp
2 ;
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 where empno = 7269;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL> select * from emp where empno = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
SQL> select * from emp where empno != 7369;
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
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
13 rows selected
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept values(50,'aaaaaaaa','beijing');
1 row inserted
SQL> select deptno from dept union select deptno from emp;
DEPTNO
----------
10
20
30
40
50
SQL> select deptno from dept union all select deptno from emp;
DEPTNO
----------
10
20
30
40
50
20
30
30
20
30
30
10
20
10
30
20
30
20
10
19 rows selected
SQL> select deptno from dept intersect select deptno from emp;
DEPTNO
----------
10
20
30
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 deptno from dept minus select deptno from emp;
DEPTNO
----------
40
50
SQL> select * from emp where empno in (select empno from emp where dep)
2 ;
select * from emp where empno in (select empno from emp where dep)
ORA-00920: 无效的关系运算符
SQL> select * from emp where empno in (select empno from emp where deptno = (select deptno from dept where dname='SALES')
2 ;
select * from emp where empno in (select empno from emp where deptno = (select deptno from dept where dname='SALES')
ORA-00907: 缺失右括号
SQL> select * from emp where empno in (select empno 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> create table employee
2 select * from emp;
create table employee
select * from emp
ORA-00922: 选项缺失或无效
SQL> create table employee as select * from emp;\
Table created
SQL> select * from employee;
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> update empleyee set ename = 'aaa' where empno =(select empno from employee where empno=7369);
update empleyee set ename = 'aaa' where empno =(select empno from employee where empno=7369)
ORA-00942: 表或视图不存在
SQL> update employee set ename = 'aaa' where empno =(select empno from employee where empno=7369);
1 row updated
SQL> select * from dept where exits(select deptno from emp,dept where emp.deptno = dept.deptno);
select * from dept where exits(select deptno from emp,dept where emp.deptno = dept.deptno)
ORA-00936: 缺失表达式
SQL> select * from dept where exits(select * from emp,dept where emp.deptno = dept.deptno);
select * from dept where exits(select * from emp,dept where emp.deptno = dept.deptno)
ORA-00936: 缺失表达式
SQL> select * from dept where exits(select * from emp where deptno = 30);
select * from dept where exits(select * from emp where deptno = 30)
ORA-00936: 缺失表达式
SQL> select * from dept where exits(select * from emp where deptno = 3);
select * from dept where exits(select * from emp where deptno = 3)
ORA-00936: 缺失表达式
SQL> select * from dept where exits(select * from emp where emp.deptno = dept.deptno);
select * from dept where exits(select * from emp where emp.deptno = dept.deptno)
ORA-00936: 缺失表达式
SQL> select * from dept where deptno in (select deptno from emp);
DEPTNO DNAME LOC
------ -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
10 ACCOUNTING NEW YORK
SQL> select * from dept where exist(select * from emp where emp.deptno = dept.deptno);
select * from dept where exist(select * from emp where emp.deptno = dept.deptno)
ORA-00936: 缺失表达式
SQL> select * from dept where EXISTS(select * from emp where emp.deptno = dept.deptno);
DEPTNO DNAME LOC
------ -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
10 ACCOUNTING NEW YORK
SQL> select * from dept where not EXISTS(select * from emp where emp.deptno = dept.deptno);
DEPTNO DNAME LOC
------ -------------- -------------
50 aaaaaaaa beijing
40 OPERATIONS BOSTON
SQL>
创建一个员工表（里面包含 员工编号，员工姓名，性别，年龄，薪水，领导编号）
主键 员工编号
做查询
查询下年龄大于30岁的男领导下的年龄在20岁到25岁之间的薪水小于2000的性别为女的员工的数量⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -