Mysql: Primer From Google Code University

Introduction to Databases and MySQL - Google Code University - Google Code

Getting Started with MySQL

The first thing you need to do to start learning MySQL is get access. We'll assume that you have access and can start up the command line interface. It will look something like:

mysql -uroot -ppassword

You should see the following:

Welcome to the MySQL monitor. Commands end with ; or \q.
Your MySQL connection id is 2129621 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

In the exercises that follow, we will create a database and a set of tables for Plastronics order-processing. Then, we'll learn the basic commands for entering, editing and manipulating orders, customers, etc. We will spend most of our time learning how to query these tables, since that's the most interesting part.

If you have worked with other SQL systems or with MySQL in other environments, the following articles and documents may be helpful:

* Comparison of SQL Server and MySQL
* Comparison of Oracle SQL and MySQL
* MySQL Documentation

Setting Up the Tables

Step one is to create a database which is a container for our tables. Enter the following command:

create database plastronics;

Note that the commands and names are not case-sensitive. Also, note that the ending semi-colon is required or you'll see something like this:

mysql> create database plastronics
->

If this happens to you, just type the semi-colon on the line with the "->" and press Enter.

mysql> create database plastronics
-> ;
Query OK, 1 row affected (0.00 sec)

mysql>

The "Query OK" is your signal that the command was accepted and the task performed. Creating a database does not select it for use; you must do that explicitly. Enter the following command:

use plastronics;

The system will respond "Database changed". Now, we can work with the database. Let's take a minute to review the final structure we designed for the order processing database.

relat4.PNG

We need to set up four tables, that relate to one another as defined in the structure above. We use the CREATE TABLE command which allows us to specify the fields and their contents, as well as primary keys and constraints. Here is the command to create the customer table:

create table customer (cust_no int not null auto_increment, name varchar(40),
address varchar(40), phone varchar(12), primary key(cust_no));

After the "create table" part of the command, we name our table "customer". Then, in parentheses, we define the fields. The minimum information required is a fieldname, and a type, indicating what kind of data we want to store in the field.

For cust_no, we want an integer (whole number), which is abbreviated "int" in MySQL. Then, we indicate that cust_no cannot be null (which means empty) and we would like the system to fill the value in for us by auto-incrementing from the previous value, every time we insert a new record.

The other fields are of type "varchar" which means variable-length strings, or sequences of characters. The numbers following "varchar" in the command above indicate maximum length for the data stored in the field. So, the name, address and phone fields are all sequences of characters with maximum lengths 40, 40 and 12.

Finally, we set cust_no to be the primary key.

You can find more information on types and how to use them in the MySQL documentation. For more details on the CREATE TABLE command, check the MySQL reference.

Here is the command to create the orders tables:

create table orders (order_no int not null auto_increment, FK_cust_no int not null,
foreign key(FK_cust_no) references customer(cust_no), primary key(order_no));

There are only two fields in this table. The order_no field is a primary key, and is an integer, not null and it will auto increment. The cust_no field is a foreign key. We have named it FK_cust_no in the orders table to distinguish it from the cust_no field in the customer table.

Recall that a foreign key is a field that references a primary key in another table. In the command, we indicate that the FK_cust_no field is a foreign key referencing the cust_no field in customer, indicated by the "foreign key(FK_cust_no) references customer(cust_no)" part of the command. By setting the table up this way, MySQL will enforce constraints, that is, any order that we enter into the orders table must reference a valid customer in the customer table. If we enter a cust_no in orders that does not exist in customers, an error will result.

Note: If you want MySQL to enforce foreign key constraints, you need to add "type=InnoDB" at the end of the CREATE TABLE statement as in:

create table orders (order_no int not null auto_increment, FK_cust_no int not null,
foreign key(FK_cust_no) references customer(cust_no), primary key(order_no)) type=InnoDB;

"type=InnoDB" may or may not be available in your MySQL installation (Note: in some distribution, to avoid "ERROR 1005", you have to add "type=InnoDB" on both table creation statement, customer and orders). Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source.

Let's see what we have so far. Enter the following command:

show tables;

Then enter the following command to look at the structure of the orders table:


describe orders;

Here is the command to create the items table:

create table items (item_no int not null auto_increment, price int, primary key(item_no));

Practice: Take a couple minutes to make sure you understand all the parts of the command for creating the items table. Using the CREATE TABLE command, create the fourth table, which has two foreign key fields and the count. Call this table "item_details".

Here is the command for creating item_details:

create table item_details (FK_order_no int not null, foreign key(FK_order_no)
references orders(order_no), FK_item_no int not null, foreign key(FK_item_no)
references items(item_no), count int);

If your table does not match the structure defined in this command, just delete it by entering:

drop table item_details;

Then, enter the command given above for creating item_details.

Entering and Updating Data

Now that we have our tables created, we need to populate them with data. Enter the following commands. Just copy the whole block and paste at your MySQL command prompt.

insert into customer set name="Joe Boo", address="123 West", phone="412-773-5322";
insert into customer set name="Rich Wrack", address="332 East", phone="412-773-8374";
insert into customer set name="Ken Bend", address="225 Main", phone="412-773-9822";
insert into customer set name="Kim Slim", address="415 Bent", phone="412-773-6721";
insert into customer set name="Tom Plom", address="633 North", phone="412-773-4156";
select * from customer;

The INSERT command sets the fields to the corresponding values. The SELECT command with a "*" outputs all the data. Notice how the cust_no field auto incremented. Next, we populate the items table:

insert into items set price=666;
insert into items set price=700;
insert into items set price=450;
insert into items set price=1200;
select * from items;

Now, the orders table:

insert into orders set FK_cust_no=4;
insert into orders set FK_cust_no=3;
insert into orders set FK_cust_no=4;
insert into orders set FK_cust_no=1;
insert into orders set FK_cust_no=2;
insert into orders set FK_cust_no=1;
insert into orders set FK_cust_no=2;
insert into orders set FK_cust_no=3;
insert into orders set FK_cust_no=4;
insert into orders set FK_cust_no=5;
select * from orders;

Finally, the item_details table:

insert into item_details set FK_order_no=1, FK_item_no=4, count=12;
insert into item_details set FK_order_no=1, FK_item_no=3, count=56;
insert into item_details set FK_order_no=2, FK_item_no=1, count=10;
insert into item_details set FK_order_no=3, FK_item_no=2, count=43;
insert into item_details set FK_order_no=3, FK_item_no=4, count=16;
insert into item_details set FK_order_no=4, FK_item_no=2, count=87;
insert into item_details set FK_order_no=5, FK_item_no=1, count=62;
insert into item_details set FK_order_no=5, FK_item_no=2, count=48;
insert into item_details set FK_order_no=5, FK_item_no=3, count=5;
insert into item_details set FK_order_no=6, FK_item_no=3, count=87;
insert into item_details set FK_order_no=7, FK_item_no=2, count=32;
insert into item_details set FK_order_no=7, FK_item_no=1, count=27;
insert into item_details set FK_order_no=8, FK_item_no=4, count=91;
insert into item_details set FK_order_no=9, FK_item_no=2, count=34;
insert into item_details set FK_order_no=9, FK_item_no=3, count=72;
insert into item_details set FK_order_no=10, FK_item_no=4, count=2;
select * from item_details;

If you need to edit a record, MySQL provides an UPDATE command:

update item_details set count=12 where FK_order_no=7 and FK_item_no=2;
select * from item_details where FK_order_no=7 and FK_item_no=2;

Notice how we can define the exact record for both UPDATE and SELECT using the WHERE clause. We have also used the AND connector. UPDATE can also be used to edit a group of records. For example, we could set count=12 for both of the items in order #7:

update item_details set count=12 where FK_order_no=7;
select * from item_details where FK_order_no=7;

We can also DELETE in a similar manner:

delete from item_details where FK_order_no=7;
select * from item_details where FK_order_no=7;

Let's put those records back in now:

insert into item_details set FK_order_no=7, FK_item_no=2, count=32;
insert into item_details set FK_order_no=7, FK_item_no=1, count=27;

For more information on these MySQL commands, check the MySQL Documentation.

Practice: See if MySQL checks for primary key constraints by trying to insert a new record in a table with a primary key that has the same primary key value as a record already in the table. Note that foreign key constraints are only checked if you created the tables using "type=InnoDB".

