导学

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

修改数据操作

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

添加记录

插入单条数据
语法：

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

注意：
指明字段新增可以不按照顺序写，但是值和字段需要一一对应
自增字段可以不用指定

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

    在实际的开发中，一般都要给定字段进行插入，不建议省略fields的定义。 SQL语句对valus没有限制，但是单条SQL语句，不能超过4M，超过MySQL无法解析SQL语句

批量插入数据
语法：

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

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

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

示例：

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

修改记录

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

语法：

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

示例：
image.png

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

删除记录

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

  1. 删除记录 ：不加条件，执行删表操作
  2. DELETE FROM tbl_name [WHERE条件]
  3. -- DELETE 清空数据的时候不会重置AUTO_INCREMENT的值

补充：

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

检索数据操作

完整语法：

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

查询表中所有的记录

语法：

  1. SELECT * FROM table_name [WHERE 条件]

示例：

  1. select * from user

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

指定字段信息查询

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

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

示例：

  1. select id,user_name,password from user

起别名查询

image.png
语法：

  1. 给字段起别名：
  2. SELECT 字段名称[AS] 别名名称,... FROM db_name.tb_name;
  3. 给数据表起别名：
  4. SELECT 字段名称,... FROM tb_name AS 别名;

避免重复数据的查询

使用关键字：DISTINCT
image.png

例如要查询有在校生的班级编号，就可以使用 DISTINCT 关键字，查询 stu_info 表。 在使用 DISTINCT 查询时要注意：其效率较低的。遇上了带索引的字段不用考虑效率问题

  1. SELECT DISTINCT clazz FROM stu_info

查询时进行数学运算

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

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

image.png

where筛选条件

where不仅仅可以和select语句使用，也可以和update，delete语句共同使用
在where后面，我们可以跟上所要设置的查询条件。那么如何描述查询条件呢？

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

    关系运算符和逻辑运算符

    关系运算符：>、 >=、 <、 <=、=、<=>；
    逻辑运算符：AND(&&)、OR(||)、NOT(!) — 不建议使用符号，正常使用英文
    not 在对单个表达式取反时可以不加括号，如果无法确定，就都加上括号
    示例： ```sql 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)

对于不为null值的判断： select from user where !(user_name<=>null) select from user where not user_name<=>null

  1. <a name="emOD5"></a>
  2. ### BETWEEN ADN
  3. > 一般用在对数值或者日期的区间判断条件中，而且是可以被替代的。
  4. > 包含区间边界
  6. ```sql
  7. SELECT * FROM stu_info WHERE age BETWEEN 16 AND 20
  8. SELECT * FROM stu_info WHERE age >= 16 AND age <=20
  9. -- 使用 NOT 取反
  10. SELECT * FROM stu_info WHERE NOT (age >= 16 AND age <=20)
  12. SELECT * FROM stu_info WHERE 16<= age <=20
  13. --永等式，不报错，逻辑上有问题。只要满足其中一个条件，就会把满足条件的全部找出来

IS NULL

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

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

IS NOT NULL

使用非空判断是要注意

  1. SELECT * FROM stu_info WHERE name IS NOT NULL
  2. SELECT * FROM stu_info WHERE NOT (name IS NULL)

IN

条件在某些离散的数据范围内 in类似设置一个集合，要求数据必须在该集合内

  1. SELECT * FROM stu_info WHERE clazz IN ('C1', 'C2');
  2. Oracle中（）内不超过1000个
  3. MySQL中（）内不超过4M
  4. -- 替代方案
  5. SELECT * FROM stu_info WHERE clazz = 'C1' OR clazz = 'C2'

LIKE

模糊查询：用的较多，一般用到的是全匹配%搜索字%，首部匹配搜索字%，尾部匹配%搜索字 其他还有单个字匹配_ 第二个子匹配 _ 搜索字%

  1. SELECT * FROM stu_info WHERE name LIKE '李_强';
  2. SELECT * FROM stu_info WHERE name LIKE '李%';
  3. SELECT * FROM stu_info WHERE name LIKE '%李%';

分组

SQL语句每执行一步，都会生成一张虚拟的表
group by 后可以有多个字段，分组时要求完全一致
在SQL中要求，group by 需要和聚合函数配合使用，否则只会展示每个虚拟子表的第一行数据
如果只要求展示group by 后面的字段，则不需要使用聚合函数
因为能够出现在同一张虚拟表中的数据，必定分组条件中的字段值完全一致
GROUP BY分组：把值相同放到一个组中，最终查询出的结果只会显示组中一条记录。
示例：

  1. SELECT id,username,age,sex FROM user

只会分别显示男 女2组里的各一条信息代表。

image.png
分组拼接信息-group_concat()

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

聚合函数

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

示例：

  1. SELECT 
  2. COUNT(*) AS totalUsers,
  3. SUM(age) AS sum_age,
  4. MAX(age) AS max_age,
  5. MIN(age) AS min_age,
  6. AVG(age) AS avg_age,
  7. FROM user

