Oracle数据库常用语法

深碍√TFBOYSˉ_ 2021-10-29 08:34 489阅读 0赞

# 基本 #

\--新建表：  
create table table1( id varchar(300) primary key, name varchar(200) not null);

\--插入数据  
insert into table1 (id,name) values ('aa','bb');  
  
\--更新数据  
update table1 set id = 'bb' where id='cc';  
  
\--删除数据  
delete from table1 where id ='cc';  
  
\--删除表  
drop table table1;

\--修改表名：  
alter table table1 rename to table2;  
  
\--表数据复制：  
insert into table1 (select \* from table2);  
  
\--复制表结构：  
create table table1 select \* from table2 where 1>1;  
  
\--复制表结构和数据：  
create table table1 select \* from table2;  
  
\--复制指定字段：  
create table table1 as select id, name from table2 where 1>1;

\--条件查询：  
select id,name (case gender when 0 then '男' when 1 then ‘女’ end ) gender from table1

# 数学函数  #

\--绝对值：abs()  
select abs(-2) value from dual; --(2)

\--取整函数（大）：ceil（）  
select ceil(-2.001) value from dual; --(-2)

\--取整函数（小）：floor（）  
select floor(-2.001) value from dual; --(-3)

\--取整函数（截取）：trunc（）  
select trunc(-2.001) value from dual; -- (-2)

\--四舍五入：round（）  
select round(1.234564,4) value from dual; --(1.2346)

\--取平方：Power（m,n）  
select power(4,2) value from dual; --(16)

\--取平方根:SQRT()  
select sqrt(16) value from dual; --(4)

\--取随机数:dbms\_random(minvalue,maxvalue)  
select dbms\_random.value() from dual; (默认是0到1之间)  
select dbms\_random.value(2,4) value from dual; （2-4之间随机数）

\--取符号：Sign()  
select sign(-3) value from dual; --(-1)  
select sign(3) value from dual; --(1)

\--取集合的最大值:greatest(value)  
select greatest(-1,3,5,7,9) value from dual; --(9)

\--取集合的最小值:least(value)  
select least(-1,3,5,7,9) value from dual; --(-1)

\--处理Null值：nvl(空值，代替值)  
select nvl(null,10) value from dual; --(10)　　  
select nvl(score,10) score from student;

# rownum相关  #

\--rownum小于某个数时可以直接作为查询条件（注意oracle不支持select top）  
select \* from student where rownum <3;

\--查询rownum大于某个数值,需要使用子查询，并且rownum需要有别名  
select \* from(select rownum rn ,id,name from student) where rn>2;  
select \* from (select rownum rn, student.\* from student) where rn >3;

\--区间查询  
select \* from (select rownum rn, student.\* from student) where rn >3 and rn<6;

\--排序+前n条  
select \* from (select rownum rn, t.\* from ( select d.\* from DJDRUVER d order by drivernumber)t )p where p.rn<10;

\--排序+区间查询1  
select \* from (select rownum rn, t.\* from ( select d.\* from DJDRIVER d order by DJDRIVER\_DRIVERTIMES)t )p where p.rn<9 and p.rn>6;

\--排序+区间查询2  
select \* from (select rownum rn, t.\* from ( select d.\* from DJDRIVER d order by DJDRIVER\_DRIVERTIMES)t where rownum<9 )p where p.rn>6;--效率远高于方式一

# 分页查询  #

（假设每页显示10条）

**不包含排序：**

\--效率低

select \* from (select rownum rn, d.\* from DJDRIVER d )p where p.rn<=20 and p.rn>=10;

select \* from (select rownum rn, d.\* from DJDRIVER d )p where p.rn between 10 and 20;

\--效率高

select \* from (select rownum rn, d.\* from DJDRIVER d where rownum<=20 )p where p.rn>=10;

**包含排序：**  
\--排序+区间查询1（效率低）

select \* from (select rownum rn, t.\* from ( select d.\* from DJDRIVER d order by DJDRIVER\_DRIVERTIMES)t )p where p.rn<=20 and p.rn>=10;

