创建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 fred@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| 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 Sally@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| 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 Sally@%
                       |
+-------------------------------------------------------------------------------
-----------------------+
| 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 Sally@%
                       |
+-------------------------------------------------------------------------------
-----------------------+
| 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 fred@'%';
Query OK, 0 rows affected (0.13 sec)

MariaDB [books]> drop user Sally@'%';
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
);