NOT NULL prevents a column from containing NULL (a missing value). The column must have a value; an empty string is still a value and is not prevented by this constraint.

firstname VARCHAR(15) NOT NULL

CHECK restricts the values allowed in a column. For example, the following definition rejects birth years of 1900 or earlier:

birthyear SMALLINT CHECK (birthyear > 1900)

This CHECK condition still allows NULL. Add NOT NULL if a birth year is also required.

DEFAULT supplies a value when the column is omitted from an INSERT statement or the keyword DEFAULT is used explicitly. For example:

city CHAR(10) DEFAULT 'Helsinki'

If no city is supplied, the database uses 'Helsinki'. An explicitly supplied NULL is not replaced by this default.

A data type defines the kind of value a column can store. The examples below use MariaDB/MySQL syntax; supported types and their limits differ between database systems.

For monetary amounts such as salaries, use DECIMAL to avoid the approximation associated with floating-point types. In DECIMAL(10,2), up to eight digits appear before the decimal point and two after it.

FROM Table1 INNER JOIN Table2 ON Table1.id=Table2.id

If you want to view all the rows from one table (usually the parent-table), even when the other table does not have all matching rows, an OUTER JOIN is used.

Types of OUTER JOIN:

• LEFT OUTER JOIN (or LEFT JOIN): Returns all rows from the left table, and matching rows from the right table. NULL values appear for non-matching rows.
• RIGHT OUTER JOIN (or RIGHT JOIN): Returns all rows from the right table, and matching rows from the left table.

Note: The word OUTER is often omitted, so LEFT JOIN and LEFT OUTER JOIN are equivalent.

Example: LEFT JOIN between project and hour tables

You can join multiple tables in a single query by chaining JOIN clauses. This is useful when you need to combine data from three or more related tables.

Example: Joining three tables from the Project database (person, hour, and project)

You can retrieve rows from several tables using a UNION query

Example
The persons which salary is over 40 and/or which belong to the project 102

SELECT lastname as Name,'Salary is over 40'
FROM person
WHERE salary>40
UNION
SELECT lastname, 'Belongs to 102'
FROM person INNER JOIN hour ON person.id_person = hour.id_person
WHERE id_project='102';

+----------+-------------------+
| Name     | Salary is over 40 |
+----------+-------------------+
| Morrison | Salary is over 40 |
| Jones    | Salary is over 40 |
| Morrison | Belongs to 102    |
| River    | Belongs to 102    |
+----------+-------------------+

SELECT lastname as Name,'Salary is over 40'
FROM person
WHERE salary>40
UNION 
SELECT lastname as Name,'Salary is not over 40'
FROM person
WHERE salary<=40;

+----------+-----------------------+
| Name     | Salary is over 40     |
+----------+-----------------------+
| Morrison | Salary is over 40     |
| Jones    | Salary is over 40     |
| Smith    | Salary is not over 40 |
| River    | Salary is not over 40 |
| Simpson  | Salary is not over 40 |
| Daniels  | Salary is not over 40 |
+----------+-----------------------+

SELECT lastname as Name, 
  IF(salary>40, 'Salary is over 40', 'Salary is not over 40') AS Explanation
FROM person;

  SELECT lastname,'Less than 39'
  FROM person
  WHERE salary<39
  UNION
  SELECT lastname,'Over 39'
  FROM person
  WHERE salary>39
  UNION SELECT lastname,'Exactly 39'
  FROM person
  WHERE salary=39
  ORDER BY 2;

+----------+--------------+
| lastname | Less than 39 |
+----------+--------------+
| Smith    | Exactly 39   |
| River    | Exactly 39   |
| Simpson  | Less than 39 |
| Daniels  | Less than 39 |
| Morrison | Over 39      |
| Jones    | Over 39      |
+----------+--------------+

Example
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

If we use word ANY like this

WHERE salary > ANY (SELECT salary FROM person WHERE city = 'Turku')

and if we use word ALL, like this

WHERE salary > ALL (SELECT salary FROM person WHERE city = 'Turku')

Example, we have this data

+----------+--------+
| city     | salary |
+----------+--------+
| HELSINKI |     39 |
| HELSINKI |     36 |
| TAMPERE  |     34 |
| TURKU    |     44 |
| TURKU    |     39 |
| TURKU    |     44 |
+----------+--------+

SELECT city, salary FROM person WHERE salary <
	ANY(SELECT salary FROM person WHERE city='Turku');

+----------+--------+
| city     | salary |
+----------+--------+
| TURKU    |     39 |
| HELSINKI |     39 |
| HELSINKI |     36 |
| TAMPERE  |     34 |
+----------+--------+

SELECT city, salary FROM person WHERE salary <
	ALL(SELECT salary FROM person WHERE city='Turku');

+----------+--------+
| city     | salary |
+----------+--------+
| HELSINKI |     36 |
| TAMPERE  |     34 |
+----------+--------+

Let's create a practical example using subqueries to find projects located in the same city where Jim Morrison lives.

A more complex example: Find the names of people who have worked on the same projects as Jim Morrison.

Delete rows, which meets on a condition

