coalesce函数用法 sql_sql学习知识小结

一，简要记述一下SQL的基本用法

首先是数据库的种类，数据库大致分5种类型：层次数据库，关系数据库，面向对象数据库，xml数据库，键值存储系统。现在主要用到的是关系数据库，通常以二维表的形式，简单易懂。

我们想要获得数据的话，通常是通过客户端先发送SQL语句给服务器，服务器读取后按指令调取数据库里面的数据给服务器，服务器把这些数据再返回给客户端，这样我们就能获得想要的数据了。返回的数据都是二维表形式，二维表有非常严格的约束条件，列称为字段，行称为记录，一个单元格中只能输入一个数据，其形式受列的约束。

二，标准SQL语句分类和规则

根据指令的不同，SQL语句可分为三类：

DDL（数据定义语言）：对数据库或者表进行操作

create:创建数据库和表

drop: 删除数据库和表

alter: 修改数据库和表

DML（数据操纵语言）：对表中的数据进行操作

select:查询表中的数据

insert:向表中插入数据

update:更新表中的数据

delete：删除表中的数据

DCL(数据控制语言)：针对用户的操作

commlt:确认对数据库中的数据进行的变更

rollback:取消对数据库中的数据进行的变更

grant：赋予用户操作权限

revoke：取消用户操作权限

在实际中，基本上用到的是DML部分的语句，下面说一下这些语句的基本规则：

1，一条SQL语句要以分号（;）结尾。

2，SQL语句不区分大小写。

3，单词之间要用半角空格或换行符进行分割

三，对表进行操作

对表进行的操作一般有表的创建、删除和更新，下面介绍一下其语句

1，数据库的创建：

create database <数据库名称>;

例：create database shop

2，表的创建：

create table<表名>（
                 <列名1><数据类型><约束条件>, <列名2><数据类型><约束条件>...
                  primary key<列名>）;

数据类型：integer整数型，char字符型，varchar可变长度字符串型，date日期型

约束条件：列的约束：not null 和null，主键约束：primary key<列名>

3，表的删除

drop table<表名>;

例：drop table product;

4,表的更新

添加列：alter table<表名>add column<列名><列的定义>;

删除列：alter table<表名>add column<列名>;

插入数据：begin(start) transaction;

