oracle笔记--sql
sql 操作
常用语句
注释
单行注释
--注释内容
多行注释
/*
注释内容
*/
表空间,表,表结构查询语句
查询某表所在表空间
select tablespace_name from all_tables where table_name = upper(' [table_name] ');
显示当前数据库的所有表和视图
select * from tab;
显示表空间中的所有表
select TABLE_NAME,TABLESPACE_NAME from all_tables where TABLESPACE_NAME=upper(' [tablespace_name] ');
常用创建语句
sql 建表语句
oracle建表、建主键、外键基本语法
-创建表格语法:
create table 表名(
字段名1 字段类型(长度) 是否为空,
字段名2 字段类型 是否为空 );
-增加主键
alter table 表名 add constraint 主键名 primary key (字段名1);
-增加外键:
alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2);
oracle命令建立主键外键
约束
primary key(pk_id) -- 主键
not null -- 非空
default -- 默认值
check -- 条件 (n < 10 and n >= 3)或 (n = 1 or n = 2)或 (n in(1,2))
unique -- 不重复
constraint [别名] -- 可做异常提示使用
-- 设置外键
constraint [fk_name] foreign key([fk_id]) references [table_name]([fk_id]) on delete cascade
on delete cascade -- 联协删除 即当父表中数据删除时外键关联项会同时被删除
on delete set null -- 父表被删除时设置为空
-- 联合主键
constraint pk_name primary key ([pk_1],[pk_2])
-- 联合主键做外键
constraint fk_name foreign key(fk_1,fk_2) references [table_name]([fk_1],[fk_2])
实例:
1、创建一张学生表
create table t_stu(
stuid number(10) primary key,
stuname varchar2(20) not null,
stusex varchar2(2) default '男' check(stusex in('男','女')),
phone char(11) constraint stu03_sname unique
);
2、创建一张课程表
create table t_couse(
couseid number(10) primary key,
cousename varchar2(20) not null,
cousetype varchar2(4)
);
3、创建一张学生课程成绩表(包括主外键)
create table t_score(
scoreid number(10) primary key,
stuid number(10) references t_stu(stuid),
couseid number(10),
constraint fk_couseid foreign key(couseid)
references t_couse(couseid)
on delete cascade
);
CREATE TABLE log(
log_id int(10) unsigned NOT NULL auto_increment,
log_time datetime NOT NULL,
log_user varchar(30) NOT NULL,
log_title varchar(30) default NULL,
log_content text default NULL,
PRIMARY KEY(log_id));
orale表管理:
-- 追加列
alter table [table_name] add(col,type,constraint);
-- 删除列
alter table [table_name] drop column [col];
-- 修改列
alter table [table_name] modify(col,type,constraint);
-- 列重命名(一般不会使用)
alter table [table_name] rename column [old_col_name] to [new_col_name];
-- 表重命名
rename [old_table_name] to [new_table_name];
-- 追加约束
alter table [table_name] add constraint 约束名 约束内容;
-- 追加联合主键
alter table [table_name] add constraint pk_name primary key ([pk_1],[pk_2]);
视图
创建视图
create or replace view view_a
as
select col1,col2,...
from table1 a,table2 b
where a.fk_id=b.fked_id;
select * from view_a;
删除视图
drop view view_a;
序列
创建序列
create sequence 序列名
[start with a] -- 从 a 开始
[increment by b] -- 每次增加 b
[maxvalue c] -- 最大值是 c
[mincalue d] -- 最小值是 d
获取序列的值
nextval: -- 获取序列的下一个值
currval: -- 获取序列的当前值
-- 使用格式:
序列名.nextval
例: 通过序列和触发器创建一个自增主键表
drop table student;
drop sequence student_sequence;
drop trigger student_trigger;
-- 创建表
create table student(
stu_id number(11) primary key,
stu_no varchar2(8) not null unique,
stu_name varchar2(20) not null,
age number(3),
address varchar2(100));
-- 创建序列
CREATE SEQUENCE student_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
nocycle
nocache;
-- 创建触发器
create or replace trigger student_trigger
before insert on student
for each row
begin
select student_sequence.nextval into :new.STU_ID from dual;
end;
-- 插入数据时已经不需要输入id
insert into student(stu_no,stu_name) values('20200001','小明',22,'北京');
insert into student(stu_no,stu_name) values('20200002','小刚',21,'天津');
insert into student(stu_no,stu_name) values('20200003','小红',19,'太原');
insert into student(stu_no,stu_name) values('20200004','小美',21,'北京');
insert into student(stu_no,stu_name) values('20200005','小军',23,'北京');
insert into student(stu_no,stu_name) values('20200006','小强',22,'天津');
insert into student(stu_no,stu_name) values('20200007','小灰',21,'北京');
insert into student(stu_no,stu_name) values('20200008','小壮',19,'太原');
insert into student(stu_no,stu_name) values('20200009','小爱',20,'北京');
insert into student(stu_no,stu_name) values('20200010','小玲',19,'太原');
-- 提交
commit;
索引
唯一索引
索引在表包含大量信息时提高查询效率,一般的会把主键列自动创建为唯一索引
-- 手动创建索引
create index 索引名 on 表名(列名);
-- 删除索引
drop index 索引名;
常用语句(删、改、增)
删除语句
delete from [table_name] where ...;
插入语句
-- 插入一条数据
insert into [table_name](col1,col2,...) values(val1,val2,...);
-- 同时插入多条数据
insert all
into [table_name](col1,col2,...) values(val1,val2,...)
into [table_name](col1,col2,...) values(val1,val2,...)
into [table_name](col1,col2,...) values(val1,val2,...)
select 1 from dual;
-- 同时插入多条数据 方法2
insert into [table_name](col1,col2,...)
(select val1,val2,... from dual union all
select val1,val2,... from dual union all
select val1,val2,... from dual);
-- 通过 union all 合并记录 将结果集插入表中 (union all 比 union 效率高 , 但不检查是否重复)
更改数据
update [table_name] set [col1=?,col2=?,...] where ...;
查询语句
常用查询
-- 基本语法:
select [col1,col2,...] from [table1,table2,...] where ...;
-- 去重 distinct
select distinct [col1,col2,...] from [table1,table2,...] where ...;
-- 字符串连接 ||
select [col1||'str1'||col2||'str2'] from [table1,table2,...] where...;
-- 模糊查找 like '%' '_'
-- '%' 表示 0 个 或 n 个任意字符
-- '_' 表示 1 个 任意字符
select [col,...] from [table,...] where (col)name like '%李'; -- name = ??李
select [col,...] from [table,...] where (col)name like '李_'; -- name = 李?
-- 可以同时使用多个 '%' '_'
-- 日期默认格式 DD-MON-YY 示例: 2020年8月15日 -> '15-8月-20'
select [col,...] from [table,...] where (col)date < '15-8月-20'; -- 日期在2020年8月15日以前
-- 判断是否为空 写法 is (not) null
select [col,...] from [table,...] where col is null; -- col 为空
-- 其他比较运算
where col between [val1] and [val2]
-- 等价于 col >= [val1] and col <= [val2] --- [val1] < [val2]
where col in(val1,val2,...)
-- col 是集合内某个值
where col >= all(val1,val2,...)
-- col 大于等于 集合内所有值
where col <= any(val1,val2,...)
-- col 小于等于 集合内任意一个
排序
关键字 order by [col] asc(desc);
格式:
-- asc 升序 从小到大
-- desc 降序 从大到小
select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc);
-- 默认为升序 一般情况升序也写上 asc
select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc),[col2] asc(desc),...;
-- 可以根据多列排序
-- 排序先根据 col1 排序 再根据 col2 排序 以此类推
-- 每个列 需要单独指定 排序规则 默认为 升序
分组
关键字 group by [col]
格式
select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc);
-- 默认为升序 一般情况升序也写上 asc
select [col1,col2,...] from [table1,table2,...]
where ...
group by [col]
order by [col1] asc(desc),[col2] asc(desc),...;
-- 根据某项分成几组 相同数据分为一组
-- 一旦使用了分组,只能查询分组中的信息
过滤
where: 是对分组前的数据进行过滤,过滤的是单条数据,where 后面不能跟分组函数,必须写在分组前面
having: 是对分组后的数据进行过滤,过滤的是组数据,必须写在分组后面
select [col1,col2,...] from [table1,table2,...]
where ...
order by [col1] asc(desc);
-- 默认为升序 一般情况升序也写上 asc
select [col1,col2,...] from [table1,table2,...]
where ...
group by [col]
having ...
order by [col1] asc(desc),[col2] asc(desc),...;
子查询
格式示例
-- 查询工资高于 ALLEN 的工资的员工信息
select *
from emp
where sal>(select sal from emp where 'ALLEN');
子查询不只可以运用到查询上,增删改 也可以使用子查询。
分页
rownum
– rownum 会按添加顺序给表的数据添加一个编号,编号为从 1 开始自然增长的正整数
格式:
-- 查询第 10 ~ 20 条的全部数据
SELECT *
FROM (SELECT ROW_.*, ROWNUM ROWNUM_
FROM (SELECT *
FROM TABLE1
WHERE TABLE1_ID = XX
ORDER BY GMT_CREATE DESC) ROW_
WHERE ROWNUM <= 20)
WHERE ROWNUM_ >= 10;
这应该是我们大部分程序里所用到的版本,因为这个版本很容易实现复用,中间ROW_部分,就是我们平常写到的sql语句,然后再将起始条数和终止条数作为专门的分页sql语句传入即可查询出我们想要的结果。
结合 between
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT *
FROM TABLE1
WHERE TABLE1_ID = XX
ORDER BY GMT_CREATE DESC) A)
WHERE RN BETWEEN 10 AND 20;
这个就是换汤不换药了,而且查询效率更低,因为:
由于查询条件BETWEEN 10 AND 20是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,这个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比原始的查询低得多。
结合 minus
SELECT * FROM TABLE1 WHERE ROWNUM <= 20
MINUS
SELECT * FROM TABLE1 WHERE ROWNUM <= 10;
查询了两次,效率上更差了一些。
rowid
rowid 与 rownum 的区别
SELECT *
FROM (SELECT RID
FROM (SELECT R.RID, ROWNUM LINENUM
FROM (SELECT ROWID RID
FROM TABLE1
WHERE TABLE1_ID = XX
ORDER BY order_date DESC) R
WHERE ROWNUM <= 20)
WHERE LINENUM >= 10) T1,
TABLE1 T2
WHERE T1.RID = T2.ROWID;
和前面ROWNUM实现方式相比,该SQL的实现方式更加繁琐,通用性也不是非常好,因为要将原始的查询语句分成两部分(查询字段在最外层,表及其查询条件在最内层),想要复用就很困难了;
但这种实现在特定场景下还是有优势的:比如我们经常要翻页到很后面,比如10000条记录中我们经常需要查9000-9100及其以后的数据;此时该方案效率可能要比前面的高;
因为前面的方案中是通过ROWNUM <= 9100来控制的,这样就需要查询出9100条数据,然后取最后9000-9100之间的数据,而这个方案直接通过ROWID取需要的那100条数据;
集合
格式:使用集合关键字连接两个完整的查询语句
select * from table where ...
union
select * from table where ...;
-- 并集
-- 保留全部的查询结果
union: -- 检查重复
union all: -- 不检查重复
-- 交集
intersect: -- 保留相同的查询结果
-- 差集
minus: -- 从一个查询结果中去除另一个查询结果
多表连接
内联
-- 只会包含有两表中关联的数据
select * from table1,table2 where table1.fid = table2,fid
外联
左外联 -- 无论是否有关联都会包含全部 table1 的数据 无关联数据的列显示 null
select * from table1
left outer join table2 on table1.fid=table2.fid
右外联 -- 无论是否有关联都会包含全部 table2 的数据 无关联数据的列显示 null
select * from table1
right outer join table2 on table1.fid=table2.fid
满外联 -- 无论是否有关联都会包含全部 table1 和 table2 的数据 无关联数据的列显示 null
select * from table1
full outer join table2 on table1.fid=table2.fid
sql函数
单行函数
- 操作数据对象
- 只对一行进行变换
- 每行返回一个结果
- 可以转换字符类型
- 可以嵌套
- 参数可以是一个列或一个值
通用函数
nvl(val1,val2) -- 当 val1 不为空时 return val1
-- 当 val1 为空时 return val2
-- val1 与 val2 数据类型相同
--再使用表达式计算时 如: (sal+count) 如果 conut 的值为 null 则无法计算出结果这时使用 (sal+nvl(count,0)) 默认 count 的值为 null 时 当做 0 来计算。
nvl2(val1,val2,val3) -- 当 val1 不为空时 return val2
-- 当 val1 为空时 return val3
-- val1 val2 val3 数据类型相同
nullif(val1,val2) -- if val1 = val2 return null
-- else return val1
字符函数
对字符串进行操作的函数
lower(str); -- 转换为小写
upper(str); -- 转换为大写
initcap(str); -- 首字母大写
concat(str1,str2); -- 连接字符串
substr(str,start,len); -- 截取字符串 (从 start 开始 截取 len 个字符)
trim(char from str); -- 去除字符串中前后的 char 字符 默认为 ' '
replace(str,char1,char2); -- 将字符串中所有的 char1 字符 替换为 char2 字符
length(str); -- 获取字符串长度
数值函数
mod(a,b); -- 求余数 a % b
power(a,b); -- 求幂数 a ^ b
sqrt(a); -- 求平方根
round(a,b); -- 四舍五入 保留 b 位小数
trunc(a,b); -- 去尾法 保留 b 位小数
日期函数
日期格式 'DD-MON-YY'
sysdate -- 获取系统日期
to_date(str) -- 日期类型转换函数
date + num -- 日期 + 天数 = 日期
date - to_date(str) -- 日期 - 日期 = 相差天数 得到的数据非常准确会带有很长的小数 可以使用 trunc/round 函数保留0位小数得到天数结果
months_between(date1,date2); -- 获取两个日期相差的月数
add_months(date,num); -- 给指定日期加上指定月数
last_day(date); -- 获取指定日期所在月的最后一天
next_day(date,num); -- 获取指定日期之后的第一个周几 (在西方周日是一周的开始周六是一周的结束,所以 1 代表周日 2 代表周一 ... 7 代表周六)
next_day(date,'星期一'); -- next_day 的第二种写法
round(date,'MONTH'); -- 四舍五入 设置精确到 ('YEAR','MONTH','DAY') 对于日期与月份等是对半分精确的 如: 按月精确 日期按30天算 不足15天舍去
trunc(date,'MONTH'); -- 去尾法 设置精确到 ('YEAR','MONTH','DAY')
转换函数
to_date(str,pattern); -- 将字符串转换为日期
to_char(date,pattern); -- 将日期转换为指定格式的字符串
/*
pattern 模式字符串
不指定显示模式 默认为 'DD-MON-YY'
YYYY -- 年
YEAR -- 年的全称 2021年
MM -- 月
MONTH -- 月的全称 8月
DY --
DAY -- 天的全称 15日
DD -- 天
HH -- 小时 12时制
HH24 -- 小时 24时制
MI -- 分钟
SS -- 秒
*/
条件表达式
/*
case col -- 默认 col = 表达式
when 表达式1 then 结果1
when 表达式2 then 结果2
...
else ...
end
或者可以这么写
case -- 可以做 > < >= 等等
when col<表达式1 then 结果1
when col=表达式2 then 结果2
...
else ...
end
*/
-- 条件表达式结果直接输出出来
select col1,
col2,
case col
when 表达式1 then 结果1
when 表达式2 then 结果2
...
else 结果n
end
from table;
-- 条件表达式作为操作条件
select col1,col2
from table
where case col
when 表达式1 then 结果1
when 表达式2 then 结果2
...
else 结果n
end
decode(表达式,值1,结果1,值2,结果2,...,结果3);
-- 当 表达式值 等于 值1 时 返回 结果1
-- 当 表达式值 等于 值2 时 返回 结果2
-- 当 表达式值 为 其他值 时 返回 结果3
多行函数
多行函数 又叫 分组函数 作用于一组数据 返回一个值的函数
min(col) -- 最小值
max(col) -- 最大值
count(col) -- 返回数据个数 不计算空数据
sum(col) -- 求和 不计算空数据
avg(col) -- 求平均 不计算空数据
关于dual表
1.dual 伪表
dual是一个虚拟表,用来构成select的语法规则,oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情,如下:
1、查看当前用户,可以在 SQL Plus中执行下面语句 select user from dual;
2、用来调用系统函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间
select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名
select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale
select dbms_random.random from dual;--获得一个随机数
3、得到序列的下一个值或当前值,用下面语句
select your_sequence.nextval from dual;--获得序列your_sequence的下一个值
select your_sequence.currval from dual;--获得序列your_sequence的当前值
4、可以用做计算器 select 7*9 from dual;
------
Oracle系统中dual表是一个“神秘”的表,网上有很多网友都对该表进行了测试,该表只有一行一列,其实该表和系统中的其他表一样,一样可以执行插入、更新、删除操作,还可以执行drop操作。但是不要去执行drop表的操作,否则会使系统不能用,数据库起不了,会报Database startup crashes with ORA-1092错误。此时也不要慌乱,可以通过执行以下步骤来进行恢复。可以用sys用户登陆。
SQL> create pfile=’d:pfile.bak’ from spfile
SQL> shutdown immediate
在d:pfile.bak文件中最后加入一条:
replication_dependency_tracking = FALSE
重新启动数据库:
SQL> startup pfile=’d:pfile.bak’
SQL> create table “sys”.”DUAL”
[an error occurred while processing this directive]
=====
DUAL ? 有什么神秘的? 当你想得到ORACLE系统时间, 简简单单敲一行SQL 不就得了吗? 故弄玄虚….
SQL> select sysdate from dual;
SYSDATE
---------
28-SEP-03
哈哈, 确实DUAL的使用很方便. 但是大家知道DUAL倒底是什么OBJECT, 它有什么特殊的行为吗? 来,我们一起看一看. 首先搞清楚DUAL是什么OBJECT :
SQL> connect system/manager
Connected.
SQL> select owner, object_name , object_type from dba_objects where object_name like '%DUAL%'; OWNER OBJECT_NAME OBJECT_TYPE
--------------- --------------- -------------
SYS DUAL TABLE PUBLIC DUAL SYNONYM
原来DUAL是属于SYS schema的一个表,然后以PUBLIC SYNONYM的方式供其他数据库USER使用.
再看看它的结构:
SQL> desc dual Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL>
只有一个名字叫DUMMY的字符型COLUMN .
然后查询一下表里的数据:
SQL> select dummy from dual;
DUMMY
----------
X
哦, 只有一条记录, DUMMY的值是’X’ .很正常啊,没什么奇怪嘛. 好,下面就有奇妙的东西出现了!
插入一条记录:
SQL> connect sys as sysdba
Connected.
SQL> insert into dual values ( 'Y');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from dual;
COUNT(*)
----------
2
迄今为止,一切正常. 然而当我们再次查询记录时,奇怪的事情发生了
SQL> select * from dual;
DUMMY
----------
X
刚才插入的那条记录并没有显示出来 ! 明明DUAL表中有两条记录, 可就是只显示一条!
再试一下删除 ,狠一点,全删光 !
SQL> delete from dual; /*注意没有限定条件,试图删除全部记录*/
1 row deleted.
SQL> commit;
Commit complete.
哈哈,也只有一条记录被删掉,
SQL> select * from dual;
DUMMY
----------
Y
为什么会这样呢? 难道SQL的语法对DUAL不起作用吗?带着这个疑问,
我查询了一些ORACLE官方的资料. 原来ORACLE对DUAL表的操作做了一些内部处理,尽量保证DUAL表中只返回一条记录.当然这些内部操作是不可见的 . 看来ORACLE真是蕴藏着无穷的奥妙啊!