永利酒店赌场SQL Server 数据库实现之T-SQL语句[备忘]

使用SSMS数据库管理工具修改DEFAULT约束

1、连接数据库、选择数据表-》右键点击-》选择设计。

永利酒店赌场 1

2、在表设计器窗口-》选中要修改的数据列-》在列属性中找到默认值绑定-》进行修改。

永利酒店赌场 2

永利酒店赌场SQL Server 数据库实现之T-SQL语句[备忘]。3、点击保存(或者ctrl+s)-》关闭表设计器-》刷新表-》重新打开表设计器查看。

永利酒店赌场 3

DEFAULT约束添加规则

1、若在表中定义了默认值约束,用户在插入新的数据行时,如果该行没有指定数据,那么系统将默认值赋给该列,如果我们不设置默认值,系统默认为NULL。

2、如果“默认值”字段中的项替换绑定的默认值(以不带圆括号的形式显示),则将提示你解除对默认值的绑定,并将其替换为新的默认值。

3、若要输入文本字符串,请用单引号 (‘)
将值括起来;不要使用双引号
(“),因为双引号已保留用于带引号的标识符。

4、若要输入数值默认值,请输入数值并且不要用引号将值括起来。

5、若要输入对象/函数,请输入对象/函数的名称并且不要用引号将名称括起来。

修改数据列

在开发和生产过程中,列名的拼写错误或者列名的更改是需要操作数据表的,大多数情况下都是不需要修改的.

以下几种情况下我们并不能直接修改数据列:

1、用于索引的列。 
2、用于 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束的列。 
3、与默认值(由 DEFAULT 关键字定义)相关联的列,或绑定到默认对象的列。 
4、绑定到规则的列。 

 

PRIMARY KEY约束添加规则

1、在表中常有一列或多列的组合,其值能唯一标识表中的每一行,这样的一列或多列成为表的主键(PrimaryKey)。

2、一个表只能有一个主键,而且主键约束中的列不能为空值。

3、只有主键列才能被作为其他表的外键所创建。

4、一般情况下一个表中只能有一个主键。

使用T-SQL脚本修改DEFAULT约束

DEFAULT约束修改规则必须首先删除现有的DEFAULT约束,然后使用新定义重新创建,才能使用Transact-SQL修改DEFAULT约束。

语法:

–使用数据库
use 数据库名;
go
–判断要创建的约束是否已存在
if exists(select * from sysobjects where
name=约束名)
alter table 表名 drop constraint
约束名;
go
–添加默认值约束
alter table 表名 add constraint 约束名
default 约束值 for 列名;
go

示例:

–使用数据库
use testss;
go
–判断要创建的约束是否已存在
if exists(select * from sysobjects where
name=’default1′)
alter table test1 drop constraint
default1;
go
–添加默认值约束
alter table test1 add constraint default2
default 18 for age;
go

 永利酒店赌场 4

永利酒店赌场 5

使用SSMS数据库管理工具添加DEFAULT约束

1、连接数据库,选择数据表-》右键点击-》选择设计。

永利酒店赌场 6

2、在表设计窗口中-》选择数据列-》在列属性窗口中找到默认值或绑定-》输入默认值(注意默认值的数据类型和输入格式)。

永利酒店赌场 7

3、点击保存按钮(或者ctrl+s)-》刷新表-》再次打开表查看结果。

永利酒店赌场 8

使用SSMS数据库管理工具修改列

1、连接数据库,打开要修改的数据库,选择数据表-》右键点击-》选择设计。

永利酒店赌场 9

2、在新打开的窗口视图中-》点击要修改的行-》可以修改列名、列类型、是否可空、属性等-》修改完成点击保存按钮(或者ctrl+s)。

永利酒店赌场 10

1、用SQL创建文件夹

使用SSMS数据库管理工具添加主键约束

总结

