Introduction to SQL

southdakotascrawnyΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

252 εμφανίσεις



Page
1




IST3
59:

I
NTRODUCTION TO
SQL

T
ABLE OF
C
ONTENTS

Topic
-

Introduction to SQL

................................
................................
................................
.............

2

Learning Objectives

................................
................................
................................
.....................

2

SQL Goals

................................
................................
................................
................................
..

2

Part 1: SQL: What it is and what it’s not.

................................
................................
.....................

3

SQL: Tow
-
may
-
tow, Tow
-
mah
-
tow?

................................
................................
........................

3

SQL: Not really a programming language

per
-
se

................................
................................
..

4

SQL: The Not
-
So
-
Standard, Standard

................................
................................
......................

4

ANSI SQL Standards

................................
................................
................................
..................

4

Popular DBMS Software and Their SQL Implementations

................................
.......................

5

A
word about SQL Implementation

................................
................................
.........................

5

Part 2: SQL Language Internals

................................
................................
................................
....

5

Case Sensitivity

................................
................................
................................
.........................

5

Naming conventions
................................
................................
................................
.................

5

Comments

................................
................................
................................
................................

6

Data Definition
Language (DDL)

................................
................................
...............................

7

Data Manipulation Language (DML)

................................
................................
........................

7

Data Control Language (DCL)

................................
................................
................................
...

7

Data Transaction Language (DTL)
................................
................................
.............................

7

Part 3: Syntax of SQL commands

................................
................................
................................
.

7

Table Creation

................................
................................
................................
..........................

7

SQL Data Types on Microsoft SQL Server
................................
................................
.................

8



Page
2




Table Manipulation

................................
................................
................................
..................

8

Table Removal

................................
................................
................................
..........................

9

Add data to a table

................................
................................
................................
...................

9

Read data from a table

................................
................................
................................
.............

9

Update data in a table

................................
................................
................................
..............

9

Remove data from a table

................................
................................
................................
........

9

SQL Operators

................................
................................
................................
..........................

9

Appendix: Other important things to know

................................
................................
..............

10

dbo? Oh my!

................................
................................
................................
...........................

10

INFORMATION_SCHEMA
................................
................................
................................
........

10



T
OPIC

-

I
NTRODUCTION TO
SQL


L
EARNING
O
BJECTI VES

In this learning unit we will explore SQL, the database language used to implement the
relational model i
n popular DBMS products. Upon completion of this learning unit you should
be able to:



Describe and use basic SQL commands



Explain how and why SQL is used, and why its important



Compare and contrast DML and DDL



Use SQL commands to create metadata structures

and perform CRUD operations.

SQL

G
OALS

Our SQL goals for this learning unit are to:

1.

Understand how to manipulate tables with the CREATE TABLE, ALTER TABLE, and DROP
TABLE commands



Page
3




2.

Understand bas
ic use of the “fantastic four”

DML commands: INSERT, SELECT, UPDATE
and DELETE

P
ART
1:

SQL:

W
HAT IT IS AND WHAT I
T

S NOT
.

SQL is a popular computer language for data a
nd meta
-
data management in a relational DBMS.
With SQL you can create database structures, such as tables, constraints, and indexes and then
populate the structures with data programmatically. For the most part, SQL is a comprehensive
language for controll
ing and manipulating all aspects of the DBMS. As part of the
implementation model, SQL is used to create the internal and external models from a logical
model. So if the conceptual model represents the ideas for a database, and the logical model
represents

the actual blueprint for it, then SQL is the tool used to build the actual database
implementation of the SDLC (aka internal and external models).



SQL:

T
OW
-
MAY
-
TOW
,

T
OW
-
MAH
-
TOW
?

First, is SQL pronounced “Ess
-
Queue
-
Elle”

or


Sequel
”?

According to

ANSI

(
http://www.ansi.org
)
the pronunciation it is the former, S
-
Q
-
L, but
I’
ve heard bo
th used both
ways in practice. I suggest just accepting there is no right or wrong way to pronounce it, and
just move on.





