oracle基本知识.docx

oracle基本知识.docx

《oracle基本知识.docx》由会员分享，可在线阅读，更多相关《oracle基本知识.docx（12页珍藏版）》请在冰豆网上搜索。

oracle基本知识

--创建表空间

createtablespacetest1datafile'C:

\APP\ADMINISTRATOR\ORADATA\ORCL\test1_02.DBF'size100M;

createtablespacetest2datafile'C:

\oracledata\test2_01.DBF'size100M;

--删除表空间

droptablespacetest1;

--创建用户

createuserxzf2identifiedby123456defaulttablespacetest1;--创建一个新的用户叫xzf2，密码是123456,默认表空间是test1.

--权限控制

grantconnecttoxzf2;--将角色connect赋予给用户xzf2

grantresourcetoxzf2;--将角色resource赋予给用户xzf2

--创建表

--要创建三个表：

--学生表student：

sid，sname,sage,ssex,sphone

--课程表course:

cid,cname,tname,chour

--成绩表score:

scid,sid,cid,grade

--在oracle中，所有表的全名是方案名.表名;方案名其实就是用户名，通过这种方式来描述表和用户的从属关系。

--如果用户操作的是自己的表，则方案名可以省略，如果方案名省略，也意味着操作的就是自己的表。

--基本格式

--createtable用户名.表名（属性1类型，属性2类型，...属性N类型）;

--创建学生表

createtablexzf1.student（

sidvarchar2（10）,--学号

snamevarchar2（20）,--姓名

ssexchar

（1）,--性别

sagenumber,--年龄

sphonenumber--电话号码

）;

--创建课程表

createtablecourse（

cidvarchar2（10）,--课程号

cnamevarchar2（20）,--课程名

tnamevarchar2（20）,--老师名

chournumber--课时

）;

--创建成绩表

createtablescore（

scidvarchar2（10）,--成绩号

sidvarchar2（10）,--学号

cidvarchar2（10）,--课程号

gradenumber--成绩

）;

--以上只要求了解、简单使用即可。

--注意事项：

a、了解方案名的作用；b、属性之间是用逗号分隔，最后一个属性不需要加逗号；

--约束

--在oracle中，除了超键是由数据库自己定义，其它约束都是由用户（DBA）来人为定义。

--约束是为了保证数据的实体完整性和参照完整性。

--约束有以下五种：

--主键约束：

primarykey,主键约束其实是具备唯一性和非空性的

--外键约束：

foreignkey

--非空约束：

notnull

--唯一约束：

unique相当于是候选键

--检查约束：

check

--修改表的属性

--关键字是alter

--基本格式alter类型对象名动作（add,modify,renamecolumn,dropcolumn）

--示例

createtabletest1（

c1number,

c2varchar2（10）

）;

--修改表test1的属性，添加主键-c1

altertabletest1add（primarykey（c1））;

altertabletest1add（c3char（10））;

--修改表中字段的属性

altertabletest1modify（c3number）;

--修改表，重命名列名

altertabletest1renamecolumnc3toc4;

--修改表，删除指定的列。

altertabletest1dropcolumnc4;

altertabletest1add（c3number）modify（c1char（10））;

altertablecourseadd（primarykey（cid））;

altertablecoursemodify（cnamenotnull,tnamenotnull）;

altertablescoreadd（foreignkey（sid）referencesxzf1.student（sid））;

altertablescoreadd（foreignkey（cid）referencesxzf1.course（cid）,unique（sid,cid））;

--创建学生表

createtablexzf1.student（

sidvarchar2（10）primarykey,--学号

snamevarchar2（20）notnull,--姓名

ssexchar

（1）,--性别

sagenumber,--年龄

sphonenumber,--电话号码

check（ssexin（'M','F'））

）;

--DML和DQL

--在很多书籍中，这两种语言都被归类为DML语言。

--insertinto插入，相当于新增记录

--delete删除，删除记录

--update更新，修改记录

--select查询，查询记录.

--查询指定表的所有记录。

select*fromstudent;

select*fromcourse;

select*fromscore;

--insert的语法

--insert的基本语法有以下两种。

--第一种：

insertinto表名values（值1，值2，...值N）

--value中的值的排序一定要与表中字段的排序保持一致，字符类型的值都需要使用单引号，非字符类型的值不需要使用单引号。

--示例1：

insertintostudentvalues（'s0001','张三1','M',20,17012345678）;

insertintostudentvalues（'s0002','张三2','F',21,17012345678）;

--altertablestudentadd（check（sage>18andsage<30））;

insertintostudentvalues（'s0003','张三3','F',21,17012345678）;

insertintostudentvalues（'s0004','张三4','F',22,17012345678）;

--第二种：

