sqlsever数据库管理

目录

1. 数据库文件在磁盘上的存储形式

  1. 主数据库文件:*.mdf
  2. 辅助数据文件:*.ndf
  3. 日志文件:*.ldf
  4. 文件组

注意:linux上使用yum安装的sqlsever服务数据库主、辅文件和日志文件的默认路径是 /var/opt/mssql/data/

2. 使用SQL语句创建数据库

2.1 简单创建一个数据库
use master;
go

create database db1;

select * from master.sys.databases;
go

查看 /var/opt/mssql/data/路径下是否有db1的数据库主文件和日志文件:

[root@myhost ~]# ll /var/opt/mssql/data/
总用量 91912
-rw-rw----. 1 mssql mssql  8388608 825 15:09 db1_log.ldf ##db1的数据库主文件##
-rw-rw----. 1 mssql mssql  8388608 825 15:09 db1.mdf ##db1的日志文件##
-rw-r-----. 1 mssql mssql  5636096 825 14:45 master.mdf
-rw-r-----. 1 mssql mssql  2097152 825 15:09 mastlog.ldf
-rw-r-----. 1 mssql mssql  8388608 825 15:09 modellog.ldf
-rw-r-----. 1 mssql mssql  8388608 825 15:09 model.mdf
-rw-r-----. 1 mssql mssql 17104896 824 13:47 msdbdata.mdf
-rw-r-----. 1 mssql mssql  5308416 824 13:47 msdblog.ldf
-rw-r-----. 1 mssql mssql  8388608 824 13:47 tempdb.mdf
-rw-r-----. 1 mssql mssql  8388608 825 14:02 templog.ldf
2.2 创建只有一个数据库主文件和一个日志文件的数据库

要求如下:

  1. 数据库名为 E_Market;
  2. 数据库主文件的逻辑文件名为 E_Market_data;
  3. 数据库主文件物理文件名为 /var/opt/mssql/data/E_Market_data.mdf;
  4. 数据库主文件的初始大小是5MB;
  5. 数据库主文件能增长到的最大值是100MB;
  6. 数据库主文件大小的增长率为15%;
  7. 日志文件的逻辑文件名为 E_Market_log;
  8. 日志文件物理文件名为 /var/opt/mssql/data/E_Market_log.ldf;
  9. 日志文件的初始大小是5MB;
  10. 日志文件大小的增长率为0(也就是不启用增长率)。
use master; 
go  

create database E_Market 
on primary
(
	name="E_Market_data", 
	filename="/var/opt/mssql/data/E_Market_data.mdf",
	size=5MB, 
	maxsize=100MB, 
	filegrowth=15%

)

log  on
(
	name="E_Market_log", 
	filename="/var/opt/mssql/data/E_Market_log.ldf",
	size=5MB, 
	filegrowth=0

)

go 

执行创建新数据库的sql语句:

在这里插入图片描述
根据错误修改创建新数据库的sql语句:

在这里插入图片描述

印证创建新数据库的sql语句是否执行成功:

use master;
go

select * from master.sys.databases;
go

点击数据库连接进行刷新,就可以看到我们刚刚创建成功的新数据库。
注意:当使用navicat数据库连接工具连接sqlsever服务器时,在图形界面中我们无法看到默认的系统数据库,但是还是可以用sql语句进行操作。
在这里插入图片描述

2.3 创建有多个数据库文件(主文件+文件组+辅文件)和多个日志文件的数据库

