mysql crud语法,Mysql——数据操作

增删改查(CRUD)

插入数据(Create)

语法：INSERT INTO 表名 (字段1, 字段2,...字段N) VALUES (值1, 值2,...值N);

例子：INSERT INTO student ('id', 'name', 'age') VALUES ('1', '张三', '17');

查询数据(Retrieve)

语法：SELECT 列1,列2 FROM 表名 WHERE 条件;

例子：SELECT name, age FROM student WHERE id = 1;

更新数据(Update)

语法：UPDATE 表名 SET 列1=值1, 列2=值2 WHERE 条件

例子：UPDATE student SET name='李四', age=27 WHERE id=1

删除数据(Delete)

语法：DELETE FROM 表名 WHERE 条件

例子：DELETE FROM student WHERE id = 1;

WHERE

语法：FROM 表名 WHERE 条件1 [AND 或者 OR] 条件2

例子1：SELECT * FROM student WHERE name = '李四' AND age = 27;

例子2：SELECT * FROM student WHERE name = '李四' OR age = 17;

运算符

运算符说明=等于

!=或者<>不等于

<=小于或等于

>大于

>=大于或等于

BETWEEN … AND …在某个范围之内

NOT BETWEEN …AND …不在某个范围之内

IN(项1,项2,…)在指定项内

NOT IN(项1,项2,…)不在指定项内

LIKE模糊匹配，与%配合使用，'%四'表示后缀匹配，'李%'表示前缀匹配

NOT LIKE与LIKE相反

IS NULL判断是否为空

IS NOT NULL判断是否不为空

AND、OR逻辑运算符，分别表示并且、或，用于多个逻辑连接。优先级：AND > OR

%与LIKE配合使用

聚合函数

聚合函数aggregation function又称为组函数，主要用于分组统计。

函数接收一个参数(列或者表达式)；

统计结果中默认忽略字段为NULL的记录，要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换；

不允许出现嵌套，count(min(列1))；

一个SELECT中可出现多个聚合函数；

如果没有GROUP BY，那么所有返回记录即为一组。

聚合函数|用法|说明

---|:--:---|:

count|count(* 或者 列 或者 表达式)|求返回结果的记录数

sum|sum(列 或者 表达式)|求返回结果某列(或者表达式)的和

avg|avg(列 或者 表达式)|求返回结果某列(或者表达式)的平均值

max|max(列 或者 表达式)|求返回结果某列(或者表达式)的最大值

min|min(列 或者 表达式)|求返回结果某列(或者表达式)的最小值

GROUP BY

GROUP BY是分组查询, 一般GROUP BY是和聚合函数配合使用，select后面的所有列中(排除使用聚合函数的列)，必须是GROUP BY过的列，GROUP BY后面的字段可以使用排序[ASC或DESC](从MySQL8.0开始，MySQL不再支持GROUP BY的隐式或显示排序，这里就不举例了)。

语法：SELECT 列1, 列2, ..., 聚合函数(任意列) FROM 表名 WHERE 分组前过滤条件 GROUP BY 列1, 列2, ... HAVING 分组后过滤条件

例子1：统计一班每个年龄段的学生的平均身高

SELECT age, avg(height) FROM student WHERE class_name = '一班' GROUP BY age;

例子2：统计一班哪些年龄段的学生的平均身高大于170

SELECT age FROM student WHERE class_name = '一班' GROUP BY age HAVING avg(height) > 170;

考虑一下WHERE与HAVING的区别？

WHERE：分组之前过滤数据，条件中不能包含聚合函数；

Having：分组后过滤，条件中可以包含聚合函数；

ORDER BY

语法：SELECT 列1, 列2, ... FROM 表名 ORDER BY 列1 [ASC|DESC], 列2 [ASC|DESC], ...

例子1：查找一班所有学生，先按年龄降序排序，同一年龄段的学生再按身高升序排序；

SELECT * FROM student WHERE class_name = '一班' ORDER BY age DESC, height ASC;

GROUP BY与ORDER BY一起使用：

