Oracle-first

1.数据库的安装

 1.咱们使用Xp虚拟机中安装oracle数据库，安装的过程是傻瓜式的，大概需要20十分钟左右完成oracle数据库服务器软件的安装。

 2.安装sqlplus命令行客户端安装的方法是将两个压缩包解压到同一个文件夹下，然后配置下path环境变量

 

sqlplus是一个连接oracle数据库的命令行客户端。用命令行工具访问刚刚安装好的数据库服务。和我们mysql -u -p差不多性质。

 

返回如下说明sqlplus和oracle数据库完成连接。

在数据库安装的机器上执行
sqlplus / as sysdba;以管理员的身份登录Oracle数据库

默认的scott用户是被锁定的，先解锁就能登陆上了。 使用下面的语句解锁scott,并且修改密码:

alter user scott identified by tiger account unlock ;

安装完oracle之后我们简单了解下oracle的几个服务。

1、OracleDBConsoleorcl
  oracle数据库控制台，如果你需要用浏览器来使用oracle企业管理器，那么就启动这个服务。

2、OracleJobSchedulerORCL
  定时器的服务进程。

3、OracleOraDb10g_homeTNSListener
  Orace监听服务，如果有客户端需要连接到数据库，此服务必须打开。服务进程为TNSLSNR.EXE， 参数文件Listener.ora，日志文件listener.log，控制台LSNRCTL.EXE，默认端口1521。

4.OracleServiceORCL
  数据库的主服务，此服务的必须启动才能使用Oracle。此服务的命名规则是OracleService+全局数据库名称。服务进程为ORACLE.EXE，参数文件initSID.ora，日志文件SIDALRT.log，控制台SVRMGRL.EXE、SQLPLUS.EXE。

2.基本SQL语句（查询）

·显示当前连接用户:

>show user;

·查询用户可见的所有表:

>select tname from tab;

 

Scott用户默认会给我们建立这么多表，用于我们学习oracle数据库语法，我们主要研究《员工表》就好了，别的都没啥数据。

 

Oracle数据库查询语句也要遵守SQL标准，那么sql语言，不是属于哪个数据库软件的特有的语言，主流的数据库oracle，mysql，sqlserver等数据库都遵循sql语法，只是根据数据库不同有些特殊的函数等等。基本上掌握了sql语句如果不是做dba的工作，完全能够满足我们日常开发，无论我们的软件是使用哪种数据库。

·查询员工信息：员工号，姓名，月薪?

 select empno,ename,sal from emp;

·查询员工信息：员工号，姓名，月薪，年薪，年收入?

1）表达式中的一项为null，结果为null。从年收入中看出凡是奖金为空的年收入都为空

2）null值不和任何值相等，包括null。使用is null和is not null判断是否空值

select ename,sal,comm from emp where comm is null;--查询出所有奖金为空的员工信息

3）nvl(判空值,为空后返回),nvl(奖金,0)

·使用as的方式给查询列设置别名？

select empno,ename,sal,sal*12 yearsal,sal*12+ nvl(comm,0) yearmoney from emp;

·使用distinct关键字查询，去重？

Select distinct deptno from emp;  

Select disctinct ename,deptno from emp;

描述：根据后边的列来判断是否重复，如果多个列，那么多个列同时判断。

 

·字符串链接符|| （伪表和伪列）(如果出现中文乱码使用如下方式解决)
 select userenv('language') from dual; 

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

在环境变量中查找一个名为“NLS_LANG”的环境变量，如果找不到，则新建一个，把变量值赋值为：“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”（需要根据实际数据库服务器字符集进行赋值）。客户端字符集设置成功。

我们oracle查询语句的语法和其他数据库一致。

select 列名1，列名2，列名3  from  表名  where条件1，条件2，条件3

·我们要查询员工姓名是‘WARD’的员工信息？
select * from emp where ename='WARD';

·查询工资大于2500元的员工信息？

 select * from emp where sal>2500;

 

操作符

含义

between ... and ...

在两个值之间比较（包含边界）

in(集合) not in(集合)

等于/非等于 值列表中的一个

like  ‘%值%’

模糊查询

is null

空值判断

·查询员工号 不是是7678,7788,6789?

·查询名字中包含S的的所有员工？名字是以S开头的员工。

·（order by asc/desc）查询员工薪水按照月薪排序？按照年薪排序？

·查询员工信息按照奖金排序（升序）？(注意观察空值)空值是最大的

3.函数

1）单行函数:只对一行的数据进行加工处理，nvl函数

字符函数：

lower(转小写)，upper(转大写),initcap(首字母大写)，

