Relational DataBases

Relational DataBases are the most common type of database and are sometimes referred to as a SQL DBMS. A relational database presents data as rows in tables with a fixed schema (structure that defines its columns). A database most often contains one or more tables. Each table is identified by a name. Each column is identified by a name. The schema defines the type of a column (numbers, strings of characters, possibly empty etc.)

RDBMS products can be quite expensive, but there are high-quality, open source options, such as PostgreSQL, MariaDB, MySQL, or SQLite.

Database Indexes

An index is an additional data structure that helps improve the performance of a query.

Each index must be associated with a specific table. An index consists of one or more columns, but all columns of an index must be in the same table.

Imagine an index in the database like an index of a book. By looking at the index, you can quickly identify page numbers based on the keywords.

A table may have multiple indexes.

Conversely, if an index is created with multiple columns, the additional columns are used as subsequent sort keys.

Referential Integrity*

Open-Source Databases

Action SQLite MySQL PostgreSQL
Logging In 

         

       		
       
         
Type sudo mysql to log in as superuser

       		
       
         

         

       		
  


  
 Show Existing DataBases
       
         

         

       		
       
         
SHOW DATABASES;

       		
       
         

         

       		
  


  
 Use/Get into a DataBase
       
         

         

       		
       
         
USE my_database;

         

       		
       
         

         

       		
  


  
 Create a DataBase
       
         

         

       		
       
         
CREATE SCHEMA my_database;

         

       		
       
         

         

       		
  


  
 Delete a DataBase
       
         

         

       		
       
         
DROP SCHEMA my_database;

         
Yet, to prevent a script from failing, add IF EXISTS:

         
DROP SCHEMA IF EXISTS my_database;

       		
       
         

         

       		
  


  
 Create a table
       
         
The common template seems to be:

         
CREATE TABLE [IF NOT EXISTS] table_name (
   column_1_definition,
   column_2_definition,
   ...
)

       		
  


  
 Typing String Literals
       
         
String literals can only be quoted within single quotation marks, as in

         
'still available'

       		
       
         
If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier. Otherwise, double quotation marks are valid, too.

       		
       
         
String literals can only be quoted within single quotation marks, as in

         
'still available'

       		
  


  
 Concatenating String Literals
       
         
Two string constants that are only separated by whitespace with at least one newline are concatenated and effectively treated as if the string had been written as one constant. The following lines are equivalent:

         
'amock'

         
and

         
'a'
'mock'

       		
  


  
 Escaping Characters
       
         
A single quote in a string must be escaped using an additional single quote, as follows: 'John''s database'. (Beware that MariaDB allows escaping the single quote with a backslash.)

       		
  

  

       
         
A backslash followed by any character other than n, x, X, or \ is interpreted as two separate characters. For example, \q inserts a backslash and the letter q.

       		
  

  

       
         
Hexadecimal escape sequences can be used for any character or binary value. A hexadecimal escape sequence is a backslash followed by an x followed by two hexadecimal digits. The hexadecimal value is interpreted as a character in the CHAR character set for both CHAR and NCHAR string literals. The value \x09 must be coded as \\x09 if you don't want the value stored as a single tab character, but \xyy would be stored as \xyy. The following example, in code page 1252, represents the digits 1, 2, and 3, followed by the euro currency symbol: '123\x80'.

       		
  

  

       
         
A backslash character in a string must be escaped using an additional backslash, as follows: 'c:\\november'. For paths, you can also use the forward slash (/) instead of a backslash: 'c:/november'

       		
  

  

       
         
To represent a new line character, use a backslash followed by n (\n), specify: 'First line:\nSecond line:'