Oracle数据库的用户管理、库的导入导出、数据类型、DQL语言的学习

于 2020-11-02 22:55:58 发布
阅读量154 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库 sql oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/i_am_handsome_/article/details/109440259
版权

Oracle数据库的学习

用户与表空间的管理

表名和列名命名规则：
必须以字母开头
必须在 1–30 个字符之间
必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
必须不能和用户定义的其他对象重名
必须不能是Oracle 的保留字

oracle数据库中常用角色
connect–连接角色，基本角色
resource–开发者角色
dba–超级管理员角色

三个删除

  1. delete删除表中全部记录
    delete from person;
  2. drop删除表结构
    drop table person;
  3. trancate先删除表，再次创建表。效果等同于删除表中全部记录。
    在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。
    索引可以提供查询效率，但是会影响增删改效率。
    truncate table person;
 -- oracle数据库中的单行注释
/*
oracle数据库中的多行注释 
*/

--创建表空间、表空间位置、初始大小、是否自动扩张、以及每次扩张的空间大小
CREATE TEBLEPLACE waterboss
DATAFILE 'c:\waterboss.dbf' 
SIZE 100M
AUTOEXTEND ON
NEXT 10M;

--删除表空间
drop tablespace waterboss;

--创建用户、用户口令、指定用户所属表空间
CREATE USER wateruser
IDENTIFIED BY orcl
DEFAULT TABLESPACE waterboss;

--给wateruser赋予DBA角色的权限

GRANT DBA TO wateruser;

--撤销用户的DBA权限
REVOKE DBA FROM wateruser;

--解锁scott用户
ALTER USER scott ACCOUNT UNLOCK;
--设置初始密码,也可用来重置密码
ALTER USER scott IDENTIFIED BY tiger;


--创建业主表
CREATE TABLE T_OWENERS(
      ID NUMBER PRIMARY KEY,
      NAME VARCHAR2(30),
      ADDRESSID NUMBER(18),
      HOUSENUMBER VARCHAR2(30),
      WATERMETER VARCHAR2(30),
      ADDDATE DATE,
      OWNERTYPEID NUMBER
)

数据库的导入与导出

导入导出命令只能在服务器端执行。
最好在执行的时候@实例名，可能之前oracle实例名就是默认的orcl不写也成功了，后面在linux系统上执行的时候就报了
在这里插入图片描述
这三个错误，网上查到大部分是说，口令要转义成字符串，或者账号密码错，tns服务有错误等。都没有作用
但加上@实例名意外的解决了

  1. 整库的导入导出
    导出：exp 管理员用户名/口令@实例名 file=文件名 full=y
    如果不指定文件名则默认导出文件为：EXPDAT.DMP
    导入：imp 管理员用户名/口令@实例名 full=y file=文件名
  2. 按用户导入导出
    导出：exp 管理员用户名/口令@实例名 owner=用户名 file=文件名
    导入：imp 管理员用户名/口令@实例名 file=文件名 fromuser=用户名
  3. 按表名导入导出
    导出：exp 用户名/口令@实例名 file=文件名 tables=表名,表名…
    导入：imp 用户名/口令@实例名 file=文件名 tables=表名,表名…
--整库导入导出
exp system/orcl file=water.dmp full=y
imp system/orcl full=y file=water.dmp

--按用户导入导出
exp system/orcl owner=wateruser file=wateruser.dmp
imp system/orcl file=wateruser.dmp fromuser=wateruser

-- 按表名导入导出
exp wateruser/orcl file=a.dmp tables=t_owners,t_ownertype
imp wateruser/orcl file=a.dmp tables=t_owners

数据类型

数据类型描述
VARCHAR2(size)可变长字符数据
CHAR(size)定长字符数据
NUMBER(p,s)可变长数值数据
DATE日期型数据
LONG可变长字符数据，最大可达到2G
CLOB字符数据，最大可达到4G
RAW (LONG RAW)原始的二进制数据
BLOB二进制数据，最大可达到4G
BFILE存储外部文件的二进制数据，最大可达到4G
ROWID行地址

日期加减时有一些规律:
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字

DQL语言的学习

简单的查询

--简单条件查询
select id,name,addressid from T_OWNERS where watermeter='30408';

select * from T_OWNERS where name like '%刘%';

select * from t_owners where name like '%刘%' and housenumber like '%5%';

select * from t_owners where name like '%刘%' or housenumber like '%5%';

--and的优先级比or大
select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3;

select * from t_account
where usenum >= 10000 and usenum <= 20000;

select * from t_account where usenum between 10000 and 20000;

select * from t_pricetable;

select * from T_pricetable where maxnum is null;

select * from t_pricetable where maxnum is not null;

