XuGang

记录一个程序员的成长

 

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

 

1、用SQL创建文件夹

用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' 


 

 2、用SQL创建数据库

用SQL创建数据库
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;

 

 

3、用SQL创建数据表

用SQL创建数据表
use myDBName
go
---------------- 删除学生信息表 ----------------
if exists(select * from sysobjects where name='stuInfo')
   
drop table stuInfo;
---------------- 创建学生信息表 ----------------
create table stuInfo
(
   stuNo       char(6primary key,
   stuName     varchar(20not 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,0unique --身份证 
)

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

-- 主键约束: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(1NULL;  

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


 

4、用SQL创建登录账户

方法一:添加 Windows 身份验证

添加 Windows 身份验证
--============== 添加 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 Server 身份验证
--============== 添加 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 将删除该功能。

     
重要事项:  
      使用“系统存储过程”创建和删除的方式,后续版本的 Microsoft SQL Server 将删除该功能。
      参考:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/eb737149-7c92-4552-946b-91085d8b1b01.htm

安全性注意:

      请尽可能使用 Windows 身份验证。

 

5、用SQL创建数据库用户

用SQL创建数据库用户
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;

 

posted on 2010-01-22 17:25  钢钢  阅读(2169)  评论(2编辑  收藏  举报

导航