Oracle(一)基础

一、数据库的相关概念

1、数据库（DataBase）：

         保存数据，以表的形式表现数据

2、关系型数据库操作语SQL

（1）SQL（structure query language） 结构化查询语言

       1）数据定义语言（DDL）：

create（创建）、alter（更改）和drop（删除）命令。

2）数据操纵语言（DML）：

insert（插入）、select（选择）、delete（删除）和update（更新）命令。

3）事务控制语言（TCL）：

commit（提交）、savepoint（保存点）和rollback（回滚）命令。

4）数据控制语言（DCL）：

grant（授予）和revoke（回收）。

3、SQLPLUS的命令

（1）设置编码：

set NLS_LANG=american_america.us7ascii (设置编码才可以使用下面脚本)

cd $ORACLE_HOME/rdbms cd demo summit2.sql

（2）查看版本号：

select * from v$version;

（3）登陆oracle的命令：

sqlplus用户名/密码

show  user显示当前登陆的身份.    

改变身份可以直接connect 用户名/密码

（4）“/”：用来执行sqlplus的buffer中缓存的最后一条sql语句

     edit命令（缩写ed）：来编辑最后一条sql语句。

     l 命令（list）：（sqlplus命令）可以显示buffer中最后一条命令。

（5）desc [表名]： 用于查看表的结构，describe的缩写

   desc命令后显示的表结构：[字段名] [字段的类型]

 注：这是一条sqlplus命令，他不是sql语句

（6）shell和sqlplus间切换

使用!shell命令 可以在sqlplus中使用shell命令。

       实际上是sqlplus开了子进程来执行shell命令。

（7）sql脚本

      也就是在文件中写有sql语句的文件，可以在sqlplus中运行。

      sqlplus 用户名/密码 @sql脚本

在脚本中最后一行写上“exit”，则运行完脚本以后，回到shell上

注意：

在用户名密码输入结束后一定要加空格然后再写@sql脚本

（8）set pause on

       set pause off 分页显示. 。

（9）spool on 开启记录

 spool off 关闭记录

 spool 文件名    此命令会把所有的操作存在某个文件中去

 

二、Oracle数据库中常用的数据类型

1、varchar2(长度)可变长字符串

2、char(长度) 定长

3、number()表示整数或者浮点数

4、clob 字符的大对象

5、blob 二进制的大对象

6、date 日期类型

三、数据库查询

SELECT语句 ----        从表中提取查询数据

1、语法：

SELECT [DISTINCT] {column1,column2,…} （DISTINCT触发排序）

FROM    tablename

WHERE {conditions}

GROUP BY {conditions}

ORDER BY {expressions} [ASC/DESC];
2、查询操作：

（1）投影操作：只查看选择的字段的信息。

（2）选择操作：查看字段中的特定某些信息。

（3）联接操作：多表查询，通过表间连接，查寻出多表中的信息

3、SQL关键字不能拆分，sql语句，以及表名，字段名是大小写不敏感的。

   sql语句要以";"结尾，来表示sql语句结束，如果不加";"系统不会执行此条sql语句，并提示。    

     注：在Oracle中字符显示是左对齐，数值右对齐。

4、说明

（1）SELECT子句用于指定检索数据库的中哪些列

<!--[if !supportLists]-->v  <!--[endif]-->别名

select [字段名或表达式] ["别名"]，[...] ["..."],.... from 表名；

可以通过在字段名或表达式后加空格"别名"，可以给列，或者表达式结果其别名。表达别名必须加双引号。

注：在Oracle中的字符或字符串要用单引号，双引号用来起别名

<!--[if !supportLists]-->v  <!--[endif]-->"||"字符串拼接

select 目标字段名||' '||目标字段名 from 表名；

<!--[if !supportLists]-->v  <!--[endif]-->distinct关键字，去掉重复行（这个关键字会触发排序操作）

eg.

select distinct dept_id,title from s_emp;

注：distinct 可以使dept_id与title的联合唯一

