检索数据

⛵️写在前面，sql语句是不区分大小写的，SELECT和select是一样的，但是许多的SQL开发人员习惯对sql关键字大写，而对所有的列和表名使用小写。

select语句

检索单个列

select prod_name from products;

检索多个列

SELECT prod_id , prod_name,prod_price  FROM products;

检索所有列

SELECT * FROM products;

检索不同行

使用DISTINCT关键字使得返回的信息是不同的行信息。

但是你不能这样：

除非两个列相同，否则所有行信息都会被展示出来。

限制结果

SELECT语句返回所有匹配的行。为了返回第一行或者前几行，可使用LIMIT子句

SELECT prod_name  FROM  products  LIMIT 5;

SELECT prod_name  FROM  products  LIMIT 5,5;

🍔LIMIT 5 , 5代表的是从行5开始的5行。

🗡行0：检索出来第一行为行0而不是行1。LIMIT 1，1检索出来的是第二行，而不是第一行。

🎲行不够时，如LIMIT 10,5, 。如果只有13行时，mysql只能返回他能返回的行数了就。

🛴MySQL 5的LIMIT语法。LIMIT 4 OFFSET 3 表示从行3开始取4行。

使用完全限定的表明

这里其实就是指，把列名通过表明限定，甚至把表明通过数据库名来完全限定。

SELECT products.prod_name FROM bizhibihui.products;

排序检索数据 （ORDER BY）

排序数据

🌈子句：之前写到的FROM就算是SELECT的一个子句。为了明确排序顺序所以我们可以使用 ORDER BY子句。

SELECT prod_name FROM products ORDER BY prod_name;

这样上面的搜索结果就实现了字母排序。

🕹上面ORDER BY 子句使用的列是检索列，其实不使用检索的列进行排序也是完全合法的。

按多个列排序

我们在现实生活中也会遇到这样的情况：比如你说按姓名排序，当姓一样的时候，你就需要按名字来排序

SELECT prod_id , prod_price ,prod_name 
FROM  products
ORDER BY prod_price,prod_name;

上面的结果就可以观察到，当价格一样的时候，就会按照产品名称排序。

指定排序方向 （DESC 降序）

🍨数据排序不仅限于升序排序（从A到Z），这只是默认的排序顺序。我们还可以使用降序排序关键字DESC

SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC;

上面的图片就实现了价格的降序排序。

🍊打算对多个列排序怎么办呢？

SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;

上面就实现了先对价格降序，然后价格相同的按名字升序。

🥞DESC关键词只应用到直接位于其前面的列名。所以如果你想对多个列进行降序排序，必须每个列指定DESC关键字。

🏷ASC是升序关键词，但其实是没有什么用处的，因为默认就是升序呀。

例子：找出最昂贵的物品

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

过滤数据 （WHERE 子句）

使用WHERE 子句

我们很少需要检索表中的所有行，通常会根据特定操作或报告的需要提取数据的子集。

🍉在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。

SELECT prod_name ,prod_price
FROM products
WHERE prod_price=2.50;

只展示价格为2.50的产品。

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

WHERE子句操作符

1️⃣检查单个值：

SELECT prod_name,prod_price
FROM products
WHERE prod_name = 'fuses';

SELECT  prod_name,prod_price
FROM products
WHERE prod_price < 10;

2️⃣不匹配检查：

SELECT vend_id,prod_name
FROM products
WHERE vend_id<>1003;

3️⃣范围值检查:

SELECT prod_name ,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

从上面的结果可以看出来这个BETWEEN的范围是闭区间

4️⃣空值检查：

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;

❗️一定要注意一点，空值不是0。

❗️过滤与不匹配：在通过过滤选择出不匹配值的行中，你可能希望可以返回具有NULL值的行，但是，不行。

数据过滤

组合WHERE子句

之前的WHERE子句都是单一的条件，其实是可以使用多个WHERE子句的，这些子句可以使用AND和OR子句的方式使用。

AND操作符

为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。

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;

AND的计算次序优先级更高

为了解决这一问题，方法就是加圆括号（）

IN操作符

IN操作符用来指定条件范围，范围内的每个条件都可以进行匹配，IN取合法值的由逗号隔开。

SELECT prod_name ,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

🔎如果你认为IN和OR功能相同，这种想法是正确的。下面就是晚上上面的同样的工作。

SELECT prod_name ,prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name;

