Oracle基础语法总结

Oracle基础语法总结

开发工具：Visual Studio 2015、SQL Server 2014 Management Studio
关键技术：C# MVC
作者：刘东标
撰写时间：2019年3月27日
02过滤和排序数据
（1）算术运算符

• 选择全部列 ，+ 加，- 减 ， * 乘，/ 除，|| 连接符
  日期和字符只能在单引号中出现
  distinct 删除重复行
  （2）比较运算
  = 等于（不是==），> 大于，>=大于、等于，< 小于，<=小于、等于，<>不等于（也可以是!=）
  （3）其他比较运算
  between…and… 在两个值之间（包含边界），in(set) 等于值列表中的一个，is null 空值
  like 模糊查询 %代表零个或者多个字符（任意个字符） _代表一个字符，escape 使用转义符
  （4）逻辑运算
  and 逻辑并，or 逻辑或，not 逻辑否，order by语句，asc（ascend）:升序，desc（descend）:降序
  ，order by子句在select语句的结尾
  03单行函数
  字符函数
  （1）、大小写控制函数
  lower 小写，upper 大写，initcap 首字母大写
  （2）、字符控制函数
  concat(‘字符’,‘字符’) 两个字符拼接，substr(‘字符’,从哪个字符开始,截取个数) ，
  length(‘字符’) 字符长度，instr(‘字符’，‘某个字符串’) 从开始字符串到某个字符串的长度
  lpad(‘sal’，10,’’) 格式化sal为 10 个字符长度，用左填充
  rpad(‘sal’，10,’’) 格式化sal为 10 个字符长度，用右填充
  trim(‘H’ from ‘Hello’) 可以去掉首尾字符，默认去到空格
  replace(‘abcd’,‘b’,‘123456’) 从abcd字符找出b字符，用123456字符替代b字符
  （3）数字函数
  round(45.156,2)–45.16 四舍五入，trunc(45.156,2)–45.15 截取，mod(1600,300)–100 求余
  （4）日期函数
  months_between(‘01-12-2019’,‘01-01-2019’)–11 两个日期相差的月数
  add_months(‘01-01-2019’,6)–01-07-2019 向指定日期中加上若干月数
  next_day 指定日期的下一个星期对应的日期，last_day 本月的最后一天
  round 日期四舍五入，trunc 日期截断
  yyyy 年，mm 月，dd 日，day 星期，hh24 小时，mi 分钟，ss 秒
  $ 美元符，L 本地货币符号，.小数点，,千位符
  select tochar(sysdate,‘yyyy-mm-dd hh:mi:ss’) from dual;
  select to_number(‘￥1,234,567,890’,‘L999,999,999,999.99’) from dual
  （5）通用函数
  NVL(expr1,expr2)将空值转换成一个已知的值
  NVL2(expr1,expr2,expr3)expr1不为NULL，返回expr2；为NULL，返回expr3。
  nullif(expr1,expr2)相等返回NULL，不等返回expr1
  coalesce(expr1,expr2,…,exorn)如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE
  条件表达式
  CASE expr WHEN comparison_expr1 THEN return_expr1
  [WHEN comparison_expr2 THEN return_expr2
  WHEN comparison_exprn THEN return_exprn
  ELSE else_expr]
  END
  select salary,decode(department_id,10,salary1.1,
  20,salary1.2,
  30,salary1.3)
  from employees where department_id in(10,20,30)
  04多表查询
  （1）外连接语法
  left join…on 左连接，right join…on 右连接
  （2）自连接
  同一张表，where worker.manager_id = manager.employee_id
  SELECT worker.last_name || ’ works for ’
  || manager.last_name
  FROM employees worker, employees manager
  WHERE worker.manager_id = manager.employee_id
  （3）叉集 cross join
  使用CROSS JOIN 子句使连接的表产生叉集。
  叉集和笛卡尔集是相同的
  SELECT last_name, department_name FROM employees CROSS JOIN departments ;
  （4）自然连接 natural join
  SELECT department_id, department_name, location_id, city
  FROM departments natural join locations ;
  (会自动匹配表中相同的列，可多列匹配；)
  （5）使用 USING 子句创建连接
  在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。
  使用 USING 可以在有多个列满足条件时进行选择。
  不要给选中的列中加上表名前缀或别名。
  JOIN 和 USING 子句经常同时使用。
  select last_name,department_name from employees
  join departments using (department_id);
  （6）左外连接left outer join
  （7）右外连接right outer join
  （8）满外连接full outer join
  05分组函数
  avg()平均数，count()统计个数，max()最大值，min()最小值，sum()求和

使用GROUP BY 子句对数据分组。，使用HAVING 子句过滤分组结果集。组函数忽略空值。
SELECT AVG(commission_pct)
FROM employees;
NVL函数使分组函数无法忽略空值。
COUNT(DISTINCT expr)返回expr非空且不重复的记录总数
06子查询
（1）单行子查询
1、只返回一行。
2、使用单行比较操作符
= equal to,，> greater than，>= greater than or equal to，< less than
<= less than or equal to，<> not equal to
（2）多行子查询
1、返回多行。，2、使用多行比较操作符。
in 等于列表中的任意一个，any 和子查询返回的某个值比较 任一，all 和查询返回的所有值比较 所有
07.创建和管理表
select * from user_tables;–查看用户创建的表
select table_name from user_tables;–查看用户定义的表
select distinct object_type from user_objects; --查看用户定义的各种数据库对象
select * from user_catalog;–查看用户定义的表, 视图, 同义词和序列
（1）/表名和列名:
必须以字母开头，必须在 1–30 个字符之间，必须只能包含 A–Z, a–z, 0–9, _, KaTeX parse error: Expected 'EOF', got '#' at position 5: , 和 #̲ 必须不能和用户定义的其他对象…abc, 2abc, _abc, a-b，–a#d
create table ceshi(id number(2),name varchar2(14))–创建表
create table emp1 as select from employees;–使用子查询创建表
create tbale emp2 as select * from employees where 1=2;–创建的emp2是空表
（2）数据类型
varchar2(size) 可变长字符数据，char(size) 定长字符数据，number(p,s) 可变长数值数据
date 日期型数据，long 可变长字符数据，最大可达到2G，clob 字符数据，最大可到达4G
raw(long raw) 原始的二进制数据，blob 二进制数据，最大可达到4G
bfile 储存外部文件的二进制数据，最大可达到4G，rowid 行地址
（3）/使用 ALTER TABLE 语句可以:
追加新的列、修改现有的列、为新追加的列定义默认值、删除一个列、重命名表的一个列名/
alter table ceshi add(job_id varchar(9));–使用 ADD 子句追加一个新列
alter table ceshi modify(job_id number(9,2) default 1000)–可以修改列的数据类型, 尺寸和默认值
alter table ceshi drop column job_id;–使用 DROP COLUMN 子句删除不再需要的列.
–执行RENAME语句改变表, 视图, 序列, 或同义词的名称
–必须是对象的拥有者
alter table ceshi rename column job_id to job_id2;
（4）/数据和结构都被删除
所有正在运行的相关事务被提交、所有相关索引被删除、DROP TABLE 语句不能回滚/
（5）/清空表/
/TRUNCATE TABLE 语句:删除表中所有的数据、释放表的存储空间/
/*TRUNCATE语句不能回滚 列子：truncate table ceshi;
（6）可以使用 DELETE 语句删除数据,可以回滚
对比：delete from emp2; select * from emp2; rollback;
（7）/create table 创建表alter table 修改表结构 drop table 删除表 rename to 重命名表 truncate table 删除表中的所有数据，并释放储存空间//commit 提交 rollback 回滚/
08、数据处理
（1）–插入数据 insert into表名 values(…)
/*为每一列添加一个新值。
按列的默认顺序列出各个列的值。
在 INSERT 子句中随意列出列名和他们的值。
字符和日期型数据应包含在单引号中。
insert into ceshi(id,name) values(1,1);
insert into ceshi select employee_id,last_name where 1=2
（2）插入数据(字符和日期型数据应包含在单引号中)
列子：insert into 表名(字段,字段，…) values(‘1,’,‘2’,…)
（3）在SQL 语句中使用 & 变量指定列值。
& 变量放在VALUES子句中。列子：insert into 表名 values(’&1’,’&2’,…)
（4）从其它表中拷贝数据
insert into emp2 select * from employees where department_id = 90;
（5）使用 UPDATE 语句更新数据。 列子：update 表名 set department_id where 条件
如果省略where 子句，则表中所有数据都将被更新 、使用 DELETE 语句从表中删除数据。
delete from 表名 where 条件
总结insert 插入、update 修正、delete 删除、commit 提交、savepoint 保存点、rollback 回滚
select * from ceshi insert into ceshi(id,name,job_id2) values (2,1,1);
update ceshi set job_id2 =‘邓冠杰66’,name=‘杨子演66’ where id =2;
savepoint a; rollback to a;
update ceshi set job_id2 =‘邓冠杰22’,name=‘杨子演22’ where id = 1;
savepoint b;
delete from ceshiwhere id = 3;
09、约束
constraint 约束 、not null 非空、unique 唯一性 空值不受影响、primary key 主键
foreign key 外键、check 检查100<salary<200是否在范围之内
（1）PRIMARY KEY 约束
可以定义在表级或列级:
CREATE TABLE departments(
department_id NUMBER(4),
department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
（2）FOREIGN KEY 约束
可以定义在表级或列级:
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
…
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
（3）FOREIGN KEY 约束的关键字
–FOREIGN KEY: 在表级指定子表中的列
–REFERENCES: 标示在父表中的列
–on delete cascade(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
–on delete set null(级联置空): 子表中相应的列置空
create table emp(
id number(6) primary key,
name varchar2(25) unique,
sal number(8,2),
dept_id number(4),
constraint dept_fk foreign key(dept_id) reference dept(dept_id) on delete cascade
)
（4）添加约束的语法
使用 ALTER TABLE 语句:添加或删除约束,但是不能修改约束、有效化或无效化约束
添加 NOT NULL 约束要使用 MODIFY 语句
create table emp as select * from employees
Alter table emp modify(empname varchar2(50) not null);
添加约束 alter table 表名 add constraint 约束名 foreign key(manager_id) references 表名(employee_id)
删除约束 alter table 表名 drop constraint 约束名
无效化约束 alter table 表名 disable constraint 约束名
激活约束 alter table 表名 enable constraint 约束名
查询约束 select constraint_name,constraint_type,search_condition
from user_constraints where 条件
查询定义约束的列 select constraint_name,column_name
from user_cons_columns
10、视图
创建视图create view emp1 as select * from employees、描述视图结构 describe emp1
修改视图create or replace view empls as select * from employees where 条件
（1）–create view子句中各列的别名应和子查询中各列相对应
视图中使用DML的规定
当视图定义中包含以下元素之一时不能使用update:
组函数、GROUP BY子句、DISTINCT 关键字、ROWNUM 伪列、列的定义为表达式
当视图定义中包含以下元素之一时不能使insert:
组函数、GROUP BY 子句、DISTINCT 关键字、ROWNUM 伪列、列的定义为表达式
、表中非空的列在视图定义中未包括
（2）屏蔽 DML 操作
可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作
任何 DML 操作都会返回一个Oracle server 错误
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;
（3）删除视图只是删除视图的定义，并不会删除基表的数据 drop view 视图名;
（4）对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据
–练习2：查询员工表中，工资排名在10-20之间的员工信息。
select * from (select rownum s,salary from(select salary
from employees order by salary desc) where rownum<=20)where s>=10
11、其它数据库对象
（1）–创建序列sequence
create sequence
compange_id
increment by 10, --每次增长的数值
start with 9999,–从哪个值开始
maxvalue 9999,–最大值 nomaxvalue
minvalue 20,–最小值 nominvalue
nocycle,–是否需要循环 cycle
nocache–是否缓存登录; cache
（2）NEXTVAL 和 CURRVAL 伪列
NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用、CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ，否则会报 CURRVAL 尚未在此会话中定义的错误。
select compange_id.nextval from dual;–下一个值
select compange_id.currval from dual;–当前的值
select sequence_name,min_value,max_value,increment_by,last_number
from compange_id;
insert into ceshi values(compange_id.nextval,‘陈希雄’,‘01-1月-19’)
select * from ceshi;
（3）删除序列：drop sequence compange_id

（4）使用序列
•将序列值装入内存可提高访问效率 •序列在下列情况下出现裂缝:
–回滚 –系统异常 –多个表同时使用同一序列
•如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值
（5）修改序列：alter sequence compange_id
increment by 10 --每次增长的数值–start with 9999–从哪个值开始
maxvalue 9999–最大值 nomaxvalue 、minvalue 20–最小值 nominvalue
nocycle–是否需要循环 cycle 、nocache–是否缓存登录; cache
（6）/必须是序列的拥有者或对序列有 ALTER 权限
只有将来的序列值会被改变、改变序列的初始值只能通过删除序列之后重建序列的方法实现/
1、索引:•一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
•索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
•索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
•在删除一个表时,所有基于该表的索引会自动被删除
•通过指针加速 Oracle 服务器的查询速度
•通过快速定位数据的方法，减少磁盘 I/O
（1）–修改索引alter index compange_id on ceshi(name)
（2）–删除索引 drop index compange_id
（3）–创建索引create index compange_id on ceshi(name);
（4）–同义词-synonym 使用同义词访问相同的对象:方便访问其它用户的对象、缩短对象名字的长度*/
create synonym e for ceshi; select * from e; drop synonym e;
（5）什么时候创建索引：以下情况可以创建索引
-列中数据值分布范围很广、-列经常在where子句或连接条件中出现
-表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
（6）什么时候不要创建索引：下列情况不要创建索引
-表很-列不经常作为连接条件或出现在where子句中、-查询的数据大于2%到4%、-表经常更新
–12、控制用户权限
/权 限：数据库安全性:、系统安全性、数据安全性
系统权限: 对于数据库的权限、对象权限: 操作数据库对象的权限/

