Oracle：基本SQL

/*

--以下代码是对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

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

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

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

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

 

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

 

以scott用户和123456密码进入oracle数据库实例

sqlplus scott/123456;

 

退出sqlplus环境

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;

 

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

 

NULL运算数字=NULL

 

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

 

null!=0

null!=空白字符串

 

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

 

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 ename || '的薪水' || sal "薪水情况" from emp;

 

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

spool d:\1234.sql;

 

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

spool off;

 

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

@ d:\1234.sql;

 

单行注释

--

 

多行注释

/**/

 

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 between 1300 and 1600;

 

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

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

 

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

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

select * from emp where deptno = 20 or deptno = 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 '____';

 

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

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

 

查询佣金为null的员工

select * from emp where comm is null;

 

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

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

 

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

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

 

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

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

 

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

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

 

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

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

 

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

select * from emp order by hiredate desc;

 

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

select * from emp order by comm desc;

select * from emp order by comm asc;

 

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

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

 

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

select * from emp where deptno=20 and sal>1500 order by hiredate asc;

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

测试lower/upper/initcap函数

 

测试concat/substr函数，从1开始 字符串连接，截取

select concat('hello','world') from dual; 引号需要小写,一般可以使用||来连接

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

select substr('helloworld',1,5) from dual;

 

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

select length('hahahahah宝贝') from dual;

select lengthb('hahahahah宝贝') from dual;

 

测试instr/lpad/rpad函数 计算w所在位置，从左边填充，从右边填充

select instr('woaini','a') from dual;

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

select rpad('a',10,'*') from dual;

 

测试trim/replace函数 去处指定字符，替换指定字符

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

select replace('foryangjin','yangjin','baobei') from dual;

 

测试round/trunc/mod函数 四舍五入，截取，取余

select round(95.126999,3) from dual;

select trunc(95.126999,1) from dual;

select mod(100,3) from dual;

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

select round(sysdate,'month') from dual;

 

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

12-3月-13

select trunc(sysdate,'month') from dual;

 

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

select trunc(sysdate,'year') from dual;

 

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

 

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

select sysdate from dual;

 

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

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

 

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

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

 

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

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

 

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

select ename 姓名,months_between(sysdate,hiredate) as 员工精确工龄 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;