回顾

数据库基本概念：数据库，数据库管理系统，数据库系统

数据库分类：关系型数据库和非关系型数据库

关系型数据库：用二维表来保存数据，数据保存在硬盘里

Mysql，Oracle，DB2，SqlServer，access

非关系型数据库：键值对保存数据，数据保存在内存

Memcache，mongodb，redis

关系型数据库典型概念

行（记录）

列（字段）

一个是结构上来区分，一个从数据上区分

SQL：结构化查询语言

DDL：数据定义语言，各种结构的维护

DML：数据操作语言，针对数据的增删改查，DQL，数据查询语言

DCL：数据控制语言，对用户权限进行操作

服务器的对象

DBMSàDBàTableàField

SQL基本操作

库操作：创建create Database，查询show databases，修改alter Database（库选项），删除drop Database

    表操作（字段）：

创建create table 表名(字段列表)[表选项]

从已有表创建表：create table 表名 like 旧表名（创建一个新表，表的结构与指定的表结构一样）

表选项：字符集，校对集，存储引擎

查询show tables，查看表字段desc/describe/show columns from 表名，查看创建语句show create table

修改alter table 表名 add/drop/modify/change [字段名] [字段类型] [位置]

删除：drop 表名

  数据操作

增加insert into 表名 [(字段列表)] values(值列表),(值列表)…

删除delete from 表名 [where条件]

修改update 表名 set 字段=值,字段=值,… [where条件]

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

 

字符集：mysql是一个c/s结构的软件

所有的数据发出都是有c客户端发出，所有的处理都是s服务器。

要做的事情就是：

1. 保证服务端能够正确接收客户端的数据，character_set_client
2. 保证客户端能够知道（服务器知道客户端能够解析什么样的数据），character_set_results

以上两个设置都是需要与客户端本身文件的编码一致。

校对集

用户数据的大小比较的集合，叫做校对集。

1  00000001                     31（ASCII）

2  00000010                     32

 

A               65              01000001

a                97              01100001

 

校对集指的是在比较的时候采用什么样的模式去进行比较，校对集受字符集的影响。一个校对集是否支持多种比较方式，受字符集影响。

通常比较的方式有以下三种

二进制比较：_bin，严格区分大小写，不论字符集

区分大小写：_cs，（case sensitive），大小写敏感

不区分大小写：_ci，（case insensitive），大小写不敏感（默认都是ci）

show collation;

对数据进行排序操作

语法：order by 字段名 [排序方式]

排序方式：asc升序（默认的），desc降序

表的校对集设置

排序区别

注意：

1. utf8不支持中文的比较
2. gbk支持中文比较，比较的是拼音

存储引擎

创建表的表选项里有个engine选项，用来指定存储引擎。

什么是存储引擎？

存储引擎是数据库开发商对于不同的文件的存储形式，以及很多数据的处理方式。

 

mysql到底支持哪些存储引擎？

mysql支持以下5种存储引擎

常用的存储引擎：InnoDB（新版mysql默认的），Myisam（老版mysql默认）

存储引擎的选择：

1. 使用系统默认的就好
2. 如果你的数据库是有较高效率的查询和插入数据，选择myisam
3. 如果数据库对安全性要求较高，使用InnoDB

两个存储引擎的区别（物理）：

1. InnoDB存储引擎

文件的物理表现

数据保存在哪？

2. myisam存储引擎

文件的物理表现

文件乱码问题

通常解决思路：三码合一（浏览器显示编码，服务器文件编码，数据库存储编码）

解决乱码原理

字段类型

字段类型指的数据类型

PHP有三大类八小类数据类型。

SQL也有三大类的数据类型：数值型，字符型，时间日期型

数值型

该字段只能存储数值，算术运算中所有使用到的数值

 

数值分为整数型和小数型

 

整数型分为很多类：tinyint，smallint，mediumint，int，bigint

tinyint：迷你整型，采用1个字节保存整型数据，能保存256个不同数值

smallint：小整型，采用2个字节保存整型数据，能保存65536个不同数值

mediumint：中整型，采用3个字节保存整型数据

int：标准整型，采用4个字节保存整型数据，大概是42亿多一点

bigint：大整型，采用8个字节保存整型数据

