什么是“高级”SQL？

What is "Advanced" SQL?

提问by DarenW

Looking at a job descriptions where "advanced SQL" is a requirement. I can write basic queries as well as anyone, and have worked with MySQL databases in a professional setting, but what would I be getting into with these jobs, if I were to be hired? What are examples of advanced SQL and where am I along the scale of SQL noob to SQL master?

查看要求“高级 SQL”的职位描述。我可以像任何人一样编写基本查询，并且在专业环境中使用过 MySQL 数据库，但是如果我被录用，我会从事这些工作吗？什么是高级 SQL 的例子，我在 SQL noob 到 SQL master 的规模上处于什么位置？

采纳答案by Beep beep

I think it's best highlighted with an example. If you feel you could write the following SQL statement quickly with little/no reference material, then I'd guess that you probably meet their Advanced SQL requirement:

我认为最好用一个例子来强调。如果你觉得你可以在很少/没有参考资料的情况下快速编写以下 SQL 语句，那么我猜你可能满足他们的高级 SQL 要求：

DECLARE @date DATETIME
SELECT @date = '10/31/09'

SELECT
      t1.EmpName,
      t1.Region,
      t1.TourStartDate,
      t1.TourEndDate,
      t1.FOrdDate,
      FOrdType  = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType  ELSE NULL END),
      FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END),
      t1.LOrdDate,
      LOrdType  = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType  ELSE NULL END),
      LOrdTotal = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END)
  FROM 
      (--Derived table t1 returns the tourdates, and the order dates
      SELECT
            e.EmpId,
            e.EmpName,
            et.Region,
            et.TourStartDate,
            et.TourEndDate,
            FOrdDate = MIN(o.OrderDate),
            LOrdDate = MAX(o.OrderDate)
        FROM #Employees e INNER JOIN #EmpTours et
          ON e.EmpId = et.EmpId INNER JOIN #Orders o
          ON e.EmpId = o.EmpId
       WHERE et.TourStartDate <= @date
         AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)
         AND o.OrderDate BETWEEN et.TourStartDate AND @date
       GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate
      ) t1 INNER JOIN #Orders o
    ON t1.EmpId = o.EmpId
   AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)
 GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate

(source of query)

（查询来源）

And to be honest, that's a relatively simple query - just some inner joins and a subquery, along with a few common keywords (max, min, case).

老实说，这是一个相对简单的查询——只有一些内部连接和一个子查询，以及一些常见的关键字（max、min、case）。

回答by OMG Ponies

Basics

基本

---

---

1. SELECTing columns from a table
2. Aggregates Part 1: COUNT, SUM, MAX/MIN
3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

1. SELECT表中的列
2. 聚合第 1 部分：COUNT, SUM, MAX/MIN
3. 聚合第 2 部分：DISTINCT, GROUP BY,HAVING

Intermediate

中间的

---

---

1. JOINs, ANSI-89 and ANSI-92 syntax
2. UNIONvs UNION ALL
3. NULLhandling: COALESCE& Native NULL handling
4. Subqueries: IN, EXISTS, and inline views
5. Subqueries: Correlated
6. WITHsyntax: Subquery Factoring/CTE
7. Views

4. JOINs、ANSI-89 和 ANSI-92 语法
5. UNION对比 UNION ALL
6. NULL处理：COALESCE& 原生 NULL 处理
7. 子查询：IN，EXISTS，和内嵌的意见
8. 子查询：相关
9. WITH语法：子查询分解/CTE
10. 观看次数

Advanced Topics

高级主题

---

---

• Functions, Stored Procedures, Packages
• Pivoting data: CASE & PIVOT syntax
• Hierarchical Queries
• Cursors: Implicit and Explicit
• Triggers
• Dynamic SQL
• Materialized Views
• Query Optimization: Indexes
• Query Optimization: Explain Plans
• Query Optimization: Profiling
• Data Modelling: Normal Forms, 1 through 3
• Data Modelling: Primary & Foreign Keys
• Data Modelling: Table Constraints
• Data Modelling: Link/Corrollary Tables
• Full Text Searching
• XML
• Isolation Levels
• Entity Relationship Diagrams (ERDs), Logical and Physical
• Transactions: COMMIT, ROLLBACK, Error Handling

