《SQL必知必会》（8-15）

1. 创建计算字段（field）

1.1 拼接（concatenate）

此操作在Access和SQLServer用加号（+），在DB2、Oracle、PostgreSQL、SQLite和Open Office Base中用两个竖杠（||）完成。

selec trtrim(vend_name) || '(' || rtrim(vend_country) || ')'as vend_title from vendors order by vend_name;

rtrim()去掉字符串右边空格，ltrim()去掉字符串左边空格。as可重新命名拼接好的字段。

但是在MySQL和MariaDB中，必须使用特殊函数concat。

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

1.2 执行算数计算

select prod_id,quantity,item_price,quantity*item_price as expended_price from Orderitems where order_num=20008;

 SQL 算术操作符 

操作符	说明
+	加
-	减
*	乘
/	除

2. 函数

虽然所有类型的函数一般都可以在每个DBMS中使用，但是各个函数的名称和语法可能极其不同。

2.1 文本处理函数

函数	说明
Left()	返回字符串左边的字符
Length()	返回字符串长度
Lower()	将字符串转换为小写
Ltrim()	去掉字符串左边的空格
Rtrim()	去掉字符串右边的空格
Right()	返回字符串右边的字符
Soundex()	返回字符串的soundex值
Upper()	将字符串转换为大写

soundex是一个将任何字符串转换为描述其语音表示的字母数字模式的算法。soundex考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。soundex不是SQL概念，但多数DBMS都提供对soundex的支持（除了Microsoft Access和PostgreSQL不支持）。

例如，Customers表中有一个顾客Kids Place，其联系名为Michelle Green。但如果这是错误的输入，此联系名实际应该是MichaelGreen，显然按正确的联系名搜索不会返回数据。若：select cust_name,cust_contact from customers where soundex(cust_contact)=soundex('Michael Green');

select vend_name, upper(vend_name) as vend_name_upcase from vendors orderby vend_name;

 

2.2 日期和时间处理函数

日期和时间函数在SQL中很不一致，可移植性最差。

Ø  检索2012年的所有订单，SQLServer中输入：

select order_num from orders wheredatepart(yy,order_date)=2012;

Ø  Access中输入：select order_num from orders where datepart('yyyy',order_date)=2012;

Ø  oracle中：select order_num from orders where to_number(to_char(order_date,'yyyy'))=2012;to_char()函数用来提取日期的成分，to_number()用来将提取出的成分转换为数值，以便能与2012比较。

Ø  MySQL和MariaDB中，select order_num from orders whereYear(order_date)=2012;

Ø  SQLite中：select order_num from orders where strftime('%y',order_date)='2012';

2.3 数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Pi()	返回圆周率
Sin()	返回一个数的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个数的正切

3. 汇总数据

3.1 聚合函数

3.1.1  AVG()函数-返回平均值

Ø  计算某列平均值：selectavg(prod_price) as avg_price from products;

Ø  计算特定列平均值：selectavg(prod_price) as avg_price from products where vend_id='DLL01';

3.1.2  count()-计数

Ø  计算所有行数：selectcount(*) as num_cust from customers;

Ø  计算特定列的行数：selectcount(cust_email) as num_cust from customers;

3.1.3  max()-返回最大值

selectmax(prod_price) as max_price from products;

在用于文本数据时，返回按该列排序后的最后一行。

3.1.4  min()函数-最小值

selectmin(prod_price) as min_price from products;

在用于文本数据时，返回按该列排序后的最前面一行。

3.1.5  sum()-求和

Ø  单列：selectsum(quantity) as items_ordered from orderitems where order_num=20005;

Ø  多列：selectsum(item*quantity) as total_ordered from orderitems where order_num=20005;

3.2聚集不同值（distinct）

使用distinct参数，只考虑不同值。例如：select avg(prod_price) as avg_price from products;

注：distinct()不能用于count(*)，可用于count()。

3.3 组合聚集函数

selectcount(*) as num_items,min(prod_price) as price_min, max(prod_price) as price_min, avg(prod_price) as price_avg from products;

4. 分组数据

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

4.1 创建分组

例如：select vend_id,count(*) as num_prods from products groupby vend_id;

 

group by子句指示DBMS按vend_id排序并分组数据，这就会对每个vend_id而不是整个表计算num_prods一次。

关于group by子句：

