Mysql操作数据常用语句(数据库信息、表修改等)

参考教程：
http://dev.mysql.com/doc/refman/5.1/zh/introduction.html

在配置文件中(windows下是my.ini)中

# The TCP/IP Port the MySQL Server will listen on 
#监听端口
port=3306

1.软件及数据位置

#Path to installation directory. All paths are usually resolved relative to this.
#MySQL安装路径
basedir="C:/Program Files/MySQL/MySQL Server 5.5/" 
#Path to the database root
#MySQL数据库文件路径
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

2.系统信息
#版本信息 使用大写字母V 小写的不行(cmd命令)

cmd > mysql -V
mysql  Ver 14.14 Distrib 5.5.27, for Win32 (x86) 

#登录
cmd > mysql -uhighill -phighill -h127.0.0.1

#查询获取版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.27    |
+-----------+
1 row in set (0.01 sec)

#查询时 分号和逗号效果不大一样

mysql> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 5.5.27    |
+-----------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2012-01-20 14:15:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION(), NOW();
+-----------+---------------------+
| VERSION() | NOW()               |
+-----------+---------------------+
| 5.5.27    | 2012-01-20 14:15:42 |
+-----------+---------------------+
1 row in set (0.00 sec)

#查询当前用户

mysql> select user();
+--------------------+
| user()             |
+--------------------+
| highill@localhost |
+--------------------+
1 row in set (0.00 sec)

#查看默认字符集

mysql> show variables like 'character%';

| character_set_client     | utf8           |
| character_set_connection | utf8           |
| character_set_database   | utf8           |
| character_set_filesystem | binary           |
| character_set_results    | utf8           |
| character_set_server     | utf8           |
| character_set_system     | utf8           |
| character_sets_dir       | C:\Program Files (x86)\MySQL\MySQL Server 5.5\share\charsets\ |

#修改字符集
最简单的修改方法，就是修改mysql的my.ini文件中的字符集键值，如    

default-character-set = utf8
character_set_server = utf8
还有一种修改字符集的方法，就是使用mysql的命令
mysql> set character_set_client = utf8;
mysql> set character_set_connection = utf8;

3.数据库操作
#新建用户 并授权

mysql> grant all privileges on *.* to 'highill'@'127.0.0.1' identified by 'highill' with grant option;

#查看数据库
mysql> show databases;

#新建数据库

mysql> create database iteyetest;

#查看数据库

mysql> show create database iteyetest;

#变更数据库字符集

mysql> alter database iteyetest character set utf8;

#使用数据库

mysql> use iteyetest;

4.表操作
#查看表

mysql> show tables;

#创建表

mysql> create table testuser(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> sex char(1),
    -> birth DATE);
Query OK, 0 rows affected (0.11 sec)

#查看表

mysql> show create table testuser;

 testuser | CREATE TABLE `testuser` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

#修改表的存储引擎
#使用MYISAM引擎，物理上有三个存储文件(user.frm)

mysql> alter table testuser engine=myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#使用INNODB存储引擎，物理上有一个存储文件(user.frm,user.MYD,user.MYI)

mysql> alter table testuser engine=innodb;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#查看引擎 可以查看支持的引擎(不同版本可能不大一样)

mysql> show engines;

#修改表的字符集

mysql> alter table testuser charset = utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#修改表结构

#增加列 
alter table table_name add field_name field_type
mysql> alter table testuser add other varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#变更列 修改列的类型、名称 
#alter table table_name change field_name_old field_name_new field_type_new
mysql> alter table testuser change other other char(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除列 alter table table_name drop column field_name
mysql> alter table testuser drop column other;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#重命名表alter table table_name_old rename table_name_new;
mysql> alter table testuser rename user2;
Query OK, 0 rows affected (0.04 sec)
mysql> alter table user2 rename testuser;
Query OK, 0 rows affected (0.01 sec)

5.备份与恢复
备份

cmd > mysqldump -uhighill -phighill iteyetest>d:\iteyetest-2012.sql

还原

cmd > mysql -uhighill -phighill
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.27 MySQL Community Server (GPL)
mysql> use iteyetest;
Database changed
mysql> source d:\iteyetest-2012.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

其他介绍(尚未验证)
备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename