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约束