oracle的SQL语句命令大全--查询

再复杂的SQL语句也不过是简单语句的堆砌而已，因此请反复练习下面的“砖块”并不断尝试组装复杂的SQL语句，多来几遍，SQL大神就是你！！！

--简单查询
SELECT * FROM dept;--基本查询
SELECT * FROM dept d;--表的别名
SELECT ROWID,deptno,dname FROM dept;--查询指定列
SELECT deptno*100 FROM emp;--带有表达式的查询
SELECT e.empno 员工编号,e.ename 员工名字 FROM emp e;--为列指定别名的查询
SELECT DISTINCT job FROM emp e;--查询不重复记录
SELECT emp.ename 员工名字,emp.sal+NVL(emp.comm,0) 员工总工资 FROM emp;--处理NULL值的NVL函数
SELECT e.empno||e.ename 编号和名字 FROM emp e;--字符串拼接
SELECT CONCAT(e.empno,e.ename) FROM emp e;--字符串拼接

--筛选查询
SELECT * FROM emp e WHERE sal >2000;--条件查询
SELECT * FROM emp e WHERE sal <2000;--比较查询【>,>=,<,<=,!,<>】
SELECT * FROM emp e WHERE sal = ANY(800,1600);--特殊比较查询ANY，任意一个满足
SELECT * FROM emp e WHERE sal <> ALL(800,1600);--特殊比较查询ALL，所有满足
SELECT * FROM emp e WHERE ename LIKE 'B%';--关键字筛选LIKE（通配符%和_）
SELECT * FROM emp e WHERE ename LIKE 'B\%' ESCAPE '\';--关键字筛选LIKE（转义通配符）
SELECT * FROM emp e WHERE sal IN (800,1600);--关键字筛选IN
SELECT * FROM emp e WHERE sal NOT IN (800,1600);--关键字筛选NOT IN
SELECT * FROM emp e WHERE sal BETWEEN 800 AND 1600;--关键字筛选Between and
SELECT * FROM emp e WHERE sal NOT BETWEEN 800 AND 1600;--关键字筛选not between and
SELECT * FROM emp e WHERE comm IS NULL;--关键字筛选IS NUlL
SELECT * FROM emp e WHERE comm IS NOT NULL;--关键字筛选IS NOT NULL
SELECT * FROM emp e WHERE sal > 1000 AND sal <2000;--逻辑筛选and
SELECT * FROM emp e WHERE sal < 1000 OR sal > 2000;--逻辑筛选or
SELECT * FROM emp e WHERE NOT sal = 5000;--逻辑筛选not

--分组查询
SELECT deptno FROM emp e GROUP BY deptno;--最简单分组
SELECT deptno,AVG(sal) FROM emp e GROUP BY deptno;--分组函数与统计函数混用
SELECT deptno,job FROM emp e GROUP BY deptno,job;--多列分组
SELECT deptno,AVG(sal) FROM emp e GROUP BY deptno HAVING AVG(sal) > 2000;--having对分组后数据筛选（与where的区别）
SELECT deptno,job,AVG(sal) FROM emp e GROUP BY ROLLUP (deptno,job);--rollup生成横向小计和总计
SELECT deptno,job,AVG(sal) FROM emp e GROUP BY CUBE(deptno,job);--cube生成横向/纵向小计和总计
SELECT deptno,job,AVG(sal),GROUPING(deptno),GROUPING(job) FROM emp e GROUP BY CUBE(deptno,job);--grouping查看统计结果是否使用特定属性
SELECT deptno,job,SUM(sal) FROM emp e GROUP BY ROLLUP((deptno,job));--复合列
SELECT deptno,job,AVG(sal) FROM emp e GROUP BY GROUPING SETS(deptno,job);--grouping sets生成多种分组统计结果

--排序查询
SELECT * FROM emp e ORDER BY e.sal;--最简单排序（默认升序）
SELECT * FROM emp e ORDER BY e.sal ASC;--升序排序
SELECT * FROM emp e ORDER BY e.sal DESC;--降序排序
SELECT * FROM emp e ORDER BY e.sal,e.empno;--多列排序
SELECT * FROM emp e ORDER BY e.sal DESC,e.empno ASC;--多列排序，分别制定排序顺序

--多表关联查询
SELECT * FROM dept d INNER JOIN emp e ON d.deptno = e.deptno;--内连接【inner可以不写】
SELECT * FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno;--左外连接【Outer可以不写】
SELECT * FROM dept d RIGHT OUTER JOIN emp e ON d.deptno = e.deptno;--右外连接【Outer可以不写】
SELECT * FROM dept d FULL OUTER JOIN emp e ON d.deptno = e.deptno;--完全外连接【Outer可以不写】
【【【【【【【【【【外连接操作符：(+)，有兴趣可以了解】】】】】】】】】】
SELECT * FROM dept d NATURAL JOIN emp e;--自然连接
SELECT * FROM dept d CROSS JOIN emp e;--交叉连接

--常用系统函数
--dual介绍：是Oracle数据库中的一个用于临时数据计算的特殊表
--字符类
SELECT ASCII('Z') FROM dual;--获取字符的ASCII码
SELECT CHR(90) FROM dual;--获取ASCII码对应字符
SELECT CONCAT('bbb','aaa') FROM dual;--连接两个字符串
SELECT INITCAP('oh my god!') FROM dual;--首字母大写，其他小写
SELECT INSTR('helloworld','l',1,2) FROM dual;--子串查找
SELECT LENGTH('helloworld') FROM dual; --求字符串长度
SELECT LOWER('Hello World') FROM dual; --全部小写
SELECT UPPER('Hello World') FROM dual; --全部大写
SELECT LTRIM(' hello ') FROM dual; --删除左边字符子串，不指定则表示删除空格
SELECT RTRIM('###hello##','#') FROM dual;--删除右边字符子串
SELECT TRIM('#' FROM '#55##') FROM dual;--删除两边字符
SELECT REPLACE('aassdd','aa','kkk') FROM dual;--使用字符串3替换1中的2，其中3默认为空字符串
SELECT SUBSTR('dbausbdasdiuiasdn',2,5) FROM dual;--截取字符串从2开始截取5个，第三个参数为空则截取2后面所有
--数学类
SELECT ABS(-5) FROM dual;--获取绝对值
SELECT CEIL(5.5) FROM dual;--返回大于等于5.5的最小整数
SELECT COS(3) FROM dual;--返回余弦值，3为弧度
SELECT EXP(3) FROM dual;--返回e的n次幂
SELECT FLOOR(5.5) FROM dual;--返回小于等于5.5的最大整数
SELECT LOG(2,4) FROM dual;--返回以2为底，4的对数
SELECT MOD(4,3) FROM dual;--取余数
SELECT POWER(2,3) FROM dual;--返回2的3次方
SELECT ROUND(5.5) FROM dual;--返回距离5.5最近的整数（相当于+0.5再向下取整）
SELECT ROUND(5.555,2) FROM dual;--返回5.555的小数点后两位处round
SELECT SIGN(-5) FROM dual;--负数返回-1，整数返回1，0返回0
SELECT SIN(2) FROM dual;--返回正弦值
SELECT SQRT(4) from dual;--取平方根
SELECT TRUNC(5.555,2) FROM dual;--截取5.555到小数点后第2位
--日期和时间类
SELECT SYSDATE FROM dual;--返回系统时间
SELECT add_months(SYSDATE,2) FROM dual;--返回系统时间加上两个月的时间
SELECT last_day(SYSDATE) FROM dual;--返回系统日期的月份的最后一天
SELECT months_between(SYSDATE,SYSDATE) FROM dual;--返回两个日期间隔的月份数
SELECT new_time(SYSDATE,时区1,时区2) FROM dual;--返回时区一的时间，在时区二的时间
--转换类
SELECT CHARTOROWID(字符串) FROM dual;--将字符串转换成rowid类型
SELECT CONVERT('s','字符集1','字符集2') FROM dual;--将字符串由字符集1转换成字符集2
SELECT Rowidtochar(ROWID) FROM dual;--将rowid转换成字符串
SELECT to_char(SYSDATE,'YYYY-MM-DD') FROM dual;--将表达式转换成指定格式的字符串
SELECT to_date('2001-05-05','YYYY-MM-DD') FROM dual;--将指定格式的字符串转换成日期
SELECT to_number('18') FROM dual;--将指定字符串转换成数字
--聚合函数
SELECT AVG(sal) FROM emp e;--求取平均数
SELECT COUNT(*) FROM emp e;--计数
SELECT MAX(sal) FROM emp e;--求取最大值
SELECT MIN(sal) FROM emp e;--求取最小值
SELECT SUM(Sal) FROM emp e;--求取总数
SELECT VARIANCE(sal) FROM emp e;--求取统计方差
SELECT STDDEV(sal) FROM emp e;--求取标准方差

--子查询
SELECT * FROM emp e WHERE e.deptno = (SELECT deptno FROM dept WHERE dept.dname = 'RESEARCH');--简单子查询
SELECT * FROM emp e WHERE e.sal = (SELECT MAX(sal) FROM emp)--单行子查询（子查询只返回一个值）【只能使用单行比较运算符】
SELECT * FROM emp e WHERE e.empno IN (SELECT empno FROM dept WHERE dept.dname IS NOT NULL);--多行子查询【只能使用多行比较运算符】
【子查询很强大，select、UPDATE、DELETE、insert等命令中允许使用表达式的地方都可以使用子查询】