热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

深入解析SQL数据库查询技术

本文深入探讨了SQL数据库查询技术,重点讲解了单表查询的各种方法。首先,介绍了如何从表中选择特定的列,包括查询指定列、查询所有列以及计算值的查询。此外,还详细解释了如何使用列别名来修改查询结果的列标题,并介绍了更名运算的应用场景和实现方式。通过这些内容,读者可以更好地理解和掌握SQL查询的基本技巧和高级用法。

目录

一、单表查询

1.1选择表中的若干列

1.1.1查询指定列

1.1.2查询全部列

1.1.3查询经过计算的值

1.1.4使用列别名改变查询结果的列标题

1.1.5更名运算

1.2选择表中的若干元组

1.2.1消除取值重复的行

1.2.2查询满足条件的元组

1.3 ORDER BY子句

1.4聚集函数

1.5 GROUP BY子句

二、多表查询

2.1等值连接与自然连接

2.2自身连接

三、嵌套查询

3.1概述

3.2带有 in谓词的子查询

3.3带有比较运算符的子查询

3.4带有 exists谓词的子查询

四、集合查询

4.1交运算

4.2并运算

4.3差运算

五、基于派生表的查询


select [ all|distinct ] <选择序列>
from <表引用>, ..., <表引用>
[ where <查询条件> ]
[ group by <分组列>, ..., <分组列> [ having <分组选择条件> ] ]
[ order by <排序列> [ ASC|DESC ], ..., order by <排序列> [ ASC|DESC ] ]--select子句&#xff1a;指定要显示的属性列
--from子句&#xff1a;指定查询对象&#xff08;基本表或视图&#xff09;
--where子句&#xff1a;指定查询条件
--group by子句&#xff1a;对查询结果按指定列的值分组&#xff0c;该属性列值相等的元组为一个组。通常会在每组中作用聚集函数
--having短语&#xff1a;只有满足指定条件的组才予以输出
--order by子句&#xff1a;对查询结果表按指定列值的升序或降序排序

一个SQL查询的含义

  1. 为 from子句中列出的关系产生笛卡尔积
  2. 在步骤1的结果上应用 where子句中指定的谓词
  3. 对于步骤2结果中的每个元组&#xff0c;输出 select子句中指定的属性或表达式的结果

一、单表查询

查询的输入是 from子句中列出的关系&#xff0c;在这些关系上进行 where和 select子句中指定的运算&#xff0c;最终产生一个关系作为结果

1.1选择表中的若干列


1.1.1查询指定列

--找出所有教师所在系的系名
select dept_name
from instructor;

1.1.2查询全部列

在 select关键字后面列出所有列名&#xff0c;或 <目标列表达式>指定为 *&#xff0c;表示“所有属性”

--选中 instructor中的所有属性
select instructor.*
from instructor,teacher
where instructor.ID &#61; teacher.ID;--select * 表示 from子句结果关系的所有属性都被选中&#xff0c;即 instructor,teaches的所有属性都被选中

1.1.3查询经过计算的值

select子句的<目标列表达式>可以是关系的属性&#xff0c;也可以是表达式&#xff0c;但不会导致对关系的任何改变

select salary-5000
from instructor;

1.1.4使用列别名改变查询结果的列标题

select Sname aName, &#39;Year of Birth:&#39; Birth, 2014-Sage Birthday, lower(Sdept) Department
from Student;
--aName&#xff0c;Birth&#xff0c;Birthday&#xff0c;Department是别名

1.1.5更名运算


  • from子句中的多个关系中可能存在同名的属性&#xff0c;导致结果中出现重复的属性名
  • 如果在 select子句中使用算术表达式&#xff0c;结果属性就没有名字
  • 想改变结果中的属性名字

as子句既可以出现在 select子句中&#xff0c;也可以出现在 from子句中

--对于大学中所有讲授课程的教师&#xff0c;找出他们的姓名以及所讲述的所有课程标识
select T.name, S.course_id
from instructor as T, teaches as S
where T.ID &#61; S.ID;--使用 as语句重命名结果关系中的属性&#xff1a;old_name as new_name

 

1.2选择表中的若干元组


1.2.1消除取值重复的行

SQL允许在关系以及SQL表达式结果中出现重复&#xff0c;如果想强行删除重复&#xff0c;可在 select后加入关键词 distinct&#xff0c;缺省为 all

