1.1 MySQL学习路线

• 基础阶段：MySQL数据库的基本操作（增删改查），以及一些高级操作（视图、触发器、函数、存储过程等）。
• 优化阶段：如何提高数据库的效率，如索引，分表等。
• 部署阶段：如何搭建真实的环境系统，如服务器集群，负载均衡等。

 

1.2 数据库的基础

1.2.1 什么是数据库？

• 数据库：database，存储数据的仓库。
• 数据库（专业定义）：高效的存储和处理数据的介质（介质主要是两种：磁盘和内存）。

 

1.2.2 数据库的分类？

• 基于存储介质的不同
  • 关系型数据库（SQL）
  • 非关系型数据库（NoSQL）　　

 

1.2.3 不同的数据库阵营中的产品有哪些？

• 关系型数据库:
  • 大型数据库：Oracle、DB2。
  • 中型数据库：SQL Server、MySQL等。
  • 小型数据库：access等。　　
• 非关系型数据库：
  • memached
  • mogodb
  • redis　　

 

1.2.4 两种数据库阵营的区别？

• 关系型数据库：安全（保存磁盘基本不可能丢失），容易理解，比较浪费空间。
• 非关系型数据库：效率高，不安全（断电会丢失）。

 

1.3 关系型数据库

1.3.1 什么是关系型数据库？

• 关系型数据库：是一种建立在关系模型（数学模型）上的数据库。
• 关系模型：一种所谓建立在关系的模型，关系模型包括以下三个方面：
  • 数据结构：数据存储的问题，二维表（有行和列）。
  • 操作指令集合：所有SQL语句。
  • 完整性约束：表内数据约束，表与表之间约束（外键）。　　

 

1.3.2 关系型数据的设计？

• 从需要存储的数据需求中分析，如果是一类数据（实体，比如人、书本等）应该设计成一张二维表，表是由表头（字段名）和数据部分（实际存储的数据单元）组成，如下图所示。　　

表头	字段1	字段2
数据单元	数据1	数据2

 

 

 

• 以实际案例来进行处理：分析一个教学系统，讲师负责教学，教学生，在教室教学。
  • ①找出系统中的实体：讲师表，学生表，班级表。
  • ②找出实体中应该存在的数据信息：
    • 讲师：姓名，性别，年龄，身高，工资等。
    • 学生：姓名，性别，学好，学科等。
    • 班级：班级名字，教室编号等。　

 

• • • 关系型数据库：维护的是实体内部，实体与实体之间的联系。
    • 实体内部的联系：每个学生都有姓名，性别，学号，学科，年龄等信息。　　　　

姓名	性别	学号	学科	年龄
张三	男	001	java	23
李四	男	002	MySQL
王二		003	Linux	17

 

 

 

 

• • • 第二行的所有字段，都是描述张三这个学生（内部联系）；第二列只能存放性别（内部约束）。　　　　
    • 关系型数据库的特点之一：如果表中对应的某个字段没有值（数据），但是系统依然需要分配空间，所以关系型数据库比较浪费空间。　　　　

 

• • • 实体与实体之间的联系：每个学生属于某个班级，每个班级一定有多个学生（一对多）。　　　　
    • 学生表：　　　　　　

姓名	性别	学号	学科	年龄
张三	男	001	java	23
李四	男	002	MySQL
王二		003	Linux	17

 

 

 

 

• • • 班级表：　　　　

班级名称	教室编号
java001	A001
Linux002	B005

 

 

 

 

• • • 解决方案：在学生表中增加一个班级字段来指向班级（必须能够唯一的找到一个班级信息）　　　　

姓名	性别	学号	学科	年龄	所属班级
张三	男	001	java	23	java001
李四	男	002	MySQL		MySQL005
王二		003	Linux	17	Linux002

 

 

 

 

• • • 学生实体与班级实体之间的关系：实体与实体之间的关系。　　　　

 

1.4 关键字说明

• 数据库：database。
• 数据库系统：database system（DBS）:是一种虚拟系统，将多种内容关联起来的称呼。DBS=DBMS+DB。
• DBMS：数据库管理系统，database management system，专门管理数据库。
• DBA：数据库管理员，database administrator。
• 行/记录：row/record,本质是一个东西，都是指表中的一行（一条记录）。行是从结构角度，记录是从数据角度出发。
• 列/字段：column/Field，本质是一个东西。

 

1.5 SQL

• SQL：Structured Query Language，结构化查询语言（数据主要以查询为主）。
• SQL分为三个部分：
  • DDL：Data Definition Language，数据定义语言。用来维护存储数据的结构（数据库，表），代表指令：create、drop、alter等。
  • DML：Data Manipulation Language，数据操作语言，用来对数据进行操作（数据表中的内容），代表指令：Insert、delete、update等。其中DML内部又单独进行了一个分类：DQL（Data Query Language：数据查询语言，如select）。
  • DCL：Data Control Language，数据控制语言，主要负责权限管理，代表指令：grant、revoke等。　　
