Oracle数据库的基本操作

 

 

 

 

SQL基本语句

操作对象	操作方式
	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

1.1 数据定义

1.1.1模式的定义和删除

//定义模式 create schema <模式名> authorization<用户名>;
//如果 模式名不写的话,那么隐含为用户名; create schema test authorization zhag create table tab( col int , lo char(2) );
//在create schema 的过程中还可以定义创建表,视图,索引的子句; 
//删除模式 drop schema <模式名> <cascade|restrict>;
//若是选择了cascade的话(级联)就是把该模式下所有数据库对象全部删除;若是restrict的话,如果该模式下有下属的数据库对象,那么就会拒绝该删除语句的执行

1.1.2基本表的定义，删除和修改

1.1.2.1定义了基本表

直接创建：CREATE TABLE <表名> （ <列名> <数据类型> [列级完整性约束], <列名> <数据类型> [列级完整性约束], .... [表级完整性约束] ）

从其他表创建：create table 表名 as select语句

1.1.2.2数据类型

数据类型	含义
char（n)	长度为n的定长的字符串
varchar(n)	长度为n的变长字符串
int	长整数（4字节）
float(n)	精度至少为n位数字
Boolean	布尔值
date	日期，包含年月日，格式为YYYY-MM-DD
time	时间，包含时分秒，格式为HH-MM-SS

1.1.2.3修改基本表

1.1.2.3.1列修改

(1.1)添加列

 alter table table_name add ( column1_name column2_datatype column3_constraint );

(1.2)改列

alter table table_name modify column_name datatype; 
alter table table_name modify ( column1_name column1_datatype, column2_name column2_datatype, column3_name column3_datatype, column4_name column4_datatype );

 (1.3)删除列

alter table table_name drop column col_name1; -- drop ONE column 
alter table table_name drop (col_name1, col_name2); -- drop MANY columns

(1.4)设置未用列

alter table table_name set unused column column_name; 
alter table table_name set unused ( col_name1,col_name2 );

1.1.2.3.4其他修改

修改表名

alter table table_name rename to new_table_name;

修改列名

alter table table_name rename column old_column_name TO new_column_name;

1.1.2.4删除基本表

删除表

DROP TABLE table_name[restrict|cascade];

如果是restrict的话则不能被其他表的约束所引用，cascade全删了

1.1.2.5约束

• 主键约束（primary key）
• 外键约束（foreign key）
• 唯一性约束（unique）
• 非空约束（not null）
• 检查约束（check）：CHECK约束指在表的列中增加额外的限制条件。

1.1.3索引的建立和删除

create [unique]index index_name on table_name(column_name [<asc/desc>])
 drop index index_name;index_name;

2.1数据查询

2.1.1单表查询（只涉及一个表的查询）

2.1.1.1选择若干列

select column_name from table_name;

2.1.1.2选择表中的若干元组

(1)消除取值重复的行：

select distinct sno from sc;

（2）查询满足条件的元组

常用的查询条件

查询条件	谓词
比较	,=,>=,不等于，
确定范围	between and,not between and
确定集合	int, not in
字符匹配	like, not like
空值	is null,is not null
多重条件	and,or,no

比较大小

 select distinct sno from sc where sage<20;

确定范围

select sname ,sdept,sage from student where sage between 20 and 30;

确定集合

 select sname ,ssex from student where sdept in('cs','ma');

字符匹配 [not] like '<匹配串>'[escape '<换码字符>']（当我们的匹配串里面含有通配符的时候才会用到换码字符） %：表示任意长度的字符串 _:表示任意单个字符

例如： 查询DB_Design课程的课程号和学分

select cno,ccredit from course where cname like'DB\_Design' escape'\';

涉及空值的查询

select sno,cno from sc where grade is null;

多重条件的查询

select sname,ssex from student where sdept='cs'and age<20

2.1.1.3order by子句

作用:按照一个或者多个属性的升序或者降序排列,,默认为升序

select sno,grade from sc where con='3' order by grade desc;

2.1.1.4聚集函数(不能作为条件判断式)

count(*) 统计元组的个数

查询学生的总人数

:select cout(*) from student;

count() 求总数

求选修了课程的人数:

select count(distinct sno) from sc;

avg() 求平均数

查询选修了1课程的人的平均成绩:

