目录
一、DDL部分（create、drop、alter）
1.1 create 语句上
1.2 drop 语句
1.3 alter 语句
二、DML(数据操纵语言)和DQL（数据查询语言）
2.1 insert 语句
2.2 update 语句
2.3 delete 语句
2.4 select 语句
2.5 其他操纵语言
2.5.1 truncate 语句
2.5.2 merge 语句
三、用户角色权限
3.1 用户相关
3.1.1 创建用户
3.1.2 修改用户
3.1.3 删除用户
3.1.4 监视用户
3.2 权限管理相关
3.2.1 权限分类
3.2.2 系统权限
3.2.3 实体权限
3.2.3 实体权限传递(with grant option)
3.2.4 实体权限回收
3.3 角色相关
3.3.1 系统预定义角色
3.3.2 管理角色
四、工作常用sql总结
4.1 cmd连接Oracle
4.2 查看数据库版本
4.3 查看所有dblink
4.4 查看所有定时job
4.5 查看当前用户所有序列
4.6 查看数据库允许的最大连接数
4.7 查看当前的session连接数
4.8 查看并发连接数
4.9 查询用户拥有的所有表
4.10 查询数据库进程数
4.11 数据误删除恢复
4.12 获取某张表的所有字段
4.13 生成连续时间区间内时间
4.14 表空间查看及扩表空间
4.15 数据库锁表解锁
4.16 Oracle忘记密码处理方法
4.17 Oracle 小数转字符时候，保留字符串小数点前面和后面的0
4.18 Oracle分页查询
4.19 Oracle根据生日计算年龄，精确到天
4.20 子查询
4.21 显示文章、提交人和最后回复时间
4.22 外连接查询
4.23 between语句使用
4.24 in 用法
4.25 两张关联表，删除主表中已经在副表中没有的信息
4.26 四表联查
4.27 日程安排提前五分钟提醒
4.28 查询前10条记录
4.29 选择在每一组b值相同的数据中对应的a最大的记录的所有信息
4.30 包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行
4.31 随机取出10条数据
4.32 删除重复记录
4.33 union 和union all 语句
4.34 intersect 语句
4.35 minus 语句
4.36 Oracle 导入导出
工作中我们基本上每天都要与数据库打交道，数据库的知识点呢也特别多，全部记住呢也是不可能的，也没必要把所有的记住（有些语句命令可能我们一辈子都用不到）。
所以呢在工作之余，把工作中经常用到的一些语句整理出来，忘记的时候可以当做字典来查。
个人在工作中用Oracle数据库比较多，就以关系型数据库Oracle为例进行整理，后面可能会整理一些非关系型数据库，如mogodb之类的。
如果你觉得有所价值可以参考。如果有不全或者错误的也欢迎大家指正。
一、DDL部分（create、drop、alter）
1.1 create 语句上
①语法：
create table table_name (
column_name datatype [null|not null],
column_name datatype [null|not null],
...
[constraint]
)
-- table_name： 数据库表名称，在一个数据库中数据表名称不能重复
-- column_name ：表中的列名，列名在一个表中也不能重复
-- datatype：该列存放数据的数据类型
-- [null|not null] ：该列是否允许为空
-- [constraint]：为表中的列设置约束（主键约束、外检约束、检查约束等）
②举例：创建一张商品信息表(productinfo)，表包含商品编号、商品名称、商品价格、商品数量、商品类型、商品描述、产地7个字段，并设置主键约束、非空、检查约束等
create table productinfo(
productid varchar2(10) PRIMARY KEY,
productname varchar2(50) not null,
productprice number(8,2) not null,
qty number(10),
category varchar2(10),
desperation varchar2(400),
orign varchar2(50)
CONSTRAINT productname_uk UNIQUE(productname)
)
1.2 drop 语句
① 语法：
drop table table_name;
②举例：删除上面所创建的商品信息表
drop table productinfo;
1.3 alter 语句
① 语法
alter table table_name
add column_name | modify column_name | drop column column_name;
--add column_name : 用于向表中添加列
--modify column_name ： 用来修改表中已存在的列信息
--drop column ： 删除表中列
② 举例 ： 向商品信息表中添加备注字段、修改备注字段类型、删除备注字段
alter table productinfo add remark varchar2(200);
alter table productinfo modify remark number(2,2);
alter table productinfo drop column remark;
二、DML(数据操纵语言)和DQL（数据查询语言）
2.1 insert 语句
① 语法：
insert into table_name(colunm_name1,colunm_name2,colunm_name3,...)values(data1,data2,data3...)
-- colunm_name1: 指定表中要添加数据的列名，可以是一个或多个
-- data1：要填入指定列的数据值，值的数目要与列数量一致
② 举例：向商品信息表中添加一条商品信息
insert into productinfo
(productid, productname, productprice, qty, category, desperation, orign)
values
('10001', '电视机', 3250, 2, '01', '65寸智能电视', '小米集团');
2.2 update 语句
① 语法：
update table_name set colunm_name1=data1,colunm_name2=data2,...{where condition};
② 举例：将商品信息表中的电视机名称修改成“小米电视机”，价格修改成4500
update productinfo
set productname = '小米电视机',
productprice = 4500
where productid = '10001';
2.3 delete 语句
① 语法：
delete from table_name {where condition};
② 举例：删除商品信息表中编号为10001 的数据
delete productinfo
where productid = '10001';
2.4 select 语句
① 语法：
select colunm_name1,colunm_name2,colunm_name3,... from table_name {where condition};
② 举例：查询出商品编码为10001的商品信息
select productid, productname, productprice, qty, category, desperation, orign from productinfo where productid = '10001'
2.5 其他操纵语言
2.5.1 truncate 语句
truncate语句和delete语句一样都是用来删除表中数据，但是两者是有区别的，使用truncate语句是没有条件的删除，可以把表中数据全部删除，truncate删除表中数据的速度比delete快
① 语法
truncate table table_name;
② 举例：删除商品信息表中全部数据
truncate table productinfo;
2.5.2 merge 语句
merge语句与update语句功能类似，都是修改表中数据。但是两者是有区别的，merge可以对数据表同时进行增加和修改操作
① 语法
merge [into] table_name1
using table_name2
on (condition)
when matched then merge_update_clause
when not matched then merge_insert_clause;
-- table_name1 : 要修改或添加的表
-- table_name2：参照的更新的表
-- condition ： table_name1 和 table_name2 表之间的关系，或其他条件
-- merge_update_clause：条件匹配执行语句
-- merge_insert_clause：条件不匹配执行语句 可以去掉
② 举例：当满足条件时，根据fin_item_compare表中的itemcode 更新ipb_itemlist表的sicode字段
merge into ipb_itemlist t1 using fin_item_compare t2
on (t1.orgcode = t2.orgcode and t1.itemid = t2.itemid and t1.isdrug= '1' and t1.inid = '30675328')
when matched then
update set t1.sicode = t2.itemcode
三、用户角色权限
3.1 用户相关
3.1.1 创建用户
① 语法：
create user username
identified by password
or externally as certificate_dn
or globally as directory_dn
[default tablespace tablespacename]
[temporary tablespace tablespaceName]
[profile profile]
[quota integer|unlimited on tablespaceName]
[password expire]
[account lock|unlock]
--username : 用户名称
--identified by password：用户口令
--[default tablespace tablespacename] ：默认表空间；
--[temporary tablespace tablespaceName] ：临时表空间；
--[profile profile]：设置当前用户使用的概要文件的名称
--[quota integer|unlimited on tablespaceName]：设置当前用户使用表空间的最大值，如果设置成unlimited 表示对表空间的使用没有限制
--[password expire]: 设置当前用户密码立即处于过期状态，用户如果想再登陆数据库必须要更改密码
--[account lock|unlock]: 设置当前用户锁的状态，lock表示不能访问数据库unlock表示可以访问数据库
②举例：创建一个user2的用户，并且设置密码为立即过期方式
create user user2
identified by abcd --口令是abcd
default tablespace test --默认表空间是test
quota 10M on test -- 在表空间test中使用的磁盘限额是10MB
temporary tablespace temp --临时表空间为temp
profile pro_test --使用概要文件是pro_test
password expire --密码为立即过期状态
3.1.2 修改用户
①语法：
alter user username identified
{by password [replace old_pwssword]
|externally as certificate_dn
| globally as directory_dn
[default tablespace tablespacename]
[temporary tablespace tablespaceName]
[profile profile]
[quota integer|unlimited on tablespaceName]
[password expire]
[account lock|unlock]
}
②举例：修改用户user2的密码为123456
alter user user2 identified by 123456 --修改user2密码为123456
③举例：修改用户缺省表空间
Alter user user2 default tablespace users; --修改user2缺省表空间为users
④举例：修改用户临时表空间
Alter user user2 temporary tablespace temp_data; --修改user2临时表空间为temp_data
⑤举例：强制用户修改口令字
Alter user user2 password expire; --强制用户修改口令
⑥用户加锁、解锁
Alter user user2 account lock; -- 加锁
Alter user user2 account unlock; -- 解锁
3.1.3 删除用户
①语法：
drop user username
② 举例：删除user2用户
drop user user2;
3.1.4 监视用户
①查询用户会话信息
select username, sid, serial#, machine from v$session;
② 删除用户会话信息
Alter system kill session 'sid, serial#';
③查询用户SQL语句
SQL> select user_name, sql_text from v$open_cursor;
3.2 权限管理相关
3.2.1 权限分类
系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。
实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。
3.2.2 系统权限
①系统权限分类
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。
②系统权限授权命令
系统权限只能由DBA用户授出：sys, system最开始只能是这两个用户。普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。
授权语法：
grant connect, resource, dba to 用户名1 [,用户名2]...;
举例：给user2授权
grant connect, resource to user2;
查询用户权限：
select * from dba_role_privs;
select * from dba_sys_privs;
select * from role_sys_privs;
drop user 用户名 cascade; --加上cascade则将用户连同其创建的东西全部删除
③ 系统权限传递
增加WITH ADMIN OPTION选项，则得到的权限可以传递。
grant connect, resorce to user2 with admin option; --可以传递所获权限。
④ 系统权限收回
Revoke connect, resource from user2;
说明：
(1)如果使用WITH ADMIN OPTION为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的系统权限并不会级联取消这些用户的相同权限。
(2)系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响；系统权限可以跨用户回收，即A可以直接收回C用户的权限。
3.2.3 实体权限
①实体权限分类：
select、 update、 insert、alter、index、 delete、all （all包括所有权限）、execute（执行存储过程权限）
举例：proudct 属于user01表，将proudct 权限授权给usert02
user01:
grant select, update, insert on product to user02;
grant all on product to user02;
user02:
select * from user01.product; --此时user02可以查询到user01.product
②将表的操作权限授予全体用户
grant all on product to public; -- public表示是所有的用户，这里的all权限不包括drop。
[实体权限数据字典]:
select owner, table_name from all_tables; -- 用户可以查询的表
select table_name from user_tables; -- 用户创建的表
select grantor, table_schema, table_name, privilege from all_tab_privs; -- 获取可以存取的表（被授权的）
select grantee, owner, table_name, privilege from user_tab_privs; -- 授出权限的表(授出的权限)
③DBA用户可以操作全体用户的任意基表(无需授权，包括删除)
DBA用户具有以下权限：
/*
创建其他用户的表
*/
Create table stud02.product(
id number(10),
name varchar2(20)
);
/*
删除其他用户的表
*/
drop table stud02.emp;
/*
根据用户1的数据为用户2创建表
*/
create table stud02.employee
as
select * from scott.emp;
3.2.3 实体权限传递(with grant option)
user01:
grant select, update on product to user02 with grant option; -- user02得到权限，并可以传递。
3.2.4 实体权限回收
user01:
Revoke select, update on product from user02; --传递的权限将全部丢失。
说明
（1）如果取消某个用户的对象权限，那么对于这个用户使用WITH GRANT OPTION授予权限的用户来说，同样还会取消这些用户的相同权限，也就是说取消授权时级联的。
3.3 角色相关
角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限
3.3.1 系统预定义角色
oracle数据库安装之后会自动创建一些角色
① CONNECT, RESOURCE, DBA
这些预定义角色主要是为了向后兼容。其主要是用于数据库管理
② DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
这些角色主要用于访问数据字典视图和包。
③ EXP_FULL_DATABASE， IMP_FULL_DATABASE
这两个角色用于数据导入导出工具的使用。
④ AQ_USER_ROLE， AQ_ADMINISTRATOR_ROLE
AQ:Advanced Query。这两个角色用于oracle高级查询功能。
⑤ SNMPAGENT
用于oracle enterprise manager和Intelligent Agent
⑥ RECOVERY_CATALOG_OWNER
用于创建拥有恢复库的用户
⑦ HS_ADMIN_ROLE
3.3.2 管理角色
① 建一个角色
create role role1;
② 将权限授权给角色
grant create any table,create procedure to role1;
③ 将角色授予角色给用户
grant role1 to user1;
④ 查看角色所包含的权限
select * from role_sys_privs;
⑤ 创建带有口令的角色(在生效带有口令的角色时必须提供口令)
create role role1 identified by password1;
⑥ 修改角色：是否需要口令
alter role role1 not identified;
alter role role1 identified by password1;
⑦ 设置当前用户要生效的角色
(注：角色的生效是一个什么概念呢？假设用户a有b1,b2,b3三个角色，那么如果b1未生效，则b1所包含的权限对于a来讲是不拥有的，只有角色生效了，角色内的权限才作用于用户，最大可生效角色数由参数MAX_ENABLED_ROLES设定；在用户登录后，oracle将所有直接赋给用户的权限和用户默认角色中的权限赋给用户。）
set role role1;--使role1生效
set role role,role2;--使role1,role2生效
set role role1 identified by password1;--使用带有口令的role1生效
set role all;--使用该用户的所有角色生效
set role none;--设置所有角色失效
set role all except role1; --除role1外的该用户的所有其它角色生效。
select * from SESSION_ROLES;--查看当前用户的生效的角色。
⑧ 修改指定用户，设置其默认角色
alter user user1 default role role1;
alter user user1 default role all except role1;
⑨ 删除角色
角色删除后，原来拥用该角色的用户就不再拥有该角色了，相应的权限也就没有了。
drop role role1;
四、工作常用sql总结
4.1 cmd连接Oracle
sqlplus scott/tiger@192.168.205.100:1521/orcl
4.2 查看数据库版本
select * from v$version;
4.3 查看所有dblink
select * from ALL_DB_LINKS;
4.4 查看所有定时job
select * from all_jobs;
4.5 查看当前用户所有序列
select * from user_sequences; --last_number就是此刻执行nextval的值，last_number - increment_by 就是当前值
4.6 查看数据库允许的最大连接数
select value from v$parameter where name ='processes';
4.7 查看当前的session连接数
select count(*) from v$session;
4.8 查看并发连接数
select count(*) from v$session where status='ACTIVE';
4.9 查询用户拥有的所有表
select * from all_tables where owner='TEST';
4.10 查询数据库进程数
select value from v$parameter where name = 'processes'; --取得进程数的上限。
select count(*) from v$process; --取得数据库目前的进程数。
4.11 数据误删除恢复
select * from tablename as of timestamp sysdate -1/24; --一小时前表数据
select * from tablename as or timestamp sysdate-5/1440; --5分钟前的表数据
4.12 获取某张表的所有字段
select * from user_tab_columns where table_name ='表名大写'
4.13 生成连续时间区间内时间
--2019-03-13 日00 点到23 点内时间
SELECT to_char(to_date('2019-03-13 00', 'yyyy-mm-dd hh24') +
(ROWNUM - 1) / 24,
'yyyy-mm-dd hh24') sdate
FROM dual
CONNECT BY ROWNUM <= (to_date('2019-03-13 23', 'yyyy-mm-dd hh24') -
to_date('2019-03-13 00', 'yyyy-mm-dd hh24')) * 24 + 1
4.14 表空间查看及扩表空间
①查询表空间的大小（表空间名称、总大小、还剩多少）
select df.tablespace_name 表空间,
totalspace 总_M,
freespace 剩_M,
round((1 - freespace / totalspace) * 100, 2) || '%' 使用率
from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace
from dba_free_space
group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name
and df.tablespace_name like '%%'
order by df.tablespace_name
② 查询需要扩的表空间的绝对路径
select t1.name, t2.name
from v$tablespace t1, v$datafile t2
where t1.ts# = t2.ts#
③扩表空间（三种方法）
注意：一个数据文件最大只能32G；
--1.手工改变已存在数据文件的大小
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP01.DBF' SIZE 20480M;
--2.允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP01.DBF'
AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;
--3.增加数据文件(设置的每个文件初始分配空间为7g, autoextend on为自动增长大小，oracle单个文件大小最大不超过32g)
--这里增加两个数据文件，需要扩容的表空间是APP_DATA
ALTER TABLESPACE APP_DATA ADD DATAFILE
'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\APP02.DBF'
size 7167M autoextend on ;
ALTER TABLESPACE APP_DATA ADD DATAFILE
'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\APP04.DBF'
size 7167M autoextend on ;
4.15 数据库锁表解锁
注意： 如果数据库是集群，则在解锁的时候需要所有节点都查看，否则可能会漏掉
① 查看锁表情况
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
--and o.object_name='table_name' --object_name 表示表名
ORDER BY sid, s.serial#;
② 解锁
alter system kill session 'sid,serial#'; --其中sid和serial#由1中查出
③ 以上两步也可以合并为一下一个sql,查出锁表语句后直接执行即可
SELECT 'ALTER system kill session ''' || s.sid || ', ' || s.serial# ||
'''; ',
object_name,
machine,
s.sid,
s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
and o.object_name = upper('R_REGISTER');
4.16 Oracle忘记密码处理方法
①免密登陆
sqlplus /nolog
② 切换到用户
conn /as sysdba
③ 修改密码
alter user sys identified by 123456; -- 将sys用户密码修改为123456
注意：如果提示sqlplus /nolog不是内部命令
确保oracle安装成功
找到此路径oracle的安装目录: 我的是在D:\app\Administrator\product\11.2.0\dbhome_1\BIN ,将此路径配置到环境变量path中即可
4.17 Oracle 小数转字符时候，保留字符串小数点前面和后面的0
① 保留小数点前面的0
SQL> select to_char(0.1) from dual
2 /
TO_CHAR(0.1)
------------
.1
--解决办法
SQL> select to_char(0.1,'fm9999990.9999') from dual
2 /
TO_CHAR(0.1,'FM9999990.9999')
-----------------------------
0.1
② 保留小数点后面的0
SQL> select to_char(2.30) from dual
2 /
TO_CHAR(2.30)
-------------
2.3
--解决办法
SQL> select to_char(2.30,'fm9999999.0000') from dual
2 /
TO_CHAR(2.30,'FM9999999.0000')
------------------------------
2.3000
③ 即保留小数点前面的0也保留小数点后面的0
SQL> select to_char(0.10) from dual
2 /
TO_CHAR(0.10)
-------------
.1
--解决办法
SQL> select to_char(0.10,'fm9999990.00') from dual
2 /
TO_CHAR(0.10,'FM9999990.00')
----------------------------
0.10
4.18 Oracle分页查询
-- 查询5到10行数据
select *
from (select *
from (select t.*, rownum rn from emp t)
where rownum <= 10)
where rn >= 6
4.19 Oracle根据生日计算年龄，精确到天
select trunc(months / 12) || '岁',
trunc(mod(months, 12)) || '月',
trunc(sysdate - add_months(birth, trunc(months))) || '天' age
from (select months_between(sysdate, birth) months, birth
from (select date '1992-09-13' birth from dual));
4.20 子查询
select a,b,c from a where a IN (select d from b ) ;--
4.21 显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b;
4.22 外连接查询
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c;
4.23 between语句使用
select * from table1 where time between time1 and time2; --限制查询数据范围时包括了边界值
select a,b,c, from table1 where a not between 数值1 and 数值2;--限制查询数据范围时不包括边界
4.24 in 用法
select * from table1 where a [not] in ('值1','值2','值4','值6');
4.25 两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 );
4.26 四表联查
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
4.27 日程安排提前五分钟提醒
select * from 日程安排 where datediff('minute',f开始时间,getdate())>5;
4.28 查询前10条记录
select top 10 * form table1 where 范围;
4.29 选择在每一组b值相同的数据中对应的a最大的记录的所有信息
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b);--可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等
4.30 包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行
(select a from tableA ) except (select a from tableB) except (select a from tableC);
4.31 随机取出10条数据
select top 10 * from tablename order by newid();
4.32 删除重复记录
delete from tablename where id not in (select max(id) from tablename group by col1,col2,...);
4.33 union 和union all 语句
--返回两个查询选定的所有不重复的行
select deptno from emp union select deptno from dept;
--合并两个查询选定的所有行，包括重复的行
select deptno from emp union all select deptno from dept;
4.34 intersect 语句
--只返回两个查询都有的行
select deptno from emp intersect select deptno from dept;
4.35 minus 语句
--返回由第一个查询选定但是没有被第二个查询选定的行, 也就是在第一个查询结果中排除在第二个查询结果中出现的行
select deptno from dept minus select deptno from emp;
4.36 Oracle 导入导出
--第一种 exp导出imp导入
exp system/manager@127.0.0.1:1521/orcl file=d:\scott.dmp owner=scott log=d:\ch_exp.log buffer=999999
imp system/manager@127.0.0.1:1521/orcl file=d:\scott.dmp log=d:\scott_imp.log fromuser=(scott) touser=(scott) buffer=999999 ignore=y
--第二种 expd导出impd 导入
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott
目前能想到的就这么多了，后面会据需更新。大佬们觉得有漏的也可以多多指点
