SQL Server 数据库实现之T-SQL语句[备忘]-阿里云开发者社区

开发者社区> 老朱教授> 正文

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

简介:
+关注继续查看

 

1、用SQL创建文件夹

复制代码
ExpandedBlockStart.gif用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创建数据库

复制代码
ExpandedBlockStart.gif用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创建数据表

复制代码
ExpandedBlockStart.gif用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 身份验证

复制代码
ExpandedBlockStart.gif添加 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 身份验证

复制代码
ExpandedBlockStart.gif添加 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创建数据库用户

复制代码
ExpandedBlockStart.gif用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;



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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server 系统数据库恢复
原文:SQL Server 系统数据库恢复 标签:SQL SERVER/MSSQL SERVER/数据库/DBA/故障恢复/master 概述      SQL Server 维护一组系统级数据库(称为“系统数据库”),这些数据库对于服务器实例的运行至关重要。
906 0
将DataTable 数据插入 SQL SERVER 数据库
原文: 将DataTable 数据插入 SQL SERVER 数据库 以下提供3中方式将DataTable中的数据插入到SQL SERVER 数据库: 一:使用sqlcommand.
867 0
Cloud Toolkit 数据库 SQL 执行器
Cloud Toolkit是一个IDE 插件,帮助开发者更高效地开发、测试、诊断并部署应用。 使用本插件,开发者能够方便地将本地应用一键部署到任意机器,或 ECS、EDAS、Kubernetes; 并支持高效执行终端命令和 SQL 等。
4086 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
4502 0
阿里云服务器远程登录用户名和密码的查询方法
阿里云服务器远程连接登录用户名和密码在哪查看?阿里云服务器默认密码是什么?云服务器系统不同默认用户名不同
444 0
tempdb对SQL Server数据库性能有何影响
1.SQL Server系统数据库介绍   SQL Server有四个重要的系统级数据库:master,model,msdb,tempdb.   master:记录SQL Server系统的所有系统级信息,包括实例范围的元数据,端点,链接服务器和系统配置设置,还记录其他数据库是否存在以及这些数据问文件的位置等等.如果master不可用,数据库将不能启动.   model:用在SQL Server 实例上创建的所有数据库的模板。
737 0
+关注
3546
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载