Oracle基本语法的使用

Oracle的基本使用

1.1 DCL语句

1.1.1 表空间的创建

表空间的作用

可以使用表空间限制数据库文件的大小
利用表空间将数据文件存放到不同的磁盘上,提高IO性能，利于数据的备份和恢复
创建表空间语法：
CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integer [K|M]]
[AUTOEXTEND [OFF|ON]];

create tablespace myspace
datafile 
  'e:/my01.dbf' size 5 M,
  'd:/my02.dbf' size 10 M

-- 创建表时,为表指定表空间
 create table person
 (
    pid number ,
    pname varchar2(20) ,
    page number(3),
    birthday date,
    address varchar2(20) default '不详'
 )tablespace myspace;

1.1.2 账号的创建

同一数据库中可以同时有多个用户，每个用户管理自己的数据库对象．比如数据库表、索引、视图等。。
Oracle中的CREATE USER命令用于创建新用户。每个用户都有一个默认表空间和一个临时表空间。如果没有指定，Oracle就将SYSTEM设为默认表空间，将TEMP设为临时表空间。

CREATE USER ahx
IDENTIFIED BY 123456
DEFAULT TABLESPACE myspace;

新创建的账号默认为锁定状态并且没有连接权限，需要解锁并授权

--将指定用户解锁
alter user ahx account unlock;

1.1.3 角色权限认证

Oracle中的常用系统预定义角色如下。

CONNECT：临时用户，特别是那些不需要创建表的用户，通常赋予该角色。
RESOURCE：更为可靠和正式的数据库用户可以授予该角色，可以创建表、触发器、过程等。
DBA：数据库管理员角色，拥有管理数据库的最高权限。一个具有DBA角色的用户可以撤销任何别的用户甚至别的DBA权限，这是很危险的，所以不要把该角色轻易授予一些不是很重要的用户。
给用户分配权限或角色

GRANT命令用于为用户分配权限或角色，而REVOKE命令用于为用户撤销权限和角色

授权

分配权限或角色语法：GRANT [<权限> | <角色>] TO <用户>;

--将指定用户授权
grant connect,dba,resource to ahx

grant create session,create table to ahx;--为用户分配权限
grant connect to ahx;--为用户分配角色
grant resource to ahx;
--grant connect,resource to ahx;--为用户分配角色

--为ahx分配查询scott账号下的emp表的权限
 -- grant select|insert|update|delete on 表 to 用户
 grant select on scott.emp to ahx
 --以xiaoming账号登录查询
 select * from scott.emp

在进行权限赋权时可以为指定账号单独赋予权限，也可以赋予角色，直接拥有角色所拥有的权限

取消授权

撤销权限和角色语法：REVOKE [<权限> | <角色>] FROM <用户>;

1.2 DDL语句

由于oracle没有库的概念，所以创建表空间就是建库语句，oracle的ddl语句经常指的是创建表或对表的列进行修改的语句

1.2.1 CREATE TABLE 语句

除数据类型外与mysql建表语句语法相同，并且oracle允许检查约束（一般交由服务器处理）

---创建表: create table 表名(列名1 数据类型 [约束])
create table dept
(
   deptno number(6) primary key,
   deptname varchar2(50) not null,
   deptnum  number(5)  check (deptnum>=0),
   loc varchar(100) default '郑州' 
)

create table employee
(
   empId number(10) primary key,
   empName varchar2(50) not null,
   empAge number(3) check(empAge>=18 and empAge<=60),
   deptno number(6) references dept(deptno)
)

Oracle没有主键自增的概念，如果需要主键自增可以通过序列解决

1.2.2 ALTER TABLE语句

对已有表的列进行操作

增加

语法: alter table tableName add columnName dataType;

alter table employee add empTel varchar2(11);

修改

语法: alter table tableName modify columnName dataType;

alter table employee modify empTel varchar2(14);

删除

语法: alter table tableName drop column columnName;

alter table employee drop column empTel;

对列添加约束

语法:alter table tableName add constraint constraintName constraintType(columnName)

alter table dept add constraint un_deptname unique(deptname);

从视图USER_CONS_COLUMNS中查看约束

select  constraint_name,column_name from user_cons_columns 

1.2.3 序列

序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数

序列的主要用途：

• 主键、外键值应用需求
• 流水号应用需求
• 序列的生成与定义的内容

序列语法：

CREATE SEQUENCE sequnce_name
[START WITH n1] //指定要生成的第一个序列号 (从n1 开始)
[INCREMENT BY n2] //用于指定序列号之间的间隔，默认值为1
[{MAXVALUE n3 | NOMAXVALUE}] //指定序列可以生成的最大值
[{MINVALUE n4 | NOMINVALUE}] //指定序列可以生成的最小值
[{CACHE n5 | NOCACHE}] //用于指定在高速缓存中可以预分配的序列号个数，默认为20
[{CYCLE | NOCYCLE}] //用于指定在达到序列的最大值或最小值后是否循环
[ORDER]; //用于指定按顺序生成序列号 ，确保序列唯一和有序