• SQL是关系型数据库的操作指令，SQL是一种约束，但不强制（类似于W3C），所以不同的关系型数据库产品（Oracle，MySQL等）内部可能有一些细微的差别。

 

1.6 MySQL数据库

• MySQL数据库是一种C/S结构的软件：客户端/服务端，如果想访问服务器必须通过客户端（服务器一直运行，客户端在需要使用的时候运行）。
• 交互方式：
  • ①客户端连接认证：连接服务器，认证身份。　　

• • ②发送SQL指令。　
  • ③服务器接收SQL指令，处理SQL指令，返回操作结果。
  • ④客户端接收结果，显示结果。　

• • ⑤断开连接（释放资源：服务器并发限制）。　　

 

 

1.7 MySQL服务器对象

•  没有办法完全了解服务器内部的内容：只能粗略的去分析数据库服务器的内部结构。
•  将MySQL服务器内部对象分成了四层：
  • 系统（DBMS）
  • 数据库（DB）
  • 数据表（Table）
  • 字段（Field）　　

 

1.8 SQL的基本操作

•  基本操作：CRUD。
• 将SQL的基本操作根据操作对象进行分类，分为三类，如下所示：
  • 库操作。
  • 表操作（字段）。
  • 数据操作。　　

 

1.9 库操作 

•  对数据库的增删改查。

1.9.1 新增数据库 

•  基本语法

create database 数据库名字 [库选项];

• 库选项：用来约束数据库，分为两个选项。
  • 字符集设定：charset/character set 具体字符集（数据存储的编码格式）。
  • 校对集设定：collate 具体校对集（数据比较的规则）。　　　　

 

• 示例：创建一个数据库。

-- 单行注释，也可以用#
# 创建数据库
CREATE DATABASE mydatabase CHARACTER SET utf8;

 

•  其实，数据库名字不能使用关键字（已经被使用的字符）或保留字（将来可能会使用的字符）。

 

• 当创建数据库的SQL语句执行之后，发生了什么？
  • ①在数据库系统中，增加了对应的数据库信息。
  • ②会在保存数据的文件夹下：Data目录，创建一个对应数据库名字的文件夹。　　

• • ③每个数据库下都有一个opt文件:保存了库选项。　　

 

1.9.2 查看数据库

• 基本语法
  • ①查看所有数据库。　　

-- 查看所有数据库
show databases;

• • ②查看指定部门的数据库：模糊查询。　　

-- 查看指定部分的数据库
show databases like ‘pattern‘; -- pattern是匹配模式
%:表示匹配多个字符
_:表示匹配单个字符 

• • ③查看数据库的创建语句。　　

-- 查看数据库的创建语句
show create database 数据库名字;

 

• 示例：查看所有数据库

 

• 示例：查看指定名称为test的数据库。

 

• 示例：查看含有t名称的数据库。

 

• 示例：显示mydatabase数据库的创建语句。

 

1.9.3 更新数据库

• 数据库名字是不可以改变的，不然如何定位哪一个数据库，换句话说，原来有一个数据库名为test，你现在将其它数据库改为test，这样好吗？
• 数据库的修改仅限库选项：字符集和校对集（校对集依赖于字符集）。

 

• 基本语法

alter database 数据库名字 [库选项]。
character set [字符集]
collate 绞对集

 

• 示例：修改mydatabase数据库的默认编码。

 

1.9.4 删除数据库

• 所有的操作中：删除是最简单的。
• 基本语法

-- 删除数据库
drop database 数据库名字;

 

• 示例：删除mydatabase数据库。

 

• 当删除数据库语句执行之后，发生了什么？
  • 在数据库内部看不到对应的数据库。
  • 在对应的数据库存储的文件夹内，数据库名字对应的文件夹也被删除（级联删除：里面的数据库表一起被删除）。　　

 

• 注意：数据库的删除不是闹着玩的，不要随意的删除，应该先进行备份操作，然后才考虑是否删除（删除不可逆）。

 

1.10 表操作

•  表和字段是密不可分的。

 

1.10.1 新增数据表

• 基本语法

-- 新增数据表
create table [if not exists] 数据表名字(
    字段名字 数据类型,
    字段名字,数据类型   -- 最后一行不不要逗号
)[表选项];
if not exists：如果表名不存在，那么就创建，否则不执行创建。
 表选项：控制表的表现。
 字符集：charset/character set 具体字符集;--保证表中数据存在的字符集。
 校对集：collate 具体校对集。
 存储引擎：engine 具体的存储引擎(innodb和myisam)

 

• 示例：创建一个名为student的表。

• • 任何一个表的设计都必须指定数据库。　　
  • 解决方案：
    • ①显示指定表所在的数据库。　　

