📚 SQL Key Points for GCSE Computer Science | GCSE 计算机：SQL 考点精讲
Structured Query Language (SQL) is the standard way to communicate with relational databases. In the GCSE Computer Science syllabus, you need to understand how to retrieve, insert, update, and delete data using SQL statements. This article breaks down every essential SQL concept you are expected to know, with clear examples and bilingual explanations to help you revise effectively.
结构化查询语言（SQL）是与关系型数据库进行交互的标准方式。在GCSE计算机科学大纲中，你需要了解如何使用SQL语句检索、插入、更新和删除数据。本文拆解了每一个你必须掌握的核心SQL概念，并配有清晰的示例和中英双语解释，帮助你高效复习。
1. Introduction to Databases and SQL | 数据库与SQL简介
A relational database stores data in tables (also called relations). Each table consists of rows (records) and columns (fields). SQL is the language used to manage and query these tables. It allows users to create, read, update, and delete data – commonly referred to as CRUD operations. SQL is declarative, meaning you tell the database what you want, not how to get it.
关系型数据库将数据存储在表（也称为关系）中。每个表由行（记录）和列（字段）组成。SQL 是用于管理和查询这些表的语言。它允许用户创建、读取、更新和删除数据，通常称为 CRUD 操作。SQL 是声明性的，这意味着你告诉数据库你想要什么，而不是如何获取它。
2. Basic SQL Structure: SELECT … FROM … | 基本SQL结构：SELECT … FROM …
The most fundamental SQL query retrieves data from a table. The syntax is: SELECT column1, column2 FROM table_name;. To select all columns, use the asterisk wildcard: SELECT * FROM table_name;. The semicolon marks the end of a statement. For example, SELECT name, age FROM students; returns the name and age of every student in the ‘students’ table.
最基本的 SQL 查询是从表中检索数据。语法是：SELECT 列1, 列2 FROM 表名;。要选择所有列，使用星号通配符：SELECT * FROM 表名;。分号标志着语句的结束。例如，SELECT name, age FROM students; 会返回 ‘students’ 表中每位学生的姓名和年龄。
3. Filtering Data with WHERE | 使用WHERE子句筛选数据
The WHERE clause is used to filter records that meet specific conditions. It follows the FROM clause. Common comparison operators include = (equal), <> or != (not equal), > (greater than), < (less than), >= and <=. Logical operators such as AND, OR, and NOT can combine conditions. For instance, SELECT * FROM products WHERE price > 100 AND category = ‘Electronics’; retrieves electronic products priced above 100.
WHERE 子句用于筛选满足特定条件的记录。它紧跟在 FROM 子句之后。常见的比较运算符包括 =（等于）、<> 或 !=（不等于）、>（大于）、<（小于）、>= 和 <=。逻辑运算符如 AND、OR 和 NOT 可以组合条件。例如，SELECT * FROM products WHERE price > 100 AND category = ‘Electronics’; 检索价格高于100的电子产品。
4. Sorting Results with ORDER BY | 使用ORDER BY对结果排序
The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order. If you do not specify ASC or DESC, ascending is the default. You can sort by multiple columns by separating them with commas. Example: SELECT name, score FROM exams ORDER BY score DESC, name ASC; orders results by score from highest to lowest, and for equal scores, alphabetically by name.
ORDER BY 子句按升序（ASC）或降序（DESC）对结果集进行排序。如果不指定 ASC 或 DESC，则默认为升序。你可以通过用逗号分隔多个列来进行多列排序。示例：SELECT name, score FROM exams ORDER BY score DESC, name ASC; 按分数从高到低排序，对于相同分数，按姓名以字母顺序排序。
5. Inserting, Updating and Deleting Data | 插入、更新与删除数据
To add a new record, use the INSERT INTO statement: INSERT INTO table_name (column1, column2) VALUES (value1, value2);. To modify existing records, use UPDATE: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; – always include WHERE to avoid updating all rows. To remove records, use DELETE FROM: DELETE FROM table_name WHERE condition;. Forgetting the WHERE clause in UPDATE or DELETE can be disastrous.
要添加一条新记录，使用 INSERT INTO 语句：INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);。要修改现有记录，使用 UPDATE：UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件; – 务必带上 WHERE 子句以避免更新所有行。要删除记录，使用 DELETE FROM：DELETE FROM 表名 WHERE 条件;。在 UPDATE 或 DELETE 中忘记 WHERE 子句可能会造成灾难性后果。
6. Aggregate Functions: COUNT, SUM, AVG, MAX, MIN | 聚合函数：COUNT, SUM, AVG, MAX, MIN
Aggregate functions perform calculations on a set of rows and return a single value. COUNT returns the number of rows, SUM the total, AVG the average, MAX the largest value, and MIN the smallest. They are often used with GROUP BY. Example: SELECT COUNT(*) FROM orders; gives the total number of orders. SELECT AVG(price) FROM products; returns the average product price.
聚合函数对一组行进行计算并返回单个值。COUNT 返回行数，SUM 返回总和，AVG 返回平均值，MAX 返回最大值，MIN 返回最小值。它们常与 GROUP BY 一起使用。示例：SELECT COUNT(*) FROM orders; 返回订单的总数。SELECT AVG(price) FROM products; 返回产品的平均价格。
7. Grouping Data with GROUP BY | 使用GROUP BY进行数据分组
GROUP BY groups rows that have the same values in specified columns, allowing aggregate functions to be applied to each group independently. It is placed after WHERE and before ORDER BY. For example, SELECT category, COUNT(*) FROM products GROUP BY category; counts how many products exist in each category. Every column in the SELECT list that is not an aggregate must appear in the GROUP BY clause.
GROUP BY 将指定列中具有相同值的行分为一组，从而对每一组独立应用聚合函数。它放在 WHERE 之后、ORDER BY 之前。例如，SELECT category, COUNT(*) FROM products GROUP BY category; 统计每个类别中有多少产品。SELECT 列表中不是聚合函数的每一列都必须出现在 GROUP BY 子句中。
8. Filtering Groups with HAVING | 使用HAVING过滤分组
HAVING is used to filter groups after aggregation, similar to how WHERE filters rows before aggregation. HAVING comes after GROUP BY. For instance, SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10; returns only categories with more than 10 products. You cannot use WHERE with aggregate functions directly on grouped data – that’s what HAVING is for.
HAVING 用于在聚合之后过滤分组，类似于 WHERE 在聚合之前过滤行。HAVING 位于 GROUP BY 之后。例如，SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10; 只返回产品数量大于10的类别。不能直接对分组数据使用 WHERE 和聚合函数，这正是 HAVING 的用途。
9. Using LIKE and Wildcards | 使用LIKE与通配符
The LIKE operator enables pattern matching in a WHERE clause. Two wildcards are commonly used: % represents zero or more characters, and _ (underscore) represents exactly one character. WHERE name LIKE ‘A%’ finds names starting with ‘A’. WHERE name LIKE ‘_n%’ finds names where the second letter is ‘n’. These are essential for searching text fields flexibly.
LIKE 运算符允许在 WHERE 子句中进行模式匹配。常用的两个通配符是：% 表示零个或多个字符，_（下划线）表示恰好一个字符。WHERE name LIKE ‘A%’ 查找以 ‘A’ 开头的名称。WHERE name LIKE ‘_n%’ 查找第二个字母是 ‘n’ 的名称。这些对于灵活搜索文本字段至关重要。
10. Joining Tables: INNER JOIN | 表连接：INNER JOIN
When data is spread across multiple related tables, JOIN combines them based on a shared key. The most common type is INNER JOIN, which returns rows where there is a match in both tables. Syntax: SELECT … FROM table1 INNER JOIN table2 ON table1.primary_key = table2.foreign_key;. For example, SELECT students.name, grades.subject FROM students INNER JOIN grades ON students.id = grades.student_id; shows each student’s name with their subjects.
当数据分布在多个相关的表中时，JOIN 根据共享键将这些表连接起来。最常见的类型是 INNER JOIN，它返回两个表中匹配的行。语法：SELECT … FROM 表1 INNER JOIN 表2 ON 表1.主键 = 表2.外键;。例如，SELECT students.name, grades.subject FROM students INNER JOIN grades ON students.id = grades.student_id; 显示每位学生的姓名及其科目。
11. Simple Data Types and Primary Keys | 简单数据类型与主键
GCSE-level SQL expects awareness of basic data types: INTEGER for whole numbers, VARCHAR(n) or TEXT for strings of variable length (n characters max), REAL or FLOAT for decimals, and DATE for dates. A primary key is a column (or combination of columns) that uniquely identifies each row. It cannot be NULL and must be unique. Example table definition: CREATE TABLE students ( id INTEGER PRIMARY KEY, name VARCHAR(100), dob DATE );
GCSE 阶段的 SQL 要求了解基本数据类型：INTEGER 用于整数，VARCHAR(n) 或 TEXT 用于可变长度字符串（最多 n 个字符），REAL 或 FLOAT 用于小数，DATE 用于日期。主键是能够唯一标识每一行的一列（或列的组合）。它不能为 NULL 且必须唯一。示例表定义：CREATE TABLE students ( id INTEGER PRIMARY KEY, name VARCHAR(100), dob DATE );
12. Practice Example: School Database | 练习实例：学校数据库
Given two tables – Teachers (id, name, subject) and Classes (class_id, teacher_id, room, time) – write SQL for: (a) List all teachers; (b) Find the names of teachers who teach Mathematics; (c) Count classes per teacher; (d) Show teacher names and their class times using a join. Answers: (a) SELECT * FROM Teachers; (b) SELECT name FROM Teachers WHERE subject = ‘Mathematics’; (c) SELECT teacher_id, COUNT(*) FROM Classes GROUP BY teacher_id; (d) SELECT Teachers.name, Classes.time FROM Teachers INNER JOIN Classes ON Teachers.id = Classes.teacher_id;
给定两个表 – Teachers (id, name, subject) 和 Classes (class_id, teacher_id, room, time) – 请写出以下查询的 SQL：(a) 列出所有教师；(b) 查找教授数学的教师姓名；(c) 统计每位教师所教授的课程数量；(d) 使用连接显示教师姓名及其上课时间。答案：(a) SELECT * FROM Teachers; (b) SELECT name FROM Teachers WHERE subject = ‘Mathematics’; (c) SELECT teacher_id, COUNT(*) FROM Classes GROUP BY teacher_id; (d) SELECT Teachers.name, Classes.time FROM Teachers INNER JOIN Classes ON Teachers.id = Classes.teacher_id;
Published by TutorHao | Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导Cancel reply