sql 基礎

去除資料庫登錄界面的所有用戶資訊

C:\Users\asus\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\SqlStudio.bin

查詢姓名中第二個字與第三個字相同:

select * from Student s2 where SUBSTRING(s2.sname,2,1)=SUBSTRING(s2.sname,3,1)

用戶授權

1 創建用戶

create login names with password='pwd' , default_database=test;
create user names for login names with default_schema=dbo;

2 增刪改查授權

grant select,insert,UPDATE,DELETE on 表 to names

3 創建表

grant create table to names 
GRANT ALTER ON SCHEMA::dbo TO names;

4 存盤程序授權

GRANT EXECUTE ON 存盤程序名 TO username

5 禁止對表授權

DENY UPDATE ON 表 TO username CASCADE;

6 回收權限

REVOKE DELETE ON 表 FROM username

7 洗掉表

truncate table a
delete from dbo.a
drop table a

8 修改列

alter table a add sa nvarchar(10) not null
Alter Table a Add Constraint PK_Course_Cno Primary Key(id)
ALTER TABLE a ALTER COLUMN id int
ALTER TABLE a DROP CONSTRAINT PK_a
alter table a drop column sa

9 創建一個簡單的登錄，登錄名為：newlogin；登錄密碼：123456；默認資料庫：master，默認資料庫也可以不指定，

EXEC sp_addlogin 'newlogin','123456','master'

10 創建用戶

• 創建一個簡單的用戶，如果不指定用戶名，則添加到當前資料庫登錄名中，如果不指定角色，則該用戶默認屬于public角色，下為添加newlogin登錄名，

EXEC sp_adduser 'newlogin'

• 創建一個帶用戶名的用戶，用戶可以與登錄名相同（同上一種類似），也可以不同，但要設定當前登錄名，用戶角色可選，默認為public，下為將用戶newuser添加到newlogin登錄名中，

EXEC sp_adduser 'newlogin','newuser'

• 創建角色

EXEC sp_addrole 'newrole'

• 下為將用戶下為將用戶newuser添加到newlogin登錄名中，并指定newrole角色，

EXEC sp_adduser 'newlogin','newuser','newrole'

• 為角色newrole賦予jobs表的所有權限

GRANT ALL ON jobs TO newrole

• 為角色newrole賦予sales表的查、改權限

GRANT SELECT,UPDATE ON sales TO newrole

• 禁止角色newrole使用employees表的插入權限

DENY INSERT ON employees TO newrole

• 另一種創建用戶和賦予角色的方式
• 為登錄newlogin在資料庫中添加安全賬戶newuser

EXEC sp_grantdbaccess 'newlogin','newuser'

• 添加newuser為角色newrole的成員

EXEC sp_addrolemember 'newrole','newuser'

• 資料庫用戶、角色、登錄的洗掉操作
• 洗掉當前資料庫用戶

EXEC sp_revokedbaccess 'newuser';

• 洗掉資料庫登錄

EXEC sp_droplogin 'newlogin'

• 洗掉資料庫角色

EXEC sp_droprole 'newrole'

• 從資料庫角色(newrole)中洗掉用戶(newuser)

EXEC sp_droprolemember 'newrole', 'newuser'

• 用SQL代碼新建登錄、用戶.創建帶密碼的mylogin登錄名，MUST_CHANGE 選項需要用戶首次連接服務器時更改此密碼，

CREATE LOGIN mylogin WITH PASSWORD = '123456' MUST_CHANGE;

• 創建映射到憑據的登錄名，以下示例將創建mylogin登錄名，此登錄名將映射到mycredential憑據，

CREATE LOGIN mylogin WITH PASSWORD = '123456',
CREDENTIAL = mycredential;

• 從Windows 域帳戶創建登錄名.如果從Windows 域帳戶映射登錄名，則登錄名必須用方括號([ ]) 括起來，

CREATE LOGIN [jack\xiangzhao] FROM WINDOWS;

• 如果指定用戶名，則不使用默認登錄名作為該資料庫用戶

CREATE USER myuser FOR LOGIN mylogin

• 以下示例將創建用戶myuser擁有的資料庫角色myrole

CREATE ROLE myrole AUTHORIZATION myuser;

• 以下示例將創建db_role固定資料庫角色擁有的資料庫角色myrole

CREATE ROLE myrole AUTHORIZATION db_role

規則

1 創建雇傭日期規則 hire_date_rule

CREATE RULE hire_date_rule
AS @hire_date>='1980-01-01' and @hire_date<=getdate()

2 創建性別規則sex_rule

CREATE RULE sex_rule
AS @sex in ('男','女')

3 -創建評分規則grade_rule

CREATE RULE grade_rule
AS @value between 1 and 100

4 創建字符規則my_character_rule

Create rule my_character_rule
As @value like '[a-z]%[0-9]' 

5 sp_helptext 查看規則,查看規則hire_date_rule的文本資訊

EXECUTE sp_helptext hire_date_rule

6 sp_bindrule系結規則

• 將規則hire_date_rule系結到employee表的hire_date列上

EXEC sp_bindrule hire_date_rule, 'employee.hire_date'

• 定義用戶定義資料型別pat_char，將規則my_character_rule系結到pat_var上

EXEC sp_addtype pat_char,'varchar(10)','NOT NULL'
EXEC sp_bindrule my_character_rule, pat_char, 'futureonly';

7 sp_unbindrule 解除規則的系結

• 解除系結在employee表的hire_date列和用戶定義資料型別pat_char上的規則

EXEC sp_unbindrule 'employee.hire_date';
* DROP RULE陳述句洗掉當前資料庫中的一個或多個規則
DROP RULE sex_rule,hire_date_rule 

分頁

select * from (
select * ,ROW_NUMBER() over(order by sid) as rowcindex --增加索引
from Student) as t
where t.rowcindex between 1 and 3;

分裂與合并

select s.sid,s.sname,
max(case c.cname when '語文' then sc.score end) as '語文',
min(case c.cname when '數學' then sc.score end) as '數學',
sum(case c.cname when '英語' then sc.score end) as '英語',
max(case c.cname when '化學' then sc.score else 0 end) as '化學'
from (Student as s inner join SC as sc on s.sid=sc.sid
inner join Course as c on c.cid=sc.cid)
group by s.sid,s.sname;

T-sql

1 全域變數

select @@VERSION --資料庫版本
insert into Course values('',''); select @@IDENTITY --獲取最近insert陳述句的標識
print @@servername --服務器名稱
print @@rowcount--回傳受影響的行數

2 選擇陳述句

declare @id int
set @id =10
if @id>5
begin
    print 'ok'
end
else
begin
    print 'no'
end

3 回圈陳述句

declare @id int
set @id=1
while @id<10
begin 
	print @id
	set @id=@id+1
end

4 例外

begin try
	delete from SC--不能成功
end try
begin catch
	print @@error --判斷錯誤資訊 0對
end catch

5 事務:回滾函式

begin try
	begin transaction --開啟事務
	--錯誤SQL
	commit tran --提交事務
end try
begin catch
	rollback tran --回退事務
end catch

6 鎖:同步

begin tran --鎖上
	 --修改操作
rollback tran --開鎖 commit tran

標籤：SQL Server

上一篇：Sql server中用現有表中的資料創建Sql的Insert插入陳述句

下一篇：SQL Server解惑——為什么你的查詢結果超出了查詢時間范圍