【数据库】Oracle数据库程序设计学习笔记（二）

查询语句

1）查询（DUAL 临时表）

     SELECT 列名1,列名2... FROM 表名;

//查询所有表
SELECT * FROM TAB| USER_TABLES;

2）运算符->算术运算符

     + - * / （数字，日期）

3）空值：无效的，未指定的（不可进行算术运算）

      NVL（空值列, 指定值）

4）连接符 ||

     列与列连接

     列与字符串连接

     单引号 ’ 表示字符串

5）去掉重复行

      DISTINCT 只能放在第一列前面

6）排序   

 ORDER BY 列名1 ASC | DESC, 列名2 ASC | DESC ...;

   //  ASC表示升序，DESC表示降序，默认ASC

7）给列名起别名     

SELECT 列名1 [as] 列别名 FROM 表名;

SELECT sal+1 “as” FROM emp;

8）过滤

 SELECT 列名 FROM 表名 WHERE 条件;

9）运算符->比较运算符

       =  >  <  >=  <=  <> !=

       BETWEEN AND   LIKE   IN   IS NULL

10）运算符->逻辑运算符

       AND  OR  NOT

       NOT LIKE   NOT IN   NOT BETWEEN AND

       LIKE % 表示任意数量任意字符

       _ 表示一个任意字符

       IN 表示某个范围内的任意值

       IS NULL 表示某个列为空值

11）交叉查询（即笛卡尔积）

SELECT 列名 FROM 表名1 CROSS JOIN 表名2;

12）自然连接（即笛卡尔积去重）

SELECT 列名 FROM 表名1 NATURAL JOIN 表名2;

13）using 子句

SELECT 列名 FROM 表名1 JOIN 表名2 USING(列名);

14）on 子句

SELECT 列名 FROM 表名1 JOIN 表名2 ON(关联条件);

15）左右连接

        左连接就是关联的表中，根据关联条件，显示匹配的记录，左表中有的记录，但是右表中没有匹配上的，以空 (null) 显示。

SELECT 列名 FROM 表名1 LEFT OUTER JOIN 表名2 ON(关联条件);

SELECT 列名 FROM 表名1 RIGHT OUTER JOIN 表名2 ON(关联条件);

//左连接：（+）在 = 的右边
//右连接：（+）在 = 的左边

16）自连接

        自连接就是把一张表通过取两个别名，从而当做两张表，并使其自己和自己关联。

SELECT  e1.*, e2.* FROM EMP e1, EMP e2 WHERE e1.empno=e2.empno;

17）子查询 (子查询可以嵌套在任何地方）

//查询年龄为20岁的员工部门
SELECT * FROM department WHERE depno IN (SELECT depno FROM employee WHERE age=20);

//查询是否存在年龄大于21岁的员工
SELECT * FROM department WHERE EXISTS (SELECT depno FROM employee WHERE age>21);

//查询满足条件的部门
SELECT * FROM department WHERE depno > ANY(SELECT depno FROM employee );

//查询满足条件的部门
SELECT * FROM department WHERE depno > ALL(SELECT depno FROM employee );

 

18）合并查询

        1、UNION 并集

        2、UNION ALL 不会去掉重复行

        3、INTERSECT 交集

        4、MINUS 差集

 

函数：

单行函数：字符、数值、日期、转换、通用函数

字符函数：

1）大小写控制函数（lower 大写、upper 小写、initcap 首字母大写其余小写）

2）字符控制函数（concat 连接、substr 截取、length 求长度、instr 查找、lpad 左填充、rpad 右填充、trim 左右截取）

 

数值函数：

1)   round 四舍五入、trunc 截断、mod 取余

 

日期函数：

1）sysdate 当前系统日期

2）日期可以进行算术运算

     日期 + 数字 = 日期

     日期 - 日期 = 天数

3）mouths_between 返回月数之差

4）add_mouths 添加指定月数

5）next_day 下一个指定星期几

6）last_day 指定日期的当前月最后一天

7）extract 提取日期

 

转换函数：

to_char 把其他类型转换为字符类型

1）日期的转换

      to_char(hiredate, ‘yyyy-mm-dd’)

      to_char(hiredate, ‘yyyy-mm-dd HH12:MM:SS am’)

      FM去掉前导0

2）数字的转换

      9 代表数字， $ 代表美元， L 代表本地货币符号

      to_number

      to_date

 

通用函数:

1）NVL（a,b） 如果 a 为空值，则返回 b 的值，否则，返回 a 的值；

2）NVL2 （a,b,c）若 a 不是空值返回 b，是空值返回 c；

3）DECODE(列，条件，结果，

                      条件，结果，

                      条件，结果，

                      ……，

                    【结果】

                      )

 

组函数及分组统计

组函数：COUNT() 返回匹配指定条件的行数, SUM () 求和, MAX (), MIN (), AVG () 平均值

分组统计：     

SELECT 列名1 [as] 列别名1，列名2 [as] 列别名2...

FROM 表名1 表别名1，表名2 表别名2...

WHERE 表与表关联条件 AND 过滤条件

GROUP BY 分组条件

HAVING 分组函数

ORDER BY 排序列名1 ASC | DESC, 排序列名2  ASC | DESC ...

ROLLUP 纵向小计和 CUBE 横纵向小计

;