Oracle之scott用户表、相关SQL语句

1.scott用户下的表结构：

雇员表EMP

No.

名称

类型

描述

1

EMPNO

NUMBER(4)

雇员编号

2

ENAME

VARCHAR2(10)

雇员姓名

3

JOB

VARCHAR2(9)

职位

4

MGR

NUMBER(4)

领导编号

5

HIREDATE

DATE

雇佣日期

6

SAL

NUMBER(7,2)

月薪，工资

7

COMM

NUMBER(7,2)

奖金，佣金

8

DEPTNO

NUMBER(2)

部门编号

部门表DEPT

No.

名称

类型

描述

1

DEPTNO

NUMBER(2)

部门编号

2

DNAME

VARCHAR2(14)

部门名称

3

LOC

VARCHAR2(13)

部门位置

工资等级表SALGRADE

No.

名称

类型

描述

1

GRADE

NUMBER

工资等级

2

LOSAL

NUMBER

最低工资

3

HISAL

NUMBER

最高工资

奖金表BONUS

No.

名称

类型

描述

1

ENAME

VARCHAR2(10)

雇员姓名

2

JOB

VARCHAR2(9)

职位

3

SAL

NUMBER

工资

4

COMM

NUMBER

奖金

2.SQL之SELECT（查询）语句：

i)查询语句格式：

SELECT * | 具体的列 别名 FROM 表名称;

ii)对个别表列的查询：

SELECT empno,ename,job FROM emp ;

iii)为返回列起个别名：

SELECT empno 编号,ename 姓名,job 工作 FROM emp ;

iv）使用DISTINCT直接消除所有的重复列：

SELECT {DISTINCT} * | 具体的列  别名  FROM 表名称 ;

v)对显示格式的设定：

要求： 编号是： 7369的雇员，姓名是：SMITH ，工作是：CLERK

实现这个功能，则可以使用Oracle提供的字符串连接操作，使用”||“ 表示。如果要加入一些显示信息的话，所有的其他的固定信息要用“‘“括起来。

SELECT ' 编号是：' || empno || '的雇员，姓名是： ' || ename || '，工作是： ' || job FROM emp ;

vi)在查询中也可以使用四则运算功能：

例如：求出每个雇员的姓名和年薪。

SELECT ename ,sal*12 FROM emp;

当然，也可以为列名 sal*12 起一个别名，注意回避中文：

SELECT ename ,sal*12 income FROM emp;

3.SQL之限定查询（WHERE字句）语句：

根据指定的条件查询，必须指定限定查询。限定的查询语法：

SELECT {DISTINCT} * | 具体的列 别名 FROM 表名称 {WHERE 条件(s)}

范例：

i)查询出工资大于1600的所有雇员信息：

SELECT * FROM emp WHERE sal>1600 ;

ii）查询每月可以得到奖金的雇员信息：

使用NOT

SELECT * FROM emp WHERE comm IS NOT NULL ;--奖金是comm字段，只需not null，就OK。

ii.v)同样查询每月没有奖金的雇员的信息：

SELECT * FROM emp WHERE comm IS  NULL ;

iii）AND-查询基本工资大于1000，同时可以领取奖金的雇员信息：

SELECT * FROM emp WHERE sal>1000 AND comm IS NOT NULL ;

iv）OR-查询，基本工资大于1000，或者可以领取奖金的 雇员信息。

SELECT * FROM emp WHERE sal>1000 OR comm IS NOT NULL ;

·使用NOT可以取反，把真的条件变为假的，假的变为真。

v)：BETWEEN…AND…

语法格式：字段 BETWEEN…最小值…AND…最大值…

SELECT * FROM empWHERE sal BETWEEN 1500 AND 3000 ;

以上等价于：

SELECT * FROM empWHERE sal >=1500 AND sal<=3000 ;

v.v）日期表示要加单引号”’“：

SELECT * FROM emp WHERE hiredate BETWEEN '1-1 月-81' AND '31-12月-81' ;

vi）IN

语法格式： 字段 IN (值1，值2，……，值n)

SELECT * FROM emp WHERE empno IN (7369,7499,7521) ;

也可以使用NOT取反：字段 NOT IN (值1，值2，……，值n)

SELECT * FROM emp WHERE empno NOT IN (7369,7499,7521) ;

不仅仅限于数字：

SELECT * FROM emp WHERE ename IN ('SMITH','ALLEN','KING') ;

vii）LIKE

在LIKE中主要使用两种通配符：

· ”%“可以匹配任意长度的内容

SELECT * FROM emp WHERE ename LIKE '_M%' ;

· ”_“可以匹配一个长度的内容

SELECT * FROM emp WHERE ename LIKE '%M%' ;

· '%%' 可以查询全部

SELECT * FROM emp WHERE ename LIKE '%%' ;

viii) <>和!=  不等于号

SELECT * FROM emp WHERE empno<>7369 ;

SELECT * FROM emp WHERE empno!=7369 ;

4. ORDER BY字句 

语法格式：

SELECT {DISTINCT} * |具体的列 别名 FROM 表名称{WHERE 条件(s)} {ORDER BY 排序的字段1, 排序的字段2 ASC|DESC} 

->ASC表示升序、 DESC表示降序

范例：对员工按照工资升序排序：

SELECT * FROM emp ORDER BY sal ASC ;

范例：查询出10部门所有员工信息，查询的信息按照工资由高到低排序，如果工资相等，则按照雇佣日期由早到晚排序：

SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC,hiredate ASC ;

注：排序操作是放到整个SQL语句最后才执行。