使用文件檔方式建立一個測試用的資料庫,並建立和指定使用者


使用 root 身份進入 mysql終端機中看一下目前資料庫列表

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

離開 mysql終端機

建立一個 phpmysql.sql 文檔,我們要把指令全部寫在這裡面
文件副檔名若為 phpmysql.txt 文字檔也是可以的

vim phpmysql.sql

首先建立一個資料庫phpmysql,要指定使用的編碼方式

CREATE DATABASE `phpmysql` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

使用這個資料庫

USE `phpmysql`;

在這個資料庫中建立一張資料表,並規劃欄位,要指定使用的編碼方式

create table `users`(
sn integer NOT NULL auto_increment
primary key,
name char(20) COLLATE utf8_unicode_ci DEFAULT NULL,
mail char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
home char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
message char(50) COLLATE utf8_unicode_ci DEFAULT NULL
);

建立一個使用者 ben,密碼為1234,並指定他擁有操作 phpmysql 資料庫裡所有資料表的權限(phpmysql.*)

GRANT all ON phpmysql.* TO ben@'localhost' IDENTIFIED BY '1234';

完整的 phpmysql.sql 文檔

CREATE DATABASE `phpmysql` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `phpmysql`;
create table `users`(
sn integer NOT NULL auto_increment
primary key,
name char(20) COLLATE utf8_unicode_ci DEFAULT NULL,
mail char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
home char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
message char(50) COLLATE utf8_unicode_ci DEFAULT NULL
);
GRANT all ON phpmysql.* TO ben@'localhost' IDENTIFIED BY '1234';

確定文檔沒有問題後,在終端機下達以下指令,匯入這個文檔

mysql -u root -p < phpmysql.sql
Enter password:

現在可以使用剛剛建立的帳號 ben 進入 mysql終端機,查看資料庫和裡面的資料表

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

mysql> use phpmysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_phpmysql |
+--------------------+
| users              |
+--------------------+
1 row in set (0.00 sec)

mysql> describe users;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| sn       | int(11)  | NO   | PRI | NULL    | auto_increment |
| name     | char(20) | YES  |     | NULL    |                |
| mail     | char(50) | YES  |     | NULL    |                |
| home     | char(50) | YES  |     | NULL    |                |
| message  | char(50) | YES  |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

如果要刪除這個資料庫的話

使用 root 身份進入 mysql終端機,下達指令

drop database phpmysql;

例如:

mysql> drop database phpmysql;
Query OK, 1 row affected (0.00 sec)

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

刪除剛剛建立的帳號 ben

主要語法是

mysql> delete from user where User = 'ben';
mysql> flush privileges;

完整的流程如下:

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)

mysql> select User from user;
+------+
| User |
+------+
| root |
|      |
| root |
|      |
| ben  |
| root |
+------+
6 rows in set (0.00 sec)

mysql> delete from user where User = 'ben';
Query OK, 1 row affected (0.00 sec)

mysql> select User from user;
+------+
| User |
+------+
| root |
|      |
| root |
|      |
| root |
+------+
5 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

要查看某資料表欄位規劃的話,使用下列命令

mysql> describe user;