組合主鍵

1. 登入 mysql。
   

   [root@kvm6 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe
   mysql 5.5.49
   mycli 1.19.0
   Chat: https://gitter.im/dbcli/mycli
   Mail: https://groups.google.com/forum/#!forum/mycli-users
   Home: http://mycli.net
   Thanks to the contributor - Klaus Wünschel
   

2. 使用資料庫 dyw。
   

   mysql root@localhost:(none)> use dyw;                                                                                                       
   You are now connected to database "dyw" as user "root"
   Time: 0.000s
   

3. 產生資料表 employees，有 id, last_name, first_name, phone 四個欄位，其中 last_name, first_name 兩個欄位組合成 primary key。
   

   mysql root@localhost:dyw> create table employees ( id int unsigned not null, last_name
                             varchar (20), first_name varchar(20),primary key(last_name,
                             first_name), phone varchar(20));                     
   Query OK, 0 rows affected
   Time: 0.293s
   

4. 列出資料表 employees 的所有欄位特徵。
   

   mysql root@localhost:dyw> desc employees;                                
   +------------+------------------+------+-----+---------+-------+
   | Field      | Type             | Null | Key | Default | Extra |
   +------------+------------------+------+-----+---------+-------+
   | id         | int(10) unsigned | NO   |     | <null>  |       |
   | last_name  | varchar(20)      | NO   | PRI |         |       |
   | first_name | varchar(20)      | NO   | PRI |         |       |
   | phone      | varchar(20)      | YES  |     | <null>  |       |
   +------------+------------------+------+-----+---------+-------+
   4 rows in set
   Time: 0.027s
   

5. employees 增加兩筆資料。
   

   mysql root@localhost:dyw> insert into employees (id,last_name,first_name,phone
                             ) values (1,'wang','dy','09123456'), (2,'lin','da','
                             09234561');                                         
   Query OK, 2 rows affected
   Time: 0.011s
   

6. 查詢 employees 有兩筆資料。
   

   mysql root@localhost:dyw> select * from employees;                            
   +----+-----------+------------+----------+
   | id | last_name | first_name | phone    |
   +----+-----------+------------+----------+
   | 2  | lin       | da         | 09234561 |
   | 1  | wang      | dy         | 09123456 |
   +----+-----------+------------+----------+
   2 rows in set
   Time: 0.009s
   

7. 再增加一筆 last_name = wang，first_name = da。
   

   mysql root@localhost:dyw> insert into employees (id,last_name,first_name,phone
                             ) values (1,'wang','da',null);                      
   Query OK, 1 row affected
   Time: 0.003s
   

8. last_name = wang，first_name = da，last_name, first_name 都與現有資料重複，但合在一起沒有重複，所以成功加入。
   

   mysql root@localhost:dyw> select * from employees;                            
   +----+-----------+------------+----------+
   | id | last_name | first_name | phone    |
   +----+-----------+------------+----------+
   | 2  | lin       | da         | 09234561 |
   | 1  | wang      | da         | <null>   |
   | 1  | wang      | dy         | 09123456 |
   +----+-----------+------------+----------+
   3 rows in set
   Time: 0.022s
   

9. 再增加一筆 last_name = lin，first_name = da，last_name, first_name 組合與現有資料重複，所以無法加入。
   

   mysql root@localhost:dyw> insert into employees (id,last_name,first_name,phone
                             ) values (1,'lin','da',null);                       
   (1062, u"Duplicate entry 'lin-da' for key 'PRIMARY'")