MySQL表操作及查询功能详解

目录

• 一.表操作
• 1.插入数据
• 2.更新数据
• 二.查询
• 1.查找数据
• （1）整体查找
• （2）定向查找
• 2.排序数据
• 3.筛选数据
• 4.更新数据
• 5.删除数据
• 6.截断表
• 7.聚合函数
• 8.数据分组

一.表操作

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;
+---+-------javascript-+----+
 | id | name | 10 |
+---+--------+----+
| 1 |   张三  | 10 |
| 2 |   李四  | 10 |
| 3 |   王五  | 10 |
| 4 |   赵六  | 10 |

如果表达式与字段无关，则会生成一列表达式数据。

SELECT id, name, english + 10 FROM exam_result;
+---+---------+-----------------+
| idphp | 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 条结果，比第二种用www.devze.com法更明确，建议使用
SELECT 列名称... FROM 表名称 LIMIT n OFFSET s;

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

4.更新数据

数据更新必须配合where使用，因为你不可能将一整列的数据全部更新，一般都是对特定行的数据进行更新。

UPDATE 表名称 SET 列名称 = 新数据 [, column = expr ...] [WHERE ..android.] [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_INCREMEN同样清除，从AUTO_INCREMEN的初始值从头开始增长。

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 过滤条件;

到此这篇关于MySQL表操作及查询功能详解的文章就介绍到这了,更多相关mysql表查询内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！