Oracle基础（一）

oracle数据库

前言    oracle sql

第一章  Selecting Rows

第二章  Sorting & Limiting Selected Rows

第三章  Single Row Functions

第四章  Displaying Data from Multiple Tables

第五章  Group Function

第六章  Subqueries

第七章  Specifying Variables at Runtime

第八章  Overview of Data Modeling and Database Design

第九章  Creating Tables

第十章  Oracle Data Dictionary

第十一章 Manipulating Data(DML)

第十二章 Altering Tables and Constraints

第十三章 Creating Sequences

第十四章 Creating View

第十五章 Creating Indexes

第十六章 Controlling User Access

前言

1.一个认知

认知什么是oracle？

oracle：商业运用第一的关系型数据库

实质：关系型数据库

了解oracle数据库发展历史(文档)

oracle8i  9i  10g  11g  12c不同字母代表的含义：

1998年9月，ORACLE公司正式发布ORACLE 8i。“i”代表Internet，这一版本中添加了大量为支持Internet而设计的特性;

2003年9月8日，ORACLE World大会上，“ORACLE 10g”发布，“g”代表“grid,网格”，这一版的最大的特性就是加入了网格计算的功能；

2013年6月26日，Oracle Database 12c版本正式发布, c是cloud,也就是代表云计算的意思。

2.二个概念

数据库：数据存储的仓库

60年代兴起，是计算机科学技术的一个重要分支。核心任务是管理数据，包括对数据分类、组织、编码、存储、检索和维护。

到现在经历了人工管理(1950之前)、文件系统(50年代后期到60年代中期)、数据库系统(60年代后期到现在)三个阶段。

关系型数据库(RDBMS)：基于关系模型来组织数据的数据库，[属于第二代数据库]。

关系模型：用一个二维表,行(记录)和列(字段)的形式来保存数据；关系模型里面的关系 主要反映到以后学习的主外键。

例如：

表名      s_dept部门

列(字段)  部门编号id  部门名称name

行(记录)  1          研发部

2   市场部

3   人事部

员工表s_emp  记录所有员工信息

id  name  dept_id salary  title

1  larry 1   15000  ceo

2  tom  1      13000  研发部经理

关系型数据库的简单理解是二维数据库，类似Excel表格,有行有列。这种数据库非高级，非性能最优，但应用最广泛，因为容易理解使用。

数据库系统发展历史：

第一代：层次和网状数据库

第二代：关系型数据库

第三代：对象型数据库(理论阶段，尚未大量应用)

具体信息可参阅 了解数据库发展历史(文档)。

关系型数据库优点：

容易理解，很贴近现实世界。

使用方便，SQL语句。（增删改查）

容易维护，丰富的完整性大大降低了数据冗余和数据不一致的概率。

关系完整性规则：

实体完整性 主键值唯一存在

参照完整性 外键值为空或取其他表中主键值

用户自定义完整性 符合应用场景中具体的约束条件

3.三个名词

sql: 结构化的查询语句，操作oracle数据库的语言  增删改查 

select * from table_name;

sqlplus: oracle软件自带的可以输入sql，且将sql执行结果显示的终端的一个工具。

注意区分sql语句和sqlplus语句。

pl/sql: 程序化的sql语句，在sql语句的基础上加入一定的逻辑操作，如if for...,使之成为一个sql块，完成一定的功能。

4.四种对象

table：表格，由行和列组成，列又称字段，每一行内容为表格的一条完整的数据。

view:  视图，一张表或者多张表的部分或者完整的映射，好比表格照镜子，镜子里面的虚像就是view。

除去常见的table和view两种常用对象以外，oracle数据库还支持如下四种对象：

sequence： 序列

index： 索引，提高数据的访问效率

synonym： 同义词，方便对象的操作

program unit：程序单元，pl/sql操作的对象

5.五种分类

sql语句可以分为五大类:

Data retrieval：数据查询

select

DML：数据操纵语言(行级操作语言)

操作的是表格当中一条一条的数据

insert update delete

DDL：数据定义语言(表级操作语言)

操作的内容为表格(对象)

create alter drop truncate rename

transaction control：事务控制

commit rollback savepoint

DCL：权限控制语言

grant revoke

*************************************

数据库安装配置 准备:

1.安装oracle数据库

1.1 最好默认按照到C盘

1.2 安装过程中有一步需要输入一个密码【oracle】,建议使用oracle作为密码,方便记忆

1.3 安装完成之后,打开系统服务,查看服务是否已经正常启动,具体情况参照文档:oracle系统服务.txt

1.4 打开cmd,输入sqlplus,然后回车查看是否能执行该命令

1.5 如果不能执行,则需要把安装目录里面的BIN里面配置到系统环境变了path中,然后重新打开一个cmd窗口即可

C:\oraclexe\app\oracle\product\10.2.0\server\BIN;

注意：oracle的卸载具体参照文档

2.登录oracle自带的管理系统,新建一个属于自己的账号

oracle自带管理系统登录地址:

http://127.0.0.1:8080/apex/

参照文档:oracle系统服务.txt

3.导入数据

用户创建好之后,使用sqlplus命令登录到oracle数据库中,然后把之后要使用到的表及其数据导入到数据库中.

参照文档:导入数据.txt

4.了解导入的三张表以及相互关系

s_emp 员工表

s_dept 部门表

s_region 地区表

s_customer  客户表

5.之后登录或者操作数据库,就可以使用这个新创建的用户了

====================================================================

第一章：select语句，数据查询操作

1.使用select语句查询某张表的所有数据内容

语法：

select *|{[distinct] col_name|expression [别名],...}

from tb_name;

注意：

语法中出现的中括号[]，表示该部分可有可无；

*：表示所有列，仅作为测试和学习使用，在企业用语中不出现，因为效率低下且可读性差；

col_name：列名，将需要查阅的数据字段列举出来，可以查看多列值，列名之间用‘,’进行分割；

s_emp ：员工信息表

s_dept：员工部门表

了解表结构： desc table_name

需求：查看s_dept表中的所有记录

select *

from s_dept;

select id,name,region_id

from s_dept;

练习：查看s_dept表中的所有记录的id和name

select id,name

from s_dept;

练习：查看所有员工的id，名字(last_name)和薪资(salary)

