逆袭之旅DAY13.东软实训.Oracle.简单的查询语句.限制.排序

2018-07-09  21:34:00

一、简单查询：
1、查询数据表的所有列：
SELECT * FROM 表名;
SELECT 列名,列名.... FROM 表名;

2、起别名：
SELECT 列名 [AS 别名],列名 别名.... 
FROM 表名;

---正常
SELECT ename AS 员工姓名,sal salary
FROM emp;

---别名是关键字，别名需要用双引号引起来

SELECT ename "select",sal "from"
FROM emp;

---别名中间存在空格，别名需要用双引号引起来
SELECT ename "employee name",sal 
FROM emp;

3、distinct:去重
---查询emp表中的不同职务
SELECT DISTINCT job
FROM emp;

---查询emp表中每个部门的不同职务
SELECT DISTINCT deptno,job
FROM emp;

---查询emp表中不同部门的不同职务
SELECT DISTINCT deptno,DISTINCT job
FROM emp;   ---错误

注意：select后面，多列去重，在所有列的前面写一个distinct关键字即可

4、|| 连接输出
---将每个员工的信息输出为如下格式：
姓名为：xxxx，工资为：xxx。
字符串和日期用单引号引起来的。

SELECT '姓名为：'|| ename || '，工资为：' || sal || '。'  员工信息
FROM emp;

5、四则运算：
---查询员工的姓名和年薪
SELECT ename,sal*12 年薪
FROM emp;

SELECT ename,(sal+comm)*12 年薪
FROM emp;

---当与null值参与运算或是比较时，结果一定为null
nvl(agr1,arg2):判断arg1是否为Null,如果不为null，结果为arg1，如果为null,结果为arg2
SELECT ename,(sal+nvl(comm,0))*12 年薪
FROM emp;

1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。
SELECT SAL*1.2 "员工转正后的月薪"
FROM EMP
2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
SELECT SAL*6+SAL*1.2*6 "工作第一年年薪"
FROM EMP
1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示列标题为员工姓名，工资收入，奖金收入，总收入。
SELECT ENAME 员工姓名,SAL*6+SAL*1.2*6 工资收入,nvl(comm,0) 奖金收入,(SAL*6+SAL*1.2*6+nvl(comm,0)) 总收入 
FROM EMP
1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示格式为:XXX的第一年总收入为XXX
SELECT ENAME||'的第一年总收入为'||(SAL*6+SAL*1.2*6+nvl(comm,0)) 
FROM EMP

二、排序：
SELECT 列名 [AS 别名],列名 别名.... 
FROM 表名
ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC...;

排序字段：列名，列的别名，列的顺序号，函数结果

SELECT *
FROM emp
ORDER BY deptno,sal desc;

SELECT ename a,deptno b,sal c
FROM emp
ORDER BY b,c desc;

SELECT ename a,deptno b,sal c
FROM emp
ORDER BY 2,3 desc;

---按照员工的部门编号 升序排序，部门相同按照奖金降序排序

SELECT ename a,deptno b,sal c,comm
FROM emp
ORDER BY 2,nvl(comm,0) desc;

三、限定查询
SELECT 列名 [AS 别名],列名 别名.... 
FROM 表名
WHERE 条件
ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC...;

1、关系运算符：
>,<,<=,>=,=,<>,!=
可以比较数据类型：数值型，字符串，日期
---查询员工的工资大于2500的员工信息
SELECT *
FROM emp
WHERE sal>2500;

---查询员工姓名 为SMITH的员工信息
SELECT *
FROM emp
WHERE ename='SMITH';

---查询在1982年之后入职的员工信息
'日-月-年'
'DD-MM-RR'

SELECT *
FROM emp
WHERE hiredate > '31-12月-1982';

2、逻辑运算符：
and:与
or:或
not:非

not>and>or

---查询员工的职务是MANAGER或是SALESMAN，且工资大于1500的员工的姓名，职务，工资
SELECT ename,job,sal
FROM emp
WHERE (job='MANAGER' OR JOB = 'SALESMAN') and sal>1500;

