oracle11g的基本SQL语句和函数.ppt

《oracle11g的基本SQL语句和函数.ppt》由会员分享，可在线阅读，更多相关《oracle11g的基本SQL语句和函数.ppt（57页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、目标q掌握 Oracle 基本SQL语句 q掌握 Oracle 单值、分组函数q掌握 Oracle 多表查询、集合运算基本SQL 语句 qSQL 是 Structured Query Language（结构化查询语言）的首字母缩写词qSQL 是数据库语言，Oracle 使用该语言存储和检索信息q表是主要的数据库对象，用于存储数据q通过 SQL可以实现与 Oracle 服务器的通信SELECT ename FROM Emp;发送 SQL 查询Oracle 服务器enameBLAKESMITH ALLEN DAVID MARTIN发送命令输出到用户端用户SQL 简介 qSQL 支持下列类别的命令：

2、l数据定义语言（DDL）l数据操纵语言（DML）增删改查l事务控制语言（TCL）commit roll backl数据控制语言（DCL）数据定义语言数据定义语言 CREATEALTERDROP数据操纵语言数据操纵语言 INSERTSELECTDELETEUPDATE事务控制语言事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言数据控制语言 GRANTREVOKEOracle 数据类型 q创建表时，必须为各个列指定数据类型q以下是 Oracle 数据类型的类别：qRAW/LONG RAW 图片换成二进制代码qLOB大对象类型（非常大的word文档等）数据类型数据类型字符数值日

