请选择 进入手机版 | 继续访问电脑版
搜索
房产
装修
汽车
婚嫁
健康
理财
旅游
美食
跳蚤
二手房
租房
招聘
二手车
教育
茶座
我要买房
买东西
装修家居
交友
职场
生活
网购
亲子
情感
龙城车友
找美食
谈婚论嫁
美女
兴趣
八卦
宠物
手机

史上最全的ORACLE基础教程

[复制链接]
查看: 50|回复: 0

8157

主题

8157

帖子

2万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
24481
发表于 2019-12-3 07:15 | 显示全部楼层 |阅读模式
ORACLE命令和语句挺多,全数记忆下来不现实,况且有不常用的指令。下面把大部分的指令做了记载和具体的诠释。倡议收藏、转发此篇文章,假如忘记可以翻出来查查。关注公众号it_learn获得更多进修资本

用户与权限

建立用户
create user 用户名 identified by 密码;
给用户点窜密码
alter user 用户名 identified by 新密码;
点窜自己的密码
password 用户名;
删除用户
drop user 用户名 [cascade]可选参数 cascade
在删除用户时,留意: 假如要删除的用户,已经建立了表,那末就必要在删除的时候带一个参数 cascade; Cascade 有级联的感化
l 给用户赋权限
grant 权限/脚色 to 用户名;
发出用户权限
revoke 权限/脚色 from 用户名;
系统权限:是数据库治理关连的权限:create session(登录权限)、create table(建立表权限)、create index(建立索引权限)、create view(建立视图权限)、create sequence(建立序列权限)、create trriger(建立触发器权限)
connnect 脚色:是授与用户的最底子的权利,可以毗连到 oralce 数据库中,并在对其他用户的表有拜候权限时,做 SELECT、UPDATE、INSERTT 等操纵。Create session--建立会话;Alter session--点窜会话;Create view--建立视图。Create sequence--建立序列等权限
resource 脚色:具有建立表、序列、视图等权限。Create table--建表;Create trigger-- 建立促发器;Create procedure--建立进程;Create sequence—建立序列;Create type--建立典范等权限;
dba 脚色:是授与系统治理员的,具有该脚色的用户就能成为系统治理员了,它具有全数的系统权限。
表空间

建立表空间必须操纵 system 用户建立
create tablespace hpu56(表空间名)

datafile 'E:\datebase\hpu6.DBF' //指定指向的数据文件途径
size 5m      //初始化表空间巨细为 5m
autoextend on next 2m      //自动扩大,每次扩大 2m
maxsize unlimited;         // unlimited最大扩大量没有限制,100M最大扩大到100M
建立用户指定默许表空间
create user 用户名 identified by 密码 default tablespace 表空间名;
点窜用户默许表空间:
alter user 用户名 default tablespace 表空间名;
检察表空间:
select * from v$tablespace;
检察用户默许表空间:
select username,default_tablespace from dba_users where username='SCOTT';
留意:这里的用户名 SCOTT 必须是大写
表结构的操纵

建立表:
create table 表的称号(字段称号 1 数据典范, 字段称号 2  数据典范);
create table 表的称号 as select 字段称号 1, 字段称号 2... from表的称号 ;
点窜表
alter table 语句增加、点窜或删除列的语法
增加列
alter table 表的称号 add(字段称号1 数据典范, 字段称号2  数据典范...);
alter table 表的称号 add 字段称号 数据典范;
点窜列
alter table 表名 modify(字段称号1 数据典范, 字段称号2  数据典范...);
       alter table 表名 modify字段称号 数据典范;
删除列
alter table 表名 drop(字段称号1 , 字段称号2,...);
alter table 表名 drop 字段称号;
点窜表的称号
rename 表名 to 新表名;
点窜列名
alter table 表名 rename column 旧列名 to 新列名;
检察表结构
desc 表名;
约束