3、between...and... 闭区间
语法：字段名 [not] between 最小值 and 最大值

--查询在1982年入职的员工信息
SELECT *
FROM emp
WHERE hiredate between '1-1月-1982' and '31-12月-1982';

4、in:集合运算
语法：字段名 [not] in(值1，值2，值3...)
---查询员工的职务是MANAGER或是SALESMAN，且工资大于1500的员工的姓名，职务，工资
SELECT ename,job,sal
FROM emp
WHERE job in('MANAGER','SALESMAN') and sal>1500;

5、is null:没有
is not null:有

---查询没有领导的员工信息
SELECT *
FROM emp
WHERE mgr is null;

---查询奖金不为null的员工信息
SELECT *
FROM emp
WHERE comm is not null;

6、like:模糊查询
语法：字段名 [not] like '匹配串' [ESCAPE '转义字符']
_:任意1个字符
%：0个或 多个任意字段

---查询员工姓名中包含S的员工姓名
SELECT ename
FROM emp
WHERE ename like '%S%';

insert into emp(empno,ename,sal,comm,deptno) values(2222,'li_si',2100,200,30);

commit;
---查询员工姓名中包含%的员工的姓名
SELECT ename
FROM emp
WHERE ename like '%#%%' ESCAPE '#';

SELECT ename
FROM emp
WHERE ename like '%#_%' ESCAPE '#';

SELECT ename
FROM emp
WHERE ename like '%!%%' ESCAPE '!';

SELECT ename
FROM emp
WHERE ename like '%a_%' ESCAPE 'a';

1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
select ename,hiredate,job
from emp
where hiredate>'9-7月-1982' and job<>'SALESMAN'


2.查询员工姓名的第三个字母是a的员工姓名。
select ename
from emp
where ename like '__a%';

3.查询除了10、20号部门以外的员工姓名、部门编号。

select ename,deptno
from emp
where deptno not in(10,20);


4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
select * 
from emp
order by sal desc,ename

5.查询没有上级的员工(经理号为空)的员工姓名。
select ename
from emp
where mgr is null

6.查询工资大于等于4500并且部门为10或者20的员工的姓名工资、部门编号。
select ename,sal,deptno
from emp
where sal>=4500 and deptno  in(10,20)

四、单行函数
字符函数，数值函数，日期函数，转换函数，通用函数
1、字符函数：
(01) upper():转大写
(02) lower():转小写
(03) initcap():转首字符大写，其它字符小写
(04) length():长度

DUAL:sys用户的一张表，只有一个字段，sys创建了该表的公共同义词

SELECT upper(ename),lower(ename),initcap(ename),length(ename)
FROM emp;

(05)trim():去首尾的空格
SELECT length(trim('      abc     '))
FROM dual;

trim(letter from str):去掉str首尾的字母letter
SELECT trim('h' from 'hello')
FROM dual;

SELECT trim('t' from 'test')
FROM dual;

SELECT trim('t' from 'ttttttttesttttttt')
FROM dual;

(06)concat(str1,str2):连接字符串   ||

SELECT concat(ename,job)
FROM emp;

SELECT concat(concat(ename,job),hiredate)
FROM emp;

(07)instr(str1,str2,[n1,n2]):返回str2在str1中的位置,从n1开始查找第n2次出现的位置
没在str1存在的时，返回0

SELECT instr('hello world','l')
FROM dual;

SELECT instr('hello world','l',1,2)
FROM dual;

SELECT instr('hello world','a')
FROM dual;

---查询员工姓名中不含有A的员工姓名
SELECT ename
FROM emp
WHERE instr(ename,'A')=0;

SELECT instr('hello world','world')
FROM dual;

(08)replace(str1,str2,str3):将str1中的str2替换为str3
SELECT replace('abc aaa abc bbb abc ccc','abc','ABC')
FROM dual;

