two-04 索引 视图 多表联查

最新推荐文章于 2024-01-06 17:35:02 发布
最新推荐文章于 2024-01-06 17:35:02 发布
阅读量180 收藏
点赞数
分类专栏： two 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_58828748/article/details/120643242
版权
本文介绍了数据库管理中的索引类型及其优缺点，包括单值、唯一和复合索引，并展示了创建、查看和删除索引的SQL语句。此外，解释了视图的概念，作为一种提高SQL复用率的工具，以及其内存占用的考虑。最后，详细阐述了多表联查的三种方式——笛卡尔积、内连接、左连接和右连接，并通过实例展示了如何进行多表查询操作。
摘要由CSDN通过智能技术生成

1. 索引

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。
Explain可以看SQL是否使用了索引。

1.1 索引的分类

  1. 单值索引：一个索引只包括一个列
  2. 唯一索引：索引列的值必须唯一，允许null值，主键自带唯一索引
  3. 复合索引：一个索引包括多个列
#查看索引
show index from emp;
#创建普通索引
#create index 索引名字 on 表名(字段名); 
create index loc_index on dept(loc); 
#创建唯一索引--索引列的值必须唯一
#CREATE UNIQUE INDEX 索引名 on 表名(字段名)
create unique index l_index on dept(loc);
#创建复合索引
create index 索引名 on 表名 (字段1, 字段2)
create index d_index on emp (ename, sal);
#删除索引
alter table dept drop index loc_index;

1.2 复合索引的最左特性

当创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

1.3 索引的优点缺点

  1. 优点：提高了检索速度，提高了查询效率
  2. 缺点：索引本身也是一张表，需要占内存，需要不断更新

2. 视图

可视化的表，视图当做是一个特殊的表，把sql执行的结果，直接缓存到了视图中。

create view 视图名 as  SQL语句;
select * from 视图名;
#视图：就是一个特殊的表，缓存上次的查询结果
#好处是提高了SQL的复用率，坏处是占内存无法被优化
 
#1.创建视图
CREATE VIEW emp_view AS
SELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询，名字里包含a的
#2.使用视图
SELECT * FROM emp_view

3.多表联查

3.1准备数据

CREATE TABLE courses
(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL,
PRIMARY KEY (cno)
)

CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno)
);
CREATE TABLE students(
sno VARCHAR(3) NOT NULL, 
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME, 
class VARCHAR(5), 
PRIMARY KEY (sno)
);
CREATE TABLE teachers(
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
)

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88); 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

3.2 使用多表联查

#多表联查:
#方式1:笛卡尔积,通过逗号连接表名
#练习1:查询部门编号是1的部门信息和员工信息
SELECT * FROM dept,emp
    #表名.字段名=表名.字段名
WHERE dept.deptno=emp.deptno#描述两个表的关系
       AND dept.deptno=1#查deptno=1的数据
#练习2:查询员工姓名叫jack的部门信息      
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno #描述了两张表的关系
   AND emp.ename='jack' #业务条件      
#练习3:查询岗位是总监所在的部门信息    
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了两张表的关系
      AND emp.job='总监' #业务条件   
      
#方式2:连接查询,分为三种:
#内连接inner join:取两张表的交集
#左连接left join:取左表的所有和右表满足条件的
#右连接right join:取右表的所有和左表满足条件的
#练习1:列出java开发部 部门下的所有员工的信息
#笛卡尔积方式
SELECT emp.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了两张表的关系
	AND dept.dname='java开发部' #业务条件
#连接查询方式
SELECT emp.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了两张表的关系
WHERE dept.dname='java开发部' #业务条件
#练习2:查询岗位是总监所在的部门信息  
SELECT dept.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了两张表的关系
WHERE emp.job='总监' #业务条件
#练习3:查询员工姓名叫jack的部门信息  
SELECT * FROM emp JOIN dept #只写join是inner join的简写形式
ON emp.deptno=dept.deptno#描述了两张表的关系
WHERE emp.ename='jack' #业务条件
#三种连接查询:inner join/left join/right join
SELECT * FROM dept INNER JOIN emp #可以简写成join
#取两张表都满足条件后的交集
ON emp.deptno=dept.deptno

SELECT * FROM dept LEFT JOIN emp
#取左表的所有,右边满足条件的取到不满足条件的用null填充 
ON dept.deptno=emp.deptno

SELECT * FROM emp RIGHT JOIN dept
#取右表的所有,左边满足条件的取到不满足条件的用null填充 
ON dept.deptno=emp.deptno

#综合练习1:查询陈冰老师能讲解的课程的名称(teachers/courses)
#笛卡尔积方式
SELECT courses.cname FROM teachers,courses
WHERE teachers.tno=courses.tno#描述两张表的关系
      AND teachers.tname='陈冰' #业务条件
