14 外键联合查询连接查询子查询视图Word文档格式.docx
《14 外键联合查询连接查询子查询视图Word文档格式.docx》由会员分享，可在线阅读，更多相关《14 外键联合查询连接查询子查询视图Word文档格式.docx（22页珍藏版）》请在冰豆网上搜索。
Union选项:
与select选项一样:
all（保存所有）和distinct（去重,默认的）
联合查询意义
需求:
所有的学生按照班级进行分类:
2班之前的所有学生按照年龄升序排序;
3班以后的学生按照年龄降序排序.
Union意义
1.能够对于从同一张表中查询的不同结果（排序）进行合并处理（解决需求问题）
2.在大型数据库设计中,通常会因为数据量太大将表进行分割:
分到不同的表中进行保存:
最终查询结果的时候需要一次性查询所有表中保存的结果.
3.缺点：
若是不同的表，不同字段混合在一起了，容易混乱
联合查询排序
联合查询与orderby要共同使用:
必须满足一下条件
1.为了保证语法没有错误:
系统无法区分orderby是对当前sql语句排序还是对整个union的结果进行排序:
对select语句使用括号（）
2.Union想要使用orderby实现排序:
必须搭配limit:
每一条SQL语句中都应该有limit
（select*fromstu_studentwherec_id<
=3orderbyageasclimit999）
union
（select*fromstu_studentwherec_id>
=4orderbydesclimit999）
存储引擎
1.什么是存储引擎?
定义了数据在数据库中存储的方式，不同的存储引擎都提供不同的存储机制、索引技巧等。
Mysql有多种存储方式:
5种
Innodb,myisam,memory,BDB和archive:
其中myisam和innodb是免费的,其他三个是收费的
在mysql5以前（5.4）,mysql的默认存储引擎是myisam;
5.5以后,默认的存储引擎是innodb
数据表选择存储引擎的时候,大体使用默认的即可.
Myisam与innodb的典型区别
1.Myisam是独立存储数据和索引:
只要产生一个myisam存储引擎的表:
会出现三个独立文件（结构文件,数据文件,索引文件）,而innodb只有一个文件
2.Innodb只有一个结构文件:
所有innodb的表的数据（索引）都存储在ibdata1文件中
外键
1.什么是外键?
外键:
一张表中有字段与另一张表中的主键建立了关系，那这个关系就叫外键
创建外键
foreignkey
有两种方式可以增加外键:
前提的外键所指向的表必须先创建.
主表:
被其他表所指向的表
从表:
如果当前表指向另外表的主键（从表）
创建主表:
班级表class
createtableclass2（
idintprimarykeyauto_increment,
namevarchar（10）notnullunique,
roomvarchar（10）
）charsetutf8
（1）方法一:
在创建表的时候就指定外键（从表:
学生表）
Foreignkey（外键字段）references主表（主键字段）;
createtablestudent2（
numberchar（10）notnullunique,
namevarchar（10）notnull,
c_idintnotnull,
foreignkey（c_id）referencesclass（id）--系统会自动给外键添加一个名字,showcreatetable看
）charsetutf8enginemyisam;
（2）方法二:
在创建表之后增加外键（指定）
Altertable表名add[constraint外键名]foreignkey（外键字段）references父表（主键）;
altertablestudent2addconstraintstudent_classforeignkey（c_id）referencesclass（id）;
外键约束（默认）
外键的建立会对表产生约束作用:
默认的约束,一旦外键创建成功:
有两种约束
1.子表（从表）插入数据的时候,不能插入父表中不存在的主键，但可以删除.
--从表插入数据一定要在主表中存在该id
insertintostudentvalues（null,'
ticast0001'
'
自来也'
2）;
--可以
3）;
--不可以
2.父表不能删除（更新）一个主键字段（被子表引用）
主表不能删除或者更新一个被从表引用的记录
注意:
所谓的不能更新是指外键关联的字段不能更新:
其他都可以更新
删除外键
外键是不能被修改的,但是可以被删除
删除语法
Altertable表名dropforeignkey外键名;
altertablestudentdropforeignkeystudent_class;
外键条件
1.只有innodb存储引擎支持外键
2.外键字段（c_id）必须与父表主键字段（id）的数据类型要严格一致
3.如果是后期增加外键:
表中的数据必须严格符合外键的约束条件
作业:
测试
外键约束（自定义）
默认的外键约束:
父表不能更新或者删除一个被子表引用的记录;
子表不能插入或者更新一个父表不存在的记录:
严格约束.
外键约束分为三种:
严格约束:
district,父表和子表都不允许随意操作
制空约束:
setnull,父表如果删除自己的记录,但是记录又被子表引用（子表的外键字段就置空）
级联约束:
cascade,父表如果删除或者更新自己的记录,那么子表相关记录的外键一起改变
一个通常的约束:
父表删除,子表置空;
父表更新,子表级联.
Foreignkey（外键）references主表（主键）ondeletesetnullonupdatecascade
altertablestudentaddconstraintstudent_class
foreignkey（c_id）referencesclass（id）
ondeletesetnull
onupdatecascade;
如果要实现置空模式:
前提是子表的外键字段必须允许为空:
如果子表字段不允许为空（外键创建失败）
PHP很少使用外键:
外键增加了php对数据的维护性.
连接查询
1.什么是连接查询?
连接（Join）查询:
将两张表（多张）,从字段上进行连接（字段数增加）.
2.为什么要连接查询?
大多数实体都不是单独存在,实体与实体之间都有关系.导致在数据进行查看的时候,通常需要从多张表中去获取数据:
就需要使用连接查询.
3.连接查询分类
内连接,外连接,自然连接和交叉连接.
4.表命名
每次连接查询至少有两张表:
因此为了表名方便称呼:
将join关键字左边的表称之为左表;
join关键字右边的表叫做右表.
交叉连接
交叉连接的结果是笛卡尔积:
多表查询的时候的结果是笛卡尔积
没有指定连接条件的内连接就是交叉连接
交叉连接:
crossjoin:
一张表的每一条记录都与另外一张表的每一条进行匹配:
每次匹配都成功.
记录数:
表1记录数*表2记录数
字段数:
表1字段数+表2字段数
交叉连接没有意义:
存在的意义是为了保证连接结构的完整性.
Ø
内连接
按照条件拿着左表的记录去右表中匹配,符合条件的就保存下来
（1）内连接语法
左表[inner]join右表on连接条件;
mysql>
select*fromstu_studentinnerjoinstu_classonstu_student.c_id=stu_class.id;
内连接可以没有inner关键字:
表可以使用别
select*fromstu_studentassjoinstu_classascons.c_id=c.id;
（2）内连接原理:
从左表中取出记录去右边挨个匹配,匹配成功就保留,失败就放弃;
（3）内连接的连接条件
可以使用where而不是使用on:
但是效率没有on高:
where每一条都匹配,然后判断:
on先判断后匹配;
select*fromstu_studentsinnerjoinstu_classcwheres.c_id=c.id;
外连接
外连接:
会从左表或者右表中取出一张表作为主表,以主表的每一条记录去对应的附表中匹配:
如果匹配上就连接,并保留整个结果;
如果是没有匹配上:
会保留主表记录,但是附表记录字段置空.
左表作为主表匹配右表,显示的行数以左表为准，匹配不到右表的就添加null制空
如果选择左表为主表:
左外连接（左连接）
如果选择右表为主表:
右外连接（右连接）
外连接语法
左表left/rightjoin右表on连接条件;
select*fromstu_studentsleftjoinstu_classcons.c_id=c.id;
--左表不足字段补null
select*fromstu_studentsrightjoinstu_classcons.c_id=c.id;
不管是左连接还是右连接:
都是左表的字段出现在前面,以左表的字段数为准,右表的字段出现在后面.左边的不足的字段会补null
内连接和外连接的区别：
◆内连接只保存显示满足条件的
◆外连接以左表数量为准显示个数
自然连接
自然连接:
系统自动的寻找条件进行匹配,系统只会认为名字相同的字段就是连接条件.如果有多个相同的条件,所有相同的字段都用来做条件.
自然连接包括自然内连接和自然外连接
自然内连接语法
左表naturaljoin右表;
有数据的自然内连接
自然外连接:
左表naturalleft/rightjoin右表
不管是自然内连接还是自然外连接:
都有可能错误（自动匹配条件:
同名字段不一定是相同条件）
如果要使用自然连接:
那么字段设计必须完全规范化（每个表的字段都不能与另外的表字段相同:
除非的确是外键字段）
Createtablestu_student（
S_idint,
S_name,
S_number,
C_id）charsetutf8;
Createtablestu_class（
C_idint,
C_name,.
C_room）charsetutf8;
内连接和外连接可以模拟自然连接（用相同字段连接,并且合并相同字段）:
连接的时候不要使用on作为条件,而是使用using关键字
左表[left/right/inner]join右表using（同名字段列表）;
设计一个教学管理系统数据库:
学生,讲师,班级,课程（按照实际开发的逻辑去设计:
字段,字段属性）
子查询
1.什么是子查询?
子查询:
在一个查询语句（select）里面还有一个select语句,里面的select语句就称之为子查询.
2.子查询分类?
根据位置进行分类:
根据子查询在select语句中出现的位置进行判断
From子查询:
子查询语句接from关键字之后
Where子查询:
子查询语句接where之后
Exists子查询:
子查询出现在exists之后（exists出现在where之后:
exists返回的结果:
0和1）
根据子查询查到的结果进行分类:
标量子查询:
子查询得到的结果只有一行一列
列子查询:
子查询得到结果是一列多行
行子查询:
子查询的二级果通常是一行多列（多行）:
前仨都属于where子查询
表子查询:
子查询的结果就是多行多列（属于from子查询）
标量子查询
知道班级的名字,查询这个班的所有学生
1.Select*fromstu_studentwherec_id=?
;
--确定数据来源
2.Selectidfromstu_classwherec_name=‘班级名字’;
--确定查询条件
基本语法:
select*from表名where字段=（select字段from表名where条件）;
列子查询
找出当前所有在班（读）的学生:
学生的班级id都有效.
1.Select*fromstu_studentwherec_idin?
--确定数据源
2.Selectidfromstu_class.--确定查询条件:
--列多行
select*from表名where字段in（select字段from表名）;
select*fromstu_studentwherec_idin（selectidfromstu_class）;
与in相同的还有几个差不多的符号:
any,some,all
Any表示任意一个（与some没有任何区别）:
字段=any/some/all
All表示全部
肯定形式的使用
否定形式的使用
行子查询
找出学生中年龄最大且身高最高的所有学生.
1.Select*fromstu_studentwhereage=?
andheight=?
2.Selectmax（age）,max（height）fromstu_student;
--确定条件
必须构造行元素（由多个字段构成）
Select*from表名where（字段1,字段2...）=/in（select字段列表from表名）;
Select*fromstu_student
Where（age,height）=（selectmax（age）,max（height）fromstu_student）;
表子查询
找出每个班最高的一个学生（分组）
1.Select*fromstu_studentorderbyheightdesc;
--先确定数据源
2.Select*fromstu_studentgroupbyc_id;
但是:
groupby在where之后在orderby之前执行.
select*from（select*from表名）as别名;
Select*from（select*fromstu_studentorderbyheightdesc）asstudentgroupbyc_id;
Exists子查询
Exists是一种专门用于条件判断的子查询
查询三班的所有学生:
c_id=3,但是条件是3这个班存在.
1.Select*fromstu_studentwherec_id=3;
2.Select*fromstu_classwhereid=3;
--条件
select*from表名whereexists（select语句）;
Select*fromstu_studentwherec_id=3andexists（select*fromstu_classwhereid=3）;
视图
1.什么是视图?
视图:
view,是一个能够显示数据,保存结构的虚拟表（只有结构不存数据）
虚拟数据表
创建视图
基本语法
Createview视图名字asselect语句;
createviewv1asselect*fromstu_student;
会创建视图文件（结构文件）在对应的数据库文件夹下
查看视图
视图是一种结构:
查看结构（凡是查看表的语句都可以查看视图:
showtables/showcreatetable/desc）
视图也可以view关键字:
showcreateview
视图是一种虚拟表:
表是用来存储数据的,因此视图可以用来当做表查询数据
Select*from视图名;
修改视图
视图可以修改:
可以修改视图的组成语句（select）
Alterview视图名字asselect语句;
删除视图
Dropview视图名字;
视图结构文件会从数据库对应的文件夹下被删除
视图数据操作
视图的确可以进行数据写操作:
但是有条件
1.多表视图是不能新增和删除数据:
a）创建多表视图的时候,不能有同名字段
B）创建合理的多表视图
C）多表示视图不能新增数据
D）多表视图不能删除数据
E）多表视图可以更新数据
2.单表视图可以进行数据写操作:
单表视图可以删除和新增
a）删除数据
b）新增数据:
视图中必须包含基表中所有不能为空的字段:
视图对基表进行数据插入的时候,如果视图中不包含基表中的对应字段,会插入null
视图算法
任何一个视图其实是有算法:
算法分为三类
Undefined:
未定义,这是系统默认的算法:
是一种让系统自动选择算法的算法
Merge:
合并算法:
将调用视图的select语句与视图内部的select语句先进行合并,后执行（执行一次:
效率高:
系统会经常选择merge）
Temptable:
临时表算法:
将视图内部的select语句先执行,当做外部select语句的数据源（执行两次）
创建视图:
视图内部进行orderby,查询视图的时候使用groupby
原因:
视图在选择算法的时候,选择的merge算法,先合并后执行:
groupby在orderby之前
指定算法为temptable:
临时表,视图优先执行（先orderby,后groupby）
视图算法选择:
大部分的时候直接忽略（使用默认的）,如果在视图中和调用视图查询的过程中,会出现五子句的顺序问题:
建议在创建视图的时候指定算法为temptable.
视图意义
1.视图能够将复杂的select语句进行结构维护,使得查询变得简单（简化SQL）
2.视图通常是对外提供数据的时候才需要
a）外部用户不一定需要基表中的所有字段:
为了保护基表的隐私,通常使用视图（安全性）
b）视图可以在不修改基表的情况下,对外提供不同形式的数据:
增加用户友好性（每个公司都是单独一张属于自己的视图,不需要共享）
c）可以根据不同视图进行权限控制
升级会员 