-- 创建表空间
create tablespace xxxx_data
logging --启用记录日志
datafile 'D:\app\Administrator\oradata\orcl\xxxx_data.dbf'
size 100M
autoextend on
extent management local uniform size 128k
segment space management auto;

-- 创建临时表空间
create temporary tablespace xxxx_temp
tempfile 'D:\app\Administrator\oradata\orcl\xxxx_temp.dbf'
size 100M
extent management local;

-- 查看用户表空间在硬盘位置
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

-- 删除表空间
drop tablespace jsptbszd_data;
drop tablespace jsptbszd_data including contents and datafiles;

--创建用户并指定密码
create user xxxx identified by xxxx
default tablespace xxxx_data
temporary tablespace xxxx_temp;

-- 给用户分配权限
grant connect,resource,dba to xxxx;

-- 删除用户
drop user xxxx cascade;

-- 如果用户无法删除，查看用户连接情况
select username, sid, serial# from v$session where username='xxx';
-- 杀掉此用户进程资源
alter system kill session'sid, serial#';

-- 查看数据库服务器编码
select userenv('language') from dual;

-- 创建同义词
create [or replace] [public] synonym DM_GY_YHHB for DM_GY_YHHB@gt3;
-- 删除同义词
drop [public] synonym DM_GY_YHHB;

-- 查看数据库数据链路
select * from dba_db_links;
-- 创建公有的数据链路（public）
create [public] database link GT3
    connect to username identified by "password" 
    using '
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = xxx)
        )
    )';
-- 删除公有的数据链路（public）
drop [public] database link GT3;

-- 清除表中所有数据
delete from DM_GY_YHHB;
truncate table DM_GY_YHHB;

-- 在所有表里找某一个列名
SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = UPPER('列名');

-- 解锁 sys 和 system 用户
-- 打开cmd输入
sqlplus / as sysdba
sql> alter user system account unlock;

-- 修改用户密码，设置密码永久有效(默认180天)
-- 打开cmd输入
sqlplus / as sysdba
sql> alter user 用户名 identified by 新密码;
sql> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

-- 备份表到另一个表(丢失索引等信息)
create table dm_gy_swjg_old as select * from dm_gy_swjg_new;

-- 导入导出 expdp 和 impdp 的使用
-- 查询所有目录
select * from dba_directories;
-- 删除目录
drop directory db_bak;
-- 创建目录
create directory db_bak as 'C:\testdump';
-- 授权(需要使用其他 dba 用户)
grant read,write on directory db_bak to gscgs;
-- 按照表空间导出，查询表空间
select * from dba_data_files;
--导出
expdp gscgs/gscgs DIRECTORY=db_bak DUMPFILE=gscgs20170622.dmp TABLESPACES=GSCGS_DATA,GSCGS_DATA_PART2019,GT3_DATA;
--导入(oraname为要导入的数据库链路)
impdp gscgs/gscgs@oraname DIRECTORY=db_bak DUMPFILE=gscgs20170622.dmp TABLESPACES=GSCGS_DATA,GSCGS_DATA_PART2019,GT3_DATA;

-- 表名
SELECT * FROM SYS_MENU;
-- 创建自增序列
CREATE SEQUENCE SEQ_SYS_MENU_ID
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1
START WITH 1
NOCACHE;
-- 创建表数据插入前的触发器，主键的值从触发器获取
CREATE OR REPLACE TRIGGER MENU_INSERT_TRIGGER
  BEFORE INSERT ON SYS_MENU
  FOR EACH ROW
BEGIN
  SELECT SEQ_SYS_MENU_ID.NEXTVAL INTO :NEW.MENU_ID FROM DUAL;
END;
/
-- 最后的斜杠不能少

-- ; 分号表示一个语句的结束
-- / 表示执行前面的一个代码块
-- commit; 表示提交一个事务
-- 命令窗口下，普通语句同时使用 ; 和 / 会导致语句执行两次。

-- 含义：当 SEX=1 返回男，当 SEX=2 返回女，其他情况 返回其他。
-- 简单 Case 函数
CASE SEX
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- Case 搜索函数
CASE WHEN SEX = '1' THEN '男'
WHEN SEX = '2' THEN '女'
ELSE '其他' END

-- 含义：如果条件=值1，则返回结果1，如果条件=值2，则返回结果2，... 都不满足则返回默认结果。
DECODE(条件, 值1, 结果1, 值2, 结果2,... 值N, 结果N, 默认结果)

-- 含义：如果expr1为空那么返回expr2，如果expr1不为空则返回expr1。
NVL(expr1,expr2) 

select * from student;

-- row_number() 顺序排序
select name,course,row_number() over(partition by course order by score desc) rank from student;

-- rank() 跳跃排序，如果有两个第一级别时，接下来是第三级别
select name,course,rank() over(partition by course order by score desc) rank from student;

-- dense_rank() 连续排序，如果有两个第一级别时，接下来是第二级别
select name,course,dense_rank() over(partition by course order by score desc) rank from student;

-- ntile() 将有序分区中的行分发到指定数目的组中

select sno,sname,course,score,rank() over(order by score desc) as 名次 from student;

select sno,sname,course,score,rank() over(partition by course order by score desc) as 名次 from student;

select sno,sname,course,score,dense_rank() over(partition by course order by score desc) as 名次 from student;