最全、最具体的ORACLE查询语法（DQL）

基本语法：
select
[dinstinct|all]/[col_name]
from
[table_name]
where [conditions]
group by [col_name] --对查询结果进行分组
having [conditions] --分组后的筛选条件，需要基于groupby
order by [col_name] --指定查询结果的排序规则
一、简单查询：
1.字段加别名：
select [col_name] as [要叫的名字] from [table_name];
2.计算列：
select [col_name],2021-[col_name,如年龄] 出生年份 from [table_name];
3.使用函数操作：
select [col_name],substr([col_name1，如电话]，8，4) from [table_name];
上面的例子：substr函数，第八个开始截取，截取四个，电话后四位
4.去除重复行：
select dinstinct from [table_name]
二、检索数据排序
1、升序降序
select * from [table_name] where [conditions] order by [col_name1] asc/desc
按[col_name1]升序/降序排列
2、NULL值的处理
select * from [table_name] where [conditions] order by [col_name1] desc nulls first/nulls last
字段中的null值放最前/null值放最后
3、排序字段
字段可以为：字段名、表达式、别名
select [col_name]，2021-[col_name1] from [table_name] where [conditions] order by 2021-[col_name1];
select [col_name]，2021-[col_name1] birth from [table_name] where [conditions] order by birth;
4、多字段排序
select [col_name]，[col_name1] from [table_name] where [conditions] order by [col_name] asc, [col_name1] desc ;
先满足第一个排序，再满足第二个字段排序
三、条件查询

—关系运算符：< 、>、>=、= 、!=、 <>

—比较运算符： is null 、like 、between…and…、in

—逻辑运算符：and 、or 、not

1、单条件查询：
select * from [table_name] where [col_name],如age >=20;
2、多条件查询：多个条件之间需要逻辑运算符
select * from [table_name] where [col_name],如age >=20 and [col_name1],如sex =‘女’；
3、模糊查询： %任意N个字符，任意一个字符
select * from [table_name] where [col_name] like '[key]%’
4、范围查询：
select * from [table_name] where [col_name],如age in (18,20,22);
5、针对Null的查询
select * from [table_name] where [col_name] is null
四、分组查询
1.聚合函数：
count() sum() avg() max() min()
求和select sum([colname]) from [tbl_name]
求最大值select max([colname]) from [tbl_name]
2.分组查询：
！如果使用了groupby进行分组查询之后，select后的关键字只能是分组字段名或者聚合函数
select [colname],sum[colname2] from [tablename] group by [colname];
! having 对groupby分组查询之后的子句进行筛选
select [colname],sum[colname2] from [tablename] group by [colname] having [colname] is not null;

五、嵌套查询
select … from … where [colname] = / in (子查询)；

select [col1] from [table1] where [col2]=’[condition]’;
select * from [table2] where [col3]=’[col1]’;
→
select * from [table2] where [col3]=（
select [col1] from [table1] where [col2]=’[condition]）;
若子查询返回多个记录，即[col]为多个值，
select * from [table2] where [col3] in （
select [col1] from [table1] where [col2]=’[condition]）;
或者–ANY/SOME
select * from [table2] where [col3] = ANY （
select [col1] from [table1] where [col2]=’[condition]）;
！注意：ALL为子查询出来的各个条件都满足
select * from [table2] where [col3] = ALL （
select [col1] from [table1] where [col2]=’[condition]）;

六、连接查询
查询多张表数据
连接查询语法：
select … from [tablename1] [left|right] join [tablename2] where [conditions]
直接举例：
select * from tb_classes; --4条记录
select * from tb_students; --7条记录
select * from tb_classes，tb_students --28条 （笛卡尔积（乘积））
1、内链接：只查询两张表中匹配的记录
语法1：select * from tb_classes,tb_students where tb_students.stu_id = tb_classes.cls_id ;
语法2：select * from tb_classes inner join tb_students on tb_students.stu_id = tb_classes.cls_id ;
注：也可以不等值链接：即=改为> 、 <

2、外连接：一张表的所有记录和另一张表的匹配记录

a.左外连接，左表所有记录，右表匹配记录
select * from tb_student left join tb_classes on tb_students.stu_id = tb_classes.cls_id ; (student表信息全部显示，再加上匹配的class信息）
b.右外链接，右表所有记录，左表匹配记录
select * from tb_student left join tb_classes on tb_students.stu_id = tb_classes.cls_id ; (classes表信息全部显示，再加上匹配的student信息）
c.全外连接,显示表中对应所有信息，若没有匹配则置空
select * from tb_student full join tb_classes on tb_students.stu_id = tb_classes.cls_id ; (classes表、student表信息全部显示，若没匹配的行则置空）

3.自连接
select * from tb_student a inner join tb_student b on a.num=b.num
会显示两个表的数据，一般无意义