資料庫mysql的常規操作

　　1. 什麼是資料庫？

　　　　資料庫(Database)是按照數據結構來組織、存儲和管理數據的建立在電腦存儲設備上的倉庫。

　　　　簡單來說是本身可視為電子化的文件櫃——存儲電子文件的處所，用戶可以對文件中的數據進行新增、截取、更新、刪除等操作。

　　　　嚴格來說，資料庫是長期儲存在電腦內、有組織的、可共用的數據集合。資料庫中的數據指的是以一定的數據模型組織、描述和儲存在一起、具有儘可能小的冗餘度、較高的數據獨立性和易擴展性的特點並可在一定範圍內為多個用戶共用。

　　2.  為什麼需要使用資料庫？

　　　　 假設這樣一個場景，需要存儲一所學校所有學生的姓名，性別，出生日期和各科成績，是用Word文檔來存，還是使用Excel表格？相信一個正常人都會選擇Excel表格；當然，使用Word文檔也能實現這一需求；使用Word文檔實現存儲這一需求當然沒有任何問題，但是如果某個學生來要查詢自己的學生，或者需要修改某個學生的成績，給這些學生按成績排名等這些需求，如果需要再擴展一下存儲內容，比如再存一下每個學生的聯繫方式，那使用Word就幾乎不可能實現了。同樣的，在程式中所需要存儲的各種信息會比上面的場景更加複雜，使用一種有結構，有規律的數據存儲方式就非常的有必要了。

　　3. 如何管理操作資料庫？

　　　　我們可以使用一種叫做資料庫管理系統(DBMS)的軟體來操作資料庫。

　　　　資料庫管理系統(Database Management System)是一種操縱和管理資料庫的大型軟體，用於建立、使用和維護資料庫，簡稱DBMS。它對資料庫進行統一的管理和控制，以保證資料庫的安全性和完整性。用戶通過DBMS訪問資料庫中的數據，資料庫管理員也通過dbms進行資料庫的維護工作。它可使多個應用程式和用戶用不同的方法在同時或不同時刻去建立，修改和詢問資料庫。大部分DBMS提供數據定義語言DDL（Data Definition Language）和數據操作語言DML（Data Manipulation Language），供用戶定義資料庫的模式結構與許可權約束，實現對數據的追加、刪除等操作。

　　　　常見的資料庫管理系統有：Oracle資料庫、DB2、MySQL、Informix、SQL Server等。

　　4. 如何使用資料庫管理系統(DBMS)？

　　　　一般我們使用SQL語言與DBMS交互來操作關係型資料庫（一般日常使用的MySQL、SQL Server、Oracle資料庫都是關係型資料庫）。

　　　　SQL語言在這些資料庫管理系統中基本通用。不能說完全通用是因為不同的資料庫系統在其實踐過程中都對SQL規範作了某些編改和擴充。（基本上能在一種資料庫管理系統中熟練的使用SQL，遷移到其他系統其實很簡單）

　　在本篇博客中，就詳細介紹一下如何在mysql中使用SQL語言實現常規操作。

　　首先介紹一下mysql中的數據類型（可以先略過，用到時再來查看）：

　　參考自（http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh）

　　MySQL有多種類型，大致可以分為三類：數值、日期/時間和字元串(字元)類型。

　　(1) 數值類型：

　　MySQL支持所有標準SQL數值數據類型。這些類型包括嚴格數值數據類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值數據類型(FLOAT、REAL和DOUBLE PRECISION)。關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

　　BIT數據類型保存位欄位值，並且支持MyISAM、MEMORY、InnoDB和BDB表。

　　作為SQL標準的擴展，MySQL也支持整數類型TINYINT、MEDIUMINT和BIGINT。下麵的表顯示了需要的每個整數類型的存儲和範圍。

 　　註：MySQL還支持選擇在該類型關鍵字後面的括弧內指定整數值的顯示寬度(例如，INT(4))。該可選顯示寬度規定用於顯示寬度小於指定的列寬度的值時從左側填滿寬度。

　　(2)日期/時間類型：

　　表示時間值的DATE和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。每個時間類型有一個有效值範圍和一個“零”值，當指定不合法的MySQL不能表示的值時使用“零”值。

　　(3)字元串類型：

　　字元串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

　　CHAR和VARCHAR類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

　　CHAR和VARCHAR類型聲明的長度表示你想要保存的最大字元數。例如，CHAR(30)可以占用30個字元。

