Mysql: Cross tabulation: Very useful article from mysql tech-resources

MySQL :: MySQL Wizardry

Cross tabulations are statistical reports where you de-normalize your data and show results grouped by one field, having one column for each distinct value of a second field.



Basic problem definition. Starting from a list of values, we want to group them by field A and create a column for each distinct value of field B.



The desired result is a table with one column for field A, several columns for each value of field B, and a total column.

Mysql: Oracle users looking for Rownum in mysql !!

Sadly, MySQL doesn't have (yet) the ROWNUM function. But a simple playing around with variables will give you the desired result.

Here is an example: (Src: dzone)
mysql code

SELECT @rownum := @rownum + 1 as rownum, t.* FROM some_table t, (SELECT @rownum := 0) r

Mysql: Change Default Prompt

Not so long ago, I used to use mysql command line client in a very traditional way.
you know, like a simple login using

mysql -u[username] -p[password] -h [hostname] -D [database]

Recently just out of curiousity  I typed "help" at the  mysql prompt. It gave me a whole list of commands. Then I realized that I can do a lot more with the mysql command line.

mysql> help


List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?           (\?) Synonym for `help'.
clear      (\c) Clear command.
connect  (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit        (\e) Edit command with $EDITOR.
ego        (\G) Send command to mysql server, display result vertically.
exit        (\q) Exit mysql. Same as quit.
go         (\g) Send command to mysql server.
help       (\h) Display this help.
nopager  (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print       (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit         (\q) Quit mysql.
rehash     (\#) Rebuild completion hash.
source     (\.) Execute an SQL script file. Takes a file name as an argument.
status      (\s) Get status information from the server.
system     (\!) Execute a system shell command.
tee          (\T) Set outfile [to_outfile]. Append everything into given outfile.
use          (\u) Use another database. Takes database name as argument.
charset     (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings   (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

Looking at the list I realize I was using "\q", "\c" and "\G" for quiet sometime now. But wasnt aware of other commands. While I am investigating other commands. Let me tell you the most interesting command, which caught my attention immediately.

It is  PROMPT. It helps you in customizing the default mysql prompt i.e. "mysql >"

To change the prompt you just need to enter prompt command with what value  you want

e.g. prompt "hello world"

and your prompt will be set to "hello world". But I guess you would like to change it to something meaningful. Here comes the special sequences, to help you with this.

mysql> prompt [mysql:\u@\d][\R:\m:\s \P] $
PROMPT set to '[mysql:\u@\d][\R:\m:\s \P] $'

and now your prompt will look like:

[mysql:root@test][10:55:58 am] $

You see I have used "\u" "\d" "\R" \m \s, \P .. They mean, in that order, 'current user name', 'current database', 'hrs of current time', 'minutes for current time', 'seconds for current time', 'AM/PM indicator'. These are the special sequences I was talking about.

There is a whole list of special sequences, you can find it at mysql documentation page.  So what are you waiting now. Go try out and have your very own customized mysql prompt :D

Update: To make sure that you get the same prompt everytime, you can modify mysql config file (typically /etc/my.cnf) or create an file name .my.cnf in your home directory with a content similar to this:

[mysql]
prompt=mysql:\\u@\\d [\\R:\\m:\\s \\P]$\\_

Table of Contents

You can see the whole post published in this blog here. To see full table of content click the link below(It make take few moments)

Show Table of Content

Mysql: Find max and minimum date in a month

Recently I was looking for a quick way to find the first and last day of a  month, given any date from that month.
Michael Williamson seem to have a quick and fugly solution, which works quiet well.

Thanks Miachael.


Michael’s Blog » Blog Archive » MySQL number of days in a month

select date_add(concat(year(curdate()),'-',month(curdate()),'-','1'), interval 0 day) as mo_start,
date_sub(date_add(concat(year(curdate()),'-',month(curdate()),'-','1'), interval 1 month), interval 1 day) as mo_end;

Update: The same thing can be achieved by replacing curdate() with "now()". Here is the modified query.

select date_add(concat(year(now()),'-',month(now()),'-','1'), interval 0 day) as mo_start,
date_sub(date_add(concat(year(now()),'-',month(now()),'-','1'), interval 1 month), interval 1 day) as mo_end;

How to Number Rows in MySQL

I was looking for a way to number the row in a sql select result. A search lead me to the following.

How to number rows in MySQL at Xaprb

set @type = ''; set @num = 1; select type, variety, @num := if(@type = type, @num + 1, 1) as row_number, @type := type as dummy from fruits;

Rake: A basic intro of Rake and db migration using rake

Rake is a simple ruby build program with capabilities similar to make. Rake has the following features:

• Rakefiles (rake‘s version of Makefiles) are completely defined in standard Ruby syntax. No XML files to edit. No quirky Makefile syntax to worry about (is that a tab or a space?)
• Users can specify tasks with prerequisites.
• Rake supports rule patterns to synthesize implicit tasks.
• Flexible FileLists? that act like arrays but know about manipulating file names and paths.
• A library of prepackaged tasks to make building rakefiles easier.

You can get the tasks lists by executing ruby --tasks a selective approach of previous would be ruby --tasks db which shows all the tasks associated with db

• rake db:fixtures:load # Load fixtures into the current environment's database. Load specific fixtures using FIXTURES=x,y
• rake db:migrate # Migrate the database through scripts in db/migrate. Target specific version with VERSION=x
• rake db:remove_unknown # Remove all migrations in db/migrate that is missing from ENVMIGRATION_DIR? directory
• rake db:schema:dump # Create a db/schema.rb file that can be portably used against any DB supported by AR
• rake db:schema:load # Load a schema.rb file into the database
• rake db:sessions:clear # Clear the sessions table
• rake db:sessions:create # Creates a sessions table for use with CGI::Session::ActiveRecordStore?
• rake db:status # Display schema status in parsable YAML format
• rake db:structure:dump # Dump the database structure to a SQL file
• rake db:test:clone # Recreate the test database from the current environment's database schema
• rake db:test:clone_structure # Recreate the test databases from the development structure
• rake db:test:prepare # Prepare the test database and load the schema
• rake db:test:purge # Empty the test database
• rake db:unmigrate # Remove a specific migration based on MIGRATION_FILE (no validation is done)

Migration

• rake db:migrate : (Migrate the database through scripts in db/migrate. Target specific version with VERSION=x)
• Generate script for your acction example: script/generate migration DummyMigration?
• it creates a file something like 01211257749_dummy_migration.rb in the directory vim db/migrate/
• and also makes an entry in history.txt in the same folder do vim db/migrate/history.txt to check that

Unmigration

• Revert back your changes using the following syntax

rake db:unmigrate MIGRATION_FILE=db/migrate/01211257749_dummy_migration.rb
rake --tasks db

Structure Dump

• Syntax: rake db:structure:dump * creates a file something like development_structure.sql on the db folder * you can change the default schema by modifying the database config file
• cd config/
• vim database.yml
• rake db:structure:dump RAILS_ENV=production
• creates a structure dump for vim production_structure.sql

Mysql: Bulk Inserts involving updates

Lets think of a situation: where you have to bulk insert,
but there might be some data which is already there in the db, and you just want to modify it.
In such cases, something like the following query will help you.

Insert
 Into t1 (id,val1,va2)
 Values (1,1,1),(2,2,2),(3,3,3)
On Duplicate Key Update
 val1= val1+ values(val1),
 val2= val2 + values(val2)
The key is to use the clause "On Duplicate Key Update"

Mysql: Show user defined functions for some schema

If you have to look for the user defined functions in a mysql schema, here is the sample syntax to do so:

select
*
from
information_schema.ROUTINES
where
ROUTINE_SCHEMA=[schemaname] \G

PS: I use "\G" for better formatting, its not something which is necessary.

Mysql: Find Indexes on a Table

Select
COLUMN_NAME,
INDEX_NAME,
INDEX_TYPE
From
information_schema.STATISTICS
where
TABLE_NAME=[table name]
and TABLE_SCHEMA=[schema name]