建立Web資料庫，用XAMPP的MySQL shell引入 .sql 檔案

標籤：

Chapter 08 : Creating  Your Web Database
Destination : set uo a MySQL database for use on a Web site
Contents :
[1] Creating a database (建立資料庫)
[2] Users and Privileges (使用者和許可權)
[3] Introduction to the privilege system (許可權系統的介紹)
[4] Creating database tables (建立資料庫表)
[5] Column types in MySQL (MySQl列類型)

For example : create a database for Book-O-Rama application
[enter MySQL]
# mysql -u root -p   <===== This is the order which can enter MySQL
Enter password: ************* <===== If you have password, input it
Welcome to the MariaDB monitor.  Commands end with ; or \g.
<===== 歡迎使用MariaDB顯示器，所有命令需要以“;”或“\g”結尾
Your MariaDB connection id is 4
<===== 你已經與MariaDB串連了4次（今天）
Server version: 10.1.13-MariaDB mariadb.org binary distribution
<===== 伺服器版本（MySQL）
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
<===== 著作權
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
<===== 輸入“help;”或者“\h”尋求更多協助；輸入“\c”清除現在的語句

[create a database]
MariaDB [(none)]> create database books;  <===== This is the order which can create a database
Query OK, 1 row affected (0.00 sec)  <==== This sentence stands that you are successful!

[create a user and give him privileges]
The order structure :
grant <privileges> [columns]
on <item>
to user_name [identified by ‘password‘]
[with grant option]

For example :
MariaDB [(none)]> use books;
Database changed
MariaDB [books]> grant all
    -> on *
    -> to fred identified by ‘mnb123‘
    -> with grant option;
Query OK, 0 rows affected (0.00 sec)
Translate the example :
授予了使用者名稱為Fred，密碼為mnb123的使用者使用資料庫books的所有許可權，並允許他向其他人授予這些許可權（注意：這裡與書上不同，必須先選定資料庫才能賦予許可權，這裡先存疑）
In English :
This grants all privileges on database books to a user called Fred with the password mnb123, and allows him to pass on those privileges.

Then you can check user‘s privileges :
MariaDB [books]> show grants for fred;
+-----------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘fred‘@‘%‘ IDENTIFIED BY PASSWORD ‘*05CB0EB8BA44ECA85BA32D90E6D2E24EB614ADF0‘ |
| GRANT ALL PRIVILEGES ON `demo`.* TO ‘fred‘@‘%‘ WITH GRANT OPTION                                    |
| GRANT ALL PRIVILEGES ON `books`.* TO ‘fred‘@‘%‘ WITH GRANT OPTION                                   |
+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Some privileges :
<privileges>是一個用逗號分隔的你想要賦予的MySQL使用者權限的列表。你可以指定的許可權可以分為三種類型：

資料庫/資料表/資料列許可權：

Alter: 修改已存在的資料表(例如增加/刪除列)和索引。
Create: 建立新的資料庫或資料表。
Delete: 刪除表的記錄。
Drop: 刪除資料表或資料庫。
INDEX: 建立或刪除索引。
Insert: 增加表的記錄。
Select: 顯示/搜尋表的記錄。
Update: 修改表中已存在的記錄。

全域管理MySQL使用者權限：

file: 在MySQL伺服器上讀寫檔案。
PROCESS: 顯示或殺死屬於其它使用者的服務線程。
RELOAD: 重載存取控制表，重新整理日誌等。
SHUTDOWN: 關閉MySQL服務。

特別的許可權：

ALL: 允許做任何事(和root一樣)。
USAGE: 只允許登入--其它什麼也不允許做。

Chances are you don‘t want this user in your system, so go ahead and revoke him:
MariaDB [books]> revoke all
    -> on *
    -> from fred
    -> ;
Query OK, 0 rows affected (0.00 sec)

Now let‘s set up a regular usesr with no privileges:
MariaDB [books]> grant usage
    -> on books.*
    -> to Sally identified by ‘mnb123‘;
Query OK, 0 rows affected (0.00 sec)

