1.《SQL必知必会》--（更新中...）

第二课：检索数据
- 1.检索单个列
- 2.检索多个列
- 3.检索所有列
- 4.检索不同的值（distinct：去重）
- 5.限制结果（MySQL：limit）
- 6.使用注释
第三课排序检索数据（order by语句）
- 1.排序数据（单个列）
- 2.按多个列排序
- 3.按列位置排序
- 4.指定排序方向
第四课过滤数据（where语句）
- 1.使用where子句
- 2.where子句操作符
第五课高级数据过滤（where 高阶操作）
- 1.组合where子句（and / or）
- 2.IN操作符(指定条件范围)
- 3.NOT操作符
第六课：用通配符进行过滤
- 1.LIKE操作符
- 2.通配符技巧
第七课：创建计算字段
- 1.计算字段
- 2.拼接字段
- 3.执行算术计算
第八课：使用函数处理数据
- 1.函数
- 2.使用函数
第九课：汇总数据
- 1.聚集函数
- 2 聚集不同值
- 3 组合聚集函数
第十课：分组数据
- 1.数据分组：使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算
- 2.创建分组：分组是使用SELECT语句的GROUP BY子句建立的
- 3.过滤分组：
- 4.分组和排序
- 5.SELECT子句顺序
第十一课：使用子查询
- 1.子查询：即嵌套在其他查询中的查询
- 2.利用子查询进行过滤
- 3.作为计算字段使用子查询
第十二课：联结表
第十三课：创建高级联结
第十四课：组合查询
第十五课：插入数据
第十六课：更新和删除数据
第十七课：创建和操纵表
第十八课：使用视图
第十九课：使用储存过程
第二十课：管理事务处理
第二十一课：使用游标
第二十二课：高级SQL特性

第二课：检索数据

1.检索单个列

select prod_name from Products;

2.检索多个列

select prod_id,prod_name,prod_price from Products;

3.检索所有列

select * from Products;

4.检索不同的值（distinct：去重）

select distinct prod_name from Products;

注意：distinct关键字作用于所有列

5.限制结果（MySQL：limit）

select prod_name from Products
limit 5 offset 4;

或者

select prod_name from Products
limit 4,5;

从第4+1行开始，检索不超过5行（起始位置为0，所以4为第五行）

6.使用注释

select prod_id,prod_name,prod_price from Products; -- 这是一条注释

注释类型
- 单行注释
  - “-- ”
  - “ # ”
- 多行注释
  - “/* ----------这是注释内容，可以多行 ---------- */” 引号中的为注释两端，内容在里面，可以多行

第三课排序检索数据（order by语句）

1.排序数据（单个列）

采用 order by子句(应出现在select语句中的最后一句，不然会报错)

select prod_name from Products
order by prod_name;

2.按多个列排序

要按多个列排序，简单指定列明，列明之间用逗号分开即可（就像选择多个列一样）

select prod_id,prod_name,prod_price from Products
order by prod_price,prod_name;

检索三个列，其中按价格先排序，随后按名称排序

3.按列位置排序

效果同2.指定select的列位置

select prod_id,prod_name,prod_price from Products
order by 2,3;

优点：简单，高效
缺点：1.不指定列明容易篡改，或选择错误；2.如果需要排序的列不在select中，不能使用
可以混用2和3

4.指定排序方向

升序（默认）降序（DESC）
例如：升序

select prod_id,prod_name,prod_price from Products
order by prod_price,prod_name;

降序,升序

select prod_id,prod_name,prod_price from Products
order by prod_price desc,prod_name;

降序，降序

select prod_id,prod_name,prod_price from Products
order by prod_price desc,prod_name desc;

desc 关键字直接应用于其前面的列名，想在多个列上降序，必须对每一个列名进行指定desc (descending的缩写，两个都可以使用)
asc 升序（ascending 的缩写，但一般不用，直接默认就是升序）

第四课过滤数据（where语句）

1.使用where子句

select prod_id,prod_name,prod_price from Products
where prod_price = 3.49;

注意：order by 在where之后

2.where子句操作符


=                                         (等于)
<>                                       (不等于)
!=                                        (不等于)
<                                         (小于)
<=                                       (小于等于)
!<                                        (不小于)
>                                         (大于)
>=                                       (大于等于)
!>                                        (不大于)
BETWEEN                         (在指定两个值之间)
IS NULL                              (为null值)

2.1 检查单个值

select prod_id,prod_name,prod_price from Products
where prod_price < 10;

2.2 不匹配检查

select prod_id,prod_name,prod_price from Products
where vend_id <> 'DLL01';

当值为字符串的时候，应该用引号将其括起来
注意<>和!=通常可以互换，但是有的不可以比如access

