SqlServer学习笔记【暂】

  Sql学习笔记，暂时先保存在着，等不忙了再整理成章节，如果其中有问题的，还请各位大神不吝赐教！
　

--------------------------------------所有的数据基于Northwind库---------------------------

--Order by 支持多个排序
select * from dbo.Employees order by EmployeeID asc,TitleOfCourtesy desc,BirthDate asc;

--Title中包含es的所有数据
select * from dbo.Employees where Title like '%es%';

--Title中倒数第二个字符为v，最后一个字符任意
select * from dbo.Employees where Title like '%v_';

--集合匹配只在 MSSQLServer 上提供支持，在 MYSQL、Oracle、DB2 等数据库中不支持， 必须采用变通的手段来实现。 
--查询LastName中以d或者s开头的数据
select * from dbo.Employees where LastName like '[ds]%';

--在其他的数据库中我们可以变通的设置下[这个就是通用的sql了]
select * from dbo.Employees where LastName  like 'd%' or LastName like 's%'

--否定符^来对集合取反，它匹配不与字符集中任意一个字符相匹配的字符
--查询LastName中所有不以d或者s开头的数据
select * from dbo.Employees where LastName like '[^ds]%';

--变为通用的sql
select * from dbo.Employees where not(LastName like 'd%') And not(LastName like 's%');

-- 通配符过滤一个非常强大的功能，不 过 在使 用通配符过滤进行检索的时候，数据库系统 会对全表进行扫描，所以执行速度非常慢。
--因此不要过分使用通配符过滤，在使用其他方式 可以实现的效果的时候就应该避免使用通配符过滤


--空值检测 
select * from dbo.Employees where Region is null;

--检测Region不为空
select * from dbo.Employees where Region is not null;

-- 反义运算符 
-- “=”、“<”、“>”等运算符都是用来进行数值判断的，有的时候则会想使用这些运算符 的反义
--查询Extension不大于2300的数据;
select * from dbo.Employees where Extension !>2300;

--通用Sql的反义运算符 不等于<> 不大于<=  不小于>=
select * from dbo.Employees where Extension <=2300;

--或者使用not
select * from dbo.Employees where not (Extension=2300) and (Extension<2300);


--多值检测 
--查询员工序列号为1,3,7的员工信息
select * from dbo.Employees where EmployeeID =1 or EmployeeID=3 or EmployeeID=7;
--简写
select * from dbo.Employees where EmployeeID in(1,3,7);

-- 范围值检测 
--检索EmployeeID在3到8之间的数据
select * from dbo.Employees where EmployeeID>=3 and EmployeeID <=8;
--简写
select * from dbo.Employees where EmployeeID between 3 and 8;

--数据分组
--需要注意的是GROUP BY子句的位置，GROUP BY子句必须放到SELECT语句的之后，
--如果 SELECT语句有WHERE子句，则GROUP BY子句必须放到WHERE语句的之后。

select EmployeeID from dbo.Orders group by EmployeeID;

--分组函数和聚合函数联合使用
select EmployeeID,COUNT(*) as a from dbo.Orders group by EmployeeID;

--首先根据EmployeeID分组，再根据OrderID分组
select EmployeeID,OrderID from dbo.Orders group by EmployeeID,OrderID;

--分组函数，聚合函数以及排序功能混合使用
select EmployeeID,ShipVia,COUNT(*) OrderCount from dbo.Orders group by EmployeeID,ShipVia order by ShipVia,EmployeeID;


--HAVING条件过滤
--根据EmployeeID分组，查询分组总数大于100的ID
select EmployeeID,COUNT(*) as a from dbo.Orders group by EmployeeID having  COUNT(*)>100;

--having过滤条件和in联合使用
select EmployeeID,COUNT(*) as a from dbo.Orders group by EmployeeID having COUNT(*) in(123,96,72)

--HAVING语句能够使用的语法和WHERE几乎是一样的，不过使用WHERE的时候,GROUP BY 子句要位于WHERE子句之后，
--而使用HAVING子句的时候，GROUP BY子句要位于HAVING子句之前