select id,last_name,salary

from s_emp;

SQL语句书写注意事项：

SQL语言大小写不敏感；

SQL可以写在一行或多行；

各子句一般分行写；

关键字不能缩写，也不能分行；

sql语句的最后要跟上';'结束;

使用缩进提高语句的可读性。

2.select语句可以对指定的列的所有值进行算术运算

语法：

select col_name 运算符 数字

from tb_name;

需求：查看每个员工的员工id，名字和年薪。

select id,last_name,salary*12

from s_emp;

练习：查看每个员工的员工id，名字和月薪涨100以后的年薪

select id,last_name,(salary+100)*12

from s_emp;

注意：*  /  +  -

select语句永远不对原始数据进行修改；

乘除的优先级高于加减；

优先级相同时，按照从左到右运算；

可以使用括号改变优先级。

3.给查询的列起别名

语法：

select old_column [as] 别名

from tb_name;

使用列的别名，便于计算；别名中可以【使用双引号】，以便于别名中包含空格或特殊的字符并区分大小写。

需求：查看员工的员工id，名字和年薪,年薪列名为annual

select id,last_name,salary*12 as annual

from s_emp;

4.使用||可以使得多列的值或者列和特殊的字符串合并到一个列进行显示

语法：

select col_name||'spe_char'||col_name

from tb_name

'spe_char':如果一个列的值要跟特殊的字符串连接显示，使用该语法。

需求：查看员工的员工id，全名

select id,first_name||last_name

from s_emp;

练习：查看员工的员工id，全名和职位名称，全名和职位名称合并成一列显示，且格式为：姓 名，职位名称

select id,first_name||' '||last_name||','||title as name

from s_emp;

5.对null值得替换运算

nvl()函数

nvl(a,b)函数作用为： 如果a为空返回b,否则返回a;

语法：

select nvl(col_name,change_value)

from tb_name;

需求：查看所有员工的员工id，名字和提成，如果提成为空，显示成0

select id,last_name,nvl(commission_pct,0) commission_pct

from s_emp;

注意：

空值是无效的，未指定的，未知的或不可预知的值；

空值不是空格，也不是0；

包含空值的表达式的值都为空值。

6.使用distinct关键词，可以将显示中重复的记录(行)只显示一条

语法：

select distinct col_name,col_name...

from tb_name;

注意1：distinct关键词只能放在select关键词后面

如：select id,distinct title

    from s_emp;

该语句语法错!!!!!

注意2：如果distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录。

test表：

id id2

1 2

1 3

2 4

3 4

3 4

select distinct id,id2

from test;

显示结果为：

id id2

1 2

1 3

2 4

3 4

需求：查看所有员工的职位名称和部门id，同职位同部门的只显示一次

select distinct title,dept_id

from s_emp;

--补充内容--

字符串

字符串可以是select 列表中的一个字符、数字、日期；

日期和字符只能在单引号中出现；

每当返回一行时，字符串被输出一次；

双引号表示别名。

7.sqlplus命令

使用sqlplus登录数据库

sqlplus 输入用户名  再输入密码

sqlplus 用户名  直接输入密码即可

sqlplus 用户名/密码 直接登录

sqlplus "/as sysdba" 超级管理员登录(很危险，  操作系统对应的用户才可以登录,在linux里面只有oracle用户才可以登录)

password 用户名  给用户改密码

show user 查看当前用户

$cls 清屏

$其他cmd命令

sqlplus登录之后,可以使用buff(缓存)来存储/执行/修改要执行的sql语句

这里的buff有以下特点:

a.buff中只能存储一条sql语句(但是这条sql语句可能有很多行)

b.每次放入新的sql语句,会把之前的覆盖掉

c.每次执行sql语句,都会把这个sql语句放到buff里面

和buff相关的命令有：

list(l) 查看缓存中的sql语句

append(a) 在[定位]的那一行后面追加新的内容

in(i)  在[定位]的那一行下面插入新的一行

change(c)  替换[定位]的那一行中的某些字符串

c/老的字符串/新的字符串

del 删除[定位]的那一行内容

n  后面加内容可以重写这一行

!  后面接终端命令 !clear:清屏(linux命令)

$ windows中使用$符号 例如:$cls

/  执行缓存sql命令

clear buffer:清空当前缓存的命令

save 路径 [replace] buff中的sql语句保存在指定文件中

get  test.sql  把test.sql中的内容在加载到buff中,但是没有运行

start test.sql  把test.sql中的内容在加载到buff中并且执行

@test.sql      把test.sql中的内容在加载到buff中并且执行

edit file_name  使用系统默认编辑器去编辑文件

desc describe

ed edit

c change

col column

for format

spool file_name 将接下来的sql语句以及sql的运行结果保存到文件中

sql1

result1

sql2

result2

...

spool off  关闭spool功能

exit：退出(sqlplus退出)

8.select id,last_name,first_name, salary, dept_id

  from s_emp

  Where rownum <=10;

  结果不好看，通过column使我们的显示界面好看。

COLUMN last_name FORMAT a15

可以简写为:

col last_name for a15

注意：

column大小写不区分，另外只能设置字符串类型格式；

column 没有改变数据表里数据，它只是改变显示的方式；

column不是sql关键字,而是sqlplus命令。

COLUMN last_name HEADING 'Employee|Name' FORMAT A15

给last_name取别名为Employee|Name , 竖杠代表换行；

A15表示十五个字节长，一短横杠就是一个字节长。

COLUMN salary JUSTIFY LEFT FORMAT $99,999.00

salary JUSTIFY LEFT : 仅仅改变列名显示为左齐；

FORMAT $99,999.00: 控制显示格式为前面加 $ 符, “，”为分隔符, 0或9代表数字(通配符)，0表示替换对齐数值,位数不足会补足,可以混合使用。

COLUMN start_date FORMAT A15 NULL 'Not hired'

如果start_date值为空的话，显示为’Not hired’;

Format后不能直接跟null, 要先a10或a15;

NULL 'Not hired'和nvl类似。

column columName 显示所有对列格式的设置情况(可以显示该列的格式设置，这里的列并不特定于某个表)

例如：

column last_name 显示对last_name列显示设置的情况

column last_name clear 删除对last_name列格式设置的情况