select avg(grade) from sc where cno='1';

sum() 求和

查询学生20133选修课程的总学分数:

select sum(credit) from sc,course where sno='20133' and sc.cno=course.cno;

max() 求最大值

查询选修1课程学生的最高分数:

select max(grade) from sc where cno='1';

min() 求最小值

查询选修1课程学生的最高分数:

select min(grade) from sc where cno='1';

2.1.1.5group by子句

例如:查询各个课程号及相应的选课人数;

select cno ,cout(sno) from sc group by cno;

再例如:查询选修了三门以上课程的学生学号:

select sno from sc group by sno having count(*)>3;

2.1.2连接查询

(1)等值和非等值连接查询

查询选修2号课程且成绩在90分以上的学生的姓名和学号

 select student.sno,sname from stundet,sc where student.sno=sc.sno and /* 连接谓词*/ sc.con='2'and sc.grade>90;/*其他限定条件*/

(2)自身连接

例如:查询每一门课的间接先行课

select first.cno,second.cpno from course first,course second where first.cpno=second.cno;

(3)外连接

左外连接:列出左边关系中所有的元组,右外连接:同理;

 select student.sno,sname,ssex,sage,sdept,cno,grade from student left outer join sc on(student.sno=sc.sno);

2.1.3嵌套查询

(1)带有in谓词的子查询

例如:查询与"刘晨"在同一个系学习的学生

select sno,sname,sdept from studnet where sdept in( select sdept from student where sname='刘晨'; );

(2)带有比较运算符的子查询

例如:查询每个学生超过他自己选修课程平均分成绩的课程号

select sno,cno from sc x where grade >=(select avg(grade) from sc y where y.cno=x.cno);

(3)带有any或all谓词的子查询

在Oracle中，ANY和ALL关键字多用于WHERE子句中作为限制条件的情况下；ALL和ANY不能单独使用，需要配合单行比较操作符 >、>=、

例如:查询非计算机系中比计算机科学系任意一个学生年龄都小的学生姓名和学号

select sname,sage from student where sage<any( select sage from student where sdept='cs' ) and sdept<>'cs';

(4)带有exists谓词的子查询

例如:查询所有选修了1号课程的学生姓名

 select sname from student where exists ( select * from sc where sno=student.sno and cno='1' );

2.1.4集合查询(列数必须相同)

主要包括:union并,intersect交,except差

例如:查询计算机科学系的学生及年龄不大于19岁的学生(系统会自动去除重复的元组,若要保留重复的元组,就需要写成union all)

select * from student where sdept='cs' union select * from student where sage<=19;

2.1.5基于派生表的查询

概念:其实就是将一个查询结果单做一个暂时存储数据的表

例如:查询每个学生超过他自己选修课程平均成绩的课程号

select sno ,cno from sc,(select sno,avg(grade) from sc group by sno) as avg_sc(avg_sno,avg_grade) where sc.sno=avg_sc.avg_sno and sc.grade>=avg_sc.avg_grade;

3.1数据更新

3.1.1插入数据

(1)插入元组

insert into <table_name>(column,column...) values(1,2,3...);

(2)插入子查询结果

insert into table_name(column,column...) 子查询;

3.1.2修改数据

(1)修改某一个元组的值

update student set age=22 where sno='3434234';

(2)修改多个元组的值

update student set sage=sage+1;

(3)带子查询的修改语句

update sc set grade=0 where sno in ( select sno from student where sdept='cs' );

3.1.3删除数据

(1)删除某一个元组的值

delete from table_name where case;

(2)删除多个元组的值

delete from sc;

（3）带子查询的删除语句

删除计算机系所有学生的选课记录

delete from sc where sno in { select sno from student where sdept='cs' };

4.1空值的处理

空值的判断:如 where sname is null;可以查找到漏填的信息;

5.1视图

5.1.1定义视图

(1)创建视图

create view view_name(column,column);

as 子查询

[with check option];------可以保证数据插入时满足这个条件,而不是任何一个数据都可以插入进去

(2)删除视图

drop view view_name;

5.1.2查询视图

和表的操作差不多(相当于一个虚表)

5.1.2更新视图

一般不更新,毕竟是个虚表,所以最好加上一个with check option

(内容不是很全,主要针对入门选手,若有错误,欢迎大家评论!)