--ROW_NUMBER OVER(排序规则) 
select ROW_NUMBER() over(order by orderid) id, OrderID,EmployeeID,ShipVia from Orders;


--查询第3行到第10行的数据[子查询]
--方法一：
select * from(
select ROW_NUMBER() over (order by orderId) id,OrderID,EmployeeID,ShipVia from Orders
)T where t.id between 3 and 10;
--方法二：
select top 8 * from dbo.Orders where OrderID not in 
(select top 2 OrderID from Orders);


--DISTINCT去重复数据
select distinct EmployeeID from Orders;

--常量字段 也称为‘常量值’ 并没有什么实际的意义
select '产品订单' as 'name',* from dbo.Orders ;

--字段间计算 [可用于select,insert,update,where等条件中]
select OrderID,(ShipVia*Freight) as Price  from dbo.Orders;

--数据处理函数 

---主流数据库系统都提供了计算字符串长度的函数，在MYSQL、Oracle、DB2中这个函数 名称为LENGTH,
-- 而在MSSQLServer中这个函数的名称则为LEN
select ProductID,ProductName,LEN(ProductName) as ProductNameLen from Products;

--主流系统都提供了取得字符串的子串的函数，在MYSQL、MSSQLServer中这个函数名称 为SUBSTRING，
--而在Oracle、DB2这个函数名称为SUBSTR。
--这个函数接受三个参数，第一个 参数为要取的主字符串，第二个参数为字串的起始位置（从1开始计数），第三个参数为字串 的长度
--注意在数据库中Start的index索引是从1开始的
select ProductID,SUBSTRING(ProductName,1,3)as 'SubProductName' from dbo.Products;

--主流系统都提供了计算正弦函数值的函数SIN和计算绝对值的函数ABS，它们都接受一个数值类型的参数
select ProductID,SIN(UnitPrice) SInPrice from dbo.Products;
select ProductID,UnitPrice,ABS(UnitPrice) AbsPrice,SIN(UnitPrice) as SInPrice from dbo.Products

--拼接字符串
select '产品ID为：'+CONVERT(varchar,ProductID)+'的产品名称为：'+ProductName as Description from dbo.Products

--不从实体表中取的数据  有的时候我们需要查询一些不能从任何实体表中能够取得的数据，
--比如将数字1作为结果集或者计算字符串“abc”的长度
select distinct 1 as '1' from Products;
--MYSQL和MSSQLServer允许使用不带FROM子句的SELECT语句来查询这些不属于任何实 体表的数据，比如下面的SQL将1作为结果集
select 1 as '1';
select LEN('abc');
SELECT 1,2,3,'a','b','c';

--求打印出打印5以内自然数以及它们的平方数
SELECT  1 as '序号',1 * 1 as '平方数'  
    UNION  SELECT 2,2 * 2  
    UNION  SELECT 3,3 * 3  
    UNION  SELECT 4,4 * 4  
    UNION  SELECT 5,5 * 5 ;

-- 分别列出所有的正式员工和所有的临时工的姓名
select '正式员工'
UNION ALL  
select LastName from dbo.emp
union all 
select '临时工'
union all
select LastName from dbo.Employees;

CREATE TABLE T_Person (FIdNumber VARCHAR(20), FName VARCHAR(20),FBirthDay DATETIME, FRegDay DATETIME,FWeight NUMERIC(10,2)) 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789120','Tom','1981-03-22','1998-05-01',56.67); 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789121','Jim','1987-01-18','1999-08-21',36.17); 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789122','Lily','1987-11-08','2001-09-18',40.33); 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789123','Kelly','1982-07-12','2000-03-01',46.23); 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789124','Sam','1983-02-16','1998-05-01',48.68); 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789125','Kerry','1984-08-07','1999-03-01',66.67); 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789126','Smith','1980-01-09','2002-09-23',51.28); 
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)  VALUES ('123456789127','BillGates','1972-07-18','1995-06-19',60.32); 

select * from T_Person


