oracle-学习笔记-1

1.1查看控制文件

SQL> desc v$controlfile
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 STATUS                                             VARCHAR2(7)
 NAME                                               VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)
 BLOCK_SIZE                                         NUMBER
 FILE_SIZE_BLKS                                     NUMBER

SQL> select * from v$controlfile;

1.2查看数据文件

SQL> desc v$datafile;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 FILE#                                              NUMBER
 CREATION_CHANGE#                                   NUMBER
 CREATION_TIME                                      DATE
 TS#                                                NUMBER
 RFILE#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 ENABLED                                            VARCHAR2(10)
 CHECKPOINT_CHANGE#                                 NUMBER
 CHECKPOINT_TIME                                    DATE
 UNRECOVERABLE_CHANGE#                              NUMBER
 UNRECOVERABLE_TIME                                 DATE
 LAST_CHANGE#                                       NUMBER
 LAST_TIME                                          DATE
 OFFLINE_CHANGE#                                    NUMBER
 ONLINE_CHANGE#                                     NUMBER
 ONLINE_TIME                                        DATE
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 CREATE_BYTES                                       NUMBER
 BLOCK_SIZE                                         NUMBER
 NAME                                               VARCHAR2(513)
 PLUGGED_IN                                         NUMBER
 BLOCK1_OFFSET                                      NUMBER
 AUX_NAME                                           VARCHAR2(513)
 FIRST_NONLOGGED_SCN                                NUMBER
 FIRST_NONLOGGED_TIME                               DATE
 
SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         
         
         
1.3 查看命令帮助
help index

1.4 查看某个命令的帮助
? set

set sqlbl on   开启sqlplus支持空行

1.5使用替代变量
SQL> select * from dept where deptno=&tt;
输入 tt 的值:  10
原值    1: select * from dept where deptno=&tt
新值    1: select * from dept where deptno=10

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
 
1.5 查看、修改、执行缓冲区的sql
查看：list(简写 l )
修改：c
执行：/

SQL> l
  1* select * fron dept
SQL> c/n/m
  1* select * from dept
SQL> /

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

1.6 删除和追加 缓冲区的sql

删除：
SQL> l
  1  select *
  2  from
  3  dept
  4* where deptno=10
SQL> del 4
SQL> l
  1  select *
  2  from
  3* dept
SQL> del 2 3
SQL> l
  1* select *

追加：
SQL> l
  1* select *
SQL> a from dept
  1* select *from dept
SQL> /

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

1.7保存缓冲区的sql
SQL> save c:\test.txt
已创建 file c:\test.txt
SQL> l
  1* select *from dept

1.8调用已保存的脚本并执行
SQL> @test1.txt

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> l
  1* select * from dept where deptno=10
SQL> get test1.txt
  1* select * from dept where deptno=10
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

1.9 编辑缓冲区
SQL> select * from dept;

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

SQL> edit
已写入 file afiedt.buf

  1* select * from dept where deptno=20
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

1.10使用col命令
SQL> col deptno heading "编号"
SQL> select * from dept;

      编号 DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
SQL> col dname format a10 heading "部门名称"
SQL> select * from dept;

      编号 部门名称   LOC
---------- ---------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

SQL> connect sys/ora as sysdba
已连接。
SQL> select bytes from v$datafile;

     BYTES
----------
 503316480
  26214400
 262144000
   5242880
 104857600

SQL> col bytes format 999,999,999
SQL> select bytes from v$datafile;

       BYTES
------------
 503,316,480
  26,214,400
 262,144,000
   5,242,880
 104,857,600

 

1.11查看表结构
SQL> desc dept
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)
 

1.12设置报表
SQL> connect scott/tiger
SQL> set linesize 50
SQL> ttitle center "我的标题" 1-
> left "测试报表" right "页" -
> format 999 sql.pno skip 2
SQL> select * from dept;

测试报表             我的标题1              页   1

      编号 部门名称   LOC
---------- ---------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

SQL> ttitle off
SQL> select * from dept;

      编号 部门名称   LOC
