MySQL之表的操作与数据检索

文章标题：

MySQL中表的相关操作与数据获取

一.表操作

在MySQL的操作里，存在一些特定的关键字，不管是大写还是小写形式都是被允许使用的。

1.插入数据

INSERT [INTO] table_name (列名称...) VALUES (列数据...), (列数据...);

方括号内的内容代表可选情况，当插入数据时，若不指定要插入的列，那就默认是插入所有列的数据，并且插入数据的顺序得和列的顺序保持一致。要是插入单行数据，只需要一个括号；要是想多行插入，就可以同时添加多个括号。

2.更新数据

另外，要是因为主键或者唯一键对应的值已经存在从而使得插入操作失败，这时候就可以进行数据更新：

INSERT INTO 表名称 (列名称...) VALUES (列数据...)ON DUPLICATE KEY UPDATE
列名称=新数据...;

除了这条语句之外，还有一条名为替换的语句也能解决主键或唯一键冲突的问题：

REPLACE INTO 表名称 (列名称...) VALUES (列数据...);

要是没有发生冲突，就相当于直接插入；要是发生了冲突，就会删除原有的数据，然后重新插入新的数据。

二.查询

1.查找数据

（1）整体查找

全部列查询

select * from 表名称;

指定列查询

select 列名称... from 表名称;

查询字段表达式

select 列名称... 表达式 from 表名称;

例如：

SELECT id, name, 10 FROM exam_result;

+---+--------+----+

| id | name | 10 |

+---+--------+----+

| 1 | 张三 | 10 |

| 2 | 李四 | 10 |

| 3 | 王五 | 10 |

| 4 | 赵六 | 10 |

如果表达式和字段没有关系，就会生成一列表达式的数据。

SELECT id, name, english + 10 FROM exam_result;

+---+---------+-----------------+

| id | name | english + 10|

+---+---------+-----------------+

**| 1 | 张三 | 66 | **

**| 2 | 李四 | 87 | **

**| 3 | 王五 | 100 | **

**| 4 | 赵六 | 77 | **

表达式和字段有关联时，比如english + 10，就会生成将学生英语成绩加10分的列并进行显示。

SELECT id, name, chinese + math + english FROM exam_result;

还有像上面这样的表达式，用来显示学生语文、数学、英语成绩的总和。

SELECT id, name, chinese + math + english 总分 FROM exam_result;

还能够给表达式指定别名，也就是在表达式后面添加别名。

SELECT DISTINCT 字段 FROM 表名称;

通过DISTINCT，可以把查询结果进行去重处理。

（2）定向查找

前面所说的查找操作，都是针对整个列的数据来进行的，要是想要查找列中特定的数据，就可以借助WHERE条件来实现。

比较运算符：

> , >=, <, <= ：分别表示大于，大于等于，小于，小于等于

= ：表示等于，但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)

where基本使用

select 字段... from 表名称 where 条件;

比如，查找一张成绩表中英语不及格的学生名单：

SELECT name, english FROM exam_result WHERE english < 60;

多个条件可以通过逻辑运算符相互组合。

要是现在要查找一个同学，但是不记得他的全名，只记得他的姓为孙，该怎么找到他呢？

SELECT name FROM exam_result WHERE name LIKE ' 孙%';

SELECT name FROM exam_result WHERE name LIKE ' 孙_';

通过LIKE条件，其中%表示该同学的名字可以是任意数量的字符，_表示该同学的名字只有一个字。

需要注意的是，在MySQL中，null、0、和空字符' '，这三者是互不相关的，在执行相关查询时要留意。

2.排序数据

对数据进行排序一般是在找到相应数据之后进行的操作。

select 列名称... from 表名称 ORDER BY 列名称 排序方式;

-- ASC为升序（从小到大）

-- DESC为降序（从大到小）

-- 如果不指明排序方式，则默认为ASC

当数据中有null时，null会被视为比任何值都小。

另外，当出现多字段排序时，比如：查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示，查询后的结果不是按照上述排序方式单独对科目进行排序，而是按照书写的优先级，先将整体数据按照数学降序排序，如果数学成绩相等，再将其后的英语成绩按照升序排序，如果英语成绩也相等，则最后排序语文成绩。

3.筛选数据

当数据量比较大的时候，直接进行查询可能会带来不少不便，比如说直接就展示到数据的最后几行了。

因此可以通过 LIMIT 按行筛选数据的方式来查询对应行的数据信息：

起始下标为 0

**从 0 开始，筛选 n 条结果 **

SELECT 列名称... FROM 表名称 LIMIT n;

**从 s 开始，筛选 n 条结果 **

SELECT 列名称... FROM 表名称 LIMIT s, n;

**从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用 **

SELECT 列名称... FROM 表名称 LIMIT n OFFSET s;

所以当我们对未知表进行查询时，最好加上一条LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

4.更新数据

数据更新的时候必须配合where来使用，因为没办法把一整列的数据全部更新，通常都是针对特定行的数据进行更新。

UPDATE 表名称 SET 列名称 = 新数据 [, column = expr ...] [WHERE ...] [ORDER BY ...]
[LIMIT ...]

可以同时对某一行的多个数据进行更新，也可以对更新后的数据进行排序筛选等操作。

需要注意的是，当需要对某数据进行加减运算时，在MySQL中不支持"+="等操作，必须采用"新数据 = 原数据 + 改变值"的方式。

5.删除数据

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

删除数据的时候，可以用where来指定要删除的数据，要是不使用where，那就默认是删除整张表的所有数据。

需要注意的是，删除数据不会影响表的各种结构定义，仅仅是删除插入的数据，像表字段的类型，以及表的各种约束条件等都不会受到影响。

特别要注意的是，delete删除数据，不会对自增长AUTO_INCREMENT产生影响，比如说原本自增长已经到了5，此时删除全部数据，在进行插入，自增长依然会为6。

6.截断表

TRUNCATE [TABLE] table_name;

截断表使用truncate，也是清空表中的所有数据，和delete不一样的是，它会把自增长的AUTO_INCREMENT也清除掉，重新从初始值开始增长。

7.聚合函数

COUNT([DISTINCT] expr) ：用于返回查询到的数据的数量

SUM([DISTINCT] expr) ：用于返回查询到的数据的总和，不是数字时没有意义

AVG([DISTINCT] expr) ：用于返回查询到的数据的平均值，不是数字时没有意义

MAX([DISTINCT] expr) ：用于返回查询到的数据的最大值，不是数字时没有意义

MIN([DISTINCT] expr) ：用于返回查询到的数据的最小值，不是数字时没有意义

聚合函数可以选择对去重后的数据进行操作，通常和数据查找一起使用，比如查找某表总共有多少行：

SELECT COUNT(*) FROM 表名称;

使用*做统计，不受NULL影响。

8.数据分组

在select语句中使用group by子句能够对指定的列进行分组查询。

select 列名称... from table group by 列名称;

所谓分组查询，就是对表中某一列中的相同数据进行组合，例如，有一张完整的学生表数据，其中包含名称为班级号的字段，现在要查询每个班级分别有多少学生，就可以通过分组查询：

select class_id count(*) from student group by class_id;

having和group by配合使用，对group by的结果进行过滤，作用类似于where：

select 列名称... from table group by 列名称 having 过滤条件;