Oracle SQL语法实例合集

最新推荐文章于 2024-04-19 21:12:28 发布

weixin_34356138

最新推荐文章于 2024-04-19 21:12:28 发布

阅读量188

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/feistel/blog/3052024

版权

2019独角兽企业重金招聘Python工程师标准>>>

如需转载请注明出处https://my.oschina.net/feistel/blog/3052024
目的：迅速激活Oracle SQL
参考：《Oracle从入门到精通》

----------------------------------------------------------------------------------------------------------------
DDL
create/drop/alter。

--创建表产地
create table origin (
OriginId varchar2(10),
OriginName varchar2(20),
constraint pk_origin primary key(OriginId)
);

-- 创建表商品
create table productinfo (
ProductId        varchar2(10),
ProductName    varchar2(20) not null,
ProductPrice    number(8,2) not null,
OriginId        varchar2(10)
-- 主键约束
-- constraint pk_productinfo primary key(ProductId)
--外键约束
/*constraint fk_productinfo foreign key(OriginId)
references origin(OriginId)
-- 级联删除（主键所在表的记录删除时，该表记录也被删除）
on delete cascade*/
);

-- 删除字段
alter table productinfo
drop column ProductPrice;

-- 添加字段
alter table productinfo
add ProductPrice number(8,2);

-- 修改字段类型
alter table productinfo
modify ProductPrice number(10,2);

-- 添加主键约束
alter table productinfo
add constraint pk_productinfo primary key(ProductId);

-- 移除主键约束
alter table productinfo
drop constraint pk_productinfo;

-- 添加外键约束
alter table productinfo
add constraint fk_productinfo foreign key(OriginId)
references origin(OriginId)
-- 级联删除（主键所在表的记录删除时，该表记录也被删除）
on delete cascade;

-- 删除外键约束
alter table productinfo
drop constraint fk_productinfo;

-- 添加check约束
alter table productinfo
add constraint ch_productinfo check(ProductPrice>0);

-- 删除check约束
alter table productinfo
drop constraint ch_productinfo;

-- 添加unique约束
alter table productinfo
add constraint uni_productinfo unique(ProductPrice);

-- 删除unique约束
alter table productinfo
drop constraint uni_productinfo;

-- 设置not null约束
alter table productinfo
modify OriginId not null;

-- 取消not null约束
alter table productinfo
modify OriginId null;

-- 删除表商品
drop table productinfo;

-- 删除表产地
drop table origin;

----------------------------------------------------------------------------------------------------------------
DML
对数据库中的数据进行操作，增、删、改，查作为DQL。

-- 增加数据
insert into origin(OriginId, OriginName)
values('1', '广东省深圳市');
insert into origin(OriginId, OriginName)
values('2', '海南省万宁市');
insert into origin
values('3', '广东省深圳市');
insert into origin
values(null, '广东省深圳市');
insert into productinfo
values('1', '牙刷', 9.9, '1');
insert into productinfo
values('2', '牙膏', 16, '2');
insert into productinfo
values('3', '牙刷', 12, '2');
insert into productinfo
values('4', '牙刷', 16, '2');
insert into productinfo
values('5', '牙刷', 100, '100');

/*-- 通过表快速创建子表
create table origin2 as
select OriginId, OriginName
from origin;
drop table origin2;*/

-- 修改数据
update origin set OriginName = '广东省广州市' where OriginId = '3';

-- 删除数据
delete from origin where OriginId = '3';

-- 截断表（比 delete from origin 快）
truncate table origin;

----------------------------------------------------------------------------------------------------------------
DQL

-- 降序查询desc(默认升序asc)
-- 多列排序
-- ull值默认为最大（可以修改nulls last）
select OriginId 产品ID, OriginName 产品名称
from origin
order by 2 asc, OriginId desc
nulls first;

--Oracle架构与MySQL不同：
--1）Oracle，一个用户对应一个模式，这里的模式对应MySQL中的数据库。
----对于Oracle来说没有数据库这个说法。
----Oracle实例下有多个模式，模式下有多个表。
--2）MySQL，一个用户可以管理多个数据库。
----MySQL下有多个数据库，数据库下有多个表。
----MySQL中新建数据库，可以看作Oracle实例下新建模式，即创建新用户，因为一个用户对应一个模式。
-- 查询其他模式下的表
select system.help.info from system.help;