1、每个字段只能有一个默认约束。
2、如果默认约束设置的值大于字段所允许的长度,则截取到字段允许长度。
3、不能加入到带有IDENTITY属性或者TIMESTAMP的字段上。
4、如果字段的数据类型为用户自定义类型,而且已有默认值绑定在此数据类型上,则不允许再次使用默认值。

 

使用T-SQL脚本添加DEFAULT约束

使用T-SQL脚本修改列

永利酒店赌场 11永利酒店赌场 12用SQL创建文件夹

使用SSMS数据库管理工具添加一列作为一个主键约束

1、连接数据库,选择数据表-》右键点击,选择设计。

永利酒店赌场 13

2、在新窗口中,选择一行,右键点击-》选择设置主键-》点击保存按钮(或者ctrl+s)。

永利酒店赌场 14

3、刷新表查看示例结果。

永利酒店赌场 15

 

当表结构已存在时

首先判断表中是否存在默认约束,如果存在则先删除默认约束再添加,如果不存在则直接添加。

语法:

use 数据库
go
–判断默认约束是否存在,如果存在则先删除,如果不存在则直接添加
if exists(select * from sysobjects where
name=约束名)
alter table 表名 drop constraint
约束名;
go
–给指定列添加默认约束
alter table 表名 add constraint 约束名
default(约束值) for 列名;
go

示例:

use [testss]
go
–判断默认约束是否存在,如果存在则先删除,如果不存在则直接添加
if exists(select * from sysobjects where
name=’defalut_height’)
alter table [testss].[dbo].[test1]
drop constraint defalut_height;
go
–给指定列添加默认约束
alter table [testss].[dbo].[test1]
add constraint defalut_height default(160) for height;
go

永利酒店赌场 16

永利酒店赌场 17

修改列名

语法:exec sp_rename 
表明.旧列名,新列名; 

示例:exec sp_rename
‘[test1].height6′,’height7’; 

永利酒店赌场 18

永利酒店赌场 19

— 1、显示高级选项
sp_configure ‘show advanced options’,1;
reconfigure; –更改当前配置
go

使用SSMS数据库管理工具添加多列作为一个主键约束

1、连接数据库,选择数据表-》右键点击-》选择设计。

永利酒店赌场 20

2、在新窗口中-》按下ctrl键,选择多行-》右键点击-》选择设置主键-》点击保存(或者ctrl+s)。

永利酒店赌场 21

3、刷新表查看示例结果。

永利酒店赌场 22

创建表时添加默认约束

首先判断表是否选在,如果存在则先删除表再添加,如果不存在则直接添加。

语法:

–创建新表时添加默认约束
–数据库声明
use 数据库名
go
–如果表已存在则先删除表再创建,如果表不存在则直接创建
if exists(select * from sysobjects where
name=表名 and type =’U’)
drop table 表名;
go
–建表语法声明
create table 表名
(
–字段声明
列名 列类型 identity(1,1) not
null,
列名 列类型) null,
列名 列类型 null,
列名 列类型 null,
列名 列类型,
列名 列类型 constraint 约束名 default
默认值,
primary key clustered(列名 asc)
with(ignore_dup_key=off) on [primary] –主键索引声明
)on [primary]

–字段注释声明
exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’列说明’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’表名’,
@level2type=N’COLUMN’,@level2name=N’列名’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’列说明’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’表名’,
@level2type=N’COLUMN’,@level2name=N’列名’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’列说明’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’表名’,
@level2type=N’COLUMN’,@level2name=N’列名’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’列说明’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’表名’,
@level2type=N’COLUMN’,@level2name=N’列名’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’列说明’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’表名’,
@level2type=N’COLUMN’,@level2name=N’列名’;

go

示例:

–创建新表时添加默认约束
–数据库声明
use testss
go
–如果表已存在则先删除表再创建,如果表不存在则直接创建
if exists(select * from sysobjects where
name=’test1′ and type =’U’)
drop table test1;
go
–建表语法声明
create table test1
(
–字段声明
id int identity(1,1) not null,
name nvarchar(50) null,
sex nvarchar(50) null,
age nvarchar(50) null,
classid int,
height int constraint default_he default
166,
primary key clustered(id asc)
with(ignore_dup_key=off) on [primary] –主键索引声明
)on [primary]

