用 yum 或者 apt 安裝包管理器 MySQL 資料庫。
# yum install mysql mysql-client mysql-server (on `Yum` based Systems)
# apt-get install mysql mysql-client mysql-server (on `Apt` based Systems)
啟動 MySQL啟動 MySQL 資料庫服務
# service mysqld start
or
# service mysql start安裝好一個 MySQL 資料庫,啟動後,下一步就可以進入MySQL資料庫了
# mysql -u root -p
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 195
Server version: 31-0+wheezy1 (Debian)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
mysql>
創建資料庫mysql> create database rumenz ;
Query OK, 1 row affected (02 sec)
mysql>Note: Query OK表示資料庫已創建。
mysql> show databases;
++
| Database |
++
| information_schema |
| mysql |
| performance_schema |
| `rumenz` |
| test |
++
9 rows in set (00 sec)
mysql>
選擇資料庫現在你需要選擇要處理的資料庫。
mysql> use rumenz;
Database changed
mysql>
在 MySQL 中創建表在這裡，我們將創建一個表test_table三個欄位為：
mysql> CREATE TABLE test_table (
-> id Int(3),
-> first_name Varchar (15),
-> email Varchar(20)
-> );
Query OK, 0 rows affected (08 sec)
mysql>Note: 上面的查詢 OK這意味著表的創建沒有任何錯誤。要查看該表，請運行以下查詢。
mysql> show tables;
+----+
| Tables_in_rumenz |
+----+
| test_table |
+----+
1 row in set (00 sec)
mysql>mysql> show columns from test_table;
+--+---+-++----+--+
| Field | Type | Null | Key | Default | Extra |
+--+---+-++----+--+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
+--+---+-++----+--+
3 rows in set (00 sec)
mysql>
varchar是具有定義的可變長度的字符。Type 後面的值是它可以存儲數據的欄位長度。現在我們需要添加一列 last_name在first_name列之後。
mysql> ALTER TABLE test_table ADD last_name varchar (20) AFTER first_name;
Query OK, 0 rows affected (16 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from test_table;
+--+---+-++----+--+
| Field | Type | Null | Key | Default | Extra |
+--+---+-++----+--+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
+--+---+-++----+--+
4 rows in set (00 sec)
mysql>
在 MySQL 中添加列現在我們將在右側添加一列，比如一列 country 在email 的右邊 .
mysql> ALTER TABLE test_table ADD country varchar (15) AFTER email;
Query OK, 0 rows affected (16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>驗證
mysql> show columns from test_table;
+--+---+-++----+--+
| Field | Type | Null | Key | Default | Extra |
+--+---+-++----+--+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| country | varchar(15) | YES | | NULL | |
+--+---+-++----+--+
5 rows in set (00 sec)
mysql>
在欄位中插入值mysql> INSERT INTO test_table VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' , 'India' );
Query OK, 1 row affected (02 sec)
mysql>批量插入值
mysql> INSERT INTO test_table VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' , 'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' , 'rumenz' , '[dot]com' , 'rumenz@gmail.com' , 'India' );
Query OK, 3 rows affected (05 sec)
Records: 3 Duplicates: 0 Warnings: 0查詢結果。
mysql> select * from test_table;
+-+--+-+----+----+
| id | first_name | last_name | email | country |
+-+--+-+----+----+
| 1 | Ravi | Saive | raivsaive@xyz.com | India |
| 2 | Narad | Shrestha | narad@xyz.com | India |
| 3 | user | singh | user@xyz.com | Aus |
| 4 | rumenz | [dot]com | rumenz@gmail.com | India |
+-+--+-+----+----+
4 rows in set (00 sec)
mysql>
刪除記錄假設上面輸出中的第三條數據無效，我們需要刪除第三條數據。
mysql> DELETE FROM test_table WHERE id = 3;
Query OK, 1 row affected (02 sec)查詢結果
mysql> select * from test_table;
+-+--+-+----+----+
| id | first_name | last_name | email | country |
+-+--+-+----+----+
| 1 | Ravi | Saive | raivsaive@xyz.com | India |
| 2 | Narad | Shrestha | narad@xyz.com | India |
| 4 | rumenz | [dot]com | rumenz@gmail.com | India |
+-+--+-+----+----+
3 rows in set (00 sec)
更新欄位中的值需要編輯 id (=4)。
mysql> UPDATE test_table SET id = 3 WHERE first_name = 'rumenz';
Query OK, 1 row affected (02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>查詢
mysql> UPDATE test_table SET id = 3 WHERE first_name = 'rumenz';
Query OK, 1 row affected (02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>mysql> UPDATE test_table SET id = 6 WHERE first_name = 'rumenz'AND last_name = '[dot]com';
Query OK, 1 row affected (03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
刪除 MySQL 中的列刪除country欄位
mysql> ALTER TABLE test_table drop country;
Query OK, 3 rows affected (15 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>查詢
mysql> select * from test_table;
+-+--+-+----+
| id | first_name | last_name | email |
+-+--+-+----+
| 1 | Ravi | Saive | raivsaive@xyz.com |
| 2 | Narad | Shrestha | narad@xyz.com |
| 6 | rumenz | [dot]com | rumenz@gmail.com |
+-+--+-+----+
3 rows in set (00 sec)
mysql>
在 MySQL 修改表名將test_table改成rumenz_table.
mysql> RENAME TABLE test_table TO rumenz_table;
Query OK, 0 rows affected (03 sec)
mysql>
列出所有表查看當前資料庫下的所有表。
mysql> show tables;
+----+
| Tables_in_rumenz |
+----+
| rumenz_table |
+----+
1 row in set (00 sec)
mysql>該表已重命名。現在備份以上內容MySQL資料庫
# mysqldump -u root -p rumenz > rumenz.sql
check the dumped file on your desktop which would have contents something like
-- MySQL dump 13 Distrib 31, for debian-linux-gnu (i686) --
-- Server version 31-0+wheezy1 --
Dump completed on 2013-09-02 12:55:37維護MySQL 資料庫的備份始終是一個好主意。恢復備份MySQL Data 又是一行簡單的代碼，你需要在終端提示符下運行。
刪除資料庫mysql> drop database rumenz;
Query OK, 1 row affected (02 sec)檢查資料庫伺服器上的資料庫rumenz。
mysql> show databases;
++
| Database |
++
| information_schema |
| my_database |
| mysql |
| performance_schema |
| phpmyadmin |
| sisso |
| test |
++
7 rows in set (00 sec)
mysql>不用擔心，我們有備份。
恢復資料庫要恢復丟失的資料庫，請運行以下命令。
# mysql -u root -p rumenz < rumenz.sql
Enter password:
ERROR 1049 (42000): Unknown database 'rumenz'還沒有創建資料庫rumenz. 因此，轉到你的 mysql 提示符並創建一個資料庫 rumenz。
mysql> create database rumenz;
Query OK, 1 row affected (00 sec)
mysql>現在是時候在你的 shell 提示符下運行 restore 命令了
# mysql -u root -p rumenz < rumenz.sql
Enter password:驗證你的資料庫。
mysql> show databases;
++
| Database |
++
| information_schema |
| mysql |
| performance_schema |
| rumenz |
| test |
++
8 rows in set (00 sec)驗證資料庫的內容。
mysql> show tables from rumenz;
+----+
| Tables_in_rumenz |
+----+
| rumenz_table |
+----+
1 row in set (00 sec)
mysql>驗證你恢復的表的內容。
mysql> select * from rumenz_table;
+-+--+-+----+
| id | first_name | last_name | email |
+-+--+-+----+
| 1 | Ravi | Saive | raivsaive@xyz.com |
| 2 | Narad | Shrestha | narad@xyz.com |
| 6 | rumenz | [dot]com | rumenz@gmail.com |
+-+--+-+----+
3 rows in set (00 sec)