distinct，关键字之后会对from之前的字段进行排重操作。

（2）FROM子句用于指定从哪一个表或视图中检索数据。

eg.   查询语句就是查询本用户下所拥有的所有表的表名。

select table_name from user_tables;（查询系统表）

（3）WHERE子句用来选择符合条件的的记录

 1）逻辑比较运算符

        ＝        等于

        ！=       不等于，还有(<>  ^=   这两个同样表示不等于)

        >         大于

        >=        大于等于

        <         小于

        <=        小于等于

 2）SQL 比较运算符

<!--[if !supportLists]-->v  <!--[endif]-->between…and… ：在两者之间。(BETWEEN 小值 AND 大值)

eg.   工资1000到1500的人，包括1000和1500。

select last_name,salary

from s_emp

where salary between 1000 and 1500；

<!--[if !supportLists]-->v  <!--[endif]-->in(列表)：在列表里面的。

eg.   第41、42部门的人

select last_name,dept_id

from s_emp

where dept_id in(41,42);

<!--[if !supportLists]-->v  <!--[endif]-->like：包含某内容的。模糊查询

可以利用通配符创建比较特定数据的搜索模式，通配符只能用于文本，非文本数据

类型不能使用通配符。

通配符在搜索模式中任意位置使用，并且可以使用多个通配符。

a）通配符%：表示任意多个任意字符；还能代表搜索模式中给定位置的0个或多个字符。

b）下划线_：表示一个任意字符。

eg.   找出“S_“开头的,由于下划线有任意字符的含义，故需另外定义转移符。

select table_name

from user_tables

where table_name like 'S\_%' escape'\';

like 'M%'：M开头的     

                    like '_a%'：第二个字符是a的   

like '%a%'所有含a的

 注：

转义的用法：like ‘S\_%’ escape ‘\’

单引号里面的内容，大小写敏感。单引号用来限定字符串，如果将值与串类型的列进行比较，则需要限定引号；

用来与数值列进行比较时，不用引号。

<!--[if !supportLists]-->v  <!--[endif]-->is null：是空。(NULL表示不包含值。与空格、0是不同的。)

eg.

select prod_name,prod_price

from Products

where prod_price is null;

注意：

 

（4）ORDER BY子句使SQL在显示查询结果时将各返回行按顺序排列

返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。       

order by 目标列名（别名） 排序顺序（不写排序顺序，会默认为升序排序）

·ASC（默认，升序）

·DESC（降序）

       eg.  

select first_name from s_emp order by first_name;

select first_name from s_emp order by first_name desc;

（5）GROUP BY分组子句按指定的分组规则分组

注意：

只要写了group by子句，select后就只能用group by后的字段或者是组函数。

（6）HAVING子句可以过滤组函数结果或是分组的信息，且写在group by子句后。

5、子查询

就是可以嵌在任何的sql语句中的select语句,子查询也会触发排序

（1）过程：

在select语句中嵌套子查询时，会先执行子查询。

（2）一般的会将子查询放在运算符的右边。

注：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值，in）。配合使用子查询返回的结果必须符合运算符的用法。

eg. 求谁的工资是最低的

            select first_name,salary

            from s_emp

            where salary =(

select min(salary)

                from s_emp );

   求谁和Smith是做一样工作的

             select first_name,title

             from s_emp

             where title in (

select title

                           from s_emp

                where last_name='Smith');

         求哪些人是普通员工

            select first_name

from s_emp

            where id not in (

select manager_id

from s_emp

                where manager_id is not null);

<==>

select e.first_name,m.first_name

from s_emp e,s_emp m

      where e.manager_id(+) = m.id and e.first_name is null

 

        求哪些人是经理

            select first_name

      from s_emp

           where id in (select manager_id from s_emp)

<==>

select a.first_name 

from s_emp a,s_emp b

where a.manager_id=b.id;

（3）from后面也可以用子查询

