数据库查询与视图全攻略：从基础运算到实战操作

作为计算机专业的学生，数据库是我们学习和未来工作中绕不开的核心技能。在数据库操作里，查询和视图又是重中之重。今天就结合学习资料，从关系运算基础讲起，详细拆解数据库查询的各种技巧，再到视图的创建与管理，帮大家系统掌握这部分知识。

一、关系运算基础：选择、投影、连接

在进行数据库查询前，我们得先理解三种最基本的关系运算，它们是后续复杂查询的 “基石”。

1. 选择（Selection）

选择运算的核心是筛选满足条件的行，就像从一堆数据里 “挑人”，只留下符合要求的记录。

比如有一张学生表，包含学号、姓名、性别、平均成绩字段：

学号	姓名	性别	平均成绩
104215	王敏	男	74
104211	李晓林	女	82
104210	胡小平	男	88

如果我们要筛选 “性别为女且平均成绩在 80 分以上” 的记录，选择运算后结果如下：

学号	姓名	性别	平均成绩
104211	李晓林	女	82

2. 投影（Projection）

投影运算则是筛选满足条件的列，简单说就是 “挑字段”，只保留我们需要的列数据，去掉无关列。

还是用上面的学生表，若只需要 “学号” 和 “平均成绩” 这两列，投影运算后结果为：

学号	平均成绩
104215	74
104211	82
104210	88

3. 连接（Join）

连接运算用于将多个表通过共同字段关联起来，整合所需数据。常见的连接方式有等值连接、自然连接等。

（1）等值连接

基于两个表中 “相等的字段” 进行连接，会保留两个表中所有匹配的记录，且包含重复的关联字段。

比如有 A 表和 B 表：

• T1	T2
  1	A
  6	F
  2	B

• B 表（含 T1、T2、T3、T4、T5 字段）：

  T1	T2	T3	T4	T5
  1	A	1	3	M
  2	B	2	0	N

若以 “T1 = T3” 为连接条件，等值连接后的结果为：

T1（A 表）	T2（A 表）	T1（B 表）	T2（B 表）	T3	T4	T5
1	A	1	A	1	3	M
2	B	2	B	2	0	N

（2）自然连接

自然连接是特殊的等值连接，会自动寻找两个表中名称和类型相同的字段作为连接条件，且连接后会去掉重复的字段。

比如 A 表（含 T1、T2、T3 字段）和 B 表（含 T1、T2、T3、T4、T5、T6 字段），自然连接后会自动匹配相同字段，去掉重复列，只保留一套关联字段。

二、数据库查询：从基础到进阶

掌握了关系运算后，我们就能用 SQL 的SELECT语句实现各种查询需求。下面从选择列、选择行、连接、统计、排序五个维度，拆解查询技巧。

1. 选择列：精准筛选字段

选择列的核心是明确 “要查哪些字段”，还能通过别名让结果更易读，甚至用表达式计算衍生字段。

（1）为列指定别名

当查询结果的列名不直观时（比如英文列名），可以用AS关键字给列起别名，格式为列名 [AS] 列别名。

示例 1：检索emp表中员工的编号、姓名、年基本工资、日基本工资，并指定中文别名。

sql

-- 用AS指定别名
select empno as "员工编号", ename as "员工名称", sal*12 as "年基本工资", sal/30 as "日基本工资" from emp;

-- 省略AS，直接用空格分隔列名和别名
select empno "员工编号", ename "员工名称", job "职务" from emp;

注意：如果别名包含特殊字符（如 “-”“空格”），必须用双引号将别名括起来。比如 “年 - 基本工资” 需要写成"年-基本工资"，否则 SQL 会报错。

（2）排除重复行

默认情况下，查询结果会包含重复行。若要去除重复记录，需在SELECT后加DISTINCT关键字。

示例：查询emp表中的所有职务，且不显示重复职务。

sql

-- 去除重复的job记录
select distinct job from emp;

注意：DISTINCT会对 “整行数据” 去重，若多行只有某一列重复、其他列不同，无法去除重复。且查询大表时尽量避免使用DISTINCT，会影响性能。

（3）使用表达式计算字段

对数字或日期类型的字段，可以用算术运算符（+ - * /）构建表达式，计算衍生数据。

示例：查询emp表中员工的姓名、原工资，以及工资上调 10% 后的新工资。

sql

select ename, sal, sal*(1+0.1) as "上调后工资" from emp;

算术运算符优先级：先乘除后加减；有括号先算括号内；同一优先级从左到右计算。

（4）查询所有列

若要查询表中所有字段，无需逐个写列名，直接用*代替即可。

示例 1：查询dept表中所有字段。

sql

-- 单个表查所有列
select * from dept;

示例 2：查询多个表（如dept和salgrade）的所有列，在FROM后用逗号分隔表名。

