access sql 效率_SQL必知必会：全方位掌握SELECT查询核心用法

原创 于 2020-12-28 14:21:48 发布 · 1.1k 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#access sql 效率

本文详细介绍了SQL中的SELECT查询，包括简单查询、汇总分析、复杂查询和多表查询。讲解了如何检索列、设定别名、去重、使用WHERE和HAVING过滤，以及GROUP BY和ORDER BY的运用。还涵盖了视图、子查询和各种联结方式，如内联结、外联结和全联结，帮助读者深入理解并掌握SQL查询的核心技巧。

4a6b6e18437065dc581d5c56d74a65eb.png

SQL四大功能增、删、查、改中，查可谓是最最核心的功能，而“肩负”着查询检索重任的SELECT语句自然也是SQL中最最重要的语句了～。那么，SQL的SELECT用法，你知多少呢？

本篇目录：

  • 简单查询
  • 汇总分析
  • 复杂查询
  • 多表查询

简单查询

检索一列：

SELECT 姓名 FROM 学生表;

检索多列：

SELECT 姓名,年龄 FROM 学生表;

为列设定别名：

SELECT 姓名 AS 名字 FROM 学生表;
SELECT 姓名,成绩/100 AS '百分比成绩' FROM 成绩表;

检索所有列：

SELECT * FROM 学生表；

去重检索：

SELECT DISTINCT 姓名 FROM 学生表；

指定条件查询：

SELECT 姓名 FROM 成绩表 WHERE 成绩>60;
SELECT 姓名 FROM 成绩表 WHERE NOT 成绩>60;

SELECT 姓名 FROM 学生表 WHERE 性别='女' AND 学号=0001;
SELECT 姓名 FROM 学生表 WHERE 性别='女' AND (姓名='张三' OR 姓名='李四');

-- IN 是 OR 的简便用法
SELECT 姓名 FROM 学生表 WHERE 学号=0001 OR 学号=0002;
SELECT 姓名 FROM 学生表 WHERE 学号 IN (0001,0002);
SELECT 姓名 FROM 学生表 WHERE 学号 NOT IN (0001,0002);

-- BETWEEN AND (MySQL 中包含边界)
-- 注意！！不同的数据库对 BETWEEN AND 操作符的处理方式是有差异的。
SELECT 学号,成绩 FROM 成绩表 WHERE BETWEEN 60 AND 90;
SELECT 学号,成绩 FROM 成绩表 WHERE 成绩>=60 AND 成绩<=90;

SELECT 姓名 FROM 学生表 WHERE 姓名 IS NULL;
SELECT 姓名 FROM 学生表 WHERE 姓名 IS NOT NULL;
SELECT 姓名 FROM 学生表 WHERE NOT 姓名 IS NULL;

模糊条件查询：

-- 查询姓李的学生
SELECT * FROM 学生表 WHERE 姓名 LIKE '李%';
-- 查询名字最后一个字是“四”的学生
SELECT * FROM 学生表 WHERE 姓名 LIKE '%四';
-- 查询名字中有“小”字的学生
SELECT * FROM 学生表 WHERE 姓名 LIKE '%小%';
-- 查询名叫“王X“的学生
SELECT * FROM 学生表 WHERE 姓名 LIKE '王_';
-- 查询名叫“张XX“的学生
SELECT * FROM 学生表 WHERE 姓名 LIKE '张__';

汇总分析

一、汇总函数

aggregate function，又叫汇总函数、聚集函数。很多时候，我们并不需要表中的具体数据，而仅仅需要数据的一个汇总结果。使用汇总函数就可以很方便地汇总数据，而不需要返回实际表数据（节省时间、节省资源、节省带宽）。

用法：

-- 计算某列的行数，不计算值为NULL的行，但是会计算空白行。
SELECT COUNT(姓名) FROM 学生表;

-- 去重计数
-- DISTINCT 可以结合任意汇总函数使用，但用在MAX()与MIN()中时没有意义。
SELECT COUNT(DISTINCT 姓名) FROM 学生表;

-- 计算总行数，包括含NULL值的行
SELECT COUNT(*) FROM 学生表;

-- 求和
SELECT SUM(成绩) FROM 成绩表;

-- 求均值
SELECT AVG(成绩) FROM 成绩表;