And we check privileges on Sally;
MariaDB [books]> show grants for Sally;
+------------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘Sally‘@‘%‘ IDENTIFIED BY PASSWORD ‘*05CB0EB8BA44ECA85BA32D90E6D2E24EB614ADF0‘ |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

After talking with Sally, we can give her the appropriate privileges:
MariaDB [books]> grant select, insert, update, delete, index, alter, create, drop
    -> on books.*
    -> to Sally;
Query OK, 0 rows affected (0.00 sec)

Then check Sally‘s privileges again:
MariaDB [books]> show grants for Sally;
+-------------------------------------------------------------------------------
-----------------------+
| Grants for [email protected]%
                       |
+-------------------------------------------------------------------------------
-----------------------+
| GRANT USAGE ON *.* TO ‘Sally‘@‘%‘ IDENTIFIED BY PASSWORD ‘*05CB0EB8BA44ECA85BA
32D90E6D2E24EB614ADF0‘ |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `books`.*
TO ‘Sally‘@‘%‘         |
+-------------------------------------------------------------------------------
-----------------------+
2 rows in set (0.00 sec)

We are wonderful!

Attention : we don‘t need to specify Sally‘s password in order to do this.

If we decide that Sally has been up to something in the database, we might decide to reduce her privileges:
MariaDB [books]> revoke alter, create, drop
    -> on books.*
    -> from Sally;
Query OK, 0 rows affected (0.00 sec)

Then we check it:
MariaDB [books]> show grants for Sally;
+-------------------------------------------------------------------------------
-----------------------+
| Grants for [email protected]%
                       |
+-------------------------------------------------------------------------------
-----------------------+
| GRANT USAGE ON *.* TO ‘Sally‘@‘%‘ IDENTIFIED BY PASSWORD ‘*05CB0EB8BA44ECA85BA
32D90E6D2E24EB614ADF0‘ |
| GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON `books`.* TO ‘Sally‘@‘%‘
                       |
+-------------------------------------------------------------------------------
-----------------------+
2 rows in set (0.00 sec)

And later, when she doesn‘t need to use the database any more, we can revoke her privileges altogther:
MariaDB [books]> revoke all
    -> on books.*
    -> from Sally;
Query OK, 0 rows affected (0.00 sec)

Drop users from our database books: (從books資料庫中刪掉剛剛建立的使用者)
MariaDB [books]> drop user [email protected]‘%‘;
Query OK, 0 rows affected (0.13 sec)

MariaDB [books]> drop user [email protected]‘%‘;
Query OK, 0 rows affected (0.00 sec)

Up to now, we have already masterred how to set up a user and give him some
privileges. Then we can move to learn how to set up a user for the Web.

At the very beginning, we need to set up a user for our PHP scripts to connect to
MySQL and comply with the privilege of least principle.

We can import a sql file to create tables for database books:
let‘s put the sql file to the c:\xampp
then we import it from the MySQL shell:

MariaDB [books]> source bookorama.sql;
Query OK, 0 rows affected (0.34 sec)

Query OK, 0 rows affected (0.27 sec)

Query OK, 0 rows affected (0.22 sec)

Query OK, 0 rows affected (0.21 sec)

Query OK, 0 rows affected (0.20 sec)

Then we check it whether the books database includes all tables from bookorama.sql:
MariaDB [books]> show tables;
+-----------------+
| Tables_in_books |
+-----------------+
| book_reviews    |
| books           |
| customers       |
| order_items     |
| orders          |
+-----------------+
5 rows in set (0.00 sec)

bookorama.sql 檔案：

create table customers( customerid int unsigned not null auto_increment primary key,  name char(50) not null,  address char(100) not null,  city char(30) not null);create table orders( orderid int unsigned not null auto_increment primary key,  customerid int unsigned not null,  amount float(6,2),  date date not null);create table books(  isbn char(13) not null primary key,   author char(50),   title char(100),   price float(4,2));create table order_items( orderid int unsigned not null,  isbn char(13) not null,  quantity tinyint unsigned,  primary key (orderid, isbn));create table book_reviews(  isbn char(13) not null primary key,  review text);

　　

建立Web資料庫，用XAMPP的MySQL shell引入 .sql 檔案