sql

-- 多个表查所有列
select * from dept, salgrade;

（5）伪列：特殊的 “虚拟字段”

伪列不是表中真实存在的字段，但能像普通列一样查询，常用的有rowid和rownum。

• rowid：返回每行数据的物理存储地址，能唯一标识一行数据，可用于快速定位记录。

  sql

  -- 查询emp表的rowid
  select rowid from emp;
  

• rownum：返回查询结果中每行的 “行号”，第一行是 1，第二行是 2，以此类推，可用于限制返回的行数。

  sql

  -- 查询emp表的前5条数据
  select * from emp where rownum < 6;
  

2. 选择行：筛选符合条件的记录

选择行的核心是用WHERE子句设置筛选条件，常见的条件类型有表达式比较、模式匹配、范围比较、空值比较、子查询等。

（1）表达式比较

用比较运算符（= < <= > >= <>/!=）设置条件，筛选符合要求的记录。

示例：查询emp表中工资大于 1500 的员工编号、姓名、工资。

sql

select empno, ename, sal from emp where sal > 1500;

（2）模式匹配：模糊查询

用LIKE关键字实现模糊查询，配合通配符使用：

• %：匹配 0 个或多个任意字符；
• _：匹配 1 个任意字符。

示例：查询emp表中姓名以 “S” 开头的员工。

sql

若要查询的字符串中包含%或_（如 “IT_%”），需要用ESCAPE关键字指定转义字符（通常用\），格式为like '转义字符+特殊字符%' escape '转义字符'。

示例：查询dept_temp表中部门名称以 “IT_” 开头的记录。

sql

-- 用\转义_，表示匹配真实的_
select * from dept_temp where dname like 'IT\_%' escape '\';

（3）范围比较

用BETWEEN...AND...或IN关键字筛选 “在某个范围” 的记录：

• BETWEEN a AND b：匹配大于等于 a 且小于等于 b 的值；
• IN (值1, 值2, ...)：匹配括号内的任意一个值。

示例 1：查询emp表中工资在 1000-2000 之间的员工。

sql

select ename, sal from emp where sal between 1000 and 2000;

示例 2：查询emp表中部门编号为 10 或 20 的员工。

sql

select ename, deptno from emp where deptno in (10, 20);

（4）空值比较

空值（NULL）表示 “未知的值”，不能用=或!=判断，必须用IS NULL或IS NOT NULL。

示例：查询emp表中没有奖金（comm为 NULL）的员工。

sql

select empno, ename, sal, comm from emp where comm is null;

（5）子查询：嵌套查询

子查询是 “查询中的查询”，即把一个查询结果作为另一个查询的条件。根据返回结果的行数，分为单行子查询、多行子查询、关联子查询。

• 单行子查询：返回 1 行数据，用单行比较运算符（= <等）。
  示例：查询emp表中既不是最高工资也不是最低工资的员工。

  sql

  select empno, ename, sal from emp
  where sal > (select min(sal) from emp)  -- 子查询：查最低工资
    and sal < (select max(sal) from emp); -- 子查询：查最高工资
  

• 多行子查询：返回多行数据，用多行比较运算符（IN ANY ALL）。
  示例：查询emp表中不是销售部门（SALES）的员工。

  sql

  select empno, ename, job from emp 
  where deptno in (select deptno from dept where dname <> 'SALES'); -- 子查询：查非销售部门的编号
  

• 关联子查询：子查询的执行依赖外部查询的结果，内外查询相互关联。
  示例：查询emp表中工资大于同职位平均工资的员工。

  sql

  select empno, ename, sal from emp f
  where sal > (select avg(sal) from emp where job = f.job) -- 子查询：查当前职位（f.job）的平均工资
  order by job;
  

3. 连接：多表关联查询

当数据分散在多个表中时，需要通过 “连接” 整合数据。常用的连接方式有内连接、外连接、自然连接、自连接、交叉连接。

（1）表别名：简化多表查询

多表查询时，表名可能较长，可用表名 别名的格式给表起短别名，简化 SQL 语句。

示例：关联emp表和dept表，查询部门经理的编号、姓名、所属部门。

sql

-- e是emp表的别名，d是dept表的别名
select e.empno as 员工编号, e.ename as 员工名称, d.dname as 部门
from emp e, dept d
where e.deptno = d.deptno  -- 连接条件：部门编号相等
  and e.job = 'MANAGER';   -- 筛选条件：职位是经理

（2）内连接（INNER JOIN）

内连接只返回 “两个表中满足连接条件” 的记录，是最常用的连接方式，INNER关键字可省略。

示例：内连接emp表和dept表，查询员工编号、姓名、所属部门。

sql

