Menu
[TOC]
Database Control
Just use ctrl+N
in SQL server and write code there . You can save it as a isolated .sql
in a certain path later on, or it will stay in temp folder.
Basically every script code starts in such structure:
1
2
3
use master
--Codes here
go
Create a database:
1
2
3
4
5
6
7
use master
create database Databasename
on
(name= test1_dat,filename='E:Microsoft SQL Serverdatabasedatabasename.mdf') --main file
log on
(name='databasename_log',filename='E:Microsoft SQL Serverdatabasetest1log.ldf')--log file
go
Use master
means all commends are under current database.
So you need point out two file at least : main file and its log.
main file must be a .mdf
file and it holds everything’s location , and log file saves every records in this database.
However , a database can contains many files attached , whose format is .ndf
, and they can be saved in a single file or a file group . Besides , you can define more parameters like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
use master
go
create database test2
on
primary
(name=prim_sub1,
filename='E:Microsoft SQL Serverdatabaseprim_sub1_dat.mdf',--Main file
size = 6mb,
maxsize = 20mb,
filegrowth = 20%),
(name = prim_sub2,
filename='E:Microsoft SQL Serverdatabaseprim_sub2_dat.ndf',--A sub-file
size = 6mb,
maxsize =20mb,
filegrowth=20%),
filegroup grouptest1 --A group of sub-files , which contains 2 files.
(name = grouptest1,
filename='E:Microsoft SQL Serverdatabasegroup1_sub1_dat.ndf',
size = 6mb,
maxsize =20mb,
filegrowth=5mb),
(name = group1_sub2,
filename='E:Microsoft SQL Serverdatabasegroup1_sub2_dat.ndf',--log file
size = 6mb,
maxsize = 20mb,
filegrowth = 5mb)
go
Detach a database from SQL server:
If you want to move the database file , or copy/delete its file manually , you shall detach it first.
And to establish database from file , make attaching on it.
It’s easy to remove a database , means your SQL server will no longer recognize it . No file will be deleted during detaching:
1
2
use master
exec sp_detach_db databsename , ture
Attaching:
1
2
3
4
5
use master
create database databasename
on(filename = 'PATH') --path of the MAIN FILE
for attach;
go
Create Backup:
-
Backing up a whole database :
1 2
back up database databasename to disk = 'd:backupfull.bak'
-
Backing up a whole database :(Rewrite every files)
1 2
back up database databasename to disk = 'TARGET PATH' with init
-
Backing up specific files or filegroups:(Changed file compared with last whole database only)
1 2
back up database databasename to disk = 'TARGET PATH' with differential
-
Creating a backup in many files:
1 2
backup database databasename to disk='d:backuppart1.bak',disk='d:backuppart2.bak'
-
Backing up the log.
1
backup log databasename To Disk='PATH'
or you can use this to keep complete log info.
1
backup log databasename To Disk='PATH' with No_Truncate
or if you want to keep tail log:
1
backup log databasename To Disk='PATH' with norecovery
Restore database:
simple:
1
restore database databasename from disk = 'd:backupfull.bak'
Delete database (Delete file):
1
2
use master
drop database databasename
Table & Columns Control
Create Table and Columns
To start editing in a specific database , type use XXX
to let you know which database will be effected. The format of creating table with its columns is : (for example)
1
2
3
4
5
6
7
8
use databaseneame
create table tablename(
column1 char(5) not null,
column2 char(3) null,
column3 float null,
primary key (column1),
foreign key (column2) references tablename2(column2)
)
Add and Alter columns
1
2
3
4
use databasename
alter table tablename --declare which table will be edited
add column5 char(40) not null --add a new column to table
alter column1 char(40) not null --reset target column , which must exist first.
Delete Table
1
2
3
use databasename
go
drop table tablename
Insert , Update or Delete Data
There’re 3 main measures to inset new data, **Import from .xls file , directly input in SSMS , and use insert
**
You can directly insert value in the format of tuple.
1
2
use databasename
insert into tablename values('value1','value2','value3',6)
When updating data:
1
2
use databasename
update tablename set[column2] = 'NewValue2' where column1 = 'value1'
You can remember this line by considering this way:
“update tablename
set column to change
= new value
where another column helping
= another column’s value
”
When deleting:
1
2
3
use databasename
delete from tablename where column7 = 'value4'
go
Copy Table
1
2
3
4
use databasename
select * into table2 from table1 --make a copy named table2 from table1
select * into table3 from table1 where column7 = 'value4' --add a fliter for data to copy.
go
近期评论