Oracle02 - Oracle语句
文章目录
一：查询 - DQL语言
1：完整语法
-- 下面是DQL语言的完整语法
select [top | distinct] [选择列 | *]
from 数据源(表名)
[where 查询条件]
[group by 分组条件]
[having 分组过滤条件]
[order by 排序条件 asc | desc nulls first | last]
上述完整语法的执行顺序是
(1) 肯定是要先知道访问的是那个表? -> from 数据源(表名) 是最先执行的
(2) 对要筛选的行进行过滤 -> where 查询条件 是第二个执行
(3) 对过滤出来的行进行分组 -> group by 分组条件 是第三个执行的
(4) 执行分组过滤的结果 -> having 分组过滤条件 是第四个执行的
(5) 然后选择出来自己想要的列或者全部的列 -> select部分
- 如果有distinct，会先筛选出想要的列，然后distinct
- 如果有top, top会在select和distinct之后执行
(6) 最后是结果的输出整理 -> order by
（5）select [（5-3）TOP|（5-2）DISTINCT] （5-1）[选择列表]|[*]
（1）from 数据源
（2）[where 查询条件]
（3）[group by 分组条件]
（4）[having 过滤条件]
（6）[order by asc|desc nulls first|last];
2：简单，别名和去重查询
-- 最简单的查询，连where都没有，表示将表中所有的行都获取
select * from emp; -- 从emp表中拿到所有的数据
-- 可以为表执行别名，这在表名非常长的时候非常的有效
select e.ename from emp e; -- 给emp表起一个别名叫做e, 后续可以使用这个e来进行操作
-- 去重查询，可以对选出来的行进行去重查询
select distinct e.deptno from emp.e; -- 查看emp表中的部门编号
3：条件查询
-- 查询工资 > 3000 的员工的信息
select * from emp where sal > 3000;
-- 查询工资在 3000 ~ 5000的员工的信息
select * from emp where sal between 3000 and 5001;
-- 查询家住在内蒙或者河北的员工信息
select * from emp where address = '内蒙古' or address = '河北';
-- 或者用in
select * from emp where address in ('内蒙古', '河北');
-- 查询姓张的员工
select * from emp where ename like '张%';
4：分组查询和过滤
-- 统计每一个部门有多少人
select deptno as '部门', count(*) as '人数'
from emp
group by deptno;
-- 分组过滤
-- 统计部门人数>5人的部门的编号和人数
select deptno as '部门', count(*) as '人数'
from emp
group by deptno having count(*) > 5;
5：排序查询
-- 按照员工主管编号由高到低进行排序，NULL值放到最后边
select * from emp
order by mgr desc # 员工编号由高到低
nulls last; # null值放到最后面
6：分页查询
-- 查询前10条员工的信息
-- 注意：Oracle中不支持limit，需要在原始表加上一列：行号，然后使用子查询来实现分页
select *
from (select rownum hanghao,e.* from emp e) t
where t.hanghao >=1 and t.hanghao <= 10;
7：多表查询
7.1：内连接 - inner join
内连接只返回两个表中满足连接条件的行。
-- 标准写法
select 列名1, 列名2..
from 表1
inner join 表2 on 表1.列 = 表2.列
-- Oracle传统写法
select 列名1, 列名2..
from 表1, 表2
where 表1.列 = 表2.列
-- 示例
-- 标准语法
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
-- 传统语法
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
7.2：外连接 - outer join
外连接返回一个表中的所有行，以及另一个表中匹配的行（如果没有匹配，则返回NULL）。
左外连接
返回左表的所有行，右表不匹配的行显示为NULL
-- 标准语法
SELECT 列名1, 列名2, ...
FROM 表1
LEFT OUTER JOIN 表2 ON 表1.列 = 表2.列;
-- Oracle传统语法(使用+)
SELECT 列名1, 列名2, ...
FROM 表1, 表2
WHERE 表1.列 = 表2.列(+);
-- 实例
-- 标准语法
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
-- 传统语法
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
右外连接
返回右表的所有行，左表不匹配的行显示为NULL。
-- 标准语法
SELECT 列名1, 列名2, ...
FROM 表1
RIGHT OUTER JOIN 表2 ON 表1.列 = 表2.列;
-- Oracle传统语法(使用+)
SELECT 列名1, 列名2, ...
FROM 表1, 表2
WHERE 表1.列(+) = 表2.列;
-- 标准语法
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
-- 传统语法
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
全外连接
返回两个表的所有行，不匹配的行显示为NULL
-- 只有标准语法
SELECT 列名1, 列名2, ...
FROM 表1
FULL OUTER JOIN 表2 ON 表1.列 = 表2.列;
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
7.3：自连接 - self join
一个表与自身连接，常用于层级数据查询。
SELECT a.列名, b.列名 FROM 表 a, 表 b WHERE a.列 = b.列;
-- 查询员工及其经理
SELECT e.employee_id, e.last_name, m.last_name AS manager_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;
7.4：交叉连接 - cross join
返回两个表的笛卡尔积，不使用连接条件
-- 标准语法
SELECT 列名1, 列名2, ...
FROM 表1
CROSS JOIN 表2;
-- 传统语法
SELECT 列名1, 列名2, ...
FROM 表1, 表2;
-- 实例
-- 标准语法
SELECT e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;
-- 传统语法
SELECT e.last_name, d.department_name
FROM employees e, departments d;
7.5：自然连接 - natural join
自动基于相同名称的列进行连接
SELECT 列名1, 列名2, ...
FROM 表1
NATURAL JOIN 表2;
-- 实例
SELECT employee_id, last_name, department_name
FROM employees
NATURAL JOIN departments;
7.6：USING子句连接
指定用于连接的列名（列名在两个表中必须相同）
-- using子句
SELECT 列名1, 列名2, ...
FROM 表1
JOIN 表2 USING (列名);
-- 实例
SELECT employee_id, last_name, department_name
FROM employees
JOIN departments USING (department_id);
8：联合查询
- 列的类型要一致
- 列的顺序要一致
- 列的数量要一致，如果不够，可以使用null填充
8.1：并集运算-union
将两个查询结果进行合并
/*
union : 它会去除重复的，并且排序
union all : 不会去除重复的，不会排序
*/
-- 工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union
select * from emp where deptno = 20;
-- 工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;
8.2：交集运算 - intersect
找两个查询结果的交集
-- 工资大于1500并且20号部门下的员工
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;
8.3：差集运算 - minus
找两个查询结果的差集
-- 1981年入职员工(不包括总裁和经理)
select * from emp where to_char(hiredate,'yyyy') = '1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
9：子查询
- 单行子查询：>、>=、<、<=、!=、<>、=、<=>
- 多行子查询：in、not in、any、some、all、exits
-- 查询所有经理的信息
select * from emp where empno in (select mgr from emp where mgr is not null);
-- 查询不是经理的信息
select * from emp where empno not in (select mgr from emp where mgr is not null);
-- 查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > any (select sal from emp where deptno = 10);
-- 查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > some (select sal from emp where deptno = 10);
-- 查询出比20号部门所有员工薪资高的员工信息
select * from emp where sal > all (select sal from emp where deptno = 20);
-- 查询有员工的部门的信息
select * from dept d1 where exists (select * from emp e1 where e1.deptno = d1.deptno);
二：Oracle函数
1：数值型函数
2：字符型函数
3：日期函数
4：聚组函数
三：数据库级别 - DCL语言
1：创建表空间
表空间是Oracle数据库中的一个重要逻辑存储结构，它是数据库对象的容器，用于组织和管理数据库中的物理存储
表空间的主要的特点
- 逻辑存储单元：表空间是Oracle数据库中的最高层逻辑存储结构
- 物理映射：一个表空间由一个或多个数据文件（Datafile）组成，这些文件是实际的物理操作系统文件
- 对象容器：数据库对象（如表、索引等）都存储在表空间中
表空间的构成
- 数据文件：表空间的物理表现形式，存储在操作系统层面
- 段（Segment）：分配给特定数据库对象（如表、索引）的空间
- 区（Extent）：由一组连续的Oracle块组成，是空间分配的最小单位
- Oracle块：数据库I/O的最小单位，通常为2KB、4KB、8KB等
-- 语法如下
create tablespace 表空间的名称
datafile '文件的路径'
size 初始化大小
autoextend on
next 每一次扩展的大小
-- 示例
create tablespace mytest
datafile 'd:/mytest.dbf'
size 100m
autoextend on
next 10m;
2：删除表空间
-- 语法
drop tablespace 表空间的名称
-- 示例
drop tablespace mytest;
3：创建用户
-- 语法
create user 用户名
indentified by 密码
default tablespace 表空间的名称
-- 示例
create user zhangsan
indentified by 123456
default tablespace mytest;
4：授权用户
系统权限分类：（系统权限只能由DBA用户授出）
DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。RESOURCE：拥有resource权限的用户只可以创建实体，不可以创建数据库结构。CONNECT：拥有connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
实体权限分类：select、update、insert、alter、index、delete、all
-- 语法
grant 系统权限列表 to 用户名
grant 实体权限列表 on 表名称 to 用户名
-- 示例
grant CONNECT to zhangsan; -- 只给张三登录权限
-- 或者
grant CONNECT,RESOURCE to zhangsan; -- 给张三登录和创建实体的权限
-- 或者
grant CONNECT,RESOURCE,DBA to zhangsan; -- 给张三所有的权限
-- 或者
grant DBA to zhangsan; -- 给张三所有的权限
-- 或者
grant all on emp to zhangsan; -- 给张三对于emp表的所有的权限
5：取消授权
-- 下面是取消授权(只能由DBA用户回收)
revoke 系统权限列表 from 用户名;
-- 或者
revoke 实体权限列表 on 表名称 from 用户名;
revoke CONNECT from zhangsan;
或者
revoke CONNECT,RESOURCE from zhangsan;
或者
revoke CONNECT,RESOURCE,DBA from zhangsan;
或者
revoke DBA from zhangsan;
或者
revoke all on emp from zhangsan;
6：修改密码
-- 语法
alter user 用户名 identified by "密码";
-- 演示
alter user zhangsan identified by "123456";
四：表级别 - DDL语言
先简单了解下常用的数据类型
- 字符串类型 -> char, nchar, varchar2, nvarchar2
- 数值类型 -> number(总位数p, 精确位数s), integer, float
- 日期类型 -> date, timestamp
1：创建表
create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);
-- 示例
create table users(
id number,
username varchar2(20),
password varchar2(20)
);
2：表的复制
-- 语法
create table 表名 as 查询语句;
-- 示例
create table emp_copy as (select * from emp);
3：删除表
-- 语法
drop table 表名;
truncate table 表名;
-- 演示
drop table emp_copy;
truncate table emp_copy;
4：修改表
-- 添加一列
alter table 表名 add 列名 列的类型;
alter table users add phone varchar2(11); -- 加一列phone列
-- 修改列名
alter table 表名 rename column 旧名称 to 新名称
alter table users rename column phone to mobile; -- phone列换成mobile列
-- 修改类型
alter table 表名 modify 列名 列的类型;
alter table users modify mobile char(11); -- 将mobile列变成char(11)类型的
-- 删除一列
alter table 表名 drop column 列名;
alter table users drop column mobile; -- 删除一列
-- 修改表名
rename 旧表名 to 新表名;
rename users to myusers; -- 修改表的名称是myusers
5：表的约束
CREATE TABLE 表名(
列名 列的类型 primary key,-- 主键约束
列名 列的类型 not null,-- 非空约束
列名 列的类型 unique,-- 唯一约束
列名 列的类型 check(列名 in (检查列表)),-- 检查约束
constraint 约束名 foreign key(字段名) references 主表(被引用列) -- 外键约束
);
-- 商品分类表
create table category(
cid number primary key,
cname varchar2(20)
);
-- 商品详情表
create table product(
pid number primary key,
pname varchar2(50) not null,
pimg varchar2(50) unique,
pflag varchar2(10) check(pflag in ('上架','下架')),
cid number,
constraint FK_CATEGORY_ID foreign key(cid) references category(cid)
);
-- 约束修改
-- 主键约束
-- 添加
alter table product add constraint PK_PRODUCT_PID primary key(pid);
-- 删除
alter table product drop constraint PK_PRODUCT_PID;
-- 或者
alter table product drop primary key;
-- 非空约束
-- 添加
alter table product modify pname not null;
-- 删除
alter table product modify pname null;
-- 预检约束
-- 添加
alter table product add constraint CK_PRODUCT_PFLAG check(pflag in ('上架','下架'));
-- 删除
alter table product drop constraint CK_PRODUCT_PFLAG;
-- 外键约束
-- 添加
alter table product add constraint FK_PRODUCT_ID foreign key(cid) references category(cid);
-- 删除
alter table product drop constraint FK_PRODUCT_ID;
五：增删改 - DML语言
1：插入语句
insert into 表名(列名1,列名2,...) values(值1,值2,...);
insert into category(cid,cname) values(1,'电视');
注意：commit;
2：修改语句
update 表名 set 列名1=值1,列名2=值2,... where 查询条件;
update category set cname='汽车' where cid = 1;
注意：commit;
3：删除语句
delete from 表名 where 查询条件;
delete from category where cid = 1;
注意：commit;
六：事务 - TCL语言
1：事务基础
一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
CAID
- 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
- 一致性：一个事务的执行不能破坏数据库数据的完整性和一致性
- 隔离性：一个事务不受其它事务的干扰，多个事务是互相隔离的
- 持久性：一个事务一旦提交了，则永久的持久化到本地
2：Oracle中的事务
1️⃣ 开启事务 -> Oracle 11g中事务是隐式自动开始的，它不需要用户显示的执行开始事务语句
2️⃣ 编写一组逻辑sql语句 -> sql语句支持的是insert、update、delete
3️⃣ 提交事务commit & 回滚rollback
1、开启事务
Oracle 11g中事务是隐式自动开始的，它不需要用户显示的执行开始事务语句
2、编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete
【设置回滚点】
savepoint 回滚点名;
3、结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方： rollback to 回滚点名;
3：事务读问题
多个事务同时操作同一个数据库的相同数据时
- 脏读：一个事务读到了另一个事务还未提交的update数据，导致多次查询的结果不一样（Oracle中不会产生）
- 不可重复读：一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致
- 幻读：一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样
可以通过设置隔离级别解决上述并发问题
Oracle中只支持READ COMMITTED、SERIALIZABLE、READ ONLY、READ WRITE
4：事务写问题
在事务的隔离级别内容中，能够了解到两个不同的事务在并发的时候可能会发生数据的影响。
细心的话可以发现事务隔离级别章节中，脏读、不可重复读、幻读三个问题都是由事务A对数据进行修改、增加，事务B总是在做读操作。
如果两事务都在对数据进行修改则会导致另外的问题：丢失更新
悲观锁：认为两个事务更新操作一定会发生丢失更新
解决：通过在语句后边添加for update来实现行级上锁，所以又称为“行级锁”
例如：select * from t_account t wheret.id=‘1’ for update;
乐观锁：认为事务不一定会产生丢失更新，让事务进行并发修改，不对事务进行锁定
解决：由程序员自己解决，可以通过给数据表添加自增的version字段或时间戳timestamp，进行数据修改时，数据库会检测version字段或者时间戳是否与原来的一致，若不一致，抛出异常或者重新查询
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
