二Oracle学习笔记

 

 

 

 

 

 

 

 

 

 

 

 

Oracle学习笔记

 

Java相关课程系列笔记之二

 

 

 

 

 

 

 

 

 

 

 

笔记内容说明

Oracle数据库、SQL（薛海璐老师主讲，占笔记内容100%）； 

 

 

目  录

一、 数据库介绍 1

1.1表是数据库中存储数据的基本单位 1

1.2数据库标准语言 1

1.3数据库（DB） 1

1.4数据库种类 1

1.5数据库中如何定义表 1

1.6 create database dbname的含义 1

1.7安装DBMS 1

1.8宏观上是数据-->database 1

1.9远程登录：telnet IP地址 1

1.10 TCP/IP通信协议 2

1.11数据库建连接必须提供以下信息 2

1.12一台机器可跑几个数据库，主要受内存大小影响 2

1.13源表和结果集 2

1.14几个简单命令 2

1.15 tarena给jsd1304授权 2

1.16课程中使用的5个表 3

二、 select from语句 5

2.1 select语句功能 5

2.2 select语句基本语法 5

2.3列别名 5

2.4算术表达式 5

2.5空值null的处理 5

2.6 nvl(p1,p2)函数 5

2.7拼接运算符 || 6

2.8文字字符串 6

2.9消除重复行 6

2.10其他注意事项 6

三、 SQL语句的处理过程 7

3.1 SQL语句处理过程 7

3.2处理一条select语句 7

四、 where子句 8

4.1 where子句后面可以跟什么 8

4.2语法和执行顺序 8

4.3字符串是大小写敏感的，在比较时严格区分大小写 8

4.4 where子句后面可以跟多个条件表达式 8

4.5 between and运算符 8

4.6 in运算符（多值运算符） 8

4.7 like运算符 9

4.8 is null运算符 9

4.9比较和逻辑运算符（单值运算符） 9

4.10多值运算符all、any 9

4.11运算符的否定形式 9

五、 order by子句 10

5.1语法和执行顺序 10

5.2升降序 10

5.3 null值在排序中显示 10

5.4 order by后面可以跟什么 10

5.5多列排序 10

六、 单行函数的使用 11

6.1数值类型 11

6.2日期类型 11

6.3字符类型 13

6.4转换函数 14

6.5其他注意事项 14

七、 SQL语句中的分支 15

7.1分支表达式 15

7.2分支函数 15

八、 组函数 16

8.1报表统计常用 16

8.2缺省情况组函数处理什么值 16

8.3当组函数要处理的所有值都为null时 16

8.4行级信息和组级信息 16

九、 group by子句 17

9.1语法和执行顺序 17

9.2分组过程 17

9.3常见错误 17

9.4多列分组 17

十、 having子句 18

10.1语法和执行顺序 18

10.2执行过程 18

10.3 where和having区别 18

十一、 非关联子查询 19

11.1语法 19

11.2子查询的执行过程 19

11.3常见错误 19

11.4子查询与空值 19

11.5多列子查询 20

十二、 关联子查询 21

12.1语法 21

12.2执行过程 21

12.3 exists 21

12.4 exists执行过程 21

12.5 not exists 22

12.6 not exists执行过程 22

12.7 in和exists比较 22

十三、 多表查询 23

13.1按范式要求设计表结构 23

13.2多表连接的种类 23

13.3交叉连接 23

13.4内连接 23

13.5外连接 25

13.6非等值连接 27

13.7表连接总结 27

十四、 集合 28

14.1表连接主要解决的问题 28

14.2集合运算 28

14.3集合运算符 28

14.4子查询、连接、集合总结 29

十五、 排名分页问题 30

15.1什么是rownum 30

15.2 where rownum<=5的执行过程 30

15.3 where rownum=5的执行过程 30

十六、 约束constraint 31

16.1约束的类型 31

16.2 primary key：主键约束 31

16.3 not null：非空约束 31

16.4 unique key：唯一建约束 31

16.5 references foreign key：外键约束 32

16.6 check：检查约束 34

十七、 事务 35

17.1 transaction 35

17.2定义 35

17.3事务的特性：ACID 35

17.4事务的隔离级别 35

17.5数据库开发的关键挑战 35

17.6锁的概念 36

17.7 Oracle的锁机制 36

17.8事务不提交的后果 36

17.9回滚事务rollback 36

17.10保留点savepoint 36

十八、 数据库对象：视图view 37

18.1带子查询的create table 37

18.2带子查询的insert 37

18.3定义缺省值：default 37

18.4 视图view 38

18.5视图的应用场景 38

18.6视图的分类 39

18.7视图的维护 39

十九、 数据库对象：索引index 41

19.1创建index 41

19.2扫描表的方式 41

19.3索引的结构 41

19.4为什么要使用索引 42

19.5哪些列适合建索引 42

19.6索引的类型 42

19.7哪些写法会导致索引用不了 43

二十、 数据库对象：序列号sequence 44

20.1什么是sequence 44

20.2创建sequence 44

20.3缺省是nocycle（不循环） 44

20.4缺省cache 20 44

二十一、 其他注意事项 46

21.1删除表，删除列，删除列中的值 46

21.2多对多关系的实现 46

21.3一对多（两张表） 46

21.4一对一 46

21.5数据库对象 46

12.6缺省（默认）总结： 46

 

 

 

 

一、数据库介绍

1.1表是数据库中存储数据的基本单位

1.2数据库标准语言

结构化查询语言SQL：Structureed Query Language

1）数据定义语言DDL：Data Definition Language

create table列表结构、alter table修改列、drop table删除列

2）数据操作语言DML：Data Manipulation Language

insert增加一行，某些列插入值、update修改一行，这一行的某些列、delete删除一行，跟列无关

3）事务控制语言TCL：Transaction Conrtol Language

commit确认，提交（入库）、rollback取消，回滚，撤销

4）数据查询语言DQL：Data Query Language

select语句

5）数据控制语言DCL：Data Control Language

系统为多用户系统因此有隐私权限问题：grant 授权、revoke回收权限

1.3数据库（DB）

DATABASE 关系数据库使用关系或二维表存储信息。

关系型数据库管理系统（EDBMS）：Relationship Database Management System是一套软件，用于在数据库中存储数据、维护数据、查询数据等。

1.4数据库种类

Oracle 10g（Oracle）、DB2（IBM）、SQL SERVER（MS）

1.5数据库中如何定义表

先画列即表头（列名，数据类型及长度，约束）；数据类型有字符、数值number、日期date。

1.6 create database dbname的含义

创建数据库即创建可用空间，创建出一堆数据文件data file

1.7安装DBMS

职位：DBA 数据库管理员（DataBase Administrator）

1.8宏观上是数据-->database

开发流程：create tabale  DML TCL -> DQL select

1.9远程登录：telnet IP地址

sql developer在linux系统--->连接--->database在solaris系统

1.10 TCP/IP通信协议

两台机器上的两个应用程序要通信，必须依赖网络，依赖TCP/IP通信协议。

IP：IP协议包中提供要连接机器的IP地址，用于标识机器。

TCP：TCP协议包中提供与机器上的哪个具体应用程序通信，通过端口号实现，oracle数据库服务缺省端口为1521，用于标识Oracle此数据库应用。

1.11数据库建连接必须提供以下信息

ip地址（确认机器）、port号（确认进程（程序）确认Oracle）

SID：一个端口可以为多个oracle数据库提供监听，因此还需要提供具体的数据库名。（确认数据库里的哪个数据库）

username、password：要想访问数据库，必须是该数据库上一个有效的用户。（确认身份）

1.12一台机器可跑几个数据库，主要受内存大小影响

1.13源表和结果集

源表：被查询的表 结果集：select语句的查询结果

1.14几个简单命令

show user：查看当前用户 desc 表名：查看表结构

drop table 表名 purge;删除表，Oracle中删除表不是真正的删除，而是占空间的移动到别的地方，因为为了不占空间，真正的删除需要用purge。

delete from 表名：删除表中所有值；若加上where 列名=value则删除某列中的值

1.15 tarena给jsd1304授权

connect tarena/tarena

grant select on account to jsd1304; grant select on service to jsd1304;

grant select on cost to jsd1304;

jsd1304 select tarena的表

connect jsd1304/jsd1304

create synonym 创建同义词 create synonym account for tarena.account;

create synonym service for tarena.service; create synonym cost for tarena.cost;

1.16课程中使用的5个表

 

 

 

 

 

二、
select from语句

2.1 select语句功能

1）投影操作：结果集是源表中的部分“列”

2）选择操作：结果集是源表中的部分“行”

3）选择操作+投影操作：结果集是源表中的部分“行”部分“列”

4）连接操作join：多表查询，结果集来自多张表，把多张的记录按一定条件组合起来

2.2 select语句基本语法

1）select colname(列名) from tabname（表名）

2）select中指定多个列名，则用“逗号”分隔：select colname1,colname2 from tabname

3）* 号表示所有列：select * from tabname

4）select语句：可有多个子句

5）select子句：投影操作（列名）、列表达式、函数 、from子句等

2.3列别名

1）给列起一个别名，能够改变一个列、表达式的标识。

2）不写的话默认都是转成大写。 3）适合计算字段。

4）在原名和别名之间可以使用as关键字。

5）别名中包含空格、特数字符或希望大小写敏感的，用“”双引号将其括起来。

2.4算术表达式

在number类型上使用算术表达式（加减乘除）。

eg：一个月使用了250小时,每种资费标准下应缴纳的费用(首次实现)

select base_cost + (250 - base_duration)*unit_cost fee from cost;

2.5空值null的处理

未知的，没写数

1）空值不等于0

2）空值不等于空格

3）在算术表达式中包含空值导致结果为空

4）在算术表达式中包含空值需要用空值转换函数nvl处理

2.6 nvl(p1,p2)函数

空值转换函数

1）两个参数类型要一致！

