C#基础知识及题目练习 Day22 数据库基础4 sql语句（分组、函数、多表联合查询）

目录

一、分组统计

二、聚合函数

三、常用的SQL内置函数

四、多表联合查询

五、执行顺序

---

一、分组统计

group by：分组

Having : 对分组以后的组进行筛选

SELECT   <字段列表>   [聚合函数]  FROM  表  [WHERE]  条件 GROUP BY <字段列表> [Having]  条件

牢记：

    SELECT语句中除聚合函数外所有列，应写在Group By语句后面,否则将出现错误。

SELECT  City as 城市, Sex as 性别, Count(*)  as 人数  FROM Customers  Group By City ,Sex

--将学生按照班级分组，查询班级编号和该班级中年龄最大的同学，只显示班级人数大于200的
select FClassId,MAX(FAge) as 最大年龄,COUNT(*)as 班级人数 from MyStudent group by FClassId having COUNT(*)>200

--统计销售总价超过3000元的商品名称和销售总价，并按销售总价降序排列   MyOrders
select 商品名称,Sum(销售数量*销售价格) as 销售总价 from MyOrders group by 商品名称 having Sum(销售数量*销售价格)>3000 Order by 销售总价 desc

二、聚合函数

1、Max（）：求最大值

2、Min（）：求最小值

    MAX和MIN除了计算数值列，还可以用于计算字符型以及日期时间类型数据列

    SELECT MAX(Birthday) as 最大日期 FROM Customers

3、Avg（）：求平均值

4、Sum（）：求和

5、COUNT(*) ：数据表中一共有多少条数据（项目登录时使用！）
select Count(*) from Users where UserName='张三' and Pwd=‘123’

注意：聚合函数不对null值做计算，如果不指定分组，默认将整行当做一组

 

三、常用的SQL内置函数

• --获取字符串长度 select LEN(FName) as 姓名长度 from MyStudent
• --转大写 select UPPER('book三体')
• --charINdex select CHARINDEX('L','HELLO',1)
• --replace 替换字符串 select REPLACE('我爱我的家乡','家乡','老婆')
• --substring 取子字符串   sql中，索引从1开始 select SUBSTRING('床前明月光',3,2)
• --GetDate:获取系统日期 select GETDATE()
• --DatePart:返回指定日期的部分整数 select DATEPART(YY,'2019-9-15')  --得到日期中的年
• --DateAdd:在指定的日期上，累加值，得到新日期 select DATEADD(DAY,35,'1991-1-1')-- 在1991年的基础上，加了21年
• --DateDiff:返回两个日期的差值 select DATEDIFF(yy,'1998.2.6','2019.9.5')
• --DATENAME：返回指定日期的部分字符串 select DATENAME(mm,'2019.9.5')
• --Year:返回日期中的年 select YEAR(GETDATE())  --返回当前日期中的年

£字符串函数

函数名	功能描述	举例
LEN	返回指定字符串的字符个数(而不是字节)，其中不包含尾随空格	SELECT LEN('李丽然作者') 返回:5
UPPER	将小写字符转换成大写字符	SELECT UPPER('book图书表') 返回:BOOK图书表
LTRIM	返回去掉左侧空格的字符串	SELECT LTRIM('  Authors') 返回: Authors
CHARINDEX	查找一个指定的字符串在另一个字符串中的起始位置	SELECT CHARINDEX('L', 'HELLO', 1) 返回:3
LEFT	返回字符串中从左边开指定个数的字符	SELECT LEFT('zhangsan', 2) 返回:zh
DATALENGTH	返回指定字符串的字节数	SELECT DATALENGTH('中国人') 返回：6
REPLACE	替换一个字符串中的字符	SELECT REPLACE('我爱我的家乡家乡', '家乡', '学校') 返回: 我爱我的学校学校
SBUSTRING	从字符串串的起始位置连续取指定个数的子串	SELECT SUBSTRING('我爱我的家乡',3, 2) 返回：我的

£日期函数

函数名	功能描述	举例
GETDATE	得到当前系统日期	SELECT GETDATE() 返回：系统当前日期
DATEPART	返回指定日期部分的整数	SELECTDATEPART(dd, '01/09/2003') 返回：9
DATEADD	在指定的日期上累加数值得到新的日期	SELECT DATEADD(yyyy, 4, '01/09/2003') 返回：2007-01-09
DATEDIFF	返回两个日期的差值	SELECT DATEDIFF(dd, '02/05/2003', '02/09/2005') 返回：735
DATENAME	返回指定日期部分的字符串	SELECT DATENAME(dw, '02/02/2009') 返回: 星期一
YEAR	返回指定日期“年”部分整数	SELECT YEAR(GETDATE()) 返回：当前年份整数
MONTH	返回指定日期“月”部分整数	SELECT MONTH(GETDATE()) 返回：当前月份整数
DAY	返回指定日期“日”部分整数	SELECT DAY(GETDATE()) 返回：当前日期整数

£数学函数

函数名	功能描述	举例
ABS	返回表达式绝对值	SELECT ABS(-90) 返回：90
ROUND	按指定的精度进行四舍五入	SELECT ROUND(56.629, 2) 返回：56.630
SQRT	返回指定表达式的平方根	SELECT SQRT(9) 返回：3
FLOOR	返回小于或等于指定数值表达式的最大整数	SELECT FLOOR(23.9) 返回: 23
CEILING	返回大于或等于指定数值表达式的最小整数	SELECT CEILING(23.9) 返回：24

£类型转换函数

函数名	功能描述	举例
CONVERT	数据类型转换： 将第2个参数转 换为第一个参数 所指定的类型。	SELECT CONVERT(DateTime, '2020-09-09') 返回: 2020-09-09 00:00:00.000   SELECT CONVERT(varchar(11), GETDATE(), 121) 返回：2010-03-24 常用日期格式：   121：日期格式为 yyyy-mm-dd hh:mi:ss.mmm   111：日期格式为 yy/mm/dd   SELECT CONVERT(varchar(5), 92.89) 返回：92.89

--6、假设今天是‘2010-7-31’输出本月通话总时长最多的前三个呼叫员的编号（使用表CallRecords）
select top 3  callerNumber,Sum(DATEDIFF(ss,StartDateTime,EndDateTime)) as 月通话总时长 from CallRecords 
where MONTH(EndDateTime)= MONTH('2010-7-31') group by callerNumber order by 月通话总时长 desc

 注意日期用‘’括起来！！

 

四、多表联合查询

£内联接(INNER JOIN)

£外联接     ——左外联接   (LEFT JOIN)

——右外联接   (RIGHT JOIN)

——完整外联接(FULL JOIN)

£交叉联接(CROSS JOIN)

1、内连接  inner join：查询两个表公共列共有的记录

SELECT <列名> FROM 表1  INNER  JOIN  表2 ON 表1.列名  条件运算符  表2.列名

[WHERE 条件]  [ORDER BY  排序列]

其中“ON 表1.列名  条件运算符  表2.列名” 中，条件运算符常用的是：=、<>。

2、外连接：

左外连接是以左表为主表，去关联右表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

SELECT <列名> FROM 左表  LEFT  [OUTER] JOIN  右表 ON 左表.列名  条件运算符  右表.列名

[WHERE 条件] [ORDER BY  排序列]

右外连接反之。

3、完全连接 full out join（少用）：略

--以成绩表为主表，查询学号 学生姓名 课程名称 成绩
select A.studentId,B.FName,A.courseName,A.score from StudentScore A left join MyStudent B
on A.studentId=B.Fid

 

五、执行顺序

SQl语句执行顺序：

-- 5) select   5-1）选择列   5-2）distinct  5-3 top 
-- 1)from MyStudent
-- 2)where FAge>16
-- 3)group by
-- 4)Having
-- 6)order by  --不管你的语句有多长，如果有order  by  要把它放到最后