SQL语法基础

复习定位

SQL(结构化查询语言)是操作关系型数据库的标准语言。包括数据定义(DDL——建表、改表)、数据操纵(DML——增/删/改)、数据查询(DQL——SELECT)和数据控制(DCL——权限)。DQL的SELECT是SQL中最灵活复杂的部分——把握SELECT子句顺序——FROM→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT——这是优化器和执行计划的顺序。

DDL

-- 创建表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT CHECK(age > 0 AND age < 120),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 修改表
ALTER TABLE students ADD COLUMN email VARCHAR(100);
ALTER TABLE students MODIFY COLUMN age INT NOT NULL;
ALTER TABLE students DROP COLUMN email;

-- 索引
CREATE INDEX idx_name ON students(name);

DML

INSERT INTO students (name, age, dept_id) VALUES ('Alice', 20, 1);
UPDATE students SET age = 21 WHERE name = 'Alice';
DELETE FROM students WHERE age < 18;

DQL——SELECT

基本查询——SELECT col1, col2 FROM table WHERE condition。

JOIN(连接)：

• INNER JOIN：两表都匹配的行
• LEFT JOIN：左表所有行+右表匹配行(不匹配时右表列为NULL)
• RIGHT JOIN：右表所有行+左表匹配行
• FULL JOIN：两表所有行(MySQL不支持——用UNION模拟)
• CROSS JOIN：笛卡尔积——行的所有组合

分组与聚合：

SELECT dept_id, AVG(age), COUNT(*)
FROM students
WHERE age > 18
GROUP BY dept_id
HAVING AVG(age) > 20
ORDER BY AVG(age) DESC
LIMIT 10;

• WHERE在分组前过滤行
• GROUP BY按列分组
• HAVING在分组后过滤组(可以使用聚合函数)
• ORDER BY排序
• LIMIT限制输出行数

聚合函数

COUNT(*)计数所有行——COUNT(col)计数列非NULL的行数。AVG(col)平均值——SUM(col)总和——MAX(col)/MIN(col)。

子查询

SELECT * FROM students WHERE dept_id IN (SELECT id FROM departments WHERE name='CS');

相关子查询——子查询引用外部查询的列——SELECT * FROM students s WHERE age > (SELECT AVG(age) FROM students WHERE dept_id=s.dept_id);

复习检查

1. WHERE和HAVING的区别——WHERE在分组前过滤行——WHERE不能使用聚合函数。HAVING在分组后过滤组——HAVING可以使用聚合函数。

2. LEFT JOIN和RIGHT JOIN的实质——LEFT JOIN返回左表所有行——RIGHT JOIN返回右表所有行——在仅按列名联表时可通过调换表顺序实现LEFT→RIGHT互换——不必改变联表类型。

3. 聚合函数COUNT(1)和COUNT(*)的区别——在典型的MySQL InnoDB下——COUNT(*)和COUNT(1)等价——都是统计行数——COUNT(col)不计NULL。

4. GROUP BY分组出去后——SELECT中只能出现分组列和聚合函数——其他列不应在SELECT中出现(否则MySQL会随机取一个值——这在SQL标准中是错误)——在更严谨的数据库中(如PostgreSQL)会导致查询失败。

5. 子查询与JOIN在性能上的差异——通常子查询可以转变为等价的JOIN——而JOIN在数据库优化器中更容易被优化——往往比嵌套子查询更快——特别是在大数据集上。