2.1 使用SQL语句创建数据库
2.1.1 创建数据库
创建数据库语法如下:
create database 数据库名称 [on[primary] ( name=logical_file_name, filename='os_file_name' [,size=size[KB|MB|GB|TB]] [,maxsize={max_size[KB|MB|GB|TB]|unlimited}] [,filegrowth=growth_increment[KB|MB|GB|TB|%]] ) [log on{<日志文件参数>[,...n]} ] ]
创建数据库示例:
create database NetBarDB on ( name=NetBar_mdf, frlename='E:\NetBar_mdf.mdf', size=3MB, maxsize=100MB, filegrowth=1MB ) log on ( name=NetBar_ldf, filename='E:\NetBar_ldf, size=3MB, maxsize=100MB, filegrowth=1MB ) go
用SQL代码实现文件夹的创建:
xp_cmdshell存储过程的语法:
sp_cmdshell {'命令提示符'} [,no_output]
利用xp_cmdshell 存储过程创建文件sql代码示例:
——启用外围配置 EXEC sp_configure show advanced options,1 --以sp开头的是系统存储过程 reconfigrue EXEC sp_configure'xp_cmdshell',1 reconfigure go --调用dos命令创建文件夹 EXEC xp_cmdshell'md E:\NetBar',no_output go ``````创建数据库语句
2.1.2 删除数据库
删除数据库的语法为:
drop database 数据库名称
删除多个数据库的语法为:
drop database 数据库名称1,数据库名称2,数据库名称3······,数据库名称n
使用drop语句删除数据库将会删除磁盘中与将要删除的数据库相关的文件,如数据文件,日志文件。
删除数据库示例:
--判断NetBarDB数据库是否存在,若存在则删除此数据库,然后再创建数据库
if exists (select * from sys.sysdatabases where [name]='NetBarDB') --sys.sysdatabases是一个系统视图 drop database NetBarDB go ``````创建NetBarDB 数据库语句
要判断SQL SERVER中是否已存在某个数据库,还有另一种常用的方式,例如:
if DB_ID('NetBarDB')is not null drop database NetBarDB go .....创建NetBarDB数据库语句
这种方式根据DB_ID(SQL server中的一个系统函数,用于返回数据库标识(ID)号)函数返回值来判断SQL server中是否已存在NetBarDB数据库。
DB_ID是SQL Server中的一个系统函数,它用于返回数据库标识(ID)号。
因为在SQL Server中不存在相同名称的数据库,而且每个数据库都进行了ID标识,如果使用DB_ID函数未查询出某个数据库的ID,那么就可以断定这个数据库在SQL Server中是不存在的。
下面是DB_ID函数的使用语法:
DB_ID('数据库名称')
数据库名称是可以省略的,如果省略了数据库名称则会返回当前数据库的ID。若查询出某数据库的ID,则会返回NULL。
2.2 SQL数据库类型
2.3.1 创建表
语法示例:
create table 表名
(
字段1 数据类型 字段特征,
字段2 数据类型 字段特征,
······
字段n 数据类型 字段特征
)
创表示例:
create table cardInfo ( CardId int not null identity(1,1), --会员卡编号 CardNumber nvarchar(50) not null, --会员卡号 CardPwd varchar(50) not null, --密码 CardBalance int not null, --余额 TransactTime datetime --办卡时间 ) go
其中identity是CardId列的属性,它用于在表中创建表示列,即CardId为标识列
identity的使用语法为:
identity[(seed,increment)] --seed表示标识种子或初始值,increment表示递增量。
在SQL server 中,identity属性只能用于int、bigint、smallint、tinyint、或decimal,或者是小数位为0的numeric等数据类型的列,并且 这些列不能为空。一个表只能有一个identity列。
2.3.2 更改表
1.添加列
语法:
alter table 表名
add{字段 数据类型 字段特征}[....n]
示例:
alter table cardInfo add remark1 varchar(20) null, remark2 varchar(20) null go
可以向现有表添加列,前提是相应列允许使用null值或者对该列创建了default约束。若表中没有数据,则无此限制。
2.删除列
语法:
alter table 表名
drop column{字段}[....n]
示例:
alter table cardInfo drop column Remark go
3.修改列
修改列主要修改列的名称、数据类型、长度、精度、小数位数以及可控性。
语法格式:
alter table 表名
alter column 字段 数据类型[null | not null]
①修改列的数据类型、长度、精度、小数位数
alter table cardInfo alter column CardBalance decimal(6,2) go
在修改数据类型后,列的数据将发生变化,比如字符串类型更改后,类型的长度小于开始长度时,字符串可能被截断。还有,数据类型不能胡乱修改。
②修改列的可控性
alter table cardInfo alter column TransactTime datetime null go
③修改列的名称
在SQL server中提供了一个系统存储过程sp_rename可以修改列的名称。sp_rename用于在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列等等,包括数据库名称也可以使用SP_rename修改
sp_rename 语法:
sp_rename 'object_name','new_name'[,'object_type']
示例:
use NetBarDB go exec sp_rename 'cardInfo.TransactTime','CreateTime','column' go
或者可以为:
use NetBarDB go exec sp_rename 'cardInfo.TransactTime','CreateTime' go
上述语句将cardInfo表的“TransactTime”列名变成了“CreateTime”。
2.3.3 删除表
语法:
drop table 表名[,....n]
示例:
use NetBarDB go drop table cardInfo go
在删除cardInfo表时要保证当前NetBarDB数据库存在cardInfo表,否则会发生错误,所以在删除表之前应该判断cardInfo表是否存在,如下列语句:
use NetBarDB go if exists(select * from sys.sysobjects where [name]='cardInfo') drop table cardInfo go
sys.sysobject 和sys.sysdatabases一样,都属于SQL server中的系统试图,在数据库中创建的每个对象(例如约束、默认值、日志、规则以及存储过程)都在sys.objects中对应一行。与判断数据库存在相似,还有另一种比较简单的方式判断表是否存在,如下列语句:
if object_ID(‘cardInfo’)is not null drop table cardInfo go
object_ID类似于DB_ID,它是一个系统函数,用于返回数据库对象标识号。
2.4.1添加约束
常用的约束类型:
主键约束(Primary Key Constraint):要求主键列数据唯一,并且不允许为空
唯一约束(Unique Constraint):要求该列唯一,允许为空,但只能出现一个空值。
检查约束(Check Constraint):某列取值范围限制、格式限制等,如有关年龄的约束
默认约束(Default Constraint):某列的默认值,如我们的男性学员较多,性别默认为“男”
外键约束(Foreign Key Constraint):用于两表间建立关系,需要指定引用主表的那列语法:
alter table 表名
add constraint 约束名称 约束类型 约束内容
①主键约束
主键约束在表中定义一个主键来唯一确定表中每一行数据,被主键约束的列(字段)是唯一的、非空的。
添加主键的语法:
alter table 表名
add constraint 约束名称 primary key (字段[.....n])
例:为cardInfo表建立主键约束
alter table cardInfo add constraint PK_cardInfo_CardId primary key(CardId) go
②唯一约束
语法:
alter table 表名
add constraint 约束名称 unique (字段[......n])
例:为cardInfo表的CardNumber字段建立唯一约束
alter table cardInfo add constraint UQ_cardInfo_CardNumber unique(CardNumber) go
③默认值约束
为表添加默认值约束的语法:
alter table 表名
add constraint 约束名称 default 默认值 for 字段
--默认值可以为常量或者为空(null)
例:
alter table PCInfo add constraint DF_PCInfo_PCNote default '这台电脑不错' for PCNote go
④检查约束
语法为:
alter table 表名
add constraint约束名称 check (逻辑表达式)
例:
alter table cardInfo add constraint CK_cardInfo_CardPassword check(len(ardPassword)>0 and len(CardPassword)<=6) go
⑤外键约束
语法:
alter table 从表表名
add constraint 约束名称 foreign key (从表字段)references 主表表名(主表字段)
例:为recordInfo表和cardInfo表建立外键关系
alter table recordInfo add constraint FK_recordInfo_cardInfo_CardId foreign key(CardId) references cardInfo(CardId) --外键约束的命名规则为:FK_从表名_主表名_外键字段名
2.4.2 删除约束
删除约束的语法:
alter table 表名
drop [constraint] 约束名称
----constraint关键字是可选的
示例:
alter table recordInfo drop constraint PK_cardInfo_CardId go ------------------------------------------------------------------------- --在删除该约束之前最好判断该约束是否存在,如果在数据库中并不存在该约束,就会报错 use NetBarDB go if exists(select * from sys.sysobjects where [name]='PK_cardInfo_CardId') alter table recordInfo drop constraint PK_cardInfo_CardId go --或者 use NetBarDB go if OBJECT_ID('PK_cardInfo_CardId')is not null alter table recordInfo drop constraint PK_cardInfo_CardId go
数据库的安全管理
为数据库创建登陆账号以及数据库用户
-- 1.创建登陆账号: (1) create login 登录名称 with password='登录密码' (2) exec sp_addlogin '登录名称','登录密码' drop login zhangsanlogin //删除登陆账号
//2.创建数据库用户: (1) create user zhangsan for login zhangsanlogin (2)exec sp_adduser 'zhangsan' (3)exec sp_adduser 'zhangsanlogin','zhangsan' alter user zhangsan with name=zhangsanuser //修改用户信息 drop user zhangsanUser //删除用户信息
//3.添加角色: create role rolename/exec sp_addrole 'newrole'
4.授权:用户权限管理的三种方式
(1)授权:
//向数据库用户admin_netbar授予对cardInfo表的select,insert,update,delete的权限 grant select,insert,update,delete on cardInfo to admin_netbar go //向数据库用户admin_netbar授予对cardInfo表指定列(CardNumber,CardBalance)的select权限 grant select on cardInfo(cardnumber,cardbalance) to admin_netbar go //向数据库用户admin_netbar授予create table(建表)的权限 grant create table to admin_netbar go
(2)对用户收回已授予的权限:
//收回对数据库用户admin_netbar授予的对cardInfo表的update权限 revoke update on cardInfo to admin_netbar go
(3)禁止用户拥有的某种权限
//对数据库用户amin_netbar禁用cardInfo表的select权限 deny select on cardInfo to admin_netbar go