select e.empno as 员工编号, e.ename as 员工名称, d.dname as 部门
from emp e inner join dept d  -- inner可省略，写成join
on e.deptno = d.deptno;       -- 连接条件用on指定

（3）外连接：保留不匹配的记录

外连接除了返回匹配的记录，还会保留其中一个表中 “不满足连接条件” 的记录，分为左外连接、右外连接、完全外连接。

• 左外连接（LEFT JOIN）：保留左表所有记录，右表只保留匹配的记录，不匹配的地方显示 NULL。
  示例：左外连接emp表和dept表，保留所有员工，即使员工没有所属部门（deptno为 NULL）。

  sql

  -- 先插入一条无部门的员工记录
  insert into emp(empno, ename, job) values(9527, 'EAST', 'SALESMAN');
  
  -- 左外连接
  select e.empno, e.ename, e.job, d.deptno, d.dname
  from emp e left join dept d
  on e.deptno = d.deptno;
  

• 右外连接（RIGHT JOIN）：保留右表所有记录，左表只保留匹配的记录，不匹配的地方显示 NULL。
  示例：右外连接emp表和dept表，保留所有部门，即使部门没有员工。

  sql

  select e.empno, e.ename, e.job, d.deptno, d.dname
  from emp e right join dept d
  on e.deptno = d.deptno;
  

• 完全外连接（FULL JOIN）：保留两个表的所有记录，不匹配的地方显示 NULL，相当于左外连接 + 右外连接。
  示例：完全外连接emp表和dept表，保留所有员工和所有部门。

  sql

  select e.empno, e.ename, e.job, d.deptno, d.dname
  from emp e full join dept d
  on e.deptno = d.deptno;
  

（4）自然连接（NATURAL JOIN）

自然连接会自动匹配两个表中名称和类型相同的字段作为连接条件，无需手动指定，且连接后会去掉重复的字段。

示例：自然连接emp表和dept表，查询工资大于 2000 的员工编号、姓名、职位、部门名称。

sql

select empno, ename, job, dname
from emp natural join dept
where sal > 2000;

（5）自连接：表自身关联

自连接是 “表与自己连接”，用于查询表中的层次关系（如员工和其上级的关系）。

示例：查询emp表中所有员工的 “上级管理者” 和 “下属员工”。

sql

-- em1是下属表别名，em2是上级表别名
select em2.ename 上层管理者, em1.ename as 下属员工
from emp em1 left join emp em2
on em1.mgr = em2.empno  -- 连接条件：下属的mgr（上级编号）=上级的empno（员工编号）
order by em1.mgr;

（6）交叉连接（CROSS JOIN）

交叉连接无需连接条件，会返回 “两个表的笛卡尔积”（即左表的每一行和右表的每一行都组合一次），结果行数 = 左表行数 × 右表行数。

示例：交叉连接dept表和emp表，计算结果总行数。

sql

select count(*) as 交叉连接结果行数
from dept cross join emp;

4. 统计：用聚合函数分析数据

统计功能主要依赖 “聚合函数”，用于对数据进行求和、求平均、计数等操作，常见的聚合函数如下：

函数	说明
AVG(x)	计算 x 的平均值，可加DISTINCT去重后计算
COUNT(x)	统计记录数，COUNT(*)统计所有行，COUNT(x)统计 x 非 NULL 的行数
MAX(x)	计算 x 的最大值
MIN(x)	计算 x 的最小值
SUM(x)	计算 x 的总和，可加DISTINCT去重后求和
VARIANCE(x)	计算 x 的方差
STDDEV(x)	计算 x 的标准差

（1）基础统计：单表聚合

示例：统计emp表的员工总数、平均工资、最高工资、最低工资。

sql

select count(*) as 员工总数,
       avg(sal) as 平均工资,
       max(sal) as 最高工资,
       min(sal) as 最低工资
from emp;

（2）分组统计：GROUP BY子句

当需要 “按某个字段分组统计”（如按部门统计平均工资）时，需用GROUP BY子句，将数据按指定字段分组后，对每组应用聚合函数。

示例：按部门编号分组，统计每个部门的平均工资。

sql

select deptno as 部门编号, avg(sal) as 部门平均工资
from emp
group by deptno;  -- 按deptno分组

（3）筛选分组结果：HAVING子句

WHERE子句用于筛选 “行”，HAVING子句用于筛选 “分组后的结果”（即对聚合函数的结果筛选），必须跟在GROUP BY后。

示例：按部门编号分组，统计平均工资，且只保留平均工资大于 2000 的部门。

sql

select deptno as 部门编号, avg(sal) as 平均工资
from emp
group by deptno
having avg(sal) > 2000;  -- 筛选平均工资>2000的分组

5. 排序：ORDER BY子句

