/*此文章可以作为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 ;