Oracle学习笔记-SELECT详解，数据库基本操作

此笔记为韩顺平老师9i的视频随堂笔记，虽然是9i，但重点在表查询上，基本的操作和目前12c很少有差别，操作表为scott用户自带emp/dept表。
12c conn / as sysdba 默认链接到cdb也就是CDB$ROOT
在CDB内不允许创建普通用户，具体报错如下
ORA-65096: invalid common user or role name
只可以创建 c##开头的用户，并且用户会在所有pdb递归创建
12c 已经将默认的scott用户删除，但其自带一个sql，路径如下：
$ORACLE_HOME/rdbms/admin/utlsampl.sql
需要alter session set container=[orclpdb]切换到容器
执行
create user scott identified by tiger; 创建用户
grant connect,resource to scott; 可连接，可建表
再执行@$ORACLE_HOME/rdbms/admin/utlsampl.sql
tips：脚本内CONNECT SCOTT/tiger这一句需要更改
因为oraclpdb不再是实例，而是实例下container的一个pluggable database
所改为： connect scott/tiger@localhost/orclpdb再执行

#查看监听的服务
lsnrctl service
#查看监听
lsnrctl status

#运行sql脚本
sql>@script.sql

#提示用户输入变量

select * from dba_users where username='&username';

#登陆
sqlplus username/password@host/instance as role

例子
sqlplus scott/tiger@localhost/orclpdb

sqlplus system/manager@localhost/orclpdb as sysdba


  <logon> is: {<username>[/<password>][@<connect_identifier>] | / }
              [AS {SYSDBA | SYSOPER | SYSASM | SYSBACKUP | SYSDG | SYSKM | SYSRAC}] [EDITION=value]
              


#启动数据库
startup
 where db_options has the following syntax:
    [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
    [ OPEN [open_db_options] [dbname] ] |
    NOMOUNT ]
    
#显示命令执行时间
set timing on;

#设置行宽度
set linesize 100
#设置页大小
set pagesize 50
#设置列明为con_id的宽度为10个字符串
col con_id format 09.99 #格式为数字，0表示如果没有此位，以0代替，9表示可以有，也可以没有此位，如果有，就显示，没有则不显示
col name format a10
col open_mode format a10
col restricted format a10

#查看当前的容器(12c)
show con_name;


#查看可插拔数据库(PDB)
select CON_ID, NAME, OPEN_MODE, RESTRICTED FROM V$PDBS;
#输出结果
CON_ID NAME       OPEN_MODE  RESTRICTED
------ ---------- ---------- ----------
 03.00 ORCLPDB    READ WRITE NO

#切换会话到容器(pdb)中
alter session set container=ORCLPDB ; 

#查看CDB
select name, cdb from v$database ;

NAME      CDB
--------- ---
ORCL      YES

#关闭一个可插拔数据库
ALTER PLUGGABLE DATABASE ORCLPDB CLOSE [IMMEDIATE];

#打开一个可插拔数据库
ALTER PLUGGABLE DATABASE ORCLPDB OPEN [READ/WRITE/READ ONLY/UPGRADE]; 





#查看当前用户
show user;


#创建用户
create user [username] identified by [password]
#删除用户
drop user [username] [cascade]

#授权
grant [role/权限] to [username]

#例子，为用户授权可以连接数据库
grant connect to scott

#更新用户密码
alter user [username] identified by [password];


#查看用户所拥有的角色
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='SCOTT';




#给另一个用户赋权可以访问当前用户的表
grant select,instrt,update,delete on [table] to [username] [with grant option]
                                                            这里为被赋权用户可以继续赋权给其他用户
#例子
grant all on emp to xiaoming;

#回收权限
rovake select,instrt,update,delete on [table] from [username]



#另一个用户访问被其他用户赋权的表
select * from scott.emp;

#创建配置文件
create profile [ProfileName] limit [Args];
#三次输入错误锁定，锁定两天
create profile testprofile1 limit failed_login_attempts 3 password_lock_time 2;
#要求用户10天之内修改密码， 宽限期为2天，10天以后可以重用老密码。
create profile changepassword limit password_life_time 10 password_grace_time 2 password_reuse_time 10;
#查看配置文件
select * from dba_profiles where profile='CHANGEPASSWORD';
#查看用户配置文件
select username,profile from dba_users where username='XIAOMING2';

#删除配置文件 cascade 把相关的东西都删除掉
drop profile [ProfileName] [cascade]

#为用户应用配置文件
alter user xiaoming profile testprofile1 ; 

#用户解锁、锁定
alter user [xiaoming] account [unlock lock]
 

#查看配置文件
select * from dba_profiles;


#建表
create table student(xh number(4), xm varchar2(20), sex char(2), birthday date, sal number(7,2));
#number(7,2) 范围 -99999.99 ~ 99999.99
#添加列
alter table student add (classId number(20));
#修改字段长度
alter table student modify (xm varchar2(40));
#修改字段类型/名（不能有数据）
alter table student modify (xm char(30));
alter table student rename column xm to xm1; 
#删除列
alter table student drop column xm ; 
#修改表名
rename student to stu ; 
#删除表
drop table student;




#增删改查###########################
#操作的相关表，先看一下表结构
SQL> select * from student ; 

        XH XM                                       SEX       BIRTHDAY          SAL    CLASSID
---------- ---------------------------------------- --------- ---------- ---------- ----------
         2 小明                                     男        1990-01-01
         1 小红                                     女        1990-01-01
         

SQL> desc emp;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 MGR                                                            NUMBER(4)
 HIREDATE                                                       DATE
 SAL                                                            NUMBER(7,2)
 COMM                                                           NUMBER(7,2)
 DEPTNO                                                         NUMBER(2)
 