2.3 范围值检查

select prod_id,prod_name,prod_price from Products
where prod_price between 5 and 10;

给定两个值

2.4空值检查

select prod_id,prod_name,prod_price from Products
where prod_price IS NULL;

第五课高级数据过滤（where 高阶操作）

1.组合where子句（and / or）

1.1 AND操作符(且,条件都要具备)

select prod_id,prod_name,prod_price from Products
where prod_price < 10 and prod_price > 5;

每个条件中都要加and

1.2 OR 操作符(或,条件其中某一个符合都可以)

select prod_id,prod_name,prod_price from Products
where prod_price = 10 or prod_price  = 5;

1.3 求值顺序

where子句可以包含任意数目的AND和OR操作符,允许两者进行复杂高级的过滤
where 先执行and语句,后执行or语句,如果怕有歧义,需用小括号进行指定顺序

select prod_id,prod_name,prod_price from Products
where prod_price > 10 or prod_price < 5 and vend_id = 'DDL01';

应该为

select prod_id,prod_name,prod_price from Products
where (prod_price > 10 or prod_price < 5) 
        and vend_id = ' DDL01 ';

2.IN操作符(指定条件范围)

select prod_id,prod_name,prod_price from Products
where prod_price in (5,10,20);

IN操作符完成了与OR操作符相同的功能,但有更多的优点:
- 在有很多合法选项时,IN操作符的语法更清楚,更直观
- 在与其他and 和 or 操作符组合使用IN时,求值顺序更容易管理
- IN操作符一般比OR执行得更快
- IN的最大的优点是可以包含其他SELECT语句,能够更动态地建立where子句

3.NOT操作符

NOT只有一个功能,就是否定其后所跟的任何条件!
NOT从不单独使用,与其他操作符一起使用
NOT where子句中用来否定其后条件的关键字

select prod_id,prod_name,prod_price from Products
where NOT vend_id  =  'DLL01';

同下面代码

select prod_id,prod_name,prod_price from Products
where vend_id <> 'DLL01';

为什么使用NOT?
-简单的子句没什么优势,但是更复杂的子句,NOT是非常有用的,例如在与IN操作符联合使用时,NOT可以非常简单的找出与条件列表不匹配的行.

第六课：用通配符进行过滤

通配符（wildcard）：用来匹配值的一部分的特殊字符。

1.LIKE操作符

1.1 “%”通配符

% 表示任何字符出现任意次数

select prod_id,prod_name,prod_price from Products
where prod_name like 'fish%';

注意：
- Access 需要*不是%
- fish% 代表fish开头的字符
- '%' 不会匹配字符值为NULL的值
- 注意后面所跟的空格

1.2 “_”通配符

用途和 % 用途一样，只不过匹配单个字符（Access使用的是？不是 _）

select prod_id,prod_name,prod_price from Products
where prod_name like 'fish_';

1.3 “[ ]”通配符

用来指定一个字符集，必须匹配指定位置（通配符的位置）的一个字符
例如：找出名字以J和M开头的物品名称

select prod_id,prod_name,prod_price from Products
where prod_name like '[JM]%';

[JM]只能匹配单个字符，匹配方括号中的任何一个
[^{JM]代表除去JM以外的字符（Access使用!而不是}）
使用NOT可有通用的效果，不过解读比较麻烦

select prod_id,prod_name,prod_price from Products
where NOT prod_name like '[JM]%';

2.通配符技巧

通配符很有用，不过这种功能是有代价的，其搜索一般比其他搜所耗费的时间要长
使用通配符建议：
- 不要过度使用，如果其他方式可以达到相同效果，优先使用别的
- 在确实需要使用通配符时，也尽量不要把他们用在搜索模式的开始，这样搜索最慢
- 仔细注意通配符的位置，不然疏忽很难返回你想要的数据（跑一次时间较长）

第七课：创建计算字段

1.计算字段

计算字段并不实际存在于数据库表中，是在运行时在select语句中创建。
只有数据库知道select语句中哪些是列哪些是计算字段。在客户端显示中，数据返回方式相同。

2.拼接字段

拼接：（concatenate）将值联结到一起（将一个值附加到另一个值）构成单个值
使用方法：
- Access和SQL sever 使用“+”
- DB2、Oracle、PostgreSQL、sqlLite和OPEN OFFICE BASE 使用 “||”
- Mysql和MariaDB使用 “concat”

# 使用“+”
select vend_name + '(' + vend_country + ')' from vendors
order by vend_name;

# 使用“||”
select vend_name || '(' || vend_country || ')'  from vendors
order by vend_name;