那为什么选择IN呢？

🉑在使用长的合法选项清单时，IN操作符的语法更清楚更直观

🉑在使用IN时，计算次序更容易管理

🉑IN操作符一般比OR操作符清单执行的更快

🉑IN的最大优点是可以包含其他SELECT 语句，使得能够更动态地建立WHERE子句。

NOT操作符

NOT功能：否定他之后所跟的任何条件。

SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

创建计算字段

计算字段

存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换计算，格式化后的数据。这就是计算字段发挥作用的所在了。计算字段并不实际存在于数据库表中。计算字段是在运行SELECT时语句内创建的。

拼接字段

我们需要输出这样的一个格式 name（location），但数据库中是没有这样的字段的，所以我们需要拼接列来显示一个这样的信息。

✔️解决方法：我们使用Concat函数来拼接两个列

SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;

😗RTrim函数去掉值右边的所有空格，通过使用RTrim（）。各个列都进行了整理。

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

⛱LTrim呢，显而易见就是去除串左边的空格。

⛵️Trim，去除串左右两边的空格。

刚刚的输出我们可以看到那个表头是很不美观的，所以我们可以使用别名

AS别名

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

执行算术计算

我们可以在SELECT语句中就添加一些算数计算，以计算一些列的值。

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

使用数据处理函数

函数

函数没有SQL的可移植性强

使用函数

文本处理函数

🔍Upper（）函数:将文本转化为大写

SELECT vend_name ,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

常用的文本处理函数

日期和时间处理函数

常用日期和时间处理函数

SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

加Date（）是一个好习惯，即使你知道表里只有日期，但是这样的方式可以万无一失，万一以后表里加入时间，也不会出现匹配不到的错误。

⛸ 例:如果你想检索出2005年9月下的所有订单。

法一：

SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01'AND '2005-09-30';

法二：

SELECT cust_id ,order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理函数

常用的数值处理函数

汇总数据

聚集函数

聚集函数：运行在行组上，计算和返回单个值的函数

SQL聚集函数

AVG函数

例：返回products表中所有产品的平均价格：

SELECT  AVG(prod_price) AS avg_price
FROM products;

🖌AVG函数忽略列值为NULL的行

COUNT函数

确定表中行的数目或符合特定条件的行的数目。

例：返回customers表中客户的总数

SELECT COUNT(*) AS num_cust
FROM customers;

例：只对具有电子邮件的客户计数

SELECT COUNT(cust_email) AS num_cust
FROM customers;

MAX函数

SELECT MAX(prod_price) AS max_price
FROM products;

🦄用于文本数据时，如果数据按相应的列排序，则max返回最后一行。

🗡MAX函数忽略列值为NULL的行

MIN函数

MIN函数与MAX函数正好相反

SUM函数

sum用来返回指定列值的和

例：检索所订购的物品的总数。

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

SUM还可以与之前的计算字段进行联动

例：求总的订单金额

SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;

聚集不同值

⚠️下面介绍聚集函数的DISTINCT的使用，mysql4.x是不能正常使用的。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

📅DISTINCT不能用于COUNT（*）。

组合聚集函数

SELECT COUNT(*)  AS num_items,
		MIN(prod_price) AS price_min,
		MAX(prod_price) AS price_max,
		AVG(prod_price) AS price_avg
FROM products;

分组数据

数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

例：

SELECT vend_id ,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

重要规定

1. GROUP BY子句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2. 如果在GROUP BY 子句中嵌套了分组，数据将会在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。（所以不能从个别列取回数据）
3. GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
4. 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出
5. 如果分组列中有null值，则null将作为一个分组返回，如果有多行null值，他们将分为一个组
6. GROUP BY子句必须在WHERE子句之后，ORDER BY 子句之前。

使用ROLLUP关键字

SELECT vend_id ,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;

过滤分组

HAVING非常类似WHERE。事实上，目前为止的所学习的所有类型的WHERE子句均可用HAVING来代替。唯一的区别在于HAVING过滤分组，WHERE过滤行

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;

分组和排序

虽然GROUP BY和ORDER BY 经常完成相同的工作，但是他们是非常不同的。

SELECT order_num ,SUM(quantity*item_price) AS ordertotal 
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

SELECT子句顺序

SELECT 子句及其顺序

使用子查询

子查询

⚠️版本要求：MySQL4.1引入了对子查询的支持，所以要想使用本章描述的mysql必须使用4.1更高的版本。

