Mysql详细学习笔记

日拱一卒，功不唐捐！
缺士怕双车，缺象怕炮
布局：出动大子，左右均衡，抢占要道
类型：急攻、缓攻、灵活、散手

MySQL 学习的要点

MySQL对于PHP程序员来说就是将业务转化成表结构。做好业务中的增、删、改、查。
如果你不知道什么是MySQL我们来介绍一下MySQL吧。

MySQL数据库简称MySQL，是一款由瑞典MySQL AB公司开发并且应用广泛的数据管理系统，MySQL数据库因其体积小、速度快、总体拥有成本低受到很多的热捧。现在,MySQL的所有者世界上最著名的数据库企业——Oracle所有。

MySQL的应用，在国内的企业包括：百度、阿里、腾讯、新浪、搜狐、网易等等企业，全部都在使用MySQL数据库。

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），MySQL数据库系统使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。

由于MySQL是开放源代码的，因此任何人都可以在GPL的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

PHP与很多数据库结合都很紧密。由于，PHP和MySQL都是开源免费的。所以PHP一直对于MySQL等数据库都有很好的支持。

最开始学PHP的时候，通常我们也将数据放在MySQL数据库里面。因此，PHP程序员对于MySQL来说是必学课程。

很多朋友在学完MySQL的初期，还走了很多不必要的弯路，学了很多原本不需要掌握的知识点，耽误了大量宝贵的学习时间。

在最开始学习MySQL的时候，并不需要学习MySQL数据库，学到DBA（数据库管理员）的级别。

写过两年代码后。很多朋友会遇到一个瓶颈期。感触最深核的是：

业务代码里面写的最多的就是增、删、改、查。

为了帮大家快速学习和入门，我帮大家总结最精华的干货。本章节可以说是一本：《mysql 精华快速入门》。

只需要学好这些内容，mysql你就入门了！

学好增、删、改、查。写业务我都不怕！

ps：你能想象像新浪、百度、搜狐等。其中的某些大公司使用分库分表和数据库中间件技术后后，有些甚至不准使用到联合查询吗？

互联网公司数据库

就数据库实际应用水平而言，互联网公司显然走在了前列，它们都在使用哪些数据库?分别对应哪些业务场景，为什么会是这样选择?了解的人却并不多。阿里巴巴/蚂蚁金服阿里巴巴/蚂蚁金服主要使用两种关系数据库：OceanBase和MySQL。数据规模：MySQL单台机器TB级，OceanBase 单个集群从几个TB到几百个TB皆有。MySQL：蚂蚁金服部分非核心业务和阿里巴巴大部分系统，类似于其他互联网公司。去哪儿采访对象：周彦伟，去哪儿网数据库总监，负责数据库平台的管理和维护工作。工作范围包括MySQL，Redis，HBase平台的架构设计，性能调优，日常运维以及自动化运维平台设计。周彦伟：去哪儿使用MySQL支撑公司大部分OLTP业务，有上千台规模。同时针对热点数据以及对访问延时特别敏感的业务，去哪儿大规模的使用Redis的满足，因此选择Mysql作为存储。Redis主要用在缓存、计数的业务场景。这些业务有的对于读取QPS非常高，可以达到每秒几万QPS，有的更新非常频繁，对于数据类型支持的要求比较丰富，因此选择redis作为存储。魅族目前魅族OLTP场景主要使用的是MySQL，缓存服务使用的是Redis。数据库实例近1000，数据大小100T+, redis实例1000+MySQL使用覆盖应用中心，游戏中心，用户中心，云服务等主要业务，之所以选择MySQL,主要是开源，无linsen 费用，而且扩展性好，如云服务，随着用户数的不断增加，数据量也不断的增加，最开始只有几台DB服务器，数据量的不断增加，即使采用商业数据库产品也无法很好的支撑数据的快快速增长，而MySQL扩展性好的优势就体现出来了，利用复制架构能快速的对单台服务器容量进行拆分，到目前为止仅云服务就已经从最开始的几组服务器增加到好几十组DB服务器。而且从最开始的不断拆分模式演变为只要增加机器即可满足数据量的快速增长。同时MySQL slave复制能很好的扩展读性能，跨机房冗灾，比如三大中心业务需要灾各个机房进行读扩展，利用slave复制就能很好的满足要求，基于复制做跨机房的冗灾也是一个不错的选择。

数据库的用途

很多朋友最开始学习数据库的时候，很难理解数据库的作用。理不清楚，数据库与我们现实生活、虚拟生活到底有什么样的关系。

现如今，我们所有见到的跟日常生活有关、需要记录的基本全部放在数据库里面：

身份证信息放在公安部的系统
银行卡的余额和交易记录、转帐信息
在酒店的开房信息（所有出现了某些方面的数据库被盗和信息泄漏）
飞机、火车、汽车联网购票记录
各个不同的网站、QQ、网上购物、贴吧、喜欢听的音乐、电影的收藏信息
手机电话机录、余额、公交卡余额、水费、电费、彩票的购买记录
打游戏的装备、等级、魔力、力量、攻击能力等信息
美国航空母舰也在使用mysql数据库在管理航母的相关信息
... ...等等
1
2
3
4
5
6
7
8
9

我们生活的一切全都记录在数据库里面。你可以想想，数据库有多么重要！

在21世纪，人类没有了数据库，世界将会怎样？

数据库的五个基本单位

数据库服务器
数据库
数据表
数据字段
数据行

我们现在来对上面的五个基本单位进行说明：

数据库服务器。是指用来运行数据库服务的一台电脑。在中小型企业通常为一台。在数据存储量计算量很大的时候可以存在多台。多台数据库服务器共同来存储或计算。由于数据安全非常重要，我们经常会对数据库服务器里面的数据进经备份。

