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

Source: Internet
Author: User
Tags web database

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.