粤嵌打卡第54天（Oracle数据库安装介绍和基本查询）

最新推荐文章于 2024-10-11 09:01:57 发布

qq_41986840

最新推荐文章于 2024-10-11 09:01:57 发布

阅读量285

点赞数

文章标签：数据库 oracle sql

本文链接：https://blog.csdn.net/qq_41986840/article/details/107736455

版权

Oracle数据库

1、Oracle基本命令

1、命令行基本命令
- 进入oracle: sqlplus
  
  管理员登录：格式：用户名 as sysdba
  
  用户名： sys as sysdba
  
  密码：不显示
- 连接管理员（方式一：需要填入密码）
```
conn  sys as sysdba;
```
  - 方式二（不需要填写密码，直接连接管理员，只能在本地使用）
```
conn /as sysdba;
```
  注意：直接连接普通用户会出现账户锁定状态，需要管理员进行对普通用户进行解锁
  - 解锁scott普通用户操作(oracle 默认内部有一个用户名scott和密码tiger)
```
alter user scott account unlock;
```
    - 解锁完成之后，重新连接(conn 用户名/密码)
```
conn scott/tiger;
```
    - 查看当前用户
```
show user;
```
    - 查看系统表
```
desc user_tables;
```
    - 查看当前系统表下表的名称
```
select table_name from user_tables;
```
2、使用PLSQL可视化界面 (注意：使用PLSQL操作oracle,必须开启oracle监听服务)
- 普通用户登录：用户名/密码选择Normal
- 管理员登录：用户名/密码选择Sysdba
- 点钥匙切换用户
- PLSQL中有两种窗口 SQL Window(只能操作sql语句) Command Window(既能操作sql语句，也能操作sql命令)
在PLSQL中操作Oracle
- 管理员新建用户
```
create user test identified by 123;
```
  - 管理员创建完用户之后，必须给该用户分配连接和操作数据的角色
```
grant connect,resource to test;
```
    - – 登录新用户
      – 先进入oracle中
```
c:> sqlplus test/123
```
      – 再连接新用户
```
conn test/123
```
      – 通过系统视图查看当前用户自己的所有表(user_tables 系统视图)
```
select table_name from user_tables;
```
      – 新建表(oracle中所有数字类型用number表示，字符串用varchar2表示)
```
create table tab(
       tid number(10),
       tname varchar2(20)
);
```
      – 插入数据（oracle添加完数据，必须手动commit或者rowback）
```
insert into tab values (1,'宝鸡');
```
      – oracle执行增删改都必须手动提交事务
```
commit;
```

2、Oracle体系结构

1、Oracle体系结构

2、Oracle逻辑结构

方案：代表不同的用户
数据库：代表一个oracle服务
表空间：代表一个用户在一个数据库服务下能操作的一张或者多张的表、视图、触发器、存储过程
段：一个段一般是一张表
区：一个区一般是一个数据行
oracle数据块：代表一个数据单元格

注意：一个用户最少具有两个表空间，方便做缓存处理

3、Oracle数据块

3、数据体系结构组件汇总

3、Oracle其他Sql语句

1、伪列
- rowid: 存储数据区的物理地址（行标识符,看不懂的物理地址）
```
select e.*,rowid from emp e;
```
- rownum: 行号（结果集产生后，附加的行号）
  
  注意：rownum会在排序（order by）之前,oracle中没有分页，一般使用rownum嵌套查询分页
  
  oracle 普通sql的分页
  - 查询前8页的员工信息
```
select t.* from(
select t.*,rownum rn from (
(select emp.* from emp order by sal) t) t
where rn between 1 and 8;
```
- 查询原理：
- 表的别名有效范围：本条sql
  
  错误sql: 括号中的表别名e不能出去
```
select e.sal from 
(select sal from emp e);
```
  正确sql
```
select e.sal from 
(select sal from emp) e;
```
  括号外边的表的别名可以进入括号内
  
  正确sql: 查询本部门员工工资占本部门总工资的百分比？ (|| 代表字符串连接器，和mysql中concat()函数一样)
```
select e.*,round(sal/(select sum(sal) from emp where deptno = e.deptno)*100,2) || '%'
from emp e
```
- 列的别名有效范围：结果集产生以后，order by之后可以使用（order by 是结果集产生以后排序）
  
  错误代码：列的别名 sals只能在结果集产生之后有效,where条件选择时查询结果集还没有查询出来
```
select e.sal sals from emp e where sals
```
  正确代码: order by 是结果集产生以后执行
```
select e.sal sals from emp e order by sals 
```
- –8，检索员工表中，每个员工的编号、姓名以及他的上司的编号、姓名，没有上司就输出空。
  – 查询原理：每列的值类型必须统一
  – 任何值和null值运算结果都是 null
  – nvl 表示如果有值则输出，没有输出为空
