Oracle数据库学习笔记

最新推荐文章于 2023-08-14 16:06:30 发布

是你吗_俊先生

最新推荐文章于 2023-08-14 16:06:30 发布

阅读量223

点赞数

分类专栏： Java及项目文章标签： oracle 数据库

本文链接：https://blog.csdn.net/junqiuyang1/article/details/105962143

版权

Java及项目专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Oracle

SQL

SQL（Structural Query Language, 结构化查询语言）分为三种类型：
- DML（Data Manipulation Language）数据操纵语言
  
  用于查询和修改数据记录，包括增删改查。
- DDL（Data Definntion Language）数据定义语言
  
  用于定义数据库结构，包括
  1.CREATE TABLE：创建数据库表
  2.ALTER TABLE：更改表结构，增加、删除、修改列长度
  3.DROP TABLE：删除表
  4.CREATE INDEX：在表上建立索引
  5.DROP INDEX：删除索引
- DCL（Data Control Language）数据控制语言
  
  用于控制数据库的访问，包括：
  1.GRANT：授予访问权限
  2.REVOKE：撤销访问权限
  3.COMMIT：提交事务处理
  4.ROLLBACK：事务处理回退
  5.SAVEPOINT：设置保存点
  6.LOCK：对数据库部分内容进行锁定
SQL不区分大小写，作为习惯，关键字通常大写，其余小写。
出现关键字换行，增加可读性。
SQL注释：两根短横线 –

SELECT

1.查看表table0的列(describe)：desc table0;
2.数字和日期可以使用算术运算符，日期不能乘除。

SELECT last_name,salary,12*salary+1000
FROM employees;

3.dual是一个虚拟表，用来构成select的语法规则，执行命令，oracle保证dual里面永远只有一条记录。
4.空值：无效，未指定，不同于0，凡是null参与的运算都为null。
5.列的别名，默认大写，中间可以加 as。

SELECT employee_id id, last_name, 12salary annual_sal
FROM employees;
–加双引号，原格式输出
SELECT employee_id as id, last_name “Name”, 12salary annual_sal
FROM employees;

6.连接符：把列与列，列与字符连接在一起，用 || 表示。可以用来合成列。