3、期时间RAW/LONG RAWLOBOracle 数据类型字符数据类型字符数据类型CHARVARCHAR2LONGq 当需要固定长度的字符串时，使用 CHAR 数据类型。q CHAR 数据类型存储字母数字值。q CHAR 数据类型的列长度可以是 1 到 2000 个字节。q VARCHAR2数据类型支持可变长度字符串q VARCHAR2数据类型存储字母数字值q VARCHAR2数据类型的大小在1至4000个字节范围内q LONG 数据类型存储可变长度字符数据q LONG 数据类型最多能存储 2GBCreate table t1( a nchar(5); 支持Create table t2( a

4、 varchar(5); 支持 ，但是oracle今后会不支持varchar，使用varchar2更好。Create table t3( a number(3,2);Insert into t3 values(2.34444);Insert into t3 values(1.23);Insert into t3 values(23.3);Select * from t3;Create table t4( a number(3);Insert into t4 values(2.34444);Insert into t4 values(561.23);Select * from t4;Oracle

5、 数据类型q数值数据类型l可以存储整数、浮点数和实数l最高精度 38 位,范围：负的10的38次方到10的38次方。 q数值数据类型的声明语法：lNUMBER ( p, s)lP表示精度，S表示小数点的位数q日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒q主要的日期时间类型有：lDATE - 存储日期和时间部分，精确到整个的秒lTIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位lSelect sysdate from dual;lSelect to_char(sysdate,yyyymmdd hh24:mi:ss) from dual;lSelect t

6、o_char(sysytimestamp,yyyymmdd hh24:mi:ssxff6) from dual;Oracle 数据类型q RAW 数据类型用于存储二进制数据 （01010）q RAW 数据类型最多能存储 2000 字节q LONG RAW 数据类型用于存储可变长度的二进制数据q LONG RAW 数据类型最多能存储 2 GBLOBq LOB 称为“大对象”数据类型，可以存储多达 128TB 的非结构化信息，例如声音剪辑和视频文件等(LOB类型的容量从原来的4G增加到了最大128T)q LOB 数据类型允许对数据进行高效、随机、分段的访问BLOBCLOBBFILECLOB 即 C

7、haracter LOB（字符型LOB），它能够存储大量字符数据BLOB 即 Binary LOB（二进制 LOB），可以存储较大的二进制对象，如图形、视频剪辑和视频文件 将数据放到列中，但数据存取慢BFILE 即 Binary File（二进制文件），列中存放指针，它用于指向将二进制数据存储在数据库外部的操作系统文件中Oracle 数据类型qOracle 中伪列就像一个表列，但是它并没有存储在表中q伪列可以从表中查询，但不能插入、更新和删除它们的值q常用的伪列有ROWID和ROWNUMROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中

8、的一行ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数数据定义语言q数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象q用于操纵表结构的数据定义语言命令有：lCREATE TABLElALTER TABLElTRUNCATE TABLElDROP TABLE q 创建表Create table student(sno number(6), sname varchar2(20),birthday date);q 更改表的结构Alter table student add tele varchar2(11);Insert tele 12334556677888

9、q 显示表结构Desc student;q 修改表的属性，扩大tele的长度Alter table student modify tele varchar(5);q Alter table student modify tele varchar(11);l 注意从大范围到小范围改变时，如果表中数据存在超过小范围的数据，表是不能改变的q 删除某列l Alter table student drop column tele;q 插入数据l Insert into student values(1,A, 11-1月-01);l Insert into student values(2,A, 11-j

10、an-01); error q 日期的转换l Alter session set nls_date_format=yyyymmddl Select sysdate from dual;l Insert into student values(2,B,20110901);l select * from student;l 如果退出系统后，quit ； 格式又恢复原来的样子q 插入空值l Insert into student values(3,null, to_date(19980311,yyyy-MM-dd); hh mm sslinsert into student(sno,birthday

11、) values(l4,to_date(19880601,yyyy-MM-dd);l select * from student;q 查询信息为空的记录l Select * from student where sname = l Select * from student where sname = null; errorl Select * from student where sname = ;errorl Select * from student where sname is null; q 根据已有表的数据创建新的表l Create table student_new1 as se

12、lect * from student;l Desc student_new1;l Select * from student_new1;q 根据已有表的数据创建新的表的结构，不要数据l Create table student_new2 as select * from student where 12;l Desc student_new2;l Select * from student_new2;q 删除表l Truncate table student ;l Truncate本身包含了commit操作，应慎用l Delete table t2;l Delete 操作会写入日志，通过回滚

13、可以恢复数据rollback;l 后面必须要有commit操作l Drop table student_new1;q更新数据lUpdate student set name=AA where sno=3;q删除数据lDelete student where sno=1;qCountlSelect count(*) from student;lSelect count(*) from student where sname is not null;lSelect * from student where sname=a; A/字符是区分大小写的lSelect * from student whe

14、re upper(sname)=A; q单引号lUpdate student set sname=AB where sno=1; qSELECT DISTINCT sname FROM student;q % -l Select * from student where sname like A%;l Select * from student where sname like A-;l Update student set sname=张三 where sno=2;l Update student set sname=张三三 where sno=3;l Select * from stude

15、nt where sname like 张_;l Select * from student where length(sname)=2;l 软件2班 88；q Order byl Select * from student order by sno desc, sname;l Select * from student order by 1 desc, 2;l 1 和 2 代表结果集的第一、二列，q 别名l Selecet sno 学号, snmae “姓名”from student ;l Selecet sno “学 号”, snmae 姓名” from student ;l /如果学号中

16、间有空格，应加双引号q 首先创建表create table 成绩(sno number, km varchar2(13), score number);Insert into 成绩 values(1,语文,60);Insert into 成绩 values(1,数学,60);Insert into 成绩 values(1,英语,60);Insert into 成绩 values(2,语文,70);Insert into 成绩 values(2,数学,70);Insert into 成绩 values(3,英语,89);commit;q 每个学生的总分l Select sno, sum(scor

17、e) from 成绩 group by sno;q /涉及到分组，按学号有3组，但是每一组中各个学生有不同的科目成绩，不能显示出来，只能显示3各组共有的信息，如总分等。q 每个课程的平均成绩l select km, avg(score) from 成绩 group by km;q 平均成绩大于60的学生的学号 和 平均成绩l select km, avg(score) from 成绩 group by km having (avg(score) 60) ;GROUP BYqselect km, avg(score) from 成绩 group by km having (avg(score)

18、70) ;数据操纵语言 q数据操纵语言用于检索、插入和修改数据q数据操纵语言是最常见的SQL命令q数据操纵语言命令包括：qSELECTqINSERTqUPDATEqDELETEDML SELECT 命令 q利用现有的表创建表q语法： CREATE TABLE AS SELECT column_names FROM ;SQL CREATE TABLE newstudent AS SELECT * FROM student;SQL CREATE TABLE newstudent1 AS SELECT sno, sname FROM student;SQL CREATE TABLE newstude

19、nt2 AS SELECT * FROM student WHERE 1 = 2;DML SELECT 命令SQL SELECT DISTINCT sname FROM student;q选择无重复的行q在SELECT子句，使用DISTINCT关键字q使用列别名q为列表达式提供不同的名称q该别名指定了列标题SQL SELECT sno 学号, 2010-extract(year from birthday) “年 龄” -中间有空格，用“” FROM student;DML INSERT 命令q插入日期类型的值q日期数据类型的默认格式为“DD-MON-RR”q使用日期的默认格式q使用TO_DA

20、TE函数转换DML INSERT 命令q插入来自其它表中的记录q语法： INSERT INTO (cloumn_list) SELECT column_names FROM ;SQL INSERT INTO student2 SELECT * FROM student; 数据控制语言q数据控制语言为用户提供权限控制命令 q用于权限控制的命令有：qGRANT 授予权限qREVOKE 撤销已授予的权限SQL 操作符SQL 操作符集合操作符逻辑操作符比较操作符算术操作符连接操作符qOracle 支持的 SQL 操作符分类如下：算术操作符q算术操作符用于执行数值计算q可以在SQL语句中使用算术表达式，

21、算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成q算术操作符包括加(+)、减(-)、乘(*)、除(/)比较操作符 q比较操作符用于比较两个表达式的值q比较操作符包括 =、!=、=、BETWEENAND、IN、LIKE 和 IS NULL等，LIKE可以使用匹配符_、%逻辑操作符SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date SELECT (venname| 的地址是 |venadd1| |venadd2 | |venadd3) address FROM vendor_master WHERE v

22、encode=V001;通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列操作符的优先级SQL 操作符的优先级从高到低的顺序是：q算术操作符 -最高优先级q连接操作符q比较操作符qNOT 逻辑操作符qAND 逻辑操作符qOR 逻辑操作符 -最低优先级 Oracle 函数 2.1.2qOracle 提供一系列用于执行特定操作的函数qSQL 函数带有一个或多个参数并返回一个值q以下是SQL函数的分类：SQL 函数单行函数分析函数分组函数单行函数分类q单行函数对于从表中查询的每一行只返回一个值q可以出现在 SELECT 子句中和 WHERE 子句中 q 单行函数可以大致划分为：l字符函数l日期

23、时间函数l数字函数l转换函数l混合函数字符函数 函数函数 输入输入 输出输出Initcap(char) Select initcap(hello) from dual;Hello Lower(char) Select lower(FUN) from dual;fun Upper(char) Select upper(sun) from dual;SUN Ltrim(char,set) Select ltrim( xyzadams,xyz) from dual; adamsRtrim(char,set) Select rtrim(xyzadams,ams) from dual; xyzad Tr

24、anslate(char, from, to) Select translate(jack,j ,b) from dual; back Replace(char, searchstring,rep string) Select replace(jack and jue ,j,bl) from dual;black and blue Instr (char, m, n) Select instr (worldwide,d) from dual; 5 Substr (char, m, n) Select substr(abcdefg,3,2) from dual; cd Concat (expr1

25、, expr2) Select concat (Hello, world) from dual; Hello world字符函数字符函数q字符函数接受字符输入并返回字符或数值字符函数SQL SELECT CHR(67) FROM dual;q以下是一些其它的字符函数：qCHR和ASCIIqLPAD和RPADqTRIMqLENGTHqDECODESQL SELECT LPAD(function,15,=) FROM dual;SELECT TRIM(9 from 9999876789999) FROM dual;select lpad(abcd, 10 , x ) from daul ; / x

26、xxxxxabcdselect chr(23) from dual;/ 整数对应的ascii码标示的字符Select chr(96) from dual;Select ascii(a) from dual; SELECT LENGTH(frances) FROM dual;日期时间函数q 日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果q 日期函数包括：l ADD_MONTHSq Select add_months(sysdate, 5) from dual;q Select sno , add_months(birthday, 12) from student; q Select

27、 * from student where add_months(birthday, 20*12) select sno, max(score) as 最高分, sum(score) as 总分 from 成绩 group by sno;思考：思考： 查出查出 平均成绩平均成绩 大于大于 所有学生的所有学生的 平均成绩平均成绩 的的 学生的学生的 学号学号 和和 平均成绩平均成绩 查出查出 平均成绩平均成绩 大于大于 60 的的 学生的学生的 学号学号 和和 平均成绩，并按照学平均成绩，并按照学号的降序排列号的降序排列 Oracle 的多表查询 2.1.3q等值连接q外连接q自连接q子查询相等

28、联接的写法相等连接相等连接(第一种写法第一种写法)： select table1.column,table2.column from table1, table2 where table1.column1=table2.column2 可以使用表的别名，为了书写的简化。可以使用表的别名，为了书写的简化。相等连接相等连接(第二种写法第二种写法)： select table1.column,table2.column from table1 inner join table2 on table1.column1=table2.column2 可以使用表的别名，为了书写的简化。可以使用表的别名，为了

29、书写的简化。 等值连接举例select * from tab;q drop table student purge;q drop table t1 purge;q drop table t2 purge;q drop table 成绩 purge;q create table student (sno number(6), sname varchar2(10), sage number(3);q insert into student values(1,张红,23);q insert into student values(2,征订,23);q insert into student valu

30、es(3,梅林,23);q insert into student values(4,吴天,23);q insert into student values(5,赵越,23);q create table address( sno number(6), zz varchar2(10);q insert into address values(1,苏州);q insert into address values(2,成都);q insert into address values(3,重庆);q insert into address values(5,荆州);q insert into add

31、ress values(6,苏州);qselect * from student;qselect * from address;q两张表的笛卡尔积lselect s.sno,s.sname,a.zz from student s ,address a;lselect s.*,a.* from student s ,address a;等值连接举例qselect s.sno, s.sname, a.sno, a.zz from student s ,address a where s.sno = a.sno ;qselect s.sno, s.sname, a.sno, a.zz from st

32、udent s inner join address a on s.sno = a.sno ;左外联接的写法左外连接左外连接(第一种写法第一种写法)： select table1.column,table2.column from table1 left outer join table2 on table1.column1=table2.column2 可以使用表的别名，为了书写的简化。可以使用表的别名，为了书写的简化。左外连接左外连接(第二种写法第二种写法)： select table1.column,table2.column from table1, table2 where tabl

33、e1.column1=table2.column2(+) 可以使用表的别名，为了书写的简化。可以使用表的别名，为了书写的简化。 左外联接q把左表信息全部显示出来qselect s.sno, s.sname, a.sno, a.zz from student s, address a where s.sno = a.sno(+) ;qselect s.sno, s.sname, a.sno, a.zz from student s left outer join address a on s.sno = a.sno ;右外联接q把右表信息全部显示出来qselect s.sno, s.sname,

34、 a.sno, a.zz from student s, address a where s.sno (+) = a.sno ;qselect s.sno, s.sname, a.sno, a.zz from student s right outer join address a on s.sno = a.sno ;全连接qselect s.sno, s.sname, a.sno, a.zz from student s full outer join address a on s.sno = a.sno ;q显示两张表的全部信息select employees.last_name, job

35、s.job_title from employees inner join jobs on employees.job_id = jobs.job_idwhere department_id =60;select employees.last_name, jobs.job_title from employees inner join jobs on employees.job_id = jobs.job_id集合操作符q集合操作符将两个查询的结果组合成一个结果集合操作符UNIONUNION ALLINTERSECTMINUSSQL SELECT orderno FROM order_mast

36、er MINUS SELECT orderno FROM order_detail;MINUS 操作符返回从第一个查询结果中排除第二个查询中出现的行。集合操作符q集合操作符将两个查询的结果组合成一个结果集合操作符UNIONUNION ALLINTERSECTMINUSINTERSECT 操作符只返回两个查询的公共行。SQL SELECT orderno FROM order_master INTERSECT SELECT orderno FROM order_detail;q Select * from student union select * from student where sno

37、=1;l 第二张表只有一条信息l 它们的交集保留一次q Select * from student union all select * from student where sno=1;l 它们的交集重复的信息也会保留q Select * from student minus select * from student where sno=1;l 第一张表的信息减去第二张表的信息l 信息少的表不能减去信息大的表q Select * from student intersect select * from student where sno=1;l 两张表的交集重命名重命名 重命名表：重命名表

38、：rename table_name1 to table_name2;rename table_name1 to table_name2; 重命名列：重命名列：alter table table_name rename column alter table table_name rename column col_oldname to colnewname ;col_oldname to colnewname ; Rename student to new_student;Rename student to new_student; Alter table new_student rename column sno to Alter table new_student rename column sno to new_sno;new_sno;