select addressid from t_owners;

select distinct addressid from t_owners;

--两个字段数据一样时才去重
select DISTINCT Addressid ,ownertypeid from t_owners;


--排序
--默认升序ASC
select * from T_account order by usenum ;
--降序
select * from T_ACCOUNT order by usenum desc;

--伪列
--ROWID
select rowid,t.* from t_owners t where rowid='AAAM1lAAGAAAACeAAC';

--ROWNUM
select rownum,t.* from T_OWNERS t where id>3;

子查询

--where中的子查询
--单行子查询
--需求：查询2012年1月用水量大于平均值的台账记录

--①查询2012年1月平均用水量
select avg(usenum) from t_account where year='2012' and month='01';

--②查询2012年1月用水量大于20009.5
select * from t_account where year='2012' and month='01' and usenum>20009.5;

--③查询2012年1月用水量大于平均值的台账记录
select * from t_account 
where year='2012' and month='01' 
and usenum>(select avg(usenum) from t_account where year='2012' and month='01');

--多行子查询
--需求1：查询地址编号为1、3、4的业主记录
select * from t_owners where addressid=1  or addressid=3 or addressid=4;

select * from t_owners where addressid in(1,3,4);

--需求2：查询地址含有"花园"的业主的信息

--查询地址含有"花园"的地址编号
select id from t_address where name like'%花园%'; 
--查询地址编号有1和4的业主信息
select * from t_owners where addressid in(1,4);

--最终语句
select * from t_owners 
where addressid in(select id from t_address where name like'%花园%'); 

--需求3：查询地址不含有花园的业主信息
select * from t_owners 
where addressid not in (select id from t_address where name like'%花园%'); 

--from后的子查询
--需求：查询显示业主编号、业主名称、业主类型名称、条件为业主类型为"居民",使用子查询来实现。

--普通方式
select o.id 业主编号,o.name 业主名称,ot.name 业主类型 
from t_owners o ,t_ownertype ot 
where o.ownertypeid = ot.id
and ot.name='居民';

--子查询方式
select * from
(select o.id 业主编号,o.name 业主名称,ot.name 业主类型 
from t_owners o ,t_ownertype ot 
where o.ownertypeid = ot.id)
where 业主类型 = '居民';

--select字句的子查询  
--只能是单行单列
select id,name,(select name || 1 from t_address where id=addressid) from t_owners;

--需求1：列出业主信息，包括ID，名称，所属的地址。
select id,name,(select name from t_address where id=addressid) addressname
from t_owners;

--需求2：列出业主信息，包括ID，名称，所属地址，所属区域
select id,name 业主名称,
(select name from t_address where id=addressid) 所属地址,
(select (select name from t_area where areaid = id) from t_address where id=addressid) 所属区域
from t_owners; 

--分页查询
--简单分页 rownum后面只能搭配 < 或 <= 
--错误的
select rownum,t.* from t_account t where rownum>=10 and rownum<=20;
--正确的
select * from 
(select rownum r,t.* from t_account t)
where r<=20 and r>10;

--基于排序的分页
select * from t_account order by usenum desc;
--先排序再分页 三层嵌套
select * from 
(select rownum r,t.* from (select * from t_account t ORDER BY usenum DESC) t )
where r<=20 and r>10;

函数

--单行函数
/*
--字符函数
--标准的sql语句中select 后必须有from, mysql可以没有
--dual：伪表
--求字符串长度
*/
select length('abcd') from dual; 

--求字符串子串(原字符串,从第几位截取,截取的字符数)
select substr('abcd',2,2) from dual //bc

--字符串拼接
--正确的
select concat(concat('ABC','D'),'EF') from dual;
--字符串拼接符号 ||
select 'ABC'||'D'||'EF' from dual;

--错误的
select concat('ABC','D','EF') from dual;
select 'ABC'+'D' from dual;

--数值函数
--默认是不保留小数点
select round (100.456) from dual;

--指定保留小数点后几位
select round (100.245,2) from dual;

--数字截取
--默认截取整数
select trunc(100.999) from dual;

--截取小数点后两位
select trunc(100.999,2) from dual;

--取模
select mod(10,3) from dual;

--日期函数
select sysdate from dual;
--加月函数
select add_months(sysdate,-2) from dual;
--求所在月的最后一天
select last_day(sysdate) from dual;
select last_day(sysdate-30) from dual;

--日期截取  只保留日期(年、月、日)
select trunc(sysdate) from dual;
--保留年、月
select trunc(sysdate,'mm') from dual;
--保留年
select trunc(sysdate,'yyyy') from dual;
--保留到分钟
select trunc(sysdate,'mi') from dual;

