CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT * FROM student;
表存储记录:每一行是一条记录,每一列是一个字段
每一行(row)是一条记录(record)。按习惯,SQL 关键字写成大写。
一条语句以分号 ; 结尾。
1.2
选择列
把你想要的列(column)列出来,用逗号分隔。用 AS 在结果里给列重命名(一个别名(alias))。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score AS mark FROM student;
选中的列也可以是一个计算——配合 AS 给新列命名:
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score + 5 AS bonus FROM student;
SELECT DISTINCT col 会去掉结果中的重复值。
1.3
使用 WHERE 过滤
WHERE 只保留符合条件(condition)的行。用 =、<>(不等于)、<、>、<=、>= 比较。文本要放在单引号里。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score >= 80;
按此逻辑顺序阅读各部分:FROM → WHERE → SELECT。(数据库可能以不同方式执行操作,但结果相同。)
CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE form = '11A' AND score >= 90;
AND 要求全部为真;OR 只要任一为真;NOT 把一个条件反过来
2.2
LIKE、IN 与 BETWEEN
LIKE 匹配一个文本模式(pattern):% 代表任意文本,_ 代表一个字符——它们是通配符(wildcard)。
CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE name LIKE 'M%'; -- starts with M
模式
匹配
'M%'
以 M 开头
'%a'
以 a 结尾
'%an%'
包含 "an"
'M_i'
M,接恰好一个字符,再接 i
IN 匹配一个值的集合(set);BETWEEN 匹配一个范围(range)(两端都包含)。
CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name, score FROM student
WHERE form IN ('11A', '11C') AND score BETWEEN 80 AND 100;
2.3
NULL 空值
NULL 表示缺失值(missing value)——这个单元格里什么都没存。NULL 不是 0,也不是空字符串。用 = 或 <> 的比较永远匹配不到它;要用 IS NULL 或 IS NOT NULL 来判断。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name FROM student WHERE score IS NULL;
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score IS NOT NULL;
ORDER BY 对结果行排序(sort)。加 DESC 表示降序(descending)(从高到低);默认是升序(ascending)(从低到高)。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC;
按多列排序
列出多个列。第一列出现平局(tie)时,由下一列来决定先后。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',88),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC, name ASC;
LIMIT(前 N 名)
LIMIT n 只保留前 n 行——配合 ORDER BY 就能得到一个前 N 名(top-N)列表。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC LIMIT 2;
CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT COUNT(*), ROUND(AVG(score), 2), MAX(score) FROM student;
4.2
GROUP BY 与 HAVING
GROUP BY 为每个组(group)生成一个汇总行。HAVING 用来筛选这些组——它像 WHERE,但在分组之后才运行。
CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form
HAVING COUNT(*) > 1;
GROUP BY 把行按值收进各自的组;每组产出一行汇总
按所需语法顺序书写 SQL 子句。下表是用于推理查询的逻辑模型;引擎可能会优化其物理执行顺序:
步骤
子句
1
FROM(以及 JOIN)
2
WHERE——筛选行
3
GROUP BY——分组
4
HAVING——筛选组
5
SELECT——计算输出列
6
ORDER BY,然后 LIMIT
常见错误
为了获得可预测的分组结果,请将每个选定的非聚合列进行分组(或使用数据库支持的已知函数依赖)。SQLite 允许在 GROUP BY 之外使用裸列,但其值可能来自组内的任意行;查询成功并不保证你获取的是预期的值。
CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT * FROM student;
5.2
INNER JOIN内连接
连接(join)把两张表的行组合起来。INNER JOIN ... ON ... 只保留键匹配的行。用短别名(alias)(s、c)让查询更易读。
CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;
INNER JOIN 用外键匹配主键,把匹配上的行组合起来
5.3
连接与分组
先连接,再用 GROUP BY 对连接后的行做汇总。
CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2), (3, 'Ana', 1);
SELECT c.name AS class, COUNT(*) AS n
FROM student s INNER JOIN class c ON s.class_id = c.id
GROUP BY c.id, c.name;
CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', NULL);
SELECT s.name, c.name AS class
FROM student s LEFT JOIN class c ON s.class_id = c.id;
INSERT INTO ... VALUES ... 添加新的行(row)。先写出列名,再按相同顺序给出值。(下面每个代码块都以 SELECT 结尾,这样你能看到结果。)
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student (id, name, score) VALUES (1, 'Mei', 88);
INSERT INTO student VALUES (2, 'Sam', 71);
SELECT * FROM student;
INSERT INTO t VALUES (...), (...); 一条语句插入多行。
INSERT 向表中添加一行新数据
6.2
UPDATE 更新行
UPDATE ... SET ... WHERE ... 改变已有的行。一定要加 WHERE,否则每一行都会被改。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
UPDATE student SET score = 75 WHERE name = 'Sam';
SELECT * FROM student;
6.3
DELETE 删除行
DELETE FROM ... WHERE ... 删除行。没有 WHERE 时,它会清空整张表(table)。
CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
DELETE FROM student WHERE score < 80;
SELECT * FROM student;
CREATE TABLE student (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
score INTEGER DEFAULT 0
);
INSERT INTO student (id, name) VALUES (1, 'Mei');
SELECT * FROM student;
PRAGMA foreign_keys = ON;
CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
class_id INTEGER REFERENCES class(id)
);
INSERT INTO class VALUES (1, 'Maths');
INSERT INTO student VALUES (1, 'Mei', 1);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE club (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE membership (
student_id INTEGER NOT NULL REFERENCES student(id),
club_id INTEGER NOT NULL REFERENCES club(id),
PRIMARY KEY (student_id, club_id)
);
INSERT INTO student VALUES (1, 'Mei'), (2, 'Sam');
INSERT INTO club VALUES (1, 'Chess'), (2, 'Art');
INSERT INTO membership VALUES (1, 1), (1, 2), (2, 1);
SELECT s.name, c.name AS club
FROM membership m
INNER JOIN student s ON m.student_id = s.id
INNER JOIN club c ON m.club_id = c.id;