要求如下:

  1. 数据库名为 E_Shop;
  2. 数据库主文件的逻辑文件名为 E_Shop_data;
  3. 数据库主文件物理文件名为 /var/opt/mssql/data/E_Shop_data.mdf;
  4. 数据库主文件的初始大小是10MB;
  5. 数据库主文件能增长到的最大值是100MB;
  6. 数据库主文件大小的增长率为10%;
  7. 数据库文件组的逻辑文件名为 fg;
  8. 数据库文件组中辅文件的逻辑文件名为 fg_E_Shop_data;
  9. 数据库文件组中辅文件的文件名为/var/opt/mssql/data/fg_E_Shop_data.ndf;
  10. 数据库文件组中辅文件的的初始大小是10MB;
  11. 数据库文件组中辅文件的大小的增长率为0(也就是不启用增长率);
  12. 日志文件1的逻辑文件名为 E_Shop_log1;
  13. 日志文件1物理文件名为 /var/opt/mssql/data/E_Shop_log1.ldf;
  14. 日志文件1的初始大小是5MB;
  15. 日志文件1大小的增长率为0(也就是不启用增长率)。
  16. 日志文件2的逻辑文件名为 E_Shop_log2;
  17. 日志文件2物理文件名为 /var/opt/mssql/data/E_Shop_log2.ldf;
  18. 日志文件2的初始大小是5MB;
  19. 日志文件2大小的增长率为0(也就是不启用增长率)。
use master;
go 


create database E_Shop 

on primary
(
	name="E_Shop_data",
	filename="/var/opt/mssql/data/E_Shop_data.mdf",
	size=10MB,
	maxsize=100MB,
	filegrowth=10%

),

filegroup fg
(
	name="fg_E_Shop_data",
	filename="/var/opt/mssql/data/fg_E_Shop_data.ndf",
	size=10MB,
	filegrowth=0
)

log  on
(
	name="E_Shop_log1",
	filename="/var/opt/mssql/data/E_Shop_log1.ldf",
	size=5MB,
	filegrowth=0

),

(
	name="E_Shop_log2",
	filename="/var/opt/mssql/data/E_Shop_log2.ldf",
	size=5MB,
	filegrowth=0

)

go 

执行创建新数据库的sql语句:

在这里插入图片描述

印证创建新数据库的sql语句是否执行成功:

use master;
go

select * from master.sys.databases;
go

在这里插入图片描述

2.4 向现有的数据库中添加文件组和数据文件
  1. 先添加文件组fg1
use E_Shop;
go 
--先添加文件组fg1
alter database E_Shop add filegroup fg1;
go
  1. 再为新建的文件组fg1添加数据文件
--再为新建的文件组fg1添加数据文件
alter database E_Shop add file
(
	name="fg1_E_Shop_data",
	filename="/var/opt/mssql/data/fg1_E_Shop_data.ndf",
	size=5MB,
	filegrowth=0


) to filegroup fg1;
go
  1. 最后将fg1文件组设置为默认文件组
--最后将fg1文件组设置为默认文件组

alter database E_Shop modify filegroup fg1 default;

go 
2.5 使用SQL语句创建数据库的错误解决
  1. 错误描述:SQL Server 不能创建数据库了,发生错误:1807 未能获得数据库 ‘model’ 上的排它锁。请稍后重试操作。

  2. 错误原因:在创建数据库时,创建错误然后删除了,结果再次创建新数据库,发现出现这个错误。

  3. 解决办法:执行以下sql语句

DECLARE @sql VARCHAR(100) 
WHILE 1 = 1
BEGIN
    SELECT TOP 1 @sql = 'kill   ' + CAST(spid AS VARCHAR(3))
    FROM   MASTER..sysprocesses
    WHERE  spid > 50
           AND spid <> @@spid   
    IF @@rowcount = 0
        BREAK 
    EXEC (@sql)
END

3. 使用SQL语句删除数据库

在这里插入图片描述

use master; --删除数据库其实也是将数据库从系统数据库master里面剔除一条记录,所以需要先选择需要使用的数据库master。
go 


--删除数据库之前或者创建数据库之前可以先判断数据库是否存在,防止执行sql语句报错
if exists (select * from sys.databases where name='E_Market')
drop database E_Market;
go

if exists (select * from sys.databases where name='E_Shop')
drop database E_Shop;
go


if exists (select * from sys.databases where name='db1')
drop database db1;
go

注意:drop 语句删除数据库是将数据库从磁盘上彻底删除,所以在删除数据库之前要备份或者确认。

4. 创建表

4.1 语法
use [数据库名];