–字段注释声明
exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’id主键’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’test1′,
@level2type=N’COLUMN’,@level2name=N’id’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’姓名’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’test1′,
@level2type=N’COLUMN’,@level2name=N’name’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’性别’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’test1′,
@level2type=N’COLUMN’,@level2name=N’sex’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’年龄’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’test1′,
@level2type=N’COLUMN’,@level2name=N’age’;

exec sys.sp_addextendedproperty
@name=N’MS_Description’, @value=N’班级id’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,@level1name=N’test1′,
@level2type=N’COLUMN’,@level2name=N’classid’;

go

永利酒店赌场 23

永利酒店赌场 24

修改列数据类型

语法:alter table 数据库名.dbo.表名 alter
column 列名 类型 [约束];  

示例:alter table [testss].dbo.[test1]
alter column height7 int null;  

永利酒店赌场 25

永利酒店赌场 26

— 2、启用 xp_cmdshell:
sp_configure ‘xp_cmdshell’,1; — 1代表“用户ID”
reconfigure
go

使用T-SQL脚本添加主键约束

DEFAULT约束优缺点

优点:

1、使用默认值可以减少代码量,新增数据时可以不用写新增默认值列,执行新增操作时时默认填充。

2、较有利于进行统计和分析,以及方便程序逻辑操作。

缺点:

1、使用不为NULL的默认值,占用了更多的存储空间。

 

修改列是否可空

语法:alter table 数据库名.dbo.表名 alter
column 列名 类型 约束;  

示例:alter table [testss].dbo.[test1]
alter column height7 int not null;

永利酒店赌场 27

永利酒店赌场 28

— 附:用SQL创建文件夹(手动):
       –> SQL Server 外围应用配置器
       –> 功能的外围应用配置器 
       –> 启用xp_cmdshell

使用T-SQL脚本添加一列作为一个主键约束

当表中约束存在时,不可以直接添加主键约束,因为一张表只允许存在一个主键约束,如果需要添加主键约束需要先删除已存在的主键约束再添加主键约束。当表中不存在主键约束时,可直接添加主键约束。

语法:

if exists(select * from sysobjects where
name=约束名)
alter table 数据库名.[dbo].表名 drop
constraint 约束名;
alter table 数据库名.[dbo].表名 add
constraint 约束名 primary key(列名 [asc|desc]);

示例:

–如果约束存在则删除约束,如果约束不存在则不删除
if exists(select * from sysobjects where
name=’idcon2′)
alter table [testss].[dbo].[test1]
drop constraint idcon2;
–添加约束
alter table [testss].[dbo].[test1]
add constraint idcon2 primary key(id asc);

永利酒店赌场 29

永利酒店赌场 30

修改列默认值

如果默认值不存在

语法:alter table 数据库名.dbo.表名 add
constraint 约束名 default 默认值 for 列名;

示例:alter table [testss].dbo.[test1]
add constraint df_h default 1 for height7;

永利酒店赌场 31

永利酒店赌场 32

— 然后你就可以使用xp_cmdshell了。

使用T-SQL脚本添加多列作为一个主键约束

当表中约束存在时,不可以直接添加主键约束,因为一张表只允许存在一个主键约束,如果需要添加主键约束需要先删除已存在的主键约束再添加主键约束。当表中不存在主键约束时,可直接添加主键约束。

语法:

–添加多列主键约束

if exists(select * from sysobjects where
name=约束名)
alter table 数据库名.[dbo].表名 drop
constraint 约束名;
alter table 数据库名.[dbo].表名 add
constraint 约束名 primary
key(列名 [asc|desc],列名 [asc|desc],……);

示例:

–添加多列主键约束
if exists(select * from sysobjects where
name=’idcon2′)
alter table [testss].[dbo].[test1]
drop constraint idcon2;
–添加约束
alter table [testss].[dbo].[test1]
add constraint idcon2 primary key(id asc,name desc);