--转换函数
--数字转字符串
select to_char(100) from dual;
--自动转换成字符串
select 100||'' from dual;
select 100||200 from dual;
--日期转字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月' || to_char(sysdate,'dd')||'日'  from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

--字符串转日期
select to_date('2016-03-10','yyyy-mm-dd') from dual;
--字符串转数字
select to_number('100')+10 from dual;
--自动转换
select '100'+0 from dual;


--其他函数
--空值处理 nvl
select nvl(null,0) from dual;
select nvl(maxnum,999) from t_pricetable where ownertypeid=1;
--nvl2 两者数据类型得一样
select nvl2(null,0,100) from dual;
select nvl2(maxnum,to_char(maxnum),'不限') from t_pricetable 
where ownertypeid=1;

--decode函数 Oracle特有 条件判断 参数个数为偶数的话，最后一个是缺省值，奇数的话缺省值为null
--缺省值为null
select decode(600,1,2,3,4,100,200) from dual;
--缺省值为300
select decode(600,1,2,3,4,100,200,300) from dual;
--案例：显示居民类型编号对应的居民类型名称
select name,decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业','其他') from t_owners

--case when then sql1999标准
--方式一：
select name,
(case ownertypeid 
when 1 then '居民'
when 2 then '行政事业'
when 3 then '商业'
else '其他'
end) from t_owners;
--方式二：
select name,
(case 
when ownertypeid=1 then '居民'
when ownertypeid=2 then '行政事业'
when ownertypeid=3 then '商业'
else '其他'
end) from t_owners;

行列转换、分析函数、集合运算

--行列转换
select (select name from t_area where id = areaid) 区域,
sum(case month when '01' then money else 0 end) 一月,
sum(case month when '02' then money else 0 end) 二月,
sum(case month when '03' then money else 0 end) 三月,
sum(case month when '04' then money else 0 end) 四月,
sum(case month when '05' then money else 0 end) 五月,
sum(case month when '06' then money else 0 end) 六月,
sum(case month when '07' then money else 0 end) 七月,
sum(case month when '08' then money else 0 end) 八月,
sum(case month when '09' then money else 0 end) 九月,
sum(case month when '10' then money else 0 end) 十月,
sum(case month when '11' then money else 0 end) 十一月,
sum(case month when '12' then money else 0 end) 十二月

from t_account where year='2012' 
group by areaid;

--按季度
select (select name from t_area where id = areaid) 区域,
sum(case  when month>='01' and month<='03' then money else 0 end) 第一季度,
sum(case  when month>='04' and month<='06' then money else 0 end) 第二季度,
sum(case  when month>='07' and month<='09' then money else 0 end) 第三季度,
sum(case  when month>='10' and month<='12' then money else 0 end) 第四季度

from t_account where year='2012' 
group by areaid;

--分析函数
/*
over()函数写法over(partition by expr2 order by expr3)，根据expr2对结果进行分区，在各分区内按照expr3进行排序； 

over函数不能单独使用，需要与row_number()，rank()和dense_rank，lag()和lead(),sum()等配合使用。

分区partiton by 与 group by的区别
group by会将结果集按照指定字段进行聚合，结果集会缩减，在统计部门人数，平均工资时会用到；
partition by会对结果集按照指定字段分层排列，结果集不会缩减，如将公司所有人按照部门进行分区，会发现结果集中同一部门的人会连续排列。
*/
--值相同，排名相同，序号跳跃 比如排名：1,2,2,4
select rank() over(order by usenum desc) 排名,t. * from t_account t;

--值相同，排名相同，序号连续  比如排名：1,2,2,3
select dense_rank() over(order by usenum desc) 排名,t.* from t_account t;

--序号连续，不管值是否相同,  比如排名：1,2,3,4
select row_number() over(order by usenum desc) 排名,t.* from t_account t;

--用分析函数来实现分页查询
select * 
from (select row_number() over(order by usenum desc) rownumber,t.* from t_account t)
where rownumber<=20 and rownumber>10;

--集合运算
--并集 不包括重复记录
select * from t_owners where id > 5
union 
select * from t_owners where id < 8;
--包括重复数据
select * from t_owners where id > 5
union all
select * from t_owners where id < 8;

--交集 两个结果集的重复部分
select * from t_owners where id > 5
intersect
select * from t_owners where id < 8;

--差集 上面查询的数据减下面查询重复的数据
select * from t_owners where id > 5
minus
select * from t_owners where id < 8;

select * from t_owners where id < 8
minus
select * from t_owners where id > 5;
--差集实现分页查询
select rownum ,t.* from t_account t where rownum<=20
minus
select rownum ,t.* from t_account t where rownum<=10;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值