Doing Queries

We have already seen the use of the SELECT command. This is the command we use to query tables. We can see all the fields in a table using "*" or we can specify certain fields. We can also limit the output using the WHERE clause. Here are some examples:

mysql6.PNG

Practice: See if you can create SELECT statements to find the following data.

* All orders that include item #4 (only list the order numbers once). Answer: orders #1, 3, 8, 10.
* All orders for customer #2. Answer: orders #5 and 7
* Try entering a query that returns no results. How does MySQL communicate this?

We can also search within varchar fields using LIKE and the "%" wildcard:

select * from customer where address like "%West";
select * from customer where name like "%o%";

This last command outputs all records where there is an "o" anywhere in the name field. Note that using LIKE is very slow on large databases - this is not something you would do on the Ads database.

The real power of the SELECT command becomes evident when we join tables using foreign key relationships. In order to illustrate how this works, we need to add some records to the orders table:

insert into orders set FK_cust_no=10;
insert into orders set FK_cust_no=11;

Notice that we have entered orders for customers that do not exist in the customer table.

Take a look at the following command:

mysql7.PNG

In this SELECT, we have combined the orders and customer tables using the customer number. This is called an inner join. We are listing fields from both tables, and the ON clause makes the connection between the primary and foreign key. Notice that the records are listed in customer number order. Thus, an inner join takes all the records from the orders table and finds the matching record(s) in the customer table.

