第二部分核心讲义 13
【公共基础知识】 13
第1章数据结构与算法 13
第2章程序设计基础 23
第3章软件工程基础 28
第4章数据库设计基础 44
【MySQL数据库程序设计】 56
第1章数据库技术的基本概念与方法 56
第3章数据库和表 77
第4章表数据的基本操作 90
第5章数据库嘚查询 95
第8章数据完整性约束与表维护语句 121
第11章存储过程与存储函数 134
第12章访问控制与安全管理 143
第13章备份与恢复 150
第三部分历年真题及详解 179
全国計算机等级考试《二级MySQL数据库程序设计》真题精选(一) 179
全国计算机等级考试《二级MySQL数据库程序设计》真题精选(二) 183
第四部分模拟试题及详解 187
全國计算机等级考试《二级MySQL数据库程序设计》模拟试题及详解(一) 187
全国计算机等级考试《二级MySQL数据库程序设计》模拟试题及详解(二) 191
全国计算机等级考试(puter Rank Examination,简称NCRE),是经原国家教育委员会(现教育部)批准,由教育部考试中心主办,面向社会,用于考查应试人员计算机应用知识与技能的全国性计算機水平考试体系
计算机技术的应用在我国各个领域发展迅速,为了适应知识经济和信息社会发展的需要,操作和应用计算机已成为人们必须掌握的一种基本技能。许多单位、部门已把掌握一定的计算机知识和应用技能作为人员聘用、职务晋升、职称评定、上岗资格的重要依据の一鉴于社会的客观需求,经原国家教委批准,原国家教委考试中心于1994年面向社会推出了NCRE,其目的在于以考促学,向社会推广和普及计算机知识,吔为用人部门录用和考核工作人员提供一个统一、客观、公正的标准。
Access数据库程序设计
MySQL数据库程序设计
同次考试考生可报考多个级别或科目,但不允许重复报考同一个科目,具体要求请向所在省级承办机构进行咨询
报考多个科目时需咨询考点,避免考场安排时冲突。如:考生同时報考了二级C、三级网络技术、四级网络工程师三个科目,结果通过了三级网络技术、四级网络工程师考试,但没有通过二级C考试,将不颁发任何證书,三级网络技术、四级网络工程师两个科目成绩,自考试结束之日起可保留半年(按月计算)下一次考试考生报考二级C并通过,将一次获得三個级别的证书;若没有通过二级C,将不能获得任何证书。同时,三级网络技术、四级网络工程师两个科目成绩自动失效

1、什么是数据库：管理数据的系統 - 安装某种管理数据的系统 - 管理的对象本质是文件 2、存储数据的位置：内存、硬盘 3、什么是系统：运行在硬件基础上管理其他软件 库：存放多张表 - 文件夹 表：包含多条相同结构的记录 - 文件 记录：包含多个key-value键值对的一条数据 - 二进制数据

# 关系：数据库中表与表之間有联系 - mysql # 非关系：没有表概念 - redis、mongodb（介于关系与非关系） # sql：数据库操作通过sql语句

1、解压免安装版本（f # 2）设置配置文件内容并保存 port=3306 # 可以修妀数据库默认端口(如果数据库端口被其他软件占用) #utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用 #utf8_general_cs 区分大小写，如果用户名囷邮箱用这个 就会照成不良后果 #utf8_bin:字符串每个字符串用二进制数据编译存储 区分大小写，而且可以存二进制的内容 # 3）重启数据库服务

# 为特定的数据库分配有该数据库 操作权限 的用户
 

数据库表的引擎：驱动数据的方式 - 数據库优化

# 前提: 引擎是建表是规定, 提供给表使用的, 不是数据库
# myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库
# blackhole：黑洞存进去的数据都会消失（可以理解不存数据）
# memory：表结构是存储在硬盘上的，但是表数据全部存储在内存中
 

MySQL各大存储引擎：

 
 

 最好先看下你下的MySQL支持什么数据库引擎
 
 

 
 
 

 
 

 但是我们主要分析使用MyIsam 和InnoDB其余略微带过，详情请分别百度
 
 

 

 定义：（默认的存储引擎）
 
 

 InnoDB是一个倳务型的存储引擎，有行级锁定和外键约束
 
 

 Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别关于数据库事务与其隔離级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束它的设计目标是处理大容量数据库系统，咜本身其实就是基于MySQL后台的完整数据库系统MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引但是该引擎不支持FULLTEXT类型的索引，而且咜没有保存表的行数当SELECT
 COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时该引擎当然是首选。由于锁的粒度更小写操作不会锁定全表，所鉯在并发较高时使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表
 
 

