Structured Query Language--2x

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

31 Οκτ 2013 (πριν από 3 χρόνια και 11 μήνες)

129 εμφανίσεις

SQL



It can …


execute
queries against a database


retrieve
data from a database


insert
records in a database


update
records in a database


delete
records from a database


create
new databases


create
new tables in a database


create
stored procedures in a database


create
views in a database


set
permissions on tables, procedures, and
views

SQL



To

build

a

web

site

that

shows

some

data

from

a

database,

you

will

need

the

following
:



An

RDBMS

database

program

(i
.
e
.

MS

Access,

SQL

Server,

MySQL)


A

server
-
side

scripting

language,

like

PHP

or

ASP


SQL


HTML

/

CSS


SQL in
Web
Site




Relational

Database

Management

System
.


It

is

the

basis

for

SQL,

and

for

all

modern

database

systems

such

as

MS

SQL

Server,

IBM

DB
2
,

Oracle,

MySQL,

and

Microsoft

Access
.


The

data

in

RDBMS

is

stored

in

database

objects

called

tables
.


A

table

is

a

collection

of

related

data

entries

and

it

consists

of

columns

and

rows
.


RDBMS




Data Manipulation
Language


The
query and update commands form the DML
part of SQL
:


SELECT

-

extracts data from a database


UPDATE

-

updates data in a database


DELETE

-

deletes data from a database


INSERT INTO

-

inserts new data into a database


SQL
-

DML




Data

Definition


Language


part

of

SQL

permits

database

tables

to

be

created

or

deleted


defines

indexes

(keys),

specifies

links

between

tables,

and

imposes

constraints

between

tables


SQL
-

DDL




DDL
statements:


CREATE DATABASE

-

creates a new database


ALTER DATABASE

-

modifies a database


CREATE TABLE

-

creates a new table


ALTER TABLE

-

modifies a table


DROP TABLE

-

deletes a table


CREATE INDEX

-

creates an index (search key)


DROP INDEX

-

deletes an index


SQL
-

DDL



Database
Tables

Persons

Table name

Tuples or rows

Attribute names




Schema

of the table


The table name and its attributes


Persons (
P_Id
,
LastName
,
FirstName
, Address, City)



Key



An attribute whose values are unique


Persons (
P_Id
,
LastName
,
FirstName
, Address, City)


Tables



Tables


Atomic types:


Characters: CHAR(20), VARCHAR(50)


Numbers: INT, BIGINT, SMALLINT, FLOAT


Others: MONEY, DATETIME, …



Every attribute must have an atomic type


Hence tables are
flat



Tables


A tuple = a record


Restriction: all attributes are of atomic type



A table = a set of tuples


Like a list…


…but it is
unorderd
:

no
first()
, no
next()
, no
last()
.




Syntax


SELECT
column_name
(s) FROM
table_name


SELECT * FROM
table_name


SELECT Statement



Simple SQL Query

PName

Price

Category

Manufacturer

Gizmo

$19.99

Gadgets

GizmoWorks

Powergizmo

$29.99

Gadgets

GizmoWorks

SingleTouch

$149.99

Photography

Canon

MultiTouch

$203.99

Household

Hitachi

SELECT

*

FROM

Product

WHERE

category=‘Gadgets’

Product

PName

Price

Category

Manufacturer

Gizmo

$19.99

Gadgets

GizmoWorks

Powergizmo

$29.99

Gadgets

GizmoWorks

“selection”



Simple SQL Query

PName

Price

Category

Manufacturer

Gizmo

$19.99

Gadgets

GizmoWorks

Powergizmo

$29.99

Gadgets

GizmoWorks

SingleTouch

$149.99

Photography

Canon

MultiTouch

$203.99

Household

Hitachi

SELECT

PName, Price, Manufacturer

FROM

Product

WHERE

Price > 100

Product

PName

Price

Manufacturer

SingleTouch

$149.99

Canon

MultiTouch

$203.99

Hitachi

“selection” and

“projection”



Notation

Product(
PName
, Price, Category, Manfacturer)

Answer(PName, Price, Manfacturer)

Input Schema

Output Schema

