oracle基础一

[/size]oracle的一些基本概念：
oracle 数据库包括逻辑结构和物理结构。
其中物理结构可以分为数据文件、控制文件、日志文件。
逻辑结构又分为数据库、表空间、段、区、数据块、模式。

补充：

表空间是数据库中最大的逻辑单位，一个 Oracle 数据库至少包含一个表空间，就是名为SYSTEM的系统表空间。每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联。
表空间的大小等于构成该表空间的所有数据文件大小之和。

段是构成表空间的逻辑存储结构，段由一组区组成。 按照段所存储数据的特征，将段分为四种类型，即数据段、索引段、回退段和临时段。区为段分配空间，它由连续的数据块组成。
当段中的所有空间已完全使用时，系统自动为该段分配一个新区。

区不能跨数据文件存在，只能存在于一个数据文件中。

数据块是Oracle服务器所能分配、读取或写入的最小存储单元。
Oracle服务器以数据块为单位管理数据文件的存储空间。

模式是对用户所创建的数据库对象的总称。
模式对象包括表、视图、索引、同义词、序列、过程和程序包等。
oracle的网络配置 ：
主要就是配置tnsname.ora这个文件 比如我的在e盘装的客户端，所以路径就是
E:\program\oracle10client\NETWORK\ADMIN
打开里面的配置文件可看到
LINK_PZ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.81.35.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
我们主要需要配置的为一下几项：
1 其中LINK_PZ 为连接的数据库服务器名 
2 host=(这里填写服务器ip或者计算机名)
3 SERVICE_NAME = orcl 这里填写的是数据库的名字

老师讲数据库的时候，一开始的内容都听枯燥的，其实我觉得这块的知识只要自己多动手操作就行了。
很多同学都是win 7 的系统，不少人为了跑oracle 重装了系统，感谢http://database.51cto.com/art/201106/271588.htm
这篇贴子让我在win7 上面完美跑起了oracle 。

oracle 有三个默认用户（sys,system,scott）
其中SCOTT用户是Oracle 数据库的一个示范帐户，在数据库安装时创建。
也可以自己来创建：
CREATE USER 用户名
IDENTIFIED BY 密码
DEFAULT TABLESPACE 表空间
TEMPORARY TABLESPACE TEMP;

创建用户之后还需要授予相应的权限才能连接到数据库。
授予有两种方式，一种是直接把角色授予用户，还有一种就只授予具体的一些权限。

把角色授予用户:
grant [角色名] to [用户名] ;
常用的角色有connect,resource,sequence
授予具体的一些权限：
grant [权限] on [表名] to [用户名];
alter user 可以用户更改用户口令
如：ALTER USER MARTIN IDENTIFIED BY martinpass;  将martin密码改为martinpass.
drop user 可以直接删除用户.






数据类型：字符型、数值型、日期类型、raw/long raw 、lob

字符类型：char、varchar2、varchar、long
varchar2类似于SQLServer中的varchar，Oracle中也有varchar类型，但建议使用varchar2
提问并讲解何时使用char及varchar2

数值类型：number;
可以存储整数、浮点数和实数
最高精度为 38 位
数值数据类型的声明语法：
NUMBER [( p[, s])]
P表示精度，S表示小数点的位数

日期类型：date 、timestamp
日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒
主要的日期时间类型有：
DATE - 存储日期和时间部分，精确到整个的秒
TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位

LOB：
CLOB 即 Character LOB（字符 LOB），它能够存储大量字符数据
BLOB 即 Binary LOB（二进制 LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件  、
BFILE 即 Binary File（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中
LOB 称为“大对象”数据类型，可以存储多达 4GB 的非结构化信息，例如声音剪辑和视频文件等
LOB 数据类型允许对数据进行高效、随机、分段的访问。

rowid和rownum
ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行
ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数.当使用where子句查询时特别注意

sql 操作符
算术操作符：
算术操作符用于执行数值计算
可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成
算术操作符包括加(+)、减(-)、乘(*)、除(/)
逻辑操作符：
逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。
逻辑操作符包括与(AND)、或(OR)和非(NOT)。
连接操作符：
连接操作符‘||’用于将多个字符串或数据值合并成一个字符串
比较操作符：
比较操作符用于比较两个表达式的值
比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等
集合操作符：
union :并集除去了重复的部分。
union all :并集没有除去重复的部分。
intersect :操作符只返回两个查询的公共行.
minus:操作符返回从第一个查询结果中排除第二个查询中出现的行。
SQL 操作符的优先级从高到低的顺序是：
算术操作符           --------最高优先级
连接操作符
比较操作符
NOT 逻辑操作符
AND 逻辑操作符
OR   逻辑操作符   --------最低优先级


