当前位置: 首页 > 阅读正文

ORACLE之DQL查询语句

2021年9月19日   264 次   7734字数

DQL也称为查询语句,指的是对表中数据的查询,本文仅适用于oracle数据库

查询语法

基本查询语法如下:

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后的筛选条件
order by
    排序

基本查询

1.查询表中所有数据

select * from 表名;

2.查询指定列,多个列名用 , 隔开

select
       列名1, -- 注释1
       列名2  -- 注释2
from 表名;

3.列名起别名展示,使用as关键字,as可以省略,别名可以使用””也可以不使用,不使用””时默认大写

select 列名1 as 别名1, 列名2 别名2 from 表名;

4.查询结果去重复,使用distinct关键字

select distinct 列名称 from 表名称;

5.列结果连接符 || ,连接字符串或以字符串形式连接其他列,例:查询员工工资,显示100元、xxx元(sal为工资列名,emp为员工表),该字符串使用”括起来

select sal || '元' as "工资:(单位元)" from emp;

6.列结果进行四则运算,乘除法优先,有小括号优先小括号,仅限数字型数据,例如对工资加100显示

select 列名+100 from 表名;

7.查询结果升序排列:(默认asc升序)

select * from 表名 order by 列1,列2...;

8.查询结果降序,使用desc关键字:

select * from 表名 order by 列1 desc, 列2 desc...;

9.分组查询,分组字段应该是查询字段中的一个(展示字段通常包含聚合函数)

select 分组字段,展示字段1,展示字段2... from 表名 group by 分组字段;

10.分组筛选,having后的条件符合,才显示该分组数据(having条件通常是聚合函数)

select 分组字段,展示字段... from 表名 group by 分组字段 having 条件;

条件查询

在where语句后跟条件,形成条件查询语句。

oracle数据库可用运算符如下:

运算符描述
>大于
<小于
<=小于等于
>=大于等于
=等于
<> 或 !=不等于
between … and在 … 之间,闭区间
in(集合)在 … 之中
is null是 null
is not null不是 null
and
or
not非,取反
like模糊运算
_单个字符
%多个任意字符

1.查询工资=3000的员工

select * from emp where sal=3000;

2.查询姓马的人,name表示名字字段

select * from 表名 where name like '马%';

3.查询第二个字是 ’化‘ 的人

select * from 表名 where name like '_化%';

4.查询姓名是三个字的人(三个短杠 _ )

select * from 表名 where name like '___';

5.查询姓名中包含 ‘马’ 字的人

select * from 表名 where name like '%马%';

函数查询

oracle函数按功能可以分成多种类别函数。这里分为单行与聚合。

单行函数

单行函数,表示对一行数据进行处理,并返回一条数据。对多行数据处理时就会有多条数据

1.变小写(以下数据类型,表示该数据类型的列)

select lower(字符串) from 表名;

2.变大写

select upper(字符串) from 表名;

3.首字母大写,其余小写

select initcap(字符串) from 表名;

4.拼接(oracle中的dual表是一个虚拟表、测试表)

select concat(字符串1,字符串2) from dual;

5.截取字符串

select substr(字符串,起始位置,终止位置) from dual;

6.取字符串长度

select length(字符串) from dual;

7.找子串位置

select instr(字符串,子字符串) from dual;

8.左补齐lpad、右补齐rpad。

select lpad(字符串,补齐后总长度,补齐字符), rpad(字符串,补齐后总长度,补齐字符) from dual;

9.去除左右两边指定字符

select trim(指定字符 from 字符串) from dual;

10.四舍五入

select round(小数,保留小数点位数) from dual;

11.截取小数点位数,后续小数点丢弃

select trunc(小数,保留小数点位数) from dual;

12.求余数

select mod(被取余数,取余数) from dual;

13.取系统当前时间

select sysdate from dual;

14.日期可以作减法,结果为天数,为数字还可以继续加减乘除

select 时间1-时间2 from 表名;

15.日期做加法,表示天数加1,结果仍是日期

select 日期+1 from 表名;

16.计算两个日期相差的月份

select months_between(日期1,日期2) from dual;