-- 显示的指定表所在的数据库
create table 数据库名.表名(
   字段1 数据类型,
   字段2 数据类型
);

• • • ②隐式的指定表所属数据库：先进入到某个数据库环境，然后这样创建的数据库表自动归属到某个数据库。　　　　

-- 进入到数据库环境
use 数据库名字;
-- 创建数据表
create table 数据表名(
  字段1  数据类型,
  字段2  数据类型
)；

 

• 当创建数据库的SQL指令执行之后，到底发生了什么？
  • 指定数据库下已经存在对应的表。
  • 在数据库对应的文件夹下，会产生对应表的结构文件（和存储引擎有关）。　　

 

1.10.2 查看数据表

• 数据库能查看的方式，表都可以查看。

 

• 基本语法
  • 查看所有表　　

-- 查看所有表
show tables

• • 查看部分表　　

-- 查看部分表
show tables like ‘pattern‘;

• • 查看表的创建语句　　

-- 查看表的创建语句
show create table 表名;

• • 查看表结构：查看表中的字段信息　　

-- 查看表结构
①desc 表名;
②describe 表名;
③show columns from 表名;

 

• 示例：查看所有表

 

• 示例：查看创建student表的语句。

 

• 示例：查看表结构。

 

1.10.3 修改数据表

• 表本身存在，还包含字段，所以表的修改分为两个部分：修改表本身和修改字段。

 

• 修改表本身：
  • 表名　　
    • 基本语法：　　　　

-- 修改表名
rename table 老表名 to  新表名;

• • • 示例：将student表的名字改为stu.　　　　

 

• • 表选项:字符集，校对集和存储引擎。　　

alter table 表名 [表选项];

• • • 示例：修改stu表的编码为gbk　　　　　　　　　　

 

• 修改字段
  • 新增字段　　　　

alter table 表名 add [column] 字段名  数据类型[列属性][位置];
位置：字段名可以存在表中的任意位置
     first：第一个位置
     after:在哪个字段之后，after 字段名，默认在最后

 

• • 修改字段　　

alter table 表名 modify 字段名 数据类型[列属性] [位置]；

 

• • 重命名字段　　

alter table 表名 change 旧字段名  新字段名 数据类型[列属性][位置];

 

• • 删除字段　　

alter table 表名 drop 字段名;

 

1.10.4 删除数据表

• 基本语法

-- 删除表
drop table 表名1,表名2,……;

 

• 当删除数据表的指令执行之后发生了什么？
  • ①在表空间中，没有了指定的表（数据也没有了）。
  • ②在数据库对应的文件夹下，表对应的文件（与存储引擎有关）也会被删除。　　

 

1.11 数据操作

1.11.1 新增数据

• 有两种方案
  • ①给全表字段插入数据，不需要指定字段列表：要求数据的值出现的位置必须和表中设计的字段出现的顺序一致，凡是非数值的数据，都需要使用引号包裹。　　

insert into 表名 values (值列表)[,(值列表)]; -- 可以一次性插入多条记录

• • ②给部分字段插入数据：需要选定字段列表，字段列表出现的顺序和字段的顺序无关，但是值列表的顺序必须和选定的字段的顺序一致。

insert into 表名 (字段列表) values (值列表);　

 

1.11.2 查看数据表

• 基本语法

select */字段列表 from 表名  [where 条件] ;

 

• 示例：查看学生的所有信息

 

• 示例：查看学生的姓名和性别

 

1.11.3 更新数据

• 基本语法

update 表名 set 字段1=值1,字段2=值2,……[where 条件];

 

• 示例：更新id=1的年龄为32

 

1.11.4 删除数据

• 基本语法

delete from 表名 [where 条件];

 

• 示例：删除name为zhangsan的学生信息

 

1.12 中文数据问题

• 中文数据问题本质是字符集问题。
• 计算机只识别二进制，人类更多的是识别符号，所以需要有个二进制和字符的对应关系（字符集）。

 

• 客户端向服务器插入中文数据：没有成功

• 原因：\xD5\xC5\xC8\FD 代表的是“”张三”在当前编码（字符集）下的二进制转换为十六进制，两个汉字-->四个字节（GBK）。

• 报错：服务器没有识别对应的四个字节，服务器认为数据是utf8的，一个汉字对应三个字节；所以服务器读取三个字节转换成汉字，失败了;剩余的再读三个字节，最终失败。
• 所有的数据库服务器认为（表现）的一些特性是通过服务器端的变量来保存的，系统先读取自己的变量，看看应该怎么表现。

 

• 查看服务器识别的所有字符集

show character set;

 

• 服务器默认的和客户端打交道的字符集。

show variables like ‘character_set%‘;

• 问题根源：客户端数据只能是GDK，而服务器认为是utf8。
• 解决方案：改变服务器。默认的接收字符集为GBK。

-- 修改服务器认为的客户端数据的字符集为GBK
set character_set_client=gbk;

