Sql练级

                             Sql练级

1. Sql Server分页：

Sqlserver:sqlserver 分页必定用到top 来限制条数 ！加上row_number() over(order by id) 函数（2005版本以上）

在使用 row_number() over()函数时候，row_number()计算行数，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。

row_number() over(order by id)为分组排序  以id为组进行排序

列：select top pageSize * from (select row_number() over(ORDER BY H_ID desc) 'rownumber',* from QueueHist) as rn where  rn.rownumber>((pageIndex-1)*pageSize);

 

2.MySql分页：

MySql：采用的是limit 来限制pageSiz：数量 pageNumbe：几列

列：select * from studnet limit (pageNumber-1)*pageSize,pageSize

 

3.sql server日期函数操作：

GETDATE()	返回当前日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间

date为有效时间

格式：    DATEPART(datepart,date) 返回日期的某部分datepart为yy返回 年的部分

DATEADD(datepart,number,date) 返回添加或减去的日期datepart为day 返回天变化后的时间 number整数为未来时间 负数为过去时间

DATEDIFF(datepart,startdate,enddate) 返回两个日期之间的时间差datepart为返回的日期格式 yy为返回的年差 hh 返回时间的小时差

CONVERT(varchar(100), GETDATE(), number) 日期格式

Varchar为返回的长度 number 返回的日期格式

 

列：select getdate()    2019-06-03 15:46:17.547(当前时间)

select datepart(yy,getdate())  2019 (返回时间某部分 年 月 日 等)

select dateadd(day,-2,getdate()); 2019-06-01 (返回某一段时间的间隔 整数为未来时间 负数为过去时间 day:为操作的数据；yy为年上做操作 hh 为小时上做操作)发

SELECT DATEDIFF(hh,'2008-12-29','2008-12-30') 24 (返回两段时间差 hh返回两段时间的小时差)

Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16(返回时间的日期格式)

Select CONVERT(varchar(100), GETDATE(), 112): 20060516

select convert(int,'2') 2 字符串转换成数字

4.mysql日期函数操作

NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

格式：

SELECT NOW(),CURDATE(),CURTIME()

DATE(date)

EXTRACT(unit FROM date) unit为返回格式date为日期参数unit：DAY，HOUR

DATE_ADD(date,INTERVAL expr type) day为时间参数expr：正数，为未来时间负type为返回格式 type为day,hour

DATE_SUB(date,INTERVAL expr type) day为时间参数expr：负数，以前时间type为返回格式 type为day,hour

DATEDIFF(date1,date2)

DATE_FORMAT(date,format) format为返回的日期格式'%m-%d-%Y'

为12-29-2008

 

 

列：

select now(),curdate(),curtime  2008-12-12 12:23:23,2008-12-12,12:23:23

date(now())  2008-12-12 12:23:23 标准日期格式

extract(day from now())   2008-12-12 12:23:23 返回某部分的日期格式

date_add(now(),interval 2 day) 2008-12-14 12:23:23 未来时间

date_sub(now(),interval 2 day) 2008-12-10 12:23:23  以前时间

datediff(‘2018-04-02’,’2018-04-01’) 1     两时间相差日期

date_format(now(),’%m%d%y’) 12-12-2008 日期返回格式

 

5.sql server空值判断函数

ISNULL(EXPR,EXPR1) 空值判断 当EXPR不为空值时返回该值 为空时返回EXPR1

列：ISNULL(1,0)  1   ISNULL(null,0)  0

6. sql server 递归查询

   With  xx()  as  (union all) 组成

列：with temp (id,parentid,NAME)

as (

select id,parentid,NAME from CONTENTTYPE where id='057a2db7-4346-4c91-8027-f1277fdda621'

union all

select con.id,con.parentid,con.NAME from CONTENTTYPE con inner join temp on temp.id=con.PARENTID

)

select * from temp

temp为递归查询后的虚拟表

select id,parentid,NAME from CONTENTTYPE where id='057a2db7-4346-4c91-8027-f1277fdda621' 为递归的基础数据

union all 为递归联合

select con.id,con.parentid,con.NAME from CONTENTTYPE con inner join temp on temp.id=con.PARENTID 判断满足递归的条件数据

select con.id,con.parentid,con.NAME from CONTENTTYPE为表的全部数据 循环是否 有满足temp.id=con.PARENTID的数据 直到没有满足的数据位置

循序为temp.id=con.PARENTID 满足那么查询出来的数据 temp。Id，temp.parenid 等于con.id con.parentid 然后在继续递归 直到没有数据时！

其中select * from temp 可以和其他操作一起

如：update CONTENTTYPE set STATUTSSN=0 where id in(select id from temp)

 

7. 聚合函数

COUNT（统计函数）：COUNT函数可以用来返回所有选中行的总行数，它有一个参数，可以是 '*'（即所有列）、'1'（效果同前者类似）和具体的列名。

列：select count(*) from table 总条数 Select count (id) from table总条数

    Select count(name) from table group by name 分组显示条数

SUM（求和函数）：SUM函数用于求和，计算指定列中所有不为空（null）的总和。返回值类型为： int

列：

select sum(xx) from CONTENTTYPE  xx为某列属性 类型int double float都可

select sum(CONVERT(float,isnull(longitude,0))) from CONTENTTYPE where longitude!='null'  将字符串转换成float类型进行计算

