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

1、用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创建数据库

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创建数据表

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);   
--==================================================== 

4、用SQL创建登录账户

方法一：添加 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 语句 ------------ 

-- 以下示例将创建带密码的 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创建数据库用户

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;