select \* from (select rownum rn, t.\* from ( select d.\* from DJDRIVER d order by DJDRIVER\_DRIVERTIMES)t )p where p.rn between 10 and 20;

\--排序+区间查询2（效率高）

select \* from (select rownum rn, t.\* from ( select d.\* from DJDRIVER d order by DJDRIVER\_DRIVERTIMES)t where rownum<=20 )p where p.rn>=10;

# 时间处理  #

1. to\_char和to\_date基本使用

\--日期  
\--年 yyyy yyy yy year  
\--月 month mm mon month  
\--日+星期 dd ddd(一年中第几天) dy day  
\--小时 hh hh24  
\--分 mi  
\--秒 ss

eg1:  
select to\_char(sysdate,'yyyy-mm-dd hh24:mi:ss')currenttime,  
to\_char(sysdate,'yyyy') year,  
to\_char(sysdate,'mm') month,  
to\_char(sysdate,'dd') day,  
to\_char(sysdate,'day') week,  
to\_char(sysdate,'hh24')hour,  
to\_char(sysdate,'mi') minute,  
to\_char(sysdate,'ss') second  
from dual;

![812043-20190801162224985-146808778.png][]

eg2:

select to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss')currenttime,  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'yyyy')year,  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'mm')month,  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'dd') day,  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'day') week,  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'day','NLS\_DATE\_LANGUAGE=American') week, --设置语言  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'hh24')hour,  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'mi') minute,  
to\_char(to\_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'ss') second  
from dual;

![812043-20190801162240679-2134738727.png][]

2）months\_between

select months\_between(to\_date('03-31-2014','MM-DD-YYYY'),to\_date('12-31-2013','MM-DD-YYYY')) "MONTHS"  
FROM DUAL;

![812043-20190801162254652-2000125870.png][]

3)next\_day

select sysdate today, next\_day(sysdate,6) nextweek from dual;

![812043-20190801162312529-1329181548.png][]

4)时间区间

例：

