sql教程二

一、select语句

    在数据库操作中使用最多的无疑使select语句。

    select最基础的最用是从数据库表中提取信息。

    为了能够详细讲解select语句，我们首先必须要使用前面两个语句创建数据库表并插入数据：

Create table store_information(

         Store_namechar(50),

         Salesfloat,

         datedate

);

INSERT INTOStore_Information (store_name, sales, date) VALUES ('Los Angeles', 1500, '1999-01-05');

INSERT INTOStore_Information (store_name, sales, date) VALUES ('San Diego', 250, '1999-01-07');

INSERT INTOStore_Information (store_name, sales, date) VALUES ('Los Angeles', 300, '1999-01-08');

INSERT INTOStore_Information (store_name, sales, date) VALUES ('Boston', 700, '1999-01-08');

插入数据后表的内容如下：

mysql> select * from store_information;

+-------------+-------+------------+

| store_name  | sales | date       |

+-------------+-------+------------+

| Los Angeles |  1500 | 1999-01-05 |

| San Diego   |  250 | 1999-01-07 |

| Los Angeles |   300 | 1999-01-08 |

| Boston      |  700 | 1999-01-08 |

+-------------+-------+------------+

4 rows in set (0.00 sec)

 

1.1、最基本的select语句结构为：SELECT "栏位名" FROM "表格名"

    例如要选出所有店名(stor_name)，我们只需输入：

SELECT store_name FROM Store_Information

结果为: store_name

Los Angeles

San Diego

Los Angele

Boston

 

1.2、（ditinct）在做项目时，我们会经常碰到需要找出表格内的不同值的情况。换句话说，我们需要知道这个表格/栏位内有哪些不同的值，而每个值出现的次数并不重要。这要如何达成呢？在 SQL 中，这是很容易做到的。我们只要在 SELECT 后加上一个DISTINCT就可以了。DISTINCT 的语法如下：

    SELECT DISTINCT"栏位名" FROM "表格名"

举例来说，若要在上述表格中找出所有不同的店名时，只需输入命令：

         SELECT DISTINCT store_name FROM Store_Information

结果为：

    store_name

    Los Angeles

    San Diego

    Boston

 

1.3、(where) 我们并不一定每一次都要将表格内的资料都完全抓出。在许多时候，我们会需要选择性地抓资料。就我们的例子来说，我们可能只要抓出营业额超过$1,000的资料。要做到这一点，我们就需要用到WHERE 这个指令。这个指令的语法如下：

    SELECT"栏位名"FROM "表格名"WHERE "条件"

若我们要在上述建立的表格中抓出营业额超过 $1,000 的资料，只需输入命令：

     SELECTstore_name FROM Store_Information WHERE Sales > 1000

结果为: store_name

Los Angeles

3.4、（AND、OR）在上一页中，我们看到 WHERE 指令可以被用来由表格中有条件地选取资料。 这个条件可能是简单的，也可能是复杂的。复杂条件是由二或多个简单条件透过AND或是 OR 的连接而成。复杂条件的语法如下：

SELECT"栏位名"FROM "表格名"WHERE "简单条件"{[AND|OR] "简单条件"}

    {}代表{}之内的情况会发生一或多次。在这里的意思就是 AND 加简单条件及 OR加简单条件的情况可以发生一或多次。另外，我们可以用 () 来代表条件的先后次序。 

    举例来说，我们若要在 Store_Information表格中选出所有 Sales 高于 $1,000 或是 Sales 在 $500 及$275 之间的资料的话，只需输入命令：

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

    结果为：

store_name

        Los Angeles

        Los Angeles

 

1.5、（IN）在 SQL 中，在两种情况下会用到IN这个指令，这一次讲解与 WHERE 有关的那一种情况。在这个用法下，我们事先已知道至少一个我们需要的值，而我们将这些知道的值都放入IN 这个子句。 IN 指令的语法为下：

SELECT"栏位名"FROM "表格名" WHERE"栏位名" IN('值一', '值二', ...)

    在括弧内可以有一或多个值，而不同值之间由逗点分开。值可以是数目或是文字。若在括弧内只有一个值，那这个子句就等于：WHERE "栏位名" = '值一'

    举例来说，若我们要在 Store_Information 表格中找出所有含盖 LosAngeles 或 San Diego 的资料，只需输入命令：

SELECT * FROM Store_Information WHEREstore_name IN ('Los Angeles', 'San Diego')

1.6、（Between）BETWEEN 使我们可以运用一个范围 (range) 内抓出数据库中的值。BETWEEN这个子句的语法如下：

     SELECT "栏位名" FROM " 表格名" WHERE "栏位名" BETWEEN '值一' AND '值二'

    举例来说，若我们要由 Store_Information 表格中找出所有介于 600 及 2000 中的资料，我们只需输入命令：

