oracle笔记<更新>

oracle笔记

数据库：存储数据的,物理存储

数据文件：dbf

数据库实例：用来操作和访问数据库

控制文件、日志文件log

表空间：数据库中包含多个表空间，一个表空间可能会有多个数据文件组成。

默认用户：sys、system、scott(密码：tiger)

角色：

sysdba：数据库管理员，拥有所有的权限

sysoper：数据库操作员

1、创建表空间------

create tablespace 名字

datafile '路径\名字.dbf' --数据文件

size 100M； --数据大小

autoextend on next 32MB maxSize unlimited --自动增长 最大值 ====可选

<1>练习：

create tablespace java05

datafile 'D:\oracle\oradata\orcl\java05.dbf'

size 100M;

2、创建用户--------

create user 用户名

identified by 密码

default tablespace 表空间名称；

<2>练习：

create user java05

identified by java05

default tablespace java05;

3、给用户赋权--------

grant 权限 to 用户；

----给用户赋予查询功能

grant select on 表空间.表名 to 用户名；

<3>练习：grant dba to java05;

--------------

===撤销权限

revoke 权限 from 用户；

<3>练习：revoke dba from java05;

权限：

connect：临时用户

resource：正式用户，创建表，触发器，序列，存储过程

dba:管理员，可以操作任何对象

//连接某个用户

conn 用户名/密码  ====连接到某个用户

4、创建数据库表--------

数据类型：

1.字符类型

char(指定长度) 字符类型（一个字节）- 最大2000字节

varchar2(指定长度) 变长字符串 --最大4000字节

nchar-一个字符  ----unicode编码

nvarchar2：长度可变的

2.数字类型

number(数字总长度,小数点后有几位)

3.日期类型

date

TimeStamp 比date类型精确

4.大数据类型

Lob类型

Clob ：存储字符(文本)

Blob :存储二进制(图片)

======创建表-------------------------

create table 表名(

 字段名 数据类型 primary key not null,------------设置主键

 字段1 数据类型 not null

)

<4>练习：

create table studet(

oid number primary key not null, ------------设置主键

name varchar2(20),

age number

shijian date default sysdate ==========默认系统当前时间

)

------------------------sql中所有的字符都使用单引号-------

======插入数据------------------------

insert into 表(字段名1，字段名2) values(值1,值2);

insert into 表 values(值1,值2);

insert into 表1 select * from 表2 where 字段=值；--将表2里面条件满足字段条件的 全部插入到表1中。

的

<4-2>练习：

insert into studet(oid,name,age) values(1,'张三',18);

insert into studet(oid,name,age) values(2,'王四',22);

======删除数据-----------------------

delete 表名;   删除表内所有数据(产生日志文件)

truncate table 表名； 删除表内所有内容(不产生日志文件)

delete 表名 where 字段名=值； 按照字段删除

drop table 表名； 删除表

======更新(修改)数据------------------------

update 表名 set 字段名=值，字段名2=值2 where 字段名=值

<练习>:

update studet set name='礼物' where oid=1;

======查询------------------------------

select * from 表名；

select * from 表名 where 字段名=值

select * from 表名 where is null ---判断空值

select * from 表名 where 字段名=值 and 字段名=值

select * from 表名 where 字段名=值 and (字段名=值 or 字段名=值)

<练习>：selet * from studet where oid=1;

select 字段名1，字段名2 from 表名 where 字段名=值；

<练习>：select name from studet where oid=2;

---------排序查询

select * from 表名 b order by b.字段 [asc|desc];--默认是升序  

----------无重复查询

select distinct 字段 from 表名；

------模糊查询关键字：like、between and、in

select * from 表名 where 字段='%值%';

select * from 表名 where 字段='值%';

select * from 表名 where 字段='%值';

select * from 表名 where 字段 bentween 范围值1 and 范围值2；

select * from 表名 where 字段名 in (值1，值2，值3...)

======创建序列----------------

create sequence 序列名        

start with 1  ----从那开始

increment by 1 ----每次增长

nomaxvalue ---最大值

cache 10;/no cache;----预缓存

序列名：seq_表名

==========查询序列中的值------------------------

select seq_表名.nextval from dual; --查询序列的下一个值

select seq_表名.currval from dual;--查询序列的当前值

===========使用序列插入数据-----------------------

insert into 表名 values(seq_表名.nextval,'值');

commit;----提交

============oracle常用函数---------

-----decode判断函数

decode(字段,if,结果,if,结果)

----其他数据库

case 字段

when 'if' then '结果'

when 'if' then '结果'

end

--------nvl判断是否为空

nvl(字段(用到转换函数),'值')；

-----排序

order by 字段名 asc|desc 默认升序asc

------聚合函数：经常与select组合使用

1.sum(字段) 对字段求和

2.count(*) 统计表内的记录条数

3.avg(字段) 统计平均值

4.max(字段) 统计最大值

5.min(字段) 统计最小值

having count(字段)>3 至少大于3

------聚合函数：分组

select sum(字段1),字段2 from 表名 group by 字段；

-----字符函数：连接函数

concat(c1,c2);---将c2连接到c1的末尾。c2为null返回c1，c1为null返回c2；

c1||c2; ----同上

nvl(c1,c2);查询c1不为null返回c1，c1为null范围c2;

------数字函数：截取函数 (也可操作date类型)

trunc(要截取的数值,截取的位置);位置为正截取小数点后面，负截取小数点前面

round(要四舍五的数，位置)；位置为正截取小数点后面，负截取小数点前面

------日期函数：转换日期函数

sysdate---返回系统当前时间

extract----返回指定的时间

to_data(date,'yyyy-mm-dd hh24:mi:ss');

to_char(字段，'yyyy-mm-dd hh24:mi:ss');

----使用函数

---- as birthday 给查询出来的某列，给一个别名

select to_char(字段，'yyyy-mm-dd hh24:mi:ss') as  字段名 from 表名 where 字段=值

-----截取字符串

select substr(字段，开始，结束) from 表名 where 字段=值；

==============修改表-----------------------

------添加约束----

alter table 表名 add constraint 外键约束名 foreign key(表字段名) references 外表的主键

unique ------------ 唯一约束

alter table 表名 add constraint 约束名 约束类型(约束字段)

check ------------- 检查性别约束

alter table 表名 add constraint 约束名 check(sex in('男','女'))

验证年龄约束----

alter table 表名 add constraint 约束名 check(age between 1 and 130);

--------非空约束

alter table 表名 modify 字段名 not null

-------添加列----

alter table 表名 add (列名 数据类型)；

-----多表查询--------------------

select * from 子表名 where 外键=(select 外键 from 表名 where 字段 like '%值%')

-------查询多行-----------------------

select * from 子表名 where 外键 in (select 外键 from 表名 where 字段 like '%值%')

==============针对表指定别名-------

select 字段 as 别名 from 表名;

<练习>：

select j.id as 学生编号,j.name as 学生姓名,j.sex as 学生年龄,j.shijian as 入学时间,c.cj as 学生成绩

  from java05 j, chengji c

 where j.id = c.id;

============高级查询--------------------------

------子查询---------

select * from 子表名 where 外键=(select 外键 from 表名 where 字段 like '%值%')

----rownum 伪列---

是oracle数据库从数据文件或缓冲区中读取数据的顺序。它是从1开始的，物理上并不真正存在，动态生成的。

select id,name,rownum rn from 表名;----固化rownum

----------