新建完一个表后不知道怎么用SQL去操作表数据,看完这篇文章后就不慌了

299 阅读8分钟

MySQL基础(七)-----并集查询和数据的插入、删除、更新

每个查询语句都有其对应的结果集,我们可以将多个查询语句的结果集合并起来,这可以称为并集查询;

对一个表操作前,你的表里面必须得保证有数据;

一、并集查询

每个查询语句都有其对应的结果集,我们可以将多个查询语句的结果集合并起来,这可以称为并集查询;

1、涉及单表的并集查询

单表的查询最简单的方法就是用and或者or运算符将两个搜索条件连起来。除了这个方法外还可以使用UNION将两个查询语句连接在一起。

样例:

SELECT a1 FROM t1 WHERE a1<2;
SELECT a1 FROM t1 WHERE a1>2;

 使用or连接起来

SELECT a1 FROM t1 WHERE a1<2 OR a1>2;

使用union连接起来

SELECT a1 FROM t1 WHERE a1<2
UNION
SELECT a1 FROM t1 WHERE a1>2;

多个查询语句也可以直接用UNION连接起来

样例:

SELECT a1 FROM t1 WHERE a1<2
UNION
SELECT a1 FROM t1 WHERE a1>2
UNION
SELECT a1 FROM t1 WHERE a1=2;

使用UNION连接起来的各个查询语句的查询列表处不光只能包含一个表达式,还可以包含多个表达式。只要每个语句的表达式数量相同即可。

在使用UNION连接起来的各个查询语句的查询列表中,位置相同的表达式的类型应该是相同的。当然,这不是硬性要求,如果不相同的话,MySQL会进行必要的类型转换。最后结果集中显示的列名将以第一个语句中的查询列表为准。

2、涉及不同表的并集查询

只在一个表中进行并集查询怎么能体现出并集查询的强大呢,并集查询更多的还是用在涉及不同表的查询语句中。

样例:

SELECT a1,b1 FROM t1 WHERE a1<2;
SELECT a2,b2 FROM t2 WHERE a2>2;

使用UNION将这两个查询语句连接起来

SELECT a1,b1 FROM t1 WHERE a1<2
UNION
SELECT a2,b2 FROM t2 WHERE a2>2;

 

3、包含或去除重复的行

先看样例,再做分析:

SELECT a1,b1 FROM t1;
SELECT a2,b2 FROM t2;

合并后 

SELECT a1,b1 FROM t1
UNION
SELECT a2,b2 FROM t2;

 

会发现,明明是有6条数据的,但是这个地方把重复的数据给去重了。原因是使用并集查询会默认过滤掉结果集中的重复的记录。

如果想要保留重复记录,可以使用UNION ALL 来连接多个查询;

样例:

SELECT a1,b1 FROM t1
UNION ALL
SELECT a2,b2 FROM t2;

 

4、并集查询中的ORDER BY 和LIMIT子句

并集查询会把每个查询的结果汇集到一块,如果我们想对最终的结果集进行排序或者只保留几行的话,可以在并集查询的语句末尾加上order by 和limit子句

样例:

SELECT a1,b1 FROM t1
UNION
SELECT a2,b2 FROM t2
ORDER BY a1 DESC LIMIT 2;

 

最好是给每个单独的查询语句加上括号,规范一些。

(SELECT a1,b1 FROM t1)
UNION
(SELECT a2,b2 FROM t2)
ORDER BY a1 DESC LIMIT 2;

注意:由于并集查询的结果集展示的列名是第一个查询中给定的列名,所以order by子句中指定的排序列也必须是第一个查询中给定的列名 

另外,大家可能向,能不能在小的查询里面排序,而不为最终的汇集后的结果排序,说实在的,没有任何意义,因为设计MySQL的人规定了,并集查询并不保证最终结果集中的顺序是按照各个小查询的结果集中的顺序排序的。所以没有任何的意义。不过,如果你只是想单纯地想从各个小的查询中获取有限条排序好的记录加入最终的结果集,还是可以的。 

二、数据的插入、删除、更新 

1、准备工作

在进行数据的插入、删除、更新前,还是先建一个表吧

CREATE TABLE test_table(
	first_column INT,
	second_column VARCHAR(100)
);

2、插入数据

在关系数据库中,数据一般都是以记录(行)为单位插入表中的,具体的插入形式请看下述分析。

2.1、插入完整的记录

在插入一条完整的记录时,需要指定要插入表的名称和该条记录中全部列的具体数据。完整的语法如下:

INSERT INTO 表名 VALUES(列1的值,列2的值,...,列n的值);

样例:

-- 最基础的
INSERT INTO test_table VALUES(1,'aaaaa');
-- 前提是声明该列无NOT NULL属性
INSERT INTO test_table VALUES(2,NULL);
-- 自定义列的插入顺序
INSERT INTO test_table(first_column,second_column) VALUES(3,'cccccc');
-- 即使结构变了,这个自定义的插入语句也没问题
INSERT INTO test_table(second_column,first_column) VALUES('cccccc',4);

 2.2、插入记录的一部分

在插入记录的时候,如果某个列运行存储NULL,或者我们通过给列定义default属性显式指定了默认值的话,那么该列的值就可以在插入语句中省略。

我们在建表的时候没有设值NOT NULL,所以MySQL给字段默认是可以存储NULL的,所以在插入的时候可以省略部分列的值。

例如:

-- 省略第二个字段
INSERT INTO test_table(first_column) VALUES(5);
-- 省略第一个字段
INSERT INTO test_table(second_column) VALUES('ffffff');

在这个地方在说明强调一下,insert语句中指定的列顺序可以改变,但是一定要与values子句中的值一一对应起来。

2.3、批量插入记录

在前面我们已经简单地额了解过批量插入记录。这个地方再说一遍,插入记录时,每插入一条就写一条insert语句也不是不行,但是太麻烦了。而且每次插入一条记录就提交一个给服务器远没有一次把所有待插入的记录全部提交给服务器的效率高。所以MySQL提供了批量插入的语句,就是直接在values后多加几组值,每组值用小括号扩起来,然后各组之间用逗号分隔就好了。

模板:

INSERT INTO 表名 VALUES(列1的值,列2的值,...,列n的值),(列1的值,列2的值,...,列n的值),(列1的值,列2的值,...,列n的值);

样例:

INSERT INTO test_table(first_column,second_column) VALUES(7,'gggggg'),(8,'hhhhhh');

2.4、将某个查询的结果集插入表中

前面的插入语句都是我们显式地将记录的值放在values后面,其实也可以将某个查询的结果集作为数据源插入表中。再建一个表作为我们的测试表

CREATE TABLE test2_table(
	second_column VARCHAR(100),
		first_column INT
);

 这个表里面有两个列,和之前的那个表的列刚好相反,用来测试最好不过。见具体语句:

INSERT INTO test2_table(second_column,first_column) SELECT second_column,first_column FROM test_table WHERE first_column <5;

这个SQL分为两步:

  •  先执行查询语句;
  • 把查询语句对应的结果集中的记录批量插入到指定的表中

在将某个查询的结果集插入到表中时需要注意,insert语句指定的列要和select语句的查询列表中的表达式一一对应。

2.5、INSERT IGNORE

对于一些主键或者unique键的列或者列组合来说,它们是不允许重复值的出现。我们先把test_table表的第一个列加上一个unique约束

ALTER TABLE test_table MODIFY COLUMN first_column INT UNIQUE;

这个时候我们在插入一个第一个列值为1的数据就会报错

可是我们在插入新纪录时,并不知道待插入记录的主键或者unique键是否在表中有重复值,所以我们迫切地需要这样一个功能,即对于那些是主键或者unique键的列或者列组合来说,如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值,那么就把待插入记录插到表中,否则忽略此次插入操作(而不是报错)。具体SQL如下:

INSERT IGNORE INTO test_table(first_column,second_column) VALUES(7,'hhhhhhhhhhhhdrgdgd'),(10,'dghfhfhgf');

 2.6、INSERT ... ON DUPLICATE KEY UPDATE

如果表中包含与待插入记录的主键或者unique键的值重复的记录,不仅仅可以选择使用insert ignore忽略待插入记录,还可以使用MySQL提供的insert ... on duplicate key update语句来更新表中已经存在的记录。如果表中存在就是更新数据,不存在就是插入数据。

可以看到,在insert ... on duplicate key update语句中可以更新多个列的值,各个列之间使用逗号分隔开就好。

对于作为主键或者unique键的列或者列组合来说,如果表中现有的记录在这些列或者列组合上与待插入记录有重复的值,则可以使用values(列名)的形式来引用待插入记录中对应列的值。

样例:

INSERT INTO test_table(first_column,second_column) VALUES(10,'雪花勇闯天涯') ON DUPLICATE KEY UPDATE second_column = VALUES(second_column);

你也可以直接写成等于对应的值也行,但是 在批量插入大量记录的时候咋办捏,还是得用values(列名)。

从MySQL8.0.20开始,不推荐使用values函数来引用带插入记录中的列,而推荐为待插入的记录定义别名来引用待插入记录中的列。例如:

-- 适用于8.0.20后
INSERT INTO test_table(first_column,second_column) VALUES(2,'红牛嘎嘎猛'),(3,'橙汁儿嘎嘎好喝') AS new ON DUPLICATE KEY UPDATE second_column = new.second_column;

就是起了一个别名,替代了原先的values函数

3、删除数据

如果某些记录不想要了,可以使用下面的语句把它们删除掉

DELETE FROM 表名 [WHERE 表达式];

 样例:

DELETE FROM test_table WHERE first_column>4;

当然,删除语句中的where子句是可选的,如果不加where子句,就意味着删除表中所有的记录。

在使用删除语句一定要特别注意,虽然删除语句中的where条件是可选的,但是如果不加上where条件,则会删除所有的记录,这是玩火呀铁汁们!十分危险的,千万千万千万慎重而行!

另外,还可以使用limit子句来限制想要删除的记录数量,使用order by子句来指定符合条件的记录的删除顺序。

DELETE FROM 表名 [WHERE 过滤条件] ORDER BY 排序列 [ASC|DESC] LIMIT 限制条数;

例如:

DELETE FROM test_table ORDER BY first_column DESC LIMIT 1;

 

4、更新数据

有时候,我们对一些记录不满意的时候,我们需要修改他们,修改记录的语法如下:

UPDATE 表名 SET 列1=值1,列2=值2,...,列n=值n [WHERE 表达式];
  • 我们在update后面指定要更新的表,然后把要更新的列的名称和该列更新后的值写到set的后面;
  • 如果想要更新多个列的话,他们直接用逗号隔开; 
  • 如果不指定where子句,那么表达式中所有记录都会被更新,否则只有符合where子句中条件的记录才会更新。

样例:

UPDATE test_table SET first_column = 5,second_column='hhhhggg' WHERE first_column IS NULL;

注意,判断某个表达式的值是否为NULL时,请使用IS NULL,不要使用= 

同理,和删除记录一样,虽然更新语句的where子句是可选的,但是如果不加where子句,将会更新表中所有的记录,这可就是要完蛋的操作,十分的冒险,请慎重而行!!!

另外,这个地方也可以使用limit子句来限制想要更新的记录数量,使用order by子句来指定符合条件的记录的更新顺序。

模板:

UPDATE 表名 SET1=1,列2=2,...,列n=值n ORDER BY 排序列 [ASC|DESC] LIMIT 限制条数;

 样例:

UPDATE test_table SET second_column='娃哈哈' ORDER BY first_column DESC LIMIT 1;

 

MySQL基础(七)-----并集查询和数据的插入、删除、更新。就分享到这个地方,后续会更新MySQL基础(八)-----视图和存储程序。

今天的分享就到此结束了,如果觉得对您有帮助,麻烦给个三连!

以上内容为本人的经验总结和平时操作的笔记。若有错误和重复请联系作者删除!!感谢支持!!