准备工作
SQL
MySql
使用MySql
查找数据（select基本用法）
排序检索数据(order by子句）
过滤数据（where子句）
通配符
- %
- _
正则表达式
计算字段
- 拼接（concat函数）
- 算数运算
函数
数据汇总
- 聚集函数
count 函数不会统计NULL值
数据分组
- 分组语句
- select子句顺序
子查询
联结表

准备工作

mysql 语句

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################
########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       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_zip     char(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  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT 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  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT 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_zip     char(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  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;
#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################
##########################
# 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@coyote.com');
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@wascally.com');
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@yosemite.com');
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, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-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', '2005-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', '2005-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', '2005-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', '2005-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', '2005-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', '2005-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', '2005-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', '2005-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', '2005-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', '2005-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', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-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', '2005-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', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

SQL

SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言。

MySql

MySQL是一个客户机-服务器DBMS，因此，为了使用MySQL，需要有一个客户机，即你需要用来与MySQL打交道(给MySQL提供要执行的命令）的一个应用。

使用MySql

连接

连接MySql需要以下4个条件

主机名(计算机名)——如果连接到本地MySQL服务器,为localhost;
端口（如果使用默认端口3306之外的端口);
一个合法的用户名;
口用户口令（如果需要)。

命令行： mysql [-h主机号 -P端口号] -u用户名 -p密码

选择数据库

例如，为了使用crashcourse数据库，应该输入以下内容:
输入

USE crashcourse;

输出

Database changed

查看数据库和表

show databases;//查看数据库
show tables;//查看表
show columns from tableName;//显示表结构
describe tableName;//显示表结构（为上句的快捷方式）
show create database tmp;//显示建数据库tmp的sql语句
show create table person;//显示建表person的sql语句
show stasus;//显示广泛的服务器状态信息
show grants;//用来显示授予用户(所有用户或特定用户）的安全权限;
SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。

MySql常用的SHOW语句：https://www.cnblogs.com/zhaoshujie/p/10422396.html

查找数据（select基本用法）

为了使用SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。

查找单列

SELECT prod_name 
FROM products;

查找多列

SELECT prod_id,prod_name,prod_price
FROM products;

查找所有列

SELECT *
FROM products;

使用通配符
一般，除非你确实需要表中的每个列,否则最好别使用*通配符。
虽然使用通配符可能会使你自己省事，不用明确列出所需列,但检索不需要的列通常会降低检索和应用程序的性能。

检索不同行（distinct)

select  vend_id 
from products;//未去重
select distinct vend_id 
from products;//去重

不能部分使用DISTINCT
DISTINCT关键字应用于所有列而不仅是前置它的列。
如果给出SELECT DISTINCT vend_ id,prod_price,除非指定的两个列都不同,否则所有行都将被检索出来。

# 错误sql语句
select vend_id, distinct prod_price 
from products;
# 正确sql语句
select distinct vend_id,  prod_price 
from products;

限制结果

SELECT语句返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句。

select  prod_name 
from products
limit 5;

返回特定位置的结果，可以使用offset子句，跳过多少条语句。

select  prod_name 
from products
limit 5 offset 5;

排序检索数据(order by子句）

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
子句(clause)
SQL语句由子句构成，有些子句是必需的，而有的是可选的。
一个子句通常由一个关键字和所提供的数据组成。
子句的例子有SELECT语句的FROM子句。

单列排序

select prod_name
from products
order by prod_name;

多列排序

下面的代码检索3个列，并按其中两个列对结果进行排序—首先按价格，然后再按名称排序。

select prod_id,prod_price,prod_name
from products
order by prod_price,prod_name;

排序方向（升序，降序）

下面的例子按价格以降序排序产品（最贵的排在最前面）

select prod_id,prod_price,prod_name
from products
order by prod_price desc;

DESC关键字只应用到直接位于其前面的列名。只对prod_price列指定DESc，对prod_name列不指定。
因此，prod_price列以降序排序，而prod_name列（在每个价格内)仍然按标准的升序排序。

select prod_id,prod_price,prod_name
from products
order by prod_price desc ,prod_name asc;

过滤数据（where子句）

数据库表一般包含大量的数据，很少需要检索表中所有行。
通常只会根据特定操作或报告的需要提取表数据的子集。
只检索所需数据需要指定搜索条件（ search criteria )，搜索条件也称为过滤条件（ filtercondition )。

Where子句操作符

WHERE子句的位置
在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后,否则将会产生错误。

# 等于过滤
select prod_name,prod_price
from products
where  prod_name='fuses';
# 不等于过滤
select prod_name,prod_price
from products
where  prod_name<>'fuses';
# 范围过滤
select prod_name,prod_price
from products
where prod_price between 5 and 10;
# NULL值过滤
select cust_id
from customers
where cust_email is null ;
# 非NULL值过滤
select cust_id
from customers
where cust_email is not  null ;

Where子句组合

and

此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。
这条SELECT语句中的WHERE子句包含两个条件，并且用AND关键字联结它们。

select prod_id,prod_price,prod_name
from products
where vend_id=1003 and prod_price<=10;

or
```
select prod_name,prod_price
from products
where vend_id=1002 or vend_id=1003;
```
此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。
OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。
and 和 or组合

AND在计算次序中优先级更高