DELETE
FROM project
WHERE id_project=2145;

  UPDATE person
  SET salary=salary*1.2
  WHERE city='TURKU';

• id SMALLINT
• name VARCHAR(50)
• height SMALLINT

  INSERT INTO test
  VALUES(101,'Matt',180);

  INSERT INTO test
  VALUES(102,'Liisa',NULL);

  INSERT INTO test (name,id)
  VALUES('Liisa',103);

You can also read data from one table and insert that data to a new table which does not already exist. CREATE TABLE ... SELECT derives columns from the query result; it does not clone the full table definition. Indexes and constraints are not automatically copied, and column types or attributes may differ. Define the required structure explicitly when those details matter.

Example
Below code will read data from project-table and create a new table named helsinki with that data.

  CREATE TABLE helsinki
  SELECT *
  FROM project
  WHERE place='Helsinki';

  INSERT INTO helsinki
  SELECT *
  FROM project
  WHERE place ='Helsinki';

The following examples work in MariaDB and MySQL. In addition to parameters such as sname in the previous example, procedures can use variables to store values:

• User-defined variables start with @ and belong to the current connection (session). Their values remain available after the procedure finishes, until changed or the connection is closed.
• Local variables are declared with DECLARE inside a BEGIN ... END block. They are available only within that block and are initialized on each call, using their DEFAULT value or NULL if no default is specified.

DELIMITER //
CREATE PROCEDURE chooseFirstName(IN p_id SMALLINT)
BEGIN
  SET @chosenFname = (
    SELECT firstname FROM person WHERE id_person = p_id
  );
END //
DELIMITER ;
  

CALL chooseFirstName(1);
SELECT @chosenFname AS chosen_firstname;
  

DELIMITER //
CREATE PROCEDURE showFirstName(IN p_id SMALLINT)
BEGIN
  DECLARE chosenFname VARCHAR(50) DEFAULT NULL;
  SET chosenFname = (
    SELECT firstname FROM person WHERE id_person = p_id
  );
  SELECT chosenFname AS chosen_firstname;
END //
DELIMITER ;
  

CALL showFirstName(1);

In MariaDB and MySQL procedures, an IF statement chooses which statements to execute based on a condition. The basic syntax is:

IF condition THEN
  -- Statements executed when the condition is true
ELSE
  -- Statements executed otherwise
END IF;

The ELSE branch is optional. End the statement with END IF;, not just END;. This control-flow statement is different from the IF() function used in SELECT expressions.

The following procedure checks an amount and returns a message. It does not require any tables:

DELIMITER //
CREATE PROCEDURE checkAmount(IN p_amount DECIMAL(10,2))
BEGIN
  IF p_amount > 0 THEN
    SELECT 'Amount is positive' AS message;
  ELSE
    SELECT 'Enter an amount greater than zero' AS message;
  END IF;
END //
DELIMITER ;

After creating the procedure, test both branches in your SQL client:

CALL checkAmount(10.00);
-- Returns: Amount is positive

CALL checkAmount(-5.00);
-- Returns: Enter an amount greater than zero

The first call executes the THEN branch because 10.00 > 0 is true. The second executes the ELSE branch. Zero or NULL also takes the ELSE branch because the condition is not true.

Stored Procedures have several benefits:

• Creating several applications will be easier (ex. Web-appliation, Mobile-application), because we can call the same procedures
• User privileges: we can give privileges to call Procedures, without any other privileges
• Security: procedures can expose controlled operations. They still require safe parameter handling; dynamically concatenated SQL can introduce SQL injection.
• Less network traffic: one Procedure can make several things

A transaction groups database operations into one unit: either commit the changes together or roll them back. An SQL error does not always roll back the whole transaction automatically. The application or procedure must handle errors and issue ROLLBACK when needed.

START TRANSACTION begins an explicit transaction, COMMIT makes its changes permanent, and ROLLBACK cancels uncommitted changes. These examples require transactional tables, so both tables explicitly use InnoDB. DDL commands such as CREATE TABLE can cause an implicit commit in MariaDB/MySQL and should not be mixed into this transfer transaction.

