mysql數(shù)據(jù)庫基本命令---多條數(shù)據(jù)的同時(shí)操作

添加主鍵
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;    #查看外鍵
* 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)

表格同時(shí)修改個(gè)字段（不同表列內(nèi)容）
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    | 上網(wǎng)      |   23 |   44 |        #成功修改
|  3 | 王四   | 68    | 看電視    | NULL | NULL |
+----+--------+-------+-----------+------+------+
3 rows in set (0.00 sec)

同時(shí)增加多個(gè)字段
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; #查看表結(jié)構(gòu)
+--------+----------+------+-----+---------+-------+
| 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    |       |
+--------+----------+------+-----+---------+-------+

同時(shí)刪除多個(gè)字段
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    | 上網(wǎng)      |
|  3 | 王四   | 68    | 看電視    |
+----+--------+-------+-----------+

同時(shí)刪除多個(gè)記錄條數(shù)（多行）
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    | 上網(wǎng)   |
|  5 | kl     | 88    | 游泳   |
+----+--------+-------+--------+

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