oracle基础概念性知识

表空间:

Oracle数据库对数据的管理是基于表空间的概念来的, 各种数据的以及存储数据的优化,

实际上也是通过优化表空间来实现的

表空间分类:

永久表空间

用来存放表的数据, 视图, 存储过程或者函数等需要永久保存的对象的空间

临时表空间

用来存放查询结果等临时数据的空间

UNDO表空间

用于存放回滚数据, 已经删除数据等对象的表空间

用户:

sys: 超级管理员

system: 次超级管理员

scott: 普通用户

oracle基础数据类型:

4类:

字符型:字符串

char(10) abc

char(最大2000), nchar(最大1000, 支持unicode)--->固定长度

varchar2(50)  abc

varchar2(最大4000), nvarchar2(最大2000, 支持unicode)--->可变长度

数字类型

包括整数和小数

number(有效位数, 小数位数);

number(11)

number(5, 3)99.999

// float--->存储二进制类型的数据, 1-126位0.30103

日期类型:时间

date:取值范围:公元前4712年1月1号---公元9999年12月31号, 可以直接精确到秒

timestamp(时间戳: 更精确的数据, 可以精确到毫秒)

其它类型:大文本, 文件

blob: 最大4g, 以二进制的形式来存放数据

clob: 最大4g, 以字符串的形式存放

oracle运算符和表达式

表达式类似于java, 可以是常量, 可以是变量, 也可以是表中的一个字段

算术运算符(+  -  *  /       注意这个地方没有取余的运算符, 取余使用mod函数)

比较运算符(>  >=  <  <=  =  <>/!=)

逻辑运算符(and  or  not)

字符串连接符(||)

sql语句四种类型:

数据定义语言（ddl）、数据操作语言（dml）、数据控制语言（dcl）和事务控制语言（tcl）。

data definition language(ddl)

ddl使我们有能力创建或删除表格。也可以定义索引（键），规定表之间的链接，以及施加表间的约束。

create database - 创建新数据库

alter database - 修改数据库

create table - 创建新表

复制表: create table 表名 as select * from 表名

alter table - 变更（改变）数据库表

drop table - 删除表

create index - 创建索引（搜索键）

drop index - 删除索引

data manipulation language(dml)

供用户实现对数据的追加、删除、更新、查询等操作。

select - 从数据库表中获取数据

update - 更新数据库表中的数据

delete - 从数据库表中删除数据

insert into - 向数据库表中插入数据

data control language(dcl)

grant - 授权

revoke - 撤销授权

deny - 拒绝授权

transaction control language(tcl)

savepoint - 设置保存点

rollback - 回滚

commit - 提交   增删改

oracle数据库约束:

定义规则

定义要输入的这个值是一个什么样的值, 或者是哪个范围内的值

作用: 确保完整性, 确保精确性

1, 非空约束

记录一条信息的时候如果用户名和密码没有被记录, 那么这条记录是没有实际意义的, 创建表的时候默认是可以为空的

2, 主键约束

确保表中每一行的数据的唯一性

非空, 唯一

一个表中只能有一个主键, 但是这个主键可以由多个字段(联合主键, 复合主键)来构成

可以在建表的时候写上设置为主键的关键字, primary key

或者写个表前约束设置

constraint 主键名称 primary key(字段名1, 字段名2)

3, 外键约束

唯一一个涉及两个表中的字段约束

插入数据的时候, 从表中的关联关系的字段, 要么是一个主表主键的关联, 要么是一个空值

4, 唯一约束(unique)

保证字段值的唯一性

与主键的区别: 唯一约束可以为空值, 唯一约束可以有多个  

使用语句创建唯一约束只能写多条语句, 类似于insert into

5, 检查约束

作用: 使表当中的值更具有实际意义

比如定义一个值的范围

checks(sno>0 and sno<1000)

常用关键字

insert into---插入列表名   values  数据

delete---删除数据

update---更新一条数据

Set--设置某些属性

Commit-提交

select---查.实际工作中尽量不要写*  where from

where---给执行的语句加限制条件  where s.name

Drop--删除表

in---在某个范围内 in (2,3,7)//表示集合,等于2或者等于3或者等于7都行

like(%, _)---模糊查询, %表示任意几个字符,包括什么都没有,   _表示任意单个字符 

//   ’%小%’

order by---(列名或者写索引:1, 2)  排序  跟 要排序的 字段名(默认是正序ASC)  /DESC倒序

//根据两个字段进行排序: selsect  * from student s order by s.cid desc(倒序),s.sage(默认正序)

//

//根据查询结果的默认排序  索引排序  从1开始

//selsect * from student s order by 6,3

group by---按照某几列分组 (无顺序)  //可以再排序 跟order by

Select s.cid,s.sses from student s group by s.cid,s.sses order by s.cid

//select 什么,group by 什么

having---分组之后(用在group by之后对分组信息进行)的过滤  count(1)=2

//Select s.cid,count(1) from student s group by s.cid  //在这句之后用

Having count(1)=2

case...when...then...else...end (别名)类似于分支

Case when...then...else...end

比如年龄
case t.age when 20 then ......
意思是当年龄是20的时候
case when t.age > 20 then......
意思是年龄大于20的时候

distinct---去除重复  selsect distinct

between...and(闭合区间, 包括前 后)

all---表示一个集合中所有的元素.符合条件的所有元素中取出一个

