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(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 身份验证 --============== 添加 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;