表的锁定
锁出现在数据共享的场合，用来保证数据的一致性。当多个会话同时修改一个表时，需要对数据进行相应的锁定。
锁有“只读锁”、“排它锁”，“共享排它锁”等多种类型，而且每种类型又有“行级锁”(一次锁住一条记录)，“页级锁”(一次锁住一页，即数据库中存储记录的最小可分配单元)，“表级锁”(锁住整个表)。
在Oracle数据库中，修改数据操作时需要一个隐式的独占锁，以锁定修改的行，直到修改被提交或撤销为止。如果一个会话锁定了数据，那么第二个会话要想对数据进行修改，只能等到第一个会话对修改使用COMMIT命令进行提交或使用ROLLBACK命令进行回滚撤销后，才开始执行。因此应养成一个良好的习惯：执行修改操作后，要尽早地提交或撤销，以免影响其他会话对数据的修改。
语 句

SELECT FOR UPDATE
锁定表行，防止其他会话对行的修改
LOCK TABLE
锁定表，防止其他会话对表的修改


数据查询语言：
数据库最重要的无不是数据查询了。也就是sql语句。
SQL语言包括了所有对数据库的操作，这些操作可以分为4个部分
即数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）和事务控制语言（TCL） 。

数据定义语言包括：
create、alter、drop

事务控制的语句有：
COMMIT - 提交并结束事务处理
ROLLBACK -  撤销事务中已完成的工作
SAVEPOINT – 标记事务中可以回滚的点

数据操纵语言包括：
select ：select [字段名...] from [表名....] where ....
update：
delete：DELETE FROM 表名 WHERE 条件;
要从表中删除满足条件的记录，WHERE条件一般不能省略，如果省略就会删除表的全部数据。
insert：NSERT INTO 表名 [(字段列表)] {VALUES(表达式1, 表达式2,...)|QUERY语句}；

数据控制语句：
grant 授权
revoke 收回权限


语法太枯燥了直接上些做过的一组练习 这样也方便理解。

创建表和输入数据
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
   SNAME VARCHAR(4) NOT NULL,
   SSEX VARCHAR(2) NOT NULL,
   SBIRTHDAY DATE,
   CLASS VARCHAR(5))
go
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
   CNAME VARCHAR(10) NOT NULL,
   TNO VARCHAR(10) NOT NULL)
go
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
   CNO VARCHAR(5) NOT NULL,
   DEGREE NUMERIC(10, 1) NOT NULL)
go
CREATE TABLE TTEACHER
(TNO VARCHAR(3) NOT NULL,
   TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
   TBIRTHDAY DATE NOT NULL, PROF VARCHAR(6),
   DEPART VARCHAR(10) NOT NULL)

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,to_date('1977-09-01','yyyy-mm-dd'),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,to_date('1975-10-02','yyyy-mm-dd'),95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,to_date('1976-01-23','yyyy-mm-dd'),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,to_date('1976-02-20','yyyy-mm-dd'),95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,to_date('1975-02-10','yyyy-mm-dd'),95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,to_date('1974-06-03','yyyy-mm-dd'),95031);
GO
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
GO
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
GO
INSERT INTO TTEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'李诚','男',to_date('1958-12-02','yyyy-mm-dd'),'副教授','计算机系');
INSERT INTO TTEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'张旭','男',to_date('1969-03-12','yyyy-mm-dd'),'讲师','电子工程系');
INSERT INTO TTEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女',to_date('1972-05-05','yyyy-mm-dd'),'助教','计算机系');
INSERT INTO TTEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),'助教','电子工程系');
题目
  1、 查询Student表中的所有记录的Sname、Ssex和Class列。
  2、 查询教师所有的单位即不重复的Depart列。
  3、 查询Student表的所有记录。
  4、 查询Score表中成绩在60到80之间的所有记录。
  5、 查询Score表中成绩为85，86或88的记录。
  6、 查询Student表中“95031”班或性别为“女”的同学记录。
  7、 以Class降序查询Student表的所有记录。
  8、 以Cno升序、Degree降序查询Score表的所有记录。
  9、 查询“95031”班的学生人数。
  10、查询Score表中的最高分的学生学号和课程号。
  11、查询‘3-105’号课程的平均分。
  12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
  13、查询最低分大于70，最高分小于90的Sno列。
  14、查询所有学生的Sname、Cno和Degree列。
  15、查询所有学生的Sno、Cname和Degree列。
  16、查询所有学生的Sname、Cname和Degree列。
  17、查询“95031”班所选课程的平均分。
  18、假设使用如下命令建立了一个grade表：
    create table grade(low   number(3,0),upp   number(3),rank   char(1));
    insert into grade values(90,100,’A’);
    insert into grade values(80,89,’B’);
    insert into grade values(70,79,’C’);
    insert into grade values(60,69,’D’);
    insert into grade values(0,59,’E’);
    commit;
    现查询所有同学的Sno、Cno和rank列。
  19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
  20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
  21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
  22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
  23、查询“张旭“教师任课的学生成绩。
  24、查询选修某课程的同学人数多于5人的教师姓名。
  25、查询95033班和95031班全体学生的记录。
  26、查询存在有85分以上成绩的课程Cno.
  27、查询出“计算机系“教师所教课程的成绩表。
  28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
  29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按
    Degree从高到低次序排序。
  30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
  31、查询所有教师和同学的name、sex和birthday.
  32、查询所有“女”教师和“女”同学的name、sex和birthday.
  33、查询成绩比该课程平均成绩低的同学的成绩表。
  34、查询所有任课教师的Tname和Depart.
  35、 查询所有未讲课的教师的Tname和Depart.
  36、查询至少有2名男生的班号。
  37、查询Student表中不姓“王”的同学记录。
  38、查询Student表中每个学生的姓名和年龄。
  39、查询Student表中最大和最小的Sbirthday日期值。
  40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
  41、查询“男”教师及其所上的课程。
  42、查询最高分同学的Sno、Cno和Degree列。
  43、查询和“李军”同性别的所有同学的Sname.
  44、查询和“李军”同性别并同班的同学Sname.
  45、查询所有选修“计算机导论”课程的“男”同学的成绩表