数据库。一个数据库服务器里面有可以有多个数据库。主要用来分类使用。我们可以建立交通信息数据库、游戏数据库、酒店开房数据库… … 主要用来将各个不同用途的数据，按照业务进行大块的划分。

数据表。例如在游戏数据库中。根据这一款游戏又分为了不同的数据表。专门用来区分游戏不同的数据。例如：用户数据（用户、密码）；人物数据；所有装备和装备信息；用户的充值信息；药品、魔力药水信息… …等

数据字段，也叫数据列。就是我们日常所见表格里面的列。在表格中，我们会将一张用户表分成多个列。如下（表一）所示：用户编号、用户名、性别、年龄是字段。在真正的数据库中数据字段需要换成英文需要写成：id、username、sex、年龄。

数据行。真正的数据存在每一个表的行里面。字段（列）划分出来了一个表应该按照什么样的格式存数据。而行，是真正的数据。每一行需要遵循数据字段（列）的规范和要求进行存入数据。

Mysql娱乐化讲解表关系

上一章我们讲到了数据库的应用范围非常广泛。如果没有了数据库，可能我们未来寸步难行。

学计算机的男孩、女孩现在都挺多。特别是80、90后互联网原著民，很多人都特别爱玩游戏。我们通过游戏里面的用户装备信息讲解表的关系。

银行取钱、转账、发红包也是我们日常中最常用银行卡操作，我们还用银行卡的存取讲解表的关系。

游戏里装备和用户的关系

在游戏里面的某个人物有头盔、衣服、靴子、武器、项链。

并且，每一个不同的武器会增加上不同的攻防值。那我们就可以这么来模拟游戏的表设计。

注：以下仅为了让大家更加理解游戏里、用户和装备的关系。

表示例

用户表中骷髅王带上了装备表中编号为1（死亡面具）和使用了编号为6（魔棒）的武器。

而用户3（半人马），使用了装备表中编号为3（的速度之靴）和7（幽魂权杖）。

这样就实现了游戏中某些用户戴上了装备。如果用户的行和列数据里面没有这个选项的话，则没有这个数据。

通过装备的属性值，与用户等级属性值相加就实现了用户穿上装备好的回血、防御值增加等不同的效果。

银行开户、取现和转载
我们在日常生活中经常进行的一个活动就是使用银行卡付钱，在银行的账单中，忠实的记录着我们每一笔交易。

我们来通过表格的方式来模拟：

场景模拟：

用户插入卡、输入密码正确后。则可以取钱
社会工程学中，人们喜欢用相同的密码。因此，用户的密码必须要进行再次加密，不可逆向解密。因为害怕看到了某个用户的密码后，用这个密码去尝试用户的其他银行卡。
我们自行规定：冻结状态可以设置为0（未冻结） 和 1 （冻结了）。如果银行收到法院的通知。则将冻结状态设置为1。有钱也不让取钱。（这块业务逻辑需要在程序中实现）。
用户若取钱了，或者存钱了将用户的余额增加或者减少。同时将记录，记录至交易流水中。
交易流水表

每当用户的余额发生变化的时候，我们都会忠实的记录到交易流水表中。让交易可查、可追述。

这样就模拟了银行的冻结、取现、存钱等流程。

注：
密码必须使用md5等加密方式帮用户进行加密。用户输入原密码如：123456。我们使用md5将用户输入的123456加密后与数据库的密码进行对比。
一致则密码通过。不一致则用户将密码输入错误了。

这样就实现保证用户密码安全，防止内部人员泄漏用户密码的可能性。
更多的密码知识，我们在下册和进阶项目中更多的为大家讲解。

选择服务器安装

develop 是指服务器为开发机【推荐】
server only 只安装服务器
client only 只**安装客户操作端 **
full 全部安装
custom 自定义

数据语句操作类型

学习数据库安装后，最重要的就是学习SQL语句。

SQL是操作数据库的核心，也是本章开始的一句话：MySQL对于PHP程序员来说就是将业务转化成表结构。做好业务中的增、删、改、查。

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL是最重要的关系数据库操作语言，并且它的影响已经超出数据库领域，得到其他领域的重视和采用，如人工智能领域的数据检索等。

SQL是关系模型的数据库应用语言，由IBM在20世纪70年代为其关系型数据库 System R 所开发。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。1989年4月，ISO提出了具有完整性特征的SQL89标准，1992年11月又公布了SQL92标准。

虽然各个数据库系统略有不同，但是他们基本均遵循SQL 92标准。或者在SQL 92上做了一些简单的扩展和变化。

学好了MySQL 的SQL 语法，其他的SQL语法学习起来均是万变不离其中。

SQL语句按照其功能范围不同可分为3各类别：

