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.每一行的数据唯一性
数据类型
- 字符型
- char(n),nchar(n) //固定长度类型
- varchar2(n),nvarchar2(n) //可变长度类型
- long //大文本类型。最大可以存储 2 个 G
- 数值型
- number(p,s) //p代表有效数字,s代表小数点后的位数
列如:number(5,2) //有效数字5位,保留两位小数,如123.45- float(n) //主要用于存储二进制,转十进制需要(乘以 0.30103),即1~38。
- 日期型
- date //表示范围:公元前4712年1月1日到公元9999年12月31日,可以直接精确到秒
- timestamp //可以精确到小数秒
- 其他类型
- blob //可以存放4G数据,以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.
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 默认值可以省略