【SQL server】基础入门4——查询，函数

█ 联合查询

即对查询结果的连接，将多个查询的结果集合并成一个结果集（不是内连接那种连接！！）

联合要求：① 结果集列数要一致

        ② 对应列的类型要一致

关键字：

union   ： 合并后消除重复行，且根据第一列进行升序排除（并集）

union all :   合并，但不能消除重复行和排序（并集）

except  ：（差集）

intersect ：（交集）

-- 格式 :
select ... from ...
union all
select ... from ...

☀ 一种典型的使用场景：

-- 查询每个学生的姓名、分数，在最后一行加上所有学生平均分
select sName, score
from StudentInfo
union all
select '平均成绩',AVG(score)		-- select还有直接填入数据的意味
from StudentInfo

另外，还可以利用union插入多条数据 ：

-- 用union一次插入多条数据
insert StudentInfo(stuName, cId)
select 'loli7', 2
union all
select 'loli8', 3
union all
select 'loli9', 3

 
 

█ 快速备份

① 向一个不存在的表快速备份数据

select 列名 into 备份表名 from 源表名

demo:
select * into test1 from userInfo

会新建表，表的结构和数据完全一致，但是不包含约束

如果test1已存在，会报错

如果想只有结构而不包含数据 ：

select top 0 * into test2 from userInfo

 

② 向已经存在的表快速备份数据

insert into test3
select * from userInfo
-- (✘)这会报错，因为identity也包含在"*"中，这会发生错误

insert into test3(UserName, UserAge, ...)
select (UserName, UserAge, ...) from userInfo
-- (✔) 这是正确的姿势：select出的是除了有identity标识的其他列，让该标识自动生成就好了

 
 

█ 内置函数
 

▶ 类型转换函数

select CAST(89.000000 as decimal(4, 1))						-- 得到 89.0
select CONVERT(decimal(4, 1), 89.000000)					-- 得到 89.0

-- CAST和CONVERT的区别一是参数格式不同，二是CONVERT还能设置格式

我们在做拼接时也会用到类型转换（）：

select 3 + '3'												-- 得到 6
select CAST(3 as char(1)) + '3'								-- 得到 33

 
▶ 字符串函数

ascii（求单个字符的ascii值），char（根据ascii转到字符）

left，right，substring（字符串截取；索引从1开始而不是0）

len（返回字符串长度）

upper，lower（转大、小写）

ltrim，rtrim（去左、右空格；不存在trim）

很简单，和其他脚本语言(Python)比较相似，举些例子一看便知 ：

select ASCII('a')									-- 97

select char(65)										-- A

select left('saikou', 2)							-- sa (截取左边的2个字符)

select substring('lolisuki', 1, 4)					-- loli (从1开始的4字符)(注意下标！和其他高级语言不太一样！)

select len('fft')									-- 3

select upper('aBc')									-- ABC								

select ltrim('   233')								-- 233

 
▶ 日期函数

getDate（获取当前日期）

dateAdd（加），dateDiff（差），datePart（取日期的部分）

print getdate()			-- 获取当前时间
print sysdatetime()		-- 时间精度很高

print dateadd(minute, 100, getdate())	-- 增加100分钟后的日期
print dateadd(day, 200, getdate())		-- 增加两百天后的日期

print datediff(year, '2000-06-21', getdate())	-- 计算我的年龄(取年份)
print datediff(second, '2000-06-21', getdate())	-- 计算我的年龄(秒数)

print datepart(year ,getdate())
print datepart(day, getdate())			-- 一个月的第几天
print datepart(dayofyear, getdate())	-- 一年中的第几天

print year(getdate())					-- 只有年月日有这种简写

print datename(year, getdate())			-- detepart返回的是数字，detename返回的是字符串

两个小demo：

-- demo1: 请统计出各个年龄的人的个数(只知道出生日期)
select datediff(year, birthday, getdate()), SUM(*)
from StudentInfo
group by datediff(year, birthday, getdate())

-- demo2：统计各个年份出生的人数
select datepart(year, birthday), SUM(*)
from StudentInfo
group by datepart(year, birthday)

year可简写成yy；其他可填项(month,hour…)及其简写现用现查帮助文档就可(F1)

F1查帮助文档很方便；但为了防止手边恰好没有电脑，我截了张图 ：

☀ 一个小练习：以“2022-6-21”的格式显示日期

select CAST(datepart(year, getdate()) as varchar) 
+ '-'
+ CAST(datepart(month, getdate()) as varchar)
+ '-'
+ CAST(datepart(day, getdate()) as varchar)

-- 其实考察的是拼接、类型转换、日期函数

其实不必如此麻烦。cast()和convert()有一个区别，还记得吗？convert还能设置格式

（在帮助文档可以查到每个格式对应的数字参数（yyyy-mm-dd hh:mi:ss对应120））

print convert(varchar(20), getdate(), 120)			-- 打印：2020-04-22 14:44:54

查询帮助文档，没有与本题题设恰好对应的格式，不过我们有巧妙的方法：

-- 直接截取前十个varchar
print convert(varchar(10), getdate(), 120)			-- 打印：2020-04-22

 

☀ 一个有点大的练习0

Q：查询班级名称，科目名称，平均分

分析：
   先想“ 需要用的表 ”和“ 关系 ”
         ↓
   (StudentInfo, SubjectInfo) -> ScoreInfo
         ↓
   还有 ClassInfo（作为StudentInfo的主键表）
         ↓
   根据外键关系全部内连接在一起
         ↓
   再找出分组依据：班级名称，科目名称

★ 其实这是个数据库经典模型：

Answer ：

select ClassInfo.className, SubjectInfo.subName, AVG(ScoreInfo.score) as SubAverage	-- 所查询的列可以最后写
from ScoreInfo																		-- 因为成绩表中做了两个外键，从成绩表开始内连接是最好的选择
inner join StudentInfo on StudentInfo.sId = ScoreInfo.sId							-- 根据学生编号连接成绩表和学生表
inner join SubjectInfo on SubjectInfo.subId = ScoreInfo.subId						-- 根据课程编号连接成绩表和科目表
inner join ClassInfo on StudentInfo.cId = ClassInfo.cId								-- 根据班级编号连接学生表和班级表
group by ClassInfo.className, SubjectInfo.subName									-- 只有分组依据和聚合式才能作为上面被查询的对象，因此这里我们就需要展示的列作为分组依据

 

 

 

 

 

 

 
 
 

 
 

  
> _ <