/*此文章可以作为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 > 50group by regionhaving 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 winnerhaving count(distinct subject)>1 ;--显示2005年及以后，有三个人获得同一奖项的年份以及奖项select yr as 年份 ,subject as 奖项 ,count (winner) as 获奖人数from nobel where yr > 2005group by yr,subjecthaving count (winner) =3 ;。