SQL语句总结(1)

Yeolar   2013-10-29 17:57  

基本SQL命令

SELECT

从表中选择数据。

SELECT "column_name" FROM "table_name";

例:

SELECT Store_Name FROM Store_Information;           -- 选择单列
SELECT Store_Name, Sales FROM Store_Information;    -- 选择多列
SELECT * FROM Store_Information;                    -- 选择全部列

Table: Store_Information:

Store_Name  Sales Txn_Date
----------- ----- ----------- =>
Los Angeles  1500 Jan-05-1999
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

Store_Name     Store_Name  Sales    Store_Name  Sales Txn_Date
-----------    ----------- -----    ----------- ----- -----------
Los Angeles    Los Angeles  1500    Los Angeles  1500 Jan-05-1999
San Diego      San Diego     250    San Diego     250 Jan-07-1999
Los Angeles    Los Angeles   300    Los Angeles   300 Jan-08-1999
Boston         Boston        700    Boston        700 Jan-08-1999

DISTINCT

SELECT 的基础上取不重复的元素。Oracle的 SELECT UNIQUE 和其等价。

SELECT DISTINCT "column_name"
FROM "table_name";

SELECT UNIQUE "column_name"
FROM "table_name";              -- Oracle

例:

SELECT DISTINCT Store_Name FROM Store_Information;

Table: Store_Information:

Store_Name  Sales Txn_Date       Store_Name
----------- ----- ----------- => -----------
Los Angeles  1500 Jan-05-1999    Los Angeles
San Diego     250 Jan-07-1999    San Diego
Los Angeles   300 Jan-08-1999    Boston
Boston        700 Jan-08-1999

WHERE

在取数据时增加一定的条件判定。

SELECT "column_name"
FROM "table_name"
WHERE "condition";

例:

SELECT Store_Name FROM Store_Information
WHERE Sales > 1000;

Table: Store_Information:

Store_Name  Sales Txn_Date       Store_Name
----------- ----- ----------- => -----------
Los Angeles  1500 Jan-05-1999    Los Angeles
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

AND, OR

可以用 ANDOR 连接多个简单条件,条件数无限制。

SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{ [AND|OR] "simple condition"}+;

例:

SELECT Store_Name FROM Store_Information
WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275);

Table: Store_Information:

Store_Name    Sales Txn_Date       Store_Name
------------- ----- ----------- => -------------
Los Angeles    1500 Jan-05-1999    Los Angeles
San Diego       250 Jan-07-1999    San Francisco
San Francisco   300 Jan-08-1999
Boston          700 Jan-08-1999

IN

WHERE 语句中,使用 IN 可以限定列中元素的值。括号中的值可以有1或多个,只有1个时等价于使用 = 判定。

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...);

例:

SELECT * FROM Store_Information
WHERE Store_Name IN ('Los Angeles', 'San Diego');

Table: Store_Information:

Store_Name    Sales Txn_Date       Store_Name  Sales Txn_Date
------------- ----- ----------- => ----------- ----- -----------
Los Angeles    1500 Jan-05-1999    Los Angeles  1500 Jan-05-1999
San Diego       250 Jan-07-1999    San Diego     250 Jan-07-1999
San Francisco   300 Jan-08-1999
Boston          700 Jan-08-1999

BETWEEN

IN 用来限制到一些离散的值,而 BETWEEN 用来选择一个范围。注意范围的两端值也包括在内。

SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2';

例:

SELECT * FROM Store_Information
WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';

SELECT * FROM Store_Information
WHERE Sales NOT BETWEEN 280 AND 1000;           -- NOT排除选择的范围

Table: Store_Information:

Store_Name    Sales Txn_Date
------------- ----- ----------- =>
Los Angeles    1500 Jan-05-1999
San Diego       250 Jan-07-1999
San Francisco   300 Jan-08-1999
Boston          700 Jan-08-1999

