oracle基础总结

oracle 数据库具有以下特点：

• 支持多用户、大事务量的事务处理
• 数据安全性和完整性控制
• 支持分布式数据处理
• 可移植性

oracle 体系结构

• 数据库
• 实例
• 数据文件(dbf)
• 表空间
• 用户

图片叙述

用户和表空间

用户登录

system/密码 可以直接登录
connet sys/密码 as sysdba 登录sys 要有 sysdba权限

用户查询

show user; 显示当前登录用户 
desc dba_users; 查看用户的信息词典（例如：select username from dba_users;查询用户）

启用用户语句

alter user username account unlock 	;

//username需要操作的用户名  unlock解锁 lock上锁
grant create session,resource to 用户名;
//刷新权限

创建用户

create user wateruser
identified by itcast
default tablespace waterboss;

wateruser 为创建的用户名
identified by 用于设置用户的密码
default tablesapce 用于指定默认表空间名称

查看表空间

desc dba_tablespaces;	管理级别用户数据字典
desc user_tablespaces;	普通用户查看的字典
select default_tablespace,temporary_tablespace 
from dba_users where username='SYSTEM';(查询SYSTEM的默认表空间和临时表空间）
alter user system default tablespace users;(将system默认表空间改成users)
select username,default_tablespace from user_users;(查询当前用户和表空间)

创建表空间

create(temporary)  tablespace  waterboss  
datafile(tempfile)  'c:\waterboss.dbf'  size 100m  autoextend on  next 10m;

temporary 当设置临时空间需要加上
waterboss 需要创建的表空间名称
datafile 后跟设置物理文件名称 (如果未指定文件位置则放在默认位置)
tempfile 当设置临时空间时替换掉tempfile
size 用于设置表空间的初始大小
autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
next 用于设置扩容的空间大小

select file_name from dba_data_files where tablespace_name='TEST_TABLESPACE';
查询某个表空间的目录地址其中 TEST_TABLESPACE 为目标表空间
如果查询临时表空间则需要把 dba_data_files 替换为 ba_temp_files

修改表空间的状态

设置联机或脱机状态

alter tablespace tablespace_name online|offline;//修改表联机状态
tablespace_name 目标表空间
online 代表联机状态
offline 代表脱机状态
select status from dba_tablespace where tablespace_name='TEST_TABLESPACE';
用于查询表空间状态 TEST_TABLESPACE为查询的表空间

设置只读或可读写状态

alter tablespace tablesapce_name read only|write;
only为只读状态write为可读写状态 修改前提是联机状态
select status from dba_tablespace where tablespace_name='TEST_TABLESPACE';
用于查询表空间状态 TEST_TABLESPACE为查询的表空间

数据文件

查询数据文件结构

desc dba_data_files;

FILE_NAME为数据文件的名称及存放路径
FILE_ID为该文件在数据库中的ID号
TABLESPCE_NAME为该数据文件对应的表空间名
BYTES为该数据文件的大小
BLOCKS为该数据文件占用的的数据块块数
select 上诉字段 from dba_data_files;

增加数据文件

alter tablespace tablespace_name add datafile 'xx.dbf' size xx;
tablespace_name 为表空间名
xx.dbf 为数据文件名 可以加上文件路径，不加则是在默认路径
size 设置文件大小

删除数据文件

alter tablespace tablespace_name drop datafile 'xx.dbf';
tablespace_name 为表空间名
xx.dbf 为所需删除的数据文件名，非默认路径前面需要带上路径
 **不能删除表空间第一个数据文件，若要删除则需要删除表空间**

删除表空间

drop tablespace tablespace_name[including contents];
tablespace_name是需要删除的表空间
including contents 需要删除表空间的数据文件时加上

管理表

认识表

• 基本存储单位
• 二维结构
• 行和列
  1.约定：每一列数据必须具有相同数据类型。
  2.列名唯一
  3.每一行的数据唯一性

数据类型

• 字符型 

1. char(n),nchar(n)     //固定长度类型
2. varchar2(n),nvarchar2(n) //可变长度类型
3. long  //大文本类型。最大可以存储 2 个 G

• 数值型

1. number(p,s)  //p代表有效数字,s代表小数点后的位数
     列如：number(5,2) //有效数字5位,保留两位小数,如123.45
2. float(n)   //主要用于存储二进制，转十进制需要（乘以 0.30103），即1~38。

• 日期型

1. date   //表示范围:公元前4712年1月1日到公元9999年12月31日，可以直接精确到秒
2. timestamp  //可以精确到小数秒

• 其他类型

1. blob  //可以存放4G数据，以2进制存储
2. clob  //可以存放4G数据，以字符串存储

表格管理

创建表

create table 表名称(
字段名 类型(长度) primary key,
字段名 类型(长度),
.......
);

修改表

添加字段

alter table 表名 add 新的字段名 数据类型;

更改数据类型

alter table 表名 modify 所需修改字段名 数据类型;//最好没有数据

删除字段

alter table 表名 drop column 所需删除字段名;

修改字段名

alter table 表名 rename column 原字段名 to 新字段名;

删除表

删除表数据

truncate table 表名;//只删除数据，不删除表

删除表本身

drop table 表名;

操作表数据

复制表数据

在建表复制

create table 新表名 as select 字段名1,字段2,...|* from 目标表名;//表结构一起复制了

在添加时复制

insert into 表名(字段名1,字段名2,...) select 字段名1,字段名2,...|* from 目标表名;//注意顺序以及类型要匹配

修改数据

update 表名 set 字段名1=值1,字段名2=值2... where 条件;//不加where就是一列全部更改

删除数据

delete from 表名 where 条件;//不加where就是删除表中全部数据

约束

约束的作用

• 用于数据表中列上的规则
• 确保完整性

非空约束

非空约束:not null
作用:不允许值为空

建表时设置非空约束

create table 表名(
字段名1 数据类型 not null,...);

在修改表时添加非空约束

alter table modify 字段名 数据类型 not null;

在修改表时去除非空约束

alter table modify 字段名 数据类型 null;

主键约束(primary key)

作用:确保表中没一行数据的唯一性(非空,唯一)一张表只能设计一个主键约束,主键约束可以由多个字段构成(联合主键或复合主键)

建表时添加主键

create table 表名(
字段名1 数据类型 primary key,...);//这样无法创建联合主键
create table 表名(
字段名1 数据类型,字段名2 数据类型,...,
constraint 约束名 primary key(字段名1,字段名2));//约束名是任意的,多字段是联合主键

修改表时添加主键

alter table 表名 add constraint 约束名 primary key(字段名1,...);//多字段是联合主键

外键约束(reference)

在创建表时设置外键

1.
create table 表名(
字段名 数据类型 references 目标表名(目标字段名),...);
2.
create table(
字段名1 数据类型,...,
constraint 约束名 foreign key(需要设置的字段名) references 目标表名(目标段名) [on delete cascade]
)

注:设置外键约束时主表(目标表)的字段必须是主键,
且主从表中相应的字段必须是同一数据类型,
从表的值必须来自主表相应字段的值,或者为null
on delete cascade 是级联删除的意思,主表中一条数被删除,从表中使用了这条数据的字段也会被删除,用的时候加上就行,不用可以写

在修改表时添加外键约束

alter table 表名 add constraint 约束名 foreign(段名) references 目标表名(目标字段名) on [delete cascade];

唯一约束(unique)

唯一约束和主键约束的区别:

1. 主键字段值必须是非空的
2. 唯一约束允许有一个空值
3. 主键在每张表中只能有一个
4. 唯一约束在每一张表中可以有多个

在创建表时设置唯一约束

1.
create table 表名 (
字段名 数据类型 unique,...);
2.
create table 表名(
字段名 数据类型,...,
constraint 约束名 unique(字段名))

在修改表时添加唯一约束

alter table 表名 add constraint 约束名 unique(字段名);

检查约束(check)

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

在创建表时设置检查约束

1.
create table 表名(
段名 数据类型 check(条件),...);
2.
create table 表明(
段名 数据类型,...,
constraint 约束名 check(条件)); 

在修改表时添加检查约束

alter table 表名 add constraint 约束名 check(条件);

约束操作

查看表中的约束名称

 select constraint_name from user_constraint where table_name="表名";

更改约束的名称

alter table 表名 rename constraint 原约束名 to 新约束名;
注:约束名不能重名

删除约束

alter table 表名 drop constraint 约束名;
alter table 表名 drop primary key[cascade];//因为外键约束一个表只有一个,cascade级联

查看表中的约束状态

 select status from user_constraint where table_name="表名";

禁用启用约束

alter table 表名 disable|enable constraint 约束名;
注:disable禁用|enable启用

查询语句

基本查询语句

select [distinct] 字段1,...|* from 表名 [where 条件];

distinct 去重

在SQL*PLUS中设置格式

column 字段名 heading 新字段名;

column可以简写成col
只针对字段名显示进行更改,并不更改字段名本身

column 字段名 fromat 显示值得格式;

字符类型只能设置显示长度

字符型格式化

clo 字段名 format 9999.9;

一个9代表一位数:例如

数字型格式化

清除设置的格式

column 字段名 clear;

查询表中的所有字段及指定字段

查询所有字段

select * from 表名;

查询指定字段

select 字段名1,字段名2,... from 表名;

给字段设置别名(是针对查询结果进行的,并没有更改字段的名字)

select 字段名 as 别名,... from 表名;
注:as可以省略例如:
select 字段名 别名,... from 表名;

运算符和表达式

• 算术运算符(+,-,*/)
• 比较运算符(>,>=,<,<=,=,<>(!=))
• 逻辑运算符(and,or,not(非的意思))

模糊查询

通配符的使用(_,%)

• 一个_只能代表一个字符
• %可以代替)到多个任意字符

