oracle数据库之常用的函数练习

/*此文章可以作为sql脚本直接运行，某些函数限于oracle数据库！

前面建表和数据插入可以不看，直接看后面红色部分的代码，前面的表只是提供一些数据供大家练习，不用自己再去建表了，真正的内容在后面*/

--人员表
create table person(
personID     number ,
pname        varchar2(20) constraint NN_pname not null,
psex         char(2) ,
pbirth       date  ,
constraint   PK_personID primary key (personID) ,
constraint   CK_psex  check (psex='男' or psex='女')
);

--增加数据
create sequence seq_personID ;
insert into person values (seq_personID.Nextval,'david','男',to_date('1990-09-10','yyyy-mm-dd'));
insert into person values (seq_personID.Nextval,'ggyy','男',to_date('1991-10-10','yyyy-mm-dd'));
insert into person values (seq_personID.Nextval,'朱刀','男',to_date('1970-10-10','yy-mm-dd'));
insert into person values (seq_personID.Nextval,'泥巴','女',to_date('1991-10-10','yy-mm-dd'));
insert into person values (seq_personID.Nextval,'憨坨','女',to_date('1991-10-10','yy-mm-dd'));
insert into person values (seq_personID.Nextval,'李静芳','女',to_date('1990-09-10','yyyy-mm-dd'));
insert into person (personID,pname,psex) values (seq_personID.Nextval,'胖子','男');
insert into person (personID,pname,psex) values (seq_personID.Nextval,'小成成','男');
insert into person (personID,pname,psex) values (seq_personID.Nextval,'乐姐','女');
insert into person (personID,pname,psex) values (seq_personID.Nextval,'靓崽波','男');

--登录账号表
create table loginzh(
userID       number ,
personID     number ,
zhanghao     varchar2(20) ,
passwd       varchar2(20) ,
logintime    date ,
loginIP      varchar2(15) ,
constraint   PK_userID primary key (userID) ,
constraint   FK_personID foreign key (personID) references person (personID)
);