• 插入中文的效果

• 查看数据效果：依然是乱码

• 原因：数据来源是服务器，解析数据是客户端（客户端只识别GBK：只会两个字节一个汉字），但是服务器给的数据是UTF8，所以导致乱码。
• 解决方案：修改服务器给客户端的数据字符集为GBK。

set character_set_results=gbk;

• 查看数据效果

 

• set 变量=值；这样修改只是会话级别（当前客户端当次连接有效，关闭失效）

• 设置服务器对客户端的字符集的认知，如果按照上面的方式，太麻烦了。可以使用快捷方式。

set names 字符集;

 

1.13 校对集问题

• 校对集：数据比较的方式。
• 校对集有三种方式：
  • _bin：binary，二进制比较，取出二进制位，一位一位的比较。区分大小写。
  • _cs：case sensitive，大小写敏感，区分大小写。
  • _ci：case insensitive.大小写不敏感，不区分大小写。　　

 

• 查看数据库所支持的绞对集

show collation;

 

• 校对集应用：只有当数据产生比较的时候，校对集才会生效。

 

1.14 Web乱码问题

• 动态web由三个部分构成：浏览器、web服务器（如Tomcat等）、数据库服务器，三个部分都有自己的字符集（尤其是中文），数据需要在三个部分之间来回传递，很容易产生乱码。
• 如果解决乱码问题：统一编码（三码合一）。

【第二章】

2.1 数据类型（列类型）

• 所谓的数据类型：对数据进行统一的分类，从系统的角度出发是为了能够使用统一的方式进行管理，更好的利用有限的空间。
• SQL中将数据类型分成了三大类：

2.2 数值类型

• 数值类型数据：都是数值。
• 系统将数值型分为整数类型和浮点数类型。

 

2.2.1 整数类型

• 在SQL中因为更多的要考虑如何节省磁盘空间，所以系统将整数类型又细分成了5类：
  • tinyint 　　迷你整型，使用一个字节存储，表示的状态最多为256种。
  • smallint      小整型，使用2个字节存储，表示的状态最多为65536种。
  • mediumint 中整型，使用3个字节存储。
  • int              标准整型，使用4个字节存储。
  • bigint         大整型，使用8个字节存储。　　

 

• 创建一张整型表

create table my_int(
   int_1 tinyint,
   int_2 smallint,
   int_3 mediumint,
   int_4 int,
   int_5 bigint 
)charset utf8;

• 插入数据

-- 插入数据
insert into my_int(int_1,int_2,int_3,int_4,int_5) values(1,2,3,4,5);

• 查看数据

select * from my_int;

 

• SQL中的数值类型全部都是默认有符号的：分正负。
• 有的时候，需要使用无符号数据，需要给数据类型限定为int unsigned;--无符号

 

• 给my_int表增加一个无符号类型。

alter table my_int add int_6 int unsigned;

•  插入无符号数据

insert into my_int (int_6) values (0);

• 如果插入的是负数

 

• 查看表结构的时候，发现每个字段的数据类型之后都会自带一个括号，里面有指定的数字

• 显示宽度：没有特别的含义，只是默认的告诉用户可以显示的形式而已，实际上用户是可以控制显示宽度，但是这种控制不会改变数据本身的大小。

 

• 增加一个int_7 int(1) 的字段

alter table my_int add int_7 int(1) unsigned ;

• 插入数据

 

• 显示宽度的意义：在于当数据不够显示宽度的时候，会自动让数据变成对应的显示宽度，通常需要搭配一个前导0(zerofill:零填充，零填充会导致数值变成无符号)来增加宽度，不改变值大小。

alter table my_int add int_8 int(2) zerofill;

 

• 零填充+显示宽度的效果

 

• 零填充的意义：保证数据格式。

 

2.3 小数类型

• 小数型：带有小数点或者范围超出整数类型的数值类型。

 

• SQL中：将小数类型细分为两种：浮点型和定点型。
  • 浮点型：小数点浮动，精度有限，会丢失精度。
  • 定点型：小数点固定，精度固定，不会丢失精度。　　

 

2.3.1 浮点型

• 浮点型数据会因为超出范围之后，丢失精度（自动四舍五入）。
• 浮点型：理论上有两种精度。
  • float：单精度，占用4个字节存储数据，精度范围大概在7位左右。
  • double：双精度，占用8个字节存储和数据，精度范围大概在15位左右。　　

 

• 创建浮点数表：浮点的使用方式，直接float表示没有小数部分；float(M,D)：M代表总长度，D代表小数部分长度，整数部分长度为M-D。

-- 浮点数表
create table my_float(
    f1 float,
    f2 float(10,2),--10位在精度范围之外
    f3 float(6,2)--6位在精度范围之内
);

• 插入数据：可以直接小数，也可以科学计数法

 

2.3.2 定点型

