Oracle基础知识及例子.docx

数据库类型：小型：Access foxbase中型：MySql SQLServer大型：DB2 Oracle Sybase Oca Oracle初级认证Ocp Oracle 专家级认证Ocm Oracle 大师级认证Oracle创始人：劳伦斯 埃里森1970做数据库起家Oracle9i 2002年Oracle10g 2003年Oracle11g 2007年Oracle管理工具：sql plus账号：user1-user19口令同上recruitsql语句：DDL：Data Definition Languagecreatedropalterdesc / describeDML:Data Manipulation LanguageInsert deleteupdate 更新select命令行：isqlplusctl start 启动isqlplus服务select 语句select * from externalcandidate;select vfirstname,vlastname,ccity from externalcandidate;select vfirstname| |vlastname as name , ccity as city from externalcandidate;查看表中唯一的值SELECT DISTINCT column_name FROM tablenameWhere字句select * from college where cstate=California;选择年龄比23-11月-67大的所有应聘者的所有信息desc externalcandidate;select * from externalcandidate where dbirthdate23-11月-67;select sysdate from dual;select * from dual;select 99*5 from dual;模糊查询：likeselect cnewspapername paper,vcontactperson person,cphone phone from newspaper where vcontactperson like %Jackson%;%表示任意字符数_表示一个字符数IS NULL关键字: 用来检查表中是否包含NULL值的空白列或行. 语法SELECT select_list FROM tablename WHERE column_name IS NULL;desc position;select * from position;select (NBUDGETEDSTRENGTH -NCURRENTSTRENGTH )need from position where cpositioncode=0001;select * from position order by cpositioncode desc;（降序）单行函数：select initcap(jack) from dual;select upper(vfirstname) from externalcandidate;select lower(vfirstname) from externalcandidate;select ltrim(helloworld,hello) from dual;select rtrim(helloworld,hello) from dual;ltrim(char,set) :从char左侧开始比较。如果在set中出现，则删除，否则就停止。select concat(hello,worldssss) from dual;select substr(helloworld,2,4) from dual;select instr(helloworld,e) from dual;select lpad(1000,7,$) from dual;select rpad(1000,7,$) from dual;select length(helloworld) from dual;select vfirstname,vlastname,round(ntestscore,1) from externalcandidate where nTestscore70;select round(123.456,-2)from dual;select abs(-9) from dual;select ceil(12.3) from dual;select floor(12.3) from dual;select power(5,3) from dual;select trunc(123.756,2) from dual;select sqrt(81) from dual;select mod(11,-3) from dual;日期函数：select ccollegecode,drecruitmentstartdate,add_months(drecruitmentstartdate,2) ProposedDeadline from campusrecruitment;select greatest(12-APR-02,12-MAY -02) from dual;select greatest(to_date(12-5月-13),sysdate) from dual;select least(to_date(12-5月-13),sysdate) from dual;select least(19-APR-02,12-MAY -02) from dual;select least(2,4) from dual;select last_day(sysdate)-sysdate from dual;select months_between(20-1月-00,13-5月-00) from dual;select next_day(sysdate,2) from dual;select ccandidatecode, to_char(ddateofapplication,MON/yy) from externalcandidate;select ccandidatecode, to_char(ddateofapplication,MON/yy yyyy-mm-dd pm hh:mi:ss) from externalcandidate;select ccandidatecode, to_char(ddateofapplication,MON/yy yyyy-mm-dd pm hh:mi:ss day d ddd) from externalcandidate; （d：sun=1 ddd:这一年第几天）select to_char(123456.789,$99,999,999.000) from dual;select to_char(123456.789,$00,000,000.000) from dual;select to_date(2000-01-01,yyyy-mm-dd) from dual;select round(sysdate,month) from dual;select trunc(sysdate,year) from dual;select to_number(cemployeecode) from employee where vfirstname=Betty;select nvl(CJOBFAIRCODE,0000) from externalcandidate;select cemployeecode,coalesce(nreferralbonus,nmonthlysalary/10,200)from monthlysalary;SELECT cCandidateCode, vFirstName, vLastName, CASE cState WHEN California THEN Send call letters ELSE No need to send call letter END “Message” FROM Employee;多表连接：select vfirstname,ccity,ccountry from externalcandidate,country where externalcandidate.ccountrycode=country.ccountrycode;select e.vfirstname,e.ccity,c.ccountry from externalcandidate e,country c where e.ccountrycode=c.ccountrycode;select e.vfirstname,ame from externalcandidate e,contractrecruiter c where e.ccontractrecruitercode=c.ccontractrecruitercode;SELECT column_name FROM table1 JOIN table2 ON table1.ref_column_name join_operator table2.ref_column_name;select e.vfirstname,ame from externalcandidate e join contractrecruiter c on e.ccontractrecruitercode=c.ccontractrecruitercode;select e.vfirstname ,c.ccountry,ame from externalcandidate e,country c,contractrecruiter co where e.ccountrycode=c.ccountrycode and e.ccontractrecruitercode=co.ccontractrecruitercode;select e.vfirstname,c.ccollegename,ame from externalcandidate e,college c,recruitmentagencies r where e.ccollegecode=c.ccollegecode and e.cagencycode=r.cagencycode;where子句给出指定条件当被连接的多个表中存在同名的字段，则需要用表名.字段 进行区分使用表别名简化查询语句连接n个表需要n-1个连接条件。外部应征者学校国家招聘中介联系人招聘中介校园招聘岗位非等值连接：select e.vfirstname,e.vlastname,r.ccandidaterating from externalcandidate e,rating r where e.nrating between r.nlowerrating and r.nhigherrating;自然连接：所有同名列进行等值连接同名列类型不同则出错SELECT cEmployeeCode, vFirstName, nAnnualSalary, nYear FROM Employee JOIN AnnualSalary USING (cEmployeeCode);Using ：起设置参考作用，不要求所有列都相同外连接：左外连接：返回第一个表的所有的行和第二个表中的匹配行select e.vfirstname,e.ccontractrecruitercode,ame from externalcandidate e left outer join contractrecruiter c on e.ccontractrecruitercode=c.ccontractrecruitercode;右外连接：返回第二个表的所有的行和第一个表中的匹配行select e.vfirstname,c.ccontractrecruitercode,ame from externalcandidate e right join contractrecruiter c on e.ccontractrecruitercode=c.ccontractrecruitercode;完整外连接：返回第二个表的所有的行，即使第一个表中没有匹配行 和第一个表中的所有行，即使第二个表中没有匹配行。select e.vfirstname,c.ccontractrecruitercode,ame from externalcandidate e full join contractrecruiter c on e.ccontractrecruitercode=c.ccontractrecruitercode;自连接：-update employee set csupervisorcode=000002 where cemployeecode=000001;select a.vfirstname,b.vfirstname from employee a,employee b where a.csupervisorcode=b.cemployeecode;1、同一张当做两张表来使用2、必须使用表的别名多行函数：select max(ntestscore) from externalcandidate;select min(ntestscore) from externalcandidate;select avg(ntestscore) from externalcandidate;select sum(ntestscore) from externalcandidate;select count(*) from externalcandidate where VQUALIFICATION =BBA;select count(VEMAILID) from externalcandidate where VQUALIFICATION =BBA; VEMAILID不能是空值select count(distinct ccity) from externalcandidate;select ccity ,avg(ntestscore) from externalcandidate group by ccity;出现在select语句中的字段，如果没有出现在多行函数里，必须出现在group by里，-select vfirstname,max(ntestscore) from externalcandidate ; （错误） select ccity ,avg(ntestscore) from externalcandidate group by ccity;select ccity ,avg(ntestscore) from externalcandidate group by ccity having avg(ntestscore) 80;having子句用来指定group by子句执行的分组操作的条件。顺序：select from .where .group by.having. order by.分数大于75的应征者，按照城市分组，分组之后的平均分数必须大于80分，查询分组之后的平均分数按照分数的倒序排列。select avg(ntestscore) from externalcandidate where ntestscore75 group by ccity having avg(ntestscore)80 order by avg(ntestscore) desc;select vfirstname,cstate,nprevannualsalary from externalcandidate group by cstate,nprevannualsalary,vfirstname having max(nprevannualsalary)30000;按照学院分组之后的平均分大于总的平均分select ccollegecode, avg(ntestscore) from externalcandidate group by ccollegecode having avg(ntestscore)(select avg(ntestscore) from externalcandidate );withemp_sal as(select cemployeecode,sum(nmonthlysalary) as emp_total from monthlysalary group by cemployeecode) select * from emp_sal where emp_total(select avg(emp_total) from emp_sal);SELECT cContractRecruiterCode, MIN(nTestScore) FROM ExternalCandidate WHERE cContractRecruiterCode IS NOT NULL GROUP BY cContractRecruiterCode HAVING MIN(nTestScore)(SELECT MIN(nTestScore)FROM ExternalCandidate WHERE cContractRecruiterCode=0004);select ccity from college where ccollegename=Wiley College;select vfirstname ,ccity from externalcandidate where ccity=(select ccity from college where ccollegename=Wiley College);select ccity from contractrecruiter where cname=George Craig;select cname,ccity from recruitmentagencies where ccity=(select ccity from contractrecruiter where cname=George Craig);多行运算符：in:等于列表中的任何元素any:大于列表中最小的值=any:等价于inall:大于列表中最大的值not in：不等于列表中的任何值select vfirstname,ccity,vaddress from employee where ccity in(Columbus ,Norton);select vfirstname,ccity,vaddress from employee where ccandidatecode in(select ccandidatecode from employee where ccity=Columbus or ccity=Norton);查询职员姓名，地址，城市，条件是他们进公司的时间比随便一个Norton市的职员晚。select vfirstname,vaddress,ccity from employee where djoiningdateany (select djoiningdate from employee where ccity=Norton);查询职员姓名，地址，城市，条件是他们进公司的时间比所有Norton市的职员晚select vfirstname,vaddress,ccity from employee where djoiningdateall (select djoiningdate from employee where ccity=Norton);分数按照城市分组，求每个城市分数最高的人，和他的分数。select vfirstname,ntestscore from externalcandidate where (ccity,ntestscore ) in (select ccity city,max(ntestscore) max_score from externalcandidate group by ccity);select vfirstname,ntestscore from externalcandidate e,(select ccity city,max(ntestscore) max_score from externalcandidate group by ccity) t where e.ntestscore=t.max_score and e.ccity=t.city;嵌套子查询：Employee表里Angela职员引荐的所有外部应聘者的姓名和地址。select vfirstname,vaddress from externalcandidate where cemployeereferralno in(select cemployeereferralno from employeereferrals where cemployeecode=(select cemployeecode from employee where vfirstname=Angela);不用多行函数求外部应聘者的最高分数select distinct ntestscore from externalcandidate where ntestscore not in (select distinct e1.ntestscore from externalcandidate e1 join externalcandidate e2 on e1.ntestscoree2.ntestscore);求平均分数最高的城市。select max(avg_score) from (select avg(ntestscore) avg_score,ccity city from externalcandidate group by ccity);求三个有三个以上广告的报纸数据库对象：表：用来存储数据的数据库对象，表由行和列组成。视图序列索引同义词表名命名规则：1、 以字母开头2、 长度在1-30个字母之间3、 表名中只能包含数字、字母、下划线、$,#4、 表名在数据库必须是唯一的5、 不同用oracle的保留字，date，union，compute等6、 表名应该是有意义的，应与表中存储的实际数据有关创建表的前提1、 具备创建表的权限2、 有可用的存储空间create table person (id char(4) not null,name varchar2(20) not null,age number(10),address varchar2(30);create table students(studentcode char(4) not null,firstname varchar2(20) not null,middlename varchar2(20) ,lastname varchar2(20),address varchar2(30),city varchar2(10),state varchar2(10),zip varchar2(10),phone varchar2(20)添加新的字段：只能被加到整个表的最后alter table students add(age number(10) not null);rename students to t_students;drop table t_students;insert into tablename column_list values (values_list);insert into students values(0001,张,三,丰,新区国家软件园18-号,无锡,江苏,214000,05108566888);insert into students (studentcode,firstname,phone) values(0002,李四;1、 insert语句一次只能向表里插入一条数据2、 缺省字段名列表时，应为新插入的记录中的每个字段设定值，包括空值，用null；3、 也可设置赋值字段的列表，只为部分字段显示设定新值，其他字段缺省为null4、可以再insert语句中使用子查询，实现表间的数据拷贝。insert into students_copy (STUDENTCODE,FIRSTNAME )select STUDENTCODE, FIRSTNAME from students;insert into students values(&code,&firstname,&mname,&lname,&address,&city,&state,&-zip,&phone);1、 不必使用values2、 子查询中的值列表应与insert子句中的字段列表对应。Update语句每次可以更新多条记录可以使用where子句限定更新的记录，如果缺省where子句，则更新多有记录。update students set phone=1386589742 where studentcode=0001;delete语句每次可以删除多条记录可以使用where子句限定要删除的记录，如果缺省where子句，就删除所有记录delete from students where studentcode=0002;delete from students_copydelete students;truncate:用来除去表总所有数据。除去表所占的存储空间。Delete：不除去表所占的存储空间。约束：非空约束：Not null：create table stu(id number(6) constraint stu_id_nn not null,name char(10)唯一性约束：unique1、 确保所在字段或者是字段组合不出现重复值2、 允许出现空值3、 会自动创建唯一性索引4、 可在字段级定义，也可以再表级定义create table stu(id number(6) unique,name char(10) constraint stu_id_nn not null)create table stu(id number(6) ,name char(10) constraint stu_id_nn not null,constraint stu_id_un unique(id) (表级约束)create table stu(id number(6) ,name char(10) constraint stu_id_nn not null,constraint stu_id_name_un unique(id,name) )主键约束：primary key（唯一且非空）可以唯一标示整条记录的字段create table stu(id number(6) primary key,name char(10) constraint stu_id_nn not null )create table stu(id number(6) ,name char(10) constraint stu_id_nn not null, constraint stu_id_pk primary key(id) )外键约束：foreign key用于确保相关的两个字段之间的参照关系，以实现参照完整性。通常构建与来自不同表的两个字段之间。create table class(id number(10) primary key,name char(20) not null)create table stu(id number(6) ,name char(10) constraint stu_id_nn not null,class number(10) references class(id),constraint stu_id_pk primary key(id) )create table stu(id number(6) ,name char(10) constraint stu_id_nn not null,class number(10),constraint stu_id_pk primary key(id),constraint stu_class_fk foreign key(class) references class(id);1、 确保外键列的值必须在主表参照列值的范围内，或null2、 外键参照必须是主表的主键或者唯一键3、 主表主键/惟一值被字表参照时，主表相应记录不允许被删除。检查约束：check只能在字段级定义create table stu(id number(6) ,name char(10) constraint stu_id_nn not null,age number(4) constraint stu_age_ch check(age between 0 and 150),class number(10),constraint stu_id_pk primary key(id),constraint stu_class_fk foreign key(class) references class(id);商品类别表：Productype：id（主键）(integer) ,name (varchar2)商品表：c Product：id （主键）(integer), typeid（外键_producttype）(number),name(varchar2),price (number(8,2),description (varchar2)顾客表：Customer: id （主键）(integer),name(varchar2) (非空) ,sex( varchar2)(检查约束) ,phone(varchar2) ,birthday(date)购买表：Purchase：id （主键）(integer) ,prod_id(integer) (外键_product) ，cust_id (integer) （外键_customer） ,quantity (number),buytime (date);create table producttype(id integer primary key,name varchar(30)create table product(id integer primary key,typeid integer references producttype(id),name varchar2(30),price number(8,2),decription varchar2(100)create table customer(id integer primary key,name varchar2(30) not null,sex varchar2(2) chec