oracle知识讲解和实例

1、-1.数据库概念 1)什么是数据库 存储数据的仓库. 数据库DataBase:指的是按一定组织和结构存储的数据集合. 关系型数据库:以表结构存储 NoSQL:非关系型数据库,以键值对形式存储 数据Data:指的是程序中的字符串,数值,时间,图片,大文本等内容. DBMS(数据库管理系统):对数据库进行存储和管理的软件.例如Oracle,MySQL等 2)为什么要使用数据库 程序中的数据需要持久化.持久化可以采用文本和数据库系统. 使用数据库系统存储具有以下优点: -存储和查询操作方便(SQL) -安全性高 -大量数据存储 常用数据库目前处于关系型数据库阶段: Oracle(Oracle) My

2、SQL DB2(IBM) SQLServer(MS) Oracle发展史 -Larry Ellision在1970年看到IBM一个研究员写的一篇文章<论大型关系型数据存储原型> -Larry和Bob Miner,ED oates三人成立了*研究实验室.1979开发出一套数据库系统.命名Oracle.(SCOTT/TIGER) -1983年为了突出Oracle数据库产品,将公司名改成了Oralce. -第一位使用Oracle客户是美国中央情报局 -2009年4月20日74亿美金收购SUN *3)怎么使用数据库 数据库通常分为客户端和服务器端两部分. 服务器端负责创建存储结果,存储数据

3、 客户端提供使用者界面,操作服务器端的内容. Oracle客户端程序主要有下几个类型: -SQLPLUS命令行操作模式 -SQL Developer可视化操作模式 -DBA管理工具(DBA操作权限) 客户端程序通过发送SQL指令操作服务器端,SQL分类如下: -DDL(Data Definition（定义） Language) 数据定义语句.用于创建表结构 -DML(Data Manipulation（操作） Language) 数据操作语句.用于增删改表中数据 -DQL(Data Query Language) 数据查询语句.用于查询表中数据 -TCL(Transaction Control

4、 Language) 事务控制语句.用于将多个DML操作封装 成一个整体. -DCL(Data Control Language) 数据控制语句.用于用户权限控制. 日后,在Java需要通过JDBC技术+SQL实现对数据库的操作.*3.SQL初步 数据库以表为单元进行存储.表结构如下: 表是由行和列构成.列被称为字段;行被称为记录. 需要存储Person对象信息, 如果Person包含id,name,sex,age属性. 1)DDL语句 可以创建,删除,修改表结构. a.创建表 create table 表名( 列名 类型, 列名 类型, . ) 注意:最后一列类型后面不加",&qu

5、ot;逗号. create table person( id NUMBER(11), name VARCHAR2(20), sex VARCHAR2(2), age NUMBER(3) ); b.查看表结构 desc 表名; desc person; c.删除表结构 drop table 表名; /立刻删除,不放入回收站 drop table person purge; /恢复回收站中的表 flashback table 表名 to before drop; 2)DML语句 负责添加,删除,更新记录. a.添加 insert into 表名 (字段名1,字段名2.) values (值1,值2

6、.); insert into person values (4,'jack','男',30); insert into person (id,name,age,sex) values (1,'scott',40,'男'); /age不指定值,默认为null insert into person (id,name,sex) values (2,'tigger','女'); /为age显示指定null值 insert into person (id,name,age,sex) values (3,&#

7、39;larry',null,'男'); 提示:执行DML操作需要commit才会将数据 写入,更新,删除表 b.更新 update 表名 set 字段名=值,字段名=值 where 字段名=条件值 /将表中所有记录的age值更新成20 update person set age=20; /将符合name=larry的记录,age值更新成20 update person set age=32 where name='larry' /将id=2的记录age更新成20,sex更新成男 update person set age=20,sex='男&#

8、39; where id=2; c.删除 delete from 表名 where 字段名=条件值 提示:没有where子句,是删除所有记录. /删除id=4的记录 delete from person where id=4; 3)TCL 负责事务处理.可以将一个或多个DML语句封装成一个整体.commit;事务提交,将先前执行的DML操作确认rollback:事务回滚,将先前执行的DML操作撤销,撤销到上一次commit位置. 4)DQL 负责表数据的查询. select *或字段名. from 表名 where 字段名=条件值 提示: *代表所有字段; 不写where子句是查询所有记录.

