SQL知识点总结

目录

什么是SQL
SQL基本语法
排序-ORDER BY
选取不重复的数据-DISTINCT
WHERE子句
LIKE 操作符
逻辑操作符
计算字段Alias（别名）
汇总数据分组数据-GROUP
BY分组数据-HAVING
SELECT子句顺序
子查询
JOIN
UNION
CASE WHEN
CREATE TABLE
EXTRACT() 函数

SQL简介

什么是数据库（database）

保存有组织的数据的容器（通常是一个文件或一组文件）。

什么是SQL

SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。SQL用于访问和处理数据库的标准的计算机语言。

SQL语言分4类：

  • 数据查询语言（DQL）：select，from，where
  • 数据操纵语言（DML）：insert，update，delete
  • 数据定义语言（DDL）：create，alter，drop
  • 数据控制语言（DCL）：grant，revoke，commit，rollback，savepoint

基本语法

SQL的语法偏向自然语言，最简单的形式如下

SELECT column1, column2....columnN
FROM   table_name;

注意：SQL 不区分大小写

假设有这样的一张表products

prod_code prod_name prod_price vend_id
p1001 a 10 1001
p1002 b 20 1002
p1003 c 30 1003

检索单个列

SELECT prod_name
FROM products

检索所有列

SELECT *
FROM products

从表中选取前10行

SELECT *
FROM products
LIMIT 10

SQL语法-子句

子句（clause） SQL语句由子句构成，有些子句是必需的，而有的是可选的
一个子句通常由一个关键字和所提供的数据组成
子句的例子： SELECT 语句和 FROM 子句

排序-ORDER BY

ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

SELECT *
FROM products
ORDER BY price
LIMIT 10

选取不重复的数据-DISTINCT

在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。
关键词 DISTINCT 用于返回唯一不同的值。
语法：
SELECT DISTINCT 列名称 FROM 表名称

SELECT DISTINCT prod_name
FROM products

WHERE子句

WHERE作用：行级过滤
WHERE子句的位置：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则会产生错误

SELECT prod_price
FROM products
WHERE prod_price>=10;

常见的WHERE子句操作符

= 等于
<>不等于
!=不等于
<=小（等）于
>=大（等）于
BETWEEN 两者之间

SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

注意：单引号用来限制字符串，如果将值与串类型的列进行比较，则需要限定引号

SELECT prod_price
FROM products
WHERE prod_name='chengzi';

NULL值（no value）

不包含值，它与字段包含0、空字符串或仅仅包含空格不同

SELECT prod_name,prod_price
FROM products
WHERE prod_price IS NULL;

LIKE 操作符

有一个Persons 表:

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" 表中选取居住在以 "N" 开始的城市里的人：
我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons
WHERE City LIKE 'N%'

提示："%" 可用于定义通配符（模式中缺少的字母）。

结果集：

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

例子 2
接下来，我们希望从 "Persons" 表中选取居住在包含 "lon" 的城市里的人：
我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons
WHERE City LIKE '%lon%’

结果集：

Id LastName FirstName Address City
1 Adams John Oxford Street London

例子 3
通过使用 NOT 关键字，我们可以从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人：

SELECT * FROM Persons
WHERE City NOT LIKE '%lon%'

结果集：

Id LastName FirstName Address City
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

逻辑操作符

操作符（operator）

用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符（logical operetor）

AND操作符

SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id=1003 and  prod_price<=10;

OR操作符

SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id=1003 or vend_id=1004;

AND在计算次序中优先级较高，为避免操作符被错位组合，可以写如下语句：

SELECT prod_id,prod_price,prod_name
FROM products
WHERE （vend_id=1003 or vend_id=1004） and prod_price>=10;

注意：圆括号比AND和OR优先级更高

IN操作符

SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

NOT操作符

SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

计算字段

字段（field）基本上与列（column）的意思相同
AS（使用别名）
算术运算：+-*/ 加减乘除

SELECT prod_id,
    quantity,
    item_price,
    quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005

Alias（别名）

假设我们有两个表分别是："Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。
现在，我们希望列出 "John Adams" 的所有定单。
我们可以使用下面的 SELECT 语句：

SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'

不使用别名的 SELECT 语句：

SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons, Product_Orders
WHERE Persons.LastName='Adams' AND Persons.FirstName='John'

从上面两条 SELECT 语句可以看到，别名使查询程序更易阅读和书写。

汇总数据

我们经常需要汇总数据而不用把它们实际检索出来
聚集函数（aggregate function） 运行在行组上，计算和返回单个值的函数。

SELECT AVG(prod_price) AS avg_price
FROM products;

表：SQL聚集函数

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

分组数据-GROUP BY

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

分组是在 SELECT 语句的 GROUP BY 子句中建立的

GROUP BY 子句可以包含任意数目的列

如果在 SELECT 中使用表达式，则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。

除聚集计算语句外， SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。

GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

输出：

vend_id num_prods
1001 3
1002 10
1003 7

分组数据--HAVING

HAVING 子句

HAVING 非常类似于 WHERE

目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代

差别是WHERE 过滤行，而 HAVING 过滤分组

WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤

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

输出：

cust_id orders
10001 3

SELECT子句顺序

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

子查询

可以把一条 SELECT语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

JOIN

作用： 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

Join 和 Key

有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。
数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列（或多个列），在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。

请看 "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

引用两个表

我们可以通过引用两个表的方式，从两个表中获取数据：
谁订购了产品，并且他们订购了什么产品？

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER 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

不同的 SQL JOIN

除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。

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

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

CASE WHEN

CASE 具有两种格式。简单CASE 函数和CASE 搜索函数。

简单CASE 函数

CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

CASE搜索函数

CASE WHEN sex = '1' THEN '男' 
WHEN sex = '2' THEN '女' 
ELSE '其他' END

已知数据按照另外一种方式进行分组

有如下的表：

country population
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250

根据这个国家人口数据，统计亚洲和北美洲的人口数量

SELECT  CASE country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END AS 洲
，SUM(population) AS 人口
FROM    Table_A
GROUP BY CASE country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END;

输出:

洲 人口
亚洲 1100
北美洲 250
其他 700

实现类似数据透视表的功能

有如下数据

country sex population
中国 1 340
中国 2 260
美国 1 45
美国 2 55
加拿大 1 51
加拿大 2 49
英国 1 40
英国 2 60

按照国家和性别进行分组，得出结果如下

国家 男 女
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60 
SELECT country,
SUM( CASE WHEN sex = '1' THEN
population ELSE 0 END) AS 男,  --男性人口
SUM( CASE WHEN sex = '2' THEN
population ELSE 0 END)   AS 女--女性人口
FROM  Table_A
GROUP BY country;

CREATE TABLE

创建空表

如要创建一个列名分别是"Id_P"、"LastName"、"FirstName"、"Address" 以及 "City"的空表，表名为Persons：

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SELECT的结果集来创建表

create table person_table
as 
select id,name from person;

EXTRACT() 函数

定义和用法

EXTRACT()函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

语法

EXTRACT(unit FROM date)

假设我们有如下的表：

OrderId ProductName OrderDate
1 'Computer' 2008-12-29 16:25:46.635

我们使用下面的 SELECT 语句：

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1

完

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,189评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,577评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,857评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,703评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,705评论 5赞 366
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,620评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,995评论 3赞 396
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,656评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,898评论 1赞 298
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,639评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,720评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,395评论 4赞 319
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,982评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,953评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,195评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,907评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,472评论 2赞 342