0402_oracle_表关系

wangjianme 贡献于2013-05-06

作者 Administrator  创建于2013-03-31 15:20:00   修改者王健  修改于2013-04-02 06:55:00字数8720

文档摘要: distinct关键字介绍。事务。事务基础。Delete与truncat的事务。显式的事务。隐式的事务。事务的保存点。2、今天的内容:1、Oracle的函数,dual伪表。
关键词:

0402 1、内容回顾 SqlPlus的基本命令 DDL创建虚拟列-、创建临时表。 alter table sometable Add (ss as (col1+col2)); Int into Sometable(id,name,salary,month) values(01,’Jack’,98.22,4); like(escape)、exists。distinct关键字介绍。 事务。 事务基础。 Delete与truncat的事务。 显式的事务。 隐式的事务。 事务的保存点。 2、今天的内容: 1、Oracle的函数,dual伪表。 1.1、在查询中,使用Oracle提供的函数。如字符串函数、日期函数、空值函数、转换函数。 1.2、聚集函数- count,sum,max,min,avg,group by、having。 1.3、使用这些函数的限制。 2、约束、表关系 2.1、约束的分类、创建各种不同的约束。 2.2、主键入外键约束,从而带出表之间接关系和E-R实体关系图。一对一、一对多、多对多查询。左右内连接。SQL92/SQL86的语法差别及建议。 Left join,corss join , inner join, right join,union all 3、子查询、关联子查询、非关联子查询 3.1、分页查询,rowid和rownum列的使用。 3、Delete与truncat Delete删除表中的数据。 Truncat截断表中的数据。-只能是将表中的数据全部删除。- select * from stud; --执行delete - 删除了表中所有行 - 开了一个事务 delete from stud; --回滚 rollback; --截断表 - 直接将表中的数据全部的删除,不会开始一个事务 truncate table stud; select * from stud; rollback; 4、dual --dual表具体操作 --dual表中,只有一列。 desc dual; --查询日期 select sysdate from dual; --显示你好 select '你好' as nm from dual; 5、函数 --dual表具体操作 --dual表中,只有一列。 desc dual; --查询日期 select sysdate from dual; --显示你好 select '你好' as nm from dual; -- select abs(-90) from dual; insert into stud values(-99,'Rose'); select * from stud; select abs(id)+1 as valueis from stud; --sign返回一个数是正数还是负数 select sign(-23),sign(-56),sign(23),sign(56),sign(0) from dual; --round回舍五入,就从第N位后面的那一位开始计算 select round(1.5787,2),round(1.45458,2) from dual; --floor - 地板,去掉所有的小数位,取这个数的最小整数 select floor(1.5787),floor(-1.5787) from dual; --取大于这个数最小整数 select ceil(1.1787),ceil(-1.1787) from dual; --x的n次方 select power(2,3) from dual; --取余数=1 select mod(7,3) from dual; -- select sqrt(3) from dual; --在计算机中,所有的字符都是用ascii = A=65,B=66,a=97 select ascii('A'),ascii('a') from dual; --char将一个ascii转成字符 select chr(83) from dual; --用concat将两个串成一个串 select concat('你好','Jack')||'早上好' from dual; --将所有以空格分开的字符第一个转成大写 select initcap('hello world') from dual; --lower-转成小写upper转成大写 select lower('HELLOWORLD') from dual; select upper('hello world') from dual; -- HELLO WORLD = Hello World select initcap(lower('HELLO WORLD')) from dual; --查询我目前有多少表-可以通过查询oracle给的tab表查询你有什么表 select * from tab; --tab不是最终保存用户创建的表的表 --所有的用户的表,都保存到了Oracle的字典表中user_tables; select * from user_tables; --所有表的列的信息则保存到user_tab_columns select * from user_tab_columns; --查询stud表,有哪些列 select * from user_tab_columns where table_name=upper('stUd'); --从一个字符串找另一个串,返回它出现的位置 select instr('Hello World Good English',initcap('good')) from dual; --中文 select instr('你好同学大家来学习','同学') from dual; --使用instrb-按字节的方式找 select instrb('你好同学大家来学习','同学') from dual; --判断一个字符的长度 b=binaray select length('你好'),lengthb('你好') from dual; --左边添加字符 select lpad('abc',4,'*'),rpad('abc',4,'#') from dual; --删除字符串两边的空格 select '>'||rtrim(ltrim(' ab c '))||'<' aa from dual; select '>'||trim(' abc ')||'<' aa from dual; --去中间的空格-替换 select replace(replace('A B','A','X'),' ','') aa from dual; --从革个串中取子串 --从ABCDEF取EF select substr('ABCDEF',5,2) from dual; --解析email /** 取出你的注册的邮件域名 wangjian_me@126.com = 126.com = substr(wangjian_me@126.com,instr(wangjian_me@126.com,'@')+1) 549051701@qq.com = qq.com wangjianme@sina.com = sina.com */ select substr('549051701@qq.com',instr('549051701@qq.com','@')+1) from dual; --创建一个表 create table mails( id int, email varchar(50) ); insert into mails values(1,'wang@11.com'); insert into mails values(2,'waadfaafsng@sina.cn'); insert into mails values(3,'wanadfag@126.com'); insert into mails values(4,'wasdfasdfang@88cc.com'); insert into mails values(5,'wasdfasdfang@126.com'); commit; --计算某个邮件被注册的个数 select substr(email,instr(email,'@')+1) mail,count(substr(email,instr(email,'@')+1)) from mails group by substr(email,instr(email,'@')+1) --soundex-声音类似就可以查询出来 select * from stud where soundex(name)=soundex('Mic'); insert into stud values(1,'Jim'); insert into stud values(2,'Jack'); insert into stud values(3,'Mike'); /** 时间日期函数 */ --加上几个月以后时间 select sysdate,add_months(sysdate,-1) from dual; --返回某个月的最后一天,可以用于计算2月的最后一天 select last_day(to_date('2004-02','yyyy-mm')) from dual; --返回下一个周4是几号 select next_day(sysdate,'星期四') from dual; --对于时间来说, -- 时间:yyyy-mm-dd hh24:mi:ss = sysdate -- 时间戳 :yyyy-mm-dd hh24:ss 000 00000000 - timestamp select current_timestamp from dual; -- select dbtimezone,sessiontimezone from dual; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --以下是加一分钟 select sysdate+(INTERVAL '1' MINUTE) from dual; --以下是加一小时 select sysdate+(INTERVAL '1' HOUR) from dual; --以下是加一天 select sysdate+(INTERVAL '1' Day) from dual; --转换函数 --to_char将任意一个对象转成串 --转时间 select sysdate from dual; select to_char(sysdate,'yyyy-mm-dd') from dual; --将两个数相加 select to_number('90')-to_number('87') from dual; --将一个字符串转成时间 --计算2009-09-09 23:45:12以后89小时以后是什么时间 select to_date('2009-09-09 23:45:12','yyyy-mm-dd hh24:mi:ss')+(INTERVAL '-1' HOUR) from dual; --聚合函数 --用count计数,count对于null值为说是不数的,条件是count(addr)=2 select * from stud; select count(*) 学生数量 from stud ; --对于count来说,我们用的更多的是- 用1的方式更快 select count(1) from stud; --对于包含null值的列进行count,因为addr中有一个null所以只算有值的列 select count(addr) from stud; --再删除stud中的所有数据 delete from stud; commit; --如果表中没有任何记录,则永远返回一行一列 insert into stud values(1,'Jim','JN'); insert into stud values(2,'Jim','JN'); insert into stud values(3,'Jack','BJ'); insert into stud values(4,'Rose','SH'); insert into stud values(5,'Tom','SH'); commit; select * from stud; --分组函数是group by - 按什么进行分组 --统计来自各地的人数 select count(1),addr from stud group by addr; --对分组以后数据进行过虑 --在where中不可以出现聚合函数 --只能是在having中出现 select count(1),addr from stud where 1=1 and 1=1 and 1=1 group by addr having count(1)>=1 order by count(1); --avg-平均 select * from stud; --添加一列 alter table stud add score numeric(5,2); --修改数据 update stud set score=id*34.2; commit; select * from stud; --查询这些学生的平均分 select avg(score) from stud; --和/人数 select sum(score)/count(1) from stud; --查询最高的分数的人 select max(score) from stud;--171 --根据分数查询某个人 --子查询==在一个查询内部双出现了另一个查询 --先执行子查询,再执行外部查询 select * from stud where score=(select min(score) from stud); --查询分数超过平均分的人 select * from stud where score<(select avg(score) from stud); --创建一个表 create table t2(age int); insert into t2 values(321); select stddev(age) from t2; --连续求和 --创建一个表 create table mm( m int, money int ); insert into mm values(1,100); insert into mm values(2,200); insert into mm values(3,50); insert into mm values(4,400); --查询 select * from mm; --连续求和 select m,money,sum(money) over(order by m) from mm; --nvl=null value = 你是空值吗 --语法:nvl(字段名,'默认值'); select * from stud; insert into stud(id,name) values(6,'张三'); --示例 select id,name,nvl(addr,'没有地址'),nvl(score,0) from stud; --语法:nvl2(字段的值 is null,'如果不是null就显示这个值','如果是null就显示这个') select id,name,nvl2(addr,'有地址','没有地址'),nvl2(score,100,0) from stud; 6、rowid与rownum --默认在一个表中,会存在两个oracle自己添加的列 --rowid - 用uuid的方式记录这行的唯一 --ronum - 只是记录某一行的行号 select rowid,rownum,id,name,addr,score from stud; --用rownum实现分页查询 --表中一共6行。每页显示3行,则一共分2页=6/3=2 --查询第一页-对于使用伪列,最好不要直接使用而应该使用子查询 select * from stud where rownum>=5 and rownum<=6; --先查询出所有数据-子查询也可以在from子句中 --以下是查询第一页 select * from (select id,name,addr,rownum num from stud) ax where ax.num>=1 and ax.num<=3; --再写入一行新的记录 insert into stud(id,name) values(7,'李四'); commit; select * from stud; /** 一共是7行/3=2 + mod(7,3)=3 第1页: 1~3 如何算如1~3 = : (第1页-1)*每页显示几行3 = start=0+1=1,end=start+(3-1)=3 第2页: 4~6 = (2-1)*3+1=4=start,end=start+3=6; 第3页: 7~9 */ select idd,adr,num,rownum from (select id as idd,name,addr adr,rownum num from stud) ay where ay.num>=5 and ay.num<=9; 7、sys_guid guid = Global Unique ideitify = 全局唯一标识符。 组成一个32位的唯一的值,字符串。 我们有些列,主键,不能使用int。 8、约束 所有的约束、表、列在数据库都是一唯一的对象。都可以指定名称。 Create table stud( id int .. ); 表的完整的性约束: 完整性: 1:保证某些列不能重复。 A:主键 primary key. = 主键约束 不能为null值。不能重复。 主键同时也会创建索引。一个表的索引相当于一个表的目录。 通过主键查询信息快。Where id=? B: unique = 唯一约束 可以为null(只能有一个),但不能重复. 2:不能超出我规定的范围。Sex char(1) – 男|女,age(0~120) A:check – 检查约束 3:主与子的约束。主表中没有的数据,子表中不能存在。表与表之间的约束。 A: primary and foreign key(外键约束) 4:非空约束 – 不能为null。 5:默认值约束。 8.1、主键约束 – primary key 可以作用在一个列上,也可以作用在多个列上。 通过代码的方式创建主键: --第一种创建主键的方式,直接在声明时指定 create table t3( id int primary key, name varchar(30) ); select * from t3; --测试主键 insert into t3 values(3,'Tom'); --查询Oracle的数据字典获取这个表上的主键叫什么名:user_constraints select * from user_constraints; --删除这个主键 alter table t3 drop constraint SYS_C006987; --第二种指定主键名称 create table t4( id int constraint t4_pk primary key, name varchar(30) ); --第二.1种,在单独一行指定约束名称 create table t5( id int, name varchar(30), constraint t5pk primary key(id) ); --第三种: create table t6( id int, name varchar(30) ); --通过修改表的方式添加约束 alter table t6 add constraint t6_mypk primary key(id); --其他操作 insert into t6 values(1,'Jack'); select * from t6; --禁用 alter table t6 disable constraint t6_mypk; --启用 alter table t6 enable constraint t6_mypk; delete from t6; commit; 8.2、在多个列的上主键约束 第一种创建联合主键的代码: 第二种: 8.2、唯一约束 关键字是unique. 要求用户输入的值不能重复。 8.3、检查-check-用于限制用户的值 如:性别, Sex char(1); --1:男,0:女 以下是就用于限制值: 在外创建检查约束: --检查约束 create table t10( id int, name varchar(30), sex char(1) check (sex in ('1','0')) ); select * from t10; --写入记录测试 insert into t10 values(3,'OracleRose','0'); delete from t10 where sex='2'; commit; --删除这个约束 alter table t10 drop constraint SYS_C006996; --添加这个约束 alter table t10 add constraint t10_chk check (sex='1' or sex='0'); --要求给name列添加一个约束,添加的条件是必须用户名以Oracle开始 alter table t10 add constraint t10_chk2 check (name like 'Oracle%'); 8.4、非空约束和默认值约束 9、总结试题 函数: Concat , || To_char(sysdate,’yyyy-mm-dd hh24:mi:ss’); To_date(‘2009-09-09 12:23:23’,’yyyy-mm-dd hh24:mi:ss’); To_number 聚合: Count Sum Avg Min/max 分组 Group by 分组成后条件 Having 限制: 在使用count..聚合函数时,如果在查询中出现了其他列,则这个列必须要出现在group by中。 如:错误的 Select addr,count(1) from stud; 正确: Select addr,count(1) from stud group by addr; 子查询: 查询某些学生大于平均分的学生的姓名 表中的伪列: Rowid – 闪回 rownum作业。 - 子查询。 Nvl – 对null Nvl2 Floor Ceil Abs/round/mod/ 串 Instr - 求位置 Substr 子串 Length/legnthb soundex 约束: 主键 唯一 检查 默认 非空 9.2、 要求创建一个BBS用户注册的表: Id – 主键。Name不能同名。 要求用户输入出生日期 要求用户输入年龄。 Sex只可是1,0.- 查询时显示成男女。 要求统计 20以下是BBS的人数有多少人 21~30岁有多少人 31~50多少 >=51人数

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 2 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档