SQL> select * from emp ;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20
      7839 KING       PRESIDENT            1981-11-17       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03        950                    30
      7902 FORD       ANALYST         7566 1981-12-03       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23       1300                    10

14 rows selected.

SQL> desc dept ;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                NOT NULL NUMBER(2)
 DNAME                                                          VARCHAR2(14)
 LOC                                                            VARCHAR2(13)
 
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

        

SQL> desc salgrade ;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 GRADE                                                          NUMBER
 LOSAL                                                          NUMBER
 HISAL                                                          NUMBER
 
SQL> select * from salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

Elapsed: 00:00:00.01


 

#增
insert into student values(1,'xm','b','08-SEP-81',1500);
insert into student(xm,xh,sex) values('小明',2,'男') ;
#删
delete student where xh=1;
#改
UPDATE SCOTT.EMP SET JOB='ENG',MGR=1000  WHERE ENAME='TEA';
update student set sal=sal/2 where sex='男';
update student set classid=1 where classid is null ;
#先查后改
#把工资低于平均工资，并且入职日期在1982年前的老员工的工资提升百分之10
update emp set sal=sal*1.1 where empno in (select empno from emp where sal<(select avg(sal) from emp) and HIREDATE>to_timestamp('1982-1-1','yyyy-mm-dd'));

############查
select * from student ;
select * from student where sal is null;
select * from student where sal is not null ;
select distinct job from emp ; # 不显示重复的数据

#计算每个人的年工资，nvl函数类似python getattr(),如果取到的值为null，则设置为0
select ename,sal*13+nvl(comm,0)*13 年工资 from emp order by 年工资 desc; 

#查询大于3000的工资的人，按工资从多到少排序
select * from emp where sal >3000 order by sal desc ;

#查询1982-1-1之后入职的员工
select ename,hiredate from emp where hiredate>to_timestamp('1982-1-1','yyyy-mm-dd');

#查询1982-1-1之后入职的员工,工资在2000-3500之间的员工
select ename,hiredate from emp where hiredate>to_timestamp('1982-1-1','yyyy-mm-dd') and sal>=2000 and sal<=3500;



#TIPS
#like操作符
%表示0到多个字符
_表示单个字符


#显示名字内有TT的员工
select ename,sal from emp where ename like '%TT';
#显示名字为S开头的员工
select ename,sal from emp where ename like 'S%';
#显示名字内第三个字母为O的员工
select ename,sal from emp where ename like '__O%';

#where in()查询  查询in()括号内的数据，类似for i in
#查询员工号为7369,7499,7521的员工
select * from emp where EMPNO in (7369,7499,7521);


#查询MGR为NULL的员工
select * from emp where mgr is null;

#查询工资大于500或者工作为manager并且ename首字母为J的员工，并按照工资排序
select * from emp where (sal>500 or JOB='MANAGER') and ename like 'J%' order by sal asc;

#按照部门升序，每一个部门按员工降序
select deptno,ename,sal*12+nvl(comm,0) "年薪" from emp order by deptno asc,sal desc;

#按照年薪，使用别名排序
select deptno,ename,sal*12+nvl(comm,0) "年薪" from emp order by "年薪" desc;

#查询最高工资，最低工资的数值
select max(sal) "max", min(sal) "min" from emp;

#查询工资最高的人是谁，工资是多少
select ename,sal from emp where sal=(select max(sal) from emp);

#查询工资高于平均工资的员工的信息
select * from emp where sal>(select avg(sal) from emp ) ; 

#把工资低于平均工资，并且入职日期在1982年前的老员工的工资提升百分之10
update emp set sal=sal*1.1 where empno in (select empno from emp where sal<(select avg(sal) from emp) and HIREDATE>to_timestamp('1982-1-1','yyyy-mm-dd'));

#group by 用于分组统计
#having 用户限制分组显示结果

#按照部门求平均工资，最低工资
select avg(sal) "平均工资" ,min(sal) "最低工资" ,deptno "部门编号" from emp group by deptno;

  平均工资   最低工资   部门编号
---------- ---------- ----------
1566.66667        950         30
      2175        800         20
2916.66667       1300         10


#求每个部门的每种职位的平均工资，按照部门编号和平均工资升序
select deptno,job,avg(sal) "pjgz" ,min(sal) from emp group by deptno,job order by deptno,"pjgz";

#显示平均工资低于2000的部门号和他的平均工资
col 平均工资 format 999999.99
select deptno, avg(sal) "平均工资" from emp group by deptno having avg(sal) > 2000 order by deptno;
    DEPTNO   平均工资
---------- ----------
        20    2175.00
        10    2916.67


#TIPS
#分组函数只能出现在选择列表、having、order by子语句
#如果在一个select语句中同时包含group by, having, order by，那么顺序必须是 group by --> having  --> order by

#多表查询
#查询员工的姓名，工资，部门，城市（部门，城市为其他表）

SELECT EMP.ENAME, EMP.SAL, DEPT.DNAME, DEPT.LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT A1.ENAME, A1.SAL, A2.DNAME, A2.LOC FROM EMP A1, DEPT A2 WHERE A1.DEPTNO=A2.DEPTNO;


ENAME             SAL DNAME          LOC
---------- ---------- -------------- -------------
CLARK            2450 ACCOUNTING     NEW YORK
KING             5000 ACCOUNTING     NEW YORK
MILLER           1300 ACCOUNTING     NEW YORK
JONES            2975 RESEARCH       DALLAS
FORD             3000 RESEARCH       DALLAS
ADAMS            1100 RESEARCH       DALLAS
SMITH             800 RESEARCH       DALLAS
SCOTT            3000 RESEARCH       DALLAS
WARD             1250 SALES          CHICAGO
TURNER           1500 SALES          CHICAGO
ALLEN            1600 SALES          CHICAGO
JAMES             950 SALES          CHICAGO
BLAKE            2850 SALES          CHICAGO
MARTIN           1250 SALES          CHICAGO


