Oracle—SQL基础语句

1  1 --去除重复行：distinct
2  2 --作用范围：是后面所有字段的组合
3  3 select distinct deptno sal from emp;
4  4 SELECT * FROM emp ORDER BY sal;
5  5 --(DATE类型)的条件
6  6 SELECT * FROM emp WHERE hiredate ='17-12月-80';
7  7 --使用like模糊查询（%代表0或多个字符，_代表一个字符,特殊字符用ESCAPE标示符查询）
8  8 SELECT * FROM emp WHERE ename LIKE 'S%';
9  9 SELECT * FROM emp WHERE ename LIKE '_M%';
10 10 SELECT * FROM emp WHERE ename LIKE '%\_%' ESCAPE '\';
11 11 --使用is null 查询
12 12 SELECT * FROM emp WHERE comm IS NULL;
13 13 SELECT * FROM emp WHERE comm IS NOT NULL;
14 14 --SQL优化：
15 15 -----AND 把检索结果较少的条件放到后面
16 16 -----OR  把检索结果较多的条件放到后面
17 17 --ORACLE函数分为单行函数和多行函数两大类
18 18 -----单行函数分为字符函数、数值函数、日期函数、转换函数、通过函数
19 19 -----多行函数分为sum()、avg()  count()  max()  min()
20 20 --首字母大写：initcap
21 21 --全转小写： lower
22 22 --全转大写： upper
23 23 --左移除： ltrim 例：ltrim('abcdef','ab');
24 24 --右移除： rtrim 例：rtrim('abcdef','ef');
25 25 --替换：replace  例：replace('jack and jue','j','bl'); 结果：black and blue;
26 26 --查找字符串的位置：instr 例：instr('worldwide','d');结果：5
27 27 --截取字符串：substr 例：substr('abcdefg',3,2);结果：cd
28 28 --连接字符串：contact 例：contact('hello','world');结果：helloworld
29 29 --数值函数
30 30 --取绝对值：ABC()
31 31 --向上取整：CEIL()
32 32 --正弦：SIN()
33 33 --余弦：COS()
34 34 --取符号：SIGN()
35 35 --向下取整：FLOOR()
36 36 --m的n次幂： POWER(m,n);
37 37 --取余数：MOD(m,n);
38 38 --四舍五入：ROUND(m,n);
39 39 --截断：TRUNC(m,n);
40 40 --平方根：SORT()
41 41 --日期函数
42 42 --sysdate:当前日期时间
43 43 --months_between:返回两个日期间的月份
44 44 --add_months:返回把月份加到日期上的新日期
45 45 --next_day:返回指定日期后的星期对应的新日期
46 46 --last_day:返回指定日期所在月的最后一天
47 47 --round:按指定日期格式对日期进行四舍五入
48 48 --trunc:对日期进行指定方式截断
49 49 SELECT * FROM dual;
50 50 --(自动类型转换)
51 51 select '12.5'+30 from dual;
52 52 --（强制类型转换）
53 53 SELECT '12.5' ||30 FROM dual;
54 54 --字符和数字的相互转换 & 字符和日期的相互转换
55 55 VARCHAR2 OR CHAR--number
56 56 VARCHAR2 OR CHAR --date
57 57 NUMBER --varchar2
58 58 DATE--varchar2
59 59 --数据类型之间可以进行隐式转换，仍建议使用显示转换函数，一把持良好的设计风格
60 60 --常用类型转换函数有to_char()  to_date()  to_number()
61 61 
62 62 
63 63 --多行函数对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数
64 64 SELECT AVG(sal) ,MAX(sal),MIN(sal) ,sum(sal) FROM emp;
65 65 --使用NVL()函数强制多行函数处理空值
66 66 select count(nvl(comm,0)),sum(nvl(comm,0)),avg(nvl(comm,0)) from emp;
67 67 COUNT(*) 返回组中总记录数目
68 68 SELECT COUNT(*) FROM emp;
69 69 SELECT * FROM emp;
70 70 COUNT(EXP) 返回表达式exp值非空的记录数目
71 71 count(distinct(exp))返回表达式exp值不重复的、非空的记录数目
72 72 --group by 子句将表中数据分组
73 73 SELECT deptno,job,COUNT(*),AVG(sal) FROM emp GROUP BY deptno,job;
74 74 --注意：
75 75 -----1.出现在SELECT列表中的字段，如果不是包含在多行函数中，那么该字段必须在GROUP BY子句中出现
76 76 -----2.包含在GROUP BY子句中的字段则不必须出现在SELECT列表中
77 77 -----3.SELECT列表中不允许出现字段（单行函数）与多行函数混用的情况
78 78 -----4.不允许唉WHERE子句中使用多行函数
79 79 --HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，WHERE过滤行，HAVING过滤分组，HAVING支持所有WHERE操作符
80 80 --1.在emp表中，列出工资最小值小于2000的职位。
81 81 SELECT job,MIN(sal)
82 82 FROM emp
83 83 GROUP BY job
84 84 HAVING MIN(sal)<2000;
85 85 2.列出平均工资大于1200元的部门和工作搭配组合
86 86 SELECT * FROM emp;
87 87 select deptno, job, avg(sal)   
88 88 from emp 
89 89 group by deptno,job 
90 90 HAVING AVG(sal)>2000
91 91 ORDER BY deptno,job
92 92 3.统计人数小于4的部门的平均工资
93 93 SELECT deptno,AVG(sal)
94 94 FROM emp
95 95 GROUP BY deptno
96 96 HAVING COUNT(*)<4;
97 97 4.统计各部门人数
98 98 SELECT deptno ,COUNT(*)
99 99 FROM emp
100100 GROUP BY deptno;
101101 5.统计各部门的最高工资，排除最高工资小于3000的部门
102102 select deptno,max(sal)
103103 from emp
104104 group by deptno
105105 having not max(sal)<3000;
106106 --查询所有表
107107 SELECT table_name FROM user_tables;
108108 --查看表结构