MySQL

Created on Oct. 2, 2012, 12:26 p.m. by Hevok & updated by Hevok on May 2, 2013, 5:05 p.m. Anonymous on May 2, 2013, 5:05 p.m.

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 [http://www.mysql.com/].

By convention SQL commands are written all in upper case while parameters are in lower case. This is only good practice and 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 [http://www.pantz.org/software/mysql/mysqlcommands.html]. All commands, with only one exception, must be ended with a semicolon (;) to indicate the execution.

Login

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 manage.py dbshell

Showing Databases

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

SHOW DATABASES;

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];

Describing

Individual fields in a table can be described: ::

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

Insertion

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.

Limiting

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.

Where?

To narrow to the selection by a conditional: ::

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

Having

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'"

Grouping

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]; # http://blog.marceloaltmann.com/how-to-rename-a-database-in-mysql/

Renaming Tables

Renaming tables updates all references through ForeignKeys (relationships) without problems. It is even possible to rename ForeignKey fields [http://forums.mysql.com/read.php?135,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 was the default in previous MySQL version, but was subsquently replaced by InnoDB [http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html]. The advantage of InnoDB tables is transactions. However tables that reley on full-text search must be MyISAM tables rather than InnoDB ones. 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.

All available engines can be displayed by issuing: SHOW ENGINES.

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

SHOW TABLE STATUS; # From the MySQL shell
mysqlshow --status [db_name] # From the bash terminal http://purab.wordpress.com/2009/10/09/how-to-find-the-mysql-database-engine-type/

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

SHOW TABLE STATUS LIKE 'table_name';
SHOW CREATE TABLE table_name;

Issue SHOW VARIABLES LIKE 'have_innodb' to check whether InnoDB is available at all.

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 settings.py # Open the settings.py with the editor of your choice.
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '',
        'USER': '',
        'PASSWORD': '',
        'OPTIONS': {
            'init_command': 'SET storage_engine=INNODB \
                             SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
        }
     }
}

Converting Engines

Engines can be converted to each other. For instance, you may want to convert MyISAM tables to InnoDB [http://djangosaur.tumblr.com/post/357759467/django-transaction-mysql-engine-innodb]. You can see all tables their creation statements and alter a specified table engine from this in the following way: ::

SHOW TABLES;
SHOW CREATE TABLE [tablename];
ALTER TABLE [tablename] ENGINE=INNODB;

Using Transactions

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

nano views.py
from django.db import transaction

@transaction.commit_on_success
def view(request):
    ...

References: http://docs.djangoproject.com/en/dev/ref/databases/#creating-your-tables http://docs.djangoproject.com/en/dev/topics/db/transactions/ * http://en.wikipedia.org/wiki/Database_transaction

Isolation Levels

The default isolation level is REPEATABLE-READ, but it can be relaxed to READ COMMITTED. This can be set as parameter in the settings.py. The current isolation level can be checked using the following: ::

SELECT @@session.tx_isolation;

References: http://stackoverflow.com/questions/12149086/does-django-create-implicit-transaction-on-first-query http://www.no-ack.org/2010/07/mysql-transactions-and-django.html

Exit

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

EXIT;

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 http://help.hardhathosting.com/question.php/124

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 # http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/

Preparing a Database

A database can be 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 specific 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 [http://yoonkit.blogspot.de/2006/03/mysql-charset-from-latin1-to-utf8.html]: ::

SHOW VARIABLES LIKE "character_set_database";
SHOW variables like "collation_database";

The database application default charset can be changed by altering my.cnf which can be located like this: ::

updatedb
locate my.cnf # >> /vol/etc/mysql/my.cnf

Open my.cnf and add the following additions under client, mysql and mysqldb to it: ::

nano /vol/etc/mysql/my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqldb]
default-character-set = utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

After this insertion restart mysql to apply the changes: ::

locate init.d/mysql # >> /etc/init.d/mysql
/etc/init.d/mysql restart

The default of a single database can be changed like this: ::

ALTER DATABASE database_name CHARACTER SET utf8;

To show char set for a table: ::

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

To discover which table fields are encoded in latin run: ::

 show table status where collation like '%latin1%' \G

Change the char set in this manner for individual tables [http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-database-table-column-is-in-mysql]: ::

ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8;

In order to change the charset in one go with a one-liner perform this operation: ::

mysqldump --add-drop-table database_to_correct | replace CHARSET=utf8 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql database_to_correct

Database Disk Space

To check how much disk space a database is using, run the following query:

 SELECT table_schema, sum((data_length+index_length)/1024/1024) AS MB FROM information_schema.tables GROUP BY 1;

Regular Expression

It is possible to us regular expressions (RegEx) within query. There are special characters and constructs that can be used for RegEx operations [http://dev.mysql.com/doc/refman/5.1/en/regexp.html].

Deleting Columns

 ALTER TABLE [table name] DROP COLUMN [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];

MySQLdb

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
print('''
[variable] = MySQLdb.connect([hostname], [username], [password], [database name])

[variable] = MySQLdb.connect(host="[hostname]",
                         user="[username]",
                         passwd="[passwod]",
                         db="[database name]")
''')
help(MySQLdb)
help(MySQLdb._mysql)

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

Tags: tutorial, coding, database, relational database
Parent: Coding Relational Databases

Update entry (Admin) | See changes

Comment on This Data Unit