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';

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-616739/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-616739/