Another type of join is the left join. It returns all the values from left table (orders) and the matched values from right table (customers). In this join, if there are records in the left table that do not match records in the right table (orders with customers #10 and 11), then MySQL will output records for these orders with NULL values for the customer fields.

mysql8.PNG

Practice: See if you can create joins for the following:

* List the price field with the item_details fields.
* List the price field with the item_details fields, but ordered by order number, not item number.

Another thing we can do with SELECT commands is GROUP BY. We can also use mathematical functions on numeric fields. Try entering the following commands and see if you can figure out what GROUP BY does:


select * from item_details;
select FK_order_no, sum(count) from item_details group by FK_order_no;

We are summing the count fields by order number. So, we can use the GROUP BY clause to group together the values in a given column. We can then use the HAVING clause to exclude groups.

select FK_order_no, sum(count) from item_details group by FK_order_no having sum(count) > 100;

This outputs only the rows where the sum of the counts is greater than 100.
For more on the use of mathematical functions in MySQL, click here. For more on the use of GROUP BY and HAVING, click here.

Practice: Can you list the total amount for each order as well as the sum of the item counts? This requires a join along with GROUP BY.

Here is the solution to the practice exercise. It's important to understand how this works - it illustrates some of MySQL's most powerful features.

mysql9.PNG

Practice: See if you can design a SELECT statement that outputs the three columns above, along with the customer name for each order. Hint: It is possible to have successive joins in a single SELECT statement as in:

select ... from item_details left join items on FK_item_no=item_no left join orders
on FK_order_no=order_no ...

A common need in working with the Ads database system is doing queries on dates. We can add a date field to our orders table to indicate the date the order was placed:

alter table orders add column order_date date;

Then we will populate it like this. Note the date format required for a date type in MySQL

update orders set order_date="2006/11/01";
update orders set order_date="2005/11/01" where order_no < 6;

To do queries, we use comparison operators as in:


select * from orders where order_date < "2006/06/01";

For more information on the SELECT command, check the MySQL Documentation.

Before we leave this section, we should mention that there are other ways of setting up tables. Instead of marking foreign keys with an FK prefix as we did above, it is also common to name the keys the same. For example,

create table customer2 (cust_no int not null auto_increment, name varchar(40),
address varchar(40), phone varchar(12), primary key(cust_no));
create table orders2 (order_no int not null auto_increment, cust_no int not null,
foreign key(cust_no) references customer(cust_no), primary key(order_no)) type=InnoDB;

Then, when you do selects, you need to identify the table name with cust_no, so MySQL knows to which one you are referring.

select * from orders2 inner join customer2 on customer2.cust_no = orders2.cust_no;

Transactions in MySQL

A transaction is a sequence of individual database operations that are grouped together. A transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail. Transactions are a relatively new addition to MySQL but not to relational database systems in general.

A good example where transactions are useful is in banking. Say you want to transfer $100 between two accounts. In order to deposit money into one account, you must first take money from another account. Without using transactions, you would have to do the following MySQL operations:

1. Check that the balance of the first account is greater than $100.
2. Deduct $100 from the first account.
3. Add $100 to the second account.

If we think of this sequence as a transaction, then if any one operation fails, the whole transaction fails and we rollback, that is, the tables and the data inside them revert to their previous state. If the transaction is successful, we commit the changes to the database. This is much easier than dealing with possible errors between each step. For example, without transactions we need to skip steps #2 and #3 if the balance is less than $100; we need to skip step #3 if for some reason, we were unable to deduct $100 from the first account; and so on. Transactions can simplify the processing.

Transactions have the following four properties, usually referred to by the acronym ACID:

* Atomicity: An atom is meant to be something that cannot be divided. The operations that make up a transaction must either all be carried out, or none at all (as with our banking example).

* Consistency: The database changes state upon a successfully committed transaction.

* Isolation: Data being used for one transaction cannot be used by another transaction until the first transaction is complete. This enables transactions to operate independently of and transparent to each other.

* Durability: This ensures that the result or effect of a committed transaction persists in case of a system failure.

To use transactions in MySQL, you must use a transaction-safe table type. The default MySQL table type, MyISAM, does not support transactions. BerkeleyDB and InnoDB are the transaction-safe table types available in open source MySQL, version 3.23.34 and greater.

Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source. If your MySQL installation supports InnoDB tables, simply add a "TYPE=InnoDB" definition to the table creation statement. You can check if you have InnoDB support by entering the following command:


show variables like 'have_innodb';

In MySQL, transactions begin with the statement BEGIN WORK or START TRANSACTION and end with either a COMMIT or a ROLLBACK statement. The SQL commands between the beginning and ending statements form the operations of the transaction. Going back to the banking example, the following statements:

update account set balance = balance - 100 where acctnumber = 1;
update account set balance = balance + 100 where acctnumber = 2;

are written as transactions like this:

start transaction;
update account set balance = balance - 100 where acctnumber = 1;
update account set balance = balance + 100 where acctnumber = 2;
commit;

The updates are done in one transaction so that both must complete successfully before the changes are committed to the database. If either update fails, you can issue a rollback statement to undo the changes. For more information on MySQL transactions, refer to the MySQL Documentation.

Finishing Up

When you are ready to leave MySQL, just type "quit". When you come back, just remember that you have to select your database before you can access the tables as in "use plastronics;". You won't need to create the database or the tables again - everything will be there when you return.

One other quick tip: In many MySQL interfaces, you can use the up-arrow key to access a list of commands that you have just entered. This can save a lot of time if you are experimenting with queries.

Additional Examples and Exercises

The following websites have MySQL tutorials, if you need more practice or want to get into more of the details.

MySQL Tutorial

Database Journal MySQL Series

References

MySQL Documentation

Speed Test

Mysql: Count Occurrence of a particular character in a given String

Count Occurrence of Character in a String Using MySQL « In Piscean’s Heart

Since there is no inbuilt MySQL function to count the occurrence of a character in string, we can do it by using these steps:

1. Counting the number of characters in the original string

2. Temporarily ‘deleting’ the character you want to count and count the string again

3. Subtract the first count from the second to get the number of occurrences

Example:

SELECT LENGTH('foobarfoobarfoobar') - LENGTH(REPLACE('foobarfoobarfoobar', 'b', '')) AS `occurrences`
--> Result: 3

In this example ‘b’ is the string you want to count the number of occurrences.

Simple Income Tax Calculator

Maintaining this simple income tax calculator for Indian Employees.

The tax calculator can be found at the following url.

India: Income Tax Calculator

Oracle Acquires Sun: Future of Mysql

Big news for Software Industry and for OpenSource software industry in particular: Oracle Aquires Sun.

While the Sun, the brand, might set forever. I think its a blessing in disguise for most of the Sun's offerings, The Spark Servers, The Solaris Operating system and especially Java.

But what about MySQL?

Well, I feel MySQL is going to stay and will thrive in the coming years.

The aquisitions wont affect the mysql community like it didn't affect when mysql was aquired by Sun.
The end users and the developers will hardly see any issue with it. Infact I feel more opensource developers will flock to MySQL as it has got a strongre backing now.

While it would be a golden opportunity for Oracle to kill MySQL. I believe its not going to happen.  Mysql is very popular and has a strong business model.  Larry and others wont try to jeopardise this opportunity. MySQL I feel was never a competitor for Oracle. Oracle is an enterprise operating systems and its competitors in Database business have always been IBM, Microsoft and Sybase, and in this arena Oracle has always been the winner. The thing that Oracle was missing was a strong foothold in web applications and SMB applications. Where MySQL is the king. MySQL's aquisition will give Oracle an edge in this sector as well.
Also, Oracle was already setting grounds to acquired Mysql for a long time now. It had acquired the most populare storage engine for mysql,Innodb long time back and also the last time MySQL was under the hammer, Oracle was one of the very keen bidders.

What I see in the future that, Oracle may use MySQL as a stepping stone towards Oracle DB.  It can use the mysql community to popularize its own homegrown technologies, something like PL/SQL which is clearly lacking in MySQL and will be happily adopted by the developers world around.

So get ready guys. I feel in future the java/mysql/plsql jobs are going to be abundant in the market.

Oracle Acquires Sun: Future of Mysql

Big news for Software Industry and for OpenSource software industry in particular: Oracle Aquires Sun.

While the Sun, the brand, might set forever. I think its a blessing in disguise for most of the Sun's offerings, The Spark Servers, The Solaris Operating system and especially Java.

But what about MySQL.

Well, I feel MySQL is going to stay and will thrive in the coming years.

The aquisitions wont affect the mysql community like it didn't affect when mysql was aquired by Sun.
The end users and the developers will hardly see any issue with it. Infact I feel more opensource developers will flock to MySQL as it has got a strongre backing now.

While it would be a golden opportunity for Oracle to kill MySQL. I believe its not going to happen.  Mysql is very popular and has a strong business model.  Larry and others wont try to jeopardise this opportunity. MySQL I feel was never a competitor for Oracle. Oracle is an enterprise operating systems and its competitors in Database business have always been IBM, Microsoft and Sybase, and in this arena Oracle has always been the winner. The thing that Oracle was missing was a strong foothold in web applications and SMB applications. Where MySQL is the king. MySQL's aquisition will give Oracle an edge in this sector as well.
Also, Oracle was already setting grounds to acquired Mysql for a long time now. It had acquired the most populare storage engine for mysql,Innodb long time back and also the last time MySQL was under the hammer, Oracle was one of the very keen bidders.

What I see in the future that, Oracle may use MySQL as a stepping stone towards Oracle DB.  It can use the mysql community to popularize its own homegrown technologies, something like PL/SQL which is clearly lacking in MySQL and will be happily adopted by the developers world around.

So get ready guys. I feel in future the java/mysql/plsql jobs are going to be abundant in the market.

*N*X: Shell Hack : Use a Different Color for the root Shell

Today's Lifehacker tells how to do it.


Lifehacker - Use a Different Color for the Root Shell Prompt - Terminal

Use a Different Color for the Root Shell Prompt
By The How-To Geek, 1:00 PM on Thu Apr 2 2009, 3,749 views (Edit post, Set to draft, Slurp)

Copy this whole post to another site
Slurp cancel
loading comment page

Linux only: Reader Chris writes in with an excellent tip that changes the prompt to red when using the root account from the terminal—as a reminder to be more careful.

Using the tip is relatively simple—just edit the /root/.bashrc file and add in the following, preferably commenting out the existing lines that set the color, though you can simply add this line to the end of the file.

PS1='${debian_chroot:+($debian_chroot)}\[\033[01;31m\]\u@\h\[\033[00m\]:\[\033[01;34m\]\w\[\033[00m\]\$ '

Once you've added this line, anytime you switch to using the root shell you will see the prompt in red with white text for the command line. Chris takes it further, with a line that turns the prompt green for regular users, which you can enable by adding the following to your ~/.bashrc file:

PS1='${debian_chroot:+($debian_chroot)}\[\033[01;32m\]\u@\h\[\033[00m\]:\[\033[01;34m\]\w\[\033[00m\]\$ '

This tip can really come in handy if you have a bunch of terminal windows open at once, so you can tell at a glance which ones are using root mode and which aren't

Mysql: Client Tips 2: Running Shell Commands from the Commandline

Ok, As you know I am trying to explore more of the mysql command line and exploring its features. I have already written about how to change default mysql prompt using the command "prompt" in one of my previous articles.

Continuing this series. I am focusing today on the "system" command or "/!".

Well, I always have some of the sql source files and I execute it from the mysql command line. But I just keep forgetting their names. And most of the times I either close the mysql session and do 'ls' at the command line or move the mysql session in background and do a 'ls'.

Mysql cli has this very useful command called "system" using which you can execute shell commands from mysql client itself.

just type

  system ls -l

or

  \! ls -l

Some of us, who are familiar with Vim, will like this "\!" version a lot, because we are already using ! to run system commands there.

You can run almost all system commands using either of system or '\!'

Some examples:

mysql:root@test [14:26:19 pm]$ \! pwd
/home/owner

mysql:root@test [14:29:33 pm]$ \! date
Thu Mar 26 14:29:46 SGT 2009

mysql:root@test [14:29:46 pm]$ \! bash
owner@rocky-desktop:~$ exit
mysql:root@test [14:30:12 pm]$

You see the last one is quiet interesting. You can actually open a shell using this command perform some tasks. Just exit from the shell and you are back in the mysql world.

Liked it ! Start playing with mysql client system command.

Mysql: Comments Trick

Nice article by Eli White. Now you can understand easily all those comments that you see in the mysqldump output.


MySQL Trick with Comments

MySQL has a custom extension (if you want to call it that) to it’s commenting system, which actually allows you to make conditional commands. The mysqldump program that many people use to back up their databases uses this concept heavily, which is where I first ran into it.

In it’s simplest form, this allows you to place commands in a SQL file that only MySQL will run:

/*! MySQL specific code */

This allows you to make a single SQL file, that not only will execute correctly (and in detail) on MySQL, but it will also work on any ANSI compatible database, since you’ve hidden any of the non-ANSI parts in MySQL conditional comments. So for example, MySQL allows you to set a variable declaring that all names will be a certain character set, such as UTF-8, and therefore you can do:

/*! SET NAMES utf8 */;

MySQL: Query to List Down All Schema Objects

Below is a simple query to ease your day to day work. It simply lists down all the objects of a MySQL Schema. The things that you are mostly interested in e.g. Tables, Views, Routines, Indexes, Triggers and so on.

Just replace "[your-schema-name-here]" in the following query with your schema name. Hope it comes handy to some of you out there.

Update:05/27/2015 - Adding Github Gist for the script

SELECT OBJECT_TYPE
,OBJECT_SCHEMA
,OBJECT_NAME
FROM (
SELECT 'TABLE' AS OBJECT_TYPE
,TABLE_NAME AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.TABLES

UNION

SELECT 'VIEW' AS OBJECT_TYPE
,TABLE_NAME AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.VIEWS

UNION

SELECT 'INDEX[Type:Name:Table]' AS OBJECT_TYPE
,CONCAT (
CONSTRAINT_TYPE
,' : '
,CONSTRAINT_NAME
,' : '
,TABLE_NAME
) AS OBJECT_NAME
,TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.TABLE_CONSTRAINTS

UNION

SELECT ROUTINE_TYPE AS OBJECT_TYPE
,ROUTINE_NAME AS OBJECT_NAME
,ROUTINE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.ROUTINES

UNION

SELECT 'TRIGGER[Schema:Object]' AS OBJECT_TYPE
,CONCAT (
TRIGGER_NAME
,' : '
,EVENT_OBJECT_SCHEMA
,' : '
,EVENT_OBJECT_TABLE
) AS OBJECT_NAME
,TRIGGER_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.triggers
) R
WHERE R.OBJECT_SCHEMA = [your-schema-name-here];

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]$\\_

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]