any/some---表示一个集合中任意一个元素.符合条件的任意一个元素,取出来

常用函数//as 别名

 

1//两个日期函数可以相互加减  ..差的天数

to_char---日期类型转换成字符型数据(s.birthday,‘yyyy-mm-dd am/pmhh(24):mi:ss’)

to_date---字符串转换成日期型数据(‘20150101’,’yyyymmdd’)

2分组函数(group by):  (自动过滤空值)

max---取最大值函数

min---取最小值函数

avg---平均数函数

sum---求和函数

count---计数函数   //每个分组里面  的数量

? wm_concat---列 转 行函数(也属于分组函数)     group by

3   mod---取余函数(10,3)   //10对3的取余.

4 Decode(//参数个数不限, 哪个字段,判断条件,显示为, 判断条件,显示为,else )---类似于分支语句的函数

5 substr---分割字符串   //可以传两个参数,  (,3)从第几个开始到最后

//可以传三个参数    (    ,3,2)从第几个开始 截取几个

6 //可以取小数

1 trunc-(s.birthday ,2)--取整函数  //只取整,取2个小数     取整取到天

2 Round(,2)   --四舍五五

//只能取整

3 Ceil(   ---向上取整函数

4 floor---向下取整

Nvl(   , -1)--过滤空值函数 //如果有值显示值 没有值显示为-1  

//传两个参数,必须为同一类型

nvl2---过滤空值函数2

//传三个参数  ,不为空 显示第二个参数  为空 显示第三个参数.没有类型限制

lower---将字母全部改为小写

upper---改为大写

concat---连接两个字符串 .拼到一起  //限制挺大的

From  User_tab_columns   //显示

复制表

create table 表名 as select * from 表名

删除表中数据

delete 表名;

truncate 表名; --删除表,再也找不回来

(truncate 关键字 慎用(楷体 加粗 大写 下划线 红色 爆闪 抖动效果)!!!!!!!!!!)

将excel的表贴到oracle

在excel中选中一部分数据, 记住第一列前面多选中一列

将查询结果导出excel

对查询结果全部选中, 右键有 copy to excel

//

序列的创建

序列的作用: 一般用来作为一个自增长的主键

oracle查询, 高级查询

简单查询

order by 1,2

group by 增强版

语法: select p.toma, p.ptype, sum(p.lastcou) from product p group by rollup(p.toma, p.ptype)

高级查询(多表连接查询)

笛卡尔积的概念

分类:

等值连接

不等值连接

外连接(啥也不写就是内连接, 直接写两个表名)---左外连接(left join), 右外连接 (左边(那个少的)写加,),

(+)修饰符的使用

数据字典的使用

自连接(自己骗一下自己)

层次查询

connect by prior 连接条件 start with 开始条件 is not null

伪列:

Level       //写法 select *, level from   层级

**Rownum 行号,不会根据   // 写法 select *, Rownum from   

必掌*

// select * from (select m.*,rownum rnum from menu  m where rownum <=10) r where  r.rnum >5   

作为条件时,必须第一条,只能使用< <= =

使用rownum分页功能

子查询(sql语句的执行顺序)

Where |from| having | select  后面 可以使用子查询的位置

单行子查询

多行子查询

exists关键字的使用

存储过程和函数

存储过程和函数的区别:

是否可以通过return返回一个值

语法:

create or replace procedure 存储过程名 as/is

create or replace function 函数名 return 返回类型 as/is

触发器

触发器是一种存储过程, 当用户进行增删改的操作的时候(没有select !!!)oracle数据库会自动启动触发器中的程序

两种触发器

语句级触发器

行级触发器

视图

将一个查询结果保存, 可以通过update语句修改

语法:

create or replace view 试图名 as select 查询语句

游标(也叫光标)

隐式游标:

之前我们用过的那个in关键字后面跟着的, 实际上就是一个隐式游标

游标就是一个结果集

语法:

declare

cursor stemp is select s.name,s.code from z_student s;

sname z_student.name%type;

scode z_student.code%type;

numss number;

begin

  numss := 1;

 open stemp;

/* loop

   exit when stemp%notfound;

   fetch stemp into sname, scode;

   dbms_output.put_line(sname||'的学号是'||scode);

 end loop;*/

 

 fetch stemp into sname, scode;

 while stemp%found

   loop

     --dbms_output.put_line(stemp%rowcount);

     dbms_output.put_line(sname||'的学号是'||scode);

     fetch stemp into sname, scode;

     --dbms_output.put_line(sname||', '||scode);

     --dbms_output.put_line(numss);

     --numss := numss + 1;

   end loop;

   --隐式游标

/*   for str in (select s.name from z_student s)

     loop

       dbms_output.put_line(str.name);

     end loop;*/

 close stemp;

end;

package程序包和程序体

package---包头

package body---包体

dbms_output.put_line();

上面的输出语句就是一个程序包加存储过程

dbms_output是包的名字, 调用里面的某个方法

包头的语法:

create or replace package 包头名 as

变量的说明...

存储过程或者函数的声明(不需要写实现的代码块)

end;

声明了一个包头, 就要有一个包体与之对应, 去实现包头中写明的存储过程或者函数

包体的语法:

create or replace package body 包体名(这里的这个包体名要和上面定义的包头名字一模一样) as

存储过程或者函数的实现...

end;

调用过程:

declare

begin

包头名.存储过程名或者函数名

end;

Is not null;