#连接查询方式
SELECT courses.cname FROM teachers INNER JOIN courses
ON teachers.tno=courses.tno#描述两张表的关系
      WHERE teachers.tname='陈冰' #业务条件
#综合练习2:查询学员李军的总得分(students/scores)
#笛卡尔积方式
SELECT SUM(degree) FROM students s1,scores s2
WHERE s1.sno=s2.sno #描述两张表的关系
     AND s1.sname='李军' #业务条件
#连接查询方式
SELECT SUM(degree) FROM students s1 INNER JOIN scores s2
ON s1.sno=s2.sno #描述两张表的关系
WHERE s1.sname='李军' #业务条件


#方式3:子查询/嵌套查询:把上次的查询结果作为这次的查询条件
#练习1:查询学员李军的总得分(students/scores)
#先查询学生表,根据学员名字查编号
SELECT sno FROM students WHERE sname='李军'#101
#再根据编号查得分表里的分数
SELECT SUM(degree) FROM scores WHERE sno=101
#子查询
SELECT SUM(degree) FROM scores WHERE sno=(
  SELECT sno FROM students WHERE sname='李军'
)
#练习2:查询陈冰老师能讲解的课程的名称(teachers/courses)
#先查老师表,根据名字查编号
SELECT tno FROM teachers WHERE tname='陈冰' #831
#再根据编号查课程名称
SELECT cname FROM courses WHERE tno=831
#子查询
SELECT cname FROM courses WHERE tno<(
  SELECT tno FROM teachers WHERE tname='陈冰'
)
百川探
关注
专栏目录
Two-factor authentication (2FA) is required for your GitHub account解决方案
weixin_43178406的博客
01-27 4万+
 本文主要介绍了Two-factor authentication (2FA) is required for your GitHub account的解决方案，希望能对使用github的同学们有所帮助。 文章目录 1. 问题描述 2. 解决方案