/系统权限:超过一百多种有效的权限、数据库管理员具有高级权限以完成管理任务，例如:
创建新用户、删除用户、删除表、备份表/
（1）–创建用户create user test1 identified by test1;
–DBA(数据库管理员) 会赋予用户一些系统权限、–grant：授予
–以应用程序开发者为例, 一般具有下列系统权限:grant create session to test;
grant create session（创建会话）、grant create table（创建表）
grant create sequence（创建序列）、grant create view（创建视图）
grant create procedure（创建过程）
GRANT create session, create table, create sequence, create view TO test;
（2）–分配用户表空间
–用户拥有create table权限之外，还有分配相应的表空间才可以开辟储存空间用于创建的表
alter user test1 quota unlimited on users
–创建角色并赋予权限
（1）–创建角色create role manager;
（2）–为角色赋予权限grant create table,create view to manager;
（3）–将角色赋予用户grant manager to test1;
（4）–删除角色drop role manager;
（5）/对象权限：不同的对象具有不同的对象权限、对象的拥有者拥有所有权限
对象的拥有者可以向外分配权限/
（6）–分配对象权限
–修改(alter),删除(delete),索引(index),插入(insert),关联(union),选择(select),更新(update)
（7）–分配表 EMPLOYEES 的查询权限grant select on system.employees to test;
（8）–分配表中各个列的更新权限grant update on system.employees to test
（9）–with grant option使用户具有分配权限的权利
grant select,insert on system.departments to test1 with grant option;
（10）–public向数据库所有用户分配权限：grant select on system.departments to public;