# 默认先and 后 or
select prod_name,prod_price
from products
where vend_id=1002 or vend_id =1003 and prod_price>=10;
# 可以使用括号改变计算顺序
select prod_name,prod_price
from products
where (vend_id=1002 or vend_id =1003) and prod_price>=10;

in操作符

in操作符用于指定条件的范围。

select prod_name,prod_price
from products
where (vend_id=1002 or vend_id =1003) and prod_price>=10;

通配符

使用like操作符

%

匹配任意字符任意次
最常使用的通配符是百分号(%)。在搜索串中，%表示任何字符出现任意次数。
例如，为了找出所有以词jet起头的产品，可使用以下SELECT

select prod_id,prod_name
from products
where prod_name like 'jet%';

_

匹配任意字符一次

select prod_id,prod_name
from products
where prod_name like '_ ton anvil';

正则表达式

# 匹配产品名称中有1000的数据
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;
# .代表匹配任意字符一次
select prod_name
from products
where prod_name regexp '.000'
order by prod_name;
# |代表或者，匹配1000或者2000
select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;
# |代表或者，匹配1000或者2000
select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;
# []代表选择其中一个进行匹配
select prod_name
from products
where prod_name regexp '[123] ton'
order by prod_name;
#[1-5]表示匹配 1-5
select prod_name
from products
where prod_name regexp '[1-5] ton'
order by prod_name;

转义字符：
为了匹配特殊字符，必须用\l为前导。\-表示查找-，\.表示查找.

计算字段

对数据库的数据进行简单计算得到的数据。

拼接（concat函数）

select concat(vend_name,'(',vend_country,')') as msg
from vendors
order by vend_name;

修剪函数

Trim 去掉两边空格
LTrim 去掉左边空格
RTrim 去掉右边空格

别名：as 语句

select concat(vend_name,'(',vend_country,')') as vend_title
from vendors
order by vend_name;

算数运算

select prod_id,quantity,item_price,quantity*item_price as expanded_price
from orderitems
where order_num=20005;

函数

文本处理函数

Upper函数（转大写）

select vend_name ,Upper(vend_name) as vend_name_upcase
from vendors
order by vend_name;

lower函数（转小写）

select vend_name ,lower(vend_name) as vend_name_upcase
from vendors
order by vend_name;

Left函数（返回左边字符）

# LEFT(str,length);
select left('abcd',2);

length函数（返回字符串长度）
```
select length('abcd');
```
locate函数（匹配子串）
```
# locate(subStr,string)
select locate('cd','abcd')
```
日期和时间处理函数

数据结构和类型
日期格式
- Date 存储数据格式为：YYYY-MM-DD，它支持的范围为’1000-01-01’到’9999-12-31’
- DateTime 日期和时间的组合，存储格式为：YYYY-MM-DD HH:MM:SS，它支持的范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’
- Timestamp 时间戳，从1970-01-01 00:00:00到当前的时间差值
时间类型
- Time

MySQL中日期和时间类型

# 日期比较  该语句无法匹配dataTime类型的 2005-09-01 12:00 
select cust_id,order_num
from orders
where order_date='2005-09-01';
# 日期比较  该语句可以匹配dataTime类型的 2005-09-01 12:00 
select cust_id,order_num
from orders
where date(order_date)='2005-09-01';

# 匹配特定月份的数据
# 方案1 使用between操作符
select cust_id,order_num
from orders
where date(order_date) between '2005-09-01' and '2005-09-30';
# 方案2 使用year和month函数
select cust_id,order_num
from orders
where year(order_date)=2005 and month(order_date)=9;

数值处理函数

数据汇总

聚集函数

Avg函数（平均值）

use tmp;
select avg(prod_price) as avg_price
from products;

count函数（计数） ```sql SELECT COUNT(*) AS num_cust FROM customers;

count 函数不会统计NULL值

SELECT COUNT(cust_email) AS num_cust from customers;


- **MAX函数（最大值)**
```sql
select max(prod_price) as max_prod_price
from products;

Min函数（最小值）

select min(prod_price) as min_prod_price
from products;

sum函数（求和）

select sum(quantity) as sum_quantity
from orderitems
where order_num=20005;

不同的值（distinct)

# 统计不同产品价格的平均值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

数据分组

分组语句

Group语句

# 查询不同供应商的产品总数
select vend_id,count(*) as num_prods
from products
group by vend_id;

having(过滤分组）

select cust_id,count(*) as orders
from orders
group by cust_id
having count(*)>=2;

HAVING和WHERE的差别
这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

# 具有2个（含）以上、价格为10（含）以上的产品的供应商
select vend_id,count(*) as num_prods
from products
where prod_price >=10
group by vend_id
having count(*)>=2;

select order_num,sum(item_price*quantity) as total_price
from orderItems
group by order_num
having sum(item_price*quantity)>50
order by total_price;

select子句顺序

子查询

如何列出订购物品TNT2的所有客户？（orders，orderItems表）

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'));
select  cust_id
from orders left join orderItems on orders.order_num=orderItems.order_num
where  orderItems.prod_id='tnt2';

要显示customers表中每个客户的订单总数（order,customers表）

select cust_name,cust_state,
        (select count(*)
            from orders
            where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name;
select cust_name,cust_state,count(orders.order_num)
from customers left join orders on customers.cust_id=orders.cust_id
group by cust_name
order by cust_name;

MySql必知必会