• 定点型：绝对的保证整数部分不会被四舍五入（不会丢失精度），小数部分有可能。

 

2.4 时间日期类型

 

2.5 字符串类型

• 在SQL中，将字符串类型分成了6类：char，varchar，text，blob，enum和set。

 

• 定长字符串
  • char:磁盘（二维表）在定义结构的时候，就已经确定了最终数据的存储长度。
  • char(L)：L代表length，可以存储的长度，单位为字符。最大长度值可以为255。char(4)：在utf8环境下，需要4 * 3 = 12 个字节。　　

 

• 变长字符串
  • varchar：在分配空间的时候，按照最大的空间分配，但是实际上最终用了多少，是根据具体的数据来确定。
  • varchar(L)：L代表length，理论长度是65536个字符，但是会出处1到2个字节来确定存储的实际长度。varchar(10):在utf8环境下，存储10个汉字，需要10 * 3 + 1 = 31 字节。
  • 注意的是，在实际开发中，如果字符的长度超过255个，我们会使用text或blob来代替varchar。　　

 

• 如何选择定长或变长字符串？
  • 定长的磁盘空间比较浪费，但是效率高；换言之，如果数据基本上确定长度都一样，就使用定长，如身份证号码，电话号码，手机号码等。
  • 变长的磁盘空间比较节省，但是效率低；换言之，如果数据不能确定长度（不同的数据，长度有变化），如姓名，地址等。　　

 

• 文本字符串
  • 如果数据量非常大，通常超过255个字符，就会使用文本字符串。
  • 文本字符串根据存储的格式进行分类：text和blob。
    • text：存储文字。
    • blob：存储二进制数据（通常不用）。　　　　

 

• 枚举字符串
  • 枚举：enum，实现将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个。
  • 枚举的使用方式：
    • 定义：enum(可能出现的元素列表);
      • 如：enum(‘男‘,‘女‘,‘不男不女‘,‘妖‘)。
    • 使用：存储数据，只能存储上面定义好的数据。　　　　　　　　　　

 

• 集合字符串
  • 集合和枚举很类似：实际存储的是数值，而不是字符串。
  • 集合的使用元素：
    • 使用：set(元素列表);
    • 使用：可以使用元素列表中的元素（多个），使用逗号分隔。　　　　　　

 

2.6 MySQL记录长度

• MySQL中规定：任何一条记录最长不能超过65535个字节。（varchar永远达不到理论值）

 

2.7 列属性

• 列属性：真正约束字段的是数据类型，但是数据类型的约束很单一。需要有一些额外的约束，来保证数据的合法性。

 

• 列属性有很多：null/not null；default；primary key；unique key，auto_increment,comment等

 

2.7.1 空属性

• 两个值：null（默认的）和not null（不为空）

 

• 数据库基本上字段默认为null，但是在实际开发的时候，尽可能的要保证所有的数据不应该为null，因为①空数据没有意义，②空数据没有办法参与运算。

 

2.7.2 描述属性

• 列描述：comment，描述：没有实际含义，是专门用来描述字段的，会根据表创建语句保存。

 

2.7.3 默认值

• 默认值： 某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。
• 用法：default 默认值;

 

【第三章】

3.1 字段属性

• 主键、唯一键和自增长。

3.1.1 主键

• 主键：primary key，一张表中只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复。
• 一张表只能有最多一个主键。

 

3.1.1.1 增加主键

• 在SQL操作中欧有多种方式可以给表增加主键，大体分为三种：

 

• 方案1：在创建表的时候，直接在字段之后，跟primary key关键字(主键本身不能为空)。

-- 增加主键
create table my_pri(
   id int primary key,
   name varchar(20) not null comment ‘姓名‘
)charset utf8;

• • 优点：非常直接；缺点：只能使用一个字段作为主键。　　

 

• 方案2：在创建表的时候，在所有的字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以是复合主键。

create table my_pri2(
   number char(10) comment ‘学号‘,
   course char(10) comment ‘课程代码：3901+0000‘,
   score tinyint unsigned default 60 comment ‘成绩‘,
   -- 增加主键限制：学号和课程代码应该是唯一的
   primary key (number,course)
)charset utf8;

 

• 方案3：当表已经创建好之后，额外追加主键：可以通过修改表字段属性，也可以直接追加。

alter table 表名 add primary key (字段列表);

• 前提：表中字段对应的数据本身是独立的（不重复）。

 

3.1.1.2 主键约束

• 主键对应的字段中的数据不允许重复，一旦重复，数据操作失败（增和该）。

 

3.1.1.3 主键更新 & 删除主键

• 没有办法更新主键：主键必须先删除，才能增加。

drop table 表名 drop primary key;

 

3.1.1.4 主键分类

• 在实际创建表的过程中，很少使用真实业务数据作为主键字段（业务主键，如学号、课程号）。
• 大部分的时候，是使用逻辑性的字段（字段没有业务含义，值是什么都没有关系），将这种字段主键称为逻辑主键。

 

