Dragon Ball 2 part 3

Key and Constraints

this are Primary Key, Composite Key and Foreign Key.

Primary Key

uniquely identifies each row.

Composite Key

primary key with 2+ columns

Foreign Key

links to another table’s key

Clustered index

orders the actual rows

Non-Clustered index

separate lookup structure

Inner Join

matching rows only

Left Join

all left + matches

Right Join

matches + all right

Cross Join

all combinations

Full Join

all rows from both tables + matches

UNION ALL

in mysql there is no syntax of Full Join. What should you use then?

SELECT

read data

INSERT

add rows

UPDATE

change values

DELETE

remove rows

Invalid

does “ = NULL “ exist?

Aggregates & Grouping

COUNT(), SUM(), AVG(), MAX(), MIX()

GROUP BY

This is how you group results

HAVING

to filter grouped data

Transaction

all-or-nothing changes

Cascade Delete

removes related rows automatically

Pattern Matching

Uses LIKE to Filter

Letter% (%D)

Find the name that start with a letter

%Letter (D%)

Find the name that ends with a letter

%Letter/s%

Find name contain a letter/s