　　CHAR列的長度固定為創建表時聲明的長度。長度可以為從0到255的任何值。當保存CHAR值時，在它們的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除掉。在存儲或檢索過程中不進行大小寫轉換。

　　VARCHAR列中的值為可變長字元串。長度可以指定為0到65,535之間的值。(VARCHAR的最大有效長度由最大行大小和使用的字元集確定。整體最大長度是65,532位元組）。

　　同CHAR對比，VARCHAR值保存時只保存需要的字元數，另加一個位元組來記錄長度(如果列聲明的長度超過255，則使用兩個位元組)。

　　註：BINARY和VARBINARY類類似於CHAR和VARCHAR，不同的是它們包含二進位字元串而不要非二進位字元串。也就是說，它們包含位元組字元串而不是字元字元串。這說明它們沒有字元集，並且排序和比較基於列值位元組的數值值。

　　　　BLOB是一個二進位大對象，可以容納可變數量的數據。有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。

　　　　有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和存儲需求。

例一（顯示一下當前系統中有哪些資料庫）：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

　　註1：使用"show databases;"命令可以顯示當前系統中有哪些資料庫。

　　註2: 在mysql中，sql語句的結束必須使用";"。

　　註3: SQL語言不區分大小寫。

例二（進入某個資料庫並顯示這個資料庫中都有哪些表）：

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A              |
| B              |
| cats           |
| users          |
+----------------+
4 rows in set (0.00 sec)

　　註："use test;"命令，進入了test資料庫；"show tables;"命令，顯示了test資料庫中的表。

例三（顯示表的結構）：

mysql> desc users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(255) | NO   |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

　　註1：使用"desc users;"命令顯示了users這張表的結構；

　　註2: 上面這張user表的結構如下表所示： 有id（存儲11為的int類型數據，而且id是自增的），emali（存儲255位的varchar類型數據），password（同前）三個欄位，相等於Excel表格中的三列（mysql中的數據類型在上面有介紹），id，emial，password即為這張表的欄位。

例四（查詢某張表中數據）：

mysql> select * from users;
+----+----------------------+-------------+
| id | email                | password    |
+----+----------------------+-------------+
|  1 | webmaster@python.org | very-secret |
|  2 | webmaster@python.org | very-secret |
|  3 | webmaster@python.org | very-secret |
|  4 | xiaoming@123.com     | simple      |
|  5 | xiaoqiang@123.com    | simple      |
|  6 | xiaozhang@123.com    | very-secret |
|  7 | xiaoli@123.com       | simple      |
|  8 | xiangwang@123.com    | simple      |
|  9 | xiaohong@123.com     | very-secret |
+----+----------------------+-------------+
9 rows in set (0.00 sec)

　　註1：使用"select * form users"查詢user表中的所有數據，*代表user表中的所有欄位。

mysql> select * from users where users.password='simple';
+----+-------------------+----------+
| id | email             | password |
+----+-------------------+----------+
|  4 | xiaoming@123.com  | simple   |
|  5 | xiaoqiang@123.com | simple   |
|  7 | xiaoli@123.com    | simple   |
|  8 | xiangwang@123.com | simple   |
+----+-------------------+----------+
4 rows in set (0.01 sec)

　　註2：使用" select * form users where users.password='simple' "命令就可以查詢users表中所有password為simple的數據(在where後面加條件即可)。

mysql> select users.email from users;
+----------------------+
| email                |
+----------------------+
| webmaster@python.org |
| webmaster@python.org |
| webmaster@python.org |
| xiaoming@123.com     |
| xiaoqiang@123.com    |
| xiaozhang@123.com    |
| xiaoli@123.com       |
| xiangwang@123.com    |
| xiaohong@123.com     |
+----------------------+
9 rows in set (0.00 sec)

　　註3: 如果只想查看某一欄位的數據，只需使用"select tablename.x from tablename"即可。

# 查詢條件為password='simple'， 只顯示users.email欄位
mysql> select users.email from users where password='simple';
+-------------------+
| email             |
+-------------------+
| xiaoming@123.com  |
| xiaoqiang@123.com |
| xiaoli@123.com    |
| xiangwang@123.com |
+-------------------+
4 rows in set (0.00 sec)

　　註4: 查詢欄位與查詢條件沒有關係。

例五（將下麵數據插入表中）：

mysql> insert into users (email,password) values ('[email protected]','very-simple');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users where password='very-simple';
+----+-------------+-------------+
| id | email       | password    |
+----+-------------+-------------+
| 10 | abc@xyz.com | very-simple |
+----+-------------+-------------+
1 row in set (0.00 sec)

　　註1：在例三中，我們知道id是自增的，所以不用人為指定，只需指定其他兩個欄位的值。

　　註2: 向表中插入數據的語法如下：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

例六（修改表中的數據）：

　　修改表中數據的語法如下。

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

　　註意例四，我們將id為2的password改為"hello word"。

mysql> update users set password='hello word' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users where id=2;
+----+----------------------+------------+
| id | email                | password   |
+----+----------------------+------------+
|  2 | webmaster@python.org | hello word |
+----+----------------------+------------+
1 row in set (0.00 sec)

例七（刪除表中的數據）：

　　刪除表中數據的語法。

DELETE FROM table_name [WHERE Clause]

　　刪除users表中id>6的數據。

mysql> select * from users;
+----+----------------------+-------------+
| id | email                | password    |
+----+----------------------+-------------+
|  1 | webmaster@python.org | very-secret |
|  2 | webmaster@python.org | hello word  |
|  3 | webmaster@python.org | very-secret |
|  4 | xiaoming@123.com     | simple      |
|  5 | xiaoqiang@123.com    | simple      |
|  6 | xiaozhang@123.com    | very-secret |
|  7 | xiaoli@123.com       | simple      |
|  8 | xiangwang@123.com    | simple      |
|  9 | xiaohong@123.com     | very-secret |
| 10 | abc@xyz.com          | very-simple |
+----+----------------------+-------------+
10 rows in set (0.00 sec)

mysql> delete from users where id>6;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from users;
+----+----------------------+-------------+
| id | email                | password    |
+----+----------------------+-------------+
|  1 | webmaster@python.org | very-secret |
|  2 | webmaster@python.org | hello word  |
|  3 | webmaster@python.org | very-secret |
|  4 | xiaoming@123.com     | simple      |
|  5 | xiaoqiang@123.com    | simple      |
|  6 | xiaozhang@123.com    | very-secret |
+----+----------------------+-------------+
6 rows in set (0.00 sec)

  　　上面的例子都是介紹對資料庫表中數據的操作，接下來就來瞭解一下對錶及資料庫本身的操作。

例八（創建資料庫）：

#創建資料庫語法
CREATE DATABASE DABASENAME;

# 創建資料庫
mysql> create database mysql_test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_test         |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

 例九（在資料庫中創建表）：

創建一個如下結構的student表:

　　欄位stu_id可存儲的類型為11位int（不能為空），欄位name可存儲的類型為32位char（不能為空），欄位age可存儲的類型為11位int（不能為空），欄位register_data可存儲的類型為date。其中stu_id為主鍵，並且是自增的。

#創建數據表語法
CREATE TABLE table_name (column_name column_type);

#進入剛纔建立的資料庫，建立student表
mysql> use mysql_test;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> create table student(
    -> stu_id int not null auto_increment,    #欄位名，類型，屬性
    -> name char(32) not null,
    -> age int not null,
    -> register_date date,
    -> primary key(stu_id)    # 設置某個欄位為主鍵
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| student              |
+----------------------+
1 row in set (0.00 sec)

mysql> desc student;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| stu_id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| name          | char(32) | NO   |     | NULL    |                |
| age           | int(11)  | NO   |     | NULL    |                |
| register_date | date     | YES  |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

　　使用alter修改表結構

# 修改表名稱
ALTER TABLE tablename RENAME TO new_tablename;

# 刪除表的某個欄位
alter table tablename drop fieldname; 

# 向表中添加欄位
alter table tablename add fieldname int(11) not null;

# 修改欄位類型，把欄位 c 的類型從 CHAR(4) 改為 CHAR(8)
ALTER TABLE tablename MODIFY c CHAR(8);

# change語句，CHANGE 關鍵字之後，緊跟著的是要修改的欄位名，然後指定新欄位名及類型。
# 修改欄位名及類型
ALTER TABLE tablename CHANGE x y BIGINT;
# 修改欄位類型
ALTER TABLE testalter_tbl CHANGE x x INT;

例十（刪除資料庫即庫中所有的表）：

# 取消資料庫及資料庫中的所有表語法 DROP DATABASE DATABASENAME;

# 取消資料庫中某張表語法 DROP TABLE TABLENAME;

註： DROP DATABASE用於取消資料庫中的所用表格和取消資料庫。使用此語句時要非常小心！如果要使用DROP DATABASE，您需要獲得資料庫DROP許可權。

# 刪除剛創建的mysql_test資料庫及庫中的student表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_test         |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mysql> drop database mysql_test;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)