mysql資料庫入門命令擴充

標籤：str   constrain   core   primary   foreign   rom   --   record   表格   

添加主鍵
mysql> alter table info add primary key(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc info; #查看主鍵
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | | #顯示出主鍵
| name | char(6) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
| hobbly | int(4) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

添加外鍵
alter table info add constraint FK_ID foreign key(hobbly) REFERENCES hy(id);

mysql> show index from info\G; #查看外鍵
1. row
Table: info
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2. row
Table: info
Non_unique: 1
Key_name: FK_ID #外鍵添加成功
Seq_in_index: 1
Column_name: hobbly
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

表格同時修改個欄位（不同表列內容）
update info set age=23,num=44 where id=2;
mysql> select * from info;
+----+--------+-------+-----------+------+------+
| id | name | score | hobbly | age | num |
+----+--------+-------+-----------+------+------+
| 1 | zl | 88 | 看書 | 33 | NULL |
| 2 | 李四 | 68 | 上網 | 23 | 44 | #成功修改
| 3 | 王四 | 68 | 看電視 | NULL | NULL |
+----+--------+-------+-----------+------+------+
3 rows in set (0.00 sec)

同時增加多個欄位
mysql> alter table info add column age int,add column num int;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc info; #查看錶結構
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(4) | YES | | NULL | |
| score | char(6) | YES | | NULL | |
| hobbly | char(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+

同時刪除多個欄位
mysql> alter table info drop column age,drop column num;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from info;
+----+--------+-------+-----------+
| id | name | score | hobbly |
+----+--------+-------+-----------+
| 1 | zl | 88 | 看書 |
| 2 | 李四 | 68 | 上網 |
| 3 | 王四 | 68 | 看電視 |
+----+--------+-------+-----------+

同時刪除多個記錄條數（多行）
mysql> delete from info where id in (3,4);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from info;
+----+--------+-------+--------+
| id | name | score | hobbly |
+----+--------+-------+--------+
| 1 | zl | 88 | 看書 |
| 2 | 李四 | 68 | 上網 |
| 5 | kl | 88 | 遊泳 |
+----+--------+-------+--------+

同時查看多條指定記錄（行）
mysql> select * from info where id in (3,4);
+----+--------+-------+-----------+
| id | name | score | hobbly |
+----+--------+-------+-----------+
| 3 | 王四 | 68 | 看電視 |
| 4 | ll | 67 | 看書 |
+----+--------+-------+-----------+

mysql資料庫入門命令擴充