SQL Server数据库命令整理大全

create database mydatabase;

drop database mydatabase;

use mydatabase;

create table employees (
    employeeid int primary key,
    firstname nvarchar(50),
    lastname nvarchar(50),
    hiredate datetime
);

insert into employees (employeeid, firstname, lastname, hiredate)
values (1, 'john', 'doe', '2023-01-01');

update employees
set firstname = 'jane'
where employeeid = 1;

delete from employees
where employeeid = 1;

alter table employees
add departmentid int foreign key references departments(departmentid);

select * from employees;

select * from employees where departmentid = 2;

select * from employees order by hiredate desc;

select count(*) from employees;

select departmentid, count(*) as countofemployees
from employees
group by departmentid;

select e.firstname, d.departmentname 
from employees e
inner join departments d on e.departmentid = d.departmentid;

create procedure getemployeesbydepartment @deptid int
as
begin
    select * from employees where departmentid = @deptid;
end

exec getemployeesbydepartment 2;

create function dbo.gettotalemployees(@deptid int) returns int
as
begin
    return (select count(*) from employees where departmentid = @deptid);
end

select dbo.gettotalemployees(2) as totalempindept2;

create view employeenames as
select firstname, lastname from employees;

select * from employeenames;

create index ix_employees_departmentid on employees (departmentid);

drop index ix_employees_departmentid on employees;

begin transaction;
-- 执行一系列操作...
commit transaction;

backup database mydatabase to disk = 'c:\backup\mydatabase.bak';
restore database mydatabase from disk = 'c:\backup\mydatabase.bak';

create login newuser with password = 'strongpassword!';

create user userfordb for login newuser;
alter role db_datareader add member userfordb; -- 给予读权限
alter role db_datawriter add member userfordb; -- 给予写权限

drop user userfordb;

grant select on employees to userfordb;
deny update on employees to userfordb;
revoke delete on employees from userfordb;

backup database mydatabase
to disk = 'c:\backup\mydatabase.bak'
with format, medianame = 'mydatabase_full', name = 'full backup';

backup database mydatabase
to disk = 'c:\backup\mydatabase_diff.bak'
with differential, format, medianame = 'mydatabase_diff', name = 'differential backup';

backup log mydatabase
to disk = 'c:\backup\mydatabase_log.trn'
with noformat, noinit, name = n'mydatabase_logbackup', skip, norewind, nounload, stats = 10;

restore database mydatabase
from disk = 'c:\backup\mydatabase.bak'
with file = 1, nounload, replace, stats = 5;

select @@version;

sp_help 'employees';

select getdate() as currentdatetime;

select 
    employeeid, 
    firstname, 
    salary,
    avg(salary) over (partition by departmentid) as avgsalaryindept
from employees;

with empsalaries as (
    select employeeid, salary
    from employees
)
select * from empsalaries where salary > (select avg(salary) from empsalaries);

create partition function pf_employeesrange (int)
as range right for values 
(2000, 2005, 2010, 2015);

create partition scheme ps_employees 
as partition pf_employeesrange 
to (
    [primaryfilegroup], 
    [secondaryfilegroup1],
    [secondaryfilegroup2],
    [secondaryfilegroup3]
);

create table partitionedemployees (
    employeeid int primary key,
    hiredate int not null
) on ps_employees(hiredate);

select firstname, lastname from employees where departmentid = 1
union all
select firstname, lastname from employees where departmentid = 2;

select employeeid from employees where departmentid = 1
intersect
select employeeid from employees where departmentid = 2;

select employeeid from employees where departmentid = 1
except
select employeeid from employees where departmentid = 2;

use msdb;
go
exec sp_add_job @job_name=n'mybackupjob', 
                @enabled=1, 
                @description='daily backup job';
go

-- 添加作业步骤
exec sp_add_jobstep @job_name=n'mybackupjob', 
                   @step_name=n'backup database',
                   @subsystem=n'tsql', 
                   @command=n'backup database mydatabase to disk = ''c:\backup\mydatabase.bak'';',
                   @retry_attempts=5, 
                   @retry_interval=5;
go

-- 启用作业调度
exec dbo.sp_add_schedule
    @schedule_name = n'dailyatmidnight',
    @freq_type = 4, -- 每日
    @freq_interval = 1, -- 每天运行一次
    @active_start_time = 000000; -- 在午夜开始

-- 将作业与调度关联
exec sp_attach_schedule
    @job_name = n'mybackupjob', 
    @schedule_name = n'dailyatmidnight';
go

-- 在查询语句前添加explain 或者 set showplan_all on
set showplan_all on;
select * from employees where departmentid = 1;
set showplan_all off;

-- 或使用图形化方式查看
-- 在sql server management studio中，运行查询后右键选择"包括实际执行计划"
select * from employees where departmentid = 1;

select *
from openrowset('microsoft.ace.oledb.12.0', 'text;database=c:\data;hdr=yes;fmt=delimited', 'select * from [employees.txt]');

-- 创建链接服务器
exec sp_addlinkedserver @server = n'mylinkedserver', @srvproduct=n'otherdb', @provider=n'sqlncli', @datasrc=n'servername\instancename';

-- 使用链接服务器查询数据
select * 
from mylinkedserver.remotedb.dbo.employees;

declare @departmentid int = 1;
declare @sql nvarchar(max) = n'select * from employees where departmentid = ' + cast(@departmentid as nvarchar(10));
exec sp_executesql @sql;

create table orders (
    orderid int identity(1,1),
    customerid int,
    orderdate date,
    primary key (orderid)
);

select cast('1234' as int), convert(int, '1234');

select employeeid, firstname, lastname,
    case when salary > 50000 then 'high'
         when salary > 30000 then 'medium'
         else 'low'
    end as salarylevel
from employees;

select employeeid, firstname, lastname,
    iif(salary > 50000, 'high', iif(salary > 30000, 'medium', 'low')) as salarylevel
from employees;

create database mydatabase_snapshot on 
    (name = mydatabase, filename = 'c:\snapshots\mydatabase_snapshot.ss')
as snapshot of mydatabase;

restore database mydatabase from database_snapshot = 'mydatabase_snapshot';