一、Select Overview
• SELECT 语句用于从表中查询(query)数据。
• 在 C 或 Java 中可以用数组和循环(loops)来描述 SELECT 的行为。
解释:像编程语言中遍历数组一样,SELECT 可以遍历表中的行并按条件选择数据
• 数据库是一个更加复杂的程序。
1. The basic structure of a SELECT statement
SELECT [DISTINCT | ALL]column-list FROM table-names[WHERE condition][ORDER BY column-list][GROUP BY column-list][HAVING condition]
• DISTINCT 用于去重(remove duplicates),ALL 保留所有行(keeps all rows)。
二、Filtering rows and columns
筛选行和列
1. SELECT
SELECT is able to fetch the columns and rows of a table.
To filter columns of a table:
SELECT col1[,col2……] FROM table_name;SELECT id, code from grade;
2. DISTINCT and ALL
DISTINCT 消除重复行; ALL 通常是默认(default)选项,不会去除重复行(retain duplicates)。
3. Expressions in SELECT
AS 关键字用于给列赋予一个新的名称
SELECT a, b, a+b as sumfrom dup_test;
4. Where
• Rows can be filtered using the WHERE clause(子句).
• * (Asterisk)indicates selecting all columns from a table.
SELECT * from table_nameWHERE predicate;
示例
to query records with marks below 60:
SELECT * FROM gradeWHERE mark < 60;
5. Where Clause
• WHERE 子句用于限制返回的行。(restrict returned rows)
• 只返回满足条件的行。
WHERE clause takes the form of a predicate
predicate: an expression that either returns a ture or false
• The evaluation process of the SELECT statement is as follows:
(1)获取表(来自 FROM 部分)。
(2) 对每个元组(行)评估(assess) WHERE 子句。
• True (or non-zero values) -> accepted
• False (or zero) -> filtered
(3) 获取列(来自 SELECT 部分)。
• Get columns (from the SELECT part).
SELECT DISTINCT IdFROM gradeWHERE mark >= 60;
SELECT ID, MarkFROM GRADEWHERE (Mark >= 50) and (Code = ‘IAI’);
LIKE 运算符
• LIKE 用于字符串匹配(string matching),% 表示任意数量的字符,_ 表示一个字符。
wildcard characters:通配符字符用于 LIKE 运算符的模式匹配,它们帮助我们在字符串中进行模糊搜索。
• LIKE '%son':查找所有以 son 结尾的字符串,如 Johnson。
• LIKE 'C_%_%_%':查找所有以 C 开头,并且总长度为 4 个字符的字符串,如 Carl。
• LIKE '%com%':查找所有包含 com 的字符串,无论 com 在什么位置,例如 company 或 accommodation。
• For example, to find records with book names containing “crypt”:
SELECT * FROM booksWHERE bookName LIKE '%crypt%';
It is NOT normally case sensitive:
This means that LIKE 'abc%' would match both abc, ABC, Abc, and so on.
SELECT * FROM books WHERE
bookName LIKE '%crypt%'
AND bookName LIKE '%cloud%';SELECT * FROM books WHERE
bookName LIKE '%crypt%'
OR bookName LIKE '%cloud%';
SELECT *FROM TrackWHERE Track_title LIKE '%boy%' OR LIKE'%girl%';
6. Dealing with Date and Time
• Dates and times can be compared like numbers.
SELECT * FROM table-name WHERE date-of-event < '2012-01-01';
• 也可以将日期作为字符串进行匹配(Dates can also be matched as strings)
SELECT * FROM table-name
WHERE date-of-event LIKE '2014-11-%';
7. Logical Statement in SELECT
在 SQL 中,返回布尔值的表达式也可以放在 SELECT 部分,这样可以直接查看布尔结果
SELECT postcode LIKE 'gb' FROM places;
• LIKE 'gb%' 检查 postcode 是否以“gb”开头。若是,则返回 True,否则返回 False。
SELECT id BETWEEN 1 AND 5 FROM staff;
• BETWEEN 1 AND 5 检查 id 值是否在指定范围内。若在范围内,则返回 True,否则返回 False。
• 这些查询不会返回原始数据,而是返回一列布尔值(True 或 False),每个值对应一行数据。
• 可以使用 AND、OR、NOT 等运算符进行组合(combine using operators)。
• Combine using operators like AND, OR, and NOT.
• 在 WHERE 子句中,可以使用 MySQL 支持的任何函数和运算符。
• 但不包括聚合(group)函数(excepte for aggregate function),这些函数在 WHERE 中是无效的(例如 SUM()、AVG() 等),它们通常用在 HAVING 子句中。
• What are the results of the following expressions and their corresponding Boolean value?
(1). (False OR 12) AND True
非零数字(如 12)被视为 True
• 结果:True
(2). 7 + 12
7 + 12 = 19。
• 结果:19
(3). True + 1
True 的数值通常为 1。
1 + 1 = 2。
• 结果:2
(4). False + 2
False 的数值通常为 0。
0 + 2 = 2。
• 结果:2
(5). 39 <> False
False 的数值通常为 0,因此 39 <> 0 是 True。
• 结果:True
(6). 27 AND False
AND 操作中,任何值与 False 组合都会返回 False。
• 结果:False
(7). 0 OR TRUE
0 相当于 False,True 相当于 1。在 OR 操作中,只要一个值为 True,结果就是 True。
• 结果:True
二、Cartesian product
笛卡尔积
• 可以使用 CROSS JOIN 或逗号分隔多个表来得到笛卡尔积。
• Use CROSS JOIN or comma-separated tables to get a Cartesian product of two tables.
SELECT * FROM Student, Grade;
= SELECT * FROM Student CROSS JOIN Grade;
• Student 表中包含5条记录,Grade 表中包含8条记录。每个 Student 记录会与每个 Grade 记录组合,最终生成 5 * 8 = 40 条结果。
• 图中展示了这种组合的过程,例如 student[0] + grade[0] 表示 Student 表的第一个记录与 Grade 表的第一个记录组合。最后生成40行结果。
• 如果两个表有相同的列名(如 ID),则 SQL 无法确定具体指的是哪个表的 ID 列,从而引发歧义(ambiguity)。
• 为了解决这个问题,需要通过 TableName.ColumnName 的形式指定表名和列名,例如 Student.ID 和 Grade.ID。
SELECT Student.ID FROOM student, GradeWHERE Student.ID = Grade.ID;
SELECT from Multiple Tables
• Unrelated combinations can be filtered out.
Question:
SELECT sName, mCodeFROM Student, EnrollmentWHERE Student.sID = Enrollment.sID;SELECT mTitleFROM Student, Enrollment, ModuleWhERE Student.sID = Enrollment.sIDAND Enrollment.mCode = Module.mCodeAND Student.sName = 'Harrison';SELECT Module.mCode, mTitle FROM Enrolment, Module, Student
WHERE (Module.mCode = Enrolment.mCode) AND (Student.sID = Enrolment.sID)AND sYear = 1;
Aliases
别名
Aliases rename columns or tables
(1). Can make names more meaning
(2). Can shorten names,making them easier to use
(3). Can resolve ambiguous names
可以使用 AS 关键字为列或表起别名,以简化引用。
• Use the AS keyword to assign aliases to columns or tables for simpler references.
• Example:
(1) Column alias
SELECT column [AS] new-col-name
(2)Table alias
SELECT * FROM table [AS] new-table-name
The AS keyword is optional
-- 写法 1:省略 AS
SELECT E.ID AS empID, E.Name, W.Department
FROM Employee E, WorksIn W
WHERE E.ID = W.ID;-- 写法 2:加上 AS
SELECT E.ID AS empID, E.Name, W.Department
FROM Employee AS E, WorksIn AS W
WHERE E.ID = W.ID;
SQL 查询的解析和执行顺序与代码的书写顺序不同。SQL 会先解析 FROM 子句,识别出表名和别名,然后再解析其他子句
Aliases and 'Self-Joins'
别名(alias)和自连接(self-join): 通过为同一个表创建不同的别名,可以让表与自己连接,从而实现自连接查询
SELECT A.NameFROM Employee A,Employee BWHERE A.Dept = B.DeptAND B.Name = 'Andy';
• Employee A 和 Employee B:这里将 Employee 表指定了两个别名 A 和 B,可以理解为创建了同一个表的两个副本,以便在一个查询中使用两次相同的表。
• B.Name = 'Andy':限定 B 表中只选择名字为 Andy 的记录。这意味着我们在 B 表中找到了 Andy 所在的部门,并通过 A.Dept = B.Dept 找到该部门的所有员工。
三、Subqueries
子查询
• SELECT 语句可以嵌套,形成子查询。(be nested to form subqueries)
• 子查询的结果可以传递给外部查询。( passed back to outer query)
SELECT Name FROM Employee WHERE Dept =(SELECT Dept FROM EmployeeWHERE Name = 'Andy')
Subqueries and Aliases
• 子查询可以在 FROM 和 WHERE 之间使用
• 子查询的结果必须重新命名,因为子查询的结果没有表名
SELECT * FROM(SELECT name, email FROM teachers) AS tWHERE t.email IS NOT NULL;
• 子查询用别名AS t,以便后续使用WHERE条件引用
集合处理
• IN:检查某个值是否在集合中
SELECT columns FROM tablesWHERE col IN set;SELECT columns FROM tablesWHERE col MOT IN set;SELECT id FROM studentWHERE id IN ('S103', 'S104');SELECT * FROM Employee
WHERE Name NOT IN(SELECT ManagerFROM Employee);-- The query is equivalent toSELECT * FROM Employee WHERE Name NOT IN('Chris', 'Jane');
• EXISTS:检查集合是否为空
SELECT columnsFROM tablesWHERE EXISTS set;SELECT columnsFROM tablesWHERE NOT EXISTS set;
-- The set is always given by a subquery
WHERE 子句中不能直接包含子查询的结果,除非这个子查询返回一个布尔值或一个单一的值
SELECT E1.*
FROM Employee AS E1, Employee AS E2
WHERE E1.Name = E2.Manager;
• ALL/ANY:检查关系是否对集合中的所有/任意元素成立
Checks if a relationship holds for every/one member of a set.
ANY and ALL compare a single value to a set of values
They are used with comparison operators like = , >, <, <>, >=, <=
● val = ANY (set)
is true if there is at least one member of the set equal to value
● val = ALL (set)
is true if all members of the set are equal to the value
• NOT:可以与上述四个选项一起使用
used with any of the above four options.
在 WHERE 子句中如果要包含子查询,通常需要搭配 IN、EXISTS、ANY 等运算符