17.向指定日期中添加若干月份

select add_months(日期1,整数) from dual;

18.表示日期中该月的最后一天

select last_day(日期) from dual;

19.隐式转换,自动完成

  • 字符串转数字
  • 字符串转日期
  • 数字转字符串
  • 日期转字符串

20.日期转字符串的多种格式

to_char(date,'格式')
  • YYYY =》四位数年份,2002
  • YEAR =》纯英文 TWO THOUSAND AND TWO
  • MM =》纯数字月份,02
  • MONTH =》 月份英文全称,JULY
  • MON =》月份英文缩写,JUL
  • DY =》 日期英文缩写 MON
  • DAY =》日期英文全称,MONDAY
  • DD =》日期数字,02

该语法中,有中文时需要使用双引号

select '当前时间:' || to_char(sysdate, 'YYYY"年"MM"月"DD"日"') from dual;

21.日期对数字转换多种格式

  • 9 =》 数字
  • 0 =》 零
  • $ =》美元货币
  • L =》当地货币
  • . =》 小数点
  • , =》千位符
select to_char(日期,'$999,999.99') from dual;

22.字符转日期TO_DATE

select to_date(日期,'DD-MON-RR') from dual;

23.字符串转数字TO_NUMBER

select to_number(字符串,'999,999') from dual;

24.为空候选nvl(a,b),如果a不为空返回a,否则返回b,其中ab数据类型相同

25.三元为空候选nvl2(a,b,c),如果a不为空返回b,否则返回c

26.判断相等nullif(a,b),如果a和b相等则返回空,不等非空

27.多级候选coalesce(a,b,c,d….n),按照a,b,c,d..优先返回一个不为空的值

引号归纳

在oracle的select语句中,存在“双引号”,“单引号”,“无引号”

对于通配符,必须是“无引号“的,不能加单、双引号。

对于”属性“,可以是”无引号“,也可以是“双引号”

单引号表示字符串,如果单引号中嵌套了字符串,里面的使用双引号

聚合函数

聚合函数,也叫分组函数、多行函数,通常在分组中使用。它指的是把列(多行)当做一个数据来计算,那么计算一列(多行)只会得到一个值。

注释:聚合函数会排除null,oracle中的null值表示空值,与”等价

1.count函数,返回某个字段的条数(如果使用*会计算最多的列,即得到表中数据条数)

select count(列名) from 表名;

2.计算某个列中最大的数据值,针对数字

select MAX(列名) from 表名;

3.计算某个列中最小的数据值,针对数字

select MIN(列名) from 表名;

4.计算某个列的数据和,针对数字

select SUM(列名) from 表名;

5.计算某个列的数据的平均值,针对数字

select AVG(列名) from 表名;

6.计算某个列的数据的标准方差,针对数字

select STDDEV(列名) from 表名;

7.计算某列的数据的统计方差,针对数字

select VARIANCE(列名) from 表名;
DQL语法执行顺序

sql查询时,有先后顺序,

  • from =》 判断从哪些表取数据
  • where =》判定条件,得出结果集
  • group by=》对结果集分组
  • having =》 对结果集过滤
  • select =》 显示最终结果

查询平均分大于等于60的学生,并显示学号、姓名、平均分,那么你不能这么写

select sno,sname,avg(grade) from sc where avg(grade)>=60;

上述语句错误在于,你在where中使用了聚合函数,聚合函数也叫分组函数,它是对结果集的分组筛选,而where完成之后才能得出结果集,所以是错误的,你应该这么写:

select sno,sname,avg(grade) from sc group by sno having avg(grade)>=60;

在这里,我们使用了学号进行分组,虽然看起来没意义,但分组之后就可以进行having筛选了,也就达到了最终效果。

多表查询

默认多表查询是笛卡尔积的结果

