sql 语句进阶操作（count、sum、case-when、group_concat、order by limit offset、dense_rank rank、exis等解析及应用）

1.统计某一表中某个字段出现的次数。

用一条sql语句统计某字段为0值和1值的总数

如果onefield 只有0或1两个值，可以使用分组计算：

select onefield , count(1) cot from table group by onefield;

结果中会如下显示：

onefield cot

0 数量

1 数量

另一种方案：

select

sum(case when onefield = 0 then 1 else 0 end) zero_count,

sum(case when onefield = 1 then 1 else 0 end) one_count

from table;

 

2.sql语句中case-when的应用

（1）对某一参数值进行转换 ，常用于将sex值中的0,1 转换为男女

select
name as '名字',
(case sex when 0 then '女' else '男' end) as '性别'
from test.student;

（2）对某一范围值进行转换，常用于成绩单中90以上为优秀、80-90为良好、60-80为及格

SQL语句

select 

name as '姓名'

,(case score when score>=90 then '优' when score>=80 then '良' when score>=60 then '及格' else '不及格' end) as '等级'

from test.stu_score; 

（3）将列转为行操作

第一步 先按照科目分开， 符合条件的设置分数，不符合的给置零。

select name as '姓名'

,(case course when '语文' then score else 0 end) as '语文'

,(case course when '数学' then score else 0 end) as '数学'

,(case course when '英语' then score else 0 end) as '英语'

from test.course_score

然后再按照名字group by ，对分数求max。

select name as '姓名'

,max(case course when '语文' then score else 0 end) as '语文'

,max(case course when '数学' then score else 0 end) as '数学'

,max(case course when '英语' then score else 0 end) as '英语'

from test.course_score group by name;

3.group_concat()

mysql中，有个不错的函数group_concat，主要作用是用来应付如一对多情况的变体的

（1）将group_concat产生的同一个分组中的值连接起来，返回一个字符串结果。

（2）语法：group_concat([distinct]要连接的字段[order by 排序字段 asc/desc] [separator’分隔符’])

 

（1）使用group_concat()和group by显示相同名字的人的id号：

（2）将上面的id号从大到小排序，且用'_'作为分隔符：

（3）上面的查询中显示了以name分组的每组中所有的id。接下来我们要查询以name分组的所有组的id和score：

 

4.多表联查的不同数据表 标识

SELECT

    s.id,s. NAME, s.age, g.gname

FROM

    student s,  grade g

WHERE

    s.gid = g.id

5.IN操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。

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

IN 操作符实例

现在，我们希望从上表中选取姓氏为 Adams 和 Carter 的人：

我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons

 WHERE LastName IN ('Adams','Carter')

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
3	Carter	Thomas	Changan Street	Beijing

 

 

6.order by  desc/asc   limit  offset 

limit n,m,       n指定第一个返回记录行的偏移量，m指定返回记录行的最大数目。

select * from tablename limit 0,1

取出第一条记录

select * from tablename limit 1,1

第二条记录

Select * from tablename limit 10,20

从第11条到第31条记录

 

当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的数量,offset表示要跳过的数量 。

select * from article LIMIT 3 OFFSET 1

= select * from article LIMIT 1,3

表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

Order by id asc 升序 desc 降序

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql>SELECT*FROM table LIMIT 95,-1; // 检索记录行 96-last.

7.ISNULL 

ISNULL(参数1，参数2)，判断参数1是否为NULL，如果是，返回参数2，否则返回参数1。

8.Dense_rank rank

 

dense_rank 生成序号连续的 rank生成序号有可能不连续：

select RANK() OVER(order by [UserId]) as rank,* from [Order]

查询结果如下图所示：

 

select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]

查询结果如下图所示：

 

Over函数与rank dense rank 连用

9.in    exist not exist

 

 

MySQL EXISTS 和 NOT EXISTS 子查询语法如下：

• SELECT … FROM table WHERE EXISTS (subquery)

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。

下面来三张表的实例

我们先介绍下使用的3个数据表：

student数据表：

sno 学号	sname	ssex	sage
20161181	Altair	男	20
20161182	Desmond	男	18
20161183	Ezio	男	22
20161184	Christina	女	19

course数据表：

cno 课程编号	cname 课程名
1	C语言
2	数据结构
3	信号与系统
4	模拟电子技术
5	高数

sc数据表：

sno 学号	cno 课程编号	grade 成绩
20161181	1	99
20161182	2	98
20161181	2	97
20161181	3	95
20161184	3	92
20161181	4	90
20161181	5	88
20161183	5	58

EXISTS

EXISTS代表存在量词∃。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑假值“false”。

一个例子1.1：

要求：查询选修了课程”信号与系统“的同学

SELECT s.Sname FROM student s

WHERE EXISTS  

(SELECT * FROM sc, course c WHERE sc.sno = s.sno AND sc.cno = c.cno AND c.cname = '信号与系统')

使用存在量词EXISTS后，若内层查询结果为非空，则外层的WHERE子句返回值为真，否则返回值为假。

在本例中，首先分析最内层的语句：

SELECT * FROM sc, course c WHERE sc.sno = s.sno AND sc.cno = c.cno AND c.cname = '信号与系统'

本例中的子查询的查询条件依赖于外层父查询的某个属性值（本例中的是Student的Sno值），这个相关子查询的处理过程是：

首先取外层查询中（student）表的第一个元组，根据它与内层查询相关的属性值（Sno值）处理内层查询，若外层的WHERE返回为真，则取外层查询中该元组的Sname放入结果表；

然后再取（student）表的下一组，重复这一过程，直至外层（Student）表全部检查完毕。

查询结果表：

Sname
Altair
Christina

NOT EXISTS

与EXISTS谓词相对的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若对应查询结果为空，则外层的WHERE子语句返回值为真值，否则返回假值。

例子2.1：
要求：查询没有选修课程”信号与系统“的同学

SELECT s.Sname FROM student s

WHERE NOT EXISTS  

(SELECT * FROM sc, course c WHERE sc.sno = s.sno AND sc.cno = c.cno AND c.cname = '信号与系统')

使用NOT EXISTS之后，若内层查询结果为非空，则对应的NOT EXISTS不成立，所以对应的WHERE语句也不成立。

在例子1.1中李勇同学对应的记录符合内层的select语句的，所以返回该记录数据，但是对应的NOT EXISTS不成立，WHERE语句也不成立，表示这不是我们要查询的数据。

查询结果表：

Sname
Desmond
Ezio