数据库 sql 语句 持续集成

1. sql server management 客户端，只能select * 查询，不能直接看数据

2. microsoft sql server management studio 客户端执行本句sql  ：  ctrl +E      

3.  等号和 like 用法

select * from currency_exchange where isDelete= 0 and CName like '%毛里求斯%';  --like 用于使用通配符的情况

select * from currency_exchange where isDelete= 0 and CName = '毛里求斯卢比';    --用于全等的情况

4.查看表结构

select * from information_schema.columns where table_name = '你要查的表名'   --sql server 就是去掉dbo 

5.内连接（即等值连接），外连接（即左连接）

  内连接外连接只有关键字不同，写sql 完全相同都是join on

  内连接关键字是join on，求的是两个集合的交集

  外连接关键字是left join on（等价于left outer join on），求的是本集合加上交集（A left B，本集合就是A，A right B，本集合就是B） 

 join on（这个是配套使用的sql 连接关键词），内外连接都用它

 （1）内外连接join on 中临时表的概念

  如果on 之后加上where 条件，则以下两个sql 结果不同(注意红框里面的数据)

  本sql 执行的慢，结果是对的，name like 毛里求斯 产生作用

SELECT top 1 poiId,A.name AS poiName,A.eName AS poiEName,A.countryId ,B.countryName ,B.countryEName ,B.CurrencyCode ,B.CurrencyName ,B.CurrencyAliasName ,B.IsSupport ,B.OrderByID ,B.DispSort ,A.lastModifyTime AS DataChange_LastTimeFROM tableA AS ALEFT OUTER JOINtableB AS BON (                              --注意ON 关键字可以用AND 连接多个条件A .countryId <> 0AND A. countryId = B .countryId   --left join 的结果中，如果一个列比如<span style="font-family: Arial, Helvetica, sans-serif;">IsDelete 只出现一次即只在一个表中存在这个名的列，那么可以不用A 这个别名去引用</span>AND B. IsDelete = 0               --如果是第六条的多重left join，A left join B left join C 那么其中的on 条件可以一直使用三表别名，不会提示不认识这个别名)where A. name like '%毛里求斯%'   --注意这里on 条件里有中间表问题，where 在on 之外就不会受中间表影响，结果一定是like ‘%毛里求斯%’ 的 

（2）本sql 执行的快，结果是错的，name like 毛里求斯 不产生作用

SELECT top 1 poiId,A.name AS poiName,A.eName AS poiEName,A.countryId ,B.countryName ,B.countryEName ,B.CurrencyCode ,B.CurrencyName ,B.CurrencyAliasName ,B.IsSupport ,B.OrderByID ,B.DispSort ,A.lastModifyTime AS DataChange_LastTimeFROM tableA AS ALEFT OUTER JOINtableB AS BON (A .countryId <> 0AND A. countryId = B .countryIdAND B. IsDelete = 0 AND A. name like '%毛里求斯%')

原因：join on 会产生临时表，第一种where 是在产生临时表之后，在临时表中搜索，所以结果正确。

   第二种join on 是产生临时表的条件，他不管on 中的条件是否为真，都会返回左边表中的记录。

参考：http://blog.csdn.net/limuzi13/article/details/39160637

6. 多重left join

SELECTTOP 10 hc.id, hce.commentidFROMhotelcommnt AS hc WITH (NOLOCK)LEFT OUTER JOIN hotelcoment_extrainfo AS hce WITH (NOLOCK) ON hc.id = hce.commentidLEFT OUTER JOIN Mbr_AvatarPictre AS avatar WITH (NOLOCK) ON hc.userid = avatar.uid;     --这里还可以用hc 这个别名去引用id

7. update 语句

UPDATE t_ptservice_info SET test_end_time='2015-10-07 19:22:34.0' WHERE task_id=14

8. 查询涉及两张表，这两张表在不同数据库中

   update DBNAME1.dbo.biaoName_1 SET modifytime='2000-01-01 00:00:00.0'
where Productid in 
(SELECT (ProductID - 1000000000) as id from DBNAME2.dbo.biaoName_2 where ProductID  > 1000000000)

9. top 子句用法

select top 10 * from Prd_Product   结果是10行

8.order by 列名, order by 是排序，按照某一列排序，asc / desc 是排序方式，下面语句也涵盖了where 和order by 同时使用的情况

select * from dbName.dbo.table_name where countryid = 1445order by name

select * from dbName.dbo.table_name where countryid = 1445order by name desc

10. distinct 列名

 select distinct 列名 from 表名

select count(distinct name) from archsearchdb.dbo.gs_poi where name like '%毛里求斯%' 

11.isnull(phone,'') 

sql server 用isnull ,   mysql 用ifnull

   如果phone 这一列有值为NULL 的，就在返回结果中显示空白，如图1

SELECT top 100 isnull(phone,'') FROM archsearchdb.dbo.gs_poi

图 1

12.ltrim(列名) 函数，把表中这一列中的值，左边的空格去掉，显示出来，如图2，类似的函数是rtrim 。

SELECT ltrim(service_name) from t_ptservice_info where task_id=311

图 2

13.sql 文件的注释是两个横线 --

14.convert(varchar(10),getdate()) 函数，转化类型，防止非varchar 类型不能字符串拼接，注意这里加号是字符串拼接