-- 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY,
	NAME VARCHAR(20)
);
INSERT INTO dept VALUES (1,'开发部');
INSERT INTO dept VALUES (2,'市场部');
INSERT INTO dept VALUES (3,'财务部');
-- 创建员工表
CREATE TABLE emp (
	id INT PRIMARY KEY,
	NAME VARCHAR(10),
	gender CHAR(2), -- 性别
	salary INT,  -- 工资
	join_date DATE, -- 入职日期
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键,关联部门表(部门表的主键)
);
INSERT INTO emp(ID,NAME,gender,salary,join_date,dept_id) VALUES(1,'孙悟空','男',7200,date'2013-02-24',1);
INSERT INTO emp(ID,NAME,gender,salary,join_date,dept_id) VALUES(2,'猪八戒','男',3600,date'2010-12-02',2);
INSERT INTO emp(ID,NAME,gender,salary,join_date,dept_id) VALUES(3,'唐僧','男',9000,date'2008-08-08',2);
INSERT INTO emp(ID,NAME,gender,salary,join_date,dept_id) VALUES(4,'白骨精','女',5000,date'2015-10-07',3);
INSERT INTO emp(ID,NAME,gender,salary,join_date,dept_id) VALUES(5,'蜘蛛精','女',4500,date'2011-03-14',1);

比如,直接查询 emp, dept 这两张表的数据

select * from emp, dept;

得到的结果如下:

+----+--------+--------+--------+------------+---------+----+--------+
| id | NAME   | gender | salary | join_date  | dept_id | id | NAME   |
+----+--------+--------+--------+------------+---------+----+--------+
|  1 | 孙悟空 | 男     |   7200 | 2013-02-24 |       1 |  1 | 开发部 |
|  1 | 孙悟空 | 男     |   7200 | 2013-02-24 |       1 |  2 | 市场部 |
|  1 | 孙悟空 | 男     |   7200 | 2013-02-24 |       1 |  3 | 财务部 |
|  2 | 猪八戒 | 男     |   3600 | 2010-12-02 |       2 |  1 | 开发部 |
|  2 | 猪八戒 | 男     |   3600 | 2010-12-02 |       2 |  2 | 市场部 |
|  2 | 猪八戒 | 男     |   3600 | 2010-12-02 |       2 |  3 | 财务部 |
|  3 | 唐僧   | 男     |   9000 | 2008-08-08 |       2 |  1 | 开发部 |
|  3 | 唐僧   | 男     |   9000 | 2008-08-08 |       2 |  2 | 市场部 |
|  3 | 唐僧   | 男     |   9000 | 2008-08-08 |       2 |  3 | 财务部 |
|  4 | 白骨精 | 女     |   5000 | 2015-10-07 |       3 |  1 | 开发部 |
|  4 | 白骨精 | 女     |   5000 | 2015-10-07 |       3 |  2 | 市场部 |
|  4 | 白骨精 | 女     |   5000 | 2015-10-07 |       3 |  3 | 财务部 |
|  5 | 蜘蛛精 | 女     |   4500 | 2011-03-14 |       1 |  1 | 开发部 |
|  5 | 蜘蛛精 | 女     |   4500 | 2011-03-14 |       1 |  2 | 市场部 |
|  5 | 蜘蛛精 | 女     |   4500 | 2011-03-14 |       1 |  3 | 财务部 |
+----+--------+--------+--------+------------+---------+----+--------+

可以测试任意两张表,可以发现笛卡尔积的规律如下:3条数据和5条数据的表一起查询,就得到15条数据,也就是一张中的每一条数据都与另一个表中的所有数据匹配。

造成笛卡尔积的原因是,表与表之间没有“联系”,我们应该指定多表之间的关系,这样才能消除笛卡尔积,得到真正需要的数据结果,这就是学习多表查询的目的

多表查询,有3种方式,①内连接,②外连接,③子查询

内连接

内连接就是限定了必须对应的字段信息

核心思想:多表当做一表查,再使用条件限定筛选。

隐式内连接,使用where限定连接条件

查询 emp, dept 表所有的数据

SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

查询 emp, dept 表其中的一些属性,可以使用 “表名.属性” 来表示 “属性”,这样能够在多表字段重名时进行区分。

SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

可以给表起别名的方式,简化查询,不能使用as

SELECT 
	t1.name, -- 员工表的姓名
	t1.gender,-- 员工表的性别
	t2.name -- 部门表的名称