2）参数的数据类型可以是数值number、字符character、日期date

3）但null转成字符串，null也要用to_char()转化。

4）实现过程：

        if  p1  is  null  then   return p2

        elase return  p1

        end  if

5）实现空值转换：null－>非null值 0

eg：一个月使用了250小时,每种资费标准下应缴纳的费用(再次实现)

select nvl(base_cost,0) + (250 - nvl(base_duration,0)) * nvl(unit_cost,0) fee from cost;

2.7拼接运算符 ||

表达字符（串）的拼接，可以将某几列或某列与字符串拼接在一起。

select colname1||colname2 from tabname

2.8文字字符串

select语句后面可以包含的文字值：字符、表达式、数字。

1）字符常量（或字符串）必须用‘’单引号括起来，作为“定界符”使用。

2）表达单引号本身，需要两个单引号 ' ' ' '  1，4定界2，3表单引号。

3）对于文字值每行输出一次。

eg：显示客户姓名的身份证号是……

select real_name | | ' ' ' s IDCARD NO is ' | | idcard_no | | ' . ' cilent from account;

4）函数转换大小写，尽量在进入数据时操作。

2.9消除重复行

distinct去重复行（对整条记录返回的结果去重，不是对后面的某个列去重），若后面有多列，则所有列联合起来唯一，即每列的值都可以重复，但组合不能重复。

eg1：哪些unix服务器提供远程登录业务

select distinct unix_host from service;

eg2：每一台unix服务器在哪些天开通了远程登录业务

select distinct unix_host,create_date from service;

2.10其他注意事项

1）调常量时用单行单列的dual表，系统提供的表。

2）invalid identifier 无效标识名，列名不。

3）table or view does not exist 表名不对。

三、
SQL语句的处理过程

3.1 SQL语句处理过程

用户进程sqlplus→建立连接→服务进程Server process oracleSID

↑--创建会话--Oracle server

3.2处理一条select语句

1）分析语句：

①搜索是否有相同语句

②用hash value计算select语句是否长得一样：大小写，关键字，空格要都一样，不一样则为两条语句，则服务进程会重新分析。若为统一语句，则直接从内存拿执行计划，计算结果

③检查语法、表名、权限 ④在分析过程中给对象加锁

⑤生成执行计划

2）绑定变量：给变量赋值

3）执行语句：

4）获取数据：将数据返回给用会进程

四、
where子句

用where子句对表里的记录进行过滤，where子句跟在from子句后面。

4.1 where子句后面可以跟什么

跟条件表达式：列名、常量、比较运算符（单、多值运算符）、文字值；不能跟组函数！不能跟列别名！

u 注意事项：对列不经过运算的条件表达式效率会更高，建议在写where子句时尽量不要对列进行运算。

eg：一年的固定费用为70.8元，计算年包在线时长

select base_duration*12 ann_duration from cost where base_cost*12=70.8; 没下面效率高

select base_duration*12 ann_duration from cost where base_cost=70.8/12;

4.2语法和执行顺序

语法顺序：select from where 执行顺序：from where select

4.3字符串是大小写敏感的，在比较时严格区分大小写

1）upper()：函数将字符串转换成大写。

2）lower()：函数将字符串转换成小写。

3）initcap()：函数将字符串转换成首字符大写（是将列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。

eg：哪些unix服务器上开通了os帐号huangr

select unix_host,os_username from service

where os_username = 'huangr';(有结果)

where lower(os_username)='HUANGR';(无结果)

where lower(os_username)='huangr';(有结果)

where upper(os_username)='HUANGR';(有结果)

4.4 where子句后面可以跟多个条件表达式

条件表达式之间用and、or连接，也可用（）改变顺序。

4.5 between and运算符

表示一个范围，是闭区间，含义为大于等于并且小于等于。

eg：哪些资费的月固定费用在5元到10元之间

select base_duration,base_cost,unit_cost from cost

where base_cost >= 5 and base_cost <= 10; where base_cost between 5 and 10;

4.6 in运算符（多值运算符）

表示一个集合，是离散值，含义为等于其中任意一个值，等价于any。

eg：哪些资费的月固定费用是5.9元,8.5元,10.5元

select base_duration,base_cost,unit_cost from cost

where base_cost = 5.9 or base_cost = 8.5 or base_cost = 10.5;

where base_cost in(5.9,8.5,10.5); where base_cost =any(5.9,8.5,10.5);

4.7 like运算符

在字符串比较中，可用like和通配符进行模糊查找。

1）通配符：%表示0或多个字符； _表示任意“一个”字符（要占位的）。

u 注意事项：若要查找%和_本身，则需要escape进行转移。

eg：哪些unix服务器上的os帐号名是以h开头的

select os_username from service where os_username like 'h%'

eg：哪些unix服务器上的os帐号名是以h_开头的

select os_username from service where os_username like 'h\_%' escape '\';

4.8 is null运算符

测试null值需要用is null。

1）null不能用等于号“＝”和不等于号“<>”跟任何值比较，包括它自身。所以不能用“＝”和“<>”来测试是否有空值。

2）即：null=null是不成立的；null不等于 null也不成立；null和任何值比较都不成立。

eg：列出月固定费用是5.9元，8.5元，10.5元或者没有月固定费。

select base_duration,base_cost,unit_cost from cost

where base_cost in (5.9,8.5,10.5,null);(错误)

where base_cost in (5.9,8.5,10.5) or base_cost is null;(正确)

4.9比较和逻辑运算符（单值运算符）

1）比较运算符：=  >  >=    < <=

2）SQL比较运算符：between and、in、like、is null

3）逻辑运算符：and、or、not

4.10多值运算符all、any

1）>all：大于所有的，等价于 >(select max()…)。

2）>any：大于任意的，等价于 >(select min()…)。

4.11运算符的否定形式

1）比较运算符：<> ！=   ^=

2）SQL比较运算符：not between and not in not like  is not null

u 注意事项：

v in相当于=or =or =or等价于any

v not in等价于 <>and <>and <>and等价于<>all

v not between and   小于下界 or 大于上界

v 集合中有null，对in无影响；但对not in有影响，有一个就没有返回值！

 eg：哪些资费信息的月固定费用不是5.9元,8.5元,10.5元

 select base_duration,base_cost,unit_cost from cost

 where nvl(base_cost,0) <> 5.9 and nvl(base_cost,0) <> 8.5 and nvl(base_cost,0) <> 10.5;

 where nvl(base_cost,0) not in (5.9,8.5,10.5);

五、
order by子句

select语句输出的结果安记录在表中的存储顺序显示，order by子句能够改变记录的输出顺序。

order by子句对查询出来的结果集进行排序，即对select子句的计算结果排序。

5.1语法和执行顺序

语法顺序：select from where order by 执行顺序：from where select order by

5.2升降序

ASC－升序，可以省略，默认值 DESC－降序

order by nvl(base_cost,0); order by unix_host,create_date desc;

u 注意事项：order by是select语句中最后一个子句

5.3 null值在排序中显示

1）被排序的列如果包含null值，用ASC方式null值的在最后；

2）用DESC方式null在最前面；

5.4 order by后面可以跟什么

可以跟列名、列别名、列位置（数字）、表达式、函数。

order by 1：表示列位置为1的列

select 1 from：表示常量1

eg：按年固定费用从大到小的顺序显示资费信息

 方式一： select id,base_cost*12 ann_cost,base_duration ann_duration from cost

       order by base_cost desc;

 方式二： select id,base_cost ann_cost,base_duration ann_duration from cost

      order by base_cost*12 desc;排序的效果和上面是一样的，但前一个效率高。

5.5多列排序

order by子句后面可以跟多列，而order by后面的列可以不出现在select后面。结果集先按第一列升序排列，若列值一样，再按第二列降序排列。

eg：按unix服务器ip地址升序，开通时间降序显示业务帐号信息

select id,unix_host,os_username,create_date from service

order by unix_host,create_date desc;

六、
单行函数的使用

SQL函数的两种类型：单行函数、多行函数（组函数）。

单行函数：数值类型、日期类型、字符类型、转换函数。处理一列数据，返回一个结果。

6.1数值类型

1）定义：create table tabname

( c1  number,c2  number(6),c3  number(4,3),c4  number(3,-3),c5  number(2,4) );

2）数值类型说明

①number：不写数值，表可写38位数

②number(6)：6位整数 999999.1 999999

③number(4,3)：数字4位，小数点占3位，四舍五入 1.234567 1.235

④number(3,-3)：小数点前三位不写数，四舍五入，然后有效位3位 1234    1000

⑤number(2,4)：小数点后4位，有效位2位 0.00991 0.0099

3）数值函数：参数类型为number

①round()：四舍五入函数，“缺省转成数字”；也可对日期

②trunc()：截取函数（不管多大值直接舍去）；也可对日期

eg：round和trunc

    round(45.923，2)：45.92 round(45.923，0)：46 round(45.923，-1)：50

    trunc(45.923，2)：45.92 trunc(45.923)：45 trunc(45.923，-1)：40

6.2日期类型

1）Oracle用7个字节来存储日期和时间：世纪、年、月、日、时、分、秒。Date不存在定宽度，就是7个字节。

2）缺省（默认）日期格式为DD-MON-RR，格式敏感。

3）sysdate是一个系统函数，返回当前系统时间和日期。

4）改变session（会话）中的日期格式：session和connection是同时建立的，两者是对同一件事情的不同层次的描述。connection是物理上的客户机同服务器端的通信链路；session是逻辑上的用户同服务器的通信交互，SQL语句的运行环境。

eg：显示的日期包含世纪、年、月、日、时、分、秒

    alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';

5）日期格式