-- 求最值
SELECT MAX(成绩),MIN(成绩) FROM 成绩表;

-- 组合使用汇总函数
-- 使用AS给查询结果取别名
SELECT COUNT(*) AS 考生人数,AVG(成绩) AS 平均成绩 FROM 成绩表;

二、数据分组

使用 GROUP BY 子句，可对数据进行分组，然后对每个分组（而不是整个结果集）进行汇总。

WHERE 与 GROUP BY 的区别：

-- 查询学号为 0001 的学生选修的课程数量
SELECT COUNT(课程名) AS 选修课数量 FROM 选修课程表 
  WHERE 学号 = '0001';
SELECT COUNT(*) AS 选修课数量 FROM 选修课程表 
  WHERE 学号 = '0001';

-- 查询每位学生选修的课程数量
SELECT 学号,COUNT(课程名) AS 选修课数量 FROM 选修课程表 
  GROUP BY 学号;
SELECT 学号,COUNT(*) AS 选修课数量 FROM 选修课程表 
  GROUP BY 学号;

-- 使用 GROUP BY 后，就不必一一指定每个组了，系统会自动进行分组

注意事项：

  1. GROUP BY 子句可以包含任意数目的列，也即：进行嵌套分组。
  2. GROUP BY 子句中必须是：检索列或有效的表达式；不可以是：汇总函数。
  3. 除汇总函数外，SELECT 中的每一列都必须在 GROUP BY 子句中给出；如果 SELECT 中使用表达式，则在 GROUP BY 子句中须指定相同的表达式，不能用别名。
  4. 如果没有GROUP BY子句，SELECT 中出现了汇总函数，就不能出现检索列或者列的表达式。
  5. 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回；如果列中有多行 NULL 值，它们将分为一组。

用法实例：

SELECT 性别,COUNT(*) FROM 学生表 
  WHERE 出生日期 > '1990-01-01' GROUP BY 性别;

SELECT 课程名,MAX(成绩),MIN(成绩) FROM 成绩表 
  GROUP BY 课程名;

SELECT 课程名,COUNT(姓名) FROM 课程表 
  GROUP BY 课程名;

三、过滤分组

HAVING 子句能够对分组进行过滤（而不是对行进行过滤）。

WHERE 与 HAVING 的区别：

  1. WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。
  2. WHERE 只能过滤行，而 HAVING 能过滤分组。
  3. HAVING 与 WHERE 非常类似，如果不指定 GROUP BY，则大多数 DBMS 会同等对待它们。

个人理解

  1. HAVING 子句不可以使用别名，必须使用汇总函数本身。
  2. HAVING 应结合汇总函数使用，否则的话直接用 WHERE 实现就好了。

例如，以下两句结果相同：

SELECT 学号,AVG(成绩) AS 平均成绩 FROM 成绩表 
GROUP BY 学号 HAVING 学号<0002;

SELECT 学号,AVG(成绩) AS 平均成绩 FROM 成绩表 
WHERE 学号<0002 GROUP BY 学号;

过滤分组实例：

SELECT 性别,COUNT(*)FROM 学生表 
  GROUP BY 性别 HAVING COUNT(*)>1;

四、排序

ORDER BY 字段名 DESC(降序) / ASC(升序,默认)

-- 注：Access不允许使用别名排序
SELECT 课程号,AVG(成绩) AS 平均成绩 FROM 成绩表
GROUP BY 课程号 HAVING AVG(成绩)>80 ORDER BY 平均成绩 DESC;

-- 指定多个列名排序
SELECT * FROM 成绩表 ORDER BY 成绩 ASC,课程号 DESC;

五、限制结果

在不同的DBMS中，查询结果的有限行的操作方法是不同的：

  • 在 SQL Server 和 Access 中，使用 TOP 行数 关键字；
  • 在 DB2 中，使用 FETCH FIRST 行数 ROWS ONLY 子句；
  • 在 MySQL、MariaDB、PostgreSQL 或者 SQLite 中，使用 LIMIT 行数 OFFSET 位置 子句；
  • 在 MySQL 和 MariaDB中，可以使用简化版： LIMIT 位置,行数。

复杂查询

