/*

--以下代码是对emp表进行显示宽度设置

col empno for 9999;

col ename for a10;

col job for a10;

col mgr for 9999;

col hiredate for a12;

col sal for 9999;

col comm for 9999;

col deptno for 99;

set pagesize 20;

--创建新表new_emp，复制emp表中的结构和数据到new_emp表中

create table xxx

as

select * from emp;

--删除xxx表

drop table xxx purge;

*/

oracle数据库服务器由二个部分组成：

A)数据库（物理概念，底层是数据库专用文件的集合）

B)数据库实例（逻辑概念，只能能通过数据库实例操作数据库）

SQL

B) DML（数据操纵语言，例如：insert，update，delete，SELECT）

B）DDL（数据定义语言，例如：create table,drop table,alter table）

C) DCL（数据控制语言，例如：grant授予，revoke收权）

D) TCL（事务控制语言，例如：rollback，commit，事务开始）

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

从sqlplus环境，以scott用户和123456密码进入oracle数据库实例

c:\>sqlplus scott/123456

退出sqlplus环境

SQL:\>exit;

查询scott用户下的所有表

select * from tab;

查询当前用户是谁

show user;

设置显示的列宽（字符型、日期型），10个字符宽度，a表示字符型

col ename for a12;

a12：表示显示宽度为12个字符,a大小写不敏感

执行最近一次的SQL语句，并不是SQLPLUS命令

/表示最近执行过的SQL语句

col ename for a10，它不是SQL语句，而是oracle提供的专用SQLPLUS工具中的命令

清屏

host cls;

SQLPLUS命令，可以简写，分号可省略

SQL语句，不可简写，必须以分号结束

查询emp表的结构

desc emp;

数字型：number

字符型：varchar2

日期型：date

查询emp表的所有内容

select * from emp;

设置显示的列宽（数字型），9表示数字型，一个9表示一个数字位，四个9表示四个数字位

col empno for 999999;

col表示:column

empno表示：列名

for表示:format

999999：表示显示6个字符宽度，只用于数字

设置在一页中最多显示20条记录

set pagesize 20;

查询emp表的员工编号，姓名，工资，部门号

select empno,ename,job,deptno from emp;

查询emp表的不重复的工作

select distinct job from emp;

select distinct job,sal from emp;

最终由job和sal的笛卡尔积决定

查询员工的编号，姓名，月薪，年薪（月薪*12)

select empno,ename,sal,sal*12 from emp;

修改最近的一条SQL语句

edit;

查询员工的编号，姓名，月薪，年薪，年收入（年薪+奖金有空值)

select empno,ename,sal,sal*12,sal*12+comm from emp;

NULL运算数字=NULL

解决null的问题，使用NVL()函数，NVL(a,b)：如果a是空，用b替代

select empno,ename,sal,sal*12,sal*12+NVL(comm,0) from emp;

null!=0

null!=空白字符串

使用别名，查询员工的编号，姓名，月薪，年薪，年收入（年薪+奖金)

select empno AS "编号",ename AS "姓名",sal AS "月薪",sal*12 AS "年薪",sal*12+NVL(comm,0) AS "年收入"

from emp;//正确

select empno "编号",ename "姓名",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"

from emp;//正确

select empno '编号',ename '姓名',sal '月薪',sal*12 '年薪',sal*12+NVL(comm,0) '年收入'

from emp;//出错，

select empno 编号,ename 姓名,sal 月薪,sal*12 年薪,sal*12+NVL(comm,0) 年收入

from emp;//正确

select empno 编 号,ename 姓 名,sal 月 薪,sal*12 年 薪,sal*12+NVL(comm,0) 年 收 入

from emp;//出错

select empno "编 号",ename "姓 名",sal "月 薪",sal*12 "年 薪",sal*12+NVL(comm,0) "年 收 入"

from emp;//正确，要在别名中使用空格符，得用双引号

使用字符串连接符号，输出"hello world"，使用dual表