# 使用“concat”
select concat(vend_name,'(', vend_country , ')' )
from vendors
order by vend_name;

TRIM函数
- LTRIM()去掉字符串左边空格
- RTRIM()去掉字符串右边空格
- TRIM（）去掉字符串两边空格
AS 使用别名

# 使用“AS”
select concat(vend_name,'(', vend_country , ')' )  AS vend_title
from vendors
order by vend_name;

AS可选，不过最好写出，在多行语句下，可读性较高

3.执行算术计算

select prod_id,quantity,item_price,quantity*ttem_price  AS expanded_price
from orderitems
where order_num = 2008;

SQL计算操作符
- “+” 加
- “-” 减
- “*” 乘
- “/” 除

第八课：使用函数处理数据

1.函数

DBMS函数的差异
- 提取字符串的组成部分

DBMS分类	提取字符串的组成部分	数据类型转换	提取当前日期
Access	MID( )	不同，每种都有一个转换函数	NOW( )
DB2	SUBSTR( )	CAST( )	CURRENT_DATE
Oracle	SUBSTR( )	不同，每种都有一个转换函数	SYSDATE
PostgreSQL	SUBSTR( )	CAST( )	CURRENT_DATE
SQLite	SUBSTR( )	CONVERT( )	DATE( )
MySQL	SUBSTRING( )	CONVERT( )	CURDATE( )
SQL Server	SUBSTRING( )	CONVERT( )	GETDATE( )

2.使用函数

大多数SQL实现支持以下类型的函数
- 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
- 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
- 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
- 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

2.1 文本处理函数

# 使用upper()函数
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase 
FROM Vendors 
ORDER BY vend_name;

常用文本处理函数

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串长度
LOWER()（Access使用LCASE()）	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()（或使用子字符串函数）	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()（Access使用UCASE()）	将字符串转换为大写

2.2 日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。
应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因，日期和时间函数在SQL 中具有重要的作用。遗憾的是，它们很不一致，可移植性最差。
我们举个简单的例子，来说明日期处理函数的用法。Orders表中包含的订单都带有订单日期。

# 为在SQL Server中检索2012年的所有订单
SELECT order_num 
FROM Orders 
WHERE DATEPART(yy, order_date) = 2012;

# 在Access中使用如下版本：
SELECT order_num 
FROM Orders 
WHERE DATEPART('yyyy', order_date) = 2012;

# Oracle没有DATEPART()函数，不过有几个可用来完成相同检索的日期处理函数
SELECT order_num 
FROM Orders 
WHERE to_number(to_char(order_date, 'YYYY')) = 2012;

# MySQL和MariaDB用户可使用名为YEAR()的函数从日期中提取年份：
SELECT order_num 
FROM Orders 
WHERE YEAR(order_date) = 2012;

# 在SQLite中有个小技巧
SELECT order_num 
FROM Orders 
WHERE strftime('%Y', order_date) = 2012;

2.3 数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期-时间处理函数使用那么频繁。
常用数值处理函数

函　　数	说　　明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率
SIN()	返回一个角度的正弦
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切

第九课：汇总数据

1.聚集函数

有时候需要汇总数据而不是将它们检索出来，使用聚集函数可以实现功能：
- 确定表中行数（或者满足某个条件或包含某个特定值的行数）；
- 获得表中某些行的和；
- 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。
聚集函数举例：

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

1.1 AVG()函数

AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

# 使用AVG()返回Products表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price 
FROM Products;

警告：只用于单个列 AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
说明：NULL值值 AVG()函数忽略列值为NULL的行

1.2 COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目
COUNT()函数有两种使用方式：
- 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
- 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

# 下面的例子返回Customers表中顾客的总数
SELECT COUNT(*) AS num_cust 
FROM Customers;

1.3 MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名

SELECT MAX(prod_price) AS max_price 
FROM Products;

提示：对非数值数据使用MAX() 虽然MAX()一般用来找出最大的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。
说明：说明：NULL值值 MAX()函数忽略列值为NULL的行

1.4 MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名

SELECT MIN(prod_price) AS min_price 
FROM Products;

1.5 SUM()函数

SUM()用来返回指定列值的和（总计）。

SELECT SUM(quantity) AS items_ordered 
FROM OrderItems 
WHERE order_num = 20005;

SELECT SUM(item_price*quantity) AS total_price 
FROM OrderItems 
WHERE order_num = 20005;

函数SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句同样保证只统计某个物品订单中的物品。
说明：NULL值值 SUM()函数忽略列值为NULL的行。

2 聚集不同值

以上5个聚集函数都可以如下使用：
- 对所有行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）。
- 只包含不同的值，指定DISTINCT参数。

SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM Products 
WHERE vend_id = 'DLL01'

