oracle多表关联查询和子查询
一、多表关联查询
例子:
SQL> create table student1
(
sid varchar(3),
sname varchar(6),
sage number(3));
Table created.
SQL> create table course1
(
sid varchar(3),
cname varchar(8),
cno number(3));
Table created.
student1表
SQL> select * from student1;
SID SNAME SAGE
--- ------ ----------
01 李逍遥 21
02 林月如 20
03 拜月 100
course1表
SQL> select * from course1;
SID CNAME CNO
--- -------- ----------
01 历史 12
02 化学 11
22 德语 18
1、内连接(inner join……on/join on):只连接匹配的行
SQL> select * from student1 inner join course1 on student1.sid=course1.sid;
SID SNAME SAGE SID CNAME CNO
--- ------ ---------- --- -------- ----------
01 李逍遥 21 01 历史 12
02 林月如 20 02 化学 11
2、左连接(left join……on):包含左边表的全部行,不管右边表的是否存在与它们匹配的行
SQL> select * from student1 left join course1 on student1.sid=course1.sid;
SID SNAME SAGE SID CNAME CNO
--- ------ ---------- --- -------- ----------
01 李逍遥 21 01 历史 12
02 林月如 20 02 化学 11
03 拜月 100
3、右连接(right join……on):包含右边表的全部行,不管左边表是否存在与它们匹配的行
SQL> select * from student1 right join course1 on student1.sid=course1.sid;
SID SNAME SAGE SID CNAME CNO
--- ------ ---------- --- -------- ----------
01 李逍遥 21 01 历史 12
02 林月如 20 02 化学 11
22 德语 18
4、全外连接(full outer join……on或者full join……on):包含左右两张表的全部行,不管另外一边表中是否存在与它们匹配的行
SQL> select * from student1 full outer join course1 on student1.sid=course1.sid;
SID SNAME SAGE SID CNAME CNO
--- ------ ---------- --- -------- ----------
01 李逍遥 21 01 历史 12
02 林月如 20 02 化学 11
22 德语 18
03 拜月 100
一般,我们测试中常用的是内连接搭配函数(数值函数、统计函数、聚合函数等等)查询使用
二、oracle中常用函数
1、数值函数
ceil(n)返回大于或者等于数值n的最小整数
SQL> select ceil(10.7) from dual;
CEIL(10.7)
----------
11
floor(n)返回小于等于数值n的最大整数
SQL> select floor(12.3) from dual;
FLOOR(12.3)
-----------
12
mod(m,n)m除以n的余数,若n=0,则返回m,若m=0也返回m
SQL> select mod(7,5) from dual;
MOD(7,5)
----------
2
SQL> select mod(6,3) from dual;
MOD(6,3)
----------
0
SQL> select mod(3,0) from dual;
MOD(3,0)
----------
3
SQL> select mod(0,4) from dual;
MOD(0,4)
----------
0
power(n,m)m的n次方
SQL> select power(12,11)from dual;
POWER(12,11)
------------
7.4301E+11
round(n,m)将n四舍五入,保留小数点后m位
SQL> select round(3.657879,2) from dual;
ROUND(3.657879,2)
-----------------
3.66
sign(n)若n=0,则返回0,否则n>0,则返回1,n<0返回-1
SQL> select sign(19) from dual;
SIGN(19)
----------
1
SQL> select sign(0) from dual;
SIGN(0)
----------
0
SQL> select sign(-23) from dual;
SIGN(-23)
----------
-1
sqrt(n)n的平方根
SQL> select sqrt(36) from dual;
SQRT(36)
----------
6
2、常用字符函数
initcap(char)把每个字符串的第一个字符转成大写
SQL> select initcap('xiaosheng') from dual;
INITCAP('
---------
Xiaosheng
lower(char)整个字符串小写
SQL> select lower('SQL') from dual;
LOW
---
sql
replace(char,str1,str2)字符串中所有str1换成str2
SQL> select replace('xiaosheng','xiao','ting') from dual;
REPLACE('
---------
tingsheng
substr(char,m,n)取出从m字符开始的n个字符串的子串
SQL> select substr('xiaosheng',1,5) from dual;
SUBST
-----
xiaos
length(char)求字符串的长度
SQL> select length('meiyou ,dengwzhuceyige ') from dual;
LENGTH('MEIYOU,DENGWZHUCEYIGE')
-------------------------------
23
||并置运算符
SQL> select 'xiao'||'sheng' from dual;
'XIAO'||'
---------
xiaosheng
3、日期型函数
sysdate返回当前日期和时间
SQL> select sysdate from dual;
SYSDATE
------------
23-AUG-19
last_day本月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYS
------------
31-AUG-19
add_months(d,n)当前日期d后推n个月
SQL> select add_months(sysdate,2) from dual;
ADD_MONTHS(S
------------
23-OCT-19
months_between(d,n)日期d和n相差月数
SQL> select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('20020812','YYYYMMDD'))
------------------------------------------------------
204.373818
next_day(d,day):表示d后第一周指定day的日期
SQL> select next_day(sysdate,'Monday') from dual;
NEXT_DAY(SYS
------------
26-AUG-19
day格式有:Monday、tuesday、Wednesday、thursday、friday、saturday、Sunday
4、特殊格式的日期型函数
Y或Y或YYY:表示年的最后一位或者两位、三位
SQL> select to_char(sysdate,'YYYY') from dual;
TO_C
----
2019
SQL> select to_char(sysdate,'Y') from dual;#可以看出是从最后一位开始取值
T
-
9
Q:表示季度,1-3个月为一个季度
SQL> select to_char(sysdate,'Q') from dual;
T
-
3
MM:表示月份数
SQL> select to_char(sysdate,'MM') from dual;
TO
--
08
RM:表示将月份转换成罗马的日期格式
SQL> select to_char(sysdate,'RM') from dual;
TO_C
----
VIII
month:表示用英文字符表示月份(比如august指的是8月)
SQL> select to_char(sysdate,'month') from dual;
TO_CHAR(SYSDA
-------------
august
ww:表示当年的第几周
SQL> select to_char(sysdate,'ww') from dual;
TO
--
34
w:表示本月的第几周
SQL> select to_char(sysdate,'w') from dual;
T
-
4
DDD:表示当年第几天,一月一日是当年的第一天:001,二月一日是当年的第32天:032
SQL> select to_char(sysdate,'DDD') from dual;
TO_
---
235
DD:表示当月第几天
SQL> select to_char(sysdate,'DD') from dual;
TO
--
23
D:表示周内第几天
SQL> select to_char(sysdate,'D') from dual;
T
-
6 ----因为西方是把周日算成一周的第一天
DY:表示周内第几天缩写
SQL> select to_char(sysdate,'DY') from dual;
TO_CHA
------
FRI ----周五的缩写
hh12:表示12小时制小时数
hh24:表示24小时制小时数
Mi:表示分钟数
ss:表示秒数
TO_CHAR(
--------
02:53:13 ---12小时制
SQL> select to_char(sysdate,'hh24:Mi:ss') from dual;
TO_CHAR(
--------
14:53:53 ---24小时制
to_number():将字符串数字化
SQL> select to_number('123333') from dual;
TO_NUMBER('123333')
-------------------
123333
to_char():表示将数字转换为字符串
5、聚合函数
avg(x):返回x的平均值
sum(x):返回x的和
count(x):统计x个数
MAX\MIN(x):返回x最大值或x最小值
median(x):返回x的中间值
stddev(x):返回x的标准差
【注意:使用聚合函数的注意事项】
(1)可以使用distinct去除重复数据,一般放在from前面
(2)如果查询中包含了聚合函数,选择的列不在聚合函数中,那么这些列就必须在group by 字句中,否则就会报错
(3)having字句可以用来过滤行组,可以放在group by 子句之后
三、子查询
子查询:指查询中嵌套查询,常放在列、表、条件上
语法:
select (子查询) from(子查询) where(子查询)group by(子查询)having(子查询)order by (asc/desc)子