clear column 清除所有column的格式设置

注意:

数字列不能设置col a15格式

例如：col salary format a15

1234  column 99.99  -- > ######

格式设置不对，出错不能显示,只是显示####

sql语句和sqlplus命令区别

sql是一种语言

ANSI标准

关键字不能缩写

使用语句控制数据库中表的定义和表中的数据

sqlplus是一种环境

Oracle的特性之一

关键字可以缩写

命令不能改变数据库中的值

集中运行

第二章：排序和限制查询

*****排序************

1.排序:所谓排序，就是根据某个字段的值按照升序或者降序的情况将记录查询出来

语法：

select col_name,...

from tb_name

order by col_name [asc|desc],...

注意：

a. 排序使用order by字句

b. 该子句只对查询记录显示调整，并不改变查询结果，所以执行权最低，即最后执行

2.排序关键词：

asc：升序(默认，默认的意思是不加关键词的时候默认为生序排序)

desc：降序

3.如果有多个列排序，后面的列排序的前提是前面的列排好序以后有重复(相同)的值

例如：

id id2

1 2

2 3

3 4

4 1

4 2

语句：

select id,id2

from test

order by id [asc],id2 desc;

结果：

id id2

1 2

2 3

3 4

4 2

4 1

注意：

先排第一列，如果第一列有重复的值再排第二列，以此类推

需求：查看员工的id，名字和薪资，按照薪资的升序排序显示，如果薪资相同则按照id降序排列。

4.order by 后面还可以跟数字,表示使用select后面的第几个列进行排序

例如:

//使用last_name列进行排序

select last_name,salary

from s_emp

order by 1;

//使用salary列进行排序

select last_name,salary

from s_emp

order by 2 desc;

******限制查询**********************

1.限制查询，即指定查询条件进行查询

语法：

select col_name,...

from tb_name

where col_name 比较操作表达式

逻辑操作符(and|or)

      col_name 比较操作表达式

...

where (条件1 or 条件2) and 条件3

select last_name,dept_id,salary

from s_emp

where (dept_id != 10 or salary > 1000 ) and salary < 1500;

注意：

1).限制查询条件，使用where子句

2).条件可以多个，使用逻辑操作符和()进行条件的逻辑整合

3).where子句的优先级别最高

4).比较操作表达式由操作符和值组成

2.常见的操作符之 逻辑比较操作符

=  >  <  >=  <=

不等于:三个都表示不等于的意思(经常用的是!=)

!=  <>  ^=

需求：查看员工工资小于1000的员工id和名字

select id,last_name,salary

from s_emp

where salary < 1000;

注意:

字符和日期要包含在单引号内

字符大小写敏感，日期格式敏感；

默认的日期格式为 DD-MON-RR

修改日期格式:

select * from v$nls_parameters; //动态性能视图，数据字典

alter session|system set NLS_DATE_FORMAT='yyyy-mm-dd';

      当前    所有

select [distinct] 字段1 as 别名，表达式

from 表

where ><>=<=!=  <>  ^=

order by 字段 asc|desc，字段 [asc];

||  concat();

3.sql比较操作符

[NOT] BETWEEN ... AND...

 [NOT] IN(list)

 [NOT] LIKE   

 IS [NOT] NULL 

between x and y：在什么范围之内 [x,y]

需求：查看员工工资在700 到 1500之间的员工id，和名字

select id,last_name,salary

from s_emp

where salary between 700 and 1500;

也可以在日期列上使用between and操作,但是要看当前session会话的语言环境来决定使用中文格式的日期还是英文格式的日期

alter session set nls_language='simplified chinese';

如果是中文的语言环境:

查询在90年3月8号到91年2月9号之间入职的员工信息

select id,last_name,start_date

from s_emp

where start_date between '08-3月-90' and '09-2月-91';

alter session set nls_language=english;

如果是英文的语言环境:

查询在90年3月8号到91年2月9号之间入职的员工信息

select id,last_name,start_date

from s_emp

where start_date between '08-MAR-90' and '09-FEB-91';

4.in(list)

在一个列表中筛选

需求：查看员工号1,3,5,7,9员工的工资

select id,last_name,salary

from s_emp

where id in (1,3,5,7,9);

需求：查看是在'08-3月-90'或者'09-2月-91'入职的员工信息

select id,last_name,start_date

from s_emp

where start_date in ('08-3月-90','09-2月-91');

需求：查看名字为Ngao或者Smith的员工信息

select id,last_name,salary

from s_emp

where last_name in ('Ngao','Smith');

5.like:模糊查询

即值不是精确的值的时候使用

通配符，即可以代替任何内容的符号

% ：通配0到多个字符     

_ : 当且仅当通配一个字符 

转义字符：

默认为\,可以指定 指定的时候用escape  符号指明即可,转义字符只能转义后面的一个字符

也可以使用其他字符作为转义字符 +

'%+_%' escape '+';

需求：查看员工名字以C字母开头的员工的id，工资。

select id,last_name,salary

from s_emp

where last_name like 'C%';

练习：查看员工名字长度不小于5，且第四个字母为n的员工id和工资

select id,last_name,salary

from s_emp

where last_name like '___n_%';

需求：查看员工名字中包换一个_的员工id和工资

注意:_是一个特殊字符,所以要转义

select id,last_name,salary

from s_emp

where last_name like '%\_%' escape '\';

6.is null

对null值操作特定义的操作符，不能使用=

需求：查看员工提成为空的员工的id和名字

select id,last_name,commission_pct

from s_emp

where commission_pct is null;

7.逻辑操作符

当条件有多个的时候使用

and：且逻辑

or： 或逻辑

注意：and优先级比or优先级要高

not：非逻辑 

  NOT BETWEEN AND

NOT IN

NOT LIKE

  IS NOT NULL

需求：查看员工部门id为41且职位名称为Stock Clerk(存库管理员)的员工id和名字

select id,last_name,dept_id,title

from s_emp

where dept_id = 41

and

title = 'Stock Clerk';

练习：查看(员工部门为41) 或者 (44号部门 且工资大于1000的)员工信息

select id,salary,dept_id

from s_emp

where dept_id = 41

or dept_id = 44

and salary>1000;

查看员工部门为41或44 且工资大于1000的员工信息