SELECT * FROM Store_Information WHERE salesBETWEEN 600 AND 2000;

1.7、（Like）LIKE 能让我们依据一个套式 (pattern) 来找出我们要的资料。LIKE 的语法如下：

SELECT "栏位名" FROM "表格名" WHERE "栏位名" LIKE {套式}

    {套式} 经常包括野卡 (wildcard). 以下是几个例子：

• 'A_Z': 所有以 'A' 起头，另一个任何值的字原，且以 'Z' 为结尾的字串。 'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字原，而不是一个字原)。

• 'ABC%': 所有以 'ABC' 起头的字串。举例来说，'ABCD' 和 'ABCABC' 都符合这个套式。

• '%XYZ': 所有以 'XYZ' 结尾的字串。举例来说，'WXYZ' 和 'ZZXYZ' 都符合这个套式。

• '%AN%': 所有含有 'AN' 这个套式的字串。举例来说， 'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个套式。

举例来说，使用的形如：  

SELECT * FROM Store_InformationWHERE store_name LIKE '%AN%'

1.8、（Order by）我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大 (ascending)或是由大往小(descending)。在这种情况下，我们就可以运用ORDER BY这个指令来达到我们的目的。ORDER BY 的语法如下:

  SELECT "栏位名" FROM "表格名"[WHERE "条件"]ORDER BY "栏位名"[ASC, DESC]

    Asc表示由小往大，desc代表由大往小，默认是asc。

    我们可以照好几个不同的栏位来排顺序。在这个情况下，ORDERBY 子句的语法如下(假设有两个栏位)： ORDER BY "栏位一"[ASC, DESC], "栏位二"[ASC, DESC]

    举例来说，若我们要依照Sales栏位的由大往小列出 Store_Information表格中的资料，我们就输入：

SELECT store_name, Sales, Date FROM Store_InformationORDER BY Sales DESC

1.9、（函数）SQL 提供一些的函数。它们是：AVG (平均) ，COUNT(计数) ，MAX (最大值)，MIN (最小值)， SUM (总合)，运用函数的语法是：

SELECT"函数名"("栏位名")FROM "表格名"

    举例来说，若我们要由我们的示范表格中求出 Sales 栏位的总合，只需输入：

      SELECT SUM(Sales) FROM Store_Information;

    除了函数的运用外，SQL 也可以做简单的数学运算，例如加(+)和减(-)。对于文字类的资料，SQL 也有好几个文字处理方面的函数，例如文字相连 (concatenation)，文字修整 (trim)，以及子字串 (substring)。不同的数据库对这些函数有不同的语法，所以最好是参考您所用数据库的信息，来确定在那个数据库中，这些函数是如何被运用的。

 

1.10、（Count）在上一页有提到，COUNT 是函数之一。由于它的使用广泛，我们在这里特别提出来讨论。基本上，COUNT 让我们能够数出在表格中有多少笔资料被选出来。它的语法是：

SELECTCOUNT("栏位名")FROM "表格名"

    举例来说，若我们要找出我们的示范表格中有几笔store_name栏不是空白的资料时，只需输入：

SELECT COUNT(store_name) FROM Store_Information WHERE store_name is notNULL

    COUNT 和 DISTINCT 经常被合起来使用，目的是找出表格中有多少笔不同的资料：

SELECT COUNT(DISTINCT store_name) FROM Store_Information

 

1.11、（Group By）Groupby的语法为：

SELECT "栏位1",SUM("栏位2") FROM "表格名" GROUP BY "栏位1"

    对于Store_Information表，如果我们需要算出每一间店的收入，只需输入命令：

SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name

 

1.12、（HAVING）如果我们需要对函数产生的结果设定条件，我们就需要使用having语句，HAVING 的语法如下：

SELECT "栏位1", SUM("栏位2")FROM "表格名" GROUP BY "栏位1" HAVING (函数条件)

   举例来说，我们可能只需要知道哪些店的营业额有超过$1,500，我们只用输入命令：

SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_nameHAVING SUM(sales) > 1500

 

1.13、（ALIAS）有两种别名机制，栏位别名及表格别名，别名机制的使用方法是：

     SELECT "表格别名"."栏位1""栏位别名" FROM "表格名""表格别名"

    我们用跟SQL GROUP BY 那一页一样的例子。这里的不同处是我们加上了栏位别名以及表格别名：

SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROMStore_Information A1 GROUP BY A1.store_name

 

1.14、（连接）为了讲述连接，我们需要新建一个数据库表geography：

Create tablegeography(

     Region_name varchar(10),

     Store_name varchar(20)

);

INSERT INTO geography(region_name, store_name) VALUES (‘EAST’,‘Boston’);

INSERT INTO geography(region_name, store_name) VALUES (‘EAST’,‘New York’);

INSERT INTO geography(region_name, store_name) VALUES (‘WEST’,‘Los Angeles’);