类型	字节	最小值（有符号/无符号）	最大值（有符号/无符号）
TINYINT	1	-128/0	127/255
SMALLINT	2	-32768/0	32767/65535
MEDIUMINT	3	-8388608/0	8388607/16777215
INT/INTEGE	4	-2147483648/0	2147483647 /4294967295
BIGINT	8	-9223372036854775808/0	9223372036854775807/18446744073709551615

为什么要有这么多整型呢？

数据库是高效率的存储和处理数据。

如人的年龄一个字节足够存，如果使用4个字节，意味着要浪费3个字节，读取效率要降低。

根据具体的实际业务产生的。

错误：超出实际数据类型的范围

显示宽度

指的是当数据的宽度小于某个指定宽度时，采用填充的方式来保证数据显示指定宽度。

显示宽度不会改变原来值的大小。是在原来数值的左边进行0填充。

显示宽度，默认不会产生填充0的效果，需要配合0填充属性，zerofill

zerofill会将有符号的整型转成无符号

有符号和无符号

无符号指的是数组从0开始，没有负数

有符号指的是有负数，默认是有符号的

声明数据类型是无符号：语法：字段 字段类型 unsigned;//无符号声明

小数型：有小数部分的数值

小数型分为：浮点型和定点型

浮点型和定点型的区别在于精度

浮点型

float和double

float：单精度，4个字节，7位数字

double：双精度，8个字节，16位数值

为什么浮点数占用相同的字节数，表示的数值却大很多？

整型：4个字节32位，都是用来保存数值大小

浮点型：4个字节32位，其中有一部分表示数值大小，还有一部分表示指数大小

浮点数使用

通常浮点数使用语法如下：

float(M,D)：M表示数值的总长度，D表示小数部分的长度

float(4,2)：表示小数有两位，整数部分2位

浮点数：会保证精度范围内的精确值，而精确范围之外的都是不准确的。

浮点数指定的整数部分的长度是不能超出

浮点数指定的小数部分可以超出长度范围，但是会对小数部分进行四舍五入

因为浮点会有丢失精度的问题，1浮点数适用比较少，2浮点数不用于比较。

 

定点型

decimal(M,D)：M表示最大显示长度，D表示小数部分的长度

定点性能够保证精度。精度之外也会进行四舍五入，指的小数部分

定点型和浮点型的比较

decimal是内存变长数据类型

decimal(65,30);

数值型的应用

1. 大部分的情况下使用整数型，只要没有小数，前提不要超出整型所能表示的最大范围
2. 凡是跟钱相关，一定是定点数。银行账户，商品价格
3. 需要非常大的数据表示，同时对精度要求没有那么高的情况下使用浮点型

 

虽然对于数据库的设计并不要求那么苛刻，但是一个良好程序员应当对数据的值进行判断，然后选择合适的数据类型。

 

字符型

SQL将字符分成了好多种：char，varchar，text，blob，enum，set

char：char(L)，L表示字符长度，L的最大值是255

varchar：varchar(L)，L表示字符长度，L的最大值65535个

text：text，表示文本

blob：blob，表示二进制文本

enum：enum(元素1，元素2，。。。)，枚举类型，表示值是元素中的某一个

set：set(元素1，元素2，。。。)，集合类型，表示值是元素中的某一些

 

char：字符长度，指的不管当前字段里面存放多少个字符，内存占用长度，永远是指定的L长度

char(10)：表示最多可以存放10个字符，如果用户只存入“a”一个字符，那么系统也将为该字段分配10个字符的内存空间

 

varchar：字符长度，指的是最多可以存放L字符，但是如果存放的实际字符数不足L长度，系统只会分配实际字符所需要的内存长度，但是因为varchar是变长的，所以系统在读取的时候不知道该读取多长的内存，系统会在存入数据的时候额外的分配一个或多字节来保存字符的实际长度

utf8字符集

字符	Char(4)	Varchar(4)	实际分配内存
A	12字节	3个字节	Char12个字节，varchar4个字节
Abcd	12字节	12个字节	Char12个字节，varchar13个字节
Abcde	不能插入	不能插入	超过指定长度系统报错

正常情况

数据实际长度超过指定长度

当前mysql是以严格模式执行，当数据的类型或者数据的大小超出指定范围时，系统会报错，而不能正常执行。

集成环境中，如wamp，phpstudy是采用非严格模式执行，系统会对数据的类型以及数据的长度进行自动转换，即便数据错了，也不会出现问题。

 

