next up previous contents
Next: 建立用戶 Up: MariaDB 資料庫 Previous: 建立資料庫   Contents

*建立並備份資料表

  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
    



De-Yu Wang 2018-09-07