#查询部门平均工资，部门名称
col "平均工资" format 99999999.99
select avg(a1.sal) as "平均工资", a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno group by a2.dname order by "平均工资";

  平均工资 DNAME
---------- --------------
   1566.67 SALES
   2175.00 RESEARCH
   2916.67 ACCOUNTING

#多表查询详解（笛卡尔集）
SQL> select * from emp where ename='MARTIN';

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30

SQL> 
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 where ename='MARTIN';

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- -------------
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         10 ACCOUNTING     NEW YORK
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         20 RESEARCH       DALLAS
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         30 SALES          CHICAGO
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         40 OPERATIONS     BOSTON

#笛卡尔集筛选（排除），笛卡尔集是相当危险的，会导致select结果成幂增长，如果有n个表，至少需要n-1个条件才能排除笛卡尔集
SQL> select * from emp,dept where emp.deptno=dept.deptno;

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     DEPTNO DNAME          LOC
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ---------- -------------- -------------
      7782 CLARK      MANAGER         7839 09-JUN-81                2450                    10         10 ACCOUNTING     NEW YORK
      7839 KING       PRESIDENT            17-NOV-81                5000                    10         10 ACCOUNTING     NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82                1300                    10         10 ACCOUNTING     NEW YORK
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20         20 RESEARCH       DALLAS
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20         20 RESEARCH       DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87                1100                    20         20 RESEARCH       DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80                 800                    20         20 RESEARCH       DALLAS
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20         20 RESEARCH       DALLAS
      7521 WARD       SALESMAN        7698 22-FEB-81                1250        500         30         30 SALES          CHICAGO
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500          0         30         30 SALES          CHICAGO
      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600        300         30         30 SALES          CHICAGO
      7900 JAMES      CLERK           7698 03-DEC-81                 950                    30         30 SALES          CHICAGO
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30         30 SALES          CHICAGO
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30         30 SALES          CHICAGO


#查询比30号部门最高工资高的员工
select * from emp where sal>(select max(sal) from emp where deptno=30);

#查询10号部门员工的部门名称，员工名称，工资（多表查询多条件）
select dept.dname, emp.ename, emp.sal from dept,emp where dept.deptno=emp.deptno and emp.deptno=10;

#查询员工姓名，工资，工资级别（between）
select a1.ename, a1.sal, a2.grade from emp a1, salgrade a2 where a1.sal between a2.losal and a2.hisal;
select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4
SCOTT            3000          4
FORD             3000          4
KING             5000          5


#显示雇员名，雇员工资及部门名字，并按照部门排序
select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a2.dname;
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno order by dept.dname; 
ENAME             SAL DNAME
---------- ---------- --------------
MILLER           1300 ACCOUNTING
KING             5000 ACCOUNTING
CLARK            2450 ACCOUNTING
ADAMS            1100 RESEARCH
FORD             3000 RESEARCH
JONES            2975 RESEARCH
SMITH             800 RESEARCH
SCOTT            3000 RESEARCH
WARD             1250 SALES
TURNER           1500 SALES
ALLEN            1600 SALES
JAMES             950 SALES
BLAKE            2850 SALES
MARTIN           1250 SALES


#显示雇员名，雇员工资及部门名字，工资等级，并按照部门排序（三表查询）

select a1.ename, a1.sal, a2.dname, a3.grade from emp a1, dept a2, salgrade a3 where a1.deptno=a2.deptno and a1.sal between a3.losal and a3.hisal order by a2.dname;

select emp.ename, emp.sal, dept.dname, salgrade.grade from emp, dept, salgrade where emp.deptno=dept.deptno and emp.sal between salgrade.losal and salgrade.hisal order by dept.dname; 

#Fromat:
SELECT emp.ename, emp.sal, dept.dname, salgrade.grade
FROM emp, dept, salgrade
WHERE emp.deptno = dept.deptno
    AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal
ORDER BY dept.dname;

ENAME             SAL DNAME               GRADE
---------- ---------- -------------- ----------
KING             5000 ACCOUNTING              5
MILLER           1300 ACCOUNTING              2
CLARK            2450 ACCOUNTING              4
ADAMS            1100 RESEARCH                1
JONES            2975 RESEARCH                4
SCOTT            3000 RESEARCH                4
SMITH             800 RESEARCH                1
FORD             3000 RESEARCH                4
JAMES             950 SALES                   1
MARTIN           1250 SALES                   2
WARD             1250 SALES                   2
TURNER           1500 SALES                   3
BLAKE            2850 SALES                   4
ALLEN            1600 SALES                   3


#不显示的字段可以用于排序
select a1.ename, a2.dname, a1.sal from emp a1, dept a2 where a1.deptno=a2.deptno order by a1.deptno ;

ENAME      DNAME                 SAL
---------- -------------- ----------
CLARK      ACCOUNTING           2450
KING       ACCOUNTING           5000
MILLER     ACCOUNTING           1300
JONES      RESEARCH             2975
FORD       RESEARCH             3000
ADAMS      RESEARCH             1100
SMITH      RESEARCH              800
SCOTT      RESEARCH             3000
WARD       SALES                1250
TURNER     SALES                1500
ALLEN      SALES                1600
JAMES      SALES                 950
BLAKE      SALES                2850
MARTIN     SALES                1250


