Chapter 4: Advanced SQLChapter 4: Advanced SQLChapter 4: Advanced SQLChapter 4: Advanced SQL
Contents
Contents
• SQL Data Types and Schemas
•
Integrity Constraints
Integrity Constraints
• Authorization
Ti
•
T
r
i
gger
•S
Q
L Function
Q
• Stored Procedure
E b dd d SQL
•
E
m
b
e
dd
e
d SQL
Chapter4 Advanced SQL
2
B ilt
i Dt T i SQL
B
u
ilt
-
i
n
D
a
t
a
T
ypes
i
n
SQL
••datedate: Made up of year-month-day in the
format yyyy
-
mm
-
dd
format yyyy
-
mm
-
dd
••timetime: Made up of hour:minute:second in
the format hh:mm:ss
dd
tt
‘
2005
07
27
’
titi
‘09 00 30’
dd
a
t
ea
t
e
‘
2005
-
07
-
27
’
ti
me
ti
me
‘09
:
00
:
30’
Insert intoInsert into
student
(name department birthday)
Insert
into
Insert
into
student
(name
,
department
,
birthday)
values values (smith, SE, datedate ‘2009-08-23’)
Chapter4 Advanced SQL
3
Built
in Data Types in SQL
Cont.
Built
-
in Data Types in SQL
ti tti t
dt l ti f d
••
ti
mes
t
amp
ti
mes
t
amp:
d
a
t
e
p
l
us
ti
me
o
f d
ay
••intervalinterval:
p
eriod of time
p
– Subtracting a date/time/timestamp value
f
rom another
g
ives an interval value
g
– Interval values can be added to
date/time/timestam
p
values
p
timestamp timestamp ‘2005-7-27 09:00:30’
intervalinterval
‘
2years3months
’
update update
license
SetSet
expireDay
=
expireDay
+
intervalinterval
‘
2years6months’
intervalinterval
2
years
3
months
Chapter4 Advanced SQL
4
Set
Set
expireDay
expireDay
+
interval
interval
2
years
6
months
WhereWhere
id =‘
12345’
Build
in Data Types in SQL
Cont.
Can get current date/time/timestamp
Build
-
in Data Types in SQL
•
Can get current date/time/timestamp
CURRENT TIME
CURRENT
_
TIME
CURRENT_DATE
CURRENT TIMESTAMP
CURRENT
_
TIMESTAMP
selectselect CURRENT_TIME;
selectselect CURRENT_DATE;
selectselect CURRENT_TIMESTAMP;
Chapter4 Advanced SQL
5
