mysql学习-INSERT,UPDATE,DELETE语句

2022-03-14 02:42:44

SELECT 是最常用的SQL语句，除此之外还有INSERT插入语句，UPDATE语句和DELETE删除语句

插入单行数据

在SQL中，使用INSERT语句可以向数据表中插入数据，一般有三种方式

  • 插入完整的一行数据

  • 插入一行数据的一部分

  • 一次向表中插入多行数据

INSERT语句语法

INSERT [INTO] table_or_view [(column_list)] VALUES (value_list)

  • table_or_view: 表名或者视图的名称

  • column_list: 由逗号分隔的列名列表

  • value_list: 作为一个或者多行插入已命名的表或者视图

说明，在某些sql实现中，INSERT之后的INTO关键字是可选的，但是最好还是带上

插入整行数据

用户有时会给数据表的所有列都插入值，即VALUES后要包含所有的列的值，而表名后的column_list参数有两种情况，一种是依次列出所有的列名; 另外一种是省略掉列名列表，尽管采用第二种情况输入起来更快，但是采用第一种情况更加易于理解和维护。

-- 使用INSERT语句插入一条完整数据

INSERT INTO orders
(id,user_id,recevie_name,recevie_address,recevie_tel,remark,addtime)
VALUES
(28,6,‘大佬‘,‘湖北‘,123456,‘我很大，你忍一下‘,‘2018-11-02 15:55:01‘);

注意，插入各值的数据类型必须与表中的对应的列的数据类型一样，否则出错

在向数据表插入整行的数据列的时候，列名和列表可以省略，如下所示：

-- 使用INSERT语句插入一条完整数据

INSERT INTO orders
VALUES
(28,6,‘大佬‘,‘湖北‘,123456,‘我很大，你忍一下‘,‘2018-11-02 15:55:01‘);

如果采用着中国省略列名列表的方式，则必须为每一列提供一个值，各列必须以它们在表中出现的次序进行填充，如果某一列没有值，且该列允许NULL值，则可以使用NULL值对该列进行填充。

说明：
最好在定义INSERT语句的时候给出列名列表，优点是，即使表的结构发生了变化，这条INSERT语句仍然有可能正确运行。

注意：
不管使用那种INSERT语法，VALUES值到表中的数目都必须正确，如果不提供列名，则必须给表中每一个列表提供一个值，如果提供列名，则必须给每个列提供一个值，否则就会产生一条错误信息，相应的数据不能成功插入。

插入部分行数据

在向数据表中插入数据的时候，如果某一列定义为允许NULL值，则可以在INSERT语句中省略该列，因此在使用INSERT语句插入的时候，可以只给某些列提供值，给允许NULL值的不提供值

插入默认值

用户在创建数据表时允许DEFAULT关键字为列定义默认值。例如，插入新数据时没有给字段new_cloumn提供数据，而这个字段有一个默认值"default_value"，在这种情况下当新数据建立时会插入“default_value"

插入多行数据

应用insert语句通常只插入一行数据，实际上，通过使用INSERT语句中的VALUES关键字一次能向数据表中插入多行数据

通过VALUES关键字插入多行数据

通过使用VALUES关键字一次向数据表中插入多行数据，语法：

INSERT [INTO] table_name
[(column_list)]
VALUES
(data1,data2,...),(data1,data2,.........),...........
-- 插入两行数据
INSERT INTO orders
(id,user_id,recevie_name,recevie_address,recevie_tel,remark,addtime)
VALUES
(30,6,‘大佬‘,‘湖北‘,123456,‘我很大，你忍一下‘,‘2018-11-02 15:55:01‘),
(31,6,‘大佬‘,‘湖北‘,123456,‘吴不吴聊，亦直发 ‘,‘2018-11-02 15:55:01‘);

通过查询语句插入多行数据

INSERT语句还存在另一种形式，这种形式由一条INSERT语句和一条SLECTE语句组成，利用他们将SELECT语句的查询结果插入到数据表中，通过这种形式也可以一次给数据表添加多行数据（即批量插入数据），使用INSERT SELECT批量插入数据时，INSERT语句后的VALUES子句指定的时一个SELECT查询的结果集。

语法格式如下：

INSERT [INTO] table_name
SELECT {* | fieldname1 [,filedname2....]}
FROM table_source [WHERE search_contion]

创建一个新表

-- 创建一个新的数据表
CREATE TABLE orders_new(
id int NOT NULL PRIMARY KEY,
user_id int NOT NULL DEFAULT ‘0‘,
recevie_name VARCHAR(60) NOT NULL,
recevie_address VARCHAR(60) NOT NULL,
recevie_tel VARCHAR(60)  NOT NULL DEFAULT ‘0‘,
remark  VARCHAR(60) NOT NULL,
addtime datetime NOT NULL
);

查询旧表结果插入到新表中

-- 将查询到旧表插入到新表中
INSERT INTO orders_new
SELECT * FROM orders
WHERE user_id = 6;