#显示员工的领导
select a1.ename, a2.ename from emp a1, emp a2 where a1.mgr=a2.empno order by a2.ename;

ENAME      ENAME
---------- ----------
MARTIN     BLAKE
JAMES      BLAKE
WARD       BLAKE
ALLEN      BLAKE
TURNER     BLAKE
MILLER     CLARK
SMITH      FORD
SCOTT      JONES
FORD       JONES
CLARK      KING
JONES      KING
BLAKE      KING
ADAMS      SCOTT

#显示FORD的信息并且把mgr字段换为老板姓名

SELECT A1.EMPNO,A1.ENAME,A1.JOB,A2.ENAME AS "老板名字",A1.HIREDATE,A1.SAL,A1.COMM,A1.DEPTNO  FROM EMP A1, EMP A2 WHERE A1.MGR=A2.EMPNO AND A1.ENAME='FORD';

#Format:
SELECT A1.EMPNO, A1.ENAME, A1.JOB, A2.ENAME AS "老板名字", A1.HIREDATE
    , A1.SAL, A1.COMM, A1.DEPTNO
FROM EMP A1, EMP A2
WHERE A1.MGR = A2.EMPNO
    AND A1.ENAME = 'FORD';
    
     EMPNO ENAME      JOB       老板名字   HIREDATE                  SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
      7902 FORD       ANALYST   JONES      03-DEC-81                3000                    20


##子查询
#单行子查询
#如何显示和SMITH同一部门的所有员工
SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800                    20
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87                1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20


#多行子查询
#如何查询和部门20号部门的工作相同的雇员的名字，岗位，工资和部门号
SELECT * FROM EMP WHERE JOB IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=20);

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800                    20
      7876 ADAMS      CLERK           7788 23-MAY-87                1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81                 950                    30
      7934 MILLER     CLERK           7782 23-JAN-82                1300                    10
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81                2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20

      
      

      
#显示工资比部门30的*所有*员工高的员工的姓名，工资和部门号

#TIPS: ALL 关键字， 100 > ALL(10,30,50,200,400,500) --> FALSE
#                   300 > ALL(10,30,50,200,400,500) --> FALSE
#                   600 > ALL(10,30,50,200,400,500) --> TRUE


SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO=30) ORDER BY ENAME;

ENAME             SAL     DEPTNO
---------- ---------- ----------
FORD             3000         20
JONES            2975         20
KING             5000         10
SCOTT            3000         20

#Also: 这种写法个人觉得更好一些，可能效率更高一些
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30) ORDER BY ENAME ;

ENAME             SAL     DEPTNO
---------- ---------- ----------
FORD             3000         20
JONES            2975         20
KING             5000         10
SCOTT            3000         20

#显示工资比部门30的所有员工高的员工的姓名，工资和部门名称

SELECT A1.ENAME, A1.SAL, A2.DNAME FROM EMP A1, DEPT A2 WHERE A1.DEPTNO = A2.DEPTNO AND A1.SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30) ORDER BY A1.ENAME; 

#Format:
SELECT A1.ENAME, A1.SAL, A2.DNAME
FROM EMP A1, DEPT A2
WHERE A1.DEPTNO = A2.DEPTNO
    AND A1.SAL > (
        SELECT MAX(SAL)
        FROM EMP
        WHERE DEPTNO = 30
    )
ORDER BY A1.ENAME;


ENAME             SAL DNAME
---------- ---------- --------------
FORD             3000 RESEARCH
JONES            2975 RESEARCH
KING             5000 ACCOUNTING
SCOTT            3000 RESEARCH

#这两种写法哪种更好呢？个人觉得上一种更清晰。
SELECT A1.ENAME, A1.SAL, A2.DNAME FROM EMP A1, DEPT A2 WHERE A1.SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30) AND A1.DEPTNO=A2.DEPTNO ORDER BY A1.ENAME; 

#Format:
SELECT A1.ENAME, A1.SAL, A2.DNAME
FROM EMP A1, DEPT A2
WHERE A1.SAL > (
        SELECT MAX(SAL)
        FROM EMP
        WHERE DEPTNO = 30
    )
    AND A1.DEPTNO = A2.DEPTNO
ORDER BY A1.ENAME;

ENAME             SAL DNAME
---------- ---------- --------------
FORD             3000 RESEARCH
JONES            2975 RESEARCH
KING             5000 ACCOUNTING
SCOTT            3000 RESEARCH


#显示工资比部门30的*任意*员工高的员工的姓名，工资和部门号

#TIPS: ANY 关键字， 100 > ANY(10,30,50,200,400,500) --> TRUE
#                   300 > ANY(10,30,50,200,400,500) --> TRUE
#                   600 > ANY(10,30,50,200,400,500) --> TRUE
#                   5   > ANY(10,30,50,200,400,500) --> FALSE

SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO=30) ORDER BY ENAME;

ENAME             SAL     DEPTNO
---------- ---------- ----------
ADAMS            1100         20
ALLEN            1600         30
BLAKE            2850         30
CLARK            2450         10
FORD             3000         20
JONES            2975         20
KING             5000         10
MARTIN           1250         30
MILLER           1300         10
SCOTT            3000         20
TURNER           1500         30
WARD             1250         30

#Also 效率高
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30) ORDER BY ENAME;

ENAME             SAL     DEPTNO
---------- ---------- ----------
ADAMS            1100         20
ALLEN            1600         30
BLAKE            2850         30
CLARK            2450         10
FORD             3000         20
JONES            2975         20
KING             5000         10
MARTIN           1250         30
MILLER           1300         10
SCOTT            3000         20
TURNER           1500         30
WARD             1250         30


