on
SQL表达能力
这篇文章收录了平时不常见但需要时十分有用的SQL语句。
《SQL Cookbook》
1.12 Transforming Nulls into Real Values
Use the function COALESCE to substitute real values for nulls: 1 select coalesce(comm,0) 2 from emp
2.3 Sorting by Substrings
Use the SUBSTR function in the ORDER BY clause: select ename,job from emp order by substr(job,length(job)-1)
2.4 Sorting Mixed Alphanumeric Data
Use the functions REPLACE and TRANSLATE to modify the string for sorting: /* ORDER BY DEPTNO / 1 select data 2 from V 3 order by replace(data, 4 replace( 5 translate(data,‘0123456789’,’##########’),’#’,’’),’’) / ORDER BY ENAME */ 1 select data 2 from V 3 order by replace( 4 translate(data,‘0123456789’,’##########’),’#’,’’)
2.6 Sorting on a Data-Dependent Key
Use a CASE expression in the ORDER BY clause: 1 select ename,sal,job,comm 2 from emp 3 order by case when job = ‘SALESMAN’ then comm else sal end
3.1 Stacking One Rowset atop Another
Use the set operation UNION ALL to combine rows from multiple tables:
1 select ename as ename_and_dname, deptno 2 from emp 3 where deptno = 10 4 union all 5 select ‘———-’, null 6 from t1 7 union all 8 select dname, deptno 9 from dept
3.4 Retrieving Values from One Table That Do Not Exist in Another
DB2, PostgreSQL, and SQL Server Use the set operation EXCEPT: 1 select deptno from dept 2 except 3 select deptno from emp Oracle Use the set operation MINUS: 1 select deptno from dept 2 minus 3 select deptno from emp MySQL Use a subquery to return all DEPTNOs from table EMP into an outer query that searches table DEPT for rows that are not among the rows returned from the subquery: 1 select deptno 2 from dept 3 where deptno not in (select deptno from emp)
3.5 Retrieving Rows from One Table That Do Not Correspond to Rows in Another
Use an outer join and filter for NULLs (keyword OUTER is optional): 1 select d.* 2 from dept d left outer join emp e 3 on (d.deptno = e.deptno) 4 where e.deptno is null
把左边当作主表，即双方连接的列中主表中有，副表中可能没有。
4.5 Copying a Table Defnition
Use the CREATE TABLE command with a subquery that returns no rows: 1 create table dept_2 2 as 3 select * 4 from dept 5 where 1 = 0
4.7 Blocking Inserts to Certain Columns
Create a view on the table exposing only those columns you want to expose. Then force all inserts to go through that view. For example, to create a view exposing the three columns in EMP: create view new_emps as select empno, ename, job from emp
4.11 Merging Records
The statement designed to solve this problem is the MERGE statement, and it can perform either an UPDATE or an INSERT, as needed. For example: 1 merge into emp_commission ec 2 using (select * from emp) emp 3 on (ec.empno=emp.empno) 4 when matched then 5 update set ec.comm = 1000 6 delete where (sal < 2000) 7 when not matched then 8 insert (ec.empno,ec.ename,ec.deptno,ec.comm) 9 values (emp.empno,emp.ename,emp.deptno,emp.comm)
5.3 Listing Indexed Columns for a Table
PostgreSQL Query PG_CATALOG.PG_INDEXES and INFORMATION_SCHEMA.COLUMNS: 1 select a.tablename,a.indexname,b.column_name 2 from pg_catalog.pg_indexes a, 3 information_schema.columns b 4 where a.schemaname = ‘SMEAGOL’ 5 and a.tablename = b.table_name
MySQL Use the SHOW INDEX command: show index from emp
Chapter 6
Many of the recipes that follow use the TRANSLATE and REPLACE functions that are now available in all the DBMSs covered in this book, with the exception of MySQL, which only has replace.
7.13 Computing Averages Without High and Low Values
MySQL and PostgreSQL Use subqueries to exclude high and low values: 1 select avg(sal) 2 from emp
3 where sal not in ( 4 (select min(sal) from emp), 5 (select max(sal) from emp) 6 )
11.5 Selecting the Top n Records
The solution to this problem depends on the use of a window function. Which win‐ dow function you will use depends on how you want to deal with ties. The following solution uses DENSE_RANK so that each tie in salary will count as only one against the total: 1 select ename,sal 2 from ( 3 select ename, sal, 4 dense_rank() over (order by sal desc) dr 5 from emp 6 ) x 7 where dr <= 5
11.7 Investigating Future Rows
The window function LEAD OVER is perfect for a problem such as this one. It not only makes for a more readable query than the solution for the other products, LEAD OVER also leads to a more flexible solution because an argument can be passed to it that will determine how many rows ahead it should look (by default one). Being able to leap ahead more than one row is important in the case of duplicates in the column you are ordering by. The following example shows how easy it is to use LEAD OVER to look at the salary of the “next” employee hired: select ename, sal, hiredate, lead(sal)over(order by hiredate) next_sal from emp
Grouping
Simply stated, grouping is a way to organize like rows together. When you use GROUP BY in a query, each row in the result set is a group and represents one or more rows with the same values in one or more columns that you specify.
A SQL group will be defined as (G, e), where G is a result set of a single or self-contained query that uses GROUP BY, e is a member of G, and the following axioms are satisfied: • For each e in G, e is distinct and represents one or more instances of e. • For each e in G, the aggregate function COUNT returns a value > 0.
Windowing
Window functions, like aggregate functions, per‐ form an aggregation on a defined set (a group) of rows, but rather than returning one value per group, window functions can return multiple values for each group.
group by每组返回单个值，window functions每组返回多个值。
The presence of the OVER keyword indicates that the invocation of COUNT will be treated as a window function, not as an aggregate function. In general, the SQL standard allows for all aggregate functions to also be window functions, and the keyword OVER is how the language distinguishes between the two uses. So, what did the window function COUNT(*) OVER () do exactly? For every row being returned in the query, it returned the count of all the rows in the table. As the empty parentheses suggest, the OVER keyword accepts additional clauses to affect the range of rows that a given window function considers.
Partitions
You can think of the PARTITION BY clause as a “moving GROUP BY” because unlike a traditional GROUP BY, a group created by PARTITION BY is not distinct in a result set.
By now it should be clear that the PARTITION BY clause works like a GROUP BY clause, but it does so without being affected by the other items in the SELECT clause and without requiring you to write a GROUP BY clause.
When using COUNT, consider whether you want to include NULLs. Use COUNT(column) to avoid counting NULLs. Use COUNT(*) if you do want to include NULLs (since you are no longer counting actual column values, you are counting rows)
Readability + Performance = Power
As you can see, window functions are extremely powerful as they allow you to write queries that contain both detailed and aggregate information. Using window functions allows you to write smaller, more efficient queries as compared to using multiple self-join and/or scalar subqueries.
Common Table Expressions
We introduce the derived table using the WITH clause, specifying the column headings in the parentheses, and use parentheses around the derived table’s query itself. If we want to add more derived tables, we can add more as long as we separate each one with a comma and provide its name before its query (the reverse of how aliasing usually works in SQL).
《Mastering SQL Joins》
CROSS JOIN
CROSS JOIN is a type of SQL JOIN that simply does a CROSS PRODUCT of records from Table 1 and Table 2. (Considering we have CROSS JOIN applied on two tables –table 1 and table 2 ). So, If there are 3 records present in Table 1 and there are 4 records present in table 2. Then the final dataset after implementing the CROSS JOIN will have 3 X 4 records, i.e., 12 total combinations of records.
INNER JOIN
Technically, “INNER JOIN is used to select records that have matching records present in both the tables”.
OUTER JOINS
These 3 different types of OUTER JOINS are:
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- FULL OUTER JOIN or FULL JOIN
LEFT OUTER JOIN displays ALL the records present in the LEFT table and ONLY the MATCHING records present in the RIGHT Table.
Technically, The RIGHT OUTER JOIN selects ALL the records that are in the RIGHT table and then ONLY the matching records present in the LEFT table.
FULL OUTER JOIN or FULL JOIN is a type of JOIN that displays ALL the records present in both tables irrespective of the fact whether the records are MATCHING or NOT-MATCHING based on the common column present in both tables.
《SQL进阶教程》
CASE表达式的一大优势在于能够判断表达式。也就是说，在CASE表达式里，我们可以使用BETWEEN、LIKE和<、>等便利的谓词组合，以及能嵌套子查询的IN和EXISTS谓词。因此，CASE表达式具有非常强大的表达能力。
COUNT函数的使用方法有COUNT（＊）和COUNT（列名）两种，它们的区别有两个：第一个是性能上的区别；第二个是COUNT（＊）可以用于NULL，而COUNT（列名）与其他聚合函数一样，要先排除掉NULL的行再进行统计。第二个区别也可以这么理解：COUNT（＊）查询的是所有行的数目，而COUNT（列名）查询的则不一定是。