类型转换函数cast/convert、联合结果集union()、复制表【不得不说，今天老师又调了一节课的bug~！哎身在这个班的人都懂得】

最新推荐文章于 2021-04-10 16:53:56 发布

zhanglan478950

最新推荐文章于 2021-04-10 16:53:56 发布

阅读量893

点赞数

本文链接：https://blog.csdn.net/zhanglan478950/article/details/8115224

版权

--类型转换函数cast/convert
1.默认的字符类型转换
select 100+'100'------------200
2.将数值转换为字符串
select
'hello'+
cast(100 as varchar(10))--------------hello100

select
convert(varchar(10),100)
+'hello'-----------------100hello
3.把日期转换成月日年形式
select
convert(varchar(50),getdate())------10/26/2012

select
convert(varchar(50),getdate(),111)-------2012/10/26
4.将varchar转换成int
select *from TestConvert
order by myId desc ------------对于字符串的排序先排第一位，第一位相同的再比较第

二位

select *from TestConvert
order by cast(myId as int) desc

select *from TestConvert
order by convert(int,myId) desc
------------------------------------------------------------------------
联合结果集union()之前我们查询的都是一个结果集，联合就是把多个结果集连起来

select *from MyStudents
select *from heSoftCSDN..Student

select FName,FAge
from MyStudents where FAge=21
union all
select sName,sAge
from heSoftCSDN..Student
注意：1.数据类型，对应的列类型必须一致
2.字段个数必须一致
3.只写union会去重复，union all不会去重复，因为一般情况下不会有重复的情况，

所以我们都习惯写union all来联合
--查询出每个销售员得销售总金额，以及总得销售金额（底部汇总）
select *from MyOrders

select
销售员=salesAssistant,
销售金额=sum(saveNumber*savePrice)
from MyOrders
group by salesAssistant
union all
select
'总的销售金额'，
销售金额=sum(saveNumber*savePrice)
from MyOrders

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select 'english最高成绩',max(english) from score
union all
select 'english最低成绩',min(english) from score
union all
select 'english平均',avg(english) from score
---------------------------------------------
--查询结果为3行一列
select
'最高分' as 类型，
max(FEnglish) as 分数
from Mystudent
union all
select
'最低分'，
min(FEnglish)
from Mystudent
union all
select
'平均分'，
avg(FEnglish)
from Mystudent

查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资
select tName,tSalary from teacher
union all
select '平均工资',avg(tSalary) from teacher
union all
select '最高工资',avg(tSalary) from teacher
----------------------------------------------
select *from TblTeacher
select
姓名=tTName,工资=tTSalary
from TblTeacher
union all
select '平均工资',
avg(tTSalary) from TblTeacher
union all
select '最高工资',
avg(tTSalary) from TblTeacher

一次插入多条数据
insert into Score(studentId,english,math)
select 1,80,100 union
select 2,60,80 union
select 3,50,59 union
select 4,66,89 union
select 5,59,100
-------------------------------------------------------------------------------
--复制表（只要表结构，不要数据）
select *from TblTeacher

1.方法一：
select * --------2
into NewTeachers---------3
from TblTeacher---------1

select *from NewTeachers---------全部复制
------------------------------
select *
into NewTeachers
from TblTeacher
where 1<>1
select *from NewTeachers----------只复制表结构

select len('哈哈hello') --返回字符个数
select Datalength('哈哈hello') --返回字节的个数
select lower(AbaB) --都小写
select upper(AbaB) --都大写
select '==='+rtrim(ltrim(' a '))+'====' --删除空格
select substring('hello world',7,5) --取字符串从7个开始取，取5个

作业：

--类型转换
1 把日期转换成月日年的字符串的格式输出

select
convert(varchar(50),getdate())
2 查询出每个销售员的销售总金额，以及总的销售金额（底

部汇总）

select *from MyOrders

、平均成绩

（1）查询结果为1行

select
英语成绩最高=MAX(FEnglish),
英语成绩最低=MIN(FEnglish),--列名
平均成绩=AVG(FEnglish)
from Mystudents
（2）查询结果为3行

select
'最高分' as 类型，
max(FEnglish) as 分数
from Mystudent
union all
select
'最低分'，
min(FEnglish)
from Mystudent
union all
select
'平均分'，
avg(FEnglish)
from Mystudent
4 复制表(只要表结构,不要数据)

（1）方法1

select *
into NewTeachers
from TblTeacher

select *from NewTeachers
（2）方法2

select *
into NewTeachers
from TblTeacher
where 1<>1
select *from NewTeachers
5 把一个表的数据copy到另一个已经用上面的方式生成的

表中。

set identity_insert New3Teacher on
insert into New3Teacher values('张三',1,25,20000,'2012-10-26')
select* from TblTeacher
set identity_insert New3Teacher off

6 创建一张表，记录电话呼叫员的工作流水，记录呼叫员编

号、对方号码、通话开始时间、通话结束时间。建表、插数

据等最后都自己写SQL语句。

create table CallRecords
(
Id int not null identity(1,1),
CallerNumber nvarchar(50),
TelNum varchar(50),
StartDateTime datetime null,
EndDateTime datetime null
)

insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
insert CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
要求：
（1）输出所有数据中通话时间最长的5条记录。orderby

datediff

select
top 5
datediff(second,StartDateTime,EndDateTime),Id,
CallerNumber, TelNum, StartDateTime, EndDateTime
from CallRecords
order by datediff(second,StartDateTime,EndDateTime) desc

（2）输出所有数据中拨打长途号码（对方号码以0开头

）的总时长。like、sum

select
sum(datediff(second,StartDateTime,EndDateTime))
from CallRecords
where TelNum like '0%'
（3）输出本月通话总时长最多的前三个呼叫员的编号。

select top 3
CallerNumber,sum(datediff(ss,StartDateTime,EndDateTime))
from CallRecords
where datediff(month,StartDateTime,'2010-07-1') = 0
group by CallerNumber
order by sum(datediff(ss,StartDateTime,EndDateTime)) desc

（4）输出本月拨打电话次数最多的前三个呼叫员的编号

.group by,count(*)按照月份分组。

select top 3
CallerNumber,count(*)
from CallRecords
where datediff(month,EndDateTime,'2010-07-1') = 0
group by CallerNumber
order by count(*) desc
select *from CallRecords