《OraclePLSQL8029.docx》由会员分享，可在线阅读，更多相关《OraclePLSQL8029.docx（51页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Oracle基础知识将Oracle中的日期设置为英文：alter Session set nls_date_language = american;SQL：结构化查询语言DML：数据操作语言 insert、update、delete、mergeDDL：数据定义语言 create、alter、drop、truncateDCL：数据控制语言Grant revoke事物控制语句 commit rollback savepoint一、 关系数据库管理术语主键（PK）和外键（FK） 主键：唯一识别表中记录。取值唯一，非空外键：建立表与表之间的关联关系。取值必须是所关联列中的值或空值二、数据库对象视图：表
2、的映像 真正的数据存在表里序列数（Sequence）：产生主键值 确保主键唯一索引（index）：加快查找速度同义词或者别名（Synonym）：程序单元（Program unit）PL/SQL：Procedure Language/SQL 过程化结构查询语言属于第三代语言只适用于Oracle 在SQL添加一些过程处理语句存在过程化语言 程序变得更加简单三、数据完整性约束实体：主键要求，非空唯一参考：外键必须是所关联列中的值或者是空值列：数据类型限制自定义类型的限制：符合业务规则实体完整性、参照完整性、自定义完整性Oracle 9i：internet Oracle 10g：grid 网格化计算t
3、elnet IPsqlplus（briup/briup）SQL 和PL/SQL 是语言SQL*Plus 客户端工具第一章 selecting rows一、格式：select distinct *,column alias,.from table;1）distinct 删除重复的记录select distinct namefrom s_emp;select distinct dept_id,title 这时distinct限制多个约束 dept_id和title from s_emp; 而且distinct 要紧跟在select之后2）* 选出所有的列的记录select * from s_emp
4、； 查询表中所有的信息select dept_id,last_name,manager_id from s_emp; 将表中的属性都显示出来 也就显示全表信息在java中与数据库连接时尽量不要使用*号 可读性差 效率低3）列标签的默认情况 日期、字符串默认在左边 数字数据默认在右边 标签默认显示大写4）算数表达式add：+subtract：-mutiply：*divide：/5) 给列指定别名select last_name,salary*12 as total 可以将as 省略from s_emp; select last_name,salary*12 as “total salary” 在
5、Oracle中很少出现双引号 别名是一个from s_emp;当列的别名出现空格，特殊字符或者大小写敏感时，将别名用双引号括起来6）字符串连接用 two vertical bars | 来连接字符串select first_name|last_name as namefrom s_emp;select first_name| |last_name as namefrom s_emp;当连接字符串时出现空格时 用单引号字符串常量用单引号引起来 7)空值处理空值处理 NULL是不确定的 无法进行比较insert into table_name values（NULL，NULL）；练习：查询员工的年
6、总工资（包括提成工资，可按月基本工资进行提成）select last_name,title,salary*12*(1+commission_pct/100) from s_emp;这个语句 将会使得没有提成的员工工资为空如果算数表达式中存在空值运算,最终运算结果为空select last_name,tilte,salary*12*(1+nvl(commission_pct,0)/100)from s_emp;nvl处理函数 则必须处理的值类型一样nvl(start_date,01-JAN-95)nvl(salary,1000)8) SQL*Plus知识点sqlplus username/pas
7、sworddesc s_dept 查看s_dept表下的列信息1、 sql*plus编辑命令u a text 追加信息select id 如果想在id 后面追加 name 则可以加入这个命令：a ，name from xtt_student;结果为：select id,name from xtt_student;u c /old/new 修改信息sselect id from xtt_student;c /sselect/selectu del 删除缓冲区的一行del 2 删除缓冲区中的第二行u I text 有效行下插入内容u list n 查看缓冲区下某行u n text 替换某行2、sq
8、l*plus 文件命令save filename 把缓冲区内容写入文件中get filename 把文件的内容读到缓冲区内start filename 执行文件 调用sql脚本 filename 执行文件edit filename 编辑文件spool filename 记录输出 查看老师视频spool hello.sqlselect id,last_namefrom s_emp;spool off将查询的内容写到hello.sql文件中exit 退出9）列命令clear：清楚列的格式限制format：改变列的显示格式heading：设置列的名字justify：设置列的排列格式 左，中，右例子：
9、column last_name heading Employee|Name format a15column salary justify left format $99,999.00column last_name 查看格式限制column last_name clear; 第二章 selecting rows 的限制和排序一、order byasc 升序排序，默认desc 降序排序order by 在select命令的最后-例子-select last_name EMPLOYEE,start_datefrom s_emporder by EMPLOYEE desc; 按EMPLOYEE降
10、序排列或者 按位置select last_name,salaryfrom s_emporder by 1;或者select last_name,dept_id,salaryfrom s_emp;order by dept_id,salary desc; 则是先按dept_id 升序排列，在按salary降序排列二、where 字符串和日期用单引号括起来数字不需要用单引号字符大小写敏感日期默认格式DD-MON-YY1、逻辑比较操作符= = =!= =2、SQL比较操作符between.and. not betweenin not inlike not likeis null is not nul
11、l-例子-select first_name,last_name,start_datefrom s_empwhere start_date between 09-may-91 and 17-jun-91; 必须是小到大select id,name,region_idfrom s_deptwhere region_id in(1,3); 在某某范围之间select last_namefrom s_empwhere last_name like M%; select last_namefrom s_empwhere last_name like _a% escape 将设置为转义字符select
12、id,name,credite_ratingfrom s_customerwhere sales_rep_id is null;3、逻辑操作符andornot第三章 Single Row Functions 单行函数一、字符函数lower： lower(SQL Server) sql server 将字符串转换为小写upper： upper(SQL Server) SQL SERVER 将字符串转换为大写initcap： initcap(SQL Server) Sql Server 将字符串首字母大写concat： concat(Good, String) GoodString 连接字符串su
13、bstr： substr(String ,1,3) Str 取字符串 从1位置开始取三个length： length(string) 6 求字符串的长度nvl：为空值指定属性值 例子 select last_name,salary from s_emp; where lower(last_name)=”SMITH”;二、数字处理相关函数round：将数值四舍五入trunc：将数值截断mod：取余round（45.923，2） 45.92round(45.923,0) 46round(45.923) 46round(45.923,-1) 50 负数：表示把小数点虚拟的移动几位，把小数点后面的几位
14、四舍五入trunc（45.923，2） 2 表示小数点后取两位trunc（45.923） 45 截断trunc（45.923,-1） 40mod（1600，300） 100 取余数三、日期类型函数a、months_between：求两个日期之间的月数select months_between(01-SEP-95, 11-JAN-94)from dual; 19.774194大的在前 小的在后 换位置为负b、add_months：将月份向后退几个月select add_moths(11-JAN-94,6)from dual; 11-JUL-94计算所有员工到现在入职多少天select last_
15、name,start_date,sysdate-start_date daysfrom s_empc、next_day：日期的下一天 select next_day(01-SEP-95, FRIDAY)from dual; 08-SEP-95d、last_day：一个月的最后一天select last_day(01-SEP-95) from dual; 30-SEP-95e、round：round操作日期类型 第二个参数year 对月进行进位 逢7进1month 对天进行进位 逢16进1select round(to_date(25-may-95, DD-MON-YY), month)from
16、 dual;01-JUN-95 select round(to_date(25-may-95, DD-MON-YY), year)from dual;01-JAN-96f、trunctrunc 操作日期类型 第二个参数month 对天进行舍位year 对月进行舍位select trunc(to_date(25-may-95, DD-MON-YY), month)from dual;01-may-95 select trunc(to_date(25-may-95, DD-MON-YY), month)from dual;01-JAN-95 日期的默认是：DD-MON-YYselect sysda
17、te from dual 查看当前系统时间SYSDATE 是一个函数 返回日期和时间 没有参数g、伪列 systimestamp 也是一个伪列 显示的时间更加精确 Rownum 代表行数 rowid 代表存储地址 默认按rowid 排序插入的顺序和显示的顺序不一定一样 所有要根据order by查询表中第一条记录rownum：限制a、,无意义select rownum,last_name,salaryfrom s_empwhere rownum=1select sysdate+100 当前时间的后100天from dual（将默认日期按照指定的格式显示select to_char(sysdat
18、e,year-month-ddsp-hh24:mi:ss) timefrom dualselect to_char(sysdate,year-month-ddspth-hh24:mi:ss) timefrom dualddspth 加th表示序数表示天如果打印的是英文的话 存在大小写之分日期格式里面双引号引起来的内容会原封不动的打印出来fm放在字符串前面 除掉空格select last_name,to_char(start_date,fmDdspth of Month YYYY fmHH:MI:SS AM)hiredatefrom s_empwhere start_date like %91;
19、h、转换字符to_char()将日期或数字转换为字符串形式 to_number()将字符串转换为数字to_date()将日期字符串转换为日期形式第四课 多表连接查询一、 连接方法equijoin 等连接non-equijion 非等连接outer join 外连接self join 自连接set operators 操作符1、等连接select first_name,last_name,namefrom s_emp,s_deptwhere s_emp.dept_id=s_dept.dept_id/给表取别名 建议在多表查询的时候 给表起别名 可读性好 性能高select e.first_nam
20、e,e.last_name,d.namefrom s_emp e,s_dept dwhere e.dept_id=d.id-创建表xtt_emp和表xtt_dept-create table xtt_emp(id number(7) primary key,name varchar2(20),dept_id number(7) references xtt_dept(id)create table xtt_dept(id number(7) primary key,name varchar2(50)insert into xtt_dept values(1,admin);insert into
21、 xtt_dept values(2,development);insert into xtt_dept values(3,sale);insert into xtt_emp values(1,Jack,1); 单引号insert into xtt_emp values(2,Jim,2);commit; insert into xtt_emp values(3,Lily,null);2、外连接 （当连接条件出现空值的时候 我们要将值查出来 则使用外连接）select e.name,d.idfrom xtt_emp eleft outer join xtt_dept d / 左外连接 将左表中所
22、有记录显示出来on e.dept_id=d.id from后面的称为左表select e.name,d.name from xtt_emp e,xtt_dept dwhere e.dept_id=d.id(+) 如果用where的话 则用加好 左外连接加好在右边-右外连接-select e.name,d.idfrom xtt_emp eright outer join xtt_dept don e.dept_id=d.id;select e.name,d.idfrom xtt_emp e,xtt_dept dwhere e.dept_id(+)=d.id;-全外连接-select e.name
23、,d.idfrom xtt_emp efull outer join xtt_dept don e.dept_id=d.id替代 select e.name,d.id from xtt_emp e,xtt_dept dwhere e.dept_id=d.id(+) /左连接union /重复的只取一次select e.name,d.name from xtt_emp e,xtt_dept dwhere e.dept_id(+)=d.idunion all 取两个集合的所有minus 减掉一个两个集合重复的intersect 两个集合交叉的内容-查询表中的第二条记录-select rownum,
24、last_name,salaryfrom s_empwhere rownum=2minusselect rownum,last_name,salaryfrom s_empwhere rownum1500;where avg(salary)1500 /在where中不能出现组函数限制注意：1）在select语句中，如果出现了组函数，所有未放到组函数中的列，一定要放在group by2）对分组条件进行限制，使用having而不能用where第六课 子查询一、 子查询指导方针子查询中的比较操作符一般是单行或者多行子查询可以出现在操作符的左边或者右边子查询可以运用许多SQL命令子查询可以包含order
25、 by子句 注：但是在where中不包含order by子句 所有一般不用二、单行子查询在select中嵌套select语句-select last_name,salary,dept_idfrom s_empwhere dept_id=(select dept_idfrom s_empwhere lower(last_name)=smith); /不能用order by-select rn,last_name,salaryfrom(select rownum rn,last_name,salaryfrom s_emporder by rn)where rn=2; 查询第二条记录练习:1)查询工
26、资低于平均工资的员工的信息2)查询平均工资低于32号部门平均工资的部门信息（部门id，部门平均工资）3)查询和smith在同一部门、并且相同职称的员工的信息1、select last_name,dept_id,salaryfrom s_empwhere salary(select avg(salary)from s_emp);2、select dept_id,avg(salary)from s_empgroup by dept_idhaving avg(salary)(select avg(salary)from s_empwhere dept_id=32group by dept_id);3
27、、select last_name,dept_id,title,salaryfrom s_empwhere dept_id=(select dept_idfrom s_empwhere lower(last_name)=smith)and title=(select titlefrom s_empwhere lower(last_name)=smith);三、多行子查询select last_name,first_name,titlefrom s_empwhere dept_id in(select id from s_deptwhere name= Financeor region_id=2
28、);第七课、运行时替换变量一、&与&-select last_name,salary,dept_idfrom s_deptwhere dept_id=#-select &columnsfrom &table where &condition;select last_name,salary,&columnfrom s_empwhere &column=#&同名的变量可以指定不同的值&同名的变量只能指定相同的值-字符和日期用单引号括起来-select id,last_name,salaryfrom s_empwhere title= &job_title;set verify on
29、 打开，关闭替换过程信息二、 define 给替换变量指定默认值define num查看变量值define num=42;undefine num 取消默认值三、accept 设置替换变量的约束accept num prompt please input num value:please input num value:accept num number promptplease num value: 加Number 限制类型accept num number prompt please num value:hide 加hide隐藏输入的值一般在编辑脚本时运用到start my_file va
30、lue1 value2 给sql脚本传递值 按照传递的位置来取值 sql脚本中 -表示单行注释第八章 数据库设计一、E-R实体关系图 Entity：需求说明文档中名词性词语Attibute：属性 实体具备的一些性质Relationships：关系 实体之间的联系# ：主键标识* ： 强制性约束 非空o ：没有强制性约束 可有可无二、关系类型一对一：全部是实线，外键可以在任意一边；如果是半虚半实，外键在实的那一边；多对一：外键在多的一方建立多对多：则需要建立桥表，另外一个表包含这两个表的主键| 在线上出现竖线表示是联合主键 三、范式第一范式：所有属性都必须是单一值第二范式：所有属性必须依赖于主属
31、性第三范式：除了主属性之外，其他属性之间不能有依赖关系四、 数据库设计表S_CUSTOMER Tablecolumn name id name phone sale_rep_idkey type PK FKnulls/unique NN,U NN FK table s_empFK column iddatatype num char char nummax length 7 25 20 7第九章 建 表一、表结构表：存储数据视图：一张或者多张表的数据的映射序列：产正主键属性索引：提高查找速度create table jd1201.xtt_test(id number(7),name varch
32、ar2(25);建表的时候可选项schema当前用户名（用户连同用户所拥有的资源）drop table xtt_test; 在同一用户下，不能创建相同的表名同一时间点 RDBMS只能管理一个DB属于不同用户的表之间可以进行关联create table jd1201.xtt_test(id number(7) constraint xtt_test_id_pk primary key,name varchar2(25) default briup-constraint xtt_test_id_pk primary key(id),表声明的约束); insert into xtt_test(id)
33、 values(100);二、限制a、列级别column constraint constraint_name constraint_type,b、表级别 涉及多个约束时column,constraint constraint_name constraint_type,1）not null约束中只有 not null不允许表级别的约束2)uniqueunique 唯一性约束如果进行唯一型约束 那么系统将会自动创建唯一性索引name varchar2(25),constraint xtt_test_name_u unique(name); 表级别3)primary key 主键约束id numb
34、er(7) constraint xtt_emp_id_pk primary key,constraint xtt_emp_id_name_pk primary key(id,name);4)foreign key 外键约束create table xtt_emp(id number(7) primary key ,name varchar2(15),dept_id number(7) constraint xtt references xtt_dept(id) );create table xtt_dept(id number(7) constraint xtt_dept_id_pk pri
35、mary key,name varchar2(15);外键表级别 foreign key(dept_id) references xtt_dept(id);要加上关键字foreign key练习1)创建两个表 xxx_a(id1,id2) 联合主键、联合外键xxx_b(id,name,aid1,aid2)create table xtt_a(id1 number(7),id2 number(7),constraint xtt_a_id1_id2_pk primary key(id1,id2);create table xtt_b(id number(7) primary key,name va
36、rchar2(15),a_id1 number(7),a_id2 number(7),constraint xtt_b_aid1_aid2_fk foreign key(a_id1,a_id2) references xtt_a(id1,id2);5)check 检查型约束create table xtt_test(id number(7),gender varchar2(15) constraint xtt_test_gender_c check(gender in(male,female);-以已有的表为基础 创建新的表并且拷贝表的结构-create table xtt_emp41 as select id,last_name,salary,dept_idfrom s_empwhere dept_id=41;-创建表但是不拷贝表的内容-