Oracle（一）

oracle数据库

前言 oracle sql
第一章 Selecting Rows
第二章 Sorting & Limiting Selected Rows

前言

1.一个认知

认知什么是oracle？
oracle：商业运用第一的关系型数据库
实质：关系型数据库
了解oracle数据库发展历史(文档)

2.二个概念

数据库：数据存储的仓库
关系型数据库：数据库中保存的对象之间可以存在一定的关联关系，并非完全独立。
主要反映到以后学习的主外键.
了解数据库发展历史(文档)

3.三个名词

sql:结构化的查询语句，操作oracle数据库的语言
sqlplus:oracle软件自带的可以输入sql，且将sql执行结果显示的终端
pl/sql:程序化的sql语句，在sql语句的基础上加入一定的逻辑操作，如if for…,
使之成为一个sql块，完成一定的功能

4.四种对象

table：表格，由行和列组成，列又称字段，每一行内容为表格的一条完整的数据。
view: 视图，一张表或者多张表的部分或者完整的映射，好比表格照镜子，镜子里面的虚像就是view
除去常见的table和view两种对象以外，oracle数据库还支持如下四种对象
sequence：序列
index：索引，提高数据的访问效率
synonym：同义，方便对象的操作
program unit：程序单元，pl/sql操作的对象

5.五种分类

sql的五大分类:
DQL：数据查询
select
DML：数据操纵语言（行级操作语言）：操作的是表格当中一条一条的数据
insert update delete
DDL：数据定义语言（表级操作语言）：操作的内容为表格（对象）
create alter drop truncate rename
transaction control：事务控制
commit rollback savepoint
DCL：数据控制语言
grant revoke
delete，truncate区别：
delete： 删除表中的一条或者多条记录，该操作需要提交事务
truncate：清空表格,该操作不需要提交事务

准备:

1.安装oracle数据库
1.1 最好默认按照到C盘
1.2 安装过程中有一步让输入一个密码,建议使用oracle作为密码,方便记忆
1.3 安装完成之后,打开系统服务,查看服务是否已经正常启动,具体情况参照文档:oracle系统服务.txt
1.4 打开cmd,输入sqlplus,然后回车查看是否能执行该命令
1.5 如果不能执行,则需要把安装目录里面的BIN里面配置到系统环境变了path中,然后重新打开一个cmd窗口即可。

2.登录oracle自带的管理系统,新建一个属于自己的账号.参照文档:oracle系统服务.txt

3.用户创建好之后,使用sqlplus命令登录到oracle数据库中,然后把之后要使用到的表及其数据导入到数据库中.
参照文档:导入数据.txt

4.了解导入的三张表以及相互关系
s_emp 员工表
s_dept 部门表
s_region 地区表

5.之后登录或者操作数据库,就可以使用这个新创建的用户了

第一章：select语句，数据查询操作

1.使用select语句查询某张表的所有数据内容

语法：
select [distinct] *{col_name1,col_name2,…}
from tb_name;
注意：语法中出现的中括号[]，表示该部分可有可无
*：表示所有列，仅仅作为测试和学习使用，在企业用语中不出现，因为效率低下且可读性差
col_name1：列名，将需要查阅的数据字段列举出来，可以查看多列值，列名之间用,进行分割即可
s_emp ：员工信息表
s_dept：员工部门表
需求：查看s_dept表中的所有记录
select *
from s_dept;
select id,name,region_id
from s_dept;
练习：查看s_dept表中的所有记录的id和name
select id,name
from s_dept;

练习：查看所有员工的id，名字(last_name)和薪资(salary)
select id,last_name,salary
from s_emp;

2.select语句可以对指定的列的所有值进行算术运算。

语法：
select col_name 运算符 数字
from tb_name;
需求：查看每个员工的员工id，名字和年薪。
select id,last_name,salary*12
from s_emp;
注意：select语句永远不对原始数据进行修改。
练习：查看每个员工的员工id，名字和月薪涨100以后的年薪
select id,last_name,(salary+100)*12
from s_emp;

3.给查询的列区别名