#注解

SQL> SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > any(1000000,20) ORDER BY ENAME;

ENAME             SAL     DEPTNO
---------- ---------- ----------
ADAMS            1100         20
ALLEN            1600         30
BLAKE            2850         30
CLARK            2450         10
FORD             3000         20
JAMES             950         30
JONES            2975         20
KING             5000         10
MARTIN           1250         30
MILLER           1300         10
SCOTT            3000         20
SMITH             800         20
TURNER           1500         30
WARD             1250         30

14 rows selected.

Elapsed: 00:00:00.01
SQL> SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > all (1000000,20) ORDER BY ENAME;

no rows selected

Elapsed: 00:00:00.01


#复杂子查询(WHERE 多列值相同)
#查询与SMITH的部门和岗位完全相同的所有雇员
SELECT * FROM  EMP WHERE (DEPTNO, JOB) = ( SELECT DEPTNO, JOB FROM EMP WHERE ENAME = 'SMITH'); 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17        800                    20
      7876 ADAMS      CLERK           7788 1987-05-23       1100                    20
      
    

###重点   
###如何显示高于(自己部门平均工资)的员工信息(SMITH)
#TIP:
#查询出各个部门的平均工资和部门号

SELECT DEPTNO, AVG(SAL) MYSAL FROM EMP GROUP BY DEPTNO ; 

    DEPTNO      MYSAL
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667
        
#可以把上面的查询看做成一张子表，然后把两张表拼接在一起
#tip: 这里输出14个rows，使用了ROWNUM < 4 限制了输出条数
SELECT * FROM EMP A1, (SELECT DEPTNO, AVG(SAL) MYSAL FROM EMP GROUP BY DEPTNO) A2 WHERE A1.DEPTNO = A2.DEPTNO AND ROWNUM < 4;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO     DEPTNO      MYSAL
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30         30 1566.66667
      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30         30 1566.66667
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30         30 1566.66667

      
#最后在WHERE一下 A1.SAL > A2.MYSAL就ok了
SELECT A1.* FROM EMP A1, (SELECT DEPTNO, AVG(SAL) MYSAL FROM EMP GROUP BY DEPTNO) A2 WHERE A1.DEPTNO = A2.DEPTNO AND A1.SAL > A2.MYSAL;

#Format:
SELECT A1.*
FROM EMP A1, (
    SELECT DEPTNO, AVG(SAL) AS MYSAL
    FROM EMP
    GROUP BY DEPTNO
) A2
WHERE A1.DEPTNO = A2.DEPTNO
    AND A1.SAL > A2.MYSAL;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO     DEPTNO      MYSAL
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30         30 1566.66667
      7566 JONES      MANAGER         7839 1981-04-02       2975                    20         20       2175
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30         30 1566.66667
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20         20       2175
      7839 KING       PRESIDENT            1981-11-17       5000                    10         10 2916.66667
      7902 FORD       ANALYST         7566 1981-12-03       3000                    20         20       2175

#其他写法
SELECT A1.*, A2.AVGSAL
FROM EMP A1
    LEFT JOIN (
        SELECT DEPTNO, AVG(SAL) AS AVGSAL
        FROM EMP
        GROUP BY DEPTNO
    ) A2 ON A1.DEPTNO = A2.DEPTNO 
WHERE A1.SAL > A2.AVGSAL;

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     AVGSAL
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600        300         30 1566.66667
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20       2175
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30 1566.66667
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20       2175
      7839 KING       PRESIDENT            17-NOV-81                5000                    10 2916.66667
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20       2175

6 rows selected.

#总结
#在from中使用子查询
#当在from子句中使用自查询时，该子查询会被作为一个视图来对待，因此叫作内嵌视图，
#当在from子句中使用查询时，必须给子查询指定别名。


##分页1 rownum
#TIP: ROWNUM是oracle的一个关键字，代表行号
#特别注意，ROWNUM只能使用一次，第二次使用将导致no rows selected
SELECT A1.*, ROWNUM FROM EMP A1 WHERE ROWNUM < 5 ; 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO     ROWNUM
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17        800                    20          1
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30          2
      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30          3
      7566 JONES      MANAGER         7839 1981-04-02       2975                    20          4


#特别注意，ROWNUM只能使用一次，第二次使用将导致no rows selected
SELECT A1.*, ROWNUM FROM EMP A1 WHERE ROWNUM BETWEEN 5 AND 10 ; 

no rows selected

      
#二分查法1
SQL> SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM EMP A1 WHERE ROWNUM < 10) WHERE RN > 5;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30          6
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10          7
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20          8
      7839 KING       PRESIDENT            1981-11-17       5000                    10          9

#二分查法2，生成子视图
SQL> SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM (SELECT * FROM EMP) A1 WHERE ROWNUM < 10 ) WHERE RN > 5 ; 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30          6
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10          7
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20          8
      7839 KING       PRESIDENT            1981-11-17       5000                    10          9


      
####练习
#不知道为什么韩顺平老师讲的要二分查，但我个人认为这种查询方式比较好，可能二分查效率要高点？

SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM EMP A1) WHERE RN BETWEEN 5 AND 10 ; 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30          5
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30          6
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10          7
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20          8
      7839 KING       PRESIDENT            1981-11-17       5000                    10          9
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30         10

#TIP 发现一个问题，如果需要排序怎么办？
#我考虑了下面语句
SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM EMP A1 ORDER BY SAL) WHERE RN BETWEEN 5 AND 10 ; 
#但是这样显然不行，RN的顺序乱了，所以还是需要再嵌套一个子视图
SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM EMP A1 ORDER BY SAL) WHERE RN BETWEEN 5 AND 10 ; 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30          5
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30         10
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10          7
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30          6
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20          8
      7839 KING       PRESIDENT            1981-11-17       5000                    10          9