视图

  1. 虚拟的表，它保存的不是数据，而是 SQL 语句。
  2. 会随着源表的更新自动更新，与数据库的连接断开后，它就会被自动删除。
  3. 可以节省数据存放空间。
  4. 简化复杂的 SQL 操作，并方便反复调用。
  5. 可以只使用表的一部分而不是整个表。
  6. 可以保护数据，只需要授予用户访问表的特定部分的权限。
  7. 可返回与底层表的表示和格式不同的数据。

注意事项：

  1. 要避免在视图基础上再次创建视图，多重视图会降低SQL的性能和效率。
  2. 不可往视图中插入数据（会报错）。
-- 创建视图
CREATE VIEW 视图名称(查询列1,查询列2)
AS
SELECT 查询列1,查询列2 FROM ……;

-- 示例
CREATE VIEW 按性别汇总(性别,人数)
AS
SELECT 性别,COUNT(*)
FROM 学生表
GROUP BY 性别;

-- 在 FROM 子句中直接写定义视图的 SQL 语句，相当于“一次性视图”
-- 此处 AS 不能省略，必须指定一个别名，否则会报错。
SELECT 性别,人数
FROM (SELECT 性别,COUNT(*) AS 人数
      FROM 学生表
      GROUP BY 性别)
AS 按性别汇总;

子查询

注意事项：

  1. 作为子查询的 SELECT 语句只能查询单个列，企图检索多个列将报错。【？？】
  2. 使用子查询有时不是执行检索的最有效方法。
  3. 子查询没有嵌套数量限制，但实际使用中由于性能限制，不应嵌套太多子查询。
-- 查询每位同学的平均分
-- 作为计算字段使用时，子查询并不是完整的 SQL 语句
SELECT 姓名,(SELECT AVG(成绩)
            FROM 成绩表
            WHERE 学生表.学号 = 成绩表.学号) AS 平均分
FROM 学生表;

MySQL子查询关键字：IN、ANY/SOME、ALL。它们的查询语法如下：

operand IN (subquery);
operand comparison_operator ANY (subquery);
operand coparison_operator SOME (subquery);
operand comparison_operator ALL (subquery);

其中，SOME是ANY的别名，用法与ANY完全相同；ANY与ALL必须与一个比较操作符一同使用。

ANY表示：与子查询返回的列中的任一值进行比较，如果比较结果为True，则返回True。（理解为「存在」：一真则真，同假为假）

ALL表示：与子查询返回的列中的所有值进行比较，如果比较结果为True，则返回True。（理解为「任意」：一假则假，同真为真）

-- 利用子查询进行过滤时，内层查询的结果，为外层查询提供限定范围。

-- 找出获得过满分(100分)的所有学生
-- 一条 SQL 语句就可从多个表中检索数据
SELECT 姓名,学号
FROM 学生表
WHERE 学号 IN (SELECT 学号
              FROM 成绩表
              WHERE 成绩=100);

-- 找出比01号学生任意一科分数高的成绩
-- 「存在」「逻辑或」
SELECT 学号,成绩
FROM 成绩表
WHERE 成绩 > ANY(SELECT 成绩
                FROM 成绩表
                WHERE 学号 = 01);

-- 找出比01号学生所有科分数都高的成绩
-- 「任意」「逻辑与」
SELECT 学号,成绩
FROM 成绩表
WHERE 成绩 > ALL(SELECT 成绩
                FROM 成绩表
                WHERE 学号 = 01);

其他笔记：

  • NOT IN 等价于 <>ALL。
  • IN 等价于 =ANY。

标量子查询(scalar subquery)

标量，是单一的意思。标量子查询就是返回单一值的子查询。

由于返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。

实例：

-- WHERE中的标量子查询
-- 定义成绩低于60分的为差生，大于等于85分的为优生。
SELECT 学号,成绩
FROM 成绩表
WHERE 成绩 BETWEEN (SELECT AVG(成绩)
                   FROM 成绩表
                   WHERE 成绩 <60)
AND (SELECT AVG(成绩)
     FROM 成绩表
     WHERE 成绩 >=85);

-- SELECT中的标量子查询(会生成一整列相同的值)
-- 给每个学生打个人分数条，最后一列为全班平均分
SELECT 学号,成绩,(SELECT AVG(成绩) 
                FROM 成绩表) AS 平均成绩
