SQL 基础知识汇总

基础知识

1、select  distinct  语句（用于返回唯一 不同的值）

     使用场景说明：有时候表中的某些列的值不是唯一的（重复数据过多，但恰恰我们不需要那些重复的数据）时，便可使用DISTINCT  语句过滤掉重复的值。

     使用语法：SELECT DISTINCT 列名称 FROM 表名称；

2、AND & OR  运算

     使用场景说明：一般与where 子句  配合使用  表示多个条件联合查询，可以把多个条件结合起来，如果第一个和第二个条件都成立，那么使用 and 结合，如果第一个条件和第二个条件只需要一个成立则使用 or 将条件连接。

     使用实例：

                     1、使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人；

                  SELECT * FROM Persons WHERE FirstName='Thomas'                  AND                  LastName='Carter' 

                           查出结果集就是满足两个条件的数据。

                    2、使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人：                          

                               SELECT * FROM Persons WHERE firstname='Thomas'

            OR

                               lastname='Carter'

                          查出的结果集就是两个条件 只要满足任何一个的结果集。

3、Order  by   语句

      根据指定的列队结果集进行排序，默认按照升序排列。

      排列方式分为  Order  by  asc (升序) 和 Order  by  desc (降序)。这里需要进一步说明的是如果排序的条件相同的数据不止一条的话，结果集还是会根据升序优先的原则展示数据（null  为优先显示数据）；

      基础语法： select  *  from  tableName   where tableOrder> '10000'  and tableOrder<'20000'  Order  by  tableOrder desc

     所查结果集为 tableName  表中  tableOrder 的值介于 10000---20000之间的数据，并按照降序排列。

4、TOP 语句

     使用场景：数据量过大的话，使用top 语句查询表头多少笔数据。

     不同的数据库系统  TOP 语句略有差异，这里不做展示。

5、LIKE 操作符（模糊查询）

     like 操作符用于where  子句中搜索列中的制定模式。

     基本语法：（实例描述 查找居住在以N开头的名字的城市的人）

          SELECT * FROM Persons  WHERE City LIKE 'N%'

       注意  like ‘%N’(以N开头)  与 like '%N%' （名称中含N） 和 like 'N%'（以 N字符结尾的） 

6、SQL 的通配符

     sql 通配符必须配合like操作符同时使用，可以在数据库查询的时候可以替代一个或多个字符，通配符有四种；

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或者 [!charlist]	不在字符列

       依据下张Person表 对以上几种操作符详细说明；

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

      1、%操作符

      现在，我们希望从上面的 "Persons" 表中选取居住在以 "Ne" 开始的城市里的人：

            SELECT * FROM Persons WHERE City LIKE 'Ne%'

       结果集：

Id	LastName	FirstName	Address	City
2	Bush	George	Fifth Avenue	New York

      2、_操作符

      现在，我们希望从上面的 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人：

            SELECT * FROM Persons WHERE FirstName LIKE '_eorge'

      结果集：

Id	LastName	FirstName	Address	City
2	Bush	George	Fifth Avenue	New York

            接下来，我们希望从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是任意          字 符，然后是 "er"：

                 SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'

      结果集：

Id	LastName	FirstName	Address	City
3	Carter	Thomas	Changan Street	Beijing

      3、[  ]操作符

           现在，我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：

                 SELECT * FROM Persons WHERE City LIKE '[ALN]%'

       结果集：

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York

      4、[! ] 操作符

       现在，我们希望从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人：

         LECT * FROM Persons WHERE City LIKE '[!ALN]%'

      结果集：

Id	LastName	FirstName	Address	City
3	Carter	Thomas	Changan Street	Beijing

7、between   操作符

     between ... and   操作符在where子句中使用，介于选取两个值之间范围的数据。 

     基本语法：select  *  from  tableName  where columName  between  value1  and  value2  

     重要事项：不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 "value1" 和 "value2"      之间的人，但不包括 "value1" 和 "value2" ；某些数据库会列出介于 "value1" 和 "value2" 之间并包括 "value1" 和                          "value2" 的数据；而另一些数据库会列出介于 "value1" 和 "value2" 之间的数据，包括 "value1" ，但不包括                                    "value2" 。所以，请检查你的数据库是如何处理 BETWEEN....AND 操作符的！

8、JOIN  语法

      join 语法用于根据两张或者多张表之间的关联关系，查询这些表中的相关数据。

      数据库中的表可通过主键将表与表关联起来， 主键时表中的一个列，主键的值在一个表中是唯一的，这样就可以把表与表通        过主键关联起来做捆绑查询。

      请看 "Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

      请注意，"Id_P" 列是 Persons 表中的的主键。这意味着没有两行能够拥有相同的 Id_P。即使两个人的姓名完全相同，Id_P          也可以区分他们。

      接下来请看 "Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

      请注意，"Id_O" 列是 Orders 表中的的主键，同时，"Orders" 表中的 "Id_P" 列用于引用 "Persons" 表中的人，而无需使用他  们的确切姓名。请留意，"Id_P" 列把上面的两个表联系了起来

      下边引用两个表，查询查询谁订购了产品，并且订购了多少。

      方式1：普通SQL

         SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE
                     Persons.Id_P = Orders.Id_P 

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

      方式2： inner  join

        SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons 
               INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName

astName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

      注：inner  join 在表中存在至少有一个匹配（即用来关联的这个一个字段是有值）的时候返回该行，否则不出现该条记录。   

   同样的查询还有：

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