INSERT INTO geography(region_name, store_name) VALUES (‘WEST’,‘San Diego’);

    这样我们就有了两张表geography和store_information，为了得到每个地区的总收入，我们可以使用命令：

SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1,Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BYA1.region_name

 

1.15、（subquery）我们可以在一个sql语句中放入另一个sql语句，subquery的语法如下：

SELECT "栏位1" FROM "表格"WHERE "栏位2" [比较运算素](SELECT "栏位1" FROM "表格" WHERE [条件])

   [比较运算素] 可以是相等的运算素，例如 =, >, <, >=, <=. 这也可以是一个对文字的运算素，例如"LIKE."

  例如我们可以运用subquery来找出所有在西部的店的营业额。我们可以用下面的SQL来达到我们的目的：

SELECTSUM(Sales) FROM Store_Information WHERE Store_name IN (SELECT store_name FROMGeography WHERE region_name = 'West')

 

1.16（UNION）UNION 指令的目的是将两个 SQL 语句的结果合并起来。另外，当我们用UNION这个指令时，我们只会看到不同的资料值。

UNION 的语法如下：

[SQL 语句 1] UNION [SQL 语句 2]

    为了举例union用法，我们新建数据库表Internet_Sales:

Create table Internet_Sales(

     Date date,

     Sales int

);

  INSERT INTOInternet_Sales (date, sales) VALUES ('1999-02-05', 250);

INSERT INTOInternet_Sales (date, sales) VALUES ('1999-02-25', 250);

    当我们要找出来所有有营业额 (sales) 的日子。要达到这个目的，我们用以下的 SQL 语句：

SELECTDate FROM Store_Information UNION SELECT Date FROM Internet_Sales

 

1.17、（UNIONALL）UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。UNION ALL 和 UNION 不同之处在于UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复。

 UNION ALL 的语法如下：

 [SQL 语句 1] UNION ALL [SQL 语句 2]

 

1.18、（concatenate）有时候我们需要将不同栏位获得的资料连在一起，mysql提供的函数是CONCAT()，CONCAT的语法如下：

CONCAT(字串1, 字串2, 字串3, ...): 将字串1、字串2、字串3等字符串连在一起。

    例如对于表geography使用语句：

SELECTCONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston';

得到的结果：EASTBoston

 

1.19、（substring）SQL中的substring 函数是用来抓出一个栏位资料中的其中一部分。

最常用的方式如下：

SUBSTR(str, pos)：其中，选出所有从第pos位置开始的字元。

SUBSTR(str,pos,len): 第pos位置开始，选出接下去的len个字元。

举两个例子如下：

SELECTSUBSTR(store_name, 3) FROM Geography WHERE store_name = 'Los Angeles';

SELECTSUBSTR(store_name,2,4) FROM Geography WHERE store_name = 'San Diego';

 

1.20、(trim) SQL 中的TRIM函数是用来移除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空白

 例1: SELECT TRIM('Sample '); 结果: 'Sample'

 例2: SELECT LTRIM(' Sample'); 结果: 'Sample '

 例3: SELECT RTRIM(' Sample'); 结果: ' Sample'

二、create view

    视观表 (Views) 可以被当作是虚拟表格。它跟表格的不同是，表格中有实际储存资料，而视观表是建立在表格之上的一个架构，它本身并不实际储存资料。

建立一个视观表的语法如下： 

CREATEVIEW "VIEW_NAME" AS "SQL 语句"

    我们也可以用视观表来连接两个表格。在这个情况下，使用者就可以直接由一个视观表中找出她要的资讯，而不需要由两个不同的表格中去做一次连接的动作。

我们可以用以下的指令来建一个包括每个地区 (region) 销售额 (sales) 的视观表：

  CREATE VIEWV_REGION_SALES AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROMGeography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BYA1.region_name

这就给我们有一个名为 V_REGION_SALES的视观表。这个视观表包含不同地区的销售哦。如果我们要从这个视观表中获取资料，我们就打入：

SELECT * FROMV_REGION_SALES

三、Update 

    我们有时候可能会需要修改表格中的资料。在这个时候，我们就需要用到 UPDATE 指令。这个指令的语法是：

UPDATE "表格名" SET "栏位1" = [新值] WHERE {条件}

    例如更新san diego的销售额，可以输入

UPDATEStore_Information SET Sales = 500 WHERE store_name = " San Diego"

四、Delete

    在某些情况下，我们会需要直接由数据库中去除一些资料。这可以藉由 DELETE FROM 指令来达成。

    它的语法是： 

DELETEFROM "表格名" WHERE {条件}

    当我们需要将有关 Los Angeles 的资料全部去除。在这里我们可以用以下的 SQL来达到这个目的: 

DELETE FROM Store_InformationWHERE store_name = "Los Angeles"