MySQL表的增删改查(基础)

1. CRUD

• 注释：在SQL中可以使用“–空格+描述”来表示注释说明
• CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

2. 新增（ Create）

2.1 全列插入

insert into [表名] values (对应的列的数据);

注:插入的字段的数目和类型需要和表结构中要求的一致

2.2 指定列插入

insert into [表名] (若干个指定列) values (对应的列的数据);

根据需要只插入其中的某几列

2.3 多行插入

insert into [表名] values (对应的列的数据) , (对应的列的数据)......;

3. 查询（ Retrieve）

3.1 全列查找

select * from [表名];

注 : *是一个通配符,意思是把所有的列都查找出来.

3.2 指定列查找

select [列名] from [表名];

3.3 查询字段为表达式

3.3.1 例 : 查找所有同学的总成绩

3.3.2 例 : 查找所有同学的成绩的语文成绩,并在基础上 +10分

3.4 查询字段指定别名.

select [列名] as [别名] from [表名];

注: as可以省略.

3.5 去重

select distinc [若干个列名] from [表名];

3.6 排序

order by 指定针对哪个列进行排序
注: 默认为升序 . ASC 为升序（从小到大） DESC 为降序（从大到小）

• NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面
• NULL 加上所有值都等于 NULL

3.6.1 查找同学们的信息并按照数学成绩升序排序

select * from [表名] order by [列名] (asc 或 desc);

3.6.2 查找同学们的信息并按照数学成绩降序排序

3.6.3 查找学生信息并按照总成绩降序排序

select name,chinese + math + english from exam_result order by chinese + math + english desc;

3.6.4 查找学生信息并按照总成绩降序排序(使用别名)

select name,chinese + math + english as total from exam_result order by total desc;

3.6.5 按照多个列来排序

先把所有同学信息按照语文降序排序,再按照数学降序排序,再按照英语降序排序.

select * from exam_result order by chinese desc,math desc,english desc;

3.7 条件查询(重要)

比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于， NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配， [a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。 % 表示任意多个（ 包括 0 个）任意字符； _ 表示任意一个字符

逻辑运算符

算符	说明
AND逻辑与	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR 逻辑或	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT逻辑取反	条件为 TRUE(1)，结果为 FALSE(0)

3.7.1 查询数据中chinese 为null 的记录.

错误的写法 null = null

正确写法:

select * from exam_result where chinese <=> null;

select * from exam_result where chinese is null;

3.7.2 查找英语成绩不及格的同学信息

select * from exam_result where english < 60;

3.7.3 查找语文比英语好的同学信息

select * from exam_result where chinese > english ;

3.7.4 查找总分在200分以下的同学

where 中不能使用别名

select name,chinese + math + english as total from exam_result where chinese + math + english > 200;

3.7.5 查找语文成绩大于80并且英语成绩也大于80的同学

select * from exam_result where chinese > 80 and english > 80;

3.7.6 查找语文成绩大于80或者英语大于80的同学

select * from exam_result where chinese > 80 or english > 80;

注: AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

3.7.7 查找语文成绩在[80,90]之间的同学

select * from exam_result where chinese between 80 and 90;

select * from exam_result where chinese >= 80 and chinese <= 90;

3.7.8 查找数学成绩是 45.0 或者 60.0 的

select * from exam_result where math in (45.0,60.0);

select * from exam_result where math = 45.0 or math = 60.0;

3.7.9 查找所有姓孙的同学的成绩

select * from exam_result where name like '孙%';

select * from exam_result where name like '孙_';

3.7.10 查找所有同学中语文成绩 9 开头的.

select * from exam_result where chinese like '9%';

3.8 分页查找

-- 从 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;

3.8.1 查找同学信息中总分最高的前3名.

select name,chinese + math + english as total from exam_result order by total desc limit 3;

3.8.2 查找同学信息中总分最高的 4 ~ 6

select name,chinese + math + english as total from exam_result order by total desc limit 3 offset 3;

3.8.3 如果limit 后面的数字太大,超出了记录的数目,返回的结果不会有任何的错误

select name,chinese + math + english as total from exam_result order by total desc limit 10 offset 3;

3.8.4 如果 offset 过大,得到的结果可能是一个空的

select name,chinese + math + english as total from exam_result order by total desc limit 3 offset 100;

4. 修改（ Update）

update [表名] set [列名] = [修改的值], [列名] = [修改的值] where 子句;

4.1 把孙悟空的数学成绩改成80

update exam_result set math = 80 where name = '孙悟空';

4.2 把曹孟德数学成绩改成50分,语文成绩改成95分.

update exam_result set math = 50,chinese = 95 where name = '曹孟德';

4.3 所有同学的语文成绩都 -10分.

mysql> update exam_result set chinese = chinese - 10 ;

4.4 将总成绩倒数3名的同学的数学成绩+10分.

mysql> update exam_result set math = math + 10 order by chinese + math + english asc limit 3;

5. 删除（ Delete）

delete from [表名] where [筛选条件];