char与varchar的区别

1. char不需要去判断字符的长度，只需要直接读取，所以效率高
2. varchar会根据实际内容分配内存，因此内存利用率高，不会浪费内存
3. varchar比char能够存储的字符数要大得多

 

如果选择使用char还是varchar？

 

 

1. 存储的是一些固定长度的数据，使用char，电话号码，身份证号码，手机号码，银行卡号，密码（如果采用加密方式md5，得出来的长度是定长的）等
2. 存储的是一些可变长度的数据，使用varchar，名字，用户名，大部分的数据都是varchar类型
3. 当实际存储字符长度超过255的时候，两者基本都不用，text来保存

 

text和blob

text保存字符文本

blob保存二进制文本

大于255长度字符串通常使用text来进行保存。

 

enum：枚举类型

指的是当前字段存储值，必须是枚举中某一个。

enum使用2个字节保存，能够存放65535个内容

 

当字段类型被设置成枚举类型之后，系统会对插入的数据进行判断，如果数据不属于枚举所定义的类型，那么数据就不合法

枚举的意义：限制数据结果，只有符合枚举定义的结果能够被保存。

最大的意义：节省空间

枚举类型里面的值，实际存储的不是字符，而是数字。但是系统做了一个对应关系，来保存了该枚举类型与数值的对应关系。

查看一个字段是否是数值：select 字段+0；mysql会将该字段进行自动转化，成数值然后进行计算并最终输出数值结果

枚举类似单选框

枚举数据库处理原理

当一个已经有值的枚举字段，被修改之后，系统会将该修改后的枚举重新应用到所有数据上，所以不会导致数据错误。

 

应用枚举有什么好处？

1. 枚举能够规范数据
2. 枚举能够大量节省空间

但是作为PHP程序员，很少使用。

set：集合

set(元素1，元素2.。。。。)，系统分配8个字节用来保存数据，元素能出现64个元素

集合类似复选框。

其中每一个元素都代表一个字节中的一位，类似开关。

插入数据和查看数据，集合数据的插入顺序与定义的顺序无关

虽然用户看到的都是具体数据，但是实际上数据库存储的却是数值

集合中每一个元素代表一个字节中的位，如果选中表示该位为1，否则为0，最后，将整个对应二进制倒过来，再计算其值

PHP会不会使用集合？一般PHP不会这么去用

 

集合的使用从数据库的角度出发效率非常高，但是从PHP应用的角度出发不是很方便。

 

时间日期类型

mysql提供了多种时间日期类型

datetime，date，time，timestamp，year

datetime：日期时间，格式：yyyy-mm-dd HH:ii:ss，0000 – 9999年

date：日期时间的日期部分

time：日期时间的时间部分，表示是一个时间段，过去的某个时间到将来的某个时间

timestamp：时间戳，是当前时间戳的日期时间格式显示，是一个标准整型：格式yyyy-mm-dd HH:ii:ss，与datetime的区别是显示的数据大小不一样

year：年份，1个字节存储，year(4)|year(2)，4位表示1900 – 2155，2位表示70-69（即将被放弃）

类型	显示格式	取值	存储空间	零值
DATETIME	YYYY-MM-DD HH:MM:SS	'1000-01-01 00:00:00'到'9999-12-31 23:59:59'	8	0000-00-00 00:00:00
TIMESTAMP	YYYY-MM-DD HH:MM:SS	是‘1970-01-01 00:00:00’到2038-01-19 03:14:07	4	0000-00-00 00:00:00
DATE	YYYY-MM-DD	'1000-01-01'到'9999-12-31	3	0000-00-00
TIME	HH:MM:SS	-838:59:59'到'838:59:59'	3	00:00:00
YEAR	YYYY	1901到2155	1	0000

 

对应的表

数据插入和显示效果

time类型

作为数据库管理者，考虑的是数据库执行效率，和如何节省空间。

作为PHP程序员，以后存储时间基本上是用int，时间戳，因为PHP时间处理很强大。

 

mysql中一条记录的长度不能超过65535个字节。

create table test_length(

id int, -- 4个字节

name char(10), -- 30个字节

describe varchar(), -- (65535 – 34)/3

)charset utf8;

