Oracle—SQL基础语句

--去除重复行：distinct
--作用范围：是后面所有字段的组合
select distinct deptno sal from emp;
SELECT * FROM emp ORDER BY sal;
--(DATE类型)的条件
SELECT * FROM emp WHERE hiredate ='17-12月-80';
--使用like模糊查询（%代表0或多个字符，_代表一个字符,特殊字符用ESCAPE标示符查询）
SELECT * FROM emp WHERE ename LIKE 'S%';
SELECT * FROM emp WHERE ename LIKE '_M%';
SELECT * FROM emp WHERE ename LIKE '%\_%' ESCAPE '';
--使用is null 查询
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;
--SQL优化：
-----AND 把检索结果较少的条件放到后面
-----OR  把检索结果较多的条件放到后面
--ORACLE函数分为单行函数和多行函数两大类
-----单行函数分为字符函数、数值函数、日期函数、转换函数、通过函数
-----多行函数分为sum()、avg()  count()  max()  min()
--首字母大写：initcap
--全转小写： lower
--全转大写： upper
--左移除： ltrim 例：ltrim('abcdef','ab');
--右移除： rtrim 例：rtrim('abcdef','ef');
--替换：replace  例：replace('jack and jue','j','bl'); 结果：black and blue;
--查找字符串的位置：instr 例：instr('worldwide','d');结果：5
--截取字符串：substr 例：substr('abcdefg',3,2);结果：cd
--连接字符串：contact 例：contact('hello','world');结果：helloworld
--数值函数
--取绝对值：ABC()
--向上取整：CEIL()
--正弦：SIN()
--余弦：COS()
--取符号：SIGN()
--向下取整：FLOOR()
--m的n次幂： POWER(m,n);
--取余数：MOD(m,n);
--四舍五入：ROUND(m,n);
--截断：TRUNC(m,n);
--平方根：SORT()
--日期函数
--sysdate:当前日期时间
--months_between:返回两个日期间的月份
--add_months:返回把月份加到日期上的新日期
--next_day:返回指定日期后的星期对应的新日期
--last_day:返回指定日期所在月的最后一天
--round:按指定日期格式对日期进行四舍五入
--trunc:对日期进行指定方式截断
SELECT * FROM dual;
--(自动类型转换)
select '12.5'+30 from dual;
--（强制类型转换）
SELECT '12.5' ||30 FROM dual;
--字符和数字的相互转换 & 字符和日期的相互转换
VARCHAR2 OR CHAR--number
VARCHAR2 OR CHAR --date
NUMBER --varchar2
DATE--varchar2
--数据类型之间可以进行隐式转换，仍建议使用显示转换函数，一把持良好的设计风格
--常用类型转换函数有to_char()  to_date()  to_number()


--多行函数对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数
SELECT AVG(sal) ,MAX(sal),MIN(sal) ,sum(sal) FROM emp;
--使用NVL()函数强制多行函数处理空值
select count(nvl(comm,0)),sum(nvl(comm,0)),avg(nvl(comm,0)) from emp;
COUNT(*) 返回组中总记录数目
SELECT COUNT(*) FROM emp;
SELECT * FROM emp;
COUNT(EXP) 返回表达式exp值非空的记录数目
count(distinct(exp))返回表达式exp值不重复的、非空的记录数目
--group by 子句将表中数据分组
SELECT deptno,job,COUNT(*),AVG(sal) FROM emp GROUP BY deptno,job;
--注意：
-----1.出现在SELECT列表中的字段，如果不是包含在多行函数中，那么该字段必须在GROUP BY子句中出现
-----2.包含在GROUP BY子句中的字段则不必须出现在SELECT列表中
-----3.SELECT列表中不允许出现字段（单行函数）与多行函数混用的情况
-----4.不允许唉WHERE子句中使用多行函数
--HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，WHERE过滤行，HAVING过滤分组，HAVING支持所有WHERE操作符
--1.在emp表中，列出工资最小值小于2000的职位。
SELECT job,MIN(sal)
FROM emp
GROUP BY job
HAVING MIN(sal)<2000;
2.列出平均工资大于1200元的部门和工作搭配组合
SELECT * FROM emp;
select deptno, job, avg(sal)   
from emp 
group by deptno,job 
HAVING AVG(sal)>2000
ORDER BY deptno,job
3.统计人数小于4的部门的平均工资
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING COUNT(*)<4;
4.统计各部门人数
SELECT deptno ,COUNT(*)
FROM emp
GROUP BY deptno;
5.统计各部门的最高工资，排除最高工资小于3000的部门
select deptno,max(sal)
from emp
group by deptno
having not max(sal)<3000;
--查询所有表
SELECT table_name FROM user_tables;
--查看表结构