【MySQL】基本查询（上）：创建、读取

1.Create(创建)

语法：

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...value_list: value, [, value] ...

接下来我们用这个下表作为例子：

-- 创建一张学生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

可以看到，我们成功创建了一个表

1.1 单行数据 全列插入

-- 插入两条记录，value_list的数量和顺序必须与定义表的列的一致
insert into students values (100, 1000, '张三', NULL);
insert into students values (101, 1001, '李四', '1234');
-- 插看插入结果
select * from students;

1.2 多行数据 指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
insert into students (id, number, name) values
(102, 1002, '王五'),
(103, 1003, '赵六');-- 查看插入结果
SELECT * FROM students;

1.3 插入/更新

由于我们在定义这个学生表时，将id设定为主键，将number设定为唯一键，则它们的值都是唯一的，如果我们的插入语句中出现重复的id或number，mysql就会报错，插入失败。

主键冲突：

唯一键冲突：

其实我们可以选择性地进行同步更新操作：

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...-- ON DUPLICATE KEY 当发生重复key的时候

1.4 替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (number, name) VALUES (1001, '李小帅');

大家可以发现：replace和on duplicate key update的区别在于：如果要插入的行已存在，replace会删除原来行，然后插入新行，适用于希望完全替换已存在行的情况；on duplicate key update则不会删除旧行，而是更新已存在行的指定列，适用于希望保留现有数据并更新部分字段的情况。

2.Retrieve(读取)

语法：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

接下来我们还是用一张表来做例子：

-- 创建表结构
create table exam_result(
id int unsigned primary key auto_increment,
name varchar(20) not null,
Chinese float default 0.0 comment '语文成绩',
Math float default 0.0 comment '数学成绩',
English float default 0.0 comment '英语成绩'
);-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

2.1 基础查询

全列查询

-- 通常情况下不建议进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。select * from exam_result;

指定列查询

-- 指定列的查询不需要按表定义的顺序来
select name, Chinese from exam_result;

查询字段为表达式

-- 表达式不包含字段
select name, 10 from exam_result;、

-- 表达式包含一个字段
select id, name, English + 10 from exam_result;

-- 表达式包含多个字段
select name, Chinese + Math + English from Exam_result;

-- 为查询结果指定别名
select name as '姓名', Chinese + Math + English as '总分' from exam_result;

2.4 distinct去重

-- 98 分重复了
select Math from exam_result;-- 对结果进行去重
select distinct Math from exam_result;

不去重的结果：

去重的结果：

2.2 where 条件

举几个例子：

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩:

姓孙的同学 及 孙某同学：

语文成绩好于英语成绩的同学：

总分在 200 分以下的同学：

语文成绩 > 80 并且不姓孙的同学：

孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80：

2.3 order by 对结果排序

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

依然是举几个例子：

查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示：

查询同学及总分，由高到低：

2.4 limit 筛选分页结果

-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 0;

-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 3;

-- 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 6;