Store_Name  Sales Txn_Date       Store_Name  Sales Txn_Date
----------- ----- -----------    ----------- ----- -----------
San Diego     250 Jan-07-1999    Los Angeles  1500 Jan-05-1999
Los Angeles   300 Jan-08-1999    San Diego     250 Jan-07-1999
Boston        700 Jan-08-1999

LIKE

LIKE 可以按模式匹配来选择。

SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN};

{PATTERN} 一般由通配符组成,SQL中有两个通配符:

  • % 表示0、1或多个字符
  • _ 表示1个字符

例:

SELECT * FROM Store_Information
WHERE Store_Name LIKE '%AN%';

Table: Store_Information:

Store_Name    Sales Txn_Date       Store_Name    Sales Txn_Date
------------- ----- ----------- => ------------- ----- -----------
LOS ANGELES    1500 Jan-05-1999    LOS ANGELES    1500 Jan-05-1999
SAN DIEGO       250 Jan-07-1999    SAN DIEGO       250 Jan-07-1999
SAN FRANCISCO   300 Jan-08-1999    SAN FRANCISCO   300 Jan-08-1999
BOSTON          700 Jan-08-1999

ORDER BY

ORDER BY 可以将结果按指定列排序, ASC 为升序, DESC 为降序,默认为升序。可以指定多个列,在前一个列的值相等时,按后面指定的列排序。

SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]{,"column_name" [ASC, DESC]}+;

例:

SELECT Store_Name, Sales, Txn_Date FROM Store_Information
ORDER BY Sales DESC;

SELECT Store_Name, Sales, Txn_Date FROM Store_Information
ORDER BY 2 DESC;                            -- 可以用数字指定列,1为第一列

SELECT Store_Name FROM Store_Information
ORDER BY Sales DESC;                        -- 排序的列可以不在选择的列中

SELECT Product_ID, Price*Units Revenue FROM Product_Sales
ORDER BY Price*Units DESC;                  -- 可以使用表达式

Table: Store_Information:

Store_Name    Sales Txn_Date       Store_Name    Sales Txn_Date       Store_Name
------------- ----- ----------- => ------------- ----- -----------    -------------
Los Angeles    1500 Jan-05-1999    Los Angeles    1500 Jan-05-1999    Los Angeles
San Diego       250 Jan-07-1999    Boston          700 Jan-08-1999    Boston
San Francisco   300 Jan-08-1999    San Francisco   300 Jan-08-1999    San Francisco
Boston          700 Jan-08-1999    San Diego       250 Jan-07-1999    San Diego

Table: Product_Sales:

Product_ID Price Units    Product_ID Revenue
---------- ----- ----- => ---------- -------
    1        10    9           1        90
    2        15    4           3        75
    3        25    3           2        60

GROUP BY

按列分组。 SELECT 语句中需要有算数函数(聚合函数),而分组只能用于其他的列。可以对多个列分组。

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1";

例:

SELECT Store_Name, SUM(Sales) FROM Store_Information
GROUP BY Store_Name;

Table: Store_Information:

Store_Name  Sales Txn_Date       Store_Name  SUM(Sales)
----------- ----- ----------- => ----------- ----------
Los Angeles  1500 Jan-05-1999    Los Angeles       1800
San Diego     250 Jan-07-1999    San Diego          250
Los Angeles   300 Jan-08-1999    Boston             700
Boston        700 Jan-08-1999

HAVING

如果希望基于聚合函数做限制,需要使用 HAVING 代替 WHERE

SELECT ["column_name1"], Function("column_name2")
FROM "table_name"
[GROUP BY "column_name1"]
HAVING (arithmetic function condition);

例:

SELECT Store_Name, SUM(Sales) FROM Store_Information
GROUP BY Store_Name
HAVING SUM(Sales) > 1500;

Table: Store_Information:

Store_Name  Sales Txn_Date       Store_Name  SUM(Sales)
----------- ----- ----------- => ----------- ----------
Los Angeles  1500 Jan-05-1999    Los Angeles       1800
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

别名和 AS

有两种常用的别名:列别名和表别名。列别名可以增强可读性,表别名方便连接操作。直接在列和表之后加上别名,也可以用 AS 关键字。

SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias";

SELECT "table_alias"."column_name1" AS "column_alias"
FROM "table_name" AS "table_alias";

例:

SELECT A1.Store_Name Store, SUM(A1.Sales) "Total Sales"
FROM Store_Information A1
GROUP BY A1.Store_Name;

SELECT A1.Store_Name Store, SUM(A1.Sales) AS "Total Sales"
FROM Store_Information AS A1
GROUP BY A1.Store_Name;

Table: Store_Information:

Store_Name  Sales Txn_Date       Store       Total Sales
----------- ----- ----------- => ----------- -----------
Los Angeles  1500 Jan-05-1999    Los Angeles        1800
San Diego     250 Jan-07-1999    San Diego           250
Los Angeles   300 Jan-08-1999    Boston              700
Boston        700 Jan-08-1999

INSERT INTO

向表中插入行数据。向表中插入数据有两种基本的方式:一次插入一行,一次插入多行。前者的语法如下:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);

例:

INSERT INTO Store_Information (Store_Name, Manager_ID, Sales, Txn_Date)
VALUES ('Los Angeles', 10, 900, 'Jan-10-1999');     -- 列的顺序任意

Table: Store_Information:

  Column Name Data Type
  ----------- ---------
  Store_Name  char(50)
  Manager_ID  integer
  Sales       float
  Txn_Date    datetime

  Store_Name  Manager_ID Sales Txn_Date
> ----------- ---------- ----- -----------
  Los Angeles         10   900 Jan-10-1999

可以不指定列名,这时列数据的顺序须与表结构一致。

例:

INSERT INTO Store_Information
VALUES ('Los Angeles', 10, 900, 'Jan-10-1999');

如果只插入部分列数据,可以忽略其他的列,这时它们为 NULL

例:

INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
VALUES ('New York', 500, 'Jan-10-1999');

一次插入多行的语法如下:

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";

更复杂的语法还可以包括 GROUP BYHAVING 、连接、别名。

例:

INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
SELECT Store_Name, SUM(Sales), Txn_Date
FROM Sales_Information
GROUP BY Store_Name, Txn_Date;

Table: Store_Information:

Column Name Data Type
----------- ---------
Store_Name  char(50)
Sales       float
Txn_Date    datetime

Table: Sales_Data:

Column Name Data Type
----------- ---------
Store_Name  char(50)
Product_ID  integer
Sales       float
Txn_Date    datetime

UPDATE

修改数据。

UPDATE "table_name"
SET "column_1" = [new value]
WHERE "condition";

例:

UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Txn_Date = 'Jan-08-1999';

Table: Store_Information:

Store_Name  Sales Txn_Date       Store_Name  Sales Txn_Date
----------- ----- ----------- => ----------- ----- -----------
Los Angeles  1500 Jan-05-1999    Los Angeles  1500 Jan-05-1999
San Diego     250 Jan-07-1999    San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999    Los Angeles   500 Jan-08-1999
Boston        700 Jan-08-1999    Boston        700 Jan-08-1999

修改多列数据的语法为:

UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2]
WHERE "condition";

例:

UPDATE Store_Information
SET Sales = 600, Txn_Date = 'Jan-15-1999'
WHERE Store_Name = 'San Diego';

Table: Store_Information:

Store_Name  Sales Txn_Date       Store_Name  Sales Txn_Date
----------- ----- ----------- => ----------- ----- -----------
Los Angeles  1500 Jan-05-1999    Los Angeles  1500 Jan-05-1999
San Diego     250 Jan-07-1999    San Diego     600 Jan-15-1999
Los Angeles   500 Jan-08-1999    Los Angeles   500 Jan-08-1999
Boston        700 Jan-08-1999    Boston        700 Jan-08-1999

使用 UPDATE 时,注意指定条件,否则会修改全部的列。

DELETE FROM

从表中删除数据。

DELETE FROM "table_name"
WHERE "condition";

例:

DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';

Table: Store_Information:

Store_Name  Sales Txn_Date       Store_Name  Sales Txn_Date
----------- ----- ----------- => ----------- ----- -----------
Los Angeles  1500 Jan-05-1999    San Diego     250 Jan-07-1999
San Diego     250 Jan-07-1999    Boston        700 Jan-08-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

DELETE FROM 命令不能删除违反外键或其他约束的行数据。

JOIN

连接多个表进行操作。

例:

SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.Store_Name = A2.Store_Name
GROUP BY A1.Region_Name;

SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
JOIN Store_Information A2 ON A1.Store_Name = A2.Store_Name
GROUP BY A1.Region_Name;                    -- 使用JOIN ON

Table: Store_Information, Geography:

Store_Name  Sales Txn_Date       Region_Name Store_Name     REGION SALES
----------- ----- -----------    ----------- ----------- => ------ -----
Los Angeles  1500 Jan-05-1999    East        Boston         East     700
San Diego     250 Jan-07-1999    East        New York       West    2050
Los Angeles   300 Jan-08-1999    West        Los Angeles
Boston        700 Jan-08-1999    West        San Diego

内连接

SQL的内连接返回在两个表中都有的行。

例:

SELECT A1.Store_Name STORE, SUM(A2.Sales) SALES
FROM Geography A1
INNER JOIN Store_Information A2 ON A1.Store_Name = A2.Store_Name
GROUP BY A1.Store_Name;

Table: Store_Information, Geography:

Store_Name  Sales Txn_Date       Region_Name Store_Name     STORE       SALES
----------- ----- -----------    ----------- ----------- => ----------- -----
Los Angeles  1500 Jan-05-1999    East        Boston         Los Angeles  1800
San Diego     250 Jan-07-1999    East        New York       San Diego     250
Los Angeles   300 Jan-08-1999    West        Los Angeles    Boston        700
Boston        700 Jan-08-1999    West        San Diego

外连接

和内连接相反,如果想显示全部行,使用外连接。Oracle中,在想要包括全部行的表的另一侧加上 (+)

例:

SELECT A1.Store_Name, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.Store_Name = A2.Store_Name (+)
GROUP BY A1.Store_Name;

Table: Store_Information, Geography:

Store_Name  Sales Txn_Date       Region_Name Store_Name     Store_Name  SALES
----------- ----- -----------    ----------- ----------- => ----------- -----
Los Angeles  1500 Jan-05-1999    East        Boston         Boston        700
San Diego     250 Jan-07-1999    East        New York       New York
Los Angeles   300 Jan-08-1999    West        Los Angeles    Los Angeles  1800
Boston        700 Jan-08-1999    West        San Diego      San Diego     250

左外连接

左外连接中,第一个表中的所有行都会被选择,不管是否匹配到第二个表的行。

例:

SELECT A1.Store_Name STORE, SUM(A2.Sales) SALES
FROM Geography A1
LEFT OUTER JOIN Store_Information A2 ON A1.Store_Name = A2.Store_Name
GROUP BY A1.Store_Name;

Table: Store_Information, Geography:

Store_Name  Sales Txn_Date       Region_Name Store_Name     STORE       SALES
----------- ----- -----------    ----------- ----------- => ----------- -----
Los Angeles  1500 Jan-05-1999    East        Boston         Los Angeles  1800
San Diego     250 Jan-07-1999    East        New York       San Diego     250
Los Angeles   300 Jan-08-1999    West        Los Angeles    New York     NULL
Boston        700 Jan-08-1999    West        San Diego      Boston        700

交叉连接

交叉连接不指定连接的条件。查询会返回所有可能的组合。

例:

SELECT A1.Store_Name STORE1, A2.Store_Name STORE2, A2.Sales SALES
FROM Geography A1
JOIN Store_Information A2;

SELECT A1.store_name STORE1, A2.store_name STORE2, A2.Sales SALES
FROM Geography A1, Store_Information A2;            -- 等价

Table: Store_Information, Geography:

Store_Name  Sales Txn_Date       Region_Name Store_Name
----------- ----- -----------    ----------- ----------- =>
Los Angeles  1500 Jan-05-1999    East        Boston
San Diego     250 Jan-07-1999    East        New York
Los Angeles   300 Jan-08-1999    West        Los Angeles
Boston        700 Jan-08-1999    West        San Diego

STORE1      STORE2      SALES
----------- ----------- -----
Boston      Los Angeles  1500
New York    Los Angeles  1500
Los Angeles Los Angeles  1500
San Diego   Los Angeles  1500
Boston      San Diego     250
New York    San Diego     250
Los Angeles San Diego     250
San Diego   San Diego     250
Boston      Los Angeles   300
New York    Los Angeles   300
Los Angeles Los Angeles   300
San Diego   Los Angeles   300
Boston      Boston        700
New York    Boston        700
Los Angeles Boston        700
San Diego   Boston        700

聚合函数

聚合函数可以对数字做运算。

SELECT "function type" ("column_name")
FROM "table_name";

AVG

求列的平均。

SELECT AVG("column_name")
FROM "table_name";

例:

SELECT AVG(Sales) FROM Store_Information;

Table: Store_Information:

Store_Name  Sales Txn_Date       AVG(Sales)
----------- ----- ----------- => ----------
Los Angeles  1500 Jan-05-1999       687.5
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

COUNT

求行的数量。

SELECT COUNT("column_name")
FROM "table_name";

例:

SELECT COUNT(Store_Name) FROM Store_Information;
SELECT COUNT(DISTINCT Store_Name) FROM Store_Information;

Table: Store_Information:

Store_Name  Sales Txn_Date       COUNT(Store_Name)    COUNT(DISTINCT Store_Name)
----------- ----- ----------- => -----------------    --------------------------
Los Angeles  1500 Jan-05-1999           4                          3
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

MAX

找出列的最大值。

SELECT MAX("column_name")
FROM "table_name";

例:

SELECT MAX(Sales) FROM Store_Information;

Table: Store_Information:

Store_Name  Sales Txn_Date       MAX(Sales)
----------- ----- ----------- => ----------
Los Angeles  1500 Jan-05-1999       1500
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

MIN

找出列的最小值。

SELECT MIN("column_name")
FROM "table_name";

例:

SELECT MIN(Sales) FROM Store_Information;

Table: Store_Information:

Store_Name  Sales Txn_Date       MIN(Sales)
----------- ----- ----------- => ----------
Los Angeles  1500 Jan-05-1999       250
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

SUM

求列的和。

SELECT SUM("column_name")
FROM "table_name";

例:

SELECT SUM(Sales) FROM Store_Information;

Table: Store_Information:

Store_Name  Sales Txn_Date       SUM(Sales)
----------- ----- ----------- => ----------
Los Angeles  1500 Jan-05-1999       2750
San Diego     250 Jan-07-1999
Los Angeles   300 Jan-08-1999
Boston        700 Jan-08-1999

ROUND

用来四舍五入一个数到指定的精度。精度位置为负时表示小数点左边的精度。

ROUND(expression, [decimal place])

例:

SELECT First_Name, ROUND(Rating, 1) Rounded_Score FROM Student_Rating;
SELECT First_Name, ROUND(Rating, -1) Rounded_Score FROM Student_Rating;

Table: Student_Rating:

StudentID First_Name Rating    First_Name Rounded_Score    First_Name Rounded_Score
--------- ---------- ------ => ---------- -------------    ---------- -------------
    1     Jenny      85.235    Jenny               85.2    Jenny                 90
    2     Bob         92.52    Bob                 92.5    Bob                   90
    3     Alice         3.9    Alice                3.9    Alice                  0
    4     James       120.1    James              120.1    James                120

字符串函数

CAST

转换类型,类型必须是有效的。转换的规则不同的数据库会有所不同。

CAST(expression AS [data type])

例:

SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Score;
SELECT First_Name, CAST(Score AS char(3)) Char_Score FROM Student_Score;

Table: Student_Score:

StudentID First_Name Score    First_Name Int_Score    First_Name Char_Score
--------- ---------- ----- => ---------- ---------    ---------- ----------
    1     Jenny       85.2    Jenny             85    Jenny             85.
    2     Bob         92.5    Bob               92    Bob               92.
    3     Alice         90    Alice             90    Alice              90
    4     James      120.1    James            120    James             120

CONVERT

在MySQL和SQL Server中, CONVERT 函数和 CAST 函数类似。

CONVERT(expression, [data type])

例:

SELECT First_Name, CONVERT(Score, Integer) Int_Score FROM Student_Score;

Table: Student_Score:

StudentID First_Name Score    First_Name Int_Score
--------- ---------- ----- => ---------- ---------
    1     Jenny       85.2    Jenny             85
    2     Bob         92.5    Bob               92
    3     Alice         90    Alice             90
    4     James      120.1    James            120

在Oracle中, CONVERT 则用来转换字符串的字符集。

CONVERT(string, [new character set], [original character set])

CONCAT

连接字符串。不同的数据库有些不同:

  • MySQL: CONCAT()
  • Oracle: CONCAT(), || (Oracle的 CONCAT 函数只能传入两个参数)
  • SQL Server: +
CONCAT(str1, str2, str3, ...)

例:

SELECT CONCAT(Region_Name, Store_Name) FROM Geography
WHERE Store_Name = 'Boston';                    -- MySQL/Oracle

SELECT Region_Name || ' ' || Store_Name FROM Geography
WHERE Store_Name = 'Boston';                    -- Oracle

SELECT Region_Name + ' ' + Store_Name FROM Geography
WHERE Store_Name = 'Boston';                    -- SQL Server

Table: Geography:

Region_Name Store_Name
----------- ----------- => EastBoston    East Boston    East Boston
East        Boston
East        New York
West        Los Angeles
West        San Diego

SUBSTR

返回字符串的一部分。不同的数据库也不同:

  • MySQL: SUBSTR(), SUBSTRING()
  • Oracle: SUBSTR()
  • SQL Server: SUBSTRING()

可以用在 SELECTWHEREORDER BY 分句中。

SUBSTR(str, position, [length])

在MySQL和Oracle中, length 是可选的,如果不指定,则返回直到结束的全部字符。

例:

SELECT SUBSTR(Store_Name, 3) FROM Geography
WHERE Store_Name = 'Los Angeles';

SELECT SUBSTR(Store_Name, 2, 4) FROM Geography
WHERE Store_Name = 'San Diego';

SELECT Store_Name FROM Geography
ORDER BY SUBSTR(Store_Name, 2, 4);

Table: Geography:

Region_Name Store_Name
----------- ----------- =>
East        Chicago
East        New York
West        Los Angeles
West        San Diego

SUBSTR(Store_Name, 3)    SUBSTR(Store_Name, 2, 4)    Store_Name
---------------------    ------------------------    ----------
s Angeles                an D                        San Diego
                                                     New York
                                                     Chicago
                                                     Los Angeles

INSTR

在字符串中查找指定模式的开始位置。只在MySQL和Oracle中可用。

INSTR(str, pattern)                                         -- MySQL
INSTR(str, pattern, [starting position, [nth location]])    -- Oracle

例:

SELECT INSTR (Store_Name, 'o') FROM Geography
WHERE Store_Name = 'Los Angeles';

SELECT INSTR (Store_Name, 'p') FROM Geography
WHERE Store_Name = 'Los Angeles';

SELECT INSTR(Store_Name,'e', 1, 2) FROM Geography
WHERE Store_Name = 'Los Angeles';                   -- 只在Oracle中可用

Table: Geography:

Region_Name Store_Name
----------- ----------- => 2    0    10
East        Boston
East        New York
West        Los Angeles
West        San Diego

TRIM

删除字符串的指定的开头结尾,常用来删除空白字符。

  • MySQL: TRIM(), LTRIM(), RTRIM()
  • Oracle: LTRIM(), RTRIM()
  • SQL Server: LTRIM(), RTRIM()
TRIM( [ [LOCATION] [remstr] FROM ] str)

