学习 MySQL

作者：孔建军

MySQL 介绍

MySQL 是瑞典 MySQL AB 公司开发的一个完全网络化的跨平台关系型数据库系统，具有多用户、多线程、多种客户工具和API支持、低消耗、底成本、高性能等特点。她具有客户机/服务器体系结构的分布式数据库管理系统，由一个服务器守护进程 mysqld 和不同客户程序和库组成。由于其源码的开放性及稳定性，在 WEB 开发应用等方面得到了广泛使用，具有较强的竞争力和市场前景。目前Internet上流行的网站构架方式是 LAMP（Linux+Apache+MySQL+PHP），即操作系统使用GNU/Linux，Web服务器使用Apache，数据库服务器使用MySQL，服务器端脚本解释器使用PHP。这四个软件都遵循 GPL 协议开放源代码，用户可以快速搭建一个真正稳定、免费的网站系统。虽然没有大型专业数据库（如Orcal）高端功能强大，但其灵活方便等优势也赢得了众多用户的关注。
Sun于今年初以10亿美元高价收购MySQL，据说 Sun 准备关闭MySQL备份方案的源代码，许多高级功能的代码也将不再开放，即不会再免费向开源社区贡献。
MySQL 特征：

1.使用C和C++编写，并使用了多种编译器进行测试，保证了源代码的可移植性
2.支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统
3.为多种编程语言提供了API。这些编程语言包括C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等
4.支持多线程，充分利用CPU资源
5.优化的SQL查询算法，有效地提高查询速度
6.既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中提供多语言支持，常见的编码如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名
7.提供TCP/IP、ODBC和JDBC等多种数据库连接途径
8.提供用于管理、检查、优化数据库操作的管理工具
9.可以处理拥有上千万条记录的大型数据库

运行效果

kongove@ubuntu:~/Desktop/zeuux.org$ mysql -h localhost -u root -p
Enter password: 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> select * from student;
+------+--------------+
| id	      | name                     |
+------+--------------+
|    1        | XiyouLinux           | 
+------+--------------+
1 rows in set (0.01 sec)
mysql> 

基本使用

安装

• Redhat 下使用可执行压缩包安装 MySQL：
# rpm -qa|grep mysql
查看是否已经安装 mysql
# wget http://URL-OF-MySQL/mysql.tar.gz
下载 mysql 软件包
# tar xcvf mysql.tar.gz
解压软件包
# cd mysql/
# make
# make install
# cd scripts
# mysql_install_db
完成具体编译、安装

• Fedora 下通过软件包管理工具 yum 安装 MySQL：
# yum install mysql-server mysql-client mysqlversion
• Ubuntu 下通过APT软件包管理工具安装 MySQL：
# apt-get install mysql-server mysql-client mysqlversion

配置

• 启动系统时自动运行 MySQL 服务器
在 /etc/rc.local中添加启动一行命令： /etc/init.d/mysql start

• 配置数据库用户
安装mysql过程中，会自动提示设置默认用户 root 的登录数据库密码。
管理员还可以使用GRANT语句添加新用户并指定其权限，使用REVOKE语句删除指定用户权限。


$ mysql> grant select,insert,update,delete on *.* to "new_user"@"%" Identified by "password";
添加新用户new_user，密码为password，让该用户可在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。
$ mysql> delete from user where User="user_name"; $ mysql> flush privileges;
delete语句删除指定用户记录，flush语句告诉服务器重载授权表。
$ mysql> grant select on table $tablename to $user;
把查询$tablename表的权利授给用户$user
$ mysql> grant select on table $tablename to public;
把对表$tablename查询的权利给所有用户
$ mysql> grant createtab on database $databasename to $user;
把在数据库$databasename中创建新表的权利授给用户$user
$ mysql> revoke update(id) on table $tablename from $user;
把用户$user更新$tablename表 id 数据项的权利收回

aCREATE和REVOKE语句影响4个表：

授权表	内容
user	能连接服务器的用户以及他们拥有的任何全局权限
db	数据库级权限
tables_priv	表级权限
columns_priv	列级权限

测试

基本操作

• 服务器命令：
#/etc/init.d/mysql start
启动数据库服务
#/etc/init.d/mysql stop
关闭数据库服务
#/etc/init.d/mysql restart
重启数据库服务
#/etc/init.d/mysql status
查看数据库服务状态
#/etc/init.d/mysql reload
重新加载服务
# /etc/init.d/mysql force-reload
强制重新加载服务

• 客户端命令选项：
mysql [-h $hostname] [-u $username] -p $datebasename

选项	含义
-h $hostname	指定数据库服务器，可以为IP地址或者域名
-p	提示用户输入密码
-u $username	指定用户名
$databasename	指定数据库名

• 客户端内部命令：

