mysql扩展命令_mysql数据库入门命令扩展

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 | 看书 |

+----+--------+-------+-----------+

原文：http://blog.51cto.com/13760351/2169168