MySQL 插入更新删除

古城微笑少年丶 2022-06-18 10:24 168阅读 0赞

##### 先建一张表, 接下来用来操作 #####

### 插入数据 ###

##### INSERT语句中不指定具体的字段名 #####

# INSERT INTO tbl VALUES (值1, 值2, ..., 值n)
    
    MariaDB [carltest]> INSERT INTO food VALUES (1, 'AA饼干', 'AA饼干厂', 2.5, '2008', 3, '北京');
    Query OK, 1 row affected (0.05 sec)
    
    MariaDB [carltest]> select * from food;
    +----+----------+-------------+-------+--------------+---------------+---------+
    | id | name     | company     | price | produce_time | validity_time | address |
    +----+----------+-------------+-------+--------------+---------------+---------+
    |  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
    +----+----------+-------------+-------+--------------+---------------+---------+
    1 row in set (0.00 sec)

##### 插入指定列 #####

# INSERT INTO tbl (属性1, 属性2, ..., 属性m)
    # VALUES (值1, 值2, ..., 值m)
    
    MariaDB [carltest]> INSERT INTO food (id, name, company, price, produce_time, validity_time, address) VALUES (2, 'CC牛奶', 'CC牛奶厂', 3.5, '2009', 1, '河北');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [carltest]> select * from food;
    +----+----------+-------------+-------+--------------+---------------+---------+
    | id | name     | company     | price | produce_time | validity_time | address |
    +----+----------+-------------+-------+--------------+---------------+---------+
    |  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
    |  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
    +----+----------+-------------+-------+--------------+---------------+---------+
    2 rows in set (0.00 sec)

##### 同时插入多行数据 #####

# INSERT INTO tbl [(属性列表)]
    # VALUES (取值列表1), (取值列表2), ..., (取值列表n);
    
    INSERT INTO food VALUES (NULL, 'EE果冻', 'EE果冻厂', 1.5, '2007', 2, '北京'),
                            (NULL, 'FF咖啡', 'FF咖啡厂', 20, '2002', 5, '天津'),
                            (NULL, 'GG奶糖', 'GG奶糖厂', 14, '2003', 3, '广东');
    
    MariaDB [carltest]> INSERT INTO food VALUES (NULL, 'EE果冻', 'EE果冻厂', 1.5, '2007', 2, '北京'),
        ->                         (NULL, 'FF咖啡', 'FF咖啡厂', 20, '2002', 5, '天津'),
        ->                         (NULL, 'GG奶糖', 'GG奶糖厂', 14, '2003', 3, '广东');
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    MariaDB [carltest]> select * from food;
    +----+----------+-------------+-------+--------------+---------------+---------+
    | id | name     | company     | price | produce_time | validity_time | address |
    +----+----------+-------------+-------+--------------+---------------+---------+
    |  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
    |  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
    |  3 | EE果冻   | EE果冻厂    |   1.5 |         2007 |             2 | 北京    |
    |  4 | FF咖啡   | FF咖啡厂    |    20 |         2002 |             5 | 天津    |
    |  5 | GG奶糖   | GG奶糖厂    |    14 |         2003 |             3 | 广东    |
    +----+----------+-------------+-------+--------------+---------------+---------+
    5 rows in set (0.00 sec)

##### 将查询结果插入到表中 #####

# INSERT INTO tbl1 (属性列表1)
    # SELECT 属性列表2 FROM tbl2 WHERE 条件表达式;
    
    MariaDB [carltest]> desc food1;
    +---------------+-------------+------+-----+---------+----------------+
    | Field         | Type        | Null | Key | Default | Extra          |
    +---------------+-------------+------+-----+---------+----------------+
    | id            | int(10)     | NO   | PRI | NULL    | auto_increment |
    | name          | varchar(20) | NO   |     | NULL    |                |
    | company       | varchar(30) | NO   |     | NULL    |                |
    | price         | float       | YES  |     | NULL    |                |
    | produce_time  | year(4)     | YES  |     | NULL    |                |
    | validity_time | int(4)      | YES  |     | NULL    |                |
    | address       | varchar(50) | YES  |     | NULL    |                |
    +---------------+-------------+------+-----+---------+----------------+
    7 rows in set (0.12 sec)
    
    MariaDB [carltest]> INSERT INTO food1 SELECT * FROM  food;
    Query OK, 5 rows affected (0.01 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    MariaDB [carltest]> select * from food1;
    +----+----------+-------------+-------+--------------+---------------+---------+
    | id | name     | company     | price | produce_time | validity_time | address |
    +----+----------+-------------+-------+--------------+---------------+---------+
    |  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
    |  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
    |  3 | EE果冻   | EE果冻厂    |   1.5 |         2007 |             2 | 北京    |
    |  4 | FF咖啡   | FF咖啡厂    |    20 |         2002 |             5 | 天津    |
    |  5 | GG奶糖   | GG奶糖厂    |    14 |         2003 |             3 | 广东    |
    +----+----------+-------------+-------+--------------+---------------+---------+
    5 rows in set (0.00 sec)

### 更新数据 ###

# UPDATE tbl SET 属性1=值1, 属性2=值2, ..., 属性n=值n WHERE 条件表达式
    
    MariaDB [carltest]> select * from food where name='CC牛奶';
    +----+----------+-------------+-------+--------------+---------------+---------+
    | id | name     | company     | price | produce_time | validity_time | address |
    +----+----------+-------------+-------+--------------+---------------+---------+
    |  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
    +----+----------+-------------+-------+--------------+---------------+---------+
    1 row in set (0.00 sec)
    
    MariaDB [carltest]> update food set address='内蒙古', price=3.2 where name='CC牛奶';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MariaDB [carltest]> select * from food where name='CC牛奶';
    +----+----------+-------------+-------+--------------+---------------+-----------+
    | id | name     | company     | price | produce_time | validity_time | address   |
    +----+----------+-------------+-------+--------------+---------------+-----------+
    |  2 | CC牛奶   | CC牛奶厂    |   3.2 |         2009 |             1 | 内蒙古    |
    +----+----------+-------------+-------+--------------+---------------+-----------+
    1 row in set (0.00 sec)

### 删除数据 ###

# DELETE FROM tbl [WHERE 条件表达式]
    
    MariaDB [carltest]> select * from food where address='北京';
    +----+----------+-------------+-------+--------------+---------------+---------+
    | id | name     | company     | price | produce_time | validity_time | address |
    +----+----------+-------------+-------+--------------+---------------+---------+
    |  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
    |  3 | EE果冻   | EE果冻厂    |   1.5 |         2007 |             2 | 北京    |
    +----+----------+-------------+-------+--------------+---------------+---------+
    2 rows in set (0.00 sec)
    
    MariaDB [carltest]> delete from food where address='北京';
    Query OK, 2 rows affected (0.00 sec)
    
    MariaDB [carltest]> select * from food where address='北京';
    Empty set (0.00 sec)