yyyy	用数字表达的四位年（2013年）
mm	用数字表达的两位月（01月）
dd	用数字表达的两月日（01日）
hh24	用数字表达的24进制的小时（20点）
h12	用数字表达的12进制的小时（8点）
mi	用数字表达的分钟（30分）
ss	用数字表达的小时（30秒）
D	用数字表达的一周内的第几天（周日：1）
day	用全拼表达的星期几（sunday）
month	用全拼表达的月（march）
mon	用简拼表达的月（mar）

eg：案例

select to_char(sysdate,'DDD') from dual;年中的第几天

select to_char(sysdate,'DD') from dual;月中的第几天

select to_char(sysdate,'D') from dual;星期中的第几天

6）在数据库中如何处理日期类型

    create table test(c1 date);

    insert into test values ('01-JAN-08');

    insert into test values ('2008-08-08');（报错）

    insert into test values (to_date('2008-08-08','yyyy-mm-dd'));

    select c1 from test;

    select to_char(c1,'yyyy-mm-dd') from test;

在create table中定义日期类型date时一定不能指定宽度。日期在数据库中用固定的7个字节存储，表示世纪、年、月、日、时、分、秒。缺省的日期格式为'DD-MON-RR'，'01-JAN-08'符合缺省日期格式可以插入表中，因为系统会自动调用to_date函数将它转成日期。 '2008-08-08'插入时报错，原因是不符合缺省格式，需要手工使用函数to_date对字符串的格式进行说明，如'2008-08-08'的格式说明串为'yyyy-mm-dd'。select时日期按缺省日期格式显示，若用指定日期格式，需要使用to_char函数。

7）日期与字符串相互转换：

to_date(char,date)函数：将字符串转换成一个日期值。对应java中parse。

to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。

eg：创建一张表，包含date类型的列，插入2008年8月8日8点8分8秒并显示。

    insert into test values (to_date('2008-08-08 08:08:08','yyyy-mm-dd hh24:mi:ss'));

    select to_char(c1,'yyyy-mm-dd hh24:mi:ss') from test;

u 注意事项：

v 格式必须用单引号括起来，并且大小写敏感。

v 必须是有效的日期格式。

v fm能去掉前导0和两端的空格。

v 对日期去重复问题，to_char获取当天日期即可，时分秒忽略，加上distinct即可做到

eg：案例

    where to_char(create_date,'mm')='03';

    若等式右边写成‘3’，‘03’ =‘3’不成立，需要在‘mm’前增加‘fm’。

    where to_char(create_date,'fmmm')='3'

    where to_number(to_char(create_date,'mm')) = 3;

    若等式右边写成3，‘03’= 3成立，‘03’是字符类型，3是数字类型，等式两边相等，说明系统做了隐式数据转换，缺省做法将字符转化为number。

8）日期函数：参数类型为date

①add_months()：一个日期加、减一个月。

②months_between()：两个日期之间相差多少个月。

③last_day()：同一个月的最后一天 ④next_day()：根据参数，出现下一个的日期。

eg1：昨天，今天，明天

    alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';

    select sysdate-1,sysdate,sysdate+1 from dual;

eg2：十分钟之后

    alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';

    select sysdate,sysdate + 1/144 from dual;

eg3：每台unix服务器上的os帐号开通了多长时间（以天为单位）

    select unix_host,os_username,create_date,round(sysdate - create_date) days from service;

eg4：上个月的今天，今天，下个月的今天

    alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';

    select add_months(sysdate,-1),sysdate,add_months(sysdate,1) from dual;

eg5：当前月的最后一天

    alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';

    select last_day(sysdate) from dual;

eg6：用户注册多长时间了

    select trunc(months_between(sysdate,create_date))from service;

6.3字符类型

1）定义：create table tabname

( c1 char(10) , c2 varchar2(10) );

2）char和varchar2区别：

①varchar2必须定义长度，按字符串的实际长度存，最大长度4000字节，更省空间。

②char可以不定义长度，默认为1，按定义长度存，最大长度2000字节，操作更快。

③列的取值是定长，定义成char类型。

④列的取值长度不固定，定义成varchar2。

u 注意事项：

v 在字符串比较中，varchar2按实际字符串比，对空格是敏感的，对大小些敏感。

v char会将短字符串补齐后，再与字符串比，对空格不敏感。

v varchar类型是ANSI定义的，varchar2类型是Oracle定义的，目前是等价的。但如果ANSI对varchar类型定义有变化，则Oracle varchar2类型不变。

eg：案例

     varchar2(10)：'abc'='abc'      yes  ;  'abc'='abc '           no

 char(10)：'abc       '='abc'   yes  ; 'abc       '='abc '   yes

3）字符函数：参数类型为字符

①upper()：函数将字符串转换成大写。②lower()：函数将字符串转换成小写。

③initcap()：函数将字符串转换成首字符大写（是将列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。

④length()：字符串的长度。⑤rpad()、lpad()：将字符补成同样长度，l和r表左右。

⑥rtrim()、ltrim()：压缩字符，l和r表左右，与fm相同效果。

⑦concat()：拼接函数与“| |”相似。⑧substr()：求子串函数。

eg：相关操作

    select rpad('FEBRARY',9, '*') from dual;

    where to_char(create_date,'fmMONTH')='MARCH';

    where rtrim(to_char(create_date,'MONTH'))='MARCH';

    select concat('ab ','c')from dual;     ↓从左往右       ↓从右往左

    select os_username,substr(os_username,1,2),substr(os_username,-2,2)from service;     

6.4转换函数

1）to_number()函数：将字符（串）转换成number数值类型，这也是系统的缺省做法，即to_number('03')=3。

u 注意事项：若to_number函数处理的字符串为'ab'，则系统报错，若转换后的值是十进制的，则要求字符串必须是数字字符。

select to_number('ab') from dual;（报错invalid number）

2）to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。

3）函数格式说明：

9	代表数字位
0	定义宽度大于实际宽度时，0会被强制显示在前面，以补齐位数
$	美元符号
L	本地货币符号
．	小数点
，	每千位显示一个逗号

u 注意事项：如果显示位数不足（定义宽度小于实际宽度），用#代替。

eg1：相关操作

select to_char(base_cost,'L99.99') from cost;

select to_char(base_cost,'L00.00') from cost;

select to_char(base_cost,'$00.00') from cost;

eg2：显示月固定费用，单位费用，单位费用为null，显示no unit cost

select base_cost,nvl(to_char(unit_cost),'no unit cost') unit_cost

3）to_date(char,date)函数：将字符串转换成一个日期值。对应java中parse。

4）number、字符、data间的转化

①to_char()：number->字符    date->字符

②to_number()：字符-> number

③to_date()：字符->date

5）显式隐式转换

①隐式数据类型转换，系统调用转换函数

     where  create_date like '%3%';隐式

②显式数据类型转换，用户调用转换函数

     where  to_char(create_date,'mm')='03';显式

6.5其他注意事项

1）insert into 表名 values(1，2，3，4，null)有多列时，插入值必须都写，没值的也要写null

2）insert into 表名(C5) values(1234) 表名最多30个字符且不能有特殊字符

3）alter session set nls_language='AMERICAN';28-MAY-13

   alter session set nls_language='SIMPLIFIED CHINESE';28-5月 -13

4）alter session set nls_territory = 'AMERICA';

   alter session set nls_territory = 'CHINA';

七、
SQL语句中的分支

7.1分支表达式

1）case when (then)，用于解决不同记录需要不同处理方式的问题。when后面跟条件表达式，当所有when条件都不满足时，若有else，表达式的返回结果为其后的值，否则返回null值。

2）寻找when的优先级：从上到下再多的when，也只有一个出口，即其中有一个满足了表达式expr就马上退出case。

3）else expr和return expr的数据类型必须相同。

eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用CASE WHEN实现）

select base_duration,unit_cost,case when base_duration=20 then unit_cost+0.05

                                 when base_duration=40 then unit_cost+0.03

                             else unit_cost

                             end new_nuit_cost

from cost;

7.2分支函数

decode，是简版的case when。

1）decode(value,if1,then1,if2,then2,……,else)标识如果value等于if1时，返回then1。如果不等于任何一个if值，则返回else。

eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用decode实现）

select base_duration,unit_cost,decode(base_duration,20,unit_cost+0.05,

                                           40,unit_cost+0.03,

                                unit_cost) n_base_cost

from cost;

八、
组函数

操作在一组行（记录）上，每组返回一个结果。

8.1报表统计常用

1）avg(distinct|all|n)：平均值，参数类型只能为number。

2）sum(distinct|all|n)：求和，参数类型只能为number。

3）count(distinct|all|expr|*)：计数，参数类型为number、字符、date。

4）max(distinct|all|expr)：最大值，参数类型为number、字符、date。

5）min(distinct|all|expr)：最小值 ，参数类型为number、字符、date。

u 注意事项：

v distinct去重复时，会保留一个。

select count(distinct base_duration)from cost;//4，distinct保留一个空，但count统计时不算

v count（*）不管null，统计“记录”数。

v count（列名）返回的是列中非null值的数量。

8.2缺省情况组函数处理什么值

所有的非空值。

8.3当组函数要处理的所有值都为null时

count函数返回0，其他函数返回null。

8.4行级信息和组级信息

返回的结果集包含多条记录，是行级信息；返回的结果集包含一条记录，是统计汇总信息，是组级别的信息；两者不能同时显示出来！

处理方式：将行级信息变成组标识或进行组函数处理。

eg1：单位费用的总和、平均值、最大值、最小值个数

select sum(unit_cost) sum1,avg(unit_cost) avg1,max(unit_cost) max1,

     min(unit_cost) min1,count(unit_cost) cnt from cost;

eg2：若null值参与运算，必须将null值转换成非null值

select avg(nvl(unit_cost,0)),sum(unit_cost)/count(*) from cost;

eg3：若unit_cost列中参与运算的数据都为null，avg(unit_cost)的函数值为null，count(unit_cost)的函数值为0。

select avg(unit_cost),count(unit_cost) from cost where unit_cost is null;

