SQL相关

一．小知识

1. SQL 查询区分大小写

  collate Chinese_PRC_CS_AS ：查询区分大小写;

   Chinese_PRC_CS_AI_WS：查询不区分大小写；（默认情况下，SQL SERVER查询不区分大小写）

 

2. SQL返回在当前会话生成的最后一个标识值。

 SCOPE_IDENTITY()：只返回插入到当前作用域中的值

  @@IDENTITY ：不受限于特定的作用域

3. SQL返回操作数据受影响的行数

    @@rowcount： SQL查询获取受影响的数据行数

4.快速保存两个日期的年月到数据表

--查询两个日期之间的年月

 declare @StartDate1 datetime; set @StartDate1='2018-07-01'

 declare @EndDate1 datetime; set @EndDate1='2019-03-01'

 select year(dateadd(month,num,@StartDate1)) as year1,month(dateadd(month,num,@StartDate1)) as month1

 FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID)-1 AS num FROM sysobjects) KK 

 where dateadd(month,num,@StartDate1) <=@EndDate1 

 

 

 

二．SQL内置函数

1. SQL内置函数 （CHARINDEX）　

说明：用于查找字符串A中是否包含字符串B。返回值是字符串B在字符串A中的位置（计数从1开始），若B中没有A,则返回0

基本语法如下：

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

1.      expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。

2.      expressionToSearch ：用于被查找的字符串。

3.      start_location：开始查找的位置，可为空（为空时默认从第一位开始查找）

返回值是从首位字符开始计数。

　 示例如下： 

--简单用法

selectcharindex('test','this Test is Test')

--增加开始位置

selectcharindex('test','this Test is Test',7)

--大小写敏感

selectcharindex('test','this Test is Test'COLLATE Latin1_General_CS_AS)

--大小写不敏感

selectcharindex('Test','this Test is Test'COLLATE Latin1_General_CI_AS)

 

2. SQL内置函数（PATINDEX）

用于查找字符串A中是否包含字符串B(支持模糊查询)。

返回值是字符串B在字符串A中的位置（计数从1开始），若B中没有A,则返回0。

基本语法如下：

PARTINDEX ( expressionToFind , expressionToSearch)

1.    expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。

2.    expressionToSearch ：用于被查找的字符串。

返回值是从首位字符开始计数。

案例如下：

selectpatindex('%cde%','abcdefgh') as A, patindex('%fgh%','abcdefgh') as A1

      ,patindex('cde%','abcdefgh') as B,patindex('abc%','abcdefgh') as B1,patindex('a%','abcdefgh') as B2

      ,patindex('%cde','abcdefgh') as C,patindex('%abc','abcdefgh') as C1,patindex('%fgh','abcdefgh') as C2

      ,patindex('cde','abcdefgh') as D,patindex('abcdefgh','abcdefgh') as D1

 

三．SQL高级查询

内连接（inner join）：查询两张表中同时拥有的数据。（交集）

左连接（left join）：以左侧表为主表。

右连接（right join）：以右侧表为主表。

全连接（full join）：存在于表a或表b中的数据。（并集）

交叉连接（across join）:返回连接操作的两张表中所有数据行的笛卡尔积，得到的结果集的行数是两个表行数的乘积。

 

--UNION (并操作) 查询选了课程17C01或17C02的学生编号 --无重复值（会做筛选）

select StudentNO from StudentCourse where CourseNo='17C01' UNION select StudentNO from StudentCourse where CourseNo='17C02'

 

--有重复值（会列出所有的值）

select StudentNO from StudentCourse where CourseNo='17C01' UNION ALL select StudentNO from StudentCourse where CourseNo='17C02'

 

--INTERSECT (交操作) 查询选了课程17C01和17C02的学生编号

select StudentNO from StudentCourse where CourseNo='17C01' INTERSECT select StudentNO from StudentCourse where CourseNo='17C02'

 

--EXCEPT(差操作) 查询选了课程17C01，没选课程17C02的学生编号

select StudentNO from StudentCourse where CourseNo='17C01' EXCEPT select StudentNO from StudentCourse where CourseNo='17C02'