orcale 数据库语句（二）

较复杂查询

先在用户表中添加工资和部门id两列

alter table userinfo add sal number(10);
alter table userinfo add deptid number(4);

查询工资总和

select sum(sal) salSum from userinfo;

分组求和（按部门id查工资总和）

select deptid,sum(sal) salSum from userinfo group by deptid;--按部门id分组
select deptid,status,sum(sal) salSum from userinfo group by deptid,status;--按部门id和状态分组
select max(sal) salSum from userinfo;--查询工资中最大值
select min(sal) salSum from userinfo;--查询工资中最小值

合并结果集

select max(sal) salSum from userinfo--合并工资最大值和最小值
union
select min(sal) salSum from userinfo;

select * from userinfo where status=0 --合并状态为0和状态为1的数据，							      
union								  --没有的列可以用相同的类型的数或字母代替
select id,name,password,status,0,0 from userinfo where status=1;
select * from userinfo where status=0--合并状态为0和状态为1的数据
union all
select * from userinfo where status=1;

去重查询

select distinct status,name from userinfo;

按部门查询平均工资

select deptid,avg(sal) avg,sum(sal) salSum,count(id) from userinfo group by deptid;
--为确保平均工资是正确的，按部门显示出其部门的人数、总工资

查询平均工资高于6000的部门（部门编号和平均工资）

select * from（
       select deptid,avg(sal) avg,sum(sal) salSum,count(id) from userinfo group by deptid
 )where avg>6000;--综合使用，把查询的各部门的平均工资的表当成总表，用where加条件实现该语句

工资高于bb

select * from userinfo where sal>(select sal from userinfo where name='bb');--将bb的工资查询出来
--作为条件来实现查询工资高于bb的

工资高于部门1所有用户的数据

select * from userinfo where sal>(select max(sal) from userinfo where deptid='1');
--此语句工资高于部门1的所有用户的数据，意思就是只要高于部门1工资的最大值的就等于高于所有用户了

工资高于部门1任一用户的数据

select * from userinfo where sal>(select min(sal) from userinfo where deptid='1');
--与上句相反，要求只高于部门1的工资最小值就好

常用的函数

select id,name,status,decode(status,0,'注销',1,'正常',2,'特别') s_name from userinfo;
select u.*,decode(status,0,'注销',1,'正常',2,'特别')s_name from userinfo u;--输出的根据值，0 为 注销 1 为正常 2 为特别
select u.*, nvl(phone,0) phone_name from userinfo u;--输出的有就是原值，没有就输出0，输出的值的类型应该与数据类型一致
select u.*, nvl2(phone,'有'，'无') phone_name from userinfo u;--输出的有就是‘有’，没有就是‘无’，输出类型不受限制
select u.*, concat(id,name) from userinfo u;--拼接（这种只能俩个两个拼接）
select u.*,(id||'-'||'name'||'-'||'status') from userinfo u--可以多个拼接，中间还能加字符串;
select u.*,substr(name,2) from userinfo u;
select u.*,substr(name,2,3) from userinfo u;--截取位置开始 （第一位就是1），截取3位
select u.*,substr('abcdefg',2,3) from userinfo;--自己创建的字符串也可以
select u.*,rpad(name,4) from userinfo u;
select u.*,rpad(name,4,'*') from userinfo u;--不够四位的用‘*’填充
select u.*,instr(name,'d') from userinfo u;--包含‘d’的返回d的位置的值
select u.*,instr(name,'d',4) from userinfo u;--包含‘d’的返回d的位置的值，从第四位开始查询
select u.*,instr(name,'d',4,2) from userinfo u;--包含‘d’的，从第四位开始查询，第二次的值

--trim函数
--leading   开头字符
--trailing    结尾字符
--both        开头和结尾字符
--trim_character  去除的字符
--trim_source      修剪源

