文章目录
01. 数据准备02. 检索数据03. 排序检索数据04. 过滤数据05. 数据过滤06. 用通配符进行过滤 like07. 使用正则表达式进行搜索 regexp08. 计算字段09. 聚合函数10. 分组函数11. 使用子查询12. 联表查询01. 数据准备
1、创建数据库表：
######################### Create customers table########################CREATE TABLE customers(cust_idint NOT NULL AUTO_INCREMENT,cust_name char(50) NOT NULL ,cust_address char(50) NULL ,cust_city char(50) NULL ,cust_state char(5) NULL ,cust_zipchar(10) NULL ,cust_country char(50) NULL ,cust_contact char(50) NULL ,cust_email char(255) NULL ,PRIMARY KEY (cust_id)) ENGINE=InnoDB;########################## Create orderitems table#########################CREATE TABLE orderitems(order_num intNOT NULL ,order_item intNOT NULL ,prod_id char(10)NOT NULL ,quantity intNOT NULL ,item_price decimal(8,2) NOT NULL ,PRIMARY KEY (order_num, order_item)) ENGINE=InnoDB;###################### Create orders table#####################CREATE TABLE orders(order_num intNOT NULL AUTO_INCREMENT,order_date datetime NOT NULL ,cust_id intNOT NULL ,PRIMARY KEY (order_num)) ENGINE=InnoDB;######################## Create products table#######################CREATE TABLE products(prod_id char(10)NOT NULL,vend_id int NOT NULL ,prod_name char(255)NOT NULL ,prod_price decimal(8,2) NOT NULL ,prod_desc textNULL ,PRIMARY KEY(prod_id)) ENGINE=InnoDB;####################### Create vendors table######################CREATE TABLE vendors(vend_idintNOT NULL AUTO_INCREMENT,vend_name char(50) NOT NULL ,vend_address char(50) NULL ,vend_city char(50) NULL ,vend_state char(5) NULL ,vend_zipchar(10) NULL ,vend_country char(50) NULL ,PRIMARY KEY (vend_id)) ENGINE=InnoDB;############################ Create productnotes table###########################CREATE TABLE productnotes(note_id int NOT NULL AUTO_INCREMENT,prod_id char(10)NOT NULL,note_date datetime NOT NULL,note_text textNULL ,PRIMARY KEY(note_id),FULLTEXT(note_text)) ENGINE=MyISAM;
2、添加数据：
########################### Populate customers table##########################INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');######################### Populate vendors table########################INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');########################## Populate products table#########################INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');######################## Populate orders table#######################INSERT INTO orders(order_num, order_date, cust_id)VALUES(20005, '-09-01', 10001);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20006, '-09-12', 10003);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20007, '-09-30', 10004);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20008, '-10-03', 10005);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20009, '-10-08', 10001);############################ Populate orderitems table###########################INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 1, 'ANV01', 10, 5.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 2, 'ANV02', 3, 9.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 3, 'TNT2', 5, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20005, 4, 'FB', 1, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20006, 1, 'JP2000', 1, 55);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20007, 1, 'TNT2', 100, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20008, 1, 'FC', 50, 2.50);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 1, 'FB', 1, 10);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 2, 'OL1', 1, 8.99);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 3, 'SLING', 1, 4.49);INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)VALUES(20009, 4, 'ANV03', 1, 14.99);############################## Populate productnotes table#############################INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(101, 'TNT2', '-08-17','Customer complaint:Sticks not individually wrapped, too easy to mistakenly detonate all at once.Recommend individual wrapping.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(102, 'OL1', '-08-18','Can shipped full, refills not available.Need to order new can if refill needed.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(103, 'SAFE', '-08-18','Safe is combination locked, combination not provided with safe.This is rarely a problem as safes are typically blown up or dropped by customers.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(104, 'FC', '-08-19','Quantity varies, sold by the sack load.All guaranteed to be bright and orange, and suitable for use as rabbit bait.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(105, 'TNT2', '-08-20','Included fuses are short and have been known to detonate too quickly for some customers.Longer fuses are available (item FU1) and should be recommended.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(106, 'TNT2', '-08-22','Matches not included, recommend purchase of matches or detonator (item DTNTR).');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(107, 'SAFE', '-08-23','Please note that no returns will be accepted if safe opened using explosives.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(108, 'ANV01', '-08-25','Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(109, 'ANV03', '-09-01','Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(110, 'FC', '-09-01','Customer complaint: rabbit has been able to detect trap, food apparently less effective now.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(111, 'SLING', '-09-02','Shipped unassembled, requires common tools (including oversized hammer).');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(112, 'SAFE', '-09-02','Customer complaint:Circular hole in safe floor can apparently be easily cut with handsaw.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(113, 'ANV01', '-09-05','Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(114, 'SAFE', '-09-07','Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be ment forwarded to vendor.');
02. 检索数据
1、检索单个列
mysql> select prod_name from products;
注意：SQL语句不区分大小写
2、检索多个列
mysql> select prod_id,prod_name from products;
3、检索所有列
mysql> select * from products;
4、检索不同的行 distinct
mysql> select distinct vend_id from products;
5、限制结果 limit
# 只有limit时，相当于从第一行开始的 mysql> select prod_name from products limit 5;
# 第1个数为开始位置，第2个数为要检索的行数# 返回结果为：从行5开始的5行（但是不加第5行）！！！mysql> select prod_name from products limit 5,5;
MySQL 5的LIMIT语法 LIMIT 3, 4的含义是从行4开始的3行还是从行3开始的4行？如前所述，它的意思是从行3开始的4行，这容易把人搞糊涂。由于这个原因，MySQL 5支持LIMIT的另一种替代语法。LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样。
# limit 代表要检索的行数# offset 代表行的开始位置，但是不包括offset这一行mysql> select prod_name from products limit 5 offset 5;
6、使用完全限定的表名
mysql> select products.prod_name from products;
03. 排序检索数据
1、排序数据
# 对prod_name列以字母顺序排序mysql> select prod_name from products order by prod_name;
注意：通过非选择列进行排序 通常，ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。
2、按多个列排序
# 在多个行具有相同的prod_price时才会对prod_name排序# 如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。 mysql> select prod_id,prod_name,prod_price from products order by prod_price,prod_name;
3、指定排序方向
数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。
mysql> select prod_name,prod_price from products order by prod_price desc;
对多个列排序，同时指定排序方向 ：
DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序。
注意：在多个列上降序排序 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。
4、查询列中的Top值：order by + limit
mysql> select prod_name,prod_price from products order by prod_price desc limit 3;
04. 过滤数据
1、使用where子句
mysql> select prod_name,prod_price from products where prod_price=55;
2、where子句操作符
(1) 检索单个值
mysql> select prod_name from products where prod_name='JetPack 2000';
mysql> select prod_name,prod_price from products where prod_price < 20;
(2) 不匹配查询
mysql> select vend_id from products where vend_id<>1003;
mysql> select vend_id from products where vend_id !=1003;
(3) 范围检查
mysql> select prod_name,prod_price from products where prod_price between 5 and 10;
在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。
(4) 空值检查
NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
mysql> select prod_price from products where prod_price is null;
05. 数据过滤
1、and操作符
AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
select prod_id,prod_name,prod_price,vend_id from products where vend_id=1001 and prod_price<10;
2、or操作符
AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
mysql> select prod_name,vend_id from products where vend_id=1001 or vend_id=1003;
3、计算次序
WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。
例：列出价格为10美元（含）以上且由1002或1003制造的所有产品。
返回结果中含有价格小于10美元的行，说明没有按照预期的结果过滤，因为and的优先级比or的优先级更高。在处理or操作符之前优先处理了and操作符，因此需要加入圆括号，因为操作符优先级：圆括号>and>or。
select prod_name,prod_price,vend_id from products where (vend_id=1002 or vend_id=1003) and prod_price>=10;
4、in操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。
mysql> select vend_id,prod_name from products where vend_id in (1002,1003);
mysql> select vend_id,prod_name from products where vend_id=1002 or vend_id=1003;
为什么要使用IN操作符？其优点具体如下：
在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。IN操作符一般比OR操作符清单执行更快。IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
6、not操作符
WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。NOT WHERE子句中用来否定后跟条件的关键字。
mysql> select prod_name,prod_price from products where vend_id not in (1002,1003);
06. 用通配符进行过滤 like
(1) 百分号%
最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。
mysql> select prod_id,prod_name from products where prod_name like 'jet%';
%告诉MySQL接受jet之后的任意字符，不管它有多少字符。
mysql> select prod_id,prod_name from products where prod_name like '%anvil%';
搜索模式’%anvil%’表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。
注意：除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。
(2) 下划线_
下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。
mysql> select prod_name from products where prod_name like '_ ton anvil';
与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。
07. 使用正则表达式进行搜索 regexp
1、基本字符匹配
例：索列prod_name包含文本1000的所有行
mysql> select prod_name from products where prod_name regexp '1000';
2、进行or匹配
例：搜索两个串之一
mysql> select prod_name from products where prod_name regexp '1000|2000';
3、匹配几个字符之一[]
mysql> select prod_name from products where prod_name regexp '[123] ton anvil';
使用了正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（没有3 ton）。正如所见，[]是另一种形式的OR语句。事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以使用后者。
4、匹配范围
mysql> select prod_name from products where prod_name regexp '[1-5] ton anvil';
08. 计算字段
1、拼接字段
mysql> select concat(vend_name,'(',vend_country,')') from vendors ;
Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。
2、执行算数计算
mysql> select prod_id,prod_price,prod_price+10 price_add_10 from products ;
09. 聚合函数
1、avg()函数
AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
# 计算所有行的平均值mysql> select avg(prod_price) avg_price from products ;
# 返回特定行的平均值mysql> select vend_id, avg(prod_price) avg_price from products where vend_id=1003;
注意：avg()函数会忽略列值为null的行。
2、count()函数
COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。COUNT()函数有两种使用方式：
使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
mysql> select count(*) as count from customers;
mysql> select count(cust_email) from customers;
如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略。
3、max()函数
MAX()返回指定列中的最大值。MAX()要求指定列名。
mysql> select max(prod_price) max_price from products;
4、min()函数
mysql> select min(prod_price) min_price from products;
5、sum()函数
mysql> select sum(prod_price) sum_price from products;
mysql> select sum(prod_price) sum_price, sum(prod_price+10) sum_price_add from products;
6、聚集不同值
# 将prod_price去重后再求平均值mysql> select avg(distinct prod_price) avg_distinct_price from products;
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。
7、组合聚集函数
select count(*) count,min(prod_price) min_price,max(prod_price) max_price,avg(prod_price) avg_price from products;
10. 分组函数
1、创建分组
mysql> select vend_id,count(*) count from products group by vend_id;
GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算count一次。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
注意：SELECT语句中的每个列都必须在GROUP BY子句中给出。
2、过滤分组
WHERE过滤行，而HAVING过滤分组。HAVING支持所有WHERE操作符 。having过滤是基于分组聚集值而不是特定行值的。
注意：！！！HAVING和WHERE的差别，这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
# 一旦分组后，聚集函数计算的就不是所有行的数据了，而是分组后，组内的数据mysql> select vend_id,count(*) count from products group by vend_id having count(*)>=3;
对比结果可以发现：having用于对分组后的组进行过滤，用在group by 之后。
# 先使用where过滤，再对过滤后的数据按照vned_id分组，分组后再计算count(*),最后使用having对组进行过滤select vend_id,count(*) count from products where prod_price>=10 group by vend_id having count(*)>=2;
3、分组和排序
order by任意列都可以使用，但是group by只可能使用选择列或者表达式，而且必须使用每个选择列或表达式。
group by放在order by 之前，放在where之后。
mysql> select vend_id,count(*) count from products group by vend_id order by vend_id;
4、select子句的顺序和执行顺序
select 6from 1where 2group by 3having 4order by 7limit5
11. 使用子查询
SELECT语句是SQL的查询。迄今为止我们所看到的所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句。SQL还允许创建子查询，即嵌套在其他查询中的查询。
1、利用子查询过滤数据
表的关联关系：orders.cust_id = customers.cust_idorders.order_num = orderitems.order_num
例：列出订购商品TNT2的所有客户 （where后面嵌套子查询）
(1) 检索包含商品TNT2的所有订单编号
mysql> select order_num from orderitems where prod_id='TNT2';
(2) 列出前一步骤列出的订单编号的所有客户id
select order_num, cust_id from orders where order_num in (select order_num from orderitems where prod_id='TNT2');
(3) 检索前一步骤列出的所有客户id的客户信息
select * from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id='TNT2'));
2、作为计算字段使用子查询 （select后面嵌套子查询）
例：检索customers表中每个客户的订单总数
# 这题较难理解# orders是一个计算字段，是由圆括号内的子查询建立的# 该子查询对检索出的每个客户计算一次！！！！！# 所以每次拿到的customers都是查询出来的，然后再计算customers.cust_id# 先计算from再计算select，这个也是mysql的执行顺序！！！select cust_name,(select count(*) from orders where orders.cust_id=customers.cust_id) orders from customers;
注意和下面的区别：必须指明有歧义的列名，否则就是orders.cust_id=orders.cust_id
select cust_name,(select count(*) from orders where cust_id=cust_id) orders from customers;
12. 联表查询
1、内部连接（等值连接）
select products.prod_id,products.vend_idfrom products join vendors on products.vend_id=vendors.vend_id;