select id,salary,dept_id

from s_emp

where (dept_id = 41

or dept_id = 44)

and salary > 1000;

需求：查看员工提成不为空的员工信息

select id,last_name,commission_pct

from s_emp

where commission_pct is not null;

需求：查看员工名字不是以C字母开头的员工信息。

select id,last_name,salary

from s_emp

where last_name not like 'C%';

*****单值函数*****************

第三章：单值(单列、单行)函数

1.函数分为两大类

单值函数

a.字符函数

b.日期函数

c.转换函数

d.数字函数

分组函数(后面章节学习)

基础知识准备：哑表、伪表dual

dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。数据库中所有用户都有权限使用。

只是为了满足语法要求。

例如:

显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法；

select 1+1 from dual;

2.字符函数

LOWER Converts to lowercase

UPPER Converts to uppercase

INITCAP Converts to initial capitalization

CONCAT Concatenates values

SUBSTR Returns substring

LENGTH Returns number of characters

NVL Converts a null value

以上这些函数可以操作表中的列，也可以操作普通字符串。

1).lower 把字符转为小写

例如:把'HELLO'转换为小写

select lower('HELLO')

from dual;

例如:把s_emp表中的last_name列的值转换为小写

select lower(last_name)

from s_emp;

2).upper 把字符转换为大写

例如:把'world'转换为大写

select upper('world')

from dual;

例如:把s_emp表中的last_name列的值转换为大写

select upper(last_name)

from s_emp;

例如:查询s_emp表中名字为Ngao的人信息

按照以下操作是查不到的:

select last_name,salary,dept_id

from s_emp

where last_name='NGAO';

修改后就可以查询到了:

select last_name,salary,dept_id

from s_emp

where upper(last_name)='NGAO';

3).initcap 把字符串首字母转换为大写

例如:把'hELLO'转换为首字母大写,其余字母小写

select initcap('hELLO')

from dual;

4).concat 把俩个字符串连接在一起(类似之前的||的作用)

例如:把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg

select concat('hello','world') msg

        from dual;

例如:把first_name和last_name俩个列的值连接到一起

select concat(first_name,last_name) as name

        from s_emp;

5).substr(操作项,start,len) 截取字符串

例如:截取'hello'字符串,从第2个字符开始(包含第二个字符),截取后面连续的3个字符

select substr('hello',2,3)

from dual;

6)length 获得字符串长度

例如:获得'world'字符串的长度

select length('world')

from dual;

例如:获得s_emp表中last_name列的每个值的字符长度

select length(last_name)

from s_emp;

7).nvl(操作项,替换值) 替换列中为null的值

在前面的章节已经使用过了

select last_name,nvl(commission_pct,0)

from s_emp;

3.数字函数

ROUND Rounds value to specified decimal

TRUNC Truncates value to specified decimal

MOD Returns remainder of division 

1)round 四舍五入

round(arg1,arg2)

第一个参数表示要进行四舍五入操作的数字

第二个参数表示保留到哪一位

例如:

保留到小数点后面2位

select round(45.926,2)

from dual;

保留到个位 (个十百千万...)

select round(45.923,0)

from dual;

保留到十位 (个十百千万...)

select round(45.923,-1)

from dual;

2).trunc 截取到某一位

trunc(arg1,arg2)

和round的用法一样,但是trunc只舍去不进位

例如:

截取到小数点后面2位

select trunc(45.929,2)

from dual;

截取到个位 (个十百千万...)

select trunc(45.923,0)

from dual;

截取到十位 (个十百千万...)

select trunc(45.923,-1)

from dual;

3).mod 取余

mod(arg1,arg2)

第一个参数表示要进行取余操作的数字

第二个参数表示参数1和谁取余

例如:

把10和3进行取余 (10除以3然后获取余数)

select mod(10,3)

from dual;

4.日期函数

MONTHS_BETWEEN  Number of months between two dates

ADD_MONTHS   Add calendar months to date

NEXT_DAY Next day of the date specified

LAST_DAY     Last day of the month

ROUND         Round to date at midnight

TRUNC         Remove time portion  from date

  1).sysdate关键字

表示系统的当前时间

例如:

显示当前系统时间

select sysdate from dual;

注意:sysdate进行加减操作的时候,【单位是天】

例如:

显示时间:明天的这个时候

select sysdate+1 from dual;

例如:

显示时间:昨天的这个时候

select sysdate-1 from dual;

例如:

显示时间:1小时之后的这个日期

select sysdate+1/24 from dual;

2).months_between

俩个日期之间相差多少个月【单位是月】

例如:

30天之后和现在相差多少个月

select months_between(sysdate+30,sysdate)

from dual;

【结果可以是小数】

select months_between(sysdate+10,sysdate)

from dual;

3).add_months  返回一个日期数据:表示一个时间点,往后推x月的日期

例如:

'01-2月-2016'往后推2个月

select add_months('01-2月-2016',2)

from dual;

例如:

当前时间往前推4个月

select add_months(sysdate,-4)

    from dual;

注意:这个数字也可以是负数,表示往前推x月

4).next_day

返回日期:表示下一个星期几在哪一天

例如:

离当前时间最近的下一个星期5是哪一个天

select next_day(sysdate,'星期五')

from dual;

注意: 如果要使用'FRIDAY',那么需要把当前会话的语言环境修改为英文

5).last_day

返回日期:表示指定月份的最后一天

例如:

当前日期所在月份的最后一天(月底)

select last_day(sysdate)

from dual;

6).round 

对日期进四舍五入,返回操作后的日期数据

例如:

把当前日期四舍五入到月

select round(sysdate,'MONTH')

from dual;

测试: 15号16号分别是舍弃还是进位

结论: 15不进，16进

把当前日期四舍五入到年(6月舍弃，7月进位)

select round(sysdate,'YEAR')

from dual;

//使用默认的日期格式进行四舍五入会出错

//因为数字函数也有一个round，俩个ronnd函数有冲突

select round('01-2月-16','MONTH')

from dual;

7).trunc 

对日期进行截取 和round类似,但是只舍弃不进位

5.类型转换函数

TO_CHAR

converts a number or date string to a character string.

 TO_NUMBER

converts a character string containing digits to a number.

  TO_DATE