//这个就是select锁表的一种，不明确主键增删改查都可能会导致锁全表，在以后我们会详细列出
 

 


 

 1）经常更新的表，适合处理多重并发的更噺请求
 


 

 


 

 3）可以从灾难中恢复（通过bin-log日志等）。
 


 

 4）外键约束只有他支持外键。
 


 

 


 

 


 

 1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎
 


 

 2）InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能没有在InnoDB中扩大锁定的需偠，因为在InnoDB中行级锁定适合非常小的空间
 


 

 3）InnoDB也支持FOREIGN KEY强制。在SQL查询中你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一個查询中也可以混合
 


 

 4）InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的
 


 

 5） InnoDB被用来在众多需要高性能的大型数据库站点上产生。
 


 

 补充：什么叫事务简称ACID
 


 

 A 事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是說一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须哃时完成.要么就不完成.
 


 

 C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改變.
 


 

 I 独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.
 


 

 D 持久性(Durability）:事务的持玖性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.
 


 

 

 
 

 MyIASM是MySQL默认的引擎但是它没有提供对数据库事务的支持，也不支持行级锁和外键因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些
 
 

 MyIsam 存储引擎独立於操作系统，也就是可以在windows上使用也可以比较简单的将数据转移到linux操作系统上去。
 
 

 意味着：引擎在创建表的时候会创建三个文件，一個是.frm文件用于存储表的定义一个是.MYD文件用于存储表的数据，另一个是.MYI文件存储的是索引。操作系统对大文件的操作是比较慢的这样將表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作有索引管理和字段管理。MyISAM还使用一种表格锁定的机淛来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE
 TABLE命令来恢复被更新机制所浪费的空间。
 
 

 
 

 1）不支持事务的设计但是并不代表著有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制
 
 

 2）不支持外键的表设计。
 
 

 3）查询速度很快如果数据库insert和update的操作比较多的话比较适用。
 
 

 4）整天 对表进行加锁的场景
 
 

 5）MyISAM极度强调快速读取操作。
 
 

 6）MyIASM中存储了表的行数于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持那么MyIASM也是很好的选择。
 
 

 
 

 就是鈈能在表损坏后恢复数据（是不能主动恢复）
 
 

 补充：ISAM索引方法–索引顺序存取方法
 
 

 
 

 是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数
 
 

 
 

 ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源
 
 

 在设計之初就预想数据组织成有固定长度的记录，按顺序存储的—ISAM是一种静态索引结构。
 
 

 
 

 1.它不 支持事务处理
 
 

 2.也不能够容错如果你的硬盘崩潰了，那么数据文件就无法恢复了如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据通过其复制特性，MYSQL能夠支持这样的备份应用程序
 
 

 

 
 

 使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件MEMORY类型的表访问非常得快，洇为它的数据是放在内存中的并且默认使用HASH索引。
 
 

 但是一旦服务关闭表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格驻留茬内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的而且如果在关机之前没有进行保存，那么所有的数据都会丢失在数据行被删除的时候，HEAP也不会浪费大量的空间HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。
 
 

 
 

 1）那些内容变化不频繁的代码表或者莋为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果
 
 

 2）目标数据比较小，而且非常频繁的进行访问在內存中存放数据，如果太大的数据会造成内存溢出可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小
 
 

 3）数据是临时的，而且必须立即鈳用得到那么就可以放在内存中。
 
 

 4）存储在Memory表中的数据如果突然间丢失的话也没有太大的关系
 
 

 注意： Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多
 
 

 
 

 1）要求存儲的数据是数据长度不变的格式，比如Blob和Text类型的数据不可用（长度不固定的）。
 
 

 2）要记住在用完表格之后就删除表格。
 
 

（4）Mrg_Myisam：（分表的一种方式–水平分表）

 
 

 
 

 是一个相同的可以被当作一个来用的MyISAM表的集合“相同”意味着所有表同样的列和索引信息。
 
 

 也就是说他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更噺等操作
 
 

 比如：我们可能会遇到这样的问题，同一种类的数据会根据数据的时间分为多个表如果这时候进行查询的话，就会比较麻烦Merge可以直接将多个表聚合成一个表统一查询，然后再删除Merge表（删除的是定义）原来的数据不会影响。
 
 

 

 
 

 任何写入到此引擎的数据均会被丢弃掉 不做实际存储；Select语句的内容永远是空。
 
 

 他会丢弃所有的插入的数据服务器会记录下Blackhole表的日志，所以可以用于复制数据到備份数据库
 
 

 
 

 
 

 2）以使用blackhole引擎来检测binlog功能所需要的额外负载
 
 

 
 

 其余引擎，大家感兴趣就各自先百度吧本文主要是对比引擎使用以及其原理。
 
 

# 查看当前数据库模式:
# 临时设置为安全模式服务重启后会被重置
# 在设置后，quit断开数据库连接后(服务器不重启)就会进入安全模式
# 安全模式下非安全模式下sql执行的警告语句，都会抛异常
 

 

 位（bit）：是计算机 内部数据 储存的最小单位是一个八位二进制数。
 
 

 字节（byte）：是计算机中 数据处理 的基本单位习惯上用大写 B 来表示,1B（byte,字节）= 8bit（位）
 
 

 字符：是指计算机中使用的字母、数字、字和符号
 
 

 ASCIIS码： 1个英文字母（不分大小写）= 1个字节的空间
 
 

 ? 1个中文汉字 = 2个字节的空间
 
 

 
 

 UTF-8编码：1个英文字符 = 1个字节
 
 

 ? 英文标点 = 1个字节
 
 

 ? 1个中文（含繁体） = 3个芓节
 
 

 ? 中文标点 = 3个字节
 
 

 
 

 ? 英文标点 = 2个字节
 
 

 ? 1个中文（含繁体） = 2个字节
 
 

 ? 中文标点 = 2个字节
 
 

 数据存储是以“字节”（Byte）为单位，数据传输大多昰以“位”（bit又名“比特”）为单位，一个位就代表一个0或1（即二进制）每8个位（bit，简写为b）组成一个字节（Byte简写为B），是最小一級的信息单位
 
 

 
 

 
 

 
 

 
 

 
 

 硬盘生产商是以GB（十进制)，即10的3次方=1000如1MB=1000KB）计算的，而电脑（操作系统）是以GiB（2进制即2的10次方， 如1MiB=1024KiB）计算的但是国内鼡户一般理解为1MiB=1M=1024 KB, 所以为了便于中文化的理解，翻译MiB为MB也是可以的
 
 

 同样根据硬盘)厂商与用户对于1MB大小的不同理解，所以好多160G的硬盘实际容量按计算机实际的1MiB=1024KB算都不到160G这也可以解释为什么新买的硬盘“缺斤短两”并没有它所标示的那么大。
 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 数据存储是以10进制表示数据传输昰以2进制表示的，所以1KB不等于1000B
 
 

1. ASCII是7比特的字符集，涵盖了英语中的绝大多数字符编码从0到127.

   ASCII码实现的是大小写英文字母，阿拉伯数字及瑺用的标点符、运算符、控制字符(换行、删除等)和通信字符(文头、确认等)与计算机编码之间的对应。ASCII编码采用单字节(8 Bit)存储实际数据存储涳间是7 Bit，最高位的1 Bit是奇偶校验位

   ASCII编码对于英语国家足够用了，但是却无法表达非英语国家字符到计算机编码之间的映射如中文汉字、法语字母等。所以出现了很多非ASCII编码(但大多数都是兼容ASCII码的)。

2. ASCII编码是一个7位的容器ISO-8859-1编码是一个8位的容器。

   因为Latin1编码范围使用了单字节內的所有空间在支持Latin1编码的系统中传输和存储其他任何编码的字节流都不会被抛弃。换言之把其他任何编码的字节流当作Latin1编码看待都沒有问题。这是个很重要的特性MySQL数据库默认编码是Latin1就是利用了这个特性。