```
select e.*,nvl(e.manager_id||'','') mgr
from employees e;
```
  – nvl2 （三个参数）表示第一个参数如果不为null，则输出第二个参数，为null则输出第三个参数
```
select e.*,nvl2(e.manager_id,manager_id||'','没有') mgr
from employees e;
```
  - – -- case 表达式：用来给值起别名
    
    方式一：可以使用不等号
```
select e.*,
       case 
           when e.manager_id is null then '没有'
             when e.manager_id = 1 then '一'
           else e.manager_id||''
        end mgr
from employees e;
```
    方式二：只能使用等号 end后面起别名
```
select e.*,
       case e.manager_id
           when 2 then '2'
             when 1 then '一'
           else '没有'
        end mgr
from employees e;
```
  - – decode 多条件函数(行列转置)
    
    第一个参数和第二个参数比较，相等则返回第三个参数
    
    第一个参数和第四个参数比较，相等则返回第五个参数
    
    都不匹配则返回没有
```
select e.*, decode(manager_id,
                    1,'1',
                    2,'2',
                    ...
                    '没有') mgr
from employees e;
```
- Oracle字符串函数
  - 大小写转换函数 upper(), lower()
    
    dual表：在oracle中表示一个单行单列的表，用于查询表达式、常量专用的表
```
select 'game',upper('game'),lower('Game') from dual;
```
  - 在字符串中查找子串，返回其下标（从1开始），没有则返回0
```
select * from employees where instr(first_name,'m')>0;
```
  - ASCII函数和CHR函数：字符和ASCII码相互转换
```
select ASCII('A'),CHR(65) FROM DUAL;
```
  - – LENGTH函数：返回字符串的长度
```
select length('this'), length(3.14) from dual;
```
  - – LPAD函数和RPAD函数：补充空白函数(也可以将宽度定小，用来截断内容的显示)
    
    lpad: 表示左边补空格 rpad：表示右边补空格
```
select lpad(e.ename,6,' '),rpad(ename,6,'#')
from emp e;
```
  - – LTRIM函数、RTRIM函数和TRIM函数
    
    表示去除空格后显示的函数
```
select ' gam e  ',
       length(ltrim(' gam e  ')), 
       length(rtrim(' gam e  ')), 
       length(trim(' gam e  '))
from dual;
```
  - – SUBSTR函数 (截取字符串、无论是正负，都是从左往右截取)
```
select substr('game',2,2),substr('game', -2,2) from dual;
```
- 数值函数：
  - – ROUND函数：对数值进行舍入（格式化补0） 3.10 3.1 不会进行四舍五入
```
select round(3.1,2) from dual;
```
  - TRUNC函数：对数值进行截断不会进行四舍五入
    select trunc(3.15999,2) from dual;
  - MOD函数：用来计算余数格式：MOD(x,y)
    – POWER函数：计算x的y次幂格式：POWER(x,y)
    – SQRT函数：计算平方根
```
select mod(10,3), power(2,3), sqrt(2) from dual;
```
  - to_char 转换数值到字符串： 9会忽略前导的0， 0不会
    SELECT TO_CHAR(23456.79,‘9999999.000’) FROM DUAL
  - – to_char 转换日期 to_char(date, ‘yyyy-mm-dd hh24:mi:ss’) 将日期转换成字符串
    
    Oracle中对字符串和时间必须相互转换，不能向mysql中日期和字符串类型一样
```
	 YYYY -- 四位的年份     YY -- 两位的年份
   	  MM -- 两位的月份
	MONTH --月份的完整单词，全大写(如果当前会话的日期语言是中文,则显示:x月)
	Month --月份的完整单词，首字母大写
	MON --月份单词的前三个字母，全大写; Mon -- 月份单词的前三个字母，首字母大写
  
	DD -- 本月中的第几天
  
	DAY -- 周几的完整单词，全大写; Day  -- 周几的完整单词，首字母大写
	Dy -- 周几单词的前三个字母，首字母大写
  
	HH24 -- 24小时格式的小时; HH -- 12小时格式的小时
	MI -- 分
	SS --秒
```
    - – to_date( dateStr, ‘yyyy-mm-dd hh24:mi:ss’) 将字符串转换为日期
      
      快速复制表数据
```
select * from emp;

create table emp2(e_no,e_name,e_date) as select empno,ename,hiredate from emp;

select * from emp2;
```
      插入指定日期
```
insert into emp2 values(1,'小明',to_date('2020-5-6 15:6:4','yyyy-mm-dd hh24:mi:ss'));
```
    - 输出当前日期: sysdate (类似于mysql中的now()函数) ，显示当前日期
