SQL uses the structure for how assets are related from hierarchy viewpoint. An SQL instance hold databses, whilst databases contain table schemas and tables with actual data in it. A good illustration of this is depicted below:

SQL Structure (https://www.slideserve.com/booth/sql-server-for-the-oracle-dba-sept-16-th-2009)

Anytime we start to work with a database we need to make sure it is loaded into our SQL instance. To load a database into MySQL, we can use the following command:

mysql> source $path_to_database_n_schema

To find out what databases are available in our SQL instance, we can issue the command below. This command returns all databases loaded into our SQL instance.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.14 sec)

To start work on a database we need to indicate which database we want to use. This can be done with the ‘USE’ command.

mysql> USE sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

To create a new database, use the ‘CREATE DATABASE’ command:

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.09 sec)

Data Types

The common data types used in SQL are:

Character Data

  • char - fixed length character set, think of it as a short string. Useful for post codes, initials, titles, etc.

  • varchar - variable length character set - useful for names, addresses, emails, etc

:bulb: useful command for character set supported.

mysql> SHOW CHARACTER SET;

Text Data

  • text - for storing data that exceed 64KB limit for varchar. useful for blog page. other text types include tinytext, mediumtext and longtext

Numeric Data

  • tinyint - signed range (-128 to 127), unsigned range(0 to 255)

  • smallint - signed range (-32,768 to 32767), unsigned range(0 to 65535)

  • medium

  • int

  • bigint

  • float - with (precision, scale) e.g. float(4,2) of 17.8675 gives you 17.87

Temporal Data

  • date - YYYY-MM-DD
  • datetime - YYYY-MM-DD HH:MI:SS
  • timestamp
  • year - YYYY
  • time - HHH:MI:SS