使用like进行模糊查询

例:查询以a开头的字段信息
select * from 表名 where 字段名 like 'a%';
例:查询第二个字母是a的列
select * from 表名 where 字段名 like '_a%';
例:查询包含a的字段
select * from 表名 where 字段名 like '%a%';

范围查询

between … and

• 从什么到什么什么之间

查询800到2000之间的值
select * from 表名 where 字段名 between 800 and 2000;

注:结果包含800和2000

查询不是800到2000的值
select * from 表名 where 字段名 not between 800 and 2000;

in

• 查询固定值

例:查询字段为'aaa'或'bbb'的字段
select * from 表名 where 字段名 in('aaa','bbb');

查询字段不是'aaa'或'bbb'的字段
select * from 表名 where 字段名 not in('aaa','bbb');

对查询结果排序

• desc 降序
• asc 升序 (默认)

select ... from 表名 [where 条件]
order by 字段名1 desc|asc ,字段名2 desc|asc,..;
注多条进行排序,需要签名的字段名相同,后面的排序才能生效

case…when语句的使用

用法

Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略else返回不符合条件的值.。
Case when 相当于一个自定义的数据透视表，group by 是行名，case when 负责列名。

case 
  when sex = '1' then '男'
  when sex = '2' then '女'
else '未知' end

用法1

用法2

decode函数的使用

decode(字段名,条件1,值1,条件2,值2...,默认值);

类似于if ... if ... else 默认值可以省略