• 函数、存储过程、包
• 透视数据：CASE 和 PIVOT 语法
• 分层查询
• 游标：隐式和显式
• 触发器
• 动态 SQL
• 物化视图
• 查询优化：索引
• 查询优化：解释计划
• 查询优化：分析
• 数据建模：范式，1 到 3
• 数据建模：主键和外键
• 数据建模：表约束
• 数据建模：链接/关联表
• 全文检索
• XML
• 隔离级别
• 实体关系图 (ERD)，逻辑和物理
• 事务：COMMIT, ROLLBACK, 错误处理

回答by mjv

The rest of the job opening listing could provide context to provide a better guess at what "Advanced SQL"may encompass.

职位空缺列表的其余部分可以提供上下文，以更好地猜测"Advanced SQL"可能包含的内容。

I disagree with comments and responses indicating that understanding JOIN and aggregate queries are "advanced" skills; many employers would consider this rather basic, I'm afraid. Here's a rough guess as what "Advanced" can mean.

我不同意那些认为理解 JOIN 和聚合查询是“高级”技能的评论和回复；恐怕许多雇主会认为这是相当基本的。这是“高级”可能意味着什么的粗略猜测。

There's been an "awful" lot of new stuff in the RDBMS domain, in the last few years!

在过去的几年里，RDBMS 领域出现了大量“可怕”的新东西！

The "Advanced SQL" requirement probably hints at knowledge and possibly proficiency in several of the new conceptssuch as:

“高级 SQL”要求可能暗示了对几个新概念的知识和可能的熟练程度，例如：

• CTEs (Common Table Expressions)
• UDFs (User Defined Functions)
• Fulltext search extensions/integration
• performance tuning with new partitionning schemes, filtered indexes, sparse columns...)
• new data types (ex: GIS/spatial or hierarchical)
• XML support / integration
• LINQ
• and a few more... (BTW the above list is somewhat MSSQL-centric, but similar evolution is observed in most other DBMS platforms).

• CTE（通用表表达式）
• UDF（用户定义函数）
• 全文搜索扩展/集成
• 使用新的分区方案、过滤索引、稀疏列进行性能调整...）
• 新数据类型（例如：GIS/空间或分层）
• XML 支持/集成
• LINQ
• 还有一些……（顺便说一句，上面的列表有点以 MSSQL 为中心，但在大多数其他 DBMS 平台中也观察到了类似的演变）。

While keeping abreast of the pro (and cons) of the new features is an important task for any "advanced SQL" practitioner, the old "advanced fundamentals" are probably also considered part of the "advanced":

虽然了解新功能的优缺点是任何“高级 SQL”从业者的重要任务，但旧的“高级基础”也可能被视为“高级”的一部分：

