sql语句遇见的常用语法

设置查询别名

查询时候指定别名

select id as '编号' , name as '姓名' from 表名;

as可以省略

查询头几条数据

MySQL 语法:

从 表中选取头两条记录:

select * from 表名 LIMIT 2;

Oracle 语法:

从表中选取头两条记录:

select * from 表名 where ROWNUM <= 2;

distinct 去重

select distinct 字段名 from 表名

count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct

col 1, col 2 ) 如果其中一列全为 NULL ，那么即使另一列有不同的值，也返回为 0 。

模糊查询

LIKE 操作符、SQL 通配符
(1)从表中选取以 "A" 为开始的的字段：

select * from 表名 where 字段名 LIKE 'A%';

(2) 从表中选取以 "g" 为结尾的字段：

select * from 表名 where 字段名 LIKE '%g';

(3) 从表中选取包含 "num" 的字段：

select * from 表名 where 字段名 LIKE '%num%';

(4) 从表中选取不包含 "num" 的字段：

select * from 表名 where 字段名 NOT LIKE '%num%';

(5) 从表中选取第一个字符之后是 "number" 的字段：

select * from 表名 where 字段名  LIKE  '_number';

(6) 从表中选取以 "A" 开头，然后是一个任意字符，然后是 "r"，然后是一个任意字符，然后是 "number"的字段：

select * from 表名 where 字段名  LIKE  'A_r_number';

(7) 从表中选取以 "A" 或 "L" 或 "N" 开头的字段：

select * from 表名 where 字段名  LIKE  '【ALN】%';

(8) 从表中选取不以 "A" 或 "L" 或 "N" 开头的字段：

select * from 表名 where 字段名  LIKE  '【!ALN】%';

参考：sql语句简便提取公有以及模糊查询,添加,更新及删除_青衣武影的博客-CSDN博客

IN 操作符

select id from 表名 where id in(value1,value2,...);
在括号中的字符串中找出与表中id相同的值,并返回

涉及到两表查询根据情况可以用in也可以用exists

函数简介：in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询表小的用in；

select * from A表 where cc(字段) in （select cc from B表）;

select * from A表 where exists(select cc(字段) from B表 where cc = A.cc);

in与等号区别

下边两个代码输出意义相同

select name from student where name in ('zhang','li','zhao'); 一对多
select name from student where name='zhang' or name='li'  or name='zhao'；一对一

注意：

in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控

制在 1000 个之内。

两表联查

通常遇到想要输出的表结果为两个表的查询数据在一个表里体现时用到并且主键id关联对应

内连接 inner join 只显示两张表有关联的数据
语法：select 字段名,字段名 from 表A inner join 表B on 表A和表B的关联条件

左连接 left join 会将左边表的全部数据进行展示，另外一张表有和它关联的就展 示，没有就展示为null
语法：select 字段名,字段名 from 表A left join 表B on 表A和表B的关联条件
右连接 right join 会将右边表的全部数据进行展示，另外一张表有和它关联的就展示，没有就展示为null
语法：select 字段名,字段名 from 表A right join 表B on 表A和表B的关联条件

例子：

select aa.字段，bb.字段 from A表 aa left join B表 bb on aa.id = bb.id （后边可以加where 添加想加的条件）

例子链接：SQL语句两个表查询数据并取对应ID主键的最近时间的一个值_青衣武影的博客-CSDN博客

聚合函数

1.count(*)

统计指定列不为NULL的记录行数； 统计个数的

select count(*) from 表名；

2.MAX()

计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

select max(age) as 最大年龄 from 表名

3.MIN()

计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

select min(age) as 最小年龄 from 表名

4.SUM()

计算指定列的数值和，如果指 定列类型不是数值类型，那么计算结果为0；

select sum(age) as 年龄之和 from 表名

注意：当某一列的值全是 NULL 时， count(col) 的返回结果为 0 ，但 sum(col) 的返回结果为

NULL ，因此使用 sum() 时需注意 NPE 问题。

