Lecture 2

Advanced SQL

DML（Data Manipulation Language）：数据操作语言，用于增删改查数据，包括SELECT、INSERT、UPDATE、DELETE等操作。 DDL（Data Definition Language）：数据定义语言，用于定义数据库对象，包括CREATE、ALTER、DROP等操作。 DCL（Data Control Language）：数据控制语言，用于控制数据库访问权限，包括GRANT、REVOKE等操作。

SQL

Aggregates

只能用在SELECT语句中。

COUNT

SELECT COUNT(*) FROM table_name;//该语句返回表中的行数。
SELECT COUNT(1) FROM table_name;//该语句返回表中的行数。

Multiple Aggregates

SELECT AVG(column_name), COUNT(column_name), SUM(column_name) FROM table_name;//返回表中某列的平均值、行数、总和。

Distinct Aggregates

SELECT COUNT(DISTINCT column_name) FROM table_name;//返回表中某列的不同值的个数。

Group By

这个语句用于将表中的数据按照某列进行分组。

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;//返回表中某列的不同值的个数。

Having

这个语句用于过滤GROUP BY语句的结果。

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;//返回表中某列的不同值的个数，且该值的个数大于1。

String Operations

LIKE

这个语句用于模糊查询，'%'表示任意字符，'_'表示一个字符。

SELECT * FROM table_name WHERE column_name LIKE 'a%';//返回表中某列以'a'开头的行。
SELECT * FROM table_name WHERE column_name LIKE '%a';//返回表中某列以'a'结尾的行。
SELECT * FROM table_name WHERE column_name LIKE '%a%';//返回表中某列包含'a'的行。
SELECT * FROM table_name WHERE column_name LIKE '_a%';//返回表中某列第二个字符是'a'的行。

CONCAT

这个语句用于连接字符串。

SELECT CONCAT(column_name1, column_name2) FROM table_name;//返回表中某两列的连接结果。

SUBSTRING

这个语句用于截取字符串。

SELECT SUBSTRING(column_name, start, length) FROM table_name;//返回表中某列从start位置开始长度为length的子串。

Date

NOW

这个语句用于返回当前时间。

SELECT NOW();//返回当前时间。

或者使用CURRENT_TIMESTAMP。

DATEDIFF

在MySQL中，DATEDIFF函数用于计算两个日期之间的天数。

SELECT DATEDIFF(DATE('2024-01-01'), DATE('2023-01-01')) AS days_diff;//返回两个日期之间的天数。

Output Redirection

INTO

这个语句用于将查询结果存储到一个新表中。

SELECT column_name INTO new_table_name FROM table_name;//将表中某列的数据存储到新表中。

SELECT column_name INTO OUTFILE 'file_name' FROM table_name;//将表中某列的数据存储到文件中。

Output Control

ORDER BY

ORDER BY [ASC|DESC]

这个语句用于对查询结果进行排序。

SELECT * FROM table_name ORDER BY column_name;//返回表中的所有行，并按照某列进行升序排序。
SELECT * FROM table_name ORDER BY column_name DESC;//返回表中的所有行，并按照某列进行降序排序。

LIMIT

LIMIT <count> [offset]

这个语句用于限制查询结果的数量。

SELECT * FROM table_name LIMIT 10;//返回表中的前10行。
SELECT * FROM table_name LIMIT 10 OFFSET 5;//返回表中的第6到第15行。

Nested Queries

查询中嵌套查询，内层查询几乎可以在任何地方使用。把内层查询的结果作为外层查询的条件。

SELECT name FROM students WHERE sid IN (SELECT sid FROM enrollments WHERE cid = 1);//返回选修了课程1的学生的姓名。

IN is equivalent to '= ANY()':

SELECT name FROM students WHERE sid = ANY (SELECT sid FROM enrollments WHERE cid = 1);//返回选修了课程1的学生的姓名。

Find student record with the highest id that is in the enrollments table:

SELECT * FROM students WHERE sid = (SELECT MAX(sid) FROM students WHERE sid IN (SELECT sid FROM enrollments));

Find all courses that have no students enrolled:

SELECT * FROM courses WHERE NOT EXISTS (SELECT * FROM enrollments WHERE enrollments.cid = courses.cid);

Window Functions

ROW_NUMBER

SELECT ROW_NUMBER() OVER (ORDER BY column_name) FROM table_name;//返回表中某列的行号。
SELECT cid,sid,ROW_NUMBER() OVER (PARTITION BY cid ORDER BY sid) AS row_num FROM enrollments;//返回选修了每门课程的学生的行号。

Find the student with the highest grade in each course:

SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY cid ORDER BY grade ASC) AS rank FROM enrollments) AS ranking WHERE ranking.rank = 1;

Common Table Expressions

提供了一种更简洁的方式来编写复杂的查询。

WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name;//创建一个临时表并查询。

Find student record with the highest id that is in the enrollments table:

WITH cteSource(maxId) AS (SELECT MAX(sid) FROM enrollments) SELECT * FROM students, cteSource WHERE students.sid = cteSource.maxId;

Recursion in CTE

Print the sequence of numbers from 1 to 10:

WITH RECURSIVE cteSource(counter) AS (
    (SELECT 1)
    UNION ALL
    SELECT counter + 1
    FROM cteSource
    WHERE counter < 10
)
SELECT * FROM cteSource;

Conclusion

尽可能只使用一个查询来计算出结果。