约束分为: 非空(NOT NULL)约束、 唯一(UNIQUE)约束、主键(PRIMARY KEY)约束、外键(FOREIGN KEY)约束、条件(CHECK)约束,约束存在表user_constraints中。
非空(NOT NULL)
增加非空
create table 表称号 (字段称号1 数据典范,字段称号2 数据典范 not null);
alter table  表名 modify 字段名 not null;
删除非空
alter table  表名 modify 字段名   null;
唯一(UNIQUE)
增加点窜唯一
create table 表称号 (字段称号1 数据典范,字段称号2 数据典范 unique);
alter table 表名 add constraint 约束名 unique (字段称号1,字段称号2);
删除唯一
alter table  表名 drop constratint 约束称号;
留意:oracle unique 可以为 null,而且答应多活动 null
主键(PRIMARY KEY)
增加点窜主键
create table 表称号 (字段称号1 数据典范,字段称号2 数据典范 primary key);
create table 表称号 (字段称号1 数据典范,字段称号2 数据典范 , constraint 约束名primary key (字段称号1,字段称号2));
alter table 表名 add constraint 约束名 primary key (字段称号1,字段称号2);
删除主键
alter table  表名 drop primary key cascade;
这是由于假如在两张表存在主从关系,那末在删除重要的主键约束时, 必须带上 cascade 选项
留意:每个表只能有且有一个主键约束。
出格分析 primary key 与 unique 的区分:
1、一张表可以有多个 unique(唯一)约束;
2、一张表只能有一个主键;
3、设备为主键的列不能有 null 值;
外键(FOREIGN KEY)
外键(FOREIGN KEY)约束:用来保护从表(Child Table)和主表(Parent Table)之间的援用完整性.可以保护数据库的数据齐截性,数据的完整性。避免毛病的渣滓数据入库;  
用于界说主表和从表之间的关系,外键约束要界说在从表上,主表则必须具有主键约束或是 unique 约束,当界说外键约束后,要求外键列数据必须在主表的主键列存在或是为 null
增加点窜主键
create table 表称号 (字段称号1 数据典范,字段称号2 数据典范 references 主表称号(字段名) );
create table 表称号 (字段称号1 数据典范,字段称号2 数据典范 , constraint 约束名 foreign key (字段称号) references主表称号(字段名)));
alter table 表名 add constraint 约束名  foreign key(字段称号)  references 主表称号(字段名);
删除主键
alter table  表名 drop constratint 约束称号;
出格分析:froeign key 外键的细节
1、外键指向主键列;
2、外键可以指向 unique 列;
3、建表时先建主表,再建从表;删除表先删从表,再删主表。
4、外键列属性值要与主键或 unique 列属性值的典范连结齐截
5、外键列的值,必须在主键列中存在。但外键列的值答应为 null
条件(CHECK)
增加点窜条件
create table 表称号 (字段称号1 数据典范,字段称号2 数据典范 check (字段称号2 in (值1,值2...)));
alter table 表名 add constraint 约束名 check (字段称号 in  (值1,值2...));
删除条件
alter table  表名 drop constratint 约束称号;
约束命名法则:
非空约束: NN_表名_列名
唯一约束: UK_表名_列名
主键约束: PK_表名   
外键约束: FK_表名_列名
条件约束: CK_表名_列名
表现表约束信息
select  *  from user_constraints  where table_name = '表名';
数据操纵

增加数据
insert into  表名[(列名 [,列名 2...])] values(值 [,值 2...]);
插入全数数据
insert into  表名 values(值 [,值 2...]);
从表中导入数据
insert into  表名 (select 值1,值2,值3...from 表名2 );
点窜数据
update 表名 set 列名=表达式 [,列名 2=表达式 2,...][where 条件];
删除数据
delete from 表名 [where 条件表达式];
删除的几种方式比力:
delete from 表名;
删除全数记载,表结构还在,写日志,可以规复的,速度慢
drop table 表名;
删除表的结构和数据
truncate table 表名;
删除表中的全数记载,表结构还在,不写日志,没法找回删除的记载,速度快。
底子查询数据

