《php与mysql权威指南》第三部分02-白红宇

《php与mysql权威指南》第三部分02

阅读量：6691 次

发布时间：2019-06-25

本文共 11300 字，大约阅读时间需要 37 分钟。

　　14.1 mysql数据类型

　　1.数值类型:

　　五种整型：tinyint,smallint,mediumint,int,bigint分别为1,2,3,4,8字节数

　　三种浮点型：float,double,decimal分别4,8,m字节

　　# 声明一个整数类型时，要给它指定一个显示宽度，只是以多少宽度显示，并不是数值的款单，所占空间是固定的，例如bigint(4),显示设置为4，所占还是8个字节，最大取值不变

　　2.字符串类型

　　char(M);//m个字节，其中0<=m<=255

　　varchar(M);//L+1个字节，其中L<=M,且0<=m<=255

　　binary(M);//m个字节，其中0<=m<=255

　　varbinary(M);//L+1个字节，其中L<=M,且0<=m<=255

　　tinyblob,tinytext;//L+1个字节,其中L<28

　　blob,text;//L+2个字节,其中L<216

　　mediumblob,mediumtext;//L+3个字节,其中L<224

　　longblob,longtext;//L+4个字节,其中L<232

　　enum(v1,v2,...);//1或2个字节，取决于枚举的个数

　　set(v1,v2,...);//1,2,3,4,8个字节，取决于set成员数目

　　# char是固定长度类型，varchar是可变长度类型

　　# set与enum区别：set允许成员同事出现，enum只允许出现一个成员

　　14.2 字符集支持

　　查看支持的字符集：show character set;

　　查找名字以utf8开头的校对规则：show collation like 'utf8%';

　　# ci(大小写不敏感),cs(大小写敏感),bin(二元)

　　14.3 索引的使用

　　myISAM表，数据行存放在数据文件里，索引值放在索引文件里。InnoDB->索引是排好序的数组，数据行与索引值存放在同一个文件里，同一个表空间中

　　1.数据库索引

　　优点：查询优化，唯一性，文本搜索

　　2.主要索引介绍

　　主键索引：

　　唯一索引：

　　常规索引：

　　全文索引：

　　# 每个表只能有一个主键索引，可以有多个唯一索引

　　# 缺点：占用磁盘的空间多，降低插入更新和删除的操作速度

　　3.最佳实践

　　只对where和order需要的列添加索引

　　创建index(first,last)就再不要创建index(first)索引

　　索引列not null

　　不适用索引的查询，使用选项-log-long-format来记录日志，然后检查日志文件对查询进行优化

　　explain语句有助于确定mysql如何执行查询

　　# explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句

　　最好使用唯一索引

　　索引长度尽量短

　　充分利用左侧前缀(复合索引)

　　索引不宜过多

　　查看日志

　　4.新建索引：create index index_name on table (field1,field2);

　　14.4 事务处理

　　事务开始、提交、回滚

　　原则：原子性，一致性，隔离性，持久性

　　# InnoDB支持事务

　　1.非事务实现方法：缩影数据表

　　lock_tables/unlock tables

　　2.事务实现方法

　　begin/commit/rollback

　　set autocommit=1;//开启自动提交，set autocommit=0;//关闭自动提交，默认开启

　　14.5 外键与数据的完整性

　　外键：是把一个表中的索引列与另一个表中的索引列关联起来

　　foreign key ('index_name') referrences 'tbl_name' ('index_columns') [on delete action][on update action]

　　# 其中index_name为字表的外键字段名，tbl_name为父表名称，index_columns为外键所关联的父表字段

　　# on delete说明当父表记录被删除时，子表的变化，有如下值：

　　on delete casecade->与之关联的子表记录也被删除

　　on delete set null->与之关联的子表外键被设置成null

　　on delete no action->产生错误并回滚delete语句

　　# on update说明当父表记录被更改时，子表的变化，有如下值

　　on update casecade->与之关联的子表外键也被更改

