sql语句学习

一：创建计算字段

1、拼接字段

在sql的select语句中，可使用一个特殊的操作符来拼接两个列。此操作符可用加好（+）或两个竖杠（||）表示。

如：

SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
或
SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;
或
SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;

输出结果：

Bear Emporium (USA )
Bears R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )

结合成一个计算字段的两个列用空格填充。许多数据库（不是所有）保存填充为列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。这可以使用SQL 的RTRIM()函数来完成，如下所示：

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors<span style="white-space:pre">	

或
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
FROM Vendors
ORDER BY vend_name;

大多数DBMS 都支持RTRIM()（正如刚才所见，它去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及TRIM()（去掉字符串左右两边的空格）。

二：文本处理函数

1、常用的文本处理函数：

函 数 说 明LEFT()（或使用子字符串函数） 返回字符串左边的字符LENGTH()（也使用DATALENGTH()或LEN()） 返回字符串的长度LOWER()（Access使用LCASE()） 将字符串转换为小写LTRIM() 去掉字符串左边的空格RIGHT()（或使用子字符串函数） 返回字符串右边的字符RTRIM() 去掉字符串右边的空格SOUNDEX() 返回字符串的SOUNDEX值UPPER()（Access使用UCASE()） 将字符串转换为大写

Customers 表中有一个顾客Kids Place，其联系名为Michelle Green。但如果这是错误的输入，此联系名实际上应该是Michael Green，该怎么办呢？

现在试一下使用SOUNDEX()函数进行搜索，它匹配所有发音类似于Michael Green 的联系名：

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

2、日期和时间处理函数

在SQL Server 中检索2012 年的所有订单，可如下进行：

SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

在Access 中使用如下版本：

SELECT order_num
FROM Orders
WHERE DATEPART('yyyy', order_date) = 2012;

此函数返回日期的某一部分。DATEPART()函数有两个参数，它们分别是返回的成分和从中返回成分的日期。在此例子中，DATEPART()只从order_date 列中返回年份。通过与2012 比较，WHERE子句只过滤出此年份的订单。

下面是使用名为DATE_PART()的类似函数的PostgreSQL 版本：

SELECT order_num
FROM Orders
WHERE DATE_PART('year', order_date) = 2012;

Oracle 没有DATEPART()函数，不过有几个可用来完成相同检索的日期处理函数。例如：

SELECT order_num
FROM Orders
WHERE to_number(to_char(order_date, 'YYYY')) = 2012;

在这个例子中，to_char()函数用来提取日期的成分，to_number()用来将提取出的成分转换为数值，以便能与2012 进行比较。完成相同工作的另一方法是使用BETWEEN 操作符：

SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');

值得注意的是，相同的代码在SQL Server 中不起作用，因为它不支持to_date()函数。但是，如果用CONVERT()替换to_date()，当然可以使用这种类型的语句。
MySQL 和MariaDB 具有各种日期处理函数，但没有DATEPART()。MySQL 和MariaDB 用户可使用名为YEAR()的函数从日期中提取年份：

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;

在SQLite 中有个小技巧：

SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2012';

3、常用的数值处理函数：

函 数 说 明
ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切

三：汇总数据

1、聚集函数

sql聚集函数：

函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

2、组合聚集函数

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;

四：分组数据

1、过滤分组

WHERE 过滤指定的是行而不是分组。事实上，WHERE 没有分组的概念。那么，不使用WHERE 使用什么呢？SQL 为此提供了另一个子句，就是HAVING 子句。HAVING 非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE 子句都可以用HAVING 来替代。唯一的差别是，WHERE过滤行，而HAVING 过滤分组。

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

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

2、select语句顺序

子 句 说 明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
五：插入语句

1、从一个表复制到另一个表

SELECT *
INTO CustCopy
FROM Customers;

这条SELECT 语句创建一个名为CustCopy 的新表，并把Customers 表的整个内容复制到新表中。因为这里使用的是SELECT *，所以将在CustCopy 表中创建（并填充）与Customers 表的每一列相同的列。要想只复制部分的列，可以明确给出列名，而不是使用*通配符。

六：创建表

CREATE TABLE OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL
);<pre name="code" class="sql"><span style="white-space:pre">	</span>ALTER TABLE Vendors
ADD vend_phone CHAR(20);<pre name="code" class="sql"><span style="white-space:pre">	</span>ALTER TABLE Vendors
DROP COLUMN vend_phone;