答案
1、 select Sname,Ssex,Class from Student;
  2、 select distinct depart from teacher;
  3、 select Sno as "学号",Sname as "姓名",Ssex as "性别",Sbirthday as "出生日期",Class as "班号" from student;或select * from student;
  4、 select * from score where degree between 60 and 80;或select * from score where degree>=60 and degree<=80;
  5、 select * from score where degree in (85,86,88);
  6、 select * from student where class='95031'or Ssex='女';
  7、 select * from student order by class desc;
  8、 select * from score order by cno asc ,degree desc;或select * from score order by cno ,degree desc;
  9、 select count(*) as CNT from student where class='95031';
  10、select Sno as '学号',cno as '课程号', degree as '最高分' from score where degree=(select max(degree) from score);
  11、select avg(degree)as 课程平均分 from score where cno='3-105';
  12、select cno,avg(degree) from score where cno like'3%'group by cno having   count(*) >5;
  13、select Sno from score group by Sno having min(degree)>70 and max(degree)<90;
  14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno;或select st.Sname,sc.Cno,sc.degree from student  st,score  sc where st.Sno=sc.Sno;
  15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno;
  16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno;
  17、select y.Cno,avg(y.degree) from student x,score y where x.Sno=y.Sno and x.class='95033' group by y.cno;

  19、select x.Cno,x.Sno,x.degree from score x,score y where x.cno='3-105' and x.degree>y.degree and y.sno='109'and y.cno='3-105';
  20、select max(b.d) from (select sno,max(degree) as d from score group by sno having count(sno)>1) b;
  21、select x.cno,x.Sno,x.degree from score x,score y where x.degree>y.degree and y.sno='109'and y.cno='3-105';
    select cno,sno,degree from score   where degree >(select degree from score where sno='109' and cno='3-105')
  22、select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy')=(select to_char(sbirthday,'yyyy') from student where sno='108');
  23、select cno,sno,degree from score where cno in (select x.cno from course x,teacher y where x.tno=y.tno and y.tname='张旭');
  24、select tname from teacher where tno in(select x.tno from course x,score y where x.cno=y.cno group by x.tno having count(x.tno)>5);
  25、select * from student where class in('95033','95031');
  26、select distinct cno from score where degree in (select degree from score where degree>85);
  27、select * from score where cno in(select x.cno from course x,teacher y where y.tno=x.tno and y.depart='计算机系');
  28、select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
  29、select * from score where cno='3-105' and degree>any (select degree from score where cno='3-245') order by degree desc;
  30、select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
  31、select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student;
  32、select tname,tsex,tbirthday from teacher where tsex='女' union select sname,ssex,sbirthday from student where ssex='女';
  33、select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
  34、select tname,depart from teacher a where exists (select * from course b where a.tno=b.tno);
  35、select tname,depart from teacher a where not exists (select * from course b where a.tno=b.tno);
  36、select class from student where ssex='男' group by class having count(*)>=2;
  37、select * from student where sname not like'王_';
  38、select sname as 姓名,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')) as 年龄 from student
  39、select sname,sbirthday as 最大 from student where  sbirthday =(select min (sbirthday) from student)union select sname,sbirthday as 最小 from student where sbirthday =(select max(sbirthday) from student)
  40、select class,sname,sbirthday from student order by class desc,sbirthday;
  41、select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男';
  42、select * from score where degree=(select max(degree)from score);
  43、select sname from student where ssex=(select ssex from student where sname='李军');
  44、select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');
  45、select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course where cname='计算机导论');