Page
4




SQL:

N
OT REALLY A PROGRAMM
ING LANGUAGE

PER
-
SE

SQL as a programming language is much different from C, Java
or Visual Basic
. The latter are
general purpose procedural programming languages and you can write almost anything in
them.
SQL is a set
-
based
declarative compu
ter language

that is
domain

specific
.

Declarative
mean
s

you focus on

what you need to do


rather th
an on

how you need to do it

.

Domain
specific

implie
s

the language solves a focus
ed set of problems.

SQL
is
notoriously easily to learn, yet difficult to master.

This is most likely because the
language is not well suited to solving more complicated
problems
.
Accomplishing some tasks in
SQL are
so
downright

trivial

you’ll wonder if yo
u’re even “programming”
, while others require
you re
-
learn what you may already know and understand about computer progra
mming in
order to accomplish the task. Most often you need to re
-
think your logic to operate on sets of
data versus
the individual items of data.

SQL:


T
HE
N
OT
-
S
O
-
S
TANDARD
,

S
TANDARD

SQL was developed by Donald D. Chamberlin and Raymond F. Boyce at IBM in the 1970

s. Their
concept w
as based upon E. F. Codd
’s

paper on relational design
, which we introduced earlier
on
. SQL was adopted as a standard by ANSI in 1986, however by that time several DBMS
implementations were already available, notably ones from IBM and Oracle. Since that dat
e
SQL has always been driven by the market leaders as opposed to the actual standard. In
addition, customers with production databases implemented in their DBMS of choice are often
reluctant to change database design to conform to the standard, forcing ven
dors to maintain
backwards compatibility. DBMS vendors are in direct competition with each other for your
DBMS dollars, and they know once you select a product your odds of switching low. As such,
standards adherence takes a back seat to adding more whiz
-
b
ang features, providing stability
and good performance. The end result is a not
-
so
-
standard standard.

ANSI

SQL

S
TANDARDS

Year

Name

Comments

1986

SQL
-
86

First Standard, also called SQL
-
87

1989

SQL
-
89 (SQL1)

Minor revision, the baseline of SQL.

1992

SQL
-
92 (SQL2)

New Join syntax added

1999

SQL
-
99 (SQL3)

Triggers, object
-
oriented features

2003

SQL:2003

XML support, Windows functions, cursors, identity columns

2006

SQL:2006

More XML support,
XQuery

support




Page
5




P
OPULAR
DBMS

S
OFTWARE AND
T
HEI R
SQL

I
MPLEMENTATIONS

DBMS Name

Maintainer

License Model

Oracle Database (10g)

Oracle

Proprietary

DB2

IBM

Proprietary

SQL Server 2008

Microsoft

Proprietary

MySQL 5

Sun / MySQL AB

Open
-
Source

PostgreSQL

PostgreSQL GDG

Open Source

Firebird

Firebird Foundation

Open Source


A

WORD ABOUT
SQL

I
MPLEMENTATION

In this course, we will use Microsoft SQL server as our primary DBMS for learning about SQL
and the internal and external data models. It is important to understand that while we use MS
SQL server for our examples, the focus of this course is not SQL serve
r training, but database
design and management principles in general.


To get the most mileage out of our learning, I will make sure to differentiate between those
items that are specific to Microsoft SQL server and those which are part of the SQL standar
d. In
addition, for any proprietary information, we will discuss how it is implemented in other DBMS
products.

P
ART
2:

SQL

L
ANGUAGE
I
NTERNALS

C
ASE
S
ENSITIVITY

In SQL keywords are case insensitive, thus the SELECT, SelecT and select commands are all the
same command. However, the data and the object definitions and data within the database can
be set to either

case
-
sensitive or case
-
insensitive based on the default collation on the
database. In a case
-
sensitive collation, tables named Employee and employee are two separate
tables! Yikes! It goes without saying that caution must be exercised, which is why you ne
ed:

N
AMING CONVENTIONS

Naming conventions or

coding style


are a set of rules for naming programming objects. In
SQL, we use naming conventions to identify tables, columns, constraints
and other objects.
While not actually a part of SQL itself, naming conventions

become imperative as database
designs get complex and there are multiple developers with hands in the pot. Many a battle has


Page
6




been waged in the corporate, open source and interne
t communities over naming conventions,
especially over the use of
Hungarian Notation
.

MY OPINION OF NAMING CONVENTIONS IS THAT ANY ONE IS GOOD AS LONG AS Y
OU ADOPT
AND ADHERE TO IT. THE POINT OF NAMING CONVENTIONS IS STANDARDIZATION FOR
CONSISTENCY AND READABILITY.

That being said here are some naming conventions I use in SQL:

What

The Fudge Convention

Rationale for use

All object
names

Use lower case
letters only

Disambiguation e.g. Employee vs. employee

All object
names

Use underscore in place of
SPACE

Avoids the need to place brackets around identifiers.

Tables

Pluralize

Since a table contains many 'things' it should be plural
eg. Employees

Tabl
es

qualify with logical schema

When learning SQL, helps disambiguate different tables
in the same database eg. fudgemart_customers vs.
fudgeflix_customers

Column
names

Qualify with table name

When learning SQL, helps you define scope
vendor_zipcode vs.
employee_zipcode

constraints

pk=primary key, fk=foreign
key u=unique, ck=check,
i=index

Disambiguation of constraints ck_vendor_zipcode vs.
fk_vendor_zipcode


C
OMMENTS

The ability to document w
hat we do is truly what separates us from the animals, so use it. To
place a comment in
-
line, use double
-
dashes
-

-
, to place a multi
-
line comment use /* */. SQL
Comments are ignored by the SQL interpreter, but useful to people! You may even find them
usef
ul later on to yourself if you need to correct your own work or find an error.


For example:


--

This is a one line comment


/* This comment

spans several



Page
7




lines */


D
ATA
D
EFINITI ON
L
ANGUAGE
(DDL)

DDL commands are used to create, modify and delete the meta
-
data structures of the DBMS,
such as tables and indexes. There are three main DDL commands:



CREATE

-

make a new object



ALTER

-

manipulate an existing object



DROP

-

delete an existing object, such as a table.


D
ATA
M
ANIPULATI ON
L
ANGUAGE
(DML)

DML commands manage the data in the tables. There are 4 DML commands, corresponding to
each of

the 4 CRUD operations:



INSERT INTO

-

creates or adds data into a table.



SELECT

-

reads data from a table



UPDATE
-

updates data in a table



DELETE
-

removes data from a table.


D
ATA
C
ONTROL
L
ANGUAGE
(DCL)

The DCL commands process the authorization aspects of the database (who has access to
what). We will learn more about DCL in the database security learning unit.



GRANT

-

Add rights.



REVOKE

-

Remove rights


D
ATA
T
RANSACTION
L
ANGUAGE
(DTL)

The DTL commands allow the user to programmatically control and manage how data is
manipulated in the database. We will learn more about DTL and transactions in general

during
the database transactions and concurrency control learning unit.

P
ART
3:

S
YNTAX OF
SQL

COMMANDS

T
ABLE
C
REATION



Page
8




CREATE TABLE tablename (


co
l
name datatype
[
NOT
]

NULL



,
...
n



,CONSTRAINT name PRI
MA
RY KEY(pkcol
name,

..n
)


,
CONSTRAINT name CHECK (expression)
,


,
CONSTRAINT name UNIQUE(ucolname,
...n
)
,


,
CONSTR
AINT name FOREIGN KEY(fkcolname
,
...
n
)


REFERENCES fktablename(pkcol
,
...
n
)]

)

SQL

