Oracle之ORDER BY

------------基本查询
--1、查询出的是一张虚拟的结果表
-----基本语法
---- * 所有列（字段）
select * from emps;

-----查询指定字段
select employee_id,first_name||last_name,salary from emps;

----根据条件查询
select * from emps where first_name||last_name='StevenKing';

---查询部门编号100下的所有员工的姓名，编号，系统当前时间
select first_name||last_name,employee_id,sysdate,department_id from emps where department_id=100;

------为结果表的列取名字
select first_name||last_name as 员工姓名,employee_id as 员工编号,sysdate as 系统时间,department_id as 部门编号 from emps where department_id=100;

--------
create table locs as select * from hr.locations;
create table deps as select * from hr.departments;

select * from locs;
select * from deps;

-------查询部门编号在20-40之间的部门地点编号和部门名称

----查询没有经理人的部门的编号和部门名称
select department_name,department_id from deps where manager_id is null;

---查询在Seattle地点的部门名称和编号
select location_id from locs where city='Seattle';
select department_name,department_id,location_id from deps where location_id=1700;

----查询员工加薪500之后的薪资情况
select salary+500 as 加薪后的薪水, salary as 加薪前的薪水 from emps;

-----隐藏的列 ROWID
select rowid,employee_id from emps;

----伪列（隐藏的列） ROWNUM-----从1开始
select rownum,employee_id ,department_id from emps where department_id=50;

--查询部门编号为50的前10个员工
select rownum,emps.* from emps where department_id=50 and rownum<=10 ;

--查询部门编号为50的11-20位员工:无法查询成功！！！
select rownum,emps.* from emps where department_id=50 and rownum between 11 and 20 ;

------------排序order by:升序 ASC--默认的、降序desc

-----order by 注意事项：1、必须放在当前查询语句的最后
------ 2、order by 永远最后执行，在rownum后
------- 3、order by 后可以是一个字段或者表达式
select * from emps where employee_id < 110 order by salary;

-----先执行rownum，再执行排序
select * from emps where employee_id < 110 and rownum<5 order by salary desc;

-----将部门按照工作地点编号进行排序

----查询部门编号为50的，并且名字中含有l的前5个员工，并按照入职时间进行排序
select rownum,emps.* from emps where department_id=50 and first_name||last_name like '%l%' and rownum<=5 order by hire_date;

----------order by 后加多个字段
---方式：先按照第一个字段进行排序，在第一个字段相同的情况下再按照第二个字段排序，以此类推
---按照A 升序，按照B降序
select * from test order by a,b desc;

select * from test order by b desc,a;

alter table student add birth date;

select *from student;
--to_date(日期类型的字符串,日期的格式)
insert into student values('tom','男',30,1,default,to_date('1986-04-05 08:30:00','yyyy-mm-dd hh24:mi:ss'));

-----------------一、转换函数 数值型、字符型、日期型
---1、to_date(日期类型的字符串,日期的格式)----将字符串---->日期类型
---YYYY年 MM月份 DD日 HH -12小时制 HH24-24小时制 MI分钟 SS秒 year

---dual

select to_date('20120511','yyyymmdd') from dual;

----2、to_char():将日期/数值转成字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'day') from dual;

select to_char(birth,'year'),stuname from student ;
--8,994,874.8333
-- 9 占位符 ,千分符 .小数点 .后面的位数会进行四舍五入 L--当地的货币符号 $
select to_char(8994874.8363,'L99,999,999,999.99') from dual;
-- 0占位符 如果位数不足，则使用0补充
select to_char(8994874.8363,'$00,000,000,000.00') from dual;

select employee_id,first_name||last_name as empname,to_char(salary,'$999,999,999.99') as sal from emps;

-----------3、to_number(要转换的字符串):将字符串转成数值,要求字符串必须是能转成数值的，否则报异常
select to_number('123') from dual;

select to_number('123s') from dual;

---------------二、字符函数
--1、UPPER/LOWER将字符串转成大写/小写
select upper('niit'),lower('NIit') from dual;

--查询员工姓名中含T/t员工的信息
select first_name||last_name from emps where upper(first_name||last_name) like '%T%' ;

---2.INITCAP:首字母大写，其余字母小写
select initcap('heLLo niit！') from dual;
---3、concat(第一个字符串,第二个字符串)：连接两个字符串
select concat(first_name,last_name) as empname from emps;
---4、字符串的长度
select length(' djhdfhau adjijdf ') from dual;

------将LOCS表中CITY长度大于10的城市的名称，城市的长度查找出来
select city,length(city) from locs where length(city)>10;

---5、LPAD--左填充
--第一个参数：源字符串，第二个参数：填充之后的长度，第三个参数：填充的字符
--如果源字符串的长度超出指定长度，则从左侧开始留
select lpad('niit',10,'*') from dual;
---6、RPAD--右填充
select rpad('niit',10,'*') from dual;

---7、LTRIM/RTRIM:去除左右空格
select length(ltrim(' nii t hloo ')) from dual;

select rtrim(' nii t hloo ') from dual;
--去除左右两侧的空格
select ltrim(rtrim(' nii t hloo ')) from dual;

-----8、INSTR():获取某字符串在源字符串中第一次出现的索引的位置
---在ORACLE中字符串的索引是从1开始
---第一个参数：源字符串,第二个参数：要查找的字符或者字符串
--查不到返回0
select instr('hello world','a') from dual;

----查询员工姓名中含A/a的员工姓名
select concat(first_name,last_name) as empname from emps where instr(UPPER(concat(first_name,last_name)),'A')>0;

---第三个参数：起始的索引位置(包含在内)
select instr('hello world','l',5) from dual;

-----9、substr()截取字符串

--第一个参数：源字符串 第二个参数：起始的索引位置(包含)
select substr('hello niit',4) from dual;
---第三个参数：截取的长度,如果指定长度过长，一直截取到最后
select substr('hello niit',4,4) from dual;

-------查询学生的邮箱的名称和邮箱地址 tom@niit.com
select * from student;
--查找@
instr(email,'@')--位置
select substr(email,1,instr(email,'@')-1) as 邮箱名,substr(email,instr(email,'@')+1) as 邮箱地址 from student;

--------将电话号码加密：189*****098 045******432
substr(tel,1,3)--前三位
substr(tel,length(tel)-2)--后三位
---右tianchong
select rpad(substr(tel,1,3),length(tel)-3,'*')||substr(tel,length(tel)-2) as tel from stuinfo;

---10.replace()替换字符串
---第一个：源字符串，第二个：要替换的字符串，第三个：最终替换的新的字符串
select replace('hello niit','e','你好') from dual;

--将学生表中的niit.com的邮箱改成163的
update student set email=replace(email,'@niit.com','@163.com');

--表LIST中有字符LISTNUM，其值的格式为A-105,C-

--87,E-194，AA-103 要求：查询表中LISTNUM，优先对字母进

--行顺序排列，然后对数字进行降序排列

select * from list order by substr(listnum,1,instr(listnum,'-')-1),substr(listnum,instr(listnum,'-')+1) desc

---------三、日期函数
--1、sysdate:系统时间
--2、extract(year/month/day from 日期):获取日期里面的year/month/day
select extract(year from sysdate), extract(month from sysdate),extract(day from sysdate)from dual;
-----查询员工入职时间是今天（8月-8日）的员工的姓名和入职时间
----查询今天生日的学生姓名和生日
select first_name||last_name as empname,hire_date from emps where extract(month from hire_date)=
extract(month from sysdate) and extract(day from hire_date)=
extract(day from sysdate);

---3、months_between(日期1,日期2)：两个日期之间的月份差

select months_between(to_date('2012-8-7','yyyy-mm-dd'),sysdate) from dual;
-----查看入职20年以上的所有员工的姓名和入职时间
select first_name||last_name as empname,hire_date from emps where months_between(sysdate,hire_date)>240;

------日期1-日期2=日差
select sysdate-to_date('2015-8-7','yyyy-mm-dd') from dual;

------查看三天后过生日的学生姓名和生日8-11
--- 2015-9-1 - 2015-8-31
select first_name||last_name as empname,hire_date from emps where to_date(extract(year from sysdate)||'-'||
extract(month from hire_date)||'-'||extract(day from hire_date),'yyyy-mm-dd') - sysdate > 3;

---4、add_months(日期,月份的数值):添加月份-----获得是日期
select add_months(sysdate,20) from dual;

-----查看入职20年以上的所有员工的姓名和入职时间
select first_name||last_name as empname,hire_date from emps where add_months(hire_date,240)<sysdate;

---5、next_day(日期,'星期几'),下一个星期数-----获得是日期
select next_day(sysdate,'星期一') from dual;

select next_day(to_date('2015-11-28','yyyy-mm-dd'),'星期一') from dual;

--------下周三过生日的学生姓名和生日

select first_name||last_name as empname,hire_date from emps where extract(month from next_day(sysdate,'星期四'))=extract(month from hire_date)
and extract(day from next_day(sysdate,'星期四'))=extract(day from hire_date)

----6、last_day(日期):当月的最后一天
select last_day(to_date('2012-2','yyyy-mm')) from dual;

-----7、round(日期,'单位');
---单位是year,判断是上半年还是下半年：如果是上半年，返回当前年的1月1号，如果是下半年，返回下一年的1月1号
---单位是month,判断是上半月还是下半月：如果是上半月，返回当年的当月的1号，如果是下半月，返回当年的下一个月的1号
select round(sysdate,'year') from dual;

select round(to_date('2014-3-16','yyyy-mm-dd'),'month') from dual;

------下半年过生日的学生姓名和生日
select first_name||last_name as empname,hire_date from emps where round(hire_date,'year')-hire_date>0;

----8、trunc(日期，单位)获取特定的日期
--year:获取日期中当年的1月1日
--month:获取月份的第一天
--day:获取当月第一个星期日
select trunc(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'day') from dual;

------------------------四、数值函数
--1、abs(数值):绝对值
select abs(-10) from dual;
--2、ceil上取整 floor 下取整 round四舍五入
select ceil(10.45),floor(10.45),round(10.5545,2) from dual;

--3、power();幂次方
select power(2,3) from dual;
--4、sqrt():开平方
select sqrt(625) from dual;

--5、mod(10,3) 取余数
select mod(10,3) from dual;
--6、trunc(数值，位数):数值截断
--位数是正数--保留几位小数
--位数是负数--对整数部分进行截取
select trunc(1093.876,2) from dual;
select trunc(1093.876,-2) from dual;
select trunc(1093.876,-4) from dual;

select * from emps;

 

 

create table stuinfo
(
tel varchar2(20)
)

insert into stuinfo values('12944333222');
insert into stuinfo values('09988776');
insert into stuinfo values('78930992');
insert into stuinfo values('1379998827');
select * from stuinfo;

select * from locs;