9、/查询person表中所有记录所有字段的值 select * from person; /查询person表中所有记录的name和sex值 select name,sex from person; /查询name=scott的id和age值 select id,age from person where name='scott' /查询name=scott并且sex=男的所有字段信息 select * from person where name='scott' and sex='男' 提示:多个查询条件,可以采用and和or关键字连接.and表

10、示并且,条件必须同时满足;or表示或者,条件满足其中一个即可. 5)DCL 负责权限控制.(由DBA使用) create user.创建用户 grant 授权 revoke 收回权限=Oracle->数据库软件->SQL->Java+SQLNUMBER(11,2)/支持2位小数;9位整数1.字符串操作 1)字符串类型 char,varchar,varchar2,nvarchar2,long,clob 字符串类型常量值,采用单引号''括起来. a.char和varchar的区别 char固定长度字符串 varchar变长字符串 b.varchar和varchar

11、2的区别 varchar是ANSI标准组织制定的一个类型,目前Oracle中,varchar和varchar2含义相同,都代表变长字符串类型.日后,varchar类型含义可能会发生变化,但varchar2不会改变,因此Oracle推荐使用varchar2类型 *c.char和varchar2存储问题 存储单位(字节),能存储多少个字符,需要看存储编码. select userenv('language') from dual; dual:表示虚表;如果select查询内容不需要表参与,通常在from 写dual虚表. gbk->a-z,0-9占1个字节长度 中文字符占2个

12、字节长度 Unicode->都占2个字节长度 UTF-8->中文占3个字节长度*提示:为了保险,可以将存储字符数*3开辟空间.GBK编码:abcde中文字 ->11个字节长度abcdefghij ->10个字节长度 d. char和varchar2最大长度 varchar2最大长度4000,使用时必须指定大小. char最大长度2000,使用时可以不指定大小, 默认大小为1. e. nvarchar2类型 用于存储字符,不区分英文字符和中文字符. 单位(字符). nvarchar2(5)意思是可以存储5个字符 (一个中文算一个,一个英文也算一个). 内部采用Unicod

13、e编码统一存储中文和 非中文字符.create table foo_2(c1 varchar2(5),c2 nvarchar2(5);insert into foo_2 (c1,c2) values('中文','中文字');insert into foo_2 (c1,c2) values('中文','插中文字符a');/成功 f. long,clob 能存储字符类型,超过varchar2空间采用 下面类型: long最大能存储2G空间. clob最大能存储4G空间.*使用建议:知道存储几个字符采用char如果存储字符在某个范围区间

14、采用varchar2如果存储字符超出4000字节空间,采用clob. -select id,name from personwhere name='scott'SELECT id,name FROM personWHERE name='scott'select ID,NAME from PERSONwhere NAME='scott'- g.字符串函数(记住5个) -可以对字符串值进行操作.具体如下: concat(s1,s2)或者s1 | s2 select concat(c1,c2) from foo_1; select concat(

15、9;hello','scott') from dual; select concat('hello',c1) from foo_1; create table foo_3( first_name varchar2(10), last_name varchar2(20);insert into foo_3 (first_name,last_name)values ('larry','ellison');insert into foo_3 (first_name,last_name)values ('bob',

16、'miner');select concat(first_name,last_name) from foo_3;select first_name|last_name from foo_3;select first_name|' '|last_name from foo_3; -大小写转换 upper(s):将s转成大写 lower(s):将s转成小写 initcap(s):将s中单词首字母大写 select upper(first_name) from foo_3; select initcap(first_name) from foo_3; select i

17、nitcap(first_name)|' '|initcap(last_name) from foo_3; select initcap(first_name|' '|last_name) from foo_3 -过滤空格 trim(s):过滤s中前后(左右)空格 ltrim(s):过滤s中前面(左)空格 rtrim(s):过滤s中后面(右)空格 ' hello ' ' scott ' select trim(' hello ')|trim(' scott ') from dual; 结果hello

18、scott select ltrim(' hello ')|trim(' scott ') from dual; 结果hello scott (保留hello后面的,其他去掉) select trim(' hello scott ') from dual; 结果hello scott (中间空格无法去掉) -补位函数 lpad(s,n,char) : 当s不够n位时,在s左边补充 char字符. rpad(s,n,char) : 当s不够n位时,在s右边补充 char字符. 注意:如果s超出n位,采取截取方式保留n位./将first_name值格

19、式化成6位,不够右边补空格 select rpad(first_name,6,' ')|last_name from foo_3;结果如下:larry ellisonbob miner -截取函数 substr(s,begin,size):对s进行截取;begin表示从第几个字符开始截取;size表示截取多少个字符 substr(s,begin): 如果begin为负数,表示从后向前取begin位字符 如果begin为正数,表示从begin位开始取到最后 'javasejavaee12345' /从第7个字符开始截取,截取6个字符 select substr(&

20、#39;javasejavaee12345',7,6) from dual; 结果javaee /从第13位开始截取,取5个字符 select substr('javasejavaee12345',13,5) from dual; /从第13位开始截取,取到最后 select substr('javasejavaee12345',13) from dual; 结果12345 /从第7位开始截取,取到最后 select substr('javasejavaee12345',7) from dual; 结果javaee12345 /从后向前取

21、5个字符 select substr('javasejavaee12345',-5) from dual; 结果12345/从第2个字符取,取2个(一个中文算一个长度)select substr('中文字符',2,2) from dual;结果:文字-字符查找 instr(s,s1,begin,n):在s中查找s1出现的位置. begin表示从哪个字符开始查找;n表示第几次 匹配. 'abcabcabcabc' /查找第一个匹配'b'的字符位置 select instr('abcabcabcabc','b&

22、#39;) from dual; 结果2 /从第3个字符开始找'b',返回位置 select instr('abcabcabcabc','b',3) from dual; 结果5 /从第3个字符开始找'b',返回第2次匹配的位置 select instr('abcabcabcabc','b',3,2) from dual; 结果8 create table foo_4( name varchar2(20); insert into foo_4 values ('Hello.java'

23、); insert into foo_4 values ('Snake.java'); insert into foo_4 values ('MyThread.java'); 练习:取出类名 select substr(name,1,instr(name,'.')-1) from foo_4; 结果: Hello Snake MyThread -计算长度length(s):计算s字符个数.(一个中文算一个长度)2.数值操作 1)数值类型 number Oracle采用number类型定义整数和浮点数. number(p,s) : p表示有效位数;

24、s表示小数位数. create table foo_5( c1 number, c2 number(2), c3 number(5,2), c4 number(3,-2), c5 number(2,3);number:默认有效位38,即可存整数, 又可以存小数.位数可以动态分配.insert into foo_5 (c1) values (100);insert into foo_5 (c1) values (100.005);number(2):整数定义,有效位为2.存储0-99值.insert into foo_5 (c2) values (10);/允许insert into foo_5

25、 (c2) values (100);/错误insert into foo_5 (c2) values (12.83);/采用四舍五入取整13insert into foo_5 (c2) values (12.43);/采用四舍五入取整12number(5,2):浮点数定义,2位小数;整数5-2=3个.insert into foo_5 (c3) values (100.1356);/允许100.14insert into foo_5 (c3) values (1000.1346);/错误insert into foo_5 (c3) values (999.9956);/错误number(3,

26、-2):整数定义,整数位3-(-2)=5位. 小数点左边2位按四舍五入清0;insert into foo_5 (c4) values (12345);/12300insert into foo_5 (c4) values (12354);/12400insert into foo_5 (c4) values (123.56789);/100insert into foo_5 (c4) values (12.356789);/0number(2,3):小于1的数,小数位是3位.insert into foo_5 (c5) values (0.02345);/0.023insert into f

27、oo_5 (c5) values (0.02354);/0.024insert into foo_5 (c5) values (0.12345);/错误=NUMBER使用规则=NUMBER(p,s)-NUMBER(p):整数定义,整数位是p位-NUMBER(p,s) 如果整数位超出p-s,会报错 如果小数位超出s,会对s之后内容四舍五入处理-p>0时 s>0时,会对数值小数点右边保留s位, 后面的进行四舍五入 s=0时,采用四舍五入取整 s<0时,会对数值小数点左边s位四舍五入清0,小数点右边可以忽略不计-p<s时 该数值是一个小于1的数.小数位是s位. 小数点右侧s-

28、p位为0.(否则报错) number(2,3)- 0.0xx number(2,4)- 0.00xx*提示:重点掌握住整数和浮点是定义. number(11);number(11,2) 2)数值函数(记住2个) - round():四舍五入 /round(n)一个参数是取整 select round(1.234) from dual;/四舍五入取整1 select round(1.678) from dual;/四舍五入取整2 /round(n,s)四舍五入保留s位小数 select round(1.234,2) from dual;/1.23 select round(1.678,2) fr

29、om dual;/1.68 -trunc():截取 /trunc(n)一个参数取整,将小数截掉 select trunc(1.678) from dual;/截掉,结果1 /trunc(n,s)保留s位小数,将多余的截掉 select trunc(1.678,2) from dual;/1.67 -mod():求余数 /取5除以2的余数 select mod(5,2) from dual;/结果1 select mod(5,3) from dual;/结果2 -ceil(n):向上取整(取大于或等于n的整数) /对5/2=2.5结果向上取整 select ceil(5/2) from dual

30、;/3 /取大于或等于-2.5的整数 select ceil(-2.5) from dual;/-2-floor(n):向下取整(取小于或等于n的整数) /对5/2=2.5结果向下取整 select floor(5/2) from dual;/2 /对3.0向下取整 select floor(3.0) from dual;/3 /取小于或等于-2.5的整数 select floor(-2.5) from dual;/-3 =create table foo_6( name varchar2(20), salary number(11,2);insert into foo_6 (name,sal

31、ary) values ('Scott',4000.58);insert into foo_6 (name,salary) values ('tiGGER',3000.25);1)查看名字和工资,工资只看整数位 select name,trunc(salary) from foo_6; select name,floor(salary) from foo_6;2)查询名字是scott的信息 select name,salary from foo_6 where lower(name)='scott' select name,salary from

32、 foo_6 where upper(name)='SCOTT'liangjq1.日期操作 *1)日期类型 date:存储年月日小时分钟秒 timestamp:存储年月日小时分钟秒毫秒时区 上述类型定义时不需要指定大小. *2)日期关键字 sysdate : 获取Oracle服务器系统当前时间 systimestamp : 获取Oracle服务器系统当前时间 create table goo_1( c1 date, c2 timestamp); /查询Oracle服务器系统时间 select sysdate from dual; /将Oracle系统时间插入 insert in

33、to goo_1 (c1,c2) values (sysdate,systimestamp); *3)转换函数 默认情况下,Oracle date格式是'DD-MON-RR',即'日-月-年'.只有输入此格式字符串Oracle才能转换成date插入数据表. insert into goo_1 (c1,c2) values ('08-DEC-08',systimestamp); to_date(s,format):将符合format格式的s字符串 转成date to_char(date,format):将date值按format转换成 字符串 to

34、_timetamp(s,format):将s字符串转成 timestamp类型 format格式元素为yyyy-mm-dd hh:mi:ss /将字符串转成日期插入 insert into goo_1 (c1) values (to_date('2008-08-08','yyyy-mm-dd'); /将date值转成字符串 select to_char(c1,'yyyy-mm-dd') from goo_1; /提取date值的月份信息 select to_char(c1,'mm') from goo_1; /将date值转成年月

35、日小时(24小时制)分钟秒字符串 select to_char(c1,'yyyy-mm-dd hh24:mi:ss') from goo_1;/插入一个timestamp值insert into goo_1 (c2) values (to_timestamp('2013-12-1 15:10:20', 'yyyy-mm-dd hh24:mi:ss'); 4)其他函数 2013-12-6->2013-12-31 -last_day(d):返回d日期中月份的最后一天 的日期select last_day(sysdate) from dual;

36、select last_day(to_date('2008-02-05','yyyy-mm-dd') from dual; -add_months(d,n):对d日期值的月份加n. n值可以为负数. /对月份加1,结果2008-03-05select add_months( to_date('2008-02-05','yyyy-mm-dd'),1) from dual; =案例= create table goo_2( name varchar2(20), birth date); insert into goo_2 values

37、('scott', to_date('1990-08-08','yyyy-mm-dd'); insert into goo_2 values('tiger', to_date('1995-12-05','yyyy-mm-dd'); insert into goo_2 values('larry', to_date('1950-01-08','yyyy-mm-dd'); insert into goo_2 values('bob', to

38、_date('1945-12-08','yyyy-mm-dd'); /获取当前月过生日的员工 select * from goo_2 where to_char(birth,'mm')=to_char(sysdate,'mm'); /获取下个月过生日的员工./如果加-1表示上个月过生日的员工 select * from goo_2 where to_char(birth,'mm')= to_char(add_months(sysdate,1),'mm'); 提示:对月份进行加减,需要对年进行加减可以

39、add_months(d,12)加1年;add_months(d,-12)减1年.需要对日进行加减,使用date+1或date-1操作.-months_between(d1,d2):计算d1和d2差多少个月 计算d1-d2=月份差 select months_between( to_date('2013-8-20','yyyy-mm-dd'), to_date('2013-5-5','yyyy-mm-dd') ) from dual; /查询员工名字和年龄 select name, floor(months_between(sys

40、date,birth)/12) from goo_2;-next_day(d,星期几):从d日期开计算下一个 星期几的时间日期. select next_day(sysdate,1) from dual;/1周日,2周一,3周二,.7周六 select next_day(sysdate,'tue') from dual; /下周二日期 select next_day(sysdate,'fri') from dual; /下周五日期-least(n1,n2):返回两个数值中小的那一个 select least(2,5) from dual;/2 select le

41、ast(sysdate, to_date('2008-8-8','yyyy-mm-dd') from dual;/返回2008-8-8日期-greatest(n1,n2):返回两个数值中大的那一个 select greatest(2,5) from dual;/5 select greatest(sysdate, to_date('2008-8-8','yyyy-mm-dd') from dual;/返回sysdate-round(d):对日期的小时分钟秒部分进行 舍入操作(12小时前的舍弃; 12小时后的日期加1) select

42、 round(to_date('2013-12-6 11:59:59', 'yyyy-mm-dd hh24:mi:ss') from dual;/返回2013-12-6 00:00:00 select round(to_date('2013-12-6 12:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual;/返回2013-12-7 00:00:00-trunc(d):对日期的小时分钟秒部分截取掉.-extract函数 extract(year from 日期值)/返回日期值的年 extract(

43、month from 日期值)/返回日期值的月 extract(day from 日期值)/返回日期值的日select extract(year from sysdate) from dual;select extract(month from sysdate) from dual;/获取小时分钟秒部分,/必须是timestamp类型值才可以用extract(hour from timestamp类型值)/返回小时extract(minute from timestamp类型值)/返回分钟extract(second from timestamp类型值)/返回秒select extract(h

44、our from systimestamp) from dual;select systimestamp from dual;2.空值操作(null) 各个字段都可以放null,意思是没有值. create table goo_3( id number(3), name varchar2(20); *1)null的写入 /插入空值 insert into goo_3 (id,name) values(1,null); /不指定 insert into goo_3 (id) values (2); /更新成空值 insert into goo_3 (id,name) values (3,'

45、;scott'); update goo_3 set name=null where id=3; *2)null条件查询select * from goo_3 where name=null;/错误应用select * from goo_3 where name is null;/正确select * from goo_3 where name is not null;/正确 3)空值函数 -nvl(字段,值1):当字段值为null时返回值1; 字段值不为null返回字段值; /查询id和name列值,如果name为null /显示'无名氏' select id,nvl

46、(name,'无名氏') from goo_3; -nvl2(字段,值1,值2):当字段值不为null返回值1; 当字段值为null返回值2; /name有值返回'有名字'为null返回'无名氏' select id,nvl2(name,'有名字','无名氏') from goo_3; *4)非空约数 不允许某列出现null的写入. create table goo_4( id number(3) not null, name varchar2(20) not null ); insert into goo_4 (

47、id,name) values(1,null);/错误 insert into goo_4 (id) values (2);/错误-面试题-drop table xxx;/删除表结构和数据,释放空间delete from xxx;/删除表数据,结构还在.可以回滚truncate table xxx;/删除表数据,结构还在,/效率高,数据不能回滚-3.DDL操作 可以创建,删除和修改表结构信息. 1)添加一列字段 alter table 表名 add 字段名 类型; -案例- create table goo_5( id number(3) not null, name varchar2(20)

48、 not null ); /添加salary工资列number(9,2),不允许为null 2)修改字段类型和大小,是否允许为空 alter table 表名 modify (字段 类型); /将salary大小改成number(11,2),允许为空alter table goo_5 modify (salary number(11,2) null); 提示:如果修改类型;或将存储空间大改成小的;修改成非空;可能会产生错误,原因是可表中现有数据冲突导致改表结构失败. 3)删除一列字段 alter table 表名 drop column 字段名; /删除salary列 alter table

49、goo_5 drop column salary; 4)修改表名字 rename 表名 to 新表名; 提示:表名最大长度30,一个中文占2个的话, 最大用15个中文字符.避免名称与关键字冲突. 不要以数字和_开始. create table _a(id number(2);/命名错误 *5)字段设置default默认值 在insert操作时,不为该字段指定值时, 采用默认值写入.显式指定值时,指定 什么写入什么,null也可以写入. create table goo_6( id number(3), name varchar2(20) not null, salary number(11,2

50、) default 1000, login_time date default sysdate ); /salary和login_time的default值有效 insert into goo_6 (id,name) values (1,'scott'); /显式指定,salary的default无效,写入1500 insert into goo_6 (id,name,salary) values (2,'tiger',1500); /显式指定,salary的default无效,写入null insert into goo_6 (id,name,salary)

51、values (2,'tiger',null); 6)创建表 create table 表名( 字段名 类型, . 字段名 类型) 上面是创建一个新表及其结构.如果需要从现有表复制一个表出来,可以采用下面语法: create table 表名 as select 语句; /基于goo_6复制一个新表goo_7 create table goo_7 as select id,name,salary from goo_6; /基于goo_6复制表结构,没有数据 create table goo_8 as select id,name,salary from goo_6 where

52、name is null; *7)主键约束 a.主键特点 -非空,不允许为null -唯一,不允许重复 -企业中一个表肯定会定义一个主键 -主键其实就是字段(可以由一到多个字段 承担主键职责) -主键用于定位要操作的某一条记录 (利用主键值查询,最多返回一条) -主键值一般不参与业务操作,不允许更新 /oracle自动分配一个存储主键名称 create table goo_9( id number(11) primary key, name varchar2(20), idcardno varchar2(18), age number(3) ; 或 指定主键存储名称goo9_pk create table goo_9( id number(11) , name varchar2(20), idcardno varchar2(18), age number(3), c