oracle第一阶段

发表于   |   分类于   |   阅读次数

Oracle

Oracle 介绍

Oracle 的安装

因为Oracle很娇气，一旦安装失败很难完全卸载干净，所以最好用虚拟机挂载xp镜像

  1. 固定虚拟机的ip地址,先自动获取ip然后查看ip记住，在手动固定一下，防止下次启动虚拟机的时候因为网络配置原因导致Oracle不可用
  2. 虚拟机上的网络适配器改为 nat ，意思是能上网，要想局域网共享服务就要设置为桥接模式
  3. 拷贝安装包，解压缩，安装oracle，确定右边，口令管理中找到scott，解锁scott用户，默认密码是tigger
    • scott 是Oracle是最老的一批开发者，为了纪念他所以保留了这些用户
    • 如果安装的时候没设置也可以在通过sql语句来解锁alter user scott account unlock; alter user scott identified by tiger;
  4. Oracle需要修改两个ip地址的文件：C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN 中的listener跟tnsnames中的ip改为虚拟机的ip
  5. 关闭虚拟机的防火墙
  6. 现在在虚拟机dos窗口中用 sqlplus 用户名/密码的方式来查看是否能进数据库

远程连接数据库

本地安装plsql developer

在虚拟机本地安装，PLSQL Developer 不用配置即可直接通过用户名密码登陆

远程连接

  1. 安装plsql dev
    • 注意安装目录不能有中文跟空格
  2. 解压缩拷贝到本地instantclient_12_1，拷贝上面安装后改的配置文件tnsnames.ora到本地
    • 其实instantclient_12_1才是真正的Oracle客户端，在虚拟机上因为本地安装了Oracle所以就不用再次安了，但是在远程中需要它
  3. 登陆plsql 破解一下
    • 直接运行 keygen 即可获得对应得破解信息
  4. 第一次登陆plsql时，不要输入账号密码，直接进入，在tools ---> preference 中配置 instantclient_12_1 对应的路劲后应用，确定
  5. 配置系统环境变量，名字TNS_ADMIN 路径是tnsnames.ora的所在路径
  6. 然后再次重启pplsql输入system账户跟密码就可以进去了
  7. 如果在安装的时候解锁了scott用户，就可以用输入scott账号跟密码进去，如果没解锁，可以通过sql语句解锁，上边有

如果不行的话，可以在虚拟机中重启listener跟service服务试试

Oracle的sql语句

中文乱码问题

  1. 查看服务器编码
    select userenv('language')from dual;
    我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK
  2. 执行语句 select * from V$NLS_PARAMETERS
    查看第一行中PARAMETER项中为NLS_LANGUAGE对应的VALUE项中是否和第一步得到的值一样，如果不是，则需要配置环境变量
  3. 设置环境变量
    • 新建变量名为NLS_LANG,变量值 ：第一步查到的值，我的是AMERICAN_AMERICA.ZHS16GBK
  4. 重新启动plsql，插入数据正常

Oracle安装之后的服务

Oracle 的角色

sql语句

注：

  1. sql语句中，单引号可能不行，要双引号
  2. 要提示按 F6
  3. dual 虚表，没有任何意义，只是为了补全Oracle查询语法
  4. Orcale中要想访问scrott里的表，需要登录scrott用户或者 system用户中查询表用from scrott.emp
  5. Oracle的查询内容区分大小写，关键字不区分
  6. 转成日期类型的 to_date(‘1981-1-1’,’yyyy-MM-dd’)
  7. 转义 '%/_%' escape '/',用什么转义用escape
  1. SQL优化的原则：尽量使用列名
  2. SQL中的null : 包含null的表达式都为null,null永远!=null
  3. null值最大
  4. null值：如果集合中含有null，不能使用not in；但可以使用in
  5. between and 1: 含有边界 2. 小值在前 大值在后
  6. order by作用于后面所有的列，desc只作用于离他最近的列
    select * from emp order by deptno ,sal desc
  7. 希望：对于某些不成立的记录，任然希望包含在最后的结果中
    左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含
    写法：where e.deptno=d.deptno(+)
    右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含
    写法：where e.deptno(+)=d.deptno
  8. 自连接：通过表的别名，将同一张表视为多张表，自连接不适合操作大表

