mysql笔记第3天.docx
《mysql笔记第3天.docx》由会员分享，可在线阅读，更多相关《mysql笔记第3天.docx（35页珍藏版）》请在冰豆网上搜索。
mysql笔记第3天
第三天
一、回顾
1.字段类型（列类型）:
数值型,时间日期型和字符串类型
2.数值型:
整型和小数型（浮点型和定点型）
3.时间日期型:
datetime，date，time，timestamp，year
4.字符串类型:
定长,变长,文本字符串（text和blob）,枚举和集合
5.Mysql记录长度:
65535个字节,varchar达不到理论长度,NULL占用一个字节,text文本不占用记录长度（但是本身占据十个字节）
6.字段属性:
空属性,列描述,默认值
二、字段属性
主键，唯一键和自增长.
1.主键primarykey
主键:
primarykey，主要的键。
一张表可以将某一个字段设置为主键或将多个字段设置为复合主键,用来唯一的约束该主键里面的数据，不能重复，这种称之为主键.
一张表最多只能有一个主键。
（Desctab_name;的时候，key那里显示某字段属性为PRI，实际上不一定它就是主键，也可能是假象）
①增加主键：
SQL操作中有多种方式可以给表增加主键，大体分为三种.
方案1:
在创建表的时候,直接在字段之后，跟primarykey关键字（主键本身不允许为空）
优点:
非常直接;缺点:
只能使用一个字段作为主键
方案2:
在创建表的时候,在所有的字段之后,使用primarykey（主键字段列表）来创建主键（如果有多个字段作为主键,可以是复合主键）
方案3:
当表已经创建好之后,额外追加主键：
a.可以通过修改表字段属性（modify或change）：
altertablemy_pri3modifycoursechar（10）primarykeycomment'课程编号：
3901+0000';
b.也可以直接追加：
Altertable表名addprimarykey（字段列表）;
前提:
表中字段对应的数据本身是独立的（不重复）
②主键约束
主键对应的字段中的数据不允许重复:
一旦重复,数据操作失败（增和改）
③更新主键&删除主键
没有办法更新主键:
主键必须先删除,才能增加.（主键只能这么删,用modify和change不能删）
Altertable表名dropprimarykey;
④主键分类
在实际创建表的过程中,很少使用真实业务数据作为主键字段，这种称为业务主键（如学号,课程号）;大部分的时候是使用逻辑性的字段（字段没有业务含义,值是什么都没有关系）,将这种字段主键称之为逻辑主键.
Createtablemy_student（
Idintprimarykeyauto_incrementcomment‘逻辑主键:
自增长’,--逻辑主键
Numberchar（10）notnullcomment‘学号’,
Namevarchar（10）notnull
）;
2.自动增长auto_increment
自增长:
当对应的字段,不给值,或者说给默认值,或者给NULL的时候,会自动的被系统触发,系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段.
自增长通常是跟主键搭配.
①新增自增长
自增长特点:
a.任何一个字段要做自增长必须前提是本身是一个索引（key一栏有值）
b.自增长字段必须是数字（整型）
c.一张表最多只能有一个自增长
②自增长使用
a.当自增长不给值或被给定的值为NULL或者默认值的时候会触发自动增长，自增长的第一个元素默认是1，自增长每次都是自增1
b.自增长如果对应的字段输入了值,那么自增长失效:
但是下一次还是能够正确的自增长（从最大值+1）
c.可以通过查看表创建语句看到下一次自增长的数值.
③修改自增长下次的值
自增长如果是涉及到字段改变:
必须先删除自增长,后增加（一张表只能有一个自增长）
修改当前自增长已经存在的值:
修改只能比当前已有的自增长的最大值大,不能小（小了不报错，但不生效）
Altertable表名auto_increment=值;
向上修改可以
思考:
为什么自增长是从1开始?
为什么每次都是自增1呢?
所有系统的变现（如字符集,校对集）都是由系统内部的变量进行控制的.
查看自增长对应的变量:
showvariableslike‘auto_increment%’;
可以修改变量实现不同的效果:
修改是对整个数据修改,而不是单张表:
（修改是会话级）
Setauto_increment_increment=5;--一次自增5
测试效果:
自动使用自增长
④删除自增长、添加自增长
自增长是字段的一个属性:
可以通过modify来进行修改（保证字段有或没有auto_increment即可）（试:
使用Altertable表名change字段新字段名类型;等效于下面,也可以）
删除：
Altertable表名modify字段类型;--如果本身是主键这里就不要把主键写上去了，因为主键理论是单独存在的
添加：
Altertable表名modify字段类型auto_increment;
3.唯一键
一张表往往有很多字段需要具有唯一性,数据不能重复:
但是一张表中只能有一个主键:
唯一键（uniquekey）就可以解决表中有多个字段需要唯一性约束的问题.
唯一键的本质与主键差不多:
唯一键默认的允许自动为空,而且可以多个为空（空字段不参与唯一性比较）
①增加唯一键
基本与主键差不多:
三种方案
方案1:
在创建表的时候,字段之后直接跟unique/uniquekey
方案2:
在所有的字段之后增加uniquekey（字段列表）;列表内写多个字段就为复合唯一键
看看下面的假象！
showcreatetable表名;就可以看到真相！
方案3:
在创建表之后增加唯一键（写多个相当于复合唯一键）
可以altertable表名adduniquekey（字段列表）;
也可以用altertable表名modify/change…来增加（此法只能增不能删）
②唯一键约束
唯一键与主键本质相同:
唯一的区别就是唯一键默认允许为空,而且是多个为空.
如果唯一键也不允许为空:
与主键的约束作用是一致的.
③更新唯一键&删除唯一键
更新唯一键:
先删除后新增（唯一键可以有多个:
可以不删除）.
删除唯一键
Altertable表名dropindex索引名字;（试：
主键不能这么删,外键也不能
唯一键只能这么删,用change和modify删不掉）--唯一键默认的使用字段名作为索引名字，索引名字可以用showcreatetabletab_name;查看到
3.索引
几乎所有的索引都是建立在字段之上.
索引:
系统根据某种算法,将已有的数据（未来可能新增的数据）,单独建立一个文件:
文件能够实现快速的匹配数据,并且能够快速的找到对应表中的记录.
索引的意义：
a.提升查询数据的效率（没有索引的查询会整表查询）
b.约束数据的有效性（唯一性等）
增加索引的前提条件:
索引本身会产生索引文件（有时候有可能比数据文件还大）,会非常耗费磁盘空间.
什么时候使用索引？
如果某个字段需要作为查询的条件经常使用,那么可以使用索引（一定会想办法增加）;
如果某个字段需要进行数据的有效性约束,也可能使用索引（主键,唯一键）
Mysql中提供了多种索引：
主键索引:
primarykey
唯一索引:
uniquekey
全文索引:
fulltextindex
普通索引:
index
全文索引:
针对文章内部的关键字进行索引
全文索引最大的问题:
在于如何确定关键字
英文很容易:
英文单词与单词之间有空格
中文很难:
没有空格,而且中文可以各种随意组合（分词:
sphinx）
三、关系
将实体与实体的关系,反应到最终数据库表的设计上来:
将关系分成三种:
一对一,一对多（多对一）和多对多.
所有的关系都是指的表与表之间的关系.
1.一对一
一对一:
一张表的一条记录一定只能与另外一张表的一条记录进行对应;反之亦然.
学生表:
姓名,性别,年龄,身高,体重,婚姻状况,籍贯,家庭住址,紧急联系人
Id（P）
姓名
性别
年龄
体重
身高
婚姻
籍贯
住址
联系人
表设计成以上这种形式:
符合要求.其中姓名,性别,年龄,身高,体重属于常用数据;但是婚姻,籍贯,住址和联系人属于不常用数据.如果每次查询都是查询所有数据,不常用的数据就会影响效率,实际又不用.
解决方案:
将常用的和不常用的信息分离存储,分成两张表
常用信息表
Id（P）
姓名
性别
年龄
体重
身高
1
不常用信息表:
保证不常用信息与常用信息一定能够对应上:
找一个具有唯一性（确定记录）的字段来共同连接两张表
Id（P）
婚姻
籍贯
住址
联系人
2
1
一个常用表中的一条记录:
永远只能在一张不常用表中匹配一条记录;反过来,一个不常用表中的一条记录在常用表中也只能匹配一条记录:
一对一的关系
2.一对多
一对多:
一张表中有一条记录可以对应另外一张表中的多条记录;但是返回过,另外一张表的一条记录只能对应第一张表的一条记录.这种关系就是一对多或者多对一.
母亲与孩子的关系:
母亲,孩子两个实体
妈妈表
ID（P）
名字
年龄
性别
孩子表
ID（P）
名字
年龄
性别
以上关系:
一个妈妈可以在孩子表中找到多条记录（也有可能是一条）;但是一个孩子只能找到一个妈妈:
是一种典型的一对多的关系.
但是以上设计:
解决了实体的设计表问题,但是没有解决关系问题:
孩子找不出妈,妈也找不到孩子.
解决方案:
在某一张表中增加一个字段,能够找到另外一张表的中记录:
应该在孩子表中增加一个字段指向妈妈表:
因为孩子表的记录只能匹配到一条妈妈表的记录.
妈妈表
ID（P）
名字
年龄
性别
孩子表
ID（P）
名字
年龄
性别
妈妈ID（妈妈表主键）
3.多对多
多对多:
一张表中（A）的一条记录能够对应另外一张表（B）中的多条记录;同时B表中的一条记录也能对应A表中的多条记录:
多对多的关系
老师教学:
老师和学生
老师表
T_ID（P）
姓名
性别
1
A
男
2
B
女
学生表
S_ID（P）
姓名
性别
1
张三
男
2
小芳
女
以上设计方案:
实现了实体的设计,但是没有维护实体的关系.
一个老师教过多个学生;一个学生也被多个老师教过.
解决方案:
在学生表中增加老师字段:
不管在哪张表中增加字段,都会出现一个问题:
该字段要保存多个数据,而且是与其他表有关系的字段,不符合表设计规范:
增加一张新表:
专门维护两张表之间的关系
老师表
T_ID（P）
姓名
性别
1
A
男
2
B
女
学生表
S_ID（P）
姓名
性别
1
张三
男
2
小芳
女
中间关系表:
老师与学生的关系
ID
T_ID（老师）
S_ID（学生）
1
1
1
2
1
2
3
2
1
4
增加中间表之后:
中间表与老师表形成了一对多的关系:
而且中间表是多表,维护了能够唯一找到一表的关系;同样的,学生表与中间表也是一个一对多的关系:
一对多的关系可以匹配到关联表之间的数据.
学生找老师:
找出学生id->中间表寻找匹配记录（多条）->老师表匹配（一条）
老师找学生:
找出老师id->中间表寻找匹配记录（多条）->学生表匹配（一条）
四、范式
范式:
NormalFormat,是一种离散数学中的知识,是为了解决一种数据的存储与优化的问题:
保存数据的存储之后,凡是能够通过关系寻找出来的数据,坚决不再重复存储:
终极目标是为了减少数据的冗余.
范式:
是一种分层结构的规范,分为六层:
每一次层都比上一层更加严格:
若要满足下一层范式,前提是满足上一层范式.
六层范式:
1NF,2NF,3NF...6NF1NF是最底层,要求最低;6NF最高层,最严格.
Mysql属于关系型数据库:
有空间浪费:
也是致力于节省存储空间:
与范式所有解决的问题不谋而合，在设计数据库的时候,会利用到范式来指导设计;
但是数据库不单是要解决空间问题,要保证效率问题:
范式只为解决空间问题,所以数据库的设计又不可能完全按照范式的要求实现:
一般情况下,只有前三种范式需要满足.
范式在数据库的设计当中是有指导意义:
但不是强制规范.
设计数据库:
满足三范式
1.1NF
第一范式:
在设计表存储数据的时候,如果表中设计的字段存储的数据,在取出来使用之前还需要额外的处理（拆分）,那么说表的设计不满足第一范式:
第一范式要求字段的数据具有原子性:
不可再分.
讲师代课表
讲师
性别
班级
教室
代课时间
代课时间（开始，结束）
朱元璋
Male
php0226
D302
30天
2014-02-27,2014-05-05
朱元璋
Male
php0320
B206
30天
2014-03-21,2014-05-30
李世民
Male
php0320
B206
15天
2014-06-01,2014-06-20
上表设计存不存在问题看需求:
如果需求是将数据查出来之后,要求显示一个老师从什么时候开始上课,到什么时候节课:
需要将代课时间进行拆分:
不符合1NF,数据不具有原子性,可以再拆分.
解决方案:
将代课时间拆分成两个字段就解决问题.
2.2NF
要满足第二范式,必须满足第一范式.
第二范式:
在数据表设计的过程中,如果有复合主键（多字段主键）,且表中有字段并不是由整个主键来确定,而是依赖主键中的某个字段（主键的部分）:
存在字段依赖主键的部分的问题,称之为部分依赖:
第二范式就是要解决表设计不允许出现部分依赖.（只要不存在复合主键就永远满足第二范式!
）
讲师带课表
以上表中:
因为讲师没有办法作为独立主键,需要结合班级才能作为主键（复合主键:
一个老师在一个班永远只带一个阶段的课）:
代课时间,开始和结束字段都与当前的代课主键（讲师和班级）有关:
但是性别并不依赖班级,教室不依赖讲师:
性别只依赖讲师,教室只依赖班级:
出现了性别和教室依赖主键中的一部分:
部分依赖.不符合第二范式.
解决方案1:
可以将性别与讲师单独成表,班级与教室也单独成表.
解决方案2:
取消复合主键,使用逻辑主键
ID=讲师+班级（业务逻辑约束:
复合唯一键）
3.3NF
要满足第三范式,必须满足第二范式.
第三范式:
理论上讲,一张表中的所有字段都应该直接依赖主键（逻辑主键:
它代表的是业务主键）,如果表设计中存在一个字段,并不直接依赖主键,而是通过某个非主键字段依赖,最终实现依赖主键:
把这种不是直接依赖主键,而是依赖非主键字段的依赖关系称之为传递依赖.第三范式就是要解决传递依赖的问题.
讲师带课表
以上设计方案中:
性别依赖讲师存在,讲师依赖主键;教室依赖班级,班级依赖主键:
性别和教室都存在传递依赖.
解决方案:
将存在传递依赖的字段,以及依赖的字段本身单独取出,形成一个单独的表,然后在需要对应的信息的时候,使用对应的实体表的主键加进来.
讲师代课表
讲师表班级表
讲师表:
ID=讲师班级表中:
ID=班级
4.逆规范化
有时候,在设计表的时候,如果一张表中有几个字段是需要从另外的表中去获取信息.理论上讲,的确可以获取到想要的数据,但是就是效率低一点.会刻意的在某些表中,不去保存另外表的主键（逻辑主键）,而是直接保存想要的数据信息:
这样一来,在查询数据的时候,一张表可以直接提供数据,而不需要多表查询（效率低）,但是会导致数据冗余增加.
如讲师代课信息表
逆规范化:
磁盘利用率与效率的对抗
四，数据高级操作
数据操作:
增删改查
1.新增数据
基本语法
Insertinto表名[（字段列表）]values（值列表）[,（值列表）];
在数据插入的时候,假设主键对应的值已经存在:
插入一定会失败!
①主键冲突
当主键存在冲突的时候（Duplicatekey）,可以选择性的进行处理:
更新和替换
（试:
这种onduplicatekey操作同样试用于uniquekey）
a.更新操作
Insertinto表名[（字段列表:
包含主键）]values（值列表）onduplicatekeyupdate字段=新值;
（试:
如果有多个字段有唯一键,插入的数据多个字段跟不同的记录冲突了,那么会更新最先遇到的那一条冲突的记录,比较顺序为字段列表从左至右!
）
b.替换操作
Replaceinto表名[（字段列表:
包含主键）]values（值列表）;
（试:
若没有唯一键或主键冲突就跟insert一模一样,若有冲突,就相当于先删掉旧的再插入新的,哪怕新的数据和旧的数据一模一样,或一条新数据跟多条旧数据冲突,也不例外!
）
②蠕虫复制
蠕虫复制:
从已有的数据中去获取数据,然后将数据又进行新增操作:
数据成倍的增加.
表创建高级操作:
从已有表创建新表（复制表结构,不复制数据）
Createtable表名like数据库.表名;
蠕虫复制:
先查出数据,然后将查出的数据新增一遍
Insertinto表名[（字段列表）]select字段列表/*from数据表名;
蠕虫复制的意义:
a.从已有表拷贝数据到新表中
b.可以迅速的让表中的数据膨胀到一定的数量级:
测试表的压力以及效率
2.更新数据
基本语法:
Update表名set字段=值[where条件];
高级新增语法:
Update表名set字段=值[where条件][limit更新数量];
3.删除数据
与更新类似:
可以通过limit来限制数量
Deletefrom表名[where条件][limit数量];
删除:
如果表中存在主键自增长,那么当删除之后,自增长不会还原
思路:
数据的删除是不会改变表结构,只能删除表后重建表
Truncate表名;--先删除该,后新增该表（仅仅保留表结构和名字,重置清空,自增长也会回到初始值）
4.查询数据
基本语法
Select字段列表/*from表名[where条件];
完整语法
Select[select选项]字段列表[字段别名]/*from数据源[where条件子句][groupby子句][having子句][orderby子句][limit子句];（五子句顺序必须保证!
）
①Select选项
即select对查出来的结果的处理方式
All:
默认的,保留所有的结果
Select*frommy_copy;
等价于selectall*frommy_copy;
Distinct:
去重,查出来的结果,将重复给去除（所有字段都相同）（针对的是查询后的结果去重）
②字段别名
字段别名:
当数据进行查询出来的时候,有时候名字并一定就满足需求（多表查询的时候,会有同名字段）.需要对字段名进行重命名:
别名
格式:
字段名[as]别名;
③数据源
数据源:
数据的来源,关系型数据库的来源都是数据表:
本质上只要保证数据类似二维表,最终都可以作为数据源.
数据源分为多种:
单表数据源,多表数据源,查询语句（子查询）
a.单表数据源:
select*from表名;
b.多表数据源:
select*from表名1,表名2...;
从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留:
（记录数和字段数）,将这种结果成为:
笛卡尔积（交叉连接）:
笛卡尔积没什么卵用,所以应该尽量避免.
c.子查询:
数据的来源是一条查询语句（查询语句的结果是二维表）
Select*from（select语句）[as]表名;--后面的加别名必须有
④Where子句
Select*frommy_student;相当于Select*frommy_studentwhere1;
Where子句:
用来判断数据,筛选数据.
（sql没有boolean类型,所以用0或1表示真假,理论上非0就是true,0就是false,0在sql中有一些特殊的地位,所以我们会发现枚举是从1开始）
Where子句返回结果:
0或者1,0代表false,1代表true.
判断条件:
比较运算符:
>,<,>=,<=,!
=,<>（大于或小于,相当于不等于）,=（等于,等号在sql中更多的情况用作比较,也可以用于赋值）,like,betweenand（这里and不能用&&代替）,in/notin
逻辑运算符:
&&（或and）,||（或or）,!
（或not）
Where原理:
where是唯一一个直接从磁盘获取数据的时候就开始判断的条件:
从磁盘取出一条记录,开始进行where判断,判断的结果如果成立保存到内存,如果失败直接放弃.
条件查询1:
要求找出学生id为1或者3或者5的学生
条件查询2:
查出区间落在180,190身高之间的学生:
Between本身是闭区间;between左边的值必须小于或者等于右边的值
⑤Groupby子句
Groupby:
分组的意思,根据某个字段进行分组（相同的放一组,不同的分到不同的组）
基本语法:
groupby字段名;
（分组后select,每组只会显示出原始表内的第一条数据,没意义）
分组的意义:
是为了统计数据（按组统计:
按分组字段进行数据统计）
SQL提供了一系列统计函数（不使用这些函数,groupby就没有意义）
Count（）:
统计分组后的记录数:
每一组有多少记录
Max（）:
统计每组中最大的值
Min（）:
统计每组中最小值
Avg（）:
统计每组的平均值（若某个数据为null,会把它算为0作为一个统计数,不会忽略）
Sum（）:
统计组的和
注意:
Count函数:
里面可以使用两种参数:
*代表统计记录,字段名代表统计对应的字段（N
升级会员 