SqlServer高级函数使用

-- 函数
-- 字符函数
--      len 字符的个数
--      charindex 某个字符的位置
--      replace 替换
--      ltrim,rtrim 去除空格
--      left,right 取几个字符
--      upper,lower 大小写
-- 日期函数
--      getdate() 当前日期
--      dateadd 在某个日期上增加xx单位
--          dateadd(dd,2,getdate())
--      datediff 求两个日期的差值
--          datediff(mm,'2020/1/1','2022/2/1')
--      year,month,day 取年月日
-- 数学函数
--      ceiling 向上取整
--      floor 向下取整
--      round 四舍五入
--      abs 绝对值

--------------------高级查询------------------

-- orders 订单表
select * from orders;

--查看2010年的订单
select * from orders where year(ordersDate)=2010;

--查看2010年之前的订单
select * from orders where year(ordersDate)<2010;

--查看2010之前的1~10月之间的订单
-- 年份<2010
-- 月份 1~10
select * from orders
where year(ordersDate)<2010
and month(ordersDate) between 1 and 10;

--查看客户名字超过2个字的订单记录
select * from orders where len(customer)>2;

-- 聚合函数

------------count() 计算次数---------------------------

--计算订单的数目，并且取中文别名
select count(*) as '订单总数' from orders;

--查看布鲁士的订单数目
select count(*) as '布鲁士的订单数' from orders
where customer='布鲁士';

------------sum() 求和---------------------------

--查看订单的总价,并且取中文别名
select sum(ordersPrice) as '总价' from orders;

--查看订单的总单数与销售总额,并且取中文别名
select count(*) as '数量',sum(ordersPrice) as '总价' from orders;

------------avg() 求平均值---------------------------

--求订单平均金额,并且取中文别名
select avg(ordersPrice) from orders;

--求订单的订单数，总和，平均值,并且取中文别名
select count(*),sum(ordersPrice),avg(ordersPrice) from orders;

--求Bush的订单总和与订单数和平均值
select count(*),
       sum(ordersPrice),
       round(avg(ordersPrice),2)
from orders
where customer='布鲁士';

------------max()最大值,min()最小值---------------------------

--求最高金额的订单
select max(ordersPrice) from orders;

--求最小金额的订单
select min(ordersPrice) from orders;

--求Bush的最大订单
--求Bush的最小订单
select max(ordersPrice),
       min(ordersPrice)
from orders where customer='布鲁士';

-----------聚合函数常和分组函数group by结合使用--------------

--查看订单表中的客户

-- 去重复 distinct
select distinct customer from orders;

-- 分组 group by
-- select 的后面只能出现聚合函数和分组依据
-- 原因:其他数据存在多个值  无法判断该取值哪一个值
select customer from orders group by customer;

--查看每个客户对应的订单数目
select customer,count(*) from orders
group by customer;

--查看每个客户对应的订单数目与总金额
select customer,
       count(*),
       sum(ordersPrice)
from orders
group by customer;

--查看每个客户对应的订单数目与总金额与平均值
select customer,
       count(*),
       sum(ordersPrice),
       avg(ordersPrice)
from orders
group by customer;

--查看订单数目最多的客户与总数，总金额
-- 先分组，取出数量
-- 再根据数量排序
-- 取第一个
select top 1 customer,
       count(*)
from orders
group by customer
order by count(*) desc;

--查看订单平均值最高的客户
select top 1 customer,
       avg(ordersPrice)
from orders
group by customer --分组
order by avg(ordersPrice) desc;  --排序

-- avg 平均
-- sum 求和
-- max 最大值
-- min 最小值
-- count 次数
-- group by 分组

------------------联表查询----------------------

create table tb_stu
(
    SCode int not null primary key,
    SName char(10) not null,
    SAddress nvarchar(50),
    SBirth datetime,
    SGrade varchar(2) default('S1'),
    SEmail nvarchar(50) check(SEmail like '%@%'),
    sex char(10)
);

insert into tb_stu(SCode, SName, SAddress, SBirth, sex)
select 1, '曾敏华', '株洲', '1990-01-01', '女' union
select 2, '杨勇', '株洲', '1990-01-01', '男' union
select 3, '陈世军', '长沙', '1990-01-01', '男' union
select 4, '张葎', '长沙', '1990-01-01', '男' union
select 5, '张玉桂', '长沙', '1990-01-01', '男' union
select 6, '刘年富', '长沙', '1990-01-01', '男' union
select 7, '刘欢', '湘潭', '1990-01-01', '男' union
select 8, '叶振溪', '湘潭', '1990-01-01', '男' union
select 9, '罗青', '湘潭', '1990-01-01', '男' union
select 10, '全乐', '衡阳', '1990-01-01', '男' union
select 11, '郑联涛', '衡阳', '1990-01-01', '男' union
select 12, '周玲芳', '衡阳', '1990-01-01', '女';

create table tb_score
(
    ScoreID int not null primary key identity(1, 1),
    StudentID int not null references tb_stu(SCode),
    Course varchar(10) not null,
    Score float
);

insert into tb_score
select 1,'JAVA',89 union
select 1,'HTML',80 union
select 2,'JAVA',92 union
select 2,'HTML',74 union
select 3,'JAVA',76 union
select 3,'HTML',95 union
select 4,'JAVA',NULL union
select 4,'HTML',NULL union
select 5,'JAVA',48 union
select 5,'HTML',79 union
select 6,'JAVA',NULL union
select 6,'HTML',NULL union
select 7,'JAVA',66 union
select 7,'HTML',88;

-- 学生表
select * from tb_stu;

-- 成绩表
select * from tb_score;

--计算1989年之后的出生的人的数目
select count(*) from tb_stu where year(SBirth)>1989;

--查看班上的地址分布
select SAddress from tb_stu group by SAddress;

--查看每个地址的人数
select SAddress,
       count(*)
from tb_stu group by SAddress;

--查看性别与人数配比
select sex,
       count(*)
from tb_stu group by sex;

--查看人数大于2的地址
-- 如果需要对分组之后的结果进行过滤
-- 请使用 having + 聚合函数
select SAddress,
       count(*)
from tb_stu group by SAddress having count(*)>2;

--查看每个地区的男女配比
select SAddress,sex,count(*) from tb_stu
group by SAddress,sex;

--查询班上出生人数大于2人的年份
select year(SBirth),count(*)
from tb_stu
group by year(SBirth) having count(*)>2;

--求参加考试学生的学号，姓名，分数

--左连接: 左表的数据都出来  left join
select * from tb_stu a left join tb_score b
on a.SCode=b.StudentID;

--右连接: 右表的数据都出来  right join
select * from tb_stu a right join tb_score b
on a.SCode=b.StudentID;

--全连接: 全部出来 full join
select * from tb_stu a full join tb_score b
on a.SCode=b.StudentID;

--内连接: 两张表都有的数据才会出来 inner join
select * from tb_stu a inner join tb_score b
on a.SCode=b.StudentID;