[LOCATION] 可以是 LEADINGTRAILINGBOTH 。如果 [remstr] 未指定,删除空白字符。

LTRIM(str)      -- 从字符串开头删除全部空白字符
RTRIM(str)      -- 从字符串结尾删除全部空白字符

例:

SELECT TRIM('   Sample   ');    -- 'Sample'
SELECT LTRIM('   Sample   ');   -- 'Sample   '
SELECT RTRIM('   Sample   ');   -- '   Sample'

LENGTH

获取字符串的长度。

LENGTH(str)     -- SQL Server为LEN(str)

例:

SELECT Length (Store_Name) FROM Geography
WHERE Store_Name = 'Los Angeles';

SELECT Region_Name, Length (Region_Name) FROM Geography;

Table: Geography:

Region_Name Store_Name     Length (Store_Name)    Region_Name Length (Region_Name)
----------- ----------- => -------------------    ----------- --------------------
East        Boston                 11             East                 4
East        New York                              East                 4
West        Los Angeles                           West                 4
West        San Diego                             West                 4

REPLACE

修改字符串。

REPLACE(str1, str2, str3)

str1 中查找 str2 ,替换为 str3

例:

SELECT REPLACE(Region_Name, 'ast', 'astern') REGION1 FROM Geography;

Table: Geography:

Region_Name Store_Name     REGION1
----------- ----------- => -------
East        Boston         Eastern
East        New York       Eastern
West        Los Angeles    West
West        San Diego      West

TO_DATE

在Oracle中用来将字符串转换为时间。

TO_DATE( String, [Format], [Optional Setting] )

例:

SELECT TO_DATE('20100105', 'YYYYMMDD') FROM DUAL;
-- 2010 01 05 00:00:00
SELECT TO_DATE('1999-JAN-05', 'YYYY-MON-DD') FROM DUAL;
-- 1999 01 05 00:00:00
SELECT TO_DATE('2005-12-12 03600', 'YYYY-MM-DD SSSSS') FROM DUAL;
-- 2005 12 12 01:00:00
SELECT TO_DATE('2005 120 05400', 'YYYY DDD SSSSS') FROM DUAL;
-- 2005 04 30 01:30:00
SELECT TO_DATE('99-JAN-05', 'YY-MON-DD') FROM DUAL;
-- 2099 01 05 00:00:00
SELECT TO_DATE('99-JAN-05', 'RR-MON-DD') FROM DUAL;
-- 1999 01 05 00:00:00

日期函数

DATEADD

在SQL Server中用来修改时间。

DATEADD(datepart, number, expression)

例:

SELECT DATEADD (day, 10, '2000-01-05 00:05:00.000');
-- '2000-01-15 00:05:00.000'

DATEDIFF

在MySQL和SQL Server中用于计算两个时间的差值。两个数据库中的用法不同。

-- MySQL:      = expression1 - expression2
DATEDIFF(expression1, expression2)
-- SQL Server: = expression2 - expression1
DATEDIFF(datepart, expression1, expression2)

例:

SELECT DATEDIFF ('2000-01-10', '2000-01-05');       -- 5
SELECT DATEDIFF (day, '2000-01-10','2000-01-05');   -- -5

DATEPART

在SQL Server中用来取时间值的指定部分。

DATEPART(part_of_day, expression)

例:

SELECT DATEPART (yyyy, '2000-01-20');   -- 2000
SELECT DATEPART (dy, '2000-02-10');     -- 41

SYSDATE, GETDATE

读取当前数据库系统时间。

SYSDATE         -- Oracle
SYSDATE()       -- MySQL
GETDATE()       -- SQL Server

例:

SELECT SYSDATE;         -- 16-JAN-2000
SELECT SYSDATE();       -- 2000-01-16 09:06:22
SELECT GETDATE();       -- '2000-03-15 00:05:02.123'

参考: http://www.1keydata.com/sql/sql.html

http://www.yeolar.com/note/2013/10/29/sql-tutorial/

http://www.yeolar.com/note/2013/10/29/sql-tutorial/