#Like this
SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM EMP A1 ORDER BY SAL) WHERE RN BETWEEN 5 AND 10 ;
#把FROM EMP A1 ORDER BY SAL --> FROM (SELECT * FROM ORDER BY SAL) A1
SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM (select * from emp order by sal) A1) WHERE RN BETWEEN 5 AND 10 ; 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30          5
      7934 MILLER     CLERK           7782 1982-01-23       1300                    10          6
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30          7
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30          8
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10          9
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30         10
#这样的结果看起来才符合预期
      
      
      
#但是如果不做分组排序，那么下面这种形式也没什么问题
#显示奇数列  
SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM EMP A1) WHERE MOD(RN,2) = 1;  

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17        800                    20          1
      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30          3
      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30          5
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10          7
      7839 KING       PRESIDENT            1981-11-17       5000                    10          9
      7876 ADAMS      CLERK           7788 1987-05-23       1100                    20         11
      7902 FORD       ANALYST         7566 1981-12-03       3000                    20         13

#显示偶数列    
SELECT * FROM (SELECT A1.*, ROWNUM AS RN FROM EMP A1) WHERE MOD(RN,2) = 0 ; 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30          2
      7566 JONES      MANAGER         7839 1981-04-02       2975                    20          4
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30          6
      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20          8
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30         10
      7900 JAMES      CLERK           7698 1981-12-03        950                    30         12
      7934 MILLER     CLERK           7782 1982-01-23       1300                    10         14     


      
##分页2 rowid
tip: 待研究
##分页3 分析函数
tip: 待研究
      
      
#create new table from select

create table [newtablename] (newcolumns) as select (columns2) from [tablename]

#练习
CREATE TABLE EMP_DEPT (
    EMPNO,
    ENAME,
    JOB,
    MGR,
    MGRNAME,
    HIREDATE,
    SAL,
    COMM,
    DEPTNO,
    MGRDNAME
)
AS
SELECT A1.EMPNO, A1.ENAME, A1.JOB, A1.MGR, A3.ENAME
    , A1.HIREDATE, A1.SAL, A1.COMM, A1.DEPTNO, A2.DNAME
FROM EMP A1, DEPT A2, EMP A3
WHERE A1.DEPTNO = A2.DEPTNO
    AND A1.MGR = A3.EMPNO;

Table created.

SQL> SET LINESIZE 150
SQL> SELECT * FROM EMP_DEPT;

     EMPNO ENAME      JOB              MGR MGRNAME    HIREDATE          SAL       COMM     DEPTNO MGRDNAME
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- --------------
      7788 SCOTT      ANALYST         7566 JONES      1987-04-19       3000                    20 RESEARCH
      7902 FORD       ANALYST         7566 JONES      1981-12-03       3000                    20 RESEARCH
      7900 JAMES      CLERK           7698 BLAKE      1981-12-03        950                    30 SALES
      7499 ALLEN      SALESMAN        7698 BLAKE      1981-02-20       1600        300         30 SALES
      7654 MARTIN     SALESMAN        7698 BLAKE      1981-09-28       1250       1400         30 SALES
      7844 TURNER     SALESMAN        7698 BLAKE      1981-09-08       1500          0         30 SALES
      7521 WARD       SALESMAN        7698 BLAKE      1981-02-22       1250        500         30 SALES
      7934 MILLER     CLERK           7782 CLARK      1982-01-23       1300                    10 ACCOUNTING
      7876 ADAMS      CLERK           7788 SCOTT      1987-05-23       1100                    20 RESEARCH
      7782 CLARK      MANAGER         7839 KING       1981-06-09       2450                    10 ACCOUNTING
      7566 JONES      MANAGER         7839 KING       1981-04-02       2975                    20 RESEARCH
      7698 BLAKE      MANAGER         7839 KING       1981-05-01       2850                    30 SALES
      7369 SMITH      CLERK           7902 FORD       1980-12-17        800                    20 RESEARCH

13 rows selected.

#然而发现下面这位丢了，问题在于KING是BOSS，并没有MGR 导致 A1.MGR = A3.EMPNO --> FLASE

SQL> select * from emp where mgr is null ; 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17       5000                    10

Elapsed: 00:00:00.01  
      
      
#怎么解决？？？

#这个方法虽然实现了功能，但是觉得查询量很大，过于冗余
CREATE TABLE EMP_DEPT(EMPNO, ENAME, JOB, MGR, MGRNAME, HIREDATE, SAL, COMM, DEPTNO, MGRDNAME)
    AS 
    select A1.EMPNO,A1.ENAME,A1.JOB,A1.MGR,A3.ENAME,A1.HIREDATE,A1.SAL,A1.COMM,A1.DEPTNO,A2.DNAME from emp a1, dept a2, emp a3 where a1.deptno = a2.deptno and a1.mgr = a3.empno
    union ALL
    select A1.EMPNO,A1.ENAME,A1.JOB,null,null,A1.HIREDATE,A1.SAL,A1.COMM,A1.DEPTNO,A2.DNAME  from emp a1, dept a2  where a1.deptno = a2.deptno and a1.mgr is null;    

#Format:
CREATE TABLE EMP_DEPT (
    EMPNO,
    ENAME,
    JOB,
    MGR,
    MGRNAME,
    HIREDATE,
    SAL,
    COMM,
    DEPTNO,
    MGRDNAME
)
AS
SELECT A1.EMPNO, A1.ENAME, A1.JOB, A1.MGR, A3.ENAME
    , A1.HIREDATE, A1.SAL, A1.COMM, A1.DEPTNO, A2.DNAME