converts a character string of a date to a date value.

1).to_char 把日期转换为字符

例如:把当前日期按照指定格式转换为字符串

select to_char(sysdate,'yyyy')

from dual;

日期格式：

  yyyy：四位数的年份   

  rrrr：四位数的年份

  yy：两位数的年份     

  rr：两位数的年份  yyyy-mm-DD hh:mi:ss

  mm：两位数的月份(数字)

  D：一周的星期几

  DD：一月的第几天

  DDD ：一年的第几天

  YEAR：英文的年份

  MONTH：英文全称的月份【中文，和语言环境有关】

  mon：英文简写的月份

  ddsp：英文的第几天(一个月的)

  ddspth：英文序列数的第几天(一个月的)

  DAY：全英文的星期

  DY：简写的英文星期

  hh[12|24]：小时

  mi：分钟

  ss：秒

  am: 上下午

例如:

测试常见的一些日期数据转换为字符串的格式

select to_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY')

from dual;

select to_char(sysdate,'dd-mm-yy')

from dual;

select to_char(sysdate,'yy-mm-dd')

from dual;

select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM')

from dual;

千年虫问题:

在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫

数据库中表示日期中年份的有俩种: yy和rr

之前一直使用的时候yy格式,后来才有的rr格式

yy表示使用一个俩位数表示当前年份:

1990 ---yy数据库格式---> 90

1968 ---yy数据库格式---> 68

1979 ---yy数据库格式---> 79

rr格式表示: 另外参照图片:rr日期格式规则.png

如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：

规则1

如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。

规则2

如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。

规则3

如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。

规则4

如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。

注意:rr格式并没有完全的解决俩位数年份保存的问题,只是将问题往后推了50年。

2).to_char 把数字转换为字符

格式: to_char(number,'fmt')

0：表示强制显示小数点后精度

9: 表示显示数字

.: 表示小数点

,：千位标识符

L: 表示系统本地的货币符号

$: 美元货币

例如:

    select to_char(salary,'$999,999.00')

from s_emp;

【fm】表示【去除】结果显示中的【开始的空格】

select to_char(salary,'fm$999,999.00')

from s_emp;

L表示系统本地的货币符号

select to_char(salary,'fmL999,999.00')

from s_emp;

3).to_number 把字符转换为数字

例如:

select to_number('0100')

from dual;

//这个写法是错的 abc不能转换为数字

select to_number('abc')

from dual;

4).to_date 把字符转换为日期

例如:

select to_date('10-12-2016','dd-mm-yyyy')

from dual;

select to_date('25-5月-95','dd-month-yy')

from dual;

select to_date('95/5月/25','yy/month/dd')

from dual;

//session语言环境设置为英文下面可以运行

select to_date('25-MAY-95','dd-MONTH-yy')

from dual;

5).总结

oracle数据库中表示一个日期数据的方式有:

  a.使用sysdate

  b.使用oracle默认的日期格式 例如:'25-MAY-95' '25-9月-95'

  c.使用日期函数ADD_MONTHS/NEXT_DAY/LAST_DAY/ROUND/TRUNC

  d.使用转换函数to_date

6).函数之间的嵌套

  格式:F3(F2(F1(arg0,arg1),arg2),arg3)

  例如:

  先把'hello'和'world'连接起来,再转换为字母大写然后再从第4个字符开始,连着截取4个字符

  select substr(upper(concat('hello','world')),4,4)

  from dual;

*****重点**********

第四章：多表查询

多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，数据通过特定的连接进行联合显示.

1.笛卡尔积

在数学中，将两个集合X和Y任意组合，得到的结果叫做笛卡尓积(Cartesian product)，又称直积，表示为X × Y。

例如集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积。

例如:

select last_name,name

from s_emp,s_dept;

2.连接查询

为了在多表查询中避免笛卡尔积的产生,我们可以使用连接查询来解决这个问题.

连接查询分为:

a.等值连接

b.不等值连接

c.外连接

左外连接

右外连接

全连接

d.自连接

3.等值连接

利用一张表中某列的值和另一张表中某列的值相等的关系,把俩张表连接起来。

一般主外键 关联。

例如:查询员工的名字、部门编号、部门名字

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id;

为了表述的更加清楚,可以给每张表起别名

select se.last_name,se.dept_id,sd.id,sd.name

from s_emp se,s_dept sd

where se.dept_id=sd.id;

查询部门的id,名称以及所在区域的名称；

select sd.id,sd.name,sr.id,sr.name

from s_dept sd,s_region sr

where sd.region_id=sr.id;

4.不等值连接(连接两张表，但并非使用等号实现)

一般针对  非主外键  的多张表

假设数据库中还有一张工资等级表:salgrade

工资等级表s_grade:

gradeName 列表示等级名称

losal 列表示这个级别的最低工资数

hisal    列表示这个级别的最高工资数

create table s_grade(

id number(7) primary key,

gradeName varchar2(30) not null,

losal number(7),

hisal number(7)

);

insert into s_grade values(1,'初级程序员',700,1200);

insert into s_grade values(2,'中级程序员',1201,2000);

insert into s_grade values(3,'高级程序员',2001,3000);

commit;

//删除表

drop table s_grade;

表中的数据类似于下面内容:

表s_grade

id  gradeName losal hisal

1  初级程序员  700  1200

2  中级程序员  1201  2000

3  高级程序员  2001  3000

例如:

查询出员工的名字、职位、工资、工资等级名称

SELECT  e.last_name, e.title, e.salary, s.gradeName

FROM    s_emp e, s_grade s

WHERE  e.salary BETWEEN s.losal AND s.hisal;

5.外连接

外连接分为:左外连接 右外连接 全连接

先分别在俩s_emp和s_dept表中插入新的数据

特点:新员工tom不在任何部门,新增部门st下面没有任何员工

insert into s_emp(id,last_name) values(26,'tom');

insert into s_dept(id,name) values(60,'st');

commit;

等测试完以后可以通过下面sql语句删除上述插入的数据：

delete from s_emp where id=26;

delete from s_dept where id=60;

commit;

这个时候再使用等值连接的话,查询出来的数据就会少,因为新增的员工tom和部门表中的数据连接不上,当然新增的部门st也和员工表中的数据连接不上.那么这俩条数据都是在等值连接中查询不出来.

