MySQL的数据管理 学习

思维导图

导学

在之前的学习中，我们已经会建库了，也会建表了，还了解了一些相关的数据库知识，那么我们今天进入MySQL的重中之重，对数据的管理。

修改数据操作

针对于数据的操作，其实我们可以分为两种，一种是针对数据的查询，还有就是针对于数据的修改。
那么，在数据的修改中，又分为数据增加操作，数据删除操作和数据修改操作。

添加记录

语法：

INSERT INTO table_name[(field1, field2,...)] VALUES(value1, value2, value3)
或
INSERT tbl_name SET 字段名称=值,字段名称=值,...;

注意：

1. field可以省略不写，但是如果不写，实际会对所有字段设置数据。而且后面VALUES中的顺序是要和你定义表时的顺序保持一致的；
2. field如果写了，那么field里面的顺序要和values后面的数值保持一致。

在实际的开发中，一般都要给定字段进行插入，不建议省略fields的定义。

批量插入数据
语法：

INSERT tbl_name (字段名称,...) VALUES (值,...),
(值,...),
(值,...);

或者利用查询，将查询结果插入到表中
语法：

INSERT INTO table_name(field1, field2,...) SELECT field1, field2, ... FROM new_talbe

示例：

INSERT INTO s_v1(code, name, birthday)
SELECT code, name, birthday FROM s

修改记录

在程序开发中，修改数据一般是非常关键的操作，所以，只要是写更新数据的语句的时候，一定要想好条件。

语法：

UPDATE table_name SET field1=value1, field2=value2, ... WHERE condition

示例：

UPDATE s SET code='ss6',birthday='1990-10-10' WHERE name='zhangsan'

删除记录

同样的和修改数据操作一样，在删除数据的时候，一定要加上删除数据的条件。

删除记录 ：

DELETE FROM tbl_name [WHERE条件]
– DELETE 清空数据的时候不会重置AUTO_INCREMENT的值

补充：

TRUNCATE [TABLE] tbl_name;
-- 清空表中所有记录，会重置AUTO_INCREMENT的值

检索数据操作

完整语法：

SELECT select_expr,... FROM tb_name
[WHERE 条件]
[GROP BY{col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr}[ASC|DESC]]
[LIMIT 限制结果采集的显示条数]

查询表中所有的记录

语法：

SELECT * FROM table_name [WHERE 条件]

示例：

select * from user

此处遍历出来的数据的顺序是创建表中字段的顺序。

指定字段信息查询

如果某张表的字段较多，但是在具体的某个场景中，只需要用到部分字段的信息，可以使用该查询。
语法：

SELECT field1, field2,... FROM table_name [WHERE 条件]

示例：

select id,user_name,password from user

起别名查询

语法：

给字段起别名：

SELECT 字段名称[AS] 别名名称,… FROM db_name.tb_name;

给数据表起别名：

SELECT 字段名称,… FROM tb_name AS 别名;

避免重复数据的查询

使用关键字：DISTINCT

例如要查询有在校生的班级编号，就可以使用 DISTINCT 关键字，查询 stu_info 表。

在使用 DISTINCT 查询时要注意：其效率较低的。

SELECT DISTINCT clazz FROM stu_info

查询时进行数学运算

数学运算的数据结果处理：+ - * / %
示例：

SELECT name, price AS '人民币', price/6 AS '美元' FROM t_menu

where筛选条件

在where后面，我们可以跟上所要设置的查询条件。那么如何描述查询条件呢？

• 带关系运算符和逻辑运算符的表达式；
• 带 BETWEEN AND 关键字的条件查询；
• 带 IS NULL 关键字的条件查询；
• 带 IN 关键字的条件查询；
• 带 LIKE 关键字的条件查询。

关系运算符和逻辑运算符

关系运算符：>、 >=、 <、 <=、=、<=>；

逻辑运算符：AND(&&)、OR(||)、NOT(!)

示例：

SELECT * FROM stu_info WHERE age >= 18 AND clazz = 'C1' AND code = '01'

