mysql数据库使用6_MySQL学习笔记（6）—— MySQL 的数据库操作

前面章节陆续完成了 MySQL 的介绍和安装配置，至此已经可以进行数据库操作语言的学习了。作为学习笔记，本章更多地采用列表地形式简洁地记录操作命令，便于后续查阅和使用，而且数据库操作语言的介绍将结合 python 一起。

数据库的显示、创建和选用

SHOW DATABASES 显示数据库

根据第四章内容，首先启动 MySQL 服务器，并登录数据系统(可以是root用户，也可以是guest用户)。登录后首先显示已有数据库：

$ ps -ef | grep mysqld # 检查数据库服务器是否启动

$ mysqld_safe & # 若未启动，则开启服务器

$ mysql -u guest -p # 前文创建，密码：guest123

mysql > show databases; # 进入服务器，并 show

+---------------------+

| Database |

+---------------------+

| information_schema |

| mysql |

| performance_schema |

+---------------------+

3 rows in set (0.00 sec)

结果表明，MySQL 服务器中已经存在3个数据库，这都是系统自带的配置数据。其中，mysql 已经在前一章有所接触，用于管理 MySQL 的用户信息。如果存在数据库较多时，可以采用 like + % 的组合方式进行显示：

mysql > show databases like 'my%'; # 进入服务器，并 show

+---------------------+

| Database |

+---------------------+

| mysql |

+---------------------+

1 rows in set (0.00 sec)

CREATE DATABASE 创建数据库

CREATE DATABASE 用给定的名字创建一个数据库。在 MySQL 中的数据库是包含对应数据库中表的文件的目录，因为数据库在初始

创建时没有任何表，CREATE DATABASE 语句只是在 MySQL 数据目录下面创建一个目录。下面语句创建一个名为 pi 的数据库：

mysql > create database pi

进行创建操作时，可能会遇到权限问题。因为 MySQL 中对各用户的权限有明确的规定，以保证数据库内多用户使用时的安全性。这里简单的介绍如何赋予用户 guest 插入和删除的权限，具体关于权限的讨论敬请参考后续章节介绍。

mysql -u guest -p

Enter your password: *****(guest123)

USE pi;

SHOW GRANTS for guest;

GRANTS SELECT, INSERT, DELETE, UPDATE, CREATE, DROP ON * TO guest;

flush privileges;

DROP DATABASE 删除数据库

DROP DATABASE 删除数据库中的所有表和数据库(小心地使用该命令! )DROP DATABASE 返回从数据库目录被删除的文件的数目，通常数目是实际数据量的3倍，因为每张表对应于一个“.MYD”文件、一个“.MYI”文件和一个“.frm”文件。另外，可以使用关键词 IF EXISTS 阻止一个错误的发生，如果数据库不存在。

mysql > drop database [if exists] pi

在 shell 环境下创建和删除

在命令行环境 shell 下可以使用 mysqladmin 创建和删除数据库：

$ mysqladmin create db_name

$ mysqladmin drop db_name

USE 选用数据库

USE db_name 语句告诉 MySQL 使用 db_name 数据库作为随后的查询的缺省数据库。数据库保持到会话结束，或发出另外一个 USE 语句:

mysql > USE db1;

mysql > SELECT count(*) FROM mytable; # selects from db1.mytable

mysql> USE db2;

mysql > SELECT count(*) FROM mytable; # selects from db2.mytable

如果你不是用 USE 语句，那么上面的例子应该写成：

mysql > SELECT count() FROM db1.mytable;

mysql > SELECT count() FROM db2.mytable;

数据库操作及 Python 实现

前文内容已经能够带你定位并进入到用户自定义的数据库 pi 了，后续将在 pi 中进行数据库的操作包含创建、删除和选择等。

SHOW / DESCRIBE 显示数据表信息

在进行数据表操作前，先得了解一些数据表的显示方法，以帮助后续操作的效果检验，其语法如下：

SHOW TABLES [FROM db_name] [LIKE wild]

SHOW COLUMNS FROM tab_name [FROM db_name] [LIKE wild]

SHOW INDEX FROM tab_name [FROM db_name]

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

{DESCRIBE | DESC} tab_name {col_name | wild}

注意:如果一个用户没有一个表的任何权限，表将不在 SHOW TABLES 中输出显示，需要先登录。

