Oracle数据库SQL基础知识整理

Oracle IT敢客 2年前 (2017-08-05) 10094次浏览 已收录 0个评论 扫描二维码

        Oracle 是以高级结构化查询语言(SQL)为基础的大型关系数据库,通俗地讲它是用方便逻辑管理的语言操纵大量有规律数据的集合。是目前最流行的客户/服务器(CLIENT/SERVER)体系结构的数据库之一。

1.创建表空间

create tablespace tablespace_name datafile 'F:\sql\tablespace_name.dmp'            - -表空间名
size 50M                                                    - -大小
autoextend on                                                - -自动扩展
next 50M maxsize 2048M                                 - -每次扩展 50M
extent management local;

2.创建用户并赋予权限

- -创建一个用户并制定表空间和临时表
create user user_name identified by 123456 default tablespace tablespace_name temporary tablespace temp;
- -赋予权限给用户
grant connect,resource,dba to user_name;
--回收权限
revoke connect from user_name;
--修改用户密码
alter user user_name identified by 12345678;

 

3.创建表

--创建新表
 create table table_name{ id, name, content  };
--根据结果集创建新表,如果只复制表结构,只需要查询的条件不成立(比如 where 1=2)
create table table_new_name as select * from table_name;
create table table_new_name as select * from table_name where 1=2;

4.插入数据

insert into table_name (s_id,b,c) values(1,2,2);
--一次插入 emp 表中的所有数据至 emp1 表中
insert into table_new_name select * from table_name;

5.更新数据

update tb_name set sex='男',age='24' where name='小芳';
--将奖金为 0 的员工上调至 100 元
update emp set comm=100 where comm=(select comm from emp where comm=0);

6.查询数据

--获取当前系统日期和时间。sysdate 返回的是当前日期;systimestamp 返回当前日期、时间和时区
select sysdate,systimestamp from dual;
 
--order by 排序,默认升序  order by XX  desc 降序
select * from tb_name where gender='男' order by age;
 
--算术运算符(+、-、*、/、MOD(x,y))
select name, mark,(mark*12) from tb_name where mark>80;
 
--关系运算(=、!=、<、>、<=、>=)和逻辑运算(and、or、not)
--字符串连接操作符(||),
select (name ||'is a '|| sex)as "students details" from tb_name where degree='3';--别名存在空格,必须使用双引号
 
---消除重复行
select distinct t_id from tb_name;
 
---null 操作,not is null
select t_id,t_name,t_mark,sex from tb_name where t_mark>80 and sex is null;
 
 
---in 操作(where 子句中可以使用 in 操作符来查询其列值在制定的列表中的行),对应有 not in 操作
select t_name,t_mark,sex,t_degree from tb_name where t_degree in('1','2');
 
---between...and...指定区间(包括边界)
---like 模糊查询(%、_)
select t_id,t_name from tb_name where t_name like 'l%y';--零个或多个任意字符
select t_id,t_name from tb_name where t_name like '_y';--任意一个字符
 
--集合运算
---intersect(交集)
---union all(并集),各个查询的所有记录,包括重复的记录
---union(并集),不包括重复记录
---minus(补集),返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录
 
/*连接查询
---内连接:inner join
---外连接:outer join(left outer join,right outer join)
*/
--内连接(年级名称存放在年级表中)
select s.name,s.mark,d.name from students s,degrees d where s.degree=d.degree and s.make>80;
select s.name,s.mark,d.name from students s inner join degrees d on s.degree=d.degree where s.mark>80;
 
--外连接
select e.ename,e.sal,d.dname from emp e,dept d  where e.deptno(+)=d.deptno;--右外连接(当(+)出现在等号右边则是左外连接)
select e.ename,e.sal,d.dname from emp e right outer join dept d on e.deptno=d.deptno--当部门无人员时,也可以检索出来
--查询和 bake 同一部门的员工的项目和受雇日期,但不包括 bake
select e.ename,e.sal,d.dname from emp e right outer join dept d on e.deptno=d.deptno where not exists (select * from emp e where e.ename='bake')
 
/*
*子查询和常用函数
*在 select、update、deleta 语句内部可以出现 select 语句
*1.单行子查询:不向外部返回结果,或者只返回一行结果
*2.多行子查询:向外部返回零行、一行、或多行结果
*/
--查询出员工表中前 5 名员工的姓名、工作、工资
select rownum,ename,job,sal from emp where rownum<=5;
 
--查询员工表中工资最高的前 5 名员工的姓名、工作、工资
select rownum,t.* from (select ename,job,sal from emp order by sal desc)t where rownum<=5;
--查询 emp 表中第五条到第十条的记录
select * from (select rownum r,ename,job,sal from emp where rownum<=10) where r>5;
 
