oracle笔记.doc_第1页
oracle笔记.doc_第2页
oracle笔记.doc_第3页
oracle笔记.doc_第4页
oracle笔记.doc_第5页
已阅读5页,还剩40页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

第二阶段第一部分数据库知识:Oracle:-! -暂时退出exit-退出show user-显示用户conn briup-切换用户(briup:用户名)su - oracleoraclesqlplus /as sysdbastartupgrant create views to briup; conn briup/briupRandomaccessFile流可以随机读取文件中的任意位置1、为什么?(1)方便的保存或者是读取数据,最好的场所是数据库(2)Oracle是目前使用最广且最好用的数据库2、学习什么?CRUD(create,retrieval,update,delete)(1)数据库中的对象(table, .)(2)表对象中的数据3、关系型数据库理论(1)1970 Dr.E.F.Codd 在IBM工作报告中提出(2)最早拿出关系型数据库产品的公司是Oracle(3)关系型数据库理论包含的内容:a、数据库是由一系列的对象组成的 (如table,index,view,sequence等等)表是由行和列组成,行和列的交叉处只能有唯一的值b、通过一系列的操作(SQL)作用于这些对象c、为了保证数据的一致性以及完整性,所存放的数据要遵循一定的规则(约束)4、数据库中的约束(1)主键约束(PK):取值不能为空,并且不能重复(唯一)。(2)外键约束(FK):取值要么为空,要么为相关联表的对应列的列值-idnamegender age address001am 23 shang002bf 24 003cf 25 beijing-telstu_idnum1001num2002num3003(3)非空约束(not null):对应的列一定要有值(4)唯一约束(unique):对应的列取值不能重复(5)自定义约束(check):取值只能是自定义的值5、SQL、SQLPlus、and PLSQL 的区别:(1)SQL:用来操作数据库的一种语言(第四代语言)由ANSI(美国国家标准委员会)制定并维护的a、1986年诞生 SQL 1.0b、1992年 SQL 2.0c、1999年 SQL 3.0(2)SQLPlus:Oracle公司提供的访问数据库服务器的客户端软件,它也是一个简单的报表编辑器,能够对访问返回的数据库格式进行控制.维护了一个buffer,缓存了最后一次执行的SQL命令.(3)PL/SQL:Oracle公司在标准SQL的基础上增加了一些过程性的控制语句(如变量,对象,控制语句),只能用于Oracle数据库.6、SQL命令的分类(1)数据获取语言:select(2)数据操纵语言(DML):insert(插入)、update(更新)、delete(删除)(3)数据定义语言:(DDL)create、drop(删除)、alter(改)、rename、truncate(永久删除)(4)事物控制语言:commit、rollback、savepoint(5)数据控制语言:(DCL)grant(授权)、revoke(回收权限)/DDL和DCL都伴随有事物的结束 telnet 20user:briuppassword:briup-sqlplususer:briuppassword:briup-sqlplus briuppassword:briup-sqlplus briup/briupselect table_name from tabs;exit第一章 - Selecting Rows1、select 语法SELECT DISTINCT *,Column alias,. FROMtable;SELECT:关键字column:列名alias:表示可有可无,列的别名:表示所有的列,但是不能跟列名混合使用FROM:关键字,表示从哪里查询table:表的名字2、如何指定列的别名只需要在列的后面加上空格,在加上别名如:select first_name name from s_emp;select first_name first name from s_emp;SQL select first_name name from s_emp;SQL select first_name first name from s_emp;结果:NAMEfirst name-CarmenCarmenLaDorisLaDorisMidoriMidoriMarkMarkAudryAudryMollyMollyRobertaRobertaBenBenAntoinetteAntoinetteMartaMartaColinColin有且只有2个地方使用双引号,上是其一.3、如何将多个列的内容连接在一起?只需要在列与列之间加上|即可.如:select first_name | last_name from s_emp;结果:SQL select first_name|last_name from s_emp;FIRST_NAME|LAST_NAME-CarmenVelasquezLaDorisNgaoMidoriNagayamaMarkQuick-To-SeeAudryRopeburnMollyUrguhartRobertaMenchuBenBiriAntoinetteCatchpoleMartaHavelColinMagee4、去掉重复的记录DISTINCT:(1)出现的位置:a、紧跟在select关键字后面b、组函数中(2)后面跟多个列:表示多个列联合唯一SQL select name from s_dept;NAME-AdministrationFinanceOperationsOperationsOperationsOperationsOperationsSalesSalesSalesSalesSQL select distinct name from s_dept;NAME-AdministrationFinanceOperationsSalesalter session set nls_language=american5、如何处理空值空值(null):没有分配值就是null,在一个表达式中只要有一个列为空,整个表达式就为空.salary+salary*commission_pct/100通过空值置换函数(NVL)来处理空值,该函数有两个参数,第一个参数可能为空的列,第二个参数是要替换成的值.两个参数的类型一定要一致.salary+salary*NVL(commission_pct,0)/1006、如何查看表结构desccribe table_name7、常用的数据类型(1)数字类型(number)idnumber 表示能够存放任意类型的数字idnumber(5)number(5,0)idnumber(5,2)如:2009.3452009.35(精度,刻度)idnumber(6,2)2009.3452009.35(存放)idnumber(3,1)20092010a、首先判断刻度:正数小数点虚拟向右移动,负数小数点虚拟向左移动b、移动完毕进行四舍五入,如果是整数部分舍去的话要补0c、计算有效数字的位数:从左边第一个不是零的数字算起到小数点虚拟移动到的位置为止d、判断精度与有效数字的大小(精度有效位数)(2)字符串定长:char如:char(5)aaacharchar(1)变长:varchar varchar(5)aaavarchar 是错误的写法 varchar2(5)只能在Oracle中使用,等价于varchar(5)(3)日期date:7个时间分量世纪、年、月、日、时、分、秒8、SQL*PLUS 常用命令(1)APPEND text : 追加文本(2)CHANGE / old/ new : 修改文本(3)CLEAR BUFFER : 清空buffer(4)DEL :删除文本(5)INPUT text : 新增加一行(6)LIST: 查看buffer中的内容(7)n text : 整行替换set pause on 分屏显示(8)SAVE filename : 将buff中的内容保存到文件夹中(9)GET filename :将文件夹中的内容加载到buff中(10)START filename : 将文件夹中的内容加载到buff中并执行(11) filename : 等价于start filename(12)EDIT filename :编辑文件中的内容(13)SPOOL filename : 录制屏幕(14)EXIT : 退出SQL*PLUS9、SQL*PLUS 控制数据输出格式(1)column : 表示对哪个列进行输出格式控制(2)format : 设置显示的宽度(3)heading: 指定列显示的标题(4)justify: 设置对齐方式 clear column - 删除所有列的格式第二章 - Limiting Selected rows (限制)1、order by 子句(1)作用:排序(2)位置:select 语句的最后(3)分成两种:升序和降序asc :升序-order by salary ascdesc:降序-order by salary desc(4)order by 子句可以跟的内容a、列名order by salaryb、列的别名order by salc、列出现的位置order by 3d、表达式order by salary*12(5)order by 子句跟多个列先跟据第一个列排序,如果第一个列大小相等,那么再按第二个列排序,以此类推。(6)null 的大小:最大2、where 子句(1)作用:筛选结果集(2)出现的位置:紧跟在 from 子句的后面(3)SQL 中的比较操作符:a、between . and . ; 区间salary between 1000 and 2000 如:SQL select id, first_name,salary 2 from s_emp 3 where salary between 1000 and 2000 4 order by 3 asc;b、in : 表示在指定的集合中id in (1,3,5,7)c、LIKE : 模糊查询% : 通配0到多个字符_ : 通配1个字符如: last_name like M% 找:_abc类型 - last_name like /_% escape /- 在oracle中任意一个字符都可以作为转义字符d、is null : 判断是否为空如:last_name is null3、优先级(1)所有的比较操作符(2)and(3)orwhere id = 10 where 10 = id第三章 - Single Row Functions (单行函数)1、作用于字符串:LOWER : 变成小写UPPER : 变成大写INITCAP : 首字母大写,其它字母小写CONCAT: 连接字符串SUBSTR: 截取字符串LENGTH: 求字符串的长度NVL : 空值置换函数2、作用于数字:NVL:空值置换函数ROUND:四舍五入TRUNC:直接舍去MOD:取余数3、作用于日期:months_between : 表示两个日期相差多少个月add_months : 给一个日期加上n个月ADD_MONTHS (10-NOV-09,5)next_day : 离给定日期最近的下一个星期几NEXT_DAY(10-NOV-09,friday)last_day : 给定日期所在月的最后一天round : 过一半就进一,否则就舍去(统一认为一半为15天)如:select round(to_date(25-may-95),month) from dual;结果:ROUND(TO_-01-JUN-95trunc : 直接舍去如:select trunc(to_date(25-sep-95),month) from dual;结果:TRUNC(TO_-01-SEP-954、不同数据类型之间转换:to_char :变成字符串to_number :变成数字 to_date :变成日期数字 字符串 注:日期和数字之间是不能互相转换的日期 字符串YYYY :4位数字表示年MM :2位数字表示月DD :2位数字表示一个月的第几天YEAR :用英文全称表示年MONTH :月的英文的全拼ddsp : 用英文全称表示一个月的第几天SQL select to_char(sysdate,ddsp) from dual;TO_CHAR(SYSD-tenddspth :用英文全称表示一个月的第几天(序数词)SQL select to_char(sysdate,ddspth) from dual;TO_CHAR(SYSDAT-tenthDAY :星期的英文全称DY :星期几的英文全称取前3个字母d :一个星期的第几天ddd :一年的第几天hh : 2位数字表示小时mi :分钟ss : 秒FM :去掉前导零 如:SQL select to_char(to_date(09-NOV-09),dd) from dual;TO-09SQL select to_char(to_date(09-NOV-09),fmdd) from dual;TO-9显示当前日期 : select to_char(sysdate,yyyy-mm-dd hh:mi:ss) from dual;结果 :TO_CHAR(SYSDATE,YY-2009-11-10 10:45:545、“千年虫”问题的解决方法: YY 机制 : 给定的年限跟当前日期处于同一个世纪RR 机制 : 结合给定的年与当前日期联合计算oracle 默认采用的是RR机制前后50年区间 :20091950 - 205020702000 - 2100问题:20091110 11:41:25显示:422511 11102009select to_char(to_date(2009-11-10 11:41:25,yyyy-mm-dd hh:mi:ss),mi-ss-hh mm/dd/yyyy) result from dual;结果:SQL select to_char(to_date(2009-11-10 11:41:25,yyyy-mm-dd hh:mi:ss), 2 mi-ss-hh mm/dd/yyyy) result from dual;RESULT-41-25-11 11/10/2009第四章select first_name,name from s_emp,s_dept;(员工表、部门表)1、笛卡尔积表现:一张表的所有行跟另外一张表的所有行匹配的结果集原因:没有有效的连接条件2、等连接select e.first_name, from s_emp e,s_dept dwhere e.dept_id=d.id;- 有效连接条件3、不等连接select ename,sal,gradefrom emp,salgradewhere sal between losal and hisal;(最低工资和最高工资)4、外连接(1)查询所有的员工以及对应的部门信息SQL select , 2 from t_emp e,t_dept d 3 where e.dept_id=d.id(+);-左外连接(+在=右边)(2)查询所有的部门以及对应的员工信息SQL select , 2 from t_emp e,t_dept d 3 where e.dept_id(+)=d.id;-右外连接(+在=左边)(3)查询所有的部门以及对应所有的员工信息SQL (select , 2 from t_emp e,t_dept d 3 where e.dept_id=d.id(+)) union 4 (select , 5 from t_emp e,t_dept d 6 where e.dept_id(+)=d.id);-union(幷集)、union all(幷集-不去除重复)、minus(做差)、intersect(交集)-5、自连接idnameparent_id1d12d213d324d43eg:-所有的领导select m.id,from s_emp m,s_emp w,where m.id=w.manager_id;-所有有领导的人select w.id,from s_emp m,s_emp w,where m.id=w.manager_id;-第五章1、组函数(1)只能作用于数字的:avg(),sum().- 求平均,求和.(2)用于数字、日期、字符串max(),min(),count().注:字符串比较大小是按照字母的accode码来比较count(column) : 求表中的记录数count(*) : 如果为空也会统计在内如:SQL select avg(salary),max(salary),min(salary) from s_emp; SQL select count(first_name) from s_emp; SQL select count(*) first_name from s_emp;-注:所有的组函数都不对空值进行处理- 问题:求公司各部门的员工的平均工资?select dept_id,avg(salary) from s_emp group by dept_id;2、group by 子句根据子句后面的列进行分组,相同的分在一组,然后再进行组函数的计算.(1)位置:from 子句或者是 where 子句后面select dept_id,avg(salary) from s_emp group by dept_id;(2)在select语句中如果使用了组函数(select子句,其它子句),那么在select子句 中没有使用组函数的列一定要出现在group by 子句中(3)如果在group by 子句后面跟多个列,表示多个列要联合唯一(多个列的值同时相同才分成一组)问题:求公司各部门的员工的平均工资,并且把平均工资大于800的部门以及平均工资查询出来?3、having 子句(1)作用:类似于where子句,都是用来筛选结果集,但是having子句可以使用组函数,where子句不可以.(2)位置:group by 子句的后面或者是前面SQL select dept_id,avg(salary) 2 from s_emp 3 group by dept_id 4 having avg(salary)800;问题:求公司各部门工资大于1000的员工的平均工资,并且把平均工资大于800的部门以及平均工资查询出来?SQL select dept_id,avg(salary) 2 from s_emp 3 group by dept_id 4 having avg(salary)800 and salary 1000;问题:求公司各部门工资大于1000的员工的平均工资,并且把平均工资大于800的部门以及平均工资查询出来,按照平均工资从大到小的审讯排列?SQL select dept_id,avg(salary) 2 from s_emp where salary 1000 3 group by dept_id 4 having avg(salary)800 5 order by 2 desc;4、如果在一个select语句中同时出现了group by 子句,where 子句,order by 子句,having 子句,以及组函数,它们执行的先后顺序?(1)where 子句(2)group by 子句(3)计算组函数(4)having 子句(5)order by 子句 问题:求工资大于公司平均工资的员工的名字以及工资?select first_name,salaryfrom s_empgroup by first_name,salaryhaving salary avg(salary);- 没有结果 - 应使用子查询第六章1、子查询子查询是完整的查询语句,可以使用select语句中的任何子句.子查询的外围的语句可以是select、create table、insert 语句(1)查询公司的平均工资select avg(salary) from s_emp(2)大于平均工资的员工select first_name,salary from s_empwhere salary (select avg(salary) from s_emp);2、子查询出现的位置(1)where 子句(2)having 子句(3)from 子句3、子查询的分类按照子查询返回结果的数量的不同,可以使用不同的操作符(1)一条记录: = (2)补充:rownum (伪列)SQL select rownum,id,last_name from s_emp;能够使用的操作符: select first_name,salary from s_emp where id select first_name,salary from s_emp where first_name =&name;SQL select first_name,salary from s_emp where first_name =zs;where first_name =Carmen2、accept给变量綁定值通过undefine var_name 解除綁定的值3、变量可以替换的内容变量可以替换语句中的任何部分,甚至是整个SQL第八章 - 数据建模1、数据库中的约束(1)主键约束:唯一、非空代理主键:没有任何的业务意义,不需要更新.(推荐)自然主键:具有实际的业务意义,有时需要更新(2)外键约束:取值可以为空,或者是相关联表中对应列的值(3)非空约束:要求列必须有值(4)唯一的约束:要求列的值必须不能重复(5)check:取值只能是固定的几个值联合主键:主键是由多个列组成的,多个列联合唯一,单列非空.联合外键:外键是由多个列组成的.联合唯一:要求多个列不能同时相同2、试读 E-R 图(1) #* : 主键约束 * : 非空约束 o : 可选(没有约束) (#)* : 唯一非空约束(2)关系:伞状符号:a、表示多对一关系b、要多增加一个外键列 : 表示的是一定要有值- : 表示可以为空| : 表示外键同时要做联合主键ordercustomer-idprojectcus_ididname1java11zs2oracle13、范式(1)第一范式:行跟列的交叉处只能有唯一的值(2)第二范式:非主键列的值取决于主键列的值(3)第三范式:非主键列不能依赖于其它非主键列-idnamepricequantitycost1java2051002xml1020200cost列 不满足第三范式,故错误第九章 - Creating Tables1、创建表create table schema.table(column datatype default expr /default expr- 默认值column_constraint, /- 约束.table_constraint);schema. : 在oracle中schema跟user一致DEFAULT : 默认值,当不给该列插入值时就会使用该值如:SQL create table cyq_tbl ( id number(5), name varchar2(20);2、命名规则:(1)必须以字母开头(2)长度不超过30个字符(3)只能包含AZ,az,09,_,$和#(4)同一shema下不能有同名的对象(5)不能使用oracle中的关键字如:create table cyq_tbl2( id number(5) primary key, name varchar2(20) constraint cyq_tbl2_name_un unique);和:create table cyq_tbl3( 2 id number(5), 3 name varchar2(20), 4 primary key(id), 5 constraint cyq_tbl3_un unique(name);3、创建表的同时加上约束:(1)表级别(2)列级别只能在表级别加:联合主键,联合外键,联合唯一只能在列级别加:非空约束(not null)如:SQL create table cyq_tbl4(id number(5) not null);和:SQL create table shihm1( 2 id number(5) primary key, 3 name varchar2(20), 4 shihm2_id number(5) references shihm2(id)-列级别加/references-关联 5 );和:SQL create table shihm1( 2 id number(5), 3 shihm2_id number(5), 4 constraint shihm1_id_fk foreign key(shihm2_id) references shihm2(id) 5 ); -表级别加 /references-关联create table shihm1(id number primary key,name varchar2(20),age number);create table shihm2(shihm1_id number,name varchar2(20),);alter table shihm2add foreign key(shihm1_id) references shihm1(id);和:SQL create table cyq_tbl8( 2 id number, 3 name varchar2(30), 4 tbl7_id number(5), 5 tbl7_name varchar2(20), 6 foreign key(tbl7_id,tbl7_name) references cyq_tbl7(id,name) 7 ); -联合外键和:SQL create table cyq_tbl7( 2 id number(5), 3 name varchar2(20), 4 primary key(id,name) 5 );4、通过子查询创建表CREATE TABLE tableculumn(,column.)AS subquery;通过子查询创建表的同时可以拷贝数据,如果不想拷贝数据只需要加上一个不成立的条件即可.创建表的同时只能拷贝非空约束,其它约束不能拷贝.SQL create table cyq_dept4( 2 userid,username) 3 as select id,name from s_dept where id1000; - 不合理条件SQL create table cyq_2( 2 id number(5) primary key, 3 name varchar(20), 4 cyq_1_id number(5) references 5 cyq_1(id) on delete cascade);- on delete cascade:级联删除,删除一个关联的对象,另一个也被删除了.第十章 - Oracle Data Dictionary1、按照权限的不同分成以下几类:(1)以 user_ 开头:用户自己所创建的对象信息如:user_tables、user_views、user_indexes、user_objects例:SQL select table_name 2 from user_tables 3 where lower(table_name) like cyq%;和:SQL select distinct object_type from user_objects; / distinct - 去除重复记录(2)以 all_ 开头:用户自己所创建的对象以及授权给该用户的对象信息如:all_tables、all_views、all_indexes(3)以 dba_ 开头:所有用户所创建的对象的信息如:dba_tables dba_views(4)以 v$ 开头:记录数据库本身以及动态生成的信息如:v$version : 记录数据库的版本2、常见的数据字典表(1)user_tables(2)user_constraints(3)user_cons_columns(4)dictionary: 数据字典表的字典3、约束的类型C : check、not nullR : foreign keyP : primary keyU : unique- 唯一的问题:查询给定表上所创建的约束的名称、类型,以及对应的列名? select con.table_name tableName,con.constraint_name conName,con.constraint_type conType,col.column_name colNamefrom user_constraints con,user_cons_columns col where con.table_name=col.table_name and lower(con.table_name)=&name;第十一章 - Manipulating Data (处理数据)1、插入数据(1)一次插入一条记录insert into table_name(column_name,.)values (value,.);如:SQL insert into cyq_tbl2(id,name) values(1,briup);(2)一次插入多条记录insert into table_name subquery;如:insert into cyq_tbl select id,first_name from s_emp;2、更新数据update table_name set column=values,column=.where colummn=.如: update cyq_tbl set name=briup where id=25;commit; - 提交更新数据3、删除数据delete from table_name where column=.;如:SQL delete from cyq_tbl where id=25;update table_name set column=null;- 删列的值4、事物将多个操作组合在一起,多个操作要么同时成功,要么同时失败.使用事物的目的是保证数据的完整性.四个特征:(1)原子性:多个操作要么同时成功,要么同时失败(2)一致性:事物操作前后数据要保持一致.如:在转帐之前两个帐户的总和是1000,转帐后两个帐户的总和还是1000.(3)隔离性:多个并发执行的事物之间相互是不可见(4)持久性:事物执行完毕要保证数据持久的保存到硬盘上5、事物边界的划分(1)开始边界:a、通过SQL*plus连接到数据库服务器b、一个事物的结束意味着另外一个事物的开始(2)结束边界:a、提交事物(commit)i)所有的事物信息都写入到了数据库ii)之前的所有操作都丢失了iii)所有的用户都可以访问该数据iv)作用于数据上的锁释放了iiv)所有的回滚点都擦除了b、回滚事物(rollback)i)之前的所有操作都丢失了ii)所有的用户都可以访问该数据iii)作用于数据上的锁释放了iv)所有的回滚点都擦除

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论