数据库三（连表联查、视图）

最新推荐文章于 2024-06-11 16:09:08 发布

一只小呆呆

最新推荐文章于 2024-06-11 16:09:08 发布

阅读量2.8k

点赞数 1

分类专栏：数据库Mysql 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_45798550/article/details/108736007

版权

数据库Mysql 专栏收录该内容

7 篇文章 4 订阅

订阅专栏

文章目录

一、mysql的三大范式
二、连表联查
三、从其他表中加载数据
四、视图
五、sql执行顺序
六、索引
七、事务
八、在xshell中执行脚本运行sql语句
九、总结

一、mysql的三大范式

1. 原子性字段不可在分割
1. 唯一性字段必须依赖于主键
1. 冗余性避免数据量过大

二、连表联查

删除之前的表，创建两个新表，插入数据
student
在这里插入图片描述

score
在这里插入图片描述

1.union

	union:多个结果集进行合并(纵)
		格式:select 字段 from 表名 union 表名;
		注意:查询时多张表的字段数量保持一致
			直接使用union时,相同的数据只显示一行
			使用union all时,相同的数据都会显示
			查询结果的列名以第一次sql语句的结果列为准

truncate table student;清空表
在这里插入图片描述

2.笛卡尔积

前面的数据与后面表的数据每一行去匹配，10*30会变成300行数据
在这里插入图片描述

3.表连接

为了减少数据膨胀，在笛卡尔积的基础上增加条件
1个ID对应三科成绩，数据变为30行
在这里插入图片描述
join

join中连接条件用on 不用where。
left join:左表为准匹配右表的数据,左表中的数据匹配不到右表的数据就显示null,右边的数据和左表不匹配就不会显示
格式:select 字段 from 表名 left join 表名 on 连接条件;
right join:右表为准匹配左表的数据,右表中的数据匹配不到左表的数据就显示null,右边的数据和左表不匹配就不会显示
格式:select 字段 from 表名 right join 表名 on 连接条件;
inner join:两张表的交集
格式:select 字段 from 表名 inner join 表名 on 连接条件;

在原先的student中增加一个学生 “学生”，便于观察结果
左连接
以左表为准，右边没有补空
在这里插入图片描述
右连接
以右表为准，学生没有匹配到

内连接
只显示两者共有的，不以某一个为准

4.逻辑判断

逻辑判断:case when 判断条件 then 值 [when 判断条件 then]* [else 值] end
格式一：
在这里插入图片描述
格式二：

注意：枚举类型判断时，要加上引号，如上的1,0表示男女，判断时需加上引号

5.连表查询

select * from student where id in(select distinct studentid from score where score<(select avg(score) from score));
查询有科目小于平均值的学生信息
select * from student,(select studentid,sum(score) as “总分” from score group by studentid) s where s.studentid=student.id;
查询每个学生的总分和学生的信息
select * from student left join (select studentid,sum(score) as “总分” from score group by studentid) s on s.studentid=student.id;
查询每个学生的总分和学生的信息
select * from student
inner join
(select studentid,sum(score) as ss from score group by studentid) s
on s.studentid=student.id order by ss desc limit 3;
获取前三信息

注意：得到的表和值如果不是原先存在的，后期需要用到，需要重给名，但给的不能是“…”类型，只能由字母或数字组成

select * from (
select * from student
inner join
(select studentid,sum(score) sum_score from score group by studentid)
ss on student.id=ss.studentid) s where (select count(*) from
(select * from student
inner join
(select studentid,sum(score) sum_score from score group by studentid)
ss on student.id=ss.studentid) s1 where s.sex=s1.sex and s.sum_score<s1.sum_score)< 3；
查询男生中的前三和女生中的前三

在这里插入图片描述

查询每科最高分
select subjectname,max(score) from score group by subjectname;
查询语文最高分及学生信息
select * from student inner join (select * from score where subjectname=“语文” and score=(select max(score) from score where subjectname=“语文”)) s on student.id=s.studentid;

注意：不能在在查询聚合函数例如最大值最小值得时候，直接查询其他数据。

三、从其他表中加载数据

	从其他表中加载数据
		格式1:create table 表名 as 查询语句
			例如:create table student1 as select * from student;
		格式2:insert into 表名 查询语句
			例如:insert into test select * from score;

在这里插入图片描述

四、视图

视图:是一张虚拟表,数据都是存储在基础表中
创建视图
格式:create view 表名 as 查询语句;
例如:create view tmp as select * from student_info where age=25;
查看视图结构：
格式:desc 表名
例如:desc tmp;
格式:show create view/table 表名;
例如:show create table tmp;
删除视图：
格式:drop view 表名
例如:drop view tmp;
视图和基础表的关系是一对一:在视图中进行增删改查时,会在基础表上发生改变，基础表发送改变，视图也会改变
视图和基础表的关系是一对多:视图中不能进行insert和delete，可以修改和查询，且基本表中数据也会改变。

五、sql执行顺序

from–where–group by–having–select–order by
- from:需要从哪个数据表检索数据
- where:过滤表中数据的条件
- group by:如何将上面过滤出的数据分组
- having:对上面已经分组的数据进行过滤的条件
- select:查看结果集中的哪个列，或列的计算结果
- order by :按照什么样的顺序来查看返回的数据

六、索引

索引:便于查询，主键默认拥有一个索引