　　on update set null->与之关联的子表外键被设置成null

　　on update no action->产生错误并回滚update语句

　　//构成外键关系的父表和子表必需是被索引的

　　//父表索引与子表索引相对应的数据列的类型必需是兼容的

　　14.6 数据库内部语句和语法

　　1.alter {database|schema}[db_name] alter_specification[,alter_specification1]...

　　其中alter_specification->[default] charater set charset_name|[default] collate set collation_name

　　2.alter [ignore] table tbl_name alter_specification

　　其中alter_specification常用功能如下:

　　1)添加列：alter table tbl_name add [column] column_definition

　　2)添加索引:alter table tbl_name add [index|primary key|fulltext] [index_name] [index_type] [index_col_name,...] ,其中索引类型如下

　　A.index->基本索引

　　B.unique->唯一索引

　　C.primary key->主键索引

　　D.fulltext->全文本搜索

　　3)增加外键：alter table tbl_name add foreign key [index_name] [index_col_name,...];//只有InnoDB支持外键

　　4)更改表信息：alter table tbl_name change old_col_nmae column_definition

　　5)删除表信息:alter table tbl_name drop...

　　6)停止更新、重新创建索引(disable keys/enable keys):在大量插入|更新操作前建议先禁用索引，完成后重新创建索引

　　7)分区：

　　create table t1(

　　id int ,

　　year_col int

　　)

　　partition by range(year_col)(

　　partition p0 values less then (1991),

　　partition p1 values less then (1995),

　　partition p2 values less then (2000),

　　);

　　//新增分区

　　alter table t1 add partition p3 values less then (2002);

　　//删除分区

　　alter table drop partition p0,p1;

　　# 删除时分区内的数据也被取消

　　3.创建数据库:create database {database|schema} [if not exists] db_name[(create_specification)]

　　4.创建索引:create [unique|fulltext|spartial] index index_name [using index_type] on tbl_name(index_col_name),其中　　index_col_name->col_name[(length)][asc|desc]

　　5.创建表:create [temporary] table [if not exist] tb_name (...)

　　6.删除数据库:drop databese [if exists] db_name

　　7.删除索引：drop inde index_name on tb_name

　　8.删除表：drop www.xycheng178.com[temporary] table [if exists] tb_name,tb_name2...

　　9.重命名表：rename www.yigouyule2.cn tb_name to new_tb_name,tb_name2 to new_2;

　　14.7 数据库操作语句和语法

　　1.删除

　　delete from tb_name where...

　　delete ta_name[.*][,tb_name2[.*]...] from table_references where;