（11）–查询权限分配情况
select * from user_sys_privs;
role_sys_privs 角色拥有的系统权限
role_tab_privs 角色拥有的对象权限
user_role_privs 用户拥有的角色
user_tab_privs_made 用户分配的关于表对象权限
user_tab_privs_recd 用户拥有的关于表对象权限
user_col_privs_made 用户分配的关于列的对象权限
user_col_privs_recd 用户拥有的关于列的对象权限
user_sys_privs 用户拥有的系统权限
（11）收回对象权限:使用revoke语句回收权限、使用with grant option子句所分配权限同样被回收
指定收回对象权限列子：revoke select,insert on employees from test;
全部收回对象权限列子：revoke all on departments from test1;
总结：create 创建用户(通常由DBA完成)、grant 分配权限create role 创建角色(通常由DBA完成)
alter user 修改用户密码、revoke 回收权限
（12）–修改用户：/DBA 可以创建用户和修改密码
用户本人可以使用 ALTER USER 语句修改密码/
alter user test identified by test;
（13）–删除 drop user test1 cascade–强制删除用户(包括用户所有东西)
rowid – 主要用于删除重复数据、rownum – 主要用于oralce查询分页
cast 转化专用:select sysdate-cast(‘20190101’ as date) from dual;
select cast(‘20195148471’ as number) from dual;
13、SET运算符
（1）union操作符返回两个查询的结果集的并集
select employee_id,job_id,department_id from employees
union select employee_id,job_id,department_id from employees;
（2）union all 操作符返回两个查询的结果的并集，对于两个结果集的重复部分，不去重。
select employee_id,job_id,department_id from employees
union all select employee_id,job_id,department_id from employees;
（3）intersect 操作符返回两个结果集的交集
select employee_id,job_id,department_id from employees
intersect select employee_id,job_id,department_id from employees;
（4）minus操作符：返回两个结果集的差集
select employee_id,job_id,department_id from employees
minus select employee_id,job_id,department_id from employees;