dual表是oracle为了拼一些语言，而设计的一个哑表。

因为oracle语法，有select子句，必须有from子句

显示系统当前时间

select now()这是mysql的语法

select sysdate from dual;

使用字符串连接符号，显示如下格式信息：xxxx的薪水是yyyy

方式一：select 'hello world' from dual;

方式二：select 'hello' || ' world' from dual;

select ename || '的薪水' || sal "薪水情况" from emp;

保存SQL语句到文件，并创建sql文件

spool d:\1234.sql;

保存SQL语句及其执行的结果

spool off;

执行文件中的sql语句，该文件必须是*.sql文件

@ d:\1234.sql;

单行注释

--select * from emp;

多行注释

/**/

SQL vs SQLPLUS

SQL

1）语言

2）ANSI标准

3）关健字不可缩写

4）必须是分号结束

5）更新表中的数据

SQLPLUS

1）环境，命令

2）是Oracle的特征之一

3）关健字可缩写

4）分号可有可无

5）不能更新表中的数据

HTML：<!-- -->

JSP： <%-- --%>

XML： <!-- -->

Pros：#

Java：

A)//

B)/* */

C)/** */

MySQL：#

Oracle:

A)--

B)/* */

JS：

A)//

B)/* */

CSS：

/* */

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

查询20号部门的员工

select *

from emp

where deptno = 20;

查询姓名是SMITH的员工，字符串值，大小写敏感

select *

from emp

where ename='SMITH';

不敏感：表名、字段名，格式'A'

敏感：字符串常量值

查询1980年12月17日入职的员工，"17-12月-80"满足oracle默认日期格式（DD-MON-RR表示2位的年份)

select *

from emp

where hiredate = '17-12月-80';

查询工资大于1500的员工

select * from emp where sal > 1500;

查询薪水在1300到1600之间的员工，包括1300和1600

select * from emp where sal>=1300 and sal<=1600;//正确

select * from emp where sal<=1600 and sal>=1300;//正确

select * from emp where sal between 1300 and 1600;//如果是数值型：小值在前，大值在后，返之无值

查询入职时间在"20-2月-81"到"23-1月-82"之间的员工

select * from emp where hiredate between '23-1月-82' and '20-2月-81';//无值

select * from emp where hiredate between '20-2月-81' and '23-1月-82';//如果是日期型：小值在后，大值在前，返之无值

查询20号或30号部门的员工

select * from emp where deptno=20 or deptno=30;

select * from emp where deptno in (20,30);

查询姓名以"S"开头的员工

select * from emp where ename like 'S%';

%表示：0个，1个，多个字符

_表示：1个字符

查询姓名以"N"结束的员工,%表示0个，或多个字符

select * from emp where ename like '%N';

查询姓名是4个字符的员工,_下划线只能表示1个字符

select * from emp where ename like 'W___';

查询员工姓名中含有'_'的员工，让\后的字符回归本来意思【like '%\_%' escape '\'】

select * from emp where ename like '%\_%' escape '\';//正确

select * from emp where ename like '%E_%' escape 'E';//正确

select * from emp where ename like '%$_%' escape '$';//正确

select * from emp where ename like '%#_%' escape '#';//正确

select * from emp where ename like '%@_%' escape '@';//正确

select * from emp where ename like '%%_%' escape '%';//错误

查询佣金为null的员工

select * from emp where comm = null;//错误

select * from emp where comm is null;//正确

select * from emp where comm is not null;//正确

查询无佣金且工资大于1500的员工

select * from emp where (comm is null) and (sal>1500);

查询工资是1500或，3000或5000的员工

select * from emp where sal in (1500,3000,5000);

查询职位是"MANAGER"或职位不是"ANALYST"的员工（方式一）

select * from emp where (job = 'MANAGER') or (job != 'ANALYST');

查询职位是"MANAGER"或职位不是"ANALYST"的员工（方式二）

select * from emp where (job = 'MANAGER') or not (job = 'ANALYST');