• triggers and stored procedures at large
• Cursors (when to use, how to avoid ...)
• design expertise: defining tables, what to index, type of indexes
• performance tuning expertise in general
• query optimization (reading query plans, knowing what's intrinsically slow etc.)
• Procedural SQL
• ...

• 触发器和存储过程
• 游标（何时使用，如何避免...）
• 设计专长：定义表、索引什么、索引类型
• 一般的性能调优专业知识
• 查询优化（阅读查询计划，知道什么本质上很慢等）
• 过程 SQL
• ...

Note: the above focuses on skills associated with programming/lead role. "Advanced SQL" could also refer to experience with administrative roles(Replication, backups, hardware layout, user management...). Come to think about it, a serious programmer should be somewhat familiar with such practices as well.

注意：以上内容侧重于与编程/领导角色相关的技能。“高级 SQL”也可以指管理角色（复制、备份、硬件布局、用户管理...）的经验。想想看，一个认真的程序员也应该对这种做法有些熟悉。

Edit: LuckyLindy posted a comment which I found quite insightful. It suggests that "Advanced" may effectively have a different purpose than implying a fair-to-expert level in most of the categories listed above...
I repeat this comment here to give it more visibility.

编辑：LuckyLindy 发表了我觉得很有见地的评论。它表明“高级”可能实际上具有不同的目的，而不是在上面列出的大多数类别中暗示公平到专家级别......
我在这里重复此评论以使其更具知名度。

I think a lot of companies post Advanced SQL because they are tired of getting someone who says "I'm a SQL expert" and has trouble putting together a 3 table outer join. I post similar stuff in job postings and my expectation is simply that a candidate will not need to constantly come to me for help writing SQL.(comment by LuckyLindy)

我认为很多公司发布高级 SQL 是因为他们厌倦了有人说“我是 SQL 专家”并且难以将 3 个表外连接放在一起。我在职位发布中发布了类似的内容，我的期望只是候选人不需要经常来找我帮助编写 SQL。（LuckyLindy 评论）

回答by dicroce

Check out SQL For Smarties. I thought I was pretty good with SQL too, until I read that book... Goes into tons of depth, talks about things I've not seen elsewhere (I.E. difference between 3'rd and 4'th normal form, Boyce Codd Normal Form, etc)...

查看SQL For Smarties。我认为我对 SQL 也很擅长，直到我读了那本书......深入到大量的深度，谈论我在其他地方没有见过的东西（IE 3'rd 和 4'th normal form 之间的区别，Boyce Codd Normal表格等）...

回答by Joe

I would expect:

我希望：

• stored procedure creation and usage
• joins (inner and outer) and how to correctly use GROUP BY
• performance evaluation/tuning
• knowledge of efficient (and inefficient) ways of doing things in queries (understanding how certain things can affect performance, e.g. using functions in WHERE clauses)
• dynamic SQL and knowledge of cursors (and IMO the few times they should be used)
• understanding of schema design, indexing, and referential integrity

• 存储过程的创建和使用
• 连接（内部和外部）以及如何正确使用 GROUP BY
• 性能评估/调整
• 了解查询中高效（和低效）的做事方式（了解某些事情如何影响性能，例如在 WHERE 子句中使用函数）
• 动态 SQL 和游标知识（以及 IMO 应该使用它们的次数）
• 了解模式设计、索引和参照完整性

回答by Scott Weinstein

Some "Advanced" features

一些“高级”功能

• recursive queries
• windowing/ranking functions
• pivot and unpivot
• performance tuning

• 递归查询
• 窗口/排序函数
• 枢轴和逆枢轴
• 性能调优

回答by matt b

Performance tuning, creating indices, stored procedures, etc.

性能调优、创建索引、存储过程等。

"Advanced" means something different to everyone. I'd imagine this type of thing means something different to every job-poster.

“高级”对每个人都有不同的含义。我想这种事情对每个工作海报都有不同的意义。

回答by Paul Sasik

When you see them spelled out in requirements they tend to include:

当您看到它们在需求中详细说明时，它们往往包括：

• Views
• Stored Procedures
• User Defined Functions
• Triggers
• sometimes Cursors

• 观看次数
• 存储过程
• 用户定义函数
• 触发器
• 有时光标

Inner and outer joins are a must but i rarely ever see it mentioned in requirements. And it's surprising how many so-called db professionals cannot get their head around a simple outer join.

内连接和外连接是必须的，但我很少在需求中看到它。令人惊讶的是，有多少所谓的数据库专业人员无法理解简单的外连接。

回答by Ignacio Vazquez-Abrams

SELECT ... HAVING ...is a good start. Not many developers seem to understand how to use it.

SELECT ... HAVING ...是一个好的开始。似乎没有多少开发人员了解如何使用它。

回答by Robert Harvey

I suppose subqueries and PIVOT would qualify, as well as multiple joins, unions and the like.

我想子查询和 PIVOT 以及多个连接、联合等都符合条件。