mysql 表有数据 修改id自增

| >, >=, <, <= | 大于，大于等于，小于，小于等于 |

| = | 等于，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 ;