Ø  groupby子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行分组。

Ø  如果在group by子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。

Ø  groupby子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在select中使用表达式，则必须在group by子句中指定相同的表达式。如果在select中使用表达式，则必须在group by子句中指定相同的表达式。不能使用别名。

Ø  大多数SQL实现不允许group by列带有长度可变的数据类型（如文本或备注型字段）。

Ø  除聚集计算语句外，select语句中的每一列都必须在group by子句中给出。

Ø  如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回，如果列中有多行null值，它们将分为一组。

Ø  groupby子句必须出现在where语句之后，order by语句之前。

4.2 过滤分组（having）

having和where的区别联系：

Ø  where的所有技术和选项都适用于having。使用方法相同，只是关键字有区别。

Ø  where是过滤行，having是过滤分组。换言之，where在数据分组前进行过滤，having在数据分组后进行过滤。where排除的行不包括在分组中，这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。

列出具有两个以上产品且其价格大于等于4的供应商：select vend_id,count(*) as num_prods from products where prod_price>=5groupby vend_id havingcount(*)>=2;

where子句先过滤出所有prod_price至少为4的行，然后按vend_id分组数据，having子句过滤出计数为2或2以上的分组。

4.3 分组和排序

groupby与orderby

Order by	Group by
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

例如：select order_num, count(*) as items from orderitems groupby order_num havingcount(*)>=3;

 

select order_num, count(*) as items from orderitems groupby order_num havingcount(*)>=3orderby items,order_num;

5. 子查询（嵌套查询）

5.1 利用子查询过滤

本书中使用的数据库表都是关系表。（具体见附录）。订单存储在两个表中。每个订单包含订单编号、客户ID、订单日期，在order表中存储为一行。各订单的物品存储在相关的orderitem表中。order表不存储顾客信息，只存储顾客ID。顾客的实际信息存储在customers表中。例如：列出订购物品RGAN01的所有顾客。

selectcust_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='RGAN01'));

 

5.2 作为计算字段使用子查询

假如需要显示customers表中每个顾客的订单总数。订单与相应的顾客ID存储在orders中。

select cust_name,

                 cust_state,

                 (selectcount(*)

                 from orders

                 where orders.cust_id=customers.cust_id) as orders

from customers

orderby cust_name;

 

6. 联结表（join）

6.1 创建联结-完全限定列名

select vend_name,prod_name,prod_price from vendors v,productsp where v.vend_id=p.vend_id;

 

在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一个行配对。where子句作为过滤条件，只包含那些匹配给定条件的行。若没有联结条件，返回的结果是笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。而不管它们逻辑上能否配在一起。

联结多张表：

 

6.2 内联结（inner join）

内联结即等值联结（equijoin），它基于两个表之间的相等测试。前面例子亦可使用如下语句实现：select vend_name,prod_name,prod_price from vendors v innerjoin products p onv.vend_id=p.vend_id;

12.1的示例可用如下语句实现：select cust_name,cust_contact from customers c,orderso,orderitems s wherec.cust_id=o.cust_id ando.order_num=s.order_num and prod_id='RGAN01';

6.3 使用表别名

SQL允许给表起别名主要有两个原因：一是缩短SQL语句；二是允许在一条select语句中多次使用相同的表。一般数据库使用as，oracle中直接指定。

6.4 不同类型的联结

除了上述内联结，还有三种其他联结：自联结（self-join）、自然联结（natural join）和外联结（outer join）。

Ø  自联结

例如，要给与Jim Jones同一公司的所有顾客发送一封邮件。这个查询要求首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客。

selectc1.cust_id,c1.cust_name,c1.cust_contact from customers c1,customers c2 wherec1.cust_name=c2.cust_name and c2.cust_contact='Jim Jones';

  

Ø  自然联结

无论何时对表联结，应该至少有一列不止出现在一个表中，标准的联结返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。select c.*,o.order_num,o.order_date,s.prod_id,s.quantity,s.item_pricefrom customers c,orderso,orderitems s wherec.cust_id=o.cust_id ando.order_num=s.order_num and prod_id='RGAN01';

 

我们建立的每个内联结都是自然联结。

Ø  外联结

外联结关联了那些在相关表中没有关联行的行，分为左关联（left outer join）和右关联（right outer join）。left是指outer join左边的表。下面例子从left outer join左边的表（customers）中选择所有行。例如：select c.cust_id,o.order_num from customers c leftouterjoin orders o onc.cust_id=o.cust_id;

 