select Sno
from sc;
--等价于
select all Sno
from sc;--指定关键词 distinct后消除重复行
select distinct dept_name
from instructor;

1.2.2查询满足条件的元组


常用查询条件

谓    词 

比    较

&#61;, >, <, >&#61;, <&#61;, !&#61;, <>, !>, !<;  not &#43;上述比较运算符

确定范围

between … and …, not between … and …

确定集合

in <值表>,  not in <值表>

字符匹配

[not] like ‘<匹配串>’ [escape ‘<换码字符>’]

空    值

is null, is not null

多重条件&#xff08;逻辑运算&#xff09;

and, or, not

select name
from instructor
where dept_name&#61;&#39;Comp.Sci&#39; and salary>70000;

 

1.3 ORDER BY子句

可以按一个或多个属性列排序

升序&#xff1a;ASC&#xff1b;降序&#xff1a;DESC&#xff1b;缺省值为 ASC

例&#xff1a;查询选修了3号课程的学生的学号及其成绩&#xff0c;查询结果按分数降序排列

select Sno, Grade
from SC
where Cno&#61;&#39;3&#39;
order by Grade DESC;

 

1.4聚集函数


  • 统计元组个数 count(*)
  • 统计一列中值的个数 count([distinct | all] <列名>)
  • 计算一列值的总和&#xff08;此列必须为数值型&#xff09;sum([distinct | all] <列名>)
  • 计算一列值的平均值&#xff08;此列必须为数值型&#xff09;avg([distinct | all] <列名>)
  • 求一列中的最大值和最小值 max([distinct | all] <列名>)&#xff0c;min([distinct | all] <列名>)

sum和 avg的输入必须是数字集&#xff0c;其他运算符可作用在非数字数据类型的集合如字符串

 

1.5 GROUP BY子句

group by子句中给出的一个或多个属性用于构造分组&#xff0c;group by子句中的所有属性上取值相同的元组将被分在同一组

--找出每个系的平均工资
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

分组情况&#xff1a;     最终结果&#xff1a;

任何没有出现在 group by子句中的属性如果出现在 select子句中&#xff0c;只能出现在聚集函数内部&#xff0c;否则该查询错误


having短语与 where子句的区别

作用对象不同

where 子句作用于基表或视图&#xff0c;从中选择满足条件的元组

having 短语作用于组&#xff0c;从中选择满足条件的组

 

 

二、多表查询

同时涉及两个及以上的表的查询

2.1等值连接与自然连接

等值连接&#xff1a;关系R、S&#xff0c;取两者笛卡尔积中属性值相等的元组&#xff0c;例如 R.A&#61;S.B&#xff0c;R.B&#61;S.B

自然连接&#xff1a;特殊的等值连接。运算作用于两个关系并产生一个关系作为结果&#xff0c;在相同属性上进行相等比较&#xff0c;并投影去掉重复属性

列出属性的顺序&#xff1a;先是两个关系模式中的共同属性&#xff0c;然后是只出现在第一个关系模式中的属性&#xff0c;最后是只出现在第二个关系模式中的属性

--from子句中可以用自然连接将多个关系结合在一起
select A1, A2, … ,An
from R1 natural join R2 natural join … natural join Rm
where P;

 

2.2自身连接

自身连接&#xff1a;一个表与其自己进行连接

由于所有属性名都是同名属性&#xff0c;因此属性前必须给表起别名以示区别

例&#xff1a;查询每一门课的间接先修课&#xff08;即先修课的先修课&#xff09;

select first.Cno, second.Cpno
from Course first, Course second
where first.Cpno &#61; second.Cno;

 

 

 

三、嵌套查询

3.1概述

一个 select-from-where语句称为一个查询块将一个查询块嵌套在另一个查询块的 where子句或 having短语的条件中的查询称为嵌套查询

