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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

 

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 ( 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 身份验证

复制代码
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语句管理索引
本次实验主要介绍如何在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
相关文章
|
9天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
51 10
|
9天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
68 6
|
4天前
|
SQL 安全 网络安全
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
8 0
|
5天前
|
SQL 数据库
数据库SQL语言实战(二)
数据库SQL语言实战(二)
|
5天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。
32 3
|
8天前
|
SQL 监控 数据库
数据库管理与电脑监控软件:SQL代码优化与实践
本文探讨了如何优化数据库管理和使用电脑监控软件以提升效率。通过SQL代码优化,如使用索引和调整查询语句,能有效提高数据库性能。同时,合理设计数据库结构,如数据表划分和规范化,也能增强管理效率。此外,利用Python脚本自动化收集系统性能数据,并实时提交至网站,可实现对电脑监控的实时性和有效性。这些方法能提升信息系统稳定性和可靠性,满足用户需求。
33 0
|
17天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
|
17天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
17天前
|
关系型数据库 MySQL 数据库
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
|
18天前
|
存储 关系型数据库 MySQL
数据库字符编码MySQL中使用UTF-8还是UTFB4
数据库字符编码MySQL中使用UTF-8还是UTFB4
20 0