永利酒店赌场 33

如果默认值存在

语法:

–第一步判断默认值否存在,如果存在就删除,如果不存在则不删除
if exists (select * from
sys.check_constraints where object_id = object_id(默认值约束名) and
parent_object_id = object_id(表名))
alter table 表名 drop constraint
默认值约束名

–第二步添加默认值

alter table 表名 add constraint  约束名
default 默认值 for 列名;

示例:

–第一步判断约束是否存在,如果存在就删除,如果不存在则不删除
if exists (select * from
sys.check_constraints where object_id = object_id(‘df_h’) AND
parent_object_id = object_id(‘[testss].dbo.[test1]’))
alter table [testss].dbo.[test1] drop
constraint df_h

–第二步添加默认值

alter table [testss].dbo.[test1] add
constraint df_h default 2 for height7;

永利酒店赌场 34

— 在C盘建立myDatabase文件夹
xp_cmdshell ‘mkdir c:myDatabase’; 
go — 或 xp_cmdshell ‘md c:myDatabase’ 

PRIMARY KEY约束优缺点

优点:

1、简单、效率高。 

2、保证数据完整性。

3、不会有空值。

4、不允许存在重复值。

缺点:

1、主键自增一般使用int型,有数据条数的限制。

2、在数据库进行数据合并时会比较麻烦。 

 

总结

在开发或者生产数据库中,数据列一经建好,不要轻易改动,随意改动可能会引起数据库的级联操作失败和代码错误。

 

 

 2、用SQL创建数据库

永利酒店赌场 35永利酒店赌场 36用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:myDatabasemyDBName.mdf’,
  size=3mb,
  maxsize=100mb, — unlimited:无限制的
  filegrowth=2mb 
)
, — 注意:逗号不能丢!
(
  name=’myDBName_1′,
  filename=’C:myDatabasemyDBName_1.ndf’,
  size=3mb,
  maxsize=unlimited,
  filegrowth= 10%
)
log on
(
  NAME = N’myDBName_log’, 
  FILENAME = N’C:myDatabasemyDBName_log.ldf’ , 
  SIZE = 1024kb, 
  MAXSIZE = 1gb, 
  FILEGROWTH = 1mb
)

collate Chinese_PRC_CI_AS –定义排序规则
go

— 删除数据库
— drop database myDBName;

 

 

3、用SQL创建数据表

永利酒店赌场 37永利酒店赌场 38用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 身份验证

永利酒店赌场 39永利酒店赌场 40添加 Windows 身份验证

–============== 添加 Windows 身份验证 ==============

———— 方法一:使用 SQL 语句(建议使用) ————

/* 
使用CREATE LOGIN为Windows用户[Windows域名域账户] 
创建 SQL Server 登录名。
*/
create login [NEWER-XGAdministrator] from windows;
go         — login_name 必须用方括号 ([ ]) 括起来。

— 删除 Windows 身份验证
drop login [NEWER-XGxugang];

———— 方法二:使用系统存储过程(不推荐使用) ————

/* sp_grantlogin:内置的系统存储过程 */
exec sp_grantlogin ‘Windows域名域账户’

exec sp_grantlogin ‘NEWER-XGxugang’;
/* 
“域”是针对于网络用户的相关设置,
    如果是本机,可用“计算机名”替换“Windows域名”,
                而“域账户” 等同于 “用户名”。
*/

— 删除 Windows 身份验证,请给出完整名称: <域用户名>
exec sp_revokelogin ‘NEWER-XGxugang’; 

方法二:添加 SQL Server 身份验证

永利酒店赌场 41永利酒店赌场 42添加 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创建数据库用户

永利酒店赌场 43永利酒店赌场 44用SQL创建数据库用户

use myDBName
go

–============ 创建’数据库用户’ ============

— 方式一:SQL语句
create user xugang for login [NEWER-XGxugang];
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-XGxugang’; –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;

 

网站地图xml地图