D
ATA
T
YPES ON
M
ICROSOFT
SQL

S
ERVER

Data Type

SQL Std?

Description

char(
length
)

Y

Fixed
-
length character string of size
length


varchar(
length
)

Y

Variable
-
length character string of size
length

text

Y

Variable
-
length character of up to 2GB, not indexable

numeric(
precision
,
scale
)

Y

Fixed precision and scale numbers.

decimal(
precision
,
scale
)

Y

Same as numeric

smallint

Y

16
-
bit integer (2 bytes)

int

Y

32
-
bit integer (4 bytes)

bigint

Y

64
-
bit integer (8 bytes)

float

Y

floating
-
point decimal

bit

Y

One bit. Used for true/false and yes/no values

uniqueidentifier

N

A globally
-
unique 16
-
byte idenfier

rowversion

N

A timestamp used for replication

money

N

synonym for
decimal(18,4)


For a complete list of data types available in Microsoft SQL Server including samples of their
usage, visit the SQL Server books online:
http://msdn.microsoft.com/en
-
us/library/ms187752.aspx

T
ABLE
M
ANIPULATI ON

ALTER TABLE tablename

{ADD colname datatype colsettings}

| {ADD CONSTRAINT
constraintinfo}

| {ALTER COLUMN colname colsettings}

| {DROP COLUMN|CONSTRAINT name}




Page
9




T
ABLE
R
EMOVAL

DROP TABLE tablename

A
DD DATA TO A TABLE

INSERT INTO tablename (

colname
, ...n
)

VALUES (data
, ...n
)

R
EAD DATA FROM A TABL
E

SELECT {colname
, ..n

| * }

FROM tablename

WHERE condition

ORDER BY colname [, ..n
]

U
PDATE DATA IN A TABL
E

UPDATE tablename SET

colname = data

, ..n

WHERE condition

R
EMOVE DATA FROM A TA
BLE

DELETE FROM tablename

WHERE condition

SQL

O
PERATORS

These op
erators are used to test conditions for the WHERE clause and CHECK constraints

Operator

Type

Purpose

Example Usage

+

Arithmetic

Addition, string concatenation

a+b

-

Arithmetic

Subtraction

a
-
b

*

Arithmetic

Multiplication

a*b

/

Arithmetic

Division

a/b

>

Comparative

Greater than

a>b

<

Comparative

Less than

a<b

=

Comparative

Equal to

a=b



Page
10




<>

Comparative

Not equal to

a<>b

>=

Comparative

Greater than or equal to

a>=b

<=

Comparative

Less than or equal to

a<=b

between

Comparative

Range checking

a
between c and d

is null

Null

Check for null

a is null

is not null

Null

Check for not null

a is not null

AND

Boolean

Logical AND
-

both must be true

a AND b

OR

Boolean

Logical OR
-

one must be true

a OR b

NOT

Boolean

Negation

NOT a

exists

Set

check
for existence

exists SQL

in

Set

check for membership

a in (1,2,3)

( )

Misc.

Order of operations

(a > b) OR (c < d)


For a complete list of SQL operators supported by Microsoft SQL Server, visit this section of the
SQL Server books online:
http://msdn.microsoft.com/en
-
us/library/ms174986.aspx

A
PPENDIX
:

O
THER IMPORTANT THING
S TO KNOW

DBO
?

O
H MY
!

In Microsoft SQL Server, each DDL object can be prefixed with a name followed by a period.
This represents the owner of the object
, and by default is the owner dbo or database owner.
You can actually have two object names in a database with different owner
s, such as
wang359.
employees and dbo.employees, which would be considered different objects! Oh my!

INFORMATION_
SCHEMA

The question you might be asking yourself at this point is if there

s a way to list the tables in a
database or list the columns in a table? Well, every DBMS implements the meta
-
data in its own
i
nternal structures of tables, known as system tables. Yes, this is the ultimate in dogfooding.
You can query the system tables using the INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS system objects.