Question

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。Mysql是开源的，而且支持大型的数据库，可以处理上千万条记录，因此如果你的数据量很大的话，MySQL确实是一个不错的选择。本节将对MySQL的一些基本操作进行讲解。

8.2.1　安装MySQL

接下来开始进行MySQL的安装，以Ubuntu和Windows为例。

1.Ubuntu下安装和配置MySQL

Ubuntu上安装MySQL非常简单，打开命令行窗口，输入以下命令即可。

·sudo apt-get install mysql-server

·sudo apt-get install mysql-client

·sudo apt-get install libmysqlclient-dev

在安装的过程中需要根据提示设置MySQL账号和密码，如图8-7所示。注意设置完不要忘记，之后会使用账号密码登录MySQL。

安装完成之后可以使用如下命令来检查是否安装成功：sudo netstat-tap|grep mysql。如果看到有mysql的socket处于listen状态则表示安装成功，效果如图8-8所示。

接下来使用之前设置的账号和密码进行登录，在命令行中输入：mysql-u root-p。这时候会提示输入密码。成功登录如图8-9所示。

图8-7　配置MySQL

图8-8　安装MySQL成功

图8-9　Ubuntu下登录MySQL成功

2.Windows下安装和配置MySQL

Windows版MySQL的下载地址为http://dev.mysql.com/downloads/mysql/ ，大家可以根据自己的系统版本进行下载，下载的是一个zip格式的压缩包。配置步骤如下：

1）将下载的mysql-*.zip解压到文件夹，如：D：\MySQL

2）在安装文件夹下找到my-default.ini配置文件，将其重命名为config.ini，打开该文件进行编辑，修改basedir和datadir。basedir为MySQL所在目录，datadir为basedir下的data文件夹（如果没有，自行创建），修改内容如下：

  basedir = D:\MySQL\mysql-5.7.16-winx64;
  datadir = D:\MySQL\mysql-5.7.16-winx64\data。

3）打开Windows环境变量设置，新建变量名MYSQL_HOME，变量值为MySQL安装目录路径，这里为D：\MySQL\mysql-5.7.16-winx64。在环境变量的Path变量中追加“%MYSQL_HOME%\bin；”

4）安装MySQL服务，以管理员权限运行Windows命令提示符，并切换到bin目录下，执行命令：mysqld--install MySQL--defaults-file=“D：\MySQL\mysql-5.7.16-winx64\config.ini”，提示“Service successfully installed.”表示成功

安装完成后，接着在windows命令行中输入：mysqld--initialize-insecure--user=mysql，最后通过以下命令实现对MySQL服务的控制：

·启动：net start MySQL

·停止：net stop MySQL

·卸载：sc delete MySQL

服务启动完成后，登录MySQL，输入：mysql-u root-p。一开始是没有密码的，要求输入密码时直接回车即可，登录成功如图8-10所示。

图8-10　Windows下登录MySQL成功

8.2.2　MySQL基础

完成MySQL的配置，接下来开始讲解MySQL的基础内容。

1.MySQL数据类型

MySQL数据类型比之前讲的SQL语法中的数据类型多了一些，下面通过表8-6对MySQL的数据类型进行以下总结。

表8-6　MySQL数据类型总结

2.MySQL关键字

MySQL有一些和数据类型有关的关键字，如表8-7所示。

表8-7　MySQL的关键字总结

3.创建数据库与表

登录成功MySQL之后，使用create database语句可完成对数据库的创建，创建命令的格式如下：

  create database 数据库名 [其他选项];

比如创建一个名称为test的数据库，并设置编码为gbk：

  create database test character set gbk;

创建成功后，会返回如下信息：

  Query OK, 1 row affected (0.01 sec)

创建成功后，我们需要对数据库进行选择，选择成功后才能对数据库进行操作。使用use语句指定，命令如下：

  use 数据库名;

选择创建的test数据库：

  use test;

选择成功后会提示：Database changed

使用create table语句可实现对表的创建，创建命令的格式如下：

  create table 表名称(列声明1, 列声明2,...);

以创建student表为例，表中有学号（id）、姓名（name）、性别（sex）、年龄（age）等列：

  create table student
  (
     id int unsigned not null auto_increment primary key,
     name char(8) not null,
     sex char(4) not null,
     age tinyint unsigned not null
  );

如果担心在命令行中输入这么长的SQL语句会出错，可以将以上SQL语句保存为create_student.sql文件，比如将它保存在D盘根目录。有两种方式可以让MySQL执行sql文件。

·在登录MySQL的时候输入：mysql-D test-u root-p< li="">

·在登录MySQL之后，输入：source D：\create_student.sql，或输入：\.D：\create_student.sql。

4.增删改查操作

MySQL中的增删改查操作基本上和SQLite一样，下面以student表进行演示：

·insert into student values（NULL，“七夜”，“男”，24）；

·update student set age=18where name=“七夜”；

·select name，age from student；

·delete from student where age=18；

效果如图8-11所示。

图8-11　MySQL下增删改查

注意 　在MySQL中字符串既可以使用单引号包裹，也可以使用双引号包裹。

5.对表结构的操作

alter table语句用于对创建后的表进行修改，MySQL对表结构的操作相对于SQLite更加完整和丰富。基本用法如表8-8所示。

表8-8　MySQL对表结构的操作

示例如下：