---------- ---------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

1.13设置报表

先在scott用户下创建一个表
CREATE TABLE "SCOTT"."BOOKS" 
   ( "BOOKS_ID" NUMBER, 
 "BOOKS_NAME" VARCHAR2(30), 
 "PRICE" NUMBER, 
 "QTY" NUMBER, 
 "PUB" VARCHAR2(50)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 

SQL> set linesize 300
SQL> select * from books;

  BOOKS_ID BOOKS_NAME                          PRICE        QTY PUB
---------- ------------------------------ ---------- ---------- --------------------------------------------------
         1 中国文学                               39         12 人民大学
         2 外国文学                             12.5         22 人民大学
         3 英语阅读                               33         21 清华大学
         4 建筑艺术                                6         33 清华大学
         5 计算机入门                           44.8         24 电子工业
         6 数据结构                             54.7         65 电子工业
         7 语文                                   43          5 电子工业

已选择7行。
SQL> break on pub
SQL> select * from books;

  BOOKS_ID BOOKS_NAME                          PRICE        QTY PUB
---------- ------------------------------ ---------- ---------- --------------------------------------------------
         1 中国文学                               39         12 人民大学
         2 外国文学                             12.5         22
         3 英语阅读                               33         21 清华大学
         4 建筑艺术                                6         33
         5 计算机入门                           44.8         24 电子工业
         6 数据结构                             54.7         65
         7 语文                                   43          5

已选择7行。

进行统计：
SQL> comp count label "计数" of books_name on pub
SQL> select * from books;

  BOOKS_ID BOOKS_NAME                          PRICE        QTY PUB
---------- ------------------------------ ---------- ---------- --------------------------------------------------
         1 中国文学                               39         12 人民大学
         2 外国文学                             12.5         22
           ------------------------------                       **************************************************
                                        2                       计数
         3 英语阅读                               33         21 清华大学
         4 建筑艺术                                6         33
           ------------------------------                       **************************************************
                                        2                       计数
         5 计算机入门                           44.8         24 电子工业
         6 数据结构                             54.7         65
         7 语文                                   43          5
           ------------------------------                       **************************************************
                                        3                       计数

已选择7行。
SQL> col pub format a15
SQL> col books_name format a15
SQL> select * from books;

  BOOKS_ID BOOKS_NAME           PRICE        QTY PUB
---------- --------------- ---------- ---------- ---------------
         1 中国文学                39         12 人民大学
         2 外国文学              12.5         22
           ---------------                       ***************
                         2                       计数
         3 英语阅读                33         21 清华大学
         4 建筑艺术                 6         33
           ---------------                       ***************
                         2                       计数
         5 计算机入门            44.8         24 电子工业
         6 数据结构              54.7         65
         7 语文                    43          5
           ---------------                       ***************
                         3                       计数

已选择7行。

1.14 保存查询的数据(结果集)
SQL> spool c:\spool.txt
SQL> select * from books;

  BOOKS_ID BOOKS_NAME           PRICE        QTY PUB
---------- --------------- ---------- ---------- ---------------
         1 中国文学                39         12 人民大学
         2 外国文学              12.5         22
           ---------------                       ***************
                         2                       计数
         3 英语阅读                33         21 清华大学
         4 建筑艺术                 6         33
           ---------------                       ***************
                         2                       计数
         5 计算机入门            44.8         24 电子工业
         6 数据结构              54.7         65
         7 语文                    43          5
           ---------------                       ***************
                         3                       计数

已选择7行。

SQL> spool off
SQL> edit spool.txt

SQL>

确实已经保存为spool.txt的文本文件了。 

 

 

2.1语言分类
2.1.1数据定义语言 DDL
create 
alter 
drop
SQL> create table abc (a varchar2(10),b char(10));

表已创建。

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
ABC
BOOKS

已选择6行。

SQL> alter table abc add c number;

表已更改。

SQL> desc abc
 名称
                                               是否为空? 类型
 -----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ -------- --------------------------------------------------------
------------------------------------------------------------
 A
                                                        VARCHAR2(10)
 B
                                                        CHAR(10)
 C
                                                        NUMBER
SQL> alter table abc drop column c;

表已更改。

 

2.1.2数据控制语言 DCL
grant 
revoke

SQL> connect decoration/decoration
已连接。
SQL> select * from scott.dept;
select * from scott.dept
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> connect scott/tiger
已连接。
SQL> select * from dept;

      编号 部门名称   LOC
---------- ---------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

SQL> grant select on dept to decoration;

授权成功。

SQL> connect decoration/decoration
已连接。

SQL> select * from scott.dept;

      编号 部门名称   LOC
---------- ---------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

SQL>

SQL> connect scott/tiger
已连接。

SQL> revoke select on dept from decoration;

撤销成功。

SQL> connect decoration/decoration
已连接。
SQL> select * from scott.dept;
select * from scott.dept
                    *
第 1 行出现错误:
ORA-00942: 表或视图不存在

2.1.3数据操纵语言 DML
select 
insert
delete
update

SQL> insert into abc values ('aa','bb');

已创建 1 行。

SQL> select * from abc;

A          B
---------- ----------
aa         1
aa         bb

SQL> update abc set a='haha' where b='1';

已更新 1 行。

SQL> delete  abc where b='bb';

已删除 1 行。

SQL> select * from abc;

A          B
---------- ----------
haha       1

SQL>

2.2系统函数
2.2.1字符
length,lengthb,ltrim,replace,rtrim,substr,trim

SQL> select length('abcdef') from dual;

LENGTH('ABCDEF')
----------------
               6

SQL> select length('abc好ef') from dual;

LENGTH('ABC好EF')
-----------------
                6

SQL> select lengthb('abc好ef') from dual;

LENGTHB('ABC好EF')
------------------
                 7
                 
                
SQL> select lengthb('  abc好e f ') from dual;

LENGTHB('ABC好EF')
------------------
                11

SQL> select ltrim('  abc好e f ') from dual;

LTRIM('AB
---------
abc好e f

SQL> select ltrim('  abc好e f ') l from dual;

L
---------
abc好e f

SQL> select lengthb(a.l) from (select ltrim('  abc好e f ') l from dual) a;

LENGTHB(A.L)
------------
           9

SQL> select lengthb(ltrim('  abc好e f ')) from dual;

LENGTHB(LTRIM('ABC好EF'))
-------------------------
                        9

SQL> select lengthb(rtrim('  abc好e f ')) from dual;

LENGTHB(RTRIM('ABC好EF'))
-------------------------
                       10

SQL> select lengthb(trim('  abc好e f ')) from dual;

LENGTHB(TRIM('ABC好EF'))
------------------------
                       8
SQL> desc aa
 名称
                                               是否为空? 类型
 -----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ -------- --------------------------------------------------------
------------------------------------------------------------
 A1
                                                        VARCHAR2(10)
 A2
                                                        VARCHAR2(10)
 A3
                                                        CHAR(10)
SQL> insert into aa values('avx','ssw','aaa');

已创建 1 行。

SQL> insert into aa values('avx','swx','bfd');

已创建 1 行。

SQL> insert into aa values('aeex','ddswx','ffbfd');

已创建 1 行。

SQL> update aa set a3='aa';

已更新3行。

SQL> select *from aa;

A1         A2         A3
---------- ---------- ----------
avx        ssw        aa
avx        swx        aa
aeex       ddswx      aa                                                        
                                                       
SQL> select length(a2),length(a3) from aa;

LENGTH(A2) LENGTH(A3)
---------- ----------
         3         10
         3         10
         5         10

SQL> select substr('abcdefrdsg',2,3) from dual;

SUB
---
bcd

SQL> select substr('abcdefrdsg',length('abcdefrdsg')-3+1,3) from dual;

SUB
---
dsg

2.2.2日期
SQL> select sysdate from dual;

SYSDATE
--------------
04-12月-10

SQL> select current_date from dual;

CURRENT_DATE
--------------
04-12月-10

SQL> alter session set NLS_DATE_FORMAT='dd-mon-yyyy hh:mi:ss';

会话已更改。

SQL> select sysdate from dual;

SYSDATE
-------------------------
04-12月-2010 11:30:37

SQL> select current_date from dual;

CURRENT_DATE
-------------------------
04-12月-2010 11:30:42

SQL> select next_day(sysdate,'星期一') as n from dual;

N
-------------------------
06-12月-2010 11:34:13

 

2.2.3转换函数
to_char,to_date,to_number

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2010-12-04 11:37:32

SQL> select to_date('4-12月-10') from dual;

TO_DATE('4-12月-10')
-------------------------
04-12月-0010 12:00:00

SQL> select to_number('4-12月-10') from dual;
select to_number('4-12月-10') from dual
                 *
第 1 行出现错误:
ORA-01722: 无效数字

SQL> select to_number('410') from dual;

TO_NUMBER('410')
----------------
             410

SQL> select to_number('000410') from dual;

TO_NUMBER('000410')
-------------------
                410

2.2.4聚集函数
sum , avg , max , min ,count

SQL> select sum(price) from books;

SUM(PRICE)
----------
       233
SQL> select max(price) from books;

MAX(PRICE)
----------
      54.7

SQL> select min(price) from books;

MIN(PRICE)
----------
         6

SQL> select avg(price) from books;

AVG(PRICE)
----------
33.2857143

SQL> select count(price) from books;

COUNT(PRICE)
------------
           7
           
SQL> commit;

提交完成。

SQL> select * from aa;

A1         A2         A3
---------- ---------- ----------
avx        ssw        aa
avx        swx        aa
aeex       ddswx      aa
esd
ddd                   er
ee         ssss       ee

已选择6行。

SQL> select count(*) from aa;

  COUNT(*)
----------
         6

SQL> select count(a2) from aa;

 COUNT(A2)
----------
         4

 

2.2.5其他

user decode nvl

SQL> select user from dual;

USER
------------------------------
SCOTT

SQL> connect decoration/decoration;
已连接。
SQL> select user from dual;

USER
------------------------------
DECORATION

SQL> connect scott/tiger;
已连接。
SQL> select user from dual;

USER
------------------------------
SCOTT

SQL> select user from dual;

USER
------------------------------
SCOTT

SQL> select * from e;

未选定行

SQL> desc e;
 名称
                                               是否为空? 类型
 -----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ -------- --------------------------------------------------------
------------------------------------------------------------
 EID
                                                        NUMBER
 ENAME
                                                        VARCHAR2(30)
 SEX
                                                        NVARCHAR2(2)

SQL> select * from e;

       EID ENAME                          SEX
---------- ------------------------------ ----
         1 赵1                            男
         2 钱1                            女
         3 孙1                            女
         3 李1                            男
         3 周1                            女

SQL> select sum(decode(sex,'男',1,0)) as "男",sum(decode(sex,'女',1,0)) as "女" from e;

        男         女
---------- ----------
         2          3

SQL> select sum(decode(sex,'男',1,0)) as 男人数,sum(decode(sex,'女',1,0)) as 女人数 from e;

    男人数     女人数
---------- ----------
         2          3

SQL> select * from aa;

A1         A2         A3
---------- ---------- ----------
avx        ssw        aa
avx        swx        aa
aeex       ddswx      aa
esd
ddd                   er
ee         ssss       ee

已选择6行。

SQL> select a1,nvl(a2,'未输入') a2 from aa;

A1         A2
---------- ----------
avx        ssw
avx        swx
aeex       ddswx
esd        未输入
ddd        未输入
ee         ssss

已选择6行。

SQL> select * from aa where a2 is null;

A1         A2         A3
---------- ---------- ----------
esd
ddd                   er

SQL> select * from aa where a2 is not null;

A1         A2         A3
---------- ---------- ----------
avx        ssw        aa
avx        swx        aa
aeex       ddswx      aa
ee         ssss       ee

SQL> select * from aa order by a1 asc;

A1         A2         A3
---------- ---------- ----------
aeex       ddswx      aa
avx        swx        aa
avx        ssw        aa
ddd                   er
ee         ssss       ee
esd

已选择6行。

SQL> select * from aa order by a1 desc;

A1         A2         A3
---------- ---------- ----------
esd
ee         ssss       ee
ddd                   er
avx        ssw        aa
avx        swx        aa
aeex       ddswx      aa

已选择6行。

SQL> select distinct a1 from aa;

A1
----------
esd
ee
ddd
avx
aeex

 

3.1分组语句

SQL> select sum(price*qty) from books;

SUM(PRICE*QTY)
--------------
        6479.7
SQL> select pub,sum(price*qty) from books group by pub;

PUB             SUM(PRICE*QTY)
--------------- --------------
清华大学                   891
人民大学                   743
电子工业                4845.7

SQL> select pub,sum(price*qty) from books where sum(price)>30 group by pub;
select pub,sum(price*qty) from books where sum(price)>30 group by pub
                                           *
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数

SQL> select pub,sum(price*qty) from books having sum(price) > 30 group by pub;

PUB             SUM(PRICE*QTY)
--------------- --------------
清华大学                   891
人民大学                   743
电子工业                4845.7

SQL> select pub,sum(price*qty) from books group by pub having sum(price) > 30;

PUB             SUM(PRICE*QTY)
--------------- --------------
清华大学                   891
人民大学                   743
电子工业                4845.7

SQL> select a1,count(a1) from aa where count(a1) > 1;
select a1,count(a1) from aa where count(a1) > 1
                                  *
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数

SQL> select a1,count(a1) from aa group by a1 having count(a1) > 1;

A1          COUNT(A1)
---------- ----------
avx                 2

3.2模糊查询
SQL> select * from aa where a1 like 'a_';

未选定行

SQL> select * from aa where a1 like 'a__';

A1         A2         A3
---------- ---------- ----------
avx        ssw        aa
avx        swx        aa

SQL> select * from aa where a1 like 'a%';

A1         A2         A3
---------- ---------- ----------
avx        ssw        aa
avx        swx        aa
aeex       ddswx      aa

SQL> select * from aa where a1 like '_a';

A1         A2         A3
---------- ---------- ----------
da         ccc        xxx

SQL> select * from aa where a1 like '__a';

A1         A2         A3
---------- ---------- ----------
sda        xxx        xxx

SQL> select * from aa where a1 like '%a';

A1         A2         A3
---------- ---------- ----------
ddda       dcc        xxx
sda        xxx        xxx
da         ccc        xxx

SQL> select * from aa where a1 like '%a%';

A1         A2         A3
---------- ---------- ----------
avx        ssw        aa
avx        swx        aa
aeex       ddswx      aa
ddda       dcc        xxx
sda        xxx        xxx
da         ccc        xxx

已选择6行。

SQL> select * from e where ename like '赵%';

       EID ENAME                          SEX
---------- ------------------------------ ----
         1 赵1                            男

3.3表的连接

SQL> col 姓名 format a10
SQL> select eid 编号,ename 姓名,sex 性别,id 所在部门 from e;

      编号 姓名       性别   所在部门
---------- ---------- ---- ----------
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
SQL> select e.eid 编号,e.ename 姓名,e.sex 性别,d.name 所在部门 from e,d where e.id=d.id;

      编号 姓名       性别 所在部门
---------- ---------- ---- ------------------------------
         1 赵1        男   A部门
         2 钱1        女   B部门
         3 孙1        女   C部门
         3 李1        男   D部门
         3 周1        女   E部门
SQL> select e.eid 编号,e.ename 姓名,e.sex 性别,d.name 所在部门 from e join d on e.id=d.id;

      编号 姓名       性别 所在部门
---------- ---------- ---- ------------------------------
         1 赵1        男   A部门
         2 钱1        女   B部门
         3 孙1        女   C部门
         3 李1        男   D部门
         3 周1        女   E部门

这是内连接，两个表数据完全匹配。

如果不完全匹配，那就是外连接，有左外连接和右外连接。

SQL> select e.eid 编号,e.ename 姓名,e.sex 性别,d.name 所在部门 from e,d where e.id=d.id(+);

      编号 姓名       性别 所在部门
---------- ---------- ---- ------------------------------
         1 赵1        男   A部门
         2 钱1        女   B部门
         3 孙1        女   C部门
         3 李1        男   D部门
         3 周1        女   E部门
         4 周2        女

已选择6行。

这是左外连接。

SQL> select e.eid 编号,e.ename 姓名,e.sex 性别,d.name 所在部门 from e,d where e.id(+)=d.id;

      编号 姓名       性别 所在部门
---------- ---------- ---- ------------------------------
         1 赵1        男   A部门
         2 钱1        女   B部门
         3 孙1        女   C部门
         3 李1        男   D部门
         3 周1        女   E部门
                           F部门
                           G部门

已选择7行。

这是右外连接。

3.4子查询
无关子查询 和 相关子查询

SQL> select * from e where id in (select id from d);

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
上面是无关子查询。

SQL> select * from e where id in (select id from d where id = e.id and id = '03');

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         3 孙1        女            3
这是相关子查询。

可以取反：
SQL> select * from e where id not in (select id from d where id = e.id and id = '03');

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         1 赵1        男            1
         2 钱1        女            2
         3 李1        男            4
         3 周1        女            5
         4 周2        女

子查询不可用 *

 

SQL> select * from e where exists (select id from d);

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
         4 周2        女

已选择6行。

SQL> select * from e;

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
         4 周2        女

已选择6行。

SQL> select * from e where exists (select id from d where id = e.id);

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5

SQL> select * from e where not exists (select id from d where id = e.id);

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         4 周2        女

做子查询 的时候，用 in 的时候 不能用 * ,用 exists 可以使用 * 。

SQL> select * from e where not exists (select * from d where id = e.id);

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         4 周2        女

SQL> select * from e where not in (select * from d where id = e.id);
select * from e where not in (select * from d where id = e.id)
                          *
第 1 行出现错误:
ORA-00936: 缺失表达式

3.5使用union

SQL> select eid,ename from e
  2  union
  3  select id,name from d;

       EID ENAME
---------- ----------
         1 A部门
         1 赵1
         2 B部门
         2 钱1
         3 C部门
         3 李1
         3 孙1
         3 周1
         4 D部门
         4 周2
         5 E部门
         6 F部门
         7 G部门

已选择13行。

SQL> select eid from e
  2  union
  3  select id from d;

       EID
----------
         1
         2
         3
         4
         5
         6
         7

已选择7行。

这样使用union可以去除重复行。

3.6使用intersect可以返回两个表中都出现的行。
SQL> select eid from e
  2  intersect
  3  select id from d;

       EID
----------
         1
         2
         3
         4
3.7一次插入多条记录
SQL> insert into e(eid,ename) select id,name from d;

已创建7行。

SQL> select * from e;

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         1 A部门
         2 B部门
         3 C部门
         4 D部门
         5 E部门
         6 F部门
         7 G部门
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
         4 周2        女

已选择13行。

3.8依赖已经有的表来创建表

SQL> create table tt as (select * from e);

表已创建。

SQL> select * from tt;

       EID ENAME      SEX          ID
---------- ---------- ---- ----------
         1 A部门
         2 B部门
         3 C部门
         4 D部门
         5 E部门
         6 F部门
         7 G部门
         1 赵1        男            1
         2 钱1        女            2
         3 孙1        女            3
         3 李1        男            4
         3 周1        女            5
         4 周2        女

已选择13行。

SQL> create table t as select eid,ename from e where eid = '001';

表已创建。

SQL> select * from t;

       EID ENAME
---------- ----------
         1 A部门
         1 赵1