FROM 成绩表;

关联子查询

标量子查询只能返回一个值。当我们的返回值有多个，且需要进行比较操作时，该怎么办？答：使用关联子查询。

实例：

-- 此处 GROUP BY 子句可以不写
SELECT 课程号,学号,成绩
FROM 成绩表 AS s1
WHERE 成绩 > (SELECT AVG(成绩)
             FROM score AS s2
             WHERE s1.课程号=s2.课程号
             GROUP BY 课程号)

个人理解：关联子查询返回的其实是两列值：一列我们需要用到的值，以及另一列与之有着对应关系的键值。每科平均分都对应一个科目id，每类商品售价都对应一个类目id，绝对不能混淆。因此，就需要用到关联条件，将子查询中的键值和表中的键值对应起来。

多表查询

组合查询

UNION 用于对多个 SELECT 语句对结果进行合并操作。

注意事项：

  1. UNION 内部的 SELECT 语句必须拥有相同数量的列；
  2. 每条 SELECT 语句中的列的顺序必须相同。
  3. 列也必须拥有相似的数据类型；
  4. UNION 返回结果前，会先去除重复记录；而 UNION ALL 会返回全部记录。

交叉联结（笛卡尔积）

由没有联结条件的表关系返回的结果为笛卡儿积（Cartesian Product）。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

实例：

-- 不给出联结条件，就从多个表中引用数据，会得出笛卡尔积。
SELECT 学生姓名,教师姓名
FROM 学生表,教师表;

内联结（等值联结）

内联结（Inner Join），又称为等值联结（Equijoin），它基于两个表之间的相等列。

实例：

-- 简单格式
SELECT 教师姓名,课程名称
  FROM 教师表,课程表
 WHERE 教师表.教师号 = 课程表.教师号；

-- 标准格式
SELECT 教师姓名,课程名称
  FROM 教师表 INNER JOIN 课程表
 ON 教师表.教师号 = 课程表.教师号；

外联结

外联结包含了那些 在相关表 中 没有关联行 的行。

不同的 SQL 实现中，用来创建外联结的语法有所不同。如：

  • SQLite 不支持 RIGHT OUTER JOIN ；
  • Access、MariaDB、MySQL、Open Office Base 和 SQLite 不支持 FULL OUTER JOIN 。（可用左右联结和 UNION 构建 FULL OUTER JOIN）

左联结：

-- 列出所有的教师和其授课的课程，包括没有开课的教师
SELECT 教师姓名,课程名称
FROM 教师表 LEFT OUTER JOIN 课程表
ON 教师表.教师号 = 课程表.教师号;

右联结：

-- 列出所有的教师和其授课的课程，包括没有开课的教师
SELECT 教师姓名,课程名称
FROM 课程表 RIGHT OUTER JOIN 教师表
ON 教师表.教师号 = 课程表.教师号;

全联结：