select cardid, borrowdate from borrow where to\_date(borrowdate,'yyyy-mm-dd hh24:mi:ss') between  
to\_date('2014-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and  
to\_date('2014-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

5）interval

select to\_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currenttime,  
to\_char(sysdate - interval '7' year,'yyyy-mm-dd hh24:mi:ss') intervalyear,  
to\_char(sysdate - interval '7' month,'yyyy-mm-dd hh24:mi:ss') intervalMonth,  
to\_char(sysdate - interval '7' day,'yyyy-mm-dd hh24:mi:ss') intervalday,  
to\_char(sysdate - interval '7' hour,'yyyy-mm-dd hh24:mi:ss') intervalHour,  
to\_char(sysdate - interval '7' minute,'yyyy-mm-dd hh24:mi:ss') intervalMinute,  
to\_char(sysdate - interval '7' second,'yyyy-mm-dd hh24:mi:ss') intervalSecond  
from dual;

![812043-20190801162345607-1901437068.png][]

6）add\_months

select add\_months(sysdate,12) newtime from dual;

7)extract

select extract(month from sysdate) "This Month",  
extract(year from add\_months(sysdate,36)) " Years" from dual;

![812043-20190801163229194-2046839391.png][]

# 字符函数  #

**--字符函数**

select substr('abcdefg',1,5)substr,           --字符串截取  
instr('abcdefg','bc') instr,                          --查找子串  
'Hello'||'World' concat,                              --连接  
trim(' wish ') trim,                                      --去前后空格  
rtrim('wish ') rtrim,                                     --去后面空格  
ltrim(' wish') ltrim,                                      --去前面空格  
trim(leading 'w' from 'wish') deleteprefix,  --去前缀  
trim(trailing 'h' from 'wish') deletetrailing,  --去后缀  
trim('w' from 'wish') trim1,  
ascii('A') A1,  
ascii('a') A2,                                             --ascii(转换为对应的十进制数)  
chr(65) C1,  
chr(97) C2,                                              --chr(十进制转对应字符)  
length('abcdefg') len, --length  
lower('WISH')lower,  
upper('wish')upper,  
initcap('wish')initcap,                               --大小写变换  
replace('wish1','1','youhappy') replace,   --替换  
translate('wish1','1','y')translate,             --转换，对应一位(前面的位数大于等于后面的位数)  
translate('wish1','sh1','hy')translate1,  
concat('11','22') concat 　　　　　　　 --连接  
from dual;

![812043-20190801164300152-10224208.png][]

to\_number  
\--to\_number(expr)  
\--to\_number(expr,format)  
\--to\_number(expr,format,'nls-param')

select to\_number('0123')number1, --converts a string to number  
trunc(to\_number('0123.123'),2) number2,  
to\_number('120.11','999.99') number3,  
to\_number('0a','xx') number4, --converts a hex number to decimal  
to\_number(100000,'xxxxxx') number5  
from dual;

![812043-20190801164330065-1997540794.png][]

# 聚合函数 #

student表如下：

![812043-20190801162448545-1896355992.png][]

count：

\--count (distinct|all)  
select count(1) as count from student;--效率最高  
select count(\*) as count from student;  
select count(distinct score) from student;  
语句1结果：11

avg

\--avg (distinct|all)  
select avg(score) score from student;  
select avg(distinct score) from student;  
select classno,avg(score) score from student group by classno;  
语句3输出结果：

![812043-20190801162505510-1258063662.png][]

max

\--max (distinct|all)  
select max(score) from student;  
select classno, max(score) score from student group by classno;

min

\--min (distinct|all)  
select min(score) from student;  
select classno, min(score) score from student group by classno;  
stddev（standard deviation）标准差

\--stddev  
select stddev(score) from student;  
select classno, stddev(score) score from student group by classno;  
sum

\--sum  
select sum(score) from student;  
select classno, sum(score) score from student group by classno;

median--中位数

\--median  
select median(score) from student;  
select classno, median(score) score from student group by classno;

# 案例1--学生选课 #

1. 创建表 stu（学生表），course（课程表），选课表（s\_c）

\--创建表

create table STU  
(  
id NUMBER not null,  
name VARCHAR2(255)  
) ;  
  
create table COURSE  
(  
id NUMBER not null,  
coursename VARCHAR2(255)  
) ;  
  
create table S\_C  
(  
sid NUMBER,  
cid NUMBER,  
score NUMBER  
);  
2.插入数据

\--插入数据  
Insert into STU (ID,NAME) values (1,'wish');  
Insert into STU (ID,NAME) values (2,'rain');  
Insert into STU (ID,NAME) values (3,'july');  
Insert into STU (ID,NAME) values (4,'joey');

Insert into COURSE (ID,COURSENAME) values (1,'math');  
Insert into COURSE (ID,COURSENAME) values (2,'english');  
Insert into COURSE (ID,COURSENAME) values (3,'Japanese');  
Insert into COURSE (ID,COURSENAME) values (4,'chinese');

Insert into S\_C (SID,CID,SCORE) values (1,1,80);  
Insert into S\_C (SID,CID,SCORE) values (1,2,90);  
Insert into S\_C (SID,CID,SCORE) values (2,4,100);  
Insert into S\_C (SID,CID,SCORE) values (4,4,90);  
Insert into S\_C (SID,CID,SCORE) values (4,1,100);  
Insert into S\_C (SID,CID,SCORE) values (4,3,80);  
Insert into S\_C (SID,CID,SCORE) values (4,2,80);  
Insert into S\_C (SID,CID,SCORE) values (2,1,90);  
Insert into S\_C (SID,CID,SCORE) values (2,4,100);  
Insert into S\_C (SID,CID,SCORE) values (3,1,60);

3.查询学生选课情况

with vt as  
(select s.id,s.name,c.coursename,sc.score from stu s, course c, s\_c sc where s.id=sc.sid and c.id=sc.cid)  
select \* from vt order by id;  
结果：

![812043-20190801162538575-1880952876.png][]

# 案例2--图书馆借阅 #

1.创建表： 图书（book），读者（reader），借阅（borrow）

\--创建表 book  
create table book(  
bookId varchar2(30), --图书总编号  
sortid varchar2(30), --分类号  
bookname varchar2(100), --书名  
author varchar2(30), --作者  
publisher varchar2(100),--出版单位  
price number(6,2) --价格  
);  
  
\--创建表 reader  
create table reader (  
cardId varchar2(30), --借书证号  
org varchar2(100), --单位  
name varchar2(100), --姓名  
gender varchar2(2), --性别  
title varchar2(30), --职称  
address varchar2(100) --地址  
);

\--创建表 borrow  
create table borrow(  
cardId varchar2(30), --借书证号  
bookId varchar2(30), --图书总编号  
borrowDate varchar2(30) --借阅时间  
);

2.插入数据

\--插入数据-book  
insert into book (bookId,sortid,bookname,author,publisher,price)  
values ('aaa','a1','gone with the wind','CA','renmin','103');

insert into book (bookId,sortid,bookname,author,publisher,price)  
values ('bbb','a2','the little prince','CB','jixie','30');

insert into book (bookId,sortid,bookname,author,publisher,price)  
values ('ccc','a3','the ordinary world','CC','renmin','130');

insert into book (bookId,sortid,bookname,author,publisher,price)  
values ('ddd','a4','the little women','CA','dianzi','110');

\--插入数据-reader  
insert into reader(cardid, org, name,gender, title, address)  
values ('xxx','A','wish','1','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('uuu','A','luna','1','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('vvv','B','harry','1','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('www','C','chander','2','professor','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('yyy','A','joey','2','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('zzz','B','richard','2','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('OOO','A','micheal','2','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('ppp','A','richal','2','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('abp','A','michal','2','student','bupt');

insert into reader(cardid, org, name,gender, title, address)  
values ('ccp','A','mike','2','student','bupt');  
\--插入数据-borrow  
insert into borrow(cardid,bookid,borrowdate) values('xxx','aaa','2014-4-29');  
insert into borrow(cardid,bookid,borrowdate) values('xxx','bbb','2014-4-29');  
insert into borrow(cardid,bookid,borrowdate) values('xxx','ccc','2014-4-28');  
insert into borrow(cardid,bookid,borrowdate) values('yyy','ccc','2014-4-28');  
insert into borrow(cardid,bookid,borrowdate) values('yyy','ddd','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('yyy','aaa','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('zzz','bbb','2014-4-28');  
insert into borrow(cardid,bookid,borrowdate) values('zzz','ddd','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('zzz','aaa','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('uuu','bbb','2014-4-28');  
insert into borrow(cardid,bookid,borrowdate) values('uuu','ddd','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('uuu','aaa','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('uuu','ccc','2014-4-26');  
insert into borrow(cardid,bookid,borrowdate) values('vvv','bbb','2014-4-28');  
insert into borrow(cardid,bookid,borrowdate) values('vvv','ddd','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('www','aaa','2014-4-27');  
insert into borrow(cardid,bookid,borrowdate) values('www','ccc','2014-4-26');  
表信息如下：

book------> reader-------> borrow

![812043-20190801162600539-1038889057.png][]

![812043-20190801162613222-1533180137.png][]

![812043-20190801162626862-2012795153.png][]

3. 查询A单位借阅图书的读者人数和人员详细信息

人数：

with vt1 as  
(select cardid from reader where reader.org='A')  
select count(1) from vt1 where exists (select cardid from borrow where borrow.cardid=vt1.cardid);

![812043-20190801162732130-1680923789.png][]

详细信息：

with vt1 as  
(select cardid,name,org from reader where reader.org='A')  
select cardid,name,org from vt1 where exists (select cardid from borrow where borrow.cardid=vt1.cardid);

![812043-20190801162712965-2004950624.png][]

4.查询借书证号尾字符为'p'的读者

select cardid, name, org from reader where cardid like '%p';

![812043-20190801162754871-1029581290.png][]

5. 查询名字以m开头的女性读者，‘1’显示为女，‘2’显示为男

select cardid, name, org,  
case when gender='1' then '女' when gender='2' then '男' else '其他' end gender  
from reader where name like 'm%';

![812043-20190801162805716-312151870.png][]

6. 2014年2-4月借过书的读者

1)查询满足条件的读者（仅包含cardid）--未去重

方式一：

select cardid, borrowdate from borrow where to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy')='2014'  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'mm')>='02'  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'mm')<='04';  
方式二：

select cardid, borrowdate from borrow where to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy')='2014' --查询  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy-mm')>='2014-02'  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy-mm')<='2014-04';  
方式三：

![812043-20190801162819220-1596236206.png][]

2) 查询+去重

select distinct cardid from borrow where to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy')='2014' --查询+去重  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy-mm')>='2014-02'  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy-mm')<='2014-04';  
select distinct cardid from borrow where to\_date(borrowdate,'yyyy-mm-dd hh24:mi:ss') between  
to\_date('2014-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and  
to\_date('2014-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

![812043-20190801162834691-933049259.png][]

3）查询+去重+读者姓名等信息

with vt1 as  
(select distinct cardid from borrow where to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy')='2014'  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy-mm')>='2014-02'  
and to\_char(to\_date(borrowdate,'yyyy-mm-dd'),'yyyy-mm')<='2014-04')  
select cardid, name,org from reader where exists (select cardid from vt1 where vt1.cardid=reader.cardid);

![812043-20190801162846709-1749778605.png][]

转载于:https://www.cnblogs.com/lgx5/p/11283332.html

[812043-20190801162224985-146808778.png]: /images/20211029/37ed5c6af526421e92f993b061ccda09.png
[812043-20190801162240679-2134738727.png]: /images/20211029/a67c4b0d78934685910a92560ffd7503.png
[812043-20190801162254652-2000125870.png]: /images/20211029/e3de58ac551e4b51b9b4f6b353588047.png
[812043-20190801162312529-1329181548.png]: /images/20211029/8ac3a383556e41aea51ed894fffca2b9.png
[812043-20190801162345607-1901437068.png]: /images/20211029/e218783d04e7417ba0d50dd59cbacb98.png
[812043-20190801163229194-2046839391.png]: /images/20211029/5f44ee8638cc4b06b3ec17b4140b10a6.png
[812043-20190801164300152-10224208.png]: /images/20211029/148322d0c67948b3b798181ce2f0d7c1.png
[812043-20190801164330065-1997540794.png]: /images/20211029/4ba624d5391f465e93346b1567a9ac76.png
[812043-20190801162448545-1896355992.png]: /images/20211029/a4ba1ea2b1d743429e85b3b1603147d0.png
[812043-20190801162505510-1258063662.png]: /images/20211029/d33ded66ba004fcbbd08f6efb8172499.png
[812043-20190801162538575-1880952876.png]: /images/20211029/60523e1a81ce41ff87eee841b6d0d1f5.png
[812043-20190801162600539-1038889057.png]: /images/20211029/287e6c4a6c844ae69f21bc14187cc727.png
[812043-20190801162613222-1533180137.png]: /images/20211029/aa3efdef35a2457b97e92cf369ec9092.png
[812043-20190801162626862-2012795153.png]: /images/20211029/a68a92deb4c84c229126f38ca6b109c0.png
[812043-20190801162732130-1680923789.png]: /images/20211029/f2b3064e81834c13bcdb004951001c7c.png
[812043-20190801162712965-2004950624.png]: /images/20211029/6ee56bcb39834638882399dee32bdbaa.png
[812043-20190801162754871-1029581290.png]: /images/20211029/6f5ce66fdcee45869e6aae1d6fa43031.png
[812043-20190801162805716-312151870.png]: /images/20211029/d8a06b2ef3b044ce95215d7ba60d0eb7.png
[812043-20190801162819220-1596236206.png]: /images/20211029/3e69279ad010437aa18dcf25db00d246.png
[812043-20190801162834691-933049259.png]: /images/20211029/b3982fa2ba774035a95261608fac0b8d.png
[812043-20190801162846709-1749778605.png]: /images/20211029/76e71060432b43539b5b481ed66d2a23.png