SQL Server 数据库实现之T-SQL语句[备忘]

--  1、显示高级选项
sp_configure  ' show advanced options ' , 1 ;
reconfigure ;  -- 更改当前配置
go

--  2、启用 xp_cmdshell：
sp_configure  ' xp_cmdshell ' , 1 ;  --  1代表“用户ID”
reconfigure
go

--  附：用SQL创建文件夹(手动)：
        -- > SQL Server 外围应用配置器
        -- > 功能的外围应用配置器 
        -- > 启用xp_cmdshell

--  然后你就可以使用xp_cmdshell了。

--  在C盘建立myDatabase文件夹
xp_cmdshell  ' mkdir c:\myDatabase ' ; 
go   --  或 xp_cmdshell 'md c:\myDatabase' 

use  master
go   --  批处理的结束

-- 在[master]中查询[myDBName]是否存在
if   exists ( select   *   from  sysdatabases  where  name = ' myDBName ' )
    drop   database  myDBName

create   database  myDBName  -- 创建数据库
on   primary
(
  name = ' myDBName ' ,
  filename = ' C:\myDatabase\myDBName.mdf ' ,
  size = 3mb,
  maxsize = 100mb,  --  unlimited：无限制的
  filegrowth = 2mb 
)
,  --  注意：逗号不能丢！
(
  name = ' myDBName_1 ' ,
  filename = ' C:\myDatabase\myDBName_1.ndf ' ,
  size = 3mb,
  maxsize = unlimited,
  filegrowth =   10 %
)
log   on
(
  NAME  =  N ' myDBName_log ' , 
  FILENAME  =  N ' C:\myDatabase\myDBName_log.ldf '  , 
  SIZE  =  1024kb, 
  MAXSIZE  =  1gb, 
  FILEGROWTH  =  1mb
)

collate Chinese_PRC_CI_AS  -- 定义排序规则
go

--  删除数据库
--  drop database myDBName;

use  myDBName
go
-- -------------- 删除学生信息表 ----------------
if   exists ( select   *   from  sysobjects  where  name = ' stuInfo ' )
    drop   table  stuInfo;
-- -------------- 创建学生信息表 ----------------
create   table  stuInfo
(
   stuNo        char ( 6 )  primary   key ,
   stuName      varchar ( 20 )  not   null ,
   stuGender    bit   default ( 1 ),
   stuAge       int   check (stuAge  between   0   and   100 ),
   stuBirthday  datetime   null   default ( ' 2000-2-2 ' ),
   stuSeat      smallint   identity ( 1 , 1 ), -- 座位号：标识列
   stuAddress   text ,  -- 默认允许为空
   stuID       numeric( 18 , 0 )  unique   -- 身份证 
)

-- -------------- 添加约束 ----------------

--  主键约束：PK_字段名
alter   table  stuInfo
add   constraint  PK_stuNo  primary   key (stuNo);

--  唯一约束：UQ_字段名
alter   table  stuInfo
add   constraint  UQ_stuNo  unique (stuID);

--  默认值约束：DF_字段名
alter   table  stuInfo
add   constraint  DF_stuBirthday 
     default ( ' 2000-2-2 ' )  for  stuBirthday;

--  检查约束：CK_字段名
alter   table  stuInfo
add   constraint  CK_stuAge 
     check (stuAge  between   0   and   100 );

--  注意：标识列只能在建表的同时建立！
--  添加字段，同时设置标识列。
alter   table  stuInfo
add  stuSeat  int   identity ( 1 , 1 );

go

-- -------------- 删除学生成绩表 ----------------
if   exists ( select   *   from  sysobjects  where  name = ' stuMarks ' )
    drop   table  stuMarks;
-- -------------- 创建学生成绩表 ----------------
create   table  stuMarks
(
   ExamNo       int   not   null ,
   stuNo        char ( 6 ),
   writtenExam  float   null ,
   labExam      float   null ,
--  补充约束
constraint  PK_ExamNostuNo  primary   key (ExamNo,stuNo)
)

--  组合主键约束：PK_字段名
alter   table  stuMarks   -- ExamNo,stuNo必须为空
add   constraint  PK_ExamNostuNo1  primary   key (ExamNo,stuNo); 

--  添加外键约束：FK_字段名
alter   table  stuMarks  -- 子表上建立
add   constraint  FK_stuNo
     foreign   key (stuNo)  references  stuInfo(stuNo);

--  删除外键约束
alter   table  stuMarks
drop   constraint  FK_stuNo;


--  判断一个表的主键是否存在
select   count ( * )  from  sysobjects 
where  xtype = ' pk '   and
parent_obj  in ( select  id  from  sysobjects  where  name  = ' stuMarks ' )






-- =================== 附加知识点 ===================

-- 1.删除原主键
   --  方法一（删除原主键） 
   alter   table  uinfor    
   drop   constraint  PK_uinfor; 

   --  方法二（删除原主键） 
   if   exists ( select   *   from  sysobjects  where  xtype = ' PK '  
             and  parent_obj = object_id (N ' uinfor ' ) )  
   begin   
   -- 使用动态语句可以保证无论主键名是怎么定义都可以正常删除  
     declare   @s   nvarchar ( 4000 )  
      select   @s = N ' alter table uinfor drop constraint ' + quotename (name)  
             from  sysobjects 
             where  xtype = ' PK '   and  parent_obj = object_id (N ' uinfor ' )  
     exec ( @s )  
   end   