数据定义语言(DDL ，Data Defintion Language)语句：数据定义语句，用于定义不同的数据段、数据库、表、列、索引等。常用的语句关键字包括create、drop、alter等。
数据操作语言(DML ， Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据的完整性。常用的语句关键字主要包括insert、delete、update和select等。
数据控制语言(DCL， Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

Mysql数据库操作

创建数据库

类别 详细解示

基本语法 CREATE DATABASE 数据库名;
示例 CREATE DATABASE PHP;

示例说明 创建一个数库，数据库的名字为PHP
示例：

mysql> CREATE DATABASE PHP;
 Query OK, 1 row affected (0.00 sec)
1
2

“Query OK” 表示上面的命令执行成功，所有的 DDL 和 DML(不包 括 SELECT)操作执行成功后都显示“Query OK”，这里理解为执行成功就可以了；“1 row affected” 表示操作只影响了数据库中一行的记录，“0.00 sec”则记录了操作执行的时间。

如果已经存在这个数据库，系统会?示:

mysql> CREATE DATABASE PHP;
 ERROR 1007 (HY000): Can't create database 'PHP'; database exists
1
2

查看数据库

基本语法：

类别 详细解示

基本语法 show databases;
示例说明 显示当前服务器的所有数据库

注意：
show是指显示
database 是指数据库
databases 是数据库的复数形式，指全部数据库。

示例：

mysql> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | user               |
 +--------------------+
 4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

选中数据库

基本语法：

类别 详细解示

基本语法 use 库名;
示例 use PHP

示例说明 使用数据库PHP
注意：
use 是指使用；
库名 是存在当前数据库系统中的具体的数据库的名称；

示例：

mysql> use PHP;
 Database changed
1
2

这样就进入到了 PHP 数据库中了。当然你可以使用 use 语句随时切换要操作的数据库，刚刚选中了PHP ，现在我们切换到mysql内容的 mysql 数据库看看：

mysql> use mysql;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 Database changed
1
2
3
4

出现 ” Database changed“ 表示切换成功。然后，看看 mysql数据库里面有什么内容（和查看当前数据库服务器数据库一样使用 show 语句）

查看数据库中的表

进入到库后我们可以看这个库里面有多少个数据表。

类别 详细解示

基本语法 show tables;
示例说明 显示当前数据库下所有的表

使用use 进入到某个数据库后可以使用show tables

示例，查看当前数据库的表：

mysql> show tables;
 +---------------------------+
 | Tables_in_mysql           |
 +---------------------------+
 | columns_priv              |
 | db                        |
 | event                     |
 | func                      |
 | general_log               |
 | help_category             |
 | help_keyword              |
 | help_relation             |
 | help_topic                |
 | innodb_index_stats        |
 | innodb_table_stats        |
 | ndb_binlog_index          |
 | plugin                    |
 | proc                      |
 | procs_priv                |
 | proxies_priv              |
 | servers                   |
 | slave_master_info         |
 | slave_relay_log_info      |
 | slave_worker_info         |
 | slow_log                  |
 | tables_priv               |
 | time_zone                 |
 | time_zone_leap_second     |
 | time_zone_name            |
 | time_zone_transition      |
 | time_zone_transition_type |
 | user                      |
 +---------------------------+
 28 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

这些表里面的内容是关系数据库服务器相关的用户、权限、数据库状态、设置等相关的信息数据。

删除数据库

类别 详细解示

基本语法 DROP DATABASE 库名;
示例 DROP DATABASE PHP;

示例说明 删除一个数库，数据库的名字为liwenkai
注意：
drop 是汉语可以翻译为指掉下来，不要了的意思
database 是指库
库名 是指要删掉的库的名称

示例：

mysql> DROP DATABASE  PHP;
 Query OK, 0 rows affected (0.01 sec)
1
2

【切记】注：数据库删除后，下面的所有数据都会全部删除，所以删除前一定要慎重并做好相应的备份。（若重要数据未备份，而实际中产生的数据风险与本书无关。）

Mysql数据表操作

创建表
类别 详细解示

基本语法 CREATE TABLE表名(字段名1 字段类型,…字段名n 字段类型n);
示例 CREATE TABLE user(username varchar(20),password varchar(32));

示例说明 创建一个表名叫user的表，第一个字段为username、表的字段类型为varchar长度为32个长度。第二个字段为password，类型也为varchar，长度也为32个长度。
注意：

为了更好的让大家入门，数据类型暂时不在我们这一章的讲解范围。害怕大家顾此失彼。快速学习数据库的管理和操作语句非常的重要，数据类型、字段、字符集、引擎都属于了解的知识点。
. 字段类型大家现在只需要学会int,代表整型。float，代表浮点。char和varchar代表字符串即可。
我们可以在类型后接上长度如：varchar(20)。
其他示例：

mysql> CREATE TABLE emp(
 ename varchar(10),
 hiredate date,
 sal float(10,2),
 deptno int(2)
 );
 Query OK, 0 rows affected (0.63 sec)


mysql> create table dept( deptno int(4), deptname varchar(20));
 Query OK, 0 rows affected (0.12 sec)
1
2
3
4
5
6
7
8
9
10
11

查看表字段结构信息

类别 详细解示

基本语法 desc 表名;
示例 desc emp

示例说明 查看emp表的表结构

mysql>DESC emp;
 +----------+---------------+------+-----+---------+-------+
 | Field | Type | Null | Key | Default | Extra |
 +----------+---------------+------+-----+---------+-------+
 | ename | varchar(10) | YES | | NULL | |
 | hiredate | date | YES | | NULL | |
 | sal | decimal(10,2) | YES | | NULL | |
 | deptno | int(2) | YES | | NULL | |
 +----------+---------------+------+-----+---------+-------+
 4 rows in set (0.39 sec)
1
2
3
4
5
6
7
8
9
10

查看表的创建SQL语句

查看表创建语句

类别 详细解示

基本语法 SHOW CREATE TABLE表名 \G;
示例 SHOW CREATE TABLE emp \G;

示例说明 查看表emp的创建语句
执行完整示例：

mysql> SHOW CREATE TABLE emp \G;
 Table: emp
 CREATE TABLE: CREATE TABLE emp (
 ename varchar(10) DEFAULT NULL,
 hiredate date DEFAULT NULL,
 sal decimal(10,2) DEFAULT NULL,
 deptno int(2) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 1 row in set (0.00 sec)
 ERROR:
 No query specified
1
2
3
4
5
6
7
8
9
10
11

上面表的创建 SQL 语句中，除了可以看到表定义以外，还可以看到表的 engine（存储引擎) 和 charset(字符集)等信息。“\G”选项的含义是使得记录能够按照字段竖着排列，对于内 容比较长的记录更易于显示。

删除表

类别 详细解示

基本语法 DROP TABLE 表名;
示例 DROP TABLE emp;

示例说明 删除表emp

mysql> DROP TABLE emp;
 Query OK, 0 rows affected (0.34 sec)
1
2

注：删除表。表和数据均会丢失，请勿必删除重要表之前备份数据。

指定表引擎和字符集

在创建表最后，我们常用MyISAM或者InnoDB引擎。在指定引擎时，我们可以使用：

ENGINE=InnoDB
1

指定表默认字符集：

DEFAULT CHARSET=utf8
1

效果如下：

CREATE TABLE emp (
     useraname varchar(10) DEFAULT NULL,
     password date DEFAULT NULL,
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4

字符集

字符集是什么？
为了更好的识别中文、日文、英文、希腊语。对于常用的符号进行了编码，这个编码就是字符集。

字符集确定了文字的存储方式。

字符集相当于是计算机中人类的语言。

举个例子：

我说的是英文，所以我存储的时候要用英文文字来存储。
如果我说的是中文，用英文字符来存储的话。那么人们就看不懂也看不明白，就是我们所说的乱码。

因为字符集太多了，足够有几十种上百种之多。所以我们不需要了解太多的字符集的知识，甚至不需要了解字符集到底是如何编成人类可见字符的。

字符集的重点知识
我们只需要了解：

常用字符集
数据库中我们用什么字符集
英文字符集：

字符集 说明 字节长度

ASCII	美国标准信息交换代码	单字节
GBK	汉字内码扩展规范	双字节
unicode	万国码	4字节
UTF-8	Unicode的可变长度字符编码	1到6个字节
ASCII
ASCII 码使用指定的7 位或8 位二进制数组合来表示128 或256 种可能的字符。标准ASCII 码也叫基础ASCII码，使用7 位二进制数来表示所有的大写和小写字母，数字0 到9、标点符号， 以及在美式英语中使用的特殊控制字符。
1
2
3
4
5
6

其中：
0～31及127(共33个)是控制字符或通信专用字符（其余为可显示字符），如控制符：LF（换行）、CR（回车）、FF（换页）、DEL（删除）、BS（退格)、BEL（响铃）等；通信专用字符：SOH（文头）、EOT（文尾）、ACK（确认）等；ASCII值为8、9、10 和13 分别转换为退格、制表、换行和回车字符。它们并没有特定的图形显示，但会依不同的应用程序，而对文本显示有不同的影响。
32～126(共95个)是字符(32是空格），其中48～57为0到9十个阿拉伯数字。
65～90为26个大写英文字母，97～122号为26个小写英文字母，其余为一些标点符号、运算符号等。

GBK
GBK 向下与 GB 2312 编码兼容。是中华人民共和国定义的汉字计算机编码规范。早期版本为GB2312。

Unicode
Unicode（统一码、万国码、单一码）Unicode是国际组织制定的可以容纳世界上所有文字和符号的字符编码方案。以满足跨语言、跨平台进行文本转换、处理的要求。

UTF-8
是一种针对Unicode的可变长度字符编码，也是万国码。因为UNICODE比ASCII占用大一倍的空间，而对ASCII来说高字节的0对他毫无用处。为了解决这个问题，就出现了一些中间格式的字符集，他们被称为通用转换格式，即UTF（Universal Transformation Format）

实际工作中要使用的编码
在中文中常用的字符集分为utf-8和GBK。

实际使用的如下：

字符集 说明
gbk_chinese_ci 简体中文, 不区分大小写
utf8_general_ci Unicode (多语言), 不区分大小写
观察（图一）的特点你会发现，MySQL字符集由三个部份组成：
1.字符集
2.语言
3.类型

最后的bin是指二进制字符集，后面的ci是指存储排序时不区分字符的大小写。

注意：
mysql在写utf-8的时候写的是utf8。不加中间的中横线。

表引擎

MySQL的强大之处在于它的插件式存储引擎，我们可以基于表的特点使用不同的存储引擎，从而达到最好的性能。

如果你足够熟悉，并且有一定工作经验后。你还可以使用阿里巴巴和网易开源出来的MySQL引擎在自己的服务器中使用。

大家在后面的一节《数据库结构定义语句》中可以学到创建表的语句。mysql在创建表的时候，可以指定对应的引擎。

在mysql命令中使用：

show engines;

可以查看到当前服务器支持的所有引擎。

我们介绍几种常用的引擎和了解几个不常用的引擎。避免未来在实际工作中看到一些引擎不知道概念。

引擎名称

MyISAM	常用。读取效率很高的引擎
InnoDB	常用。写入，支持事处等都支持
Archive	不常用。归档引擎，压缩比高达1:10，用于数据归档
NDB	不常用。主要在MySQL 集群服务器中使用，不做介绍
1
2
3
4

MyISAM

不支持事务，表锁(表级锁，加锁会锁住整个表)，支持全文索引,操作速度快。常用于读取多的业务。

myisam存储引擎表由myd和myi组成。.myd用来存放数据文件，.myi用来存放索引文件。
对于myisam存储引擎表，mysql数据库只缓存其索引文件，数据文件的缓存由操作系统本身来完成。

InnoDB

支持事务，主要面向在线事务处理(OLTP)方面的应用。
行锁设计，支持外键，即默认情况下读取操作不加锁。
InnoDB是为处理巨大数据量时的最大性能设计。

注：
行锁：写入、更新操作的时候将这一行锁起来，不让其他人再操作了。
表锁：写入、更新操作时，将表给锁起来不让其他人再操作了。
事务：同时操作多个数据，若其中的一个数据操作失败。可回滚到操作之前。常用于银行、电商、金融等系统中。

索引

索引看着挺高大上的一个名字，说白了就是我们书最新面的目录。

假如你用新华字典来查找“张”这个汉字，不使用目录的话，你可能要从新华字典的第一页找到最后一页，可能要花二个小时。字典越厚呢，你花的时间就越多。现在你使用目录来查找“张”这个汉字，张的首字母是z，z开头的汉字从900多页开始，有了这条线索，你查找一个汉字可能只要一分钟，由此可见索引的重要性。

索引用于快速找出在某个列中有一特定值的行。

不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。

当然索引也不易过多，索引越多写入，修改的速度越慢。因为，写入修改数据时，也要修改索引。

MySQL的索引类型

索引类型 功能说明
普通索引 最基本的索引，它没有任何限制
唯一索引 某一行企用了唯一索引则不准许这一列的行数据中有重复的值。针对这一列的每一行数据都要求是唯一的
主键索引 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，常用于用户ID。类似于书中的页码
全文索引 对于需要全局搜索的数据，进行全文索引
注意：以下部份请学习完12.7后再进行学习。

普通索引

类型 详细说明
基本语法 alter table 表 add index(字段)
示例 ALTER TABLE money ADD INDEX(username);
示例解释 为money表的username字段增加索引

唯一索引

类型 详细说明
基本语法 alter table 表 add UNIQUE(字段)
示例 ALTER TABLE money ADD UNIQUE(email);
示例解释 为money表的email字段增加唯一索引

全文索引

类型 详细说明
基本语法 alter table 表 add FULLTEXT(字段)
示例 ALTER TABLE money ADD FULLTEXT(content);
示例解释 为money表的content字段增加唯一索引

主键索引

类型 详细说明
基本语法 alter table 表 add PRIMARY KEY(字段)
示例 ALTER TABLE money ADD PRIMARY KEY(id);
示例解释 为money表的id字段增加主键索引
创建表时也可以声明索引
创建表时可在创建表语句后加上对应的类型即可声明索引：

PRIMARY KEY(字段)
 INDEX [索引名] (字段)
 FULLTEXT [索引名] (字段)
 UNIQUE[索引名] (字段)
1
2
3
4

注：中括号中的索引名，代表可选。

整体示例如下：

CREATE TABLE test (
     id INT NOT NULL ,
     username VARCHAR(20) NOT NULL ,
     password INT NOT NULL ,
     content INT NOT NULL ,
     PRIMARY KEY (id),
     INDEX pw (password),
     UNIQUE (username),
     FULLTEXT (content)
 ) ENGINE = InnoDB;
1
2
3
4
5
6
7
8
9
10

DCL语句

创建库用户

添加权限

类别 详细解示

基本语法	grant 权限 on 库.表 to '用户'@'主机' identified by '密码';

示例	grant select, insert on test.* to 'liwenkai'@'localhost' identified by '4311';
1
2
3

示例说明 给予liwenkai用户，在本机连接test库所有表的权限。操作的这些表具有查询和写入权限
注：可以针对一个用户增加多条权限。

删除权限

类别 详细解示

基本语法	revoke 权限 on 库.表 from '用户'@'主机';

示例	revoke select, insert on test.* to 'liwenkai'@'localhost' identified by '4311';
1
2
3

示例说明 给予liwenkai用户，在本机连接test库所有表的权限。操作的这些表具有查询和写入权限

参数说明

符号 说明

grant all 在grant后接all说明给予所有权限
revoke all 在revoke后接all说明删除所有权限

权限 on . . 所明给予所有库所有表的操作权限
‘用户’@‘主机’ 主机里面若为%。任意来源的主机均可以使用这个用户来访问
创建数据库用户liwenkai ，具有对test数据库中所有标的 select / insert 权限

示例：增加权限

mysql> grant select, insert on test.* to 'liwenkai'@'localhost' identified by '4311';
 Query OK, 0 rows affected (0.00 sec)
1
2

示例：移除权限

mysql> revoke insert on test.* from 'liwenkai'@'localhost';
 Query OK, 0 rows affected (0.30 sec)
1
2

注：
上面的一些语句用的较少。你可以将知识点的掌握级别设置为了解级别。
更多的时候，权限设置项特别多，人们往往记不住具体的命令。更多 的时候人们使用专门的工具来操作权限。

PHP操作mysql数据库

通过上一章的学习，我们学习完了MySQL。PHP向MySQL发送数据、PHP操作MySQL数据库是重点中的重点。

为大家举一些例子：

要注册一个用户，是将表单的数据POST发送给PHP写入数据库
购买一个商品，是将商品信息和用户信息通过PHP写入到数据库
在线付费，是将用户的充值信息通过PHP写入数据库
修改头像上传的头像地址得到后，通过PHP修改数据库里头像字段的值
... ...太多太多的应用场景。从网页、移动端、QQ微信公众号都在使用PHP连接数据库进行操作。
1
2
3
4
5

为了更方便大家的学习，我们将连接数据库的知识进行了步骤化。你会发现，你很轻松的就学会了PHP连接数据库的知识。

我们准备的数据库连接的知识，几乎适用于所有的数据库连接的方式。

您也可以使用这一套方案和步骤用于PDO，PgSQl等数据库连接函数使用。

在正式开始学习前，我们需要开启mysqli扩展，使用phpinof()你可以看到如下展示就说明开启成功：

若没有mysqli这个扩展模块。在《10.PHP图像处理》章节跟大家一起学习过，若没有看到mysqli扩展在windows服务器下，打开php.ini文件，将php_mysqli.dll打开即可。

注意：

*从PHP7开始默认不再支持mysql扩展，即不再支持mysql_系列函数。请使用mysqli连接数据库。

mysqli即支持php5也支持php7。

php数据库连接步骤

我们为大家将数据库连接整理成了最重要的8个步骤，我戏称它为：“数据库连接天龙八步”。

这八个步骤如下，并且将每一步使用的函数都做了说明：

第一步: 连接数据库服务器

类型 说明

函数	mysqli_connect
功能	连接到mysql数据库服务器
参数1	主机
参数2	数据库服务器登陆名
参数3	密码
参数4	数据库的名称
参数5	数据库服务器端口不填默认3306
若参数4，数据库名称在此步已填并择，不需要执行第三步。
1
2
3
4
5
6
7
8

第二步: 判断错误

类型	说明
函数	mysqli_errno
功能	返回连接错误号，无错误返回0
参数1	传入mysqli_connect返回的资源
类型	说明
函数	mysqli_error
功能	返回连接错误字符串
参数1	传入mysqli_connect返回的资源
1
2
3
4
5
6
7
8

第三步: 选择数据库

类型	说明
函数	mysqli_select_db
功能	选择本连接中的数据库
参数1	传入mysqli_connect返回的资源
参数2	需要连接的数据库名
若在第一步已填数据库，不需要更换成其他数据库，则不需要执行第三步。
1
2
3
4
5
6

第四步: 设置字符集

类型	说明
函数	mysqli_set_charset
功能	设置与mysql服力器连接，结果，校验字符集
参数1	传入mysqli_connect返回的资源
参数2	字符集类型
更多注意项，请关注本书《13.6 数据显示乱码终极解决方案》
1
2
3
4
5
6

第五步: 准备SQL语句

其实就是一个SQL语句的字符串。

例如：

$sql = "insert into user(username,password) values('$username','$password')";
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

// some code

?>
1
2
3
4
5
6
7
8
9
10
11

我们通常要把变量赋值在SQL语句中使用。可是变量或者SQL语句出错了，非常不好排查。

我们根据实际工作经验增加了这一步。

如果在执行此步的时候报错了，我们可以把SQL语句打印出来，粘贴到phpMyAdmin或者相关工具中。

排错时，如果执行成功就说明不是SQL语句的问题。如果执行失败，请仔细检查SQL语句。

第六步: 发送SQL语句

类型	说明
函数	mysqli_query
功能	发送SQL语句
参数1	传入mysqli_connect返回的资源
参数2	传入发送的SQL语句
1
2
3
4
5

SQL语句准备完成，需要通过mysqli_query将SQL语句发送给MySQL服务器。

MySQL服务器会执行发送过来的SQL语句进行执行。

第七步: 判断是否执行正常或者遍历数据

读取
第6步中，发送的是select类别的语句，通常需要将结果输出显示出来。就需要用到遍历显示数据的函数。

类型 说明
函数 mysqli_fetch_array
功能 得到result结果集中的数据，返回数组进行便利
参数1 传入查询出来的结果变量
参数2 传入MYSQLI_NUM返回索引数组，MYSQLI_ASSOC返回关联数组，MYSQLI_BOTH返回索引和关联
类型 说明
函数 mysqli_fetch_assoc
功能 得到result结果集中的数据，返回关联数组进行便利
参数1 传入查询出来的结果变量
类型 说明
函数 mysqli_fetch_row
功能 得到result结果集中的数据，返回索引数组进行便利
参数1 传入查询出来的结果变量
类型 说明
函数 mysqli_fetch_object
功能 得到result结果集中的数据，返回对象进行遍历
参数1 传入查询出来的结果变量
类型 说明
函数 mysqli_num_rows
功能 返回查询出来的结果总数
参数1 传入查询出来的结果变量
类型 说明
函数 mysqli_num_rows
功能 返回查询出来的结果总数
参数1 传入查询出来的结果变量
注 实际工作中用得非常少，了解
写入
第6步中，如果发送的是insert的语句，通常需要得到是否执行成功，或者同时拿到自增的ID。

类型 说明
函数 mysqli_fetch_field
功能 遍历数据行
参数1 传入查询出来的结果变量
修改和删除
第6步中，如果发送的是update和delete类别的语句。只需要判断是否执行成功即可。

我们将这些常用函数列出数据表给给大家查看。

第八步: 关闭数据库

类型 说明
函数 mysqli_close
功能 关闭数据库连接
参数1 传入mysqli_connect返回的资源
数据库连接是一个资源类型。我们在之前的章节中讲解资源类型的时候跟大家说过。凡是涉及到数资源类型的有打开就有关闭。这样能够保证PHP更高效的处理和回收资源。

因此，数据库连接成功后，不需要使用的时候。我们可以关闭这个连接。

其他：显示服务器信息函数
类型 说明
函数 mysqli_get_server_info
功能 返回服务器信息
参数1 传入mysqli_connect返回的资源
类型 说明
函数 mysqli_get_server_version
功能 返回服务器版本
参数1 传入mysqli_connect返回的资源
注意：
mysqli只学过程化的方法即可。在面向对象阶段实际工作中完全抛弃了mysqli的对象用法，而是使用的是PDO对象连接数据库的方式。

案例：用户注册

我们做一个最简单的注册页面。注册页面中有三个参数：

1.用户名

2.密码

3.重复密码
1
2
3
4
5

用户写好三个参数后，点击提交的时候向connect.php页面中传入POST记录。

我们可以把POST记录处理后写入到MySQL数据库中，即完成了用户注。

代码如下：

<form action="connect.php" method="post">
    用户名：<input type="text" name="username"><br />
    密码：<input type="password" name="password"><br />
    重复密码：<input type="password" name="repassword"><br />
    <input type="submit" value="提交">
</form>
1
2
3
4
5
6

为了更快的表现我们的代码界面没有进行美化。以最快的速度带大家完成用户注册。

一、 判断重复密码

由于有重复密码，如果用户两次输入的密码不一致也就是有没有进行下一步的任何意义。

在网页中很多地方还是使用到了重复密码。因为，害怕的是用户产生手误。将密码填写出错。

用户在输入密码的时候可能在左右两边多打两个空格。因此，我们会使用trim将密码和重复密码的两边去掉空格。

if(trim($_POST['password']) != trim($_POST['repassword'])){
    exit('两次密码不一致,请返回上一页');
}
1
2
3

二、 准备好写入的数据

我们需要把用户的输入数据和隐藏的数据都写入到数据库。

可见数据有：

变量 说明

$_POST['username']	用户名
$_POST['password']	密码
1
2

我们需要把用户名去掉两边的空格，这样避免输入不必要的这些信息。

在mysql这一章节我们讲过，用户的密码不要让包括公司内部人员可见。保证密码是不可逆向的。在初级阶段大家学习一下MD5即可。以后我们再教大家其他的加密方式。

不可见数据有：

变量	说明
$time	用户的注册时间
$_SERVER['REMOTE_ADDR']	用户的注册IP
    
    1.time返回的unix时间戳

    2.REMOTE_ADDR返回的是IP地址，我们可以用ip2long将其转为整型存储。

$username = trim($_POST['username']);
$password = md5(trim($_POST['password']));
$time = time();
$ip = ip2long($_SERVER['REMOTE_ADDR']);
1
2
3
4
5
6
7
8
9
10
11
12

三、连接数据库、判断错误、选择库和字符集

1.我们使用mysqli_connect连接到数据库服务器。

2.如果有错误，使用mysqli_errno得到错误号

3.如何时存在错误mysqli_error打印出所有的错误，并且退出程序执行

4.选择数据库并且设置字符集为utf8.

//连接数据库
$conn = mysqli_connect('localhost','root','123456');
//如果有错误，存在错误号
if(mysqli_errno($conn)){
    echo mysqli_error($conn);
    exit;
}
mysqli_select_db($conn,'user');
mysqli_set_charset($conn,'utf8');
1
2
3
4
5
6
7
8
9

四、组合SQL语句

我们需要把得到的信息写入到数据库里面去，用户名、密码、创建时间、IP我们都得到了。

将对应的变量插入到SQL语句中即可。组合出来的SQL语句如下：

$sql = "insert into user(username,password,createtime,createip) 
values('" . $username . "','" . $password . "','" . $time . "','" . $ip . "')";
1
2

而我们的创建表的语句如下：

CREATE TABLE IF NOT EXISTS user (
id int(11) NOT NULL,
username varchar(30) NOT NULL,
password char(32) NOT NULL,
createtime int(11) NOT NULL,
createip int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

表的格式（字段对应说明）：

id username password createtime createip
用户编号 用户名 密码 创建时间 创建IP

五、发送语句，判断状态

mysqli_query我们在上面说过，需要传入两个参数：

连接的资源，在这里对应的变量是$conn。

发送的SQL语句。在上面已经准备好了$sql。

SQL语句可以通过mysqli_query发送给MySQL服务器。发送成功$result则为true。否则为false。

成功的话，我们就可以提示用户注册成功啦。

有些情况下，可能还需要使用到mysqli_insert_id()。在这里把自增的主键ID打印出来。

大家记住这个知识点，避免以后需要的时候忘记了。

mysqli_insert_id应用场景：新加的一个行的数据。我们需要得到自动增长的ID值，将这个ID值插入到另外一个表里面去时。就需要用到这个函数。

mysqli_insert_id（） 函数返回最后一个查询中自动生成的 ID（通过 AUTO_INCREMENT 生成）。
1

$result = mysqli_query($conn,$sql);
if($result){
    echo '注册成功';
}else{
    echo '注册失败';
}
echo '当前用户插入的ID为'.mysqli_insert_id($conn);
1
2
3
4
5
6
7

五、关闭数据库连接

将资源变量传到到mysqli_close这个函数里面即可。

mysqli_close($conn);
1

用户注册的基本实现代码就写完了。我们上面讲的都是代码片段。

我们整实现的connect.php代码如下：

if (trim($_POST['password']) != trim($_POST['repassword'])) {

   exit('两次密码不一致,请返回上一页');

}

$username = trim($_POST['username']);

$password = md5(trim($_POST['password']));

$time = time();

$ip = $_SERVER['REMOTE_ADDR'];

$conn = mysqli_connect('localhost', 'root', '123456');

//如果有错误，存在错误号
if (mysqli_errno($conn)) {

   echo mysqli_error($conn);

   exit;
}

mysqli_select_db($conn, 'book');

mysqli_set_charset($conn, 'utf8');

$sql = "insert into user(username,password,createtime,createip) values('" . $username . "','" . $password . "','" . $time . "','" . $ip . "')";

$result = mysqli_query($conn, $sql);

if ($result) {
   echo '成功';
} else {
   echo '失败';

}

echo '当前用户插入的ID为' . mysqli_insert_id($conn);

mysqli_close($conn);

?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

别忘了 设置id主键 设置自增----大坑！！！！

案例：做一个列表显示

上一章我们按照我们的“数据库连接天龙八步”，很顺利的就完成了用户注册。

我们来做一个后台的用户列表展示。在实际的管理过程当中，我们通过后台，可以个修改用户的密码和用户的相关资料。

在后台需要将所有用户以表格的形式展示出来就是用户列表。

一、连接数据库、判断错误和设置字符集

连接、错误判断和字符集选择都在上面已经讲过。老规矩，第一步使用mysqli_connect连接数据库。在第一节我们讲过，可以在第四个参数中加上库选择。就可以不用使用mysqli_select_db函数在后面再次选择一个数据库了。

返回的类型就是一个连接资源。我们在mysqli_errno、mysqli_error和mysqli_set_charset都要传入资源，才能确定我们操作的是哪个连接。

$conn = mysqli_connect('localhost', 'root', 'secret', 'book');
if (mysqli_errno($conn)) {
    mysqli_error($conn);
    exit;
}
mysqli_set_charset($conn, 'utf8');
1
2
3
4
5
6

二、准备并发送SQL语句

我们需要查询的的将候将用户ID，用户名、时间和IP都查出来。并且使用order by id 进行降序排序。

按照人的思维人们一般喜欢看最新注册的一批用户。而ID自增，也就是ID在越大，就是时间注册越新的用户。因此我们在写SQL语句的时写上的是order by id desc。

$sql = "select id,username,createtime,createip from user order by id desc";
$result = mysqli_query($conn, $sql);
1
2

三、判断结果

查询出来的结果只要SQL语句正确结果变量$result就为真。因此，在实现的时候我们需要多加一步判断，不仅判断$result。而且，判断查询出来的行数。

查询出来的行数可以使用mysqli_num_rows。这个函数要求传入$result查询的结果变量。

如果有结果则显示列表，如果没有结果我们产生一句提示即可。

代码片段如下：

if($result && mysqli_num_rows($result)){
    //显示列表代码段
}else{
    //提示没有结果的代码段
}
1
2
3
4
5

四、循环显示数据

所有结果我们需要使用列表的形式展示出来。表格的行和列和数据表的行和列是一样的。所示展示起来很方便。

先声明一个表格，每次循环的时候输出一行。将结果展示到各个列里面。

使用到的函数是mysqli_fetch_assoc，返回的会是一个关联数组。

这个函数读取一个结果集，会向后移动一次。读取到最后没有结果的时候会返回bool值的false。因此，我们选择while来配合mysqli_fetch_assoc。

每次循环的结果赋值给$row，$row中是关联数组。因此我在这次循环中，可以将行和列都显示出来。

  echo '';while($row=mysqli_fetch_assoc($result)){echo'';echo'';echo'';echo'';echo'';}echo'

        
         

         
' . $row['username'] . '
         
' . date('Y-m-d H:i:s', $row['createtime']) . '
         
' . long2ip($row['createip']) . '
         
    
     
';
1
2
3
4
5
6
7
8
9

五、增加编辑和删除控制

1.在删除的时候我们分为单选删除和多选删除。

2.而编辑的时候，我们会选择一个用户
1
2
3

我们在上一步的代码中增加几个小东西就在页面中实现了删除和编编。

我们来看**看实际的效果图,**来推理具体的实现过程，效果如下：

在实现过程当中有几个要点：

1.单选择删除和编辑时需使用get方法传入ID，我们才知道要编辑或者删除的是哪个用户。

2.多选删除时，需要使用传入多个用户。因此，我们可以使用form表单，使用post方法来提交这批用户ID。

单选删除我们可以在delete.php后面我们跟上?加上id和值就点击时进行删除请求即可。

echo '. $row['id'] . '">删除用户';
1

编辑用户也是同理，我们在edit.php加上?写上id和值，点击时就知道是需要编辑的哪个用户了。

echo '. $row['id'] . '">编辑用户';
1

而多选删除，我们需要使用到html中的checkbox，传入多个用户ID的时候需要在name 后加上id[]。使用form表单将表格包起来，在表格外加上一个submit标签就实现了多选删除。

<form action="delete.php" method="post">
echo '. $row['id'] . '" />';
echo '';
echo '';
1
2
3
4

代码如下：

echo '
';
    echo '';while($row=mysqli_fetch_assoc($result)){echo'';echo'';echo'';echo'';echo'';echo'';echo'';echo'';}echo'

        
         

         
. $row['id'] . '" />
         
' . $row['username'] . '
         
' . date('Y-m-d H:i:s', $row['createtime']) . '
         
' . long2ip($row['createip']) . '
         
. $row['id'] . '">编辑用户
         
. $row['id'] . '">删除用户
         
    
     
';
    echo '';
    echo ''
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

六、关闭数据库连接

我们操作完数据库，关闭掉这个数据库连接。

mysqli_close($conn);
1

表list.php完整代码

我们整实现的用户列表list.php代码如下：

$conn = mysqli_connect('localhost', 'root', 'secret', 'book');

if (mysqli_errno($conn)) {
   mysqli_error($conn);
   exit;
}

mysqli_set_charset($conn, 'utf8');

$sql = "select id,username,createtime,createip from user order by id desc";

$result = mysqli_query($conn, $sql);

if ($result && mysqli_num_rows($result)) {


   echo '';while($row=mysqli_fetch_assoc($result)){echo'';echo'';echo'';echo'';echo'';echo'';echo'';}echo'


       

        


        
' . $row['username'] . '
        
' . date('Y-m-d H:i:s', $row['createtime']) . '
        
' . long2ip($row['createip']) . '
        
. $row['id'] . '">编辑用户
        
. $row['id'] . '">删除用户

        
   

    
';

} else {
   echo '没有数据';
}

mysqli_close($conn);
?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

在form表单中显示用户信息的源代码

if (is_numeric($_GET['id'])) {

   $id = (int) $_GET['id'];

}

$sql = "select id,username from user where id = " . $id;

$result = mysqli_query($conn, $sql);

$data = mysqli_fetch_assoc($result);

?>


<form action="update.php" method="post">

   用户名：<input type="text" name="username" value="$data['username'];?>"><br />

   密码：<input type="password" name="password"><br />

   <input type="hidden" value="$data['id'];?>" name="id" />

   <input type="submit" value="提交">

</form>
<?php

mysqli_close($conn);

?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

update.php修改的源代码

include 'connection.php';

$id = (int) $_POST['id'];

if (trim($_POST['password'])) {

    $password = md5(trim($_POST['password']));

    $sql = "update user set password='" . $password . "' where id = $id";

} else {

    echo '修改成功';
}

$result = mysqli_query($conn, $sql);

if ($result) {
    echo '修改成功';
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

php数据库操作之数据显示乱码终极解决办法

php连接mysql乱码是开发过程当中，这是开发中新手经常遇到的问题。 根据实际大家遇到的问题，将乱码的问题，总结成了9个要点来彻底解决连接后乱码的问题。

解决乱码问题的核心思想，就是：一定要多个不同的文件系统中一定要统一编码。

这9个要点分别是：

1.html编码与MySQL编码一致

2.PHP编码与MySQL编码一致

3.若有header头发送字符集，请与数据库一样

4.要和页面的文字编码一致

5.数据库建库的字符集要统一

6.表的字符集要统一

7.列的字符集要统一(表设了,列就默认写表的)

8.连接,校验的字符集要统一

9.结果集的字符集要统一
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17