6.左外连接

使用左外连接可以解决上述问题，在等值连接的基础上，额外显示左表中多出来的数据。

例如:

查询所有员工 以及对应的部门的名字,没有部门的员工也要显示出来

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+);

或者 俩者是等价的

select last_name,dept_id,name

from s_emp left outer join s_dept

on s_emp.dept_id=s_dept.id;

注意:outer可以省去不写

7.右外连接

例如:

查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

select last_name,dept_id,name

from s_emp right outer join s_dept

on s_emp.dept_id=s_dept.id;

注意:outer可以省去不写

8.全连接

全连接可以将两个表中额外多余的数据都显示出来。

例如:

查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来,没有部门的员工也要显示出来

select last_name,dept_id,name

from s_emp full outer join s_dept

on s_emp.dept_id=s_dept.id;

注意:outer可以省去不写

9.自连接

两张相同的表，进行连接

例如:

查询每个员工的名字以及员工对应的管理者的名字

select s1.last_name,s2.last_name manager_name

from s_emp s1,s_emp s2

where s1.manager_id = s2.id;

s_emp s_emp

1 zs 2 1 zs 2

2 ls  3 2 ls 3

3 ww  3 ww

10.对查询结果集(ResultSet rs)的操作

结果集: sql语句查询得到的结果。

如果有俩条sql语句,每一条sql都可以查询出一个结果,这个被称之为结果集。那么我们可以使用下面的关键字对俩个结果集进行操作。

union 获得俩个结果集的并集

union all 把俩个结果集 合在一起显示出来

minus    第一个结果集除去第二个结果集和它相同的部分

intersect 获得俩个结果集的交集

注意:前提条件 俩个结果集中【查询的列】要完全一致

1).union  获得俩个结果集的【并集】

(两个结果集公共部分+左表额外+右表额外)

例如:

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

union

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

2).union all  把俩个结果集 合在一起显示出来

例如:

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

union all

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

3).minus  第一个结果集除去第二个结果集和它相同的部分

例如:

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

minus

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

对比俩种情况的结果

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id

minus

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+);

4).intersect  求俩个结果集的【交集】(公共部分)

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id(+)

intersect

select last_name,dept_id,name

from s_emp,s_dept

where s_emp.dept_id(+)=s_dept.id;

11.oracle中的伪列 rownum

伪列rownum，就像表中的列一样，但是在表中并不存储。伪列【只能查询】，不能进行增删改操作。它会根据返回的结果为每一条数据生成一个【序列化】的数字。

rownum是oracle特有的。

rownum 所能作的操作有以下三种:

1).rownum 能等于1

如果让其等于其他数 则查不到数据

例如:

select rownum,id,last_name

from s_emp

where rownum=1;

2).rownum 能大于0

如果让其大于其他数 则查不到数据

rownum>=1也是可以的

例如:

select id,last_name

from s_emp

where rownum>=1;

3).rownum 可以<=任何数

例如:

select id,last_name

from s_emp

where rownum<=7

实际应用:

学完子查询后，可以利用伪列进行分页显示。

select *

from (

select rownum r, last_name,id

from s_emp

where rownum <= 20

order by id

)

where r >= 10;

注意: 伪列的次序是在排序之前就已经确定的。

第五章：组函数

group by 在查询表中数据的时候进行分组的关键字

思考:为什么要对数据进行分组

having  分组之后的进行进一步数据筛选的关键字

having和where的功能类似

1.组函数(分组函数)

组函数指将数据按照某列的值进行分组后，然后使用组函数分别对每个分好的小组中的数据进行处理；

注意: 组函数一般要结合着分组关键字group by来使用。

具体组函数:

avg      求平均值

count    计算有多少条数据

max      最大值

min      最小值

sum      求和

stddev    标准差

variance  方差

如果使用组函数的时候没有出现group by子句，则将表中所有数据作为一个大组，进行处理。

2.具体实例

使用组函数，但是不结合group分组使用

注:如果【不使用group by分组】的话,那么默认当前查询到的【所有行数据是一组】。

例如:

查询s_emp表中所有员工的平均工资

select avg(salary)

from s_emp;

查询s_emp表中共有多少条数据

select count(*)

from s_emp;

查询s_emp表中所有员工中的最大工资

select max(salary)

from s_emp;

查询s_emp表中所有员工中的最小工资

select min(salary)

from s_emp;

查询s_emp表中所有员工的工资总和

select sum(salary)

from s_emp;

查询s_emp表中所有员工的工资的标准差

select stddev(salary)

from s_emp;

查询s_emp表中所有员工的工资的方差

select variance(salary)

from s_emp;

3.sql语句完整构成与执行步骤

select  ...

from ...

where ...(遍历整个表，逐行筛选)

group by ...

having ...(组级别的筛选)

order by ...

注:除了select和from之外其他的都不是必须的。

sql语句执行顺序:

1).from

2).where 

3).group by分组

4).执行组函数

5).having筛选

6).order by

注意：

分组查询时，select后面除了可以跟分组函数，还可以跟组级别的信息【group by后面的列】;

如果列没有出现在group by后面，则该列不可以放在select后面；

在select后面出现的内容，除了分组函数外，其他的列必须出现在group by后面。

4.具体实例2

使用组函数，同时结合group by分组使用。

例如:

查询s_emp表中每个部门的平均工资

select dept_id,avg(salary)

from s_emp

group by dept_id;

查询s_emp表中每个部门员工的最高工资

select dept_id,max(salary)

from s_emp

group by dept_id;

查询s_emp表中每个部门员工的工资总和

select dept_id,sum(salary)

from s_emp

group by dept_id;

查询s_emp表中每个部门员工的人数

select dept_id,count(*)

from s_emp

group by dept_id;

分组之后使用各种组函数

select dept_id,count(*),min(salary),max(salary),sum(salary),avg(salary)

from s_emp

group by dept_id;

5.组函数可以出现的位置

a.select后面

b.having后面

c.order by后面

d.where后面一定【不能】出现组函数(如果出现了，调整到having后面即可)

【特别注意:】

如果select/having语句后面出现了组函数,

那么select/having/order by后面那些【没有被组函数修饰的列】，