CREATE TABLE 创建数据表

语法：

CREATE TABLE tab_name(create_definition,...) [TYPE =table_type]

create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value]

[AUTO_INCREMENT][PRIMARY KEY]

SQL 语言实例：

CREATE TABLE EMPLOYEE (

ID INT AUTO_INCREMENT PRIMARY KEY,

FIRST_NAME CHAR(20) NOT NULL,

LAST_NAME CHAR(20),

AGE INT,

SEX CHAR(1),

INCOME FLOAT )

Python 语言实例：

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接

db = MySQLdb.connect("localhost","guest","guest123","pi" )

# 使用cursor()方法获取操作游标

cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。

cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句

sql = """CREATE TABLE EMPLOYEE (

ID INT AUTO_INCREMENT PRIMARY KEY,

FIRST_NAME CHAR(20) NOT NULL,

LAST_NAME CHAR(20),

AGE INT,

SEX CHAR(1),

INCOME FLOAT )"""

cursor.execute(sql)

# 关闭数据库连接

db.close()

采用前文介绍方法登录数据库，查看代码运行结果。

Tips: 这里将表格 EMPLOYEE 的 ID 属性设置为主键，联系前文得知，工号可以作为一个员工的唯一识别方法;

与创建相对应的是拷贝一个数据表，采用 CREATE + SELECT 组合的方法，如下：

CREATE TABLE pi2 SELECT * from pi;

INSERT 插入数据

语法：

INSERT [INTO] tab_name [(col_name,...)] VALUES (pression,...),...

INSERT [INTO] tab_name SET col_name=expression, ...

SQL 语言实例：

INSERT INTO EMPLOYEE(FIRST_NAME,

LAST_NAME, AGE, SEX, INCOME)

VALUES ('Mac', 'Mohan', 20, 'M', 2000)

Python 语言实例：

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接

db = MySQLdb.connect("localhost","guest","guest123","pi" )

# 使用cursor()方法获取操作游标

cursor = db.cursor()

# SQL 插入语句

sql = """INSERT INTO EMPLOYEE(FIRST_NAME,

LAST_NAME, AGE, SEX, INCOME)

VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:

# 执行sql语句

cursor.execute(sql)

# 提交到数据库执行

db.commit()

except:

# Rollback in case there is any error

db.rollback()

# 关闭数据库连接

db.close()

同样，可以采用 INSERT + SELECT 的组合方式进行数据插入，例如：

INSERT INTO tab_name1(col1,col2) SELECT col3,col4 from tab_name2;

REPLACE 功能与 INSERT 完全一样，除了如果在表中的一个老记录具有在一个唯一索引上的新记录有相同的值，在新记录被插入之前，老记录被删除。对于这种情况，INSERT 语句的表现是产生一个错误。

LOAD 批量导入数据

语法:

LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tab_name

LOAD DATA INFILE 语句从一个文􏰀文件中以很高的速度读入一个表中。如果指定 LOCAL 关键词，从客户主机读文件。如果 LOCAL 没指定，文件必须位于服务器上。

REPLACE 和 IGNORE 关键词控制对现有的唯一键记录的重复的处理。如果你指定 REPLACE，新行将代替有相同的唯一键值的现有行。如果你指定 IGNORE，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键键时，出现一个 错误，并且文文件的余下部分被忽略时。

当在服务器主机上寻找文件时，服务器使用下列规则:

如果给出一个绝对路径名，服务器使用该路径名。

如果给出一个或多个前置部件的相对路径名，服务器相对服务器的数据目录搜索文件。

如果给出一个没有前置部件的文件名，服务器在当前数据库的数据库目录寻找文件。

Tips: 这些规则意味着一个像“./myfile.txt”给出的文件是从服务器的数据目录读取，而作为“myfile.txt”给出的一个文件是从当前数据库的数据库目录下读取。

小结

为数据库装载数据是管理员的重要职责之一，本节主要介绍 MySQL 中创建数据库和添加数据的方法，并结合 Python 语言进行讲解。 其中主要的命令列举如下:

SHOW / DESCRIBE 显示数据库状态

CREATE / DROP 创建和删除数据库和数据表

INSERT / REPLACE 添加数据

LOAD DATA INFILE 批量导入文件中的数据 (后续会介绍 mysqlimport 工具)