SQL 2008技术内幕 T-SQl 编程 学习笔记1

第一章 T_SQl查询和编程基础

1 第一章大多数都是基础知识和概念的介绍,在这里就有不详细记录了,因为我也不是很清楚.我的学习过程是 先用起来,然后在去深入了解其概念和理解

2 IF OBJECT_ID('dbo.Employees','U') IS NOT NULL DROP TABLE Employees 这OBJECT_ID 函数是用来用来查看数据库中是否存在 若存在则返回其ID SELECT OBJECT_ID('dbo.Employees','U') 2105058535

3 编码风格要注意前后一致性 和可读性,建议采用大写,每一条语句结束后加一个分号来标记这句已经结束

第二章 单表查询

 

SELECT TOP 10 ID as serviceID FROM dbo.CollaborationService WHERE ID>1094 GROUP BY ID HAVING COUNT(ID)>0 ORDER BY ID; 表单查询顺序 不是按照先后顺序查询的 是 按 FROM ,GROUP BY,HAVING ,SELECT,ORDER BY ,所以在select中命名的别名是不能在order by 之前使用的 每一句结束后推荐使用;结尾 作为一个习惯,命名别名是不用要 列表 空格 别名 不规范, 在sql中 可以重复适应同一个表达式 而至执行一次,不消耗其他的效率和性能

2 WHERE 字句 WHERE字句后面是 布尔表达式 有可能是 true 或者flase 或者是 unnokwon ,不知道 ,where 后面的条件顺序很有可能影响性能,要悠闲吧限定条件放在前面

 

3 GROUP BY 字句 可以在聚合函数中使用DISTINCT COUNT( DISTINCT ID)

4 HAVING 字句 只能过滤group by 字句 且只能是true

5 ORDER BY 若在select中使用了 DISTINCT 则order by 后面的排序必须使用select 中的字段

6 OVER 聚合函数 能在返回基本列的同时 在同一行聚合 select top 10 ID,count(ID) over() as q from CollaborationService where id>1765 不用再使用group by 分组 在select 中 over dintsict top 执行顺序

7 CASE SELECT (CASE WHEN ID=1765 THEN '1' WHEN ID BETWEEN 1234 AND 1902 THEN '2' ELSE 0 END ) A FROM CollaborationService WHEN 很灵活

 

-- case when then else
--简单表达式
select
case IsDeleted
when 0 then '是'
when 1 then '否'
else ''
end as '删除'
from ServiceItem

--select * from ServiceItem
--搜索表达式
select
case
WHEN OnLinePrice <20 then 'less 20'
when OnLinePrice between 20 and 40 then 'between 20 and 40'
when OnLinePrice> 40 then 'more 40'
else '0'
end as '价格'
from ServiceItem
--where后面的的值有可能是三种情况 true false unknown 三种情况
--left 和 right 函数
--left (string str,n) 从左侧截取的字符串长度
select left ('abcdes',2)
--右边
select right ('abcdes',2)
--len 返回字符的长度(字符数不是字节数) unicode(nchar nvarchar) 站俩个字节 N'asc'代表unicode 类型 不包含尾随空格
--datalength 返回字节数 包含尾随空格
select len(N'asss') --返回 4
select datalength(N'asss') --返回8

--可以使用replace来计算某个字符出现的次数,替换成空"" 然后计算字符长度的减少
--escape转义字符函数
-- cast(标准) 和covert 转换函数

select cast ('2015-12-12' as datetime)
--返回系统中的数据
sp_help '表名'

 

--返回每个月的最后一天的数据 同理可以返回每个月的第一天 每周的第一天和每年的第一天 最后一天
select OrderID,CreationDate
from [order]
where CreationDate=dateadd(month,datediff (month,'2013-12-31',CreationDate),'2013-12-31')
--select
--datediff (month,'2013-12-31',CreationDate)
--from [order]
--datadiff (part ,date_1,date_2) 返回俩个日期之间指定的时间间隔
--dataadd (part ,n,date_1) 位日期指定的部分增加时间长度

select datediff (month,'2013-12-31',CreationDate) from [order]
--统计一个字符在字段中出现的次数
select *
from ServiceItem
where (len(ServiceItemName)-len(replace(ServiceItemName,'车','')))>1

select *
from ServiceItem
where ServiceItemName like'%车%车%'
--排序时 默认会吧null值放到前面去 可以吧他处理成为0或者1

select *
from ServiceItem
order by
case when isnull(ServiceItemDesc,'') ='' then 0 else 1 end desc

第三章 子查询

 

1  子查询和表连接 效率谁好不一定好说,要试试才知道呢

2 exists 返回行则是ｔｒｕｅ，是俩值逻辑不是三值逻辑　要么是ｔｒｕｅ　要么是ｆａｌｓｅ　，不存在　不知道是否返回行的情况

 

３　ｎｏｔ　in 是三值逻辑 not exists 是俩值逻辑自动的消除NULl 相比较更安全

 

4 with ties

--没有用with ties
select top 1 *
from Sales.Orders
order by empid desc

--值返回一条

11058 6 9 2008-04-29 00:00:00.000 2008-05-27 00:00:00.000 NULL 3 31.14 Ship to 6-A Forsterstr. 2345 Mannheim NULL 10300 Germany

 

--with ties
select top 1 with ties *
from Sales.Orders
order by empid desc

--返回多条数据

11058 6 9 2008-04-29 00:00:00.000 2008-05-27 00:00:00.000 NULL 3 31.14 Ship to 6-A Forsterstr. 2345 Mannheim NULL 10300 Germany
11022 34 9 2008-04-14 00:00:00.000 2008-05-12 00:00:00.000 2008-05-04 00:00:00.000 2 6.27 Destination SCQXA Rua do Paço, 7890 Rio de Janeiro RJ 10195 Brazil
11017 20 9 2008-04-13 00:00:00.000 2008-05-11 00:00:00.000 2008-04-20 00:00:00.000 2 754.26 Destination CUVPF Kirchgasse 1234 Graz NULL 10159 Austria
11016 4 9 2008-04-10 00:00:00.000 2008-05-08 00:00:00.000 2008-04-13 00:00:00.000 2 33.80 Ship to 4-A Brook Farm Stratford St. Mary 0123 Colchester Essex 10238 UK
10978 50 9 2008-03-26 00:00:00.000 2008-04-23 00:00:00.000 2008-04-23 00:00:00.000 2 32.82 Ship to 50-A Rue Joseph-Bens 3456 Bruxelles NULL 10241 Belgium
10970 8 9 2008-03-24 00:00:00.000 2008-04-07 00:00:00.000 2008-04-24 00:00:00.000 1 16.16 Ship to 8-C C/ Araquil, 1234 Madrid NULL 10360 Spain
10963 28 9 2008-03-19 00:00:00.000 2008-04-16 00:00:00.000 2008-03-26 00:00:00.000 3 2.70 Destination CIRQO Jardim das rosas n. 8901 Lisboa NULL 10176 Portugal
10953 4 9 2008-03-16 00:00:00.000 2008-03-30 00:00:00.000 2008-03-25 00:00:00.000 2 23.72 Ship to 4-B Brook Farm Stratford St. Mary 1234 Colchester Essex 10239 UK
10951 68 9 2008-03-16 00:00:00.000 2008-04-27 00:00:00.000 2008-04-07 00:00:00.000 2 30.85 Ship to 68-A Starenweg 6789 Genève NULL 10294 Switzerland
10942 66 9 2008-03-11 00:00:00.000 2008-04-08 00:00:00.000 2008-03-18 00:00:00.000 3 17.95 Ship to 66-C Strada Provinciale 2345 Reggio Emilia NULL 10290 Italy
10905 88 9 2008-02-24 00:00:00.000 2008-03-24 00:00:00.000 2008-03-06 00:00:00.000 2 13.72 Ship to 88-A Rua do Mercado, 4567 Resende SP 10353 Brazil
10893 39 9 2008-02-18 00:00:00.000 2008-03-18 00:00:00.000 2008-02-20 00:00:00.000 2 77.78 Destination RMBHM Maubelstr. 1234 Brandenburg NULL 10209 Germany
10889 65 9 2008-02-16 00:00:00.000 2008-03-16 00:00:00.000 2008-02-23 00:00:00.000 3 280.61 Ship to 65-C 9012 Milton Dr. Albuquerque NM 10287 USA
10871 9 9 2008-02-05 00:00:00.000 2008-03-05 00:00:00.000 2008-02-10 00:00:00.000 2 112.27 Ship to 9-B 9012, rue des Bouchers Marseille NULL 10368 France
10853 6 9 2008-01-27 00:00:00.000 2008-02-24 00:00:00.000 2008-02-03 00:00:00.000 2 53.83 Ship to 6-B Forsterstr. 3456 Mannheim NULL 10301 Germany
10849 39 9 2008-01-23 00:00:00.000 2008-02-20 00:00:00.000 2008-01-30 00:00:00.000 2 0.56 Destination DKMQA Maubelstr. 0123 Brandenburg NULL 10208 Germany
10837 5 9 2008-01-16 00:00:00.000 2008-02-13 00:00:00.000 2008-01-23 00:00:00.000 3 13.32 Ship to 5-A Berguvsvägen 9012 Luleå NULL 10267 Sweden
10829 38 9 2008-01-13 00:00:00.000 2008-02-10 00:00:00.000 2008-01-23 00:00:00.000 1 154.72 Destination QVTLW Garden House Crowther Way 7890 Cowes Isle of Wight 10205 UK
10828 64 9 2008-01-13 00:00:00.000 2008-01-27 00:00:00.000 2008-02-04 00:00:00.000 1 90.85 Ship to 64-B Av. del Libertador 5678 Buenos Aires NULL 10283 Argentina
10799 39 9 2007-12-26 00:00:00.000 2008-02-06 00:00:00.000 2008-01-05 00:00:00.000 3 30.76 Destination DKMQA Maubelstr. 0123 Brandenburg NULL 10208 Germany
10782 12 9 2007-12-17 00:00:00.000 2008-01-14 00:00:00.000 2007-12-22 00:00:00.000 3 1.10 Destination CJDJB Cerrito 8901 Buenos Aires NULL 10136 Argentina
10771 20 9 2007-12-10 00:00:00.000 2008-01-07 00:00:00.000 2008-01-02 00:00:00.000 2 11.19 Destination CUVPF Kirchgasse 1234 Graz NULL 10159 Austria
10750 87 9 2007-11-21 00:00:00.000 2007-12-19 00:00:00.000 2007-11-24 00:00:00.000 1 79.30 Ship to 87-C Torikatu 3456 Oulu NULL 10352 Finland
10745 63 9 2007-11-18 00:00:00.000 2007-12-16 00:00:00.000 2007-11-27 00:00:00.000 1 3.52 Ship to 63-C Taucherstraße 3456 Cunewalde NULL 10281 Germany
10736 37 9 2007-11-11 00:00:00.000 2007-12-09 00:00:00.000 2007-11-21 00:00:00.000 2 44.10 Destination DGKOU 6789 Johnstown Road Cork Co. Cork 10204 Ireland
10705 35 9 2007-10-15 00:00:00.000 2007-11-12 00:00:00.000 2007-11-18 00:00:00.000 2 3.52 Destination JYDLM Carrera1234 con Ave. Carlos Soublette #8-35 San Cristóbal Táchira 10199 Venezuela
10687 37 9 2007-09-30 00:00:00.000 2007-10-28 00:00:00.000 2007-10-30 00:00:00.000 2 296.43 Destination KPVYJ 5678 Johnstown Road Cork Co. Cork 10203 Ireland
10672 5 9 2007-09-17 00:00:00.000 2007-10-01 00:00:00.000 2007-09-26 00:00:00.000 2 95.75 Ship to 5-C Berguvsvägen 1234 Luleå NULL 10269 Sweden
10646 37 9 2007-08-27 00:00:00.000 2007-10-08 00:00:00.000 2007-09-03 00:00:00.000 3 142.33 Destination ATSOA 4567 Johnstown Road Cork Co. Cork 10202 Ireland
10586 66 9 2007-07-02 00:00:00.000 2007-07-30 00:00:00.000 2007-07-09 00:00:00.000 1 0.48 Ship to 66-B Strada Provinciale 1234 Reggio Emilia NULL 10289 Italy
10577 82 9 2007-06-23 00:00:00.000 2007-08-04 00:00:00.000 2007-06-30 00:00:00.000 2 25.41 Ship to 82-B 9012 DaVinci Blvd. Kirkland WA 10338 USA
10566 7 9 2007-06-12 00:00:00.000 2007-07-10 00:00:00.000 2007-06-18 00:00:00.000 1 88.40 Ship to 7-C 2345, place Kléber Strasbourg NULL 10331 France
10557 44 9 2007-06-03 00:00:00.000 2007-06-17 00:00:00.000 2007-06-06 00:00:00.000 2 96.72 Ship to 44-C Magazinweg 6789 Frankfurt a.M. NULL 10224 Germany
10538 11 9 2007-05-15 00:00:00.000 2007-06-12 00:00:00.000 2007-05-16 00:00:00.000 3 4.87 Destination DLEUN Fauntleroy Circus 4567 London NULL 10132 UK
10506 39 9 2007-04-15 00:00:00.000 2007-05-13 00:00:00.000 2007-05-02 00:00:00.000 2 21.19 Destination DKMQA Maubelstr. 0123 Brandenburg NULL 10208 Germany
10501 6 9 2007-04-09 00:00:00.000 2007-05-07 00:00:00.000 2007-04-16 00:00:00.000 3 8.85 Ship to 6-C Forsterstr. 4567 Mannheim NULL 10302 Germany
10475 76 9 2007-03-14 00:00:00.000 2007-04-11 00:00:00.000 2007-04-04 00:00:00.000 1 68.52 Ship to 76-C Boulevard Tirou, 0123 Charleroi NULL 10319 Belgium
10411 10 9 2007-01-10 00:00:00.000 2007-02-07 00:00:00.000 2007-01-21 00:00:00.000 3 23.65 Destination XJIBQ 1234 Tsawassen Blvd. Tsawassen BC 10129 Canada
10386 21 9 2006-12-18 00:00:00.000 2007-01-01 00:00:00.000 2006-12-25 00:00:00.000 3 13.99 Destination RNSMS Rua Orós, 2345 Sao Paulo SP 10160 Brazil
10331 9 9 2006-10-16 00:00:00.000 2006-11-27 00:00:00.000 2006-10-21 00:00:00.000 1 10.19 Ship to 9-C 0123, rue des Bouchers Marseille NULL 10369 France
10324 71 9 2006-10-08 00:00:00.000 2006-11-05 00:00:00.000 2006-10-10 00:00:00.000 1 214.27 Ship to 71-C 9012 Suffolk Ln. Boise ID 10307 USA
10263 20 9 2006-07-23 00:00:00.000 2006-08-20 00:00:00.000 2006-07-31 00:00:00.000 3 146.06 Destination FFXKT Kirchgasse 0123 Graz NULL 10158 Austria
10255 68 9 2006-07-12 00:00:00.000 2006-08-09 00:00:00.000 2006-07-15 00:00:00.000 3 148.33 Ship to 68-A Starenweg 6789 Genève NULL 10294 Switzerland

 

 

sql  都是一步步查询的,分开查询 要有思维 不是一部到位的,还是要注意思维过程 和逻辑 不雅想问题的时候 一片空白

 

 

第10章动态SQL

 

1 临时表 表变量 

 什么时候需要临时表  

  1 循环过程中需要保存临时结果数据,2 需要多次访问某个开销很贵的处理结果

 select  into 像临时表中插入数据

 

2 sp_executesql (接受unicode字符)动态执行sql   他的参数化有助于重用缓存过得执行计划.执行计划是sql  为查询生成的物理处理计划 包含了一组指令,要访问那些对象,按什么顺序,使用哪个索引,如何访问他 , 要重新使用缓存计划 就是查询字符串必须和缓存中已存在的查询计划字符串一样,即使 字符串值发生了变化,但是查询字符串仍然相同

存储过程 :是预编译的语句,只需要创键一次,以后可以重复使用.1 执行效率高,预编译的,是参数化的,2 防止sql注入,3减少网络传输流量,4模块化设计 可以重复使用