insertinto表名（字段1，字段2，..字段N）values（值1，值2，...值N）

--value中的值的排序一定要与表中字段的排序保持一致，字符类型的值都需要使用单引号，非字符类型的值不需要使用单引号。

--示例2：

insertintostudent（sname,sid,ssex,sage,sphone）values（'张三5','s0005','F',22,17012345678）;

insertintostudent（sname,sid,ssex）values（'张三6','s0006','M'）;

--删除

deletefromstudentwheresid='张三4';

--deletefromstudent；表示将表student中的所有数据全部删除。

--格式：

deletefrom表名where条件

select*fromtest1;

insertintotest1values（123,'aaa',123）;

insertintotest1values（124,'abc',123）;

insertintotest1values（125,'abb',123）;

insertintotest1values（126,'bbc',123）;

deletefromtest1;

deletefromtest1wherec1>124;

--truncate，属于DDL语言，也可以用来删除指定表.

truncatetabletest1;

--修改、更新数据.

--update

--基本格式：

update表名set赋值表达式[where条件]。

不加条件就是修改整列的值。

--示例1:

updatestudentsetssex='M'wheresid='s0004';

--示例2：

updatestudentsetsage=24,sphone=13812345678wheresid='s0006';

--示例3：

updatestudentsetsage=sage+1wheressex='M';

--

updatestudentsetsphone=sage;

--要求，学生表至少有十条以上，有同名的同学，要年龄有相同和不同的

--课程表至少有5条以上，课时数最好不一样，有老师担任多门科目的授课任务。

--成绩表，要求至少有一名学生没有成绩，至少有一门课程没人选择。

--向学生表插入数据。

insertintostudentvalues（'s0001','张三1','M',20,17012345678）;

insertintostudentvalues（'s0002','张三2','F',21,17012345678）;

--altertablestudentadd（check（sage>18andsage<30））;

insertintostudentvalues（'s0003','张三3','F',21,17012345678）;

insertintostudentvalues（'s0004','张三4','M',22,17012345678）;

insertintostudentvalues（'s0005','张三5','F',21,17012345678）;

insertintostudentvalues（'s0006','张三6','M',23,17012345678）;

insertintostudentvalues（'s0007','张三7','F',25,17012345678）;

insertintostudentvalues（'s0008','张三8','M',26,17012345678）;

insertintostudentvalues（'s0009','张三1','F',27,17012345678）;

insertintostudentvalues（'s0010','测试1','M',28,17012345678）;

insertintostudentvalues（'s0011','测试2','F',29,17012345678）;

insertintostudentvalues（'s0012','测试3','M',19,17012345678）;

insertintostudentvalues（'s0013','测试4','F',22,17012345678）;

insertintostudentvalues（'s0014','测试','M',25,17012345678）;

select*fromstudent;

select*fromcourse;

--课程表

insertintocoursevalues（'c0001','Oracle','肖志斐',32）;

insertintocoursevalues（'c0002','Linux','肖志斐',24）;

insertintocoursevalues（'c0003','黑盒测试用例','祁彩云',8）;

insertintocoursevalues（'c0004','JAVA','陆一一',32）;

insertintocoursevalues（'c0005','单元测试','陆小刚',4）;

insertintocoursevalues（'c0006','项目实践1','陆小刚',40）;

insertintocoursevalues（'c0007','项目实践2','许爱国',32）;

insertintocoursevalues（'c0008','自动化测试','陆一一',56）;

insertintocoursevalues（'c0009','性能测试','肖志斐',56）;

select*fromscore;

--成绩表

insertintoscorevalues（'sc001','s0001','c0001',90）;

insertintoscorevalues（'sc002','s0002','c0002',91）;

insertintoscorevalues（'sc003','s0003','c0003',92）;

insertintoscorevalues（'sc004','s0004','c0004',93）;

insertintoscorevalues（'sc005','s0005','c0005',94）;

insertintoscorevalues（'sc006','s0006','c0006',95）;

insertintoscorevalues（'sc007','s0007','c0007',96）;

insertintoscorevalues（'sc008','s0008','c0008',97）;

insertintoscorevalues（'sc009','s0009','c0001',98）;

insertintoscorevalues（'sc010','s0010','c0002',99）;

insertintoscorevalues（'sc011','s0011','c0001',90）;

insertintoscorevalues（'sc012','s0012','c0002',91）;

insertintoscorevalues（'sc013','s0013','c0003',92）;

insertintoscorevalues（'sc014','s0004','c0002',93）;

insertintoscorevalues（'sc015','s0005','c0003',94）;

insertintoscorevalues（'sc016','s0006','c0004',95）;

insertintoscorevalues（'sc017','s0001','c0007',96）;

