SQL tutorial

SQLtutorial.md

SQL DIGEST

Basics

Here's some SQL fundamental reminders.

• IMPORTANT: DON'T USE utf8; I use utf8mb4_0900_ai_ci;
• \G to terminate a SQL statement instead of ; displays the result vertically rather than normal tabular format if they are too wide for the current window;
• NOT NULL constraint;
• PRIMARY KEY constraint (BEWARE: different on SQL Server): only ONE;
• `UNIQUE constraint (example: a phone number);
• DEFAULT value for the column: you don't need to assign its value in the INSERT if the table column is NOT NULL;
• FOREIGN KEY: relationship between data in two tables:

CREATE TABLE employees (
    emp_id INT NOT NULL PRIMARY KEY,
    emp_name VARCHAR(55) NOT NULL,
    hire_date DATE NOT NULL,
    salary INT,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

• (Not in MySQL) CHECK constraint: CHECK (salary >= 3000 AND salary <= 10000);
• SELECT column_list FROM table_name WHERE condition NOT IN (1, 3) ORDER BY emp_name ASCLIMIT 10: example of SELECTin MySQL;
• CAST('2006-01-01' AS DATE): this function converts the values to the desired data type for the best results;
• LIMIT on MySQL (ROWNUM on Oracle and TOP on SQL Server): BEWARE: if 2 arguments, the first is the offset (AKA starting point) and the latter is the number of rows;
• DISTINCT directly after SELECT: 2 NULL values are considered unique while at the same time they are not considered distinct from each other;
• UPDATE table_name SET col1=val1, col2=val2 WHERE condition;
• don't do this: DELETE FROM table; (if you need it, TRUNCATE TABLE table is faster).

PHP

To prevent SQL injections, use mysqli functions:

$link = mysqli_connect("localhost", "{...}", "{...}", "demo");
if ($link === FALSE){
  die("ERROR: Could not connect to database.");
}

$username_val = mysqli_real_escape_string($link, $POST['username']);
$password_val = mysqli_real_escape_string($link, $POST['password']);

if(isset($username_val,$password_val)){
  $sql="SELECT * FROM users WHERE username='" . $username_val . "' AND password='" . $password_val='" . $password_val . "'";
  if ($result = mysqli_query($link,$sql)){
    if($mysqli_num_rows($result) == 1){
      $row = mysqli_fetch_array($result);
      $_SESSION['user_id']=$row['user_id'];
      $_SESSION['first_name']=$row['first_name'];
      header('Location: welcome.php');
    } else {
      echo "ERROR: Invalid username or password.";
    }
  } else {
    echo "ERROR: Something went wrong. Please try again.";
  }
}
mysqli_close($link);
?>

Joins

In order to join tables, data of the columns which are used for joining tables should match, not necessarily the column names. Example with an inner join:

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 INNER JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;

Inner

When joining tables, prefix each column name with the name of the table it belongs in order to avoid confusion and ambiguous column error in case columns in different tables have the same name.

Outer

Left/Right joins

This statement returns all rows from the left/right table along with the rows from the right/left table respectively for which the join condition met.

Full joins

This statement returns all the rows from the joined tables, whether they are matched or not.

Cross

Don't use it! If you don't specify a join condition when joining two tables, database system combines each row from the first table with each row from the second table. This type of join is called a cross join or a Cartesian product (if there's 5 rows in a table and 10 in another one, a cross-join query produces 50 rows).

Advanced

Various features

• UNION: this operation eliminates the duplicate rows from the combined result set, by default;
• LIKE: wildcard-based pattern matching operator. You must use WHERE BINARY instead of WHERE for the case-sensitive case. Here's some wildcards:
  • % matches any number of characters even zero;
  • _ : matches exactly one character;
• ALTER TABLE:
  • ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;; you must specify an explicit default value if you want to add a NOT NULL column;
  • ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;;
  • ALTER_TABLE shippers ADD UNIQUE (phone);;
  • ALTER_TABLE shippers MODIFY phone CHAR(15);;
• IMPORTANT: no aliases in a WHEREclause; you can use them in GROUP BY, ORDER BY or HAVING;
• the GROUP BY clause must appear after the FROM and WHEREclauses, and before (like HAVING) the ORDER BY in a SELECT statement;
• use HAVING to filter a GROUP BY;
• CREATE VIEW example (DROP VIEW to drop the view from the database):

CREATE VIEW OR REPLACE emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

• CREATE INDEX to create an index on some columns wisely (because, every time a row is added, updated or removed from a table, all indexes on the table must be modified; it may lead to slower performance):

CREATE UNIQUE INDEX user_name_idx ON users (first_name, last_name);

• auto-initialize/auto-update properties (like TIMESTAMP on MySQL):

-- Syntax for MySQL Database
CREATE TABLE users (
    id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    birth_date DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• duplicate a table like this:

CREATE TABLE employees_clone LIKE employees;
INSERT INTO employees_clone SELECT * FROM employees;

• use CREATE TABLE {...} SELECT to quickly create a simple copy of any table that only includes the structure and data of the source table;
• REMINDER: since temporary tables created by CREATE TEMPORARY TABLE are session-specific, so two different sessions can use the same temporary table name without conflicting with each other;

Subqueries

A subquery is a nested query ie a SELECT query embedded within the WHERE or HAVING clause of another query. Here are the rules:

• a subquery must always appear within parentheses;
• a subquery must return only one column (so no SELECT * unless the table has only one column); you may use a subquery that returns multiple columns, if the purpose is row comparison;
• a subquery cannot be a UNION;
• you can only use subqueries that return more than one row with multiple value operators such as the IN or NOT IN operator.

Example:

INSERT INTO premium_customers
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders
                  WHERE order_value > 5000);

A subquery can return a single value, a single row, a single column, or a table containing one or more rows of one or more columns.

Digest by Martial BONIOU, 2021