isnull 把null进行转换CONVERT 把字符串转换成float

AVG（求平均值函数）：AVG函数用于求平均值，计算指定列中所有不为空（null）的平均值。返回值类型为： int

列：

select avg(xx) from CONTENTTYPE   xx类型int double float都可

select avg (CONVERT(float,isnull(longitude,0))) from CONTENTTYPE where longitude!='null'  将字符串转换成float类型进行计算

isnull 把null进行转换CONVERT 把字符串转换成float

MAX（最大值函数）：MAX函数用于计算最大值，可以使用于numeric、char、varchar、money、smallmoney、或datetime列，但不能用于bit列。返回值类型为： int

select max(xx) from CONTENTTYPE

select max (CONVERT(float,isnull(longitude,0))) from CONTENTTYPE where longitude!='null'  将字符串转换成float类型进行计算

isnull 把null进行转换CONVERT 把字符串转换成float

MIN（最小值函数）：MIN函数用于计算最小值，可以使用于numeric、char、varchar、money、smallmoney、或datetime列，但不能用于bit列。返回值类型为： int

select min(xx) from CONTENTTYPE

select min (CONVERT(float,isnull(longitude,0))) from CONTENTTYPE where longitude!='null'  将字符串转换成float类型进行计算

isnull 把null进行转换CONVERT 把字符串转换成float

HAVING：HAVING子句仅用于带有 GROUP BY 子句的查询语句中，WHERE子句用于每一行数据的筛选（在变成一个组的某一部分之前），而HAVING子句用于分组的聚合值的筛选

8. case when

  Case后跟某字段 when 对字段值进行比对给与值展示 end为结束语

列：

select id,case longitude when '0' then '有效'

                    when '1' then '无效'

                                                                ELSE '略' end

                                                                from CONTENTTYPE

longitude为CONTENTTYPE某字段 case可以和其他函数连用

如：

select id,case CONVERT(float,isnull(longitude,0))

when 0 then '有效'

                    when 1'then '无效'

                                                                ELSE '略' end

                                                                from CONTENTTYPE where longitude!='null'

把字符串转换成int类型 这里转换的是float

   

9.sql server 执行顺序及优先值

1. 针对mysql，其条件执行顺序是 从左往右，自上而下

2. 针对orcale，其条件执行顺序是从右往左，自下而上

3. 针对sqlserver，其条件执行顺序是 从左往右，自上而下

Mysql,sqlserver 会建议将选择性高（过滤数据多）的条件放到WHERE条件的前面，这是为了让查询优化器优先考虑这些条件，减少生成最优(或相对最优)的执行计划的时间，但最终的执行计划生成过滤顺序还是决定这些条件的选择性与判断bool值的容易程度

你比他们 更了解你的数据！！！

10.sql server执行语句顺序

 

 FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1

ON：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。

OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。

WHERE：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.

GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.

CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.

HAVING：对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.

SELECT：处理SELECT列表，产生VT8.

DISTINCT：将重复的行从VT8中移除，产生VT9.

ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).

TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

注：步骤10，按ORDER BY子句中的列列表排序上步返回的行，返回游标VC10.这一步是第一步也是唯一一步可以使用SELECT列表中的列别名的步骤。这一步不同于其它步骤的 是，它不返回有效的表，而是返回一个游标。SQL是基于集合理论的。集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序无关紧要。对表进行排序 的查询可以返回一个对象，包含按特定物理顺序组织的行。ANSI把这种对象称为游标。理解这一步是正确理解SQL的基础。

因为这一步不返回表（而是返回游标），使用了ORDER BY子句的查询不能用作表表达式。表表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。它的结果必须返回给期望得到物理记录的客户端应用程序。

select * 
from(select orderid,customerid from orders order by orderid) 
as d

在SQL中，表表达式中不允许使用带有ORDER BY子句的查询，而在T—SQL中却有一个例外（应用TOP选项）。

      所以要记住，不要为表中的行假设任何特定的顺序。换句话说，除非你确定要有序行，否则不要指定ORDER BY 子句。排序是需要成本的，SQL Server需要执行有序索引扫描或使用排序运行符。

总结：减少sql的运行时间 主要还是减少虚拟表的量 量分两种 1种是虚拟表的量2种是虚拟表中需要比对数据的量；

比如执行一条sql语句 不管是mysql或则sql server都是按一定的执行顺序来执行这条sql语句 比如sqlserver来说。它执行条复杂的联合查询 它都是先执行sql 语句的from段语句， 对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表 在对生成的虚拟表执行ON语句进行条件筛选 在生成另一个虚拟表 然后在执行后面的sql顺序语句生成对应的虚拟表 最终生成自己想要的虚拟表 怎么优化sql语句说百了就是怎么缩短这个过程 缩短这个过程主要还是减少虚拟表的生成数量和减少虚拟表中sql条件比对的数据量。怎么来减少 主要还是看实际的情况 有可能跟表结构相关 有可能跟条件相关 或则其他的！如果跟表结构相关，那么就会影响生成虚拟表量，如果是条件相关，就会影响虚拟表中数据的比对效率！因为表的结构越复杂，查询涉及到的表越多，生成的虚拟表就越多！如果针对虚拟表中数据的比对效率来优化，就要优化sql语句中的条件语句段 那段是跟where后面执行语句相关 还是跟连接查询对应字段比对相关 需不需要进行索引！主要还是看从哪方面入手！