MySQL高级语法拓展

内链接 有值得显示
SELECT s.*,c.from t stu as s inner join t class as c on s.fk class id = c.id:

左链接（左表为基础，右表有数据显示，没有数据为空）
SELECT s.*,c.from t stu as s left JOIN t class as c on s.fk class id = c.id:

右链接（右表为基础，左表有数据显示，没有数据为空）
SELECT s.*,c.from t stu as s right join t class as c on s.fk class id = c.id:

自链接
select s.*,s01.stu name as ‘ 爹’ from t stu as s LEET JOIN t stu as s01 on s.p id = s01.id:

为什么需要设计数据库良好的数据库设计
1,节省数据的存储空间
2．能够保证数据的完整性
3方便进行数据库应用系统的开发

糟糕的数据库设计
1,数据冗余、存储空间浪费 、
2,内存空间浪费
3,数据更新和插入的异常等等

数据库设计步骤
1.需求分析
需求分析阶段需要准确了解与分析用户需求,包括数据、功能和性能需求是整个设计过程的基础,是最困难、最耗费时间的一步。
需求:是指对用户需要解决的问题的整体描述。
2.概念设计
整个数据库设计的关键，通过对用户需求进行综合、归纳与抽象,形成一个独立干具体DBMS的概念模型

概念设计的特点:1.能充分地反映现实世界,包括实体和实体乏间的联系,能满足用户对数据处理的要求，是现实世界的一个真实的模型,或接近真实的模型。2.易于理解,从而可以和不熟悉计算机的用户交换意见。用户的积极参与是数据库应用系统设计成功与否的关键。3.易于更动。当现实世界改变时容易修改和扩充,特别是软件、硬件环境变化时更应如此。4,易于向关系、网状或层次等各种数据模型转换。概念结构是各种数据模型的共同基础,它比任意一种数据模型更独立于机器, 更抽象,从而更加稳定。

描述概念模型的工具E-R模型
E-R模型也称实体-关系图(Entity Relationship Diagram),它提供了表示实体类型、属性和关系的方法,用来描述现实世界的概念模型。

3逻辑设计
逻辑设计阶段逻辑结构设计是将概念模型转换成逻辑模型的过程,也就是将E-R图中的实体、关系、属性转化为DBMS所支持的数据结构的过程。与具体的DBMS无关,主要反映业务逻辑。

4物理设计
物理设计阶段设计数据库的物理结构就是根据数据库的逻辑结构，结合选定的DBMS (oracle,mysal.susbase等)设计数据库的存储结构、存取方式等。
数据库物理设计步骤1,确定数据库的物理结构2,评价实施空间效率和时间效率

5. 数据库的实现
   实现数据库应做的主要工作1,用DPL定义数据库结构2,组织数据入库3.编制与调试应用程序4,数据库试运行·功能测试·性能测试

6. 数据库的运行与维护工
   数据库的运行与维护工作在数据库运行阶段,对数据库经常性进行维护工作主要是DBA完成的。包括1.数据库的转储和恢复2,数据库安全性、完整性控制3,数据库性能的监督、分析和改进4.数据库的重组织和重构造

为什么要用ER模型
E-R模型它提供不受任何DBMS约束的面向用户的表达方法,在数据库设计中被广泛用作数据建模的工具。

什么是E-R模型
E-R:实体(Entity)-关系(Relation)E-R图也称实体-关系图(Entity Relationship Diagram),提供了表示实体类型、属性和关系的方法，用来描述现实世界的概念模型。

如何将E-R图转换为表将各实体转换为对应的表,将各属性转换为各表对应的列标识每个表的主键列，需要注意的是：没有主键的表添加ID参号列，它没有实际含义，用于做主键或外键,发贴表和跟贴表中的"TID"列例如用户表中的"UID"列，版块表中添加"SID"列，在表乏间建立主外键，体现实体乏间的映射关系

1. 标识对象—实体 entity
2. 标识实体熟悉 attribate
3. 标识对象之间的关系

范式理论
数据库范式是数据库设计中女不可少的知识,没有对范式的理解,就无法设计出高效率、优雅的数据库。甚至设计出错误的数据库。在关系型数据库中这种规则就称为范式。什么是范式简单的说,范式是符合某一种设计要求的总结。范式是为了消除重复数据,减少冗余数据,从而让数据库内的数据更好的组织，让磁盘空间得到更有效利用的一种标准化标准,满足高等级的范式的先决条件是满足低等级范式。

目前关系数据库有六种范式：第一范式(1NF) 、第二范式(2NF) 、第三范式(3NF) 、巴斯-科德范式(BCNF) 、第四范式(4NF)和第五范式(5NF,又称完美范式) 。满足最低要求的范式是第一范式(INF) 。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF) ,其余范式以次类推。一般说来，数据库只需满足第三范式(3NF)就行了。

