Oracle数据系列(三)、高级查询1

第三章、高级查询1

学习目标

3.1、查询

3.1.1、什么是查询

3.1.2、怎么查的

3.1.3、条件查询实例

查询全部行和列
```
select * from students;
```

查询部分行

#查询地址是山东菏泽的学生信息
select * from students where saddress = "山东菏泽";
#查询地址不是山东菏泽的学生信息
select * from students where saddress <> "山东菏泽";
select * from students where saddress != "山东菏泽";

`注意！！！此处的'<>'在Oracle中是不等于的意思，但是Oracle中仍然保留了'!='符号，可能是为了老程序员的习惯才保留的吧，这就很人性了，两者效果是一样的，但是一般在Oracle中常用'<>'不等于操作符。`

3.1.4、为字段取别名

AS关键字取别名

`注意！！！AS 关键字可以省略不写，但是中间一定要有空格，还有别名最好最好不要使用中文，虽然语法允许中文，但是如果没有特殊要求下，最好不要使用，首先是命名不规范，其次就是太low，在企业中工作的时候基本是不允许的。`

查询空行

#是查询某列是空值，不是空字符串
select * from students where semail is null;

3.2、分页查询

emp2表为例

–rowID是表隐藏的地址列，用于保存每行数据的地址
–rownum是表隐藏的计数列,在where后使用，
–单条语句中不可以使用rownum的别名，因为rownum在select语句未执行前是未知字段
–rownum只能使用小于号“<”,如果想使用大于号“>”，大于号后只能是0，并且是查询所有数据

限制行数查询

--实现分页查询
--查询所有数据
select e.*,rownum from emp2 e where rownum>0;
select e.*,rownum from emp2 e;
--查询前9条数据
select e.*,rownum from emp2 e where rownum<=9;

--查询第6-9条数据（嵌套子查询-）
select * from (select e.*,rownum r from emp2 e where rownum<=9) where r>=5;

返回结果：

带排序的分页查询

--带排序的翻页(排过序后显示1-5)
--(如果使用了order by进行排序，在最内层的查询只做排序)
--(因为最内层排过序后，rownum序列的值发生了改变，想要显示前五条数据，要以该rownum为准)
select * from (
  select e.*,rownum r from (
     select * from emp2 order by sal desc --按工资降序排列
  ) e where rownum<10
) where r<=5;

返回结果：

3.3、SQL函数

日期函数
字符函数
上述函数实例操作

日期函数

--当前月份加上2---6月份
select add_months(sysdate,2) from dual
select add_months(sysdate,12) from dual--下年2022/4/13

--指定（入职）日期和当前日期相差月份
select empno,ename,hiredate,months_between(sysdate,hiredate) "sysdate-hiredate" from emp2
--直接拿当前日期减去入职日期
select empno,ename,hiredate,sysdate-hiredate from emp2

--返回某个月的最后一天的日期
select last_day(sysdate) from dual

--返回指定日期所在周的指定星期的日期（返回的只能是指定日期后的日期）
--例如今天周二，指定当前系统日期，然后指定星期一，返回的下个周的星期一的日期
select next_day(sysdate,'星期一') from dual

------截取时间值函数
select extract(year from sysdate) from dual--2021
select extract(month from sysdate) from dual--4
select extract(day from sysdate) from dual--13

--查询员工入职月份和当前日期月份相等的员工信息
select * from emp2
where extract(month from hiredate) = extract(month from sysdate)

字符函数

--将字符串的首字母变为大写
select initcap('hello') from dual

--将字符串的首字母转换为小写
select lower('WANG') from dual

--将整个字符串转换为大写
select upper('wang') from dual

--去掉字符串左边的指定字符串
select ltrim('abcdefgabc','abc') from dual

--去掉字符串右边的指定字符串
select rtrim('abcdefgabc','abc') from dual
       
--去掉字符串两边的空格
select trim('     abcdefgabc     ') from dual

--替换指定字符串
select translate('abcdeafg','a','A') from dual