就【必须放在group by】后面。

求部门平均工资大于1000的信息,并按照部门|平均工资排序输出

select se.dept_id,sd.name,avg(salary)

from s_emp se,s_dept sd

where se.dept_id=sd.id

group by se.dept_id,sd.name

having avg(salary)>1000

order by sd.id;

6.where和having区别

a.where和having都是做条件筛选的

b.where执行的时间比having要早

c.where后面不能出现组函数(组函数执行次序比where晚)

d.having后面可以出现组函数

e.where语句要紧跟from后面

f.having语句要紧跟group by后面

group by和having的关系:

  1.group by可以单独存在,后面可以不出现having语句

  2.having不能单独存在,有需要的话,必须出现在group by后面

order by语句

  1.如果sql语句中需要排序,那么就一定要写在sql语句的最后面

  2.order by后也可以出现组函数

7.具体实例3

使用组函数，结合group分组以及having筛选使用

例如:

查询s_emp表中部门的平均工资大于等于1400的部门

select dept_id,avg(salary)

from s_emp

group by dept_id

having avg(salary)>=1400;

思考:上面的sql语句修改为如下,是否可以?

select dept_id,avg(salary)

from s_emp

where avg(salary)>=1400

group by dept_id;

查询s_emp表中部门的总工资大于等于4000的部门

select dept_id

from s_emp

group by dept_id

having sum(salary)>4000;

8.综合实例

使用组函数:其他实例

例1: 查询s_emp表中部门的平均工资大于等于1400的部门,并且显示出这些部门的名字,同时按照部门编号进行排序

第一步:查询出基本需求

select dept_id,avg(salary)

from s_emp

group by dept_id

having avg(salary)>=1400

order by dept_id;

第二步:加入多表查询,并且分别给表起别名

select se.dept_id,avg(se.salary)

from s_emp se,s_dept sd

group by se.dept_id

having avg(se.salary)>=1400

order by se.dept_id;

第三步:查询出s_dept表中的部门名称,并且进行等值连接

select se.dept_id,avg(se.salary),sd.name

from s_emp se,s_dept sd

where se.dept_id = sd.id

group by se.dept_id

having avg(se.salary)>=1400;

order by se.dept_id;

第四步:select语句后出现了组函数,那么没有被组函数修饰的列放到group by分组后面

select se.dept_id,avg(se.salary),sd.name

from s_emp se,s_dept sd

where se.dept_id = sd.id

group by se.dept_id,sd.name

having avg(se.salary)>=1400

order by se.dept_id;

思考:是否可以把where后的条件筛选 转移 到having语句后面?

答案是可以的(无非条件筛选时间延迟)，但需要注意语法规则。

（注意，having后面没有组函数修饰的列，需要放到group by后面）

select se.dept_id,avg(se.salary),sd.name

from s_emp se,s_dept sd

group by se.dept_id,sd.name,sd.id

having avg(se.salary)>=1400

and se.dept_id=sd.id

order by se.dept_id;

9.多表查询案例

查询所有员工信息，同时显示所在部门名称；

查看所有员工信息，同时显示所在区域名称；

查看区域id和名字，同时显示该区域所有员工总工资；

查看每个区域员工的人数；

查询南美地区的部门数；

查询南美地区工资大于1400的员工的信息；

select se.id,se.salary,sd.name,sr.name

from s_emp se,s_dept sd,s_region sr

where se.dept_id=sd.id

and sr.id=sd.region_id

and lower(sr.name)='south america'

and se.salary>1400;

第六章：子查询(嵌套查询)

子查询，即一个select语句中嵌套了另外的一个或者多个select语句。

子查询的第一种格式:

select ...

from ...

where 列 = (select ...)

group by ...

having 列 = (select ...)

order by ...;

1.基本应用

例1：查询工资比Smith工资高的员工信息

1).提取主句

select id,last_name,salary

from s_emp

where salary>(?);

2).提取子句（求Smith的工资）

select salary

from s_emp

where last_name='Smith';

3).组合

select id,last_name,salary

from s_emp

where salary>(

select salary

from s_emp

where last_name='Smith'

);

例2：查询以N开头，名字由四个字符组成，该员工所在部门员工的基本信息

1).主句 查询员工基本信息

select id,last_name,salary,dept_id

from s_emp

where dept_id in (?);

2).子句 获取到对应的部门

select dept_id

from s_emp

where last_name like 'N___';

3).组合

select id,last_name,salary,dept_id

from s_emp

where dept_id in (

select dept_id

from s_emp

where last_name like 'N___'

);

例3：查询平均工资比 41号部门的平均工资 高 的部门中员工的信息

1).先分析子句  求41部门平均工资

select avg(salary)

from s_emp

where dept_id=41;

select avg(salary)

from s_emp

group by dept_id

having dept_id=41;

1247.5

2).找avg(salary)>1247.5的那些部门

select dept_id

from s_emp

group by dept_id

having avg(salary)>1247.5;

3).提取上述部门中员工的信息

select id,last_name,dept_id

from s_emp

where dept_id in (?);

4).组合

select id,last_name,dept_id

from s_emp

where dept_id in (

select dept_id

from s_emp

group by dept_id

having avg(salary)>(

select avg(salary)

from s_emp

where dept_id=41

)

);

例4：查询比Smith所在部门最高工资 高 的员工的信息

1).求部门

select dept_id

from s_emp

where last_name='Smith';

2).求最高工资

select max(salary)

from s_emp

where dept_id=(?);

3).提取员工信息

select id,last_name,salary

from s_emp

where salary > (?);

4).组合

select id,last_name,salary

from s_emp

where salary > (

select max(salary)

from s_emp

where dept_id=(

select dept_id

from s_emp

where last_name='Smith'

)

);

例5：查询比Ngao所在部门平均工资高的部门的员工信息

1).求Ngao所在部门

select dept_id

from s_emp

where last_name='Ngao';

41

2).求41部门平均工资

select avg(salary)

from s_emp

where dept_id=41;

1247.5

3).找部门(平均工资>1247.5)

select dept_id

from s_emp

group by dept_id

having avg(salary)>1247.5;

4).提取员工信息

select id,last_name,dept_id

from s_emp