SELECT last_name||’`s job_id is '||job_id as details
FROM employees;

7.日期和字符只能在单引号中出现，每返回一行，字符串被输出一次。只有起别名用双引号。
8.重复行：默认情况下，查询会反胃全部行，包括重复行，null也算一行。去重用 distinct .

SELECT DISTINCT department_id from employees;
–查询多个时注意行数匹配

9.SQL和SQLPlus
SQL是一种语言；SQLPlus是一种环境，Oracle的特征之一，有自己的关键字，并可以缩写（如desc, ed）。

过滤（where）

字符和日期要包含在单引号内，字符大小写敏感，日期格式敏感。日期的默认格式时 DD-MON月-RR

select employee_id,last_name,salary,hire_date
from employees
–where hire_date = ‘7-6月-1994’
where to_char(hire_date,‘yyyy-mm-dd’) = ‘1994-06-07’

betwen…and…(包含边界)

select employee_id,last_name,salary
from employees
where salary between 4000 and 7000

in(set)

select employee_id,department_id,salary
from employees
– where department_id = 90 or department_id = 80 or department_id = 70
where department_id in(70,80,90)

select employee_id,last_name,salary
from employees
–含有字母a，一个‘%’表示0个以上字符
where last_name like ‘%a%’
–第二个字母为a，一个‘_’表示一个字符
where last_name like ‘_a%’
–查找含有下划线，此时斜线为转义字符
where last_name like ‘%_%’ escape ‘’
–查询94年雇佣的员工
select last_name,hire_date
from employees
–where hire_data like ‘%94’
where to_char(hire_date,‘yyyy’) = ‘1994’
–选择姓名中有a和e的员工姓名
select last_name
from employees
where last_name like ‘%a%e%’ or last_name like ‘%e%a%’
–where last_name like ‘%a%’ and last_name like ‘%e%’

is null / is not null

select employee_id,last_name,salary,commission_pct
from employees
where commission_pct is null

排序（order by）

order by语句在select语句的结尾，也可以按别名排。
ASC(ascend) 升序（默认）
DESC(descend) 降序

select employee_id,department_id,salary
from employees
where department_id=80
order by salary desc
–多层排序
–order by salary desc,last_name asc

函数

单行函数

只对一行进行操作，且只返回一个结果。可以转换数据类型，可以嵌套，参数可以是一列或一个值。

字符函数

大小写控制函数
- lower 转为小写
- upper 转为大写
- initcap 首字母大写

select *
from employees
where lower(last_name) = 'king'

字符控制函数
- concat(‘str1’, ‘str2’) 连接字符
- substr(‘str’, start, num) 取子字符串
- length(‘str’) 长度
- instr(‘str1’, str2’’) 判断str2在str1中首次出现的位置，若不存在输出0
- lpad(var, num, ‘str’) 左补位，用str来补齐，不能为空
- rpad(var, num, ‘str’) 右补位
- trim(‘str1’, FROM ‘str2’) 将str1从str2中移除，仅能去首尾
- replace(‘str’, ‘str1’, ‘str2’) 取代（所有）

 SELECTt raplace('abcab','b','m')
 FROM dual

数字函数

ROUND(num,bit) 四舍五入，bit为小数位数，默认为保留整数部分，以小数点位为0，为负的话左取整
TRUNC(num,bit) 截断
MOD(num1,num2) 求余

日期函数

日期型数据实际包含两个值：日期和时间。

日期和数字可以做加减运算。
两日期相减返回相差天数，但不能做加法运算。
可以用数字除以24来向日期中加上或者减去天数。
MONTHS_BETWEEN 两个日期间相差的月数
ADD_MONTHS 向指定日期中加上若干月
NEXT_DAY 指定日期的下个星期*对应的日期
LAST_DAY 本月最后一天
ROUND 日期四舍五入（月初），默认按日取整。
TRUNC 日期截断

SELECT employee_id,last_name,(sysdate-hire_date)/30,MONTHS_BETWEEN(sysdate,hire_date)
FROM employees
--查询入职时间是月底倒数第二天的员工
SELECT last_name,hire_date
FROM employees
where hire_date = last_day(hire_date)-1

转换函数

隐式数据类型转换（Oracle自动完成）
- DATE <——> VARCHAR2(CHAR) <——> NUMBER
显式数据类型转换
to_char to_number

--不填充
SELECT to_char(1234567.89,'999,999,999.99') from dual
--填充0
SELECT to_char(1234567.89,'L000,000,999.99') from dual
--
SELECT to_number('L001,234,567.89', ,'L000,000,999.99') + 1 from dual

通用函数

适用于任何数据类型，也适用于空值。

NVL(expr1,expr2)
- 将空值转换成一个已知的值
- 可以使用的数据类型有日期、字符、数字。
- 当expr1为空，用expr2代替。

SELECT employee_id last_name,salary*12*(1+NVL(commission_pct,0)) “Annual Salary”
FROM employees
-- 注意类型转换
SELECT last_name,NVL(to_char(department_id,'9999'),'这是老板') "部门"
FROM employees

NVL2(expr1,expr2,expr3)
- expr1不为空，返回expr2；为空，返回expr3。
NULLIF(expr1,expr2)
- 相等返回NULL，不等返回expr1。
COALESCE(expr1,expr2,…,exprn)
- 若expr1为空，返回expr2，依次类推…

条件表达式

在SQL语句中使用 IF-THEN-ELSE 逻辑。
两种方法
- CASE表达式

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN ... THEN ...
ELSE else_expr]
END
-- 例
select employee_id,last_name,department_id,case department_id when 10 then salary * 1.1
                                                               when 20 then salary * 1.2
                                                               else salary * 1.3
                                                               end new_salary
from employees
where department_id in (10,20,30)
order by department_id

DECODE函数

select employee_id,last_name,department_id,decode(department_id,10,salary * 1.1,
                                                                20,salary * 1.2,
                                                                salary * 1.3) new_salary
from employees
where department_id in (10,20,30)

多表查询

笛卡尔集：
- 1 省略连接条件；2 连接条件无效；3 所有表中的所有行相互连接。
- 为了避免笛卡尔集，可以用where加入有效条件。
等值连接

select employees.employee_id,employees.department_id,department_name
from employees,departments
where employees.department_id = departments.department_id
--其别名
select employee_id,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id
-- 三个表连接
select e.employee_id,e.department_id,d.department_name,l.city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id

非等值连接

select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal

内连接与外连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表和另一个表不匹配的行。
外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行，称为左（或右）外连接。没有匹配的行，结果表中相应的列为空（NULL）。外连接的where子句条件类似于内部链接，但连接条件中没有匹配行的表的列后面要加外连接运算符(+)。

--左外连接
select e.employee_id,e.department_id,d.department_name
from employees e,departments d
where e.department_id = d.department_id(+)
--右外连接
select e.employee_id,e.department_id,d.department_name
from employees e,departments d
where e.department_id(+) = d.department_id
-- 不能左右都连接，一个谓词只能引用一个外部联接的表

SQL99语法连接

cross join table2 （叉集，错误）
natural join table2 会以两个表中具有相同名字的列为条件创建等值链接，当数据类型不同时会出错。
join table2 using col_name
join table2 on table1.col_name = table2.col_name
left | right | full couter join table2 on …

 select e.employee_id,e.department_id,d.department_name
from employees e join departments d
on e.department_id = d.department_id
-- 三表连接
 select e.employee_id,e.department_id,d.department_name,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
-- 左外
 select e.employee_id,e.department_id,d.department_name
from employees e left outer join departments d
on e.department_id = d.department_id
-- 满外连接
 select e.employee_id,e.department_id,d.department_name
from employees e full outer join departments d
on e.department_id = d.department_id

自连接

本表连接查询，相当与把本表复制一份。

-- 查询公司中员工‘chen’的manager的信息
select emp.last_name,manager.last_name,manager.salary,manager.email
from employees emp, employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name) = 'chen'
-- 子查询
select ‘Chen’||last_name,salary,email
from employees 
where employee_id  = (select manager_id
									 from employees 
									 where lower(last_name) = 'chen')

组函数

组函数作用于一组数据，并对一组数据返回一个值。处理多行数据返回一行
1 AVG / SUM
只能存放number类型
2 COUNT
适用于任何数据类型，仅仅计算非空的值
3 MAX / MIN
适用于varchar,date,number等任何类型
4 STDDEV (分析样本标准差)

select count(1),count(2),count(*) from employees; --返回进入记录行数（employees的行数）
-- 忽略空值
select avg(commission_pct),sum(commission_pct)/count(nvl(commission_pct,1)) from employees;
-- 去重
select count(distinct department_id) from employees;
-- Toronto这个城市中的平均工资
select ‘Toronto’,avg(salary)
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
and lower(l.city) = 'toronto'

分组数据

1 group by

在select查询中，所有未包含在组函数中的列，都应该出现在group by之中
不能做where子句中使用组函数
可以在having子句中使用组函数

-- 求各部门的平均工资
select department_id,avg(salary)
from employees
where department_id in (40,60,80)
group by departmen_id
-- 求不同部门不同工种的平均工资
select department_id,job_id,avg(salary)
from employees
where department_id in (40,60,80)
group by departmen_id,job_id
-- 组函数嵌套
select max(avg(salary))
from employees
group by department_id

2 having 过滤分组
不用像where一样和from挨着。

-- 求出各部门中平均工资大于6000的部门，以及其平均工资
select department_id,avg(salary)
from employees
having avg(salary) > 6000
group by departmen_id
order by department_id asc
-- 查询各管理者手下员工的最低工资
select manager_id, min(salary)
from employees
-- where manager_id is not null
group by manager_id
having min(salary) >= 6000 and manager_id is not null
-- 查询公司在1995-1998年间的雇佣人数
select count(*) "total",
		  count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
		  count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
		  count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
		  count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')

子查询

-- 单行子查询
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary) > (
								select min(salary)
								from employees
								where department_id = 50
								)
-- 查询平均工资最低的部门的信息
select *
from departments
where department_id = (
									select department_id
									from employees
									having avg(salary) = (
																	select min(avg(salary))
																	from employees
																	group by department_id   -- 嵌套分组函数应该有 GROUP BY，对分组求平均
																	)
									group by department_id   -- 修饰 avg(salary) ，先分组，再算各个组内平均工资
									)
-- 查询平均工资最低的部门的信息和平均工资
select d.*,(select avg(salary) from employees where department_id = d.department_id)
from departments d
where department_id = (
									select department_id
									from employees
									having avg(salary) = (
																	select min(avg(salary))
																	from employees
																	group by department_id   -- 嵌套分组函数应该有 GROUP BY，对分组求平均
																	)
									group by department_id   -- 修饰 avg(salary) ，先分组，再算各个组内平均工资
									)
-- 多行子查询   ANY / ALL
-- 查询各部门中平均工资比本部门平均工资高的员工的员工号，姓名和工资
select employee_id,last_name,salary
from employees e1
having salary > (
						select avg(salary)
						from employees e2
						where e1.department_id = e2.department_id		--内外表连接
						group by department_id
						)

创建和管理表（DDL）

DDL原子操作，不能回滚rollback，增删改可以回滚

常见的数据库对象
查看表
select * from user_tables;
命名规则
创建表

-- 方法1
create table emp1(
id nuber(10),
name varchar2(20),
salary number(10,2),  -- 10位，其中后两位为小数
dire_date date
)
-- 方法2 (导入表)
create table emp2
as
select employee_id id,last_name name,hire_date,salary
from employees
where department_id = 80
-- where 1=2  --创建空表

更改表
更改时只能改以后插入的数据，以前的不变。
删除/清空表
drop table emp1;
truncate table emp1;–表数据清空，结构不变
改变表名 (没有TABLE)
rename emp2 to employees2;

数据处理（DML）

插入数据 insert into … value(…) / select …from …where …

-- 建立一个空表
create table emp1
as
select employee_id,last_name,hire_date,salary from employees
where 1=2 
--
insert into emp1
value(1001,aa,to_date(''1998-12-21,'yyyy-mm-dd'),10000)
-- 按value导入
insert into emp1(employee_id,last_name,hire_date)
value(1001,aa,to_date(''1998-12-21,'yyyy-mm-dd'))
-- 从已知表中导入数据
insert into emp1(employee_id,last_name,hire_date,salary)
select employee_id,hire_date,last_name,salary
from employees
where dapartment_id = 80
--手动输入(数字型不加引号，字符型加引号)
insert into emp1(employee_id,last_name,hire_date,salary)
value(&id,'&name',&salary,'&hire_date')

更新数据(可回滚) update … set … where …

update emp1
set salary = 12000
where employee_id=179
commit

删除数据(可回滚) delete from … where …

delete from employees
where department_id = (
										select department_id
										from departments
										where department_id is like '%Public%'
										)

更新和删除数据可能出现 数据完整性错误

数据库事务

一种逻辑处理单元，使数据从一种状态到另一种状态。
事务有以下部分组成：
- 一个或多个DML语句
- 一个DDL语句
- 一个DCL语句
  
  – 未完待续

是你吗_俊先生

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库学习笔记

OracleSQLSQL（结构化查询语言）分为三种类型：DML（Data Manipulation Language）数据操纵语言用于查询和修改数据记录，包括增删改查。DDL（Data Definntion Language）数据定义语言用于定义数据库结构，包括1.CREATE TABLE：创建数据库表2.ALTER TABLE：更改表结构，增加、删除、修改列长度3...
复制链接

扫一扫