xampp mysql 创建 数据库_创建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 [columns]

on

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 :

是一个用逗号分隔的你想要赋予的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

);