警告：
- 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
提示：将DISTINCT用于MIN()和MAX()
- 虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否只考虑不同值，结果都是相同的。
说明：其他聚集参数
- 除了这里介绍的DISTINCT和ALL参数，有的DBMS还支持其他参数，如支持对查询结果的子集进行计算的TOP和TOP PERCENT。为了解具体的DBMS支持哪些参数，请参阅相应的文档。

3 组合聚集函数

SELECT COUNT(*) AS num_items,       
                MIN(prod_price) AS price_min,       
                MAX(prod_price) AS price_max,       
                AVG(prod_price) AS price_avg 
FROM Products;

警告：取别名
- 在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做也算合法，但许多SQL实现不支持，可能会产生模糊的错误消息。

第十课：分组数据

1.数据分组：使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算

2.创建分组：分组是使用SELECT语句的GROUP BY子句建立的

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

注意：
- 如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
- 有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列。例如，GROUP BY 2, 1可表示按选择的第二个列分组，然后再按第一个列分组

3.过滤分组：

WHERE过滤行，而HAVING过滤分组

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

注意：HAVING和和WHERE的差别
- WHERE在数据分组前进行过滤（行级过滤）
- HAVING在数据分组后进行过滤（分组过滤）

-- where和having都存在的情况下：
SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id 
HAVING COUNT(*) >= 2;

4.分组和排序

GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同

ORDER BY	GROUP BY
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

-- order by 与 group by 混合使用
SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 ORDER BY items, order_num;

5.SELECT子句顺序

第十一课：使用子查询

1.子查询：即嵌套在其他查询中的查询

2.利用子查询进行过滤

SELECT cust_id FROM Orders 
WHERE order_num IN 
    (
        SELECT order_num                    
        FROM OrderItems                    
        WHERE prod_id = 'RGAN01'
    );

警告：子查询只能查询单列

3.作为计算字段使用子查询

第十二课：联结表

第十三课：创建高级联结

第十四课：组合查询

第十五课：插入数据

第十六课：更新和删除数据

第十七课：创建和操纵表

第十八课：使用视图

第十九课：使用储存过程

第二十课：管理事务处理

第二十一课：使用游标

第二十二课：高级SQL特性

posted on 2019-09-01 20:28 Mario_mj 阅读(542) 评论(0) 编辑收藏举报

刷新页面返回顶部

Mario_mj

导航

公告

1.《SQL必知必会》--（更新中...）

第二课：检索数据

1.检索单个列

2.检索多个列

3.检索所有列

4.检索不同的值（distinct：去重）

5.限制结果（MySQL：limit）

6.使用注释

第三课 排序检索数据（order by语句）

1.排序数据（单个列）

2.按多个列排序

3.按列位置排序

4.指定排序方向

第四课 过滤数据（where语句）

1.使用where子句

2.where子句操作符

2.1 检查单个值

2.2 不匹配检查

2.3 范围值检查

2.4空值检查

第五课 高级数据过滤（where 高阶操作）

1.组合where子句（and / or）

1.1 AND操作符(且,条件都要具备)

1.2 OR 操作符(或,条件其中某一个符合都可以)

1.3 求值顺序

2.IN操作符(指定条件范围)

3.NOT操作符

第六课：用通配符进行过滤

1.LIKE操作符

1.1 “%”通配符

1.2 “_”通配符

1.3 “[ ]”通配符

2.通配符技巧

第七课：创建计算字段

1.计算字段

2.拼接字段

3.执行算术计算

第八课：使用函数处理数据

1.函数

2.使用函数

2.1 文本处理函数

2.2 日期和时间处理函数

2.3 数值处理函数

第九课：汇总数据

1.聚集函数

1.1 AVG()函数

1.2 COUNT()函数

1.3 MAX()函数

1.4 MIN()函数

1.5 SUM()函数

2 聚集不同值

3 组合聚集函数

第十课：分组数据

1.数据分组：使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算

2.创建分组：分组是使用SELECT语句的GROUP BY子句建立的

3.过滤分组：

4.分组和排序

5.SELECT子句顺序

第十一课：使用子查询

1.子查询：即嵌套在其他查询中的查询

2.利用子查询进行过滤

3.作为计算字段使用子查询

第十二课：联结表

第十三课：创建高级联结

第十四课：组合查询

第十五课：插入数据

第十六课：更新和删除数据

第十七课：创建和操纵表

第十八课：使用视图

第十九课：使用储存过程

第二十课：管理事务处理

第二十一课：使用游标

第二十二课：高级SQL特性

第三课排序检索数据（order by语句）

第四课过滤数据（where语句）

第五课高级数据过滤（where 高阶操作）