作为一个测试人员，需要了解数据库的基本知识；
数据库知识体系：
在PHP阶段，分为三个阶段：
第一个阶段：mysql数据库的基本操作（增，删，改，查），以及一些高级操作（视图，触发器，函数，存储过程），和PHP操作msql数据库；
第二阶段：如何提高数据库效率，如索引，分表；
第三段：如何搭建真实的环境系统，如服务器集群，负载均衡；
数据库基础
1,什么是数据库 ？
数据库：database,存储数据的仓库；
数据库：高效的存储和处理数据的介质（介质主要是两种：磁盘和内存）；
2,数据库的分类？
数据库基于存储介质的不同：进行了分类，分为两类：关系型数据库(SQL)和非关系型数据库(NOSQL:Not Only SQL不是关系型的数据库都叫做非关系型数据库);
3，不同的数据库阵营的产品有哪些？
关系型数据库
大型：Oracle,DB2
中型：SQL-SERVER,Mysql等
小型：access
非关系型数据库：memcached,mongodb,redis(同步到磁盘里)；
4，两种数据库阵营的区别？
关系型数据库：安全（保存在磁盘里基本不可能丢失），容易理解，比较浪费空间（二维表）；
非关系型数据库：效率高，不安全（断电丢失，保存在内存里）；
关系型数据库
- 什么是关系型数据库？
关系型数据库：是一种建立在关系模型（数学模型）上的数据库；
关系模型：一种所谓建立在关系上的模型，关系模型包含三个方面；
数据结构：数据存储的问题，二维表（有行和列）；
操作指令集合：所有的SQL语句；
完整性约束：表内数据约束（字段与字段），表与表之间的约束（外键）；
- 关系型数据库的设计？
关系型数据库：从需要存储的数据需求中分析，如果是一类数据（实体）应该设设计成一张二维表：表是由表头（字段名：用来规定数据的名字）和数据部分组成（实际存储的数据单元）
二维表：行和列
| 表头 | 字段名1 | 字段名2 |
| 数据单元 | 数据1 | 数据2 |
以实际案例来进行处理：分析一个教学系统，讲师负责教学，教学生，在教室教学生；
①找出系统中所有的实体：讲师表，学生表，班级表
②找出实体中应该存在的数据信息：
讲师：姓名，性别，年龄，工资；】
学生：姓名，性别，学号，学科；
班级：班级名字，教师编号；
关系型数据库：维护实体内部，实体与实体之间的联系
实体内部联系：每个学生都有姓名，性别，学号，学科信息
| 姓名 | 性别 | 学号 | 学科 | 年龄 |
| 程明 | 男 | 06151018 | PHP | 20 |
| 晶晶 | 女 | 06151019 | PHP |
|
| 亮亮 |
| 06151020 | UI |
|
第二行的所有字段，都是在描述程明这个学生（内部联系）；第二列只能放性别（内部约束）
关系型数据库的特点之一：如果表中对应的某个字段没有值（数据），但是系统依然要分配空间：关系型数据库比较浪费空间
实体与实体之间的联系;每个学生肯定属于某个班级，每个班级一定有多个学生（一对多）
学生表
| 姓名 | 性别 | 学号 | 学科 | 年龄 |
| 程明 | 男 | 06151018 | PHP | 20 |
| 晶晶 | 女 | 06151019 | PHP |
|
| 亮亮 |
| 06151020 | UI |
|
班级表
| 班级名称 | 教室编号 |
| PHP1501 | B205 |
| PHP1502 | A203 |
解决方案：在学生表之后增加一个班级字段来指向班级（必须能够唯一的找到一个班级信息）
| 姓名 | 性别 | 学号 | 学科 | 年龄 | 班级名称 |
| 程明 | 男 | 06151018 | PHP | 20 | PHP1501 |
| 晶晶 | 女 | 06151019 | PHP |
| PHP1501 |
| 亮亮 |
| 06151020 | UI |
| PHP1502 |
学生实体与班级实体的关联关系：实体与实体之间的关系
关键字说明
数据库：database
数据库系统：DBS(Database System):是一种虚拟系统，将多种内容关联起来的称呼；
DBS=DBMS+DB
数据库管理系统：DBMS(DataBase Managcment System)：专门管理数据库
DBA：Database Administrator,数据库管理员；
行/记录：row/record，本质是一个东西：都是指表中的一行（一条记录）：行是从结构角度出发，记录是从数据角度出发；
列/字段：column/field,本质是一个东西：都是指表中的一列（一条记录）：列是从结构角度出发，字段是从数据角度出发；
SQL
SQL：Structured Query Language,结构化查询语言（数据以查询为主：99%是在进行查询操作）
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）可能内部会有一些轻微的区别。
Mysql 数据库
Mysql数据库是一种c/s结构的软件：客户端/服务端，若想访问服务器必须通过客户端（服务器一直运行，客户端在需要使用的时候运行）。
- 客户端连接认证：连接服务器，认证身份：mysql.exe –hPup
mysql –uroot -p
- 发送SQL指令
- 服务器接收SQL指令；处理SQL指令；返回操作指令
- 客户端接收结果；显示结果
- 断开连接（释放资源：服务器并发限制）:exit/quit/ \q
Mysql服务器对象
没有办法安全了解服务器内部的内容：只能粗略的去分析数据库服务器的内部的结构。
将mysql服务器内部对象分成了四层：系统（DBMS）->数据库（DB）->数据表（Table）->字段（Field-存储数据）
SQL基本语句操作
基本操作：CRUD
将SQL的基本操作根据操作对象进行分类，分为三类：库操作，表操作（字段），数据操作。
库操作
对数据库的增删改查；
新增数据库
基本语法
Create database 数据库名字 [库选项]；
Create {database|schema} [IF NOT EXISTE] 数据库名字 [DEFAULT] CHARACTER SET [=] charset_name;
库选项：用来约束数据库，分为两个选择
字符集设定：charset/character set 具体字符集（数据存储的编码格式）：常用的字符集：GBK和UTF8；
校对集设定：collate 具体校对集（数据比较的规则）
|
|
其中：数据库名字不能用关键字（已经被使用的字符）或者保留字（将来可能会用到的）
如果非要使用关键字或者保留字，那么必须使用反引号
中文数据库是可以的，但是有前提条件：保证服务器可以识别（建议不用）
解决条件：set names gbk;
- 当创建数据库SQL语句执行之后，发生了什么？
2，会在保存数据的文件夹下：Data 目录，创建一个对应数据库名字的文件夹
3，每一个数据库下都有一个opt文件：保存了库选项
查看数据库
- 查看所有数据库
- 查看指定部分数据库：模糊查询
Show database like ‘pattern’; --pattern 是匹配模式
%：表示匹配多个字符
_:表示匹配单个字符
- 查看数据库的创建语句：show create database 数据库名字；
更新数据库
数据库名字不可以修改；
数据库的修改仅限库选项：字符集和校对集（校对集依赖字符集）
Alter database 数据库名字 [库选项]；
Charset / character set [=]字符集 Collate 校对集
删除数据库
所有的操作中：删除是最简单的
Drop database 数据库名字；
- 在数据库内部看不到对应的数据库
- 在对应的数据库存储的文件夹内；数据库名字对应的文件夹也被删除级联删除；里面的数据表也全部删除）
注意：数据库的删除不是闹着玩的，不要随意删除，应该先进行备份后操作；
表操作
表与字段是密不可分的
新增数据表
Create table [if not exists] 表名(
字段名字 数据类型，
字段名字 数据库类型 -- 最后一行不需要逗号
)[表选项]；
If not exists:如果表名不存在，那么就创建，否则不执行创建代码：检查功能
表选项：控制表的表现
字符集：charset /character set 具体字符集； --保证表中数据存储的字符集
校对集：collate 具体校对集；
存储引擎：engine 具体的存储引擎(innodb和myisam)
任何一个表的设计都必须指定数据库
方案1：显示的指定表所属的数据库
Create table 数据库.表名(); --将当前数据表创建到指定的数据库下
方案2：隐式的指定表属于：先进入到某个数据库环境，然后这样创建的表自动归属到某个指定的数据库；
进入数据库环境：use 数据库名字；
当创建数据表的SQL指令执行之后，到底发生了什么？
- 指定数据库下已经存在对应的表
- 在数据库对应的文件夹下，会产生对应表的结构文件(跟存储引擎有关系)
查看数据表
数据库能查看的所有方式，表都可以查看
- 查看所有表：show tables；
- 查看部分表：模糊匹配：show tables like ‘pattern’;
-
3,查看表的创建语句：show create table 表名；
- 查看表结构：查看表中的字段信息
-
Desc/describe/show columns from 表名；
修改数据表
表本身存在，还包含字段：表的修改分为两个部分：修改表本身和修改字段
修改表本身
表本身可以修改：表名和表选项
修改表名：rename table 老表名 to 新表名；
修改表选项：字符集，校对集和存储引擎
Alter table 表名 表选项 [=] 值 例：alter table my_student charset =GBK;
修改字段
字段操作很多：新增，修改，重名，删除
新增字段
Alter table 表名add[column] 字段名 数据类型 [列属性] [位置]；
位置：字段名可以存放表中的任意位置
First:第一个位置
After:在哪个字段之后；after 字段名；默认是在最后一个字段之后
修改字段：修改通常是修改属性或者数据类型
Alter table 表名
modify 字段名 数据类型 [属性] [位置]
重命名字段
Alter table 表名 change 旧字段 新字段名 数据类型 [属性] [位置]
删除字段
Alter table 表名 drop 字段名；
小心：如果表中已经存在数据，那么删除字段会清空该字段的所有数据(不可逆)
删除数据表
Drop table 表名1，表名2，表名3….; --可以一次性删除多张表
当删除数据表的指令执行之后发生了什么？
- 在表空间中，没有了指定的表(数据也没有了)
- 在数据库对应的文件夹下，表对应的文件(与存储引擎有关)也会被删除
-
注意:删除有危险，操作需谨慎(不可逆)
数据操作
新增数据
有两种方案
方案1：给全表字段插入数据，不需要指定字段列表；要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致；凡是非数值数据，都需要使用引号(建议是单引号)包裹；
Insert into 表名 values(值列表)[,(值列表)]； --可以一次性插入多条记录
方案2：给部分字段插入数据，需要选定字段列表；字段列表出现的顺序与字段的顺序无关；但是值列表的顺序必须与选定的字段的顺序一致；
Insert into 表名 （字段列表）values(值列表) [,(值列表)]；
查看数据
Select */字段列表 from表名 [where 条件]；
查看所有数据
查看指定字段，指定条件数据
更新数据
Update 表名 set 字段 = 值 [where 条件]；-- 建议使用where 要不就是更新全部；
更新不一定会成功：如没有要真正要更新的数据
删除数据
删除是不可逆的：谨慎删除
Delete from 表名 [where 条件]；
中文数据问题
中文数据问题的本质是字符集问题。
计算机只识别二进制：人类更多是识别符号；需要有个二进制与字符的对应关系(字符集)
校对集问题
校对集：数据比较的方式
校对集有三种格式
_bin:binary,二进制比较，取出二进制位，一位一位的比较，区分大小写；
_cs：case sensitive,大小写敏感，区分大小写
_ci:case insensitice,大小写不敏感，不区分大小写；
查看数据库所支持的校对集：show collation;
校对集应用：只有当数据库产生比较的时候，校对集才会生效。
对比：使用utf8的_bin 和_ci来验证不同校对集的效果
- 创建不同校对集对应的表
-
- 插入数据
-
- 比较：根据某个字段排序：order by 字段名 [asc|desc] ;asc升序，desc 降序；默认是升序；
-
校对集：必须在没有数据之前声明好，如果有了数据，那么在进行校对集修改：那么修改无效；
Web乱码问题
动态网站由三部分构成：浏览器，apache服务器，数据库服务器，三个部分都有自己的字符集(中文)，数据需要在三部分之间来回传递：很容易产生乱码
如何解决乱码问题：统一编码(三码合一)
但是事实上不可能：浏览器是用户管理(根本不可能控制)
数据类型(列类型)
所谓的数据类型：对数据进行统一的分类，从系统的角度出发为了能够使用统一的方式进行管理：更好的利用有限的空间。
SQL中将数据类型分成三大类：数值类型，字符串类型和时间类型
数值型
数值型数据：都是数值
系统将数值型分为整数型小数型
整数型
存放整型数据：在SQL中因为更多要考虑如何节省磁盘空间，所以系统将整型有细分5类：
Tinyint：迷你整型，使用一个字节存储，表示的状态最多为256种(常用)；
Smallint:小整型，使用2个字节存储，表示的状态最多为65536种；
Mediumint:中整型，使用3个字节存储；
Int:标准整型，使用4个字节存储(常用)；
Bigint:大整型，使用8个字节存储；
创建一张整型表
插入数据：只能插入整型，只能插入范围内的整型
SQL中的数值类型全部都是默认有符号：分正负
有时候需要使用无符号数据：需要给数据类型限定：int unsigned; -- 无符号从0开始；
查看表结构的时候，发现每个字段的数据类型之后都会自带一个括号，里面有指定的数字
显示宽度：没有什么特殊的含义，只是默认的告诉用户可以显示的形式而以，实际上用户是可以控制的，这种控制不会改变数据本身的大小；
显示宽度的意义：在于当数据不够显示宽度的时候,会自动让数据变成对应的显示宽度：通常需要搭配一个前导0来增加宽度，不改变值大小：zerofill(零填充)；零填充会导致数值自动变成无符号；
零填充+显示宽度的效果
零填充的意义(显示宽度)：保证数据格式；
小数型
小数型：带有小数或者范围超出整型的数值类型
SQL中：将小数型细分成两种：浮点型和定点型
浮点型：小数点浮动，精度有限，而且会丢失精度；
定点型：小数点固定，精度固定，不会丢失精度；
浮点型
浮点型数据是一种精度型数据：因为超出范围之后，会丢失精度(自动四舍五入)
浮点型：理论分为两种精度
Float:单精度，占用4个字节存储数据，精度范围大概在7位左右；
Double:双精度，占用8个字节存储数据，精度范围大概在15位左右；
创建浮点数表：浮点的使用方式，直接float表示没有小数部分；float(M,D)：M代表总长度，D代表小数部分长度，整数部分长度为M-D；
插入数据：可以是直接小数，也可以是科学计数法；
浮点型数据的插入：整型部分不能超出长度，但是小数部分可以超过长度(系统会自动四舍五入)
结果：浮点数一定会进行四舍五入(超出精度范围)：浮点数如果因为系统进位导致整数部分超出指定的长度，那么系统允许成立；
定点型
定点型：绝对的保证整数部分不会被四舍五入(不会丢失精度)，小数部分有可能(理论小数部分也不会丢失精度)
创建定点数表：以浮点数作为对比
插入数据：定点数的整数部分一定不能超出长度(进位也不可以)，小数部分的长度可以随意超出(系统自动四舍五入)
浮点数如果进位导致长度溢出没有问题，但是定点数不行
查看数据效果
时间日期类型
Datetime:时间日期，格式是YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999年，有0值：0000-00-00 00：00：00
Date:日期，就是datetime中的date部分
Time:时间(段)，指定的某个区间之间，-时间到+时间
Timestamp:时间戳，并不是时间戳，只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致
Year:年份，两种形式，year(2):和year(4):1901-2156
创建时间日期表
插入数据：时间time可以是负数，而且可以是很大的负数，year可以使用2位数插入，也可以使用4位数
Timestamp:只要当前所在的记录被更新，该字段一定会自动更新成当前时间；
网站是以PHP为实现的主要操作对象：PHP中有非常强大的时间日期处理函数：date只需要一个时间戳就可以转换成任意类型的时间：以PHP为主的时候，都是在数据库使用时间戳(整型)来存储时间。
字符串类型
在SQL中，将字符串类型分成了6类：char,varchar,text,blob,enum和set
定长字符串
定长字符串：char,磁盘(二维表)在定义结构的时候，就已经确定了最终数据的存储长度；
Char(L):L代表length,可以存储的长度，单位为字符，最大长度值可以为255.
Char(4):在UTF8环境下，需要4*3=12个字节
变长字符串
变长字符串：varchar,在分配空间的时候，按照最大的空间分配：但是实际上最终用了多少是根据具体的数据来确定；
Varchar(L)：L表示字符长度 理论长度是65536字符，但是多出1到2个字节来确定存储的实际长度:但是实际上如果长度超过255，既不用定长也不用变长，使用文本字符串text
Varchar(10):的确存了10个汉字，utf8环境，10*3+1=31(bytes)
存储了3个汉字：3*3+1=10(bytes)
定长与变长的存储实际空间(UTF8)
实际存储空间
Char(4)
Varchar(4)
Char占用字节
Varchar(占用字节)
ABCD
ABCD
ABCD
4*3=12
4*3+1=13
A
A
A
4*3=12
1*3+1=4
ABCDE
X
X
数据超过长度
数据超过长度
如何选择定长或者变长字符串呢？
定长的磁盘空间比较浪费，但是效率高；如果数据基本上确定长度都一样，就是使用定长，如身份证，电话号码，手机号码等
变长的磁盘空间比较节省，但是效率低；如果数据不能确定长度(不同数据有变化)，如姓名，地址等
文本字符串
如果数据量非常大，通常说超过255个字符就会使用文本字符串
文本字符串根据存储的数据的格式进行分类：text和blob
Text:存储文字(二进制数据实际上都是存储路径)
Blob:存储二进制数据(通常不用)
枚举字符串
枚举：enum,事先将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个。
枚举的使用方式
定义：enum(可能出现的元素列表)； //如 enum(‘男’，’女’，‘保密’)；
使用：存储数据，只能存储上面定义好的数据；
创建枚举表
插入数据：作用之一：规范数据格式：数据只能是规定的数据中的其中之一
作用之二：节省存储空间(枚举通常有一个别名：单选框)：枚举实际存储的是数值，而不是字符串本身；
在mysql中，系统也是自动转换数据格式的：而且基本与PHP一样(尤其是字符串转换数字)
证明字段存储的数据是数值：将数据取出来+0就可以判断出原来的数据存的到底是字符串还是数值；如果是字符串最终结果永远为0，否则就是其他值；
找出枚举元素的实际规律：按照元素出现的顺序，从1开始编号
枚举原理：枚举在进行数据规范的时候(定义的时候)，系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中)：然后在进行数据插入的时候，系统自动将字符换成对应的数字存储，然后在进行数据提取的时候，系统自动将数值转换成对应的字符串显示；
因为枚举实际存储的是数值，所以可以直接插入数值；
集合字符串
集合跟枚举很类似：实际存储的是数值，而不是字符串(集合是多选的)
集合使用方式：
定义：Set(元素列表)
使用：可以使用元素列表中的元素(多个)，使用逗号分隔；
创建集合表
插入数据：可以使用多个元素字符串组合，也可以直接插入数值；
查看数据：数值+数据查看
集合中每一个元素都是对应二进制
集合中元素的顺序没有关系：最终系统都会去匹配顺序
集合的强大在于能够规范数据和节省空间：PHP也可以规范数据，但是对于PHP来说效率优先，而且数据的维护可以通过数字进行，增强PHP的维护成本：PHP根本没有办法判断数据在数据库的形式。
Mysql记录长度
Mysql中规定：任何一条记录最长不能超过65535个字节(varchar永远达不到理论值)
Varchar的实际存储长度能达到多少呢？看字符集编码。
Utf8下varchar的实际顶配：21844字符
Gbk下的varchar的实际顶配：32766字符
想要完整个65535个字节长度：增加一个tinyint字段即可
Mysql记录中：如果有任何一个字段允许为空，那么系统会自动从整个记录中保留一个字节来存储NULL(若想释放NULL所占用的字节：必须保证所有的字段都不允许为空)
Mysql中text文本字符串，不占用记录长度：额外存储·，但是text文本字符串也是属于记录的一部分；一定需要占据记录中的部分长度：10个字节(保存数据的地址与长度)
列属性
列属性：真正约束字段的是数据类型，但是数据类型的约束很单一，需要一些额外的约束，来更加保证数据的合法性。列属性：NULL/NOT NULL，default,Primary key,unique key,auto_increment,comment，Foreign key
空属性：
两个值：NULL(默认的)和NOT NULL(不为空)
虽然默认的，数据库基本是字段为空，但是实际上在真实开发的时候，尽可能多的要保证所有的数据都不应该为空：空数据没有意义；空数据没有办法参加运算；
创建一个实际案例表：班级表(名字，教室)
列描述
列描述：comment，描述，没有实际含义：是专门用来描述字段，会根据表创建语句保存：用来给程序员(数据管理员)来进行了解的。
默认值
默认值：某一个数据会经常性的出现某个具体的值，可以在一开始就指定好：在需要真实数据的时候，用户可以选择性的使用默认值；
默认值关键字：default
默认值的生效：使用，在数据进行插入的时候，不给该字段赋值；
字段属性
主键
主键：primary key,主要的键，一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复：这种称之为主键；
一张表中只能有一个主键；
增加主键
SQL操作中有多种方式可以给表增加主键：大体分为三种
方案1：在创建表的时候，直接在字段之后，跟primary key关键字(主键本身不允许为空)
优点：非常直接；缺点：只能使用一个字段作为主键
方案2：在创建表的时候，在所有的字段之后，使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键，可以是复合主键)
方案3：当表已经创建好之后，再次额外追加主键：可以通过修改表字段属性，也可以直接追加。
Alter table 表名 add primary key(字段列表) ；
alter table my_pri3 modify course char(10) primary key comment '课程编码： 3901 + 0000';
alter table my_pri3 add primary key(course);
前提：表中字段对应的数据本身是独立的(不重复)
主键约束
主键对应的字段中的数据不允许重复：一旦重复，数据操作失败(增和改)
更新主键&删除主键
没有办法更新主键：主键必须先删除，才能增加
Alter table 表名 drop primary key;
主键分类
在实际创建表的过程中，很少使用真实业务数据作为主键字段(业务主键，如学号，课程号)，大部分的时候是使用逻辑性的字符(字段没有业务含义，值是什么都没有关系)，将这种字段主键称为之为逻辑主键
Create table my_studebt(
Id int primary key auto_increment comment ‘逻辑主键：自增长’ –逻辑主键
Number char(10) not null comment ‘学号’,
Name varchar(10) not null,
)charset utf8;
自动增长
新增自增长：当对应的字段，不给值，或者说给默认值，或者给NULL的时候，会自动的被系统触发，系统会从当前字段中已有的最大值在进行+1操作，得到一个新的在不同的字段；
自增长通常是跟主键搭配的；
自增长特点：auto_increment
- 任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值)
-
- 自增长字段必须是数字(整型)
-
- 一张表最多只能由一个自增长
-
自增长使用
自增长被给定的值为NULL 或者默认值的时候会触发自动增长
自增长如果对应的字段输入值，那么自增长失效，但是下一次还是能够正确的自增长(从最大值+1)
如何确定下一次是什么自增长呢？可以通过查看表创建语句看到；
修改自增长
自增长如果涉及到字段改变：必须先删除自增长，后增加(一张表只能有一个自增长)修改当前自增长已经存在的值：修改只能比当前已有的自增长的最大值大，不能小(小不生效)
思考：为什么自增长是从1开始？为什么每次都是自增1呢？
所有系统的实现(如字符集，校对集)都是由系统内部的变量进行控制的；
查看自增长对应的变量：show variables like ‘auto_increment%’
删除自增长
自增长是字段的一个属性：可以通过modify来进行修改(保证字段没有auto_increment即可)
Alter table 表名 monify 字段 类型；
唯一键
一张表中往往有很多字段需要具有唯一性，数据不能重复：但是一张表中只能有一个主键：唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题；
唯一键的本质与主键差不多：唯一键默认的允许自动为空，而且可以多个为空(空字段不参与唯一性比较)
增加唯一键
基本与主键差不多：三种方案
方案1：在创建表的时候，字段之后直接跟unique /unique key
方案2：在所有的字段之后增加unique key(字段列表)； -- 复合唯一键
方案3：在创建表之后增加唯一键
唯一键约束
唯一键与主键本质相同：唯一的区别就是唯一键默认允许为空，而且是多个为空；
如果唯一键也不允许为空：与主键的约束作用是一致的；更新唯一键&删除唯一键
更新唯一键：先删除后新增(唯一键可以有多个：可以不删除)
删除唯一键
Alter table 表名 drop index 索引名字； -- 唯一键默认的使用字段名作为索引名字
索引
几乎所有的索引都是建立在字段之上
索引：系统根据某种算法，将已有的数据(未来可能新增的数据)，单独建立一个文件：文件能够快速的匹配数据，并且能够快速的找到对应表中的记录；
索引的意义
- 提升查询数据的效率
- 约束数据的有效性(唯一性等)
-
增加索引的前提条件：索引本身会产生索引文件(有时候有可能比数据文件还大)，会非常耗费磁盘空间。
如果某个字段需要作为查询的条件经常使用，那么可以使用(一定会想办法增加)
如果某个字段需要进行数据的有效性约束，也可能使用索引(主键，唯一键)；
Mysql中提供了多种索引
- 主键索引：primary key
- 唯一索引：unique key
- 全文索引：fulltext index
- 普通索引：index
-
全文索引：针对文章内部的关键字进行索引
全文索引最大的问题：在于如何确定关键字
英文很容易：英文单词与单词之间有空格
中文很难：没有空格，而且中文可以随意组合(分词：sphinx)
关系
将实体与实体之间的关系，反应到最终数据库表的设计上来：将关系分成三种：一对一，一对多(多对一)和多对多；
所有的关系都是指的表与表之间的关系
一对一
一对一：一张表的一条记录一定只能与另外一张表的一条记录进行对应；反之亦然
学生表：姓名，性别，年龄，身高，体重，婚姻状况，籍贯，家庭住址，紧急联系人
ID(PRI)
姓名
性别
年龄
体重
身高
婚姻
籍贯
住址
联系人
表设计成以上这种形式：符合要求，其中姓名，性别，年龄，身高，体重属于常用数据；但是婚姻，籍贯，住址，联系人属于不常用数据。如果每次查询都是查询所有数据，不常用的数据就会影响效率，实际有不用。
解决方案：将常用的和不常用的信息分离存储，分为两张表
常用信息表
ID(PRI)
姓名
性别
年龄
体重
身高
1
不常用信息表：保证不常用信息与常用信息一定能够对应上：找一个具有唯一性(确定记录)的字段来共同连接两张表
婚姻
籍贯
住址
联系人
2
1
一个常用表中的一条记录：永远只能在一张不常用表中匹配一条记录：反过来，一个不常用表中的一条记录在常用表中也只能匹配一条记录；一对一的关系
一对多
一对多：一张表中有一条记录可以对应另外一张表中的多条记录；但是返回过，另外一张表的一条记录只能对应第一张表的一条记录.这种关系就是一对多或者多对一。
母亲与孩子的关系:母亲，孩子两个实体
妈妈表
ID(P)
名字
年龄
性别
孩子表
ID(P)
名字
年龄
性格
以上关系：一个妈妈可以在孩子表中找到多条记录(也有可能是一条)；但是一个孩子只能找到一个妈妈：是一种典型的一对多的关系。
但是以上设计：解决了实体的设计表问题，但是没有解决关系问题：孩子找不到妈，妈也找不到孩子。
解决方案：在某一张表中增加一个字段，能够找到另外一张表的中记录：应该在孩子表中增加一个字段指向妈妈表：因为孩子表的记录只能匹配到一条妈妈表的记录；
ID(P)
名字
年龄
性格
妈妈表
妈妈表主键
多对多
多对多：一张表中(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->中间表寻找匹配记录(多条)->学生表匹配(一条)
范式（数据库的设计)
范式：Normal Format,是一种离散数学中的知识，是为了解决一种数据的存储与优化的问题：保存数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储：终极目标是为了减少数据的冗余。
范式：是一种分层结构的规范，分为六层：每一层都比上一层更加严格：若要满足下一层范式，前提是满足上一层范式。
六层范式：1NF,2NF,3NF…6NF,1NF是最底层，要求最低；6NF最高层，最严格。
Mysql属于关系型数据库：有空间浪费：也是致力于节省存储空间：与范式所有解决的问题不谋而合；在设计数据库的时候，会利用到范式来指导设计。
但是数据库不单是要解决空间问题，要保证效率问题：范式只为解决空间问题，所以数据库的设计又不可能完全按照范式的要求实现：一般情况，只有满足前三种范式需要满足；
范式在数据库的设计当中是有指导意义：但是不是强制规范。
1NF：字段设计必须符合原子性
第一范式：在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前还需要额外的处理(拆分)，那么说表的设计不满足第一范式：第一范式要求字段的数据具有原子性：不可再分
讲课代课表
讲师
性别
班级
教室
代课时间
代课时间(开始，结束)
朱元璋
Male
Php0226
D302
30天
2014-02-07，2014-05-05
李世民
Male
Php0320
B206
30天
2014-03-21，2014-04-21
上表设计不存在问题：但是如果需求是将数值查出来之后，要求显示一个老师从什么时候开始上课，到什么时候结课;需要将代课时间进行拆分：不符合1NF，数据不具有原子性；
解决方案：将代课时间拆分成两个字段就解决问题；
讲师
性别
班级
教室
代课时间
开始
结束
朱元璋
Male
Php0226
D302
30天
2014-02-27
2014-03-27
李世民
Male
Php0320
B206
30天
2014-03-21
2014-04-21
2NF：不存在部份依赖(没有复合主键)
第二范式：在数据表设计的过程中，如果有复合主键(多字段主键)，且表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段(主键的部分)：存在字段依赖主键的部分的问题，称之为部分依赖：第二范式就是要解决表的设计不允许出现部分依赖。
讲师带课表
讲师P
性别
班级P
教室
代课时间
开始
结束
朱元璋
Male
Php0226
D302
30天
2014-02-27
2014-03-27
李世民
Male
Php0320
B206
30天
2014-03-21
2014-04-21
以上表中：因为讲师没有办法作为独立主键，需要结合班级才能作为主键(复合主键：一个老师在一个班永远带一个阶段的课)：代课时间，开始和结束字段都与当前的代课主键(老师与班级)：但是性别并不依赖班级，教室不依赖讲师：性别只依赖讲师，教室只依赖班级：出现了性别和教室依赖主键中的一部分：部分依赖。不符合第二范式。
解决方案1：可以将性别与讲师单独成表，班级与教室也单独成表。
解决方案2：取消复合主键，使用逻辑主键；
IDP
讲师
性别
班级
教室
代课时间
开始
结束
1
朱元璋
Male
Php0226
D302
30天
2014-02-27
2014-03-27
2
李世民
Male
Php0320
B206
30天
2014-03-21
2014-04-21
ID=讲师+班级(业务逻辑约束：复合唯一键)
3NF：不存在传递依赖(实体单独建表)
要满足第三范式，必须满足第二范式。
第三范式，理论上讲，应该一张表中的所有字段都应该直接依赖主键(逻辑主键：代表的是业务主键)，如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键：把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。第三范式就是解决传递依赖的问题
讲师带课表
IDP
讲师
性别
班级
教室
代课时间
开始
结束
1
朱元璋
Male
Php0226
D302
30天
2014-02-27
2014-03-27
2
李世民
Male
Php0320
B206
30天
2014-03-21
2014-04-21
以上设计方案中：性别依赖讲师存在，讲师依赖主键；教室依赖班级，班级依赖主键；性别和教室都存在传递依赖。
解决方案：将存在传递依赖的字段，以及依赖的字段本身单独取出，形成一个单独的表，然后在需要对应的信息的时候，使用对应的实体表的主键加进来。
讲师带课表
讲师表 班级表
讲师表：ID=讲师 班级表中：ID=班级
逆规范化
有时候，在设计表中的时候，如果一张表中有几个字段是需要从另外的表中去获取信息。理论上，的确可以获取到想要的数据，但是就是效率低一点。会刻意的在某些表中，不去保存另外表的主键(逻辑主键)，而是直接保存想要的数据信息：这样一来，在查询数据的时候，一张表可以直接提供数据，而不需要多表查询(效率低)，但是会导致数据冗余增加。
逆规范化：磁盘利用率与效率的对抗；
数据的高级操作
数据操作：增删改查
新增语法
基本语法
Insert into 表名 [(字段列表)] values(值列表)；
在数据插入的时候，假设主键对应的值已经存在：插入一定会失败！
主键冲突
当主键存在冲突的时候(Duplicate key),可以选择性的进行处理：更新和替换
主键冲突：更新操作
Insert into 表名[(字段列表：包含主键)] values(值列表) on duplicate key update 字段 = 新值；
主键冲突：替换
Replace into 表名 [(字段列表：包含主键)] values(值列表)；
蠕虫复制
蠕虫复制：从已有的数据中获取数据，然后将数据又进行新增操作：数据成倍的增加；
表创建高级操作：从已有表创建新表(复制表结构)
Create table 表名 like 数据库.表名；
蠕虫复制：先查出数据，然后将查出的数据新增一遍；
Insert into 表名 [(字段列表)] select 字段列表/* from 数据表名；
蠕虫复制的意义：
- 从已有表中拷贝数据到新表；
- 可以迅速的让表中的数据膨胀到一定的数量级：测试表的压力以及效率；
-
更新数据
基本语法
Update 表名 set 字段 = 值 [where 条件]；
高级新增表语法
Update 表名 set 字段 = 值 [where条件] [limit 更新数量]；(限制记录)
删除数据
与更新类似：可以通过limit 来限制数量
Delete from 表名 [where 条件] [limit 数量]；
删除：如果表中存在主键自增长，那么当删除之后，自增长不会还原；
思路：数据的删除是不会改变表结构，只能删除表之后重建表
Turncate 表名； -- 先删除改变，后增加改变；
查询数据
基本语法
Select 字段列表/* from 表名 [where条件]；
完整语法
Select [select选项] 字段列表[字段别名]/* from 数据源 [where条件子句] [group by 子句] [having子句] [order by子句] [limit 子句]；
Select 选项
Select 选项：select对查出来的结果处理方式
All：默认的，保留所有的结果
Distinct:去·重，查出来的结果，将重复给去除(所有字段都相同)
字段别名
字段别名：当数据进行查询出来的时候，有时候名字并一定就满足需求(多表查询的时候，会有同名字段)，需要对字段名进行重命名：别名
语法： 字段名 [as] 别名；
数据源
数据源：数据的来源，关系型数据库的来源都是数据表：本质上只要保证数据类似二维表，最终都可以作为数据源。
数据源分为多种：单表数据源，多表数据源，查询语句
单表数据源：select * from 表名；
多表数据源：select * from 表名1，表名2，表名3…..;
从一张表中取出一条记录，去另外一张表中匹配所有记录，而且全部保留 ：（记录数与字段数），将这种结果成为：笛卡尔(交叉连接)：笛卡尔积没什么卵用，所以应该尽量避免。
子查询：数据的来源是一条查询语句(查询语句的结果是二维表)
Select * from(select 语句)as 表名；
Where 子句
Where 子句：用来判断数据，筛选数据。
Where 子句返回结果：0或者1，0代表false,1代表true.
判断条件：
比较运算符：>,<,>=,<=,!=,<>,=,like,between and ,in/not in
逻辑运算符：&&(and),||(or),!(not)
Where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件：从磁盘取出一条记录，开始进行where判断：判断的结果如果成立保存到内存；如果失败直接放弃。
条件查询1：要求找出id为1或者3的学生
条件查询2：查出区间的值
Between 本身是闭区间；between左边的值必须小于或者等于右边的值；
Croup by子句
Group by:分组的意思，根据某个字段进行分组(相同的放一组，不同的分到不同的组)
基本语法：group by 字段名；
分组的意思：是为了统计数据(按组统计：按分组字段进行数据统计)；
SQL提供了一系列统计函数；
Count():统计分组后的记录数；每一组有多少记录；
MaxI():统计每组中最大的值；
Min():统计最小值；
Avg():统计平均值；
Sum():统计和；
Count函数：里面可以使用两种参数；*代表统计记录，字段名字代表统计对应的字段(NULL不统计)
分组会自动排序：根据分组字段：默认升序
Group by 字段 [asc|desc]; -- 对分组的结果然后合并之后的整个结果进行排序；
多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组；
Having子句
Having子句：与where子句一样：进行条件判断的。
Where是针对磁盘数据进行判断：进入到内存之后，会进行分组操作：分组结果就需要having来处理；
Having能做where能做的几乎所有的事情，但是where却不能做having能做的很多事情。
- 分组统计的结果或者说统计函数都只有having能够使用。
-
- Having能够使用字段别名：where不能：where是从磁盘取数据，而名
-
右表：在join关键字右边的表
连接查询分类
SQL中将连接查询分成四类：内连接，外连接，自然连接和交叉连接
交叉连接
交叉连接：cross join,从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配：匹配一定保留(没有条件匹配)，而连接本身字段就会增加(保留)，最终形成的结果叫做：笛卡尔积。
字只可能是字段名；别名是在字段进入内存后才会产生。
Order by子句
Order by:排序，根据某个字段进行升序或者降序排序，依赖校对集；
使用基本语法
Order by 字段名 [asc|desc]; -- asc是升序(默认的)，desc降序
排序可以进行多字段排序：先根据某个字段进行排序，然后排序好的内部，再按照某个数据进行再次排序；
Limit子句
Limit子句是一种限制结果的语句：限制数量；
Limit有两种使用方式
方案1;只用来限制长度(数据量)：limit数据量；
方案2：限制起始位置，限制数量；limit起始位置，长度；
Limit方案2主要用来实现数据的分页；为用户节省时间，提交服务器的响应效率，减少资源的浪费；
对于用户来讲：可以点击的分页按钮：1，2，3，4；
对于服务器来讲：根据用户选择的页码来获取不同的数据：limit offset,length;
Length:每页显示的数据量；基本不变
Offset:offest = (页码 -1)*每页显示量；
连接查询
连接查询：将多张表(可以大于2张)进行记录的连接(按照某个指定的条件数据拼接)：最终结果是：记录数有可能变化，字段数一定会增加(至少两张表的合并)
连接查询的意义：在用户查看数据的时候，需要显示的数据来自多张表。
连接查询：join,使用方式：左表 join 右表
左表：在join关键字左边的表
右表：在join关键字右边的表
连接查询分类
SQL中将连接查询分成四类：内连接，外连接，自然连接和交叉连接
交叉连接
交叉连接：cross join,从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配：匹配一定保留(没有条件匹配)，而连接本身字段就会增加(保留)，最终形成的结果叫做：笛卡尔积。
基本语法：左表cross join 右表；==from 左表，右表；
笛卡尔积没有什么意义：应该尽量避免(交叉连接没用)
交叉连接存在的价值：保证连接这种结构的完整性；
内连接
内连接：[inner]join,从左表中取出每一条记录，去右表中与所有记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留；
基本语法
左表 [inner]join 右表 on 左表.字段 = 右表.字段；on表示连接条件：条件字段就是代表相同的业务含义(如my_student.c_id 和my_class.id)
字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要加上表名才能区分，而表名太长，通常可以使用别名；
内连接可以没有连接条件：没有on之后的内容，这个时候会保留所有结果(笛卡尔积)；
内连接还可以使用where代表on关键字(where没有on效率高)
外连接
外连接：outer join 以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接：不管能不能匹配上条件，最终都会保留：能匹配，正确保留；不能匹配，其他表的字段都置空NULL。
外连接分为两种：是以某张为主：有主表
Left join:左外连接(左连接)，以左表为主表
Right join:右外连接(右连接)，以右表为主表
基本语法：左表 left/right join 右表 on 左表.字段 = 右表.字段；
虽然左连接和右连接有主表差异，但是显示的结果：左表的数据在左边，右表数据在右边。
左连接与右连接可以互转；
自然连接
自然连接：natural join ，自然连接，就是自动匹配连接条件：系统以字段名字作为匹配模式(同名字段就作为，多个同名字段都作为条件)。
自然连接：可以分为自然内连接和自然外连接.
自然内连接：左表 natural join 右表；
select * from my_student natural join my_class; -- 自然内连接
自然外连接：左表 natural left/right join 右表；
Select * from my_student natural left join my_class; -- 自然左外连接
其实；内连接和外连接都可以模拟自然连接：使用同名字段，合并字段
左表
外键
外键：foreign key,外面的键(键不在自己表中)：如果一张表中有一个字段(非主键)指向另外一张表的主键，那么该字段称之为外键。
增加外键
外键可以在创建表的时候或者创建表之后增加(但是要考虑数据的问题)。
一张表可以有多种外键
创建表的时候增加外键：在所有的表字段之后，使用foreign key(外键字段)references外部表(主键字段)
外键：要求字段本身必须先是一个索引(普通索引)，如果字段本身没有索引，外键会先创建一个索引，然后创建外键本身。
在新增表之后增加外键：修改表结构
Alter table 表名 add [constraint 外键名字] foreign key (外键字段) references 父表(主键字段)；
修改外键&删除外键
外键不可修改：只能先删除后新增。
删除外键语法
Alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键，但是名字不能相同；
外键删除不能通过查看表结构体现，应该通过查看表创建语句查看。
外键作用
外键默认的作用有两点：一个对父表，一个子表(外键字段所在的表)
对字表约束：子表数据进行写操作的时候，如果对应的外键字段在父表找不到对应的匹配那么操作会失败。(约束子表数据操作)
对父表约束：父表数据进行写操作(删和改：都必须涉及到主键本身)，如果对应的主键在子表中已经被数据引用
外键条件
- 外键要存在：首先必须保证表的存储引擎是innodb(默认的存储引擎)
-
如果不是innodb存储引擎，那么外键可以创建成功，但是没有约束效果；
- 外键字段的字段类型(列类型)必须与父表的主键类型完全一致。
- 一张表中的外键名字不能重复。
- 增加外键的字段(数据已经存在)，必须保证数据与父表主键要求一致对应。
-
外键约束
所谓的外键约束：就是指外键的作用。
之前所讲的外键作用：是默认的作用；其实可以通过对外键的需求，进行定制操作。
外键约束有三种约束模式：
District:严格模式(默认的)，父表不能删除或者更新一个已经被子表数据引用的记录；
Cascade:级联模式：父表的操作，对应子表关联的数据也跟着被删除；
Setnull:置空模式：父表的操作之后，子表对应的数据(外键字段)被置空；
通常的一个合理的做法(约束模式)：删除的时候子表置空，更新的时候子表级联操作；
指定模式的语法
Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade；
更新操作：级联更新
删除操作：置空
删除置空的前提条件：外键字段允许为空(如果不满足条件，外键无法创建)；
联合查询
联合查询：将多次查询(多条select语句)，在记录上进行拼接(字段不会增加)
基本语法
多条select语句构成：每一条select语句获取的字段数必须严格一致(但是字段类型无关)
Select 语句1 Union [union选项] select 语句1…….
Union 选择：与select 选项一样有两个
All:保留所有(不管重复)
Distinct:去重(整个重复)：默认的
联合查询只要求字段一样，跟数据类型无关；
意义
联合查询的意义分为两种：
- 查询同一张表，但是需求不同：如查询学生信息，男生身高升序，女的身高降序。
- 多表查询：多张表的结构是完全一样的，保存的数据(结构)也是一样的。
-
Order by使用
在联合查询中：order by 不能直接使用，需要对查询语句使用括号；
若要order by 生效：必须搭配limit limit使用限定的最大数即可；
子查询
子查询：sub query,查询是在某个结果之上进行的。(一条select语句内部包含了一条select 语句)。
子查询有两种分类方式：按位置分类
按位置分类：子查询(select语句)在外部查询(select 语句)中出现的位置
From子查询：子查询跟在from之后
Where子查询：子查询出现在where条件中
Exists子查询：子查询出现在exists里面
按结果分类：根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)
标量子查询：子查询得到的结果是一行一列、
列子查询：子查询得到的结果是一列多行
行子查询：子查询得到的结果是多列一行(多行多列)
表子查询：子查询得到的结果是多行多列(出现的位置是在from之后)
上面几个出现的位置都是where之后
表子查询：子查询得到的结果是多行多列(出现的位置是在from之后)
标量子查询
需求：知道班级名字为PHP0710，想获取该班的所有学生
1,Select * from my_student where c_id = ?;
2,获取班级ID：可以通过班级名字确定
Select id from my_class where c_name = ‘PHP0710’; -- id一定只有一个值(一行一列)
列子查询
需求：查询所有在读班级的学生(班级表中存在的班级)
- 确定数据源：学生
-
Select * from my_student where c_id in(?);
2, 确定有效班级的id:所有班级id
Select id from my_class;
列子查询：
列子查询返回的结果会比较：一列多行，需要使用in作为条件匹配：其实在mysql中有还有几个类似的条件：all, some,any
=Any === in;
Any======some; -- any 跟some 是一样；
=all ====为全部；
肯定结果
否定结果
行子查询
行子查询：返回的结果可以是多行多列(一行多列)
需求：要求查询整个学生中，年龄最大且身高是最高的学生；
1,确定数据源
Select * from my_student where age =? And height =?
2,确定最大的年龄和最高的身高；
Select max(age),max(height) from my_student;
表子查询
表子查询：子查询返回的结果是多行多列的二维表：子查询返回的结果是当做二维表来使用；
需求：找出每一个班最高的一个学生
- 确定数据源：先将学生按照身高进行降序排序
-
Select * from my_student order by height desc;
2.从每个班选出第一个学生
Select * from my_student group by c_id; -- 每个班选出一个学生
表子查询：from子查询：得到的结果作为from的数据源
Exists 子查询
Exists:是否存在的意思，exists子查询就是用来判断某些条件是否满足(跨表)，exists是接在where之后：exists返回的结果只有0和1；
需求：查询所有的学生：前提条件是班级存在
- 确定数据源
-
Select * from my_student where ?;
- 确定条件是否满足
-
Exists (Select * from my_class); -- 是否成立
Exists子查询
视图
视图：view,是一种有结构(有行有列)但是没结果(结构中不真实存放数据)的虚拟表，虚拟表的结构来源不是自己定义，而是从对应的基表中产生(视图的数据来源)。
创建视图
基本语法
Create view 视图名字 as select 语句；-- select语句可以是普通查询；可以是连接查询；可以是联合查询；可以是子查询。
创建单表视图：基表只有一个
创建多表视图：基表来源至少两个
视图基表有多张的情况下：注意字段名不能重复查看视图
查看视图：查看视图的结构
视图是一张虚拟表：表，表的所有查看方式都适用于视图：show tables [like]/desc 视图名字 /show create table 视图名；
视图比表还是有一个关键字的区别：view. 查看“表(视图)”的创建语句的时候可以使用view关键字；
视图一旦创建：系统会在视图对应的数据库文件夹下创建一个对应的结构文件：frm文件。
使用视图
使用视图主要是为了查询：将视图当做表一样查询即可。
视图的执行：其实本质就是执行封装的select语句
修改视图
视图本身不可修改，但是视图的来源是可以修改的。
修改视图：修改视图本身的来源语句(select语句)
Alter view 视图名字 as 新的 select语句；
删除视图
删除视图
Drop view 视图名字；
视图意义
- 视图可以节省SQL语句：将一条复杂的查询语句使用视图进行保存：以后可以直接对视图进行操作。
- 数据安全：视图操作是主要针对查询的，如果对结构进行处理(删除)，不会影响基表数据(相对安全)。
- 视图往往是在大项目中使用，而且是多系统使用：可以对外提供有用的数据，但是隐藏关键(无用)的数据：数据安全。
- 视图可以对外提供友好型：不同的视图提供不同的数据，对外好像专门设计。
- 视图可以更好（容易）的进行权限控制。
-
视图数据操作
视图的确可以进行数据写操作的：但是有很多限制
将数据直接在视图上进行操作
新增数据
数据新增就是直接对视图进行新增。
- 多表视图不能新增数据。
- 可以向单表视图插入数据：但是视图中包含的字段必须有基表中所有不能·为空(或者没有默认值)字段。
- 视图是可以向基表插入数据的。
-
删除数据
多表视图不能删除数据，单表视图可以。
更新数据
理论上不论单表视图还是多表视图都可以更新数据。
视图限制：with check option
视图算法
视图算法：系统对视图以及外部查询视图的select语句的一种解析方式。
视图算法分为三种
Undefined:未定义(默认的)，这是一种实际使用算法，是一种推卸责任的算法：告诉系统，视图没有定义算法，系统在自己看着办。
Temptable:临时表算法：系统应该先执行视图的select语句，后执行外部查询语句。
Merge:合并算法：系统应该先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行(效率高：常态)
算法指定：在创建视图的时候
Create algorithm = 指定算法 view 视图名字 as select 语句；
视图算法选择：如果视图的select语句中会包含一个查询子句(五子句)，而且很有可能顺序比外部的查询语句要靠后，一定要使用算法temptable,其他情况可以不用指定(默认即可)；
数据备份与还原
备份：将当前已有的数据或者记录保留
还原：将已经保留的数据恢复到对应的表中
为什么要做备份还原？
- 防止数据丢失：被盗-误操作
- 保护数据记录
-
数据备份还原的方式有很多种：数据表备份，单表数据备份，SQL备份，增量备份。
数据表备份
不需要通过SQL来备份：直接进入数据库文件夹复制对应的表结构以及数据文件，以后还原的时候，直接将备份的内容放进去即可。
数据表备份有前提条件：根据不同的存储引擎有不同的区别。
存储引擎：mysql进行数据存储的方式：主要两种：innodb和myisam(免费)
对比myisam和innodb:数据存储引擎
Innodb:只有表结构，数据全部存储到ibdata1文件中
Myisam:表，数据和索引全部单独分开存储
这种文件备份通常适用于myisam存储引擎：直接复制三个文件即可，然后直接放到对应的数据库下即可以使用。
单表数据备份
每次只能备份一张表：只能备份数据(表结构不能备份)
通常的使用：将表中的数据进行导出到文件
备份：从表中选出一部分数据保存到外部的文件中(outfile)
Select * /字段列表 into outfile 文件所在路径 from 数据源； -- 前提外部文件不存在。
高级备份：自己制定字段和行的处理方式
Select * /字段列表 into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源；
Fileds:字段处理
Enclosed by:字段使用什么内容包裹，默认是‘‘，空字符串；
Terminated by:字段以什么结束，默认是“\t”,tab键
Escaped by:特殊符号用什么方式处理，默认是‘\\’使用反斜杠转义.
Lines:行处理
Starting by:每行以什么开始，默认是’’,空字符串
Terminated by:每行以什么结束，默认是”\r\n”换行符;
数据还原：将一个在外部保存的数据重新恢复到表中(如果表结构不存在，那么sorry)
Load data infile 文件所在路径 into table 表名[(字段列表)] fields 字段处理 lines 行处理 ；-- 怎么备份的怎么还原；
SQL备份
备份的是SQL语句：系统会对表结构以及数据进行处理，变成对应的SQL语句，然后进行备份：还原的时候只要执行SQL指令即可。(主要针对表结构)
备份：mysql没有提供备份指令：需要利用mysql提供的软件：mysqldump.exe
Mysqldump.exe也是一种客户端，需要操作服务器：必须连接认证
Mysqldump/mysqldump.exe-hPup 数据库名字 [数据表名字1 [数据表名字2….]]>外部文件目录(建议使用.sql)
单表备份
整库备份
Mysqldump/mysqldump.exe –hPup 数据库名字 > 外部文件目录
SQL还原数据：两种方式还原
方案1：使用mysql.exe客户端还原
Mysql.exe/mysql –hPup 数据库名字 < 备份文件目录
方案2：使用SQL指令还原
Source 备份文件所在路径；
SQL备份优缺点
- 优点：可以备份结构
- 缺点：会浪费空间(额外的增加SQL指令)
-
增量备份
不是针对数据或者SQL指令进行备份：是针对mysql服务器的日志文件进行备份；
增量备份：指定时间段开始进行备份。备份数据不会重复，而且所有的操作都会备份(大项目都用增量备份)
需求：有一张银行账户表，有A用户给B用户转账：A账户减少，B账户增加。但是A操作完之后断电了.
解决方案：A减少钱，但是不要立即修改数据表，B收到钱之后，同时修改数据表。
事务安全
事务：transaction,一系列要发生的连续的操作。
事务安全：一种保护连续操作同时满足(实现)的一种机制。
事务安全的意义：保证数据操作的完整性。
事务操作
事务操作分为两种：自动事务(默认的)，手动事务
手动事务：操作流程
- 开启事务：告诉系统以下所有的操作(写)不要直接写入到数据表，先存放
-
事务日志。Start transaction;
- 进行事务操作：一系列操作
- 李四账户减少
-
- 张三账户增加
-
- 关闭事务：选择性的将日志文件中操作的结果保存到数据表(同步)或者说直接清空事务日志(原来操作全部清空)
- 提交事务：同步数据表(操作成功)：commit
-
- 回滚事务：直接清空日志表(操作失败):rollback
-
关闭事务
注意：通常都会使用自动事务；
事务特性
事务有四大特性：ACID
A:atomic原子性，事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败；
C：consistency,一致性，事务操作的前后，数据表中的数据没有变化；
I：isolation，隔离性，事务操作是相互隔离不受影响的。
D：durability,持久性，数据一旦提交，不可改变，永久的改变数据表数据
锁机制：innodb默认是行锁，但是如果在事务操作的过程中，没有使用到索引，那么系统会自动全表检索数据，自动升级为表锁。
行锁:只有当前行被锁住，别的用户不能操作；
事务原理
事务操作原理：事务开启之后，所有的操作都会临时保存到事务日志里，事务日志只有在得到commit命令才会同步到数据表，其他任何情况都会清空(rollback,断电，断开连接)
回滚点
回滚点：在某个成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功：可以在当前成功的位置，设置一个点；可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。
设置回滚点语法：savepoint 回滚点名字；
回到回滚点语法：rollback to 回滚点名字；
自动事务处理
在mysql中：默认的都是自动事务处理，用户操作完会立即同步到数据表中。
自动事务：系统通过autocommit变量控制
Show variables like ‘autocommit’;
关闭事务
注意：通常都会使用自动事务；
事务特性
事务有四大特性：ACID
A:atomic原子性，事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败；
C：consistency,一致性，事务操作的前后，数据表中的数据没有变化；
I：isolation，隔离性，事务操作是相互隔离不受影响的。
D：durability,持久性，数据一旦提交，不可改变，永久的改变数据表数据
锁机制：innodb默认是行锁，但是如果在事务操作的过程中，没有使用到索引，那么系统会自动全表检索数据，自动升级为表锁。
行锁:只有当前行被锁住，别的用户不能操作；
表锁：整张表被锁住，别的用户都不能操作；
变量
变量分为两种：系统变量和自定义变量
系统变量
系统定义好的变量：大部分的时候用户根本不需要使用系统变量：系统变量是用来控制服务器的表现的：如autocommit,auto_increment_increment等；
查看系统变量
Show variables; -- 查看所有系统变量
查看具体变量值：任何一个数据返回的内容都是由select查看
Select@@变量名；
修改系统变量
修改系统变量分为两种方式：会话级别和全局级别
会话级别：临时修改，当前客户端当次连接有效
Set 变量名 = 值；Set@@变量名 = 值；
全局修改：一次修改，永久生效(对所有客户端都生效)
Set global 变量名 = 值；
如果对方(其他)客户端当前已经连上服务器，那么当次修改无效，要退出重新登录才会有效。
自定义变量
定义变量
系统为了区分系统变量，规定用户自定义变量必须使用一个@符号
Set @变量名 = 值；
在mysql中，“=”会默认的当做比较符号处理(很多地方)，mysql为了区分比较和赋值的
概念：重新定义一个新的赋值符号； ：=
Mysql允许从数据表中获取数据，然后赋值给变量：两种方式
方案1：边赋值，边查看结果
Select @变量名 ：= 字段名 from 数据源； -- 从字段中取值赋值给变量名，如果使用
=会变成比较
方案2：只有赋值不看结果：要求很严格：数据记录最多只允许获取一条：mysql不支持数组；
Select 字段列表 from 表名 into 变量列表；
所有自定义变量都是会话级别：当前客户端当次连接有效；
所有自定义变量不区分数据库(用户级别)
需求：有两张表，一张订单表，一张商品表，每生成一个订单，意味着商品的库存减少。
触发器
触发器：trigger,事先为张表绑定好一段代码，当表中的某些内容发生改变的时候(增删改)系统会自动触发代码，执行。
触发器：事件类型，触发时间，触发对象
事件类型：增删改，三种类型 insert,delete和update
触发时间:前后：before 和after
触发对象：表中的每一条记录(行)
一张表中只能拥有一种触发器时间的一种类型的触发器：最多一张表能有6个触发器。
创建触发器
在mysql高级结构中：没有大括号，都是用对应的字符号代替
触发器基本语法
--临时修改语句结束符
Delimiter 自定义符号：后续代码中只有碰到自定义符号才算结束
Create trigger 触发器名字 触发时间 事件类型 on 表名 for each row
Begin -- 代表左大括号：开始
--里面就是触发器的内容：每行内容都必须使用语句结束符：分号
End -- 代表右带括号：结束
--语句结束符
自定义符号
--将临时修改修正过来
Delimiter ;
查看触发器
查看所有触发器或者模糊匹配
Show triggers [like ‘pattern’];
可以查看触发器创建语句
Show create trigger 触发器名字；
所有的触发器都会保存一张表中：information_schema.triggers
使用触发器
触发器：不需要手动调用，而是当某种情况发生时会自动触发。（订单里面插入记录之后）
修改触发器&删除触发器
触发器不能修改，只能先删除，后新增。
Drop trigger 触发器名字；
触发器记录
触发器记录：不管触发器是否触发了，只要当某种操作准备执行，系统就会将操作的记录的当前状态和即将执行之后新的状态分别保留下来，供触发器使用：其中，要操作的当前状态保存到old中，操作之后的可能形态保存给new.
Old代表的是旧纪录，new代表的是新纪录
删除的时候是没有new的；插入的时候是没有old
Old和new都是代表记录本身：任何一条记录除了有数据，还有字段名字。
使用方式：old.字段名 /new.字段名(new代表的是假设发生之后的结果)
代码执行结构
代码执行结构有三种：顺序结构，分支结构和循环结构
分支结构
分支结构：实现准备多个代码块，按照条件选择性执行某段代码。
在mysql中只有if分支
基本语法
If 条件判断 then
-- 满足条件要执行的代码;
Else
-- 不满足条件要执行的代码;
End if;
触发器结合if分支：判断商品库存是否足够，不够不能生成订单
循环结构
循环结构：某段代码在指定条件执行重复执行。
While 循环(没有for循环)
While 条件判断 do
-- 满足条件要执行的代码
-- 变更循环条件
End while;
循环控制：在循环内部进行循环判断和控制
Mysql 中没有对应continue 和break 但是有替代品。
Iterate:迭代，类似continue,后面的代码不执行，循环重新来过；
Leave:离开，类似break，整个循环接收；
使用方式：ITrate/leave 循环名字；
-- 定义循环名字
循环名字：while 条件 do
-- 循环体
-- 循环控制
Leave/itrate 循环名字；
End while;
函数
函数：将一段代码块封装到一个结构中，在需要执行代码块的时候，调用结构执行即可。(代码复用)
函数分为两类：系统函数和自定义函数
系统函数
系统定义好的函数，直接调用即可。
任何函数都有返回值，因此函数的调用是通过select 调用。
Mysql 中，字符串的基本操作单位(最常见的是字符)
Substring：字符串截取(字符为单位)
Char_length:字符长度
Length：字节长度
Instr：判断字符串是否在某个具体的字符串中存在，存在返回位置。
Lpad：左填充，将字符串按照某个指定的填充方式，填充到指定长度(字符)
Insert：替换，找到目标位置，指定长度的字符串，替换成目标字符串
Strcmp：commpare,字符串比较
不区分大小写。
自定义函数
函数要素：函数名，参数列表(新参和实参)，返回值，函数体(作用域)
创建函数
创建语法
Create function 函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型。
Begin
-- 函数体
-- 返回值：return 类型(指定数据类型)；
End
定义函数
自定义函数与系统函数的调用方式是一样：select 函数名([实参列表])；
查看函数
查看所有函数：show function status[like ‘pattern’];
查看函数的创建语句：show create function 函数名；
修改函数&删除函数
函数只能先删除后新增，不能修改。
Drop function 函数名；
函数参数
参数分为两种：定义时的参数叫形参，调用时的参数叫实参(实参可以是数值也可以是变量)
在函数内部使用@定义的变量在函数外部也可以访问；
作用域
Mysql种的作用域与js中的作用域完全不一样
全局变量可以在任何地方使用；局部变量只能在函数内部使用。
全局变量：使用set关键字定义，使用@符号标志；
局部变量：使用declare关键字声明，没有@符号，所有的局部变量的声明，必须在函数体
开始之前。
存储过程
存储过程简称过程，procedure,是一种用来处理数据的方式。
存储过程是一种没有返回值的函数。
创建过程
Create procedure 过程名字 ([参数列表])
Begin
-- 过程体
End
查看过程
函数的查看方式完全适用于过程：关键字换成procedure
查看所有过程：show procedure status [like ‘pattern’];
查看过程创建语句：show create procedure 过程名；
调用过程
过程没有返回值：select 是不能访问的。
过程有一个专门的调用关键字：call
修改过程&删除过程
过程只能先删除，后新增
Drop procedure 过程名；
过程参数
函数的参数需要数据类型指定，过程比函数更严格。
过程还有自己的类型限定：三种类型
In:数据只是从外部传入给内部使用(值传递)：可以是数值也可以是变量
Out:只允许过程内部使用(不用外部数据)，给外部使用的(引用传递：外部的数据会被先清空才会进入到内部)：只能是变量
Inout:外部可以在内部使用，内部修改也可以给外部使用：典型的引用传递：只能传变量。
基本使用
Create procedure 过程名(in 形参名字 数据类型，out 形参名字 数据类型，inout 形参名字 数据类型)
调用：out和inout类型的参数必须传入变量，而不能是数值
正确调用：传入变量
Out和inout类型属于引用传递：内部修改会影响外部
存储过程对于变量的操作(返回)是滞后的：是在存储过程调用结束的时候，才会重新将内部修改的值赋值给外部传入的全局变量。
扫一扫
3200
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