--一般用 Date 来表示日期类型
--一般用 Time 来表示时间类型
-- 在数据库中， 一般用 DateTime 来表示日期时间类型
--一般用 TimeStamp 来表示日期时间类型

--获取当前时间
select GETDATE() as '当前日期时间';

--当前日期
select CONVERT(varchar(50),GETDATE(),101) as '当前日期';

--当前时间
select CONVERT(varchar(50),GETDATE(),108) as '当前时间';


--取值                 别名                说明 
--year                yy,yyyy             年份 
--quarter             qq,q                季度 
--month               mm,m                月份 
--dayofyear           dy,y                当年度的第几天 
--day                 dd,d                日 
--week                wk,ww               当年度的第几周 
--weekday             dw,w                星期几 
--hour                hh                  小时 
--minute              mi,n                分 
--second              ss,s                秒 
--millisecond         ms                  毫秒 

--在当前日期上加两天
select DATEADD(DAY,2,GETDATE());
select CONVERT(varchar(50),(select DATEADD(DAY,2,GETDATE())),101);

--当前时间的下一个星期
select DATEADD(week,1,getdate());

--当前时间的上一个月
select DATEADD(MONTH,-1,getdate());

--DATEDIFF()函数用于计算两个日期之间的差额

--计算月份差
select DATEDIFF(month,getdate(),(select dateadd(month,2,getdate()))) as '计算月份差';

--DATENAME()函数，这个函数可以返回一个日期的特定部分，并且 尽量用名称来表述这个特定部分，
--其参数格式如下： DATENAME(datepart,date)  其中参数date为待计算日期，
--date 参数也可以是日期格式的字符串；参数datepart指定 要返回的日期部分的参数

--今天是星期几
select DATENAME(dw,getdate());

--当前年份
select DATENAME(YY,GETDATE()) as '当前年份';

--获取当年度的第几天 
select '今天是'+DATENAME(YYYY,GETDATE())+'年第: '+ DATENAME(DAYOFYEAR,GETDATE())+'天' as '当年度的第几天 ';

select  GETDATE()  as '当前日期',
        DATENAME(YY,(select getdate())) as '年',
        DATENAME(MONTH,(select getdate())) as '月',
        DATENAME(DAY,(select GETDATE())) as '日',
        DATENAME(HH,(select GETDATE())) as '小时',
        DATENAME(MM,(select getdate())) as '分钟',
        DATENAME(S,(select GETDATE())) as '秒',
        DATENAME(MS,(select GETDATE())) as '毫秒';
        
        
--DATEPART()函数和DATENAME()函数完全一样，不过其实它们并不是只是名称不同的别名函数，
--虽然都是用来提取日期的特定部分的,不过DATEPART()函数的返回值是数字而DATENAME()函数则会将尽可能的以名称的方式做为返回值

select DATEPART(YY,(select getdate()));

--类型转换 类型转换
SELECT  CAST('-30' AS INTEGER) as i,  
        CONVERT(DECIMAL,'3.1415726') as d, 
        CONVERT(DATETIME,'2008-08-08 08:09:10') as dt;
        
select    RIGHT('123456789120',3) as '后三位',
        CAST(RIGHT('123456789120',3) as integer) as '后三位转化为整数类型',
        CAST(RIGHT('123456789120',3) as integer)+1 as '后三位加1',
        CONVERT(integer,RIGHT('123456789120',3))/2 as '后三位除以2';
        
--空值验证
--COALESCE的简写版本ISNULL
select ShipRegion,ISNULL(ShipRegion,'为空') from Orders;
    
--case when 函数
--第一种用法
select OrderID,
(case ShipVia
    when 1 then 'good'
    when 2 then 'ok'
    when 3 then 'no'
    else 'Error'
    end
) as orderinfo
 from Orders;
 --第二种用法
 select OrderID,
 (
 case when ShipPostalCode<'100 ' then 'no'
 when ShipPostalCode <'1000'  then 'number' 
 else 'ok'
 end)as ShipPostInfo from Orders;