检索数据
检索数据
⛵️写在前面,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子句操作符
WHERE子句操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
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;
常用的文本处理函数
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换为小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX值 (这玩意是发音类似匹配) |
SubString() | 返回子串的字符 |
Upper() | 将串转换为大写 |
日期和时间处理函数
常用日期和时间处理函数
函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天,周等) |
AddTime() | 增加一个时间(时,分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
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;
数值处理函数
常用的数值处理函数
函数 | 说明 |
---|---|
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Pi() | 返回圆周率 |
Rand() | 返回一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个角度的正切 |
汇总数据
聚集函数
聚集函数:运行在行组上,计算和返回单个值的函数
SQL聚集函数
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
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;
重要规定
- GROUP BY子句可以包含任意数目的列,这使得能对分组进行嵌套,为数据分组提供更细致的控制。
- 如果在GROUP BY 子句中嵌套了分组,数据将会在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算。(所以不能从个别列取回数据)
- GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式,不能使用别名
- 除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出
- 如果分组列中有null值,则null将作为一个分组返回,如果有多行null值,他们将分为一个组
- 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 经常完成相同的工作,但是他们是非常不同的。
ORDER BY | GROUP 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 子句及其顺序
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表中选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
使用子查询
子查询
⚠️版本要求: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还允许给表名起列名。主要两个原有、
- 缩短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 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。就可以对整个结果集进行排序。