Table Content

MariaDB [company]> SELECT * FROM employee_info;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
5 rows in set (0.00 sec)

DISTINCT

Function: Repeated data is displayed only once

MariaDB [company]> SELECT sex FROM employee_info;
+--------+
| sex    |
+--------+
| male   |
| male   |
| female |
| male   |
| male   |
+--------+
5 rows in set (0.00 sec)

MariaDB [company]> SELECT DISTINCT sex FROM employee_info;
+--------+
| sex    |
+--------+
| male   |
| female |
+--------+
2 rows in set (0.00 sec)

WHERE

Operator	describe
=	Be equal to
!=	Not equal to
>	greater than
<	less than
=	Greater than or equal to
<=	Less than or equal to
BETWEEN	Within a certain range
LIKE	Search for a pattern

MariaDB [company]> SELECT * FROM employee_info WHERE age!=23;
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 001 | Alex  |   30 |  13000 | NULL    | male |
| 002 | Jack  |   27 |   8500 | NULL    | male |
| 004 | John  |   36 |  15000 | NULL    | male |
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
4 rows in set (0.00 sec)

AND / OR

AND Logic and/OR Logic None

MariaDB [company]> SELECT * FROM employee_info WHERE name='Alex' AND sex='male';
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 001 | Alex |   30 |  13000 | NULL    | male |
+-----+------+------+--------+---------+------+
1 row in set (0.00 sec)

MariaDB [company]> SELECT * FROM employee_info WHERE age<=25 OR age >=40;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
2 rows in set (0.00 sec)

# AND OR Can be mixed
MariaDB [company]> SELECT * FROM employee_info WHERE (name='Alex' AND sex='male') OR age=23;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
2 rows in set (0.00 sec)

ORDER BY

The default is ascending order (ASC). DESC is used for descending order.

# Equivalent to MariaDB [company]> SELECT * FROM employee_info ORDER BY age ASC;
MariaDB [company]> SELECT * FROM employee_info ORDER BY age;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
5 rows in set (0.00 sec)

# Arrange in ascending order by sex column and descending order by salary column
MariaDB [company]> SELECT * FROM employee_info ORDER BY sex ASC, salary DESC;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 005 | Bruce |   42 |  30000 | NULL    | male   |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
5 rows in set (0.00 sec)

TOP

Displaying only top n rows is particularly useful for large data tables
MySQL grammar, LIMIT < number >

MariaDB [company]> SELECT * FROM employee_info LIMIT 3;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
3 rows in set (0.00 sec)

# limit defaults from 0 Start counting, default 0 Can be omitted
# From 1 Line counting, display 4 That's ok
MariaDB [company]> SELECT * FROM employee_info LIMIT 1, 4;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
4 rows in set (0.00 sec)

LIKE

The LIKE operator is used to search for the specified pattern in the column in the WHERE clause.

# Search name in the beginning of'B', again stressed that SQL is case-insensitive.
MariaDB [company]> SELECT * FROM employee_info WHERE name LIKE 'J%';
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 002 | Jack |   27 |   8500 | NULL    | male |
| 004 | John |   36 |  15000 | NULL    | male |
+-----+------+------+--------+---------+------+
2 rows in set (0.00 sec)

MariaDB [company]> SELECT * FROM employee_info WHERE name LIKE '%A%';
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
3 rows in set (0.00 sec)

# Return to the contrary
MariaDB [company]> SELECT * FROM employee_info WHERE name NOT LIKE '%A%';
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 004 | John  |   36 |  15000 | NULL    | male |
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
2 rows in set (0.00 sec)

MySQL wildcard

When searching for data in a database, SQL wildcards can replace one or more characters.
The SQL wildcard must be used with the LIKE operator.
In SQL, the following wildcards can be used:

wildcard	describe
%	Replacement of one or more characters
_	Replace only one character

MariaDB [company]> SELECT * FROM employee_info WHERE salary LIKE '_0000';
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
1 row in set (0.00 sec)

Regular Expression Matching

REGEXP / NOT REGEXP

# Query name starts with A or B. It's still case insensitive.
MariaDB [company]> SELECT * FROM employee_info WHERE name REGEXP '^[AB]';
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 001 | Alex  |   30 |  13000 | NULL    | male |
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
2 rows in set (0.00 sec)

# The first name does not begin with A or B.
MariaDB [company]> SELECT * FROM employee_info WHERE name REGEXP '^[^AB]';
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 004 | John  |   36 |  15000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
3 rows in set (0.00 sec)
# or
MariaDB [company]> SELECT * FROM employee_info WHERE name NOT REGEXP '^[AB]';

IN

The IN operator allows us to specify multiple values in the WHERE clause.

MariaDB [company]> SELECT * FROM employee_info WHERE name IN ('Alex', 'Jack');
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 001 | Alex |   30 |  13000 | NULL    | male |
| 002 | Jack |   27 |   8500 | NULL    | male |
+-----+------+------+--------+---------+------+
2 rows in set (0.00 sec)

BETWEEN ... AND ...

The operator BETWEEN... AND... selects a range of data between two values. These values can be numeric, textual, or date.

MariaDB [company]> SELECT * FROM employee_info WHERE salary BETWEEN 10000 AND 20000;
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 001 | Alex |   30 |  13000 | NULL    | male |
| 004 | John |   36 |  15000 | NULL    | male |
+-----+------+------+--------+---------+------+
2 rows in set (0.00 sec)

AUTO INCREMENT

We usually want to automatically create the value of the primary key field each time a new record is inserted.
We can create an auto-increment field in the table.
The auto increment column in MySQL must be PRIMARY KEY. The default counts from 1.