SELECT

PName, Price, Manufacturer

FROM

Product

WHERE

Price > 100




Syntax


SELECT
DISTINCT
column_name
(s
)


FROM

table_name


SELECT DISTINCT



PERSON





SELECT DISTINCT City FROM Persons

SELECT DISTINCT



Eliminating Duplicates

SELECT

DISTINCT

category

FROM

Product

Compare to:

SELECT

category

FROM

Product

Category

Gadgets

Gadgets

Photography

Household

Category

Gadgets

Photography

Household



Details


Case insensitive:


Same: SELECT Select select


Same: Product product


Different: ‘Seattle’ ‘seattle’



Constants:


‘abc’
-

yes


“abc”
-

no




Syntax


SELECT
column_name
(s
)


FROM

table_name


WHERE
column_name

operator value


WHERE Statement



PERSONS





SELECT * FROM Persons WHERE City=‘
Sandnes


SELECT DISTINCT



Text Fields

SELECT * FROM Persons WHERE
FirstName
=“Anne”

SELECT * FROM Persons WHERE
FirstName
=Anne

FOR TEXT VALUES:

SELECT * FROM Persons WHERE Year=1965

SELECT * FROM Persons WHERE Year=‘1965’

FOR NUMBER VALUES:




The following operators can be used:


WHERE Statement



AND operator


displays
a record if both the first condition and the
second condition is
true


OR
operator


displays a record if either the first condition or the
second condition is true.

AND & OR Operators



PERSONS





SELECT * FROM Persons

WHERE
FirstName
='
Tove
'

AND
LastName
='
Svendson
'

AND operators



PERSONS





SELECT * FROM Persons

WHERE
FirstName
='
Tove
'

OR
FirstName
='Ola'

OR operators



PERSONS





SELECT * FROM Persons WHERE

LastName
='
Svendson
'

AND (
FirstName
='
Tove
' OR
FirstName
='Ola')

Combining AND & OR operators




used to sort the result
-
set by a specified
column


sort the records in ascending order by default
.



Syntax


SELECT
column_name
(s)


FROM
table_name


ORDER
BY
column_name
(s) ASC|DESC

ORDER BY Statement



PERSONS





SELECT * FROM Persons

ORDER BY
LastName

ORDER BY operators



PERSONS





SELECT * FROM Persons

ORDER BY
LastName

DESC

ORDER BY operators



Syntax


The first form doesn't specify the column names where
the data will be inserted, only their values
:


INSERT INTO
table_name


VALUES
(value1, value2, value3
,...)



The second form specifies both the column names and the
values to be inserted
:


INSERT INTO
table_name

(
column1, column2, column3,...)


VALUES
(value1, value2, value3,...)

INSERT INTO Statement



PERSONS





INSERT INTO Persons

VALUES (4,'Nilsen', 'Johan', '
Bakken

2', 'Stavanger')

INSERT INTO statements



PERSONS





INSERT INTO Persons (
P_Id
,
LastName
,
FirstName
)

VALUES (5, '
Tjessem
', '
Jakob
')

INSERT INTO statements

specified columns




used
to update existing records in a table.


Syntax


UPDATE
table_name


SET
column1=value, column2=value2,...


WHERE
some_column
=
some_value

UPDATE Statement



PERSONS






UPDATE Persons

SET Address='
Nissestien

67', City='
Sandnes
'

WHERE
LastName
='
Tjessem
' AND
FirstName
='
Jakob
'

UPDATE statements



PERSONS






UPDATE Persons

SET Address='
Nissestien

67', City='
Sandnes
'

Note:




is used to delete rows in a
table


Syntax


DELETE FROM
table_name


WHERE
some_column
=
some_value


It

is

possible

to

delete

all

rows

in

a

table

without

deleting

the

table
.



This

means

that

the

table

structure,

attributes,

and

indexes

will

be

intact
:


Syntax


DELETE FROM
table_name

or


DELETE
* FROM
table_name

DELETE Statement



PERSONS






DELETE FROM Persons

WHERE
LastName
='
Tjessem
' AND
FirstName
='
Jakob
'

DELETE statements