底子语法:
select [能否剔除反复数据] *|{字段名(列名),字段名 2(列名 2),字段名 3(列名 3)..} from 表名 [where {条件}];
留意事项:
1、select 指定查询哪些列的数据;
2、column 指定列名;
3、*代表查询全数列;
4、from 指定查询哪张表;
5、distinct 可选,指表现成果时,能否剔除反复数据;
6、where 条件。
操纵列的别名
select ename as "姓名",sal*13+nvl(comm,0)*13 as "年支出" from emp;
出格留意:
oracle 在操纵别名时,可以用双引号或倒霉用或操纵 as 来表白别名。但不能操纵单引号。
如那边置赏罚 null 值
         nvl 函数:oracle 供给的函数,是用于处置赏罚 null 值操纵的。
SQL>select ename,sal*13+nvl(comm,0)*13 from emp;
nvl(值 1,值 2) 表白:nvl 值 1 为 null 时则取值 2,值 1 不为 null 时则取值 1 原值。
怎样毗连字符串(||)
在查询的时候,渴望把多列内容做为一列内容返回可以操纵||毗连符。
SQL>select ename ||'年支出'||(sal*13+nvl(comm,0)*13) "雇员的年支出" from emp;
取范围内的值(between)
SQL>select * from emp where sal between 2000 and 2500;
between 是指定区间内取值,如:between 2000 and 2500,取 2000 至 2500 内的值,同时包含
2000 和 2500
怎样操纵 like 操纵符
%:表现尽情 0 到多个字符
_:表现尽情单个字符
1)怎样表现首字符为 S 的员工姓名和人为
SQL>select ename,sal from emp where ename like 'S%';
2)怎样表现第三个字符为大写 O 的全数员工的姓名和人为
SQL>select ename,sal from emp where ename link 'O%';
在 where 条件中操纵 in
SQL>select * from emp where empno in(123,345,800);
留意:in 只能放1000个值
操纵 is null 的操纵符
在查询语句中不能操纵=大要!=null判定能否为空
SQL>select * from emp where mgr is null;
SQL>select * from emp where mgr is not null;
操纵逻辑操纵标记
查询人为高于 500 或是岗位为 manager 的雇员,同时还要满足他们的姓名首写字母为大写的 J
SQL>select * from emp where (sal>500 or job='MANAGER') and (ename like 'J%');
操纵 order by 子句
怎样依照人为的从低到高的顺序表现雇员的信息
SQL>select * from emp order by sal asc;
留意:asc 写或不写都是升序排序即从小到大排序,desc 则是降序排序从大到小排序。
操纵列的别名排序
SQL>select ename,sal*12 "年薪" from emp order by "年薪" asc;
别名必要操纵“”号圈中。
Oracle分组查询
在现实利用中经常必要实行复杂的数据统计,经常必要表现多张表的数据;要用到分组函数 max,min,avg,sum,count
Max(),min()最大最小
怎样表现全数员工中最高人为和最低人为
SQL>select max(sal) "最高人为",min(sal) "最低人为" from emp;
请查询最高年人为
SQL>select max(sal*13+nvl(comm,0)*13) "最高年人为",min(sal*13+nvl(comm,0)*13) "最低年人为" from
emp;
Avg()求均匀
表现全数员工的均匀人为和人为总和
SQL>select avg(sal) "均匀人为",sum(sal) "人为总和" from emp;
出格留意:
avg(sal)不会把 sal 为 null 的行举行统计,是以我们要留意,假如为空值也斟酌,则可以
SQL>selec sum(sal)/count(*) from emp;
count(*)求总数
盘算共有几多员工
SQL>select count(*) "共有员工" from emp;
group by 和 having 子句