基本查询

  • 1.查询出所有emp中的信息，并用中文进行字段重命名
    select * from scott.emp
    select empno "编号",ename "姓名",job "工作" from scott.emp
  • 2.查询emp表中员工的job信息，并去除重复信息
    select distinct(job) from scott.emp
  • 3.查询emp表中员工的全年的工资总和(sal总和)
    select sum(sal) from scott.emp
  • 4.查询emp表中员工的全年收入总和(sal+comm的总和)
    select sum(sal)+sum(comm) from scott.emp
  • 5.查询emp表中员工编号，姓名（输出格式如下：编号:xxx，姓名：xxx）
    • Concat拼接方式,可以多层嵌套
      select concat(concat('编号:',empno),concat(',姓名:',ename)) from scott.emp
    • Oracle的||方式,就是java里的+号
      select '编号：'||empno||'，姓名：'||ename from scott.emp

条件查询

  • 1.查询工资大于1500的员工
    select ename,sal from scott.emp where sal>1500
  • 2.查询工资大于1500并且有奖金的雇员
    select ename,sal,comm from scott.emp where sal>1500 and comm is not null
  • 3.查询工资大于1500或者有奖金的雇员
    select ename,sal,comm from scott.emp where sal>1500 or comm is not null
  • 4.查询工资大于1500并且没有奖金的雇员
    select ename,sal,comm from scott.emp where sal>1500 and comm is null
  • 5.查询员工姓名为smith的员工,注意大小写
    select empno,ename from scott.emp where ename='SMITH'

范围查询

  • 1.查询工资大于1500但小于3000的全部雇员
    • />=,<=方式
      select ename from scott.emp where sal>=1500 and sal<=3000
    • between and方式
      select ename from scott.emp where sal between 1500 and 3000
  • 2.查询1981-1-1到1981-12-31号入职的雇员（between and）
    select ename from scott.emp where hiredate between to_date('1981-01-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');
  • 3.查询员工编号是7369,7654,7566的员工
    • OR方式
      select empno,ename from scott.emp where empno='7369' or empno='7654' or empno='7566'
    • IN方式
      select empno,ename from scott.emp where empno in(7369,7654,7566)
  • 4.查询雇员姓名是’SMITH’,’ALLEN’,’WARD’的雇员信息 (IN方式)
    select empno,ename from scott.emp where ename in('SMITH','ALLEN','WARD')

模糊查询like

  • 1.查询所有雇员姓名中第二个字符有‘M’的雇员
    select ename from scott.emp where ename like '_M%'
  • 2.查询名字中带有‘M’的雇员
    select ename from scott.emp where ename like '%M%'
  • 3.查询雇员编号不是7369的雇员信息
    • <>方式
      select ename from scott.emp where empno <> 7369
    • !=方式
      select ename from scott.emp where empno != 7369

排序 order by

nvl(字段名，0) 如果字段为null把null改为0

排序的时候，让null在最后边 desc nulls last

  • 1.查询雇员的工资进行降序排序
    select ename,sal from scott.emp order by sal desc

  • 2.查询雇员的奖金并做降序排序（关于nulls first/nulls last）
    select ename,nvl(comm,0) from scott.emp order by nvl(comm,0) desc

  • 3.查询雇员的工资做降序排列并且其中奖金部分是升序排序
    select ename,sal,nvl(comm,0) from scott.emp order by sal desc,nvl(comm,0) asc

单行函数

伪表，虚表：dual 没有任何的实际意义，只是为了补全Oracle查询语法

  • 字符函数
    1.将’smith’转换成大写–关键字:upper
    select upper('smith') from dual
    2.将’SMITH’转换成小写–关键字:lower
    select lower('SMITH') from dual
    3.将’smith’首字母大写–关键字:initcap
    select initcap('smith') from dual
    4.将’helloworld’截取字符串成’hello’ 关键字substr,Orcale 默认是从1开始的，但是0也可以
    select substr('helloworld',0,5) from dual
    select substr('helloworld',1,5) from dual
    5.获取’hello’的字符串长度–关键字length
    select length('hello') from dual
    6.将’hello’中的l用x进行替换–关键字replace
    select replace('hello','l','x') from dual
  • 数值函数
    1.将15.66进行四舍五入（从-2到2）–关键字round
