1 表
create table <table_name> (
<column1> <数据类型1>;
<column2> <数据类型2>;
……
);
create table <new_table_name> as select * from <old_table_name>;
create table <new_table_name> as select * from <old_table_name> where 1=2；
insert into <table_name>(列1，列2，...，列n) values(值1，值2...，...，值n);
insert into <table_name>(列1，列2，...，列n) select ‘值1’,’值2’, ...,’值n’ from dual;
insert into <new_table_name> select * from <old_table_name>;
insert into <new_table_name> (column1,column2...) select column1,column2 ... from <old_table_name>;
update <table_name> set 列1=值1,列2=值2,…… [where子句]；
alter table <table_name> add <新增列名> <数据类型>;
alter table <table_name> modify <列名> <新数据类型>;
rename <old_table_name> to <new_table_name>；
alter table <table_name> rename column <旧列名> to <新列名>;
alter table <table_name> drop column <列名>;
truncate table <table_name>; //释放空间，不能回滚
delete from <table_name> [where子句]; //不释放空间，可以回滚
drop table 表名 [cascade constraints] [purge]; //释放空间，不能回滚,[删除表级约束][不能闪回]
# 通过SCN号回滚
select current_scn from v$database; //登陆管理用户,查询当前scn号,假设是100
select * from 表名 as of scn 95; //选择适当scn号找回删除前的状态
alter table 表名 enable row movement; //启用行移动功能
flashback table 表名 to scn 95; //恢复数据
# 通过timestamp回滚
select * from 表名 as of timestamp to_timestamp(‘时间’, ‘yyyy-mm-dd hh24:mi:ss’); //查询提交之前的数据
alter table 表名 enable row movement; //启动行移动功能
flashback table 表名 to timestamp to_timestamp(‘时间’, ‘yyyy-mm-dd hh24:mi:ss’); //恢复数据
三类视图：user_*(当前用户所拥有的对象的信息)、all_*（当前用户能访问的对象的信息）、dba_*（数据库中所有对象的信息，需用有管理员权限）
user_users //关于用户的信息
user_tablespaces //关于表空间的信息
user_tables //关于数据库表的信息
user_views //关于视图的信息
user_sequences //关于序列的信息
user_constraints //关于用户表约束的信息
user_tiggers //关于用户的触发器的信息
user_source //关于用户存储过程的信息
select distinct 列名 from 表名;
# between…and用于指定特定条件范围，包含边界
select empno,ename,sal from emp where sal between 1500 and 3000;
# in执行列表匹配条件，in（'值1'，'值2'，...，'值n'）
# 模糊查询like，需要用到通配符%和_,%表示0个或多个字符,_表示单个字符
注：查询字符值包含通配符，使用转义符回避。将[%]转为[\%]、[_]转为[\_]，然后加上[escape '\']即可
select * from emp where ename like 'G\_%' escape '\'; //模糊匹配G_的字符值
# 逻辑运算：与（and）、或（or）、否（not）, 优先级排序：括号、not、and、or
select empno,ename,job,sal from emp where (sal>2000 or deptno=30) and job not in ('PRESIDENT','MANAGER');
# 排列数据order by子句，ASC升序，DESC降序，语法：order by 列名 ASC|DESC
1、当select语句包含多个子句（where，group by，having，order by）时，order by必须是最后一个子句。
2、当时用order by时，默认升序
3、当存在null值时，null值最大
4、多列排序：先以第一种序列排序，当遇到相同值时，在以第二种序列排序
2 表空间
create [TEMPORARY] TABLESPACE 表空间名 TEMPFILE|DATAFILE 'XXX.dbf' size 10G; //注：创建临时表空间，需添加关键字"TEMPORARY"
drop tablespace 表空间名 [including contents [and datafiles]];
加"including contents"表示删除表空间和所有段，再加and;datafiles表示还要删除数据文件及操作系统物理文件
alter tablespace 表空间名 drop datafile 'XXX.dbf';
# 新增数据文件：
alter tablespace 表空间名 add datafile 'XXX.dbf' size 10G;
# 表空间改名
alter tablespace A rename to B;
# 修改原数据文件大小
alter database datafile 'XXX.dbf' RESIZE 10G;
# 修改数据文件自动增长200M，最大扩展5000M
alter database datafile 'XXX.dbf' autoextend on next 200m maxsize 5000m;
# 修改数据文件无限大
alter database datafile 'XXX.dbf' autoextend on maxsize unlimited;
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
select a.file_name,a.bytes/1024/1024 "TOTAL(M)",b.sb/1024/1024 "FREE(M)",round(100*b.sb/a.bytes,2) "FREE%"
from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b
where a.file_id=b.file_id order by a.file_name;
//注：dba_free_space视图不显示已写满的数据文件
# 包括"所属表空间"、"数据文件号"、"数据文件大小"、"数据文件路径"
select tablespace_name,file_id,bytes/1024/1024,file_name
from dba_data_files order by file_id;
//注：临时表空间需查询dba_temp_files
select * from v$datafile;
select default_space,temporary_tablespace from dba_users|user_users where username = '用户名';
select default_space,temporary_tablespace from user_users;
3 用户
create user <user_name> identified by <passwd> default tablespace <tablespace_name> temporary tablespace <tablespace_name>;
drop user <user_name> [cascade];
// cascade是将用户连其创建的东西全部删除
alter user <user_name> identified by <passwd>;
alter user <user_name> default tablespace <tablespace_name> temporary tablespace <tablespace_name>;
4 权限角色
系统自带三种标准角色：
connect(连接角色)[仅连接] //对普通用户授予
resource(资源角色)[创建表、视图、索引等] //对普通用户授予
dba（数据库管理员角色）[创建角色等实体] //对超级用户授予
create session //创建会话
create sequence //创建序列
create table //创建表
create user //创建用户
alter user //更改用户
drop user //删除用户
create view //创建视图
# 查看当前用户权限
select * from session_privs;
# 查看系统所有权限
select * from system_privilege_map;
# 授予权限
grant 权限1，权限2 to 用户1，用户2;
# 收回权限
revoke 权限1,权限2 from 用户;
# 对象权限
1、常见对象权限：
select,update,insert,delete,all //all包括所有对象权限
2、赋予对象权限：
grant 对象权限|all on 某个对象 to 用户|角色
eg: 赋予角色manager02对scott用户的emp表，具有查询、更新权限
grant select，update on scott.emp to manager2;
3、收回对象权限：
revoke 权限1，权限2 on 某个对象 from 用户|角色
eg: 撤销user04用户对scott用户的emp表的所有对象权限
revoke all on scott.emp from user04;
# 创建角色：
create role [自建角色名]； //创建角色
grant 权限 to 角色名； //将权限赋给角色
eg: 给角色赋予创建表和创建视图的权限
grant create table,create view to 角色;
# grant 角色 to 用户； //将角色赋予给用户
eg: 将角色赋予给user01和user02:
grant 角色 to user01,user02;
# 删除角色
drop role 角色；
# 收回角色
revoke 角色 from 用户；
# 查看当前用户角色
select * from user_role_privs;
# 查看所有角色
select * from dba_roles；
5 约束
//非空，不能重复，主键只能有一个，但可以由多个列组成
Eg1:创建表时添加主键约束
create table PASSWD_2
(
use_name VARCHAR2(30),
passwd VARCHAR2(30),
remarks VARCHAR2(30)，
constraint passwd_2_pk primary key(use_name)
)；
Eg2:创建表后添加约束：
格式:alter table 表名 add constraint 约束名 约束类型 （列名） //非空约束除外
Eg: 添加主键约束
alter table passwd add constraint passwd_2_pk primary key (use_name)；
Eg1: 创建表时添加非空约束
create table PASSWD_2
(
use_name VARCHAR2(30),
passwd VARCHAR2(30) not null, //非空约束，省略约束名
remarks VARCHAR2(30) constraint nn_rem not null， //非空约束，指定约束名
constraint passwd_2_pk primary key(use_name)
)；
Eg2：创建表时添加非空约束
alter table 表名 modify 列名 数据类型 not null
Eg1: 创建表时添加唯一约束
create table PASSWD_2
(
use_name VARCHAR2(30),
passwd VARCHAR2(30) unique, //唯一约束，省略约束名
remarks VARCHAR2(30)， //唯一约束，指定约束名
constraint passwd_2_uk unique (use_name)
)；
Eg2: 修改表时添加唯一约束：
alter table passwd_2 add constraint passwd_2_uk unique (use_name);
Eg1: 创建表时添加检查约束
create table PASSWD_2
(
use_name VARCHAR2(30),
passwd VARCHAR2(30),
remarks VARCHAR2(30)，
constraint passwd_2_ck check(use_name='张三' or use_name='李四')
)；
Eg2：修改表是添加主键约束
alter table passwd_2 add constraint passwd_2_ck check(use_name='张三' or use_name='李四')
# 建表时创建外键约束
constrait 外键名 foreign key (列名) references 主表（列名） [on delete cascade];
// on delete cascade表示级联删除
//注：
设置外键时，主从表列名数据类型保持一致;
主表字段必须是主键列（或唯一列）;
从表中外键字段的值必须来自主表中相应字段的值，或为null值
# 修改表时添加格式
alter table 从表名 add constrait 约束名 foreign key (列名) references 主表名 （列名） [on delete cascade]；
# 将约束无效化或激活
alter table 表名 disable[enable] constrait 约束名;
# 彻底删除约束
alter table 表名 drop constraint 约束名;
# 删除非空约束
alter table 表名 modify 列名 数据类型 null;
user_constraints //查看表上所有的约束
user_cons_columns //查看与约束相关的列名
6 序列
# 序列定义
- 序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。
- 其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
# 创建格式
//创建序列需要拥有create sequence系统权限
create sequence 序列名
[increment by n]
[start with n]
[{maxvalue/ minvalue n| nomaxvalue}]
[{cycle|nocycle}]
[{cache n| nocache}];
# 说明：
increment by: 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。
start with: 定义序列的初始值(即产生的第一个值)，默认为1
maxvalue: 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义。对于递减序列，最大值是-1。
minvalue: 定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义。对于递增序列，最小值是1。
cycle和nocycle 表示当序列生成器的值达到限制值后是否循环。
cache(缓冲)定义存放序列的内存块的大小，默认为20。nocache表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
nextval: 返回序列中下一个有效的值，任何用户都可以引用。
currval: 存放序列的当前值,nextval 应在 currval 之前指定,二者应同时有效。
# 使用序列
调用nextval将生成序列中的下一个序列号，调用时要指出序列名。
即用以下方式调用: 序列名.nextval。
currval用于产生序列的当前值，无论调用多少次都不会产生序列的下一个值。如果序列还没有通过调用nextval产生过序列的下一个值，先引用currval没有意义。调用currval的方法同上，要指出序列名。
即用以下方式调用:序列名.currval。
示例：
sql> create sequence seqname increment by 1 start with 1; //创建序列
sql> drop sequence t1_seq; //删除序列
sql> create table t1(id number,qq number,ww number); //建表
sql> insert into t1 values(t1_seq.nextval,1,1); //插入序列
# 修改序列
修改序列的注意事项：
1、必须是序列的拥有者或对序列有 ALTER any sequence权限
2、只有将来的序列值会被改变
3、改变序列的初始值只能通过删除序列之后重建序列的方法实现
# 查询序列
- 通过数据字典user_objects可以查看用户拥有的序列。
- 通过数据字典user_sequences可以查看序列的设置。
7 同义词
# 创建同义词格式
create [public] synonym 同义词名 for 用户名.对象名;
# 删除同义词格式
drop [public] synonym 同义词名;
8 db_link
# 创建私有[公有]db_link
用本地用户登陆本地数据库
格式：
create [public] database link 链接名 connect to 远程数据库用户名 identified by 远程数据库密码
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 远程数据库ip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 远程数据库ssid)
)
)'；
验证:
select * from 远程用户名.表名@链接名;
# 删除db_link
1、删除公有：登陆sys用户执行 drop public database link 连接名;
2、删除私有：登陆本地用户执行 drop database link 连接名;
# db_link三种权限
create database link
create public database link
drop public database link
9 函数
# 数值函数
四舍五入round（n[,m]）
n表示要进行四舍五入的值，m表示保留小数点后前几位或后几位;
省略[,m]时，m默认为0;
m>0时，取小数点后m位；m<0时，取小数点前m位。
取整函数
向上取整：ceil(n)
向下取整：floor(n)
常用计算
1、取绝对值: abs（n）
2、取余: mod（m,n） //m表示被除数，n表示除数
3、m的n次方: power（m,n）
4、n的平方根: sqrt（n）
# 字符函数
大小写转换
1、 小写转大写: upper（char）
2、 大写转小写: lower（char）
3、 首字母大写转换: initcap（char）
# 截取字符串
substr（char,[m[,n]]）
//char表示源字符串,m表示截取位置,n表示截取位数
//n省略时，从m位置截取到最后
//m为正数时，正数第m位往后截取；m为负数时，倒数第m位往后截取
# 获取字符串长度
length（char）
# 字符串连接
concat（char1,char2）
//与||操作符的作用一样，但只限2个字符,多个字符需嵌套使用
日期函数
# 查询当前日期
Select sysdate from dual;
# 在指定日期添加月: add_months(date,i)
// i可以是任何整数
// 如果i是小数，系统截取整数部分
// 如果i是负数，相当于为原日期减去月份
# 返回指定日期是下周n是哪天: next_day(date,char)
Eg: next_day(to_date('2018-12-20 10:00:00','yyyy-mm-dd hh24:mi:ss'),'星期一')
//返回20181220所在周的下周一日期
# 判定指定日期所在月的最后一天日期: last_day(date)
eg: last_day(to_date('2018-12-20','yyyy-mm-dd'))
//返回181231
# 判断两个日期之间相隔的月份: month_between(date1,date2)
# 返回datetime中date部分: extract（date from datetime）
Eg1: select extract(year from sysdate) from dual;
//获取当前时间的年份
Eg2: select extract(hour from timestamp'2017-11-25 21:03:12') from dual;
//获取小时，执行结果为21
转换函数
# 日期转换成字符：to_char(date,fmt) //fmt为转换格式，默认为DD-MON-YY
Eg: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
# 字符转换成日期: to_date(char,fmt)
Eg: select to_date('2015-05-22',' yyyy-mm-dd ') from dual;
# Timestamp与to_date
下面两个句子结果一致：
1、select next_day(timestamp'2018-12-20 10:00:00','星期一') from dual；
2、select next_day(to_date(‘2018-12-20 10:00:00’,’yyyy-mm-dd hh24:mi:ss’)) from dual;
其他函数
# 平均值: avg(column)
# 总和: sum(column)
# 最大值: max(column)
# 最小值: min(column)
# 计数不去重: count(column)
# 计数去重:count(distinct column)
# 行转列: wm_concat(column)
eg: select deptno 部门号,wm_concat(ename) 部门姓名 from emp group by deptno;
# 空值函数: nvl(column,0) //当column为空时，返回第二个值；当column不为空时，返回第一个值
10 分组函数
# 分组数据group by子句
示例一：求每个部门的平均工资，要求显示：部门号，部门的平均工资
//单条件分组查询
select deptno,avg(sal) from emp group by deptno;
注：1、在select列表中所有未包含在组函数中的列都应该包含在group by子句中
2、包含在group by子句中的列不必包含在select列表中
示例二：按部门、不同职位，统计员工的工资总额 //多条件分组查询
select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
# 过滤分组having子句
示例一：求平均工资大于2000的部门，要求显示：部门号，部门的平均工资
select deptno,avg(sal) from emp group by deptno having avg（sal）> 2000;
注：不能再where子句使用组函数，可以在having子句中使用
示例二：查询10号部门的平均工资
方式一：select deptno,avg(sal) from emp group by deptno having avg(sal)= 10;
方式二：select deptno,avg(sal) from emp where deptno = 10 group by deptno;
注：查询条件没有分组函数是，where与having效果相同，尽量使用where（效率高）
# 分组后排序order by 子句
示例: 求每个部门的平均工资，要求显示：部门号，部门的平均工资，并按平均工资排序。
select deptno,avg(sal) from emp group by deptno order by avg(sal); //或者order by 2[order by 默认升序，降序可添加字段desc]