利用子查询进行过滤

例：列出订购物品TNT2的所有客户的客户信息。

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') );

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于(=)，不等于（<>）

作为计算字段使用子查询

例：显示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;

联结表

联结

SQL强大的功能之一就是可以在数据检索查询的执行中联结。

关系表

我们设计两个表，一个供应商表，一个商品表，供应商表的主键标识就是商品表的外键。

这样关系数据可以有效的存储和方便的处理，他的可伸缩性是要远远好于非关系数据库。

可伸缩性

能够不断适应增加的工作量而不失败，设计良好的数据库或应用程序称之为可伸缩性好。

为什么使用联结

就像上面说的，你把数据分解到多个数据表这是有代价的，如果你想要的数据在多个数据表中你要怎么办呢？

🗯答案就是使用联结。

创建联结

SELECT vend_name ,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

🐼这里要完全限定列名，不然MySQL可处理不了这种充满二义性的WHERE语句。

WHERE子句的重要性

在联结两个表的时候。实际是将第一个表的每一行，与第二个表的每一行配对，WHERE子句作为过滤条件，他只包含哪些匹配给定条件的行。没有WHERE子句，返回的信息将是特别特别多的呀。

笛卡尔积

由于没有连接条件的表关系返回的结果为笛卡尔积。检索出的行的数目

内部联结

目前为止的所有连接都是等值连接，他是基于两个表之间的相等测试。这种连接称为内部联结。其实，这种连接是原有另外一种写法 的。（这种写法可以明确指定连接的类型）

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

连接多个表

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;

🌂mysql处理联结表是特别浪费性能的，所以我们要仔细，不要联结不必要的表。联结的表越多，性能的下降越厉害。

我们可以把之前的一个SELECT拿出来搞一个一题多解。

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';

创建高级联结

使用表别名

🍨 这是我们之前使用到的表别名的例子

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

别名除了用于列名和计算字段之外，SQL还允许给表名起列名。主要两个原有、

1. 缩短SQL语句
2. 允许在单条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  p1.prod_id , p2.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

⛸使用别名，我们就可以用自联结而不是子查询。有时候会快的多。

自然联结

迄今为止，我们建立的每一个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

外部联结

联结包含哪些在相关表中没有关联行的行，这种类型的联结称为外部联结。

🌮 例：内部联结:

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;

那个10002对应的null就很能说明问题，因为你是left联结，所以左边表是要包含全部内容的，所以就会有10002对应的null这样一个信息，因为你左联结了，所以左边表的所有行都要出现。

☔️mysql不支持一个*=的操作符，这在其他的dbms里是十分受欢迎的。

使用带聚集函数的联结

例： 检索所有客户以及每个客户所下的订单数

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 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;

例：检索所有客户及每个客户所下的订单数

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;

组合查询

组合查询

MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并或复合查询。

创建组合查询

可以使用union操作符来组合数条SQL查询。利用union，可给出多条SELECT语句，将他们的结果组合成单个结果集。

使用union

union的使用其实是很简单的，所需要你做的就是给出每条SELECT语句，在各条语句之间放上关键字union。

🍨你比如说你像找价格小于等于5的所有物品，还想找供应商是1001和1002生产的所有物品，当然你写两个SELECT语句必然能解决这一问题，但是那就不是一个结果集了呀，所以你可以使用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);

呐。union的使用是不是很简单呢。

当然上面这个例子我们使用WHERE子句加or也是可以完成这个任务的。但是对于更加复杂的过滤条件，或者从多个表中检索数据的情形，使用union可能会使处理更简单。

union规则

union的使用虽然是很简单的，但是还是有一些需要注意的地方

• union必须由两条或两条以上的SELECT语句组成，语句之间用union关键词分隔。
• union中每个查询必须包含相同的列，表达式或聚集函数（不过每个列不需要以相同的次序出现）
• 列数据的类型必须兼容：类型不必完全相同，但是必须是DBMS可以隐含转换的类型。

包含或取消重复的行

union是有一个默认行为的，就是虽然 是两个SELECT语句但是对于重复的行，它是会自动去除掉的。

既然是默认行为就能改变，我们使用union all 关键词就可以返回所有匹配行。

对组合查询结果排序

union只需要在最后的一条select语句上写order by。就可以对整个结果集进行排序。

全文本搜索