9、LEFT  Join  On（左连接，某些数据库也将其称为左外连接）

        Left Join 会从左表哪里返回所有的行，即使在右表中没有匹配的行。

        语法：SELECT   column_name(s)    FROM    table_name1 LEFT JOIN      table_name2     ON                                              table_name1.column_name=table_name2.column_name

        实例：下表

      "Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

      "Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

      业务需求：列出Persons 表中所有欧订单的人,并根据LastName进行降序排列。

       sql  语句：Select  Persons.lastName Persons.firstName,Order.OrderNo  from Persons left  Join Orders  On                                                   Persons.ID_p=Orders.ID_p  Order  By  Persons.lastName 。

      查询结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

 

10、Right  Join  (右连接   也称右外连接)

       Right  Join 会根据右表返回所有的行，即使在左表中没有匹配的行。     

       语法：SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON                                                               table_name1.column_name=table_name2.column_name

       实例：下表

       Persons  表 

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

      Orders 表  

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

   业务需求：列出所有的订单  和他的所有人 并根据Persons表中的LastName 字段进行降序排列 。   

     sql  语句：Select  Persons.LastName,Persons.FristName,Orders.OrderNo from  Persons  Right  Join Orders  On                     Persons.Id_p = Orders.Id_p  ORDER BY  Persons.LastName;    

     查询结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
		34764

11、Full  Join  On关键字 

      只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

      基础语法：SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON                                                            table_name1.column_name=table_name2.column_name

      实体：下表

      "Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

      "Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

     业务需求：列出所有的人，以及他们的定单，以及所有的定单，以及定购它们的人。

     SQL  语句：SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON           Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

     查询结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George
		34764

      full  join on  关键字会从左表（Persons）和右表（Orders）那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有    匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。

12、平均数函数 AVG()

      AVG()函数返回数值列。null 值不包括计算中。
      基础语法： Select SVG(column_name)  from table_name;

      SVG()实例 :下表Orders表

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

      需求：计算 "OrderPrice" 字段的平均值。

      SQL 语句 ：Select  AVG(OrderPrice)  AS  orderAvgrage  from  orders;

      查询结果集：

OrderAverage
950

      拓展实例：找到 OrderPrice 值高于 OrderPrice 平均值的客户。

      SQL 语句 ：SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

      查询结果集：

Customer
Bush
Carter
Adams

13、查询总数  Count()；

       函数返回指定列的值的数目（null 值不计入在内）

      说明：COUNT(column_name)  与COUNT(*) 

      COUNT(column_name)  返回列 column_name （值为null 的不计算在内）。

      COUNT(*)  返回数据库表中的总资料数目。

      基础语法：SELECT COUNT(column_name) FROM table_name；

      COUNT（）语法实例一：

             COUNT(*) 函数返回表中的记录数

             SQL 语句：SELECT COUNT(*) FROM table_name

      COUNT（）语法实例二：

             COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

             SQL 语句：SELECT COUNT(DISTINCT column_name) FROM table_name

14、First()  函数  与Last（） 函数

       FIRST() 函数返回指定的字段中第一个记录的值。

       Last() 函数返回指定的字段中最后一个记录的值。

      基础语法：Select  first(column_name)   from  tableName;

      基础语法：Select  last(column_name)   from  tableName;

      实例：如下表Orders

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

      需求一：查找 "OrderPrice" 列的第一个值。

      SQL 语句：SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

      查询结果集：

FirstOrderPrice
1000

      需求二：查找“OrderPrice” 列的最后一个值。

      SQL 语句：SELECT Last(OrderPrice) AS FirstOrderPrice FROM Orders

      查询结果集：

LastOrderPrice
100

15、Max()函数与Min()函数、sum （）函数

        MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

        MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

        SUM 函数返回数值列的总数（总额） 通常与Gourp By 配合使用。

        基础语法：SELECT MAX(column_name) /MIN(column_name)  FROM table_name;

        实例：下表Orders

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

       需求一：查找OrderPrice 列下最大值；

      SQL 语句：SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders；

LargestOrderPrice
2000

      需求二：查找OrderPrice 列下最小值；

      SQL 语句：SELECT MIN(OrderPrice) AS LargestOrderPrice FROM Orders；

SmallestOrderPrice
100

      需求三：查找 "OrderPrice" 字段的总数。

   SQL 语句：SELECT SUM(OrderPrice) AS OrderTotal FROM Orders;

OrderTotal
5700

16、SQL  快速参考

语句	语法
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE (add column)	ALTER TABLE table_name  ADD column_name datatype
ALTER TABLE (drop column)	ALTER TABLE table_name  DROP COLUMN column_name
AS (alias for column)	SELECT column_name AS column_alias FROM table_name
AS (alias for table)	SELECT column_name FROM table_name  AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name)
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... )
CREATE UNIQUE INDEX	CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE FROM	DELETE FROM table_name  (Note: Deletes the entire table!!) or DELETE FROM table_name WHERE condition
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1
HAVING	SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2,....) or INSERT INTO table_name (column_name1, column_name2,...) VALUES (value1, value2,....)
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO (used to create backup copies of tables)	SELECT * INTO new_table_name FROM original_table_name or SELECT column_name(s) INTO new_table_name FROM original_table_name
TRUNCATE TABLE (deletes only the data inside the table)	TRUNCATE TABLE table_name
UPDATE	UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE condition