MySQL基础笔记(3) - 子查询,联结查询&正则表达式

子查询,联结查询&正则表达式

目录

• 子查询,联结查询&正则表达式
  • • 1. 子查询
    • 2. 联结查询
    • 3. 正则表达式

1. 子查询

定义：出现在其它语句中的select语句，称为子查询或内查询；而外部的查询语句，称为主查询或外查询。子查询并不常用，它的效率较低，而且不如联结查询来得简洁快速，常常作为联结查询的下位替代出现。

#查询每个客户的订单数量
SELECT cust_name,cust_state,(SELECT COUNT(*)
			     FROM orders
			     WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

#需要列出订购物品TNT2的所有客户
SELECT cust_name,cust_contact #3.检索前一步返回的所有客户ID的客户信息
FROM customers
WHERE cust_id IN (SELECT cust_id #2.检索具有前一步骤列出的订单编号的所有客户ID
		  FROM orders
		  WHERE order_num IN (SELECT order_num #1.检索包含物品TNT2的所有订单编号
				      FROM orderitems
				      WHERE prod_id = 'TNT2'));

#exists与子查询
#exists相当于Java中的isXxx方法,返回一个Boolean值(1/0)表示对象存不存在
SELECT EXISTS(SELECT prod_name 
	      FROM products
	      WHERE prod_price=131.00) AS EXIST;

2. 联结查询

联结查询有两种：

• 内联结(inner)：等值联结，非等值联结，自联结
• 外联结：左外联结(left)，右外联结(right)

#1.等值联结:等值联结的结果为多表的交集部分

#假设生产商信息和产品信息分别储存在两张表中
#查询生产商，产品名，产品价格
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.`vend_id` = products.`vend_id`
ORDER BY vend_name,prod_name;
#对于在两表中同名的列，需要在前面额外指定是哪个表
#sql99写法
SELECT vend_name,prod_name,prod_price
FROM vendors
INNER JOIN products
ON vendors.`vend_id` = products.`vend_id`
ORDER BY vend_name,prod_name;

#在使用别名时要额外注意，如果为表起了别名，则查询的字段就不能使用原来的表名去限定
SELECT c.cust_name,c.cust_contact #这里不能使用customers.cust_name
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_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;
#sql99
SELECT prod_name,vend_name,prod_price,quantity
FROM vendors
INNER JOIN products
ON products.`vend_id` = vendors.`vend_id`
INNER JOIN orderitems
ON orderitems.`prod_id` = products.`prod_id`
WHERE order_num = 20005;


#2.非等值联结:涉及多个表,但筛选条件不使用'='
#(一个没什么意义的例子)
SELECT p.prod_price,v.vend_id
FROM products p,vendors v
WHERE p.prod_price BETWEEN v.`vend_id` AND v.`vend_zip`;
#sql99写法
SELECT p.prod_price,v.vend_id
FROM products p
INNER JOIN vendors v
ON p.prod_price BETWEEN v.`vend_id` AND v.`vend_zip`;


#3.自联结:只涉及一个表，但需要多次访问自身
#查询ID为DTNTR的商品的生产商生产的其它所有产品
#子查询写法
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');
#从子查询的角度更好理解，这里查询了表两次，第一次查询了产品id为'DTNTR'的生产商id，第二次根据生产商id筛选出符合条件的产品名和产品id。单次的查询操作无法做到，所以这里用了两次。自联结就相当于把相同的一张表联结两次，从而以一次查询达到两次查询的效果。
#spl92写法
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';
#sql99写法
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e
INNER JOIN employees m
ON e.'manager_id' = m.'employee_id';


#4.左/右外联结
#左外联结:left join左边的是主表
#右外联结:right join右边的是主表
#检索所有客户，包括没有订单的客户
SELECT customers.`cust_id`,orders.`order_num`
FROM customers LEFT OUTER JOIN orders
ON customers.`cust_id` = orders.`cust_id`;
/*	
	外联结的查询结果为主表中的所有记录,
		若从表中有匹配的记录,则显示对应的值
		若从表中没有匹配的记录,则显示null
	这点与等值联结不同,等值联结不会显示没有匹配的记录
*/	

3. 正则表达式

• 基本字符匹配

  正则表达式关键字，REGEXP和Mysql中的LIKE关键字很像，但是REGEXP的应用范围更广，而且会返回值

  #查询名称结尾带有'000'的产品
  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '.000'
  ORDER BY prod_name;
  #这里的'.'类似于mysql中的'_'表示任意字符，但没有'_'严格
  

• OR匹配

  '|'关键字和Mysql中的OR类似，多个OR语句可以并入正则表达式

  #查询名称中带有1000或2000的产品
  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '1000|2000'
  ORDER BY prod_name;
  

• 匹配几个字符之一

  如果想要匹配特定的字符，可以使用[]。比如[123]和[1|2|3]等效，由[]圈定选择的生效范围

  #查询名称中带有"1 Ton"或"2 Ton"或"3 Ton"的产品
  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '[123] Ton'
  ORDER BY prod_name;
  #如果想要取补集，可以加^符号。比如[^123] Ton
  

• 匹配范围

  如果一组数值/字符时连续且有序的，可以使用[?-?]来界定范围。比如[abcdefghi]可以简写成[a-i]

  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '[1-5] Ton'
  ORDER BY prod_name;
  

• 匹配特殊字符

  对于诸如'.'或'-'这样的具有特殊意义的字符，如果要对它们进行匹配，一般需要在前面加上\\

  SELECT vend_name
  FROM vendors
  WHERE vend_name REGEXP '\\.'
  ORDER BY vend_name;
  

• 匹配字符类

  有时候可以使用一些预定义的字符集(字符类)，来方便查询工作

  类	说明
  [:alnum:]	任意字母和数字
  [:alpha:]	任意字符
  [:blank:]	空格和制表符
  [:cntrl:]	ASCII控制字符(0~31,127)
  [:digit:]	任意数字
  [:print:]	任意可打印字符
  [:graph:]	与[:print:]相同，但不包括空格
  [:lower:]	任意小写字母
  [:punct:]	既不在[:alnum:]也不在[:cntrl:]中的任意字符
  [:space:]	包括空格在内的任意空白字符
  [:upper:]	任意大写字母
  [:xdigit:]	任意十六进制数字

• 匹配多个实例

  为了加强对匹配的数目的控制，可以使用重复元字符。

  元字符	说明
  *	0个或多个匹配
  +	1个或多个匹配
  ？	0个或一个匹配
  	指定数目的匹配
  	不少于指定数目的匹配
  	匹配数目的范围

  重复元字符的使用比较复杂，配合举例尝试理解

  #查询名称中带有([0~9] stick)或([0~9] sticks)的产品
  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
  ORDER BY prod_name;
  #这里的?标记的是前面的字符s，表示与s由0个或1个匹配，即存在一个s或不存在s
  
  #查询名称中包含任意4个数字的产品
  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '[[:digit:]]{4}'
  ORDER BY prod_name;
  #{4}要求它前面的字符出现4次，所以[[:digit:]]{4}匹配连在一起的任意4个数字
  

• 定位符

常规的匹配都是匹配任意位置，为了匹配特定位置的文本，需要使用定位符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

#查询名称以一个数或小数点开始的所有产品
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;