create sequence ms
   start with 1
   increment by 1
   maxvalue 5
   minvalue 1
   nocycle
   cache 10;

使用

通过nextval获取下一个序列的值

select ms.nextval from dual；

通过currval获取当前序列的值

select ms.currval from dual;

序列的使用

--创建序列
create sequence ms
start with 1  --从1开始
increment by 1 --序列的间隔(每次变化的数值)
maxvalue 10000000 --最大值
minvalue 1   --最小值
nocycle --不循环
cache 10 

 select * from dual
 select my_seq.nextval from dual
 select my_seq.currval from dual
   
 select * from employee
   --Oracle中没有主键自增的概念,但可以通过自定义序列来实现
   insert into employee(empId,empName,empAge,deptno) values(ms.nextval,'zhangsan',20,10)

修改序列

#更改序列
ALTER SEQUENCE ms maxvalue 5000 cycle;
#删除序列
DROP SEQUENCE ms;

1.3 DML语句

oracle基本语句使用的还是sql所以与mysql学习时使用的dml语句基本保持一致

1.3.1 insert 增加

insert into 表名(列名1,列名2,列名3…)values(值1, 值2 ,值3…)

由于没有主键自增所以在添加时使用序列进行数据的id生成

1.3.2 update 修改

update 表名 set 列名1=值1, 列名2=值2 … [where 条件]

1.3.3 delete删除

delete from 表名 [where 条件]

基本查询语句练习

--使用scott账号练习基本查询语句
--1 查询当前用户下的所有表
select * from tab;
--2 查询雇员表中所有信息
select * from emp;
--3 查询雇员编号,姓名,工作,工资
select empno,ename,job,sal from emp
--4 查询雇员编号,姓名,工作,工资,并显示中文(为列起别名)
select empno as 编号,ename as 姓名,job as 工作,sal as 工资 from emp
--5 消除重复列,查询雇员工作种类
select distinct job from emp
--6 字符串连接操作(||)
--查询雇员编号,姓名,工作.按以下格工显示:编号:7369,姓名:Smith,工作:Clerk
select '编号:'||empno,'姓名:'||ename,'工作:'||job from emp
--7 查询列支持四则运算(年薪=(工资+奖金)*12)
--查询雇员编号,姓名,工作,年薪 
select empno,ename,job,(sal+nvl(comm,0))*12 from emp
nvl(comm,0)==>如果comm值为空，取值0

--8 Where条件查询
-- 查询工资大于1500的所有雇员
select * from emp where sal>1500
--查询可以得到奖金的所有雇员
select * from emp where comm is not null
--查询工资大于1500或可以得到奖金的雇员
select * from emp where sal>1500 or comm is not null
--查询工资大于1500并且可以领取奖金的雇员
select * from emp where sal>1500 and comm is not null
--查询工资不大于1500或者不可以领取奖金的雇员
select * from emp where sal<=1500 or comm is null
--查询工资在1500到3000的所有雇员信息
select * from emp where sal>=1500 and sal<=3000
select * from emp where sal between 1500 and 3000
--查询在1981年雇用的员工信息
select * from emp where hiredate like '%81%'
--查询雇员姓名中第二个字母为"M"的雇员
select * from emp where ename like '_M%'
--查询雇员工资中带8这个数字的
select * from emp where sal like '%8%'
--查询编号是7369,7499,7521,7799的雇员信息
select * from emp where empno=7369 or empno=7499 or empno=7521 or empno=7799
select * from emp where empno in(7369,7499,7521,7799)
--查询雇员编号不是7369,7499,7521,7799的所有雇员信息
select * from emp where empno not in(7369,7499,7521,7799)
--查询雇员编号为7369的雇员信息
select * from emp where empno =7369
--查询雇员编号不为7369的雇员信息
select * from emp where empno !=7369
select * from emp where empno <>7369

--查询雇员信息,按工资由低到高排序
select * from emp order by sal asc
--查询雇员信息,按工资由高到低排序
select * from emp order by sal desc

--操作集合:
--union:将两个记录合并,去掉重复项 
select distinct deptno from emp union select deptno from dept;
--union:将两个记录合并,不去掉重复项 
select distinct deptno from emp union all select deptno from dept;
--intersect:取两个集合的交集
select distinct deptno from emp intersect select deptno from dept;
--minus:去掉交集(集合A-(集合A和集合B的交集))
select deptno from dept minus select distinct deptno from emp ;