1
2
3
4
5
6
select round(15.66) from dual    //16
select round(15.66,-2) from dual //0  小数点左边第二位进
select round(15.66,-1) from dual //20 小数点左边第一位进
select round(15.66,0) from dual  //16
select round(15.66,1) from dual  //15.7 保留一位小数
select round(15.66,2) from dual  //15.66 保留两位小数

2.将15.66进行截断（从-2到2）–关键字trunc

1
2
3
4
5
6
select trunc(15.66) from dual    //15从小数点截取
select trunc(15.66,-2) from dual //0
select trunc(15.66,-1) from dual //10
select trunc(15.66,0) from dual  //15
select trunc(15.66,1) from dual  //15.6
select trunc(15.66,2) from dual  //15.66

3.对15/3进行求余数–关键字mod
select mod(15,3) from dual

日期函数

  • 1.查询系统时间–关键字sysdate
    select sysdate from dual
  • 2.查询雇员进入公司的周数
    select ename,trunc((sysdate-hiredate)/7) as "周" from scott.emp
  • 3.查询雇员进入公司的月数–关键字months_between
    select round(months_between(sysdate,hiredate)) from scott.emp
  • 4.求出三个月后的日期–关键字add_months
    select add_months(sysdate,3) from dual

转化函数：

    1. 将系统日期显示为yyyy-mm-dd hh:mi:ss. 注意分钟是mi
      select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
    1. 去掉补0,就是有的单位数会前面有个0
      select to_char(sysdate,'fmyyyy-mm-dd hh:mi:ss') from dual;
    1. 显示成24小时制
      select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    1. 显示成年月日
      select to_char(sysdate,'yyyy')||'年' from dual；
    1. 将字符串1981-1-1转换成日期函数
      select to_date('1981-1-1','yyyy-mm-dd') from dual;

通用函数

    1. 空值的处理函数，有null置为0
      select nvl(comm,0) from scott.emp;
    1. nvl2(判断值，非空返回值，空返回值)
      select nvl2(null,'非空','空') from dual;

条件表达式

  • 1.查询员工的job内容并转成中文显示
    • decode方式 ：在这里，要区分字段的大小写
      select ename,decode(job,'CLERK','柜员','SALESMAN','销售'，'其他') from scott.emp //最后那个其他表示没有写出的，要不会转成null
    • case when then end 方式
      select ename , case job when 'CLERK' then '柜员' when 'SALESMAN' then '销售' else '其他' end from scott.emp

多行(聚合)函数

查询的时候最好查哪个在count中放哪个字段，查询所有最好用主键

  • 1.查询所有员工记录数–关键字count
    select count(*) from scott.emp
  • 2.查询佣金的总数–（如何查询某个字段的总数量）
    select sum(sal) from scott.emp
  • 3.查询最低工资–关键字min
    select min(sal) from scott.emp
  • 4.查询最高工资–关键字max
    select max(sal) from scott.emp
  • 5.查询平均工资–关键字avg
    select avg(sal) from scott.emp
  • 6.查询20号部门的员工工资总和
    select deptno,sum(sal) from scott.emp group by deptno having deptno=20

分组函数 (group by后边有什莫select后边就必须有什么)

  • 1.查询部门编号及人数–分组查询关键字group by
    select deptno,count(*) "人数" from scott.emp group by deptno
  • 2.查询每个部门编号及平均工资
    select deptno,round(avg(sal)) from scott.emp group by deptno
  • 3.查询部门名称，部门编号，平均工资
    select dname,s.deptno,round(avg(sal)) from scott.emp s, scott.dept d where s.deptno=d.deptno group by s.deptno,dname
  • 4.查询出部门人数大于5人的部门
    select deptno,count(*) from scott.emp group by deptno having count(*)>5
  • 5.查询部门编号，部门名称，平均工资且平均工资大于2000
    select s.deptno,dname,round(avg(sal)) from scott.emp s,scott.dept d where s.deptno=d.deptno group by s.deptno,dname having round(avg(sal))>2000

