Oracle学习笔记 (PDF版)

SummarizedbyLeonLiu1.使用SQLPlus三种方式:1).cmd:输入sqlplus,输入用户名密码2).用Oracle的SQLPlus客户端3).网页:解锁Oracle账户:alteruserxxxxxaccountunlock;3.显示表结构:descxxxx;4.字段类型:NUMBER;NUMBER(…);整数位数NUMBER(…,….);前面是整数位数,后面是小数位数VARCHAR2(…);字符型5.Oracle的特殊语句:1).选出表名(相当于是mysql中的showtables):2).select语句执行顺序:where—groupby—having—orderby3).修改管理员权限(先登录超级管理员账户,然后授权给scott这个账户创建表和创建视图的权限):4).创建用户,首先登陆超级管理员,然后进行创建(用户名密码都为liu,用户的默认表空间为users,配额10M):5).导出用户信息,下面的执行完后全部回车默认进行导出就可以了6).导入用户信息到新创建的用户(后面都是默认回车,当又出现用户名的时候填scott):SummarizedbyLeonLiu7).进行备份:8).回滚:rollback;撤销上一步数据操作9).删除表:10).有关视图1.视图不要轻易建立,当表被修改的时候视图也要跟着修改,不利于维护2.当需要选出表中某些字段时,使用视图,保护私有数据6.SELECT语句1).对某行进行算术运算并将其选出:selectxxx*10fromxxxx;2).算术运算:select10*5fromdual;(dual:虚表,里面只有一条记录,用来计算各种表达式,若是其他表,则有几条记录就会选出多少条记录)3).选出系统时间:selectsysdatefromdual;4).别名中有特殊字符的时就加双引号(用来保持原本的格式):selectxxx*10“xxxxxx”fromxxx;5).字符串连接符:||用于连接两个字段名,相当于java的连接两个String的+,如果单引号的字段中有单引号,则用两个单引号表示一个单引号.6).消掉重复的行:selectdistinctxxxfromxxx;消除组合重复的行:selectdistinctxxx,xxxfromxxx;7).where选择条件:1.不等于:SummarizedbyLeonLiu2.某字符,这里比较字符是挨个比较ASCII的大小(同java一样)3.取两个之间的行:betweenxxxandxxx其中包括800和1500,相当于:4.空值问题:wherexxxisnull(不是xxx=null)wherexxxisnotnull5.用in或notin来选择有确定条件的数据wherexxxin(xxx,xxx,xxx)或SummarizedbyLeonLiu7.匹配字符,使用通配符%wherexxxlike‘%xxx%’若要匹配的字符中有%,则使用转义字符,在%前加一个\:自定义转义字符(把自定义的符号作为转义符号):8.按照多个字段进行排序:orderbyxxx,xxx(先按照deptno的升序进行排列,如果deptno有相同的值,则在相同的值中再按照ename的降序进行排列)8).函数的使用:1.lower(xxxx)将选出的字段的值都转化成小写2.截取子串:substr(xxxx,x,x)(把ename中取出的值从第2个值开始截取3个字符)3.将ASCII码转换成字符:chr(xxx)SummarizedbyLeonLiu将字符转换成ASCII码:ASCII(‘xxx’)4.四舍五入round(xxxx,x)和5.转换格式:to_char(xxxx,’xxx,xxx.xxx’)转化成本地货币:to_char(xxxx,’Lxxx,xxx.xxx’)转换日期格式:24小时制:SummarizedbyLeonLiu比较日期使用的转换:先把要比较的转换成date类型,再和date类型的字段进行比较to_date(‘xxxx-xx-xxxx:xx:xx’,’YYYY-MM-DDHH24:MI:SS’)将字符串类型转换成数字类型:to_number(‘xxxx’,’xxxx’)将有空值的字段里的空值转换为非空,其他数据不变:nvl(xxx,xx)6.组函数:求字段最大值:max(xxx)最小值:min(xxx)平均值:avg(xxx)求和:sum(xxx)数据的数目(非空的值的数目):count(xxx)求不重复的数目:组函数嵌套(最多两层,因为第一层可能产生多行,第二层就只有一行了):9).分组:groupbyselect列表里的字段不是出现在组函数中就是要出现在groupby中:SummarizedbyLeonLiuwhere语句是对单条语句进行过滤,不能过滤分组之后的新的数据where要先于groupby执行使用having对分组进行限制10).子查询:谁的工资最高:谁的工资是大于平均工资的:按照部门分组之后,每个部门工资最高的(要使用表连接):查找出每个部门的平均薪水的等级(有一个salgrade表,需要联表查询):或SummarizedbyLeonLiu11).连接:1.自连接(把一张表通过起别名的方式当成两张表来用):2.交叉连接,即笛卡尔积(crossjoin):连接条件写在on里面,最好和where语句区别开,即不在where语句中写连接条件第二个字母不是A的员工的一些信息(三个表进行连接):自连接(推荐写法):SummarizedbyLeonLiu左外连接(把左边表的所有数据都拿出来,比如下面的KING):右外连接:全外连接(把左右两张表的多余数据都取出来):12).分页(使用ROWNUM):要取大于某行数要用一个子查询:SummarizedbyLeonLiu查找工资最高的五个人的一些信息:查找工资是第5到第10的人的信息(三层嵌套,最里面一层排序,第二层把rownum加到已排好序的数据上面,再在最外面一层进行条件控制查询):13).练习:1.那些人是经理(即有下属):2.平均薪水最高的部门的部门编号:3.平均薪水最高的部门的名称:SummarizedbyLeonLiu或4.平均薪水等级最低的部门的名称:5.求部门经理人中平均薪水最低的部门的部门名称:或使用视图(view):先创建一个视图再使用这个视图中进行查询:6.工资比普通员工的最高工资还要高的经理人的名称:SummarizedbyLeonLiu7.比较效率:理论上第一条效率高8.SummarizedbyLeonLiu7.DML语句(数据操纵语言):1).insert语句:1.插入完整数据:2.插入不完整的具体某些字段的数据:3.直接插入从表中选出的数据2).update语句:update[Table_name]set[column_name]=[xxxx]where[xxxx]=xxx;3).delete语句:deletefrom[Table_name]wherexxx=xxx;8.Trasactioni(事务):开始:起源于一条DML语句,当这条DML语句开始执行时,事务开始启动结束:1).commit;(提交完成,以上的事务结束,无法再rollback)2).遇到DDL语句(create语句)或DCL语句(grant授权语句)时提交并结束事务SummarizedbyLeonLiu3).正常断开连接,比如exit指令,会提交并结束事务4).不正常断开连接,比如断电,会rollback9.constraint(约束):1).notnull(非空):约束名为stu_name_nn2).unique(唯一):插入两个空值不认为违反唯一约束:几个字段的组合唯一:要加一个表级约束,加在最后:表示email和name的组合是唯一的3).primarykey(主键):可以唯一标识一条记录,相当于是”非空”和”唯一”的组合或或加在多个字段上SummarizedbyLeonLiu4).foreignkey(外键):模拟外键约束:先创建一个class表:references(参考某个字段):在这里参考class表的id字段,如果插入的class这个字段在class表中没有找到则出错.或者加一个标记约束:把class这个字段添加外键约束,并参考class表中的id字段.class字段是参考字段,class表中的id是被参考的字段被参考的字段必须是主键,字段如果被参考则无法删除相应记录5).check:10.alter语句(修改表结构)1).删除字段:2).添加字段:3).修改字段:修改后的精度必须能容纳原有的数据4).删除约束:5).添加约束:SummarizedbyLeonLiu11.用户字典(用户的表):1).显示用户表结构:2).显示用户表的表名:3).查询用户视图:4).查询用户约束:5).查看数据字典表的结构:6).查看数据字典表:SummarizedbyLeonLiu7).查找数据字典中通配的表名:8).查找索引;12.index(索引):1).创建索引:2).删除索引:3).索引不要轻易建立,提高了查找效率,但是降低修改效率13.sequence(序列):1).创建序列:2).测试:线程同步,不会出现重复的情况SummarizedbyLeonLiu3).用做主键:14.数据库设计三范式:原则:尽量避免冗余1).第一范式:任何表都要有主键,列不可分,也不能重复2).第二范式:如果表有两个或两个以上的字段,则,其他字段不能依赖于部分主键即不能存在部分依赖如:学生姓名只依赖于学号,学号决定学生姓名,学号是部分主键,则有部分依赖,会出现冗余,要分割成几张表3).第三范式:不能存在传递依赖,除主键外其他字段必须直接依赖于主键如:emp和dept如果在一个表中,则dname就是传递到deptno再被传递到主键这种情况就要拆成两个表,不然会存在大量冗余15.PL/SQL1).熟悉格式:1.打开输出功能(默认为off),然后进行输出语句的编写2.先声明变量(变量最好以v_开头,varchar2类型,大小20字节),然后进行赋值,用:=将’myname’赋给v_name3.抛出异常:whenotherthen:如果前面没有捕获异常的话,则到这一定会捕获到异常的SummarizedbyLeonLiu4.table(类似于java的数组):type(声明一种类型)type_table_emp_empno(命名规则:是一种type,table类型,具体是emp中的empno类型)….5.Record(类似于java的类):先声明一个record类型,在里面定义一些变量上面的这段程序扩展性差,如果增加一个字段,则这个record也要更改,那么则:定义一个rowtype类型,声明表的rowtype类型,在调用的时候直接用变量访问表的字段SummarizedbyLeonLiu6.PL/SQL中的select语句:这里的select语句返回的记录必须有且只有一条,并且必须和into一起用7.PL/SQL中的update语句:--是注释8.PL/SQL中的create语句:9.流程控制语句:a.if语句:else后面没有thenelsif中间没有e最后endifSummarizedbyLeonLiu练习:b.循环:a).相当于dowhile语句:SummarizedbyLeonLiub).相当于while循环:c).相当于for循环:正反两种循环:SummarizedbyLeonLiu10.错误处理:a.too_many_rows:b.no_data_found:等等很多系统异常c.常用做法(建立错误日志):先创建一张表:再创建序列:出错后先