哈夫曼树的学习：
创建计算字段
字段：基本上与列的意思相同（数据库列一般称为列，而字段通常用于计算字段连接上）
拼接字段
拼接：将值联结到一起构成单个值
把两个结拼接起来，在MYSQL的SELECT语句中，可以用Concat()函数来拼接
SELECT Concat(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name;
可以检索到：ACME (USA)等
删除数据右端多余的空格，可以使用MYSQL的RTrim()函数来完成
LTrim()是去掉串左边的空格
Trim()是去掉串两边的空格
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')' FROM vendors ORDER BY vend_name;
使用别名
拼接后，新计算的名字是不知道是什么，在客户机查看结果时，一个未命名的列不能用于客户机中，此时可以使用别名。
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors ORDER BY vend_name;
别名也叫导出列，别名的另一个作用：在实际的表列名中包含不符合规定的字符（如空格）是重新命名他，在原来的名字混淆或者容易误解是扩充它。
执行算术计算
SELECT prod_id,quantity,item_price FROM orderitems WHERE order_num=200005;
这个SQL语句是检索每项物品的单价
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems WHERE order_num=200005;
这条语句的输出显示expanded_price列，是计算的quantity*item_price
测试计算：
可以用SELECT Trim('abc');返回abc；
SELECT 3*2;返回6；
SELECT Now();返回当前日期和时间。
使用数据处理函数
Soundex()函数的解释：
如果customers表中有一个顾客是Coyote Inc，其联系名是Y.Lee。但如果这个是输入错误，此联系名实际应该是Y.Lie。
SELECT cust_name,cust_contact FROM customers WHERE cust_contact='Y.Lie';
这个检索查不到数据，因为输入错误，但是用Soundex()函数进行检索，他匹配所有发音类似于Y.Lie，所以可以搜索到Y.Lee
SELECT cust_name,cust_contact FROM customers WHERE Soundex(cust_contact)=Soundex('Y.Lie');
日期和时间处理函数
SELECT cust_id,order_num FROM orders WHERE order_date='2005-09-01';
order_data数据类型为datatime,检索出订单中“2005-09-01”，但是如果订单中这一列包含日期和时间，“2005-09-01 11:30:05”,则该条件会检索失败
解决的办法是：仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较，此时用到Date(order_date)指示提取列的日期部分：
SELECT cust_id,order_num FROM orders WHERE Date(order_date)BETWEEN '2005-09-01' AND '2005-09-30';
可以匹配到从一个日期范围。
数值处理函数
汇总数据
聚集函数：
运行在行组上，计算和返回单个值的函数。
检索数据，以便分析和报表生成。
- 确定表中的函数（或满足某个条件或包含某个特定值的行数）
- 获得表中行组的和
- 找出表列（或所有行或特定的行）的最大值、最小值和平均值
AVG()函数
AVG()函数通过对表中行数奇数并计算特定列值之和，求得改列的平均值，也可以用来返回特定列或行的平均值。
SELECT AVG(prod_price) AS avg_price FROM products;
返回值是avg_price，包含products表中的所有产品的平均价格。（avg_price是一个别名）
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
返回特定类的平均价格。
- AVG()只能用来确定单个列的平均值，列名不想作为函数参数给出，为了获得多个列的平均值，不行使用多个AVG()函数。
- AVG()函数忽略列值为NULL的行 。
COUNT()函数
COUNT函数进行计数，两种使用方式：
- 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是不是空值（NULL）
SELECT COUNT(*) AS num_cust FROM customers;
对所有行进行计数，不管列中有什么值
- 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值
SELCT COUNT(cust_email) AS num_cust FROM customers;
对cust_email列中有值的所有行进行计数（不包括NULL）
MAX()函数
MAX()返回指定列中的最大值，MAX()要求指定列名。
SLECT MAX(prod_price) AS max_price FROM products;
对非数值数字使用MAX() ，一般用来找到最大的数值或日期值
用在文本数据时，如果数据按相应的列排序，则MAX()返回最后一行
MIN()函数
MIN()返回指定列中的最小值，要求列名
使用方法与MAX()函数相似
SUM()函数
SUM() 函数用来返回指定列值的和
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE orderitems WHERE order_num=20005;
返回订单中所有order_num=200005的物品数量之和
可以在多个列中进行计算，进行计算时忽略NULL的行。
聚焦不同值
以上的聚集函数都可以使用：
- 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）
- 只包含不同的值，指定DISTINCT参数
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;
返回的是特定供应商提供的产品的平均价格（平均值只考虑各个不同的价格）
注意：
- DISTINCT必须使用列名，不能用于计算或表达式。
- 将DISTINCT用于MIN()和MAX()没有价值。
组合聚集函数
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语句执行了四个聚集计算，返回四个值。
分组数据
创建分组
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;
过滤分组
除了能用GROUP BY分组数据之外，还允许过滤分组，规定包括哪些分组，排除哪些分组，例如：列出至少两个订单的所有顾客，为得到这种数据，必须基于完整的分组而不是个别的行进行过滤。
WHERE过滤行，而HAVING过滤分组
SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >=2;
分组后添加了HAVING子句，过滤了COUNT(*)>=2的那些分组
注意：
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
分组和排序
GROUP BY和ORDER BY是非常不同的：
一般在使用GROUP BY子句时，应该也给出ORDER BY子句，这是保证正确排序的唯一方法。
SELECT子句的顺序
使用子查询
以下两条语句可以合并为一句：
SELECT order_num FROM orderitems WHERE prod_id='TNT2';
用查询到结果：20005,20007
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');
在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。
作为计算字段使用子查询
从customers表中检索客户列表
对于检索出的每个客户，统计其在orders表中的行进行计数，并通过一条WHERE子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。
SELECT cust_name,cust_state,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
联结表
相同数据多次出现不是好事，此因素是关系数据库设计的基础，关系表的设计就是要保证把信息分解成多个表，一类数据一个表，，各表通过某些常用的值（关系）互相关联。
例子：products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息，vendors表的主键有叫做products的外键，将vendors表语products表关联，可以用供应商ID从vendors表中找出相应供应商的详细信息。
外键：是某个表的一列，包含另一个表的主键值，定义了两个表之间的关系：
好处：
- 供应商信息不重复，不浪费时间空间
- 如果供应商信息变动，可以只更新一个表中的记录
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name,prod_name;
完全限定列名：用一个点分隔的表名和列名
WHERE子句的重要性：在联结两个表时，要将第一个表中的每一行与第二个表的每一行配对，WHERE子句作为过滤条件，它只包含哪些匹配给定条件的行。
没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管逻辑上是否可以配在一起。
内部联结
两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定，在使用这种语法时联结条件用特定的ON子句而不是WHERE子句给出，传递给ON的实际条件与传递给WHERE的相同
SELECT vend_name,prod_name,prod_price
FROM vendors
INNER JOIN products ON vendors.vend_id=products.vend_id;
联结多个表
SELECT cust_name,cust_contact
FROM customers
WHERE cust_d IN(SELECT cust_id FROM orsers 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;
创建高级联结
使用表别名
SLECT Concat(RTrim(vend_name),'(',RTrim(vend_countru),')') 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=cust_id
AND oi.order_num=o.order_num
AND prod_id='TNT2';
FROM子句中3个表全都具有别名,表别名值用于WHERE子句，但是表别名不仅挪用于WHERE子句，还可以用于szelect的列表，ORDER BY子句已经其他部分。
使用不同类型的联结：自联结，自然联结和外部联结
自联结：
如果一个商品存在问题，想知道生产该物品的供应商生成的其他物品是否也存在这些问题，此查询要求首先找到生成I为DTNTR的物品的供应商，然后找到该供应商生产的其他物品。
子查询的方法：
SELECT prod_id,prid_name
FROM products
WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id='DTNTR');
自联结：
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';
此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。但对于products的引用具有二义性。为了解决该问题，使用了表别名。
使用自联结而不用子查询，有时候处理联结远比处理子查询快得多。
自然联结：
无论何时对表进行连接，应该至少有一个列出现在不止一个表中（被连接的列）。标准的联结（内部联结）返回所有数据，甚至相同的列会多次出现。
自然联结排除多次出现，使每个列只返回一次。
SELECT c.*,o.order_date,oi.prod_id,oi.quantity,OI.item_price
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='FB';
通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来
外部联结：
很多连接将一个表中的行与另一个表中的行相关联，但有时候会需要包含没有关联行的那些行。
- 对每个客户下了多少订单进行计数，包括尚未下单的客户
- 列出所有产品以及订购数量，包括没有人订购的产品
- 计算平均销售规模，包括至今尚未下单的客户
内部联结：
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 ON customers.cust_id=orders.cust_id;
使用了OUTER JOIN来指定联结类型（而不是在WHERE子句中指定），在使用OUTER JOIN语法时，必须使用RIGHT或LRFT关键字指定包括其所有行的表（RIGHT支持的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）
上面的例子使用LEFT OUTER JOIN从FROM子句左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN。
使用带聚集函数的联结
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;
使用左外部联结来包含所有客户，甚至包含哪些没有任何下订单的客户，结果显示也包含了有零个订单的客户。
扫一扫
857
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