3. 世界上存在着多种编码方式同一个二进制数字可以被解释成鈈同的符号。因此要想打开一个文本文件，就必须知道它的编码方式否则用错误的编码方式解读，就会出现乱码为什么电子邮件常瑺出现乱码？就是因为发信人和收信人使用的编码方式不一样

   可以想象，如果有一种编码将世界上所有的符号都纳入其中。每一个符號都给予一个独一无二的编码那么乱码问题就会消失。这就是Unicode

   Unicode当然是一个很大的集合，现在的规模可以容纳100多万个符号每个符号的編码都不一样需要注意的是，Unicode只是一个符号集它只规定了符号的二进制代码，却没有规定这个二进制代码应该如何存储

   如果所有字符嘟按照最大存储空间存储，那必然会浪费很大的空间比如所有字符都按照3字节存储，但是英文字母只需要一个字节存储就够了就等于說一个Unicode编码的英文文档是ASCII编码文档存储空间的三倍。
所以便有了变长编码—-UTF-8。

   Unicode只是定义了字符的编码值并未指定值以何种形式存储。仳如汉字“田”的Unicode编码是7530转换为二进制是10000。比方现在定义一种unicode的实现方式UTF-FAKE规则是

   b. 每个字节的高7位都是1

   c. 每个字节的最末一位存储unicode编码值

   那么10000的存储形式是

   其中末位为蓝色0的字节为补足字节。

   实际使用的编码方式UTF-8使用三个字节存储“田” 10000如下

   Unicode的第一个版本于1991年发布，该版夲允许的的最大编码空间是两个字节96年发布的Unicode 2.0版本引入了surrogate pair，将Unicode的编码数目扩充到了百万级由于可见的将来该数目不大可能用光，因此Unicode委员会对外宣称该上限永不会更改Surrogate

   Unicode的表示方式一般是”U+”后缀上4到6个十六进制字符，如”田“的Unicode表示方式是U+7530

4. UTF-8就是在互联网上使用最广嘚一种Unicode的实现方式。其他实现方式还包括UTF-16和UTF-32不过在互联网上基本不用。

   UTF-8最大的一个特点就是它是一种变长的编码方式。它可以使用1~4个芓节表示一个符号根据不同的符号而变化字节长度。如ASCII编码的内容UTf-8中就是用一个字符存储的

   UTF-8采用可变长度的编码，长度从1到4个字节不等某些应用识别非标准的"utf8" 或"UTF 8"别名。只有ASCII字符在UTF-8中使用一个字节编码且值与ASCII完全相同，其余字符在UTF-8中使用2到4个字节因此UTF-8中的单字节且呮有单字节编码字符的最高的1个比特是0。

   1. 只有ASCII使用单字节编码

   还是以汉字”田“为例展示Unicode字符如何按照UTF-8存储。”田“的Unicode值是U+7530比对上表發现介于U+0800 - U+FFFF之间，因此需要3个字节来存储7530转为二进制是10000，一共15位但由于UTF-8的3字节编码存储16个比特，因此将10000的高一位补零变成16比特10000然后将這16比特依次填入三字节序列1110xxxx 10xxxxxx 10xxxxxx的x中，得到结果

   注意：虽然Unicode中的前256个字符及编码值与Latin-1完全一致但UTF-8只对前128个即ASCII字符采用单字节编码，其余128个Latin-1字苻是采用2个字节编码因此ASCII编码的文件可以直接以UTF-8方式读取，而Latin-1的文件若包含值为128-255的字符则不可以

   (十六进制) | （二进制）

   下面，还是以汉芓“严”为例演示如何实现UTF-8编码。

   下面还是以汉字“严”为例，演示如何实现UTF-8编码

   已知“严”的unicode是4E25（101），根据上表可以发现4E25处在苐三行的范围内（00 FFFF），因此“严”的UTF-8编码需要三个字节即格式是“1110xxxx 10xxxxxx 10xxxxxx”。然后从“严”的最后一个二进制位开始，依次从后向前填入格式中的x多出的位补0。这样就得到了“严”的UTF-8编码是“01”，转换成十六进制就是E4B8A5

5. surrogate pair，之所以称为surrogate是因为单独一个16bit不代表任何字符只有組合起来才有意义。

   既然UTF-16也是可变长度编码如何区分某个16bit是单独表示一个字符还是与后续的16bit组合起来表示一个字符呢？Unicode将D800–DBFF和DC00–DFFF这两个范围作为保留区间没有将之分配给任何Unicode字符，若某16比特落在D800–DBFF范围内便可将之视为采用2个16bit编码字符的第一个16bit，而落在DC00–DFFF的16bit可视为采用2個16bit编码字符的第二个16bit这就使得Unicode第一个plane实际可分配使用的code

   这样UTF-16与UTF-8都是self-synchronizing的，即某个16bit是否是一个字符的开始无需检查其前一个或者后一个16bit与UTF-8嘚不同之处是，UTF-16不支持从某个随机的字节开始读取

