Oracle数据库——基本使用《上》

最新推荐文章于 2022-04-26 13:40:20 发布

阔皮大师

最新推荐文章于 2022-04-26 13:40:20 发布

阅读量361

点赞数

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/mollen/article/details/84109184

版权

数据库专栏收录该内容

13 篇文章

订阅专栏

1、【用户操作】

/*第1步：创建临时表空间  */
create temporary tablespace mollen_temp 
tempfile 'e:\myorcldb\mollen_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  

/*第2步：创建数据表空间  */
create tablespace mollen_data  
logging  
datafile 'e:\myorcldb\mollen_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  

/*第3步：创建用户并指定表空间  */
create user mollen identified by mollen  
default tablespace mollen_data  
temporary tablespace mollen_temp;  

/*第4步：给用户授予权限  */
grant connect,resource,dba to mollen;

--删除表空间
--drop tablespace mollen;
	
--创建用户
--create user mollen	--创建用户
--identified by mollen	--设置密码
--default tablespace mollen;	--指定表空间
	
--给用户授权
--connet：连接角色，基本角色
--resource:开发者角色
--dba：超级管理员角色
--给mollen用户授予dba角色
	grant dba to mollen;
--删除授权
--revoke dba from mollen；

2、【表操作】

--创建一个user表
	create table tab_user(
		id number(20),
		uname varchar2(10)
	);
--添加一列
	alter table tab_user add (gender number(1));	
--修改列类型
	alter table tab_user rename column gender to sex;	
--删除一列
	alter table tab_user drop column sex;	
--添加一条数据
	insert into tab_user (id,uname) values(1,'小李');
	commit;
--修改一条记录
	update tab_user set uname = '小马' where id = 1;
	commit;

-------【删除表操作】------------
--删除表中全部记录
	delete from tab_user;
--删除表结构
	drop table tab_user;
--先删再建：数据量大效率高
	truncate table tab_user;

-------------【序列】---------
--默认1开始，依次递增，用于主键赋值
--dual:虚表，补全语法
	create sequence s_iuser;
	select s_iuser.nextval from dual;
--添加一条记录
	insert into tab_user(uid,uname) values(s_user.nextval,'小张');
	commit;

3、【scott用户，密码tiger】

--解锁scott用户
	alter user scott account unlock;
--解锁scoot用户密码（可用于密码重置）
	alter user scott  identified by tiger;

4、【函数】

--字符函数
	select upper('yes') from dual; --YES
	select lower('YES') from dual; --yes

--数值函数
	select round(56.16,-2) from dual; --四舍五入，后面参数表示保留几位
	select trunc(56.16,-1) from dual; --直接截取，不再看后面是否大于5
	select mod(10,3) from dual;--求余数

--日期函数
--查询emp表中所有员工入职天数
	select sysdate-e.hiredate from emp e;
--查询明天此刻
	select sysdate+1 from dual;
--查询出emp表中所有员工入职几个月
	select months between (sysdate,e.hiredate) from emp e;
--查询出emp表中所有员工入职几年
	select months between(sysdate,e.hiredate)/12 from emp e;
--查询出emp表中所有员工入职几周
	select round((sysdate-e.hiredate)/7) from emp e;

--转换函数
--日期转字符串
	select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
--字符串转日期
	select to_date('2018-6-7 16:39:50','fm yyyy-mm-dd hh24:mm-ss') from dual;

--通用函数
--算出emp表中所有员工的底薪
--奖金里面有null值，如果null值和任意数字做算术运算，
	select e.sal*12+nvl(e.comm,0) from emp e;

--多行函数【聚合函数】：作用于多行，返回一个值。
	select count(1) from emp; ---查询总数量
	select sum(sal) from emp; --工资总和
	select max(sal) from emp; --最低工资
	select avg(sal) from emp; --平均工资

5、【条件表达式】

--条件表达式的通用写法，mysql和oracle通用
--给emp表中员工起中文名
	select e.ename,
		case e.ename
		when 'SMITH' then '大王'
		when 'ALLEN' then '小王'
		when 'WARD' then '三王'
		--else '无名'
		end
	from emp e;

--判断emp表中员工工资，如果高于15000显示高收入，
--8000到15000显示中等收入
--其余显示低收入
	select e.sal,
		case
		when e.sal>3000 then '高收入'
		when e.sal>1500 then '中等收入'
		else '低收入'
		end
	from emp e;

---oracle中除了起别名，都用单引号。
---oracel专用条件表达式  
	select e.ename,
	       decode(e.ename,
	         'SMITH','大王',
	         'ALLEN','小王',
	         'WARD','三王',
	         '无名')"中文名"                                          
	from emp e;

6、【分组查询】

--分组查询中，出现再group by后面的原始列，才能出现再select后面
--没有出现再group by后面的列，想在select后面，必须加上聚合函数
--聚合函数有一个特性，可以把多行记录变成一个值
--查询出部门平均工资大于2000的
	select 
	     e.deptno,   --group by 字段
	     avg(e.sal)  --e.ename 聚合函数
	from
	     emp e
	group by
	      e.deptno
	having 
	      avg(e.sal)>2000;  

--查询每个部门工资高于800的员工的平均工资
	select 
	      e.deptno,
	      avg(e.sal) asal
	from 
	      emp e
	where 
	      e.sal>800
	group by 
	      e.deptno
	having 
	      avg(e.sal)>2000;

7、【多表查询的一些概念】

--笛卡儿积
	select 
	      * 
	from 
	      emp e,dept d
	where 
	      e.deptno =d.deptno