insert into <表名> values(<数据1>，<数据2>...）；--插入是数据与列要对应
...
commit

5，表名 的修改

rename table<原表名>to<新表名>;

6，列的查询

select<列名>as<列别名>...from<表名>;  --列别名是中文时用双引号（“”）括起来

常数的查询

select <常数>as<列名>

例：SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date, product_id,product_name

FROM Product;

在结果中删除重复行

select distinct <列名> from <表名>;

7，where条件查询

select<列名>from<表名>
where<条件表达式>;

8，算术运算符和比较运算符

*算术运算就是用列名进行正常的四则运算，注意null参与的运算都是null。

*比较运算符一般用于条件语句中，要额外注意字符串比较大小时的规则。

*选取条件中有null时应注意判定方法：is null 和is not null。

9，逻辑运算符

not ，and 和or用于多个查询条件时对条件的约束，注意运算顺序

四，聚合与排序

聚合运算通常是写在select之后，通常是以列计算，会将null排除在外

1，通常所用到的聚合函数有：count,sum,avg,max,min

计算行数：count（*）或count<列名>

计算合计值：sum<列名>

计算平均值：avg<列名>

计算最大值和最小值：max<列名>,min<列名>

删除重复值：count(distinct<列名>)，在函数里面使用distinct

2，分组

使用group by对数据进行分组时应注意：

*group by 语句写在where之后，

*分组时在select子句中只能出现常数、聚合函数和group by子句中的分组列，

*group by 中不能用列的别名

3，having子句中使用元素：常数、聚合函数和group by子句中的分组列，having是对分组结果所对应的条件，

4，对查询结果进行排序：order by

*order by是对查询结果进行排序，默认排序是升序(asc)，若想要降序排列使用desc关键字

*可以使用多个排序键，优先顺序从左到右，排序时null出现在开头或结尾

*在排序键中可以使用列的别名，也可以使用其他列

语句的执行顺序：from——where——group by——having——select——order by

五，数据的处理

1，insert into <表名>（列清单）values（值清单）；

在列设置了default 默认值的情况下，可以向表中插入默认值

insert语句可以把数据导入一个新表中，导入方式用select语句实现：

insert into <表名>（列清单）select<被复制数据列>；

注意两表列要一致

2，数据的删除

*drop table 语句是把表完全删除

*delete 语句是清空表中的数据：

delete from <表名>where<条件>；

3，数据的更新

update<表名>set<列名>=<表达式>，<列名>=<表达式>
where<条件>；

null清空：表达式的值写为null即可

4，事务

事务是将多个DML操作语句同时进行处理，具体流程如下（MySQL中）：

start transaction;
DML语句1；
DML语句2；
commit

结尾使用rollback时为取消处理，数据不会改变，

事务的特性：ACID

A:原子性，所有子句同时执行

C：一致性，约束条件一致

I：隔离性，各事务之间互不干扰

D：持久性，数据在特定时间内能恢复

六，复杂查询

1，视图：使用select语句来保存数据的操作，那么如何创建视图

create view 视图名称（视图列名1，视图列名2...）
as 
<select语句>

视图其实就是保存好的select语句，使用视图查询时需要使用到多个select语句，视图可以更新，但是视图如果是汇总得到的，则无法更新

删除视图语句

drop view 视图名称（视图列1，视图列2）

2，子查询

子查询就是一次性视图，是一种嵌套结构，将定义视图的select语句直接用于查询from子句，内层查询会先被执行

select <子查询列>
from (
<原表select语句>)
as <子查询表名>

标量子查询：标量子查询只能返回一个单元格的值，常跟一些聚合函数搭配用于一些不能使用聚合函数的子句中（having等），

关联子查询：在有多个分组数据时无法使用标量子查询，需要使用关联子查询来实现，通常用having<表名>.<列名>语句搭配分组来实现，关联子查询跟标量子查询用途相同，只是出现分组情况时，细化分析分组后的多个数据时，需用关联子查询

七，一些其他的函数，谓词和case表达式

1. 针对数值的函数：

• 绝对值：abs(数值)
• 求余：mod(被除数，除数)
• 四舍五入：round（数值，保留小数位数）

2.针对字符串的函数：

• 拼接：<字符串1>||<字符串2>，注，在MySQL中用concat函数，SQL server中用“+”
• 字符串长度：length（字符串）
• 小写转换：lower（字符串），大写转换：upper（字符串）
• 字符串替换：replace（对象字符串，替换前字符串，替换后字符串）
• 字符串截取：substring(对象字符串，from 截取的起始位置 for 截取的字符数)

3.针对日期的函数：

• 当前日期：current_date;
• 当前时间：current_dime
• 当前日期和时间：current_timestamp
• 截取日期元素：extract（日期元素 from 日期） ；注：元素有year，month，day,hour,minute,second

4.一些特殊的转换函数：

• 类型转换：cast（转换前的值 as 想要转换的数据类型）
• 将null转换为其他值：coalesce（数据1，数据2，数据3...）

5.谓词：

• 字符串的部分一致查询：like<%字符串%>或like<字符串_ _>
• 范围查询：between 阈值 and 阈值
• 判断是否为null：is null和is not null
• 指定子查询或数据内查询：in（子查询）或in（数据1，数据2...），not in 用法相同，注意的是in参数中不能包含null，返回结果中也不能有null
• exist用法和in相似，大部分能用in代替

6.case表达式：

case 

case表达式是依次执行的，先判断是否满足第一个子句的条件，如果满足就执行，如果不满足跳到下一个子句判断，直到执行到end。

八、集合运算

1.表的全集：<select子句>union<select子句>；

注意事项：表的加法类似与两个集合的全集，两个select子句中的列必须一致，order by子句只能放在最后使用一次，如需保留重复行的话使用 union all。

2.表的交集： <select子句>intersect<select子句>；用法与全集相同

3.表的差集：<select子句>except<select子句>；注意被减数与减数的分别，前后位置

表的联结

内联结：inner join、

<表名>inner join<表名>
on<联结键>

注意：在select子句中，列的书写要加上别名来区别来源，from子句中表写上别名便于查看，on子句写在from和where之间，如果指定多个键，可以使用and联结。

外联结：outer join，外联结结果显示表的所有行

交叉联结：cross join，笛卡尔积，所有出现的情况都列出来

九，高级处理

1，窗口函数

<窗口函数>over（[partition by<列清单>]
                       order by<排序用列清单>）

窗口函数之后书写在select子句中，窗口函数有分组和排序两种功能，partition by指定分组列，order by指定排序列，

在执行partition by之后的集合称之为窗口，窗口函数可以使用聚合函数或者专用函数rank、dense_rank，row_number等

rank函数：1，1，1，4，4，5，相同位次跳过之后位次

dense_rank函数：1，1，1，2，2，3，不跳过位次排序

row_number函数：1，2，3，4，5，6，连续排序

使用聚合函数时，是以自身为基准进行统计，在order by后面加上限制条件（如rows 2 preceding为自身加前面2行 ，following为之后行）限制窗口范围

2，grouping运算符

rollup:计算合计值，group by rollup<聚合键>

grouping：超级分组记录时null返回1，可以用字符串替换，

cube：语法与rollup类似，只是把其他聚合键分类情况也列出来

grouping sets：先按聚合键1来分类，再按聚合键2分类

例：代码

SELECT CASE WHEN GROUPING(product_type) = 1 
 THEN '商品种类 合计'
 ELSE product_type END AS product_type,
 CASE WHEN GROUPING(regist_date) = 1 
 THEN '登记日期 合计'
 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
 SUM(sale_price) AS sum_price
 FROM Product
 GROUP BY CUBE(product_type, regist_date);

输出结果：

 product_type     regist_date        sum_price
-------------- ------------ ----------
商品种类 合计     登记日期 合计       16780
商品种类 合计     2008-04-28         880 ←追加
商品种类 合计     2009-01-15         6800 ←追加
商品种类 合计     2009-09-11         500 ←追加
商品种类 合计     2009-09-20         4500 ←追加
商品种类 合计     2009-11-11         100 ←追加
商品种类 合计                        4000 ←追加
厨房用具         登记日期 合计        11180
厨房用具          2008-04-28         880
厨房用具          2009-01-15         6800
厨房用具          2009-09-20         3500
办公用品         登记日期 合计        600
办公用品          2009-09-11         500
办公用品          2009-11-11         100
衣服             登记日期 合计       5000
衣服             2009-09-20         1000
衣服                                4000