-- 2. 删除字段  
   -- if exists(select * from syscolumns 
   --           where id=object_id(N'uinfor') and name='u_id')  
      alter   table  uinfor 
      drop   column  u_id;

-- 3. 添加字段  
   alter   table  uinfor
   add  u_note  varchar ( 1 )  NULL ;  

-- 4. 修改字段  
   -- if exists(select * from syscolumns where id=object_id(N'uinfor')   and   name='u_key')  
      alter   table  uinfor 
      alter   column  u_key  varchar ( 18 )  NOT   NULL ;  

   -- 5. 添加新主键  
   -- if not exists(select * from sysobjects 
   --    where xtype='PK' and parent_obj=object_id(N'uinfor'))  
   alter   table  uinfor 
   add   primary   key (u_key,u_name);   
-- ==================================================== 

-- ============== 添加 Windows 身份验证 ==============

-- ---------- 方法一:使用 SQL 语句(建议使用) ------------

/*  
使用CREATE LOGIN为Windows用户[Windows域名\域账户] 
创建 SQL Server 登录名。
*/
create  login  [ NEWER-XG\Administrator ]   from  windows;
go           --  login_name 必须用方括号 ([ ]) 括起来。

--  删除 Windows 身份验证
drop  login  [ NEWER-XG\xugang ] ;


-- ---------- 方法二:使用系统存储过程(不推荐使用) ------------

/*  sp_grantlogin：内置的系统存储过程  */
exec  sp_grantlogin  ' Windows域名\域账户 '

exec  sp_grantlogin  ' NEWER-XG\xugang ' ;
/*  
“域”是针对于网络用户的相关设置，
    如果是本机，可用“计算机名”替换“Windows域名”，
                而“域账户” 等同于 “用户名”。
*/

--  删除 Windows 身份验证，请给出完整名称: <域\用户名>
exec  sp_revokelogin  ' NEWER-XG\xugang ' ; 

-- ============== 添加 SQL Server 身份验证 ==============

-- ---------- 方法一:使用 SQL 语句 ------------ 

--  以下示例将创建带密码的 xugang 登录名。
create  login  [ Andy ]   with  password  =   ' Andy_123456 '  
MUST_CHANGE; 
--  MUST_CHANGE 选项需要用户首次连接服务器时更改此密码。
go

--  删除 SQL Server 登录用户
drop  login  [ Andy ] ;


-- ---------- 方法二:使用系统存储过程(不推荐使用) ------------ 

-- sp_addlogin：创建新的 SQL Server 登录用户。格式如下：
exec  sp_addlogin  ' SQL登录名 ' , ' 密码 ' , ' 默认数据库 ' ;

   -- 示例一
   exec  sp_addlogin  ' Andy ' , ' Andy123456 ' , ' myDBName ' ;
   -- '默认数据库'为可选;

   -- 示例二
   exec  sp_addlogin  ' Andy ' , ' 1234 ' ; 
   -- 后续版本的 Microsoft SQL Server 将删除该功能。


--  删除 SQL Server 登录用户
exec  sp_droplogin  ' Andy ' ;
-- 后续版本的 Microsoft SQL Server 将删除该功能。

use  myDBName
go

-- ============ 创建'数据库用户' ============

--  方式一：SQL语句
create   user  xugang  for  login  [ NEWER-XG\xugang ] ;
go

create   user  aa  for  login  [ xugang ] ;
go

--  方式二：存储过程
exec  sp_grantDBaccess  ' 登录账户 ' , ' 数据库用户 ' ;
-- 后续版本的 Microsoft SQL Server 将删除该功能。

-- 示例一
exec  sp_grantDBaccess  ' xugang ' , ' xg ' ;

-- 示例二
exec  sp_grantDBaccess  ' xugang ' ;
-- 当省略‘数据库用户’时，即:'登录账户'与'数据库用户'相同。

/*  注意：
dbo用户是一个比较特殊的数据库用户，无法删除，且始终出现在数据库中。
一般来说，某个用户创建的数据库，一般情况下，拥有者即dbo用户。
*/

-- ============ 删除'数据库用户' ============

--  方式一：SQL语句
DROP   USER  xugang;

--  方式二：存储过程
exec  sp_revokedbaccess  ' xugang ' ;           -- SQL用户
exec  sp_revokedbaccess  ' NEWER-XG\xugang ' ;  -- Windows用户
-- 后续版本的 Microsoft SQL Server 将删除该功能。



/* ================================================
注意：创建用户后，一定要授予该用户相应的权限！
================================================ */


-- ============ 向'数据库用户'授权 ============

-- 注意：该权限一般由sa用户授予。
-- 常用的权限：insert、delete、update、select。
grant   insert , delete , update , select   on  表名  to  数据库用户;

-- 示例：  
use  myDBName
grant   insert , select   on  stuInfo  to  xg;  -- ‘xg’为用户名
-- grant all on stuInfo to xg;


-- 授予用户建表的权限
grant   create   table   to  xg;


-- ============ 移除'数据库用户'的权限 ============

-- 移除'数据库用户'的权限 (设置为null)
revoke   insert   on  stuInfo  from  xg;
-- revoke all on stuInfo from xg;

-- 移除'数据库用户'的权限 (设置为“×”打叉)
deny   insert   on  stuInfo  to  xg;
-- deny all on stuInfo to xg;

本文转自钢钢博客园博客，原文链接：http://www.cnblogs.com/xugang/archive/2010/01/22/1654343.html，如需转载请自行联系原作者