语法：
select old_column [as] new_column_name
from tb_name;
需求：查看员工的员工id，名字和年薪,年薪列名为annual
select id,last_name,salary*12 as annual
from s_emp;

4.使用||可以使得多列的值或者列和特殊的字符串合并到一个列进行显示

语法：
select col_name||‘spe_char’||col_name
from tb_name
‘spe_char’:如果一个列的值要跟特殊的字符串连接显示，使用该语法。
需求：查看员工的员工id，全名
select id,first_name||last_name
from s_emp;
练习：查看员工的员工id，全名和职位名称，全名和职位名称合并成一列显示，且格式为：姓 名，职位名称
select id,first_name||’ ‘||last_name||’,’||title as name
from s_emp;

5.对null值得替换运算

nvl()函数
语法：
select nvl(col_name,change_value)
from tb_name;
需求：查看所有员工的员工id，名字和提成，如果提成为空，显示成0
select id,last_name,nvl(commission_pct,0) commission_pct
from s_emp;

6.使用distinct关键词，可以将显示中重复的记录只显示一条

语法：
select distinct col_name,col_name…
from tb_name;

注意1：distinct关键词只能放在select关键词后面
如：select id,distinct title
from s_emp;
该语句语法错!!!
注意2：如果distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录。
test表：
id id2
1 2
1 3
2 4
3 4
3 4
select distinct id,id2
from test;
显示结果为：
id id2
1 2
1 3
2 4
3 4
需求：查看所有员工的职位名称和部门id，同职位同部门的只显示一次
select distinct title,dept_id
from s_emp;

7.sqlplus命令

sqlplus 登录之后,可以使用buff(缓存)来存储/执行/修改要执行的sql语句
这里的buff的特点:
1.buff中只能存储一条sql语句(但是这条sql语句可能有很多行)
2.每次放入新的sql语句,会把之前的覆盖掉
3.每次执行sql语句,都会把这个sql语句放到buff里面

l 查看缓存中的sql语句
a 在[定位]的那一行后面追加新的内容
i 在[定位]的那一行下面插入新的一行
c 替换[定位]的那一行中的某些字符串
c/老的字符串/新的字符串
del 删除[定位]的那一行内容
n 后面加内容可以重写这一行
! 后面接终端命令 !clear:清屏 windows中使用$符号例如:$cls
/ 执行缓存sql命令

clear buffer:清空当前缓存的命令

save test.sql buff中的sql语句保存在test.sql文件中
get test.sql 把test.sql中的内容在加载到buff中,但是没有运行
start test.sql 把test.sql中的内容在加载到buff中并且执行
@test.sql 把test.sql中的内容在加载到buff中并且执行
edit file_name 使用系统默认编辑器去编辑文件

spool file_name 将接下来的sql语句以及sql的运行结果保存到文件中
sql1
result1
sql2
result2
…
spool off 关闭spool功能
exit：退出

select id,last_name,first_name, salary, dept_id
from s_emp
Where rownum <=10;

结果不好看，通过column使我们的显示界面好看。

COLUMN last_name FORMAT a15;
可以简写为:
col last_name for a15;
COLUMN first_name FORMAT a15;

Column 没有改变数据表里数据，它只是改变显示。Column不是SQL命令,而是sqlplus命令。除了刚才这个作用之外，我们下面来看看它还有什么作用。

COLUMN last_name HEADING ‘Employee|Name’ FORMAT A15
. 给last_name取别名为Employee|Name , 竖杠代表换行。
. A15表示十五个字节长，一短横杠就是一个字节长

COLUMN salary JUSTIFY LEFT FORMAT $99,999.00
. salary JUSTIFY LEFT : 仅仅改变列名显示为左齐
. FORMAT $99,999.00: 控制显示格式为前面加 $ 符, “，”为分隔符, 0或9代表数字(通配符)，0表示替换对齐数值,位数不足会补足,可以混合使用。

COLUMN start_date FORMAT A8 NULL ‘Not hired’
. 如果start_date值为空的话，显示为’Not hired’;
. Format后不能直接跟null, 要先a8或a10;
. NULL 'Not hired’和nvl类似