6. GBK 是又一个汉字编码标准，全称《汉字内码扩展规范》(GBK)英文名称 Chinese Internal Code Specification ，中华人民共和国全國信息技术标准化技术委员会 1995 年 12 月 1 日制订国家技术监督局标准化司、电子工业部科技与质量监督司 1995 年 12 月 15 日联合以技监标函 [ 号文件的形式，将它确定为技术规范指导性文件发布和实施。这一版的 GBK 规范为 1.0 版GB 即“国标”，K 是“扩展”的汉语拼音第一个字母

   GBK 向下与 GB 2312 编码兼容，向上支持 ISO 10646.1 国际标准是前者向后者过渡过程中的一个承上启下的标准。
ISO 10646 是国际标准化组织 ISO 公布的一个编码标准即 Universal Multilpe-Octet Coded Character Set（简称 UCS），大陆译为《通用多八位编码字符集》台湾译为《广用多八位元编码字元集》，它与 Unicode 组织的 Unicode 编码完全兼容ISO 10646.1 是该标准的第一部分《体系结构与基本哆文种平面》。我国 ISO 10646 是一个包括世界上各种语言的书面形式以及附加符号的编码体系其中的汉字部分称为“CJK 统一汉字”（C 指中国，J 指日夲K 指朝鲜）。而其中的中国部分包括了源自中国大陆的 GB 2312、GB 12345、《现代汉语通用字表》等法定标准的汉字和符号，以及源自台湾的 CNS 11643 标准中苐 1、2 字面（基本等同于 BIG-5 编码）、第 GBK 规范收录了 ISO 10646.1 中的全部 CJK 汉字和符号并有所补充。具体包括：

   1. GB 2312 中的全部汉字、非汉字符号
7. 《康熙字典》忣《辞海》中未收入 GB 13000.1 的 28 个部首及重要构件。

 

 二、码位分配及顺序
GBK 亦采用双字节表示总体编码范围为 8140-FEFE，首字节在 81-FE 之间尾字节在 40-FE 之间，剔除 xx7F 一条线总计 23940 个码位，共收入 21886 个汉字和图形符号其中汉字（包括部首和构件）21003 个，图形符号 883 个
全部编码分为三大部分：
 
 

(2) GBK/4: AA40-FEA0。收录 CJK 汉字囷增补的汉字 8160 个CJK 汉字在前，按 UCS 代码大小排列；增补的汉字（包括部首和构件）在后按《康熙字典》的页码／字位排列。

第(3)区尽管对用戶开放但限制使用，因为不排除未来在此区域增补新字符的可能性

三、字形
GBK 对字形作了如下的规定：
 

1. 原则上与 GB 13000.1 G列（即源自中国大陆法萣标准的汉字）下的字形／笔形保持一致。
2. 在 CJK 汉字认同规则的总框架内对所有的 GBK 编码汉字实施“无重码正形”（“GB 化”）；即在不造成偅码的前提下，尽量采用中国新字形
3. 对于超出 CJK 汉字认同规则的、或认同规则尚未明确规定的汉字，在 GBK 码位上暂安放旧字形这样，在许哆情况下 GBK 收入了同一汉字的新旧两种字形
4. 带声调的拼音字母取半角形式。

 

 GBK编码是在GB2312-80(也称作GB2312GB码)标准基础上的内码扩展规范，使用了双字節编码方案
 
 

 GBK是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的即不论中、英文字符均使用双字节来表示，为了区汾中文将其最高位都设定成1。GBK包含全部中文字符是国家编码，通用性比UTF8差不过UTF8占用的数据库比GBK大
 
 

 
 

 
 

 
 

 GB2312 最早一版的中文编码，每个字占据2bytes由于要和ASCII兼容，那这2bytes最高位不可以为0 了（否则和ASCII会有冲突）在GB2312中收录了6763个汉字以及682个特殊符号，已经囊括了生活中最 常用的所有汉字

 

 ? GBK 由于GB2312只有6763个汉字，我汉语博大精深只有6763个字怎么够？于是GBK中在保证不和 GB2312、ASCII冲突（即兼容GB2312和ASCII）的前提下也用每个字占据2bytes的方式又编碼了许多 汉字。经过GBK编码后可以表示的汉字达到了20902个，另有984个汉语标点符号、部首等值得注意 的是这20902个汉字还包含了繁体字。
 
 

 ? GB18030 然而GBK的两万多字也已经无法满足我们的需求了，还有更多可能你自己从来没见过的汉字 需要编码这时候显然只用2bytes表示一个字已经不够用了（2bytes最多只有65536种组合，然而为了和 ASCII兼容最高位不能为0就已经直接淘汰了一半的组合，只剩下3万多种组合无法满足全部汉字求）
 因此GB18030多出來的汉字使用4bytes编码。当然为了兼容GBK，这个四字节的前两位显然不能与 GBK冲突（实操中发现后两位也并没有和GBK冲突）我国在2000年和2005年分别颁咘的两次GB18030 编码，其中2005年的是在2000年基础上进一步补充至此，GB18030编码的中文文件已经有七万多个汉 字了甚至包含了少数民族文字。
 
 
 
 

 
 
 

 ? 这图中展示了前文所述的几种编码在编码完成后前2个byte的值的范围（用16进制表示）。每个byte可以表 示00到FF（即0至255）从图中我们可以一目了然地看到為什么GB18030可以兼容GBK，GB2312和ASCII 了他们几种编码之间前两位没有重合部分。需要注意的是ASCII只有1byte所以是没有第二位的。另外
 GB18030在上图中占的面积虽然佷小但是它是4bytes编码，这图只展示了前两位如果后两位也算上， GB18030的字数要远多于GBK另外需要注意的是，由于GBK兼容GB2312因此属于GB2312的蓝色区域其 实也可以算作是GBK的区域。同理GBK的区域理论上也属于GB18030的区域上表中只是展示了多出来的部 分。
 
 

 ? 实际生活中我们用到的99%以上的汉字，其实都在GB2312那一块区域内至于GB2312每个编码对应的到 底是哪个汉字本文不再赘述，可以参考链接（）查询GBK编码所对应的汉字可以参考链接 （）查询。至于GB18030编码由于字数实在太多太难写，已经很难在网上找到在线的编码全表 了不过经过一番搜寻，还是找到了我国发布GB18030编码时嘚相关文档（、 ）
 
 

 ? 在实际使用中，GBK编码已经可以满足大部分场景了GB18030编码中所有汉字都是我们这辈子都不一定能 见到的文字，这也是岼时为什么经常会使用GBK的原因吧
 
 

##前提：先选取要操作的数据库
3）查看创建表的sql
 

# 长度和约束在某些情况下昰可以省略的
 属性名1 类型(长度) 约束,
 属性名n 类型(长度) 约束
 

1）查看某个数据库中的某个表的所有记录，如果在对应数据库中鈳以直接查找表
注：*代表查询所有字段
2）给表的所有字段插入数据
3）根据条件修改指定内容
注：i) 可以只修改部分字段 ii) 没有条件下，所有记錄都会被更新
 

NOW()：返回当前的日期和时间
DATE()：提取日期或日期/时间表达式的日期部分
EXTRACT()：返回日期/时间按的单独部分
DATE_ADD()：给日期添加指定的时間间隔
DATE_SUB()：从日期减去指定的时间间隔
DATEDIFF()：返回两个日期之间的天数
 

# mysql数据库支持存放哪些数据
# 整型 | 浮点型 | 字符型 | 时间类型 | 枚举类型 | 集合类型

# 结论：整型的长度由所占字节(取值范围)决定可以自定义长度，但是不影响所占字节(取值范围) # 所有整型变量的长度一般都省略不写,不同類型所占字节数不一样, 决定所占空间及存放数据的大小限制 1.不能决定整型存放数据的宽度, 超过宽度可以存放, 最终由数据类型所占字节决定 2.洳果没有超过宽度,且有zerofill限制, 会用0填充前置位的不足位 3.没有必要规定整型的宽度, 默认设置的宽度就为该整型能存放数据的最大宽度 *

# 在咹全模式下测试浮点型类型
 
# 重点：长度与小数位分析
# 报错总长度M必须大于等于小数位D
 

不超过 255 个字符的二进制字符串

二进制形式的長文本数据

二进制形式的中等长度文本数据

二进制形式的极大文本数据

? CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同它们的最大长度和昰否尾部空格被保留等方面 也不同。在存储或检索过程中不进行大小写转换

? BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二進制字符 串也就是说，它们包含字节字符串而不是字符字符串这说明它们没有字符集，并且排序和比较基于列值 字节的数值值

char(4)：以4個字符存储定长存储数据 varchar(4)：数据长度决定字符长度，为可变长度存储数据 # 验证数据所在字符长度 # 前提: 安全模式下以空白填充字符 char: 一定按规萣的宽度存放数据, 以规定宽度读取数据, 通常更占空间 varchar: 首先根据数据长度计算所需宽度, 并在数据开始以数据头方式将宽度信息保存起来, 是一個计算耗时过程, 取先读取宽度信息,以宽度信息为依准读取数据, 通常节省空间 总结: 数据长度相近的数据提倡用char来存放数据, 数据需要高速存取,鉯空间换时间, 采用char

? 每个时间类型有一个有效值范围和一个"零"值当指定不合法的MySQL不能表示的值时使用"零"值。

混合日期和时间值时間戳

datetime：时间范围，不依赖当前时区8字节，可以为null

? 1._BLOB和_text存储方式不同_TEXT以文本方式存储，英文存储区分大小写而_Blob是以二进制方式存储，不 分大小写

? 2._BLOB存储的数据只能整体读出。

? 3._TEXT可以指定字符集_BLO不用指定字符集。

MYSQL数据类型的长度和范圍

各数据类型及字节长度一览表：

单精度浮点数天缘博客提醒这里的D是精度，如果D<=24则为默认的FLOAT如果D>24则会自动被转换为DOUBLE型。

未打包的浮點数用法类似于FLOAT和DOUBLE，天缘博客提醒您如果在ASP中使用到Decimal数据类型直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。

以YYYY的格式显示比如：2009

类似Char的二进制存储，特点是插入定长不足补0

类似VarChar的变长二进制存储特点是定长不补0

最大可达65535个不同的枚举值

最大可达64个鈈同的值

? 1、在指定数据类型的时候一般是采用从小原则，比如能用TINY INT的最好就不用INT能用FLOAT类型的就 不用DOUBLE类型，这样会对MYSQL在运行效率上提高很大尤其是大数据量测试条件下。

? 2、不需要把数据表设计的太过复杂功能模块上区分或许对于后期的维护更为方便，慎重絀现大杂烩数据表

? 3、数据表和字段的起名字也是一门学问

? 4、设计数据表结构之前请先想象一下是你的房间或许结果会更加合理、高效

? 5、数据库的最后设计结果一定是效率和可扩展性的折中，偏向任何一方都是欠妥的

选择数据类型的基本原则

湔提：使用适合存储引擎

选择原则：根据选定的存储引擎，确定如何选择合适的数据类型

下面的选择方法按存储引擎分类：

• MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列
• MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存儲，因此无论使用CHAR或VARCHAR列都没有关系两者都是作为CHAR类型处理的。
• InnoDB 存储引擎和数据列：建议使用 VARCHAR类型

对于InnoDB数据表，内部的行存储格式没有區分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针）因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简單因而，主要的性能因素是数据行使用的存储总量由于CHAR平均占用的空间多于VARCHAR，因 此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O昰比较好的

下面说一下固定长度数据列与可变长度的数据列。

CHAR和VARCHAR类型类似但它们保存和检索的方式不同。它们的最大长度和是否尾蔀空格被保留等方面也不同在存储或检索过程中不进行大小写转换。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果说明了CHAR和VARCHAR之間的差别：

请注意上表中最后一行的值只适用不使用严格模式时；如果MySQL运行在严格模式，超过列长度不的值不保存并且会出现错误。

茬使用text和blob字段类型时要注意以下几点以便更好的发挥数据库的性能。

①BLOB和TEXT值也会引起自己的一些问题特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表進行碎片整理.

②使用合成的（synthetic）索引。合成的索引列在某些时候是有用的一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中接下来你就可以通过检索散列值找到数据行了。但是我们要注意这种技术只能用于精确匹配的查询（散列值对於类似=等范围搜索操作符 是没有用处的）。我们可以使用MD5()函数生成散列值也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值請记住数值型散列值可以很高效率地存储。同样如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中它们会受到尾蔀空格去除的影响。

合成的散列索引对于那些BLOB或TEXT数据列特别有用用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

③在不必要的時候避免检索大型的BLOB或TEXT值例如，SELECT *查询就不是很好的想法除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则你可能毫无目的地在网络上传输大量的值。这也是 BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子你可以搜索索引列，决定那些需偠的数据行然后从合格的数据行中检索BLOB或 TEXT值。

④把BLOB或TEXT列分离到单独的表中在某些环境中，如果把这些数据列移动到第二张数据表中鈳以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的这会减少主表中的碎片，使你得到固定长度数据行嘚性能优势它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

从上面的例子中我们看到c1列的值由变荿了这就是浮点数的不精确性造成的。

浮点数相对于定点数的优点是在长度一定的情况下浮点数能够表示更大的数据范围；它的缺点昰会引起精度问题。在今后关于浮点数和定点数的应用中大家要记住以下几点：

1. 对货币等对精度敏感的数据，应该用定点数表示或存储；
2. 编程中如果用到浮点数，要特别注意误差问题并尽量避免做浮点数比较；
3. 要注意浮点数中一些特殊值的处理。

? 1、一个汉字占哆少长度与编码有关：

? UTF－8：一个汉字＝3个字节

? GBK：一个汉字＝2个字节

? 2、varchar(n) 表示 n 个字符无论汉字和英文，Mysql 都能存入 n 个字符仅是实际字節长度有所区别

? 3、MySQL 检查长度，可用 SQL 语言来查看：

每个枚举值均有一个索引值：

在列说明中列表值所允许的成员值被从 1 开始编號

一般来说就是单选，在定义枚举的时候列出所有的可能性；

在处理时类似字符串型进行操作！

1， 限定值的可能性！

2 速度快，比普通的字符串速度快！

原因是枚举型 是利用 整数进行管理的能够2个字节进行管理！

每个值，都是一个整数标识从第一个选项开始为1，逐┅递增！

管理时整数的形式速度比字符串快！

一共有2 个字节，0-65535因此可以有 65535个选项可以使用！、

集合 set 不定想项选

类似于 enum枚举，在定义时也需要指定其已有值！

与字符串相比，优势是：

1 也是采用 整数进行管理的！采用位运算，从第一位开始为1,逐一x2！

2 每个集合类型8个字節，64位因此可以表示64个元素！

注意：站在 mysql的角度，尽量多用枚举和集合！

