MS SQL - Database Programming
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (360 KB, 30 trang )
MS SQL Server
MS SQL Server
DATABASE PROGRAMMING
DATABASE PROGRAMMING
Index
Index
Session 1: Database Management
Session 2: Database Normalisation
Session 3: Joins & View
Session 4: Stored Procedure, Union & SubQuery
Session 5: SQL Operator & Functions
Session 6: Trigger & Curror
Appendex: MS SQL Administrator
SQL Server Overview
SQL Server Overview
What Can SQL do?
What Can SQL do?
Execute queries against a database
Retrieve data from a database
Insert records in a database
Updata records in a database
Delete records in a database
Create new databases
Create new tables in database
Create stored procedures in a database
Create views in a database
Set permissions on tables, procedures, and views
Index
Index
Session 1: Database Management
Session 1: Database Management
Session 2: Database Normalisation
Session 3: Joins & View
Session 4: Stored Procedure, Union & SubQuery
Session 5: SQL Operator & Functions
Session 6: Trigger & Curror
Appendex: MS SQL Administrator
1. Creating Databases
The SQL Server Management Studio (SSMS) graphical user interface
can be used to create and configure new databases.
1. Creating Databases (Continue)
Using CREATE DATABASE command
•
Almost all the possible arguments to the CREATE DATABASE
command have default values so that it's possible to create a database
using a simple form of CREATE DATABASE:
CREATE DATABASE <Database Name>
•
Other default values apply to the new database and its files. If the LOG
ON clause is not specified, but data files are specified, a log file will be
automatically created with a size that is 25 percent of the sum of the
sizes of all data files.
•
For the files, if the MAXSIZE clause isn't specified, the file will grow
until the disk is full. (In other words, the file size is considered
unlimited.) Both SIZE and MAXSIZE can be specified in units of MB
(the default) or KB. A value of 0 for FILEGROWTH indicates no
growth. The default value is 10 percent, and the minimum value is 64
KB.
1. Creating Databases (Continue)
CREATE DATABASE Example
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\mssql7\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\mssql7\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\mssql7\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
2. Creating Tables
What is a Tables
The tables is the most important part of a relational database.
Tables are the storage location for all information within a database.
Each table holds a single set of structured informatinon.
Columns and Rows
A table is organised into columns and rows. One named column is
defined in a table for each individual piece of data that may be stored
Types of Data
In SQL Server, each column, local variable, expression, and
parameter has a related data type. A data type is an attribute that
specifies the type of data that the object can hold: integer data,
character data, monetary data, date and time data, binary strings...
2. Creating Tables (Continue)
o
The simplest way to use the CREATE TABLE command is to provide
a table name and a comma-separated list of column specifications. This
is shown in the syntax below:
CREATE TABLE table-name
(
column-specification1,
column-specification2,
...
column-specificationN
)
o
Each column specification describes a column that will be created
within the table. Here we can provide a name for the column and
specify the column type and size to use.
2. Creating Tables (Continue)
o
We can also determine if the column should permit the storage of null
values by appending either "NULL" or "NOT NULL" to the
specification. These three items are separated by space characters,
making the syntax for a column specification as follows:
column-name data-type [NULL | NOT NULL]
The use of "NULL" is optional. If omitted, a column will be nullable
by default.
3. Data Manipulation
The INSERT Statement
The simplest form of the INSERT statement is used when adding a full
row of data to a table. The syntax for the command is as follows:
INSERT INTO table-name VALUES (value1, value2, ..., valueN)
Updating Data
The basic syntax of the statement is as follows:
UPDATE table-name
SET column1 = value1,
column2 = value2,
...,
columnN = valueN
Using this syntax, every row in the specified table is updated
3. Data Manipulation (Continue)
o
The WHERE Clause
Although the above syntax for updating information is useful, more
often you will want to update a single item or a set of rows that meet a
given criteria. This is achieved by adding a WHERE clause to the
statement. The WHERE clause contains a condition that is checked for
every row in the table. If the condition is met, the row is updated. If not, the
row remains unchanged.
Example
UPDATE Engineers
SET HourlyRate = HourlyRate + 0.25
WHERE HourlyRate >= 29.50
This statement increases the hourly rate for all engineers that currently have
a rate of £29.50 or greater. If the engineer is paid less than £29.50, their rate
remains the same.