SELECT top 10 'poi_' + getdate(),name FROM poiTable    错误

Conversion failed when converting date and/or time from character string.

SELECT top 10 'poi_' +convert(varchar(10),getdate()) ,name FROM poiTable  正确 

或者convert(varchar(10),poi)   poi是bigint 类型

SELECT top 10 'poi_' + poiId ,name FROMpoiTable    错误

Error converting data type varchar to bigint.

SELECT top 10 'poi_' + convert(varchar(10),poiId) ,A.name FROMpoiTable    正确

15. sql server 当前时间，以及当前时间减去分钟写法，注意datachange_lasttime 是 datetime 型

select datachange_lasttime, BidRecommend,productid from [ArchSeDB].[dbo].[tableA] 
where datachange_lasttime >  DATEADD( minute,-5,GETDATE())

16. is not null 和 ！= null

要用is not null

17. distinct 和 top 遇到，要先写distinct 再写top

SELECT DISTINCT TOP 10 ...

18. CONVERT 函数

语法：CONVERT(data_type(length),data_to_be_converted,style)

例子：CONVERT (VARCHAR (23), b1.effectdate, 121)  

其中121代表yyyy-mm-dd hh:mi:ss.mmm(24h) 这个格式。参考http://www.w3school.com.cn/sql/func_convert.asp

结果：如图 3

图  3

19. replace 函数

   语法：用第三个表达式替换第一个字符串中出现的所有第二个给定的字符串。

   例子：replace(CONVERT (VARCHAR (23), effectdate, 121), ' ', '_')  空格替换成下划线

replace(wordtext, '|', '')     |北京|   被替换成  北京   

   结果：如图 4

        图  4

20. 大sql 断句方法，从from 后的括号开始，把括号中的拿出来

 

图 5

21. 嵌套sql 的执行先后问题

   子查询先执行，也就是最里面的，然后外层查询再执行

22. 别名作用域问题

   宗旨：外层的别名可以被里层引用，里层不能引用外层的别名，但是还要注意DB执行的顺序，先where 再select的顺序
   (1)select b.cc,b.dd from (select cc,dd from tableA) as b这个是可以的，

   select/from/as/where 这四个组合是在一层的。
   (2)select b.cc,b.dd as BiddiRankReco from (select ee,ff from tableA) as b where BiddiRankReco!= ''   不可以，
   (3)SELECT productid, BiddiRankReco from (select b.cc,b.dd as BiddiRankReco from (select ee,ff from tableA) as b) where BiddiRankReco!=''  可以
   解释：第二句和第三句相比，要第三句写法才可以,虽然select/from/as/where 这四个组合是在一层的，但是数据库引擎先执行where再执行select，所以要在外面再嵌套一层，DB才认识这个别名。第三句子查询先执行，所以DB 认识BiddiRankReco，之后外面的才执行。而第二句DB 先执行里面的，再执行外面的where，此时DB 不认识BiddiRankReco，所以报错。

23. 别名必须写问题

效果如图6, 

图  6

原因：其中红色字写的存疑的别名必须写，是因为brr 相当于命名空间，最外层的where BiddingRankRecomd !=' ' 是在这个命名空间下的变量，

等价于wherebrr. BiddingRankRecomd !=' '，如果不写as brr 这个别名来指定命名空间，那么sql 引擎执行完内层sql 时，执行完也就执行完了，

没有brr 这个名字，没法引用上次执行的临时表，所以执行到where BiddingRankRecomd !=' '  时，不知道BiddingRankRecomd  这个变量是哪个表中的列，故报错

24. stuff 函数

语法：STUFF ( character_expression , start , length ,character_expression )

语法解释：在指定的起点，删除指定长度的字符，并在指定的起点处插入指定的新字符。

例子：STUFF(cityname, 2, 3, '包')

原始的： 保定  

替换后： 保包

现象解释：从第二个字符开始，覆盖掉原来字符串的3个字符。

25.alter 表加列

alter table `ljltest` add `ExtendKey` varchar(500) null default null comment '匹配到资源名称的关键词' after `age`

这里面第一个null 是本列值，第二个null 是默认值，after 是在哪一列后面加，效果如图：

26.mysql 和sql server 的sql 语句不同点

  select *,age as fdfdsfds from ljltest;  --mysql 能用，sql server 能用  

  select age as fdfdsfds,* from ljltest;  --mysql 不能用，sql server 能用  

27.exists 关键字

--只出来typeid=12 这一条

select * from  hotel_words_type where typeids in (2016,12)   

--exists 子句只要是真的，就执行exists 前面的select 语句，这句出全表

select * from  hotel_words_type where exists (select typeids from hotel_words_type where typeids = 12) 

--exists 子句只要是假的，就不执行exists 前面的select 语句，这句一个结果都没有

select * from  hotel_words_type where exists (select typeids from hotel_words_type where typeids = 13) 

hotel_words_type 全表如下：

28.max min 关键字查某一列最大值最小值

select min(writingId),max(writingId) from comm_hotel_wr_cment where writingtype=1

29.将类型为bigint 的2003 和1 拼接成-20031，如下，注意必须转化成字符串才能拼接，bigint 类型可以存负数

-convert(bigint,(convert(varchar(10), 2003) + convert(varchar(10), cityid)))