3.1.2 自动增长

• 自增长：当对应的字段，不给值，或者给默认值，或者给null的时候，会自动的被系统触发，系统会从当前字段中的已有的最大值+1操作，得到一个新的不同的字段。

• 通常自动增长和主键搭配。

 

3.1.2.1 新增自增长

• 自增长特点：auto_increment
  • 任何一个字段要做自增长必须前提是本身是一个索引（key一栏有值）。

• • 自增长必须是数字。　　

• • 一张表最多只能有一个自增长。　　

 

3.1.2.2 自增长使用

• 当自增长被给定的值为null或者默认值的时候，会触发自动增长。

 

3.1.2.3 修改自增长

• 自增长如果是涉及到字段改变：必须先删除自增长，后增加（一张薄只能有一个自增长）。

 

• 修改当前自增长已经存在的值：修改只能比当前已有的自增长的最大值大，不能小（小不生效）。

-- 修改表选项的值
alter table 表名 auto_increment = 值;

 

• 思考：为什么自增长是从1开始？为什么每次都是自增1呢？
  • 所有系统的变现（如字符集、校对集）都是系统内部的变量进行控制的。
  • 查看自增长对应的变量：show variables like ‘auto_increment%‘;　　

• 可以修改变量实现不同的效果，但是修改是针对整个数据库的修改，而不是单张表，不建议修改。

-- 不建议修改
set auto_increment_increment = 5;

 

3.1.2.4 删除自增长

• 自增长是字段的一个属性：可以通过modify来进行修改（保证字段没有auto_increment即可）

alter table 表名 modify 字段 类型;

 

3.1.3 唯一键

• 一张表往往有很多字段需要具有唯一性，数据不能重复；但是一张表中只能有一个主键，所以唯一键就可以解决表中有多个字段需要唯一性的约束。

 

• 唯一键的本质和主键差不多，唯一键默认的允许自动为空，而且可以多个为空。

 

3.1.3.1 增加唯一键

• 基本上和主键差不多：三种方案。

 

• 方案一：在创建表的时候，字段之后直接跟unique/unque key。

 

• 方案二：在所有的字段之后增加unique key（字段列表）；--复合唯一键

 

• 方案三：在创建表之后增加唯一键。

 

3.1.3.2 唯一键约束

• 唯一键与主键本质相同，唯一的区别就是唯一键默认允许为控控，而且是多个为空。
• 如果唯一键也不允许为空，那么与主键的约束作用是一致的。

 

3.1.3.3 更新唯一键&删除唯一键

• 更新唯一键：先删除唯一键，再增加唯一键。

 

• 删除唯一键，默认使用字段名作为索引名字

alter  table 表名 drop index 索引名字;

 

3.2 索引

• 几乎所有的索引都是建立在字段之上。

 

• 系统根据某种算法，将已有的数据（未来可能新增的数据），单独建立一个文件：文件能够实现快速的匹配数据，并且能够快速的找到对应表中的记录。

 

• 索引的意义：
  • 提升查询数据的效率。
  • 约束数据的有效性（唯一性等）。　　

 

• 增加索引的前提条件：索引本身会产生索引文件（有时候可能比数据文件还打），会非常消耗磁盘空间。

 

• 如果某个字段需要作为查询条件经常使用，那么可以使用索引。
• 如果某个字段需要进行数据的有效性约束，也可以使用索引（主键、唯一键）。

 

• MySQL中提供了多种索引。
  • 主键索引 primary key
  • 唯一索引 unique key
  • 全文索引 fulltext index
  • 普通索引 index

 

• 全文索引：针对文章内部的关键字进行索引。
• 全文索引最大的问题在于如何确定关键字。

 

3.3 关系

• 将实体与实体的关系，反应到最终数据库表的设计上来。将关系分成三种：一对一，一对多和多对多。

 

3.3.1 一对一

• 一对一：一张表的一条记录一定只能和另一张表的一条记录进行对应；反之亦然。

 

• 学生表：姓名、性别、年龄、身高、体重、婚姻、籍贯、家庭住址、紧急联系人。

id	姓名	性别	年龄	身高	体重	籍贯	家庭住址	紧急联系人	婚姻

 

 

 

 

• 表设计成以上这种形式，是符合要求的。其中姓名、性别、年龄、身高、体重是常用数据，但是婚姻、籍贯、住址和紧急联系人属于不常用数据。如果每次查询都是查询所有数据，不常用的数据就会影响效率，实际又不用。

 

• 解决方案：将常用的和不常用的信息分离存储，分成两张表。
• 学生常用信息表

id	姓名	性别	年龄	身高	体重

 

 

 

• 学生不常用信息表

籍贯	家庭住址	紧急联系人	婚姻

 

 

 

