26--- 数据库&MySQL（上）

=======

基本概念

在讲数据库之前先思考一个问题，如果我们建立了一个论坛，想存储注册用户的id，password，name，age，gender，area 等信息，应该如何存储？能否像/etc/passwd 文件一样存储用户的信息，即每行记录一条用户信息？当然是可以的！但进一步思考，如果我们的论坛非常火爆，注册用户达百万，那么我们的这一个文件（或几个文件）来记录的用户信息就有百万条之多。那么当一个用户登陆时，系统会如何检索该用户的信息以验证其身份呢？大概是这么个流程：即我们的服务器接到请求后去通过系统调用open()该磁盘文件，并通过系统调用read()一点点地读数据然后看读到的数据是否能匹配上用户请求中提供的数据。

有没有发现什么问题？如果我们真的用一个（或几个）文本文件来记录用户数据，一个用户登陆时最坏的场景就是，他的信息被记录在最后一条，也就是服务器要遍历完了所有的记录才找到该匹配用户的信息。同时，即使不存在的用户尝试登陆，我们的服务器也会把记录用户信息的记录遍历一边，然后返回说该用户不存在，听上去是不是很扎心？确实，这样检索用户信息的效率太低了。

我们刚刚说的是在文件内容里面检索某条信息，现在让我们把自己的思维比例尺放大，想想我们是如何在文件系统中检索某个文件的。比如/home/9527/xiaoqiang.txt 这个文件，我们的操作系统是怎么找到它的？大概的流程是这样：先找到/ 这个文件（目录也是文件，大家都懂的），这个 / 文件记录了home 这个文件的位置（应该是inode），我们按图索冀就找到了home，然后home中记录了9527这个文件在哪里可以找到，于是我们就找到了9527,同理9527中记录了xiaoqiang.txt的位置，于是我们就找到了xiaoqiang.txt。是不是效率很高？如果我们的文件存储也是线性的，即存储结构为(文件名1：内容1)（文件结束标志）（文件2：内容2）（文件结束标志）。。。那我们要查个文件的话，就要等很长时间咯，最坏的情况就是我们要检索的文件在磁盘最后，或者压根就没有这个文件。

显然文件系统通过了将数据结构化存放（而非线性存放）提高了查找效率，那么我们检索某文件的内容时候，能否也模仿这种方式呢，也就是说我们在要检索数据的文件中（或另外一个专门文件中）加上索引数据的结构，这些结构类似一些目录，以后当我们检索某信息的时候我们就先来查这些目录不就可以了嘛。So smart！数据库管理系统（DBMS）就是干这个事情的。DBMS把我们要存储的数据按照他们的数据组织算法进行结构化，结构化之后再存储在文件系统上。

下面开始抛出几个基础概念，数据库（database）和表（table），数据库管理系统（DBMS)

数据库和表

数据其实就是一些文件，我们把自己要存储的数据结构化（数据库软件完成该工作）之后就存储在这些文件中，在MySQL中，数据库是以目录的形式存在的。那么我们的数据（如网站注册用户信息）存在哪里？存在表中。表又以什么形式存在哪里呢？以文件的形式存在数据库这个目录中。也就是存储我们数据的表，就在数据库目录下面。就是一层目录，一层文件，这就是我们说的数据库，是不是没有想象中的复杂？而 SQLite 数据库就是一个文件，这个文件里面存储了表。下文中会简单介绍如何使用Python操作SQLite数据库。

数据库管理系统（DBMS）

DBMS又分为服务端和客户端。对于MySQL来说，数据库和表可以认为就是目录和文件，那么这些目录和文件是谁创建的，又是谁来管理呢？目录手工创建没有问题，一条mkdir就搞定了，那结构化的数据怎么创建呢？其实这些目录和文件的创建和管理都是由专门的软件来完成的，我们就把这个软件称之为MySQL服务端，服务端的软件管理数据的一个重要方面就是对用户的管理，哪些用户可以对哪些数据库做哪些操作。那我们如何与这个服务端软件交互呢？

其实MySQL服务端软件就是个软件，运行起来就是个进程，我们再写一个客户端程序，让它把我们要做的操作（比如建立数据库，建表等）通过进程间通信的方式告诉服务端进程不就完了嘛。怎么编写这么一个客户端程序呢？常见的就是调用sql通信的库，让这些库里的代码把我们的指令格式化为数据库服务器可以理解的形式。什么？我们要自己写一个程序与MySQL服务端交互？不会编程怎么办？？？没关系，官方也提供了一个客户端程序，我们可以通过这个客户端程序和服务端通信。下文会详细讲解，如何使用官方的客户端，以及如何使用Python编写简单的客户端程序。