group by:用于对查询的成果分组统计;
having 子句:用于限制(过滤)分组表现成果
1)怎样表现每个部分的均匀人为和最高人为
SQL>select avg(sal) "均匀人为",max(sal) "最高人为",deptno "部分编号" from emp group by deptno;
2)表现每个部分的每种岗位的均匀人为和最低人为
SQL>select avg(sal) "均匀人为",min(sal) "最低人为",job "职位",deptno "部分编号" from emp group by  deptno,job order by deptno;
对数据分组的总结:
1、分组函数(avg...)只能出现在挑选列表、having、order by 子句中;
2、假如在 select 语句中同时包含有 group by/having/order by 那末他们的顺序是 group by/having/order
by;
3、在挑选列中倘使有列、表达式和分组函数,那末这些列和表达式必须有一个出现在 group by 子句中,否则会出错。
Oralce函数
Ascii()
返回与指定的字符对应的十进制数;
SQL>select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
chr()
给出整数,返回对应的字符;
SQL>select chr(54740) zhao,chr(65) chr65 from dual;
concat()
毗连两个字符串,与||感化类似;
SQL>select concat('hello','world') from dual;
initcap()
返回字符串并将字符串的第一个字母酿成大写;
SQL>select initcap('smith') upp from dual;
instr(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1    被搜索的字符串;
C2    渴望搜索的字符串;
I    搜索的起头位置,默以为 1;
J       第  j 次出现的位置,默以为 1;
SQL>select instr('oracle training','ra',1,2) instring from dual;
length()
返回字符串的长度;
SQL>select ename, length(ename) , job , length(job), sal, length(to_char(sal)) from emp where ename='SMITH';
lower()
返回字符串,并将全数的字符小写;
SQL>select lower('AaBbCcDd') "lower-AaBbCcDd" from dual;
upper()
返回字符串,并将全数的字符大写;
SQL>select upper('AaBbCcDd') "upper-AaBbCcDd" from dual;
rpad 和 lpad(粘贴字符)
rpad 在列的右侧粘贴字符 rpad('表现内容'或字段,表现长度,'加添占位符')
lpad 在列的左侧粘贴字符 lpad('表现内容'或字段,表现长度,'加添占位符')
SQL>select lpad(rpad('htf',10,'*'),17,'*') from dual;
ltrim 和 rtrim
ltrim  删除左侧出现的字符串 ltrim('原内容'或字段,'要删除的字符串')
rtrim  删除右侧出现的字符串 rtrim('原内容'或字段,'要删除的字符串')
SQL>select rtrim('**han teng fei**','*') from dual;
substr(string,start,count)
取子字符串,从 start 起头,取 count 个
SQL>select substr('13088888888',3,8) from dual;
replace('string','s1','s2')
string   渴望被更换的字符或变量
s1 被更换的字符串
s2 要更换的字符串
SQL>select replace('he love you','he','i') from dual;
trim('s' from 'string')
假如不指定参数,默以为空格符。
SQL>select trim(0 from 0009872348900) "trim example" from dual;
数学函数

ceil(向上取整)
返回大于或即是给出数字的最小整数;
SQL>select ceil(3.14159265) from dual;
floor(向下取整)
对给定的数字取整数;
SQL>select floor(2345.67) from dual;
trunc(精度截取)
依照指定的精度截取一个数;
SQL>select trunc(124.1666,-2),trunc(124.16666,2) from dual;
round 和 trunk
依照指定的精度举行舍入;
round 函数为四舍五入
trunc(间接截取)
SQL>select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
abs
返回指定值的绝对值;
SQL>select abs(100),abs(-100) from dual;
acos
给出反余弦的值;
SQL>select acos(-1) from dual;
asin
给出归正弦的值;
SQL>select asin(0.5) from dual;
atan
返回一个数字的归正切值;
SQL>select atan(1) from dual;
cos
返回一个给定数字的余弦;
SQL>select cos(-3.14159265) from dual;
mod(n1,n2)
返回一个 n1 除以 n2 的余数;(取模函数)
SQL>select mod(10,3),mod(3,3),mod(2,3) from dual;
日期函数

先容 :日期函数用于处置赏罚 date 典范的数据。
默许情况下日期格式是 dd-mon-yy 12-7 -78
add_months(把握)
add_months(日期值,增加(淘汰)值)增加或减去月份;
SQL>select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
last_day
返回日期当月的末端一天;
SQL>select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;
months_between(date2,date1)
给出 date2-date1 的月份,共有几多个月;
SQL>select months_between('19-12 月-1999','19-3 月-1999') mon_between from dual;
next_day(date,'day')
给出日期 date 和星期 X(day)以后盘算下一个星期的日期;
SQL>select next_day('1-3 -2017','星期五') next_day from dual;
Sysdate
用来获得系统确当前日期;
SQL>select to_char(sysdate,'day') from dual;
转换典范函数
to_char(date,'format')
日期典范转换成字符串格式(重要用于将日期以风尚的格式输出表现)
SQL>select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
to_char()
出格分析: 日期格式:
yy:两位数字的年份 2004--04
yyyy:四位数字的年份 2004 年
mm:两位数字的月份 8 月--08
dd:两位数字的天数 30 号--30
hh24:二十四小时制 8 点--20
hh12:十二小时制 8 点--08 mi,ss--表现分钟\秒
day 表现星期几
month 表现几月
year 表现年
数字格式:
9:表现数字,并疏忽前面 0
0:表现数字,如位数不够,则用 0 补齐
.:在指定位置表现小数点
,:在指定位置表现逗号
$:在数字前加美圆标记
L:在数字前加当地货币标记
C:在数字前加国际货币标记
G:在指定位置表现组分隔符  D:在指定位置表现小数点标记(.)
分析:,逗号.和小数点可以合在一路操纵,G 分隔符和 D 小数点符可以合在一路操纵,但,.不能和 GD
综合操纵,否则报错。
to_date(string,'format')
将字符串转换成日期(重要用于将日期按风尚的格式输入到 oracle 数据库中)
to_number
将给出的数字典范的字符转换为数字;
系统函数

decode 函数类似于 java 的 switch case 分支语句
decode (value, if1, then1, if2,then2, if3,then3, . . . else )
DECODE(VALUE,‘0’,‘1’,‘2’)
若value为0,则为1,否则为2
表之间的毗连

自毗连
指在同一张表的毗连查询(把一张表看做两张表)
表现员工的下级带领的姓名
SQL>select e2.ename from empe1,emp e2 where e1.mgr=e2.empno;
内毗连(inner join - on)
内毗连底子语法:
select 列名 1,.. from 表 1 inner join 表 2 on 条件;
分析:内毗连只要两张表同时满足条件才会被查询到。
表现员工的信息和部分称号
SQL>selecte.*,d.dname from emp e,dept d where e.deptno=d.deptno;
等价于
SQL>selecte.*,d.dname from emp e inner join dept d  on e.deptno=d.deptno;
左外毗连 (left join - on)
假如左侧的表完全部现我们就说是左外毗连
底子语法:
select 列名 1,列名 2,.. from 表 1 left join 表 2 on 条件;
select 列名 1,列名 2,.. from 表 1,表 2 where 条件 1=条件2(+);
右外毗连 (right join - on)   
假如右侧的表完全部现我们就说是右外毗连                             
底子语法:
select 列名 1,列名 2,.. from 表 1 right join 表 2 on 条件;
select 列名 1,列名 2,.. from 表 1,表 2 where 条件 1(+)=条件2;
完全外连 (full outer join - on)
完全部现两个表,没有婚配的记载置为空
底子语法:
select 列名 1,列 2,.. from 表 1 full outer join 表 2on 条件;
分页查询

select t2.*  from ( select t1.*, rownum rnfrom (select *  from 表名) t1
where rownum=小范围(从第几条数据起头取);
出格分析:
oracle 分页查询是经过三层挑选法举行查询的。每一次都可以带 where 条件来对要查询的信息举行挑选。
第一层:构建我们所要查询字段信息并排序;
第二层:构建 rownum 别名 rn

第三层:加 where 条件,rn>=M and rn
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Copyright © 2006-2014 淄博新闻网-淄博日报 淄博晚报 淄博财经新报 掌中淄博 淄博专业新闻资讯发布网站 版权所有 法律顾问:高律师 客服电话:0791-88289918
技术支持:迪恩网络科技公司  Powered by Discuz! X3.2
快速回复 返回顶部 返回列表