因为字段里没有一个强制规定不为空null，mysql需要从65535个字节里保留一个来存储null

不允许为空之后，把null所需要的一个字节给释放出来

text不占用数据长度（内容不占用数据长度，而本身需要占用10个字节）

不同的字符集下，能存储的varchar的最大字符数

GBK

UTF8

发现在显示创建语句的时候，系统给自动添加一部分信息。

字段属性

跟在字段类型后面，用来修饰字段的一些额外的信息。

 

mysql的字段属性：null/not null，default，primary key，unique key，comment，auto_increment

null/not null：表示字段数据可以或者不可以为空

在创建表，如果没有给指定字段添加not null，默认是可以为空

 

一旦数据有不能为空的，那么就必须插入值

可以为空的数据，在进行数据插入时，可以不插入数据

通常不会让一个数据为空，因为空数据没有任何意义。

如果不想使用空数据，通常是搭配default，默认值使用

 

default：表示字段的默认值

语法：default ‘默认值’

1. 不给有默认值的字段添加值

2. 在需要使用默认值的字段值处，使用default关键字

如果没有为字段指定默认值，系统默认字段默认值为空（除非指定了not null）

 

primary key：一张表的主键，一张表只能有一个，主键值不能为空

用来严格区分记录的唯一性，其次提升查询的效率，主键是一个索引

如何给表添加主键？

1. 与其他属性一样，创建表时在字段后面添加（添加之前必须保证该字段的所有值不重复）

主键说明

效果

2. 创建表时，单独指定主键

语法：primary key(字段名,字段名) -----》复合主键 primary key(id)

主键说明

效果

3. 主键添加，通过修改表的形式

表中没有主键，而又希望添加一个主键，如表中有一个ID并未重复

alter table 表名 modify id int primary key;-- 通过修改表属性来设定主键

alter table 表名 add primary key (字段名，字段名); -- 通过添加主键形式

 

删除主键：

语法：alter table 表名 drop primary key;

如果想实现换字段做主键：先删除已有主键—》添加一个新主键

unique key：唯一键，表示当前字段的值不能重复，可以为空，空不在判断范围之内

一张表只能有一个主键，但是又有可能存在其他字段不允许重复，就使用unique key

语法：

1. unique key的添加语法与primary key的添加基本一致
2. 也可以使用表字段后面添加唯一键

unique key (字段列表) – 与主键一样，可以创建复合唯一键

3. 采用修改表的形式添加唯一键

unique key不能比较null，所以里面可以插入多个null值

唯一键的约束效果

删除unique key：

语法：alter table 表名 drop unique key； -- 错误的语法

原因：primarykey具有唯一性，有额外的权限，而unique key可能存在多个，而且其权限没有那么高。mysql没有提供专门的删除唯一键的语法，把唯一键当做普通索引处理的。

alter table 表名 drop index 索引名

对于单字段唯一键，索引名使用是就是对应的字段名

索引名称不能使用单引号，只能使用反引号

 

如果想指定索引名字

unique key ‘索引名字’ (字段);

comment：注释，写字表的创建语句里面，用于其他人去查看表的时候方便理解

SQL中支持的注释有三种

-- ：两个中划线+一个空格，表示，当前行空格后面的内容为注释，sql执行的时候请忽略

#：当前行后面的代码为注释，请忽略

/**/：块注释，内部的所有内容为注释，请忽略掉

comment：是一个属性，表示里面内容是注释，不需要执行

注释会被忽略掉，而comment属性会被保留

auto_increment：自动增长，要求是整型，一张表只有一个自动增长，自动增长的字段必须是索引字段

一般情况下，自动增长都是搭配主键使用。

自动增长指的是当某个自动增长的列没有被插入值，或者插入null值的时候就会触发自动增长，从而实现自动增长。如果该字段被插入值，那么自动增长就会不执行。

实现自动增长

自动增长原理
1.      当一条数据插入，直接使用自动增长的时候，系统分配了一个1，当第一条插入完之后，该值会变成2，等待下一条插入

2       自增长跟PHP索引数组的下标一样，会根据当前最大的那个值加1。

为什么系统能够从1开始进行自动增长？而为什么每次又是自动增加1？

系统是通过系统里面的配置文件来识别。

show variables like ‘auto_increment%’;

 

作业：自己回去修改自动增长的起始值和自增长变量。