eg4：每台unix服务器上开通的os帐号数即开户数？

select unix_host,count(os_username) from service group by unix_host;

eg5：tarena26（192.168.0.26）上开通的os帐号数即开户数？

select max(unix_host),count(os_username) cnt from service where unix_host = '192.168.0.26';         用min(unix_host)也可

九、
group by子句

将表中的记录进行分组

9.1语法和执行顺序

语法顺序：select from where group by order by  

执行顺序：from where group by select order by

9.2分组过程

根据group by子句指定的表达式，将要处理的数据分成若干组（若有where子句即为通过条件过滤后的数据）。每组有唯一的组标识，组内有若干条记录，根据select后面的组函数对每组的记录进行计算，每组对应一个返回值。

9.3常见错误

若没有group by子句，select后面有一个是组函数，则其他都必须是组函数（记录（行）信息和组信息不能放一起，要么都是组函数，要么都是单行函数）。

若有group by子句，select后面跟group by后面跟的表达式以及组函数，其他会报错。

9.4多列分组

包含多列用“，”分开，分组的个数多了，每组的记录少了。

eg：根据unix服务器ip地址、开通时间统计开通的os帐号数即开户数

select unix_host,to_char(create_date,'yyyymmdd') create_date,count(os_username) cnt

from service

group by unix_host,to_char(create_date,'yyyymmdd');

 

十、
having子句

对分组过滤。

10.1语法和执行顺序

语法顺序：select from where group by having order by

执行顺序：from where group by having select order by

10.2执行过程

行被分组，将having子句的条件应用在每个分组上，只有符合having条件的组被保留，再应用select后面的组函数对每组的数据进行处理。

10.3 where和having区别

1）where：过滤的是行（记录），后面可跟任意列名，单行函数，不能跟组函数（无法对应到具体记录），先执行，不允许用列别名。

2）having：过滤的是分组（组标识、每组数据的聚合结果），后面只能包含group by后面的表达式和组函数（能表达组信息的），后执行，不允许用列别名。

eg1：哪些unix服务器开通的os帐号数即开户数多于2个

select unix_host,count(os_username) cnt from service

group by unix_host

having count(os_username) > 2;

eg2：哪些unix服务器在哪几天的开户数多于1个

select unix_host,to_char(create_date,'yyyymmdd') create_date,count(os_username) cnt

from service

group by unix_host,to_char(create_date,'yyyymmdd')

having count(os_username) > 1;

 

十一、
非关联子查询

子查询就是在一条SQL（DDL、DML、TCL、DQL、DCL）语句中嵌入select语句。

11.1语法

select colname,… from tabname where expr operator(select colname2 from subtabname);

11.2子查询的执行过程

先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。子查询只执行一遍。若子查询的返回结果为多个值，Oracle会自动去掉重复值后，再将结果返回给主查询。

注意事项：不需要distinct，会自动去重的。

eg1：哪些os帐号的开通时间是最早的

select unix_host,os_username,create_date from service

where create_date = (select min(create_date) from service);

eg2：哪些os帐号的开通时间比unix服务器192.168.0.26上的huangr晚

select unix_host,create_date,os_username from service

where create_date > ( select create_date from service

                  where os_username = 'huangr'   and unix_host = '192.168.0.26');

eg3：哪些os帐号的开通时间比huangr晚？（多台unix服务器上都有名为huangr的os帐号）

select unix_host,create_date,os_username from service

where create_date > all (select create_date from service

          where os_username = 'huangr');大于所有的

where create_date > (select max(create_date) from service

    where os_username = 'huangr');大于最大的

where create_date > any (select create_datefrom service

                  where os_username = 'huangr');大于任意一个

where create_date > (select min(create_date) from service

                     where os_username = 'huangr');大于最小的

11.3常见错误

单行子查询返回多条记录！此时要注意运算符的选择：

1）若子查询的返回结果仅为一个值，可用单值运算符，如“=”号。

2）若子查询的返回结果可能为多个值，必须用多值运算符，如in等。

eg：哪些客户是推荐人

select real_name from account

where id in (select recommender_id from account);

11.4子查询与空值

若子查询的返回结果中包含空值null，并且运算为not in，那么整个查询不会返回任何行。not in等价于<>all，任何值跟null比（包括null本身），结果都不为true。

eg：哪些客户不是推荐人

select real_name from account

where id not in (select recommender_id from account where recommender_id is not null);

11.5多列子查询

where子句后面可以跟多列条件表达式。

eg1：哪些os帐号的开通时间是所在unix服务器上最早的？（每台unix服务器上最早开通的os帐号）

select unix_host,os_username,create_date from service

where (unix_host,create_date)  in (select unix_host,min(create_date) from service

             group by unix_host);

eg2：哪些os帐号的开通时间比所在unix服务器上最早开通时间晚九天

select unix_host,os_username,create_date from service

where (unix_host,to_char(create_date,'yyyymmdd'))    in

                 (select unix_host,to_char(min(create_date) + 9,'yyyymmdd') from service

        group by unix_host);

十二、
关联子查询

关联子查询采用的是循环（loop）的方式。

12.1语法

select column1,…from table1 o where column1 operator

(select column1,column2 from table2 i where i.expr1=o.expr2);

12.2执行过程

1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表查询。

2）内部查询基于传入的值执行。

3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成处理，若符合条件，outer表中得到的那条记录就放入结果集中，否则放弃。

4）重复执行1－3，直到把outer表中的所有记录判断一遍。子查询执行n遍。

eg:哪些os帐号的开通天数比同一台unix服务器上的平均开通天数长。

select unix_host,os_username,create_date,round(sysdate - create_date)open_age

from service o

where round(sysdate - create_date) > (selectavg(round(sysdate - create_date)) from service i

                                               where o.unix_host = i.unix_host);

12.3 exists

exists采用的是循环（loop）的方式，判断outer表中是否存在在inner表中找到的一条匹配的记录。

12.4 exists执行过程

1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表进行查询。

2）对inner表中的的记录依次扫描，若根据条件，存在一条记录与outer表中的记录匹配，则立即停止扫描，返回true，将outer表中的记录放入结果集中；若扫描了全部记录，没有任何一条记录符合匹配条件，则返回false，outer表中的该记录被过滤掉，不能出现在结果集中。

3）重复执行1－2，直到把outer表中的所有记录判断一遍。

eg1：哪些客户是推荐人

select real_name from account o where exists (select 1 from account i

                                            where o.id = i.recommender_id);

                   //1可随便写，不关心结果什么样，只关心是否有满足的条件返回

eg2：哪些客户申请了远程登录业务

非关联子查询：

select real_name from account where id in (select account_id from service);

关联子查询：

select real_name from account o where exists (select 1 from service i

     where o.id = i.account_id);

12.5 not exists

采用的是循环（loop）的方式，判断outer表中是否不存在记录（它能在inner表中找到匹配的记录）。

12.6 not exists执行过程

1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表进行查询。

2）对inner表中的的记录依次扫描，若根据条件，存在一条记录与outer表中的记录匹配，则立即停止扫描，返回false，将outer表中的记录过滤掉，不能出现在结果集中；若扫描了全部记录，没有任何一条记录符合匹配条件，则返回true，outer表中的该记录放入结果集中。

3）重复执行1－2，直到把outer表中的所有记录判断一遍。

eg1：哪些客户不是推荐人

select real_name from account o where not exists (select 1 from account i

   where o.id = i.recommender_id);

eg2：哪些客户没有申请远程登录业务

非关联子查询：

select real_name from account where id not in (select account_id from service);

关联子查询：

select real_name from account o where not exists (select 1 from service i

where o.id = i.account_id);

12.7 in和exists比较

1）exists是用循环（loop）的方式，有outer表的记录数决定循环次数，对于exists影响最大，所以，外表的记录数要少。

2）in先执行子查询，子查询的结果去重之后，再执行主查询，所以，子查询的返回结果越少，越适合用该方式。

十三、
多表查询

结果集中的记录保存在多张表中。

13.1按范式要求设计表结构

第二范式：每个非主属性必须完全依赖于主属性（主键pk列）（避免多对多合表造成数据冗余）。

第三范式：每个非主属性不能依赖于另一个非主属性（避免一对多合表造成数据冗余，不一致）。

13.2多表连接的种类

交叉连接（cross join）、内连接（inner join）、外连接（outer join）。

13.3交叉连接

数学中的组合问题。

1）假设table1表中有m条记录，table2表中有n条记录，交叉连接产生的结果集为m*n。该结果产生的结果集为笛卡尔积。

2）语法：

select tabname1.colname1,tabname2.colname2 from tabname1 cross join tabname2;

eg：案例

select a.real_name,a.id,s.account_id,s.unix_host,s.os_username

from account a cross join service s;

13.4内连接

核心解决匹配问题，建议用on and  and多条件组合，不用where。

1）语法：

select tabname1.colname1,tabname2.colname2 from tabname1 join tabname2

on tabname1.colname1=tabname2.colname2 and 其他条件;

2）如果有多个条件表达式，on关键字后面跟一个，其余用and条件连接。

eg：客户huangrong在哪些unix服务器上申请了远程登录业务

    select a.real_name,s.unix_host,s.os_username,s.create_date

    from account a join service s on a.id = s.account_id and a.real_name = 'huangrong';

3）内连接原理一：

t1和t2表作内连接，连接条件为on t1.c1=t2.c2，假设t1表作驱动表，t2表作匹配表，记录过程如下：

①从t1表中读取一条记r1，若它的列c1值为1

②根据该值到t2表中查找匹配的记录，即需要遍历t2表，从t2表中的第一条记录开始，若查找的记录的c2列的值为1，我们就说这两条记录能够匹配上，那么t1的r1和t2中刚刚匹配的该条记录组合起来，作为结果集里的一条记录，否则检测t2表中的下一条记录。

③按照步骤2依次将t2表中所有的记录检测一遍，只要匹配就放入结果集中。

