Change - MySQL

Created on Oct. 2, 2012, 12:26 p.m. by Hevok & updated on Oct. 21, 2012, 12:48 a.m. by Hevok

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, 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 [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 DAT
ABASES; ¶


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 d
iescription 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. &para;<br> &para;<br> SELECT * FROM [table_name] LIMIT [skip] [show] &para;<br> &para;<br>The general definition for limit is:LIMIT {[offset,] row_count | row_count OFFSET offset}&para;<br>In such command can also be executed like this: &para;<br> &para;<br> SELECT * FROM [table] LIMIT 1 OFFSET 0 # Limit the number of returned rows and starts at offset. &para;<br> &para;<br>###Where? &para;<br>To narrow to the sel</span><ins style="background:#e6ffe6;">e</ins><span>ction by a conditional: :: &para;<br> &para;<br> FROM [table] SELECT * WHERE [column] = [value] &para;<br> &para;<br>###Having &para;<br>Having can be used to narrow down a selection by a condition: :: &para;<br> &para;<br> HAVING [where_condition] &para;<br> &para;<br>AlternativelyWHEREcan be used too. &para;<br> &para;<br> &para;<br>###Counting Records &para;<br>All the records of a table can be counted withSELECTin combination withCOUNT(*): :: &para;<br> &para;<br> SELECT COUNT(*) FROM [table_name] # Returns number o rows in table. &para;<br> &para;<br> &para;<br>###Patten matching in queries &para;<br>Specific patterns can be defined instead of explicit variables: &para;<br>* '%' # Zero or more characters matched in aggregate. &para;<br>* '_' # Any characters matched individually (Any single character). &para;<br> &para;<br> &para;<br>###Like or not like Something &para;<br>Specify the to be likedLIKEor not likedNOT LIKEkeywords make broader selections: :: &para;<br> &para;<br> SELECT * FROM [table] WHERE [field] LIKE '%s'" # Everything with ends withs. &para;<br> SELECT * FROM [table] WHERE [field] NOT LIKE '%s'" &para;<br> &para;<br> &para;<br>Grouping &para;<br>-------- &para;<br> &para;<br> GROUP BY {col_name | expr | position} [ASC | DESC], [WITH ROLLUP] &para;<br> &para;<br>Ordering &para;<br>'[ORDER BY {col_name | expr | position} [ASC | DESC], ...]' &para;<br> &para;<br> &para;<br>Renaming Databases &para;<br>------------------ &para;<br>Renaming a database/schema (synonym) is done like this: :: &para;<br> &para;<br> RENAME { DATABASE | SCHEMA } [db_name] TO [new_db_name]; # http://blog.marceloaltmann.com/how-to-rename-a-database-in-mysql/ &para;<br> &para;<br> &para;<br>Renaming Tables &para;<br>--------------- &para;<br>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]. &para;<br> &para;<br> RENAME TABLE [table_name] TO [new_table_name] &para;<br> &para;<br> &para;<br>Table Engines &para;<br>------------- &para;<br>There are different MySQL table engines which are like flavours. Each one has some unique properties. &para;<br>Two commonly used ones are MyISAM and InnoDB. MyISAM is the default. 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. &para;<br> &para;<br>All available engines can be displayed by issuing:SHOW ENGINES. &para;<br> &para;<br>The current database engine can be figured out with status report request: :: &para;<br> &para;<br> SHOW TABLE STATUS; # From the MySQL shell &para;<br> mysqlshow --status [db_name] # From the bash terminal http://purab.wordpress.com/2009/10/09/how-to-find-the-mysql-database-engine-type/ &para;<br> &para;<br>The storage engine for a specific table can be checked with either: :: &para;<br> &para;<br> SHOW TABLE STATUS LIKE 'table_name'; &para;<br> SHOW CREATE TABLE table_name; &para;<br> &para;<br>IssueSHOW VARIABLES LIKE 'have_innodb'to check whether InnoDB is available at all. &para;<br> &para;<br>The supported storage engines can be checked with the following statement: ::SHOW ENGINES;&para;<br> &para;<br>###Specifying Database Table Engine &para;<br>In a Django project the engine for all tables can can be specified with an initial command under the options of the DATABASE definition: :: &para;<br> &para;<br> nano settings.py # Open the settings.py with the editor of your choice. &para;<br> DATABASES = { &para;<br> 'default': { &para;<br> 'ENGINE': 'django.db.backends.mysql', &para;<br> 'NAME': '', &para;<br> 'USER': '', &para;<br> 'PASSWORD': '', &para;<br> 'OPTIONS': { &para;<br> "init_command": "SET storage_engine=INNODB", &para;<br> } &para;<br> } &para;<br> &para;<br>###Converting Engines &para;<br>Engines can be converted to each other. &para;<br>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: :: &para;<br> &para;<br> SHOW TABLES; &para;<br> SHOW CREATE TABLE [tablename]; &para;<br> ALTER TABLE [tablename] ENGINE=INNODB; &para;<br> &para;<br>Using Transactions &para;<br>-------------------- &para;<br>To actually use transaction a view can be explicitly decorated to do it: :: &para;<br> &para;<br> nano views.py &para;<br> from django.db import transaction &para;<br> &para;<br> @transaction.commit_on_success &para;<br> def view(request): &para;<br> ... &para;<br> &para;<br>References: &para;<br>* http://docs.djangoproject.com/en/dev/ref/databases/#creating-your-tables &para;<br>* http://docs.djangoproject.com/en/dev/topics/db/transactions/ &para;<br>* http://en.wikipedia.org/wiki/Database_transaction &para;<br> &para;<br> &para;<br>Exit &para;<br>---- &para;<br>In order to exit/quit/leave the MySQL shell you simple useEXITeven with out trailing semicolon: :: &para;<br> &para;<br> EXIT; &para;<br> &para;<br> &para;<br>Outputting Database &para;<br>------------------- &para;<br>To output the data from a database: :: &para;<br> &para;<br> INTO OUTFILE [file_name] &para;<br> &para;<br>The data and schema of a database can be outputted together in a process which is called "dump": :: &para;<br> &para;<br> mysqldump -h localhost [database_name] &gt; [database_name].sql # dumps out a database http://help.hardhathosting.com/question.php/124 &para;<br> &para;<br>The opposite operation, i.e. to load data into a already existing database is done like this: :: &para;<br> &para;<br> mysql -u root [database_name] &lt; [database_name].sql # http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/ &para;<br> &para;<br> &para;<br>Preparing a Database &para;<br>-------------------- &para;<br>A database can </span><del style="background:#ffe6e6;">e</del><span>b</span><ins style="background:#e6ffe6;">e</ins><span> prepared with prepopulated schema and data: :: &para;<br> &para;<br> CREATE [database_name]; &para;<br> USE [database_name]; &para;<br> SOURCE [database.sql]; # Imports a file containing schema and data &para;<br> &para;<br> &para;<br>Character Set &para;<br>------------- &para;<br>Char and text fields use a specifi</span><del style="background:#ffe6e6;">e</del><span>c character set. The default islatin1with a very limited set of characters, but the recommended isutf8. &para;<br>The default character set can be shown [http://yoonkit.blogspot.de/2006/03/mysql-charset-from-latin1-to-utf8.html]: :: &para;<br> &para;<br> SHOW VARIABLES LIKE "character\_set\_database"; &para;<br> &para;<br>To show char set for a table: :: &para;<br> &para;<br> SELECT CCSA.character_set_name FROM information_schema.TABLEST, &para;<br> information_schema.COLLATION_CHARACTER_SET_APPLICABILITYCCSA &para;<br> WHERE CCSA.collation_name = T.table_collation &para;<br> AND T.table_schema = "[database_name]" &para;<br> AND T.table_name = "[table_name]"; &para;<br> &para;<br>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]: :: &para;<br> &para;<br> ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8; &para;<br> &para;<br>The database default charset can be changed by alteringmy.cnfwhich can be located like this: :: &para;<br> &para;<br> updatedb &para;<br> locate my.cnf # &gt;&gt; /vol/etc/mysql/my.cnf &para;<br> &para;<br>Open my.cnf and add the following additions under client, mysql and mysqldb to it: :: &para;<br> &para;<br> nano /vol/etc/mysql/my.cnf &para;<br> [client] &para;<br> default-character-set=utf8 &para;<br> &para;<br> [mysql] &para;<br> default-character-set=utf8 &para;<br> &para;<br> [mysqldb] &para;<br> default-character-set = utf8 &para;<br> collation-server = utf8_unicode_ci &para;<br> init-connect='SET NAMES utf8' &para;<br> character-set-server = utf8 &para;<br> &para;<br>After this insertion restart mysql to apply the changes: :: &para;<br> &para;<br> locate init.d/mysql # &gt;&gt; /etc/init.d/mysql &para;<br> /etc/init.d/mysql restart &para;<br> &para;<br></span><ins style="background:#e6ffe6;">The default of a single database can be changed like this: :: &para;<br> &para;<br> ALTER DATABASE database_name CHARACTER SET utf8; &para;<br> &para;<br></ins><span> &para;<br>Deleting Columns &para;<br>---------------- &para;<br> &para;<br> ALTER TABLE [table name] DROP COL</span><del style="background:#ffe6e6;">O</del><ins style="background:#e6ffe6;">U</ins><span>MN [column name] &para;<br> &para;<br> &para;<br>Mass-Deletion of all Table Rows &para;<br>------------------------------- &para;<br> &para;<br>There are two ways of deleting all the data of a table &para;<br>(i.e. massive row deletion): :: &para;<br> &para;<br>1.TRUNCATE TABLE tablename;&para;<br>2.DELETE FROM tablename;&para;<br> &para;<br>Truncating (1) the table deletes all entries very quickly, &para;<br>but deleting (2) allows to chain more complex conditionals &para;<br>withWHERE, 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() ¶


Comment: Corrected typos.

Comment on This Data Unit