column 显示所有对列格式的设置情况
column last_name 显示对last_name列显示设置的情况
column last_name clear 删除对last_name列格式设置的情况
Clear column 清除所有column的格式设置

Column columName
可以显示该列的格式设置，这里的列并不特定于某个表.

注意:
1234 column 99.99 – > ###### //出错的时候不能显示,只是显示####

第二章：排序和限制查询

一.排序

1.排序:

所谓排序，就是根据某个字段的值按照升序或者降序的情况将记录查询出来
语法：
select col_name,…
from tb_name
order by col_name [asc|desc],…
注意：1.排序使用order by字句，该子句只对查询记录显示调整，并不改变查询结果，所以执行权最低，即最后执行。

2.排序关键词：

asc：升序（默认，默认的意思是不加关键词的时候默认为生序排序）
desc：降序

3.如果有多个列排序，后面的列排序的前提是前面的列排好序以后有重复（相同）的值。

例子：
id id2
1 2
2 3
3 4
4 1
4 2

语句：
select id,id2
from test
order by id,id2 desc;

结果：
id id2
1 2
2 3
3 4
4 2
4 1
注意：先排第一列，如果第一列有重复的值再排第二列，以此类推
需求：查看员工的id，名字和薪资，按照薪资的降序排序显示。

二.限制查询，即指定查询条件进行查询

1.语法：
select col_name,…
from tb_name
where col_name 比较操作表达式
逻辑操作符
col_name 比较操作表达式
…
注意：
1.限制查询条件，使用where子句
2.条件可以多个，使用逻辑操作符和()进行条件的逻辑整合
3.where子句的优先级别最高
4.比较操作表达式由操作符和值组成
常见的操作：
1》逻辑比较操作符
= > < >= <= !=
2》不等于:三个都表示不等于的意思(经常用的是!=)
!= <> ^=

需求：查看员工工资小于1000的员工id和名字
select id,last_name,salary
from s_emp
where salary < 1000;

2.sql比较操作符
between and：在什么范围之内
需求：查看员工工资在700 到 1500之间的员工id，和名字
select id,last_name,salary
from s_emp
where salary between 700 and 1500;
in(list):在一个列表中
需求：查看员工号1,3,5,7,9员工的工资
select id,last_name,salary
from s_emp
where id in (1,3,5,7,9);
like:模糊查询，即值不是精确的值的时候使用
通配符，即可以代替任何内容的符号
% ：通配0到多个字符
_ : 当且仅当通配一个字符

转义字符：
默认为\,可以指定，指定的时候用escape 符号指明即可,转义字符只能转义后面的一个字符
需求：查看员工名字以C字母开头的员工的id，工资。
select id,last_name,salary
from s_emp
where last_name like ‘C%’;
练习：查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资
select id,last_name,salary
from s_emp
where last_name like ‘___n_%’;
需求：查看员工名字中包含一个_的员工id和工资
select id,last_name,salary
from s_emp
where last_name like ‘%_%’ escape ‘\’;

is null：对null值操作特定义的操作符，不能使用=
需求：查看员工提成为为空的员工的id和名字
select id,last_name,commission_pct
from s_emp
where commission_pct is null;
3.逻辑操作符
当条件有多个的时候使用
and：且逻辑
or：或逻辑
注意：and逻辑比or逻辑要高
not：非逻辑
需求：查看员工部门id为41且职位名称为Stock Clerk（存库管理员）的员工id和名字
select id,last_name,dept_id,title
from s_emp
where dept_id = 41
and
title = ‘Stock Clerk’;
练习：查看员工部门为41 或者 44号部门 且工资大于1000的员工id和名字
select id,last_name,dept_id,title
from s_emp
where salary > 1000
and
(dept_id = 41
or
dept_id = 44);
查看员工部门为41且工资大于1000 或者 44号部门的员工id和名字
select id,last_name,dept_id,title
from s_emp
where salary > 1000
and
dept_id = 41
or
dept_id = 44;