create table [表名] 
(
 id int not null identity(1,1) primary key,  #设置为主键和自增长列,起始值为1,每次自增1
 name varchar(20) not null,   #默认不为空
 gender varchar(2) not null
)
go

4.2 示例
use E_Market;
create table student
(
 id int not null identity(1,1) primary key,
 name varchar(20) not null, 
 gender varchar(2) not null
)

go

-----验证----
select * from E_Market.dbo.student;

5. 向表中插入数据

5.1 语法
数据库名是指定的表所驻留的数据库名称。
dbo是表所有者的名称。
表名是含有标识列的表名。
use [数据库名];

insert into [表名] ([列名1],[列名2],[列名3]) values ('列名数据1','列名数据2','列名数据3');
--或者
insert into dbo.[表名] ([列名1],[列名2],[列名3]) values ('列名数据1','列名数据2','列名数据3');
--或者
insert into [数据库名].dbo.[表名] ([列名1],[列名2],[列名3]) values ('列名数据1','列名数据2','列名数据3');

go
5.2 示例
use E_Market;

insert into student (id,name,gender) values ('1','zhangsan','男');

insert into dbo.student (id,name,gender) values ('2','李四','女');

insert into E_Market.dbo.student (id,name,gender) values ('3','王五','男');

go

-----验证----
select * from E_Market.dbo.student;
5.3 使用SQL语句向表中插入数据的错误解决

错误详情:

在向表中插入数据时报以下错误:[SQL Server]当 IDENTITY_INSERT 设置为 OFF 时,不能为表 ‘student’ 中的标识列插入显式值。 (544)

在这里插入图片描述
错误原因:

当数据库中创建表中的id设置为自动增长的时候,如果在插入数据时还对id进行插入则会报这个错误。

解决办法:

1.可以在创建表时,对id这个字段不进行identity(1,1)限制;

具体操作方法:在创建表的时候,把 [id] [int] identity(1,1) NOT NULL , 把identity(1,1)去掉。

2.可以在插入数据insert语句时,不插入id这个字段;

在这里插入图片描述

  1. SET IDENTITY_INSERT允许将显式值插入表的标识列中。

具体操作方法实在insert插入语句前面加上下面的sql语句:

语法:
SET IDENTITY_INSERT [数据库名].dbo.[表名] [ON | OFF ]
SET IDENTITY_INSERT E_Market.dbo.student on;
参数:
数据库名是指定的表所驻留的数据库名称。
dbo是表所有者的名称。
表名是含有标识列的表名。。

在这里插入图片描述
在这里插入图片描述

6. 查看表中数据

6.1 语法

use [数据库名];

select * from [表名]
--或者
select * from dbo.[表名]
--或者
select * from [数据库名].dbo.[表名]
go

##带条件查看数据
select * from [数据库名].dbo.[表名] where 列名='xx' 
go

6.2 示例

use E_Market;

select * from student;
--或者
select * from dbo.student;
--或者
select * from E_Market.dbo.student;
go

##带条件查看数据
select * from E_Market.dbo.student where id='1'  
go

7. 更新数据

7.1 语法

use [数据库名];

update [数据库名].dbo.[表名] set [列名]=‘更新后的数据’ where [列名]=‘更新列数据’;

go
7.2 示例

注意:sqlsever的sql语法中不能用双引号只能用单引号


use E_Market;

update E_Market.dbo.student set name='yuki' where id=5;

go

-----验证----
select * from E_Market.dbo.student;

8. 删除表中数据

8.1 语法

use [数据库名];

delete from [数据库名].dbo.[表名] where [列名]=‘更新列数据’;

go
8.2 示例
use E_Market;

delete from E_Market.dbo.student where name='zhangsan';

go

-----验证----
select * from E_Market.dbo.student;

9. 删除表

9.1 语法

use [数据库名];

drop table [表名]

go
9.2 示例

use E_Market;

drop table student;

go

-----验证----
select * from E_Market.dbo.student; --这时会报错:[SQL Server]对象名 'E_Market.dbo.student' 无效。 (208)
  • 0
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值