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;