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

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

 

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;



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

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
25天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
6天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
12天前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
26天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤,您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。
|
27天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第7天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统要求检查与准备、配置安装源、安装 SQL Server 2019、配置 SQL Server 以及数据库初始化(可选)。通过这些步骤,你可以成功安装并初步配置 SQL Server 2019,进行简单的数据库操作。
|
1月前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
19 2
|
SQL 数据库
如何快速备份还原Sql Server 数据库
备份数据库 选择你要备份的数据库,鼠标右键单击,选择任务-备份   弹出备份数据库窗口,选择添加    弹出选择备份目标窗口,点击浏览,选择存放备份数据库的目录,输入文件名,后缀名输入.bak,点击确定,确定,备份完成     还原数据库  鼠标右键单击数据库,选择还原文件和文件   ...
1365 0