SQL Tutorial - SQL Syntax

This is my learning notes about the SQL (based on MySql). As far as I known, Mysql was acquired by Oracle, whose mainly product is also the enterprise highly professional database, and the founder of the Mysql worried the future of the MySQL, then he started another open sourced database named MariaDB. I consider the Mysql and MariaDB the same thing, they even have the same command line tool name. And with no hesitate, I use MariaDB as the alternative to Mysql in the tutorial.

Database Creation

The operation of Database include Change, Create, Alter, Drop database.

USE db_name;
CREATE DATABASE IF NOT EXIST db_name CHARACTER SET=utf-8;
ALTER DATABASE db_name CHARACTER SET=utf-8;
DROP DATABASE IF EXIST db_name;

Table Creation

The basic composition of a database is its tables, table schema definition is the beginning stage in the database design. There is two import aspect in define a table schema, the column data types and the constrictions.

Escape the SQL standards, data types capability and definitions are difference case by case,  take MariaDB as an example, here is its data types definition.

I will take a sample database named sakila as an example, which is MySQL's sample database located at this link, this is sakila's installation hints. Then following its step to step instructions to install sakila.

USE sakila;
SHOW TABLES;
SHOW CREATE TABLE actor\G;

This instrument print the actor table's creation SQL commands.

CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

Its data types have smallint(5), varchar(45) and timestamp, the most common used datatypes are numberic types and string types. The constrictions are the instruments after data type declaration, for example, "NOT NULL", "AUTO_INCREMENT".

There is another SQL instrument to show the columns definition in a table.

MariaDB [sakila]: show columns from actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

Table insert and update

I insert a row into table actor.

INSERT INTO actor(first_name, last_name) VALUE('zpcat', 'su');
SELECT * FROM actor where first_name='zpcat';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      201 | zpcat      | su        | 2017-02-19 15:27:31 |
+----------+------------+-----------+---------------------+

I update the above row by rename my first_name to 'moses'.

UPDATE actor set first_name='moses' where first_name='zpcat';
SELECT * FROM actor where actor_id=201;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      201 | moses      | su        | 2017-02-19 15:32:35 |
+----------+------------+-----------+---------------------+

Table query

check how many rows in table actor:

SELECT COUNT(*) AS size FROM actor;
+------+
| size |
+------+
|  201 |
+------+

There are 201 records inside table actor. Then how about query how many actors with the same last_name.

SELECT COUNT(last_name) as size, last_name FROM actor GROUP BY last_name;
+------+--------------+
| size | last_name    |
+------+--------------+
|    3 | AKROYD       |
|    3 | ALLEN        |
|    1 | ASTAIRE      |
|    1 | BACALL       |

Query how many unique last_name with sub query.

SELECT COUNT(*) as size FROM (SELECT COUNT(last_name) as size, last_name FROM actor GROUP BY last_name) b;
+------+
| size |
+------+
|  122 |
+------+

There are two skills when use sub query after comparison instrument,  use aggregated function inside the sub query selection, or use ANY, SOME, ALL before the sub query selection when the sub query can return more than one row. Take film table for example.

SELECT title FROM film WHERE rental_rate >= ALL (SELECT rental_rate FROM film WHERE length > 180);
SELECT title FROM film WHERE rental_rate >= (SELECT MAX(rental_rate) FROM film WHERE length > 180);

Both of the above two SQL generate the same results.

Create another table from current actor table.

CREATE TABLE t_actor (
     actor_id smallint(5) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
     first_name varchar(45) NOT NULL,
     last_name varchar(45) NOT NULL
     ) SELECT first_name, last_name FROM actor;

This SQL create a new table named t_actor, then create another table t_actor_first_name.

CREATE TABLE t_actor_first_name (
     id smallint(5) unsigned NOT NULL auto_increment PRIMARY KEY,
     first_name varchar(45) NOT NULL
     )
     SELECT first_name FROM t_actor GROUP BY first_name;

update t_actor's column first_name.

UPDATE t_actor a INNER JOIN t_actor_first_name b ON a.first_name = b.first_name
     SET a.first_name = b.id;

Then table t_actor and t_actor_first_name is related. How about query all actors with first_name and last_name from those two related tables by inner join two tables.

SELECT b.first_name, a.last_name FROM t_actor a  
            JOIN t_actor_first_name b on a.first_name = b.id;

I think that's the beautiful of the relational database, to use JOIN to link multi tables together. Of course, in MySQL, there are four types of JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN.