韩顺平玩转oralce.综合部分MLDN魔乐oracle.视频学习小结.V1.0

1Oracle基础介绍1.1基本概念1.1.1实例往往是数据库里面一些相关的进程启动起来，调入到内存当中，共同起到一个协调的作用；在Oracle中，表、视图、触发器、存储过程等等均称之为“数据对象”1.1.2用户管理选择口令管理，主要观察以下几个用户：普通用户：scott/tiger普通管理员：system/manager超级管理员：sys/change_on_install1.1.3服务项管理除以下两个服务外，其他的服务“自动”设置为“手动”(以Oracle10g作说明)OracleOraDb10g_home1TNSListener:表示监听服务，如果客户端想连接到数据库，此服务必须打开，在日后的程序开发中此服务起作用OracleServiceMLDN：表示数据库的主服务，命名规则为：OracleService数据库名称。此服务必须启动，否则Oracle根本就无法运行1.2SQL简单介绍SQL(StructuredQueryLanguage，结构查询语言)是一个功能强大的数据库语言，有如下三种简单划分：DML(DataManipulationLanguage,数据操作语言)——用于检索或者修改数据DDL(DataDefinitionLanguage,数据定义语言)——用于定义数据的结构，如创建、修改或者删除数据库对象DCL(DataControlLanguage,数据控制语言)——用于定义数据库用户的权限1.3SQL常用命令1）setlinesize长度：设置每行显示的长度2）setpagesize行数:设置每页显示记录的行数3）showuser：显示当前连接的用户是哪一个4）select*fromtab：得到当前用户下一个数据库中全部表的名称5）desc表名称:查看一个表的完整结构6）ed及@指令SQLed打开一个类似文本的编辑器SQL@c:\a.txt假设a.txt文本中的内容是SELECT*FROMemp;7）连接：conn用户名/密码ASSYSDBA2简单查询语句2.1SELECT语句SELECT{DISTINCT}*|具体的列别名FROM表名{WHERE条件}{GROUPBY表达式{HAVING分组条件}}{ORDERBY排序的字段1，排序的字段2ASC|DESC}简单说明：1）DISTINCT：消除重复的内容2）没有内容表示NULL，不为空的话：ISNOTNULL3）BETWEEN最小值AND最大值4）字段IN(值1，值2，...，值n)，如果要求查询的内容不在此范围中，则用NOTIN5）使用LIKE语句：注意通配符的问题，有两种通配符：“%”：可以匹配任一长度的内容“_”：可以匹配一个长度的内容6）在操作条件中，还可以使用:===不等于符号：有两种形式：!=7）对结果进行排序：ORDERBY2.2单行函数语法：function_name(column|expression,[arag1,arg2,...])2.2.1单行函数分类共有5种：字符：专门处理字符的，比如大小写变化、求出字符的长度数值日期转换通用(NVL、DECODE)2.2.2字符函数UPPER(STRING)：转换成大写LOWER(STRING)：转换成小写INITCAP(STRING):将字母首字母大写CONCAT(STR1,STR2)：连接字符串substr(String,int,int):第二个int可以取负值，倒过来截取字符串length(String)：返回当前字符串的长度replace('hello','l','x')：替换字符串，当前返回值是hexxo2.2.3数值函数四舍五入：ROUND(number[,int])截断小数位：TRUNC(number[,int]):不会四舍五入，可以取负值取余(取模)：MOD(number,number)2.2.4日期函数MONTHS_BETWEEN(sysdate)：求出给定日期范围的月数ADD_MONTHS(sysdate,4)：在指定日期上加上指定的月数，求出之后的日期NEXT_DAY(sysdate,'星期一')：下一个的今天是哪一个日期LAST_DAY(sysdate)：求出给定日期的最后一天日期2.2.5转换函数TO_CHAR():数字9表示一位数字to_char(number,'99,999')TO_NUMBER()：转换成数字TO_DATE()：转换成日期2.2.6通用函数NVL(STR1,STR2):如果STR1的值为null，则返回STR2的值DECODE(column|expressionsearch1,result1[,search2,result2,...][default])：如果column|expression的值与searchi相比较，如果相同的话，则返回resulti，否则返回default的值3多表查询3.1左、右连接select*fromemp,deptwhereemp.deptno(+)=d.deptno;(+)左：=左边表示右连接(+)右：=右边表示左连接3.2SQL：1999语法对SQL的支持3.2.1交叉连接（CROSSJOIN）产生笛卡尔积emp,dept等价于empcrossjoindept3.2.2自然连接（NATURALJOIN）自动进行关联字段的匹配3.2.3USING子句直接关联的操作列select*fromempejoindeptdusing(deptno)wheredeptno=30;3.2.4ON子句用户自己编写连接的条件select*fromempejoindeptdon(e.deptno=d.deptno)wheredeptno=30;3.2.5左|右(外)连接LEFT|RIGHTOUTERJOIN4组函数分组统计分组：例如把男生分为一组，女生分一组4.1常用的分组函数COUNT()：求出全部的记录数MAX():求出一组中的最大值，一般用于针对数字的应用上MIN():求出最小值AVG():求出平均数SUM():求和4.2分组统计语法：GROUPBY分组条件范例：selectdeptno,count(empno)fromemp报错：ORA-00937:不是单组分组函数范例：selectdeptno,empno,count(empno)fromempgroupbydeptno报错：ORA-00979:(empno)不是GROUPBY表达式原因：1）如果程序中使用了分组函数，则有两种可以使用的情况：程序中存在了GROUPBY，并指定了分组条件，可以将分组条件一起查询出来如果不使用分组的话，则只能单独的使用分组函数2）在使用分组函数的时候，不能出现分组函数和分组条件之外的字段范例：selectdemptno,AVG(sal)fromempwhereAVG(sal)2000GROUPBYdeptno报错：此处不允许使用分组函数3）分组函数只能在分组中使用，不允许在WHERE语句之中出现，可通过HAVING分组条件指令完成相应功能范例：selectdemptno,AVG(sal)fromempGROUPBYdeptnoHAVINGAVG(sal)20004.3分组的简单原则1）只要一列上存在重复的内容才有可能考虑到分组2）分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句5子查询在一个查询的内部还包含一个查询。所有的子查询必须在“()”中编写，分为三类：单列子查询：返回的结果是一列的一个内容，出现的几率最高单行子查询：返回多个列，有可能是一条完整的记录多行子查询：返回多条记录在子查询中，存在以下三种查询的操作符号：5.1IN指定一个查询的范围5.2ANY=ANY:与IN的操作符功能完全一样ANY:比里面最小的值大ANY:比里面最大的值小5.2ALLALL:比里面最大的值大ALL:比里面最小的值要小6数据库更新操作6.1查询操作SELECT6.2更新操作INSERTUPDATEDELETE6.3表的复制执行以下语句后，会将表结构及数据完整的复制出来CREATETABLEmyempASSELECT*FROMEMP;7事务处理7.1定义所谓事务处理，就是保证数据操作的完整性，所有的操作要么同时成功，要么同时失败7.2本质1）在Oralce中对于每一个连接到数据库的窗口(sqlplus、plsqldeveloper)连接之后，实际上都会与数据库的连接建立一个session，即：每一个连接到数据库上的用户都表示创建了一个session。2）一个session对数据库所做的修改，不会立刻反映到数据库的真实数据之上，是允许回滚的，当一个session提交所有的操作之后，数据库才真正的做出修改。7.3事务处理在数据库的操作中提供了以下的两个主要命令完成事务的处理：提交事务:commit回滚事务:rollback注意：如果数据已经提交了，则肯定无法回滚7.4死锁在Oracle中关于事务的处理上也会存在一种死锁的概念(即，一种等待的状态)，换句话说，一个session如果更新了数据库中的记录，其他session事无法立刻更新的，要等待对方提交之后才允许更新。8表的管理8.1建表语法：CREATETABLE表名AS子查询说明：1）如果现在子查询写的是：SELECT*FROMemp，表示将表结构和表内容一起复制2）如果现在子查询写的是：SELECT*FROMempwhere1=2，加入了一个永远不可能成立的条件，则此时表示的是只复制表结构，但是不复制表内容8.2表的修改8.2.1增加列语法：ALTERTABLE表名称ADD(列的名称列的类型DEFAULT默认值,列的名称列的类型DEFAULT默认值,...)8.2.2修改列语法：ALTERTABLE表名称MODIFY(列的名称列的类型DEFAULT默认值)8.3为表重命名语法：RENAME旧的表名称TO新的表名称(只能在Oralce中使用)8.4截断表如果将某表中的一条数据使用DELETE语句删除了，则可以通过rollback进行回滚，如果现在假设要想清空一张表的数据，但是同时又不需要回滚，可以立刻释放资源，就需要使用截断表的语法，如下所示：TRUNCATETABLE表名称;9约束9.1约束的分类在实际中，约束只要分为以下五种约束：主键约束PRIMARYKEY：主键表示一个唯一的标识，本身不能为空|-例如：身份证编号是唯一的标识，本身不能为空唯一约束UNIQUE：在一个表中只允许建立一个主键约束，而其他列如果不希望出现重复值的话，则就可以使用唯一约束检查约束CHECK：检查一个列的内容是否合法|-例如：年龄，只能在0~150CHECKCHECK(ageBETWEEN0AND150)|-例如：性别，只能是男、女、中性CHECK(sexIN('男','女'))非空约束NOTNULL：姓名这样的字段里面的内容就不能为空外键约束FOREIGNKEY：在两张表中进行约束操作9.2修改表的约束----单表约束----ALTERTABLE表名称ADDCONSTRAINT约束名称约束类型(约束字段)----主外键约束，两张表----ALTERTABLE表名称ADDCONSTRAINT约束名称约束类型(约束字段)REFERENCE父表名(参考字段)ONDELETECASCADE9.3删除约束ALTERTABLE表名称DROPCONSTRAINT约束名称9.4综合练习CREATETABLEperson(pidvarchar2(18),namevarchar2(20)NOTNULL,agenumber(3)NOTNULL,birthdayDATE,sexvarchar2(4)DEFAULT'男',CONSTRAINTperson_pid_pkPRIMARYKEY(pid),CONSTRAINTperson_name_ukUNIQUE(name),CONSTRAINTperson_age_ckCHECK