--等值连接
	select 
	      * 
	from 
	      emp e,dept d
	where 
	      e.deptno = d.deptno;
--内连接
	select 
	      * 
	from 
	      emp e 
	inner join 
	      dept d 
	on 
	      e.deptno = d.deptno;
--查询出所有部门，以及部门下的员工信息（外连接）
	select 
	      * 
	from 
	      emp e 
	right join 
	      dept d
	on 
	      e.deptno = d.deptno;
	      
--查询所有员工信息，以及员工所属部门
	select 
	      * 
	from 
	      emp e  
	left join 
	     dept d 
	on 
	     e.deptno = d.deptno;
	     
--oracle专用外连接
	select 
	     * 
	from
	     emp e,dept d
	where
	     e.deptno(+) =d.deptno;
     
--查询出员工姓名，员工领导姓名
--自连接：自连接其实就是站在不同的角度把一张表堪称多张表
	select 
	     e1.ename ,e2.ename
	from 
	     emp e1,emp e2
	where 
	     e1.mgr = e2.empno;

--查询出员工姓名，员工部门名称，员工领导姓名,员工领导部门名称
	select 
		e1.ename,d1.dname,e2.ename,d2.dname
	from
		emp e1,emp e2,dept d1,dept d2
	where
		e1.mgr = e2.empno
	and
		e1.deptno = d1.deptno
	and
		e2.deptno=d2.deptno;

---子查询
--子查询返回一个值
--查询出工资和scott一样的员工信息
	select * from emp where sal in
	(select sal from emp where ename = 'SCOTT')

--子查询返回一个集合
--查询出工资和10号部门任意员工一样的员工信息
	select * from 
		emp 
	where 
		sal 
	in
		(select sal from emp where deptno = 10);

--子查询返回一张表
--查询出每个部门最低工资，和最低工资员工姓名，和员工所在部门名称
--1.先查询出每个部门最低工资
	select deptno,min(sal) msal from emp group by deptno;

--2.三表联查，得到最终结果
	select 
	      t.deptno,t.msal,e.ename,d.dname
	from
	      (select deptno,min(sal) msal from emp group by  deptno) t,
	      emp e,
	      dept d
	where 
	      t.deptno = e.deptno
	and
	      t.msal = e.sal
	and
	      e.deptno = d.deptno;

8、【oracle中的分页】

--rownum 行号：当我们做select操作的时候，
--每查询出一行记录，就会再该行上加一个行号
--行号从1开始，一次递增，不能跳着走
--排序操作会影响rownum的顺序
	select rownum,e.* from emp e order by e.sal desc;
	
--如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询
	select rownum , t.* from(
	select rownum,e.*from emp e order by e.sal desc) t;

--emp表工资倒叙排列后，每页五条记录，查询第二页
--rownnum行号不能写上大于一个正数。
	select * from 
		(select rownum rn,tt.* from
			(select * from emp order by sal desc) tt 
		where rownum<11) 
	where 
		rn>5;

9、【exp/imp】

exp username/password@192.168.1.117/orcl file=d:mydb.dmp
imp username/password@192.168.1.117/orcl file=d:mydb.dmp  full=y

10、【用户授权】

CONNECT角色： --是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。
	ALTER SESSION --修改会话
	CREATE CLUSTER --建立聚簇
	CREATE DATABASE LINK --建立数据库链接
	CREATE SEQUENCE --建立序列
	CREATE SESSION --建立会话
	CREATE SYNONYM --建立同义词
	CREATE VIEW --建立视图
RESOURCE角色： --是授予开发人员的，能在自己的方案中创建表、序列、视图等。
	CREATE CLUSTER --建立聚簇
	CREATE PROCEDURE --建立过程
	CREATE SEQUENCE --建立序列
	CREATE TABLE --建表
	CREATE TRIGGER --建立触发器
	CREATE TYPE --建立类型

11、【11g空表无法导出解决方案】

--设置立刻分配表空间（设置后，后续新增的表即使没有数据会自动创建表空间，不再延迟创建）
alter system set deferred_segment_creation=false;

--查询当前用户下的所有空表
select table_name from user_tables where NUM_ROWS=0; 

-- 根据上述查询，可以构建针对空表分配空间的命令语句，执行下列查询出的语句即可
Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

--后续直接exp导出数据即可
exp gw_wh/gw_wh@127.0.0.1/orcl file=d:mydb.dmp

12、【表及表字段注释查询】

---查询表的名称，字段信息以及字段注释
	select 
		ut.COLUMN_NAME,--字段名称
		uc.comments,--字段注释
		ut.DATA_TYPE,--字典类型
		ut.DATA_LENGTH,--字典长度
		ut.NULLABLE--是否为空
	from 
		user_tab_columns  ut
	inner JOIN 
		user_col_comments uc
	on 
		ut.TABLE_NAME  = uc.table_name and ut.COLUMN_NAME = uc.column_name
	where 
		ut.Table_Name='RC_PERSON' 
	order by 
		ut.column_name

--获取表字段：
	select 
		* 
	from 
		user_tab_columns 
	where 
		Table_Name='RC_PERSON' 
	order by 
		column_name

--获取表注释：
	select 
		* 
	from 
		user_tab_comments 
	where 
		Table_Name='RC_PERSON'
	order by 
		Table_Name

--获取字段注释：
	select 
		* 
	from 
		user_col_comments 
	where 
		Table_Name='RC_PERSON'
	order by 
		column_name

--获取表:
	select table_name from user_tables; --当前用户的表      
	select table_name from all_tables; --所有用户的表  
	select table_name from dba_tables;--包括系统表
	select table_name from dba_tables where owner='GW_WH'