七：游标

1、介绍

SQL 检索操作返回一组称为结果集的行，这组返回的行都是与SQL 语句相匹配的行（零行或多行）。简单地使用SELECT 语句，没有办法得到第一行、下一行或前10 行。但这是关系DBMS 功能的组成部分。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在DBMS 服务器上的数据库查询，它不是一条SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

2、使用游标

使用游标涉及几个明确的步骤。

 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT 语句和游标选项。
 一旦声明，就必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
 对于填有数据的游标，根据需要取出（检索）各行。
 在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的DBMS）。
声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

3、创建游标

我们创建一个游标来检索没有电子邮件地址的所有顾客，作为应用程序的组成部分，帮助操作人员找出空缺的电子邮件地址。下面是创建此游标的DB2、MariaDB、MySQL 和SQL Server 版本。

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

下面是Oracle 和PostgreSQL 版本：

DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL

在上面两个版本中，DECLARE 语句用来定义和命名游标，这里为CustCursor。SELECT 语句定义一个包含没有电子邮件地址（NULL 值）的所有顾客的游标。

4、使用游标

使用OPEN CURSOR 语句打开游标，这条语句很简单，在大多数DBMS中的语法相同：

OPEN CURSOR CustCursor

在处理OPEN CURSOR 语句时，执行查询，存储检索出的数据以供浏览和滚动。现在可以用FETCH 语句访问游标数据了。FETCH 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。第一个例子使用Oracle语法从游标中检索一行（第一行）：

DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
FETCH CustCursor INTO CustRecord;
CLOSE CustCursor;
END;

在这个例子中，FETCH 用来检索当前行（自动从第一行开始），放到声明的变量CustRecord 中。对于检索出来的数据不做任何处理。
下一个例子（也使用Oracle 语法）中，从第一行到最后一行，对检索出来的数据进行循环：

DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
LOOP
FETCH CustCursor INTO CustRecord;
EXIT WHEN CustCursor%NOTFOUND;
...
END LOOP;
CLOSE CustCursor;
END;

与前一个例子一样，这个例子使用FETCH 检索当前行，放到一个名为CustRecord 的变量中。但不一样的是，这里的FETCH 位于LOOP 内，因此它反复执行。代码EXIT WHEN CustCursor%NOTFOUND 使在取不出更多的行时终止处理（退出循环）。这个例子也没有做实际的处理，实际例子中可用具体的处理代码替换占位符...。
下面是另一个例子，这次使用Microsoft SQL Server 语法：

DECLARE @cust_id CHAR(10),
@cust_name CHAR(50),
@cust_address CHAR(50),
@cust_city CHAR(50),
@cust_state CHAR(5),
@cust_zip CHAR(10),
@cust_country CHAR(50),
@cust_contact CHAR(50),
@cust_email CHAR(255)
OPEN CustCursor
FETCH NEXT FROM CustCursor
INTO @cust_id, @cust_name, @cust_address,
@cust_city, @cust_state, @cust_zip,
@cust_country, @cust_contact, @cust_email
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CustCursor
INTO @cust_id, @cust_name, @cust_address,
@cust_city, @cust_state, @cust_zip,
@cust_country, @cust_contact, @cust_email
END
CLOSE CustCursor

在此例中，为每个检索出的列声明一个变量，FETCH 语句检索一行并保存值到这些变量中。使用WHILE 循环处理每一行， 条件WHILE@@FETCH_STATUS = 0 在取不出更多的行时终止处理（退出循环）。这个例子也不进行具体的处理，实际代码中，应该用具体的处理代码替换其中的...占位符。
5、关闭游标

如前面几个例子所述，游标在使用完毕时需要关闭。此外，SQL Server等DBMS 要求明确释放游标所占用的资源。下面是DB2、Oracle 和PostgreSQL 的语法。

CLOSE CustCursor

下面是Microsoft SQL Server 的版本。

CLOSE CustCursor
DEALLOCATE CURSOR CustCursor

CLOSE 语句用来关闭游标。一旦游标关闭，如果不再次打开，将不能使用。第二次使用它时不需要再声明，只需用OPEN 打开它即可。