--效果同上替换指定字符串
select replace('jack and jose','ja','b') from dual

-----------indexof(返回下标索引)
--返回指定字符串的位置
select instr('abc@163.com','@163') from dual

--截取字符串(从第几位开始截取几位)
select substr('abcdefg',3,2) from dual--从第三位开始截取两位

--拼接字符串
select concat('aaa','bbb') from dual
--在Oracle中也可以用'||'来拼接字符串
select 'aaa'||'bbb' from dual

--指定字符串长度后，前者如果不够指定长度，用后者字符串从左边拼接
select lpad('function',10,'=!=!=') from dual

--指定字符串长度后，前者如果不够指定长度，用后者字符串从又边拼接
select rpad('function',10,'=!=!=') from dual

--取出字符串中两边指定的字符串
select trim(9 from 999911119999) from dual

--返回字符串的长度
select length('abcd') from dual;

--判断指定字段是否为空(类似于java中三元运算符)
--其中'500'可带引号也可不带
select empno,ename,sal,comm,decode(comm,'500','补助为500',comm) from emp2

--将日期转换为指定字符串类型
select empno,ename,hiredate,to_char(hiredate,'yyyy-mm-dd') from emp2

--将字符串转换为指定格式日期
insert into emp2(empno,ename,hiredate,deptno) 
values(9999,'haha',to_date('2021-09-09','yyyy-mm-dd'),10)

select * from emp2 where empno = 9999

--将字符串转换为数字
select to_number('100') from dual;

3.4、案例1

-------创建表Card
create table Card(
 cid varchar2(12) primary key not null,
 cname varchar2(10) not null,
 PassWord varchar2(6) not null
)

--插入数据
insert into card values('181451081001','张三','hokjai');
insert into card values('181451081002','李四','uofaud');
insert into card values('181451081003','王五','ofuisd');

--查询表数据
select * from card;

--案例1
--卡里面的“O和0”（哦和零）“i和1”（哎和一），
--用户反映说看不清楚，公司决定，
--把存储在数据库中的密码中所有的“O”都改成“0”，把所有的“i”都改成“1”；

------------分析案例1
--个人认为最好把大小写字母全部转换掉
--首先将密码中的O和i全部转换成大写字母(或者小写字母)，这里以转换成大写为例
update card set password = replace(password,'o','O');
update card set password = replace(password,'i','I');
--也可一行代码完成两个转换
update card set password = replace(replace(password,'o','O'),'i','I');

--然后再将O和I转换成0和1
update card set password = replace(replace(password,'O','0'),'I','1');

--再次查询
select * from card;

3.5、案例2

-----创建表SellRecord（对数字进行排列）
create table SellRecord(
 numid varchar2(10) primary key not null,
 ListNumber varchar2(10) not null
)

--插入数据
insert into sellrecord values('1001','13-1');
insert into sellrecord values('1002','13-2');
insert into sellrecord values('1003','13-3');
insert into sellrecord values('1004','13-10');
insert into sellrecord values('1005','13-100');
insert into sellrecord values('1006','13-108');
insert into sellrecord values('1007','13-18');
insert into sellrecord values('1008','13-11');
insert into sellrecord values('1009','13-15');
insert into sellrecord values('1010','14-1');
insert into sellrecord values('1011','14-2');


select * from sellrecord;

--1.先找到'-'的位置
select instr(listnumber,'-') from sellrecord;

select substr('abcdefg',3) from dual;

--2.代码实现
select listnumber from sellrecord 
order by 
to_number(substr(listnumber,1,instr(listnumber,'-')-1)),
to_number(substr(listnumber,instr(listnumber,'-')+1));

如果不理解如下分析：

--根据'-'的位置截取后边字符并转换为数字
--先判断‘-’前的数字是几进行排序，然后再判断‘-’后的数字进行排序
select 
s.*,
to_number(substr(listnumber,1,instr(listnumber,'-')-1)) "-l",
to_number(substr(listnumber,instr(listnumber,'-')+1)) "-r",
rownum 
from sellrecord s order by "-l","-r";