oraclesql数据库基础知识复习

Oracle

1、数据库技术引言

a) Oracle数据库的安装
注意：oracle安装路径需要不能含有中文或者空格。
注意：oracle system用户 密码为要记住

b) OracleXE数据库的卸载
从新执行安装程序，选择卸载项。

c) 激活测试账号

i. 进入命令行窗口执行

ii. 键入 alter user hr account unlock;

iii. 测试是否成功
测试账号的用户名为hr
密码hr
select * from employees 结果展示

d) 为什么需要应用数据库
作用：对数据进行持久化（persistence）操作的。

e) 数据库存储数据的结构
Table(表格) ： 用于存储数据的结构
Column(列) : 数据的属性（特点）
Row(行) ： 具体对应数据内容
主键：(Primary key) : 唯一标示一行数据
外键（Foreign Key）: 多张表之间的关系

f) 数据库的分类

i. 关系型数据库（RDB Relationship DataBase）
Oracle|DB2 MySQL|SQLServer|Postgre

ii. 对象型数据库 （Object DataBase 对象)

iii. NOSQL数据库 （HbaseRdis MongoDB ...）
 

g) Oracle数据库的访问方式

i. 命令行方式访问 sqlplus 访问

ii. Web页面访问 isqlplus [oracle11g 开始不再支持]

iii. 第三方工具
PL/SQL developer 工具

 

h) SQL（Structure Query Language）语句查询【重点】不区分大小写

i. 全表查询

1. 查询所有
select * from 表名
select employee_id,frist_name,last_name,email....
from 表名
注意：* 进行又有列的查询，运行效率偏低。
* 可读性差

2. 查询的定列的内容
select 列名1，列名2，...
from 表名

3. 对列的内容进行运算 (+-*/)
select last_name,first_name,salary*12
from employees;

4. 列起别名 alias
select 列名 [as] 别名，列名 [as] 别名
from employees;
注意as关键字可以省略

5. 多列内容的连接
||运算符可以完成2个列或者 多个列的内容拼接
select first_name||last_name as name , salary
from employees;
 

ii. 条件查询 （where查询）

1. 比较查询 > < = != >= <=

2. 逻辑运算 and or not
 

3. 特殊谓词
查询工资为6000 或者 工资为9000 或者 工资为24000

in 或者 not in


查询工资为6000 至 12000 这个范围内的员工 （包含 6000,12000）

between .... and 在一个范围 或者区间中 （闭区间）
not between ..... and



查询某一些列的内容为null 不能应用 = null 判断
is null is not null

like 模糊查询

iii. 排序子句（order by)
基本语法

注意：

a) orderby 字句必须书写在sql语句的最后

b) 多列排序
 

iv. 函数

1. 内置函数

a) sysdate 获取当前系统时间

oracle中的虚表，哑表 dual
获得当前时间 select sysdate from dual;

b) to_char(日期,’日期格式’)
作用：把一个日期类型，转换成一个字符串

查询4月份入职的员工

3月18日入职的员工

c) to_date(‘字符串’，‘日期格式’)
把字符串转换成日期

to_date函数主要应用在数据的插入过程。
 

2. 组函数
针对于一组数据的操作。默认在不分组的情况下，会把一张表的数据划分成一个组。

a) 取最大值 max()
查询最高的工资
select max(column_name)
from table;

b) 取最小值 min()
查询最低的工资
select min(column_name)
from table;

c) 取平均值 avg()
select avg(column_name)
from table;

d) 取数据的和 sum()
查询员工工资之和
select sum(column_name)
from table;

e) 取条数 count()
查询全表的条数
select count(*)
from table;
注意：
select count(column_name)
from table;
运行结果：统计非null的条数。
===============================================

1. 分组（group by）【重点】
查询每个部门中的员工个数？

查询每个部门中员工的平均工资？
select deparement_id,avg(salary)
from employees
group by department_id;

查询每个部门中的每个工种的最大工资？


查询1997年每个月份入职的员工人数

i. 先要获得1997年的员工

ii. 按照月份进行分组

iii. 结果

注意：
1. 只有出现在group by中的列名，才可以书写在select字句中。
2. 如果列名出现在组函数中，也可以书写在select字句中。
3. 普通的内置函数，如果应用在了group by子句中，可以书写在select字句中。

2. 分组之后的条件判断 having 【重点】

平均工资大于5000 这些部门？

having的使用依据：如果你的条件判断中，涉及到应用组函数的条件判断，则需要使用having,其他条件判断需使用 where
查询1997年个月中 大于2人 入职的这些月份及其员工

3. SQL语句中的语法关键字

4. 子查询
查询效率偏低，尽量避免使用。
在一个SQL语句中，嵌套另一个SQL语句。

a) where子查询【重点】

i. 单值where子查询
where子句中，嵌套的SQL语句的返回值是一个结果。= > <操作处理。
案例：查询Last_name 为Abel 这个员工的部门名称。

ii. 多值where子查询嵌套的SQL语句的返回值是一个结果 in 关键字处理
查询last_name 叫做 ‘King’ 这个员工的部门

