oracle数据库学习笔记心得.docx

select* |字段表名 from 表名where 布尔表达式【条件】externalcandidate 职员相关信息contractrecruiter 猎头公司查看表里所有字段 describe描述 contractrecruiterselect cname,nperecentagecharge from contractrecruiter 不显示原来名字，显示现在名字【用AS】select cname as Recruiter Name,nperecentagecharge as Hire Fees from contractrecruiter 不显示原来名字，显示现在名字【可不用AS】select cname Recruiter Name,nperecentagecharge Hire Fees from contractrecruiter 【双引号是否写】双引号是为了区分大小写职位表 positionselect *from position缺额运算:想减select VDESCRIPTION ,nbudgetedstrength,NBUDGETEDSTRENGTH -NCURRENTSTRENGTH ,NYEAR from position select VDESCRIPTION Potion,nbudgetedstrength Budgeted Strength ,NBUDGETEDSTRENGTH -NCURRENTSTRENGTH Vacancies,NYEAR Year from position 显示非重复运行 查询来源地的人数 describe externalcandidate查看职员城市名字 select ccity from externalcandidate查看职员城市名字【名字不重复，插入一个关键字：distinct】select distinct ccity,cstate from externalcandidate运算符：两列select vfirstname,vlastname from externalcandidate字段拼接select vfirstname|vlastname from externalcandidateselect vfirstname| |vlastname from externalcandidate学校describe college查看所有学校select *from college查看只是加利福尼亚的学校select * from college where cstate=California一个条件select vfirstname,vlastname,dbirthdate,ntestscorefrom externalcandidatewhere dbirthdate=01-1月-70加上第二个条件符合条件，逻辑与：select vfirstname,vlastname,dbirthdate,ntestscorefrom externalcandidatewhere dbirthdate=01-1月-70 and ntestscore=80求反，在where后面加上个【not】select vfirstname,vlastname,dbirthdate,ntestscorefrom externalcandidatewhere not dbirthdate=01-1月-70 and ntestscore=80describe newspaperselect *from newspaper匹配模式的运算符 :like通配符： % 代表1n个字符； _ 代表一个字符select *from newspaper where cnewspapername like %Texas% and vcontactperson like Jackson %Jackson %以Jackson大头的。%Jackson以Jackson结尾的。select vfirstname,vlastname from externalcandidate名字第二个字母是“a”select vfirstname,vlastname from externalcandidate where vfirstname _a% 想查名字的最后一个字母是%的人：必须利用转义字符【】select vfirstname,vlastname from externalcandidate where vfirstname % % 前面%代表通配符，而后面%代表就是%本身！ escape 注明那个字符时作为转义字符用的即【空值】或者【null值】面试时间select vfirstname,vlastname,dinterviewdatefrom externalcandidatewhere dinterviewdate is null /判断字段上是为空的！select vfirstname,vlastname,dinterviewdatefrom externalcandidatewhere dinterviewdate is not null /判断字段上是不为空的！select vfirstname,vlastname,vemailid,cphonefrom externalcandidate where vemailid is nullselect vfirstname,vlastname,dbirthdate, ntestscorefrom externalcandidate order by ntestscore 按ntestscore排序，默认是升序；aesc升序 desc 降序select cname ,vaddressfrom contractrecruiterorder by cname desc先按照日期降序，然后再按照年龄降序select vfirstname,vlastname,dbirthdate, ntestscorefrom externalcandidate order by ntestscore desc ,dbirthdate desc作业：toy数据库第三章：1、字符函数initcap以大写方式显示字符首字母,lower转换成小写,upper转换成大写,ltrim,rtrimcontractrecruiter 猎头公司代码：select upper(CNAME) Name,lower(VADDRESS) Addressfrom contractrecruiter2、ABS绝对值、CEIL返回大于这个数的最小整数、FLOOR截取数值的整数部分、POWER求几次方、MOD返回第一个参数除以第二个参数的余数、ROUND四舍五入、TRUNC、SQRTVFIRSTNAMECPHONENTESTSCORE代码：SELECTvfirsTname vFirstname,cphone cPhone,ROUND(ntestscore) MarksFROM externalcandidate where ntestscore703 、数据类型转换varchar2(40) 表示可变长度，最长为40char(40) 表示固定长度，固定为40第四章：查询多表中数据1.用等值联接显示两个表中数据A【内链接】：只有满足联接条件的才显示，不联接的不显示个人表RecruitmentAgencies和公司表ExternalCandidateJoin on 句式格式：select vfirstname,cnamefrom externalcandidate join recruitmentagencies on recruitmentagencies.cagencycode=externalcandidate.cagencycode 因为表明太长，所以要给表取一个别名select vfirstname,cnamefrom externalcandidate e join recruitmentagencies r on r.cagencycode=e.cagencycodewhere ntestscore=80 添加一个whereWhere 句式格式:select vfirstname,cnamefrom externalcandidate e,recruitmentagencies rwhere e. cagencycode = r.cagencycode and ntestscore=80 逻辑与 and cpositioncode 表externalcandidate（应聘者CPOSITIONCODE 表position（资料VDESCRIPTION）selectvfirstname ,vlastnamefrom externalcandidate e join position p on e.CPOSITIONCODE =p.CPOSITIONCODE 或者：selectvfirstname ,vlastnamefrom externalcandidate e , position pwhere e.CPOSITIONCODE =p.CPOSITIONCODE 2、自然联接【两个表中有且仅有同名的条件】有且仅有:selectvfirstname ,vlastnamefrom externalcandidate natural join position 非有且仅有：（不能完成）selectvfirstname ,cnamefrom externalcandidate natural join recruitmentagencies3、交叉连接4、外联接只有join on 句式select co.ccollegecode, co.ccollegename, ca.ccampusrecruitmentcode from college co join campusrecruitment caon co.ccollegecode=ca.ccollegecode我们一般都用outer join onleft|right|full outer joinselect co.ccollegecode, co.ccollegename, ca.ccampusrecruitmentcode from college co left outer join campusrecruitment caon co.ccollegecode=ca.ccollegecode这种句式是在Oracle在身上用的。select co.ccollegecode, co.ccollegename, ca.ccampusrecruitmentcode from college co, campusrecruitment cawhere co.ccollegecode=ca.ccollegecode(+)作业：第5课Externalcandidate表Monthlysalary表Newspaper表Newsad表【投广告】Recruitmentagencies推荐公司一、多行函数：Avg:返回n个数据的平均值select avg(列名) from (表名) where (条件)select avg(ntestscore)from externalcandidate Sun:返回n个值的总和select sun(列名) from (表名) where (条件)select sum(NMONTHLYSALARY )from MonthlysalaryMax:返回一列中的最大值select max(列名) from (表名) select max(ntestscore) from externalcandidate Min:返回这一列的最小值select min(列名) from (表名) select min(ntestscore)from externalcandidate Count:显示查询的行数-包括null值select count(列名) from (表名) where (条件)select count(*)from externalcandidate Stddev:返回一组值得标准偏差 select stddev (列名) from (表名)Variance:返回一组值的方差select Variance (列名) from (表名)例题：select max(NPERCENTAGECHARGE ) Maximum Hiring Changes,min(NPERCENTAGECHARGE ) Minimum Hiring Changes,avg(NPERCENTAGECHARGE ) Avarage Hiring Changesfrom contractrecruiter where ccity=Alexandria二、组合函数Group by:Select 列1，列2From 表名Where 条件Group by 杯分组的列名Having 条件select from newsadwhere 例题：select cnewspapercode Newspaper Code,count(cnewsadno) No.of.Advts.Placedfrom newsadgroup by cnewspapercodeselect r.CAGENCYCODE ,count (e.CCANDIDATECODE )from recruitmentagencies r left outer join externalcandidate e on r.CAGENCYCODE=e.CAGENCYCODEgroup by r.CAGENCYCODEHaving:后面的条件是对group by后的条件现在需要对至少三个申请者的职位进行分析。要求报表显示“Position Code” No.of.Application列select CPOSITIONCODe Position Code,count(ccandidatecode) No.of.Applicationfrom externalcandidate group by CPOSITIONCODe having count(ccandidatecode)=3select CCITY ,count(VQUALIFICATION) MBAfrom externalcandidate where VQUALIFICATION=MBAgroup by ccity第6章：子查询1、单行子查询查询面试者中成绩最高的人的姓名，电话号码等信息！select vfirstname,vlastname,cphonefrom externalcandidate where ntestscore= ( select max(ntestscore) from externalcandidate)2、多行子查询select CNEWSPAPERNAME , vtypeofnewspaperfrom newspaperwhere cnewspapercode in ( select cnewspapercode from newsad group by cnewspapercode having count(cnewsadno)3)第7章DDL1、以行列形式存储数据-表按照逻辑次序表示数据或数据字集-视图自动生成顺序的数值-序列【每个值都不是重复的】增强查询性能-索引为对象分配备选的名字-同义词2、表-用户表和数据字典(1)、查看数据库里面的所有用户表：select * from user_table(2)、命名规则：以字母开头(3)、创建表： create table 表名 （ 字段的定义1名称 数据类型【名字+长度】 约束定义【】 默认值， 字段的定义2名称 数据类型【名字+长度】 约束定义【】 默认值，）步骤：（1）确定表名（2）确定表的属性（3）确定每个属性的数据类型（4）确定每个属性的长度（5）设计创建表的语句create table students( cstudentcode char(4) not null, vfirstname varchar2(20) not null, vmiddlename varchar2(5), vlastname varchar2(20), vaddress varchar2(35), ccity char(20), cstate char(20), cphonte char(16) 创建好后可以通过 describe students来查看(4)、更改表结构：添加列，删除列alter table 表名add 字段定义【添加字段】alter table studentsadd ddateofbirth date not nullmodify修改列alter table studentsmodify vmiddlename varchar2(10)drop colume 字段名删除字段名alter table students drop column ddateofbirthrename to (5)、撤销表-删除表drop table语句drop table 第8章 管理表和实施数据完整性DML查看表中的约束条件：Select*from user_constraintsWhere table_name=INSERT() UPDATE DELETE()1、 添加表中行insert into (字段列表) /字段列表默认情况下是全部字段values(值列表1-新一行每个字段的值)， (值列表2-新一行每个字段的值)， (值列表3-新一行每个字段的值); commit;(commit语句是实现永久生效的，；若不添加commit只能是生成预览效果)值列表与字段列表必须要个数一样，顺序一样！例题：insert into recruitmentagenciesvalues (0010,Head Hunters,Hill Street,Clevland,Ohio, 44167-5943,(440)345-8872,(440)345-8943,7,1000);commit;2、 更新表中的行-一次只能更改一个表中的数据，但是可以更改不唯一的字段update set 字段1=新值1，字段2=新值2，where 布尔表达式可以不写例题：update employeeset cdepartmentcode=0009where CEMPLOYEECODE=000015例题：update externalcandidate set NTESTSCORE =79where CCANDIDATECODE =000049;commit;3、 删除表中的行 delect from where 布尔表达例题：delete from recruitmentagencies where CAGENCYCODE=0010;commit;truncate table 清除表中记录如果你不写commit,那么再写一个rollback回滚。那么数据又恢复到以前状态。4、数据完整性实体完整性：域完整性：引用完整性：用户定义完整性：5、定义约束【主键约束】：系统随机给你定义一个主键名create table students(vstudentcode char(4) primary key, /主键列vfirstname varchar2(20) not null,vlastname varchar2(20) not null) 或者：自定义主键名create table students(cstudentcode char(4), /主键列vfirstname varchar2(20),vlastname varchar2(20)，constraint【约束】 约束名【自己取，不能喝数据里面的重复】 primary key(cstudentcode)/ constraint pk_students primary key(cstudentcode) 【外键约束：】create table students(cstudentcode char(4) primary key, /主键列vfirstname varchar2(20),vlastname varchar2(20)，foreign key(cclasscode) references class（cclasscode） /外键约束) 【唯一约束check：】create table class (cclasscode char(4), vclassname varchar2(20) not null unique,primary key (cclasscode)）select *from user_constraintswhere table_name =CLASS【检查约束】：Not Null 约束：create table students(cstudentcode char(4) primary key, vfirstname varchar2(20) not null, vlastname varchar2(20) not null，cclasscode char(4),nage number,foreign key(cclasscode) references class（cclasscode）,check(nage between 1 and 150)select *from user_cons_colums【添加约束：】alter table 表名 add constraint 约束名 foreign key (约束字段) reference 参照表明（约束字段）撤销约束：alter table 表名drop constraint 约束名【禁用和启动约束：】禁用：alter table 表名 disable constraint 约束名启用：alter table 表名 enable constraint 约束名第9章 使用事务transaction 事务事务：把一串一起执行的操作作为单个逻辑工作单元处理！事物四个属性：ACID【原子性Atomicty，一致性Consistency，隔离性Isolation，持久性Durability】事务的开始和终止：开始：执行事务里的第一条DML语句（非查询语句）开始终止：(1)接受或拒绝事务所做的变动 commit递交 rollback-回滚 (2)当你使用DDL语句（如create）创建数据库对象 (3)当你执行一条DCL语句 (4)机器发生故障或系统崩溃，回滚rollback 正常退出-事务提交Autocommit自动提交-影响事务的原子性！保存点：savepoint 部分回滚，回滚点保存点，节省资源。一个事务中可以写多个保存点Dml1；Dml2；Dml3；Dmln;Savepoint sp1;Dml n+1;Dml n+2; 错误点Dml n+n;Rollback sp1; 第10章 使用视图View：视图是一个或者多个表（或其他视图）中导出的行或列的子集。视图是一个虚拟表。视图只是给我们提供了一观察基表数据的窗口。删除视图，并不能改变基表的内容。select vfirstname,vlastname,vqualification,vskillfrom employee join positionskill on employee.ccurrentposition =positionskill.cpositioncode join skill on positionskill.cskillcode=skill.cskillcode 【创建视图：】create view 视图名 as 查询语句【例子：DDL】create view view_employeeskillas select vfirstname,vlastname,vqualification,vskillfrom employee join positionskill on employee.ccurrentposition =positionskill.cpositioncode join skill on positionskill.cskillcode=skill.cskillcode【查看视图：】select *from view_employee【更改视图：】create or replace view 新视图名 as 新的查询语句【撤销视图：】drop view 视图名第11章 使用其他数据库对象序列：Create sequence 序列名Increment