在此之前我们再回头看看刚才提到的进程间通信，进程间的通信我们可以简单的分为主机内部通信和主机间的通信，也就是说我们的客户端可以和服务器端在同一台主机上，也可以在不同的主机上。我们分别讨论下，先说主机间的通信。其实就是普通的TCP socket通信，服务端监听在某个端口上（MySQL监听3306），等待客户端发起连接。

如果客户端进程和服务端进程在同一台主机上，那能否使用TCP socket通信呢？当然可以，但是报文要在同一台机器上封装然后解封装，是不是感觉是在做无用功？这就是无用功！！！所以如果客户端和服务端在同一台主机上，我们就可以借助socket文件进行通信了，而不用经过协议栈。

==========

安装 & 配置

这里仅介绍在CentOS 7上安装和配置MySQL的一个分支，即MariaDB。CentOS 7 的光盘上有客户端和服务端的rpm 包，我们以光盘为 yum 源，安装这两个软件包：

yum -y install mariadb

yum -y install mariadb-server

安装完成后可以使用 systemctl start mariadb.service 启动服务端，此时在该主机上执行mysql（回车），即可连接到MariaDB-Server。此时我们是以root的身份在本机localhost登陆的，root默认密码为空，所以我们之执行mysql命令就可以连接上（执行 select user(); 可以查看当前用户信息）。可以使用ss -ntl 查看已经建立的TCP连接，结果中没有自己到自己3306的连接，所以说此时是使用socket文件进行通信的。那么问题来了，如何让其他主机也能连接到我们的MariaDB-Server呢？这个我们后面会讲到，现在先让我们看下软件包中有哪些程序，以及服务端和客户端的配置文件。

MariaDB的程序组成：

     Clietn:

          mysql <--

          mysqldump

          mysqladmin

     Server:

          mysqld <-- daemon

          mysql_safe <-- shell脚本，通过ps aux | grep mysql 可以看到运行的是mysql_saft（内部调用mysqld）

          mysqld_multi

客户端的配置文件也被称为选项文件（option file），有了这些文件在某些情况下就可以避免手工输入连接参数了。比如有时候连接MariaDB服务端要在客户端键入类似mysql -h192.168.10.130 -uxiaoqiang -p 这样的命令，如果我们把选项放到文件中，以后就不用再手工键入了。我们手工键入命令时可以使用短选项（-开头）或长选项（--开头），而在选项文件中，我们只能使用长选项（option=value，option不带--）。选项文件中的内容分了不同的option group，各个group以方括号阔住的字符串为 group name，如[client]。客户端的配置文件为/etc/my.cnf.d/下的 client.cnf 以及my-clients.cnf，/etc/my.cnf.d/下的所有文件都被包含在/etc/my.cnf 中。另外在家目录下建立的 .my.cnf 也可以作为配置文件，这么多配置文件，那加载顺序是什么呢？/etc/mysql/my.cnf --> /etc/my.cnf（含 /etc/my.cnf.d/下的*.cnf） -->--default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf，如果设置有冲突，则以最后读到的值为准。

举例：在client.cnf 下的[client]中加入skip-column-names，之后连接数据库查询得到的结果就不再有表头了。

服务端的配置文件为/etc/my.cnf 和/etc/my.cnf.d/server.cnf , 配置文件的example为/usr/share/mysql/ 下的my-*.cnf，可以参考该文件配置服务端。

对于服务器端，一般建议在[mysqld]下配置skip-name-resolve=yes，这样当客户端连接服务端时，服务端就不会尝试根据客户端的IP 反解其主机名了。（服务端默认是反解客户端IP到主机名，若得到主机名，则把该主机名和服务器端设置的用户信息表对比，匹配则成功，不匹配则失败。若得不到主机名，则用IP和服务器端设置的用户信息表对比，匹配则成功，不匹配则失败。）

=============

账号 & 权限管理

这里只简单说明下基本用法，详细的使用方法请用mysql连接到服务端后使用help create user 和 help grant 得到。

CREATE USER 'userName'@'hostName/IP' IDENTIFIED BY 'userPass'; //创建用户

create user创建用户，userName为用户名，userPass为用户的密码，hostName/IP 则限制这个用户只能在特定主机名或IP的主机登陆。

GRANT ALL ON db_name.tbl_name TO 'userName'@'userHost/IP'; //为用户分配权限

这里的ALL代表所有权限，这个权限是对数据库db_name下的表tbl_name 来说的，该权限被分配给用户userName（且客户端主机被userHost/IP限定）

=============

客户端的使用

