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 8月  25 15:09 db1_log.ldf ##db1的数据库主文件##
-rw-rw----. 1 mssql mssql  8388608 8月  25 15:09 db1.mdf ##db1的日志文件##
-rw-r-----. 1 mssql mssql  5636096 8月  25 14:45 master.mdf
-rw-r-----. 1 mssql mssql  2097152 8月  25 15:09 mastlog.ldf
-rw-r-----. 1 mssql mssql  8388608 8月  25 15:09 modellog.ldf
-rw-r-----. 1 mssql mssql  8388608 8月  25 15:09 model.mdf
-rw-r-----. 1 mssql mssql 17104896 8月  24 13:47 msdbdata.mdf
-rw-r-----. 1 mssql mssql  5308416 8月  24 13:47 msdblog.ldf
-rw-r-----. 1 mssql mssql  8388608 8月  24 13:47 tempdb.mdf
-rw-r-----. 1 mssql mssql  8388608 8月  25 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

2. 再为新建的文件组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

3. 最后将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这个字段；

3. 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)