-- 列出所有的教师和其授课的课程，包括没有开课的教师，和由外聘教师（没有教师编号）授课的课程
SELECT 教师姓名,课程名称
FROM 教师表 LEFT OUTER JOIN 课程表
ON 教师表.教师号 = 课程表.教师号;
拾亿年
关注
sql语句练习50题(Mysql版)
IsNinetyBro的博客
03-20 3473
数据来自@廖致君 参考@启明星的指引 数据字典 可以复制到电脑的记事本或者备忘录，方便及时查阅，加粗的是主键。 1、学生表 Student(Sid, Sname, Sage, Ssex)——学生表（学号，姓名，出生日期，性别） 2、课程表 Course(Cid, Cname, Tid)——课程表（课程编号，课程名称，教师编号） 3、教师表 Teacher(Tid, Tname)——教师表（教师编...
SQL语句练习
05-06
适合了解过SQL语句的，想要复习的。被资源分为单表查询和多表查询，另外有SQL脚本。单表查询是按难易程度来排的。单表15条，多表10条，几乎涵盖了所有关键字，要注意的地方都会有标注和总结。文件是txt，可以直接用记事本打开，或者NotePad++ 看着会清晰些。
sql语句练习
12-14
一.sql语句基础 1.顯示德國 Germany 的人口 SELECT population FROM world WHERE name = 'Germany' 2.查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值 SELECT name, gdp/population FROM world WHERE area > 5000000 3.顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。 SELECT name, population FROM world WHERE name IN
Mysql | sql语句练习50题（丰富+解析+拓展）
@范恒宾的博客
09-10 1452
表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) --教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_score) --学生编号,课程编号,分数 测试数据 --学生表 CREATE TABLE `Student`( `s_id` VA
ASP_ACCESS_SQL_Injection_Test.rar_asp access注入_linux_sql注入_注入_注入
09-19
ASP_ACCESS_SQL_Injection_Test 是一个针对ASP访问Access数据库时可能出现SQL注入漏洞的测试工具。SQL注入是一种常见的网络安全问题，攻击者通过在输入字段中插入恶意SQL代码，试图获取未经授权的数据或者控制数据库...
sql.zip_SQL java_java sql_sql 查询_数据库 java_结果 查询
09-24
描述中提到的“sql查询”是数据库操作的核心部分。SQL（Structured Query Language）是一种用于管理关系数据库的标准语言。基本的SQL查询通常包括SELECT语句，用于从表中检索数据。例如，`SELECT * FROM student`会...
LabVIEW-SQL-Access.rar_ labview access_LabVIEW SQL_LabVIEW 数据库
09-24
在与SQL（Structured Query Language）和Access结合使用时，LabVIEW可以实现与数据库的交互，如存储、检索和处理大量数据。本文将深入探讨LabVIEW如何与SQL及Microsoft Access数据库进行交互，并介绍相关知识点。 1...
Database Access with SQL.rar_SQL java_java access database
09-14
本资源"Database Access with SQL.rar"提供了一系列的示例程序，帮助开发者了解如何使用Java与SQL进行数据库交互，特别是与Microsoft Access数据库的结合。以下是这个压缩包中涉及的关键知识点： 1. **SQL...
sql.zip_Access和SQL_oracle
09-14
标题“sql.zip_Access和SQL_oracle”表明这个压缩包包含与SQL语言相关的学习资料，特别是关于Access和Oracle数据库的使用。描述指出这是一个教程，旨在教授如何使用SQL来访问和处理多种数据库系统中的数据，包括...
mysql查询必练50题
10-10
摘要： -- 1、查询“01”课程比“02”课程成绩高的所有学生的学号； SELECT a.sid FROM (SELECT sid, score FROM sc WHERE cid=01) a, (SELECT sid ,score FROM sc WHERE cid=02) b WHERE a.score > b.score AND a.sid= b.sid; SELECT a.sid FROM student a LEFT JOIN SC b ON b.cid=01 AND a.sid=b.sid LEFT JOIN SC c ON c.cid=02 AND a.sid=c.sid WHERE b.score>c.score; -- 2、查询平均成绩大于60分的同学的学号和平均成绩； SELECT sid 学号, AVG(score) 平均成绩 FROM sc GROUP BY sid HAVING 平均成绩 > 60; SELECT sid, AVG(score) FROM sc GROUP BY sid HAVING AVG(score)>60; -- 3、查询所有同学的学号、姓名、选课数、总成绩； SELECT s.sid 学号, s.sname 姓名, COUNT(*) 选棵数, SUM(score) 总成绩 FROM student s, sc WHERE s.sid = sc.sid GROUP BY sc.sid; SELECT s.sid, sname, COUNT(*), SUM(score) FROM student s LEFT JOIN sc ON s.sid = sc.sid GROUP BY sc.sid;
数据库常用面试题(SQL Server)
11-25 804
题目1:======　　为管理岗位业务培训信息，建立3个表:　　S (S#,SN,SD,SA)    S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄　　C (C#,CN )    C#,CN 分别代表课程编号、课程名称　　SC ( S#,C#,G )    S#,C#,G 分别代表学号、所选修的课程编号、学习成绩　1. 使用标准SQL嵌套语句查询选修课程名称为 税收基础 的学员
掌握SQL核心技能：《SQL必知必会 第4版》教材数据解析
《SQL必知必会 第4版》作为一本专注于SQL语言的书籍，对数据库技术的学习者来说，是一本不可或缺的入门级教材。SQL（Structured Query Language，结构化查询语言）是一种用于存取和操作数据库的标准编程语言。它被...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值