Learning SQL Server: Database,Table and Column

Posted by Riino on March 2, 2019 T-SQL Database Completed



T-SQL : At Beginning

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:

use master
--Codes here

Create a database:

use master
create database Databasename
  (name= test1_dat,filename='E:\Microsoft SQL Server\database\databasename.mdf') --main file
log on
  (name='databasename_log',filename='E:\Microsoft SQL Server\database\test1log.ldf')--log file

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:

use master
create database test2

filename='E:\Microsoft SQL Server\database\prim_sub1_dat.mdf',--Main file
size = 6mb,
maxsize = 20mb,
filegrowth = 20%),
(name = prim_sub2,
filename='E:\Microsoft SQL Server\database\prim_sub2_dat.ndf',--A sub-file
size = 6mb,
maxsize =20mb,

filegroup grouptest1  --A group of sub-files , which contains 2 files.
(name = grouptest1,
filename='E:\Microsoft SQL Server\database\group1_sub1_dat.ndf',
size = 6mb,
maxsize =20mb,
(name = group1_sub2,
filename='E:\Microsoft SQL Server\database\group1_sub2_dat.ndf',--log file
size = 6mb,
maxsize = 20mb,
filegrowth = 5mb)

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:

use master
exec sp_detach_db databsename , ture


use master
create database databasename
on(filename = 'PATH') --path of the MAIN FILE
for attach;

Create Backup:

  1. Backing up a whole database :

    back up database databasename
      to disk = 'd:\backup\full.bak'
  2. Backing up a whole database :(Rewrite every files)

    back up database databasename
      to disk = 'TARGET PATH' with init
  3. Backing up specific files or filegroups:(Changed file compared with last whole database only)

    back up database databasename
      to disk = 'TARGET PATH' with differential
  4. Creating a backup in many files:

    backup database databasename 
    to disk='d:\backup\part1.bak',disk='d:\backup\part2.bak'
  5. Backing up the log.

    backup log databasename To Disk='PATH'

    or you can use this to keep complete log info.

    backup log databasename To Disk='PATH' with No_Truncate

    or if you want to keep tail log:

    backup log databasename To Disk='PATH' with norecovery

Restore database:


restore database databasename from disk = 'd:\backup\full.bak'

Delete database (Delete file):

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)

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

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

use databasename
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.

use databasename
insert into tablename values('value1','value2','value3',6)

When updating data:

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:

use databasename
delete from tablename where column7 = 'value4'

Copy Table

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.