一些数据库用法

1. select * from tblActivityBookmarkFlag A, tblFlags as B where (A.ActBookMarkID = @Id) and (A.FlagId = B.ID) 调用B表和A表有相同FlagId的项。

 

2. sql database create new user: first create user under security/login, second add user into a database/security/user

 

3. use of JOIN sql

http://www.cnblogs.com/pcjim/articles/799302.html
http://hi.baidu.com/yinfuqing666/blog/item/7a85423eb90bfecb7d1e71d3.html

 

4. use of LOOP sql

http://www.cnblogs.com/wildfish/archive/2008/01/09/1031943.html

 

5. add a not null column in a table which already has data saved

alter 

table tblActivitiesadd ActivityStatusTypeIDintnotnulldefault-1

or

UPDATE TABLE table_name SET null_column-name='value' WHERE column_name='value

EX
UPDATE TABLE employee SET add1='SARA' WHERE sno=1

 

6. add a foreign key to a table, Based on my expereince, when the table(tand_tbl) in theforeignkey relation has some some data that does not have a match in the primarykey colum of the table (perple_tbl), the issue will happens. we could verify that by comparing the data(parent_id) of the 2 tables.

7. log sql server server.

1) ->server properties/security/server authentication/sql server and windows authentication mode

2) sql server configuration manager/sql server services/sql server ->restart

 

特殊情况：删除具体数据库中的user，在security设置User Mapping到具体的数据库。

 

8. 查找表TB第31-40条数据， 主键id为int型自增长（sql数据库）

Select top 10 from TB  where id not in （Select top 30 id from TB）
SELECT*FROM (SELECT ROW_NUMBER()OVER(ORDERBY IDASC)AS ROWNUMBER,*FROM TB) AWHERE ROWNUMBERBETWEEN31and40

 

9. remote connect SQL engine,  start 'Sql Server Configuration Manager' -> 'SQL server network confiuration'-> TCP/IP enabled-> Named Pipes enabled