concat（连接）,substr(求字符串的子字符串,起始,lenght/lengthb(长度)

instr(在一个字符串当中查找另外一个字符串)--instr(a,b)在a中找b,

trim(去掉前后指定的字符)--trim(‘H’ from ‘Hello WorldH’),

lpad(左填充),rpad(右填充),replace(替换)

select ename,lower(ename) from emp;

select upper('sdfsfd') from dual;
  select initcap('hello world') from dual;
select substr('1234567',0,2) from dual;

数值函数：

round:四舍五入

round(45.926,2)  45.93

 

select round(45.926,2) 一,round(45.926,1)二,round(45.926,0)三,round(45.926,-1)四,round(45.926,-2) from dual;

日期函数：

to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)

两个日期相减返回差的天数(两个日期不允许相加):计算员工的工龄：天 星期 月 年

函数

描述

用例

MONTHS_BETWEEN

返回两个日期间相差月数

MONTH_BETWEEN(sysdate,hiredate)

ADD_MONTHS

向指定日期加上若干个月数

add_months(sysdate,20)

LAST_DAY

当前月份的最后一个日期

last_day(sysdate)

NEXT_DAY

返回指定日期的下一个星期几所对应的日期

next_day(sysdate,’星期五’)

 

数据类型的转换:

 

 

 

格式

说明

举例

yyyy

年份

2000

mm

月份

07

month

月份全称

7月

day

星期全称

星期一

dd

日期

11

格式化日期

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

字符转日期

 select to_date('2000-01-20 23:11:02','yyyy-mm-dd hh24:mi:ss') from dual;

通用函数:

函数

描述

举例

nvl(第一个,第二个)

第一个值为空返回第二个参数

nvl(null,1)

nvl2(a,b,c)

A为空返回c，否则返回b

nvl(null,1,2)

nullif(a,b)

当a和b相等的时候返回null

 

coalesce(a,b,c,d)

从左到右找到第一个不为空的返回

 

 

条件判断

select ename,job,sal 原来,case job

when 'PRESIDENT' then sal + 1000

when 'MANAGER' then sal + 800

end 加工资 from emp;

 

2）多行函数:对多行数据进行加工处理。

就是我们常说的分组函数和group by 关键字一起使用。

函数名

描述

AVG

求平均值

COUNT

统计

MAX

最大值

MIN

最小值

SUM

求和

 

·求员工的平均工资？

select avg(sal) from emp;

·求员工工资的总和？

select sum(sal) from emp;

·求一共有多少个员工？

select count(1) from emp;

·select  count(1) 一,count(comm)二from  emp;从查询结果我们看出了什么

count遇到空值不统计。

·select count(distinct ename) from emp; 去重关键字

·求每个部门的平均工资？
select avg(sal),deptno,ename from emp group by deptno;  注意：group by后边出现的字段才能够写到select返回字段里

·按照职位统计平均工资？

select avg(sal),job from emp group by job;

·求每个部门的总工资？

select sum(sal),deptno from emp group by deptno;

·求每个职位的总工资？
select sum(sal),job from emp group by job;

·查询平均工资大于2000的部门？Having：注意where不能使用组函数，having先分组再过滤。

select avg(sal) avg_sal,deptno from emp group by deptno having avg(sal)>2000;

 

4.连接查询

等值连接，不等值连接，外连接，自连接

 create table testleft(

    deptno number,

    leader varchar2(20)

 );

insert into testleft values(10,'王振');

---做连接

 select t.leader,d.dname,d.deptno from testleft t left join dept d

 on t.deptno=d.deptno;

--有链接

 select t.leader,d.dname,d.deptno from testleft t right join dept d

 on t.deptno=d.deptno;

 

大白话总结：外链接，是从一张表的方向，往另一张表的方向去找，无论找到找不到数据，都把第一张表数据列出。

内联接，是根据连接条件看两张表如果能找到匹配的，那么将记录拼接到一起，如果找不到那么连边的记录都不往结果集里返回.（两张表必须匹配相同的主键和外键）

testleft

 

 

 

dept

 

 

 

deptno

leader

 

 

 

deptno

dname

 

 

 

10

王振

 

 

 

10

ACCOUNTING

 

 

 

 

 

 

 

 

20

RESEARCH

 

 

 

 

 

 

 

 

30

SALES

 

 

 

 

 

 

 

 

40

OPERATIONS

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

左外(testleft->dept)

 

 

右外(dept->testleft)

10

王振

10

ACCOUNTING

 

 

10

ACCOUNTING

10

王振

 

 

 

 

 

 

20

RESEARCH

 

 

 

 

 

 

 

 

30

SALES

 

 

 

 

 

 

 

 

40

OPERATIONS

 

 

内连接

 

 

 

 

 

 

10

王振

10

ACCOUNTING

 

 

 

 

 

 

 

·查询出员工姓名，工资，职位，部门编号，部门名称

·查询出员工姓名，工资，职位，工资级别

·按部门统计员工：部门号，部门名称，人数

select d.deptno 部门号,d.dname部门名称,count(e.empno)人数

from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname;

·查出员工和员工上级的名字？

 

递归查询:select empno,ename,mgr,level from emp connect by prior empno=mgr start with mgr is null order by level;