正确例子： 可以使用如下方式来避免 sum 的 NPE 问题： SELECT IF(ISNULL(SUM(g)) ,0, SUM(g))

FROM table;

5.AVG()

计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

select avg(age) as 平均年龄 from 表名

Case使用

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

注意：Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

计算数量时候例子：

SUM(CASE 字段 WHEN 1 THEN 1 END ) as xxx

排序

通常用到排序用法ORDER BY 默认为升序ASC，降序用DESC

例子：

以id升序排列

select 字段 from 表名 order by id;

已降序排列

select 字段 from 表名 order by id DESC;

两个字段一个升序排列一个降序排列

select 字段a,字段b,字段... from 表名 order by 字段a ASC;字段b DESC;

分组(group by having)

生活里常用到分组查询group by，

比如每一个班级对应多个学生，对班级进行分组查询每个班级学生个数

Select ClassNum,Count(*) from 表名 group by ClassNum

再比如每一个公司对应了多条数据，对公司进行分组取每一个公司一条最新数据，这时候也会遇到分组

select customer_id,max(字段名)定义字段名 from 表名 group by  customer_id

提到group by就不得不说一下having

having使用其实和where有些类似，只是where是过滤行，而having是过滤分组，结合group by使用

分区(partition by)

与分组区分：group by更强调的是一个整体，就是组，只能显示一个组里满足聚合函数的一条记录， partition by 在整体后更强调个体，能显示组里所有个体的记录。

例子：

select
        ROW_NUMBER() OVER(PARTITION BY 字段 ORDER BY 字段2 DESC) rn

具体区分：分区函数Partition By的用法_程序员之道-CSDN博客

分页

用limit实现sql的 分页查询

select * from 表名 limit i,n

注：

i:是查询结果的索引值（默认从0开始）i=0时可省略   limit n=limit 0,n

n:查询结果返回的数量

例子：

select * from table limit 0,3：  检索0代表的是第一个数，从第一个数开始往后3个

select * from table limit 1,4：  检索从第2个数开始，往后显示4个数

截取

substr()用于字符串的截取有两种格式

格式1： substr(string string, int a, int b);

格式2：substr(string string, int a) ;

注：

格式1：
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取，负数代表倒数截取）
3、b 要截取的字符串的长度

格式2：
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。

例子：substr(SAL_FINISH_TIME,1,6) 取前6个字符的时间如：202101 (yyyyMM)

具体例子：Oracle中的substr()函数 详解及应用 - DSHORE - 博客园

并集union和union all

写sql时候，有些数据的存储有可能涉及到分库分表，查询的时候，可能需要查询所有的分表，这个时候，就需要用到UNION或者UNION ALL

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

UNION语法：

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

union操作符合并的结果集，不会允许重复值，如果允许有重复值的话，使用UNION ALL.

具体查看链接：SQL中UNION和UNION ALL的详细用法_zouxucong的博客-CSDN博客

union和union all的用法_hstjbj的博客-CSDN博客

百分比查询（round,concat,count,cast函数,decode判断）

认识round函数

round函数返回数字表达式并四舍五入为指定的长度或精度。

ROUND(X)： 返回参数X的四舍五入的一个整数。

ROUND(X,D)： 返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

语法：select concat(round(值A/值B*100,m),'%')

concat用来拼接字符串 

比如模糊查询   字段 like concat('%',concat(字段,'%')）

注：在Oracle里 concat只能连接两个字符，而“||”可以连接多个字符。

例子：round(a值/b值*100,2)||'%' 

count函数

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL。

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空。

cast函数

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

sql中计算百分比：（转成字符串然后拼接%）

SELECT CONCAT(CAST(round((3/21)*100,3) AS CHAR),'%') as aa from act_canal;

例子转载于：sql中计算百分比 - super超人 - 博客园

decode()条件判断

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

如果条件=值1，return 返回值1...否则缺省值

decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3

当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

实例：后台柜员评价占比例系统的查询sql语句（包含下属机构分支所有数据参照银行）_青衣武影的博客-CSDN博客