DQL语言:
DQL:数据库查询语言
简单查询:
语法:
select <字段列表> from <表名> [where] <条件> [group by] <分组字段> [having] <分组条件> [order by] <排序字段>
案例1:查询学生表中所有学生的消息细腻(所有信息)
--*表示所有的字段
select * from t_student;
select sname as 姓名,sage as 年龄 from t_student;
以上使用是别名,
select sname as "【姓名】",sage as "【年龄】" from t_student;
别名中如果出现特殊字符,需要用双引号括起来
其中as 一般情况下都会省略
select sname "【姓名】",sage "【年龄】" from t_student;
拼接符号(||):
select t_student.*,sname,saddress
from t_student
在这种情况下,首先要确定*要匹配哪一张表,如果不确定,就会报错
select t_student.*, '【'|| sname || '】'|| saddress as 名字
from t_student
带条件的查询:
案例1:查询学生表中sid为13的学生的所有信息
select * from t_student where sid =13;
模糊查询:(like 百分号(%) 下划线 )
案例1:查询学生表中所有姓张的学生的所有信息
select *
from t_student
where sname like '张%';
--%表示匹配0个或者以上的字符
案例2:查询学生表中姓名以祥结尾的学生的所有信息
select *
from t_student
where sname like '%祥';
案例3:查询学生表中名字带有"志"这个字的学生的所有信息
select *
from t_student
where sname like '%志%'
案例4:查询学生表中名字带有"志"且是三个字的名字的学生的所有信息
select *
from t_student
where sname like '_志_' or sname like '志__' or sname like '__志' -- 志-- --志 -志-
--_表示一个字符
排序:
注意:如果没有指定升序或者降序排列,默认按照升序排列。
升序 asc 降序关键字 desc
案例1:查询学生表中所有学生的信息并按照年龄排序。
select *
from t_student
order by sage desc;
select *
from t_student
order by sage asc;
案例2:查询学生表中所有学生的信息,并按照年龄排序降序,如果年龄相等,按照学号排序升序
select *
from t_student
order by sage desc,sid asc;
注意:以多个字段排序的话,orderby在查询语句中只能出现一次。多个排序字段名用英文逗号隔开。
--多表查询:
1、交叉查询
交叉查询的结果集的总记录数时候两张表的笛卡尔乘积,数据重复。
select t1.*,t2.* from t_student t1, t_class t2;
等值查询:在交叉连接的基础上过滤了一些不符合条件的数据。
select t1.*,t2.* from t_student t1, t_class t2 where t1.cid = t2.cid;
注意:会过滤掉不符合条件的记录,在数据量大的时候,执行效率低,数据不重复。
2、内连接:执行效率比交叉查询高
select * from t_student t1 inner join t_class t2 on t1.cid = t2.cid;
内连接数据较少的放在左边执行效率比放在右边高。
3、外连接
1)左外连接
在内连接的基础上,将左边不符合条件的数据也显示出来
以左边的表为主,左边的数据全部显示,右边的表的数据不满足条件的会被干掉
select *
from t_student t1 left join t_class t2 on t1.cid = t2.cid;
2)右外连接
以右边的表为主,右边的数据全部显示,左边的表的数据不满足条件的会被干掉
select *
from t_student t1 right join t_class t2 on t1.cid =t2.cid;
3)全连接
全连接 = 左外连接+右外连接+去掉重复项
将左边和右边的数据全部显示
select *
from t_student t1 full join t_class t2 on t1.cid = t2.cid;
union 和 union all
union :用来求两个结果集的交集,并且去掉重复项,根据第一列字段进行升序排序
union all:用来求两个结果集的交集,但是没有去掉重复项,也没有进行排序
常用函数:
1、to_date():
将字符串类型的数据根据指定的格式转化成日期类型的格式
select '2017-02-15' ,to_date('2017-02-15','yyyy-mm-dd') from dual;
2、to_char():将日期类型或者数值类型的数据根据指定的格式转化为字符类型
select sysdate , to_char(sysdate ,'yyyy-mm-dd HH:mi:ss') from dual;
select sysdate , to_char(sysdate ,'yyyy-mm-dd HH:mi:ss'), to_char(sysdate ,'yyyy'), to_char(sysdate ,'mm') from dual;
select sysdate , to_char(sysdate ,'yyyy"年"mm"月"dd"日"') from dual;
注意:若格式中存在非格式化的字符,则需要用双引号将字符括起来
3 decode(字段名,值1,返回值1,值2,返回值2,值3,返回值4...,值n,返回值n,缺省值):
类似于Java当中的if ...else if ....else
select t.*,decode(ssex,'1','男','2','女','未知')
from t_student t
select t.*,decode(ssex,'1','男','未知')
from t_student t
4、nvl(x,value): 如果x为空,则返回value值,否则返回x
select t.* ,nvl(sname,'身份不明') ,decode(sname,null,'身份不明',sname)
from t_student t;
思考题:统计出学生表中年龄在18-20和21-26之间的人数。
select sage, count(1)
from t_student
where sage>=18 and sage<=20 or sage>=21 and sage<=26
group by sage
sum()求和
select sum(decode (sage ,18,1,19,1,20,1,0)) "[18-20]",sum(decode (sage ,21,1,22,1,23,1,24,1,25,1,26,1,0))"[21-26]"
from t_student t;
5、case when
case when 条件 then 值1 else 值1 end
select sum( case when sage between 18 and 20 then 1 else 0 end ) "[18-20]",
sum(case when sage between 21 and 26 then 1 else 0 end ) "[21-26]"
from t_student t;
使用count来实现:
select count( case when sage between 18 and 20 then 1 end ) "[18-20]",
count(case when sage between 21 and 26 then 1 end ) "[21-26]"
from t_student t;
6、last_day():获得当月的最后一天
select last_day(sysdate)
from dual;
7、extract:提取日期中的特定部分
select extract(year from sysdate)
from dual;
8、substr :截取字符串
select substr('abcdefg',4,3)
from dual;
9、length:用来判断字符的长度
select t.*, length(sname)
from t_student t;
10、instr 查找字符的方法 'abcdefg'
select instr('abcdefg','h')
from dual;
select t.*,instr(sname,'祥')
from t_student t
where instr(sname,'祥')!=0
伪列:数据库会为每一张表添加一个伪列,伪列不会存储在表中。例如 rowid
rowid:是每一条数据的物理地址,唯一确定每一条数据的,具备唯一性。
只能查询 不能修改。
--插入数据的时候,自动生成
select t.*,rowid from t_student t;
rownum:行号,数据库为了维护每一条数据所生成的一行唯一的标识符。每次维护都会生成。
rownum会在执行查询后,在对结果集进行编号。
select *from t_student t where ssex ='1';
案例1:查询学生表中的前10条数据
select t.*,rownum
from t_student t
where rownum<=10;
案例2:查询学生表中的年龄最大的前三条数据
select * from(
select t.* ,rownum num from t_student t order by sage desc
) where rownum<=3
案例3:查询学生表中的第3个到第10个学生的所有数据
分页
select t.* ,rownum from t_student t where rownum<=10 and rownum>=3
--每次查询rownum都会重新排序
select t2.*,rownum from (
select t1.* ,rownum num from t_student t1 where rownum<=10
) t2 where num>=3
DQL:数据库查询语言
简单查询:
语法:
select <字段列表> from <表名> [where] <条件> [group by] <分组字段> [having] <分组条件> [order by] <排序字段>
案例1:查询学生表中所有学生的消息细腻(所有信息)
--*表示所有的字段
select * from t_student;
select sname as 姓名,sage as 年龄 from t_student;
以上使用是别名,
select sname as "【姓名】",sage as "【年龄】" from t_student;
别名中如果出现特殊字符,需要用双引号括起来
其中as 一般情况下都会省略
select sname "【姓名】",sage "【年龄】" from t_student;
拼接符号(||):
select t_student.*,sname,saddress
from t_student
在这种情况下,首先要确定*要匹配哪一张表,如果不确定,就会报错
select t_student.*, '【'|| sname || '】'|| saddress as 名字
from t_student
带条件的查询:
案例1:查询学生表中sid为13的学生的所有信息
select * from t_student where sid =13;
模糊查询:(like 百分号(%) 下划线 )
案例1:查询学生表中所有姓张的学生的所有信息
select *
from t_student
where sname like '张%';
--%表示匹配0个或者以上的字符
案例2:查询学生表中姓名以祥结尾的学生的所有信息
select *
from t_student
where sname like '%祥';
案例3:查询学生表中名字带有"志"这个字的学生的所有信息
select *
from t_student
where sname like '%志%'
案例4:查询学生表中名字带有"志"且是三个字的名字的学生的所有信息
select *
from t_student
where sname like '_志_' or sname like '志__' or sname like '__志' -- 志-- --志 -志-
--_表示一个字符
排序:
注意:如果没有指定升序或者降序排列,默认按照升序排列。
升序 asc 降序关键字 desc
案例1:查询学生表中所有学生的信息并按照年龄排序。
select *
from t_student
order by sage desc;
select *
from t_student
order by sage asc;
案例2:查询学生表中所有学生的信息,并按照年龄排序降序,如果年龄相等,按照学号排序升序
select *
from t_student
order by sage desc,sid asc;
注意:以多个字段排序的话,orderby在查询语句中只能出现一次。多个排序字段名用英文逗号隔开。
--多表查询:
1、交叉查询
交叉查询的结果集的总记录数时候两张表的笛卡尔乘积,数据重复。
select t1.*,t2.* from t_student t1, t_class t2;
等值查询:在交叉连接的基础上过滤了一些不符合条件的数据。
select t1.*,t2.* from t_student t1, t_class t2 where t1.cid = t2.cid;
注意:会过滤掉不符合条件的记录,在数据量大的时候,执行效率低,数据不重复。
2、内连接:执行效率比交叉查询高
select * from t_student t1 inner join t_class t2 on t1.cid = t2.cid;
内连接数据较少的放在左边执行效率比放在右边高。
3、外连接
1)左外连接
在内连接的基础上,将左边不符合条件的数据也显示出来
以左边的表为主,左边的数据全部显示,右边的表的数据不满足条件的会被干掉
select *
from t_student t1 left join t_class t2 on t1.cid = t2.cid;
2)右外连接
以右边的表为主,右边的数据全部显示,左边的表的数据不满足条件的会被干掉
select *
from t_student t1 right join t_class t2 on t1.cid =t2.cid;
3)全连接
全连接 = 左外连接+右外连接+去掉重复项
将左边和右边的数据全部显示
select *
from t_student t1 full join t_class t2 on t1.cid = t2.cid;
union 和 union all
union :用来求两个结果集的交集,并且去掉重复项,根据第一列字段进行升序排序
union all:用来求两个结果集的交集,但是没有去掉重复项,也没有进行排序
select *
from t_student t1 full join t_class t2 on t1.cid = t2.cid;
select *
from t_student t1 left join t_class t2 on t1.cid = t2.cid
union
select *
from t_student t1 right join t_class t2 on t1.cid =t2.cid;
select *
from t_student t1 left join t_class t2 on t1.cid = t2.cid
union all
select *
from t_student t1 right join t_class t2 on t1.cid =t2.cid;
常用函数:
1、to_date():
将字符串类型的数据根据指定的格式转化成日期类型的格式
select '2017-02-15' ,to_date('2017-02-15','yyyy-mm-dd') from dual;
2、to_char():将日期类型或者数值类型的数据根据指定的格式转化为字符类型
select sysdate , to_char(sysdate ,'yyyy-mm-dd HH:mi:ss') from dual;
select sysdate , to_char(sysdate ,'yyyy-mm-dd HH:mi:ss'), to_char(sysdate ,'yyyy'), to_char(sysdate ,'mm') from dual;
select sysdate , to_char(sysdate ,'yyyy"年"mm"月"dd"日"') from dual;
注意:若格式中存在非格式化的字符,则需要用双引号将字符括起来
3 decode(字段名,值1,返回值1,值2,返回值2,值3,返回值4...,值n,返回值n,缺省值):
类似于Java当中的if ...else if ....else
select t.*,decode(ssex,'1','男','2','女','未知')
from t_student t
select t.*,decode(ssex,'1','男','未知')
from t_student t
4、nvl(x,value): 如果x为空,则返回value值,否则返回x
select t.* ,nvl(sname,'身份不明') ,decode(sname,null,'身份不明',sname)
from t_student t;
思考题:统计出学生表中年龄在18-20和21-26之间的人数。
select sage, count(1)
from t_student
where sage>=18 and sage<=20 or sage>=21 and sage<=26
group by sage
sum()求和
select sum(decode (sage ,18,1,19,1,20,1,0)) "[18-20]",sum(decode (sage ,21,1,22,1,23,1,24,1,25,1,26,1,0))"[21-26]"
from t_student t;
5、case when
case when 条件 then 值1 else 值1 end
select sum( case when sage between 18 and 20 then 1 else 0 end ) "[18-20]",
sum(case when sage between 21 and 26 then 1 else 0 end ) "[21-26]"
from t_student t;
使用count来实现:
select count( case when sage between 18 and 20 then 1 end ) "[18-20]",
count(case when sage between 21 and 26 then 1 end ) "[21-26]"
from t_student t;
6、last_day():获得当月的最后一天
select last_day(sysdate)
from dual;
7、extract:提取日期中的特定部分
select extract(year from sysdate)
from dual;
8、substr :截取字符串
select substr('abcdefg',4,3)
from dual;
9、length:用来判断字符的长度
select t.*, length(sname)
from t_student t;
10、instr 查找字符的方法 'abcdefg'
select instr('abcdefg','h')
from dual;
select t.*,instr(sname,'祥')
from t_student t
where instr(sname,'祥')!=0
伪列:数据库会为每一张表添加一个伪列,伪列不会存储在表中。例如 rowid
rowid:是每一条数据的物理地址,唯一确定每一条数据的,具备唯一性。
只能查询 不能修改。
--插入数据的时候,自动生成
select t.*,rowid from t_student t;
rownum:行号,数据库为了维护每一条数据所生成的一行唯一的标识符。每次维护都会生成。
rownum会在执行查询后,在对结果集进行编号。
select *from t_student t where ssex ='1';
案例1:查询学生表中的前10条数据
select t.*,rownum
from t_student t
where rownum<=10;
案例2:查询学生表中的年龄最大的前三条数据
select * from(
select t.* ,rownum num from t_student t order by sage desc
) where rownum<=3
案例3:查询学生表中的第3个到第10个学生的所有数据
分页
select t.* ,rownum from t_student t where rownum<=10 and rownum>=3
--每次查询rownum都会重新排序
select t2.*,rownum from (
select t1.* ,rownum num from t_student t1 where rownum<=10
) t2 where num>=3