·alter table student add address varchar（60）after age；

·alter table student change address addr char（60）；

·alter table student drop addr；

·alter table student rename students；

效果如图8-12所示。

图8-12　表结构操作

6.删除数据库和表

基本用法如表8-9所示。

表8-9　MySQL删除数据库和表的操作

7.MySQL常用命令

下面主要说一下常用的MySQL命令，其中例子均是在命令行中的操作，用法如表8-16所示：

连接MySQL。 命令格式为：mysql-h主机地址-u用户名－p用户密码。示例如下：

1）连接到本机MySQL。

在命令行中输入mysql-u root-p；，回车后按提示输入密码。

2）连接到远程主机上的MySQL。

远程主机的IP为：10.110.18.120，用户名为root，密码为123：mysql-h 10.110.18.120-u root-p 123；

修改密码。 命令格式为：mysqladmin-u用户名-p旧密码password新密码。示例如下：

1）给root加个密码abc12。

在命令行中输入mysqladmin-u root-password abc123；，开始的时候root没有密码，所以“-p旧密码”一项就可以省略了。

2）再将root的密码改为root123。

  mysqladmin -u root -p abc123 -password root123;

增加新用户。 命令格式：grant权限1，权限2，…权限n on数据库名称.表名称to用户名@用户地址identified by’密码‘；，示例如下：

给来自10.163.215.87的用户qiye分配可对数据库company的employee表进行select、insert、update、delete、create、drop等操作的权限，并设定口令为123。

  mysql>grant select,insert,update,delete,create,drop on company.employee to qiye@10.163.215.87 identified by '123';

显示数据库。 命令格式：show databases。示例如下：

  mysql> show databases;

备份数据库。 数据库的备份包括数据库的备份、表的备份。格式：mysqldump-h主机名-P端口-u用户名-p密码-database数据库名表名>文件名.sql。示例如下：

1）导出整个数据库。

  mysqldump -u user_name -p123456 database_name > outfile_name.sql

2）导出一个表。

  mysqldump -u user_name -p123456 database_name table_name > outfile_name.sql

8.2.3　Python操作MySQL

1.导入MySQLdb数据库模块

在导入MySQLdb之前，需要安装MySQLdb模块。使用pip安装，命令如下：

  pip install MySQL-python

安装成功后，导入MySQLdb模块：

  import MySQLdb

2.打开数据库

sqlite3模块使用connect方法打开数据库，方法参数可以为主机ip（host）、用户名（user）、密码（passwd）、数据库名称（db）、端口（port）和编码（charset）。

  con = MySQLdb.connect(host='localhost',user='root',passwd='',db='test', port=3306, charset='utf8')

3.数据库连接对象

上面通过connect方法返回的con对象，即是数据库连接对象，它提供了以下方法：

·cursor（）方法用来创建一个游标对象。

·commit（）方法用来事务提交。

·rollback（）方法用来事务回滚。

·close（）方法用来关闭一个数据库连接。

4.游标对象的使用

对数据库的查询需要使用到游标对象，首先通过cursor（）方法创建一个游标对象：

  cur = con.cursor()

游标对象有以下方法支持数据库的操作：

·execute（）用来执行SQL语句。

·executemany（）用来执行多条SQL语句。

·close（）用来关闭游标。

·fetchone（）用来从结果中取一条记录，并将游标指向下一条记录。

·fetchmany（）用来从结果中取多条记录。

·fetchall（）用来从结果中取出所有记录。

·scroll（）用于游标滚动。

5.建表

首先使用游标对象创建一个person表，包含id、name、age等3列，代码如下：

  cur.execute(' CREATE TABLE person (id int not null auto_increment primary key,name varchar(20),age int)')

6.插入数据

向person表中插入两条数据。插入数据一般有两种做法，第一种做法是直接构造一个插入的SQL语句，代码如下：

  data="'qiye',20"
  cur.execute(' INSERT INTO person (name,age) VALUES (%s)'%data)

但是这种做法非常不安全，容易导致SQL注入。另一种做法使用占位符“%s”的方式来规避这个问题，代码如下：

  cur.execute(' INSERT INTO person (name,age) VALUES (%s,%s)',('qiye',20))

还可以使用executemany（）执行多条SQL语句，使用executemany（）方法比循环使用execute（）方法执行多条SQL语句效率高很多。

  cur.executemany(' INSERT INTO person (name,age) VALUES (%s,%s)',[('marry',20),
('jack',20)])

这两种方法插入数据都不会立即生效，需要使用数据库对象con进行提交操作：

  con.commit()

如果出现错误，还可以使用回滚操作：

  con.rollback()

7.查询数据

查询person表中的所有数据，代码如下：

  cur.execute('SELECT * FROM person')

要提取查询数据，游标对象提供了fetchall（）和fetchone（）方法。fetchall（）方法获取所有数据，返回一个二维的列表。fetchone（）方法获取其中的一个结果，返回一个元组。使用方法如下：

  cur.execute('SELECT * FROM person')
  res = cur.fetchall()
  for line in res:
     print line
  cur.execute('SELECT * FROM person')
  res = cur.fetchone()
  print res

8.修改和删除数据

  cur.execute('UPDATE person SET name=%s WHERE id=%s',('rose',1))
  cur.execute('DELETE FROM person WHERE id=%s',(0,))
  con.commit()
  con.close()