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