Mysql——数据操作

呼呼菜菜
430 阅读6分钟

上一篇：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）：
idname
1一班
2二班
3三班
  • 学生表（student）：
idnameclass_id
1张三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

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

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

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

下一篇：Mysql——存储引擎

avatar
文章
阅读
粉丝