select Sname /*外层查询/父查询*/
from Student
where Sno in (select Sno /*内层查询/子查询*/from SCwhere Cno&#61; &#39;2&#39;);

  • 上层的查询块称为外层查询父查询
  • 下层查询块称为内层查询子查询
  • SQL语言允许多层嵌套查询&#xff0c;即一个子查询中还可以嵌套其他子查询
  • 子查询的限制&#xff1a;不能使用ORDER BY子句

相关子查询&#xff1a;子查询的查询条件依赖于父查询

  1. 首先取外层查询中表的第一个元组&#xff0c;根据它与内层查询相关的属性值处理内层查询&#xff0c;若 where子句返回值为真&#xff0c;则取此元组放入结果表
  2. 然后再取外层表的下一个元组
  3. 重复这一过程&#xff0c;直至外层表全部检查完为止

 

3.2带有 in谓词的子查询

谓词 in测试元组是否是集合中的成员&#xff0c;集合由 select子句产生的一组值构成

--找出在2009年秋季和2010年春季学期同时开课的所有课程
select distincy course_id
from section
where semester &#61; &#39;Fall&#39; and year &#61; 2009 and course_id in (select course_idfrom sectionwhere semester &#61; &#39;Spring&#39; and year &#61; 2010);

in和 not in操作符能用于枚举集合

--找出"Mozart"和"Einstein"之外的老师
select distincy name
from instructor
where name not in (&#39;Mozart&#39;,&#39;Einstein&#39;);

 

3.3带有比较运算符的子查询

当能确切知道内层查询返回单值时&#xff0c;可用比较运算符&#xff08;>&#xff0c;<&#xff0c;&#61;&#xff0c;>&#61;&#xff0c;<&#61;&#xff0c;!&#61;<>&#xff09;

  • “至少比某一个大”用 > some表示

--找出至少比 Biology系某一个教师的工资高的所有老师的姓名
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name &#61; &#39;Biology&#39;;
--注意这里 as语句的用法select name
from instructor
where salary > some (select salaryfrom instructorwhere depr_name &#61; &#39;Biology&#39;);

  • “比所有的都大”用 > all表示

--找出比 Biology系所有教师的工资都高的所有老师的姓名
select name
from instructor
where salary > all (select salaryfrom instructorwhere depr_name &#61; &#39;Biology&#39;);

 

3.4带有 exists谓词的子查询

带有 exists谓词的子查询不返回任何数据&#xff0c;只产生逻辑真值“true”或逻辑假值“false”

  • 若内层查询结果非空&#xff0c;则外层的where子句返回真值
  • 若内层查询结果为空&#xff0c;则外层的where子句返回假值

由exists引出的子查询&#xff0c;其目标列表达式通常都用 * &#xff0c;因为带exists的子查询只返回真值或假值&#xff0c;给出列名无实际意义

 

 

四、集合查询

intersect, union, except分别对应交、并、差运算&#xff0c;均可以自动去除重复&#xff0c;若想保留重复只需在后面加上 "all"

4.1交运算

--找出2009年秋季和2010年春季同时开课的所有课程
(
select course_id
from section
where semester &#61; &#39;Fall&#39; and year &#61; 2009
)
intersect
(
select course_id
from section
where semester &#61; &#39;Spring&#39; and year &#61; 2010
);--结果中出现的重复元组数等于在 c1和 c2中出现的重复次数里最少的那个

 

4.2并运算

--找出2009年秋季开课&#xff0c;或2010年春季开课&#xff0c;或两个学期都开课的所有课程
(
select course_id
from section
where semester &#61; &#39;Fall&#39; and year &#61; 2009
)
union
(
select course_id
from section
where semester &#61; &#39;Spring&#39; and year &#61; 2010
);--结果中出现的重复元组数等于在 c1和 c2中出现的重复元组数的和

 

4.3差运算

--找出2009年秋季开课但不在2010年春季开课的所有课程
(
select course_id
from section
where semester &#61; &#39;Fall&#39; and year &#61; 2009
)
except
(
select course_id
from section
where semester &#61; &#39;Spring&#39; and year &#61; 2010
);
--except运算从其第一个输入中输出所有不出现在第二个输入中的元组--结果中出现的重复元组数等于在 c1中出现的重复元组数减去 c2中出现的重复元组数&#xff08;前提是结果为正&#xff09;

 

 

五、基于派生表的查询

子查询不仅可以出现在 where子句中&#xff0c;还可以出现在 from子句中&#xff0c;这时子查询生成的临时派生表成为主查询的查询对象

如果子查询中没有聚集函数&#xff0c;派生表可以不指定属性列&#xff0c;子查询 select子句后面的列名为其缺省属性

--找出系平均工资超过50000美元的那些系中教师的平均工资
select dept_name,avg_salary
from (select dept_name,avg(salary)from instructorgroup by dept_name)
as dept_avg (dept_name,avg_salary) --括号外是表名&#xff0c;括号内是属性名
where avg_salary > 50000;
--子查询的结果关系被命名为 dept_name&#xff0c;其属性名是 dept_name和 avg_salary


推荐阅读
  • 本文详细介绍了MySQL数据库的基础语法与核心操作,涵盖从基础概念到具体应用的多个方面。首先,文章从基础知识入手,逐步深入到创建和修改数据表的操作。接着,详细讲解了如何进行数据的插入、更新与删除。在查询部分,不仅介绍了DISTINCT和LIMIT的使用方法,还探讨了排序、过滤和通配符的应用。此外,文章还涵盖了计算字段以及多种函数的使用,包括文本处理、日期和时间处理及数值处理等。通过这些内容,读者可以全面掌握MySQL数据库的核心操作技巧。 ... [详细]
  • 在使用 Cacti 进行监控时,发现已运行的转码机未产生流量,导致 Cacti 监控界面显示该转码机处于宕机状态。进一步检查 Cacti 日志,发现数据库中存在 SQL 查询失败的问题,错误代码为 145。此问题可能是由于数据库表损坏或索引失效所致,建议对相关表进行修复操作以恢复监控功能。 ... [详细]
  • Ihavetwomethodsofgeneratingmdistinctrandomnumbersintherange[0..n-1]我有两种方法在范围[0.n-1]中生 ... [详细]
  • MicrosoftDeploymentToolkit2010部署培训实验手册V1.0目录实验环境说明3实验环境虚拟机使用信息3注意:4实验手册正文说 ... [详细]
  • 本文介绍如何使用 Python 的 DOM 和 SAX 方法解析 XML 文件,并通过示例展示了如何动态创建数据库表和处理大量数据的实时插入。 ... [详细]
  • 本文对比了杜甫《喜晴》的两种英文翻译版本:a. Pleased with Sunny Weather 和 b. Rejoicing in Clearing Weather。a 版由 alexcwlin 翻译并经 Adam Lam 编辑,b 版则由哈佛大学的宇文所安教授 (Prof. Stephen Owen) 翻译。 ... [详细]
  • 本文详细介绍了 PHP 中对象的生命周期、内存管理和魔术方法的使用,包括对象的自动销毁、析构函数的作用以及各种魔术方法的具体应用场景。 ... [详细]
  • 本文总结了在SQL Server数据库中编写和优化存储过程的经验和技巧,旨在帮助数据库开发人员提升存储过程的性能和可维护性。 ... [详细]
  • 在 Ubuntu 中遇到 Samba 服务器故障时,尝试卸载并重新安装 Samba 发现配置文件未重新生成。本文介绍了解决该问题的方法。 ... [详细]
  • php更新数据库字段的函数是,php更新数据库字段的函数是 ... [详细]
  • 通过使用Sqoop导入工具,可以精确控制并高效地将表数据的特定子集导入到HDFS中。具体而言,可以通过在导入命令中添加WHERE子句来指定所需的数据范围,从而在数据库服务器上执行相应的SQL查询,并将查询结果高效地存储到HDFS中。这种方法不仅提高了数据导入的灵活性,还确保了数据的准确性和完整性。 ... [详细]
  • MySQL Decimal 类型的最大值解析及其在数据处理中的应用艺术
    在关系型数据库中,表的设计与SQL语句的编写对性能的影响至关重要,甚至可占到90%以上。本文将重点探讨MySQL中Decimal类型的最大值及其在数据处理中的应用技巧,通过实例分析和优化建议,帮助读者深入理解并掌握这一重要知识点。 ... [详细]
  • 深入解析Struts、Spring与Hibernate三大框架的面试要点与技巧 ... [详细]
  • 本文详细探讨了几种常用的Java后端开发框架组合及其具体应用场景。通过对比分析Spring Boot、MyBatis、Hibernate等框架的特点和优势,结合实际项目需求,为开发者提供了选择合适框架组合的参考依据。同时,文章还介绍了这些框架在微服务架构中的应用,帮助读者更好地理解和运用这些技术。 ... [详细]
  • 单片微机原理P3:80C51外部拓展系统
      外部拓展其实是个相对来说很好玩的章节,可以真正开始用单片机写程序了,比较重要的是外部存储器拓展,81C55拓展,矩阵键盘,动态显示,DAC和ADC。0.IO接口电路概念与存 ... [详细]
author-avatar
mobiledu2502915773
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有