-- 查找全部记录 SELECT*FROM {tb}; -- SELECT * FROM student;
-- 查找全部记录的指定列 SELECT {field1}[, ...] FROM {tb}; -- SELECT name, birth FROM student;
-- 查找记录去除重复值 SELECTDISTINCT ... FROM {tb};
-- 查找两个表的笛卡尔积 SELECT {tb1}.{field1}[, ...] FROM {tb1}, {tb2}[, ...];
更改数据
1 2 3
-- 更新 tb1 中满足 condition1 的记录, 更新执行多个 {field} = {value} 指令 UPDATE {tb1} SET {field1} = {value1}[, ...] WHERE {condition1}; -- UPDATE student SET name = "Mike", birth = 19991231 WHERE name = "Jamhus Tao" and birth = 20000229;
删除数据
1 2 3
-- 删除 tb1 中满足 condition1 的记录 DELETEFROM {tb} WHERE {condition1}; -- DELETE FROM student WHERE id = 1;
子句
WHERE 筛选
1 2 3
... WHERE {condition}; -- SELECT * FROM student WHERE name = "Jamhus Tao"; -- 除 INSERT INTO 均可使用 WHERE 筛选
表达式的运算符包括:
操作符
描述
示例
补充
=<>><>=<=
比较运算符
WHERE id <= 10
在某些 SQL 版本中,<> 可作 != 。
NOTANDOR
逻辑运算符
WHERE NOT name = "Jamhus Tao"
IN
多值查找
WHERE id IN (1, 2)
BETWEEN
区间比较
WHERE name BETWEEN "a" AND "b"
如果使用字符串表示字典序,忽略大小写,含边界。
LIKE
模式匹配
WHERE birth LIKE "2000-%-%"
% 匹配多字符(≥0)、_ 匹配单字符。
ORDER BY 排序
只有 SELECT FROM 可以使用 ORDER BY 排序,ORDER BY 必须放在最后。
1 2 3 4
... ORDERBY {field1} [ASC|DESC][, ...] -- SELECT * FROM student ORDER BY id; # 按 id 升序排序, 默认均使用升序排列 -- SELECT * FROM student ORDER BY name ASC, birth DESC; # 首要按 name 升序排序, 次要按 birth 降序排序 -- 只有 SELECT FROM 可使用 ORDER BY 排序
AS 别名
1 2 3 4
... {tb} [AS] {alias} ... # 表别名, 用于快捷表示列, AS 可缺省, 使用了别名就不能使用原名 -- SELECT s.name, c.name FROM student AS s, course AS c; ... {field} [AS] {alias} ... # 列别名, 用于自定义输出列名, AS 可缺省 -- SELECT student.name AS student, course.name AS course FROM student, course;