MySQL (C API)VC執行個體及代碼下載 （1）(5)

11.4. 通過SQL語句來更改使用者的密碼

修改別人的,需要sysadmin role

EXEC sp_password NULL, 'newpassword', 'User'

如果帳號為SA執行EXEC sp_password NULL, 'newpassword', sa

11.5. 怎麼判斷出一個表的哪些欄位不允許為空白？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename

11.6. 如何在資料庫裡找到含有相同欄位的表？

a. 查已知列名的情況

SELECT b.name as TableName,a.name as columnname

From syscolumns a INNER JOIN sysobjects b

ON a.id=b.id

AND b.type='U'

AND a.name='你的欄位名字'

b. 未知列名查所有在不同表出現過的列名

Select o.name As tablename,s1.name As columnname

From syscolumns s1, sysobjects o

Where s1.id = o.id

And o.type = 'U'

And Exists (

Select 1 From syscolumns s2

Where s1.name = s2.name

And s1.id <> s2.id

)

11.7. 查詢第xxx行資料

假設id是主鍵：

select *

from (select top xxx * from yourtable) aa

where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

如果使用遊標也是可以的

fetch absolute [number] from [cursor_name]