免费素材网站大全,选择电商网站建设,物业管理系统排行,wordpress 单页分类目录
一、表的设计
1、一对一
2、一对多
3、多对多
二、新增 三、查询
1、聚合查询
#xff08;1#xff09;聚合函数#xff1a;
#xff08;2#xff09; group by 子句
#xff08;3#xff09;having 2、联合查询
(1)内连接
(2)外连接
(3)自链接 (4)…
目录
一、表的设计
1、一对一
2、一对多
3、多对多
二、新增 三、查询
1、聚合查询
1聚合函数
2 group by 子句
3having 2、联合查询
(1)内连接
(2)外连接
(3)自链接 (4)子查询
(5)合并查询
四、总结 一、表的设计
此处只是讨论一些比较基本的设计表的方法原则
设计数据库的表就需要先把实体和关系梳理清楚
实体从需求场景中提炼出一些 关键性 的名词可以理解成Java中的对象
关系是实体和实体之间的 关联关系
数据库这里的关系只有四种
1、一对一 一个人可以有一个身份证号
一个身份证号只能归一个人所有 这三种方法 都是可以的 2、一对多 一个同学 只能存在于一个班级中
一个班级可以包含多个同学 这种设计方式并不推荐
因为MySQL 这里的类型并没有提供 数组 这样的类型
如果想要这么做就需要按照一定的格式把多个 student 构造成一个字符串
这样的过程比较繁琐也比较低效
但是有些数据库redis支持 数组 这样的类型就可以采取上述的方法来设计了 按照 classId 来查询 student 就能够知道这个表里面的每个班级里包含哪些同学 3、多对多 一个同学 可以选择多门课程来学习
一门课程 也可以被多个同学来选择 往往表示多对多关系需要引入一个关联表 二、新增
把insert 和 select 两个操作合并在一起了
语法
INSERT INTO table_name [(column [, column ...])] SELECT ... 把 select 查询出来的结果数据插入到另一个表中
我们先重新创建两个表 分别为 student 和 student2
并向 student 中插入三条数据 然后再运用上述语法将 student 中的数据转移到 studen2 中 此处 select 查询的记过 得和插入的表能对应列的数目类型约束得匹配 三、查询
1、聚合查询
表达式查询本质上是在用 列 和 列 之间进行运算
还有的时候我们需要进行 行 和 行 之间的运算(聚合查询)
1聚合函数
常见的统计总数、计算平局值等操作可以使用聚合函数来实现常见的聚合函数有 使用示例
-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;-- 统计平均总分
SELECT AVG(chinese math english) 平均总分 FROM exam_result;、-- 返回英语最高分
SELECT MAX(english) FROM exam_result;-- 返回 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math 70;注意在sql 中要求聚合函数和 () 必须是紧紧挨在一起的
sum 是进行求和会把这一列的若干行按照 double 的方式进行累加。会尝试把这一列的每一行的数据先转成 double 2 group by 子句
有的时候希望把数据进行分组来进行计算这个时候就要用到 group by 语句
group by 语句 是指定一个列按照这个列进行分组这一列中数值相同的行会被放到一组
每个分组中都可以使用上述的聚合函数来进行计算
我们重新创建一个员工表并向其中插入几个数据 现在我们想统计每一个岗位的平均薪资此时就可以使用 group by 子句 在分组查询中select 指定的列必须是当前group by 指定的列如果select 想用到其它的列其它的列必须放到聚合函数中否则直接写此时查询的结果无意义 3having
分组查询也是可以搭配条件来使用的
1、分组之前的条件 where
示例求每个岗位的平均薪资但是除掉张三 2、分组之后的条件 : having
示例 求每个岗位的平均薪资但是除去平均薪资超过10000 的
分组之后的条件也是搭配聚合函数来使用的 3、一个SQL 中可以同时包含分组前的条件和分组后的条件
示例求每个岗位的平均薪资但是除掉张三和除去平均薪资超过10000 的 2、联合查询
联合查询也叫做多表查询是查询中最复杂的一种写法
联合查询就是给你多个表结合多个表的数据进行一些综合性更强的查询 实际开发中往往数据来自不同的表所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积 针对任意的两张表都可以计算笛卡尔积但是一般来说如果两个表没有任何的关系那么计算的结果也是无意义的
笛卡尔积 就是把两个表里的记录 按照排列组合的方式 构成了一个更大的表
笛卡尔积的列数 就是原来两个表的列数之和
笛卡尔积的行数 就是原来两个表的行数之积
笛卡尔积是一个非常低效的操作尤其是表的本身的记录比较多的情况
同样的 多表联合查询也是非常低效的
因此在实际开发中使用联合查询一定要慎重
计算笛卡尔积的时候会出现一些不太靠谱的数据如果此时我们加上链接条件那么筛选出来的数据就都是合理的数据了 (1)内连接
语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
我们现在有四张表包含了三个实体学生、班级、课程
其中包含的关系有学生和班级一对多的关系、学生和课程多对多的关系
分数表就相当于学生和课程之间的这个关联表
我们先使用学生表和班级表进行笛卡尔积 此时可以看到现在得到的这些结果就是笛卡尔积的结果
这个时候如果 class_id 和 id 是一样的那么我们就称这个数据是合法的数据 此时我们的笛卡尔积在加上一些必要的条件后就构成了多表联合查询
如果是单个表进行查询条件中直接写列名即可
如果是多个表进行查询条件中最好写作 表名. 列名 的形式这是因为进行联合查询的这两个表里有些列名可能是一样的如果列名没有重复的此时直接写列名也不是不可以但是最好还是带上表名
示例查询许仙同学的成绩 许仙 来自于 student 表 成绩 来自于 score 表 1我们先把 score 表和 student 表进行笛卡尔积 2指定链接条件 此处是按照 学生id 进行筛选 3根据需求进一步的添加条件 此处按照名字为 “ 许仙 ”再来进行筛选 4针对查询的结果的列进行精简 多表查询本质上就是把所有的情况都类出来再筛选出合适的因此多表查询效率是比较低的
示例查询所有同学的总成绩及个人信息
每个同学可以有多门课程这几门课的成绩是按照行来组织的 (1) 两个表进行笛卡尔积 (2) 指定链接条件 (3) 这里要求的是每个学生的总成绩就需要按照学生维度进行 group by (4) 需要搭配聚合函数针对分数进行进一步计算 示例列出同学的名字课程名字分数
现在我们需要通过 student , course , score 三张表进行联合查询
这里 student 和 course 是多对多的关系score 是它们之间的一个关联表因此三个表进行笛卡尔积筛选数据就需要两个链接条件 此处进行三张表的笛卡尔积由于此时的表比较小所以执行笛卡尔积的过程其实是非常快的但是此处显示了几秒才结束这个是控制台打印的缘故
此处能够写出链接条件 是因为这两个实体之间存在关联关系如果是没有关系的两个表这种条件就写不了
前面多个表我们使用 逗号 来分割现在使用 join 来分割前面链接条件通过 where 指定现在使用 on 来指定 示例查询许仙同学的成绩 1直接只写 join 没有 on 则是完整的笛卡尔积 2使用 on 表示链接条件 3结合需求加上其它的条件 4针对 列 进行精简 (2)外连接
那么内连接和外连接有什么区别呢
我们现在假设有这么两张表 此时左侧表的每一条记录都能在右侧找到对应的右侧表的记录也能在左侧找到
此时针对这两个表进行内连接和外连接结果完全相同
当这两个表里面的数据对不上的时候内连接就和外连接产生差别了 例如这种情况此时王五同学在 score 表中没有数据 id 为 4的同学在学生表中也没有数据
内连接的时候产生的结果一定是两个表中都存在的数据公共的部分 外连接在MySQL 里有两种情况一种叫 左外连接 一种叫 右外连接 left join / right join
左外连接就是以左侧表为主左侧表的每个记录 都会存在于最终结果里如果遇到了左侧表中存在但是右侧表中不存在的数据此时就会把对应的 列 填成空值(王五的成绩是空) 同理右外连接就是以右侧的表为主右侧表的每个记录都会存在于最终结果里如果遇到了右侧表中存在左侧表中不存在的数据也会把对应的列填成 NULL 全外连接 这个操作MySQL 不支持但是Oracle 能支持 (3)自链接 自链接 本质上是自己和自己做笛卡尔积
本质是把 行 之间的关系转换成 列 之间的关系
SQL 中编写条件条件都是 列 和 列 之间进行比较但是SQL无法进行 行 和 行 之间的比较
示例查询所有 “计算机原理” 成绩 比“Java”成绩高的成绩信息 1算出笛卡尔积 2加上链接条件此处可以使用 学生id 作为链接条件也可以使用 课程id 3再加上进一步的业务中的条件让左侧的表只保存 course_id 3 的记录同时让右侧的表保存 course_id 1的记录 4再针对列进行精简 score 表是按照 行 来组织多个课程的成绩的 因此在这里 我们要将 行 转换成 列把未知问题转换成已知问题 我们会发现当直接对score 表及其自己进行笛卡尔积的时候会进行报错
这是因为select 的时候多个表的名字不能相同因此需要起一个别名也是使用 as 起别名 这个时候score 就可以进行笛卡尔积了 最后的答案如下 (4)子查询
子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询
本质上就是把多个SQL合并成一个SQL 示例查询与 不想毕业 同学同班的同学
分成两步完成是比较推荐的做法 子查询就是将两个SQL语句合并在一起 SQL 支持这么写但是并不建议因为可读性低
IN 关键字
-- 使用IN
select * from score where course_id in (select id from course where
name语文 or name英文);-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!语文 and name!英文);
EXISTS 关键字
-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
where (name语文 or name英文) and cou.id sco.course_id);-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
where (name!语文 and name!英文) and cou.id sco.course_id); (5)合并查询 把多个 select 查询得到的结果合并成一个集合关键字union / union all
如果想要出现两个张三可以使用 union all 能合并的前提是这两个查询的结果集的 列 得对应
之前学的 or 也是把一些结果合并起来但是只能局限在一个表里里面
union 则是针对任意两个表的数据进行合并 四、总结