对于null值的判断：
select * from user where user_name=null;(x) 不可以使用=进行null值的判断
select * from user where user_name<=>null (ok)

BETWEEN ADN

一般用在对数值或者日期的区间判断条件中，而且是可以被替代的。

SELECT * FROM stu_info WHERE age BETWEEN 16 AND 20
SELECT * FROM stu_info WHERE age >= 16 AND age <=20
-- 使用 NOT 取反
SELECT * FROM stu_info WHERE NOT (age >= 16 AND age <=20)

IS NULL

判断数据结果集中非空元素，要注意的是：NULL 和 空字符串是两个概念，使用的查询条件不尽相同

SELECT * FROM stu_info WHERE name IS NULL;
SELECT * FROM stu_info WHERE name = ''; -- 判断空字符串

使用非空判断是要注意

SELECT * FROM stu_info WHERE name IS NOT NULL

IN

条件在某些离散的数据范围内

SELECT * FROM stu_info WHERE clazz IN ('C1', 'C2');
-- 替代方案
SELECT * FROM stu_info WHERE clazz = 'C1' OR clazz = 'C2'

LIKE

模糊查询：用的较多，一般用到的是全匹配%搜索字%，尾部匹配搜索字%

其他还有单个字匹配_和首部匹配%搜索字

SELECT * FROM stu_info WHERE name LIKE '李_强';
SELECT * FROM stu_info WHERE name LIKE '李%';
SELECT * FROM stu_info WHERE name LIKE '%李%';

分组

GROUP BY分组：把值相同放到一个组中，最终查询出的结果只会显示组中一条记录。
示例：

SELECT id,username,age,sex FROM user

只会分别显示男 女2组里的各一条信息代表。
分组拼接信息-group_concat()

SELECT GROUP_CONTACT(username),age,sex,addr FROM user

聚合函数

• count 总数
• sum 求和
• avg 平均值
• max 最大值
• min 最小值

示例：

SELECT 
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
FROM user

**注意：**需要注意count(*)与count(字段)的区别。

HAVING对分组后的数据进行筛选

我们使用where对分组前的数据进行筛选，使用having对分组后的数据进行筛选。
示例：

select subject,avg(score) as avg_score from student group by subject having (avg_score < 60);

排序

数据的排序方式：顺序 ASC、逆序 DESC。
在排序中是可以多字段排序的，即会有第一排序条件和第二、三...次排序条件

SELECT * FROM stu_info ORDER BY clazz ASC, code DESC

随机顺序ORDER BY RAND();

限制查询数量

使用 LIMIT 关键字限制查询的条数，后面跟两个参数，第一个参数是从第几条开始，第二个是一共显示多少条记录

SELECT * FROM stu_info ORDER BY clazz ASC, code DESC LIMIT 9, 3

– 显示 page 页，每页显示 num 条记录
– page = 2; num = 3;
– x = (page - 1) * num
– y = num

LIMIT同样也可以用于更新和删除操作，但是使用的时候只能添加一个参数

-- 更新user1表中的前3条记录，将age加5
UPDATE user1 SET age=age+5 LIMIT 3;

– 将user1表中id字段降序排列，更新前三条记录，将age减10
UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;

– 删除user1表中前三条记录
DELETE FROM user1 LIMIT 3;

– 删除user1表中id字段降序排列的前三条记录
DELETE FROM user1 ORDER BY id DESC LIMIT 3;

查询顺序解释

对于一个较完整的 SQL 语句执行的解释

SELECT 
clazz,MAX(age) AS '最大年龄',
COUNT(*) AS '多少人' 
FROM stu_info 
WHERE id > 2
GROUP BY clazz HAVING count(*) > 1
ORDER BY MAX(age) DESC

执行顺序

1. 筛选整个表找那个id > 2的数据；
2. 把筛选出的记录按照clazz字段进行分组；
3. 把分组完的结果，筛选出每组数据总数量 > 1的数据count(*) > 1；
4. 按照分组后的字段进行排序MAX(age) DESC；
5. 按照 SELECT 中要求显示的字段输出结果集。

高级查询

表之间的关系