用ORDER BY子句对查询结果排序，格式为ORDER BY 字段名 [ASC/DESC]，其中ASC是升序（默认），DESC是降序。

示例 1：查询emp表员工信息，按工资降序排序（工资高的在前）。

sql

select empno, ename, sal
from emp
order by sal desc;

示例 2：按多个字段排序，先按部门编号升序，同一部门内按工资降序。

sql

select empno, ename, deptno, sal
from emp
order by deptno asc, sal desc;

三、数据库视图：虚拟表的创建与管理

视图是 “虚拟表”，它本身不存储数据，数据来自定义视图的查询语句（即 “基表”）。视图的核心作用是简化查询、控制数据访问权限（如只让用户看到部分字段）。

1. 视图的概念

• 本质：视图是存储在数据库中的查询语句，其结果以 “表” 的形式展示。
• 数据来源：视图的数据来自基表，基表数据变化时，视图结果也会同步变化；反之，通过视图修改数据，也会同步修改基表。
• 用途：简化复杂查询（将多表连接的查询封装成视图，后续直接查视图即可）、控制权限（如只开放员工的 “姓名”“职位” 字段，隐藏 “工资” 字段）。

2. 创建视图

创建视图有两种方式：用图形化工具（如 SQL Developer）或CREATE VIEW语句。

（1）用CREATE VIEW语句创建

语法格式：

sql

create [or replace] view <视图名> [别名1, 别名2, ...]
as <子查询语句>  -- 定义视图的查询语句，即视图的数据来源
[with check option] [constraint 约束名]  -- 限制通过视图修改数据的范围
[with read only];  -- 设为只读视图，禁止修改

示例 1：创建视图emp_view，包含emp表中部门编号为 30 的员工的编号、姓名、职位、工资。

sql

create view emp_view as
select empno, ename, job, sal
from emp
where deptno = 30;

示例 2：创建只读视图emp_view_readonly，禁止修改数据。

sql

create view emp_view_readonly as
select empno, ename, job
from emp
with read only;  -- 只读属性

（2）用 SQL Developer 创建

1. 打开 SQL Developer，连接目标数据库，在 “方案” 下找到 “视图”，右键选择 “新建视图”；
2. 填写视图名称（如stu_view1），选择方案（如SCOTT）；
3. 在 “SQL 查询” 框中编写定义视图的查询语句（如select * from stu where zy='计算机'）；
4. 点击 “测试语法”，确认无误后点击 “确定”，视图创建完成。

3. 查询视图

查询视图和查询普通表完全一致，用SELECT语句即可。

示例：查询视图emp_view的所有数据。

sql

select * from emp_view;

4. 更新视图

通过视图修改数据（INSERT/UPDATE/DELETE），本质是修改基表的数据，但并非所有视图都能更新，可更新视图需满足以下条件：

• 未使用聚合函数（如AVG COUNT）、GROUP BY DISTINCT关键字；
• 视图的字段不是通过基表字段计算得到的（如sal*12这种衍生字段）；
• 视图未设置with read only属性。

示例：更新视图emp_view中员工编号为 7698 的员工的工资，改为 3000。

sql

update emp_view
set sal = 3000
where empno = 7698;

执行后，基表emp中该员工的工资也会同步改为 3000。

5. 修改视图定义

若要修改视图的查询逻辑（如将视图的部门编号从 30 改为 20），可使用CREATE OR REPLACE VIEW语句，相当于 “覆盖” 原有视图。

示例：修改视图emp_view，使其包含部门编号为 20 的员工。

sql

create or replace view emp_view as
select empno, ename, job, sal
from emp
where deptno = 20;  -- 原条件是deptno=30，现在改为20

也可在 SQL Developer 中修改：右键视图名称，选择 “编辑”，修改 SQL 查询语句后，点击 “确定” 即可。

6. 删除视图

当视图不再需要时，用DROP VIEW语句删除，删除视图不会影响基表数据。

示例：删除视图emp_view。

sql

drop view emp_view;

注意：若要删除其他用户方案下的视图，需拥有DROP ANY VIEW系统权限。

四、总结

数据库查询和视图是 SQL 操作的核心，今天我们从基础到进阶，梳理了：

1. 关系运算：选择（挑行）、投影（挑列）、连接（多表整合），是查询的理论基础；
2. 数据库查询：从选择列（别名、去重、表达式）、选择行（条件筛选、子查询）、连接（内连接、外连接等）、统计（聚合函数、分组）、排序（ORDER BY），覆盖了大部分实战场景；
3. 视图：从概念到创建、查询、更新、修改、删除，掌握视图能简化复杂查询和控制权限。

这些知识点需要多动手练习，比如用emp和dept表尝试不同的查询语句，或创建视图封装常用查询，才能真正熟练掌握。如果有疑问，欢迎在评论区交流～