4. mysql資料庫操縱常用命令

純愛楓若情發表於2018-01-12
MySql資料庫

顯示資料庫

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
7 rows in set (0.00 sec)

建立資料庫

//建立了一個名為menageries的資料庫
mysql> CREATE DATABASE menageries;
Query OK, 1 row affected (0.03 sec)

切換資料庫

//USE命令和QUIT命令相似，不需要以分號結尾，當然如果你加上也不會報錯
mysql> USE test
Database changed

建立表

//在menageries資料庫中建立一張名為寵物的關係表
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.42 sec)

顯示錶

mysql> SHOW TABLES;
+----------------------+
| Tables_in_menageries |
+----------------------+
| pet                  |
+----------------------+
1 row in set (0.00 sec)

顯示錶裡面的詳細資訊

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

插入資料

1.通過命令列的方式插入

mysql> INSERT INTO pet
    -> VALUES ('Fluffy', 'Harold', 'cat', 'f','1993-02-04', NULL);
Query OK, 1 row affected (0.13 sec)

2.通過匯入本地檔案的方式插入

//檔案內容，注意相鄰的資料之間用TAB隔開，空資料用\N符號表示
Claws   Gwen    cat m   1994-03-17  \N
Buffy   Harold  dog f   1989-05-13  \N
Fang    Benny   dog m   1990-08-27  \N
Bowser  Diane   dog m   1979-08-31  1995-07-29
Chirpy  Gwen    bird    F   1998-09-11  \N
Whistler    Gwen    bird    \N  1997-12-09
Slim    Benny   snake   m   1996-04-29  \N

//插入命令，注意Windows要加上下面一行命令，不然無法插入成功
mysql> LOAD DATA LOCAL INFILE 'E:/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';
Query OK, 7 rows affected, 1 warning (0.08 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 1

//瀏覽插入的資料
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | F    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

修改資料與刪除資料

刪除資料

//刪除掉pet表中的全部資料
mysql> DELETE FROM pet;
Query OK, 8 rows affected (0.07 sec)

//刪除後查詢是否正確刪除
mysql> SELECT * FROM pet;
Empty set (0.00 sec)

//查詢知，delete只是刪除了資料，並未刪除關係表的整體結構
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

修改資料

//將Fang的出生日期改為2000-08-27
mysql> UPDATE pet SET birth = '2000-08-27' WHERE name = 'Fang';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

//修改後表中的資料
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 2000-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | F    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

刪除掉值為NULL的行

//刪除掉性別未知的寵物的資訊，注意這裡的用法
mysql> DELETE FROM pet WHERE sex IS NULL;
Query OK, 1 row affected (0.08 sec)

//刪除後
mysql> SELECT * FROM pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang   | Benny  | dog     | m    | 2000-08-27 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen   | bird    | F    | 1998-09-11 | NULL       |
| Slim   | Benny  | snake   | m    | 1996-04-29 | NULL       |
+--------+--------+---------+------+------------+------------+
7 rows in set (0.00 sec)

相關文章