• 但是如果我有时候又需要使用不常用信息怎么办？
  • 为了保证不常用信息和常用信息一定能够对应上，我们唯有找到一个具有唯一性的字段来共同连接两张表。--主键字段　　
• 所以，学习不常用信息表修改如下　　

id	婚姻	籍贯	家庭住址	紧急联系人

 

 

 

 

• 综上所述，一个常用表中的一条记录，永远只能在一张不常用表中匹配一条记录；反过来，一个不常用表中的一条记录在常用表中也只能匹配一条记录。

 

3.3.2 一对多

• 一对多：一张表中的一条记录可以对应另外一张表中的多条记录，反过来，另一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多或多对一。

 

• 学生和班级的关系：
• 学生表

id	姓名	年龄	性别

 

 

 

• 班级表

id	班级名字

 

 

 

• 我们知道，一个学生只能属于一个班级，而一个班级却有多个学生，一对多。

 

• 但是以上设计：解决了实体的设计表问题，但是没有解决关系问题：学生找不到班级，班级没有学生。

 

• 解决方案：在某一张表中增加一个字段，能够找到另一张表中的记录。如何做到呢？在学生表中增加一个字段指向班级表，因为学生表的记录只能匹配到一个班级的记录。
• 学生表

id	姓名	性别	年龄	班级id
				班级主键
				班级主键

 

 

 

3.3.3 多对多

• 多对多：一张表（A）的一条记录能够对应另外一张表（B）的多条记录；同时B表中的一条记录也能对应A表中的多条记录。

 

• 老师教学：；老师和学生
• 老师表

t_id	姓名	性别	工资
1	A	男	6000
2	B	女	8000

 

 

 

• 学生表

s_id	姓名	性别	分数
1	张三	男	59
2	李四	男	95

 

 

 

 

• 以上设计方案：实现了实体的设计，但是没有维护实体的关系。
• 一个老师教过多个学生，一个学生也被多个老师教多。

 

• 解决方案：不管在那张表中增加字段，都会出现问题：该字段要保存多个数据，而且是与其它表有关系的字段，不符合表的设计规范，增加一张新表，专门维护两张表之间的关系。
• 中间关系表：老师与学生的关系

T_ID	S_ID
1	1
1	2
2	1
2	2

 

 

 

 

 

• 增加了中间表之后：中间表与老师表形成了一对多的关系，而且中间表是多表，维护了能够唯一找到一表的关系。

 

3.4 范式

• 范式：是离散数学中的知识，是为了解决一种数据的存储与优化的问题（保存数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储，起终极目标是为了减少数据冗余）。

 

• 范式：是一种分层结构的规范，分为6层：每一层都比上一层更加严格。

 

• 六层范式：1NF、2NF、3NF、4NF、5NF和6NF，其中1NF要求最低，6NF要求最高。

 

• MySQL属于关系型数据库：有空间浪费，而范式致力于节省存储空间。所以，在设计数据库的时候，会利用范式来指导设计。但是数据库不单是解决空间问题，还要保证效率；而范式只为解决空间问题，所以数据库的设计不可能完全按照范式的要求实现，所以一般情况下，只有前三种范式需要满足。

 

• 范式在数据库的设计当中是有指导意义，但是不是强制规范。

 

3.4.1 第一范式 1NF

• 第一范式：在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前还需要额外的处理（拆分），那么就说表的设计不满足第一范式。
• 第一范式：属性不可再分，字段保证原子性。

 

• 讲师代课表

讲师	性别	班级	教室	代课时间	代课时间（开始时间、结束时间）
朱元璋	男	java001班	B23	30天	2014-02-17 2014-05-05
朱元璋	男	java002班	C15	30天	2014-05-05 2014-05-30
李世民	男	Linux003班	C15	15天	2016-02-21 2014-06-20

 

 

 

 

• 上表的设计不存在问题，但是如果需求是将数据查出来之后，要求一个老师从什么时候开始上课，到什么时候结束课程，此时需要将代课时间进行拆分，不符合第一范式，因为数据不具有原子性，可以再拆分。

 

• 解决方案：：将代课时间拆成两个字段就可以了。

 

3.4.2 第二范式 2NF

• 第二范式：在数据表设计的过程中，如果有复合主键，且表中有字段并不是由整个主键来确定，而是依赖主键的某个字段（主键的部分），存在字段依赖主键部分的问题，称之为部分依赖。第二范式就是要解决表设计不允许出现部分依赖。

姓名	性别	班级	教室	代课时间	开始时间	结束时间
朱元璋	男	java001班	C01	30天	2014-02-27	2014-05-05
朱元璋	男	java002班	B23	30天	2014-03-21	2014-05-30
李世民	男	Linux003班	A15	15天	2014-06-01	2014-06-20

 

 

 

 

