USE 資料庫名稱
mysql> USE D1; Database changed
使用USE D1;表示開啟資料庫D1 ,我們可以透過SELECT DATABASE();來查看目前開啟的資料庫:
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | d1 | +------------+1 row in set (0.00 sec)
CREATE TABLE [IF NOT EXISTS] table_name (
column_name datatype,
......
)
這個結構很簡單,對於[IF NOT EXISTS],在第一篇「MySQL基本操作」 已經說明,這裡不贅述。
我們來建立一個資料表table1:
mysql> CREATE TABLE table1( -> username VARCHAR(20), -> age TINYINT UNSIGNED, -> salary FLOAT(8,2) UNSIGNED -> ); Query OK, 0 rows affected (0.74 sec)
注意這裡的UNSIGNED,表示無符號值,即是正數,可回顧「MySQL基本資料類型」 查看,TINYINT UNSIGNED 表示0 ~ 255 之間的數值。
這裡提示建立成功,我們可以透過以下語句來驗證:
SHOW TABLES [FROM db_name][LIKE 'pattern' | WHERE expr]
mysql> SHOW TABLES FROM D1; +--------------+ | Tables_in_d1 | +--------------+ | table1 | +--------------+1 row in set (0.00 sec)
這裡我們可以看到創建了table1這張表。
SHOW COLUMNS FROM tbl_name
mysql> SHOW COLUMNS FROM table1; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | salary | float(8,2) unsigned | YES | | NULL | | +----------+---------------------+------+-----+---------+-------+3 rows in set (0.10 sec)
建立完表之後就要寫入數據了,透過以下語句插入記錄:
##這裡INSERT [INTO] tbl_name [(col_name,...)] VALUE(val,...)
[(col_name,...)] 為可選項,如果不加,那麼在VALUE裡面的值必須一一與資料表的欄位對應,否則無法插入,我們看一下:
mysql> INSERT table1 VALUE("LI",20,6500.50); Query OK, 1 row affected (0.14 sec)
mysql> INSERT table1 Value("Wang",25); ERROR 1136 (21S01): Column count doesn't match value count at row 1
[(col_name,...)] 即可靈活插入資料:
mysql> INSERT table1(username,age) VALUE("Wang",25); Query OK, 1 row affected (0.11 sec)
SELECT expr,... FROM tbl_name對於資料庫的查找語句SELECT,內容比較多,後面文章會具體講解,我們用一個簡單的語句來查找表的內容:
mysql> SELECT * FROM table1 -> ; +----------+------+---------+ | username | age | salary | +----------+------+---------+ | LI | 20 | 6500.50 | | Wang | 25 | NULL | +----------+------+---------+2 rows in set (0.00 sec)
table2:
mysql> CREATE TABLE table2( -> username VARCHAR(20) NOT NULL, -> age TINYINT UNSIGNED NULL, -> salary FLOAT(8,2) -> );
mysql> SHOW COLUMNS FROM table2; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | salary | float(8,2) | YES | | NULL | | +----------+---------------------+------+-----+---------+-------+3 rows in set (0.01 sec)
AUTO_INCREMENTauto_increment,auto自動,increment是增加的意思,組合起來表示自動增加,也就是可以自動依照從小到大的順序編號。
mysql> CREATE TABLE table3( -> id SMALLINT UNSIGNED AUTO_INCREMENT, -> username VARCHAR(20) -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
PRIMARY KEY
mysql> CREATE TABLE table3( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) -> ); Query OK, 0 rows affected (0.42 sec)
mysql> INSERT table3(username) VALUES("Zhang"); Query OK, 1 row affected (0.09 sec) mysql> INSERT table3(username) VALUES("Weng"); Query OK, 1 row affected (0.07 sec) mysql> INSERT table3(username) VALUES("Chen"); Query OK, 1 row affected (0.09 sec) mysql> SELECT * FROM table3; +----+----------+ | id | username | +----+----------+ | 1 | Zhang | | 2 | Weng | | 3 | Chen | +----+----------+3 rows in set (0.00 sec)
UNIQUE KEY
mysql> CREATE TABLE table4( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) UNIQUE KEY, -> age TINYINT UNSIGNED -> ); Query OK, 0 rows affected (0.43 sec) mysql> INSERT table4(username) VALUE("Li"); Query OK, 1 row affected (0.11 sec) mysql> INSERT table4(username) VALUE("Li"); ERROR 1062 (23000): Duplicate entry 'Li' for key 'username' mysql> INSERT table4(username) VALUE("Chen"); Query OK, 1 row affected (0.10 sec)
預設值DEFAULT
來設定預設值,如果在插入資料時沒給給出對應的值,那麼就用預設的,下面的例子就是設定number的預設值為3,在插入資料的時候,因為沒有給出number,所以預設為3。 以上是關於MySQL資料表操作的詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章!mysql> CREATE TABLE table5(
-> number ENUM("1","2","3") DEFAULT "3",
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.41 sec)
mysql> INSERT table5(username) VALUES("Luo");
Query OK, 1 row affected (0.10 sec)
mysql> INSERT table5(username) VALUES("Fang");
Query OK, 1 row affected (0.15 sec)
mysql> SELECT * FROM table5;
+--------+----------+
| number | username |
+--------+----------+
| 3 | Luo |
| 3 | Fang |
+--------+----------+2 rows in set (0.00 sec)