④从t1表中读取第二条记录，依次重复步骤2和3，产生最终的结果集。

eg：列出申请了远程登录业务的客户姓名以及在unix服务器上的开通信息

    select a.real_name,s.unix_host,s.os_username,s.create_date

    from account a join service s on a.id = s.account_id;

4）内连接原理二：

t1和t2表作内连接，连接条件为on t1.c1=t2.c2，假设t1表作驱动表，t2表作匹配表，记录的匹配有如下三种情况：

①t1表中的某条记录在t2表中找不到任何一条匹配的记录，那么t1表中的该条记录不会出现在结果集中。

②t1表中的某条记录在t2表中只有一条匹配的记录，那么t1表中的该记录和t2表中匹配的记录组合成新的记录出现在结果集中。

③t1表中的某条记录在t2表中有多条匹配的记录，那么t1表中的该记录会和t2表中每一条匹配的记录组合成新的记录出现在结果集中。

u 注意事项：内连接的核心为：任何一张表里的记录一定要在另一张表中找到匹配的结果，否则不能出现在结果集中。

5）内连接原理三：

t1和t2表作内连接，连接条件为on t1.c1=t2.c2，以下两种方式都可以得到相同的结果集：

①一种t1作驱动表，t2作匹配表

②一种t2作驱动表，t1作匹配表

③无论那种方式，最终得到的结果集都一样，所不同的是效率。

6）内连接的结果集结构：

t1.c1 t1.c2 t1.c3 …… t2.c1 t2.c2 t2.c3

7）内连接的语句执行顺序：

先根据on和and条件对要连接的表进行过滤，将过滤后的结果集进行内连接操作（join on），再根据select语句的定义生成最终的结果集。

注意事项：内连接中使用on和where都可以。

8）from后面可跟子查询

eg1：列出客户姓名以及开通的远程登录业务的数量

方式一：先连接再统计

    select a.id,max(a.real_name),count(a.id)

    from account a join service s on a.id = s.account_id

    group by a.id;

方式二：先统计再连接，效率更高。

    select a.real_name,count(a.id)

    from account a join (select account_id count(id) cnt from service group by account_id) c

         on a.id = c.account_id

eg2：列出客户姓名以及他的推荐人（考查了内连接、空值转换、decode）

    select a1.real_name recommended,decode(a2.id,a1.id,'No Recommender',a2.real_name)

    recommender from account a1 join account a2

                             on nvl(a1.recommender_id,a1.id) = a2.id;

9）自连接：

①同一张表的行（记录）之间的匹配关系可以用同一张表的列之间的条件表达式描述。

②通过给表起别名，将同一张表的列之间的关系转换成不同表的列之间的条件表达式。

eg：哪些客户是推荐人

 select distinct a2.id,a2.real_name from account a1 join account a2 on a1.recommender_id = a2.id;

10）其他案例

eg1：显示客户姓名，开通的远程登录业务的数量。(结果集中只包含开通了远程登录业务的客户)

    select t1.real_name,t2.cnt from account t1 join  

    (select account_id,count(*) cnt from service group by account_id) t2

                               on t2.account_id=t1.id;

u 注意事项：count(*)已经到了不得不起别名的地步，组函数不可作与单行函数在一起显示的。

eg2：显示客户姓名，开通的远程登录业务的数量。(结果集中只包含开通了远程登录业务的客户)

    select min(a.real_name),count(s.account_id)

    from account a join service s on a.id=s.account_id group by a.id;  

u 注意事项：

v eg1比eg2的效率高！两个表出统计结果时，一个表就能出来结果的就先单表统计再连接。否则，就先连接再统计！！！！

v in（非关联子查询） exists（关联子查询） join（表查询） 都是在解决匹配问题。

v 匹配是记录和记录的匹配，是逻辑上的匹配，不一定非要是物理上独立个体的匹配，也可在一个表的记录间相互匹配。

13.5外连接

作用：①把匹配和不匹配的都找出来。②只找不匹配的，匹配的交给内连接作。

1）语法：left right full定驱动表的

from t1 left （outer） join t2 on t1.c1=t2.c2   outer 可省 左表为驱动表

from t1 right（outer） join t2 on t1.c1=t2.c2 右表为驱动表

from t1 full （outer） join t2 on t1.c1=t2.c2 左右表都为驱动表

2）外连接原理一：

t1和t2表作外连接，连接条件为from t1 left outer join t2 on t1.c1=t2.c2，t1表必须作驱动表，t2表作匹配表，记录的匹配过程如下：

①从t1表中读取一条记r1，若它的列c1值为1

②根据该值到t2表中查找匹配的记录，即需要遍历t2表，从t2表中的第一条记录开始，若查找的记录的c2列的值为1，我们就说这两条记录能够匹配上，那么t1的r1和t2中刚刚匹配的该条记录组合起来，作为结果集里的一条记录，否则检测t2表中的下一条记录。

③按照步骤2依次将t2表中所有的记录检测一遍，只要匹配就放入结果集中。若扫描完后，t1的r1记录在t2表中找不到任何匹配的记录，t2表中模拟一条null记录与t1表中的r1组合起来，放入结果集中。

④从t1表中读取第二条记录，依次重复步骤2和3，产生最终的结果集。

3）外连接原理二：

t1和t2表作外连接，连接条件为from t1 left outer join t2 on t1.c1=t2.c2，t1表必须作驱动表，t2表作匹配表：

①外连接的结果集＝内连接结果集+t1表中匹配不上的记录和一条null记录（按t2表的结构）组成的记录的组合。

②外连接的核心可以将t1中匹配不上的记录（按on条件在t2中找不到对应的匹配记录）也显示出来，而不像内连接直接过滤掉，即t1中的记录一个都不少的出现在结果集中。

③外连接结果集的记录数不一定是驱动表的记录数（结果集记录数>=驱动表记录数）。

4）外连接原理三：

t1和t2表作外连接，连接条件为from t1 right outer join t2 on t1.c1=t2.c2，t2表必须作驱动表，t1表作匹配表：

①外连接的结果集＝内连接结果集+t2表中匹配不上的记录和一条null记录（按t1表的结构）组成的记录的组合。

②外连接的核心可以将t2中匹配不上的记录（按on条件在t1中找不到对应的匹配记录）也显示出来，而不像内连接直接过滤掉，即t2中的记录一个都不少的出现在结果集中。

③外连接结果集的记录数不一定是驱动表的记录数（结果集记录数>=驱动表记录数）。

5）外连接原理四：

t1和t2表作外连接，连接条件为from t1 full outer join t2 on t1.c1=t2.c2，t1表必须作驱动表，t2表作匹配表：

①外连接的结果集＝内连接结果集+t1表中匹配不上的记录和一条null记录（按t2表的结构）组成的记录+t2表中匹配不上的记录和一条null记录（按t1表的结构）组成的记录的组合。

②外连接结果集的记录数不一定是t1表和t2表的记录数之和。

eg1：列出客户姓名以及他的推荐人

select a1.real_name customer,nvl(a2.real_name,'No Recommender') recommender

from account a1 left join account a2 on a1.recommender_id = a2.id;

eg2：列出客户姓名以及所开通的远程登录业务的信息 (没有申请远程登录业务的客户也要出现在结果集中)

select a.id,a.real_name,s.unix_host,s.os_username from account a left join service s

     on a.id = s.account_id;

eg3：哪些客户不是推荐人

select a1.real_name recommender

from account a1 left join account a2 on a1.id = a2.recommender_id

where a2.id is null;

6）外连接语句的执行顺序

若on子句后面有and条件，则现对匹配表进行过滤，然后再进行外连接（join on），再对外连接的结果集用where子句进行过滤，最后用select语句生成最终的结果集。on和where后面都可以跟多个条件表达式，表达式之间用and连接

eg：哪些UNIX服务器上没有os帐号weixb

    select h.id,h.name,h.location

    from host h left join service s on h.id=s.unix_host and s.os_username='weixb'

    where s.id is null;

①先过滤service表，用s.os_username='weixb'

②过滤后的结果集作匹配表，host表作驱动表，进行外连接，用where对外连接的结果集进行过滤，产生最终结果。

u 注意事项：

v 驱动表和匹配表的关系，也就是指驱动表中的记录和匹配表中的记录的关系，通过on联系；要想统计出正确的数量count，必须统计匹配表的“非空列”！

v 对内连接and、where用谁都行，但外连接则有严格的使用位置。

v 过滤驱动表一定用where子句。

13.6非等值连接

不同表没有共同属性的列，但两张表的列可以写成一个SQL条件表达式。

eg1：显示客户的年龄段

select t1.real_name,round((sysdate-t1.birthdate)/365) age,t2.name

from account t1 join age_segment t2 on round((sysdate-t1.birthdate)/365)

                                              between t2.lowage and t2.hiage;

eg2：显示客户huangrong的年龄段

select t1.real_name,round((sysdate-t1.birthdate)/365) age,t2.name

from account t1 join age_segment t2 on round((sysdate-t1.birthdate)/365)

                                              between t2.lowage and t2.hiage

  and real_name='huangrong';

eg3：显示青年年龄段中的客户数

select t1.real_name,round((sysdate-t1.birthdate)/365) age,t2.name

from account t1 join age_segment t2 on round((sysdate-t1.birthdate)/365)

                                              between t2.lowage and t2.hiage

                                              and t2.name like'青年%';

eg4：显示各个年龄段的客户数（没有客户的年龄段的客户数为0）

select max(t2.name),count(t1.id)

from account t1 right join age_segment t2 on round((sysdate-t1.birthdate)/365)

                                              between t2.lowage and t2.hiage

group by t2.id;搞清楚为何用t1.id统计（思考连接过程）；

            若没有客户的年龄段不用出现在结果集中则采用内连接。

13.7表连接总结

1）内连接，解决匹配问题

①等值连接：on子句后有等值条件。