　　delete from ta_name[.www.michenggw.com/*www.leyouzaixian2.com][,tb_name2[.*]...] using table_references where...;

　　2.do:指定表达式，不返回任何结果

　　示例：do get_lock('str',10)

　　3.handler语法:提供通往表存储引擎接口的直接通道

　　handler tb_name open [as www.hengy178.com alias] # 打开一个表

　　handler tb_name read index_name {= | >= | <= | www.mengzhidu178.com<} (values1,...) where...limit.. # 建立读取表的通道

　　handler tb_name read index_name {first|next|prev|last} where...limit...

　　handler tb_name read index_name www.yongxinzaixian.cn{first|next} where...limit...

　　handler tb_name close #关闭

　　4.insert

　　insert into tb_name (field1,field2..)values(v1,v2)

　　insert into tb_name set filed=v1,filed2=v2...

　　insert into tb_name select

　　5.load data infile将信息从一个文本文件中告诉的读入表中

　　6.select * from tb_name,join,group by,order by,limit,having,like,and,in,not in,or,any,between,union

　　7.truncate [table] tb_name:删除表再创建表，多用于清空

　　8.update

　　9.explain tb_name|explain select ...:显示表结构|解释如何执行select语句

　　14.8 存储过程：为了完成特定功能、经编译后存储在数据库中sql语句集，用户通过指定存储过程的名字并给出参数来执行

　　优点：灵活性、一致性、高效性、安全性

　　1.创建:

　　create procedure sp_name(www.huachengj1980.com[proc_parameter[,..]])

　　begin

　　satement block

　　end

　　示例：

　　-----

　　mysql>create procedure sp(in inputid int)//有in out inout三种类型,in传入参数,out向存储过程外传出参数,inout传入修改后传出,inputid为参数名,int为参数类型

　　begin

　　select * from nc_product where p_id=inputid

　　end

　　mysql>delimiter;

　　-----

　　2.调用存储过程:call sp_name()

　　示例：call sp(1)

　　3.删除存储过程;dtop procedure sp_name

　　4.show procedure status：显示数据库中所有存储过程的基本信息；show create procedure sp_name：用来显示某个存储过程的详细信息

　　第15章 mysql数据管理

　　15.1 mysql分区

　　水平分区：对表的行进行分区

　　垂直分区：通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行

　　一般都采取水平分区，其中水平分区还包括如下分区

　　1.range(范围)分区：允许DBA将数据划分为不同的范围

　　示例：

　　-----

　　create table employees(

　　id int not null,

　　fname varchar(30),

　　lname varchar(30),

　　...

　　strore_id int not null,

　　)

　　partition by range(store_id)(

　　partition p0 valuse less then (11),

　　partition p1 valuse less then (16),

　　partition p2 valuse less then (21),

　　partition p3 valuse less then (26),

　　)

　　-----

　　# 当需要删除旧的数据时

　　# 想要使用一个包含有日期或时间值或者包含有从一些其他级数开始增长的值的列

　　# 经常运行直接一来分割表的查询

　　# 删除分区： alter table employees drop partition p2;

　　# 新增分区： alter table employees add partition (partition p4 values less than (31));

　　# 往前新增分区：alter table employees reorganize partition p0 into (partition s0 values less than (6),partition s1 values less than (11));

　　2.list(预定义列表)分区:允许通过dba定义的列表的值所对应的行数据进行分割

　　示例：

　　-----

　　create table employees(

　　id int not null,

　　fname varchar(30),

　　lname varchar(30),

　　...

　　strore_id int not null,

　　)

　　partition by range(store_id)(

　　partition pe valuse in (1,3,5,7),

　　partition pn valuse less then (2,4,6,8),

　　partition pw valuse less then (9,11,13),

　　partition pc valuse less then (10,12,14),

　　);

　　# 与地区相关

　　-----

　　3.hash(哈希)分区：允许DBA通过对表的一个或多个列的hash key进行计算，最后通过这个hash码来对不同数据区域进行分区，例如可以建立一个对表的主键进行分区的表

　　4.key(键值)分区：是hash模式的延伸，这里的hash key是由mysql系统产生的

　　5.composite(复合模式)分区：以上模式分区的组合

　　好处：提升新能、简化数据管理

　　15.2 mysql的备份

　　mysqldump [options] db_name tables>filename;

　　mysqldump [options] ---database db1 [db2...]>filename;

　　mysqldump [options] --all--database>filename;

　　# 其中[options]代表-h host -u root -p paswd

　　15.3 mysql恢复

　　导入文件：mysql -u root -p < filename

　　15.4 mysql复制：单向复制和异步复制

　　主从异步复制的配置如下：

　　master：192.168.0.3

　　slave：192.168.0.4

　　database:db_shopnc

　　1.在master服务器中启动mysql

　　1)在主服务器上为服务器设置一个连接账户。该账户需授予replication slave的权限

　　---

　　mysql>grant replication slave on *.* replication@192.168.0.4 identified by 'password';

　　mysql>flush privileges;

　　---

　　2)请空所有表和块写入语句

　　---

　　mysql>flush tables with read lock;

　　---

　　3)重新打开已终端，备份想要复制的数据

　　---

　　tar zcxf db_shopnc.tar.gz /opt/mysql/var/db_shopnc/

　　scp db_shopnc.tar.gz 192.168.0.4:opt/mysql/var/ //将主服务器的库传到slave相应的路径下

　　---

　　4)返回上一个终端，读取主服务器上当前的二进制日志名和偏移量值

　　---

　　mysql>show master status;

　　---

　　5)给数据库解锁

　　---

　　mysql>unlock tables;

　　---

　　6)编辑mysql配置文件

　　[mysqld]

　　log-bin=myskq-bin

　　server-id=1

　　binlog-do-db=db_shopnc

　　2.slave服务器配置

　　1)将从master中备份的库解压到相应路径下

　　2)修改my.cnf后，代码如下

　　server-id=2

　　master-hot=192.168.0.3

　　master-user=replication

　　master-password=password

　　log-bin=

　　# 每个从服务器有唯一的server-id的值，且必须与主服务器及其他从服务器的值不同

　　3.重启master和slave的mysql服务

　　1)先启动master，再启动slave

　　2)slave服务器重启登录mysql

　　---

　　mysql>stop slave;

　　mysql>change master to master_host=192.168.0.3,master_user='replication',master_pawword='password',master_log_file='mysql-bin.000014',master_log_pos=98;

　　mysql>start slave;

　　mysql>show slave status\G;

　　...

　　slave_io_running:yes

　　slave_sql_running:yes

　　...

　　---

　　# 两个参数为yes时，证明主从复制成功

　　第16章 Mysql的存储引擎及表类型

　　支持多个存储引擎为不同类型的处理器。从对事务支持的角度划分，mysql存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

　　MyISAM管理非事务表

　　memory存储引擎可以提供'内存中'表

　　InnoDB和BOB存储引擎提供事务安全表

　　example存储引擎是一个'存根'引擎

　　NDB是被mysql cluster 用来实现分割到许多台计算机上的表的存储引擎

　　acheive存储引擎非常适合存储大量的、独立的、作为历史记录的数据

　　csv存储引擎将数据以逗号分隔的格式存储在文本文件中

　　blackhol存储引擎接收单不存储数据，并且检索总是返回一个空集

　　federated存储引擎将数据存储在远程数据库中

　　engine|type定义存储引擎，默认为MyISAM

　　mysql总是创建一个.frm文件来保持表和列的定义

　　事务安全表(TST)比起非事务安全表(NTST)优势如下：更安全;可以合并许多语句并用commit语句同时全部接受;可以执行rollback来忽略你的改变;如果更新失败所有改变恢复到初始状态;事务安全存储引擎可以更好的保持数据完整性;

　　非事务安全的优势如下：更快;需要更少的磁盘空间;执行更新需要更少的内存

　　16.1 MyISAM

　　三个文件：表名文件，扩展名指出文件类型，.frm文件存储表定义。数据文件的扩展名为.myd，索引文件的扩展名为.myi。

　　特征：所有数据值先存储低字节，数据库与操作系统分离；先存储数据低字节并不影响速度；支持大文件的文件系统和操作系统；当删除、更新和插入混合时，动态尺寸的行碎片更少；每个MyISAM的最大索引数是64；最大的键长度是1000字节；blob和text可以被索引；在索引的列中允许null，其占每个键0-1个字节；当记录以排好序的顺序插入，索引树被劈开以便高节点仅包含一个键，改善了索引树的空间利用率；可以把数据文件和索引文件放在不同的目录中；每个字符列都可以有不同的字符集

　　1.启动选项

　　设置为崩溃时MyISAM表自动回复的模式:--myisam-recover=mode

　　用在块插入优化中的树缓冲区的大小：bulk_insert_buffer_size

　　myisam_max_sort_file_size:索引缓冲区大小

　　myisam_sort_buffer_size:设置恢复表时使用的缓冲区的尺寸

　　2.损坏的myisam表

　　3.未被适当关闭的表的问题

　　16.2 InnoDB存储引擎：提供事务

　　1.配置：

　　Innodb_data_file_path=datafile_spec1[;datafile_spec2]..

　　2.启动选项：

　　3.创建innodb表空间