【AI面试】目标检测中one-stage、two-stage算法的内容和优缺点对比汇总
钱多多先森
04-30 9375
目标检测算法one stage和two stage的对比
视图与索引&连表查询（内/外联）和子查询
最新发布
m0_74276368的博客
01-06 1369
目录一、视图 1.1、概念：1.2、场景：1.3、用视图的意义1.2、创建(增加)视图1.3、修改视图1.4、删除视图1.5、查看视图​编辑二、索引2.1、概念2.2、优缺点优点：缺点：2.3、应用场景2.4、会失效2.5、分类：2.6、语法创建索引的语法三、复杂SQL语句3.1、用处3.2、内联（inner join/ join）概念：应用场景：3.3、外联(left join\right join或full outer join)概念：分类：应用场景：3.3、子查询概念：面试题3.4、案例(运用两种方式
mysql 表 视图 索引吗_Mysql多表查询, 视图,事务,索引，函数，go连接数据库
weixin_33872754的博客
01-27 138
1.1 今日目标理解多表查询理解子查询能够创建视图能够删除视图能够查看创建视图的SQL语句能够理解事务的作用能够操作事务理解索引的作用能够创建索引能够删除索引知道常用的函数了解预处理语句的作用能够使用预处理语句了解存储过程的作用能够创建存储过程能够调用存储过程能够通过Go语言连接MySQL数据库能够通过Go语言对MySQL进行增、删、改、查的操作1.2 多表查询分类测试代码/*stu测试数据*...
多个表进行连接（主表，子表，代码表，客户往来表）生成的视图
01-11
多个表进行连接（主表，子表，代码表，客户往来表）生成的视图，你会在编程时经常用到。
mysql 视图 联合查询_mysql之旅_外键_联合查询_子查询_视图
weixin_39638309的博客
02-01 263
回顾4连接查询:连接多张表到一起，不管记录数如何，字段数一定会增加。分类：内连接，外连接。自然连接，交叉连接，交叉连接：cross join (笛卡尔积)内连接：inner join，左右两张表有连接条件匹配(不匹配自动忽略)外连接：left/right join 主表的一条记录一定会存在匹配保留副表数据，否则置空自然连接：natural join 自动匹配(相同字段名)PHP操作：mysqlPh...
数据库三（连表联查、视图）
qq_45798550的博客
09-23 2864
文章目录一、mysql的三大范式二、连表联查1.union2.笛卡尔积3.表连接4.逻辑判断5.连表查询三、从其他表中加载数据四、视图五、sql执行顺序六、索引七、事务 一、mysql的三大范式 原子性 字段不可在分割 唯一性 字段必须依赖于主键 冗余性 避免数据量过大 二、连表联查 删除之前的表，创建两个新表，插入数据 student score 1.union union:多个结果集进行合并(纵) 格式:select 字段 from 表名 union 表名; 注
在APPLE从创建ID到申请发布AppStore账户（四）登记前Apple ID 的双重认证（Two-factor authentication for Apple ID）
I am HadesZ，我是金瀚海
02-25 1万+
6.登记前Apple ID 的双重认证（Two-factor authentication for Apple ID） 点击说明中的链接进入双重认证页面 英文地址：https://support.apple.com/en-us/HT204915 Two-factor authentication for Apple ID Two-factor authentication is an extr...
two-stream双流网络 知乎、CSDN学习笔记
Tian__Gao的博客
10-22 7834
文章目录一、文章解决的问题二、论文细节1.双流网络的具体结构是什么2.两个网络怎么合并在一起3.optical flow(光流)是什么4.多任务学习怎么做5.Bi-directional optical flow(双向光流)6.优点和不足 一、文章解决的问题 1.提出了two-stream结构的CNN，由空间和时间两个维度的网络组成 2.使用多帧的稠密光流场作为训练输入，可以提取动作的信息 3.利用了多任务学习的方法把两个数据集联合起来 二、论文细节 1.双流网络的具体结构是什么 该网络有两个独立的识别流
Two-Stream Convolutional Networks for Action Recognition in Videos算法笔记
AI之路
12-08 1万+
论文：Two-Stream Convolutional Networks for Action Recognition in Videos 链接：https://arxiv.org/abs/1406.2199 这篇文章是NIPS 2014年提出一个two stream网络来做video action的分类，比较经典。two stream表示两个并行的网络：spatial stream conv
MySQL视图的使用与多表视图查询
polsnet的专栏
05-21 4764
视图提供了一种简化和抽象化数据库查询的方式，可以隐藏复杂的查询逻辑，简化数据访问，并提供了一致性和安全性的控制。在MySQL中，视图的使用广泛应用于各种场景，并且可以进行多表视图查询。下面将详细介绍MySQL视图的使用、场景以及多表视图查询，并提供在创建视图时需要注意的要点。通过创建视图，可以将复杂的多表关联、计算和筛选操作封装成一个简单的视图查询。在查询多表视图时，可以使用JOIN操作将多个表关联起来，并使用相关的条件进行筛选和连接。在使用视图时，应该了解视图的基础表结构和数据，避免出现意外的结果。
2.数据库的设计
weixin_39563769的博客
07-11 213
数据库范式： 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。 数据库三大范式： 第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解(比如说有一列: sex，很明显这个sex列是不可能再分了，是原子性的) 第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。 考虑在分布式系统，高并发的情况下，确保id主键的唯一性？ 第三范式：3NF是对字段冗余性的约束，.
MySQL数据库存储过程（存过）
weixin_67792584的博客
03-30 3116
mysql 5.0 以后版本才开始支持存储过程 存储过程：是一种在数据库中存储的复杂的程序，以便外部程序或者其他应用程序对数据进行调用的数据库对象。 两个要点：存储在数据库中 复杂的程序 被调用 简单的来说：存储过程就是在数据库中编写代码，目的是为了完成特定的任务或者功能的 SQL 的集合，它是一个 语句集，经过编辑保存到数据库中，可以让指定的用户对其进行调用，提升效率。 即：是数据库中...
SQL回炉系列（二） 多表联合查询和视图
日西月东的专栏
11-23 5397
多表联合 日期截取比较 排序 视图
MySQL多表查询及视图
weixin_34348805的博客
10-11 1264
多表查询视图 转载于:https://blog.51cto.com/13878078/2298973
利用视图进行多表关联
Jin__nan的博客
07-10 1万+
疑问 在大数据中我们关联的码表大于8个，然后数据存储量大于500W，那么在进行hive清洗的时候极有可能会被卡死。可是我们就是要在一张表上关联10多个表，比如一张表中的很多字段都要关联码表，因为其有对应的翻译字段。那我们改怎么办呢？ 只能将他分成俩次进行关联。 难不成我们要重新创建一个中间表就是为了关联一下么？卧槽，这也太操蛋了吧。 答案 答案流程 1、面对这样的问题的时候s...
SQL视图和多表连接
热门推荐
忠麦一生
08-30 3万+
本篇博客关注的焦点是视图的使用以及视图和多表连接的配合。以便可以了解视图，以及更好的使用视图。        首先，还是要说明一下视图的定义：视图是基于SQL语句的结果集的可视化虚拟表，换句话说视图就是SQL查询结果的可视化呈现，是多个表的部分数据的关系虚拟表。为了后面的内容更容易理解，我们还要看一下视图的建立过程。如图所示是机房收费系统中的《User_sys》和《CheckInfo_sys》建
解决CDMA系统two-way问题：百货大楼基站高掉话分析
"CDMA系统two-way问题的解决" CDMA（码分多址）系统是一种无线通信技术，它允许多个用户在同一频率上同时传输数据，通过分配不同的编码序列来区分不同的信号。在CDMA网络中，two-way问题通常指的是双向切换问题，即...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值