eg.   找出哪个人的平均工资比本部门的平均工资高

             select e.first_name,e.salary,a.avgsal

             from s_emp e,(

select dept_id,avg(salary) avgsal

from s_emp

group by dept_id) a

             where e.dept_id=a.dept_id and e.salary>a.avgsal;

四、数据操作语言

1、insert：向表myTab中插入一行数据；

 insert  into myTab values(‘001’,’John’);

（1）插入空值：

<!--[if !supportLists]-->v  <!--[endif]-->隐式插入

     insert  into s_dept (id, name) values(12, 'MIS');

        不往想为空的字段中插数据，系统默认为NULL

<!--[if !supportLists]-->v  <!--[endif]-->显示插入

     insert  into s_dept values(13, 'Administration', NULL);

（2）用select语句向表中插入值

           insert  into tt1 value(select * from tt2);

2、update 更新

  eg.      将表empa中sal小于1500的行的sal值全部改为1500。

      update empa set sal=1500 where sal<1500;

3、delete

（1）用delete 删除一张大表会花很长的时间。

（2）用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。

（3）delete操作不会释放表所占用的空间，delete不适合删除记录多的大表。

（4）delete操作会占用大量的系统资源。      

  eg. 删除表empa中sal小于1500的行；

      delete from empa where sal<1500;

5、truncate（截断表）

表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表

  eg. 删除表myTab中的所有行（截断表）

truncate  table myTab;

 注:不可以回滚。

五、数据定义语言

1 、create

（1）创建一个名为myTab的表，包含两列no和name；

 create tablemyTab(

no number(4),

name varchar2(20));

（2）从emp表中选择“empno,ename,job,sal”四列的数据建立新表empa

create table empa

as

select empno,ename,job,sal

from emp;

（3）使用一个假条件根据现有表emp创建一个只包含结构的空表empa；

create table empa

as

      select * from emp where 1=2;

（4）通过查询建表

    create table tt_emp

    as

    select id,first_name,salary

    from s_emp

    where dept_id=42

2、alter

（1）修改myTab中的name列，使此列能容纳25个字符；

 alter table myTab modify (name varchar2(25));

（2）给表myTab增加一列tel_no；

 alter table myTab add (tel_no varchar2(20));

（3）删除表myTab的tel_no列;

 alter table myTab drop column tel_no;

3、drop

eg. 删除表myTab

 drop table myTab;；

六、事务控制语言

1、用于提交并结束事务处理；

 commit;

2、保存点类似于标记，用来标记事务中可以应用回滚的点；

 savepoint mark1;

3、回滚到保存点mark1

 rollback to savepoint mark1;。

七、Oracle数据库函数

注意:dual表(虚表)是专门用于函数测试和运算的.

1、字符函数

   注：字符是大小写敏感的

   （1）转小写 lower(字段名)

   （2）转大写 upper(字段名)

   （3）首字母大写 initcap(字段名)

   （4）字符串拼接 concat(字段1, 字段2)

   （5）截取子串 substr(字段名, 起始位置，取字符个数)

eg.   从名字的第二个字符开始取两个字符

select  first_name,substr(first_name,2,2) sub

from  s_emp;

    从名字的倒数第二个字符开始取两个字符

select  first_name,substr(first_name,-2,2) sub   from s_emp;

注：默认的是从左向右，如果是-2则表示从右向左数

   （6）空值函数nvl  (字段名, 值) 两个参数的类型要匹配，统一的

表示：如果有，则返回前面的参数，如果没有就返回后面的参数

eg.

select first_name,salary*12*(1+NVL(commission_pct,0)/100) "total salary"

from s_emp;

（7）

2、数值函数

   （1）四舍五入函数 round(数据,保留到小数点后几位)

            1表示保留到小数点后一位，-1表示保留到小数点前一位。

      eg.

select  round(15.36,1) from dual;

   （2）截取数值函数 trunc(数据，保留到小数点后几位)

      eg.

select trunc(123.456,1) from dual;

              截取到小数点后一位,注意:与round函数不同,不会四舍五入。

