【澳门金沙vip】再学TSQL基础–单表查询

1.返回 每月最后一天订单

本内容是我学习tsql2008的阅读笔记

一、MySQL查询今天、昨天、上周、近30天、去年等的数据的方法:

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate);

什么是关系模型?

 

 使用EMONTH 对输入的日期返回月末日期   类似动态条件

若对列创建唯一约束,背后中的物理机制也是创建了一个唯一索引.

 

DATEDIFF(month, ‘19991231’, orderdate)  相差多少月 
从19991231到 orderdate之间先查多少月

SQL语句的逻辑解析顺序是FROM

删除建立时间超过3天的订单记录

 

delete 订单表 where datediff( dd, order_addtime, getdate() ) > 3
用函数datediff() datediff( dd, 时间1, 时间2
),意思是:计算时间1到时间2之间的天数 所以,datediff( dd,
order_addtime, getdate() ) > 3,就是超过3天的

今天  

select * from 表名 where to_days(时间字段名) = to_days(now());  
昨天  
SELECT * FROM 表名 WHERETO_DAYS(NOW( ) ) – TO_DAYS(
时间字段名) <= 1  
7天  
SELECT * FROM 表名 whereDATE_SUB(CURDATE(), INTERVAL 7 DAY)
<=date(时间字段名)  
近30天  
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY)
<=date(时间字段名)  
本月  
SELECT * FROM 表名 WHEREDATE_FORMAT( 时间字段名, ‘%Y%m’ )
=DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )  
上一月  
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ )
, date_format( 时间字段名, ‘%Y%m’ ) ) =1  
#查询本季度数据  
select * from `ht_invoice_information`
whereQUARTER(create_date)=QUARTER(now());  
#查询上季度数据  
select * from `ht_invoice_information` where
QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  
#查询本年数据  
select * from `ht_invoice_information` where
YEAR(create_date)=YEAR(NOW());  
#查询上年数据  
select * from `ht_invoice_information` where
year(create_date)=year(date_sub(now(),interval 1 year));  
查询当前这周的数据   
SELECT name,submittime FROM enterprise WHERE
YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());  
查询上周的数据  
SELECT name,submittime FROM enterprise
WHEREYEARWEEK(date_format(submittime,’%Y-%m-%d’))
=YEARWEEK(now())-1;  
查询当前月份的数据  
select name,submittime from enterprise   where
date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)  
查询距离当前现在6个月的数据  
select name,submittime from enterprise where submittime between
date_sub(now(),interval 6 month) and now();  
查询上个月的数据  
select name,submittime from enterprise   where
date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(),
INTERVAL 1 MONTH),’%Y-%m’)  
select * from ` user ` where DATE_FORMAT(pudate, ‘ %Y%m ‘ ) =
DATE_FORMAT(CURDATE(), ‘ %Y%m ‘ ) ;  
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’))
= WEEKOFYEAR(now())  
select *   
from user
where MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())  
select *   
from [ user ]   
where YEAR (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = YEAR (now())  
and MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())  
select *   
from [ user ]   
where pudate between 上月最后一天  
and 下月第一天  
where date(regdate)   =   curdate();  
select   *   from   test   where year(regdate)=year(now())   and
month(regdate)=month(now())   and day(regdate)=day(now())  
SELECT date( c_instime ) ,curdate( )  
FROM `t_score`  
WHERE 1  
LIMIT 0 , 30 

二、相关函数简介

 

DATEADD(month, DATEDIFF(month, ‘19991231’, orderdate), ‘19991231’)
中间是加上的月数  最后一个从什么时间开始加

WHERE

0、Sql server中DateDiff()用法



 

DATEDIFF 函数 [日期和时间]

功能 
返回两个日期之间的间隔。

语法 
DATEDIFF ( date-part, date-expression-1, date-expression-2 )

date-part :
year | quarter | month | week | day | hour | minute | second |
millisecond

参数 
date-part    指定要测量其间隔的日期部分。

有关日期部分的详细信息,请参见日期部分。

date-expression-1    某一间隔的起始日期。从 date-expression-2
中减去该值,返回两个参数之间 date-parts 的天数。

date-expression-2    某一间隔的结束日期。从该值中减去
Date-expression-1,返回两个参数之间 date-parts 的天数。