--为这个表加点数据
create sequence seq_userID ;
insert into loginzh  values (seq_userID.Nextval,1,'111111','123456',sysdate,'127.0.0.1');  -- sysdate为系统当前时间
insert into loginzh  values (seq_userID.Nextval,2,'222222','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,3,'333333','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,4,'444444','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,5,'555555','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,6,'666666','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,6,'777777','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,6,'888888','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,6,'999999','123456',sysdate,'127.0.0.1');
insert into loginzh  values (seq_userID.Nextval,6,'000000','123456',sysdate,'127.0.0.1');

--角色表
create table trole (
roleID       number ,
rolename     varchar2(20) ,
roleremark   varchar2(100) ,
constraint PK_roleID primary key (roleID)
);

--为这个表加点数据
create sequence seq_roleID ;
insert into trole values (seq_roleID.Nextval,'剑圣','一个能转出剑刃风暴和无敌斩的家伙！');
insert into trole values (seq_roleID.Nextval,'宙斯','一个能全屏放闪电的家伙！');
insert into trole values (seq_roleID.Nextval,'LION','用大招抢人头的屌丝！');
insert into trole values (seq_roleID.Nextval,'炸弹人','我不说什么了...');
insert into trole values (seq_roleID.Nextval,'UG','辉耀在手，天下我有！');
insert into trole values (seq_roleID.Nextval,'风行者','风一样的男子，哦不，是女子！');
insert into trole values (seq_roleID.Nextval,'小黑','和风行者是双胞胎，只是一个整天穿白衣服，一个整天穿黑衣服');
insert into trole values (seq_roleID.Nextval,'凤凰','凤凰开大有两个结果，要不就是信春哥，要不就是蛋都碎了...');
insert into trole values (seq_roleID.Nextval,'术士','A帐加刷新能瞬间甩出四个石头人的家伙');
insert into trole values (seq_roleID.Nextval,'火枪','传说哥');
select * from trole ;

--账号与角色表
create table userandrole(
userID number ,
roleID number ,
constraint FK_userID foreign key (userID) references loginzh (userID) ,
constraint FK_roleID foreign key (roleID) references trole (roleID)
);

--为这个表添加数据
insert into userandrole values (1,1);
insert into userandrole values (2,1);
insert into userandrole values (3,2);
insert into userandrole values (4,2);
insert into userandrole values (5,3);
insert into userandrole values (6,3);
insert into userandrole values (7,5);
insert into userandrole values (8,6);
insert into userandrole values (9,7);
insert into userandrole values (10,8);

--功能节点表
create table tfunction(
functionID number ,
nodename   varchar2(20) ,
noderemark varchar2(100),
constraint PK_functionID primary key (functionID)
);

--加数据
create sequence seq_functionID ;
insert into tfunction values (seq_functionID.Nextval,'剑刃风暴','反正就是这么转，转的时候魔免');
insert into tfunction values (seq_functionID.Nextval,'鬼影重重','一个鬼影跟着你屁股后面跑，还打你');
insert into tfunction values (seq_functionID.Nextval,'死亡一指','抢人头必备！');
insert into tfunction values (seq_functionID.Nextval,'无敌斩','孤身一人被逮到了？我削你！');
insert into tfunction values (seq_functionID.Nextval,'地狱火','还不就是天上掉下个石头人');
insert into tfunction values (seq_functionID.Nextval,'超级新星','好大一个蛋，好烫！');
insert into tfunction values (seq_functionID.Nextval,'仙女之尘','哦一下～');
insert into tfunction values (seq_functionID.Nextval,'新月之痕','就是一道动感光波');
insert into tfunction values (seq_functionID.Nextval,'梦境缠绕','一切皆为梦境，越逃脱，越受伤．．．');
insert into tfunction values (seq_functionID.Nextval,'相位转移','传说中的八度空间');

--角色到节点表
create table roleandnode(
roleID number ,
functionID number ,
constraint FK_roleID1 foreign key (roleID) references trole (roleID) ,
constraint FK_functionID foreign key (functionID) references tfunction (functionID)
);

--加数据
insert into roleandnode values (1,1);
insert into roleandnode values (2,1);
insert into roleandnode values (3,1);
insert into roleandnode values (4,2);
insert into roleandnode values (5,2);
insert into roleandnode values (6,6);
insert into roleandnode values (7,7);
insert into roleandnode values (8,8);
insert into roleandnode values (9,9);
insert into roleandnode values (10,6);

--计数函数  count  注意  ：只能用于数值型字段
----统计总行数
select count (*) as 总人数 from person ;
----统计满足某些条件的记录的行数
select count (*) as 男同学 from person where psex = '男' ;
select count (*) as 女同学 from person where psex = '女' ;

--第一个
create table bbc(
 name varchar(50) primary key not null,
 region varchar(60),
 area decimal(10),
 population decimal(11),
 gdp decimal(14,2)
);
insert into bbc values('中国','亚洲',960000,1400000000,100);
insert into bbc values('美国','美洲',45000,2100000,980);
insert into bbc values('日本','亚洲',2000,140000,90);
insert into bbc values('印度','亚洲',300,240000000,60);
insert into bbc values('法国','欧洲',230000,3000,40);
insert into bbc values('俄罗斯','欧洲',450000,1400000,50);
insert into bbc values('英国','欧洲',961000,13000000,30);
insert into bbc values('马来西亚','大洋洲',960000,140000000,60.5);
insert into bbc values('埃及','非洲',50000,140000,40);
insert into bbc values('阿尔及利亚','非洲',10000,14000,20);

--求平均数  avg函数   注意  ：只能用于数值型字段
----求某个字段的平均数
select avg(gdp) as 平均gdp from bbc ;
select trunc(avg(gdp),2) as 亚洲平均gdp from bbc where region = '亚洲';

--返回字符串长度
select name,length(name) from bbc ;

----substr   字符串切割函数
select substr ('123123',2,3) from dual ;
select * from dual ;   --系统测试表

--四舍五入函数  round (数值)
select round(123.5) from dual ;
select round(123.4) from dual ;

--取整函数  floor (数值)
select floor(123.789) from dual ;

--设置精度函数  trunc (数值,数值的精度)    如果设置的精度值大于已有的精度值，那么取已有的精度
select trunc (123.11111,2) from dual ;
select trunc (123.11,3) from dual ;  --设置的精度大于已有的精度

--分组   group by 字段
select region , count(*) as 国家数 from bbc group by region ;
select psex,count(*) as 人数 from person group by psex ;

--having   在分组后筛选     where   在分组前筛选
select region , count (*) as 国家数 , sum (area) as 总面积
from bbc where gdp > 50
group by region
having sum (area)>50000

/*   下面的练习可以帮大家检测下自己的学习情况，如果都能独立完成的话那么常用的函数就基本掌握了   */

--练习
--第二个
create table nobel(
 yr int,
 subject varchar(15),
 winner varchar(50)
);
insert into nobel values(2005,'医学奖','张风');
insert into nobel values(2005,'物理学奖','david');
insert into nobel values(2005,'和平奖','刘小');
insert into nobel values(2006,'文学奖','何林');
insert into nobel values(2006,'医学奖','张风');
insert into nobel values(2006,'和平奖','奥巴马');
insert into nobel values(2007,'医学奖','张三风');
insert into nobel values(2007,'和平奖','朱刀');
insert into nobel values(2008,'医学奖','张三风');
insert into nobel values(2008,'文学奖','朱刀');
insert into nobel values(2008,'物理学奖','david');
insert into nobel values(2008,'物理学奖','ggyy');
insert into nobel values(2008,'物理学奖','朱刀');

select * from nobel ;

--给出表中得过诺贝尔奖的总人数
select count(distinct winner) as 总人数 from nobel ;
 
--给出每人诺贝尔奖的获奖总次数
 select winner,count(*) as 总次数 from nobel group by winner ;
 
--显示每个奖项的获奖总次数
 select subject , count(*) as 次数 from nobel group by subject ;
 
--显示每个奖项在2005年获奖人数
 select subject ,count(*) as 获奖人数 from nobel where yr = 2005 group by subject ;
 
--显示每个奖项不同获奖者的人数
 select subject ,count (distinct winner) as 人数 from nobel group by subject ;
 
--显示每个奖项有多少年有人获奖
 select subject ,count(yr) as 年数 from nobel group by subject ;

--显示当年有三个诺贝尔奖的年份
 select yr, count(*) as 奖数 from nobel group by yr having count(*)>=3 ;
 
--显示得奖超过一次的获奖者
 select winner, count(*) as 获奖次数 from nobel group by winner having count(*)>1 ;
 
--显示得到多个奖（不同种类）的获奖者
 select winner, count (distinct subject) as 获奖种类
 from nobel group by winner
 having count(distinct subject)>1 ;

--显示2005年及以后，有三个人获得同一奖项的年份以及奖项
select yr as 年份 ,subject as 奖项 ,count (winner) as 获奖人数
from nobel where yr > 2005
group by yr,subject
having count (winner) =3 ;