Chapter 09 Creating a Web database and data type table

Yesterday, we learned how to create DATABASE, let's open the MySQL shell and check databases which we set up yesterday.

MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Books |
| Information_schema |
| MySQL |
| Performance_schema |
| phpMyAdmin |
| Test |
+--------------------+
6 rows in Set (0.00 sec)

Okay, then we change into database books.

MariaDB [(None)]> use books;
Database changed
MariaDB [books]>

Then we can check there is how many tables in the This database:

MariaDB [books]> Show tables;
+-----------------+
| Tables_in_books |
+-----------------+
| Book_reviews |
| Books |
| Customers |
| Order_items |
| Orders |
+-----------------+
5 rows in Set (0.00 sec)

We can see there is five tables in the database, then we learn some key words in a table first:
[NOT NULL]
It must is hava a value for all the rows in this attribute.
If it isn ' t being specified, the field can be blank (NULL).

[Auto_increment]
A Special MySQL Festure You can use the on Interger columns.
If We leave this field blank when inserting rows into the table, MySQL wil auto-matically generate a unique identifier Val Ue.
(That is, when we insert an empty null field in the table, Field,mysql automatically generates a unique identifier value for it)
The value would be a greater than the maximum value in the column already.
One table can only use auto_increment once, and columns this specify auto_increment must be indexed.
(that is, it must be the primary key)

[PRIMARY KEY]
Primary key after a column name specifies that this column is the Primary key for the table and it would be unique.
We can use "PRIMARY key (Filed1, Field2)" To declare the primary key of this table consists of the columns together.

[INT undesigned]
undesigned after an integer type means the it can only hava a zero or positive value. (Natural number)

Then we should understand the column types:
MariaDB [books]> desc customers;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| CustomerID | Int (Ten) unsigned | NO | PRI | NULL | auto_increment |
| name | CHAR (50) |     NO | |                NULL | |
| Address | CHAR (100) |     NO | |                NULL | |
| City | char (30) |     NO | |                NULL | |
+------------+------------------+------+-----+---------+----------------+
4 rows in Set (0.14 sec)

Look Customers table, we hava four columns as specified in our schema. The first one, CustomerID, is the primary key. We ' ve also taken advantage of auto_increment facility so this MySQL can manage these for us--it's one less thing to Worr Y about.
Attention:the range of the type "tinyint unsigned" is from 0 RO 255.

Last, let us see some tables about data types.

MySQL identifier

Integer data type

Floating-point data types

Date and Time type

Timestamp Display Type

General String Type

TEXT and BLOB types

Set and enum types

Chapter 09 Creating a Web database and data type table