MySQL必知必会17~20
组合查询(UNION)
执行多个查询(多个SELECT语句),并将结果作为单个查询结果集返回,这些组合查询通常称为并(UNION)或复合查询。
有两种基本情况,其中需要使用组合查询(UNION):
●在单个查询中从不同的表返回类似结构的数据
●对单个表执行多个查询,按单个查询返回数据
现在思考一下,为什么要使用UNION?使用多个WHERE子句不是也可以达到相同的效果吗?对于这个问题来看下面这个例子:
-- UNION的使用很简单,所需做的只是给出每条SELECT语句,在各条语句之间放上关键字UNION就行。
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5;
UNION -- 这里有一个小知识注意一下,UNION在返回结果时会取消重复的行(只显示一个),如果要全部显示的话可以替换成UNION ALL。
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);
-- 可以发现使用WHERE子句做出相同的查询
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5 OR vend_id IN (1001,1002);
在这里使用UNION可能比使用WHERE子句更加复杂,但对于更复杂的过滤条件,或者从多个表(而不是单个表)中检索数据的情形,使用UNION可能会处理更简单,所以要试一下这两种技术,以确定对特定的查询哪一种更好。简单来说就是数据库版的 “适者生存”。
对组合查询的结果进行排序:
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5;
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);
ORDER BY vend_id,prod_price; -- 使用一个之前学习的排序子句就行
UNION规则:
●UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔(因此,如果组合4条SELECT语句,就要使用3个UNION)。
●UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序出现)。
全文本搜索
为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。在对表列进行适当的设计后,MySQL会自动进行所有的索引和重新索引。
在索引之后,SELECT可与 Match()和Against() 一起使用以实际执行搜索。
1.启用全文本搜索支持:一般在创建表时启用全文本搜索,CREATE TABLE语句接受FULLTEXT子句,它给出被索引列的一个逗号分隔的列表。下面的CREATE语句演示一下FULLTEXT子句的使用:
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE = MyISAM;
-- CREATE TABLE语句定义表productnotes并列出它所包含的列。这些列中有一个note_text,
-- 为了进行全文本搜索,MySQL根据子句FULLTEXT(note_text)的知识对它进行索引。
-- 这里的FULLTEXT索引单个列,如果需要也可以指定多个列。
在定义之后,MySQL自动维护该索引,在增加、更新或删除行时,索引随之自动更新。
2.进行全文本搜索,在索引之后,使用两个函数Match()和Against()执行全文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式。 下面是举一个例子:
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
-- 此SELECT语句检索单个列note_text.由于WHERE子句,一个全文本搜索被执行。Match(note_text)知识MySQL针对指定的列进行搜索
-- Against('rabbit')指定词rabbit作为搜索文本,有几行包含词rabbit,就返回几行。
3.布尔文本搜索,布尔搜索即使没有FULLTEXT索引也可以使用。
下面是IN BOOLEAN MODE的例子:
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); -- 需要指定布尔操作符。其中-he*就是两个布尔操作符中。
-- -ropes*指示MySQL排除包含rope*(任何以rope开始的词,包括ropes)的行。
可以看到两个全文本搜索布尔操作符-和*,-排除一个词,而*是截断操作符(可以想象成用于词尾的一个通配符)。
插入数据
INSERT用来插入(或添加)行到数据库表的。插入可以用这些凡是使用:插入完整的行、插入行的一部分、插入多个行、插入某些查询的结果。
1.插入完整的行,废话不多说,直接上例子:
INSERT INTO customers
VALUES(
'aaa'
'bbb'
'ccc'
'ddd'
NULL
NULL);
没有输出:INSERT语句一般不会产生输出。
这个例子表示的意思是插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出,对每个列必须提供一个值,如果这个列没有值,就使用NULL值填充。
虽然这种语法很简单,但是并不安全,应该尽量避免使用。
编写INSERT语句更安全的方法(当然更加繁琐一些)如下:
INSERT INTO customers(
cust_name.
cust_id,
cust_country,
cust_zip,
cust_address,
cust_email)
VALUES(
'aaa'
'bbb'
'ccc'
'ddd'
NULL
NULL);
这个例子与前一个语句完成完全能相同的工作,但在表名后的括号里明确地给出了列名。在插入行时,MySQL将用VALUES列表中的相应值填入列表中的对应项。 VALUES中的第一个值对应第一个指定的列名,第二个对应第二个,如此反复。
因为提供了列名,VALUES必须以其指定的次序匹配指定的列名,不一定按各个列出现在实际表中的次序。这样做的优点是,即使表的结构发生变化,此INSERT语句仍然能够正确工作。
2.插入多个行,如下所示:
INSERT INTO customers(
cust_name.
cust_id,
cust_country,
cust_zip,
cust_address,
cust_email)
VALUES(
'aaa'
'bbb'
'ccc'
'ddd'
NULL
NULL);
INSERT INTO customers(
cust_name.
cust_id,
cust_country,
cust_zip,
cust_address,
cust_email)
VALUES(
'fff'
'ggg'
'hhh'
'iii'
NULL
NULL);
-- 或者只要每条INSERT语句中的列表名(和次序)相同,可以像下面组合语句
INSERT INTO customers(
cust_name.
cust_id,
cust_country,
cust_zip,
cust_address,
cust_email)
VALUES(
'aaa'
'bbb'
'ccc'
'ddd'
NULL
NULL),
(
'fff'
'ggg'
'hhh'
'iii'
NULL
NULL
);
-- 其中每条INSERT语句有多组值,每组值用一对圆括号括起来,用括号分隔。
3.插入检索出的数据
INSERT也可以将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT,它是由一条INSERT和一条SELECT语句组成的。
INSERT INTO customers(
cust_name.
cust_id,
cust_country,
cust_zip,
cust_address,
cust_email)
SELECT cust_name.
cust_id,
cust_country,
cust_zip,
cust_address,
cust_email
FROM custnew;
这个例子使用INSERT SELECT从custnew中将所有数据导入customers。 SELECT语句从custnew检索出要插入的值,而不是列出它们。
更新和删除数据
1.更新数据,可使用UPDATE语句,可采用两种方式使用UPDATE:更新表中的特定行、更新表中的所有行。
基本的UPDATE由3部分组成:要更新的表,列名和它们的新值,确定要更新行的过滤条件。
UPDATE customers
SET cust_email = '471228501@qq.com'
WHERE cust_id = 10002;
-- 更新多个列
UPDATE customers
SET cust_email = '471228501@qq.com',
cust_name = 'CJ'
WHERE cust_id = 10002;
-- 在更新多个列是,只需要使用单个SET命令每个“列 = 值”之间用逗号分隔即可
-- 为了删除某个列的值,可设置它为NULL
UPDATE customers
SET cust_email = NULL, -- NULL用来去除cust_email列中的值。
cust_name = 'CJ'
WHERE cust_id = 10002;
2.删除数据,使用DELETE语句,可以两种方式使用DELETE:从表中删除特定的行、从表中删除所有行。
DELETE
FROM customers
WHERE cust_id = 10002;
-- DELETE FROM要求指定从中删除数据的表名。WHERE子句过滤要删除的行。此语句只删除客户10002,如果没有WHERE将删除表中的每一个客户。
DELETE不需要列名或通配符,DELETE删除整个行而不是删除列,为了删除指定的列,需要使用UPDATE语句。
好了,这次就到这里吧。