②非等值连接：不同表没有共同属性的列，但两张表的列可以写成一个SQL条件表达式。

③自连接：同一张表，通过起别名，表达列之间的关系。

2）外连接，解决不匹配问题和表中所有记录出现在结果集

①等值连接：on子句后有等值条件。

②非等值连接：不同表没有共同属性的列，但两张表的列可以写成一个SQL条件表达式。

③自连接：同一张表，通过起别名，表达列之间的关系。

3）交叉连接，笛卡尔积

十四、
集合

14.1表连接主要解决的问题

1）两张表记录之间的匹配问题。

2）两张表记录之间的不匹配问题。

3）匹配问题+不匹配问题。

14.2集合运算

1）若将两张表看成集合，匹配问题就是集合运算中的交集。

2）若将两张表看成集合，不匹配问题就是集合运算中的差。

3）匹配问题+不匹配问题就是集合运算中的并集。

14.3集合运算符

1）union：结果集为两个查询结果的并集，是去掉重复值的，最后有自动升序。

2）union all：结果集为两个查询结果的并集，是包含重复值的，输出效果为记录升序。

3）tersect：结果集为两个查询结果的交集，不包含重复值。

4）minus：结果集为属于第一个查询的结果集，但不属于第二个查询的结果集，即从第一个查询的结果集中减去他们的交集，不包含重复值；A-B=C，A为被减数，B为减数，C为差；从A中减去和B中相同的部分。

u 注意事项：集合运算要求两个select语句是同构的，即列的个数和数据类型必须一致。

eg1：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用union all实现）

    select base_duration,unit_cost+0.05 from cost where base_duration=20

    union all

    select base_duration,unit_cost+0.03 from cost where base_duration=40

    union all

    select base_duration,unit_cost from cost where base_duration not in(20,40)

  or base_duration is null;效率低，换成case when较好

eg2：列出客户姓名以及他的推荐人

select t2.real_name,t1.real_name from account t1 join account t2 on t1.id=t2.recommender_id

union all

select real_name,'No recommender' from account where recommender_id is null;

eg3：sun280和sun-server上的远程登录业务使用了哪些相同的资费标准

 方式一：select name from cost where id in(

                  select cost_id

      from host h join service s on h.id=s.unix_host and h.name='sun280'

     intersect

  select cost_id

  from host h join service s on h.id=s.unix_host and h.name='sun-server');

方式二：select name from cost where id in(

                  select cost_id

  from service s where s.unix_host in (

  select id from host where name='sun280')

  intersect

  select cost_id

  from host h join service s on h.id=s.unix_host and h.name='sun-server');

eg4：哪台UNIX服务器上没有开通远程登录业务

    select id from host minus select unix_host from servce;

14.4子查询、连接、集合总结

1）匹配问题：in、exists、inner join、intersect

2）不匹配问题：not in、not exists、(outer join+where匹配表非空列 is null)、minus

3）匹配+不匹配问题：outer join、union、union all

十五、
排名分页问题

15.1什么是rownum

rownum是一个伪列，对查询返回的行编号即行号，由1开始依次递增。

u 注意事项：关键点：Oracle的rownum数值是在获取每行之后才赋予的！

15.2 where rownum<=5的执行过程

1）Oracle获取第一个符合条件的1行，将它叫做第一行。

2）有5行了吗？如果没有，Oracle就再返回行，因为它要满足行号小于等于5的条件。如果到了5行，那么Oracle就不再返回行。

3）Oracle获取下一行，并递增行号（从2到3再到4再到5…）。

4）返回到第2步。

15.3 where rownum=5的执行过程

1）由于Oracle没有获取到第一个符合条件的1行，即第一行。

2）所以Oracle无法获取下一行，即无法从编号为1的第一行开始递增行号（从2到3再到4再到5…）。

3）最终结果为空。

eg1：找出帐务信息表的前三条记录

select rownum,id,real_name,create_date from account where rownum <= 3;

eg2：找出帐务信息表的第四条到第六条记录？

select rn,real_name from (select rownum rn,real_name from account where rownum<=6)

where rn>=4;

u 注意事项：此时rownum必须有别名，否则结果将按照子查询后的表进行伪列查找，结果就为空了。

eg3：最晚开通系统的前三个客户？

select rownum,real_name,create_date from (select real_name,create_date from account

               order by create_date desc)

where rownum<=3;

u 注意事项：要先排序后过滤，注意where、order by的执行顺序。

eg4：最晚开通系统的第四到第六名客户？

select rn,real_name,create_date from (select rownum rn,real_name,create_date

         from (select real_name,create_date  from account

              order by create_date desc)

         where rownum<=6)

where rn>=4;

十六、
约束constraint

面临的问题：某列必须有值而且唯一；某列的取值受到另一列的限制。

数据库提供的解决方法：限制无效的数据进入到表中；数据库层面的“安检”。

16.1约束的类型

primary key、not null、unique key、references foreign key、check

16.2 primary key：主键约束

一张表只能有一个主键约束，其他约束没有这个限制！

1）创建主键约束

①列级约束：

create table test(

c1 number(2) constraint test_c1_pk primary key,

c2 number);

②表级约束：

create table test(

c1 number(2),

c2 number,

constraint test_c1_pk primary key(c1));

2）主键约束解决的问题是：不允许表中有null记录，不允许表中有重复的记录

3）增加删除操作

①增加一列：alter table test add(同建表时的列定义方法，表级约束语法);

②删除一列：alter table test drop(列名);

③删除约束：alter table test drop constraint 约束名;

④删除主键约束：alter table test drop primary key;

⑤增加主键约束：alter table test add constraint 约束名 primary key(列名);

u 注意事项：

v 约束一定要有名字，自己不写，系统也会加上的“SYS_C+一堆数字”。

v 约束名是在同一用户下的，因此不能有相同的名称，建议“表名_列名_约束类型”。

16.3 not null：非空约束

不允许将该列的值置为空，只有列级约束形式。

1）创建非空约束

create table test(

c1 number constraint test_c1_pk primary key,

c2 number not null);

2）修改非空约束：

alter table test modify(c1 null);not null->null

alter table test modify(c1 default 1 not null);null->not null 默认、缺省值为1

16.4 unique key：唯一建约束

一张表可有多个唯一建约束！

1）创建唯一建约束

①列级约束：

create table test(

c1 number constraint test_c1_pk primary key,

c2 number constraint test_c2_uk unique,

c3 number constraint test_c3_uk unique);注意，unique后没有key单词

②表级约束：

create table test(

c1 number constraint test_c1_pk primary key,

c2 number ,

c3 number ,

constraint test_c3_uk unique(c2,c3));

u 注意事项：

v 唯一性约束和主键约束的共同点是都能保证列值的唯一性，都可以被外键列引用。

v 联合唯一键约束与在两列上分别定义唯一键约束不一样。

2）唯一建的列值不允许重复。

3）唯一建上的任意一列的取值允许为空，并且可以是多个null值。

4）primary key = unique key + not null，表示主键列要求非空，而唯一键列允许为null，并且可以是多个null值。

eg：用DDL语句创建一张表，有三列，每列的值都要求唯一且非空。

create table test(  c1 number constraint test_c1_pk primary key,

               c2 number not null constraint test_c2_uk unique,

               c3 number not null constraint test_c3_uk unique);

16.5 references foreign key：外键约束

1）创建外键约束

①列级约束：

create table child(

c1 number(2) constraint child_c1_pk primary key,

c2 number(3) constraint child_c2_fk references parent(c1));注意references有“s”

②表级约束1：

create table child(

c1 number(2) constraint child_c1_pk primary key,

c2 number(3) constraint child_c2_fk foreign key(c2) references parent(c1));

表级定义外键约束时用foreign key

③表级约束2：

create table child1(

c1 number(2) constraint child1_c1_pk primary key,

c2 number(3) constraint child1_c2_fk references parent(c1) on delete cascade)；

④表级约束3：

create table child2(

c1 number(2) constraint child1_c1_pk primary key,

c2 number(3) constraint child1_c2_fk references parent(c1) on delete set null)；

2）外键约束的作用：外键约束是用来解决一对多关系的。

3）外键约束下父子表的联系：

①建立外键约束：首先先创建父表，并且引用的列必须唯一键或主键，至于被引用的列本身是否为null，外键约束并没有要求。然后子表再去创建外键约束。

②插入外键约束列中的值：首先先向父表中被引用列插入值，然后再向子表中外键约束列插入值。

③删除外键约束列中的值：首先先删除子表中外键约束列的值，然后再删除父表中被引用列的值。

④删除表：首先先删除子表，然后再删除父表

u 注意事项：

v 通过外键FK可以与同一张表的主键PK或唯一键UK建立引用关系，也可以与不同表的主键PK或唯一键UK建立引用关系。

v 外键的取值必须匹配父表中已有的值或空值。

4）删除外键约束：alter table child drop constraint child_c2_fk;

5）cascade constraints：级联约束

通过级联约束删除表：drop table parent cascade constraints purge;通过级联约束删表不用管父子表的约束，直接删表。否则要先删子表，再删父表。

u 注意事项：

v 此语句constraints有“s”！

v 等价于执行两个动作：alter table child（子表） drop constraint child_c2_fk（子表中的约束）;   drop table parent（父表） purge;

6）on delete cascade：级联删除

即在删除父表记录的时候，系统会将子表的记录先删除，再删除父表的记录。

7）on delete set null：将子表中的记录某列置空，再删除父表中的记录。

insert into parent values (1);

insert into child2 values (1,1);

delete from parent where c1 = 1;

相当于以下两条语句：

update child2 set c2 = null where c2 = 1;

delete from parent where c1 = 1;

8）外键约束关键字：

①foreign key：用表级约束定义外键时使用该关键字。

②references：表示引用父表中的某列。

③on delete cascade：级联删除，删除父表的记录前，先删除子表里的相关记录。