FROM emp a1, dept a2, emp a3
WHERE a1.deptno = a2.deptno
    AND a1.mgr = a3.empno
UNION ALL
SELECT A1.EMPNO, A1.ENAME, A1.JOB, NULL, NULL
    , A1.HIREDATE, A1.SAL, A1.COMM, A1.DEPTNO, A2.DNAME
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno
    AND a1.mgr IS NULL;
    

SQL> SELECT * FROM EMP_DEPT;

     EMPNO ENAME      JOB              MGR MGRNAME    HIREDATE                  SAL       COMM     DEPTNO MGRDNAME
---------- ---------- --------- ---------- ---------- ------------------ ---------- ---------- ---------- --------------
      7369 SMITH      CLERK           7902 FORD       17-DEC-80                 800                    20 RESEARCH
      7499 ALLEN      SALESMAN        7698 BLAKE      20-FEB-81                1600        300         30 SALES
      7521 WARD       SALESMAN        7698 BLAKE      22-FEB-81                1250        500         30 SALES
      7566 JONES      MANAGER         7839 KING       02-APR-81                2975                    20 RESEARCH
      7654 MARTIN     SALESMAN        7698 BLAKE      28-SEP-81                1250       1400         30 SALES
      7698 BLAKE      MANAGER         7839 KING       01-MAY-81                2850                    30 SALES
      7782 CLARK      MANAGER         7839 KING       09-JUN-81                2450                    10 ACCOUNTING
      7788 SCOTT      ANALYST         7566 JONES      19-APR-87                3000                    20 RESEARCH
      7839 KING       PRESIDENT                       17-NOV-81                5000                    10 ACCOUNTING
      7844 TURNER     SALESMAN        7698 BLAKE      08-SEP-81                1500          0         30 SALES
      7876 ADAMS      CLERK           7788 SCOTT      23-MAY-87                1100                    20 RESEARCH
      7900 JAMES      CLERK           7698 BLAKE      03-DEC-81                 950                    30 SALES
      7902 FORD       ANALYST         7566 JONES      03-DEC-81                3000                    20 RESEARCH
      7934 MILLER     CLERK           7782 CLARK      23-JAN-82                1300                    10 ACCOUNTING

14 rows selected.



#发现lefg/right/inner join可以满足

#tip:
#left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
#right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
#inner join(等值连接) 只返回两个表中联结字段相等的行


#Copy了一下网上的资料
#表A记录如下：
#aID　　　　　aNum
#1　　　　　a20050111
#2　　　　　a20050112
#3　　　　　a20050113
#4　　　　　a20050114
#5　　　　　a20050115
#
#表B记录如下:
#bID　　　　　bName
#1　　　　　2006032401
#2　　　　　2006032402
#3　　　　　2006032403
#4　　　　　2006032404
#8　　　　　2006032408
#
#--------------------------------------------
#1.left join
#sql语句如下: 
#select * from A
#left join B 
#on A.aID = B.bID
#
#结果如下:
#aID　　　　　aNum　　　　　bID　　　　　bName
#1　　　　　a20050111　　　　1　　　　　2006032401
#2　　　　　a20050112　　　　2　　　　　2006032402
#3　　　　　a20050113　　　　3　　　　　2006032403
#4　　　　　a20050114　　　　4　　　　　2006032404
#5　　　　　a20050115　　　　NULL　　　　　NULL
#
#（所影响的行数为 5 行）
#结果说明:
#left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
#换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
#B表记录不足的地方均为NULL.
#--------------------------------------------
#2.right join
#sql语句如下: 
#select * from A
#right join B 
#on A.aID = B.bID
#
#结果如下:
#aID　　　　　aNum　　　　　bID　　　　　bName
#1　　　　　a20050111　　　　1　　　　　2006032401
#2　　　　　a20050112　　　　2　　　　　2006032402
#3　　　　　a20050113　　　　3　　　　　2006032403
#4　　　　　a20050114　　　　4　　　　　2006032404
#NULL　　　　　NULL　　　　　8　　　　　2006032408
#
#（所影响的行数为 5 行）
#结果说明:
#仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
#--------------------------------------------
#3.inner join
#sql语句如下: 
#select * from A
#innerjoin B 
#on A.aID = B.bID
#
#结果如下:
#aID　　　　　aNum　　　　　bID　　　　　bName
#1　　　　　a20050111　　　　1　　　　　2006032401
#2　　　　　a20050112　　　　2　　　　　2006032402
#3　　　　　a20050113　　　　3　　　　　2006032403
#4　　　　　a20050114　　　　4　　　　　2006032404
#
#结果说明:
#很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
#--------------------------------------------
#注: 
#LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。
#使用 LEFT JOIN 运算来创建一个左边外部联接。
#左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，
#即使在第二个（右边）表中并没有相符值的记录。
#
#语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2
#
#说明：table1, table2参数用于指定要将记录组合的表的名称。
#field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。
#compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。
#如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误. 



#最后发现这个方法是比较满意的。
CREATE TABLE EMP_DEPT (
    EMPNO,
    ENAME,
    JOB,
    MGR,
    MGRNAME,
    HIREDATE,
    SAL,
    COMM,
    DEPTNO,
    MGRDNAME
)
AS
SELECT A1.EMPNO, A1.ENAME, A1.JOB, A1.MGR, A3.ENAME
    , A1.HIREDATE, A1.SAL, A1.COMM, A1.DEPTNO, A2.DNAME