范式的存在有什么好处
范式可以避免数据冗余，减少数据库的空间减轻维护数据完整性的麻烦。
我们在利用范式设计表的时候，要根据具体的需求再去权衡是否使用更高范式去设计表。在一般的项目中,我们用的最多也就是第三范式,第三范式也就可以满足我们的项目需求,性能好而且方便管理数据;

范式的存在有什么好处范式可以避免数据冗余，减少数据库的空间减轻维护数据完整性的麻烦。我们在利用范式设计表的时候，要根据具体的需求再去权衡是否使用更高范式去设计表。在一般的项目中,我们用的最多也就是第三范式,第三范式也就可以满足我们的项目需求,性能好而且方便管理数据;

概念索引
是一种特殊的文件,它们包含着对数据表里所有记录的引用指针。它是对数据库表中一列或多列的值进行排序的一种结构。简单理解数据库,引好比是一本书前面的目录,能够加快数据库的查询速度,数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

为什么要创建索引建立索引的目的是加快对表中记录的查找或排序。
创建索引可以大大提高系统的性能
1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表乏间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时, 同样可以显著减少查询中分组和排序的时间。
5,通过使用索引,可以在查询的过程中, .吏用优化器,提高系统的性能。

在以下几种情况下, mysql在查询中即使有索引也不会去使用
1,在多列索引,查询条件中用的不是最左边的列,那么此时是不会使用索引。
2.like查询时%出现在第一位也不会使用索引。
3．条件中有or也不会使用索引。
4．如果musql估计使用全表扫描比使用索引快,它也不会使用索引。
5．表达式
唯一索引它与前面的普通索引类似,不同的就是:索引列的值必领唯一,但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引
create unique index索引名 on表名(列名)
修改表结构
alter table 表名 add unique index(列名)

创建全文索引
create fulltext index索引名 on 表名(列名)
修改表结构
alter table表名 add fulltext索引名(列名)
创建表的时候直接指定
create table表名(列名列的数据类型列的约束,fulltext索引名(列名)

在全文搜索中,mysql指定了最小字符长度,默认是4,必须要匹配大于4的才会有返回结果,可以用SHOW VARIABLES LIKE ft min word len’来查看指定的字符长度,也可以在mysa配置文件mu.ini更改最小字符长度,方法是在muj.ini增加一行比如: ftminword len=2,改完后重启mysq即可 ，MySQL还会计算一个词的权值,以决定是否出现在结果集中, musq默认的阀值是50% (例如: mysql出现在全文的50%行中)MySQL不支持中文的全文检索

select添加语句
insert into表名(列名列表) select 列名列表from表名
例如：为测试索引表(ttestIndex)添加一些数据insert into t testIndex (name, gender) select name, gender from t testIndex

增加索引也有许多不利的方面
1,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。
4. 当对表中的数据进行增加、删除和修改的时候,索引也要动态酌维护,这样就降低了数据的维护速度。

在这些列上创建索引
1,在经常需要搜索的列上,可以加快搜索的速度。
2,在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构。
3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。
4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序,其指定的范围是连续的。
5在经常需要排序的列上创建索引,这样查询可以利用索引的排序,加快排序查询时间。
6,在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

不应该创建索引的这些列具有下列特点：
1,对于那些在查询中很少使用或者参考的列不应该创建索引。
2,对于那些只有很少数据值的列也不应该增加索引。
3,对于那些定义为text, blob数据类型的列不应该增加索引。
4.当修改性能远远大于检索性能时,不应该创建索引。

使用Expalin可以得到
1,表的读取顺序
2,表的读取操作的操作类型
3,哪些索引可以使用
4,哪些索引被实际使用
5,表之间的引用
6,每张表有多少行被优化器查询子,
7.额外开销

Explain语法标准结构和desc表名作用一样

explain表名或者explain select操作

id: select识别符
select_tupe:查询中每个
select子句的类型
table:输出的行所引用的表
partitions:代表给定表所使用的分区
tupe:查询执行计划中表使用的连接方式
possible_keys: mysql能使用那个索引在该表中找到行数据
key:显示mysq!实际决定使用的索引
key._len:选定的索引键的长度
ref:显示使用哪个列或常数与key一起从表中选择行
rows:显示myql认为它执行查询时必须检查的行数
filtered:显示mysal过滤数据的百分比的值
Extra :显包含了MuSQL执行查询时的其它额外信息

tupe:查询执行计划中表使用的连接方式
下面是最常用的几种连接方式.
const :当表中最多只有一行匹配时出现。.
system :表仅有一行(-系统表) ,这是const的一神特例。.
eq.ref :对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的连接类型。. ref :对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。.
range :这个值表示所有符合一个给定范围值的索引行都被用到。.
All :这个值表示需要一次全表扫描(是最差的一种连接方式,表示sql语句是需要优化的)。
结果值从好到坏依次是：system > const > eq-ref> ref> fulltext> ref or null > index_merge > unique_subquery > index subquery range > index> ALL
当tupe的值为al额外信息出现using fileSort, useing, tempoary,表示查询语句需要优化。

什么是存储过程
Musql存储过程是一组为了完成特定功能的SQL语句集,经过编译乏后存储在数据库中,当需要使用该组SQL语句时,用户只需要通过指定存储过程的名字并给定参数(如果该存储过程带有参数的话)就可以调用执行它了。

存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。

为什么要用存储过程及其优点
储存过程是一个可编程的函数,它在数据库中创建并保存。
它可以有SQL语句和一些特殊的控制结构组成。
当希望在不同的应用程序或平台上执行相同的函数,
或者封装特定功能时,存储过程是非常有用的。
数据库中的存储过程可以看做是对编程中面向对象方海的模拟。它允许控制数据的访问方式

存储过程可以重复使用,可减少数据库开发人员的工作量存储过程被创建后,可以在程序中被多次调用,而不灯重新编写该存储过程的SOL语句,而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。当对数据库进行复杂操作时(如对多个表进行Update,Insert,Ouery,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用

存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算I：功能强大，逻辑强大

安全性高，可设定只有某些用户才具有对指定存储过程的使用权系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

存储过程能过减少网络流量针对同一个数据库对象的操作（如查询、修改) ,如果这一操作所涉及的Transaction(事务)-SOL语句被组织成存储过程,那么当在客户计算机上调用该将储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并隆低了网络负载。

存储过程的基本语法
创建存储过程
create procedure存储过程名称([参数列表] )
begin
From duai尾表 过程集
End
唤醒函数
Call +名称

修改结束符
Delimiter

查询某个数据库下所有的存储过程
show procedure status where db=数据库名称
例如：查看qs数据库下的存储过程show procedure status where db= ‘ys’

MYSQL变量大体可以分为四种类型
1,局部变量
在begin到end语句块之间,在该语句块里设置的变量。
作用范围：局部变量只在begin-end语句块乏间有效。
局部变量一般用declare采声明,可以使用default来说明默认值。

2,用户变量
用户自定义的变量就叫用户变量。
用户变量:以"@“开始,形式为”@变量名"例如: @test
作用范围：用户变量跟mysq!客户端是绑定的,设置的变量,只对当前用户使用的客户瑞生效。当当前连接断开后，其所定义的用户变量都会消失。

3. 会话变量
   （从打开客户端连接到mysql服务器到关闭连接的过程,我们称为一个会话)
   只对连接的客户端有效。客户瑞只能更改自己的会话变量,而不能更改其它客户端的会话变量。
   会话变量的作用域与用户变量一样,仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。