索引不是越多越好,因为索引也需要存储空间去存放,索引越多占的资源就越多
添加索引:
格式:alter table 表名 add index 索引名称(索引列);
例如:alter tabel student add index Index_name(name);
删除索引:
格式:alter table 表名 drop index 索引名称;
例如:alter table student drop index Index_name;

七、事务

事务就是一段sql语句的批处理，一个操作下可以分为多个操作,事务决定了这多个小操作都实行完成,才认为这一个操作执行结束
sql默认执行自动提交
set autocommit=0；关闭i自动提交，等下一次打开数据库时，又默认为自动提交
事务的操作:
begin:开始一个事务
rollback:事务出现问题进行回滚
commit:事务没有问题进行提交
- 例如:
  BEGIN;
  update person set money=5000-2000;
  update bank set money=0+2000;
  select * from person;
  select * from bank;
  commit;

注意:事务中所有的操作都是临时或虚拟的,在进行commit才会把这些操作才原数据上进行执行
在这里插入图片描述

关闭自动提交事务

这里暂时为500，由于未提交，实际表中的数据并未被更改。

加上commit后，事务提交，表中数据更改

在交之前如果怕数据出错，可以加上回滚，出错后，会重新运行，确保每次提交的数据都是正确的。

八、在xshell中执行脚本运行sql语句

Shell操作mysql
	格式:#!/bin/sh
		MYSQL="mysql -h192.168.154.110 -uroot -p123456 --default-character-		set=utf8"
                sql="select * from shujuku.student"
                result="$($MYSQL -e "$sql")"
                echo -e "$result"

建立j脚本

在这里插入图片描述
执行脚本

九、总结

mysql三大范式
	1. 原子性 字段不可在分割
	2. 唯一性 字段必须依赖于主键  
	3. 冗余性 避免数据量过大
连表联查
	union:多个结果集进行合并(纵)
		格式:select 字段 from 表名 union 表名;
		注意:查询时多张表的字段数量保持一致
			直接使用union时,相同的数据只显示一行
			使用union all时,相同的数据都会显示
			查询结果的列名以第一次sql语句的结果列为准
	join
		left join:左表为准 匹配右表的数据,左表中的数据匹配不到右表的数据就显示null,右边的数据和左表不匹配就不会显示
			格式:select 字段 from 表名 left join 表名 on 连接条件;
		right join:右表为准 匹配左表的数据,右表中的数据匹配不到左表的数据就显示null,右边的数据和左表不匹配就不会显示
			格式:select 字段 from 表名 right join 表名 on 连接条件;
		inner join:两张表的交集
			格式:select 字段 from 表名 inner join 表名 on 连接条件;
	逻辑判断:case when 判断条件 then 值 [when 判断条件 then]* [else 值] end

	从其他表中加载数据
		格式1:create table 表名 as 查询语句
			例如:create table student1 as select * from student;
		格式2:insert into 表名 查询语句
			例如:insert into test select * from score;
	视图:是一张虚拟表,数据都是存储在基础表中
		创建视图
			格式:create view 表名 as 查询语句;
			例如:create view tmp as select * from student_info where age=25;
		查看视图结构：
			格式:desc 表名
			例如:desc tmp;
			格式:show create view/table 表名;
			例如:show create table tmp;
		删除视图：
			格式:drop view 表名
			例如:drop view tmp;
		视图和基础表的关系是一对一:在视图中进行增删改查时,会在基础表上发生改变
		视图和基础表的关系是一对多:视图中不能进行insert和delete

		from--where--group by--having--select--order by
		from:需要从哪个数据表检索数据 
		where:过滤表中数据的条件 
		group by:如何将上面过滤出的数据分组 
		having:对上面已经分组的数据进行过滤的条件  
		select:查看结果集中的哪个列，或列的计算结果 
		order by :按照什么样的顺序来查看返回的数据 
	索引:主键默认拥有一个索引
		索引不是越多越好,因为索引也需要存储空间去存放,索引越多占的资源就越多
		添加索引:
			格式:alter table 表名 add index 索引名称(索引列);
			例如:alter tabel student add index Index_name(name);
		删除索引:
			格式:alter table 表名 drop index 索引名称;
			例如:alter table student drop index Index_name;
	事务:一个操作下可以分为多个操作,事务决定了这多个小操作都实行完成,才认为这一个操作执行结束
		执行事务之前关闭自动提交:set autocommit=0;
		事务的操作:
			begin:开始一个事务
			rollback:事务出现问题进行回滚
			commit:事务没有问题进行提交
		例如:
			BEGIN;
			update person set money=5000-2000;
			update bank set money=0+2000;
			select * from person;
			select * from bank;
			commit;
		注意:事务中所有的操作都是临时或虚拟的,在进行commit才会把这些操作才原数据上进行执行
Shell操作mysql
	格式:#!/bin/sh
		MYSQL="mysql -h192.168.154.110 -uroot -p123456 --default-character-set=utf8"
		sql="select * from shujuku.student"
		result="$($MYSQL -e "$sql")"
		echo -e "$result"

一只小呆呆

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
数据库三（连表联查、视图）

文章目录一、mysql的三大范式二、连表联查1.union2.笛卡尔积3.表连接4.逻辑判断5.连表查询三、从其他表中加载数据四、视图五、sql执行顺序六、索引七、事务一、mysql的三大范式原子性字段不可在分割唯一性字段必须依赖于主键冗余性避免数据量过大二、连表联查删除之前的表，创建两个新表，插入数据studentscore1.union union:多个结果集进行合并(纵) 格式:select 字段 from 表名 union 表名; 注
复制链接

扫一扫

专栏目录