dual 伪表 用来测试函数和表达式
1.查询EMP表中所有人的信息，结果格式样例为“某人的月薪是1000$”
SELECT ENAME||'的月薪是'||SAL||'$' FROM EMP;
2.查询EMP表的部门号，去掉重复数据
SELECT DISTINCT DEPTNO FROM EMP;
3.查询EMP表员工的姓名和年薪，年薪列用“ANNUAL”显示
SELECT ENAME AS "姓名",SAL*12 AS "ANNUAL" FROM EMP;
4.描述employees表结构
DESC EMPLOYEES;
5.查询EMPLOYEES表中的内容，预测每个员工姓名和月加薪10%之后的月薪，列名为“预测月薪”
SELECT FIRST_NAME AS "姓名",SALARY*1.1 AS "预测月薪" FROM EMPLOYEES;
6.下面语句有四个编码错误，找出它们
SELECT EMPLOYEES_ID,LAST_NAME,SAL*12 ANNUAL SALARY FROM EMPLOYEES;
LAST_NAME 后面没有逗号 sal*12后面没有AS 应该是SALARY ANNUAL SALARY缺少双引号
7.用两种方式写出 FIRST_NAME.LAST_NAME 月薪在2000-3000之间的
SELECT FIRST_NAME||'.'||LAST_NAME FROM EMPLOYEES WHERE SALARY BETWEEN 2000 AND 3000;
SELECT FIRST_NAME||'.'||LAST_NAME FROM EMPLOYEES WHERE SALARY >=2000 AND SALARY<=3000;
8.显示工资为2000，3000，4000，5000的FIRST_NAME.LAST_NAME
SELECT FIRST_NAME||'.'||LAST_NAME FROM EMPLOYEES WHERE SALARY IN(2000,3000,4000,5000);
9.FIRST_NAME是以S开头的所有的数据
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%';
10.FIRST_NAME是以s结尾的所有的数据
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%s';
11.FIRST_NAME第三个字母是a的所有的数据
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '__a%';
12.FIRST_NAME倒数第三个字母是a的所有的数据
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%a__';
13.显示第三个字母是下划线的
SELECT * FROM T1 WHERE X LIKE '__\_%' ESCAPE '\';
14.显示第五个字母是百分号的
SELECT * FROM T1 WHERE X LIKE '____\%%' ESCAPE '\';
15.查询COMMISSION_PCT为非空的所有内容
SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;
16.查询薪水大于一万和COMMISSION_PCT>0.3的所有内容
SELECT * FROM EMPLOYEES WHERE SALARY>10000 AND COMMISSION_PCT>0.3;
17.查询薪水大于一万或者COMMISSION_PCT非空的所有内容
SELECT * FROM EMPLOYEES WHERE SALARY>10000 OR COMMISSION_PCT IS NOT NULL;
18.查询部门ID不在80和90的所有内容
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID NOT IN (80,90);
19.按工资升序排列
SELECT * FROM EMPLOYEES ORDER BY SALARY ASC;
20.按工资降序排列
SELECT * FROM EMPLOYEES ORDER BY SALARY DESC;
21.SELECT EMPLOYEE_ID FROM EMPLOYEES ORDER BY 8 ASC 为什么会报错
因为只查EMPLOYEE_ID 没有第八列
22.ORDER BY 后面跟数字
SELECT EMPLOYEE_ID FROM EMPLOYEES ORDER BY 1 DESC
23.多列排序
SELECT * FROM EMPLOYEES ORDER BY MANAGER_ID DESC,SALARY DESC;
24.ORDER BY跟数字 根据结果集的第几列排序
25.ORDER BY跟列名 根据列表里的该列排序
26.upper函数 lower函数
SELECT UPPER(FIRST_NAME),LOWER(FIRST_NAME),FIRST_NAME FROM EMPLOYEES；
27.initcap函数
SELECT INITCAP('COURSE Course cOURSE course) from dual;
大小写转换函数不能有逗号
28.CONCAT函数 字符连接函数 只能有两个参数
SELECT CONCAT('World','haha')from dual;
SUBSTR函数 第一个数字是字符串的第几位开始输出
SELECT SUBSTR('HelloWorld',2,5)from dual;
第二个数字是字符串需要输出几个
29.LENGTH函数 统计字符串长度
SELECT LENGTH('HELLO ,,WORLD)FROM DUAL; 返回值15
30.instr函数 第一列 给出的字符串
第二个 要寻找的字符
第三列 从字符串第几位开始寻找
第四列 第几次出现在字符串的绝对位置
SELECT INSTR('HELLOWORLD','W',2,1)FROM DUAL;返回6
SELECT INSTR('HELLOWORLD','L',1,1)FROM DUAL;返回3
SELECT INSTR('HELLOWORLD','L',2,2)FROM DUAL;返回4
SELECT INSTR('HELLOWORLD','L',4,1)FROM DUAL;返回4
31.LPAD函数 LPAD(SALARY,10,'*')在左边补补*补到10个字符
RPAD函数 LPAD(SALARY,10,'*')在右边补补*补到10个字符
SELECT LPAD(SALARY,10,'*') FROM EMPLOYEES;
SELECT RPAD(SALARY,10,'$') FROM EMPLOYEES;
32.REPLACE函数 'JACK and JUE','J','BL'把所有的J换成BL
SELECT REPLACE('JACK and JUE','J','BL')FROM DUAL;
33.TRIM函数 TRIM（'l'from 'HelloWorld'）首尾都删l 删到不是l为止就停了
SELECT TRIM（'H'from 'HelloWorld'）FROM DUAL
SELECT TRIM（'H'from 'HHelloWorldH'）FROM DUAL
SELECT TRIM（'H'from 'HHHelloWorldHH'）FROM DUAL
34.显示EMPLOYEES表中收入超过12000的雇员的名字和薪水
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY>12000;
35.显示EMPLOYEES表中雇员号为176的雇员的名字和部门号
SELECT FIRST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=176;
36.显示EMPLOYEES表中薪水不在5000到12000之间的雇员名字和薪水
SELECT FIRST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE SALARY<5000 OR SALARY>12000;
37.显示EMPLOYEES表中受雇日期在2005年2月20日到2005年5月1日之间的雇员的名字、岗位
SELECT FIRST_NAME,JOB_ID FROM EMPLOYEES WHERE HIRE_DATE BETWEEN TO_DATE('2005-02-20','yyyy-mm-dd') AND TO_DATE('2005-05-01','yyyy-mm-dd');
38.显示EMPLOYEES表中所有在部门20和50中的雇员的名字和部门号，并以名字按字母顺序排序
SELECT FIRST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN (20,50) ORDER BY FIRST_NAME;
39.显示EMPLOYEES表中收入在5000和12000之间，并且在部门20或50的员工名字和薪水，将列标题分别显示为EMPLOYEE和MONTHLY SALARY
SELECT FIRST_NAME AS "EMPLOYEE" , SALARY AS "MONTHLY SALARY" FROM EMPLOYEES WHERE (SALARY BETWEEN 5000 AND 12000)AND (DEPARTMENT_ID IN (20,50));
40.ROUND函数：四舍五入到指定的十进制
TRUNC函数：截断到指定的十进制值
MOD：返回余数
SELECT ROUND(123.45678,3) FROM DUAL;四舍五入，截止到小数点后3位
SELECT TRUNC(123.45678,-1) FROM DUAL;不四舍五入，截止到小数点前一位
41.SYSDATE返回当前日期 SELECT SYSDATE-1 AS "昨天",SYSDATE AS "今天",SYSDATE+1 AS "明天" FROM DUAL;
42.SELECT ENAME,SYSDATE-HIRE_DATE||'天' AS "入职天数"，（SYSDATE-HIRE_DATE）/365||'年' AS "入职年数" FROM EMP; SYSDATE 减去 HIRE_DATE等于入职天数
43.MONTHS_BETWEEN 两个日期相差的月数
ADD_MONTH 向指定日期加上若干月数
NEXT_DAY 指定日期的下一个日期
LAST_DAY 本月的最后一天
44.SELECT MONTHS_BETWEEN(TO_DATE('2016-12-20','yyyy-mm-dd'),TO_DATE('2018-03-28','yyyy-mm-dd')) FROM DUAL;
用TO_DATE函数进行字符串与日期格式的转换来求出月份多少
- SELECT
SYSDATE,NEXT_DAY(SYSDATE,1),NEXT_DAY(SYSDATE,2),NEXT_DAY(SYSDATE,3),NEXT_DAY(SYSDATE,4),NEXT_DAY(SYSDATE,5),NEXT_DAY(SYSDATE,6),NEXT_DAY(SYSDATE,7),
NEXT_DAY函数 后面的数字是显示下一个周几 按日一二三四五六排1234567
46.显示EMPLOYEES表中每一个在2005年受雇的雇员的名字和受雇日期
SELECT FIRST_NAME,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE BETWEEN TO_DATE('2005-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2005-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss');
显示EMPLOYEES表中所有没有主管经理的雇员名字和工作岗位
SELECT FIRST_NAME,JOB_ID FROM EMPLOYEES WHERE MANAGER_ID IS NULL;
显示EMPLOYEES表中所有有佣金的雇员的名字、薪水和佣金，以薪水和佣金的降序排列数据
SELECT FIRST_NAME,SALARY,COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL ORDER BY SALARY DESC,COMMISSION_PCT DESC;
显示EMPLOYEES表中所有名字第三个字母是a的雇员的名字
SELECT FIRST_NAME WHERE FIRST_NAME LIKE '__a%';
显示EMPLOYEES表中所有名字中有一个a和一个e的雇员的名字
SELECT FIRST_NAME WHERE FIRST_NAME LIKE '%a%' OR FIRST_NAME LIKE '%e%';
显示EMPLOYEES表中所有工作是SA_REP或者ST_CLERK，并且薪水不等于2500、3500或7000的雇员的名字、工作和薪水
SELECT FIRST_NAME,JOB_ID,SALARY FROM EMPLOYEES WHERE (JOB_ID = 'ST_CLERK' OR JOB_ID = 'SA_REP') AND SALARY NOT IN (2500,3500,7000);
47.TO_CHAR,TO_NUMBER,TO_DATE 使NUMBER CHAR DATE三种格式相互转换
TO_CHAR 函数 常用的几种格式
9代表一个数字 0强制显示0 $设置一个浮动的美元符号 . 代表小数点 ，在千分位加逗号
48.NVL函数 NVL（com,0）若com不为空，则输出com；若com为空，则输出0
49.NVL2函数 NVL(e1,e2,e3) 若e1不为空，输出e2；若e1为空，则输出e3
SELECT NVL2(COMMISSION,(SALARY+SALARY*COMMISSION)*12,SALARY*12) FROM EMPLOYEES ;
50.NULLIF函数 NULLIF(X1,X2)当X1=X2返回空值；当X1!=X2返回X1
51.CASE表达式
SELECT LAST_NAME ,JOB_ID,SALARY，
CASE JOB_ID WHEN ‘IT_PROG’ THEN 1.10*SALARY
WHEN ‘ST_CLERK’ THEN 1.15*SALARY
WHEN ‘SA_REP’ THEN 1.20*SALARY
ELSE SALARY
END
“REVISED_SALARY”
FROM EMPLOYEES;
52.DECODE函数
SELECT LAST_NAME ,JOB_ID,SALARY，
DECODE(JOB_ID ‘IT_PROG’ ，1.10*SALARY，
‘ST_CLERK’ ，1.15*SALARY，
‘SA_REP’ ，1.20*SALARY，
SALARY)
“REVISED_SALARY”
FROM EMPLOYEES;
53显示雇员的LAST_NAME和COMMISSION比率，如果雇员没有佣金，则显示为“NO COMMISSION”,列标签为COMM
SELECT LAST_NAME,NVL(TO_CHAR(COMMISSION_PCT),'NO COMMISSION') AS "COMM" FROM EMPLOYEES;
54.用DECODE函数，按照下面的数据显示所有雇员基于JOB_ID的类别
SELECT LAST_NAME,JOB_ID,DECODE(JOB_ID,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP','D','ST_CLERK','E','0') AS "GRADE" FROM EMPLOYEES;
55.用CASE表达式，重写上述问题的答案
SELECT LAST_NAME,JOB_ID ,CASE JOB_ID WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK' THEN 'E' ELSE '0' END AS "GRADE" FROM EMPLOYEES;
56.分组函数作用于一组数据，并对一组数据返回一个值
AVG 平均值 COUNT 统计值 MAX最大值 MIN最小值 SUM总和
可以对数值型数据使用AVG和SUM函数
可以对数值型、字符型和日期型使用MIN和MAX函数
SELECT COUNT(*) 返回表中总行数
SELECT COUNT(*) FROM EMPLOYEES;返回107行
SELECT COUNT(expr)返回非空值的expr的行数
SELECT COUNT(LAST_NAME) FROM EMPLOYEES;返回107 行
SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES;返回35行
SELECT COUNT(DISTINCT espr)返回去掉重复行的行数
SELECT COUNT(DISTINCT LAST_NAME) FROM EMPLOYEES;返回102行
组函数忽略空值
SELECT AVG(COMMISSION_PCT) FROM EMPLOYEES;返回0.2，这个把空值忽略掉了，结果结果变大了
NVL函数使组函数无法忽略空值
SELECT AVG(NYL(COMMISSION_PCT,0)) FROM EMPLOYEES;返回0.07，这个把空值算0，结果是对的
57.GROUP BY 聚合函数 不能使用列别名。SELECT后面有限制
SELECT COMMISSION_PCT,COUNT(COMMISSION_PCT),ROUND(AVG(SALARY),0) FROM EMPLOYEES GROUP BY COMMISSION_PCT;
58.多列分组，把两列进行分组，在GROUP BY字句中包含多个列
比如department_id 和 job_id都相同的 进行分组
SELECT DEPARTMENT_ID,JOB_ID,SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID>40
GROUP BY DEPARTMENT_ID,JOB_ID
ORDER BY DEPARTMENT_ID;
59.查询哪个部门的最高薪水>10000
SELECT * FROM(SELECT DEPARTMENT_ID,MAX(SALARY) AS "MAXSALARY" FROM EMPLOYEES GROUP BY DEPARTMENT_ID ) WHERE MAXSALARY>10000;
使用HAVING字句过滤分组：1.行已经被分组2.使用了组函数3.满足having字句中条件的分组将被显示
SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING MAX(SALARY)>10000;
HAVING字句 GROUP BY字句不能使用别名