- 建表(create)
-- 创建学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT,
sn INT comment '学号',
name VARCHAR(20) comment '姓名',
qq_mail VARCHAR(20) comment 'QQ邮箱'
)
- 插入(insert)
-- 插入两条记录,value_list 数量必须和定义表的列的数量及顺序一致
INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');
INSERT INTO student (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');
- 查询(select)
语法
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
案例
-- 全列查询
SELECT id, name, english FROM exam_result
-- 查询指定字段,设置别名
SELECT id, name, chinese + math + english 总分 FROM exam_result;
-- 去重 distinct
SELECT DISTINCT math FROM exam_result;
- 排序(order by)
--默认为升序(ASC)
SELECT name, qq_mail FROM student ORDER BY qq_mail;
--降序需添加DESC
SELECT name, qq_mail FROM student ORDER BY qq_mail DESC;
-
条件查询(where)
比较运算符
逻辑运算符
-
分页查询(limit)
-- 起始下标为0
-- 从 0 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
- 修改(update)
语法
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
案例
-- 将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;
- 删除(delete)
语法
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
案例
-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';
-- 删除整表数据
DELETE FROM exam_result;