FROM
	emp t1,
	dept t2
WHERE 
	t1.dept_id = t2.id;
显式内连接,使用join on限定

显式内连接效果一样,只是语法不同,语法如下

select 字段列表 from 表名1 [inner] join 表名2 on 条件;

其中 表1 是主表,后面的是从表,方括号表示 inner 关键字可以省略

例如,仍然查询 emp, dept 表的所有数据

SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
内连接注意事项
  1. 查询哪些字段
  2. 从哪些表查询数据
  3. 查询条件是什么

外连接

核心思想:按条件查询完成一表后,按顺序按条件继续查询下一张表。

1.左外连接:查询“左表”所有数据,并按照左表条件继续查询右表

左外连接语法如下

select 字段列表 from 表1 left [outer] join 表2 on 条件;

例如,查询所有员工信息,以及所在部门名称,即使没分配部门也显示

首先加入一个新入职员工,还未分配部门

insert into emp(id,name,gender,salary) values(6,'小白龙','男',3000);
SELECT 	t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
2.右外连接:查询“右表”所有数据,并按照右表条件继续查询左表
select 字段列表 from 表1 right [outer] join 表2 on 条件;

例如,查询所有员工及其部门,如果员工没有加入部门,则不显示该行数据

SELECT 	t1.*,t2.name FROM emp t1 RIGHT JOIN dept t2 ON t1.dept_id = t2.id;
3.满外连接:按顺序查询所有的表,最后按条件筛选

例如,查询员工信息,部门名称,没有部门的员工,以及没有员工的部分都要显示

select 字段列表 from 表1 full [outer] join 表2 on 条件

增加一个新部门,此时没有员工

INSERT INTO dept VALUES (4,'公关部');
select  t1.*,t2.name from emp t1 full join dept t2 on t1.dept_id=t2.id;

子查询

查询中“嵌套”查询,则称为子查询

核心思想:查询一张表得到数据后,把这些数据当做新表、新数据给外层表使用

那么按照得到的数据不同,也有不同的语法,总体来说

使用一个小括号()把子查询嵌套起来
1.子查询的结果是单列的:结果通常当作值使用。

查询工资最高的员工信息

SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

查询’财务部’和’市场部’所有的员工信息

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

注释:单列的也可以当作表使用,只是通常不这么用!!!

2.子查询的结果是多列的,可以当做一张表使用

查询员工入职日期是2011-11-11日之后的员工信息和部门信息

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > date'2011-11-11') t2
 WHERE t1.id = t2.dept_id;			

注释:多列的只能当作表使用,而不能做值判断

多表查询总结

多表查询,有3种方式,其中

  • 内连接最简单
  • 外连接效率最高
  • 子查询最灵活

子查询效率和外连接相比,为什么效率低于外连接?

通常来说子查询更慢是有原因的,子查询虽然灵活,但是可能会被重复执行,理想情况下是我们认为会生成一个临时空间存放该数据,实际上可能没有,那么此时就慢了。

分页查询

oracle中查询数据时,结果集数据中的每一行数据都对应一个行号,行号所在的列,叫rownum

rownum从1开始,所以不能限定rownum大于一个正数。

在涉及排序时,排序后的rownum会被打乱。

那么,综上所述,容易得出一个解决方案:在进行分页时,首先把排序后的表当成一个新的表(取一个别名,假设tt),在对tt表进行<限定,以及对tt的行号rownum取别名(假设rn),接着再把这个表当成一个新表,在最外层的表中对rn进行>限定。

select * from(
    select rownum rn, tt.* from(
          select * from emp order by sal desc  -- 真正的查询语句
    ) tt where rownum<11  -- 小于限定
) where rn>5  -- 大于限定

在上面这个例子中,我们取出了emp表按sal降序排序的6-10行,假设每页5行,那么这个就是第2页数据,修改大于、小于限定,即可取出想要的页数。

可以发现,真正的查询语句实际在最中间的位置,外表套了2层查询,格式是比较固定的,需要注意的是别名tt和rn,最好不要和真正的查询语句撞名。

本篇完,还有疑问?留下评论吧

发表评论