例子2：查找一班每个年龄段中，不同性别学生的平均身高，按照年龄升序排序

SELECT age, sex, avg(height) FROM student WHERE class_name = '一班' GROUP BY age, sex ORDER BY age ASC;

LIMIT

语法：SELECT * FROM 表名 LIMIT offset, count;

例子：查找一班学号前三的学生；

SELECT * FROM student WHERE class_name = '一班' ORDER BY no ASC LIMIT 0, 3;

offset: 是查询结果的偏移量，起始点是0；

count：指定返回条数。

关键字优先级

WHERE -> GROUP BY ... HAVING -> ORDER BY -> LIMIT

UNION

语法：SELECT * FROM 表1 [WHERE 条件] UNION [ALL | DISTINCT(默认)] SELECT * FROM 表2 [WHERE 条件];

例子：查找一班身高180或者身高170的学生；

SELECT * FROM student WHERE height = 180 UNION SELECT * FROM student WHERE height = 170;

表连接

假设有两个表，分别为班级班级表，学生表。

班级表(class)：

idname1一班

2二班

3三班

学生表(student)：

idnameclass_id1张三1

2李四1

3王五2

4小明3

5小东4

连接类型

内连接(INNER JOIN)：左表及右表符合连接条件的记录，取两张表的交集；

例子：找出一班所有学生

SELECT s.* FROM student s INNER JOIN class c ON s.class_id = c.id WHERE s.class_id = 1

等价于

SELECT s.* FROM student s JOIN class c ON s.class_id = c.id WHERE s.class_id = 1

等价于

SELECT s.* FROM student s, class c WHERE s.class_id = c.id AND s.class_id = 1

左外连接(LEFT JOIN)：显示左表的全部记录及右表符合连接条件的记录，右表不符合可以放空；

例子：找出所有学生，及对应的班级信息

SELECT * FROM student s LEFT JOIN class c ON s.class_id = c.id

右外连接(RIGHT JOIN)：与左表相反；

例子：找出所有学生，及对应的班级信息

SELECT c.*, s.* FROM class c RIGHT JOIN student s ON c.id = s.class_id

笛卡尔积

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}，因此笛卡尔积的元素个数 = A的元素个数 * B的元素个数。

在sql中，应避免使用笛卡尔积，笛卡尔积的写法：SELECT * FROM 表1 连接 表2

解决方案：在ON或者WHERE加入等值条件

SELECT * FROM student s JOIN class c ON s.class_id = c.id

子查询

如果从子查询位置来划分，我们可以把它分为：

SELECT 之后：

语法：SELECT *, 子查询 FROM 表

例子：找出所有学生，及对应的班级信息

SELECT s.*, (SELECT c.* FROM class c WHERE c.id = s.class_id) FROM student s

FROM 之后：

语法：SELECT * FROM 表, 子查询

例子：找出一班所有学生

SELECT s.* FROM student s INNER JOIN (SELECT c.* FROM class c WHERE c.name = '一班') tmp on s.class_id = c.id

WHERE(或者HAVING)之后：

语法：SELECT * FROM 表 WHERE 子查询

例子：找出一班所有学生

SELECT s.* FROM student s WHERE s.class_id IN (SELECT c.id FROM class c WHERE c.name = '一班')

如果从子查询执行顺序，我们可以把它分为：

嵌套子查询，不依赖于外部查询，执行过程(子查询 -> 外部查询)：

例如：SELECT s.* FROM student s INNER JOIN (SELECT c.* FROM class c WHERE c.name = '一班') tmp on s.class_id = c.id

执行子查询，传递给外部查询；

执行外部查询，并显示整个结果。

相关子查询，需要依赖于外部查询，执行过程(外部查询 -> 子查询 -> 外部查询)：

例如：SELECT s.*, (SELECT c.* FROM class c WHERE c.id = s.class_id) FROM student s

从外部查询中取出某些值，将值传给子查询；

执行子查询，得到子查询操作的值；

外部查询处理子查询返回的值，并显示整个结果。