@demonly 2017-02-03T14:07:06.000000Z 字数 3767 阅读 1288

数据库查询

SQL

数据库查询

SELECT 语句

SELECT [ * | ALL | DISTINCT COLUMN1 , COLUMN2 ]
FROM TABLE1 [, TABLE2 ];

SELECT 命令可以对数据库进行查询，关键字 SELECT 后面是字段列表，它们是查询输出的组成部分，默认是输出所有字段。

以空格对于每一个字段都可以设置别名，以下例子中将第二个 PROD_DESC 的别名设置为了 PRODUCT。

SELECT PROD_DESC,
       PROD_DESC PRODUCT
FROM PRODUCTS_TBL;

FROM 子句

from table1 [ ,table2 ]

FROM子句指定要从哪些表里查询数据。

WHERE 子句

where [ condition1 | expression1 ]
[ and|OR condition2 | expression2 ]

WHERE 子句限定了返回的记录必须符合的条件。

ORDER BY 子句

ORDER BY colum1|integer [ ASC|DESC ]

ORDER BY 子句可以以用户指定的列表格式对查询结果进行排列。ASC 为按照字母顺序显示，DESC 为反字母顺序显示。

使用操作符对数据进行分类

比较操作符

=：相等
<>：不等于
<：小于
>：大于

逻辑操作符

ISNULL：与 NULL 值进行比较
BETWEEN：寻找某个范围内的值，包括边界值

WHERE SALARY BETWEEN '20000' AND '30000'

IN：把值与列表中的项进行比较

WHERE SALARY IN ('20000', '30000', '40000')

LIKE：利用通配符把一个值与类似的值比较，%表示任意数量的任意字符，_表示一个任意字符

WHERE SALARY LIKE '%200%'

EXISTS：搜索指定表中是否存在满足特定条件的记录，如果不存在则不会返回任何记录

SELECT COST
FROM PEODUCTS_TBL
WHERE EXISTS ( SELECT COST
               FROM PRODUCTS_TBL
               WHERE COST > 100 );

ALL：将一个值与另一个集合中所有值进行比较

SELECT *
FROM PRODUCTS_TBL
WHERE COST > ALL ( SELECT COST
                   FROME PRODUCTS_TBL
                   WHERE COST < 10 );

ANY：将一个值与另一个集合中的任意值比较

连接操作符

AND：与
OR：或

求反操作符

NOT 可以颠倒逻辑操作符的含义（IS NULL 求反为 IS NOT NULL）

算术操作符

汇总查询到的数据

汇总函数

在 SELECT 语句中可以用函数作为 SELECT 的参数，在一次查询中可以使用多个参数。

COUNT 函数用于统计不包含 NULL 值的记录或者字段值，在用于查询之中时，它返回一个数值。ALL 命令是默认的，不需要指定。DISTINCT 命令表示只统计数据集里不同的记录数量。 * 将返回所有项，包括重复项和 NULL。

COUNT ( [ * | DISTINCT | ALL ] COLUMN NAME)

在其他汇总函数里面同样可以使用 DISTINCT 命令，但是大多情况下都没有实际的意义。

SUM 函数返回一组记录中某一个字段值的总和。SUM 函数处理的字段数据类型必须是数值型的。

SUM ( COLUMN NAME)

AVG 函数可以计算一组指定记录的平均值。AVG 函数处理的字段数据类型必须是数值型。

AVG ( COLUMN NAME)

MAX 函数返回一组记录中某个字段的最大值，NULL 值不在计算之内。

MAX (COLUMN NAME)

MIN 函数返回一组记录里某个字段的最小值，NULL 值不在计算之内。

数据排序与分组

GROUP BY 子句

GROUP BY COLUMN1, COLUMN2

GROUP BY 子句将相同的数据（后面列出的每一个列都必须相同）划分为组。GROUP 子句在 WHERE 子句之后，在 ORDER BY 子句之前。

GROUP BY 子句中的字段不必与 SELECT 子句里的字段次序相同，但是 SELECT 子句中的字段名称如果符合条件就必须出现在 GROUP BY 子句中（分组函数中的字段除外）。

以下代码先根据 PROD_DESC 字段进行分组，然后在再对每个分组执行汇总函数。

SELECT PROD_DESC, SUM(COST)
FROM PRODUCTS_TBL
GROUP BY PROD_DESC
ORDER BY 2

ROLLUP 语句

GROUP BY ROLLUP(COLUMN1,COLUMN2)

ROLLUP 语句的工作方式是，完成基本的分组数据汇总之后，去掉字段列表中的最后一个字段，再次进行统计，被去掉的字段会被填充为 NULL，最后再对全表进行一次统计，所有字段位置均使用 NULL。字段列表以外的字段将不受影响。

