首页 / ORACLE / Oracle 基础知识
Oracle 基础知识
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 基础知识，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16233字，纯文字阅读大概需要24分钟。
内容图文
1、系统用户
sys、system、sysman --均为管理员权限，权限由高到低
scott --密码tiger，普通用户
为用户授权：grant 权限 to username;
2、用户登录
打开SQLplus：cmd->sqlplus
username/password @orcl [as sysdba|sysoper] --orcl是服务名
设置SQLplus显示行宽：set linesize 500
3、切换用户
connect username/password [as sysdba]
4、查看当前用户
show user --Oracle命令不需要";"结束，SQL语句才需要";"结束
5、用户字典表（保存用户信息）
管理员用户：dba_users
普通用户：user_users
6、启用被锁用户
alter user username account unlock;
7、表空间分类
永久表空间
临时表空间
undo表空间
8、表空间字典表
管理员用户：dba_tablespaces
普通用户：user_tablespaces
9、更改用户默认表空间和临时表空间
alter user username default|temporary tablespace tbsname;
10、创建表空间（需要对应创建一个数据文件）
create tablespace tbsname datafile 'xx.dbf' size xm; --默认表空间
create temporary tablespace tbsname tempfile 'xx.dbf' size xm; --临时表空间
查看表空间文件位置：select file_name from dba_data_files where tablespace_name='必须大写表空间名';
11、修改表空间状态（联机、脱机、只读）
alter tablespace tbsname online|offline|read only;
查看表空间状态：select status from dba_tablespaces where tablespace_name='TEST_TBS';
提示：online与read write等效。
12、给表空间增加或删除数据文件
alter tablespace tbsname add datafile 'xx.dbf' size xm;
alter tablespace tbsname drop datafile 'xx.dbf';
提示：最初的数据文件不可删除。
13、删除表空间
drop tablespace tbsname; --只删除表空间
drop tablespace tbsname including contents and datafiles; --删除表空间及其内容和数据文件
14、常用数据类型
字符串：char(n) --定长，n最大2000个字节。注意是字节
nchar(n) --定长，n最大2000个字符。注意是字符。下同
varchar2(n) --可变长，n最大4000个字节
nvarchar2(n) --可变长，n最大4000个字符
--注意：带前缀"n"指存储Unicode，长度是字符数，而不是字节数，更适合存储中文
--字符串必须是单引号，而服务名、表名可以用双引号！
数值型：number(p,s) --定点型，p是最大有效位数（不超过38个），s是小数位数，省略s则向上取整
integer --整型，四舍五入，等效于number(38)
日期型：date --精确到秒
timestamp --时间戳
大字符：clob --字符串数据，最大长度4G
blob --二进制数据，最大长度4G
15、增加字段
alter table tbname add colname datatype; --add后面不要带column，区别于MySQL
16、更改字段类型
alter table tbname modify colname datatype;
17、删除字段
alter table tbname drop column colname; --drop后面带上column
18、字段改名
alter table tbname rename column oldname to newname;
19、表改名
rename oldname to newname;
20、清空表
truncate table tbname;
21、删除表结构
drop table tbname;
22、添加数据
insert into tbname (column1, column2, ...) values(value1, value2, ...);
23、复制表
建表时：create table newtable as select columns from oldtable;
添加时：insert into newtable (columns) select columns from oldtable; --对应字段类型一致，名称不一定一致
24、更新数据
update tbname set column1=value1, column2=value2 where conditions;
25、删除数据
delete from tbname where conditions;
26、约束（系统约束信息表："user_constraints"）
非空约束：not null --默认null，可为空
建表时添加：create table tbname(id number(6) not null);
修改时添加：alter table tbname modify colname not null;
主键约束：primary key --一张表只能一个主键，但可以是联合主键
建表时添加：create table tbname(id number(6) primary key);
联合主键：create table tbname(id number(6), name varchar2(32), constraint cons_name primary key(id,name));
修改时添加：alter table tbname add constraint pk primary key(id,...);
外键约束：foreign key ... references ...
建表时添加：create table tbname(fk number(32) references mastable(pk) on delete cascade);
修改时添加：alter table tbname add constraint fk foreign key(id) references mastable(pk) on delete cascade;
唯一约束：unique --可以有空值，但是空值只能有一个
建表时添加：create table tbname(id number(6) unique);
修改时添加：alter table tbname add constraint un unique(id);
检查约束：check（范围）
建表时添加：create table tbname(id number(6) check(id>0 and id <100000));
修改时添加：alter table tbname add constraint ck check(id>0 and id <100000);
27、更改约束名称
alter table tbname rename constraint oldname to newname;
28、禁用和删除约束
alter table tbname disable constraint cons_name; --禁用约束
alter table tbname drop constraint cons_name; --删除约束
alter table tbname drop primary key; --删除主键
29、case…when…语句
例：
select id,username,
case
when id<=1 then '一等奖'
when id>=2 then '二等奖'
end as 等级奖
from users;
30、常用函数
字符串函数：
--------------------------------------------+--------------------------------------------------------------------
函数 | 描述
--------------------------------------------+--------------------------------------------------------------------
lower(str) | 将字符串表达式str中的所有大写字母转换为小写字母
--------------------------------------------+--------------------------------------------------------------------
upper(str) | 将字符串表达式str中的所有小写字母转换为大写字母
--------------------------------------------+--------------------------------------------------------------------
initcap(str) | 首字母转换成大写
--------------------------------------------+--------------------------------------------------------------------
substr(str,start,length) | 返回字符串表达式str中从第start开始的length个字符
--------------------------------------------+--------------------------------------------------------------------
length(str) | 返回字符串表达式str的长度
--------------------------------------------+--------------------------------------------------------------------
ascii(str) | 取str的ascii值
--------------------------------------------+--------------------------------------------------------------------
chr(number) | 取number的ascii值
--------------------------------------------+--------------------------------------------------------------------
replace(str,search_str[,replace_str]) | 将字符串str中的子串search_str替换成replace_str；如果search_str=null，返回str；
| 如果replace_str=null，则会去掉str中的search_str
--------------------------------------------+--------------------------------------------------------------------
instr(str1,str2[,n[,m]]) | 获取子串str2在字符串str1中的位置。n为其实搜索位置，m为子串出现的次数；
| n为负，则从尾部开始搜索；n\m默认为1
--------------------------------------------+--------------------------------------------------------------------
lpad(str1,n,str2) | 在字符串str1的左端填充字符串str2直到长度达到n；str2默认为空格，
| 如果str1.length>n，则返回str1左端的n个字符
--------------------------------------------+--------------------------------------------------------------------
rpad(str1,n,str2) | 在字符串str1的右端填充字符串str2直到长度达到n；str2默认为空格，
| 如果str1.length>n，则返回str1左端的n个字符
--------------------------------------------+--------------------------------------------------------------------
ltrim(str[,set]) | 去掉字符串str左端包含的set中的任意字符
--------------------------------------------+--------------------------------------------------------------------
rtrim(str[,set]) | 去掉字符串str右端包含的set中的任意字符
--------------------------------------------+--------------------------------------------------------------------
trim(str from string) | 从字符串的头尾或者两端截断特定字符
--------------------------------------------+--------------------------------------------------------------------
concat(str1,str2) | 连接字符串，同"||"的作用一样
--------------------------------------------+--------------------------------------------------------------------
日期函数：
--------------------------------------------+--------------------------------------------------------------------
函数 | 描述
--------------------------------------------+--------------------------------------------------------------------
sysdate | 返回系统当前日期和时间
--------------------------------------------+--------------------------------------------------------------------
current_date | 返回当前会话时区所对应日期时间
--------------------------------------------+--------------------------------------------------------------------
next_day(day,char) | 返回指定日期day后的第一个工作日char所对应的日期
--------------------------------------------+--------------------------------------------------------------------
last_day(day) | 返回day日期所指定月份中最后一天所对应的日期
--------------------------------------------+--------------------------------------------------------------------
add_months(day,n) | 返回day日期在n个月后(n为正数)或前(n为负数)的日期
--------------------------------------------+--------------------------------------------------------------------
months_between(day1,day2) | 返回day1日期和day2日期之间相差的月份
--------------------------------------------+--------------------------------------------------------------------
round(day[,fmt]) | 返回日期的四舍五入结果。如果fmt指定year，则7月1日为分界线；
| 如果fmt指定month，则16日为分界线；如果指定day，则中午12:00为分界线，默认舍入到日
--------------------------------------------+--------------------------------------------------------------------
trunc(day,[,fmt]) | 日期截断函数。如果fmt指定year，则结果为本年度的1月1日；
| 如果为month，则将结果为本月1日，默认截断到day
--------------------------------------------+--------------------------------------------------------------------
extract(year|month|day from day) | 从日期中获取年月日
--------------------------------------------+--------------------------------------------------------------------
类型转换函数：
--------------------------------------------+--------------------------------------------------------------------
函数 | 描述
--------------------------------------------+--------------------------------------------------------------------
to_char(str[,fmt]) | 将一个数字或日期转换成字符串，fmt如：yyyy-mm-dd hh24:mi:ss
--------------------------------------------+--------------------------------------------------------------------
to_number() | 将字符型数据转换成数字型数据
--------------------------------------------+--------------------------------------------------------------------
to_date(str,fmt) | 将字符型数据转换为日期型数据，fmt如：yyyy-mm-dd hh24:mi:ss
--------------------------------------------+--------------------------------------------------------------------
cast | 将一种built-in类型转换成另一种built-in类型
--------------------------------------------+--------------------------------------------------------------------
集合函数：
--------------------------------------------+--------------------------------------------------------------------
函数 | 描述
--------------------------------------------+--------------------------------------------------------------------
avg(colname) | 计算一列值的平均值
--------------------------------------------+--------------------------------------------------------------------
count(colname) | 统计一列中值的个数
--------------------------------------------+--------------------------------------------------------------------
max(colname) | 求一列值中的最大值
--------------------------------------------+--------------------------------------------------------------------
min(colname) | 求一列值中的最小值
--------------------------------------------+--------------------------------------------------------------------
sum(colname) | 计算一列值的总和
--------------------------------------------+--------------------------------------------------------------------
其它常用函数：
--------------------------------------------+--------------------------------------------------------------------
函数 | 描述
--------------------------------------------+--------------------------------------------------------------------
decode(colname,val1,res1, | 类似于case...when语句。
val2,res2,…[,default]) | if语句的另一形式。将输入数值与参数列表比较，返回对应值。
| 应用于将表的行转换成列以及if语句无法应用的场合
--------------------------------------------+--------------------------------------------------------------------
sign(number) | 如果number大于0，sign则返回1；如果number小于0，sign则返回-1；
| 如果number等于0，sign则返回0
--------------------------------------------+--------------------------------------------------------------------
trunc(number[,decimal_places]) | number是要截取的数字，decimal_places是要保留的小数位。这个参数必须是个整数。
| 如果此参数缺省，默认保留0位小数
--------------------------------------------+--------------------------------------------------------------------
greatest(expr1[,expr2]…) | 返回表达式中值最大的一个
--------------------------------------------+--------------------------------------------------------------------
least(expr1[,expr2]…) | 返回表达式中值最小的一个
--------------------------------------------+--------------------------------------------------------------------
nullif(expr1,expr2) | 如果expr1=expr2；则返回null，否则返回expr1
--------------------------------------------+--------------------------------------------------------------------
nvl(expr1,expr2) | 如果expr1=null；则返回expr2，否则返回expr1
--------------------------------------------+--------------------------------------------------------------------
nvl2(expr1,expr2,expr3) | 如果expr1!=null；则返回expr2；如果expr1=null；则返回expr3
--------------------------------------------+--------------------------------------------------------------------
31、存储过程与存储函数
含义：存储在数据库中供所有用户程序调用的子程序。
区别：函数有return，过程没有return。
存储过程简单示例：
不带参数：
create or replace procedure hello
as
--声明部分
begin
--子程序主体
dbms_output.put_line('hello world !');
end;
SQLplus调用：先打开sqlplus输出"set serveroutput on"，然后"exec hello()";
带输入参数：
create or replace procedure incrsalary(uid in number)
as
orisalary users.salary%type;
lastsalary users.salary%type;
begin
select salary into orisalary from users where id = uid;
update users set salary=salary+200 where id = uid;
lastsalary := orisalary + 200; --注意！赋值号用":="
dbms_output.put_line('旧工资：'||orisalary||' 新工资：'||lastsalary);
end;
32、存储函数
示例：
create or replace function querysalary(uid in number)
return number --返回值类型
as
sal users.salary%type;
countyear users.salary%type;
begin
select salary into sal from users where id = uid;
countyear := sal * 12;
return countyear; --返回一个值
end;
SQLplus调用：select querysalary(1) from dual; --不能用"exec"命令
33、in和out参数
存储过程和存储函数都可以一个或多个out参数；
存储过程虽无return，但可以通过out参数来实现返回值。
原则：若只有一个返回值，用存储函数；否则，就用存储过程。
示例：
create or replace procedure userinfo(uid in number,uname out varchar2,usalary out number)
as
begin
select username,salary into uname,usalary from users where id = uid;
end;
SQLplus调用：先申明out的接收变量
var uname varchar2;
var usalary number;
exec userinfo(1,:uname,:usalary); --":"传入变量
select :uname,:usalary from dual;
待续。。
内容总结
以上是互联网集市为您收集整理的Oracle 基础知识全部内容，希望文章能够帮你解决Oracle 基础知识所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】