MySQL 插入更新删除

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

MariaDB [carltest]> CREATE TABLE food (id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(20) NOT NULL, -> company VARCHAR(30) NOT NULL, -> price FLOAT, -> produce_time YEAR, -> validity_time INT(4), -> address VARCHAR(50)); Query OK, 0 rows affected (0.31 sec) MariaDB [carltest]> desc food; +---------------+-------------+------+-----+---------+----------------+ | 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.09 sec)

插入数据

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)