Exploring MySql - Getting Started

In This post we are going to explore world of MySql 

• Downloaded Mysql for Win 7 - mysql-5.5.12-winx64 (Link)
• Downloaded Toad for MySql
• Installed MySql and Toad 
• All set for some exploration with Mysql
• Creating Databases
• Writing Procedures
• Checking Execution Plan

Welcome to world of MySql. Everytime when I try things like this, I recollect my inspiration from Bala.

Connect Using MySql Command Line Client

--Create a Database
CREATE DATABASE TestDB;

--Show Databases
SHOW DATABASES;

--USED Previous Example Post and Create those tables in MySQL

CREATE TABLE TestTable1
(
    Number INTEGER AUTO_INCREMENT,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D VARCHAR(20) NOT NULL,
    PRIMARY KEY  (NUMBER)
);

--SEE TABLE Created Details
DESCRIBE TestTable1;

--Entering Data in Tables Using Stored Procedure
DELIMITER $$ 
CREATE PROCEDURE dowhile() 
BEGIN 
DECLARE I INT DEFAULT 5; 
v1loop: WHILE I < 10000 DO    
INSERT INTO TestTable1(A,B,C,D)    
SELECT CONCAT(I,'A'), CONCAT(I,'B'), CONCAT(I,'C'), CONCAT(I,'D');    
SET I = I + 1; 
END WHILE v1loop; 
END$$ 
DELIMITER ;

--Execute MySQL Stored Procedure
call dowhile();

--Create Index
CREATE INDEX ABCD ON TestTable1 (A,B,C,D);

--Check Index on table
SHOW INDEX FROM testdb.TestTable1;

--SELECT Statement Example
SELECT Number FROM TestTable1 WHERE A = '1000A' AND B = '1000B' AND C = '1000C';

--Viewing Query Plan in MySql
Explain SELECT Number FROM TestTable1 WHERE A = '1000A' AND B = '1000B' AND C = '1000C';

Conceptually all TSQL best practices of MSSQL should be applicable for MySql as well. I hope so. I could not connect MySql client using Toad. I did this exercise using MySQL 5.5 Command Line Client.

This was a Good Learning!!

Thanks to Stackoverflow for helping me fix stored procedure error.

More Reads

Introduction to mySQL - Google University Tutorial
Oracle to start charging for MySQL – Standard edition @ 2000 USD
Mysql - Improving Performance with better indexes
MySQL Performance Analysis Essentials

Happy Reading!!