Day7 SQL常用命令续集

1、连接查询join...on

连接查询适用于当需要的结果来自多张表时。
内连接：inner join，查询两表中完全匹配的数据；
左外连接：left outer join，查询两表中完全匹配的数据，以及左表特有的数据；
左外连接：left outer join，查询两表中完全匹配的数据，以及右表特有的数据；
完全外连接：full outer join，查询两表中完全匹配的数据，以及左表特有的数据，还有右表特有的数据；

示例：

select * from StudentInfo as si inner join ClassInfo as ci on si.cid=ci.cId--查
询表StudentInfo中cid与表ClassInfo中cId完全匹配的所有列,as：为表取别名，StudentInfo
as si就是为表Student取别名为si
select * from ClassInfo as ci left join StudentInfo as si on ci.cId=si.cid--查
询表ClassInfo中cId与表StudentInfo中cid完全匹配的，以及ClassInfo特有的所有列,

2、聚合函数

聚合函数用于对行数据进行合并，一般是对数字类型的列进行操作，一条查询中可以写多个聚合函数（null不参与运算）。
常用聚合函数：sum（求和）、avg（求平均值）、count（计数）、max（求最大值）、min（求最小值）。

sum示例：计算表StudentInfo中列cid的和，并取别名为sum

select SUM(cid) as sum from StudentInfo

avg示例：计算表StudentInfo中列cid的平均值

select AVG(cid) from StudentInfo

count示例：计算StudentInfo表中一共多少行，如果存在某行的值全为null，则不计数

select COUNT(*) from StudentInfo

max示例：查询表StudentInfo的列cid的最大值

select MAX(cid) from StudentInfo

min示例：查询表StudentInfo的列cid的最小值

select MIN(cid) from StudentInfo

3、开窗函数over()

开窗函数和聚合函数结合使用，用于将聚合结果还原至原数据，便于将聚合结果与原数据进行对比。
开窗函数还可以和排名函数ROW_NUMBER()结合使用。

结合聚合函数示例：计算表StudentInfo的列cid的平均值，并利用开窗函数与原cid进行对比

select *,AVG(cid) over() from StudentInfo

结果：

结合排名函数示例：将表StudentInfo中sGendre=1的数据进行排序，并且进行排号

select *,ROW_NUMBER() over(order by sId desc) as rowindex from 
StudentInfo where sGender=1

结果：

4、分组group by

聚合函数一般结合分组使用，进行分组内的数据统计；分组依据值相同的示例在一组，在结果列中只能出现分组依据列和聚合列。
ps：group by与where共存时，group by写where后面。

单一列依据分组示例：将表studentInfo按照sGender进行分组，并计算每组数量

select sGender,count(*) from StudentInfo group by sGender--分组将屏蔽除分组依据以
外的其他列，故结果集中只能显示分组依据列、聚合函数列

结果：

多列分组示例：将表StudentInfo按照sGender和cid进行分组，并计算每组数量

select sGender,cid,count(*) from StudentInfo group by sGender,cid
select * from StudentInfo

结果：

5、分组结果筛选having

示例：将表StudentInfo按照cid进行分组，并且计算每组数量，筛选出数量大于1的组

select cid,count(*) from StudentInfo group by cid having COUNT(*)>1

6、联合查询union

联合查询：将多个查询的结果集合并成一个结果集。
联合要求：
结果集列数一致；
对应列的类型一致。

关键字：
union：将多个结果集的数据进行合并，并且消除重复行，按照第一列从小到大排序；
union all：将多个结果集进行合并，但不消除重复行，也不排序；
except：差集，A except B表示A结果集中的数据但不包括B结果集中的数据；
intersect：交集，结果集中都有的数据。

union示例：

select cId from ClassInfo union select sId from StudentInfo

结果：

union all示例：

select cId from ClassInfo union all select sId from StudentInfo

结果：

except示例：

select cId from ClassInfo except select sId from StudentInfo

结果：

intersect示例：

select cId from ClassInfo intersect select sId from StudentInfo

结果：

7、快速备份

向未有表备份：select 列名 into 备份表名 from 源表名。
（备份表如果不存在将新建表，表的结构完全一致，但是不包含约束，如果想只包含结构不包含数据，可以加个top 0）
示例：将表ClassInfo的结构和数据快速备份到表test1，将自动新建表test1

select * into test1 from ClassInfo

向已有表备份：insert into 备份表名 select 列名 from 源表名。
示例：向已有表test2中备份表ClassInfo的列cTitle

insert into test2(cTitle) select cTitle from ClassInfo

8、内置函数

8.1、类型转换函数

cast(expression as date_type)：将任意类型转到任意类型
convert(date_type ,expression[,style])：将任意类型转到任意类型，如果目标类型是字符串，则style可以设置样式
示例：将89.0000转换成89.0

select CAST(89.0000 as decimal(4,1))
select CONVERT(decimal(4,1),89.0000)

8.2、字符串函数

ascii：求字符的ascii码值；
char：根据ascii码转到字符；
left：自左开始往右截取字符串；
right：自右开始往左截取字符串；
substring：从任意位置开始截取字符串，函数参数：字符串、开始索引、截取数量（索引从1开始）；
len：返回字符串的长度；
lower：转小写；
upper：转大写；
ltrim：去除左侧空格；
rtrim：去除右侧空格；

示例：

8.3、日期函数

getDate：获取当前日期时间；
dateAdd：日期加；
dateDiff：日期差；
datePart：取日期的某部分
year：取年；
month：取月；
day：取日。
（返回值都是int类型）

示例：