SQLServer查询操作

select distinct Name from dbo.Student;--distinct去掉重复的名字


select * from dbo.Student where Name LIKE '王[0-9]%';
select * from dbo.Student where Name Like '%[%]%';
select * from dbo.Student where Name Like '%[_]%';
select * from dbo.Student where Name Like '%['']%';
select * from dbo.Student where Name Like '%^%';


select NULL +'a';--null表示不知道
select * from dbo.Student where Age <> NULL;--什么也查不出来
select * from dbo.Student where Age is NULL;--Age为空的都查出来
select * from dbo.Student where Age is not null;--Age不为空的全部查出来


select Name,ISNULL([Age],26) from dbo.Student;--snull可以判断后面是否为null，为null就用第二个参数代替


--select Name,ISNULL([Age],'') from dbo.Student;
--数据排序
select * from dbo.Student where Age>20 order by Id desc;
select * from dbo.Student where Age >20 order by Name desc,Gender desc;


select * from dbo.Teacher order by Salary desc;




--分组
select Address,COUNT(Id) from dbo.Student Group by Address;


select Gender,COUNT(Id) from dbo.Student group by Gender;


select Gender,COUNT(Id),MAX(Age),MIN(Age),SUM(Age) from dbo.Student group by Gender;


select * from dbo.Student ORDER BY Gender


--Having
select Age,COUNT(Id) from dbo.Student where Age>20 group by Age having Age>25;
select Age,COUNT(Id) from dbo.Student where Age>20 group by Age having COUNT(Id)>1;


--类型转换CAST、CONVERT
select Name,ISNULL(cast(Age as nvarchar(10)),'保密') from dbo.Student
select CAST(Age as nvarchar(10)) from dbo.Student;
select CAST('123' as int);
select CONVERT(int,'a');
--CAST(Age as nvarchar(10))=='转换过后' 
--select ISNULL(转换过后,'保密') from dbo.Student;
select CAST(NULL as nvarchar(10));
select '你的班级编号是'+1;
select '123'+1;
select '123'+CAST(1 as nvarchar(10));


--日期转换
select GETDATE();
select CONVERT(varchar(20),GETDATE(),104);


--联合查询
select Name,Age from dbo.Student where Age<27
union --两个集合必须具有相同的列数,列具有相同的数据类型(至少能隐式转换的),最终输出的集合的列名由第一个集合的列名来确定,默认会去除重复行
select Name,Age from dbo.Student where Age>30;


select 1,2,3
union all --不会去除重复行
select '1','2','3'


--查询每位老师的信息,包括姓名、工资、并且在最后一行加上平均工资和最高工资
select Name,Salary from dbo.Teacher
union all
select CAST(AVG(Salary) as nvarchar(10)),MAX(Salary) from dbo.Teacher;


--一次插入多条
insert into dbo.Student (Name, Gender, Address, Phone, Age, Birthday, CardId, CId) select Name, Gender, Address, Phone, Age, Birthday, CardId, CId from dbo.Student;
--insert into Score (Sid,English,Math)
--select 1,80,100 union
--select 2,80,100 union 
--select 3,90,80 union all
--select 5,50,78 union 
--select 4,40,70 
--只复制表的结构
--select * into student2 from dbo.Student where 1<>1
--复制表结构和表数据
--select * into student2 from dbo.Student




--字符串函数
select LEN('今天天气不错，北京居然没有雾霾')--计算字符串的字符个数
select DATALENGTH('今天天气不错，北京居然没有雾霾q')
--一个汉字两个字节数
--一个字母一个字节
--一个中文标点符号两个字节
--一个英文标点符号一个字节
--一个数字一个字节


--大小写转换
select LOWER('AAA')--转换成小写
select UPPER('aaaa')--转换成大写


--去除空格
select LTRIM('    aaa      ')
select LTRIM('a   a')--去除左空格
select RTRIM('   aaa   ')
select DATALENGTH(RTRIM('   aaa   '))--去除右空格


--截取字符
select LEFT('今天天气不错，北京居然没有雾霾',5)--从左向右截取5个字符
select RIGHT('今天天气不错，北京居然没有雾霾',5)--从右向左截取5个字符
--从中间开始截取，从第几个开始截取，从第5个开始，截取5个




select SUBSTRING('今天天气不错，北京居然没有雾霾',5,5)--从第二个参数的下标开始截取几个字符


--取当前日期
select GETDATE()
select DATEADD(YEAR,2,GETDATE())
select DATEADD(MONTH,2,GETDATE())
select DATEADD(DAY,2,GETDATE())
select DATEADD(HOUR,2,GETDATE())--在第三个参数的实践基础上,加上第一个参数指定的部分,加的量是第二个参数


select DATEDIFF(MINUTE,GETDATE(),DATEADD(YEAR,2,GETDATE()))--取得两个时间之间的差
select DATEPART(YEAR,GETDATE())
select DATEPART(MONTH,GETDATE())
select DATEPART(DAY,GETDATE())
select DATEPART(HOUR,GETDATE())--取得一个日期的特定部分,如:年,月等
select YEAR(GETDATE())--取到日期的年份
select MONTH(GETDATE())--取到日期的月份
select DAY(GETDATE())--取到日期的天


select DAY('2010-02-12')


--CREATE TABLE [CallRecords]
--(
-- [Id] [int] NOT NULL identity(1,1),
-- [CallerNumber] [nvarchar](50), --三位数字，呼叫中心员工编号（工号）
-- [TelNum] [varchar](50),
-- [StartDateTime] [datetime] NULL,
-- [EndDateTime] [datetime] NULL  --结束时间要大于开始时间,默认当前时间
--)


--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
--INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));


--输出所有数据中通话时间最长的5条记录
select top 5 *,DATEDIFF(SECOND,StartDateTime,EndDateTime) from dbo.CallRecords 
order by DATEDIFF(SECOND,StartDateTime,EndDateTime) desc
--输出所有数据中拨打长途号码(对方号码以0开头)的总时长
select sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) from dbo.CallRecords where TelNum like '0%'
--输出7月份通话总时长 最多的前三个 呼叫员的 编号
select CallerNumber,SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) '总时长' from dbo.CallRecords 
where MONTH(StartDateTime)=7 group by CallerNumber order by SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) desc


select top 3 CallerNumber,DATEDIFF(SECOND,StartDateTime,EndDateTime) from dbo.CallRecords 
where MONTH(StartDateTime)=7 order by DATEDIFF(SECOND,StartDateTime,EndDateTime) desc