MariaDB [company]> CREATE TABLE test_table5
    -> (
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

MariaDB [company]> INSERT INTO test_table5 (name) VALUE('Alex');
Query OK, 1 row affected (0.05 sec)

MariaDB [company]> INSERT INTO test_table5 (name) VALUE('Bruce');
Query OK, 1 row affected (0.01 sec)

MariaDB [company]> SELECT * FROM test_table5;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  2 | Bruce |
+----+-------+
2 rows in set (0.00 sec)

# from 100 Start counting
MariaDB [company]> CREATE TABLE test_table6
    -> (
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(255) NOT NULL
    -> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.07 sec)

SQL Constraints

Constraints are used to restrict the type of data added to the table.
Constraints can be specified when a table is created (through the CREATE TABLE statement), or after the table is created (through the ALTER TABLE statement).
We will focus on the following constraints:
* NOT NULL
* UNIQUE
* PRIMARY KEY
* FOREIGN KEY
* CHECK MySQL does not support CHECK
* DEFAULT

SQL NOT NULL Constraints

NOT NULL constraints force columns not to accept NULL values.
NOT NULL constraints force fields to always contain values. This means that if you don't add values to fields, you can't insert new records or update records.
The following SQL statement forces the "id" column and the "name" column not to accept NULL values:

MariaDB [company]> CREATE TABLE test_table
    -> (
    -> id varchar(10) NOT NULL,
    -> name varchar(255) NOT NULL,
    -> age int
    -> );
Query OK, 0 rows affected (0.07 sec)

MariaDB [company]> INSERT INTO test_table VALUE('001',null,24);
ERROR 1048 (23000): Column 'name' cannot be null

SQL UNIQUE Constraints

UNIQUE constraints uniquely identify each record in a database table.
UNIQUE and PRIMARY KEY constraints provide uniqueness guarantees for columns or sets of columns.
PRIMARY KEY has automatically defined UNIQUE constraints.
Note that each table can have multiple UNIQUE constraints, but only one PRIMARY KEY constraint per table.

MariaDB [company]> CREATE TABLE test_table2
    -> (
    -> id varchar(20) NOT NULL UNIQUE,
    -> name varchar(255)
    -> );
Query OK, 0 rows affected (0.06 sec)
# or
MariaDB [company]> CREATE TABLE test_table3
    -> (
    -> id varchar(20) NOT NULL,
    -> name varchar(255),
    -> UNIQUE(id)
    -> );
Query OK, 0 rows affected (0.08 sec)

MariaDB [company]> INSERT INTO test_table2 VALUE('001','Alex');
Query OK, 1 row affected (0.19 sec)

MariaDB [company]> INSERT INTO test_table2 VALUE('001','Bruce');
ERROR 1062 (23000): Duplicate entry '001' for key 'id'

When a table has been created, to create UNIQUE constraints in the "age" column, use the following SQL:

MariaDB [company]> CREATE UNIQUE INDEX uni_age ON employee_info(age);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe employee_info;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| id      | varchar(20)           | NO   | PRI |         |       |
| name    | varchar(255)          | NO   |     | NULL    |       |
| age     | int(11)               | YES  | UNI | NULL    |       |
| salary  | int(11)               | NO   |     | NULL    |       |
| address | varchar(255)          | YES  |     | NULL    |       |
| sex     | enum('male','female') | YES  |     | NULL    |       |
+---------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Revoke UNIQUE constraints

MariaDB [company]> ALTER TABLE employee_info DROP INDEX uni_age;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe employee_info;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| id      | varchar(20)           | NO   | PRI |         |       |
| name    | varchar(255)          | NO   |     | NULL    |       |
| age     | int(11)               | YES  |     | NULL    |       |
| salary  | int(11)               | NO   |     | NULL    |       |
| address | varchar(255)          | YES  |     | NULL    |       |
| sex     | enum('male','female') | YES  |     | NULL    |       |
+---------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Multi-column UNIQUE constraints please google yourself.

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in the database table.
Primary keys must contain unique values.
Primary key columns cannot contain NULL values.
Each table should have a primary key, and each table can only have one primary key.
The following SQL creates PRIMARY KEY constraints in the Id_P column when creating the Persons table:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)
# or
CREATE TABLE Persons
(
Id_P int PRIMARY KEY NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)

Multiple columns of PRIMARY KEY please google yourself.

FOREIGN KEY

Please google your own content.

DEFAULT

DEFAULT constraints are used to insert default values into columns.
If no other values are specified, the default values are added to all new records.

MariaDB [company]> CREATE TABLE test_table4
    -> (
    -> id varchar(20),
    -> name varchar(255),
    -> graduate_age int DEFAULT 22
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [company]> INSERT INTO test_table4 (id,name) VALUE('001','Alex');
Query OK, 1 row affected (0.04 sec)

MariaDB [company]> SELECT * FROM test_table4;
+------+------+--------------+
| id   | name | graduate_age |
+------+------+--------------+
| 001  | Alex |           22 |
+------+------+--------------+
1 row in set (0.00 sec)

Add a column DEFAULT constraint for an existing table

MariaDB [company]> ALTER TABLE test_table4 ALTER name SET DEFAULT 'Unknown';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe test_table4;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(20)  | YES  |     | NULL    |       |
| name         | varchar(255) | YES  |     | Unknown |       |
| graduate_age | int(11)      | YES  |     | 22      |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Revoke DEFAULT Constraints

MariaDB [company]> ALTER TABLE test_table4 ALTER name DROP DEFAULT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe test_table4;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(20)  | YES  |     | NULL    |       |
| name         | varchar(255) | YES  |     | NULL    |       |
| graduate_age | int(11)      | YES  |     | 22      |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)