查询员工信息（编号，姓名，月薪，年薪），按月薪升序排序，默认升序

select empno "编号",ename "姓名",sal "月薪",sal*12 "年薪" from emp order by sal asc;

查询员工信息，按入职日期降序排序

select * from emp order by hiredate desc;

order by后面可以跟列名、别名、表达式、列号，从1开始

列名排序：select * from emp order by hiredate desc;

别名排序：select ename "姓名",sal "月薪",hiredate "入职时间" from emp order by 入职时间 desc;

表达式排序：select ename "姓名",sal*12 "年薪",hiredate "入职时间" from emp order by sal*12 desc;

列号排序：select ename "姓名",sal*12 "年薪",hiredate "入职时间" from emp order by 2 desc;

查询员工信息，按佣金升序或降序排列，null值看成最大值

select * from emp order by comm desc;

查询员工信息，按工资降序排列,相同工资的员工再按入职时间升序排列

select * from emp order by sal desc,hiredate asc;

查询20号部门，且工资大于1500，按职入时间降序排列

select *

from emp

where (deptno=20) and (sal > 1500)

order by hiredate desc;

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

测试lower/upper/initcap函数

select lower('www.itCAST.cN') from dual;

select upper('www.itCAST.cN') from dual;

select initcap('www.itCAST.cN') from dual;

测试concat/substr函数，从1开始

select concat('hello',' world') from dual;

select 'hello' || ' world' from dual;

select substr('www.itcast.cn',12,2) from dual;

12表示：从第几个字符开始，第一个字符为1

2表示：共取几个字符

测试length/lengthb函数，前提是，装oracle时，编码方式为UTF-8，一个中文占3个Byte长度

select length('CHINA中国') from dual;//7

select lengthb('CHINA中国') from dual;

测试instr/lpad/rpad函数

select instr('你好 world','w') from dual;//4

select lpad('hello',10,'@') from dual;

select rpad('hello',10,'#') from dual;

测试trim/replace函数

select trim('w' from 'wwwwwbookwwwwwwwwwwwwww') from dual;

select replace('www.itcast.cn','cn','com') from dual;

测试round/trunc/mod函数

select round(3.1415,3) from dual;

select trunc(3.1415,3) from dual;

select mod(10,3) from dual;

测试round作用于当前时间（month）

12-3月-13

select round(sysdate,'month') from dual;//01-3月-13

select round(sysdate,'year') from dual;//01-1月-13

测试trunc作用于当前时间（month）

12-3月-13

select trunc(sysdate,'month') from dual;//01-3月-13

测试trunc作用于当前时间（year）

select trunc(sysdate,'year') from dual;//01-1月-13

round和trunc除了用在数值型，也能用在日期型。

测试sysdate，默认情况下，只显示日期部份，格式为：日-月-2位的年

select sysdate from dual;

显示昨天，今天，明天的日期，日期类型只能+-数值

select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;

以年和月形式显示员工近似工龄，只能日期-日期=数值

select ename,hiredate,(sysdate-hiredate)/30 "近似月工龄",(sysdate-hiredate)/365 "近似年工龄" from emp;

使用months_between函数，计算到年底还有多少个月

select months_between('31-12月-13',sysdate) from dual;

使用months_between函数，以精确月形式显示员工工龄

select ename,(sysdate-hiredate)/30 "近似月工龄",months_between(sysdate,hiredate) "精确月工龄" from emp;

测试add_months函数

select add_months(sysdate,1) from dual;

测试next_day函数，从今天开始算，下一个星期三是多少号？【中文平台】

select next_day(sysdate,'星期日') from dual;

测试last_day函数，本月最后一天是多少号？

select last_day(sysdate) from dual;

测试last_day函数，下一个月最后一天是多少号？

select last_day(add_months(sysdate,1)) from dual;

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

双引：

1)为列设置别名

单引：

1）字符串常量单引

 

动手练习：

将今天讲过的OracleSQL，删除答案，自已练习一遍