Oracle超全SQL，细节狂魔

　　前言
　　SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。  1.基本命令 1、登陆SQL*Plus（以system用户登陆） conn system/jeames@orcl conn sys/jeames@orcl as sysdba 断开连接 – disc  2、显示用户名 show user  3、设置显示行的宽度为100 set linesize 100  4、设置每页显示的行数目为18  set pagesize 18  5、显示当前数据库的全称 select * from global_name;  6、Oracle中究竟有多少种角色 select * from dba_roles;  7、查询数据库的表空间，一般是DBA用户去查 SQL>select tablespace_name from dba_tablespaces;  8、查询Oracle中所有的系统权限 SQL>select * from system_privilege_map order by name;  9、查询Oracle中所有的对象权限 SQL>select distinct privilege from dba_tab_privs;  10、当前用户下的表 SQL>select table_name from user_tables;  11、 显示当前数据库可以访问的所有数据字典视图 SQL>select * from dict where comments like’%grant%’;  12、查看某个用户具有什么样的角色？ SQL>select * from dba_role_privs where grantee=‘SCOTT’;  13、查看某个用户（角色）具有什么样的系统权限？ SQL>select * from dba_sys_privs where grantee=‘SCOTT’;  14、查看某个用户（角色）具有什么样的对象权限？ SQL>select * from dba_tab_privs where grantee=‘SCOTT’;  15、查询Oracle中所有用户信息 SQL> select * from all_users;  16、关闭数据库 SQL>shutdown  17、启动数据库 SQL>startup 18、显示初始化参数 SQL>show parameter 19、提交事务 SQL>COMMIT; 20、打开输出选项 SQL>set serveroutput on ————————————————  2.用户管理 1、创建用户watchdog SQL> create user watchdog identified by watchdog;  2、给用户watchdog修改密码 SQL>password watchdog  3、删除用户watchdog –删除的用户已经创建了表，就需要在删除时带一个参数cascade SQL>drop user watchdog cascade  4、运行Sql脚本（f:wdd.sql） SQL>@ d:wdd.sql SQL>start d: wdd.sql  5、将内容输出到指定文件中去 SQL>spool d:bb.sql SQL>select * from emp SQL>spool off  6、授权resource角色给watchdog –resource角色可以创建表 SQL>grant resource to watchdog;  7、授权create session权限给watchdog –create session权限色可以登陆数据库 SQL> grant create session to watchdog;  8、授权查询Scott用户的imp表给watchdog SQL>grant select on scott.emp to watchdog;  9、授权all权限(scott.emp)给watchdog SQL>grant all on scott.emp to watchdog;  10、system希望收回watchdog对scott.emp表的查询权限 –谁授权谁收回 SQL>revoke select on scott.emp from watchdog;  11、希望watchdog用户可以去查询Scott的imp表， 还希望watchdog能把这个权限给别人 SQL>grant select on scott.emp to watchdog with grant option; 如果是系统权限，就加入with admin option,其他同理 ————————————————  3.管理用户口令 1、账户锁定 指定cfmaster这个用户最多只能尝试3次登录，锁定时间为2天 SQL>create profile lock_account limit failed_login_attempts 3 >password_lock_time 2; SQL>alter user cfmaster profile lock_account;  2、账户解锁 SQL>alter user cfmaster account unlock;  3、终止口令 给用户xiaoming创建一个profile文件，要求该用户每隔10天修改自家的密码 SQL>create profile myprofile limit password_life_time 10 password_grace_time 2; SQL>alter user xiaoming profile myprofile;  4、删除profile文件(lock_account) SQL>drop profile lock_account;  4.表的管理 4.1 创建表SQL>create table student( 2 xuehao number(4), 3 xingming varchar2(20), 4 sex char(2), 5 birthday date, 6 sal number(7,2) 7 ) 8 / SQL> create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from scott.emp; ————————————————
　　4.2 修改表 1、给表添加一个字段CLASSID SQL> alter table student add (classid number(2));  2、修改字段xingming的长度为30 –修改字段的类型要求不能有数据 SQL> alter table student modify (xingming varchar2(30));  3、删除字段SAL SQL>alter table student drop column sal;  4、修改表的名字为STU SQL> rename student to stu; 5、删除表(stu) SQL>drop table stu  4.3 添加数据 1、所有字段都插入 SQL> insert into student values(1,‘李芳’,‘女’,‘03-8月-99’);  2、改日期的默认格式 SQL> alter session set nls_date_format=‘yyyy-mm-dd’;  3、部分字段插入（xingming、sex） SQL>insert into student（xingming,sex） values(‘李芳’,‘女’);  4、改一个字段（修改sex为＂女＂的生日为＂2001-05-22＂） SQL>update student set birthday=＂2001-05-22’where sex=‘女’;  5、修改多个字段 –修改sex为女的生日为＂2014-05-21＂，xingming为张三 SQL> update student set birthday=‘2014-05-21’,xingming=＂张三’where sex=‘女’;  6、删除所有数据 SQL> delete from student;  7、删除某行 SQL> delete from stu where fullname=‘王平平’;  8、设置回滚点 SQL> savepoint a; SQL> rollback to a;  5.表的查询 5.1 简单查询命令SQL> clear –清屏 SQL> desc dba_users –查询表结构  SQL> set timing on –打开显示操作的时间  SQL> select count（）from student; –统计行数  SQL> select sal12 ＂总工资＂ from imp; –取别名及使用算数表达式  SQL> select sal*12+nvl(comm,0)12 ＂平均工资＂ from imp; –空值处理用nvl函数  SQL> select distinct deptno,job from imp; –取消重复行  SQL> select ename ＂姓名＂, sal12 as sum from imp; –使用列的别名 SQL> select ename ||‘is a’||job from imp; –如何连接字符串（||）  5.2 where子句 1、如何查找1982.1.1日后入职的员工 SQL> select * from imp where hiredate>‘1-1月-1982’；  2、如何显示工资在2000到2500的员工的情况 SQL> select * from imp where sal>=2000 and sal<=2500;  3、如何显示empno为7369,7499,7876的员工的情况（IN的使用）. SQL>select * from imp where empno in（7369,7499,7876）; 5.3 like操作符1、如何显示首字符为S的员工姓名和工资 SQL>select * from imp where ename like’S%’;  2、如何显示第三个字符为大写O的员工姓名和工资 SQL>select * from imp where ename like’__O%’;
　　5.4 逻辑操作符号 查询工资高于500或者是岗位为MANAGER的雇员， 同时还要满足他们的姓名首字母为大写的J？ SQL> select * from imp where (sal >500 or job = ‘manager’) and ename like ‘J%’;
　　5.5 order by字句1、如何按照工资的从低到高的顺序显示雇员的信息？ SQL> select * from imp order by sal; 2、按照部门号升序而雇员的工资降序排列 SQL> select * from imp order by deptno, sal desc; 6、表的复杂查询1、数据分组 显示所有员工中最高工资和最低工资？. SQL> select max(sal),min(sal) from imp; 最高工资那个人是谁？ SQL> select ename from imp where sal=(select max(sal) from imp); 显示工资高于平均工资的员工信息 SQL> select * from imp where sal > (select avg(sal) from imp); 显示每个部门的平均工资和最高工资？ SQL> select avg(sal), max(sal), deptno from imp group by deptno; –分组查询的话，分组的字段一定要出现在查询的列表里面，否则会报错 显示平均工资低于2000的部门号和它的平均工资？ SQL> select avg(sal), deptno from imp group by deptno having avg(sal) < 2000;  2、多表查询 显示雇员名，雇员工资及所在部门的名字 SQL> select e.ename, e.sal, d.dname from imp e, dept d where e.deptno = d.deptno; 显示各个员工的姓名，工资及工资的级别？ SQL> select e.ename, e.sal, s.grade from imp e, salgrade s where e.sal between s.losal and s.hisal; 显示雇员名，雇员工资及所在部门的名字，并按部门排序？ SQL> select e.ename, e.sal, d.dname from imp e, dept d where e.deptno = d.deptno order by e.deptno;  3、合并查询 1）union该操作符用于取得两个结果集的并集。 当使用该操作符时，会自动去掉结果集中重复行。 SQL> select ename, sal, job from imp where sal >2500 union select ename, sal, job from imp where job = ‘MANAGER’; 2）union all该操作符与union相似， 但是它不会取消重复行，而且不会排序。 SQL> select ename, sal, job from imp where sal >2500 UNION ALL select ename, sal, job from imp where job = ‘MANAGER’; 3）intersect使用该操作符用于取得两个结果集的交集。 SQL> select ename, sal, job from imp where sal >2500 Intersect select ename, sal, job from imp where job = ‘MANAGER’; 4）minus使用改操作符用于取得两个结果集的差集， 他只会显示存在第一个集合中，而不存在第二个集合中的数据。 SQL> select ename, sal, job from imp where sal >2500 Minus select ename, sal, job from imp where job = ‘MANAGER’;
　　7.数据库管理1、	导出表 1.1、导出自己的表 exp userid=Scott/targer@wddorcl tables=(imp) file=f:wdd.dmp; 1.2、导出其他方案的表（DBA权限，如system就可以） exp userid=system/cfmaster@wddorcl tables=(scott.emp) file=f:wdd1.dmp; 1.3、导出表的结构 exp userid=Scott/targer@wddorcl tables=(imp) file=f:wpp.dmp rows=n; 1.4、使用直接导出方式（速度快，数据量大时可以考虑） exp userid=Scott/targer@wddorcl tables=(imp) file=f:wpp.dmp direct=y; 2、	导出方案（方案中的所有对象，表、索引、约束等） 2.1、导出自己的方案 exp userid=Scott/targer@wddorcl owner=Scott file=f:wpp1.dmp; 2.2、导出其他方案（DBA权限，如system就可以） exp userid=system/cfmaster@wddorcl owner=(system,scott) file=f:wpp2.dmp; 3、	导出数据库（导出数据库中的对象及数据，需要DBA权限，如system就可以） exp userid=system/cfmaster@wddorcl full=y inctype=complete file=f:wdd.dmp; 4、	导入表 4.1、导入自己的表 imp userid=Scott/targer@wddorcl tables=(imp) file=f:wdd.dmp; 4.2、导入表到其他用户（DBA权限，如system就可以） imp userid=system/cfmaster@wddorcl tables=(imp) file=f:wdd.dmp touser=Scott; 4.3、导入表的结构(只导入表的结构，而不导入数据) imp userid=Scott/targer@wddorcl tables=(imp) file=f:wpp.dmp rows=n; 4.4、导入数据（如果对象已经存在,可以只导入表的数据） imp userid=Scott/targer@wddorcl tables=(imp) file=f:wdd.dmp ignore=y; 5、	导入方案（方案中的所有对象，表、索引、约束等） 5.1、导入自己的方案 imp userid=Scott/targer file=f:wpp1.dmp; 5.2、导入其他方案（DBA权限，如system就可以） imp userid=system/cfmaster file=f:wpp1.dmp formuser=system touser=Scott; 6、	导入数据库（导入数据库中的对象及数据，需要DBA权限，如system就可以） imp userid=system/cfmaster full=y file=f:wpp2.dmp; 7、	管理表空间和数据文件 7.1、建立数据表空间 SQL>create tablespace data01 datafile ＂f:data01.dbf＂ size 20m uniform size 128k; 7.2、使用数据表空间 SQL>create table mypart(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace data01; 7.3、改变表空间的状态 1）	使表空间脱机 SQL>alter tablespace 表空间名 offine; 2)使表空间连机 SQL>alter tablespace 表空间名 onine; 3)只读表空间 SQL>alter tablespace 表空间名 read only; 4)可读写表空间 SQL>alter tablespace data01 read write; 实例： 1)	知道表空间名，显示该表空间包括的所有表 SQL>select table_name from all_tables where tablespace_name=＂表空间名＂; 2)	知道表名，查看该表属于哪个表空间 SQL>select * from all_tables where table_name=＂表名＂; 7.4、	删除表空间 SQL>drop tablespace 表空间including contents and datafiles;(彻底删除); 7.5、	扩展表空间 1)	增加数据文件 SQL>alter tablespace data01 add datafile＂f:data02.dbf＂size 30m; 2)	增加数据文件的大小 SQL>alter database datafile ＂f:data01.dbf＂ resize 50m;(大小不能超过500m) 3)	设置文件的自动增长 SQL>alter database datafile ＂f:data01.dbf＂autoextend on next 10m maxsize 500m; 7.6、	移动数据文件 1)	确定数据文件所在的表空间 SQL>select tablespace_name from dba_data_files  where file_name=＂F:DATA02.DBF＂; 2)	使表空间脱机（确保数据文件的一致性，把表空间转变为Offline状态） SQL>alter tablespace data01 offline; 3)	使用命令移动数据文件到指定的目标位置(或者剪切) SQL>host move f:data02.dbf e:data02.dbf; 4)	执行alter tablespace命令 SQL>alter tablespace data01 rename datafile ＂f:data02.dbf＂to＂e:data02.dbf＂; 5)	使得表空间联机 SQL>alter tablespace data01 online; 补充： 1）	查询表空间使用情况 SQL>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; 2）	查询表空间的总容量 SQL>select tablespace_name, sum(bytes) / 1024 / 1024 as MB 　　from dba_data_files 　　group by tablespace_name; 3)显示表空间所包含的数据文件 SQL>select file_name,bytes from dba_data_files where tablespace_name=＂表空间名＂;
　　8.数据库管理1、	显示当前用户约束信息 SQL> select constraint_name,constraint_type,status,validated  from user_constraints where table_name=＂EMP＂; 2、	显示约束所对应的表列信息 SQL> select column_name,position from user_cons_columns  where constraint_name=＂约束名＂; 3、	约束包括：not null、unique、primary key foreign key、check五种 SQL> name varchar2(50) not null SQL> email varchar2(50) unique SQL> goodsId char(8) primary key SQL> cusomerId char(8) references customer(customerId) SQL> nums number(10) check (nums between 1 and 30) 4、	增加not null约束时需要使用modify选项，而增加其他四种约束使用add选项 SQL> alter table goods modify goodsName not null; SQL> alter table customer add constraint cardybuque unique(cardId); --constraint cardybuque即约束及其名字（名字随便取） 5、删除约束 SQL> alter table 表名 drop constraint 约束名称; 在删除主键约束的时候可能有错误，比如说 SQL> alter table 表名 drop primary key; --这是因为如果在两张表存在主存关系，那么删除主表的主键约束时，必须带上cascade选项 SQL> alter table 表名 drop primary key cascade；
　　9、oracle的函数1、	字符函数 1.1、问题：将所有员工的名字按小写的方式显示  SQL> select lower(ename) from emp;  1.2、问题：将所有员工的名字按大写的方式显示。  SQL> select upper(ename) from emp;  1.3、问题：显示正好为5个字符的员工的信息。  SQL> select * from emp where length(ename)=5;  1.4、问题：显示所有员工姓名的前三个字符。  SQL> select substr(ename,1,3) from emp;  1.5、问题：以首字母大写,后面小写的方式显示所有员工的姓名。  SQL>select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))   from emp;  1.6、问题：显示所有员工的姓名，用＂我是老虎＂替换所有＂A＂  SQL> select replace(ename,＂A＂, ＂我是老虎＂) from emp;  2、数学函数 2.1、问题：显示在一个月为30天的情况下，所有员工的日薪金，忽略余数。 SQL> select trunc(sal/30), ename from emp;  or  SQL> select floor(sal/30), ename from emp; 2.2、在做oracle测试的时候，可以使用dual表  SQL> select mod(10,2) from dual;结果是0  SQL> select mod(10,3) from dual;结果是1  2.3、其它的数学函数 abs(n)： 返回数字n的绝对值  acos(n)： 返回数字的反余弦值  exp(n)： 返回e的n次幂  power(m,n)： 返回m的n次幂  3、日期函数 默认情况下日期格式是dd-mon-yy 即12-7月-78 3.1、问题：查找已经入职8个月多的员工  SQL> select * from emp where sysdate>=add_months(hiredate,8);  //sysdate: 该函数返回系统时间 //add_months(d,n):时间点d再加上n个月 3.2、问题：对于每个员工，显示其加入公司的天数。  SQL> select floor(sysdate-hiredate) ＂入职天数＂,ename from emp;  or  SQL> select trunc(sysdate-hiredate) ＂入职天数＂,ename from emp;  3.3、问题：找出各月倒数第3天受雇的所有员工。  SQL> select hiredate,ename from emp  where last_day(hiredate)-2=hiredate;  // last_day(d)：返回指定日期所在月份的最后一天 1、	转换函数  5.1、问题：日期是否可以显示 时/分/秒 SQL> select ename, to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’)  from emp; yy：两位数字的年份 2004-->04  yyyy：四位数字的年份  2004年  mm：两位数字的月份 8月-->08  dd：两位数字的天 30号-->30  hh24： 8点-->20  hh12：8点-->08  mi、ss-->显示分钟秒  5.2、问题：显示薪水的时候，把本地货币单位加在前面 SQL>select ename,  to_char(hiredate, ＂yyyy-mm-dd hh24:mi:ss＂),  to_char(sal,＂L99999.99＂)  from emp;  5.3、问题：显示1980年入职的所有员工  SQL> select * from emp where to_char(hiredate, ＂yyyy＂)=1980;  5.4、问题：显示所有12月份入职的员工  SQL> select * from emp where to_char(hiredate, ＂mm＂)=12;  5.5、to_date  //函数to_date用于将字符串转换成date类型的数据  SQL>insert into emp values   (9998, to_date(＂1988-12- 12＂, ＂yyyy-mm-dd＂));   6、系统函数 //sys_context(＂USERENV＂,＂lanuage＂) -- USERENV是固定的，不能改的，lanuage可以换成其它 1）terminal：当前会话客户所对应的终端的标示符（本地客户端的计算机名称） 2）lanuage: 语言  3）db_name： 当前数据库名称  4）nls_date_format： 当前会话客户所对应的日期格式  5）current_schema： 当前会话客户所对应的默认方案名  6）host： 返回数据库所在主机的名称 （本地客户端的工作组/计算机名称） 注：返回oracle服务器的IP地址及服务器名称 SQL>select utl_inaddr.get_host_address,utl_inaddr.get_host_name  from dual;
　　觉得写得好的小伙伴给个点赞+关注啦，谢谢~
　　如果有写得不正确的地方，麻烦指出，感激不尽。
　　同时非常期待小伙伴们能够关注，后面慢慢推出更好的干货~嘻嘻
　　原文 https://blog.csdn.net/weixin_