-- ||表示连接字符串
select ProductName, ProductPrice || '*0.8=' || ProductPrice*0.8 as DiscountPrice
from productinfo;

-- 除去重复
select distinct(OriginName) from origin;

-- 条件查询
-- 不等于也可以用!=
-- 模糊查询（_匹配单字符，%匹配多个字符）
-- in在某个范围之内
-- is null查询空
select *
from origin
where OriginName <> '海南省万宁市'
and OriginId between '1' and '3'
and OriginName like '%广东%'
and OriginId in('1','2')
and OriginId is not null;

-- group by / having子句
-- 先where筛选再分组
-- 根据产品名分组后再根据产地ID分组，计算平均价格，最后having筛选
select ProductName, avg(ProductPrice) 平均价格, OriginId
from productinfo
where OriginId <= 2
group by ProductName, OriginId
having avg(ProductPrice) <= 10;

-- 子查询
-- >=any表示大于它的最小值
-- <=any表示小于它的最大值
-- >=all表示大于它的最大值
-- <=all表示小于它的最小值
select *
from productinfo
where ProductPrice >=
any (select ProductPrice from productinfo where ProductId != '1')
and ProductPrice <=
all (select ProductPrice from productinfo where ProductId != '1');

-- 连接查询内连接=简单连接
select p.ProductId, p.ProductName, o.OriginName
from productinfo p, origin o
where p.OriginId = o.OriginId;
-- 另一种写法（内连接）
-- select p.ProductId, p.ProductName, o.OriginName
-- from productinfo p inner join origin o on p.OriginId = o.OriginId;

-- 外连接
-- left join左外连接
-- right join右外连接
-- full join全连接
select p.ProductId, p.ProductName, o.OriginName
from productinfo p left join origin o on p.OriginId = o.OriginId;
--另一种方法(左外连接)
select p.ProductId, p.ProductName, o.OriginName
from productinfo p, origin o
where p.OriginId = o.OriginId(+);

-- 自连接
-- 查询自身价格相同的商品
select p1.ProductId, p1.ProductName
from productinfo p1, productinfo p2
where p1.ProductPrice = p2.ProductPrice
and p1.ProductName <> p2.ProductName;

----------------------------------------------------------------------------------------------------------------
DCL

-- 创建默认用户
create user kangkang
identified by 123456;
grant create session to kangkang;

-- 创建用户
-- 验证方式为口令验证，口令123456
-- 设置默认表空间
-- 设置默认临时表空间
-- 设置该用户能使用的表空间大小（unlimited不受限制）
-- 设置概要文件
-- 设置口令过渡状态，第一次登陆成功需要修改口令
-- 设置账户解锁
create user lilei
identified by 123456
default tablespace USERS
temporary tablespace TEMP
quota 10M on USERS
--profile DEFAULT
--password expire
account unlock;
-- 赋予用户创建会话的权限
grant create session to lilei;

-- 修改用户
alter user lilei
identified by 888888
password expire;

-- 删除用户
drop user lilei cascade;

-- 给用户授权系统权限
-- 用户也具有授予all privileges的权限
grant all privileges to lilei
with admin option;

-- 给用户授权对象权限(可以是all)
-- 用户也具有授予对象权限的权限
grant insert,delete on origin to lilei
with grant option;

-- 撤销权限系统权限
revoke all privileges from lilei;

-- 撤销权限对象权限
revoke insert,delete on origin from lilei;

-- 查询用户权限数据字典
-- 系统权限 DBA_SYS_PRIVS
-- 对象权限 DBA_TAB_PRIVS
select * from DBA_SYS_PRIVS
where grantee = 'lilei';

-- 创建角色
create role all_privileges
--not identified
identified by 123456;

-- 给角色授权
grant all privileges to all_privileges;

-- 设置角色
grant all_privileges to lilei;

-- 在当前用户设置角色
set role all_privileges identified by 123456;

-- 修改角色
alter role all_privileges
not identified;

-- 删除角色
drop role all_privileges;

-- 查询角色
select GRANTED_ROLE.DEFAULT_ROLE from DBA_ROLE_PRIVS
where grantee = 'LILEI';

----------------------------------------------------------------------------------------------------------------
如需转载请注明出处https://my.oschina.net/feistel/blog/3052024
参考：《Oracle从入门到精通》

转载于:https://my.oschina.net/feistel/blog/3052024