• 一对多（多对一）
  在多的一方加入一的一方的外键。
• 多对多
  通过一个中间表将两个表之间建立关系。
• 一对一
  在所谓的子表中加入所谓主表的外键，并加上唯一性约束。

笛卡尔积查询-自然连接查询

自然连接查询是用的比较少的，同学们只需要了解即可。
示例：

SELECT t.id, t.name, t.age, ts.depName
FROM teachar t, teach_species ts

要注意的是，如果不加条件，直接查询，会把两个表进行笛卡尔积的操作，查询出来的数据是有问题。

SELECT t.id, t.name, t.age, ts.depName
FROM teachar t, teach_species ts where t.dep_id=ts.id

内连接查询

内连接查询是可以使用自然连接查询替代的，但是效率方面，内连接会高

-- 内连接查询
SELECT s.`code`, s.`name`, c.`name` FROM student s 
INNER JOIN clazz c ON s.clazz_id = c.id

内连接会查询出两个表中符合条件的交集部分

外连接查询

左外连接：

SELECT 字段名称,... FROM tb_name1 LEFT [OUTER] JOIN tb_name2 ON 条件

先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替
右外连接：

SELECT 字段名称,... FROM tb_name1 RIGHT [OUTER] JOIN tb_name2 ON 条件

先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以NULL代替
关键字LEFT左边的那个表为主表，右边的表为从表；
关键字RIGHT右边的那个表为主表，左边的表为从表。
左外连接查询

以左表为主表，左表中的数据都会被显示出来，关联的右表中，如果存在符合条件的数据，那么会被关联出并显示，如果没有，则会显示 NULL。

-- 左外连接
SELECT s.`code`, s.`name`, c.`name` FROM student s 
LEFT JOIN clazz c ON s.clazz_id = c.id

特殊查询

子查询

内层语句的查询结果可以作为外层语句的查询条件。
语法：

子查询（必须要放在括号里）
SELECT 字段名称 FROM tbl_name WHERE col_name =（SELECT col_name FROM tbl_name)

示例：

1、由IN引发的子查询
SELECT * FROM emp WHERE depId IN (SELECT id FROM dep);

2、由NOT IN 引发的子查询
SELECT * FROM emp WHERE depId NOT IN (SELECT id FROM dep);

3、由比较运算符引发的子查询
SELECT id,username,score,FROM stu WHERE score>=(SELECT score FROM level WHERE id=1);

4、由EXISTS引发的子查询
EXISTS后面的SELECT语句返回一个布尔类型，若后面的值不存在前面的语句不执行，若存在，则执行（EXISTS 后面的语句返回的BOOLEAN值,是是否执行前面语句的条件）
SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);

带有 ANY SOME ALL的子查询

ANY 和 SOME 理解为与 或 | 相似
例如:表达式: A > SOME(1,2,3,4,5,6)
只要A的值大于SOME括号中的任意一个值, 整个表达式返回TURE, 否则返回FALSE
ALL 则理解为与 并且 & 相似
例如:表达式: A > ALL(1,2,3,4,5,6)
只有A的值大于ALL括号中的所有值, 整个表达式返回TURE, 否则返回FALSE
示例：

带有ANY SOME ALL 关键字查询

SELECT * FROM stu
WHERE score>=ANY(SELECT score FROM level);
ANY跟SOME意义相同
SELECT * FROM stu
WHERE score>=SOME(SELECT score FROM level);

SELECT * FROM stu
WHERE score>=ALL(SELECT score FROM level);

联合查询

使用的比较少。
通过多个 select 语句查询，将查询的结果合并在一起，并返回一个新的结果集。 union 关键字用于合并数据，默认选择不同的数据，若允许重复的数据，可以使用 union all。 请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。

UNION：
SELECT 字段名称,... FROM tb_name1 UNION SELECT 字段名称,... FROM tb_name2；会去掉两个表中的重复项

UNION ALL：
SELECT 字段名称,… FROM tb_name1 UNION ALL SELECT 字段名称,… FROM tb_name2；直接合并两个表中的记录