用法 
此函数计算两个指定日期之间日期部分的数目。结果为日期部分中等于(date2 –
date1)的有符号的整数值。

当结果不是日期部分的偶数倍时,DATEDIFF 将被截断而不是被舍入。

当使用 day 作为日期部分时,DATEDIFF
返回两个指定的时间之间(包括第二个日期但不包括第一个日期)的午夜数。

当使用 month 作为日期部分时,DATEDIFF
返回两个日期之间(包括第二个日期但不包括第一个日期)出现的月的第一天的数目。

当使用 week 作为日期部分时,DATEDIFF
返回两个日期(包括第二个日期但不包括第一个日期)之间星期日的数目。

对于更小的时间单位存在溢出值:

milliseconds    24 天

seconds    68 年

minutes    4083 年

others    没有溢出限制

如果超出这些限制,此函数将返回溢出错误。

标准和兼容性 
SQL/92    Transact-SQL 扩展。

SQL/99    Transact-SQL 扩展。

Sybase    与 Adaptive Server Enterprise 兼容。

下面示例的语句返回 1:

SELECT datediff( hour, ‘4:00AM’, ‘5:50AM’ )下面的语句返回 102:

SELECT datediff( month, ‘1987/05/02’, ‘1995/11/15’ )下面的语句返回 0:

SELECT datediff( day, ’00:00′, ’23:59′ )下面的语句返回 4:

SELECT datediff( day,
   ‘1999/07/19 00:00’,
   ‘1999/07/23 23:59’ )下面的语句返回 0:

SELECT datediff( month, ‘1999/07/19’, ‘1999/07/23’ )下面的语句返回 1:

SELECT datediff( month, ‘1999/07/19’, ‘1999/08/23’ )

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');

GROUP BY

两种写法  记住 可以用动态条件 区分记录

HAVING

 

SELECT

  1. 姓氏a上有相同的 

    SELECT empid, firstname, lastname
    FROM HR.Employees
    WHERE lastname LIKE ‘%a%a%’;

OVER

3.对每个客户的订单日期排序

DISTINCT

SELECT custid, orderdate, orderid,
  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid, rownum;

TOP

4.条件筛选case

ORDER BY

SELECT empid, firstname, lastname, titleofcourtesy,
  CASE titleofcourtesy
    WHEN 'Ms.'  THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Mr.'  THEN 'Male'
    ELSE             'Unknown'
  END AS gender
FROM HR.Employees;

SELECT empid, firstname, lastname, titleofcourtesy,
CASE 
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;

 如果SELECT语句中涉及到分组,则后面的select order
having的操作对象将是分组。

第一种 是指向搜索  固定的指向  第二种没有可以多字段条件

 所有聚合函数都会忽略NULL值,只有count(*)除外。

5.在order by 加限制排序条件

带有order by子句的查询被ANSI称之为游标。表表达式不能处理游标。

SELECT custid, region
FROM Sales.Customers
ORDER BY
  CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;

TOP 是TSQL所特有的,用来限制返回的行数或百分比。

两个条件 0,1 默认排序规则 通过int 对相应的条件进行排序

开窗函数使用OVER子句提供窗口作为上下文,对窗口中的一组值进行操作,而不是使用GROUP
BY 子句提供的上下文。这样可以同时返回基础行的列和聚合列。

ROW_NUMBER()
函数用于为查询的结果集中的各行分配一个递增的序列号。其逻辑顺序通过OVER子句中的ORDER
BY语句来进行指定。但即使ORDER
By的列不唯一,则序列值也会增加,这样会造成返回的值不确定。

RANK 和 DENSE_RANK为具有相同逻辑排序值的所有行生成相同的排名。

RANK表示前面有多少行更低的排序值,而DENSE
RANK表示前面有多少个不同的排序值。

 

可以认为在同一SELECT子句中不同时指定DISTINCT和ROW NUMBER是一条最佳实践,

因为DISTINCT在这种情况下不起任何作用。

 

SELECT empid,firstname,lastname

FROM HR.Employees

WHERE lastname like N’D%’;

N表示National,用于表示字符串是Unicode数据类型(NCHAR或NVARCHAR).

 

SQL中的三值逻辑,如果想返回region列不等于WA的所有行,如下:

SELECT custid,country,region,city

FROM Sales.Customers

WHERE region<>N’WA’

OR region IS NULL;