/*
*oracle 函数
*1.单行函数:对于每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果。
*                    比如:字符函数、数字函数、转换函数、日期函数
*2.聚合函数:可以对多行数据进行操作,并返回一个结果
*                    比如:sum(x)
*/
 
--字符函数:
---1.ASCII(x):返回 x 的 ASXII 码
select ASCII('a') from dual;
---2.concat(x,y)连接字符串等
select concat('hello','world') from dual;
---3.instr(x,str[start],n)从 x 中查找 str,可以指定从 start 开始,也可以指定从第 n 次开始
select instr('hello world','or')from dual;
---4.length(x)返回字符串的长度
select length('hello') from dual;
---5.lower(x)x 转换为小写
---6.upper(x)x 转换为大写
---7.ltrim(x,trim_str)把 x 左边的截取 trim_str 字符串
select ltrim('===hello====','=')from dual;
select '=='||ltrim('===hello====','=')from dual; --  ==hello====
---8.rtrim (x,trim_str)把 x 右边的截取 trim_str 字符串
 
 
---9.trim(trim_str from x)去掉两边的 trim_str
select trim('='from'===hello====')from dual;   --   hello
---10.replace(x,old,new)在 x 中查找 old,并替换为 new
select replace('aabbcc','bb','sss')from dual;   --aassscc
---11.substr(x,start,length)返回 x 的字符串,从 start 开始,截取 length 个字符
select substr('abcde',2)from dual;   -- bcde
select substr('abcde',2,3)from dual; --bcd
 
--转换函数
---1.to_char(d,fmt)   把日期和数字转换为指定格式的字符串,fmt 格式化字符串
select to_char(sysdate,'YYYY"年"MM"月"DD"日"HH24:MI:SS')"date" from dual;
---2.to_date(x,fmt)
---3.to_number(x,fmt)
 
--其他单行函数
---1.nvl(x,value)如果 x 为空,返回 value,否则返回 x
--对工资 2000 以下的员工,如果没有发奖金,每人奖金 100 元
select ename,job,sal,nvl(comm,100) from emp where sal<2000;
---2.nvl2(x,value1,value2)如果 x 非空,返回 value1,否则返回 value2
 
--求出各个部门工资的平均值(聚合函数 avg(),max())
select min(d.dname),e.deptno,trunc(avg(e.sal),2) from emp e left join dept d on e.deptno=d.deptno group by e.deptno;

7.创建序列

--创建序列
create sequence sequence_name
start with 0    --从一整数开始,升序默认值为 1,降序默认值-1
increment by 1  --增长数,如果是正数则升序,负数降序。升序默认值为 1,降序默认值-1
maxvalue 100    --最大值
minvalue 10     --最小值
cycle   --nocycle    --达到最大值后循环
cache 5;--nocache    --该序列会根据序列规则预生成一组序列号。保存在内存中,使用下一个序列号,可以更快的响应。当内存中的序列号用完时,会再生成一组新的序列号
                    --并保存到缓存中。默认生产 20 个序列号
                   
--序列使用 (currval 当前值、nextval 下一个值)                
select empno.nextval from emp;
 
--修改序列.限制:不能修改序列的初始值。最小值不能大于当前值,最大值不能小于当前值
alter sequence empno maxvalue 10000 minvalue -300;
                   
--删除序列
drop sequence empno;

8.创建视图

--视图
create or replace view empdetall
as
select empno,ename,job,mgr,hiredate,emp.deptno,dname from emp join dept on emp.deptno=dept.deptno
with read only;

9.创建索引

--索引
create unique index uq_ename_idx on emp(ename);  --唯一索引
create index sal_idx on emp(sal);   --普通索引
create index lower_job_idx on emp(lower(job));   --先把 job 列变成小写再创建索引
 
--查询索引                                                                                
select * from user_indexes where table_name='emp';
select * from user_ind_columns where index_name='uq_ename_idx';

 

10.数据泵模式(逻辑备份 expdp/impdp  exp/imp)

--命令提示符 导入导出数据
----进入数据库创建数据目录
create directory expnc_dir as 'E:\lyt_dir';
----导出 sql:
expdp system/123456@lyt directory=lyt_dir dumpfile=test.dmp
----导入 sql:
impdp system/123456@lyt directory=lyt_dir dumpfile=test.dmp full=y
 
----12c 数据库导出 dmp 后,导入到 11g 中,导出需指定版本
expdp system/123456@lyt directory=lyt_dir dumpfile=test.dmp version=11.2.0.1

 


IT 敢客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:Oracle 数据库 SQL 基础知识整理
喜欢 (155)
[313176056@qq.com]
分享 (0)
IT敢客
关于作者:
“我所做的一切都是为了方便我的生活~~~“
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址