CUBE 语句

GROUP BY CUBE(COLUMN1,COLUMN2)

CUBE 语句与 ROLLUP 语句很类似，但是 CUBE 语句会针对字段列表中的字段的所有组合进行统计汇总。

HAVING 子句

HAVING CONDITIONS

HAVING 语句告诉 ORDER BY 子句在输出里需要包含哪些分组。HAVING 的位置是在 GROUP BY 子句之后，在 ORDER BY 子句之前。

调整数据的外观

ANSI 字符函数

字符函数用于以不同于储存方式的格式来表示字符串。

串接函数
串接函数将两个字段或者字符串串接起来，中间没有空格。在不同的 SQL 数据库中传戒函数的实现略有不同。

SQL Server

COLUMN_NAME + COLUMN_NAME

MySQL

SELECT CONCAT(COLUMN_NAME, COLUMN_NAME)

Oracle

COLUMN_NAME || COLUMN_NAME

TRANSLATE 函数搜索字符串中的字符并查找特定的字符，然后替换掉。

TRANSLATE(CHARACTER SET, VALUE1， VALUE2)

以下代码把结果中的'I'全部替换成'A'，'N'全部替换成'B'。

SELECT TRANSLATE(CITY, 'IND', 'ABC')
FROM EMPLOYEE_TBL

REPLACE 函数用于把字符串替换为字符串。

REPLACE('VALUE', 'VALUE', 'VALUE')

以下代码将结果中的'T'全部换成'B'

SELECT CITY, REPLACE(CITY, 'I', 'Z')

UPPER 函数可以把字符串里的小写字母全部转化为大写。

UPPER(character string)

LOWER 函数把字符串里的大写字符转化为小写字符。

UPPER(character string)

SUBSTR 函数获取字符串的子串，在 SQL Server 和 MySQL 中是 SUBSTRING 函数。

SUBSTR(COLUMN NAME, STARTING POSITION, LENGTH)

INSTR 函数用于在字符串里寻找指定的字符集，返回其所在的位置。

INSTR(COLUMN NAME, 'SET',
[ START POSITION ，[ OCCURRENCE ] ])

下面的 SQL 语句表示查找个 STATE 中字母 I 第一次出现的位置。

INSTR(STATE, 'I', 1, 1)

在 SQL Server 中要使用 CHARINDEX 函数

CHARINDEX('SET', COLUMN NAME, START POSITION)

LTRIM 函数用于从左剪除字符串中的字符。

LTRIM(CHARACTER STRING [ ,'set' ])

RTRIM 函数用于从右剪除字符串中的字符。

其他字符函数

LENGTH 函数

LENGTH(CHARACTER STRING)

在 SQL Server 中为 LEN 函数。
- COALESCE 函数用指定值替代 NULL 值，它接受一个数据集，依次检查其中每一个值，直到发现一个非 NULL 值，如果没有发现非 NULL 值，则返回一个 NULL 值。
- LPAD 函数用于在字符串左侧添加字符或者空格。

LPAD(COLUMN NAME, LENGTH, CHARACTER)

RPAD 函数用于在字符串右侧添加字符或者空格。
ASCII 函数接受一个字符，返回它的 ASCII 码。

算术函数

ABS：绝对值
ROUND：舍入
SQRT：平方根
SIGN：符号
POWER：幂
CEIL：上限
FLOOR：下限
EXP：指数
SIN、COS、TAN

日期和时间

日期和时间的储存方式

标准的储存时间的数据类型有三种。

DATE：YYYY-MM-DD
TIME：HH:MI:SS.nn...
TIMESTAMP：YYYY-MM-DD HH:MI:SS.nn...

在不同的数据库中都有不同的实现。

日期函数

当前日期

SQL Server 中使用 GETDATE 函数获取日期。
My SQL 中使用 NOW 函数获取当前日期和时间。
Oracle 中使用 SYSDATE 函数获取日期和时间。

SELECT SYSDATE FROM DUAL

时间与日期相加

SQL Server

DATEADD(unit, expr, date)

Oracle

ADD_MONTHS(date, expr)

MySQL

DATE_ADD(date, INTERVAL expr unit)

除了日期函数以外这些数据库还都支持直接与天数的数值相加。

日期转换为字符串

SQL Server

DATENAME(format, date)

Oracel

TO_CHAR(date, format)

其中的 format 字符串都是使用日期元素组成的，不同的实现里有不同的日期元素。

字符串转换为日期

MySQL

STR_TO_DATE(str, format)

数据库查询