3、日期函数

   注：缺省日期格式，日-月-年 dd-mon-rr

   （1）修改当前会话的日期格式，会按照指定的格式输出日期

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

   （2）求两个日期间相隔了多少个月 months_between(date1,date2)

   （3）加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。

   （4）从date日期开始的第一个星期五 next_day(date,FriDay

   （5）返回月末的日期 last_day(date)

4、不同数据类型间转换函数

   （1）将日期转成字符 to_char(date,'日期格式')

   （2）日期格式要用有效格式，'yyyy mm dd hh24:mi:ss'(标准日期格式),

'year'(年的全拼),'month'(月的全拼)，'day'(星期的全拼)，'ddspth' (日期的全拼)

     eg.

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

select  to_char(sysdate,'year month day ddspth')              from dual;

   （3）将字符串转成日期 to_date('...','日期格式')

     eg.

select  to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy')  from dual；

   （4）将字符转换成数字 to_number('...') ,缺省是按十进制来算，字符串只能是0---9的数字

              select  to_number('ab','xx') from dual; 把ab转成十六进制

   （5）将数字转字符to_char(number，'fmt') fmt是数字格式

注：##########表示越界，大于显示宽度

eg.

       select to_char(salary,'$99,999.99') from s_emp;

              select to_char(salary,'$00,000.00') from s_emp;  》

              select to_char(salary,'L00,000.00') from s_emp;

5、组函数

（1）avg(..),求平均值 

eg.

select avg(salary) from s_emp group by dept_id; 

（2）sum(..),求和

（3）count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。

（4）max(..),min(..)求最大值和最小值，

  注：  

<!--[if !supportLists]-->v  <!--[endif]-->组函数会忽略空值，但是count(*)除外，他会把空记录也记录在内。

<!--[if !supportLists]-->v  <!--[endif]-->avg和sum这两个函数的参数只能是number型的。。

eg.

select  max(b.name),avg(a.salary), max(c.name)

from   s_emp a,s_dept b,s_region c

where  a.dept_id=b.id and b.region_id=c.id

group by  b.id;

八、表连接（关联查询）

1、等值连接

  select table1.column1，table2.column2

  from table1 t1，table2 t2

  where t1.column3=t2.column4;

  表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。

注意：当为表起了别名，就不能再使用表名.字段名了。

eg.  

select e.first_name ||’ ’|| e.last_name name,d.name dept_name

from s_emp e, s_dept d

where e.dept_id=d.id;

2、非等值连接

  select [表别名1.字段名1]，[表别名2.字段名2],...

  from 表1 表别名1 ，表2 表别名2

  where 表别名1.字段名3 ..... 表别名2.字段名4

  ....可以使比较运算符，也可以使其他的除了'='的运算符

eg.  

select first_name, salary

from s_emp

where salary between 1000 and 2000;

3、自连接

把一个表的两个字段关系转换成两个表字段之间的关系.

      select [表别名1.字段名1]，[表别名2.字段名2],...

      from 表1 表别名1 ，表1 表别名2

      where 表别名1.字段名3=表别名2.字段名4;

eg.

select a.first_name ename,b.first_name cname

from s_emp a,s_emp b

where a.manager_id=b.id;

4、外连接

使用一张表中的所有记录去和另一张表中的记录按条件匹配(空值也会匹配)这个表中的所有记录都会显示。

//想在哪边模拟记录就在哪边加上(+)

（1）left  out  join：左外连接

  eg. 所有员工及对应部门的记录，包括没有对应部门编号dept_id的员工记录。

select e.last_name, e.dept_id, d.name 

       from s_emp e 

       left outer join s_dept d on (e.dept_id = d.id);

<==>

       select e.last_name, e.dept_id, d.name 

       from s_emp e, s_dept d 

       where e.dept_id=d.id(+);

（2）right out join ：右外连接
         eg. 所有员工及对应部门的记录，包括没有任何员工的部门记录。

       select e.last_name, d.name 

       from s_emp e 

       right out join s_dept d  on(e.dept_id = d.id);

<==>

       select e.last_name,d.name 

       from s_emp e, s_dept d 

       where e.dept_id(+)=d.id;

（3）full outer join：全外关联

  eg. 所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和              没有任何员工的部门记录

       select e.dept_id,d.id 

       from s_emp e 

       full outer join s_dept d on(e.dept_id = d.id);

九、常用系统表

1、user_tables表：是系统表，用于存储用户下所拥有的所有表的表名

2、dual表，是专门用于函数测试和运算的，他只有一条记录

eg.   返回当前日期 sysdate

select sysdate from dual;

查找sequence s1的当前值

select s1.currval from dual;

十、将业务需求转换成可操作的表

1、E-R图（E entity 实体, R relation 关系）

（1）E-R图属性:

       * 为强制的非空属性

       o 可选属性(可以有值也可以没有)

       #* 表示此属性唯一且非空 

       # 表示唯一的

（2）实体关系:

1）一对一关系  

       2）一对多关系  

       3）多对一关系   

       4）多对多关系           

（3）范式：

1）第一范式，所有的属性都必须是单值，也就是属性只表示单一的意义。（记录可以重复，没有任何限制）

2）第二范式，属性要求唯一且非空，（记录不可重复，但是数据可能会出现冗余）。

3）第三范式，非主属性只能依赖于主属性，不能依赖于其他非主属性。（解决数据冗余问题）

十一、约束

（1）约束是针对表中的字段进行定义的。

（2）primary key（主键约束 PK）保证实体的完整性，保证记录的唯一

主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键。

1）第一种定义形式：

列级约束

create table test(c number primary key  ); 

2）第二种定义形式：

所有列定义完后再定义约束称为表级约束(能定义联合主键)

create table test(c number ,primary key(c) ) ;

联合主键

create table test(c number , c2 number , primary key (c ,c1) ) ;     

3）定义约束名,默认约束名为SYS_ ，在列后面定义约束称为列级约束

create table test(c1 number constraints test_c1 primary key); 

（3）foreign key（外建约束 FK）保证引用的完整性，

外键约束，外键的取值是受另外一张表中的主键或唯一值得约束，不能够取其他值，只能够引用主键会唯一键的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表）。

要想创建子表，就要先创建父表，后创建子表

记录的插入也是如此，先父表后子表，

删除记录，要先删除子表记录，后删除父表记录，

要修改记录，如果要修改父表的记录要保证没有被子表引用

要删表时，要先删子表，后删除父表。

eg.

1）先要定义父表

create table child(c1 number primary key);   

                  然后定义子表  references parent定义外键

create table child(c1 number primary key, c2 number references parent(c1));  

<==>

create table child (c1 number primary key, c2 number,foreign key(c2) references

parent(c1));

2）on delete cascade为级联删除,删除父表时子表也被删除

create table child(c1 number primary key, c2 number references parent(c1) on

delete cascade); 

   on delete set null删除后将外键置空

create table child(c1 number primary key, c2 number references parent(c1) on

delete set null); 

（4）unuque key（唯一键，可以为空 UK），值为唯一

     unique 唯一约束

create table test(c1 number primary key,c2 number unique);

  一个字段上可以加多个约束

create table test(c1 number primary key,c2 number not null unique);

（5）check 约束

1）约束c1的值大于100 ，列级约束

create table test(c1 number check(c1>100)); 

2）表级约束

create table test(c1 number,check(c1>100)); 

十二、索引

1、创建索引：

create index索引名 on表名 (字段名);

eg.

create index testindex on test(c1, c2);

注：

在建表时会根据表中的PK或UK自动的建立唯一性索引。

2、建索引的目的：为了加快查询速度。

（1）用索引就是为了快速定位数据：(理解时就以字典的目录为例)

创建索引就是创建key和记录的物理位置（rowid）组成的键值对。

（2）哪些字段应该建索引：

创建索引就是为了减少物理读，索引会减少扫描的时间。经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率就越高。

3、索引是有独立的存储空间,但是和表是逻辑关联的,索引和表的关系是依附关系,表被删除了,索引也没有存在的意义也就被删除了

   注：

truncate 表时索引结构在，但是数据不存在。

4、索引是会进行排序。

（1）查看表的rowid：

select rowid,first_name from s_emp;

   注：

rowid 定义的信息有：object block table

每条记录都有自己的rowid

5、索引的分类：

（1）唯一性索引，

（2）联合索引。

注：索引中是不会维护空值的。

十三、sequence

1、序列(sequence) 可以自动产生唯一值

2、创建：

create sequence  序列名;

注：

不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值)