b) from 子查询【了解】
注意：from 表名
from (sql 语句)
结论：让sql语句的查询结果，充当表概念。

绝大多数情况下 from子查询 很少使用，对查询内容 做一些预处理工作。

5. 伪列
特殊列：不需要程序员手工创建的列，oracle自动创建。
不主动发起查询，看不到伪列的数据内容。

a) rowid 【了解】
行数据 在数据库中的唯一标示，代表行数据所对应的物理存储空间

注意：
1 可以基于rowid进行查询，查询效率最快。
2 表别名
 

b) rownum【重点】分页操作
对本次查询结果，符合要求的数据，进行标号 1......

为什么rownum 不能做 > >= = 操作？

如何应用rownum 做 >= > =操作？
oracle分页sql的书写
 

6. 多表操作【重点】

a) 多张表的关系（联系）体现 外键（foreign key）

b) 多表连接的语法
为什么要做多表连接呢？
在操作中可能需要涉及到两张或者 多张表数据的同时展示。

1. 内连接 （innner join)
 

2. 左外连接 （left outer join）

3. 右外连接 （right outer join)

4. 多表连接
===============================================

1. 自连接【掌握】
自己连接自己，特殊多表连接
查询雇员的姓名（first_name）,及其管理者姓名（first_name）

典型的案例分析：

2. SQL语句体系

a) DQL(Data Query Language) 查询 单表（子查询）多表（自连接）

b) DDL(Data Define Language) 数据的定义语言 表的定义修改 删除【掌握】

i. 创建表
整体思路：

create table 表名(
列名1 数据类型 约束，
列名2 数据类型 约束，
列名 3 数据类型 约束
);

外键

ii. 删除一张表 drop tablet_user;
注意：如果删除某一张表时，有别的表与其建立了外键关系，则应该先删除子表，在删除父表
可以进行级联删除 在删除表的同时，把约束（外键）同时删除

iii. 修改表【了解】

1. 修改列的类型
alter table 表名 modify 列名 类型
前提条件 列 没有存储数据。

2. 修改列的名字
alter table 表名 rename column 老列名 to 新列名

3. 追加新列
alter table 表名 add 列名类型
 

c) DML(Data Modify Language) 数据的修改语言 表中数据的插入修改 删除【重点】

i. 插入数据

1. insert into t_user (id,name,birthday) values (1,’suns’,sysdate);
注意：DML 语句 必须执行 commit操作 才可以同步数据库
处理日期 to_date函数处理

2. insert into t_user values(1,’suns’,sysdate);

ii. 删除数据

1. delete from t_user 全表所有的数据全部删除

2. delete from t_user where ...条件删除
delete from t_user where id = ?
delete from t_user where age in (10,30)
delete from t_user where name like ‘suns%’

iii. 修改数据

1. update t_user set name=xxx,birthday=xxx,column_name=value 修改全表数据
注意：主键 不能进行修改

2. update t_user set name=xxx,birthday=xxxx where ....
update t_user set name=xxx,birthday=xxx where id = ?
update t_user set name=xxx,birthday=xxx where name = ? or id= ?
udate t_user set name=xxx,birthday=xxx where name like ‘suns%’


 

d) TCL(Transaction Control Language) 事务控制语言
事务：保证业务操作完整性的一种数据库机制。

commit|rollback(撤销 回滚)

1. 提交DMIL操作，撤销DML操作
commit就是把回滚段中的数据，提交到数据库的文件系统中进行存储
rollback就是把回滚段中的数据，清空撤销。
2. 可以控制一组sql语句，要不一起成功，要不一起失败，从而保证业务操作的完
整性

3. ,事务的特性（ACID）【了解 面试】
1 A 原子性一组sql语句 一起成功一起失败 原子操作 完整整体

2 C 一致性 操作前的数据操作后的数据 保持一致。
3 I 隔离性保证多用户并发访问的数据安全。
4 D 持久性 事务操作的数据持久化到数据库中

3. Oracle数据库中的其他对象

a) 序列 Sequence 【重点】
作用：主键的自动生成。

i. 创建sequence create sequence suns_seq start with 100increment by 2

ii. 如何使用suns_seq.nextval
insert into t_user values (suns_seq.nextval,’xiaohei’,sysdate);

iii. 删除序列
drop sequence suns_seq
 

b) 视图 View
把查询的sql语句，命名，存储在数据库中便于后续的重复使用。

i. 创建View create view emp_dept_view as (select语句)

ii. 如何使用视图
select * from emp_dept_view where ....
注意：1 视图存储的是什么？
视图存储的是sql语句 效率上 和原来书写程序一样。
2 不建议应用视图进行增删改（DML）的操作

iii. drop view emp_dept_view;
 

c) 索引 Index
提高sql语句查询效率 （B+tree,BitMap,FullText）

i. 创建索引 create index name_index on t_user(name);

ii. 查询 select name from t_user;
注意：1 索引的创建，是占用存储空间（硬盘），不能简单的就创建索引。
2 oracle自动的为pk ,唯一列 加入索引。

iii. drop index name_index;