CREATE TABLE bank_account(
account_number VARCHAR(20) PRIMARY KEY,
balance DECIMAL(10,2) NOT NULL,
credit_limit DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
  

CREATE TABLE action (
idaction int PRIMARY KEY AUTO_INCREMENT,
account_number VARCHAR(20) NOT NULL,
action VARCHAR(45) NOT NULL,
action_time timestamp NOT NULL,
amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
  

  INSERT INTO bank_account VALUES('FI001',500,1000);
  INSERT INTO bank_account VALUES('FI002',500,2000);
  

DELIMITER //
CREATE PROCEDURE debit_transfer(
	IN first_account VARCHAR(20), 
	IN second_account VARCHAR(20), 
	IN transfer_amount DECIMAL(10,2) )
  BEGIN
  DECLARE test1,test2 INT DEFAULT 0;
  START TRANSACTION;
  UPDATE bank_account SET balance=balance-transfer_amount WHERE account_number=first_account AND balance >= transfer_amount;
  SET test1=ROW_COUNT();
  UPDATE bank_account SET balance=balance+transfer_amount WHERE account_number=second_account;
  SET test2=ROW_COUNT();
    IF (test1 > 0 AND test2 >0) THEN
      INSERT INTO action(account_number,action,amount,action_time) VALUES(first_account,'withdrawal',transfer_amount,NOW());
      INSERT INTO action(account_number,action,amount,action_time) VALUES(second_account,'deposit',transfer_amount,NOW());
      COMMIT;
      SELECT 1 AS success;
    ELSE
      ROLLBACK;
      SELECT 0 AS success;
  END IF;
  END //
DELIMITER ;
  

  CALL debit_transfer('FI001','FI002',100);
  SELECT * FROM bank_account;
  SELECT * FROM action;

  CALL debit_transfer('FI001','ACC003',100);
  SELECT * FROM bank_account;
  SELECT * FROM action;

  CALL debit_transfer('FI001','FI002',2000);
  SELECT * FROM bank_account;
  SELECT * FROM action;  

DELIMITER //
CREATE PROCEDURE credit_transfer(
	IN first_account VARCHAR(20), 
	IN second_account VARCHAR(20), 
	IN transfer_amount DECIMAL(10,2) )
  BEGIN
  DECLARE test1,test2 INT DEFAULT 0;
  START TRANSACTION;
  UPDATE bank_account SET balance=balance-transfer_amount WHERE account_number=first_account AND balance + credit_limit >= transfer_amount;
  SET test1=ROW_COUNT();
  UPDATE bank_account SET balance=balance+transfer_amount WHERE account_number=second_account;
  SET test2=ROW_COUNT();
    IF (test1 > 0 AND test2 >0) THEN
      INSERT INTO action(account_number,action,amount,action_time) VALUES(first_account,'withdrawal',transfer_amount,NOW());
      INSERT INTO action(account_number,action,amount,action_time) VALUES(second_account,'deposit',transfer_amount,NOW());
      COMMIT;
      SELECT 1 AS success;
    ELSE
      ROLLBACK;
      SELECT 0 AS success;
  END IF;
  END //
DELIMITER ;

Triggers are little modules stored on database. These modules are executed automatically, when certain SQL-commands are executed. We can define the triggers start when UPDATE-, INSERT-, or DELETE-command is executed.

  CREATE TABLE Customers(
  idCustomers INT PRIMARY KEY AUTO_INCREMENT,
  customerName VARCHAR(20)
  );

  CREATE TABLE Logs(
  idLogs INT PRIMARY KEY AUTO_INCREMENT,
  customerName VARCHAR(20),
  idCustomers INT,
  updateType VARCHAR(50) DEFAULT NULL,
  updateTime TIMESTAMP
  );
  

  DELIMITER $$
  CREATE TRIGGER updateCustomer
      BEFORE UPDATE ON Customers
      FOR EACH ROW
  BEGIN
  INSERT INTO Logs
    SET updateType = 'update',
    idCustomers = OLD.idCustomers,
    customerName = OLD.customerName,
    updateTime = NOW();
  END$$
  DELIMITER ;
  

  DELIMITER $$
  CREATE TRIGGER deleteCustomer
      BEFORE DELETE ON Customers
      FOR EACH ROW
  BEGIN
  INSERT INTO Logs
    SET updateType = 'delete',
    idCustomers = OLD.idCustomers,
    customerName = OLD.customerName,
    updateTime = NOW();
  END$$
  DELIMITER ;
  

MariaDB and MySQL can schedule SQL statements using events. Events require the EVENT privilege for the database and a running Event Scheduler. Check SHOW VARIABLES LIKE 'event_scheduler';. An administrator can enable it with SET GLOBAL event_scheduler = ON;; to keep it enabled after a restart, configure event_scheduler=ON in the server configuration. The following example updates a value after one minute.

Create a test database and a table with below code

create database eventtest;

use eventtest;

create table atest(
id_atest INT PRIMARY KEY AUTO_INCREMENT,
amount int);

insert into atest(amount) values(5);

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO
      UPDATE atest SET amount=amount + 1;

You can check what kind of event your database has with below command

SHOW events;

DELIMITER //
CREATE EVENT permanent_event
  ON SCHEDULE EVERY 1 MINUTE
  DO
    BEGIN
      UPDATE atest SET amount=amount + 1;
    END //
DELIMITER ;

DROP EVENT permanent_event;

If you are going to make an application (web-application, Java-application etc), you should take care that there is no risk for SQL injection. SQL injection is a code injection technique that exploits security vulnerabilities in an application's database layer.

The first two animation steps illustrate a deliberately vulnerable legacy login that concatenates input and compares plain-text passwords. For this demonstration only, assume person also contains a password column; do not add plain-text passwords to the tutorial's person table. The final step uses a separate users table, password hashes and a parameterized query.

CREATE TABLE users (
  id_user INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

During registration, generate the hash with password_hash($password, PASSWORD_DEFAULT) and save it using a parameterized INSERT. During login, retrieve the hash by username and check it with password_verify(). The final animation assumes this table contains a registered user named Morrison.

For more information about SQL injection and security best practices, visit:
OWASP SQL Injection Prevention Cheat Sheet