MYSQL Exercises

Couple of learning exercises while working on MYSQL. Earlier MYSQL post please refer link
Learning #1 - Load data from flat file
Created a Temp table, Loaded bulk data using command - LOAD DATA LOCAL INFILE

CREATE TABLE TestTable2 ( A INT NOT NULL, B INT NOT NULL, C INT NOT NULL, D INT NOT NULL );

Dataset looks as below

LOAD DATA LOCAL INFILE 'E:\\DataUpload50K.txt' INTO TABLE TestTable2 FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n';

Reference - link1 , link2
Learning #2 - Enable Remote Access to MYSQL

Execute the Grant All Privileges command by specifying username, IP Address of remote host and password to logon
GRANT ALL PRIVILEGES ON *.* TO 'username'@'XX.XX.XX.XX' IDENTIFIED BY 'pwd';
Encountered error codes 1130, 1045
Reference - link1, link2, link3

Learning #3 - Show System Configuration Settings
Command - SHOW VARIABLES
Reference - link1

Happy Learning!!!

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!!