FROM EMP A1
    LEFT JOIN DEPT A2 ON A1.DEPTNO = A2.DEPTNO
    LEFT JOIN EMP A3 ON A1.MGR = A3.EMPNO;
    
    
SQL> select * from emp_dept; 

     EMPNO ENAME      JOB              MGR MGRNAME    HIREDATE                  SAL       COMM     DEPTNO MGRDNAME
---------- ---------- --------- ---------- ---------- ------------------ ---------- ---------- ---------- --------------
      7788 SCOTT      ANALYST         7566 JONES      19-APR-87                3000                    20 RESEARCH
      7902 FORD       ANALYST         7566 JONES      03-DEC-81                3000                    20 RESEARCH
      7499 ALLEN      SALESMAN        7698 BLAKE      20-FEB-81                1600        300         30 SALES
      7521 WARD       SALESMAN        7698 BLAKE      22-FEB-81                1250        500         30 SALES
      7654 MARTIN     SALESMAN        7698 BLAKE      28-SEP-81                1250       1400         30 SALES
      7844 TURNER     SALESMAN        7698 BLAKE      08-SEP-81                1500          0         30 SALES
      7900 JAMES      CLERK           7698 BLAKE      03-DEC-81                 950                    30 SALES
      7934 MILLER     CLERK           7782 CLARK      23-JAN-82                1300                    10 ACCOUNTING
      7876 ADAMS      CLERK           7788 SCOTT      23-MAY-87                1100                    20 RESEARCH
      7782 CLARK      MANAGER         7839 KING       09-JUN-81                2450                    10 ACCOUNTING
      7566 JONES      MANAGER         7839 KING       02-APR-81                2975                    20 RESEARCH
      7698 BLAKE      MANAGER         7839 KING       01-MAY-81                2850                    30 SALES
      7369 SMITH      CLERK           7902 FORD       17-DEC-80                 800                    20 RESEARCH
      7839 KING       PRESIDENT                       17-NOV-81                5000                    10 ACCOUNTING

14 rows selected.


#UNION
#使用该操作符用于取得两个结果的并集，当使用该操作符时，会自动去掉结果中重复行。
SELECT ENAME, SAL, JOB FROM EMP WHERE SAL > 2500
    UNION
    SELECT ENAME, SAL, JOB FROM EMP WHERE JOB = 'MANAGER';

ENAME             SAL JOB
---------- ---------- ---------
BLAKE            2850 MANAGER
CLARK            2450 MANAGER
FORD             3000 ANALYST
JONES            2975 MANAGER
KING             5000 PRESIDENT
SCOTT            3000 ANALYST   



#解释：
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL > 2500; 

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7788 SCOTT      ANALYST         3000
      7839 KING       PRESIDENT       5000
      7902 FORD       ANALYST         3000

SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER';

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL > 2500
  2  UNION
  3  SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER';

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7788 SCOTT      ANALYST         3000
      7839 KING       PRESIDENT       5000
      7902 FORD       ANALYST         3000

6 rows selected.


SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL > 2500
  2  UNION ALL
  3  SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER';

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7788 SCOTT      ANALYST         3000
      7839 KING       PRESIDENT       5000
      7902 FORD       ANALYST         3000
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450

8 rows selected.


#INTERSECT 取交集
SQL> 
SQL> SELECT * FROM TABLE1; 

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     AVGSAL
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80                 800                    20          1
      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600        300         30          2
      7521 WARD       SALESMAN        7698 22-FEB-81                1250        500         30          3
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20          4
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30          6

6 rows selected.

SQL> SELECT * FROM TABLE2; 

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     AVGSAL
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30          6
      7782 CLARK      MANAGER         7839 09-JUN-81                2450                    10          7
      7788 SCOTT      ANALYST         7566 19-APR-87                3000                    20          8
      7839 KING       PRESIDENT            17-NOV-81                5000                    10          9
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500          0         30         10

6 rows selected.

SQL> SELECT * FROM TABLE1 INTERSECT SELECT * FROM TABLE2; 

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL       COMM     DEPTNO     AVGSAL
---------- ---------- --------- ---------- ------------------ ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30          6
      
      
      









#更改当前session内的的日期格式
alter  session set nls_date_format = 'yy-mm-dd';
insert into student values(1,'小明','男','1997-12-11',1500);



#查看字符集
select userenv('language') from dual;
select * from nls_database_parameters;  #服务器字符集
select * from nls_instance_parameters;  #客户端字符集
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

#修改字符集
shutdown immediate
startup mount
alter system enable restricted session; #限制session
show parameter job_queue_processes; 
show parameter aq_tm_processes; 
alter system set job_queue_processes=0;
alter database open;
alter database character set ZHS16GBK;
#出现错误提示，新字符集必须是老字符集的超集，
#也就原来字符集是新字符集的子集，
#可以再Oracle官方文档上查询字符集包含关系。
#下面使用Oracle内部命令internal_use，跳过超集检查，
#生产环境不建议使用此方法。
alter database character set internal_use ZHS16GBK;
shutdown immediate;
startup;
#改完查一遍，然后改系统环境变量NLS_LANG，不然中文乱码
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';



#删除数据
delete from [tablename]
#删表
drop table [tablename]
#快速删除(不写日志，无法找回)
truncate table [tablename]
最后编辑于：2018.09.29 14:09:22
人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 205,132评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,802评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,566评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,858评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,867评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,695评论 1赞 282
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,064评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,705评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 42,915评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,677评论 2赞 323
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,796评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,432评论 4赞 322
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,041评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,992评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,223评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,185评论 2赞 352
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,535评论 2赞 343
Oracle学习笔记-SELECT详解，数据库基本操作

推荐阅读更多精彩内容
