The most widely employed open source relational database management system (RDBSM) is MySQL (Officially pronounced "My Sequel") which runs as a server that provides multi-user access to a number databases at the same time [].

By convention SQL commands are written all in upper case while parameters are in lower case. This is only good practice, but actually not necessary as MySQL commands are case-insensitive, but helps to see what are the commands and what are the variables. There are a number of useful commands which should be memorized for working with this relational database []. All commands, with only one exception, must be ended with a semicolon (;) to indicate the execution.


To login in the MySQL shell you can simply run mysql (if it is installed), but the access also allows to specify some flags like the hostname (-h), the user (-u) and a password (-p): ::

mysql -h [host_name] -u [root] -p [password] # Access mysql.

Django allows to quickly jump into the database shell like this: ::

python dbshell

Showing Databases

To show all available database just tell the shell: ::


Database Schema

To see the schema of an individual database

 SHOW DATABASE;  # Describes database schema.

Use Database

To specify a certain database/schema use use: ::

USE [database_name]

Show Tables

To see all tables in a previous selected database type: ::

SHOW TABLES; # Shows all tables in schema.
SHOW TABLES in [database name];


Individual fields in a table can be described: ::

DESCRIBE [table];  # Gives a discription of the columns in a table


Data need to be inserted which is done in raw sql like this: ::

INSERT INTO far VALUES("everything");
INSERT INTO [some table] ([some column names]) VALUES("[some values]");'
REPLACE INTO [some table] SET("[some column name]" = "[value]");'

Selecting Records

Individual records within a table can retrieved with SELECT and a limiter can be set: ::

SELECT * FROM [table]                 # Shows all records in a table.


Selection can be limited to specific records: ::

SELECT * FROM [table_name] LIMIT [number] # Selects the first records up to the specified number.

The star is the legendary wild-card character, meaning here all.

The LIMIT directive can actually take in one or two attributes. If only the first is specified it shows only the records which get taken off of the top of the list of results. If the second attribute, e.g. `LIMIT 10, 5" is used, it will skip the number of records indicated by the first number and then display the records indicated by the second number.

SELECT * FROM [table_name] LIMIT [skip] [show]

The general definition for limit is: LIMIT {[offset,] row_count | row_count OFFSET offset} In such command can also be executed like this:

SELECT * FROM [table] LIMIT 1 OFFSET 0 # Limit the number of returned rows and starts at offset.


To narrow to the selction by a conditional: ::

FROM [table] SELECT * WHERE [column] = [value]


Having can be used to narrow down a selection by a condition: ::

HAVING [where_condition]

Alternatively WHERE can be used too.

Counting Records

All the records of a table can be counted with SELECT in combination with COUNT(*): ::

SELECT COUNT(*) FROM [table_name]       # Returns number o rows in table.

Patten matching in queries

Specific patterns can be defined instead of explicit variables: '%' # Zero or more characters matched in aggregate. '_' # Any characters matched individually (Any single character).

Like or not like Something

Specify the to be liked LIKE or not liked NOT LIKE keywords make broader selections: ::

SELECT * FROM [table] WHERE [field] LIKE '%s'"     # Everything with ends with `s`.
SELECT * FROM [table] WHERE [field] NOT LIKE '%s'"


GROUP BY {col_name | expr | position} [ASC | DESC], [WITH ROLLUP]

Ordering '[ORDER BY {col_name | expr | position} [ASC | DESC], ...]'

Renaming Databases

Renaming a database/schema (synonym) is done like this: ::

RENAME { DATABASE | SCHEMA } [db_name] TO [new_db_name]; #

Renaming Tables

Renaming tables updates all references through ForeignKeys (relationships) without problems. It is even possible to rename ForeignKey fields [,429224,429224#msg-429224].

RENAME TABLE [table_name] TO [new_table_name]

Table Engines

There are different MySQL table engines which are like flavours. Each one has some unique properties. Two commonly used ones are MyISAM and InnoDB. MyISAM is the default. The advantage of InnoDB tables is transactions. With transactions if you make multiple edits to the database and something goes wrong, you would not be left with all of the data updated. Either everything gets written or nothing.

The current database enigne can be figured out with status report request: ::

SHOW TABLE STATUS; # From the MySQL shell
mysqlshow --status [db_name] # From the bash terminal

The storage engine for a specific table can be checked with either: ::


The supported storage engines can be checked with the following statement: :: SHOW ENGINES;

Specifying Database Table Engine

In a Django project the engine for all tables can can be specified with an initial command under the options of the DATABASE definition: ::

nano # Open the with the editor of your choice.
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '',
        'USER': '',
        'PASSWORD': '',
        'OPTIONS': {
            "init_command": "SET storage_engine=INNODB",

Converting Engines

Engines can be converted to each other. For instance, you may want to convert MyISAM tables to InnoDB []. You can see all tables their creation statements and alter a specified table engine from this in the following way: ::

SHOW CREATE TABLE [tablename];

Using Transactions

To actually use transaction a view can be explicitly decorated to do it: ::

from django.db import transaction

def view(request):

References: *


In order to exit/quit/leave the MySQL shell you simple use EXIT even with out trailing semicolon: ::


Outputting Database

To output the data from a database: ::

INTO OUTFILE [file_name]

The data and schema of a database can be outputted together in a process which is called "dump": ::

mysqldump -h localhost [database_name] > [database_name].sql # dumps out a database

The opposite operation, i.e. to load data into a already existing database is done like this: ::

mysql -u root [database_name] < [database_name].sql #

Preparing a Database

A database can eb prepared with prepopulated schema and data: ::

CREATE [database_name];
USE [database_name];
SOURCE [database.sql]; # Imports a file containing schema and data

Character Set

Char and text fields use a specifiec character set. The default is latin1 with a very limited set of characters, but the recommended is utf8. The default character set can be shown []: ::

SHOW VARIABLES LIKE "character\_set\_database";

To show char set for a table: ::

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "[database_name]"
  AND T.table_name = "[table_name]";

Change the char set in this manner for individual tables []: ::


Deleting Columns

 ALTER TABLE [table name] DROP COLOMN [column name]

Mass-Deletion of all Table Rows

There are two ways of deleting all the data of a table (i.e. massive row deletion): ::

  1. TRUNCATE TABLE tablename;
  2. DELETE FROM tablename;

Truncating (1) the table deletes all entries very quickly, but deleting (2) allows to chain more complex conditionals with WHERE, e.g.: DELETE FROM tablename WHERE columnname = row_value;

Destruction of a Database

An existing database can be destroyed by dropping its name: ::

DROP DATABASE [database_name];


The MySQLdb api allows to programmatically interface with the RDBMS. Here is a small Kata on its usage: ::

import MySQLdb# as mydb
import MySQLdb as mysql     #makes working with the module easier by import it with an alias
[variable] = MySQLdb.connect([hostname], [username], [password], [database name])

[variable] = MySQLdb.connect(host="[hostname]",
                         db="[database name]")

print('[courser name] = [connect object name].cursor()')
'mydb = MySQLdb.connect(host, user, passwd, db)'
mydb = MySQLdb.connect('localhost','root','','Decode')
cursor = mydb.cursor()
'results_variable = cursor_handle.execute("MySQL statement")'
mydb.commit()    # if auto-commit is deactivated.

mysql1.png/ mysql-logo.jpg/

Edit post (Admin)

Comment on This Data Unit