*建立並備份資料表

1. 本節非授課範圍，但練習時必須匯入一資料庫備份檔，此節為此檔產生方式，學生也可自行練習產生。先登入資料庫。

   [root@kvm5 ~]# mysql -uroot -pqweqwe
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 14
   Server version: 5.5.41-MariaDB MariaDB Server
   
   Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   

2. 產生資料庫 Contacts。

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

3. 使用資料庫 Contacts。

   MariaDB [(none)]> use Contacts;
   Database changed
   

4. 在資料庫 Contacts 中建立資料表 staff。

   MariaDB [Contacts]> create table staff
       -> id int not null auto_increment,
       -> firstname varchar(40) not null,
       -> lastname varchar(40) not null,
       -> password varchar(40) not null,
       -> pid int not null,
       ->  primary key (id));
   Query OK, 0 rows affected (0.08 sec)
   

5. 查看資料表 staff 的欄位。

   MariaDB [Contacts]> show columns from staff;
   +-----------+-------------+------+-----+---------+----------------+
   | Field     | Type        | Null | Key | Default | Extra          |
   +-----------+-------------+------+-----+---------+----------------+
   | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
   | firstname | varchar(40) | NO   |     | NULL    |                |
   | lastname  | varchar(40) | NO   |     | NULL    |                |
   | password  | varchar(40) | NO   |     | NULL    |                |
   | pid       | int(11)     | NO   |     | NULL    |                |
   +-----------+-------------+------+-----+---------+----------------+
   5 rows in set (0.00 sec)
   

6. 查看資料庫 Contacts 已存在資料表 staff。

   MariaDB [Contacts]> show tables;
   +--------------------+
   | Tables_in_Contacts |
   +--------------------+
   | staff              |
   +--------------------+
   1 row in set (0.00 sec)
   

7. 資料表 staff 中建立六筆資料。

   MariaDB [Contacts]> insert into staff (id,firstname,lastname,password,pid)
       -> values
       -> ('Linda','Lin','123qwe',3),
       -> ('Linda','Wang','123qwe',3),
       -> ('Rita','Lin','123123',2),
       -> ('Rita','Wang','qwe123',1),
       -> ('Peter','Lin','123123',2),
       ->  ('Peter','Wang','qwe123',2);
   Query OK, 6 rows affected (0.04 sec)
   Records: 6  Duplicates: 0  Warnings: 0
   

8. 查看資料表 staff 中的資料。

   MariaDB [Contacts]> select * from staff;
   +----+-----------+----------+----------+-----+
   | id | firstname | lastname | password | pid |
   +----+-----------+----------+----------+-----+
   |  1 | Linda     | Lin      | 123qwe   |   3 |
   |  2 | Linda     | Wang     | 123qwe   |   3 |
   |  3 | Rita      | Lin      | 123123   |   2 |
   |  4 | Rita      | Wang     | qwe123   |   1 |
   |  5 | Peter     | Lin      | 123123   |   2 |
   |  6 | Peter     | Wang     | qwe123   |   2 |
   +----+-----------+----------+----------+-----+
   6 rows in set (0.00 sec)
   

9. 退出資料庫。

   MariaDB [Contacts]> exit
   Bye
   

10. 備份資料表 staff 為 staff.mdb。

    [root@kvm5 ~]# mysqldump -uroot -pqweqwe Contacts staff > staff.mdb
    

11. 刪除資料庫 Contacts，MariaDB 跟 Linux 一樣，資料庫名稱要分大小寫

    [root@kvm5 ~]# mysqladmin -uroot -pqweqwe drop contacts
    Dropping the database is potentially a very bad thing to do.
    Any data stored in the database will be destroyed.
    
    Do you really want to drop the 'contacts' database [y/N] y
    mysqladmin: DROP DATABASE contacts failed;
    error: 'Can't drop database 'contacts'; database doesn't exist'
    [root@kvm5 ~]# mysqladmin -uroot -pqweqwe drop Contacts
    Dropping the database is potentially a very bad thing to do.
    Any data stored in the database will be destroyed.
    
    Do you really want to drop the 'Contacts' database [y/N] y
    Database "Contacts" dropped