MySQL高级操作.docx
《MySQL高级操作.docx》由会员分享，可在线阅读，更多相关《MySQL高级操作.docx（26页珍藏版）》请在冰豆网上搜索。
MySQL高级操作
昨日回顾
列类型针对单独个体（记录中的某一个字段）的限制。
日期时间型：
timestamp时间戳
JS是毫秒数php是秒数MySQL是DateTime
列属性针对整列的一约束。
primarykey索引，MySQL索引包含（主键索引、唯一键索引、普通索引index、全文索引）
创建：
1、创建表时，直接在主键字段后使用primarykey
2、创建表时，在所有字段后primarykey（字段名）
3、修改表结构
altertable表名addprimarykey（字段列表）
复合主键
创建方式只能使用2、3方法创建
一般会自定义一个与用户数据无关的字段作为主键字段（id）。
主键特点：
值唯一、不可以是null
模拟主键：
当一个表中没有主键字段的时候，如果第一个具有notnull与unique约束的字段会被MySQL默认为主键字段。
主键的删除
altertable表名dropprimarykey;
auto_increment自增长
使用条件：
字段类型必须是整型
自增长必须与primarykey、unique配合使用。
删除自增长
altertable表名modify字段名字段类型;不需要加primarykey与auto_increment
查看与自增长相关的变量
showvariableslike'auto_increment_%';
auto_increment_increment步长
auto_increment_offset起始值
default缺省值
unique唯一性约束
删除
altertable表名dropindex索引名（字段名）;
对于唯一键索引约束的字段的值，可以为Null,原因中null不等null
关系：
实体：
现实中的一个事物，计算机就是用来存储事物的信息，在设计数据表中，一个实体，会单独设计成一个表。
实体表与实体表之间就具有了与现实同样的关系。
一对一
一对多
多对多
范式理论：
1NF
原子性，字段不能可再进行拆分。
2NF
部分依赖，指的是非主键字段的信息依赖于部分主键，部分依赖是出现在复合主键的情况。
解决办法：
就是自定义一个与用户数据无关的字段作为主键字段（逻辑主键）
3NF
传递依赖，
高级操作
复制表结构
语法：
createtable表Blike表A;
示例：
复制表中的数据
语法：
createtable表Bselect*或字段列表from表A;
蠕虫复制
语法：
insertinto表名【字段列表】select*或字段列表from表名;
修改操作
限制修改指定的行数。
语法：
update表名set字段=值...【where子句】limitn;
说明：
在执行update语句时，限制最多修改n行
示例：
删除操作
限制删除的行数
语法：
deletefrom表名【where子句】limitn;
说明：
用于限制最多只能删除n条记录
示例：
插入操作
主键冲突
基本语法：
insertinto表名【（字段列表）】values（值列表）;
主键冲突
insertinto表名【（字段列表）】values（值列表）onduplicatekeyupdate字段=值,字段=值...;
示例：
说明：
主键冲突的方式也可应用在唯一键冲突。
唯一键冲突
同理
replaceinto表名【字段列表】values（值列表）;
说明：
在主键冲突或唯一键冲突时，替换冲突的记录
示例：
查询操作
标准语法：
select【all|distinct】*|字段列表|字段名【as】别名from数据源【where子句】【groupby子句】【having子句】【orderby子句】【limit子句】
说明：
select选项
all（缺省）表示显示所有的记录（包含重复的记录）
distinct表示不显示重复的记录（去掉重复记录）
示例：
字段别名
语法：
字段名【as】别名
示例：
说明：
只是在显示的更改字段的名子，并没有更改表结构中的字段的名子。
一般是用在多表查询时
表别名
语法：
from表名【as】表别名
示例：
数据源
数据的来源，数据的来源可以分3种：
1、单表数据源
select*from表名;
2、多表数据源
select*from表A,表B;
说明：
将多个表的字段进行横向连接，
记录数相乘
术语：
迪卡尔积
示例：
3、子查询数据源
select语句查询的结果是一个结果集，那再将此结果集作为from的一个数据源，此种数据源即为子查询数据源
示例：
说明：
子查询数据源，必须将子查询使用括号括起来，并且设置一个别名
【where子句】
根据条件进行数据筛选，条件即为一个表达式，表达式就需要运算符
isnull
isnotnull
<=>安全相等专用于判断null，只适用于MySQLSQLServer不适用
between
betweenmandn
介于m与n之间（包含m与n）
in
like
%表示当前位置及其后所有的字符
_只表示当前位置的字符
where原理
首先明确：
计算机数据存储磁盘，运算在内存中。
where就是将存储磁盘中的数据，读取到内存，并根据条件进行筛选。
如果省略where，则表示所在记录都匹配
图解：
【groupby子句】
作用是根据groupby的条件进行分组，就是一个大的结果集，划分为多个小区域，再进行统计。
简单分组:
说明：
groupby虽然是分组的意义，但groupby更重要的作用是对大的结果集中的小区域进行统计。
groupby的原理
更改分组排序
示例：
需求，每一组中价格最高的商品
错误示例：
由于orderby是根据groupby之后的结果再行排序，
解决办法，在分组之前就对原数据进行排序。
统计函数（聚合函数）
groupby最主要的作用是对分组的小区域进行统计
count（）;
统计显示结果中记录数
语法：
count（*|字段名）;
说明：
*：
表示统计所有的记录数
字段名：
根据字段名进行统计记录数
示例1：
示例：
max（）;
统计最大值
示例：
min（）;
统计最小值
示例：
avg（）;
统计平均值
示例：
sum（）;
统计和
示例：
分组统计：
示例1：
示例2：
多字段分组
groupby可以对多字段进行分组
语法：
groupby字段1,字段2;
说明：
先使用字段1对结果集进行分组，再使用字段2在分组的结果中再进行分组。
有几个字段相当于分了几层。
示例：
图解：
对多字段进行分组并统计时，统计函数是应用在最小组
示例：
withrollup（回溯）
使用汇总，对多个字段进行分组时所产生的多个层级的组，由里向外再应用统计函数。
示例：
原理图：
【having子句】
作用：
对groupby分组后的结果再进行筛选。
where能够实现的having也能够实现，但是having能实现的，where不一定能实现
示例:
where的效率高于having。
having可以使用统计函数，但是where不可以使用统计函数。
说明：
统计函数是对分组后的数据进行再次统计。
在where执行过程中，结果集还没有完成生成，所以无法应用统计函数。
【orderby子句】
按指定的字段对数据进行排序
asc升序
desc降序
【limit子句】
限制数据的操作（显示、更新、删除）
外键
外键，就是外面的键。
为了某种需求，表A中的某个字段是表B中的主键，那么该字段叫表B的外键。
需求，有两个表，学生表与班级表，学生与班级有关系。
如下：
虽然两个表建立关系，但并没有实质的约束存在，如下：
为了解决此种问题，必须使用外键约束。
创建外键：
条件：
前提外键所附属的表必须先存在
外键字段必须所附属表的主键
字段的类型一定要相同。
语法1：
在创建表时创建外键。
foreignkey（字段）references表名（字段名）
示例：
语法2：
通过修改表结构，创建外键
查看外键
showcreatetable表名;
示例：
删除外键
语法：
altertable表名dropforeignkey外键名;
示例：
使用外键
1、向class插入数据
2、向stu表中插入数据
外键的约束
class为主表，stu为从表
主表被从表约束
1、对主表的数据进行操作时（update与delete），不能违反外键约束。
示例：
2、如果主表与从表建立了外键约束，那么主表不能被删除
示例：
从表被主键约束
向从表中插入数据时，外键字段，必须是主表中主键字段已有的数据。
置空约束
ondeletesetnull
当删除主表中的记录时，从表中与之有关联的记录的外键自动设置为null
级联约束
onupdatecascade
当对主表中的记录的主键字段进行更改时，从表中的与之关联的外键同步更新。
示例：
添加置空与级联约束
添加数据：
insertintostuvalues（default,'php007','lisi',30,'cls001'）;
insertintostuvalues（default,'php008','wang',30,'cls002'）;
insertintostuvalues（default,'php009','zhao',30,'cls003'）;
insertintostuvalues（default,'php009','zhao',30,'cls001'）;
尝试删除与更新
升级会员 