技术开发部oracle初级

技术开发部oracle初级内容摘要：

sql course UPPER(column|expression) UPPER(„SQL Course‟) → SQL COURSE INITCAP(column|expression) INITCAP(„SQL Course‟) → Sql Course CONCAT(column1|expression1, column2|expression2) CONCAT(„Good‟, „String‟) →GoodString SUBSTR(column|expression,[m,n]) SUBSTR(„String‟, 1, 3) → Str LENGTH(column|expression) LENGTH(„String‟) → 6 NVL(column|expression1,column|expression2) 数字型函数 数字型函数 例 ROUND(column|expression,n) ROUND(, 2) → ROUND(, 0) → 46 TRUNC(column|expression,n) TRUNC (, 2) → TRUNC (, 0) → 45 MOD(m,n) MOD(1600, 300) → 100 日期型函数 Oracle 以内部数字格式存储以下日期信息： Century， Year， Month， Day， Hours， Minutes，Seconds。 缺省的显示日期格式为 DDMMYY 对日期可以进行算数运算 date + number date number date date 第 16 页 共 58页 date + number/24 日期型函数 例 MONTHS_BETWEEN(date1, date2) MONTH_BETWEEN(„01SEP95‟, 1JAN94‟) → ADD_MONTHS(date,n) ADD_MONTHS(„11JAN94‟,6)→ 11JUL94‟ NEXT_DAY(date,„char‟) NEXT_DAY(„01SEP95‟, „FRIDAY‟) → „08SEP95‟ LAST_DAY(date) LAST_DAY(„01SEP95‟) → „30SEP95‟ ROUND(date[,„fmt‟]) ROUND(„25MAY95‟, „MONTH‟) → „01JUN95‟ ROUND(„25MAY95‟, „YEAR‟) → „01JAN95‟ TRUNC(date[,„fmt‟]) TRUNC(„25MAY95‟, „MONTH‟) → „01MAY95‟ TRUNC(„25MAY95‟, „YEAR‟) → „01JAN95‟ 日期格式模型元素 TO_CHAR(number|date,[‘fmt’]) TO_NUMBER(char) TO_DATE(char,[‘fmt’]) 例： SQL SELECT ID,TO_CHAR(date_ordered,‟MM/YY‟) ORDERED 2 FROM s_ord； ID ORDERED 100 08/92 105 09/92 109 09/92 第 17 页 共 58页 110 09/92 111 09/92 SQL SELECT id， 2 TO_CHAR(total,‟ $9,999,999‟) TOTAL 3 FROM s_ord； ID TOTAL 107 $142,171 110 $1,539 111 $2,770 聚集函数 1） Count([DISTINCT|ALL] 表达式 |*) 求元组个数 例：查询学生总数 Select Count(*) From Student。 例：查询各门课及其相应选课人数。 Select Cno, Count(Sno) From SC Group By Cno。 例：查询选课至少为三门的学生的学号及其选课门数。 Select Sno, Count(Sno) From SC Group By Sno Having Count(Sno)=3。 2） Sum([DISTINCT|ALL] 表达式 ) 对数值列求总和 例：统计某单位的职工平均奖金，平均收入和总劳资花费 SELECT AVG(BONUS), AVG(SALARY+NVL(BONUS,0)), 第 18 页 共 58页 SUM(SALARY+NVL(BONUS,0)) FROM EMP。 3） Avg([DISTINCT|ALL] 表达式 ) 求数值列的平均值 例：计算 1 号课程的学生平均成绩 Select AVG(Grade) From SC Where Cno=„1‟。 例：统计某单位的职工平均奖金，平均收入和总劳资花费 SELECT AVG(BONUS), AVG(SALARY+NVL(BONUS,0)), SUM(SALARY+NVL(BONUS,0)) 4） Max([DISTINCT|ALL] 表达式 ) 求最大值 例： 查询选修 1 号课程的学生最高分数 Select MAX(Grade) From SC Where Cno=„1‟。 Min([DISTINCT|ALL] 表达式 ) 求最小值 连接条件 连接查询中用来连接两个表的条件称 为连接条件或连接谓词，其一般格式为： [表名 .] 列名 比较运算符 [表名 .] 列名 其中的 列名 称为连接字段， 连接条件的各个连接字段类型必须是可比的，但不必是相同的。 比较运算符 主要有： = ， ， ， = ， = ， !=。 当连接运算符为等号时，称为等值连接，否则称非等值连接。 1）连接查询 查询每个学生及其所选课程的情况 Select Student.*, SC.* From Student, SC Where =。 第 19 页 共 58页 查询每一门课的间接先修课（即先修课的先修课） Select , From Course FIRST, Course SECOND Where FIRST. Cpno = SECOND. Cno 查询所有学生的学号、姓名、选修的课程号、成绩 Select , Sname, Cno, Grade From Student,SC Where =(+) 查询选修了 2 号课程且成绩在 90 分以上的学生的学号和姓名 SELECT , Sname FROM Student, SC WHERE = AND (=‘2’ AND 90)。 2）嵌套查询 在 SQL 语言 中，一个 SELECTFROMWHERE 称为一个查询块，将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询。 上层的查询块称为外层查询或父查询，下层查询称为内层查询或子查询。 外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。 通过子查询，可以利用一系列简单查询来构造一个复杂查询。  子查询必须用圆括号括起来，只能出现在比较运算符的右边，子查询中的 SELECT语句中不能使用 ORDER BY 子句， ORDER BY 子句只能对最终查询结果排序。  不相关子查询 不相关子查询的执行顺序是由内向外，即每个子查询在上一级查询处理前求解，子查询的结果集合作为其外部查询的检索条件的条件值。  相关子查询 相关子查询的查询条件引用了其外部查询的某个属性值，从而其执行依赖于其外部查询。 其执行顺序是：首先取外部查询选取一行记录（称为候选行），然后子查询利用 第 20 页 共 58页 候选行中相关列的值查询结果数据。 接着外部查询利用子查询返回的结果集合判断候选行是否满足检索条件，若满足，则把它放入外部查询的结果集合。 重复上述步骤直到处理完外部查询的每一行候选行。 例： 查询与刘晨在同一个系学习的 学生。 Select Sno, Sname, Sdept From Student Where Sdept = (Select Sdept From Student Where Sname = „ 刘晨 ‟)。 查询选修了课程名为 „信息系统 ‟的学生的学号和姓名。 Select Sno, Sname From Student Where Sno IN (Select Sno From SC Where Cno IN (Select Cno From Course Where Cname = „ 信息系统 ‟))。 查询其他系中比信息系中某一学生年龄小的学生姓名和年龄。 Select Sname, Sage From Student Where Sdept „IS‟ And Sage Any(Select Sage From Student Where Sdept = „IS‟) 查询选择了 1 号课程的学生姓名。 Select Sname From Student Where Exists (Select * 第 21 页 共 58页 from SC Where Sno= And Cno=„1‟) 查询没有选择 1 号课程的学生姓名。 Select Sname From Student Where Not Exists (Select * from SC Where Sno= And Cno=„1‟) 查询平均成绩比 2 号课程平均成绩高的那些课程 Select Cno, AVG(Grade) From SC Group By Cno Having AVG(Grade)(SELECT AVG(Grade) From SC Where Cno=„2‟)。 3）集合查询 查询选修了 1 号或 2 号课程的学生 Select Sno From SC Where Cno=„1‟ Union Select Sno From SC Where Cno=„2‟ 查询选修了 1 号课程的学生和选修了 2 号课程的学生的交集 Select Sno From SC Where Cno=„1‟ Intersect Select Sno From SC Where Cno=„2‟。 第 22 页 共 58页 查询计算机系的学生与年龄不大于 19 岁的学生的差集 Select * from Student Where Sdept = „CS‟ Minus Select * from Student Where Sage 19。 第四节 更新 Insert语句 语法： Insert Into 表名 [(属性列 [{,属性列 }])] Values(值 [{,值 }]) 插入子查询结果 Insert Into 表名 [( 属性列 [{, 属性列 }])] 子查 询 例： Insert Into Student Values („95020‟, „陈冬 ‟, „IS‟, 18)。 Insert Into SC (Sno, Cno) Values(„95020‟, „1‟)。 Insert Into Dept_Age (Sdept, Avgage) Select Sdept, AVG(Sage) From Student Group By Sdept。 第五节 更新语句 Update 第 23 页 共 58页 语法 Update 表名 Set 列名 = 表达式 [{, 列名 = 表达式 }] [Where 条件 ]。