sequence 的参数：

<!--[if !supportLists]-->v  <!--[endif]-->increment by n  递增量

<!--[if !supportLists]-->v  <!--[endif]-->start with n     起始值

<!--[if !supportLists]-->v  <!--[endif]-->maxvalue n     最大值  nomaxvalue  定义的最大值

<!--[if !supportLists]-->v  <!--[endif]-->minvalue n     最小值

<!--[if !supportLists]-->v  <!--[endif]-->cycle|no cycle   轮回 

<!--[if !supportLists]-->v  <!--[endif]-->cache n        缓存(第一次取时会一次取多少个id存起来)

3、删除序列sequence

drop sequence 序列名;

4、修改序列：(此命令不常用，只需了解就行不必深究)

alter sequence  序列名  修改项；

5、查看sequence 示图：

（1）desc  user_sequences ;

（2）select sequence_name , cache_size , last_number 

 from  user_sequences  

 where   sequence_name  like 's_';

（3）查看当前的序列数

select  序列名.currval  from   dual   

（4）查看下一个序列数，它会自动给当前的序列加１

select  序列名.nextval  from   dual   

注：

伪列：nextval   currval

(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)

清空当前会话的内存：

alter system  flush  shared_pool;（执行此命令要有DBA权限，一般用户执行出错）