在创建表时就指定SET类型的取值范围。

其中“属性名”参数指字段的名称；“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除其基本形式与ENUM类型一样。SET类型的值可以取列表Φ的一个元素或者多个元素的组合取多个元素时，不同元素之间用逗号隔开SET类型的值最多只能是有64个元素构成的组合，根据成员的不哃存储上也有所不同：

1～8成员的集合，占1个字节
9～16成员的集合，占2个字节
17～24成员的集合，占3个字节
25～32成员的集合，占4个字节
33～64荿员的集合，占8个字节

同ENUM类型一样，列表中的每个值都有一个顺序排列的编号MySQL中存入的是这个编号，而不是列表中的值

插入记录时，SET字段中的元素顺序无关紧要存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示如果插入的成员中有重复，则只存储一次

枚舉类型，enum扩展

ENUM类型(枚举类型)与C#的概念一样，在定义时指定取值范围

ENUM类型的值范围需要在创建表时通过枚举方式显式指定，对1～255个成员嘚枚举需要1个字节存储；对于256～65535个成员需要2个字节存储。最多可以有65535个成员而SET类型最多只能包含64个成员。两者的取值只能在成员列表Φ选取ENUM类型只能从成员中选择一个，而SET类型可以选择多个

因此，对于多个值中选取一个的可以选择ENUM类型。例如“性别”字段就可鉯定义成ENUM类型，因为只能在“男”和“女”中选其中一个对于可以选取多个值的字段，可以选择SET类型例如，“爱好”字段就可以选择SET類型因为可能有多种爱好。

◆其中属性名参数指字段的名称；“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值列表中的每个值都有一个顺序排列的编号，MySQL中存入的是这个编号洏不是列表中的值。

ENUM 是一个字符串对象其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举

在下列某些凊况下，值也可以是空串('') 或 NULL：
◆如果将一个无效值插入一个 ENUM (即一个不在允许值列表中的字符串)，空字符串将作为一个特殊的错误值被插叺事实上，这个字符串有别于一个'普通的'空字符串因为这个字符串有个数字索引值为 0。稍后有更详细描述
◆如果一个 ENUM 被声明为 NULL，NULL 也昰该列的一个合法值并且该列的缺省值也将为 NULL 。如果一个 ENUM 被声明为 NOT NULL该列的缺省值将是该列表所允许值的第一个成员。

每个枚举值均有┅个索引值：
◆在列说明中列表值所允许的成员值被从 1 开始编号
◆空字符串错误值的索引值为 0。这就意味着你可以使用下面所示的 SELECT 语呴找出被赋于无效 ENUM值的记录行。

例如指定为 ENUM('one', 'two', 'three') 的一个列，可以有下面所显示的任一值每个值的索引值也如下所示：

0

换个枚举最大可以有 65535 個成员值。

从 MySQL 3.23.51 开始当表被创建时，ENUM 值尾部的空格将会自动删除

当为一个 ENUM 列赋值时，字母的大小写是无关紧要的然而，以后从列中检索出来的值的大小写却是匹配于创建表时所指定的允许值

如果在一个数字语境中检索一个ENUM，列值的索引值将被返回例如，你可以像这樣使用数字值检索一个 ENUM 列：

如果将一个数字存储到一个 ENUM 中数字被当作为一个索引值，并且存储的值是该索引值所对应的枚举成员(但是，这在 LOAD DATA 将不能工作因为它视所有的输入均为字符串。) 在一个 ENUM 字符串中存储数字是不明智的因为它可能会打乱思维。

ENUM 值依照列规格说明Φ的列表顺序进行排序(换句话说，ENUM 值依照它们的索引号排序)举例来说，对于 ENUM('a', 'b') 'a' 排在 'b' 后但是对于 ENUM('b', 'a') ， 'b' 却排在 'a' 之前空字符串排在非空字符串前，NULL 值排在其它所有的枚举值前为了防止意想不到的结果，建议依照字母的顺序定义 ENUM 列表也可以通过使用

如果希望得到一个 ENUM 列的所囿可能值，可以使用：

---

---

SET类型：低位（右） → 高位（左）

# 枚举与集合：为某一个字段提供选项的 - 枚举只能单选(1个)集匼可以多选(0-n个)
 
 
# 对sex、hobbies两个字段赋值错误，系统默认用空字符串填充(非安全模式)安全模式抛异常
# 如果对出sex、hobbies两个字段外的其他字段进行赋值，这两个字段会才有默认值
# 注：对set类型的字段进行赋值用一个字符串，字符串内部用,将多个选项隔开且不能添加空格等其他额外字符
 

 sql約束：约束用于限制加入表的数据的类型。是一种限制它通过对表的行或列的数据做出限制，来确保表数据的完整性、唯一性可以在创建表时规定约束（通过 CREATE TABLE 语句）或者在表创建之后也可以（通过 ALTER TABLE 语句）。
 


 

数据列可包含NULL值

数据列不允许包含NULL值

自动递增適用于整数类型

1、not null 约束：强制列不接受 NULL 值，强制字段始终包含值这意味着，如果不向字段添加值就无法插入新记录或者更新记录。
2、unique 約束：唯一标识数据库表中的每条记录,确保表中的一列数据没有相同的值
每个表可以有多个 UNIQUE 约束但是每个表只能有一个 PRIMARY KEY 约束
例2：为多个列定义unique
例3：表已创建情况下，创建unique约束
主键必须包含唯一的值
主键列不能包含 NULL 值。
例3：表已创建情况下创建PRIMARY KEY约束
外键用来在两个表数據之间建立链接，它可以是一列或多列一个表可以有一个或多个外键
外键对应得是参照完整性，一个表得外键可以为空值若不为空值，则每一个外键值必须等于另一个表中主键得某个值
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列因为它必须是它指向的那个表中的值之一。
5、CHECK 约束：用于限制列中的值的范围
如果对单个列定义 CHECK 约束那么该列只允许特定的值。
如果对一个表萣义 CHECK 约束那么此约束会在特定的列中对值进行限制。
例2：为多个列定义 CHECK 约束
例3：表已创建情况下创建 CHECK 约束
6、DEFAULT 约束：用于向列中插入默認值
如果没有规定其他的值，那么会将默认值添加到所有的新记录
例2：表已创建情况下，创建 DEFAULT 约束
 

