SQL server 2017的脚本使用

记录一下使用SQL server 的过程

1.创建数据库

　　使用新建查询，脚本如下：

 1 create database testdb01
 2 on primary(
 3 name ='testdb01',
 4 filename ='E:	estdb	estdb01.mdf',
 5 size = 10MB,
 6 maxsize = 100MB,
 7 filegrowth=5MB
 8 )
 9 log on (
10 name = 'testdb01_log',
11 filename = 'E:	estdb	estdb01_log.ldf',
12 size = 10MB,
13 maxsize = 100MB,
14 filegrowth = 5MB
15 )

注意：使用脚本不会自动创建文件夹，需要手动创建新的文件。

然后分析，运行就行了。

通过右键查看其属性可进行修改

查看数据库文件具体信息：

exec sp_helpdb testdb01;

修改数据库名：

alter database testdb01
modify name = testdb02;

修改数据库文件名：　　

alter database testdb02
modify file(
name ='testdb01',
size =20MB,
maxsize = 50MB,
filegrowth = 10MB
);

删除数据库：（处于连接状态不能删除）

drop database testdb01;

创建两个表

use ems
go
create table dept(deptno char(3) primary key,
                deptname char(20) not null,
                location char(20));

create table employee(eno char(4),
                        ename char(4)not null,
                        manager char(4),
                        salary int,
                        deptno char(3),
                        primary key(eno),
                        foreign key(manager) references employee(eno),
                        foreign key(deptno) references dept(deptno));

创建视图

create view annualsal(eno,ename,annualsal)
as select eno,ename,12*salary
    from employee

create view manager(manager,name,clerknum)
    as select e1.manager,e2.ename,e1.clerknum
        from(select manager, count(*)as clerknum
            from employee
            group by manager having count(*)>0) e1,employee e2
        where e1.manager = e2.eno

表格插入操作

insert into dept values('D01','Computer School','North 1-405');
insert into employee values('E02','YIN','E01','7000','D01');
insert into employee values('E03','XU','E01','2000','D01');
insert into employee values('E04','QU','E02','2000','D01');
insert into employee values('E05','ZAO',null,'5000','D02');
insert into employee values('E06','PANA','E05','4000','D02');
insert into employee values('E07','PANB','E05','3000','D03');

简单的查询

select ename,salary,deptno
    FROM employee
    WHERE salary between 2000 and 3000
    ORDER by salary DESC

创建登陆

exec sp_addlogin qbb,qbb

删除登陆

exec sp_droplogin qbb

查看所有登陆者的信息

exec sp_helplogins

备份完整数据库

注意：这里需要先创建备份的文件子目录

exec sp_addumpdevice 'DISK','backupdevice_ems','F:ackupdevems.bak'
BACKUP DATABASE ems TO backupdevice_ems

备份日志

BACKUP LOG ems TO backupdevice_ems

用已存在的登录名创建用户名

EXEC sp_grantdbaccess 'qbb','qbb'

显示登陆用户

EXEC sp_helpuser