ORACLE课堂讲义712.docx
《ORACLE课堂讲义712.docx》由会员分享，可在线阅读，更多相关《ORACLE课堂讲义712.docx（33页珍藏版）》请在冰豆网上搜索。
ORACLE课堂讲义712
ORACLE课堂讲义
（课堂讲义有三部分共十八讲，点我ID看其他的，授课老师：
贾峰）
第七讲
集合（SET）
>union/unionall
>intersect
>minus
--集合运算要求两个select语句是同构的，即列的个数和数据类型必须一致
SQL>selectempno,enamefromempwheredeptno=10;
SQL>selectdeptno,dnamefromdeptwheredeptno<40;
--这两个列是同构，数据类型和数量都相同叫同构。
A={1,2,3,4,5,6}
B={2,4,6,8}
AunionB={1,2,3,4,5,6,8}
AunionallB={1,2,2,3,4,4,5,6,6,8}
AintersectB={2,4,6}
AminusB={1,3,5}
【例】查看部门10和20的职位，分别用union/unionall/intersect/minus试一下。
SQL>selectjobfromempwheredeptno=10;
SQL>selectjobfromempwheredeptno=20;
--教师演示union
SQL>selectjobfromempwheredeptno=10
union
selectjobfromempwheredeptno=20;
数据操作语句（DML）
--之前我们用很长时间徘徊在select，现在我们学DML。
首先建立新表：
SQL>createtablestudent_ning（
idnumberprimarykey,
namevarchar2（20）,
emailchar（40）,
registtimedatedefaultsysdate）;
--可以查询：
SQL>descstudent_ning;
1、insert插入语句
--新增记录
SQL>INSERTINTOstudent_ning（id,name,email,registtime）
VALUES（1,'peter','peter@',sysdate）;
--如果新增全部列,可以省略列名称，必须提供全部的列数据，数据的顺序必须按照表结构，如果有数据没提供，用null表示。
SQL>INSERTINTOstudent_ning
VALUES（2,'chris','chris@',sysdate）;
--如果增加的不是全部字段,必须提供字段列表。
--建议采用写列名的方式，即使是插入全部数据，也建议把列名写全。
SQL>INSERTINTOstudent_ning（id,name,registtime）
VALUES（3,'bono',to_date（'2012/11/01','yyyy/mm/dd'））;
--to_date函数是oracle独有的，在oracle数据库中插入日期数据，一定要用to_date处理。
不要采用默认格式，比如：
'01-NOV-11'。
--一次增加多条记录，使用子查询代替values。
在emp表中查询部门10的员工编号，员工姓名，那么id、name与empno和ename相对应。
SQL>insertintostudent_ning（id,name）
selectempno,enamefromemp
wheredeptno=10;
--作此操作是为了开发时候保护数据，做备份。
那么我现在不想创建一个表再复制数据，而直接复制一个表作为备份，怎么办？
下面再介绍另一种向表里增加记录的方法。
2、复制表
2.1、复制全表
语法格式：
createtable表名
as
查询语句;
2.2、只复制结构，不复制数据
【例】复制表：
只复制结构，不复制数据
SQL>createtablesalgrade_yyy
as
select*fromsalgrade
where1<>1;--红字部分是结果是false。
2.3、复制一部分数据（给查询语句加条件）
--如果复制表时的查询语句中有表达式或者函数（包括单行函数和组函数），必须指定新表中的列名。
指定方式：
给列设置别名；或者在新表中设置列名。
【例】复制表：
复制一部分数据（通过设置别名的方式）。
SQL>createtableemp_yyy
as
selectempno,ename,sal*12year_sal--year_sal为新表的列名
fromemp
wheredeptno=10;
【例】复制表：
复制一部分数据（指定新表中的列名）。
SQL>createtableemp_count（did,emp_num）--新表中的列名
as
selectdeptno,count（*）
fromemp
groupbydeptno;
2.4、创建一个空表，并同时向表中插入多条记录
【例】创建一个同emp表结构相同的空表，将部门号为10的员工信息放入该表。
--情景描述：
如果有一张表emp的数据量为一百万条，此时需要建立1张测试表只放入少量测试数据（如100条）,执行步骤如下所示：
第1步创建一个空表
SQL>createtableemp_bak
as
select*fromemp
where1=0;
第2步插入少量测试数据
SQL>insertintoemp_bak
（select*fromempwheredeptno=10）;
【例】把表中的数据换为部门20和30的员工记录
SQL>deletefromemp_bak;--删除全表的数据（from可以省略）
SQL>insertintoemp_bak
（select*fromempwheredeptnoin（20,30））;
3、update
语法结构：
update表名set列名=新的列值,列名=新的列值.….
where条件;
--复制emp表信息到表emp_ning;
SQL>droptableemp_ning;
SQL>createtableemp_ning
as
select*fromemp;
【例】修改员工‘SMITH’的信息，把他的工资改为$1000
SQL>updateemp_ningsetsal=1000
whereempno=7369;
--大家想想我把whereempno=7369去掉，会出现什么结果？
【例】如果想改多个字段，把员工‘SMITH’的工资改为1500，部门改为20，职位改为‘salesman’。
SQL>updateemp_ningsetsal=1500,
deptno=20,job='salesman'
whereempno=7369;
【例】如果把部门编号换成99，此部门在dept表里不存在，看看会有什么结果。
SQL>updateempsetdeptno=99whereename='SCOTT';
修改的值必须处与dept列表当中，否则也会报错，改为正确的形式：
SQL>updateempsetdeptno=40whereename='SCOTT';
4、delete
语法结构：
delete[from]表名where条件;
--droptable会删除表结构和数据；truncate删除表数据，保留表结构。
drop和truncate都不可以回退。
delete仅删除数据，可以回退。
--举个例子：
仓库里有一个货架，delete是相当于拿走货架上的商品暂时保存起来，需要时再放上去；truncate是相当于拿走货架上的商品并把它们销毁掉；而drop是把房子拆了。
【例】在emp_ning表中删除部门10里面的三行记录
SQL>deleteemp_ningwheredeptno=10;
【例】我们查询select*fromemp；字段deptno中存在部门10，执行语句：
deletefromdeptwheredeptno=10；会报“以找到子记录”错误。
为什么？
DML:
insert/update/delete
第八讲
事务语句（Transaction）
1）事务是一组DML操作的逻辑单元，用来保证数据的一致性。
2）在一个事务内，组成事务的这组DML操作，或者一起成功提交，或者一起被撤销。
3）事务控制语言TCL（TransactionControlLanguage）
commit事务提交：
将所有的数据改动提交
rollback：
事务回滚、回退到事务之初，数据的状态和事务开始之前完全一致。
savepoint：
事务保存点（较不常用）。
1、事务的开始和终止（事务边界）
1）事务开始
事务开始于上一个事务的终止或者第一条DML语句
2）事务终止
事务终止于commit/rollback显式操作（即控制台输入commit/rollback）。
如果连接关闭，事务（Transaction）将隐式提交。
DDL操作（比如create），事务将隐式提交。
如果出现异常，事务将隐式回滚。
【例】：
Transaction演示
步骤1：
开启一个会话1,创建表并插入1条数据（注意：
不提交）
（注意练习时使用同一个用户在两个窗口中登录,比如scott）
步骤2
开启第2个会话2,在会话1进行commit之前,会话2只能查看表结构，查看不到数据
步骤3
会话1中进行commit操作后;会话2中就可以查看数据了。
步骤4
会话1进行update操作（没有commit）,会话2看到的仍然是原先的数据
步骤5
会话1提交（commit）后,会话2看到被改变的结果
步骤6
会话1进行update操作（没有commit），会话2进行delete操作时被挂起，因为试图操作相同的数据。
步骤7
会话1提交（commit）,会话2结束阻塞状态,开始执行
步骤8
会话1更新后进行回滚操作（rollback）
结论
1）提交、回滚前事务内部的数据改变只有在自己的会话中能够看到。
2）提交、回滚前事务会对操作的数据加锁，不允许其它事务操作。
3）如果提交（commit）后，数据的改变被确认，则
●所有的会话都能看到被改变的结果;
●数据上的锁被释放;
●保存数据的临时空间被释放
4）如果回滚（rollback）,则
●数据的改变被取消；
●数据上的锁被释放；
●临时空间被释放。
2、Savepoint
--设置保存点,可以回滚（rollback）到指定的保存点。
【例】：
执行如下代码，理解savpoint含义
SQL>insertintotemp_ningvalues
（1）;
SQL>savepointA;
SQL>insertintotemp_ningvalues
（2）;
SQL>savepointB;
SQL>insertintotemp_ningvalues（3）;
SQL>savepointC;
SQL>insertintotemp_ningvalues（4）;
SQL>rollbacktoB;
SQL>rollbacktoC;--检查结果，看是否能得到结果。
SQL>rollbacktoA;
SQL>commit;
第九讲
数据定义语言（DDL操作）
数据定义语言DDL：
create/drop/alter/truncate
1、create（建表）**
建表的两种方式：
1）第1种，自定义表的列和数据类型
createtable表名（
列名列的数据类型,....
）;
2）第2种，由一个现存的表复制新表
createtable表名
as
查询语句;
2、drop（删除结构和全部的表数据）**
（1）drop语法结构：
droptable表名;
（2）和表相关的数据字典*
在Oracle数据库中，数据库表可以分为两大类：
用户表和数据字典表。
用户表指被用户创建和维护的表，数据库大多是用户表，主要存放自己数据。
数据字典表是被Oracle数据库创建和维护的表，也叫做系统表，存放数据库自身信息，包括描述数据库和它的所有对象的信息，以及一些分析数据库的视图，也是DBA管理和维护数据库的重要途径。
1）user_tables：
数据库用户自己方案下的数据表字段table_name（表名）
2）user_objects：
数据库用户自己方案下的所有对象
字段created（对象的创建时间）字段object_name（对象名）
--查询所有表名
SQL>selecttable_namefromuser_tables;
--查询所有对象名，包括表名、视图名、索引名等。
SQL>selectobject_namefromuser_objects;
【例】：
找出12年9月10日后创建的表，删除过时的表。
--注意日期格式，下例是英文环境下，如果是中文环境，需要写成：
'10-9月-12'这种形式。
也可以使用to_date函数。
SQL>selecta.table_name,b.created
fromuser_tablesajoinuser_objectsb
ona.table_name=b.object_name
whereb.created>'10-SEP-12';
【例】：
计算五个月之前创建的数据表的个数
SQL>selectcount（a.table_name）
fromuser_tablesajoinuser_objectsb
ona.table_name=b.object_name
whereb.created3、truncate（截取，截断）*
1）truncate保留表结构，删除表中所有数据
2）truncate操作不需提交（commit），没有回退（rollback）的机会
3）语法结构：
truncatetable表名;
4）truncate和delete的区别：
truncate在功能上等同于：
delete+commit。
delete操作将删除数据存储到临时空间中，不直接删除，可以回退。
truncate操作直接删除，不占用临时空间，不能回退。
SQL>truncatetabletemp_ning;
4、alter（修改结构）*
（1）add关键字
【例】：
增加列（只能增加在最后一列）
SQL>createtabletemp_ning（idnumber（4））;
SQL>altertabletemp_ningadd（namechar（10））;
SQL>altertabletemp_ningadd（passwordchar（4））;
SQL>desctemp_ning
（2）rename关键字
【例】：
修改列名password为pwd
SQL>altertabletemp_ningrenamecolumnpasswordtopwd;
SQL>desctemp_ning
（3）modify关键字
【例】：
修改列的数据类型pwdchar（8）
SQL>altertabletemp_ningmodify（pwdchar（8））;
（4）dropcolumn
【例】：
删除列
SQL>altertabletemp_ningdropcolumnpwd;
5、执行数据库脚本（script）文件*.sql**
【例】Windows操作系统：
步骤1：
在指定路径新建一个.sql为后缀名的文件
步骤2：
编辑
步骤3：
在服务器端运行执行.sql脚本（脚本文件和sqlplus在同一台机器上）
SQL>@e:
\aaa\test.sql
数据控制语言DCL
【例】权限演示
步骤1：
假设数据库中有2个用户zhang和scott
步骤2：
假设现在的用户是scott，Scott将查看emp表的权限赋予zhang,zhang只能看不能改
SQL>grantselectonemptozhang;
步骤3：
scott用户将select权限回收；zhang账户再次访问时出错。
SQL>revokeselectonempfromzhang;
第十讲
表上的约束（CONSTRAINTS）保证数据的完整性一致性
约束条件:
主键：
primarykey，PK=unique+notnull，是约束条件的一种，
用于标识唯一+非空。
外键：
foreignkey，FK。
Major:
专业。
唯一：
unique。
注册一个论坛，e-mail是唯一的。
非空：
notnull。
注册邮箱时候，用户名、密码是非空。
检查：
check。
学生的年龄只能是正数。
性别设定只能男/女，M:
male/F:
female，还可以设置为空。
约束条件constraint
一、主键约束
创建约束条件，如果创建的约束条件没有命名，由系统自动命名的SYS_C…。
1、创建主键约束有2种方式:
1）建表时创建，可以建立在列级或表级。
2）在建表后创建，建立在表级。
列级约束是跟在列定义后边的约束定义；表级约束就是定义完列后再定义的约束。
【例】以student_ning为例，创建主键约束，不命名，由系统命名，建立在列级。
SQL>createtablestudent_ning（
idnumberprimarykey,
namevarchar2（20）,
emailchar（40）,
registtimedatedefaultsysdate）;
数据字典：
只能查看不能修改。
就像我们学校的信息，如所在的系和班级，老师的信息都在学校档案室的目录，这个目录就是数据字典。
SQL>selectconstraint_name,constraint_type
fromuser_constraints
wheretable_name='STUDENT_NING';
大家发现我的表名用大写'STUDENT_NING'，这里不能用小写，数据库数据大小写敏感，表名、列名在数据字典中都要大写。
【例】创建主键约束，自己命名，建立在表级，约束条件命名为:
stuning1_id_pk。
SQL>CREATETABLEstudent_ning1（
idNUMBER,
nameVARCHAR2（20）,
CONSTRAINTstuning1_id_pkPRIMARYKEY（id））;
主键约束条件命名为stuning1_id_pk，表名_列名_主键，再用数据字典查询下约束名称为：
【例】建表后创建主键约束，约束条件名称自定义（stuning2_id_pk），最好有意义。
SQL>createtablestudent_ning2（
idnumber,
namevarchar2（20））;
SQL>altertablestudent_ning2
addconstraintstuning2_id_pkprimarykey（id）;
--查看主键约束条件
SQL>selectconstraint_name,constraint_type
fromuser_constraints
wheretable_name='STUDENT_NING2';
--了解主键约束条件的作用。
SQL>insertintostudent_ning（id,name）
values（1234,'peter'）;
--重复执行上一条语句，试图插入重复id值1234，将返回ORA-00001违反唯一约束。
--不提供主键值，将返回“无法将NULL插入”错误，表明“id不能为空”
SQL>insertintostudent_ning（name）
values（'peter'）;
二、创建非空约束
--创建非空约束：
只能建立在列级。
【例】删除表后再创建表，对name字段创建非空约束，不命名，由系统命名。
SQL>droptablestudent_ning;
SQL>createtablestudent_ning（
idnumberprimarykey,
namevarchar2（20）notnull）;
--查看主键约束条件
SQL>selectconstraint_name,constraint_type
fromuser_constraints
wheretable_name='STUDENT_NING';
--要求此列上必须有值。
错误的sql：
无法将NULL插入
SQL>insertintostudent_ning（id）values
（1）;
三、创建唯一约束
--唯一约束可以定义在表级、列级。
【例】删除表后再创建表，对email和nickname字段进行唯一约束，email的唯一约束建立在列级；nickname（昵称）的唯一约束建立在表级。
SQL>droptablestudent_ning;
SQL>createtablestudent_ning（
idnumberprimarykey,
nicknamevarchar2（20）,
emailchar（30）unique,
constraintstuning_nickname_ukunique（nickname））;
--查看主键约束条件：
--如果想删除约束条件，比如删除刚加的唯一约束stuning_nickname_uk：
SQL>altertablestudent_ningdropconstraintstuning_nickname_uk;
--唯一约束允许为空，但不允许重复。
SQL>insertintostudent_ning（id,nickname,email）
values（1,null,'ning@'）;
SQL>insertintostudent_ning（id,nickname,email）
values（2,'ning','ning@'）;
四、check约束
升级会员 