上面已经简单使用过客户端并发送命令到服务器，其实上面的命令可以拆分为两部分：发给服务端的命令+命令结束符，前面使用的都是以分号为结束符，告诉客户端，命令已经键入完毕，请发送给服务器端，当然发送给服务器的消息中就不带分号，也就是分号不是SQL命令的一部分。

这里的分号我们就是一个delimiter，也就是命令分隔符，标识一条命令的结束，我们可以使用delimiter 命令后跟某符号，以将该符号设定为新的分隔符。除了delimiter外，还有很多指令是作用于客户端的，常用的如下：

\g    可以代替delimiter（无论delimiter被设置成什么），命令\g加回车，即可将命令发送给服务器端

\c    键入命令后，不想执行，可以在命令后键入\c，然后回车，类似于bash下的 ctrl+ c

\d    作用同delimiter，后跟新的分隔符

\!    后跟shell命令，vi下的末行模式也有这个功能（:!COMMAND）

\.sql_script    执行文件系统下的sql_script，只要sql有权限读和执行

\q    退出连接

若要获得所有的客户端命令，mysql>提示符下执行 help即可。

====================

简单的Python客户端

这里用Python实现了连接MySQL服务端并执行测试命令的一个简单程序，其中的SQL语句不在本文中详细解释，如有需要，可以参考下一篇讲解SQL基本用法的文章。

Python脚本如下：

#!/usr/bin/python

import sys

import MySQLdb //如果MySQLdb没有安装，可通过 yum install MySQLdb 安装之

try:

     conn=MySQLdb.connect(host='192.168.10.133', //MySQL服务端地址，用户名，密码及要操作的数据库

                                             user='xiaoqiang',

                                             passwd='xiaoqiang',

                                             db='xiaoqiang_test')

     print "connected"

     cur=conn.cursor() //创建cursor对象，之后通过cursor对象的execute方法发送SQL语句到指定服务端

     cur.execute('drop table if exists pytable') //可以看到命令后并无分号，即上文所说的分号不是SQL语句的一部分

     cur.execute('create table pytable(id tinyint not null primary key,name varchar(20) not null)')

     cur.execute("insert into pytable (id,name) values (1,'wangchongyang')")

     cur.execute("insert into pytable (id,name) values (2,'huangyaoshi')")

     cur.execute("insert into pytable (id,name) values (3,'ouyanfeng')")

     cur.execute("insert into pytable (id,name) values (4,'yidengdashi')")

     cur.execute("insert into pytable (id,name) values (5,'hongqigong')")

     conn.commit()

     cur.execute("select * from pytable where id >=3")

     for daxia in cur.fetchall(): //cur.fetchall 返回结果集中剩下的所有行

          print '%s\t%s' %daxia

except MySQLdb.Error,e:

     print "cannot connect to the server"

     print "Error code",e.args[0]

     print "Error msg",e.args[1]

cur.close()

conn.close()

print "disconnected"

解释：这个简单的Python脚本首先以xiaoqiang的身份连接到192.168.10.133 的MySQL服务端的xiaoqiang_test 这个数据库，然后在该数据库中新建了一张表，并在表中插入了5行记录，之后使用select 语句查询出指定条件的记录，完成操作后关闭到服务器的连接。

上文说过SQLite是个轻量的数据库，我们也可以使用Python来操作之，测试代码如下，该脚本在/tmp目录下建立了mysqlite.sqli 文件（就是一个数据库），然后在该数据库中新建了两张表，并插入了一些数据，之后通过select将这些数据查询显示出来。

#!/usr/bin/python

import sqlite3

conn=sqlite3.connect('/tmp/mysqlite.sqli')

cur=conn.cursor()

cur.execute('drop table if exists tbl_01')

cur.execute('drop table if exists tbl_02')

cur.execute('create table tbl_01(id tinyint not null primary key,name varchar(20),menpai_id tinyint)')

cur.execute('create table tbl_02(mp_id tinyint not null primary key,menpai varchar(20))')

cur.execute("insert into tbl_01 (id,name,menpai_id) values (1,'zhangsanfeng',66)")

cur.execute("insert into tbl_01 (id,name,menpai_id) values (2,'wangchongyang',99)")

cur.execute("insert into tbl_02 (mp_id,menpai) values (66,'wudang66')")

cur.execute("insert into tbl_02 (mp_id,menpai) values (99,'wudang99')")

cur.execute('select * from tbl_01')

for gaoshou in cur.fetchall():

     print gaoshou

cur.execute('select * from tbl_02')

for menpai in cur.fetchall():

     print menpai

cur.close()

conn.commit()

conn.close()