Mysql資料庫一些簡單命令

2005年開發時曾經用過mysql資料庫，時隔4年，需要把一個mysql的資料匯入到一個新的mysql資料庫中。
下載一個windows版本的mysql5.0.27版本，預設安裝。（安裝時需要設定資料庫使用者root的密碼，否則為空）

1、進入mysql資料庫
C:\Documents and Settings\Administrator>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

2、檢視mysql中的資料庫名
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

3、建立一個資料庫名
mysql> create database rfws;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| rfws               |
| test               |
+--------------------+
4 rows in set (0.00 sec)

4、建立一個使用者
mysql> create user rfws identified by 'password';
Query OK, 0 rows affected (0.01 sec)

5、把rfws資料庫的select、insert、update、delete授權給rfws使用者
mysql> grant select,insert,update,delete on rfws.* to rfws;
Query OK, 0 rows affected (0.00 sec)

把所有的許可權賦予給rfws使用者，這樣該使用者可以自由的建立表等
mysql> grant all privileges on rfws.* to rfws;
Query OK, 0 rows affected (0.00 sec)

6、用剛建立的使用者進入資料庫
C:\Documents and Settings\Administrator>mysql -u rfws -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| rfws               |
+--------------------+
2 rows in set (0.00 sec)

7、使用剛建立的資料庫
mysql> use rfws;
Database changed
mysql>

8、把mysql的指令碼匯入到mysql資料庫中
mysql> source C:\WINDOWS\system32\sais_db1015.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

用命令匯入資料時，漢子現實為亂碼，但把指令碼複製後放在命令視窗插入資料庫時，則可以正常現實中文。

9、刪除一個資料庫
mysql> drop database rfws;
Query OK, 35 rows affected (0.06 sec)

10、檢視mysql字符集
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | latin1
     |
| character_set_connection | latin1
     |
| character_set_database   | latin1
     |
| character_set_filesystem | binary
     |
| character_set_results    | latin1
     |
| character_set_server     | latin1
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> show variable like 'collation_%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'varia
ble like 'collation_%'' at line 1
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

11、命令關閉mysql資料庫
C:\Documents and Settings\Administrator>net stop mysql
MySQL 服務正在停止..
MySQL 服務已成功停止。

12、命令啟動
C:\Documents and Settings\Administrator>net start mysql

MySQL 服務已經啟動成功。

13、建立一個資料庫時指定字符集
mysql>create database rfws default character set 'GBK';