MySQL必知必会笔记——查询的进阶知识

查询进阶知识

第十五章 联结表

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作。

例如：两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商应具有唯一的标识，称为主键（primary key）。products表只存储产品信息，除了存储供应商ID之外不存储其他的供应商信息。vendors表的主键又叫products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

# 创建联结，两个表用WHERE子句联结
SELECT  vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

笛卡儿积：由没有连结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

# 笛卡尔积
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;

内部连结

等值联结：基于两个表之间的相等测试，也叫内部连结

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

• 这个结果等效于上面的创建联结的sql
• 推荐使用这种语法，更加明确。可确保不会忘记联结条件，有时候这样做也能影响性能。

联结多个表

-- 显示编号为20005的订单中的物品。订单物品存储在orderitems表中，按每个产品的ID存储。
它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;

不要联结不必要的表。联结的表越多，性能下降越厉害。

下面的一个嵌套查询sql语句

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
				  FROM orders
                  WHERE order_num IN (SELECT order_num
									  FROM orderitems
                                      WHERE prod_id = 'TNT2'));

等效于：

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num
  AND prod_id = 'TNT2';

关于外键的补充：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中列的值！

1.创建表的过程中创建外键

create table team(
id int primary key auto_increment,
name varchar(40)
);

create table star(
id int ,
name varchar(40),
team_id int,
foreign key (team_id) references team(id)
);

外键语法：FOREIGN KEY(ordersid) references orders(id)

insert into team values(null,‘Toronto Raptors’),(null,‘Milwaukee Bucks’),(null,‘Boston Celtics’),(null,‘Golden State Warriors’),(null,‘Oklahoma City Thunder’),(null,‘Dallas Mavericks’);

insert into star values(2,‘科怀-伦纳德’,1),(7,‘洛瑞’,1),(34,‘阿德托昆博’,2),(22,‘米德尔顿’,2),(11,‘欧文’,3),(20,‘海沃德’,3),(35,‘杜兰特’,4),(30,‘库里’,4),(0,‘威斯布鲁克’,5),(13,‘保罗-乔治’,5),(77,‘卢克-东契奇’,6),(41,‘诺维斯基’,6);

2.表已存在，增加外键

语法：ALTER TABLE yourSecondTableName ADD CONSTRAINT yourConstraintname FOREIGN KEY(yourForeignKeyColumnName) REFERENCES yourFirstTableName (yourPrimaryKeyColumnName);

示例：

ALTER TABLE orders
ADD CONSTRAINT fk_irderitems_customers
FOREIGN KEY (cust_id) REFERENCES customers(cust_id);

3.删除外键
alter table 表名 drop foreign key 外键名;

4.测试

我们用1创建的两张表来做示例，插入一条数据

insert into star VALUES(99, "test01", 6);

这条语句可以执行成功，但如果我们插入下面这条语句：

insert into star VALUES(100, "test02", 7);

系统会报错：

Cannot add or update a child row: a foreign key constraint fails (`test`.`star`, CONSTRAINT `star_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`))

即：无法添加或更新子行:外键约束失败。因为父表team中的id没有7，这里添加队伍id为7就会失败。

我们再执行以下的语句：

delete from star where team_id = 6;

我们可以把star表中的相应数据删掉。但是在执行下面的语句时会报错：

delete from team where id = 5;

-- 报错信息：
-- Cannot delete or update a parent row: a foreign key constraint fails (`test`.`star`, CONSTRAINT `star_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`))

这是因为team表的id与star表team_id相关，删掉team表id为5的行，但是star表中仍然有数据时team_id = 5的，此时就会报错。但是因为我们之前删了star表中team_id为6的行，所以我们再删除team表id=6的行就不会出错了。

delete from team where id = 6;

在操作有外键关联的表时，删除父表的数据要先删除子表相关联的数据才能删除成功。

第十六章 创建高级联结

SELECT Concat(RTrim(vend_name),'('Rtrim(vend_country),')') AS vend_title
FROM vendors ORDER BY vend_name;

别名除了用于列名和计算字段外，SQL还允许给表起别名，这样做有两个主要理由：

• 缩短SQL语句
• 允许在单条SELECT语句中多次使用相同的表。

例：

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';

使用不同类型的联结

自联结

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

-- 子查询
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

-- 自联结
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（内部联结）返回所有数据，甚至相同的列多次出现，自然联结排除多次出现，使每个列只返回一次。

自然联结是这样一种联结，其中你只能选择那些唯一的列，这一版是通过使用通配符，对所有其他表的列使用明确的字集来完成的。

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';

外部联结

-- 内部联结
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

-- 外部联结
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

外部联结语法类似。可以检索所有客户，包括没有订单的客户。

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表。（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表。）上面的例子中从customers中选择所有的行。

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

使用带聚集函数的联结

-- 检索所有客户及每个客户所下的订单数
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

聚集函数也可以方便地与其他联结一起使用。例：

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。

使用联结和联结条件

• 注意所使用的联结类型，一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡尔积。
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。应该在一起测试他们前分别测试每个联结。

关于联结的补充

第十七章 组合查询

创建组合查询

使用UNION

#单条语句
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;

SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

#组合上述语句
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);

#等于
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001, 1002);

UNION规则

• UNION 必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION中的每个查询必须包含先沟通的列、表达式或聚集函数
• 列数据类型必须兼容：类型不必完全向东，但必须是DBMS可以隐含地转换的类型

包含或取消重复的行

UNION从查询结果集中自动去除了重复的行，如果需要返回所有行，可以使用UNION ALL

对组合查询结果排序

使用ORDER BY子句排序，只能使用一条ORDER BY子句，必须在最后一条SELECT语句之后。

第十八章 全文本搜索

略