4. 全局变量
   对所有客户端生效。
   只有具有super权限才可以设置全局变量作用范围:全局变量作用于服务器的整个生命周期,但是不能重启。即重启后所有设置的全局变量均失效。
   要想让全局变量重启后继续生效，需要更改相应的配置文件。

注意：变量之间的层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量

declare声明局部变量语法：
create procedure存储过程名称(「参数列表1)
begindeclare变量名数据类型「 defalut默认值1
过程体(SQL代码集)
End
注意:默认值可以被指定为一个表达式或者常教,如果没有default子句,初始值为NUILL(请记得一定要初始化变量)。

使用变量set语句语法：
create procedure存储过程名称(「参数列表])
begindeclare变量名数据类型[ defalut默认值]
set变量名=值1表达式（set 变量名：=值||表达式）
End

声明用户变量：
使用select声明用户变量语法
select @变量名:=值
注意使用select和set设置变量的区别, set可以使用两神形式设置变量。而select只能使用":="的形式设置变量。

select into语句把选定的列值直接存储到变量, 只有单一的行可以被取回。
语法：
select列名1,列名2,…列名N into变量1,变量2,…变量N from表名where过滤条件

inout输入输出参数
调用者可以通过inout参数传递值给存储过程,也可以从存储过程内部传值给调用者。简单理解:参数即是输入参数,也是输出参数。如果需要把数据传给MySQL存储过程,还要经过一些计算后再传回给我们,此时,要使用"inout"类型参数。

如果仅仅想把数据传给MySQL存储过程,那就使用"in"类型参数；
如果仅仅从MySQL存储过程返回值,那就使用"out"类型参数
如果需要把数据传给MySQL存储过程,还要经过一些计算后再传回给我们,此时,要使用"inout"类型参数。
MySQL存储过程参数如果不显式指定"in"、“out”、“inout”,则默认为"in"。

什么是触发器触发器？
是提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发。触发器是一种特殊的存储过程，它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
触发器的优点
1,自动
2,安全性
3,审计
4．业务逻辑封装性好,修改方便
5,实现复杂的数据完整性规则
6.实现复杂的非标准的数据库相关完整性规则