十四、视图

1、视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句。

视图不占空间,使用view 不会提高性能，但是能简单化sql语句

使用视图的好处：控制数据访问权限，限制对数据库的访问，简化查询。

2、创建视图：

create view视图名;

eg.

create or replace view test_vi

as

select * from test1 where c1=1;

注：or replace的意义，如果view存在就覆盖，不存在才创建。

3、分类：

（1）简单视图：来自于单表，且select语句中不能包括函数，能进行DML操作。

（2）复杂视图：来源于多张表，不能执行DML操作。

4、视图的约束

with read only 视图只读约束(O)

with check option 不允许插入与where条件不符的记录，类似于check约束的功能(V)

5、内嵌视图

在select from 后也可以使用子查寻，这个写法也叫做内嵌视图

eg.

select first_name,salary,avgsal

from s_emp e,(

select dept_id,avg(salary) avgsal

from s_emp

group by dept_id) s

where e.dept_id=s.dept_id and e.salary>s.avgsal;

6、删除视图

drop views 示图名;

十五、行号（rownum）

1、rownum  有个特点要么等于1 要么小于某个值，不能直接等于某个值, 不能大于某个值。

2、rownum常用于分页显示。

3、rownum只用于读入内存的数据。

eg.   找出工资前三名的员工

select first_name,salary

from (select first_name,salary from s_emp order by salary desc)

where rownum<=3;

eg.   列出每一个表的外键的定义，主表表名，主表字段名，子表表名，子表字段名 （画出E-R图）

select c.table_name,cc.column_name, p.table_name,pc.column_name,p.constraint_type

from user_constraints c,user_cons_columns cc,user_constraints p,user_cons_columns pc

where c.constraint_name=cc.constraint_name

and p.constraint_name=pc.constraint_name

and c.r_constraint_name=p.constraint_name

and c.table_name='S_EMP'

 

and c.constraint_type='R';