④on delete set null：删除父表的记录前，先将子表中的外键列的相关值置空。

9）update语句：更新表中已经存在的记录，即修改记录的某列的值。

update tabname set colname=value[,colname=value] [where condition]

delete语句：删除已经存在的记录。

delete [from] tabname [where condition];

u 注意事项：

v integrity constraint：看到这个就是违反了外键约束。

v 被引用的记录应先放入表中。

16.6 check：检查约束

1）创建检查约束

 

①列级约束：

create table test(

c1 number(3) constraint test_c1_pk primary key,

c2 number(3) constraint test_c2_ck check ( c2 > 100 ));

②表级约束：

create table test(

c1 number(3) constraint test_c1_pk primary key,

c2 number(2) ,

c3 number(2) ,constraint test_c2_ck check (( c2 + c3 ) > 100 ));

2）定义条件表达式，每个列值必须满足该条件。

3）以下表达式不允许

①伪列：currval、nextval、level、rownum

②函数：sysdate、uid、user、userenv

③引用其他记录的其他值

4）在create table时定义约束，表已经存在，用alter table追加约束。

十七、
事务

17.1 transaction

交易，事务；（一笔）交易，（一项）事务。

client端建立连接

connect jsd1304/jsd1304(sqlplus/sql developer/jdbc)

DDL操作：

create table (column 列 datatype 数据类型 constraint约束)；

alter table 对列、约束等的操作

drop table cascade constraint purge;

DML操作：insert、update、delete

TCL操作：commit、rollback，数据才真正入库

table:account

column id,balance余额    row：A  B

update account set balance = balance-1000 where id='A';

update account set balance = balance+1000 where id='B';

交易：包含2个update，一笔交易应看作一个原子操作：要做一起作，要么都不作

帐平，不是单条DML，而是一组DML+commin、rollback（transaction）

17.2定义

事务是由一组DML语句和commit/rollback(TCL)组成，是改变数据库数据的最小逻辑单元。

1）如果是commit，表示数据入库；如果是rollback，表示取消所有的DML操作。

2）事务的结束：commin/rollback，DDL语句自动提交，DML一定要有显式commit。用程序传数据入库也要传一个事务！

3）事务的开始：上一个事务的结束就是下一个事务的开始。

u 注意事项：我们传送的是一个事务，而不是简单的DML语句了！数据库中也都是一个个事务。切记！！

17.3事务的特性：ACID

1）原子性（atomicity）：一个事务或者完全发生，或者完全不发生。由DML和TCL共同完成的！！

2）一致性（consistency）：事务把数据库从一个一致状态转变到另一个状态。

3）隔离性（isolation）：在事务提交之前，其他事务觉察不到事务的影响。

4）持久性（durability）：一旦事务提交，它是永久的。

17.4事务的隔离级别

数据库应用程序中最常用的隔离级别。

Read committed：一个事务只可以读取在事务开始之前提交的数据和本事务正在修改的数据。

17.5数据库开发的关键挑战

在开发多用户、数据库驱动的应用程序中，关键性的挑战之一是要使并行的访问量达到最大化，同时还要保证每一个用户（会话）可以以一致的方式读取并修改数据。

1）锁（lock）机制：用来管理对一个共享资源的并行访问

2）多版本一致读：

①非阻塞查询：写不阻塞读，读不阻塞写

②一致读查询：在某一时刻查询产生一致结果

17.6锁的概念

1）排他锁（X锁）：如果一个对象上加了X锁，在这个锁被采用后，直到commit或rollback释放它之前，该对象上不能施加任何其他类型的锁。

2）共享锁（S锁）：如果一个对象被加上了S锁，该对象上可以加其他类型的S锁，但是，在该锁释放之前，该对象不能被加任何其他类型的X锁。

17.7 Oracle的锁机制

为确保并发用户能正确使用与管理共享资源，如表中的记录，Oracle引进锁机制。

1）对表的数据进行写操作时，系统会自动加两类，共3种锁。

①DML锁：用于保护数据的完整性，会加以下两种锁（对DML操作而言的，用排队机制wait）。

TX锁：即事务锁（行级锁），类型为X锁；操作哪行记录就加排他锁

TM锁：即意向锁（表级锁），属于一种S锁；操作哪个表就加共享锁

u 注意事项：

v 不提交，锁不会被释放！就会把别人堵塞住！

v 不提交，锁不会被释放！别人也看不到你对数据的操作。

v select语句是读操作，没有上锁。

②DDL锁：用于保护数据库对象的结构（例如表、索引的结构定义）（正在写操作时，不可修改表的结果，否则直接报错error）。

X类型的DDL锁：这些锁定防止其他的会话，自己获得DDL锁定或TM（DML）锁定。这意味着可以在DDL其间查询一个表，但不可以以任何方式进行修改。

u 注意事项：出现wait和no wait（即DDL报错）都是因为不能再加X锁导致的。

17.8事务不提交的后果

1）其他事务看不见它的操作结果。 2）表和行上的锁不释放，会阻塞其他事务的操作。

3）它所操作的数据可以恢复到之前的状态。

4）占用的回滚端资源不释放，rollback segment/undo segment会滚段（公共空间）。

17.9回滚事务rollback

1）数据的改变就像从未发生过一样

2）插入的数据没有了，更新前和删除前的数据都恢复出来。 3）锁被释放。

17.10保留点savepoint

用savepoint在当前事务里创建一个保留点，用rollback to savepoint命令将事务回滚到标记点。

SQL>insert……; SQL>update……; SQL>savepoint update_done;

savepoint created.

SQL>insert……; SQL>rollback to update_done; rollback complete.

十八、
数据库对象：视图view

18.1带子查询的create table

create table tabname[column(,column…)] as subquery;

1）根据子查询语句创建表并插入数据（根据已有的表创建新表）。

2）表结构由子查询的select语句决定，create table指定的列的数量要跟select语句指定的列的数量一致。

3）create table 定义列只能定义列名、缺省值、完整性约束，不能定义数据类型。

4）约束不能被复制过来，但非空约束不需要定义可以直接复制过来。

eg1:20机器上的业务信息

    create table service_20 as select * from service where unix_host='192.168.0.20';

eg2：创建一张表account_90，表结构与account一致，没有数据

    create table account_90 as select * from account

    where 1 = 2 ;

u 注意事项：where 1=2;是通用的，所以记录不符合条件；若子查询的返回记录数为0，新建的表就只有结构。1 = 2是永假式，任何表都不会返回记录。where 1=1;所有记录符合条件

18.2带子查询的insert

insert into new_tab(colname1,colname2,…colnamen)

select colname1,colname2,…colnamen

from old_tab

where condition;

1）根据子查询语句向表中插入数据。

2）insert指定的列的数量要跟select语句指定的列的数量一致。

3）一次可以插入多条记录，不能用values子句。

u 注意事项：若插入多个列，则需要把所有的非空列都选出，否则报错，因为非空约束直接复制过来了。

eg：account_90表中包含所有的90后客户

insert into account_90_chang

select * from account

where to_char(birthdate,'yyyy') between 1990 and 1999;

18.3定义缺省值：default

…colname date default sysdate,…

1）缺省值的数据类型必须匹配列的数据类型。

2）有效的缺省值为文字值，表达式、sql函数：sysdate、user等。

3）无效的缺省值为另一个列的列名或伪列。

4）default可以用于insert语句、update语句。

u 注意事项：insert语句、update语句都可写非关联自查询。

eg1：案例1

drop table test purge;

create table test( c1 number default 1,c2 number);

insert into test (c2) values (2);     insert into test values (default,3);

eg2：案例2

insert into test values (4,4);

update test set c1 = default where c1=4;把c1列等于4的都换成默认值

18.4 视图view

1）视图在数据库中不存储数据值，即不占空间。

2）只在系统表中存储对视图的定义。

3）视图实际就是一条select语句。

4）类似windows中的快捷方式。

u 注意事项：对象类型都按大写存储的，所以查找也写大写。

eg1：创建view

create of replace view test_v1 as select * from test where c1=2

eg2：查找view test_v1是如何定义的，找对应的select语句

select view_name,text from user_views

where view_name = 'TEST_V1';

eg3：查找view test_v1当前的状态，若为invalid则源表出问题了

select object_name,object_type,status from user_objects

where object_name = 'TEST_V1';

eg4：当视图无效时

alter view test_v1 compile;

//当视图无效时，尝试先编译视图，若不能编译，则源表不存在，要创建表。

create table test(c1 number,c2 number);//此时系统不会自动编译。

select * from test_v1;//当从视图中查询时，系统会做alter view test_v1 compile即编译操作，此时视图就有效了。

18.5视图的应用场景

1）简化操作，屏蔽了复杂的SQL语句，直接对视图操作。

2）控制权限，只允许查询一张表中的部分数据。解决办法：对其创建视图，授予用户读视图的权限，而非读表的权限。

eg：允许授权account表部分数据给jsd1304

create or replace view account_1304

as

select * from account

where 条件表达式

grant select on account_1304 to jsd1304;

3）通过视图将多张表union all成一张逻辑表，作为单独一个数据库对象，实现表的超集。

eg：数据库中有分区表

table heap table堆表，我们当前用的这些无序的一般表

table partition table分区表

create table haidian

create table haidian1

create table xicheng

create table changping ……

    create or replace view beijing as select * from haidian

 union all

 select * from haidian1

 union all

 select * from xicheng

 union all

 select * from changping

eg：每个客户选择了哪些资费标准

    方式一：

    create or replace view cost_account_service

    as

    select a.real_name,s.unix_host,c.name

    from account a join service s on a.id=s.account_id

                 join cost c on s.cost_id=c.id;

    方式二：

    create or replace view cost_account_service

    as

    select a.real_name,s.unix_host,c.name

    from account a left join service s on a.id=s.account_id

                 left join cost c on s.cost_id=c.id;  

    方式三：

    create or replace view cost_account_service

    as

    select a.real_name,t.unix_host,t.name

    from account a left join (select s.account_id,s.unix_host,c.name

    from service s join cost c

    on s.cost_id = c.id) t

    on a.id = t.account_id;