(09)substr(str,loc,length):截取子串,从str的loc开始截取长度为length的子串
--从首位截取,起始位置可以为0或者1
SELECT substr('hello world',1,3)
FROM dual;

SELECT substr('hello world',0,3)
FROM dual;

---截取到字符串的尾部,第三个参数省略
SELECT substr('hello world',5)
FROM dual;

---截取中间子串
SELECT substr('hello world',4,3)
FROM dual;

(10)lpad(str1,length,str2):判断 str1的长度是否等于Length,如果小于length,在str1的左边补充str2，直到长度等于length为止。
(11)rpad(str1,length,str2):判断 str1的长度是否等于Length,如果小于length,在str1的右边补充str2，直到长度等于length为止。

---判断hello的长度是否等于10，如果 不等于，在左边补充#
SELECT lpad('hello',10,'#')
FROM dual;

SELECT lpad('hellohellohello',10,'#')
FROM dual;

SELECT lpad('hello',10,'#$')
FROM dual;

1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，
条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）
SELECT initcap(ename),LENGTH(ename)
FROM emp
WHERE substr(ename,1,1) IN('J','A','M')
ORDER BY 1;


1.查询员工姓名中中包含大写或小写字母A的员工姓名。

SELECT ename
FROM emp
WHERE upper(ename) like '%A%';

---查询emp表中职务是clerk的员工姓名
SELECT ename
FROM emp
WHERE lower(job)='clerk';


2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，
员工姓名长度（提示，要求使用INSTR函数，不能使用like进行判断)



3.查询每个职工的编号,姓名，工资
要求将查询到的数据按照一定的格式合并成一个字符串.
前10位：编号,不足部分用*填充,左对齐
中间10位：姓名，不足部分用*填充,左对齐
后10位：工资，不足部分用*填充,右对齐

SELECT rpad(empno,10,'*') || rpad(ename,10,'*') || lpad(sal,10,'*')
FROM emp;

2、数值函数
（01）round(num,p):四舍五入
p=0:round(num),四舍五入为整数
   SELECT round(455.55)
   FROM dual;
p>0:四舍五入到小数点右边第P位
   SELECT round(455.55,1)
   FROM dual;
p<0:从小数点左边的第|p|位进行四舍五入
    SELECT round(455.55,-1)
   FROM dual;
   
  SELECT round(455.55,-3)
   FROM dual;
   SELECT round(555.55,-3)
   FROM dual;
（02）trunc(num,p):截断
p=0:trunc(num),截断为整数
   SELECT trunc(455.55)
   FROM dual;
p>0:截断到小数点右边第p位
     SELECT trunc(455.55,1)
   FROM dual;
p<0:从小数点左边的第|p|位进行截断
    SELECT trunc(455.55,-1)
   FROM dual;

(03):mod(num1,num2):求余
SELECT mod(3,2)
FROM dual;

SELECT mod(3,0)
FROM dual;

3、日期函数
规则： 日期-日期=天数
      日期+天数=日期
      
当前系统日期：sysdate

(01)months_between(date1,date2):返回两个日期之间相差的月份。如果date1>date2，返回正数，反之，返回是负数
SELECT trunc(months_between(SYSDATE,'1-1月-2000'))
FROM dual;

---统计 emp表中每人入职了多少个月？
SELECT ename,trunc(months_between(SYSDATE,hiredate))
FROM emp;

---统计 每个人到目前为止入职了多少年零多少月零多少天？
SELECT trunc(months_between(SYSDATE,hiredate)/12) 年, trunc(mod((SYSDATE-hiredate),365)/30) 月,
       trunc(mod(mod((SYSDATE-hiredate),365),30)) 天
FROM emp;

（02）next_day(DATE,weekday):返回指定日期的下个星期几是什么日期
SELECT next_day(SYSDATE,'星期一')
FROM dual;

(03) last_day(DATE):返回指定日期的所在月份的最后一天

SELECT last_day(SYSDATE)
FROM dual;

