oracle基本语句格式_百度文库

1、数据定义语言 数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象 用于操纵表结构的数据定义语言命令有： CREATE TABLE ALTER TABLE TRUNCATE TABLE DROP TABLE数据操纵语言 数据操纵语言用于检索、插入和修改数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括： SELECT INSERT UPDATE DELETEDML SELECT 命令 利用现有的表创建表 语法：CREATE TABLE AS SELECT column_names FROM ; SQL CREATE TABLE newstudentAS SELECT * F

2、ROM student;SQL CREATE TABLE newstudent1 AS SELECT sno, sname FROM student;SQL CREATE TABLE newstudent2AS SELECT * FROM studentWHERE 1 = 2; 选择无重复的行 在SELECT子句，使用DISTINCT关键字SQL SELECT DISTINCT sname FROM student; 使用列别名 为列表达式提供不同的名称 该别名指定了列标题SQL SELECT sno 学号, 2010-extract(year from birthday “年 龄” -中间有

3、空格，用“”FROM student;DML INSERT 命令 插入日期类型的值 日期数据类型的默认格式为“DD-MON-RR” 使用日期的默认格式 使用TO_DATE函数转换 插入来自其它表中的记录 语法：INSERT INTO (cloumn_list SELECT column_names FROM ; SQL INSERT INTO student2SELECT * FROM student; 数据控制语言 数据控制语言为用户提供权限控制命令 用于权限控制的命令有： GRANT 授予权限 REVOKE 撤销已授予的权限算术操作符 算术操作符用于执行数值计算 可以在SQL语句中使用算术

4、表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成 算术操作符包括加(+、减(-、乘(*、除(/ 比较操作符用于比较两个表达式的值 比较操作符包括 =、!=、=、BETWEENAND、IN、LIKE 和 IS NULL等，LIKE可以使用匹配符_、%逻辑操作符 逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。 逻辑操作符包括与(AND、或(OR和非(NOT显示 2005-5月-10 至 2005-5月-26的订单信息SQL SELECT * FROM order_master WHERE odate 10-5月-05 AND del_date SELECT

5、 (venname| 的地址是 |venadd1| |venadd2 | |venadd3 addressFROM vendor_master WHERE vencode=V001SQL 操作符的优先级从高到低的顺序是： 算术操作符 -最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 -最低优先级 分组函数 单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为： 字符函数 日期时间函数 数字函数 转换函数 混合函数 以下是一些其它的字符函数： CHR和ASCII LPAD和RPAD

6、 TRIM LENGTH DECODE(在2.2视频中讲解SQL SELECT LENGTH(frances FROM dual;SQL SELECT TRIM(9 from 9999876789999 FROM dual;日期时间函数 日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果 日期函数包括： ADD_MONTHS MONTHS_BETWEEN LAST_DAY ROUND NEXT_DAY TRUNC EXTRACT转换函数 转换函数将值从一种数据类型转换为另一种数据类型 常用的转换函数有： TO_CHAR TO_DATE TO_NUMBERSELECT TO_DATE(

7、2005-12-06 , yyyy-mm-ddFROM dual;SELECT TO_CHAR(sysdate,YYYY年fmMM月fmDD日 HH24:MI:SSFROM dual;SELECT TO_NUMBER(100 FROM dual;混合函数 以下是几个用来转换空值的函数： NVL，第一为空返回二；否则返回一。 NVL2，第一个不空则返回二；否则返回三。 NULLIF，两个表达式，相等则返回空；否则第一个。分组函数 分组函数基于一组行来返回结果 为每一组行返回一个值COUNTSELECT COUNT(DISTINCT qty_hand FROM itemfile;SELECT CO

8、UNT(* FROM itemfileSELECT AVG(re_level FROM itemfile WHERE p_category=accessories;SELECT COUNT(itemrate FROM itemfile; SELECT MAX(max_level FROM itemfile;SELECT SUM(itemrate*max_level FROM itemfile; GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果 HAVING子句 用于指定 GROUP BY 子句检索行的条件思考：查出 平均成绩 大于 所有学生的 平均成绩 的 学生的

9、 学号 和 平均成绩 查出 平均成绩 大于 60 的 学生的 学号 和 平均成绩，并按照学号的降序排列 SQL select sno, max(score as 最高分, sum(score as 总分 from 成绩 group by sno;Oracle 的多表查询 等值连接 外连接 自连接 子查询相等连接(第一种写法：select table1.column,table2.columnfrom table1, table2 where table1.column1=table2.column2可以使用表的别名，为了书写的简化。相等连接(第二种写法：select table1.column

10、,table2.columnfrom table1 inner join table2 on table1.column1=table2.column2可以使用表的别名，为了书写的简化。 左外连接(第一种写法：select table1.column,table2.columnfrom table1 left outer join table2on table1.column1=table2.column2可以使用表的别名，为了书写的简化。左外连接(第二种写法：select table1.column,table2.columnfrom table1, table2 where table1.

11、column1=table2.column2(+可以使用表的别名，为了书写的简化。 集合操作符 集合操作符将两个查询的结果组合成一个结果MINUSMINUS 操作符返回从第一个查询结果中排除第二个查询中出现的行。SQL SELECT orderno FROM order_master MINUSSELECT orderno FROM order_detail;INTERSECT 操作符只返回两个查询的公共行。SQL SELECT orderno FROM order_master INTERSECT SELECT orderno FROM order_detail重命名表：rename tab

12、le_name1 to table_name2;重命名列：alter table table_name rename column col_oldname to colnewname ;常见的select、from、where的顺序：1, from 2, where 3, select完整的select、from、where、group by、having、order by的顺序：1, from 2, where 3, group by 4,having 5, select 6, order byEXISTS用来判断查询所得的结果中，是否有满足条件的纪录存在。例:select *from st

13、udent where exists(select * from addresswhere zz=郑州;从select 、from、where三者的先后执行顺序来分析。子查询的使用 create table student(sno number(6 ,birthday date, sname varchar2(10;insert into student values(1, 11-1月-81 , 张三;insert into student values(2, 10-3月-82 , 李四;insert into student values(3, 06-1月-83 , 王五;insert in

14、to student values(4, 26-1月-83 , 赵六;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(4, 郑州;要求：找出zz是郑州的学生中，sno最大的学生的 sname select sname from student where sno =( select max(s

15、no from addresswhere zz= 郑州 ;1.创建student表create table student(xh number,xm varchar2(10,nl int;insert into student values (1,A,21; insert into student values (2,B,22;insert into student values (3,A,23; insert into student values (4,A,24;insert into student values (5,A,25; insert into student values (

16、6,C,26;insert into student values (7,B,27;任务：查找xm有相同的纪录，并显示出来，如下：XH XM NL- - -1 A 213 A 234 A 245 A 252 B 227 B 27答案：select * from student where xm in (select xm from student group by xm having count(*1;CREATE TABLE 管理人员 (编号 char(10 ,姓名 varchar2(10 ,管理人员编号 char(10;insert into 管理人员 values(001, 张一, 00

17、4;insert into 管理人员 values(002, 张二, 004;insert into 管理人员 values(003, 张三, 003;insert into 管理人员 values(004, 张四, 004;要求：现在想显示：编号，姓名，管理人员姓名select a.编号,a.姓名,b.姓名 as 管理人员姓名 from 管理人员 a join 管理人员 b on a.管理人员编号=b.编号;SELECT CASE WHEN的的使用语法：CASE WHEN 条件1 THEN action1WHEN 条件2 THEN action2 WHEN 条件3 THEN action3

18、. ELSE actionN END CASE select case when substr(20090310,5,2 = 01 then 一月份when substr(20090310,5,2 = 02 then 二月份when substr(20090310,5,2 = 03 then 三月份when substr(20090310,5,2 = 04 then 四月份else nullend from dual;语法：CASE selector WHEN value1 THEN action1 WHEN value2 THEN action2 WHEN value3 THEN actio

19、n3 . ELSE actionNEND CASEselect case substr(20090310,5,2 when 01 then 一月份when 02 then 二月份when 03 then 三月份when 04 then 四月份else nullend from dual;create table 成绩(sno number, km varchar2(10, score number,grade char(6; insert into 成绩 values(1, 语文, 65,null;insert into 成绩 values(2, 数学, 76,null;insert into

20、 成绩 values(3, 英语, 86,null;insert into 成绩 values(4, 语文, 94,null;要求：把每个学生的grade列，用相应的等级来更新。update 成绩 set grade = (select grade from (select sno ,case when score = 90 then 优秀when score = 80 then 良好when score = 70 then 中等when score = 60 then 及格 else 不及格end gradefrom 成绩awhere 成绩.sno = a.sno ;表T1里有 a,b,c.

21、N个字段，表T2里有 a,b,c三个字段，然后想在T1中c与表T2中c相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做 ？create table T1(a int ,b int ,c int ,d int ,e int;create table T2(a int ,b int ,c int ;insert into T1 values(1,2,3,4,5;insert into T1 values(10,20,3,4,5;insert into T1 values(10,20,4, 40,50;insert into T2 values( -1, -1 , 3;insert into

22、 T2 values( -2, -2, 4;update t1 set a= (select a from t2 where t1.c= t2.c , b =(select b from t2 where t1.c= t2.c where t1.c in (select c from t2;分析函数分析函数用于计算完成聚集的累计排名、序号等分析函数为每组记录返回多个行以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始ROW_NUMBER 返回连续的排序，不论值是否相等RANK 具有相等值的行排序相同，序数随后跳跃DENSE_RANK 具有相等值的行排序相同，序号是连续的DECOD

23、E 中的if-then-else逻辑 在逻辑编程中，经常用到If Then Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下： DECODE(value, if1, then1, if2,then2, if3,then3, . . . else Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，

24、Decode 结果就返回else 。 需要注意的是，这里的if、then及else 都可以是函数或计算表达式。Create table student(id number,name varchar2(10,sex char(1;Insert into student values(1, 张, 1;Insert into student values(2, 王, 2;Insert into student values(3, 李, 1;Select name ,decode(sex, 1,男生, 2,女生 from student;用case实现：select id,name,case sexw

25、hen 1 then 男when 2 then 女end 性别from student;DECODE 取出一行内两列中的较大值 Create table sales(month char(2,sales_tv number,sales_computer number;Insert into sales values(01, 10, 18;Insert into sales values(02, 28, 20;Insert into sales values(03, 36, 33;select month, decode(sign(sales_tv -sales_computer, 1, sal

26、es_tv, sales_computer as 较大销售量 from sales; Oracle中的行列转换create table 销售(商品名称 varchar2(10, 季度 char(2, 销售额 number;insert into 销售 values(电视机, 01, 100;insert into 销售 values(电视机, 02, 200;insert into 销售 values(电视机, 03, 300;insert into 销售 values(空调, 01, 50;insert into 销售 values(空调, 02, 150;insert into 销售 va

27、lues(空调, 03, 180;格式1：商品名称 季度 销售额- - -电视机 01 100电视机 02 200电视机 03 300空调 01 50空调 02 150空调 03 180格式2：商品名称 一季度 二季度 三季度 四季度- - - - -电视机 100 200 300 0空调 50 150 180 0从格式1到格式2：select a.商品名称,sum(decode(a.季度,01, a.销售额 ,0 一季度,sum(decode(a.季度,02, a.销售额 ,0 二季度,sum(decode(a.季度,03, a.销售额 ,0 三季度,sum(decode(a.季度,04,

28、a.销售额 ,0 四季度from 销售 a group by a.商品名称 order by 1;ROWNUM 的使用创建yggz表create table yggz (bh number(6 ,gz number;insert into yggz values(1,1000;insert into yggz values(2,1100;insert into yggz values(3,900;insert into yggz values(4,2000;insert into yggz values(5,1500;insert into yggz values(6,3000;insert into yggz values(7,1400;insert into yggz values(8,1200;作用：对查询结果，输出前若干条记录注意：只能与、=3 and rn = 5;或者：select * from yggz where rownum=5minus select * from yggz where rownum=2;任务：(2按工资由高到底，查找表中，第3高的到第5高的记录，并显示出来。结果如下:BH GZ- -5 15007 14008 1200任务(2答案：select bh,gz from (select a.*,rownum rn fr