18.6视图的分类

1）简单视图：基于单张表并且不包含函数或表达式的视图，在该视图上可以执行DML语句（即可执行增、删、改操作）。

2）复杂视图：包含函数、表达式或者分组数据的视图，在该视图上执行DML语句时必须要符合特定条件。

在定义复杂视图时必须为函数或表达式定义别名。

3）连接视图：基于多个表建立的视图，一般来说不会在该视图上执行insert、update、delete操作（即不可进行DML操作）。

18.7视图的维护

1）视图中的with check option约束

create or replace view test_ck as select * from test

where c1=1 with check option;

通过“视图”插入数据时，必须符合条件才能插入（避免不符合逻辑的问题：能插入其他值，但通过视图查询时看不到其他值的情况，即能操作却不能看）。

2）视图中的with read only约束

create or replace view test_ck as select * from test

where c1=1 with read only;

只读视图，只能看不能操作，报错提示这些是虚拟列。

3）视图的DDL语句

create or replace view view_name

alter view

drop view

十九、
数据库对象：索引index

19.1创建index

create index index_name on table_name(colname);

eg：创建service表中的account_id索引

  create index service_account_id_idx on service(account_id);

19.2扫描表的方式

1）全表扫描FTS(Full Table Scan)                                            

高水位线HWM(High Water Mark)：曾经插入数据的最远块（数据存储的最小单元数据块）。数据块：口口口12｜口口，1、2被删掉后，高水位线不动，所以叫曾经……

delete from tabname中delete也同理，清除数据但不释放空间，高水位线不动，count(*)花很长时间，但结果为0；同时数据也要写入rollback回滚段，因此时间又长了，但好处是不提交数据是可恢复的；若等了半天，出现回滚段空间不足，则又会把数据返回表中 -_-！所以，delete不适合删除大表的所有数据！

truncate table tabname；DDL操作，空间释放，高水位线前移，时间短，不写回滚段，数据是不可恢复的（删除表中的所有行，但表结构及其列、约束、索引等保持不变）。 

eg：将扫描高水位线以下的所有数据块

    select real_name from account where id=1010;不建立索引，则会扫描全表

2）通过rowid（伪列）来扫描数据

rowid：标识一条记录的物理位置（唯一标识），rowid的数据类型就是rowid，会隐式把字符转成rowid类型。

rowid包含如下信息：

①该记录属于哪张表的（哪个数据库对象）：data_object_id

②该记录在数据文件的第几个数据块里：block_id

③该记录在数据块里是第几条记录：row_id

而索引则会记录：key，rowid键值对，即为index entry索引项。

19.3索引的结构

 

 

 

 

 

 

 

 

B*tree索引由根块(root block)、分支块(branch block)、叶子块(leaf block)组成。

1）根块下面是分支块，用于导航结构，包含了索引列范围和另一非根块(可以是分支块或叶子块)的地址。

2）最底层为叶子块，包含索引项(index entry)，索引由key值(被索引列的值)和该列所在行的rowid组成。

3）叶子块实际上是双向链表的表。一旦找到叶子块的“开始”点(一旦找到第一个值)，对值进行顺序扫描（索引范围扫描）是很容易的。不必再做结构导航，只要通过叶子块转发就行。最主要的就是索引对数据进行了排序。

19.4为什么要使用索引

1）Oracle server通过rowid快速定位要找的行。

2）通过rowid定位数据能有效降低读取数据块（data block）的数量。

3）索引的使用和维护是自动的，一般情况下不需要用户干预。

19.5哪些列适合建索引

1）经常出现在where子句的列。

2）经常用于表连接的列。

u 注意事项：A、B两表连接，B表有引用A表的外键，则从外键约束角度看A表为父表，B表为子表；一般父表数据量小，子表数据量大，所以从表连接角度看A表应作为驱动表，B表应作匹配表，把匹配表作索引，则匹配时不再进行全表扫描，效率将提高。

3）该列是高基数数据列（高基数数据列是指有很多不同的值）。

4）该列包含许多null值。

u 注意事项：where is null一定是全表扫描，因为索引不记录null值。

5）表很大，查询的结果集小。

6）主键（PK）列、唯一键（UK）列。

7）外键（FK）列。

8）经常需要排序（order by）和分组（group by）的列。

u 注意事项：

v 索引不是万能的，结果集和源表数据差不多时，使用索引就不好了，因为系统还要去读索引。

v 但没有索引是万万不能的。

19.6索引的类型

1）唯一性索引（unique）：等价于唯一性约束，唯一性约束用唯一性索引实现的。

2）非唯一性索引：用于提高查询效率

eg：创建唯一性索引

create unique index test_c1_uniidx on test(c1);

insert into test values (1);

insert into test values (1);

ERROR at line 1:

ORA-00001: unique constraint (JSD1302.TEST_C1_UNIIDX) violated

注意：唯一性约束的名字是唯一性索引的名字

结论：唯一性约束是通过唯一性索引实现的，二者是等价的。

3）单列索引：索引建在一列上

4）联合索引：索引建在多列上

eg：创建联合索引

create unique index srt_cour_pkid on stu_cour(sid,cid);联合主键索引：则

                                          index entry存储的为1011，10，rowid

where c1=2 and c2=1;联合列索引：则index entry存储的为2，1，rowid

19.7哪些写法会导致索引用不了

1）函数导致索引用不了：where upper(colname)='carmen'

2）表达式导致索引用不了：where colname*12=12000

3）部分隐式数据类型导致索引用不了：where colname=2(c1为varchar2类型)

u 注意事项：经过计算的结果，在索引里是找不到的。只能建函数或表达式的索引。

4）like和substr

where colname like 'ca%';只要通配符不在前面，就可以用索引

where substr(colname,1,2)='ca';不可用索引

5）查询所有的null值：where colname is null

6）否定形式：not in、<>

u 注意事项：把not in转成not exist可以用索引。

eg：创建函数索引

create index test_c2_funidx on test(round(c2));

二十、
数据库对象：序列号sequence

面临问题：主键约束和唯一键约束要求列中每个值都必须是唯一的

程序员怎样获得唯一值：

使用Oracle提供的数据库对象sequence：序列号

程序员自己写代码实现

20.1什么是sequence

Oracle提供的数据库对象。

1）为了解决主键值和唯一键值的唯一性（即解决如果保证插入的数据是唯一的）。

2）按照预定义的模式自动生成整数的一种机制，保证数字的自动增长。

20.2创建sequence

create sequence seq_name

[increment by 1 | inteher]

[start with integer]

[maxvalue inteher | nomaxvalue]

[minvalue inteher | nomaxvalue]

[cycle | nocycle]   （有缺省值）

[cache 20（缺省值） | integer | no cache]

u 注意事项：integer一定为数值类型

eg1：创建序列号，创建sequence最简单的方式：create sequence s1

create sequence s1

start with 1

increment by 1

maxvalue 5  

u 注意事项：表、视图、索引、序列号都不能重名！

eg2：若找到重名的对象

select object_type from user_object where object_name='s1';找出重名的s1是那个对象中的

drop sequence s1;发现是sequence中的s1，则删除sequence s1

20.3缺省是nocycle（不循环）

eg1：不循环情况

create sequence s1 start with 1 maxvalue 5;

select s1.nextval from dual;

select 语句连续执行6遍，最后一次：报错

ORA-08004:sequence S110.NEXTVAL exceeds MAXVALUE and cannot be instantiated

eg2：循环情况

create sequence s2 start with 1 maxvalue 5 cycle cache 4

select语句连续执行6遍，最后一次：第6次重新从1开始

20.4缺省cache 20

当没写cycle时，即为缺省值nocycle时，由于为cache 20，所以一次性取出20个数，存入到内存中，以后的用户直接在内存中取数，若内存中没有，则再次读取序列号。

u 注意事项：

v 当cycle（循环）时，cache的值要比最大值小才能成功建立！

v 当nocycle（不循环）时，大于最大值会报错。

eg1：获取当前序列号的值

select s1.currval from dual;

eg2：从user_sequences获取所有序列号信息

select sequence_name,cache_size,last_number from user_sequences;

二十一、
其他注意事项

21.1删除表，删除列，删除列中的值

1）drop table 表名 purge;删除表

2）alter table test drop(列名);删除一列

3）delete from 表名：删除表中所有值（列保留）；若加上where 列名=value则删除某列中的值

4）alter table test add(同建表时的列定义方法);增加一列

5）truncate table tabname;删除表中的所有行，但表结构及其列、约束、索引等保持不变

21.2多对多关系的实现

都需要增加一个中间表（三张表）

stu course stu-cour

sid cid sid fk-stu(sid)

name name cid fk-course(cid)  pk(sid,cid)

21.3一对多（两张表）

p表，c表，对pid设置(fk pk)

21.4一对一

husband wife

一张表：h info w info

两张表：husband id pk fk->w(id)  wife id pk

另一种案例：man id pk fk wid->woman(id) uk   woman id pk

21.5数据库对象

table、view、index、sequence

12.6缺省（默认）总结：

1）oracle数据库服务缺省端口为1521。

2）日期类型缺省格式为DD-MON-RR。

3）round()：四舍五入函数，缺省转成数字；也可对日期。

4）数据类型转换：缺省将字符转化为number（即会调用to_number()函数，要求字符串必须是数字字符）。

5）组函数缺省处理所有的非空值。

6）定义缺省值：c1 default 1

7）sequence缺省nocycle和cache 20  

8）不写列别名的话，默认都是转成大写。

9）ASC－升序，可以省略，缺省。

10）char可以不定义长度，默认为1，按定义长度存，最大长度2000字节，操作更快。