数据库上手避坑之--创建数据库和表

　　在插入和数据前，需要确定插入和操作的表。而表需要数据库存储，数据库就是存储数据表的容器。建表时需要考虑以下问题：

1、用哪个数据库存储，要建多少个表，需要多少个表名;

2、每个表中要见多少列，以及需要多少个列名

3、每一列存储的数据类型，简单的四种类型：数字INT、少量字符VARCHAR、大量文字TEXT或二进制文字，日期和时间信息

一、创建数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student_info       |
| test               |
+--------------------+
5 rows in set (0.065 sec)

MariaDB [(none)]> create database rookery;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rookery            |
| student_info       |
| test               |
+--------------------+
6 rows in set (0.001 sec)

MariaDB [(none)]> drop database rookery;
Query OK, 0 rows affected (0.120 sec)

MariaDB [(none)]> create schema rookery;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rookery            |
| student_info       |
| test               |
+--------------------+
6 rows in set (0.001 sec)

MariaDB [(none)]> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rookery            |
| student_info       |
| test               |
+--------------------+
6 rows in set (0.002 sec)

二、数据表

MariaDB [(none)]> use rookery;
Database changed
MariaDB [rookery]> show tables;
Empty set (0.000 sec)

MariaDB [rookery]> create table birds(birdId int auto_increment primary key,
    -> scientificName varchar(255) unique,
    -> commonName varchar(50),
    -> familyId int,
    -> description text);
Query OK, 0 rows affected (0.165 sec)

MariaDB [rookery]> describe birds;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| birdId         | int(11)      | NO   | PRI | NULL    | auto_increment |
| scientificName | varchar(255) | YES  | UNI | NULL    |                |
| commonName     | varchar(50)  | YES  |     | NULL    |                |
| familyId       | int(11)      | YES  |     | NULL    |                |
| description    | text         | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)

MariaDB [rookery]> insert into birds(scientificName, commonName)
    -> values('Charadrius vociferus', 'Killdeer'),
    -> ('Gavia immer', 'Great Northern Loon'),
    -> ('Aix sponsa', 'Wood Duck'),
    -> ('Chordeiles minor', 'Common Nighthawk'),
    -> ('Sitta carolinensis', 'White-breasted Nuthatch'),
    -> ('Apteryx mantelli', 'North Island Brown Kiwi');
Query OK, 6 rows affected (0.083 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [rookery]> select * from birds;
+--------+----------------------+-------------------------+----------+-------------+
| birdId | scientificName       | commonName              | familyId | description |
+--------+----------------------+-------------------------+----------+-------------+
|      1 | Charadrius vociferus | Killdeer                |     NULL | NULL        |
|      2 | Gavia immer          | Great Northern Loon     |     NULL | NULL        |
|      3 | Aix sponsa           | Wood Duck               |     NULL | NULL        |
|      4 | Chordeiles minor     | Common Nighthawk        |     NULL | NULL        |
|      5 | Sitta carolinensis   | White-breasted Nuthatch |     NULL | NULL        |
|      6 | Apteryx mantelli     | North Island Brown Kiwi |     NULL | NULL        |
+--------+----------------------+-------------------------+----------+-------------+
6 rows in set (0.001 sec)

MariaDB [rookery]> create schema birdWatchers;
Query OK, 1 row affected (0.001 sec)

MariaDB [rookery]> show schemas;
+--------------------+
| Database           |
+--------------------+
| birdWatchers       |
| information_schema |
| mysql              |
| performance_schema |
| rookery            |
| student_info       |
| test               |
+--------------------+
7 rows in set (0.001 sec)

MariaDB [rookery]> create table birdWatchers.humans(
    -> humanId int auto_increment primary key,
    -> formalTitle varchar(25),
    -> nameFirst varchar(25),
    -> nameLast varchar(25),
    -> emailAddress varchar(255));
Query OK, 0 rows affected (0.154 sec)

MariaDB [rookery]> insert into birdWatchers.humans(
    -> formalTitle, nameFirst, nameLast, emailAddress) values
    -> ('Mr. ', 'Russell', 'Dyer', 'russell@mysqlresources.com'),
    -> ('Mr. ', 'Richard', 'Stringer', 'richard@mysqlresources.com'),
    -> ('Ms. ', 'Rusty', 'Osborne', 'rusty@mysqlresources.com'),
    -> ('ms. ', 'Lexi', 'Hollar', 'alexandra@mysqlresources.com');
Query OK, 4 rows affected (0.031 sec)
Records: 4  Duplicates: 0  Warnings: 0
MariaDB [rookery]> describe birds;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| birdId         | int(11)      | NO   | PRI | NULL    | auto_increment |
| scientificName | varchar(255) | YES  | UNI | NULL    |                |
| commonName     | varchar(50)  | YES  |     | NULL    |                |
| familyId       | int(11)      | YES  |     | NULL    |                |
| description    | text         | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

MariaDB [rookery]> show create table  birds \G;
*************************** 1. row ***************************
       Table: birds
Create Table: CREATE TABLE `birds` (
  `birdId` int(11) NOT NULL AUTO_INCREMENT,
  `scientificName` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `commonName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `familyId` int(11) DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`birdId`),
  UNIQUE KEY `scientificName` (`scientificName`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.000 sec)

ERROR: No query specified

三、更高级理解表

MariaDB [rookery]> create table birdFamilies(
    -> familyId int auto_increment primary key,
    -> scientificName varchar(255) unique,
    -> briefDescription varchar(255));
Query OK, 0 rows affected (0.197 sec)

MariaDB [rookery]> create table birdOrders(
    -> orderId int auto_increment primary key,
    -> scientificName varchar(255) unique,
    -> briefDescription varchar(255),
    -> orderImage blob)
    -> default charset=utf8 collate=utf8_general_ci;
Query OK, 0 rows affected (0.177 sec)

MariaDB [rookery]>  show create table  birds \G;
*************************** 1. row ***************************
       Table: birds
Create Table: CREATE TABLE `birds` (
  `birdId` int(11) NOT NULL AUTO_INCREMENT,
  `scientificName` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `commonName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `familyId` int(11) DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`birdId`),
  UNIQUE KEY `scientificName` (`scientificName`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.000 sec)

ERROR: No query specified