--如果指定leading参数，oracle数据库将去除任何等于trim_character的开头字符。
select trim(leading 'x' from 'xdylan') "test_trim" from dual;
--如果指定traling参数，oracle将去除任何等于trim_character的结尾字符。
select trim(trailing 'x' from 'dylanx') "test_trim" from dual;
--如果指定了both参数或者三个参数都未指定，oracle将去除任何等于trim_character的开头和结尾字符。
--可写可不写，默认就是both
select trim(both 'x' from 'xdylanx') "test_trim" from dual;
select trim('x' from 'xdylanx') "test_trim" from dual;
--如果没有指定trim_character参数，默认去除的值为空格。
select trim(both from ' dylan ') "test_trim" from dual;
--如果只指定修剪源（trim_source），oracle将去除trim_source的开头和结尾的空格。
select trim(' dylan ') "test_trim" from dual;
--trim函数返回一个varchar2类型值。该值最大的长度等于trim_source的长度

--如果trim_source和trim_character有一个为null，则trim函数返回null。
select trim(trailing null  from 'dylan ') "test_trim" from dual;
select trim(trailing 'd'  from null ) "test_trim" from dual;

连接

先做准备工作，假设这是一个关于学生的数据库，我们已经有了用户表，现在还需要一个成绩表

--创建成绩表
create table result(
       ID int primary key,
       student_id int not null,
       course_id int not null,
       score int
 );

--同时插入多条数据
--1
 --由于insert all方式插入多条时，通过sequence获取的值是同一个，不会自动获取多个，所以id需要其他方式设置
    insert all
           into result values(1,'2','1','66')
           into result values(2,'2','2','69')
           into result(ID,student_id,course_id,score) values(3,'3','1','82')
    select * from dual;
    --insert all 还支持往不同的表里插入数据
    --dual是系统给的一个空表，在（一）中我们用于创建了序列
--2
    insert into result (ID,student_id,course_id,score)
                select 4,'3','2','93'from dual
    union all select  5,'4','1','55'from dual
    union all select  6,'111','1','55'from dual 

交叉连接

select * from userinfo,student;
select * from userinfo cross join student;
select * from userinfo u,result r where u.id=r.student_id;
select distinct u.* from userinfo u,result r where u.id=r.student_id;
--distinct列出不同的，相同的会合并

内连接

select * from userinfo u join result r on u.id=r.student_id;
select * from userinfo u inner join result r on u.id=r.student_id;--inner可省略

在条件相同时交叉连接和内连接的查询内容一样

外连接：会以主表为主，没有的地方为空

select * from userinfo u left join result r on u.id=r.student_id;--左外连接,以左边的表为主表
select * from userinfo u left outer join  result r on u.id=r.student_id;--outer可省略
select * from userinfo u right join result r on u.id=r.student_id;--右外连接，以右边的表为主表
select * from userinfo u right outer join result r on u.id=r.student_id;--outer可省略

左外连接如图所示

右外连接如图所示

全连接

select * from userinfo u full join result r on u.id=r.student_id;
--查询结果没有的地方都会空着

如图所示

自连接（查询与aa状态一致的用户的信息）

select * from userinfo where status=(select status from userinfo where name='aa');
select u1.* from userinfo u1,userinfo u2 where u1.status=u2.status and u2.name='aa';
select u1.* from userinfo u1 inner join userinfo u2 on u1.status=u2.status where u2.name='aa';

自然连接（要有一个同名列，根据同名列进行合并）

select * from userinfo u natural join result r;

如图，因两个表中都有id，id被合并了。

判断

将状态从数字转换为汉字区分数据。

select u.*,
       case to_char(status)
         when '0' then '已删除'
         when '1' then '未审核'
         when '2' then '正常'
         when null then '无'
        end sta
from userinfo u;

已基本满足效果，但不能处理数据为空的地方。

--推荐使用
select u.*,
       case 
         when status='0' then '已删除'
         when status='1' then '未审核'
         when status='2' then '正常'
         when status is null then '无'
        end sta
from userinfo u;

改进之后，已经可以处理空值啦

今天你学费（hui）啦吗？