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

发布时间:2020-07-31 18:36:08 作者:低调的男孩
来源:网络 阅读:997

添加主键
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    | 看书      |
+----+--------+-------+-----------+
推荐阅读:
  1. Solaris 10基本命令操作
  2. MongoDB基本命令操作

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 数据库 同时

上一篇:Plasma Cash 合约解读

下一篇:UUID.randomUUID().toString()的作用

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》