Oracle基本操作及详细讲解
一、Oracle语句
-
关系型数据库:mysql,oracle,sqlserver
非关系型数据库:mogodb
-
SQL支持下列类别的命令:
- 数据定义语言(DDL):create创建,alter修改,drop删除
- 数据操作语言(DML):insert 、select、update、delete
- 事务控制语言(TCL):commit 提交;rollback回滚
- 数据控制语言(DCL):grant授权;revoke撤销
- sql:结构化查询语言
-
数据类型;
-
字符:char(定长度),varchar2、nvarchar(变长度)
-
数值:number
-
日期:date,timestamp
-
-
dual表:(伪表,虚表)
-
作用:就是查询的结果和任何表都没有关系的一张表
-
dual表只有在oracle中才有
查寻系统的当前日期 select sysdate from dual select systimestamp from dual
-
-
创建一个表
create table student( sid number primary key; sname varchar2(20) not null, score number(5,2 ) check(score between 0 and 100), sex varchar2(20) default('男'), idCard varchar2(20) unique, hiredate date , enddate timestamp ) --删除表 drop table student; --添加约束 --格式:alter table 表明 modify 字段名 约束 --格式:alter table 表明 add constaint 键名 约束 alter table student modify sid primary key;
-
Oracle约束:
- 主键约束(primary key)、非空约束(not null)、外键约束(freign)、检查约束(check)、唯一约束(unique)、默认约束(default)
-
oracle:伪列 rowid和rownum
-
rowid存储的的唯一地址和rownum行号
-
***只要用伪列
-
rownum
--emp员工表(empno员工编号,ename员工名称,job工种,mgr上级领导,hiredate日志日期,sal工资,comm奖金,deptno部门) select rownum,e.* from emp e; --分页用rownum只认识<,<=;不认识>,>= --查询表中前五条的数据 select e.*from emp e where rownum<=5 --查询表中后五条的数据 select * from (select rownum r,e.* from emp e) where r>=5 and r<10
-
rwoid
--rowid select e.*,rowid from emp e --查询emp表中AAAQ+jAAEAAAAAeAAA的信息 select * from emp where rowid='AAAQ+jAAEAAAAAeAAA'
-
查询表结构只能在command中查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
desc emp; --回车
-
查看用户表
select table_name from user_tables
-
-
关键字的优先级:from、where 、group by、hiving、select 、order by
-
sql语言不区分大小写关键字,表名,字段名都不区分大小写,但是传值需要区分大小写
-
判空
--mysql:判断空使用if() --oracle:nvl() --查询员工的名称和年薪 select ename,12*(sal+nvl(comm,0))from emp
-
串联(将字段串联)
--串联 select ename || sal from emp
-
日期(oracle 查询日期必须倒置)
--日期必须倒置 --查询入职日期早于1981-5-5 select * from emp where hiredate<='5-5月-1981'
-
集合操作符
-
--查询20部门和16号部门的员工信息表 select* from emp where deptno=20 or deptno=10 select* from emp where deptno=20 union all select* from emp where deptno=10
-
-
聚合函数
-
oracle 比mysql更加安全
-
清空表
-
--清空 delete from student --截断表 truncate table studnet --delete效率慢但是安全,截断表效率快但是不安全
-
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
二、常用函数
- 数学函数
-
--数学函数 select abs(-10.5),ceil(15.2),floor(15.5),trunc(15.999,1),round(15.4) from dual;
-
字符串函数
-
字符串函数: --CONCAT(str1,str2)连接两个字符串 select concat('java','script') from dual; select concat(ename,sal) from emp --INITCAP(str)返回字符串并将字符串的第一个字母变为大写 select initcap('java') from dual; select initcap('ename') from emp --INSTR(src,search,start,number)在一个字符串中搜索指定的字符,返回发现指定的字符的位置 select instr('helloworld','l') from dual; select instr('helloworld','l',1,2) from dual; --LENGTH(str)返回字符串的长度 select length('java') from dual selct length(ename) from emp --LOWER(str)返回字符串,并将所有的字符小写 select lower ('JAVA') from dual --UPPER(str)返回字符串,并将所有的字符大写 select upper ('java') from dual --RPAD|LPAD(str,length,char) 在字符串的右(左)边粘贴字符 (右)select rpad('jvava',5,'v') from dual (左) select lpad('jvava',2,'a') from dual --RTRIM|LTRIM (str,search)删除右(左)边出现的字符串 select rtrim ('javascript','va') from dual select ltrim ('java','va') from dual --SUBSTR(str,start,count) 取子字符串,从start开始,count截取长度 select substr('oracle',1,4) from dual --REPLACE(string,s1,s2)替换字符串 select replace ('java','a','A') from dual --REVERSE( )反转字符串中的每个字符 select reverse('java') from dual
-
-
转换函数
-
转换函数: to_char 转化成字符串 to_number转化数值 to_date转化日期
-
to_char:转化成字符串
--to_char把一个数值类型的转化为字符串 select to_char(sal,'$99,999.9999') from emp; select to_char(sal,'L99,999.9999') from emp; select to_char(sal,'$000000.0000') from emp; select to_char(sal,'L000000.0000') from emp; --注:“L”表示本地货币符号 , “9”表示不补位的数字符号 ,“0”表示需补位的数字符号 --把一个日期转化成一个字符串 --查询员工表中的年份和月份 select to_char(hiredate,'yyyy-mm-dd') from emp select ename,substr(to_char(hiredate,'yyyy-mm-dd'),1,4),substr(to_char(hiredate,'yyyy-mm-dd'),6,2) from emp
-
to_number:转化数值
--to_number select to_number('999') from dual 日期不能转为数值类型
-
to_date:转化日期
--to_date select add_months(sysdate,1) from dual
-
-
连表查
-
内连接:inneer join …on
-
外联:左联:left join …on
右联:right join …on
-
全连:full join … on
-
三、对象操作
-
面向对象
最基本的对线表
对象:表空间,用户,角色,权限,锁,视图,索引,序列,触发器,存储空间 -
表空间:tablespace(默认表空间)和temporary tablespace(临时表空间)
用超级管理员(system)登录
-
tablespace(默认表空间)表空间:
--表空间:tablespace --普通用户没有这个权限,用system(超管)创建 --创建表空间 create tablespace space1 --数据文件路径 datafile 'd:/a.mdf' --文件大小 size 1M --自动扩展 autoextend on --每次扩展多大 next 1M --设置最大扩展到哪 maxsize 10M
-
temporary tablespace(临时表空间):
--临时表空间 create temporary tablespace space2 tempfile 'd:/b.mdf' ----文件大小要比表空间大 size:2M autoextend on next 1M maxsize
-
表空间的后缀名为 :.mdf
-
删除表空间:
--删除表空间 drop tablespace space1 including contents and datafiles;
-
-
用户
--创建用户user的时候必须要一个表空间和临时表空间 identified标识 --创建用户 create user lfc --创建密码 identified by 123 --默认表空间 default tablespace space1 --临时表空间 temporary tablespace space2 --分配不受限给这个用户 quota unlimited ON users; --删除一个用户 drop user lfc cascade --修改一个用户 alter user lfc identified by 456 --查看用户 select username from dba_users;
-
角色(Role)
--角色(role):connect,rescource,dba --创建角色 create role r1; --删除角色 drop role r1 --授权修改表 grant update any table to r1; grant drop any table to r1; grant select any table to r1; grant r1 to lfc --orcale里面自带了很多角色 DBA ROLE(数据库管理员角色),RESOURCE ROLE(资源角色),CONNECT ROLE(连接角色); grant CONNECT to lfc --查看所有角色 select * from dba_roles --查看角色用户权限 select * from dba_role_privs;
-
权限(grant)
--lfc没有设置权限 --登录system设置权限 --授权 create session登录的权限 grant create session to lfc; --权限privilege --系统权限和对象权限 --系统权限:系统权限允许用户执行某些数据库操作,如创建表(create table)就是一个系统权限 --对象权限:对象权限允许用户对数据库对象(如表、视图、序列等)执行特定操作 --使用权限注意事项 --不同的对象具有不同的对象权限 --对象的拥有者拥有所有权限 --对象的拥有者可以向外分配权限 --查看dba系统权限: select * from dba_sys_privs; --查看用户系统权限: select * from user_sys_privs; --授权 create table 建表的权限 grant create table lfc; --查看用户对象权限 select * from user_tab_privs; --查看dba对象权限 select * from dba_tab_privs;
-
锁(locak)
-
锁的作用:为了保护数据的安全(相对安全)
-
锁的类型:行级锁和表级锁
(1.)行级锁:可以自动加锁
(2.)表级锁:需要手动加锁
--表级锁 --locak table 表名 锁定类型 MODE --共享锁 lock table student in share mode --排他锁 lock table student in exclusive mode
-
你了解的锁有哪几种?
地址: mysql 悲观锁与乐观锁的理解 - 怀素真 - 博客园 (cnblogs.com)
mysql自动commit而oracle是手动commit
-
-
对于数据库而言都是以对象为单位的
同义词:起别名就叫同义词
-
序列:序列是自动生成的,唯一的连续号的对象
--序列sequence:序列是用于生成唯一、连续序号的对象 --创建一个序列 create sequence seq1 start with 5 --从5开始 increment by 2 --每次自增2 maxvalue 10 --最大值为10 cycle --循环 minvalue 5 --最小值为5 cache 2 --缓存 --删除序列 drop sequence seq1 --查看序列 nextval(返回下一个值) currval(当前值) select seq1.currval from dual -- 后运行 select seq1.nextval from dual --先运行 --Oracle 没有自增,用序列可以自增 insert into student values(seq1.nextval,'张三',79,'北京') insert into student values(seq1.nextval,'张三',79,'北京') insert into student values(seq1.nextval,'张三',79,'北京') insert into student values(seq1.nextval,'张三',79,'北京')
-
视图
--视图 view- 相当于一张表,是为了存储复杂的数据而创建的一个虚拟的表 --试图也称为虚表 create view v1 as select e.enam,e.sal from emp e left join dept d on e.deptno=d.deptno left join salgrade s on e.sal between s.losal and s.hisal --查询一个视图 select * from v1 --删除视图 drop view v1 --修改视图 如果有的话覆盖没有的话创建 create or replace view v1 as select e.enam,e.sal from emp e left join dept d on e.deptno=d.deptno
-
索引
--索引的作用是为了方便大批量处理数据 --索引:索引和表无关 --创建索引 --类型:唯一(unique)索引,位图(bitmap)索引 create unique index i1 on student(sname asc) --删除索引 drop index i1 --修改索引 --(1.)重命名 alter index i1 rename to i2 --(2.)合并索引 alter index i2 coalesce --(3.)重建索引 alter index i2 rebuild --查看索引 select * from all_indexes where table_name='student' --查看所有函数索引 select * from all_indexes where table_name='upper(name)' --索引的分类 --(1.)树索引 --(2.)位图索引
-
存储过程
--存储过程 --创建存储 --无参 create or replace procedure demo as id number sname varchar hiredate date begin --逻辑 exception --异常的处理 end --有参 create or replace procedure demo(pamal student.sid%TYPE) as begin sname student%TYPE age number :=20 end --选择语句
create or replace procedure demo
as
id number
sname varchar
scode number
hiredate datebegin if score>60 then dbms_output.put_line('成绩合格') else if scode>70 then dbms_output.put_line('成绩合格') else dbms_output.put_line('成绩合格') end if end ``` --有参 create or replace procedure demo(pamal student.sid%TYPE) as begin sname student%TYPE age number :=20 end --选择语句
create or replace procedure demo
as
id number
sname varchar
scode number
hiredate datebegin if score>60 then dbms_output.put_line('成绩合格') else if scode>70 then dbms_output.put_line('成绩合格') else dbms_output.put_line('成绩合格') end if end ```
-