Create a Web database and introduce a. sql file with the XAMPP MySQL shell

Chapter 08:creating Your Web Database
Destination:set UO a MySQL database for use on a Web site
Contents:
[1] Creating a database (creating databases)
[2] Users and privileges (users and permissions)
[3] Introduction to the privilege system (Introduction to the permission systems)
[4] Creating database tables (create a table of databases)
[5] column types in MySQL (MySQL column type)

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.
<===== Welcome to the MARIADB display, all commands need to be ";" or "\g" end
Your MariaDB Connection ID is 4
<===== you have connected with mariadb 4 times (today)
Server version:10.1.13-mariadb mariadb.org Binary distribution
<===== Server version (MySQL)
Copyright (c), Oracle, MariaDB Corporation Ab and others.
<===== Copyright
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
<===== input "help;" or "\h" for more help; enter "\c" to clear the current statement

[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 is 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:
The user named Fred, the user with password mnb123, uses all of the permissions of the database books, and allows him to grant these permissions to others (note: This is different from the book, you must first select the database to give permission, here first doubt)
In 中文版:
This grants all privileges on database books to a user called Fred with the password mnb123, and allows him-pass on tho SE 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 "Demo". * to ' Fred ' @ '% ' with GRANT OPTION |
| GRANT all privileges the ' books '. * to ' Fred ' @ '% ' with GRANT OPTION |
+-----------------------------------------------------------------------------------------------------+
3 Rows in Set (0.00 sec)

Some Privileges:
<privileges> is a comma-delimited list of MySQL user rights that you want to give. The permissions you can specify can be divided into three types:

Database/data Table/Data column permissions:

Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
Insert: Adds a table record.
Select: Displays/searches the table's records.
Update: Modifies a record that already exists in the table.

To manage MySQL user rights globally:

File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.

Special permissions:

All: Allow to do anything (as root).
USAGE: Only allow login-nothing else is allowed.

Chances is 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 does this.

If we decide that Sally have been up to something on 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 ' @ '% '
       & nbsp;               |
+-------------------------------------------------------------------------------
----------------------- +
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: (delete the user just created from the books databases)
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 had already masterred how to set up a user and give him some
privileges. Then we can move to learn what 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 file:

CREATE TABLE Customers (CustomerID int unsigned NOT NULL auto_increment primary key,  name Char (a) not NULL,  Addre SS Char (+) NOT NULL, City char (+) not  null); CREATE TABLE orders (orderid int unsigned NOT NULL auto_increment prima  Ry Key,  customerid int unsigned NOT NULL,  amount float (6,2),  date date is not null); CREATE Table Books (  ISBN char (+) NOT null primary key,   author char (+),   title char (+), price   float (4,2)); CREATE TABLE Order_items (orderid int unsigned NOT NULL,  ISBN char (+) not NULL,  quantity tinyint unsigned,  primary key (OrderID, I SBN)); Create Table Book_reviews (  ISBN char () NOT NULL primary key,  review text);

　　

Create a Web database and introduce a. sql file with the XAMPP MySQL shell