注意：需要注意count()与count(字段)的区别。区别在于对null值的统计
如果写的是count(字段名称)，字段中的值为null，不统计进来
写count()会统计null值

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

where中不能出现聚合数据，having中不能出现表中的普通字段
我们使用where对分组前的数据进行筛选，使用having对分组后的数据进行筛选。
示例：

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

排序

数据的排序方式：顺序 ASC（从小到大）、逆序 DESC（从大到小）。 在排序中是可以多字段排序的，即会有第一排序条件和第二、三…次排序条件 多个条件，优先满足前面一个条件

  1. SELECT * FROM stu_info ORDER BY clazz ASC, code DESC

随机顺序ORDER BY RAND();

限制查询数量

image.png
分页原理：说明这是MySQL中的分页原理，在Oracle和SQL server中没有limit关键字
分析：需要设置每页显示多少条数据（num），总共有多少数据（allNum），得到总共有多少页（allPage）
还需要得知当前的页数，以得到limit的第一个参数
需要计算limit的一个参数
使用 LIMIT 关键字限制查询的条数，后面跟两个参数 x，y，第一个参数是从第几条开始，第二个是一共显示多少条记录

  1. SELECT * FROM stu_info ORDER BY clazz ASC, code DESC LIMIT 9, 3
  2. -- 显示 page 页，每页显示 num 条记录
  3. -- page = 2; num = 3;
  4. -- x = (page - 1) * num
  5. -- y = num

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

  1. -- 更新user1表中的前3条记录，将age加5
  2. UPDATE user1 SET age=age+5 LIMIT 3;
  3. -- 将user1表中id字段降序排列，更新前三条记录，将age减10
  4. UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;
  5. -- 删除user1表中前三条记录
  6. DELETE FROM user1 LIMIT 3;
  7. -- 删除user1表中id字段降序排列的前三条记录
  8. DELETE FROM user1 ORDER BY id DESC LIMIT 3;

mysql关键字应用顺序
1、操作关键字 select delete update（后两个不可和group by having一起使用）
2、字段 3、表名
4、where 需要跟在表名后，配合其他条件使用
5、group by having
6、order by 在有分组的情况下，应该是对分组后的聚合函数进行排序
7、limit

查询顺序解释

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

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

执行顺序

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

    高级查询

    针对多张表的联合查询
    image.png

    表之间的关系

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

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

    自然连接查询是用的比较少的，同学们只需要了解即可。
    在不指定匹配条件的情况下，查询的多张表中所有数据相互组合
    查询效率较低
    示例：
    1. SELECT t.id, t.name, t.age, ts.depName
    2. FROM teachar t, teach_species ts
    要注意的是，如果不加条件，直接查询，会把两个表进行笛卡尔积的操作，查询出来的数据是有问题。
    1. SELECT t.id, t.name, t.age, ts.depName
    2. FROM teachar t, teach_species ts where t.dep_id=ts.id

    内连接查询

    内连接查询和笛卡尔积查询一样，获取到的是两个表中有关联的交集部分

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

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

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

外连接查询

外连接查询会设置一个表为主表，能将主表中的数据完全查询出来
如果主表没有与子表对应的数据，则子表的数据将显示为null
左外连接：

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

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

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

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

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

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

特殊查询

子查询

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

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

示例：
利用in的子查询，即子查询可以有多个值
image.png

  1. 1、由IN引发的子查询
  2. SELECT * FROM emp WHERE depId IN (SELECT id FROM dep);
  3. 2、由NOT IN 引发的子查询
  4. SELECT * FROM emp WHERE depId NOT IN (SELECT id FROM dep);
  5. 3、由比较运算符引发的子查询
  6. SELECT id,username,score,FROM stu WHERE score>=(SELECT score FROM level WHERE id=1);
  7. 4、由EXISTS引发的子查询
  8. EXISTS后面的SELECT语句返回一个布尔类型，若后面的值不存在前面的语句不执行，若存在，则执行（EXISTS 后面的语句返回的BOOLEAN值,是是否执行前面语句的条件）
  9. SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);

带有 ANY SOME ALL的子查询

3.MySQL数据的管理 - 图11
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
示例：

  1. 带有ANY SOME ALL 关键字查询
  2. SELECT * FROM stu
  3. WHERE score>=ANY(SELECT score FROM level);
  4. ANY跟SOME意义相同
  5. SELECT * FROM stu
  6. WHERE score>=SOME(SELECT score FROM level);
  7. SELECT * FROM stu
  8. WHERE score>=ALL(SELECT score FROM level);

联合查询

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

  1. UNION：
  2. SELECT 字段名称,... FROM tb_name1 UNION SELECT 字段名称,... FROM tb_name2；会去掉两个表中的重复项
  3. UNION ALL：
  4. SELECT 字段名称,... FROM tb_name1 UNION ALL SELECT 字段名称,... FROM tb_name2；直接合并两个表中的记录