多表关联查询

  • 查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,及领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级（可分为以下几个逻辑）
  1. 查询员工编号,员工姓名,领导编号,领导姓名
    select s1.empno,s1.ename,s2.empno,s2.ename from scott.emp s1,scott.emp s2 where s1.mgr=s2.empno
  2. 查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,领导编号,领导姓名,领导部门编号,领导部门名称
1
2
3
select s1.empno,s1.ename,s1.deptno,d1.dname,d1.loc,s2.empno,s2.ename,s2.deptno,d2.dname,d2.loc 
from scott.emp s1,scott.emp s2,scott.dept d1,scott.dept d2 
where s1.mgr=s2.empno and s1.deptno=d1.deptno and s2.deptno=d2.deptno
  1. 查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,领导工资等级
1
2
3
4
select s1.empno,s1.ename,s1.deptno,d1.dname,d1.loc,g1.grade,s2.empno,s2.ename,s2.deptno,d2.dname,d2.loc,g2.grade 
from scott.emp s1,scott.emp s2,scott.dept d1,scott.dept d2,scott.salgrade g1,scott.salgrade g2 
where s1.mgr=s2.empno and s1.deptno=d1.deptno and s2.deptno=d2.deptno 
and s1.sal between g1.losal and g1.hisal and s2.sal between g2.losal and g2.hisal
  1. 询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级
1
2
3
4
select s1.empno,s1.ename,s1.deptno,d1.dname,d1.loc,decode(g1.grade,1,'一级',2,'二级',3,'三级',4,'四级'),s2.empno,s2.ename,s2.deptno,d2.dname,d2.loc,decode(g2.grade,1,'一级',2,'二级',3,'三级',4,'四级')
from scott.emp s1,scott.emp s2,scott.dept d1,scott.dept d2,scott.salgrade g1,scott.salgrade g2 
where s1.mgr=s2.empno and s1.deptno=d1.deptno and s2.deptno=d2.deptno 
and s1.sal between g1.losal and g1.hisal and s2.sal between g2.losal and g2.hisal

外连接

因为内连接有的时候，查不到没有关联的数据，所以用到了外连接

  • 1.查询员工编号，姓名，领导编号，领导姓名，包括没领导的

    • left join on方式
      select s1.empno,s1.ename,s2.empno,s2.ename from scott.emp s1 left join scott.emp s2 on s1.mgr=s2.empno
    • Orcl的(+)方式
      select s1.empno,s1.ename,s2.empno,s2.ename from scott.emp s1,scott.emp s2 where s1.mgr=s2.empno(+)

  • 2.查询出所有部门信息(包括没员工的部门)及部门下的员工信息

1
2
3
select d.deptno,dname,loc,empno,ename,sal,comm from scott.dept d left join scott.emp s on d.deptno=s.deptno
select d.deptno,dname,loc,empno,ename,sal,comm from scott.dept d , scott.emp s where d.deptno=s.deptno(+)

  • 3.案例【1.3】查询学员成绩列表，显示学员姓名，考试分数，如果该学员没有考试记录也要列出姓名，不能用join on
    成绩表：

    学生表：

    课程表：

    • (+)方式

      1
      select s.id,s.name "学生姓名",tc.name "课程名称", m.score "考试分数" from T_MARK m,T_STUDENT s ,T_COURSE tc where s.id=m.studentid(+) and m.courseid=tc.id(+)

    • 嵌套方式

      1
      select sid,"学生姓名",tc.name,"考试分数" from (select s.id sid,s.name "学生姓名",m.courseid mcourseid, m.score "考试分数" from T_STUDENT s ,T_MARK m where s.id=m.studentid(+)) st,T_COURSE tc where st.mcourseid=tc.id(+) order by sid

多表关联查询的时候，如果逻辑不清楚可以考虑画图

张冲 wechat
欢迎扫一扫上面的微信关注我，一起交流！
坚持原创技术分享，您的支持将鼓励我继续创，点击打赏！