select c.cust_id,o.order_num from customers c rightouterjoin orders o onc.cust_id=o.cust_id;

有的数据库还支持全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。与左外关联和右外关联包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。selectc.cust_id,o.order_num from customers c fullouterjoin orders o on c.cust_id=o.cust_id;

 

6.5 使用聚合函数的联结

例如：select c.cust_id,count(o.order_num) as num_ord from customers c fullouterjoin orders o on c.cust_id=o.cust_id groupby c.cust_id;

  

7. 组合查询（UNION）

7.1 组合查询

SQL允许执行多个查询（多条select语句），并将结果作为一个查询结果集返回。

7.2 创建组合查询

7.2.1  使用union

使用union很简单，就是在每条select语句之间加上关键字union即可。

select cust_name,cust_contact,cust_email from customers where cust_state in ('IL','IN','MI') UNIONselectcust_name,cust_contact,cust_email from customers where cust_name='Fun4All';

 

或者使用select cust_name,cust_contact,cust_email from customers where cust_state in ('IL','IN','MI') or cust_name='Fun4All'；

7.2.2  union使用规则

Ø  必须使用由两条或两条以上的select语句组成。

Ø  union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。

Ø  列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

7.2.3  包含或取消重复的行

union从查询结果集中自动去除了重复的行，若要返回所有匹配的行，可用unionall，而不是union。

7.2.4  对组合查询结果排序

在用union组合查询时，只能使用一条orderby子句，它必须位于最后一条select语句之后，DBMS将用它来排序所有select语句返回的所有结果。

select cust_name,cust_contact,cust_email from customers where cust_state in ('IL','IN','MI') UNIONselectcust_name,cust_contact,cust_email from customers where cust_name='Fun4All'orderby cust_name,cust_contact;

注：以上实例都是使用union来组合针对同一表的多个查询。实际上，union在需要组合多个表的数据时也很有用，即使是有不匹配列名的表，在这种情况下，可以将union与别名组合，检索一个结果集。union操作的各结果集，只要保证列数相同，并且各列的数据类型一致即可，并不要求具有统一的列名，没有别名时，结果为第一个select语句的列名，有别名时结果列名为别名。例如：

select order_num, cust_id from orders unionselect order_item,prod_id from orderitems;

 

select order_num num, cust_id idfrom orders unionselect order_item,prod_id from orderitems;

 

8.插入数据（insert）

8.1 数据插入

插入有几种方式：

Ø  插入完整的行。

Ø  插入行的一部分。

Ø  插入某些查询结果。    

8.1.1    插入完整的行

比较安全的做法是加入列名。insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip) values('1000000007',NULL,NULL,'Toy Land','123 Any Street','New York','NY','11111');

8.1.2    插入部分行

省略列：如果表的定义允许，则可以在insert操作中省略某些列。省略的列必须满足一下某个条件。

Ø  该列定义为允许NULL（无值或空值）。

Ø  在表定义中给出默认值。这表示如果不给出值，将使用默认值。

例如：insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip)  values('1000000008','Toy Land','123 Any Street','New York','NY','11111');

8.1.3    插入检索出的数据（insert…select）

例如想把另一表中的顾客列合并到customers表中。insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip) select cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip from custnew;

说明：

Ø  insert select语句不一定要求列名匹配，它使用的是列的位置，因此select中的第一列（不管其列名）将用来填充表列中指定的第一列，以此类推。

Ø  insert select中的select语句可以包含where子句。

Ø  insert通常只插入一行。插入多行，必须使用多个insert语句。insert select例外，不管select返回多少行，都将被插入。

8.2 表的复制

要将一个表的内容复制到一个全新的表（运行中创建的表），可以使用select into语句。

select * into custcopy from customers;

要想只复制部分的列，可以明确给出列名，而不是使用*通配符。

MariaDB、MySQL、Oracle、PostgreSQL和SQLite使用的语法稍有不同：

create table custcopy as select * from customers;

关于使用select into的说明：

Ø  任何select选项和子句都可以使用，包括where和group by；

Ø  可利用联结从多个表插入数据；

Ø  不管从多少个表检索数据，数据都只能插入到一个表中。