MySQL基础
什么是数据库
存储数据的地方，高效的存储和处理数据的介质(介质主要是两种: 磁盘和内存)
数据库的分类
关系型数据库（SQL）
非关系型数据库（NoSQLinux）
（不是关系型的数据库都叫做非关系型数据库）
不同的数据库有那些
- 关系型数据库
大型：Oracle、DB2
中型：SQL-SERVER、MySQL
小型：assess...
- 非关系型数据库
memcached
mongodb
redis
关系型数据库与非关系型的区别
- 关系型数据库
安全（保存磁盘基本不可能丢失），容易理解，比较浪费空间
- 非关系型数据库
效率高，不安全，断电丢失
什么是关系型数据库
建立在关系型模型（数学模型）上的数据库
关系模型，一种所谓建立在关系上的模型，三种：
- 数据结构：
数据存储的问题，二维表（有行有列）
- 操作指令结合
所有的SQL语句
- 完整性约束
表内数据约束（字段与字段），表与表之间的约束（外键）
关系型数据库的设计?
关系型数据库: 从需要存储的数据需求中分析
如果是一类数据(实体)应该设计成一张二维表:
表是由表头(字段名: 用来规定数据的名字)和数据部分组成(实际存储的数据单元)二维表: 行和列
分析一个教学系统：讲师负责教教学、教学生，在教师教学生
- 实体：
讲师表、学生表、班级表
- 实体中的信息：
讲师表：姓名、性别、年龄、工资
学生表：姓名、性别、学号、学科
班级表：班级名字、班级编号
- 关系型数据库
维护实体内部，实体与实体之间的联系
- 实体内部联系
每个学生都有姓名、性别、学号、学科
- 内部联系
第二行都是描述陈明这个学生的信息
- 内部约束
第二列只能放性别
关系型数据库的特点
如果表中对应的某个字段没有值（数据），但是系统依旧要分配空间
关系型数据库比较浪费空间
实体与实体的联系
每个学生肯定属于某个班级，某个班级一定有多少学生（一对多）
在学生表中添加一个班级表中的一个字段来指向班级（必须能唯一找到一个班级的信息）
学生表与班级表的关联关系就是实体与实体之间的关系
关键字说明
数据库：database
数据库系统：DBS（Database System）是一种虚拟系统，将多种内容关联起来的称呼
DBS = DBMS + DB
DBMS：数据库管理系统（Database Management System）
DB：数据库管理员（Database Administrator）
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等
Mysql数据库是一种C\S结构的软件: 客户端/服务端,
若想访问服务器必须通过客户端(服务器一直运行, 客户端在需要使用的时候运行).
客户端与MySQL服务器交互方式
- 客户端链接认证
- 客户端发送SQL指令
- 服务器接收SQL指令
处理SQL指令，返回操作结果
- 客户端接收结果
显示结果
- 断开链接
释放资源：服务器并发限制
、
添加数据库（增）
- 常用字符集GBK与UTF8
create database xiu charset utf8;//添加名为xiu的数据库，
- 数据库名字不能使用关键字或者保留字
- 如果偏要使用关键字作为数据库名，就需要用反引号括起来
create database `create` charset utf8;
- 也可以使用中文数据库，前提是服务器可以识别（不建议使用）
create database `修抗` charset utf8;
删除数据库（删）
- 删除名为xiu的数据库
drop database xiu;
- 修改数据库（改）
数据库名字是不能修改的
查看数据库（查）
- 查看所有数据库
show databases;
- 默认数据库
use xiu;
添加表（增）
- 添加表
create table xiu (
id int,
name varchar(30),
age varchar(10)
)
- if not exists：如果表名不存在，就创建，否则就不执行创建代码
create table if not exists xiu (
id int,
name varchar(30),
age varchar(10)
)
- 指定数据库添加表（数据库名：xiu----表名：kang）
create table xiu.kang (
id int,
name varchar(30),
age varchar(10)
)
删除表（删）
- 删除xiu表
drop table xiu;
修改表（改）
- 重命名：将xiu表重命名为kang表
rename table xiu to kang;
查询表（查）
- 查看所有表
show tables;
- 查看部分表：模糊查询（查询以u结尾的表）
show tables like "%u";
- 查看表的创建语句
show create table xiu;//查询xiu表
- 查看表结构（三种方法）
- 1
desc xiu;
- 2
describe xiu;
- 3
show columns from xiu;
添加一个列（增）
alter table xiu add column kang varchar(10);
删除一个列（删）
alter table xiu drop birdate;
重命名一个列（改）
alter table kang change age sex varchar(10);
修改一个列（改）
alter table xiu change kang kang varchar(10) not null;
添加数据（增）
- 添加数据
insert into xiu values (1,"小明","男"), (1,"小红","女");
- 指定字段添加数据
insert into xiu(name,age) values ("小明","男"), ("小红","女");
删除数据（删）
- 删除表中所有数据
delete from xiu;
- 删除表中指定数据
delete from xiu where id = 1;
修改数据（改）
- 修改数据
将id=1的数据的name字段的值修改为"user"
update xiu set name="user" where id=1;
查询数据（查）
- 查看数据
* 代表查询所有字段，也可以指定字段查询
select * from xiu;
查看数据库识别的字符集
- 基本上服务器什么字符集都支持
show character set;
- 查询默认识别的字符集
show variables like "character_set%";
向数据库添加中文数据
出现乱码
原因：客户端数据只能是GBK，而服务器认为是UTF8
- 改变服务器默认字符集
set character_set_client = gbk;
添加数据查询数据还是乱码
原因：数据来源服务器，解析数据是客户端，而服务器向客户端返回的数据为UFT8，而客户端只识别GBK
修改服务器向客户端数据的字符集
set character_set_results = gbk;
然后在查询数据
数据类型（列类型）
SQL中将数据类型分成了三大类: 数值类型, 字符串类型和时间日期类型
整数型
存放整型数据: 在SQL中因为更多要考虑如何节省磁盘空间, 所以系统将整型又细分成了5类:
Tinyint: 迷你整型,使用一个字节存储, 表示的状态最多为256种(常用)
Smallint: 小整型,使用2个字节存储,表示的状态最多为65536种
Mediumint: 中整型, 使用3个字节存储
Int: 标准整型, 使用4个字节存储(常用)
Bigint: 大整型,使用8个字节存储
- 创建整数表
- 插入数据
SQL中的数值全部都是默认有符号：分正负
有时候需要使用无符号数据，需要给数据类型限定 unsigned 无符号，从0开始
- 添加一个无符号类型字段
显示宽度没有特别的含义，只是默认的告诉用户显示的形式而已
显示宽度的含义在于数据不够显示宽度的时候，会自动让数据变成对应的显示宽度
通常添加一个前导来增加宽度，不改变值的大小 zerofill 零填充会导致数值自动变成无符号（就是unsigned默认不写）
- 添加一个前导字段
- 添加数据，查看前导
零填充的意义（显示宽度）保证数据的格式
小数型
- 浮点型
浮点型数据是精度型数据，超出范围就会丢失精度（四舍五入）
float: 单精度, 占用4个字节存储数据, 精度范围大概为7位左右
double: 双精度,占用8个字节存储数据, 精度方位大概为15位左右
直接float表示没有小数部分; float(M,D): M代表总长度,D代表小数部分长度, 整数部分长度为M-D
插入数据可以是直接小数，也可以是科学计数法
浮点型数据插入，整数部分不能超出长度的，小数部分可以超出长度会自动四舍五入
浮点数进行四舍五入时导致整数部分超出指定长度，系统可以允许
- 定点型
绝对的保证整数部分不会被四舍五入(不会丢失精度),小数部分有可能(理论小数部分也不会丢失精度)
decimal：定点型，M：65位，D：30位
定点数的整数部分一定不能超出长度(进位不可以),小数部分的长度可以随意超出(系统自动四舍五入)
浮点数如果进位导致长度溢出没有问题,但是定点数不行
时间日期类型
datetime: 时间日期 YYYY-mm-dd HH:ii:ss 从1000到9999年 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可以是负数,而且可以是很大的负数（-838）
year可以使用2位数插入,也可以使用4位数
Timestamp字段: 只要当前所在的记录被更新, 该字段一定会自动更新成当前时间
字符串类型
在SQL中,将字符串类型分成了6类: char、varchar、text 、 blob、enum、set
- 定长字符串
char：最大长度255个字符
char(4)：在UTF8环境下，需要 4*3=12 个字节
- 变成字符串
varchar：最大长度65536个字符（在分配空间时，按照最大空间分配：但是最终用了多少，是根据具体的数据来确定）
varchar(10)：在UTF8环境下，需要10*3+1=31个字节
- 定长与变长的存储实际空间
如何选择定长或者是变长字符串：
定长的磁盘空间比较浪费, 但是效率高: 如果数据基本上确定长度都一样, 就是使用定长, 如身份证, 电话号码, 手机号码等
变长的磁盘空间比较节省, 但是效率低: 如果数据不能确定长度(不同数据有变化), 如姓名, 地址等
- 文本字符串
如果数据量非常大，通常超过255个字符就会使用文本字符串
text：存储文字
blob：存储二进制数据
- 枚举字符串（单选框）
enum：可能出现的元素列表（65535个选项量）
如：enum(‘男’,’女’)
存储数据时,只能存储上面定义好的数据
枚举的作用：
规范数据格式: 数据只能是规定的数据中的其中一个
节省存储空间: 枚举实际存储的是数值而不是字符串本身
证明字段存储的数据是数值：
在mysql中,系统也是自动转换数据格式的: 而且基本与PHP一样(尤其是字符串转数字)
将数据取出来 + 0 就可以判断出原来的数据存的到底是字符串还是数值:如果是字符串最终结果永远为0, 否则就是其他值.
枚举原理:
枚举在进行数据规范的时候(定义的时候),系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中)
然后在进行数据插入的时候,系统自动将字符转换成对应的数字存储
然后在进行数据提取的时候, 系统自动将数值转换成对应的字符串显示
因为枚举实际存储的是数值,所以可以直接插入数值.
- 集合字符串（多选框）
集合跟枚举很类似: 实际存储的是数值,而不是字符串
set：64个元素数量
插入数据: 可以使用多个元素字符串组合, 也可以直接插入数值
MySQL记录长度
MySQL中规定: 任何一条记录最长不能超过65535个字节.(varchar永远达不到理论值)
varchar的实际存储长度能达到多少呢? 看字符集编码.
UTF8 下varchar的实际顶配：21844字符
GBK 下varchar的实际顶配：32766字符
列属性
列属性: 真正约束字段的是数据类型, 但是数据类型的约束很单一. 需要有一些额外的约束, 来更加保证数据的合法性.
列属性: NULL/NOT NULL、default、Primary key、auto_increment、unique key、comment
- 空属性NULL/NOT NULL
NULL（默认的）、NOT NULL（不为空）
虽然NULL默认的, 数据库基本都是字段为空, 但是实际上在真实开发的时候
尽可能的要保证所有的数据都不应该为空: 空数据没有意义; 空数据没有办法参与运算
- 默认值（default）
某一种数据会经常性的出现某个具体的值, 可以在一开始就指定好:
在需要真实数据的时候,用户可以选择性的使用默认值
默认值的生效: 在数据进行插入的时候,不给改字段赋值
- 主键（primary key）
用来唯一约束该字段里面的数据, 不能重复: 这种称之为主键.
- 在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)
优点：非常直接
缺点：只能使用一个字段作为主键
-
在创建表的时候, 在所有的字段之后, 使用primary key(主键字段列表)来创建主键
如果有多个字段作为主键,可以是复合主键
- 当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加.
- 主键约束
- 主键对应的字段中数据不允许重复
- 删除主键
- 没有办法更新主键，只能删除在添加主键
- 自动增长（auto_increment）
当对应的字段,不给值,或者说给默认值,或者给NULL的时候, 会自动的被系统触发自动增长（auto_increment）
系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段.
自增长通常是跟主键搭配
- 任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值)
- 自动增长的字段必须为整数
- 一张表中最多一个自动增长
- 删除自动增长
- 唯一键（unique key）
- unique key 与 primary key 的区别
unique key：可以为空，多个为空、可以约束多个字段不重复
primary key：不能为空、只能约束一个字段不重复
- 添加唯一键（1）
在创建表的时候，字段之后直接更unique或unique key
- 添加唯一键（2）
在所有字段后面增加unique key
- 添加唯一键（3）
在创建表之后添加唯一键
- 删除唯一键
索引
几乎所有的索引都是建立在字段之上
系统根据某种算法, 将已有的数据(未来可能新增的数据),单独建立一个文件
文件能够实现快速的匹配数据, 并且能够快速的找到对应表中的记录.
- 索引的意义
提升查询数据的效率
约束数据的有效性（唯一性等）
- 增加索引的前提条件:
索引本身会产生索引文件(有时候有可能比数据文件还大) ,会非常耗费磁盘空间
- 索引使用的地方
某个字段需要作为查询的条件经常使用, 那么可以使用索引(一定会想办法增加);
某个字段需要进行数据的有效性约束, 也可能使用索引(主键,唯一键)
- Mysql中提供了多种索引
主键索引: primary key
唯一索引: unique key
全文索引: fulltext index
普通索引: index
- 全文索引
针对文章内部的关键字进行索引
全文索引最大的问题: 在于如何确定关键字
英文很容易：英文单词与单词之间有空格
中文很难:：没有空格, 而且中文可以各种随意组合(分词: sphinx)
关系
将实体与实体的关系, 反应到最终数据库表的设计上来
所有的关系都是指的表与表之间的关系.
- 将关系分成三种:
一对一
一对多(多对一)
多对多
一对一
一对一：一张表的一条记录一定只能与另外一张表的一条记录进行对应
学生表：姓名、性别、年龄、身高、体重、婚姻状况、 籍贯、家庭住址、紧急联系人
- 以上关系:
姓名,性别,年龄,身高,体重属于常用数据
婚姻,籍贯,住址和联系人属于不常用数据
- 问题
如果每次查询都是查询所有数据,不常用的数据就会影响效率, 实际又不用
- 解决方案
将常用的和不常用的信息分离存储,分成两张表
常用信息表
不常用信息表
保证不常用信息与常用信息一定能够对应上: 找一个具有唯一性(确定记录)的字段来共同连接两张表
一对多
一张表中有一条记录可以对应另外一张表中的多条记录; 但是返回过, 另外一张表的一条记录只能对应第一张表的一条记录. 这种关系就是一对多或者多对一.
母亲与孩子的关系: 母亲,孩子两个实体
妈妈表
孩子表
- 以上关系:
一个妈妈可以在孩子表中找到多条记录(也有可能是一条); 但是一个孩子只能找到一个妈妈: 是一种典型的一对多的关系.
- 问题
解决了实体的设计表问题, 但是没有解决关系问题: 孩子找不出妈,妈也找不到孩子.
- 解决方案
在某一张表中增加一个字段,能够找到另外一张表的中记录: 应该在孩子表中增加一个字段指向妈妈表: 因为孩子表的记录只能匹配到一条妈妈表的记录.
妈妈表
孩子表
多对多
一张表中(A)的一条记录能够对应另外一张表(B)中的多条记录; 同时B表中的一条记录也能对应A表中的多条记录: 多对多的关系
老师教学: 老师和学生
老师表
学生表
- 以上关系
现了实体的设计, 但是没有维护实体的关系
- 问题
一个老师教过多个学生; 一个学生也被多个老师教过.
- 解决方案
在学生表中增加老师字段: 不管在哪张表中增加字段, 都会出现一个问题: 该字段要保存多个数据, 而且是与其他表有关系的字段, 不符合表设计规范: 增加一张新表: 专门维护两张表之间的关系
老师表
学生表
中间关系表: 老师与学生的关系
增加中间表之后: 中间表与老师表形成了一对多的关系: 而且中间表是多表,维护了能够唯一找到一表的关系; 同样的,学生表与中间表也是一个一对多的关系: 一对多的关系可以匹配到关联表之间的数据.
学生找老师: 找出学生id -> 中间表寻找匹配记录(多条) -> 老师表匹配(一条)
老师找学生: 找出老师id -> 中间表寻找匹配记录(多条) -> 学生表匹配(一条)
范式
范式: Normal Format, 是一种离散数学中的知识, 是为了解决一种数据的存储与优化的问题: 保存数据的存储之后, 凡是能够通过关系寻找出来的数据,坚决不再重复存储: 终极目标是为了减少数据的冗余.
范式: 是一种分层结构的规范, 分为六层: 每一次层都比上一层更加严格: 若要满足下一层范式,前提是满足上一层范式.
六层范式: 1NF,2NF,3NF...6NF, 1NF是最底层,要求最低;6NF最高层,最严格.
Mysql属于关系型数据库: 有空间浪费: 也是致力于节省存储空间: 与范式所有解决的问题不谋而合: 在设计数据库的时候, 会利用到范式来指导设计.
但是数据库不单是要解决空间问题,要保证效率问题: 范式只为解决空间问题, 所以数据库的设计又不可能完全按照范式的要求实现: 一般情况下,只有前三种范式需要满足.
范式在数据库的设计当中是有指导意义: 但是不是强制规范.
- 1NF
第一范式要求字段的数据具有原子性: 不可再分.
讲师代课表
- 问题
如果需求是将数据查出来之后,要求显示一个老师从什么时候开始上课,到什么时候节课: 需要将代课时间进行拆分：不符合1NF, 数据不具有原子性, 可以再拆分.
- 解决方案
将代课时间拆分成两个字段就解决问题.
- 2NF
第二范式就是要解决表设计不允许出现部分依赖
有复合主键(多字段主键), 且表中有些字段并不是由整个主键来确定, 而是依赖主键中的某个字段(主键的部分): 存在字段依赖主键的部分的问题, 称之为部分依赖:
讲师带课表
- 问题
讲师没有办法作为独立主键，所以需要结合班级才能作为主键(复合主键)：代课时间、开始、结束字段都与当前的复合主键联系
但是性别并不依赖班级、教室不依赖讲师、性别只依赖讲师、室只依赖班级：出现了性别和教室依赖主键中的一部分: 部分依赖.不符合第二范式.
- 解决方案
可以将性别与讲师单独成表, 班级与教室也单独成表.
- 解决方案
取消复合主键, 使用逻辑主键
ID = 讲师 + 班级(业务逻辑约束: 复合唯一键)
- 3NF
要满足第三范式,必须满足第二范式
第三范式就是要解决传递依赖的问题
如果表设计中存在一个字段, 并不直接依赖主键,而是通过某个非主键字段依赖,最终实现依赖主键: 把这种不是直接依赖主键,而是依赖非主键字段的依赖关系称之为传递依赖.
讲师带课表
- 问题
性别依赖讲师、讲师依赖主键、 教室依赖班级、班级依赖主键、性别和教室都存在传递依赖.
- 解决方案
将存在传递依赖的字段,以及依赖的字段本身单独取出,形成一个单独的表, 然后在需要对应的信息的时候, 使用对应的实体表的主键加进来.
讲师代课表
讲师表
班级表
讲师表: 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 数量];
- 删除数据但是不删除表结构
查询数据
- 基本语法
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...;
从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留:(记录数和字段数),将这种结果成为: 笛卡尔积(交叉连接): 笛卡尔积没什么卵用, 所以应该尽量避免.
子查询: 数据的来源是一条查询语句(查询语句的结果是二维表)
Select * from (select 语句) as 表名;
- Where子句
Where子句: 用来判断数据,筛选数据.
Where子句返回结果: 0或者1, 0代表false,1代表true.
- Group by子句
Group by:分组的意思, 根据某个字段进行分组(相同的放一组,不同的分到不同的组)
基本语法: group by 字段名;
分组的意思: 是为了统计数据(按组统计: 按分组字段进行数据统计)
SQL提供了一系列统计函数
Count(): 统计分组后的记录数: 每一组有多少记录
Max(): 统计每组中最大的值
Min(): 统计最小值
Avg(): 统计平均值
Sum(): 统计和
Count函数: 里面可以使用两种参数: *代表统计记录,字段名代表统计对应的字段(NULL不统计)
分组会自动排序: 根据分组字段:默认升序
Group by 字段 [asc|desc]; -- 对分组的结果然后合并之后的整个结果进行排序
多字段分组: 先根据一个字段进行分组,然后对分组后的结果再次按照其他字段进行分组
有一个函数: 可以对分组的结果中的某个字段进行字符串连接(保留该组所有的某个字段): group_concat(字段);
回溯统计: with rollup
任何一个分组后都会有一个小组, 最后都需要向上级分组进行汇报统计
根据当前分组的字段. 这就是回溯统计: 回溯统计的时候会将分组字段置空.
多字段回溯: 考虑第一层分组会有此回溯: 第二次分组要看第一次分组的组数, 组数是多少,回溯就是多少,然后加上第一层回溯即可.
- Having子句
Having子句: 与where子句一样: 进行条件判断的.
Where是针对磁盘数据进行判断: 进入到内存之后,会进行分组操作: 分组结果就需要having来处理.
Having能做where能做的几乎所有事情, 但是where却不能做having能做的很多事情.
- 分组统计的结果或者说统计函数都只有having能够使用.
- Having能够使用字段别名: where不能: where是从磁盘取数据,而名字只可能是字段名: 别名是在字段进入到内存后才会产生.
- 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: offset = (页码 - 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 右表;
自然外连接: 左表 natural left/right join 右表;
其实, 内连接和外连接都可以模拟自然连接: 使用同名字段,合并字段
左表 left/right/inner join 右表 using(字段名); -- 使用同名字段作为连接条件: 自动合并条件
多表连接: A表 inner join B表 on 条件 left join C表 on条件 ...
执行顺序: A表内连接B表,得到一个二维表, 左连接C表形成二维表...
PHP操作MySQL
事实上: PHP本身不能操作mysql, 但是PHP有扩展可以实现操作mysql: PHP借助扩展来实现操作mysql.
PHP操作mysql的扩展还挺多: mysql, mysqli和PDO扩展.
Mysql扩展: 纯面向过程, 里面都是函数,加载扩展后可以调用函数.(当前只能使用面向过程)
Mysqli扩展: 面向过程+面向对象,里面有函数也有类, 加载扩展后可以选择调用函数或者调用类操作.
PDO: 纯面向对象,只有类, 加载后只能调用类.
Mysql扩展在搭建服务器的时候就已经加载开启.不再进行扩展加载.
建立和断开链接
$mysqli = new mysqli();//实例化mysqli类 $mysqli -> connect("localhost","root","123");//链接数据库 $mysqli -> select_db("text");//选择text数据库 $mysqli -> close();//关闭链接
获取错误信息
- 获取错误码
$mysqli = new mysqli("localhost","root","123","test"); echo $mysqli -> errno;//没有错误返回0
- 获取错误信息
$mysqli = new mysqli("localhost","root","123");//实例化mysqli类 $mysqli -> select_db("text");//选择text数据库 if($mysqli -> errno){ echo $mysqli -> error;//Unknown database 'text' 没有text数据库 } $mysqli -> close();//关闭链接
- 在单独的文件中存储链接信息
//mysql.connect.php文件 <?php $mysqli = new mysqli("localhost","root","123","test"); ?>
- 在必要时包含此文件
<?php include "mysql.connect.php";//调用mysql.connect.php文件 ?>
与数据库交互
- 向数据库发送查询
·获取数据
query()方法
mysqli_store_result 较高的内存和处理需求，查询整个结果集（默认）
mysqli_use_result 较低的内存需求，查询几行结果集
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "select id, name, age from xiu";//创建查询语句 $result = $mysqli -> query($query,MYSQLI_STORE_RESULT);//$query()方法负责将query发送到数据库 while(list($id,$name,$age) = $result -> fetch_row()){//fetch_row()方法将获得的值生成一个数组 printf("%d*%d*%d",$id,$name,$age); } $mysqli -> close();//关闭数据库链接
·插入、删除或更新数据
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "alter table xiu add column birdate date";//创建查询语句 $mysqli -> query($query);//$query()方法负责将query发送到数据库 echo $mysqli -> affected_rows;//提示影响的多少行 $mysqli -> close();//关闭数据库链接
·释放查询内存
有时可能会获取一个特别大的结果集，会占用大量内存，使用free()方法释放占用的内存
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "select id, name, age from xiu";//创建查询语句 $result = $mysqli -> query($query,MYSQLI_STORE_RESULT);//$query()方法负责将query发送到数据库 while(list($id,$name,$age) = $result -> fetch_row()){//fetch_row()方法将获得的值生成一个数组 printf("%d*%d*%d",$id,$name,$age); } $mysqli -> free();//释放内存
- 解析查询结果
·将结果放在对象中
fetch_object()方法将结果集放入对象中
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "select id, name, age from xiu";//创建查询语句 $result = $mysqli -> query($query);//$query()方法负责将query发送到数据库 while($xiu = $result -> fetch_object()){//fetch_object()方法将结果集放入对象中 $id = $xiu -> id; $name = $xiu -> name; $age = $xiu -> age; printf("id：%d，name：%s，age：%d",$id,$name,$age); } $mysqli -> close();//关闭数据库链接
·使用索引数组和关联数组获取结果
fetch_array()将结果集放入数组
MYSQLI_ASSOC 将行作为一个关联数组返回，键由字段表示，值由字段内容表示
MYSQLI_NUM 将行作为一个数字索引数组返回，元素的属性有查询中的顺序决定
MYSQLI_BOTH 将行作为关联数组和数组索引数组返回
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "select id, name, age from xiu";//创建查询语句 $result = $mysqli -> query($query);//$query()方法负责将query发送到数据库 while($xiu = $result -> fetch_array(MYSQLI_ASSOC)){//fetch_array()方法将结果集放入数组 $id = $xiu["id"]; $name = $xiu["name"]; $age = $xiu["age"]; printf("id：%d，name：%s，age：%d",$id,$name,$age); } $mysqli -> close();//关闭数据库链接
- 确定所选择的行和受影响的行
·确定返回的行数
num_rows属性返回查询了多少行数据
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "select id, name, age from xiu";//创建查询语句 $result = $mysqli -> query($query);//$query()方法负责将query发送到数据库 echo $result -> num_rows;//num_rows属性查询返回了多少行数据 $mysqli -> close();//关闭数据库链接
·确定受影响的行数
affected_rows属性返回受insert、update或delet查询影响的行数
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "insert into xiu values(4,'user',20)";//创建查询语句 $mysqli -> query($query);//$query()方法负责将query发送到数据库 echo $mysqli -> affected_rows;//affected_rows属性返回影响的行数 $mysqli -> close();//关闭数据库链接
- 处理准备语句
捆绑参数
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "select id,name,age from xiu";//创建查询即相对应的占位符(?) $stmt = $mysqli -> stmt_init();//创建语句对象 $stmt -> prepare($query);//为执行准备语句 $stmt -> execute();//执行语句 $stmt -> bind_result($id,$name,$age);//绑定结果参数 while($stmt -> fetch()){//fetch()获取准备语句结果的每一行 printf("id：%d，name：%s，age：%d",$id,$name,$age); } $stmt -> close();//恢复语句资源 $mysqli -> close();//关闭数据库链接
·捆绑变量
$mysqli = new mysqli("localhost","root","123","test");//链接数据库服务器并选择test数据库 $query = "select id,name,age from xiu";//创建查询即相对应的占位符(?) $stmt = $mysqli -> stmt_init();//创建语句对象 $stmt -> prepare($query);//为执行准备语句 $stmt -> execute();//执行语句 $stmt -> bind_result($id,$name,$age);//绑定结果参数 while($stmt -> fetch()){//fetch()获取准备语句结果的每一行 printf("id：%d，name：%s，age：%d",$id,$name,$age); } $stmt -> close();//恢复语句资源 $mysqli -> close();//关闭数据库链接
综合应用
PHP与MySQL的登陆与分页
login.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Document</title> </head> <body> <form action="login.php" method="POST"/> <p>账号：</p> <input type="text" name="username"/> <p>密码：</p> <input type="password" name="password" /> <p> <input type="submit" value="登陆" name="submit"/> </p> </form> </body> </html>
login.php
<?php if(isset($_POST["submit"])){ //获取表单的数据，并去除两端的空格 $username = trim($_POST["username"]); $password = trim($_POST["password"]); // 判断表单数据是否为空 if($username == "" || $password == ""){ // 设置重定向，返回登陆界面 header("Refresh:3;url=login.php"); echo "用户名或者密码不能为空"; // 结束程序运行 exit; }else { // 链接数据库 $mysqli = new mysqli("localhost","root","123","test"); // 添加SQL语句 $query = "select * from xiu where `password` = MD5('{$password}') and username = '{$username}'"; // 将SQL语句发送到数据库 $result = $mysqli -> query($query); // 判断数据库是否响应了0条数据 if($result -> num_rows == 0){ // 设置重定向，返回登陆界面 header("Refresh:3;url=login.php"); echo "用户名或者密码错误"; // 结束程序运行 exit; }else { // 设置重定向，登陆成功 header("Refresh:1;url=index.php"); } // 断开数据库链接 $mysqli->close(); } }else { // 引用用户静态登陆模板 include "login.html"; } ?>
index.php
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Document</title> </head> <body> <table border="1px" style="margin:100px auto"> <tr> <td>序号</td> <td>姓名</td> <td>年龄</td> <td>操作</td> </tr> <?php while($xiu = $result -> fetch_array()){ ?> <tr> <td><?php echo $xiu["id"]; ?></td> <td><?php echo $xiu["name"]; ?></td> <td><?php echo $xiu["age"]; ?></td> <td><a href="#">操作</a>||<a href="#">删除</a></td> </tr> <?php } ?> <tr> <td><a href="index.php ? page = 1">首页</a></td> <td><a href="index.php ? page = <?php echo $prev ?>">上一页</a></td> <td><a href="index.php ? page = <?php echo $next ?>">下一页</a></td> <td><a href="index.php ? page = <?php echo $pages ?>">尾页</a></td> </tr> </table> </body> </html>
index.php
<?php $mysqli = new mysqli("localhost","root","123","test"); $page = isset($_GET["page"]) ? $_GET["page"] : 0; //设置每页条数 $length = 2; //求出每页偏移量 $offset = ($page - 1) * $length; //获取数据库总条数 $zong = "select count(*) from kang"; $count = $mysqli -> query($zong); $counts = ($count -> fetch_array())[0]; //获取总页数（总条数 / 每页页数 = 总页数）ceil()向上取整 $pages = ceil($counts / $length); //上一页 $prev = $page > 1 ? $page - 1 : 1; //下一页 $next = $page < $pages ? $page + 1 : $pages; // 创建SQL语句 $query = "select * from kang limit {$offset},{$length}"; $result = $mysqli -> query($query); include "index.html"; $mysqli -> close(); ?>
外键
如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键（foreign key）
一张表可以有多个外键
-
增加外键
创建表的时候添加外键
创建表的时候增加外键：在所有的表字段之后,使用foreign key(外键字段) references 外部表(主键字段)
查看外键名
创建表之后添加外键(但是要考虑数据的问题)
在新增表之后增加外键: 修改表结构
Alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);
-
修改外键&删除外键
外键不可修改: 只能先删除后新增
Alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键,但是名字不能相同
外键删除不能通过查看表结构体现，应该通过查看表的创建语句查看
-
外键作用
外键默认的作用有两点: 一个对父表,一个对子表(外键字段所在的表)
对子表约束: 子表数据进行写操作(增和改)的时候, 如果对应的外键字段在父表找不到对应的匹配: 那么操作会失败.(约束子表数据操作)
对父表约束: 父表数据进行写操作(删和改: 都必须涉及到主键本身), 如果对应的主键在子表中已经被数据所引用, 那么就不允许操作
-
外键条件
-
外键要存在: 首先必须保证表的存储引擎是innodb(默认的存储引擎): 如果不是innodb存储引擎,那么外键可以创建成功,但是没有约束效果.
-
外键字段的字段类型(列类型)必须与父表的主键类型完全一致.
-
一张表中的外键名字不能重复.
-
增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应.
-
外键约束
所谓外键约束: 就是指外键的作用.
之前所讲的外键作用: 是默认的作用; 其实可以通过对外键的需求, 进行定制操作.
外键约束有三种约束模式: 都是针对父表的约束
District: 严格模式(默认的), 父表不能删除或者更新一个已经被子表数据引用的记录
Cascade: 级联模式: 父表的操作, 对应子表关联的数据也跟着被删除
Set null: 置空模式: 父表的操作之后,子表对应的数据(外键字段)被置空
通常的一个合理的做法(约束模式): 删除的时候子表置空, 更新的时候子表级联操作
指定模式的语法
Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;
更新操作: 级联更新
删除操作: 置空
删除置空的前提条件: 外键字段允许为空(如果不满足条件,外键无法创建)
外键虽然很强大, 能够进行各种约束: 但是对于PHP来讲, 外键的约束降低了PHP对数据的可控性: 通常在实际开发中, 很少使用外键来处理.
联合查询
联合查询: 将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)
基本语法
多条select语句构成: 每一条select语句获取的字段数必须严格一致(但是字段类型无关)
Select 语句1
Union [union选项]
Select语句2...
Union选项: 与select选项一样有两个
All: 保留所有(不管重复)
Distinct: 去重(整个重复): 默认的
联合查询只要求字段一样, 跟数据类型无关
意义
联合查询的意义分为两种:
1. 查询同一张表,但是需求不同: 如查询学生信息, 男生身高升序, 女生身高降序.
2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.
Order by使用
在联合查询中: order by不能直接使用,需要对查询语句使用括号才行
若要orderby生效: 必须搭配limit: limit使用限定的最大数即可.
子查询
子查询: sub query, 查询是在某个查询结果之上进行的.(一条select语句内部包含了另外一条select语句).
子查询分类
子查询有两种分类方式: 按位置分类; 按结果分类
按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置
From子查询: 子查询跟在from之后
Where子查询: 子查询出现where条件中
Exists子查询: 子查询出现在exists里面
按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)
标量子查询: 子查询得到的结果是一行一列
列子查询: 子查询得到的结果是一列多行
行子查询: 子查询得到的结果是多列一行(多行多列)
上面几个出现的位置都是在where之后
表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)
标量子查询
需求: 知道班级名字为PHP0710,想获取该班的所有学生.
-
确定数据源: 获取所有的学生
Select * from my_student where c_id = ?;
-
获取班级ID: 可以通过班级名字确定
Select id from my_class where c_name = ‘PHP0710’; -- id一定只有一个值(一行一列)
标量子查询实现
列子查询
需求: 查询所有在读班级的学生(班级表中存在的班级)
-
确定数据源: 学生
Select * from my_student where c_id in (?);
-
确定有效班级的id: 所有班级id
Select id from my_class;
列子查询
列子查询返回的结果会比较: 一列多行, 需要使用in作为条件匹配: 其实在mysql中有还有几个类似的条件: all, some, any
=Any ==== in; -- 其中一个即可
Any ====== some; -- any跟some是一样
=all ==== 为全部
肯定结果
否定结果
行子查询
行子查询: 返回的结果可以是多行多列(一行多列)
需求: 要求查询整个学生中,年龄最大且身高是最高的学生.
-
确定数据源
Select * from my_student where age = ? And height = ?;
-
确定最大的年龄和最高的身高;
Select max(age),max(height) from my_student;
行子查询: 需要构造行元素: 行元素由多个字段构成
表子查询
表子查询: 子查询返回的结果是多行多列的二维表: 子查询返回的结果是当做二维表来使用
需求: 找出每一个班最高的一个学生.
-
确定数据源: 先将学生按照身高进行降序排序
Select * from my_student order by height desc;
-
从每个班选出第一个学生
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 数据源;
Fields: 字段处理
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服务器的日志文件进行备份
增量备份: 指定时间段开始进行备份., 备份数据不会重复, 而且所有的操作都会备份(大项目都用增量备份)
回顾
外键: 关联关系(表与表之间: 表中字段指向另外一张表的主键)
外键条件: 字段类型必须一致, 存储引擎必须为innodb
外键约束:
子表约束: 不能插入父表不存在的记录
父表约束: 三种约束模式(district, cascade,set null), on delete set null on update cascade
联合查询: union, 多表合并和单表不同查询条件
联合查询使用order by: select语句必须使用括号; 还必须配合limit
子查询: 查询语句中有查询语句
分类: 按位置(from,where和exists),按返回结果(用途):标量, 列,行和表
视图: view
节省SQL语句; 安全性控制
视图本质: 虚拟表, 有结构无数据
视图数据操作: 多表只能改, 单表可以增删改(增删有条件限制)
视图算法: undefined未定义, temptable临时表和merge合并
文件备份与还原
文件备份: 存储引擎(myisam适用)
单表数据备份: 只能备份数据
SQL备份: 备份的是SQL指令(mysqldump.exe客户端备份)
增量备份: 备份系统日志文件
需求: 有一张银行账户表, 有A用户给B用户转账: A账户先减少, B账户增加. 但是A操作完之后断电了.
解决方案: A减少钱, 但是不要立即修改数据表, B收到钱之后, 同时修改数据表.
事务安全
事务: transaction, 一系列要发生的连续的操作
事务安全: 一种保护连续操作同时满足(实现)的一种机制
事务安全的意义: 保证数据操作的完整性
事务操作
事务操作分为两种: 自动事务(默认的), 手动事务
手动事务: 操作流程
-
开启事务: 告诉系统以下所有的操作(写)不要直接写入到数据表, 先存放到事务日志
Start transaction;
-
进行事务操作: 一系列操作
a) 李四账户减少
b) 张三账户增加
-
关闭事务: 选择性的将日志文件中操作的结果保存到数据表(同步)或者说直接清空事务日志(原来操作全部清空)
a) 提交事务: 同步数据表(操作成功): commit;
b) 回滚事务: 直接清空日志表(操作失败): rollback;
事务原理
事务操作原理: 事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表,其他任何情况都会清空(rollback, 断电, 断开连接)
回滚点
回滚点: 在某个成功的操作完成之后, 后续的操作有可能成功有可能失败, 但是不管成功还是失败,前面操作都已经成功: 可以在当前成功的位置, 设置一个点: 可以供后续失败操作返回到该位置, 而不是返回所有操作, 这个点称之为回滚点.
设置回滚点语法: savepoint 回滚点名字;
回到回滚点语法: rollback to 回滚点名字;
自动事务处理
在mysql中: 默认的都是自动事务处理, 用户操作完会立即同步到数据表中.
自动事务: 系统通过autocommit变量控制
Show variables like ‘autocommit’;
关闭自动提交: set autocommit = off/0;
再次直接写操作
自动关闭之后,需要手动来选择处理: commit提交, rollback回滚
注意: 通常都会使用自动事务
事务特性
事务有四大特性: ACID
A: Atomic原子性, 事务的整个操作是一个整体, 不可分割,要么全部成功,要么全部失败;
C: Consistency, 一致性, 事务操作的前后, 数据表中的数据没有变化
I: Isolation, 隔离性, 事务操作是相互隔离不受影响的.
D: Durability, 持久性, 数据一旦提交, 不可改变,永久的改变数据表数据
锁机制: innodb默认是行锁, 但是如果在事务操作的过程中, 没有使用到索引,那么系统会自动全表检索数据, 自动升级为表锁
行锁: 只有当前行被锁住, 别的用户不能操作
表锁: 整张表被锁住, 别的用户都不能操作
变量
变量分为两种: 系统变量和自定义变量
系统变量
系统定义好的变量: 大部分的时候用户根本不需要使用系统变量: 系统变量是用来控制服务器的表现的: 如autocommit, auto_increment_increment等
查看系统变量
Show variables; -- 查看所有系统变量
查看具体变量值: 任何一个有数据返回的内容都是由select查看
Select @@变量名;
修改系统变量
修改系统变量分为两种方式: 会话级别和全局级别
会话级别: 临时修改, 当前客户端当次连接有效
Set 变量名 = 值;/Set @@变量名 = 值;
全局级别: 一次修改,永久生效(对所有客户端都生效)
Set global 变量名 = 值;
如果对方(其他)客户端当前已经连上服务器,那么当次修改无效,要退出重新登录才会生效
自定义变量
定义变量
系统为了区分系统变量, 规定用户自定义变量必须使用一个@符号
Set @变量名 = 值;
自定义变量也是类似系统变量查看
Select @变量名;
在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代表的是假设发生之后的结果)
查看触发器的效果
如果触发器内部只有一条要执行的SQL指令, 可以省略大括号(begin和end)
Create trigger 触发器名字 触发时间 事件类型 on 表名 for each row
一条SQL指令;
触发器: 可以很好的协调表内部的数据处理顺序和关系. 但是从PHP角度出发, 触发器会增加数据库维护的难度, 所以较少使用触发器.
代码执行结构
代码执行结构有三种: 顺序结构, 分支结构和循环结构
分支结构
分支结构: 实现准备多个代码块, 按照条件选择性执行某段代码.
在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/iterate 循环名字;
End while;
函数
函数: 将一段代码块封装到一个结构中, 在需要执行代码块的时候, 调用结构执行即可.(代码复用)
函数分为两类: 系统函数和自定义函数
系统函数
系统定义好的函数, 直接调用即可.
任何函数都有返回值, 因此函数的调用是通过select调用.
Mysql中,字符串的基本操作单位(最常见的是字符)
Substring: 字符串截取(字符为单位)
char_length: 字符长度
Length: 字节长度
Instr: 判断字符串是否在某个具体的字符串中存在, 存在返回位置
Lpad: 左填充, 将字符串按照某个指定的填充方式,填充到指定长度(字符)
Insert: 替换,找到目标位置,指定长度的字符串,替换成目标字符串
Strcmp: compare,字符串比较
自定义函数
函数要素: 函数名, 参数列表(形参和实参), 返回值, 函数体(作用域)
创建函数
创建语法
Create function 函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型
Begin
-- 函数体
-- 返回值: return 类型(指定数据类型);
End
定义函数
自定义函数与系统函数的调用方式是一样: select 函数名([实参列表]);
查看函数
查看所有函数: show function status [like ‘pattern’];
查看函数的创建语句: show create function 函数名;
修改函数&删除函数
函数只能先删除后新增,不能修改.
Drop function 函数名;
函数参数
参数分为两种: 定义时的参数叫形参, 调用时的参数叫实参(实参可以是数值也可以是变量)
形参: 要求必须指定数据类型
Function 函数名(形参名字 字段类型) returns 数据类型
在函数内部使用@定义的变量在函数外部也可以访问
作用域
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类型的参数必须传入变量,而不能是数值
正确调用: 传入变量
存储过程对于变量的操作(返回)是滞后的: 是在存储过程调用结束的时候,才会重新将内部修改的值赋值给外部传入的全局变量.
测试: 传入数据1,2,3: 说明局部变量与全局变量无关
最后: 在存储过程调用结束之后, 系统会将局部变量重复返回给全局变量(out和inout)
