oracle学习第一天

sqlplus

其他的图形化工具

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

使用的用户：

用户名：scott

密码：tiger

登录：

sqlplusscott/tiger

退出：

exit

查看当前用户有哪些表：

select*fromtab;

显示当前用户

SQL>showuser

USER为"SYS"

切换用户

SQL>connectscott/tiger

已连接。

命令可以使用缩写

conn[ect]user

执行上一条sql语句

/

设置行宽

setlinesize150默认为80

设置页面大小（一页显示的数据）

setpagesize100默认为14

显示参数的值

show参数名

如:

showlinesize

永久保存配置

修改glogin.sql文件。

注释：

单行注释：--

多行注释：/**/

查看表结构

SQL>descdept

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

SCOTT用户的表：

dept表：

deptno编号

dname

loc位置

emp表：

empno员工编号

ename

job

mgr上级主管的员工编号

hiredate入职时间

sal薪水

comm奖金

deptno所属的部门编号-->引用dept.deptno

清屏：

Windows中：hostcls

Linux中：hostclear

设置某列的宽度：

columnjobformata15或coljobfora15

columnSALformat9999或colsalfor9999

格式：

col[umn]列名for[mat]格式

对于字符串：

a20，20是一个数据，表示20个字符的宽度。

对于数字

9表示一位，有几个，就是多宽。

空值的处理

不是一个有效的值。不是0，也不是空字符串。

null!=null

isnull

isnotnull

含有null的表达式结为null

修改上一条SQL语句

ed[it]

注意：后面不要写分号

滤空函数

nvl(表达式,当表达式为空时使用的值)

selectempno,ename,sal,sal*12as年薪,nvl(comm,0),(sal*12+nvl(comm,0))总收入

fromemp

字符串

是匹分大小写的，在使用时要加引号。

在指定别名时，引号可以加，也可以不加。

当含有空格、特殊字符时，一定要加引号。

不加引号时，显示都为大写，加上引号后，就是按所写的显示了。

在使用字符串时，要使用单引号。

在写别名时，要使用双引号。

去掉重复的行

--作用于一列

SQL>selectdistinctjobfromemp;

--作用于多个列，所有列的值加一起重复才算重复的记录

SQL>selectdistinctjob,deptnofromemp;

如果只查询一个表达式，没有用到任何表的数据，这时也必须得写from...

可以写成fromdual

dual是一个虚表，本身就存在的，可以直接使用。

如：

select3+2

select'Hello'||'World'fromdual;

字符串连接符

SQL>selectename||'的薪水是'||salfromemp;

ENAME||'的薪水是'||SAL

----------------------------------------------------------

SMITH的薪水是800

ALLEN的薪水是1600

WARD的薪水是1250

JONES的薪水是2975

MARTIN的薪水是1250

BLAKE的薪水是2850

CLARK的薪水是2450

SCOTT的薪水是3000

KING的薪水是5000

TURNER的薪水是1500

ADAMS的薪水是1100

JAMES的薪水是950

FORD的薪水是3000

MILLER的薪水是1300

也可以使用函数concat()：

SQL>selectconcat('Hello','World')fromdual;

where(aorb)andc

LIKE

在使用like时，可以使用%与_，分别表示任意数量的任意字符或任意一个字符。

要想表达%或_本身，需要使用转义符，例：

SQL>select*fromempwhereenamelike'KI\%%'escape'\';

BETWEEN

包含两个边界。

一定是小值写到前面，大值写到后面，否则没有结果。

IN

where..in(..,..,..,...)如果含有null，没有影响。

例：查询所有是经理的员工

SQL>select*fromempwhereempnoin(selectmgrfromemp);

where..notin(..,..,...)如果含有null，则不返回任何结果。

例：查询所有不是经理的员工

SQL>select*fromempwhereempnonotin(selectmgrfromempwheremgrisnotnull);

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

MySQL中日期类型：date,time,datetime

Oracle中只有Date.

SimpleDateFormat

yyyy-MM-dd

对日期的处理

1，查询指定日期后入职的员工信息

SQL>select*fromempwherehiredate>'31-12月-81';

2，修改日期的格式

SQL>select*fromv$nls_parameters;

SQL>altersessionsetnls_date_format='yyyy-mm-dd';//只对当前session有效

3，使用日期函数的方式：

SQL>select*fromempwherehiredate>to_date('1981-12-31','yyyy-MM-dd');

或

SQL>select*fromempwhereto_char(hiredate,'yyyy-MM-dd')>'1981-12-31';

语法：TO_CHAR(date,'format_model')

语法：TO_DATE(str,'format_model')

格式字符串不区分大小写：

