introducing ms sql_server
TRANSCRIPT
www.elarion.com
Microsoft SQL Server
LARION_TDT@Internship_03
Never stop improving quality
2
Contents Introduction
● Relational Database
● Database management system
● Microsoft SQL server
● Structures of Microsoft SQL server
● How to create index for each record
● Backup, Restore Database
● Stored Procedure, Trigger, Function
● View
● Supporting tools for MS SQL server
● Demo
3
Relational Database (1 of 4)
Definition
● Relational Database is database that all data are
established many tables to have relationship each
other.
● Each table consists of many columns and many rows
that each column is usually called a register,each row
is described an attribute.
4
Relational Database (2 of 4)
The main components
● The main components :
Table: each table stores information of an object
outside real life.
Primary key: a collection of one column or many
columns inside tables that it is not permit null data and
is only one.
Foreign key: a collection of one column or many
columns inside tables that they are a primary of other
tables and their data can be permitted null
5
Relational Database (3 of 4)
Example 1:
6
Relational Database (4 of 4)
Example 2:
Database Management System(1 of 3)
7
● Popular Database Management Systems
MS SQL server
MySQL
Oracle
PostgreSql
DB2
…
8
Database Management System(2 of 3)
Definition
● Database management system is a
software to permit users to create,
control and access into database.
● Database management system has
maintained consistency of database
during its cycle life.
9
Database Management System (3 of 3)
Function
● The main functions of database management system
Database definition
Database construction
Database manipulation: add , delete...
Database administrator: security...
Database protection: backup, restore
10
Mircosoft SQL server(1 of 3)
● MS SQL Server is a database management
system that it is established and developed by
Microsoft based on Transact-SQL method in
order to exchange data between Client and MS
SQL Server.
11
Mircosoft SQL server(2 of 3)
Adaptation ● MS SQL Server is optimized in order to run Very
Large Database Environment (VLDE) even
though Tera-Byte, especially for serving user
thousands
● MS SQL Server can collaborate with other
servers such as Microsoft Internet Information
Server (IIS), Oracle...
12
Mircosoft SQL server (3 of 3)
Versions ● Versions of MS SQL Server
Enterprise Edition
Developer Edition
Standard Edition
Workgroup Edition
SQL Server Express Edition
13
Mircosoft SQL server(1 of 3)
Structure ● One of the most usual structures MS SQL Server
Client / Server Architecture
N-tier Architecture
14
Structure of MS SQL server(2 of 3)
●Client / Server Architecture
15
●N-tier Architecture
Structure of MS SQL server (3 of 3)
16
How to create index for each record(1 of 2)
● Index is used to supporting and improving
for accessing more quick to data flows base
on value of one or many columns.
● Two forms:
Clustered index
Non-clustered index
● Syntax:
CREATE [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name(column_name [, column_name]...)
● For example for creating non-clustered index on
MADV of NHANVIEN columns:
CREATE NONCLUSTERED INDEX idx_nhanvien_madv
ON nhanvien(madv)
17
How to create index for each record(2 of 2)
18
Backup, Restore Database(1 of 7)
Backup
What is Backup Database?
● That is the activity of copying files or
databases from SQL server to another
storage device.
● Having two backup database forms:
By backup database commands
By direct action in your SQL Server
19
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text'| @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text'| @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ =percentage ] ]
]
Backup, Restore Database(2 of 7)
Backup
Syntax of Backup Database Commands
20
° BLOCKSIZE: automatic parameter
° DIFFERENTIAL: differential backup is only
necessary change datas
° EXPIREDATE: expiration of backup day
° FORMAT/NOFORMAT: destroying existing datas
or not
° INIT/NOINIT: replace datas or not
° DESCRIPTION: details of description
° STATS: appearance of programbar
Backup, Restore Database(3 of 7)
Backup Description
21
For example backup database
BACKUP DATABASE QLNGK
TO DISK= ‘F:\ ngk.bak’
WITH
DESCRIPTION = ‘BACK UP’,
STATS
GO
Backup, Restore Database(4 of 7)
Backup
22
Result
Backup, Restore Database(5 of 7)
Backup
23
Syntax
RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH {
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var } ]
| , <general_WITH_options> [ ,...n ] | , <replication_WITH_option>
| , <change_data_capture_WITH_option> | , <service_broker_WITH options>
| , <point_in_time_WITH_options—RESTORE_DATABASE>
} [ ,...n ]
]
[;]
Backup, Restore Database(6 of 7)
Restore
24
For example:
RESTORE DATABASE users
FROM DISK='C:\backupusers.BAK'
WITH RECOVERY
Backup, Restore Database(end)
Restore
25
Stored Procedure, Trigger (1 of 9)
Stored Procedure What is Stored Procedure ?
● SP (Stored Procedure) is the name given to a
group of SQL statement is created and stored in
the server database.
● SP allows transmission parameters, and may be
called by many clients over the network with
different parameters. When SP is changed, all
clients will automatically receive the new
version, because the SP is stored at the Server,
not Client.
26
Creating procedure
CREATE PROCEDURE procedure_name [;number]
[([parameter1] [,parameter2]...[parameter255])]
AS sql_statements
Stored Procedure, Trigger (2 of 9)
Stored Procedure
27
For example
CREATE PROCEDURE sp_users_insert
(@firstname varchar(50),
@lastname varchar(50),
@birthday date,
@groupid int)
AS
BEGIN
if exists(select groupid from groups where groupid = @groupid)
BEGIN
insert into users(firstname, lastname, birthday, groupid)
values(@firstname,@lastname,@birthday,@groupid)
END
END
Stored Procedure, Trigger (3 of 9)
Stored Procedure
28
● Trigger is a data type that is specific saved.
Otherwise, trigger to help ensure data integrity.
● A table has maximum 3 trigger types:
Update trigger
Insert trigger
Delete trigger
● For example: employer salary is not permitted
higher than his top-banana
Stored Procedure, Trigger (4 of 9)
Trigger
29
Syntax:
CREATE TRIGGER trigger_name
ON table_name | view_name
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF}{ [INSERT] [,] [UPDATE ] [,]
[DELETE] }
AS sql_statements
Stored Procedure, Trigger (5 of 9)
Trigger
30
CREATE TRIGGER groups_users_delete
on groups
for delete
as BEGIN DECLARE @groupid int;
Set @groupid=(Select groupid from DELETED);
delete from users where groupid = @groupid
END
Stored Procedure, Trigger (6 of 9)
Trigger
31
Function is database object similar procedures .
However,the difference between procedures and
function that function returns to a value via its
function name while procedures do not.
Stored Procedure, Trigger (7 of 9)
Function
32
Syntax CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ]
[ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] } [ ,...n ]S
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Stored Procedure, Trigger (8 of 9)
Trigger
33
For example CREATE FUNCTION f_users_tong()
RETURNS @bangthongke TABLE
(groupid int,groupname varchar(50),tonguser INT)
AS
BEGIN INSERT INTO @bangthongke
SELECT groups.groupid,groups.groupname,COUNT(userid)
FROM (groups INNER JOIN users
ON groups.groupid = users.groupid)
GROUP BY groups.groupid,groups.groupname
RETURN
END
Stored Procedure, Trigger (end)
Trigger
34
View(1 of 4)
Definition
● View is a method that we can watch at data into
one or many tables of database.
● View is a virtual screen that it is created as a union
of rows and columns by one or many different
tables.
● Decreasing more complex in database
35
View(2 of 4)
Syntax:
CREATE VIEW COMMAND used to create view:
CREATE VIEW <virtual_name_table>
AS
<select_statement>
36
View(3 of 4)
For example:
CREATE VIEW DSSV
AS
SELECT masv, hodem, ten,
DATEDIFF (YY, ngaysinh,GETDATE())
AS tuoi, tenlop
FROM sinhvien, lop
37
View(end)
Result
38
Supportting tools for MS SQL Server
One of the most popular supporting tools for MS
SQL sever is SQL Assistant .You can download
from link
http://www.softpedia.com/progDownload/SoftTree-SQL-Assistant-Download-92428.html
39
Demo
● How to use Diagram
● Decentralized
● Backup
● Restore
● Function
● Store Procedure
● Trigger
40
Reference
● Document of LARION_TDT@internship_02 Courses
● Website: www.msdn.microsoft.com
41
Question and Answer
?
42
Thank you!