connect
or a constructor),exec
, execute
, prepare
),fetch
, open
, next
, seek
) – work with resultseterror
, lastError
).LIMIT
and OFFSET
on Postgres;
TOP
, BOTTOM
, ROWNUM
keywords.SELECT * FROM person ORDER BY id_person LIMIT 10 OFFSET 5
ORDER BY
! Otherwise you’ll have random results.CREATE
statements are practically used only during imports and exports.
information_schema
: invisible, accessible, read-only.SELECT * FROM information_schema.tables WHERE table_schema = 'information_schema'
SELECT * FROM information_schema.columns WHERE table_name = 'person'
DESCRIBE
, SHOW
ALTER
, RENAME
, DROP
, MODIFY
, ADD
…LIKE
compares without spacesreal
/ float
/ double
:
2 != 6 / 3
!number
/ numeric
/ decimal(precision, scale)
:
money
-263..263 = 92 233 720 368 547 758.08datetime
/ date
/ time
:
datetime
is sort of same as timestamptimestamp
, time
, date
cannot be used to store a date / datetime interval:
interval
– store the interval of date time values.serial
when creating the table.
CREATE TABLE table (id serial NOT NULL,
…nextval('sequence_name')
SELECT currval('sequence_name')
,$db->lastInsertId('sequence_name')
in PDO.SELECT currval('sequence_name')
$db->lastInsertId('sequence_name')
SELECT MAX(id) FROM table
UNIQUE (KEY)
, PRIMARY (KEY)
– keysCHECK
– arbitrary rules, not supported by some systems
height > 0
FOREIGN KEY
– dependency between tablesCASCADE
– delete the dependent records too,RESTRICT / NO ACTION
– default behavior, do not allow deletion,SET NULL
– set the dependent column to null, cancel the relationshipperson |
||
---|---|---|
id_person | name | id_location |
1 | John | 11 |
2 | Karl | 12 |
contact |
||
---|---|---|
id_contact | contact | id_person |
21 | karl@doe.com | 2 |
22 | carl@doe.com | 2 |
23 | john@doe.com | 1 |
person
table id_person=2
, contacts depending on that row will be deleted too.person |
||
---|---|---|
id_person | name | id_location |
1 | John | 11 |
2 | Karl | 12 |
location |
||
---|---|---|
id_location | city | street |
11 | Ulm | Abbey Rd. |
12 | Linz | Left Rd. |
id_location=11
is deleted from the location
table, the
value in the person.id_location
table is set to NULL.SET NULL
where a link can be lost,CASCADE
where related records can be lost,RESTRICT
in all other cases.WHERE
clauses (should be keys)Index File | |
---|---|
id | position |
1 | 1 |
2 | 563 |
3 | 1124 |
first_name | last_name | birth_day | id |
---|---|---|---|
John | Doe | 2001-10-20 | 1 |
Jenny | Doe | 1960-15-09 | 2 |
Gina | Rae | NULL | 3 |
u.articles
refers to the table:ON DELETE CASCADE
or ON DELETE SET NULL
?MAX()
to obtain ID of the last inserted record?text
data type everywhere?