(04)add_months(DATE,months):在指定日期上增加若干个月后的日期
SELECT add_months(sysdate,4)
FROM dual;

(05)EXTRACT([MONTH DAY YEAR] FROM DATE):从指定日期中取出日期中的年，月，日
SELECT EXTRACT(MONTH FROM SYSDATE)
FROM dual;

SELECT EXTRACT(year FROM SYSDATE)
FROM dual;

---查询在1982年入职的员工信息
SELECT *
FROM emp
WHERE extract(year from hiredate)=1982;

---在一年月前半年入职的员工信息
SELECT *
FROM emp
WHERE EXTRACT(MONTH FROM hiredate)<=6;

(06)round(DATE,fmt):
(07)trunc(DATE,fmt):
fmt:
   CC：世纪
   YY：年
   MM:月
   DD:日
   HH:时
SELECT round(SYSDATE,'MM')
FROM dual;

SELECT round(to_date('17-3月-2018'),'MM')
FROM dual;

4、转换函数
（01）to_char():转为字符串
将日期转为字符串：
   to_char(DATE,'format'):将日期转为固定格式的字符串
常用的格式：
  yyyy:年
  mm:月
  dd:日
  day：星期
  hh:时  hh24:24小时制
  mi:分
  ss:秒
  mon:月
  
SELECT to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss day')
FROM dual;

SELECT to_char(SYSDATE,'yyyy')
FROM dual;

SELECT ename,hiredate
FROM emp
WHERE to_char(hiredate,'yyyy')='1982';

将数值 转为字符串：
to_char(NUMBER,'format')：将指定的数据转为固定格式的字符串
SELECT to_char(455.55)
FROM dual;
常见的格式：
   .:小数点
   9：任意数字
   ,:千位符
   L:本地货币符号
   $：美元

SELECT to_char(4555.55,'L999,999.99')
FROM dual;

(02)to_number(str,'format'):转为数值,将固定格式的字符串转为数值

SELECT TO_NUMBER('￥4,555.55','L9,999.99')
FROM dual;

(03)to_date(str,'format'):转为日期，将固定格式的字符串转为日期
SELECT to_date('21-12-2017','dd-mm-yyyy')
FROM dual;

5、通用函数：
（01）nvl():
(02) nvl2(arg1,arg2,arg3):判断arg1是否为null，如果不为Null,返回arg2，如果为Null,返回arg3
SELECT ename,comm,nvl2(comm,comm,0)
FROM emp;

---查询员工的姓名，奖金，如果奖金为null,输出为“No comm.”
SELECT ename,comm,nvl2(comm,to_char(comm),'No comm.')
FROM emp;

(03)decode():类似于等值判定，swith
语法：decode(exp/comm,值1,结果1,值2,结果2....,值n,结果n,结果m);
---判定员工姓名中含A或a的位置，如果位置在首位，显示“A在首位”,如果位置在末尾，显示“A在末尾”，如果A在中间，显示"A在中间"，否则，“不包含A”
SELECT ename,decode(instr(upper(ename),'A'),1,'A在首位',0,'不包含A',LENGTH(ename),'A在末尾','A在中间')
FROM emp;

(04):CASE():
格式一：
CASE WHEN 条件1 THEN 结果1
     WHEN 条件2 THEN 结果2
     ....
     WHEN 条件N THEN 结果n
     ELSE 结果m
     END;

SELECT ename,CASE WHEN upper(ename) LIKE 'A%' THEN 'A在首位'
                  WHEN upper(ename) LIKE '%A' THEN 'A在末尾'
                  WHEN upper(ename) NOT LIKE '%A%' THEN '不包含A'
                  ELSE 'A在中间'
                  END
FROM emp;

格式二：
CASE 表达式/列名 WHEN 值1 THEN 结果1
               WHEN 值2 THEN 结果2
               。。。
               WHEN 值n THEN 结果n
               ELSE 结果m
               END
