SQL Databases - Veb Programiranje

SQL stands for

Structured Query Language

MySQL is

Open source relational database management system

Relational database consists of

Tables connected by relations

Primary key is

Field/set of fields that uniquely identifies each record in table

Foreign key is

"Field in secondary table for linking

Three relationship types

"One-to-one

SQL processing phases (4)

Parsing -> Optimization -> Row source generation -> Execution

DML statements (Data Manipulation Language)

"SELECT

DDL statements (Data Definition Language)

"CREATE

TCL statements (Transaction Control Language)

"COMMIT

DCL statements (Data Control Language)

"GRANT

SELECT returns

Data from tables based on criteria

Projection refers to

Selecting columns (done via SELECT)

Selection refers to

Selecting rows (done via WHERE clause)

WHERE clause

Filters rows based on condition

ORDER BY clause

Sorts results

GROUP BY clause

Groups results by one or more columns (used with aggregate functions)

HAVING clause

Filters results of aggregate functions (WHERE cannot filter aggregates)

Aggregate functions (5)

"COUNT()

COUNT() returns

Number of rows matching criteria

SUM() returns

Total of numeric column

AVG() returns

Average of numeric column

MIN() returns

Smallest value in column

MAX() returns

Largest value in column

LIKE operator uses wildcards

% (zero/more chars) and _ (exactly one char)

IN operator

Shortcut for multiple OR conditions in WHERE

BETWEEN operator

Values within defined inclusive range

EXISTS operator

Returns TRUE if subquery returns one or more rows

ANY operator

Returns TRUE if ANY subquery value satisfies condition

ALL operator

Returns TRUE if ALL subquery values satisfy condition

SQL comments single-line

-- (double dash)

SQL comments multi-line

/* */

INNER JOIN returns

Only rows with matching values in BOTH tables

LEFT OUTER JOIN returns

ALL rows from left table + matching from right

RIGHT OUTER JOIN returns

ALL rows from right table + matching from left

FULL OUTER JOIN returns

All rows where match exists in either table

WHERE vs INNER JOIN

"WHERE includes all matching rows

INNER JOIN is

ANSI standard syntax

UNION combines

Results of two or more SELECT statements

UNION ALL vs UNION

"UNION ALL shows duplicates

UNION requirements

"Same number of columns

SELECT INTO copies

Data from one table to NEW table (MySQL uses INSERT INTO SELECT)

INSERT INTO SELECT copies

Data from one table to EXISTING table

IFNULL() and COALESCE()

Return alternative value if column is NULL

mysqli_connect() creates

Connection to MySQL database

mysqli_connect_error() returns

Error description if connection fails

mysqli_query() executes

SQL query on database

mysqli_fetch_assoc() returns

Associative array of result row

mysqli_fetch_array() returns

Both numeric and associative array

mysqli_num_rows() returns

Number of rows in result set

mysqli_close()

Closes database connection

mysqli_connect parameters

"servername

mysqli_begin_transaction() starts

Database transaction

mysqli_commit()

Saves all changes in transaction

mysqli_rollback()

Undoes all changes in transaction

Prepared statements prevent

SQL injection attacks

mysqli_prepare() creates

Prepared statement template

mysqli_stmt_bind_param() binds

Variables to prepared statement parameters

mysqli_stmt_execute() executes

Prepared statement