获取当前时间：

SQL>selectto_char(sysdate,'YYYY-MM-DD')fromdual;

TO_CHAR(SY

----------

2012-03-07

SQL>selectto_char(sysdate,'yyyy-mm-dd')fromdual;

SQL>selectto_char(sysdate,'yyyy-mm-ddhh:mi:ss')fromdual;

TO_CHAR(SYSDATE,'YY

-------------------

2012-03-0702:33:41

排序：

orderby列名,...

可以作用在：数字、日期、字符串。

可以使用列名，表达式，别名，序号（表示select中的第几个列）

升序、降列

按一个列排列，按多个列排序

当orderby所在的列中有null，会：

升序时，null的在下面。

降序时，null的在上面。

我们希望，不管升序还是降序，null值的始终在下面

方式一：SQL>select*fromemporderbycommdescnullslast;

方式二：

selectempno,ename,job,hiredate,sal,nvl(comm,0)

orderby6desc

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

1，组函数

select

max(sal)最高工资,

min(sal)最低工资,

avg(sal)平均工资,

sum(sal)所有员工的工资和,

count(sal)领工资的员工数量

fromemp;

2，组函数对null的处理

例，查询所有员工的平均奖金（有人的奖金为null）

selectsum(comm)/count(*)平均奖金fromemp

组函数会自动过滤掉null值。

在使用avg()时要注意处理null值：

selectavg(nvl(comm,0))fromemp;

函数可以嵌套使用

3，分组

Groupby，写在FROM后，如果有WHERE，就在WHERE后面。

查询的列一定要是：

在groupby中出现的列（在Select中不一定全写上）

或是使用组函数

按一个列分组

按多个列分组

参与分组的多个列有一个不相同就是不同的组。

4，分组结果过滤

Having，是分完组后再进行过滤，只显示符合条件的结果。

在Groupby与Having中都不可以使用别名。

与Where的区别

Having是是分完组后再进行过滤。

Where是先过滤，再进行分组操作。

如果可以，尽量写Where条件，不写Having。

Select

...

From

Where

Groupby

Having

Orderby

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

子查询：

当一步不能求解时，可以使用子查询。

分为：

单行子查询

多行子查询

可以在主查询的select,from,where,having都可以放子查询

不可以在主查询的groupby放子查询

单行操作符对应单行子查询，多行操作符对应多行子查询

在select中放子查询时，要求只能是单行子查询。

IN：

ANY：

小于某集合中的任意一个值，就是小于集合中的最大值。

大于某集合中的任意一个值，就是大于最小值。

ALL：

小于某集合中的所有值，就是小于最小值。

大于某集合中的所有值，就是大于最大值。

练习题一：

题目：找到薪水大于本部门平均薪水的员工。

答案：

select*

fromempe1

wheresal>(selectavg(sal)fromempe2wheree2.deptno=e1.deptno)

练习题二：

题目：找到员工表中工资最高的前三名的员工信息

selectrownum,empno,ename,sal

from(selectempno,ename,salfromemporderbysaldesc)

whererownum<=3

rownum是一个伪列，表示记录在结果集中的行号。

1，rownum一旦生成，就不会变化（会先按没有排序时的默认顺序生成rownum，然后再执行排序）。

2，对于rownum，只能使用<与<=，不能使用>与>=与=。

SQL>selectrownum,empno,ename,salfromemp;

ROWNUMEMPNOENAMESAL

----------------------------------------

17369SMITH800

27499ALLEN1600

37521WARD1250

47566JONES2975

57654MARTIN1250

67698BLAKE2850

77782CLARK2450

87788SCOTT3000

97839KING5000

107844TURNER1500

117876ADAMS1100

127900JAMES950

137902FORD3000

147934MILLER1300

rownum,empno,ename,sal

from(

selectrownumr1,empno,ename,sal

whererownum<=10

)

whererownum<=5

from

...r1...

where

rownum<=10andr1>5

SQL>ed

已写入fileafiedt.buf

1select

2empno,

3ename,

4salmy_sal,

5mgr,

6(selectsalfromempwhereempno=e.mgr)ASmgr_sal

7*fromempe

SQL>/

EMPNOENAMEMY_SALMGRMGR_SAL

--------------------------------------------------

7369SMITH80079023000

7499ALLEN160076982850

7521WARD125076982850

7566JONES297578395000

7654MARTIN125076982850

7698BLAKE285078395000

7782CLARK245078395000

7788SCOTT300075662975

7839KING5000

7844TURNER150076982850

7876ADAMS110077883000

7900JAMES95076982850

7902FORD300075662975

7934MILLER130077822450