MySQL的增删改查

新增(Create)

语法

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

例如

create table student (
id int,
sn int,
name char(50),
qq_mail varchar(20)
);

单行数据，全列插入

//插入的数据，value_list 数量必须和定义表的列的数量及顺序一致
insert into student values(9, 111, 'Jack', '20202090');

多行插入，指定列插入

//插入记录，value_list 数量必须和指定列数量及顺序一致
insert into student(id, sn, name) values(10, 222, 'Jin');
insert into student(id, sn, name) values(11, 322, 'Kin');

查询(Retrieve)

语法

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

全列查询

SELECT * FROM exam_result;

例如：

指定列查询

-- 指定列的顺序不需要按定义表的顺序来
SELECT id, name, english FROM exam_result;

例如：

查询字段为表达式

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result;
-- 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;
-- 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

例如：查询语文，数学和英语三门的总成绩
select id, name, chinese + english + math from exam_result;

别名

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称，语法：

SELECT column [AS] alias_name [...] FROM table_name;

例如：查询语文，数学和英语三门的总成绩
select id, name, chinese + english + math as total from exam_result;

去重(Distinct)

例如：数学成绩去重
select distinct math from exam_result;

排序(Order by)

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

• 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
• NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

例如：

• 数学成绩排序（升序）
  select * from exam_result order by math
  
• 数学成绩排序（降序）
  select * from exam_result order by math desc;
  

使用表达式或者别名排序

例如：对数学，语文，英语三门成绩总和升序
select name, chinese + english + math as total from exam_result order by total;

对多个字段排序

例如：
对数学成绩降序，语文和英语升序:
select name, chinese, english, math from exam_result order by math desc, chinese, english;

条件排序(Where)

运算符

逻辑运算符

注：

• WHERE条件可以使用表达式，但不能使用别名
• AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

基本查询：

• 查询英语成绩不及格的同学:
  select id, name ,english from exam_result where english < 60;
  

• 查询语文成绩大于英语成绩的同学
  select id, name, chinese, english from exam_result where chinese > english;
  

• 查询总分在 200 分以下的同学
  select name, chinese + math + english from exam_result where (chinese + math + english) > 200;
  

And 与 Or

例如：

• 查询语文成绩大于80分，且英语成绩大于80分的同学
  select name, chinese,english from exam_result where (chinese > 80) and (english > 80);
  
• 查询语文成绩大于80分，或英语成绩大于80分的同学
  select name, chinese,english from exam_result where (chinese > 80) or (english > 80);
  
• 观察and 和 or 的优先级
  select * from exam_result where chinese > 80 or math > 70 and english > 70;
  
  
  select * from exam_result where (chinese > 80 or math > 70) and english > 70;
  

可以and 的优先级大于 or

范围查询

• between...and...
  例如：查询语文成绩在[80, 90]的同学
  select id, name, chinese from exam_result where chinese between 80 and 90;
  

• in...
  例如：查询数学成绩是 58 或者 59 或者 98 的同学
  select id, name, math from exam_result where math in(58, 59, 98);
  

• 模糊查询：like
  例如：
  查询姓孙的学生(%代表任意多个字符)
  select id, name from exam_result where name like '孙%';
  

  
  查询姓孙并且名字只有一个字符的学生（_代表一个字符)
  select id, name from exam_result where name like '孙_';
  

• null的查询：is (not) null
  例如：
  查询名字不是null的同学
  select name from exam_result where name is not null;
  

  
  查询名字是空的同学
  select name from exam_result where name is null;
  

分页查询(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;

例如：按id分页，每页有三条记录
select * from exam_result order by id limit 3;

修改(Update)

语法

UPDATE table_name SET column = expr [, column = expr ...]
 [WHERE ...] [ORDER BY ...] [LIMIT ...]

例如：将孙悟空同学的数学成绩改为80分
update exam_result set math = 80 where name = '孙悟空';

删除(Delete)

语法：

DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

例如：
删除孙悟空同学的考试成绩
delete from exam_result where name = '孙悟空';

重点内容总结

新增：

• 单行插入
  insert into 表(字段1, ..., 字段N) values (value1, ..., value N);
• 多行插入
  insert into 表(字段1, ..., 字段N) values
  (value1, ...),
  (value2, ...),
  (value3, ...);

查询：

• 全列查询
  select * from 表
• 指定列查询
  select 字段1,字段2... from 表
• 查询表达式字段
  select 字段1+100,字段2+字段3 from 表
• 别名
  select 字段1 别名1, 字段2 别名2 from 表
• 去重DISTINCT
  select distinct 字段 from 表
• 排序ORDER BY
  select * from 表 order by 排序字段
• 条件查询WHERE：
  比较运算符
  BETWEEN ... AND ...
  IN
  IS NULL
  LIKE
  AND
  OR
  NOT
  select * from 表 where 条件

修改：

update 表 set 字段1=value1, 字段2=value2... where 条件

删除：

delete from 表 where 条件