------------------------------练习题--------------------------------------------
---课后练习
---第三章课后作业
1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。

select ename,hiredate,job 
from emp
where hiredate>'9-7月-1982' and job not in('SALESMAN');

2.查询员工姓名的第三个字母是a的员工姓名。

select ename
from emp
where ename like '__a%'


3.查询除了10、20号部门以外的员工姓名、部门编号。

select ename,deptno
from emp
where deptno not in(10,20);

4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。 

select * 
from emp
where deptno=30 
order by sal desc ,ename ;

5.查询没有上级的员工(经理号为空)的员工姓名。

select ename
from emp
where mgr is null;

6.查询工资大于等于4500并且部门为10或者20的员工的姓名工资、部门编号。

select ename,sal,deptno
from emp
where sal>=4500 and deptno in(10,20);


---第四章课后作业
---？？？-----------------------------------------------------
1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。

select round(months_between(sysdate,'1-1月-2000')) 月,round(months_between(sysdate,'1-1月-2000'))*4 周
from emp;

2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。

select ename
from emp
where ename like '__A%';

select ename
from emp
where instr(ename,'A')=3;



3.使用trim函数将字符串‘hello’、‘  Hello ’、‘bllb’、‘ hello    ’
分别处理得到下列字符串ello、Hello、ll、hello。
---'hello'---ello
select trim('h' from 'hello')
from dual

---'  Hello '---Hello
select trim('  Hello ')
from dual;

---'bllb'---ll
select trim('b' from 'bllb')
from dual;

---' hello    '---hello
select trim(' hello    ')
from dual;

4.将员工工资按如下格式显示：123,234.00 RMB 。

select to_char(sal,'999,999,99') ||' RMB'
from emp;

5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
---nvl2()返回的数值类型必须一致
select ename,nvl2(mgr,to_char(mgr),'No Manager')
from emp;

6.将员工的参加工作日期按如下格式显示：月份/年份。 

select to_char(hiredate,'mon/yyyy') 
from emp;

7.在员工表中查询出员工的工资，并计算应交税款：
如果工资小于1000,税率为0，
如果工资大于等于1000并小于2000，税率为10％，
如果工资大于等于2000并小于3000，税率为15％，
如果工资大于等于3000，税率为20％。

select sal 员工工资,
case when sal<1000 then 0 
     when sal >=1000 and sal<2000  then sal*0.1 
     when sal >=2000 and sal<3000  then sal*0.15
     when sal >=3000 and sal<2000  then sal*0.2  
     end
from emp;
---end后不加";"号
---应交税款 sal*0.1  工资*税率


8.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。

select ename ,lpad(sal,15,'$') SALARY
from emp;

实验２  使用WHERE和ORDER BY子句
实验要求
（１）掌握WHERE子句的用法。
（２）掌握ORDER BY子句的用法。
实验步骤
（１）创建一个查询显示工资大于2850美元的雇员的姓名及工资

select ename,sal
from emp
where sal>2850;

（２）显示工资不在1500到2850美元之间的雇员的姓名及工资

select ename,sal
from emp
where sal not between 1500 and 2850 ;


（３）显示在10和30部门工作并且工资大于1500美元的雇员的姓名和工资，
      列标题显示为Employee和Monthly Salary 
      
select ename Employee,sal "Monthly Salary"
from emp
where deptno in(10,30) and sal >1500;

（４）显示奖金比工资多10％以上的雇员的姓名、工资及奖金。

select ename,sal,comm
from emp
where sal*(1.1)<comm;

思考练习题
１．创建一个查询显示雇员编号为7566的雇员的姓名和部门编号。

select ename,deptno
from emp
where empno=7566

２．显示受雇时间在February 20，1981和May 1，1981之间的雇员的姓名、工资、
及受雇时间，并以受雇时间升序排列。

select ename,sal,trunc(mod((SYSDATE-hiredate),365)/30)  受雇月数
from emp 
where hiredate between '20-2月-1981' and '1-5月-1981' 
order by  3;


