SQL Server 数据库实现之T-SQL语句[备忘]
1、用SQL创建文件夹
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
-- 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'
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创建数据库
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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;
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创建数据表
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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);
--====================================================
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 身份验证
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
--============== 添加 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 语句(建议使用) ------------
/*
使用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 身份验证
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
--============== 添加 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 将删除该功能。
------------ 方法一:使用 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创建数据库用户
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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;
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;
作者: XuGang 网名:钢钢 |
出处: http://xugang.cnblogs.com |
声明: 本文版权归作者和博客园共有。转载时必须保留此段声明,且在文章页面明显位置给出原文连接地址! |