命令	简洁命令	含义
?	(\?)	等价于'help'
clear	(\c)	清除命令
connect	(\r)	重新连接服务器，选项参数是数据库名和主机名
delimiter	(\d)	设置定界符
edit	(\e)	采用 $EDITOR 编辑命令
ego	(\G)	向 mysql 数据库服务器发送命令，并将返回结果垂直输出
exit	(\q)	退出 mysql, 等价与 quit
go	(\g)	向数据库服务器发送命令
help	(\h)	显示此帮助信息
nopager	(\n)	紧用 PAGER，打印到标准输出
notee	(\t)	不写入输出文件
pager	(\P)	设置通过 PAGER 打印结果
print	(\p)	打印当前命令
prompt	(\R)	改变 mysql 提示模式
quit	(\q)	退出 mysql
rehash	(\#)	重新编译完成 hash
source	(\.)	执行 SQL 脚本文件，带一个文件名作为参数
status	(\s)	从服务器得到状态信息
system	(\!)	执行一个 Shell 命令
tee	(\T)	指定输出文件，添加所有信息到此文件
use	(\u)	指定别的数据库，新数据库名作为参数
charset	(\C)	转换成另一个字符编码，可能需要处理多自己编码的 binlog
warnings	(\W)	显示执行指令的警告信息
nowarning	(\w)	不显示执行指令的警告信息

• 常用客户端命令范例：
$mysql -h mysql.kongove.cn -u kong -p
登录数据库
$ mysql> show databases;
显示当前服务器上所有数据库
$ mysql> use $databasename;
选择使用数据库
$ mysql> show tables;
显示当前数据库中的所有表
$ mysql> create database $databasename;
创建数据库
$ mysql> create table $tablename(id int, name char(5), age int);
在当前数据库里创建表
$ mysql> create view $viewname as select id, name from $tablename;
在当前数据库里创建视图
$ mysql> drop table $tablename;
删除当前数据库里指定的表
$ mysql> drop view $viewname;
删除当前数据库里指定的视图
$ mysql> select * from $tablename;
显示指定表的内容
$ mysql> select id, name from $tablename;
显示指定表中指定数据项
$ mysql> select name from $viewname;
显示指定视图中指定内容
$ mysql> select * from $tablename where id>20;
根据条件筛选查看指定表中id号大于20的表项
$ mysql> select id from $tablename where age=(select age from $tablename2 where name='孔建军');
带有比较运算符的子查询
$ mysql> insert into $tablename(id,name,score) values(1,'kong',100);
添加指定表项
$ mysql> update $tablename set score=99 where id=1;
修改指定表中满足条件表项的相关值
$ mysql> delete from $tablename where id=1;
删除满足条件的指定表项

数据备份与恢复

• 备份数据库原则：
1. 定期实施备份；
2. 让服务器执行更新日志；
3. 使用一种传统、易理解的备份文件名机制；
4. 用文件系统备份用户的备份文件。

• 备份
$ mysqldump -h $hostname -u $username -p $databasename >2008.5.22.sql
使用 mysqldump 备份整个数据库到文件2008.5.22.sql
$ cp -r $datadir/$databasename /usr/archive/mysql/
直接复制数据库进行数据备份
编写Shell脚本，自动备份数据库内容：


#!/bin/bash
#备份指定服务器、用户名的数据库内容，并以当前时间命名文件。
mysqldump -u username -h hostname -p databasename > `date +%Y%m%d_%H:%M:%S`.sql

运行脚本完成备份：
[crystallight]$ ./mysqldump.sh 
Enter password: 
[crystallight]$ ls
20081014_22:56:39.sql	mysqldump.sh

• 还原
$ mysql> source 2008.5.22.sql
使用备份文件还原数据库
$ mysql -u root -h mysql.kongove.cn -p $databasename <2008.5.22.sql
使用备份文件还原指定数据库

数据库连接

• PHP连接 MySQL 数据库

$db['host'] = "222.24.20.86";
$db['user'] = "kong";
$db['pass'] = "passwrod";
$db['database'] = "kong_database";
$db['table'] = "kong_table";
#连接数据库
ql_connect($db['host'],$db['user'],$db['pass']);
mysql_select_db($db['database']);
mysql_query($sql);
#插入数据项
$sql = "insert into ".$table." (id,user_name,user_info,head_type,hide,message,ip,time,reply,
reply_time) values(null,'$user_name','$user_info','$head_type','$hide','$message','$ip','$time',null,null);";
$result = mysql_query($sql);
#筛选查询
$sql = "select * from $table order by id desc limit $start_msg,$max_msg;";
$result = mysql_query($sql);

• Jsp 连接 MySQL 数据库

String url ="jdbc:mysql://localhost/softforum?user=soft&password=soft1234&useUnicode=true&characterEncoding=8859_1"
Connection conn= DriverManager.getConnection(url);
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="select * from test";
ResultSet rs=stmt.executeQuery(sql); 

• C 语言连接 MySQL 数据库

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd,
const char *db, unsigned int port, const char *unix_socket, unsigned int client_flag)

• Python 连接数据库

import MySQLdb as mydb
db = mydb.connect(host='192.168.0.110',user='jianjun',passwd='lovelinux',db='webdatabase', charset="utf-8")
cur = db.cursor()
sql = unicode("select user,id from user_table where sex = '男'","utf-8")
print sql
cur.execute(sql)
cur.close()
db.close()

图形管理工具

phpMyAdmin、mysql-admin、MySQL Administrator、MySQL Query Browser、phpMyBackupPro 等都是不错的图形化管理数据库工具，可以方便的进行数据库各项管理、维护操作。
phpMyAdmin 可以通过 web 界面，使用 http 方式来进行连接管理，用户在浏览器即可完成管理任务，不需要特殊软件环境。Web 服务器端必须支持php解析功能。在Web服务器端配置好 apache 和 php ，下载 phpadmin，解压到 apache 能够访问的一个目录中。修改phpadmin/config.inc.php文件，其中主要是对mysql连接方式，连接路径以及用户名密码的设置。然后运行访问 index.php 进入管理页面。
MySQL Administrator、MySQL Query Browser、mysql-admin 是linux系统中几款数据库管理软件，其管理功能完备，配置简单。
phpMyBackupPro 也是由 PHP 写成的，可以透过 Web 介面创建和管理数据库。它可以创建伪 cronjobs，可以用来自动在某个时间或周期备份MySQL 数据库。

总结

参考资料

• MySQL 官方参考手册