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

1、2.1 基本SQL语句和函数,目标,掌握 Oracle 基本SQL语句 掌握 Oracle 单值、分组函数 掌握 Oracle 多表查询、集合运算,基本SQL 语句,SQL 是 Structured Query Language（结构化查询语言）的首字母缩写词 SQL 是数据库语言，Oracle 使用该语言存储和检索信息 表是主要的数据库对象，用于存储数据,通过 SQL可以实现与 Oracle 服务器的通信,发送 SQL 查询,用户,SQL 简介,SQL 支持下列类别的命令： 数据定义语言（DDL） 数据操纵语言（DML）增删改查 事务控制语言（TCL）commit roll back 数据控

2、制语言（DCL）,数据定义语言,CREATE,ALTER,DROP,数据操纵语言,INSERT,SELECT,DELETE,UPDATE,事务控制语言,COMMIT,SAVEPOINT,ROLLBACK,数据控制语言,GRANT,REVOKE,Oracle 数据类型,创建表时，必须为各个列指定数据类型 以下是 Oracle 数据类型的类别： RAW/LONG RAW 图片换成二进制代码 LOB大对象类型（非常大的word文档等）,数据类型,字符,数值,日期时间,RAW/LONG RAW,LOB,Oracle 数据类型,字符数据类型,CHAR,VARCHAR2,LONG,当需要固定长度的字符串时

3、，使用 CHAR 数据类型。 CHAR 数据类型存储字母数字值。 CHAR 数据类型的列长度可以是 1 到 2000 个字节。,VARCHAR2数据类型支持可变长度字符串 VARCHAR2数据类型存储字母数字值 VARCHAR2数据类型的大小在1至4000个字节范围内,LONG 数据类型存储可变长度字符数据 LONG 数据类型最多能存储 2GB,Create table t1( a nchar(5); 支持 Create table t2( a varchar(5); 支持 ，但是oracle今后会不支持varchar，使用varchar2更好。 Create table t3( a numb

4、er(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 数据类型,数值数据类型 可以存储整数、浮点数和实数 最高精度 38 位,范围：负的10的38次方到10的38次方。 数值数据类型的

5、声明语法： NUMBER ( p, s) P表示精度，S表示小数点的位数,日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒 主要的日期时间类型有： DATE - 存储日期和时间部分，精确到整个的秒 TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位 Select sysdate from dual; Select to_char(sysdate,yyyymmdd hh24:mi:ss) from dual; Select to_char(sysytimestamp,yyyymmdd hh24:mi:ssxff6) from dual;,Oracle 数据类

6、型,RAW 数据类型用于存储二进制数据 （01010） RAW 数据类型最多能存储 2000 字节 LONG RAW 数据类型用于存储可变长度的二进制数据 LONG RAW 数据类型最多能存储 2 GB,LOB,LOB 称为“大对象”数据类型，可以存储多达 128TB 的非结构化信息，例如声音剪辑和视频文件等(LOB类型的容量从原来的4G增加到了最大128T) LOB 数据类型允许对数据进行高效、随机、分段的访问,BLOB,CLOB,BFILE,CLOB 即 Character LOB（字符型LOB），它能够存储大量字符数据,BLOB 即 Binary LOB（二进制 LOB），可以存储较大的

7、二进制对象，如图形、视频剪辑和视频文件 将数据放到列中，但数据存取慢,BFILE 即 Binary File（二进制文件），列中存放指针，它用于指向将二进制数据存储在数据库外部的操作系统文件中,Oracle 数据类型,Oracle 中伪列就像一个表列，但是它并没有存储在表中 伪列可以从表中查询，但不能插入、更新和删除它们的值 常用的伪列有ROWID和ROWNUM,ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行,ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数,数据定义语言,数据定义语言用于改变数据库结

8、构，包括创建、更改和删除数据库对象 用于操纵表结构的数据定义语言命令有： CREATE TABLE ALTER TABLE TRUNCATE TABLE DROP TABLE,创建表 Create table student(sno number(6), sname varchar2(20),birthday date); 更改表的结构 Alter table student add tele varchar2(11); Insert tele 12334556677888 显示表结构 Desc student; 修改表的属性，扩大tele的长度 Alter table student mod

9、ify tele varchar(5); Alter table student modify tele varchar(11); 注意从大范围到小范围改变时，如果表中数据存在超过小范围的数据，表是不能改变的 删除某列 Alter table student drop column tele; 插入数据 Insert into student values(1,A, 11-1月-01); Insert into student values(2,A, 11-jan-01); error,日期的转换 Alter session set nls_date_format=yyyymmdd Selec

10、t sysdate from dual; Insert into student values(2,B,20110901); select * from student; 如果退出系统后，quit ； 格式又恢复原来的样子 插入空值 Insert into student values(3,null, to_date(19980311,yyyy-MM-dd); hh mm ss insert into student(sno,birthday) values( 4,to_date(19880601,yyyy-MM-dd); select * from student; 查询信息为空的记录 Se

11、lect * from student where sname = Select * from student where sname = null; error Select * from student where sname = ;error Select * from student where sname is null;,根据已有表的数据创建新的表 Create table student_new1 as select * from student; Desc student_new1; Select * from student_new1; 根据已有表的数据创建新的表的结构，不要

12、数据 Create table student_new2 as select * from student where 12; Desc student_new2; Select * from student_new2; 删除表 Truncate table student ; Truncate本身包含了commit操作，应慎用 Delete table t2; Delete 操作会写入日志，通过回滚可以恢复数据rollback; 后面必须要有commit操作 Drop table student_new1;,更新数据 Update student set name=AA where sno=

13、3; 删除数据 Delete student where sno=1; Count Select count(*) from student; Select count(*) from student where sname is not null; Select * from student where sname=a; A/字符是区分大小写的 Select * from student where upper(sname)=A; 单引号 Update student set sname=AB where sno=1; SELECT DISTINCT sname FROM student;,

14、% - Select * from student where sname like A%; Select * from student where sname like A-; Update student set sname=张三 where sno=2; Update student set sname=张三三 where sno=3; Select * from student where sname like 张_; Select * from student where length(sname)=2; 软件2班 88； Order by Select * from student

15、 order by sno desc, sname; Select * from student order by 1 desc, 2; 1 和 2 代表结果集的第一、二列， 别名 Selecet sno 学号, snmae “姓名”from student ; Selecet sno “学 号”, snmae 姓名” from student ; /如果学号中间有空格，应加双引号,首先创建表 create table 成绩(sno number, km varchar2(13), score number); Insert into 成绩 values(1,语文,60); Insert in

16、to 成绩 values(1,数学,60); Insert into 成绩 values(1,英语,60); Insert into 成绩 values(2,语文,70); Insert into 成绩 values(2,数学,70); Insert into 成绩 values(3,英语,89); commit; 每个学生的总分 Select sno, sum(score) from 成绩 group by sno; /涉及到分组，按学号有3组，但是每一组中各个学生有不同的科目成绩，不能显示出来，只能显示3各组共有的信息，如总分等。 每个课程的平均成绩 select km, avg(scor

17、e) from 成绩 group by km; 平均成绩大于60的学生的学号 和 平均成绩 select km, avg(score) from 成绩 group by km having (avg(score) 60) ;,GROUP BY,select km, avg(score) from 成绩 group by km having (avg(score) 70) ;,数据操纵语言,数据操纵语言用于检索、插入和修改数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括： SELECT INSERT UPDATE DELETE,DML SELECT 命令,利用现有的表创建表 语法：

18、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 newstudent2 AS SELECT * FROM student WHERE 1 = 2;,DML SELECT 命令,SQL SELECT DISTINCT sname FROM student;,选择无重复的行 在SELECT子句，

19、使用DISTINCT关键字,使用列别名 为列表达式提供不同的名称 该别名指定了列标题,SQL SELECT sno 学号, 2010-extract(year from birthday) “年 龄” -中间有空格，用“” FROM student;,DML INSERT 命令,插入日期类型的值 日期数据类型的默认格式为“DD-MON-RR” 使用日期的默认格式 使用TO_DATE函数转换,DML INSERT 命令,插入来自其它表中的记录 语法： INSERT INTO (cloumn_list) SELECT column_names FROM ;,SQL INSERT INTO stud

20、ent2 SELECT * FROM student;,数据控制语言,数据控制语言为用户提供权限控制命令 用于权限控制的命令有： GRANT 授予权限 REVOKE 撤销已授予的权限,SQL 操作符,SQL 操作符,集合操作符,逻辑操作符,比较操作符,算术操作符,连接操作符,Oracle 支持的 SQL 操作符分类如下：,算术操作符,算术操作符用于执行数值计算 可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成 算术操作符包括加(+)、减(-)、乘(*)、除(/),比较操作符,比较操作符用于比较两个表达式的值 比较操作符包括 =、!=、=、BE

21、TWEENAND、IN、LIKE 和 IS NULL等，LIKE可以使用匹配符_、%,逻辑操作符,SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date 26-5月-05;,显示 2005-5月-10 至 2005-5月-26的订单信息,逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。 逻辑操作符包括与(AND)、或(OR)和非(NOT)。,连接操作符,连接操作符用于将多个字符串或数据值合并成一个字符串,SQL SELECT (venname| 的地址是 |venadd1| |venadd2 | |vena

22、dd3) address FROM vendor_master WHERE vencode=V001;,通过使用连接操作符可以将表中 的多个列合并成逻辑上的一行列,操作符的优先级,SQL 操作符的优先级从高到低的顺序是： 算术操作符 -最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 -最低优先级,Oracle 函数 2.1.2,Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：,SQL 函数,单行函数,分析函数,分组函数,单行函数分类,单行函数对于从表中查询的每一行只返回一个值 可以

23、出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为： 字符函数 日期时间函数 数字函数 转换函数 混合函数,字符函数,字符函数,字符函数接受字符输入并返回字符或数值,字符函数,SQL SELECT CHR(67) FROM dual;,以下是一些其它的字符函数： CHR和ASCII LPAD和RPAD TRIM LENGTH DECODE,SQL SELECT LPAD(function,15,=) FROM dual;,SELECT TRIM(9 from 9999876789999) FROM dual; select lpad(abcd, 10 , x ) fro

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

25、t; Select * from student where add_months(birthday, 20*12) sysdate; MONTHS_BETWEEN Select months_between (sysdate, to_date(20131112,yyyymmdd) ) from dual ; Select extract( year from sysdate) from dual; Select extract( month from sysdate) from dual; Select extract( day from sysdate) from dual; LAST_D

26、AY Select last_day(sysdate) from dual;,日期时间函数,ROUND四舍五入 NEXT_DAY TRUNC直接截断指定的部分 EXTRACT Select floor ( sysdate birthday ) from stufent; Select floor (4.67 ) from dual; /4 Select * from student where last_day(birthday ) -7 =birthday 本月倒数7天 Select round( to_date (sysdate,yyyymmdd), year) from dual; /按

27、年四舍五入 Select round( to_date (20130912,yyyymmdd), month) from dual; /按月四舍五入 Select round(12.34) from dual;/取整数 Select round( 12.34 , 2) from dual;/取两位小数 Select round( 12.34 , 1) from dual;/取一位小数 Select round( 12.34 , -1) from dual;/对个位进行四舍五入,数字函数,数字函数接受数字输入并返回数值结果,数字函数,转换函数,转换函数将值从一种数据类型转换为另一种数据类型 常用

28、的转换函数有： TO_CHAR TO_DATE TO_NUMBER,SELECT TO_CHAR(sysdate,YYYY年fmMM月fmDD日 HH24:MI:SS) FROM dual;,SELECT TO_CHAR(itemrate,C99999) FROM itemfile;,SELECT TO_DATE(2005-12-06 , yyyy-mm-dd) FROM dual;,SELECT TO_NUMBER(100) FROM dual;,Clear screen; Select to_char(33,l99.99) from dual; ￥33.00 Select to_char(

29、33,$99.99) from dual; $33.00 Select to_number(123) from dual;,混合函数,以下是几个用来转换空值的函数： NVL，第一为空返回第二个数；否则返回第一个数。 NVL2，第一个不空则返回二；否则返回三。 NULLIF，两个表达式，相等则返回空；否则第一个。 Select nvl(2,1) from dual; /2 Select nvl(, 1) from dual; /1 Select nvl(null, 1) from dual; /1 Select nvl2(11, 22 , 33) from dual ; /22 Select n

30、ullif(2,2) from dual;/null Select nullif(2, 222) from dual;/2,分组函数,分组函数基于一组行来返回结果 为每一组行返回一个值,AVG,MIN,MAX,SUM,COUNT,SELECT AVG(re_level) FROM itemfile WHERE p_category=accessories;,SELECT MAX(max_level) FROM itemfile;,SELECT SUM(itemrate*max_level) FROM itemfile;,SELECT COUNT(*) FROM itemfile;,SELECT

31、 COUNT(itemrate) FROM itemfile;,SELECT COUNT(DISTINCT qty_hand) FROM itemfile;,分组函数,GROUP BY和HAVING子句,GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果 HAVING子句 用于指定 GROUP BY 子句检索行的条件,SQL select sno, max(score) as 最高分, sum(score) as 总分 from 成绩 group by sno; 思考： 查出 平均成绩 大于 所有学生的 平均成绩 的 学生的 学号 和 平均成绩 查出 平均成绩 大于

32、 60 的 学生的 学号 和 平均成绩，并按照学号的降序排列,Oracle 的多表查询 2.1.3,等值连接 外连接 自连接 子查询,相等联接的写法,相等连接(第一种写法)： 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.

33、column2 可以使用表的别名，为了书写的简化。,等值连接举例,select * from tab; drop table student purge; drop table t1 purge; drop table t2 purge; drop table 成绩 purge; create table student (sno number(6), sname varchar2(10), sage number(3); insert into student values(1,张红,23); insert into student values(2,征订,23); insert into

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

35、 address values(6,苏州);,select * from student; select * from address; 两张表的笛卡尔积 select s.sno,s.sname,a.zz from student s ,address a; select s.*,a.* from student s ,address a;,等值连接举例,select s.sno, s.sname, a.sno, a.zz from student s ,address a where s.sno = a.sno ; select s.sno, s.sname, a.sno, a.zz fr

36、om student 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 table1.column1=table2.column2(+

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