• 在上面的表中：因为讲师没有办法作为独立主键，需要结合班级才能作为主键（复合主键：一个老师在一个班永远只带一个阶段的课）。代课时间、开始时间和结束时间字段都与当前的代课主键（讲师和班级）：但是性别并不依赖班级，教室不依赖讲师，性别只依赖讲师，教室只依赖班级，出现了性别和教室依赖主键的一部分：即部分依赖。

 

• 解决方案：取消复合主键，使用逻辑主键。

 

3.4.3 第三范式 3NF

• 要满足第三范式，必须满足第二范式。
• 第三范式：理论上讲，一张表中的所有字段都应该直接依赖主键（逻辑主键除外），如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键依赖，最终实现依赖主键，把这种不是直接依赖主键，而是依赖非初见字段的依赖关系称之为传递依赖。第三范式就是解决传递依赖的问题。

 

• 讲师代课表

id	讲师	性别	班级	教室	代课时间	开始时间	结束时间
1	朱元璋	男	java01班	A03	30天	2014-02-27	2014-05-05
2	朱元璋	男	Linux02班	B23	30天	2014-03-21	2014-05-30
3	李世民	男	java001班	A03	30天	2014-06-01	2014-06-20

 

 

 

 

• 以上设计方案中，性别依赖讲师存在，讲师依赖主键；教室依赖班级，班级依赖主键；性别和教室都存在传递依赖。

 

• 解决方案：将存在传递依赖的字段，一级依赖的字段本身单独取出，形成一个单独的表，然后在需要对应的信息的时候，使用对应的实体表的主键加起来。

id	讲师id	班级id	代课时间	开始时间	结束时间
1	1	10	30天	2014-02-27	2014-05-05
2	1	12	30天	2014-03-21	2014-05-30
3	2	12	30天	2014-06-01	2014-06-20

 

 

 

 

id	讲师	性别
1	朱元璋	男
2	李世民	男

 

 

 

id	班级	教室
10	java01班	A03
12	Linux02班	B23

 

 

 

3.5 逆规范化

• 有的时候，在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息。理论上讲，的确可以获取到想要的数据，但是就是效率低一点。所以我们会刻意在某些表中，不去保存另外表的主键（逻辑主键），而是直接保存想要的数据信息，这样一来，在查询数据的时候，一张表可以直接提供数据，而不需要多表查询（效率低），但是会导致数据冗余增加。

 

【第四章】

 

4.1 蠕虫复制

 

• 蠕虫复制：从已有的数据中去获取数据，然后将数据又进行新增操作,数据成倍增加。 

• 表创建高级操作：从已有创建新表（复制表结构）

 

create table 表名 like 数据库.表名;

 

 

 

 

• 蠕虫复制：先查出数据，然后将查出的数据新增一遍。

 

insert into 表名 [(字段列表)] select 字段列表/* from 数据表名;

 

 

 

• 蠕虫复制的意义
  • 从已有表拷贝数据到新表中
  • 可以迅速的让表中的数据膨胀到一定的数量级：测试表的压力以及效率。　　

 

 

 

4.2 查询

 

• 基本语法

 

select 字段列表/* from 表名 [where 条件];

 

• 完整语法

 

select [select 选项] 字段列表 [字段别名]/* from 数据源 [where 条件子句] [group by 子句] [having 子句][order by子句][limit 子句];

 

• • select选项：select对查出来的结果的处理方式。
    • all ：默认的，保留所有的结果。
    • distinct：去重，查出来的结果，将重复给去除（所有字段都相同）。　　 　　

 

 

 

• 字段别名
  • 当数据进行查询出来的时候，有时间名字并不一定就满足需求（多表查询的是偶，会有同名字段），需要对字段进行重命名。　　

 

字段  [as]  别名;

 

 

 

• 数据源
  • 数据源：数据的来源，关系型数据库的来源都是数据表，本质上只要保证数据类似二维表，最终都可以作为数据源。　
  • 数据源分为：
    • 单表数据源

 

select * from 表名;

 

• • • 多表数据源(笛卡尔积)　　　　

 

select * from 表名1,表名2,……;

 

 

 

• where子句
  • 用来判断数据，筛选数据。
  • where子句返回结果：0（代表false）或1（代表true）。　
  • 判断条件：
    • 比较运算符：>、<、>=、<=、!=、<>、like、between and、in、not in
    • 逻辑运算符：and、or、not　
  • where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件，从磁盘取出一条记录，开始进行where判断，判断的结果如果成立就保存到内存，如果失败直接放弃。　　

 

 

 

• group by子句
  • group by：分组，根据某个字段进行分组（相同的放一组，不同的分到不同的组）　
  • 分组的意义：是为了统计数据(按组统计：按分组字段进行统计)。　

 

 

 

• having子句
  • having子句：where是针对磁盘数据进行判断，进入到内存之后，会进行分组操作，分组结果就需要having来处理。
  • having能够使用字段别名：where不能，where是从磁盘　　

 

 

&n