对 象
名 称
作 用
TABLE
表
用于存储数据的基本结构
VIEW
视图
以不同的侧面反映表的数据，是一种逻辑上的表
INDEX
索引
加快表的查询速度
CLUSTER
聚簇
将不同表的字段并用的一种特殊结构的表集合
SEQUENCE
序列
生成数字序列，用于在插入时自动填充表的字段
SYNONYM
同义词
为简化和便于记忆，给对象起的别名
DATABASE LINK
数据库链接
为访问远程对象创建的通道
STORED PROCEDURE、FUNCTION
存储过程和函数
存储于数据库中的可调用的程序和函数
PACKAGE、PACKAGE BODY
包和包体
将存储过程、函数及变量按功能和类别进行捆绑
TRIGGER
触发器

 
数据库模式对象
1 TABLE
表用于存储数据的基本结构
2 VIEW
视图以不同的侧面反映表的数据，是一种逻辑上的表
3 INDEX
索引加快表的查询速度
CLUSTER
聚簇将不同表的字段并用的一种特殊结构的表集合
4 SEQUENCE
序列生成数字序列，用于在插入时自动填充表的字段
5 SYNONYM
同义词为简化和便于记忆，给对象起的别名
6 DATABASE LINK
数据库链接为访问远程对象创建的通道
7 STORED PROCEDURE、FUNCTION
存储过程和函数存储于数据库中的可调用的程序和函数
8 PACKAGE、PACKAGE BODY
包和包体将存储过程、函数及变量按功能和类别进行捆绑
9 TRIGGER
触发器
由DML操作或数据库事件触发的事件处理程序

表的五种约束:
1 主键(PRIMARY KEY)
主键是表的主要完整性约束条件，主键惟一地标识表的每一行。一般情况下表都要定义主键，而且一个表只能定义一个主键。主键可以包含表的一列或多列，如果包含表的多列，则需要在表级定义。主键包含了主键每一列的非空约束和主键所有列的惟一约束。主键一旦成功定义，系统将自动生成一个惟一索引，用于快速访问主键列。
主键约束的语法如下：
[CONSTRANT 约束名] PRIMARY KEY     --列级
[CONSTRANT 约束名] PRIMARY KEY(列名1,列名2,...)     --表级
2 非空(NOT NULL)
非空约束指定某列不能为空，它只能在列级定义。在默认情况下，Oracle允许列的内容为空值。比如“图书名称”列要求必须填写，可以为该列设置非空约束条件。
非空约束语法如下：
[CONSTRANT 约束名] NOT NULL     ---列级
3 惟一(UNIQUE)
惟一约束条件要求表的一列或多列的组合内容必须惟一，即不相重，可以在列级或表级定义。但如果惟一约束包含表的多列，则必须在表级定义。比如出版社表的“联系电话”不应该重复，可以为其定义惟一约束。
惟一约束的语法如下：
[CONSTRANT 约束名] UNIQUE--列级
[CONSTRANT 约束名] UNIQUE(列名1,列名2,...)--表级
4．检查(CHECK)
检查约束条件是用来定义表的一列或多列的一个约束条件，使表的每一列的内容必须满足该条件(列的内容为空除外)。在CHECK条件中，可以调用SYSDATE、USER等系统函数。一个列上可以定义多个CHECK约束条件，一个CHECK约束可以包含一列或多列。如果CHECK约束包含表的多列，则必须在表级定义。比如图书表[size=x-small]的“单价”的值必须大于零，就可以设置成CHECK约束条件。
检查约束的语法如下：
[CONSTRANT 约束名] CHECK(约束条件)  --列级，约束条件中只包含本列
[CONSTRANT 约束名] CHECK(约束条件)  --表级，约束条件中包含多列
模式对象的使用都大同小异，主要是create alter drop 操作
5．外键(FOREIGN KEY)
指定表的一列或多列的组合作为外键，外键参照指定的主键或惟一键。外键的值可以为NULL，如果不为NULL，就必须是指定主键或惟一键的值之一。外键通常用来约束两个表之间的数据关系，这两个表含有主键或惟一键的称为主表，定义外键的那张表称为子表。如果外键只包含一列则可以在列级定义；如果包含多列，则必须在表级定义。
外键的列的个数、列的数据类型和长度，应该和参照的主键或惟一键一致。

有时间在总结一下plsql编程。希望大家支持。

评论

1 楼 hailong886 2012-03-02  

oracle基本扫盲