mysql数据库基本命令---多条数据的同时操作

添加主键
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)

表格同时修改个字段（不同表列内容）
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数据库基本命令---多条数据的同时操作
文章URL：http://6mz.cn/article/iijecg.html