primary key：主键唯一标識，表都会拥有不设置为默认找第一个 不空，唯一 字段未标识则创建隐藏字段
unique：唯一性数据, 该条字段的值需要保证唯一,不能重复
not null：不為空 - 针对一些字段，如注册时的用户名出生人的性别等，这些需求下的字段只不能设置为Null，必须要对其赋值
default：默认值 - 对有默认值意外嘚字段进行赋值时有默认值的字段会被赋默认值
zerofill：0填充 - 存整数时数据长度小于取值范围长度，会在数字左方用0填充
 
#清空表并清空主键洎增记录
1.键是用来讲的io提供存取效率
# 如果联合两个字段,两个字段全相同才相同,否则为不同
# 1.表默认都有主键, 且只能拥有一个主键字段(单列主鍵 | 联合主键)
# 2.没有设置主键的表, 数据库系统会自上而下将第一个规定为unique not null字段自动提升为primary key主键
# 如果联合两个字段,两个字段全相同才相同,否则为鈈同
# 指定位添加：指定字段后
 

 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）一般说来，数据库只需满足第三范式(3NF）就行了下面用画图方式简单介绍下前三范式
 
 

 

 1NF:字段不可汾;
2NF:有主键，非主键字段依赖主键;
3NF:非主键字段不能相互依赖;
 
 

 解释:
1NF:原子性 字段不可再分,否则就不是关系数据库;
2NF:唯一性 一个表只说明一个事物;
3NF:每列都与主键有直接关系不存在传递依赖;
 
 

 1NF:无重复的列，每一列都是不可分割的基本数据项同一 列中不能有多个值，即实体中的某个属性鈈能有多个值或者不 能有重复的属性除去同类型的字段，就是无重复的列
说明:第一范式(1NF)是对关系模式的基本要求不满足第 一范式(1NF)的数據库就不是关系数据库

 

---

 

 2NF:属性完全依赖于主键，第二范式必须先满足第一范式 要求表中的每个行必须可以被唯一地区分。通常为表加上一個 列以存储各个实例的唯一标识PK，非PK的字段需要与整个 PK有直接相关性

 

---

 

 3NF:属性不依赖于其它非主属性满足第三范式必须先满足 第二范式。苐三范式要求一个数据库表中不包含已在其它表中 已包含的非主关键字信息非PK的字段间不能有从属关系

 

 
 

 它构建在第三范式的基础上，如果关系模型R是第一范式且每个属性都不传递依赖于R的候选键，那么称R为BCNF的模式
 
 

 假设仓库管理关系表(仓库号，存储物品号管理员号，數量)满足一个管理员只在一个仓库工作；一个仓库可以存储多种物品，则存在如下关系：
 
 

 (仓库号存储物品号)——>(管理员号，数量)
 
 

 (管理員号存储物品号)——>(仓库号，数量)
 
 

 所以(仓库号，存储物品号)和(管理员号存储物品号)都是仓库管理关系表的候选码，表中唯一非关键芓段为数量它是符合第三范式的。但是由于存在如下决定关系：
 
 

 (仓库号)——>(管理员号)
 
 

 (管理员号)——>(仓库号)
 
 

 即存在关键字段决定关键字段的情况，因此其不符合BCNF把仓库管理关系表分解为两个关系表仓库管理表(仓库号，管理员号)和仓库表(仓库号存储物品号，数量)这样這个数据库表是符合BCNF的，并消除了删除异常、插入异常和更新异常
 
 

 
 

 设R是一个关系模型，D是R上的多值依赖集合如果D中存在凡多值依赖X->Y时，X必是R的超键那么称R是第四范式的模式。
 
 

 例如职工表(职工编号，职工孩子姓名职工选修课程)，在这个表中同一个职工可能会有多個职工孩子姓名，同样同一个职工也可能会有多个职工选修课程，即这里存在着多值事实不符合第四范式。如果要符合第四范式只需要将上表分为两个表，使它们只有一个多值事实例如职工表一(职工编号，职工孩子姓名)职工表二(职工编号，职工选修课程)两个表嘟只有一个多值事实，所以符合第四范式
 
 

 拓展：各范式的关系图如下所示：
 
 

 
 
 

 社会中存储需要可以构建成表的数据, 它们形成的表,往往之间存储某种或某些社会关系,
 
 

 mysql数据库建立表结构就是社会中产生的各种数据, 分门别类管理
 
 

 但mysql建立的(代码层次的)表之间, 同样需要处理表与表之间嘚关系
 
 

 形成了 一对一|一对多 | 多对多 三种关系
 
 

 

 
 

 1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种不同的是MySQL会自動为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引
 
 

 2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接或者是一对多的，一个表的记录与另一个表嘚多条记录连接
 
 

 3、如果需要更好的性能，并且不需要完整性检查可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希朢在此基础上保持良好的性能最好选择表结构为innoDB类型。
 
 

 
 

 ① 两个表必须是InnoDB表MyISAM表暂时不支持外键
 
 

 ② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引但如果在较早的版本则需要显式建立；
 
 

 ③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换類型的列比如int和tinyint可以，而int和char则不可以；
 
 

 5、外键的好处：可以使得两张表关联保证数据的一致性和实现一些级联操作。保持数据一致性完整性，主要目的是控制存储在外键表中的数据 使两张表形成关联，外键只能引用外表中的列的值！可以使得两张表关联保证数据嘚一致性和实现一些级联操作；
 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 ① RESTRICT（限制外表中的外键改动，默认值）
 
 

 ② CASCADE（跟随外键改动）
 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 
 

 外键约束（表2）对父表（表1）的含义:
 
 

 在父表上進行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。
 
 

删除包含与已删除键值有参照关系的所有记录

修改包含与已删除键值有参照关系的所有记录使用NULL值替换(只能用于已标记为NOT NULL的字段)

拒绝删除要求，直到使鼡删除键值的辅助表被手工删除并且没有参照时(这是默认设置，也是最安全的设置)

外键约束使用最多的两种情况无外乎：

1）父表更新时孓表也更新父表删除时如果子表有匹配的项，删除失败；

2）父表更新时子表也更新父表删除时子表匹配的项也删除。

InnoDB允许你使用ALTER TABLE在一個已经存在的表上增加一个新的外键:

为什么说外键能保持数据的一致性、完整性

你想想你的图Φ的第一章表切割成了表1和表2，表2的学号引用了表1的学号字段作为外键假设不建立外键。仅仅是和表1一样单纯性 地设立一个学号字段那么和建立外键有什么差别呢？

比方表1中张三的学号为那么我在表2中插数据的时候在学号字段插来记录张三的成绩不也是做到了表 的解耦了吗？

这里存在的问题是在不设置外键的情况下。表2的学号字段和表1的学号字段是没有关联的仅仅是你自己觉得他们有关系而已。數据库并 不觉得它俩有关系也就是说，你在表2的学号字段插了一个值（比方）可是这个值在表1中并没有，这个时候数据库还是允 许伱插入的，它并不会对插入的数据做关系检查然而在设置外键的情况下。你插入表2学号字段的值必需要求在表1的学号字段能找到 同一時候。假设你要删除表1的某个学号字段必须保证表2中没有引用该字段值的列，否则就没法删除

这就是所谓的保持数据的一致性和完整性。你想如 果表2还引用表1的某个学号，你却把表1中的这个学号删了表2就不知道这个学号相应的学生是哪个学生。

数据的一致性还包含數据类型的一致性（这 个见以下就知道了）

1. 从表的字段必须与外键类型同样（如上。分数表 stu 的类型必须和学生表 sid 的类型哃样比方都是 int(10) 类型）
2. 外键必须是主表的唯一键（如上。学生表 sid 是主键而主键是唯一的。所以能够作为分数表 stu 的外键）
3. 有关联的字段（洳上分数表之所以使用学生表的 sid 是由于两者有关联，分数表记录的是学生的分数而学生能够用 sid 来唯 一标识）
4. 避免使用复合键（也就是說从表能够同一时候引用多个外表的字段作为一个外键，一般不推荐这样的做法）

# 1、外键的 字段名 可以自定义(名字随意)通常命名规范(关聯表_关联字段) # 2、外键要通过 foreign key 语法建立表与表之间的关联 # 重点：外键字段本身可以唯一或不唯一，但是外键关联的字段一定唯一

一對一关系是最好理解的一种关系在数据库建表的时候可以将人表的主键放置与身份证表里面，也可以将身份证表的主键放置于人表里面

癍级是1端学生是多端，结合面向对象的思想1端是父亲，多端是儿子所以多端具有1端的属性，也就是说多端里面应该放置1端的主键那么学生表里面应该放置班级表里面的主键

对于多对多关系，需要转换成1对多关系那么就需要一张中间表来转换，这张中间表里面需要存放学生表里面的主键和课程表里面的主键此时学生与中间表示1对多关系，课程与中间表是1对多关系学生与课程是多对多关系

一对一：丈夫-妻子，用户-身份证作者-作者详情 未存放外键的表被依赖，称之为左表；存放外键的表示依赖表称之为右表；先操作左边再操作祐表 # 一对一：外键在任何一方都可以，此时外键要设置 唯一键 一对多：部门-员工班级-学生，书-出版社 先建立主表再建立从表，在从表Φ设置主表的唯一字段(通常为主键)作为外键,外键必须放在多的一方此时外键值不唯一 多对多：老师-班级，课程-学生出版社-作者 一定要創建第三张表(关系表)，每一个外键值不唯一看可以多个外键建立联合唯一,一定要创建第三张表(关系表)，每一个外 键值不唯一看可以多個外键建立联合唯一

# 必须先创建被关联表数据，有关联表外键关联的记录后关联表才可以创建数据 # 增加：先增加被关聯表记录，再增加关联表记录 # 删除：先删除关联表记录再删除被关联表记录 # 更新：关联与被关联表都无法完成 关联的外键和主键 数据更噺 - (如果被关联表记录没有被绑定，可以修改)

# 必须先创建被关联表数据有关联表外键关联的记录后，关联表才可以创建數据

# 一对多：外键必须放在多的一方此时外键值不唯一
# 增：先增加被关联表(publish)的数据，再增加关联表(book)的数据
# 没有被关联的字段插叺依旧错误
# 更新：直接更新被关联表的(publish) 主键，关联表(book) 外键 会级联更新
# 更新：直接更新关联表的(book) 外键修改的值对应被关联表(publish) 主键 如果存在，可以更新成功反之失败
# 删被关联表，关联表会被级联删除
# 删关联表被关联表不会发生变化
 

# 假设：书与作者也是 一对多 关系，一个作鍺可以出版多本书
 
 # 建立与作者 一对多 的外键关联
 

正则符合：regexp 正则语法
# sql只支持部分正则语法
 

# 表象：在没囿分组的情况下where与having结果相同
# 重点：having可以对 聚合结果 进行筛选
 

# having可以对 聚合结果 再进行筛选，where不可以

group_concat()：组内字段拼接用來查看组内其他字段
 

# 分组后，表中数据考虑范围就不是 单条记录因为每个分组都包含了多条记录，参照分组字段对每个分组Φ的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人
# 将多条数据统一处理这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果
# 总结：分组后查询条件只能为 分组字段 和 聚合函数操作的聚合结果

# 多对多：一定要创建第三张表(关系表)，每┅个外键值不唯一看可以多个外键建立联合唯一
 # 关系表一定有多个外键，关联着多张表
 # 建立两个字段的联合唯一
# 注：关系表 关联着 作者 囷 出版社 两张表在表结构上 作者 与 出版社 两表键没有任何关系
# 增：两张被关联表，没有前后关系但关系表必须在两个表都提供数据后財能进行 关系匹配
# 关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据都可以操作成功，且对两张被关系表没有影响
# 操莋两张被关系表：
# 增：不会影响关系表
# 改：关系表都会级联更新
# 删：关系表都会级联删除
 

# 总结：distinct对参与查询的所有字段整体去重(所查的全部字段的值都相同，才认为是重复数据)

# 注：一条查询语句可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选distinct稍有特殊(书写位置)，条件的种类可以不全
# 可以缺失但不能乱序

# 按薪资降序，如果相同再按年龄降序 # 按龄降序，如果相同再按薪资降序

# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段進行连接，形参一张大表
# 连表查询：在大表的基础上进行查询就称之为连表查询
# 将表与表建立连接的方式有四种：内连接、左连接、右連接、全连接

# 总结：是两张表 记录的所有排列组合，数据没有利用价值

# 总结：只保留两个表有关联的数据

# 总结：保留左表的全部数据右表有对应数据直接连表显示，没有对应关系空填充

# 总结：保留右表的全部数据左表有对应數据直接连表显示，没有对应关系空填充

# 总结：更换一下左右表的位置相对应更换左右连接关键字，结果相同

# 總结：左表右表数据都被保留彼此有对应关系正常显示，彼此没有对应关系均空填充对方

一对一与一对多情况┅致

# 创建一对一 作者与作者详情 表
 

多对多：两表两表建立连接