说明

  • INSERT SELECT 中的SELECT语句可以包含WHERE子句，用来筛选插入的数据

  • 虽然brand_new数据表和brand商品品牌表的结构需要一致，但是并不是要求列名匹配

表中的数据的复制

在mysql中复制表数据

在msyql中将已窜在的数据复制到索要创建的数据表中使用的时CREATE TABLE SELECT语句，语法如下：

CRATE TABLE new_table AS
SLECT [select_list]
FROM table_name
WHERE search_condition
-- 查询分类 并插入新表中
CREATE TABLE orders_new_new
SELECT id ,user_id ,recevie_name ,recevie_address ,recevie_tel 
FROM orders WHERE recevie_tel =‘18910441510‘;

有时候，需要对数据表中的一行数据进行更新，或者对某些数据进行删除操作，这时就需要使用update和delete语句。

UPDATE

语法

UPDATE语句用来修改表中的数据，使用update语句通常由两种形式，一种更新表中的指定的行，一种更新表中所有行。

UPDATE table_name
SET column1=value1.column2=value3,....
WHERE search_condtion

注意，UPDATE语句中的WHERE子句规定哪条或者哪些记录需要更新，如果省略了where,那么表中所有的记录都会被更新。

使用UPDATE语句更新列值

更新数据表中的列值通常有两种情况，一种是只更新一行数据（包括where子句),另外一种是更新表中的所有数据（不包含where子句)

更新一行数据的单个列

-- 修改列值名称
UPDATE orders_new 
SET recevie_name=‘我好大‘
WHERE recevie_tel=‘123456‘;

更新一行数据多个列

UPDATE orders_new
SET user_id=7,remark=‘老王，不得不防‘
WHERE recevie_name=‘小明‘

更新表中所有的行的列值

-- 对所有行的列进行更新，修改为123456789
UPDATE orders_new_new
SET recevie_tel =‘123456789‘;

依据外表值更新数据

虽然update语句只允许改变单个表的列值，但是在update语句中的where子句可以使用任何可用的表。因此可以根据其他表中的相关的值来决定目标表中要更新的数据的行

-- 应用update对商品分类为“平板电脑”的所有的行store_count列进行更新，将该列的值加1000
UPDATE goods
SET store_count =store_count +1000
WHERE cat_id =(SELECT id FROM goods_category WHERE name=‘平板电脑‘)
-- 更新数据之后，使用select查询goods商品表中商品分类为"平板电脑"的所有商品id（goods_id),商品名称（goods_name)和库存数量(store_count) ,查看数量是否更新成功
SELECT goods_id,goods_name,store_count
FROM goods
WHERE cat_id=(SELECT id FROM goods_category WHERE name=‘平板电脑‘)

DELETE语句

DELETE用来删除表中的数据，使用DELETE语句通常有两种形式，一种是删除表中指定的行，另外一种就是删除表中的所有行。

语法

DELET [FROM]
{table_name | view_name}
[WHERE search_condtions]

DELETE不需要列名或者通配符，使用DELETE删除是整行而不是整列，如果要删除指定的列，可以使用update语句

说明：

  • 在某些SQL实现中，DELETE之后的FROM关键字是可选的，但是最好提供这个关键字，这样有利于sql语句在DBMS之间的可移植性

  • DELETE语句中的WHERE子句规定哪条记录或者哪些记录需要被删除，如果省略了WHERE子句，表中的所有记录都会被删除

    使用DELETE删除数据

    删除数据表中的数据通常有三个情况，第一种情况只删除表中的一行数据（包含where子句);第二种是删除表中的多行数据；第三种是删除表中的所有数据(不包含where子句)

    删除一行数据

    使用delete语句删除单行数据的时候where子句不能省略（除非数据表中只包含一行数据）；如果省略的where语句，那么所有行都会被删除。

-- 删除表中指定一行
DELETE FROM orders_new_new
WHERE recevie_name=‘张三‘;

删除多行数据

删除多行数据，同样需要在where子句中设置过滤条件

-- 删除多行数据
DELETE FROM orders_new_new
WHERE recevie_name IN (‘测试‘,‘测试3‘);

删除所有行的数据

-- 所有行数据
DELETE FROM orders_new_new

注意，DELETE可以删除所有行，但是语句不会删除表本身

使用TRUNCATE TABLE语句删除行

TRUNCATE TABLE语句不但删除了数据，而且所删除的数据在事务处理日志中还会做相应的记录

语法

TRUNCATE TABLE table_name

说明：TRUNCATE TABLE语句实现的结果等同与不带WHERE子句的DELETE语句，如果要删除表中的所有行，建议使用TRUNCATE TABLE语句，因为TRUNCATE TABLE语句的效率比DLETE语句的效率要高

上一篇：SQL 入门教程：组合 WHERE 子句


下一篇：039.PGSQL-备份和恢复-开启wal归档