３．显示在10号和30号部门工作的雇员的姓名及其部门编号，并以字母顺序排列。

select ename,deptno
from emp
where deptno in(10,30)
order by ename;

４．显示所有受雇于1982年的雇员的姓名和受雇时间。

select ename ,hiredate
from emp
where extract(year from hiredate)=1982;


５．显示没有上级管理员的雇员的姓名及其工作。

select ename,job
from emp
where mgr is null;

６．显示能挣得奖金的雇员的姓名、工资、奖金，并以工资和奖金降序排列。

select ename,sal,comm
from emp
where comm is not null and comm<>0
order by sal desc,comm desc;

７．显示姓名中第三个字母为A的雇员的姓名。
select ename
from emp
where ename like '__A%';

８．显示姓名中两次出现字母L并且在30部门工作或者其管理员编号是7782的雇员的姓名。

select ename
from emp
where instr(ename,'L')=2 or mgr=7782;

９．显示工作为Clerk或Analyst并且工资不等于$1000、$3000、$5000的雇员的姓名、工作及工资。

select ename,job,sal
from emp
where job in (upper('Clerk'),upper('Analyst')) and sal not in(1000,3000,5000);

实验３  在SELECT语句中使用单行函数
实验要求
掌握常用的单行函数的用法。
实验步骤
（１）显示雇员的编号、姓名、工资以及工资增长15％后的整数值（以New Salary作为列标题）

select empno,ename,sal,trunc(sal*(1+0.15)) "New Salary"
from emp;

（２）在(1)的基础上，增加一个列Increase显示new salary和salary的差值

select empno,ename,sal,trunc(sal*(1+0.15)) "New Salary",trunc(sal*(1+0.15))-sal 上涨工资
from emp;

---???
（３）显示雇员姓名并以*为指示符代表工资数额（列标题为EMPLOYEE_AND_THEIR_SALARIES），
一个指示符代表一百美元，并以工资数额降序排列。

select ename,lpad(' ',(trunc(sal,-2)/100)+1,'*') EMPLOYEE_AND_THEIR_SALARIES
from emp
order by sal desc;

    
思考练习题
１．编写一个显示当期日期的查询，列标题为Date。

select sysdate "Date"
from emp;

２．显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后6个月后的第一个星期一），
复审日期以列标题REVIEW显示。

select ename,hiredate,next_day(add_months(hiredate,6),'星期一')
from emp;

３．显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，
以列标题MONTHS_WORKED显示月数（四舍五入），输出以月数升序排列。

select ename,round(mod((SYSDATE-hiredate),365)/30) MONTHS_WORKED
from emp
order by MONTHS_WORKED;

４．编写一个以下面的形式输出的查询：
<employee name> earns <salary> monthly but wants <3 times salary>.
列标题为Dream Salaries。

select ename||'earns'||sal||'monthly but wants'||sal*3  "Dream Salaries"
from emp;
---重写列名有空格，" " 双引号

５．创建一个查询显示每个雇员的姓名及工资（列标题为SALARY），
工资的显示模式为：占15个字符的宽度，对于不够位数的以$填充。

select ename,rpad(sal,15,'$') SALARY
from emp;

６．编写一个查询显示名字以J、A、M开头的雇员的姓名及姓名所占的字符数，
姓名的显示格式为第一个字母大写其他字母小写，为每个列设置合适的列标题。

SELECT initcap(ename) 员工姓名,LENGTH(ename) 姓名长度
FROM emp
WHERE substr(ename,1,1) IN('J','A','M');

７．显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为DAY），
并以DAY升序排列

select ename,hiredate,to_char(hiredate,'day') DAY
from emp
order by 3;

８．创建一个查询显示雇员姓名及其奖金（列标题为COMM），
如果该雇员不能获得奖金则以“No Commission.”显示。

select ename,nvl2(comm,to_char(comm),'No Commission.') COMM
from emp;