where dept_id in (

select dept_id

from s_emp

group by dept_id

having avg(salary)>1247.5

);

查看员工名字为Chang的员工所在部门的名称

查看所有和Chang不同部门的员工信息

查询和Chang相同职位的其他员工信息

查询公司里面工资比 平均工资 低的员工的信息

查看Chang员工所在部门其他员工薪资总和

查看部门平均工资大于32号部门平均工资的部门id

查看薪资大于Smith所在部门平均薪资的员工

查看薪资高于Chang员工经理薪资的员工信息

查看薪资高于 Chang员工经理所在区域最低工资 的员工信息

查看部门平均工资大于Chang所在部门平均工资的员工信息

查询工资大于41号部门平均工资的员工，且该员工所在部门的平均工资也要大于41号部门的平均工资

查看员工的基本信息(id,last_name,salary,部门名称,区域名称)，要求这些员工 薪资大于Chang所在部门的平均工资或者跟Chang不在同一个部门。

select se.id,se.last_name,se.salary,sd.name,sr.name

from s_emp se,s_dept sd,s_region sr

where se.dept_id=sd.id

and sd.region_id=sr.id

and (se.salary>1050

or

sd.id!=44);

Chang部门平均工资为 1050

select avg(salary)

from s_emp

group by dept_id

having dept_id=(?);

Chang所在部门 44

select dept_id

from s_emp

where last_name='Chang';

*************************************

以下是子查询第二种情况

select

from (select ...)

where 列>(select...)

group by

having 列<(select ...)

order by;

注意：

子查询其实可以分为两种情况，第一种就是上述用法：在查询过程中，需要另一个select查询到的结果,直接拿来用；第二种用法相比较更为复杂.

在查询过程中需要用来一张不存在的表，这张表需要用户通过select子句查询的结果进行构建，然后再利用这张构建的表实现最终的查询。

注意:(可以把子查询的结果作为一张表来使用)

01.查询员工信息,这些员工的工资要比自己所在部门的平均工资高

部门平均工资表s_avgs   

id  avgs

10 1400

31 1250

44 1050

41 1247.5

1 1200 10 10 1400

2 800 10 10 1400

3 1100 44 44 1050

4 970 41 41 1247.5

1).获取员工部门平均工资表

select dept_id id,avg(salary) avgs

from s_emp

group by dept_id;

2).写出完整功能

select se.id,se.salary,se.dept_id,sa.avgs

from s_emp se,(?) sa

where se.dept_id = sa.id

and se.salary>sa.avgs;

3).组合:

select se.id,se.salary,se.dept_id,sa.avgs

from s_emp se,(

select dept_id id,avg(salary) avgs

from s_emp

group by dept_id

) sa

where se.dept_id = sa.id

and se.salary>sa.avgs;

02.查询员工信息,这些员工的工资要比自己所在部门的平均工资高,同时显示部门的名称以及所在地区

【在上题的基础上，添加s_dept和s_region多表连接查询】。

select se.id,se.salary,sa.avgs,sd.name,sr.name

from s_emp se,(

select dept_id id,avg(salary) avgs

from s_emp

group by dept_id

) sa,s_dept sd,s_region sr

where se.dept_id=sa.id

and se.dept_id=sd.id

and sd.region_id=sr.id

and se.salary>sa.avgs;

03.查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资

a.41部门平均工资

select avg(salary)

from s_emp

where dept_id=41;

1247.5

b.找部门，平均工资高于1247.5

select dept_id

from s_emp

group by dept_id

having avg(salary) > 1247.5;

10,31,32,33,35,50

c.找以上部门 中员工信息

select se.last_name,se.dept_id

from s_emp se

where se.dept_id in (10,31,32,33,35,50);

d.准备部门平均工资表

select dept_id,avg(salary) avgs

from s_emp

group by dept_id;

e.同时显示 员工所在部门平均 工资

select se.last_name,se.dept_id,sa.avgs

from s_emp se,(

select dept_id,avg(salary) avgs

from s_emp

group by dept_id

) sa

where se.dept_id in (10,31,32,33,35,50)

and sa.dept_id = se.dept_id;

f.组合

select se.last_name,se.dept_id,sa.avgs

from s_emp se,(

select dept_id,avg(salary) avgs

from s_emp

group by dept_id

) sa

where se.dept_id in (

select dept_id

from s_emp

group by dept_id

having avg(salary) > (

select avg(salary)

from s_emp

where dept_id=41

)

)

and sa.dept_id = se.dept_id;

04.查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资,  同时显示出部门的名字

求41部门平均工资 1247.5

select avg(salary)

from s_emp

where dept_id=41;

求部门

select dept_id

from s_emp

group by dept_id

having avg(salary)>1247.5;

显示员工信息

select se.id,se.last_name,se.dept_id,sd.name,sa.avgs

from s_emp se,s_dept sd,(

select avg(salary) avgs,dept_id id

from s_emp

group by dept_id

) sa

where se.dept_id=sd.id

and se.dept_id in (10,31,32,33,35,50)

and sa.id=sd.id;

05.查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高

1).找Ngao所在部门

select dept_id

from s_emp

where last_name='Ngao';

41

2).求Ngao所在部门平均工资

select avg(salary)

from s_emp

where dept_id=(?);

1247.5

3).提取员工信息(工资高于？)

select id,last_name,salary

from s_emp

where salary>(1247.5);

4).准备部门平均工资表

select dept_id id,avg(salary) avgs

from s_emp

group by dept_id;

5).添加  该员工部门平均工资高于1247.5的筛选条件

select se.id,se.last_name,se.salary,sa.avgs

from s_emp se,(

select dept_id id,avg(salary) avgs

from s_emp

group by dept_id

) sa

where se.dept_id=sa.id

and se.salary>(1247.5)

and sa.avgs>1247.5;

06.查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显示当前部门的平均工资以及部门的名字和所在地区

select se.last_name,se.salary,se.dept_id,sa.avgs,sd.name,sr.name

from s_emp se,(

select dept_id,avg(salary) avgs

from s_emp

group by dept_id

)sa,s_dept sd,s_region sr

where sd.id = sa.dept_id

and sr.id = sd.region_id

and sa.dept_id = se.dept_id

and se.salary > 1247.5

and sa.avgs > 1247.5;