```
select sysdate from dual;
```
    - – LAST_DAY() 返回当月的最后一天
    – MONTHS_BETWEEN(x,y) 两个日期之间的月份差
    
    – add_months 添加月份
```
select add_months(sysdate,-12),LAST_DAY(add_months(sysdate,2)) ,
       MONTHS_BETWEEN(sysdate, to_date('2020-6-29','yyyy-mm-dd'))
from dual;
```
    - – round(date, unit); 对日期进行舍入取整，如果没有参数unit,则将日期设为最近的一天
    - – TRUNC(x[,unit]) 对日期进位进行截断
    - – unit: yyyy 舍入/截断年； mm舍入/截断月； hh：舍入/截断小时
```
select round(sysdate, 'yyyy'),trunc(sysdate, 'yyyy') from dual;
```
    - – TIMESTAMP 给表中插入时间戳类型的数据时，可以在字符串值前面使用TIMESTAMP
```
TIMESTAMP'2020-07-31 28:34:45.343434343'
```
    - –TO_TIMESTAMP 字符串和时间戳之间的转换可以使用TO_TIMESTAMP
```
TO_TIMESTAMP('2020-07-31 28:34:45.343434343','yyyy-mm-dd hh24:mi:ss.ff9')
```
    - – systimestamp 系统当前时间戳 (Oracle中只有Date类型,没有DateTime类型)
```
select systimestamp, to_char(systimestamp,'hh24') from dual;
```
    例题：输出所有员工到60岁退休的时间，还需要工作多少年，多少月？
    
    select (60 - trunc((sysdate - to_date(‘1990-10-1’,‘yyyy-mm-dd’))/360))*360 + sysdate from dual;
- 子查询
  - 单行单列子查询
    
    获取Scott所在部门的其他员工
```
select * from emp where deptno = (select deptno from emp where ename='scott') and ename != 'scott'
```
  - 单行多列子查询
    
    和scott在同一个部门并且工资相同的人
```
select * from emp where(deptno,sal) = (select deptno,sal from emp where ename='scott') and ename != 'scott';
```
  - 多行单列子查询
    
    和20部门工作相同的其他人员信息
```
select * from where job in (select distinct job from emp where deptno = 20) and deptno != 20;
```
    多行条件使用以下关键字：
    - IN 匹配多行结果中某一个即可（相等条件）
    - ANY 符合多行结果中某一个即可（不等条件）、
    - ALL 符合多行结果中任意一个（不等条件）
      
      – any 工资高于20部门某一个员工即可的其他部门人员信息（高于20部门最低工资）
```
select * from emp where sal > any(select sal from emo where deptno = 20) and deptno != 20;
```
      – all 工资高于20部门任意一个员工的其他部门人员信息（高于20部门最高工资）
```
select * from emp 
where  sal > all(
select sal from emp where deptno = 20)
and deptno!=20;
```
  - 多行多列子查询
    – 和20部门同样工作并且同样工资的其他部门人员信息
```
select * from emp where (job,sal) in(select job,sal from emp where deptno = 20) and deptno != 20;
```
    oracle数据库中，在通过PLSQL工具修改表中数据时，打开表必须带有rowId物理地址字段，点击开锁，修改数据完毕，进行提交操作。如果没有rowId字段，打开锁不成功，不能修改数据
```
select e.*,rowid from emp e;
```
- 子查询能放置的位置
  - 1- where子句后：子查询作为查询条件
    
    输出本部门工资最高的人员信息 (使用in 效率低)
```
select e.* from emp e where (deptno,sal) in(select deptno,max(sal) sal from emp group by deptno) 
```
  - 2-from子句后：子查询作为数据源
    
    输出本部门工资最高的人员信息
```
select e.* from emp e,(select deptno,max(sal) from emp group by deptno) t
where e.sal = t.sal and e.deptno = t.deptno;
```
  - 3-select子句后：作为表达式
    
    – 输出员工的信息以及所占部门总工资的百分比
```
select e.*,round(sal/(select sum(sal) from emp where deptno = e.deptno)*100,2)|| '%' percent_sal from emp e 
```
  - 4- 放在order by之后：作为表达式的结果进行排序
```
select * from emp e order by(select sal * deptno from emp where empno = e.empno)
```
4、数据库的启动的三个阶段

5、数据库关闭的四个阶段

6、数据库出现异常的两种解决方案

7、DOS命令下执行sql文件命令

sqlplus进入oracle服务
以管理员身份登录 sys as sysdba
执行sql文件
```
@ sql文件地址
```
sql文件比较大时，使用DOS命令执行sql文件比较快，因为直接从Oracle服务器下执行，PLSQL工具执行sql是通过jdbc方式执行sql,所以比较慢。