insertintoscorevalues（'sc018','s0001','c0008',97）;

insertintoscorevalues（'sc019','s0002','c0001',98）;

insertintoscorevalues（'sc020','s0010','c0005',99）;

--等待锁。

select*fromscoreforupdate;

--查询

--关键字是select

--格式：

--select内容select是必须字段

--from来源from是必须字段

--where条件where是可选

--orderby字段orderby是排序，是可选

--groupby字段groupby是分组，是可选

--having条件是和groupby结合使用

--on条件是和连表查询结合使用。

--示例1：

--查询所有学生的信息。

select*fromstudent;

--查询所有学生的姓名，年龄和性别。

selectsname,sage,ssexfromstudent;

--查询成绩表中所有分数

selectgradefromscore;

--查询所有课程的名称

selectcnamefromcourse;

--查询所有老师的名称

selecttnamefromcourse;

--查询不重名的老师的名称；

--在oracle中，是通过关键字distinct来实现去除重复值的操作。

selectdistincttnamefromcourse;

--查询有多少学生已经选课。

selectcount（distinctsid）fromscore;

--查询所有女生的姓名和电话

selectsname,sphonefromstudentwheressex='F';

--查询姓张的同学的姓名和年龄模糊查询

selectsname,sagefromstudentwheresnamelike'张%';

--查询25岁以上的学生的所有信息

select*fromstudentwheresage>25;

--查询22到24岁之间的学生信息。

select*fromstudentwheresage>22andsage<24;

--查询某一位老师所教课程的信息。

select*fromcoursewheretname='肖志斐';

--条件常用的控制符有:

>,<,=,>=,<=,!

=,in,like,between,exists,notexists

--条件的连接符有：

and,or

--like是用来实现模糊查询，%表示通配，没有长度；_表示匹配一个字符。

selectsnamefromstudent;

selectsnamefromstudentwheresnamelike'测%';

selectsnamefromstudentwheresnamelike'测__';

--betweennum1andnum2;

select*fromstudentwheresagebetween22and25;

--查询年龄在25岁以上的女生的信息

select*fromstudentwheressex='F'andsage>25;

--查询课时在20小时以上的课程信息

select*fromcoursewherechour>20;

--查询所有及格的学生的学号

selectdistinctsidfromscorewheregrade>=60;

--查询姓陆的老师所教课程的名称

selectcnamefromcoursewheretnamelike'陆%';

--查询名字中带’三‘的学生的信息

select*fromstudentwheresnamelike'%三%';

--查询电话号码以170开头的同学的信息

select*fromstudentwheresphonelike'170%';

--查询年龄在23岁以下的学生的信息

select*fromstudentwheresage<23;

--查询电话号码以5678结尾的同学的信息

select*fromstudentwheresphonelike'%5678';

--查询电话号码以170开头，5678结尾的同学的信息

select*fromstudentwheresphonelike'170%5678';

--查询电话号码中包含数字7的所有同学的信息

select*fromstudentwheresphonelike'%7%';

select*fromstudentwheresidin（'s0001','s0002','s0003'）;

--等价于select*fromstudentwheresid='s0001'orsid='s0002'orsid='s0003';

--聚合函数

--oracle提供的一些内置的函数。

--count（）统计数量

--max（）求最大值

--min（）求最小值

--sum（）求和

--avg（）求平均值

--查询学生的数量

selectcount（*）fromstudent;

--查询男生的数量

selectcount（*）fromstudentwheressex='M';

--查询女生的数量

selectcount（*）fromstudentwheressex='F';

--查询有成绩的学生的数量

selectcount（distinctsid）fromscore;

--查询有成绩的课程的数量

selectcount（distinctcid）fromscore;

--查询年龄最大的女生信息

selectmax（sage）fromstudentwheressex='F';

select*fromstudentwheresage=29andssex='F';

--查询年龄最小的男生信息

selectmin（sage）fromstudentwheressex='M';

select*fromstudentwheressex='M'andsage=（selectmin（sage）fromstudentwheressex='M'）;

--查询所有课程的总课时

selectsum（chour）fromcourse;

--查询所有同学的平均成绩

selectavg（grade）fromscore;

--查询某位同学的平均成绩

selectgradefromscorewheresid='s0001';

selectavg（grade）fromscorewheresid='s0001';

--查询某门课程的平均成绩

selectavg（grade）fromscorewherecid='c0001';

--排序

--orderby排序字段，默认是升序排列,加desc就表示降序排列

--如果排序字段是数值类型，则是按数值大小进行排序；如果排序字段是字符类型，则按字符串从左往右排序。

select*fromstudent;

select*fromstudentorderbysid;

select*fromstudentorderbysiddesc;

select*fromscoreforupdate;

select*fro