Chapter 09 创建Web数据库和数据类型表

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 are how many tables in 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 are five tables in this database, then we learn some key words in a table first:
[not null]
It must be hava a value for all the rows in this attribute.
If it isn't be specified, the field can be blank(NULL).

[auto_increment]
A special MySQL festure you can use on interger columns.
If we leave that field blank when inserting rows into the table, MySQL wil auto-matically generate a unique identifier value.
(即当我们在表中插入一个为空NULL的字段field，MySQL会自动为其产生一个唯一的标识符值)
The value will be one greater than the maximum value in the column already.
One table can only use auto_increment once, and columns that specify auto_increment must be indexed.
(即使用这个的一定是主键)

[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 two columns together.

[int undesigned]
Undesigned after an integer type means that it can only hava a zero or positive value. (自然数)

Then we should understand the column types:
MariaDB [books]> desc customers;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| customerid | int(10) 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 that MySQL can manage these for us —— it's one less thing to worry about.
Attention: the range of the type "tinyint unsigned" is from 0 ro 255.

Last, let us see some tables about data types.

MySQL标识符

 

 

 

整数数据类型

浮点数据类型

 

日期和时间类型

 

 

TIMESTAMP显示类型

 

常规字符串类型

 

TEXT 和 BLOB类型

 

SET和ENUM类型