Create Stored Procedure in PHPMyAdmin

Creating stored procedure in the phpmyadmin is little bit different as we are creating using mysql IDE.

This is the method how we can create a stored procedure in PHPMYADMIN also,

delimiter ;;
drop procedure if exists test2;;
create procedure test2()
begin
select ‘Hello World’;
end
;;

By executing this will get successful new stored procedure created in your database as we are doing with SQLYOG or some IDE.

Advertisements

MYSQL to swap 2 columns

In MYSQL swaping 2 column values on same time can done by the following way. For example create a table swap_test as follows,

CREATE TABLE `swap_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` varchar(255) DEFAULT NULL,
  `y` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Insert some values on the ‘swap_test’ table as seen below,

INSERT INTO `swap_test` VALUES (‘1’, ‘a’, ’10’);
INSERT INTO `swap_test` VALUES (‘2′, NULL, ’20’);
INSERT INTO `swap_test` VALUES (‘3’, ‘c’, NULL);

Now here we are going to interchange the field value ‘X’ to ‘Y’ and ‘Y’ to ‘X’ on the same time by running following query,

UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

Multi-Language Support in MYSQL

This article is about how to store and manipulate multi languages in mysql table. May be useful while developing globalization / locale support enabled websites

By default mysql supports many european languages, Since unicode character(UTF-8) support implemented in mysql it allows us to store many of the indian (Asian) languages.

Mysql supports Gujrathi, Hindi, Telugu and TAMIL among too many languages in the subcondinent
Let’s consider TAMIL language and workout:-

To store & search tamil character sets in MySQL table, first of all we need to create a table with character set UTF-8.

CREATE TABLE multi_language
(
id INTEGER NOT NULL AUTO_INCREMENT,
language VARCHAR(30),
characters TEXT,
PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET = utf8;

INSERT INTO multi_language VALUES (NULL, ‘English’, ‘abcdefghijklmnopqsrtuvwxyz’);
INSERT INTO multi_language VALUES (NULL, ‘Arabic’, ‘ﺃ‎ﺏﺝﺩ‎ﻫﻭﺯﺡﻁﻱﻙﻝ‎ﻡﻥ’);
INSERT INTO multi_language VALUES (NULL, ‘Arabic’, ‘ﺃ‎ﺏﺝﺩ‎ﻫﻭﺯﺡﻁﻱﻙﻝ‎ﻡ ﻥ’);
INSERT INTO multi_language VALUES (NULL, ‘Hindi’, ‘ਓਊਨਣਥਨਫ’);
INSERT INTO multi_language VALUES (NULL, ‘Thai’, ‘ЁώύЂЬЫЗЪШДГЦШГЕ’);

INSERT INTO multi_language VALUES (NULL, ‘Telugu’, ‘ని మీ హొమ్ పేజిగా అమర్చుకోండి’);
INSERT INTO multi_language VALUES (NULL, ‘Tamil’, ‘இந்தியா நாட்டின் பக்கங்கள்’);
INSERT INTO multi_language VALUES (NULL, ‘Arabic’, ‘البحث في الصفحات العربية ‘);
INSERT INTO multi_language VALUES (NULL, ‘Korean’, ‘시작페이지로 하세요 채용정보 광고 프로그램 정보’);

Command to Change the client character set (which sends request to the server):

this has to be done so that the server can understand the request which send by Client.

— To be executed at Client Side
 SET NAMES ‘utf8’;

— System Variable Name : character_set_client

— To Set Locale time zone name
SET @@lc_time_names = ‘en_US’;

For Tamil language, we need to set the time zone as follows..

SET @@lc_time_names = ‘ta_IN’;

Now, we use select query and check out the result.
Important :
————–

 1. If the result shows like the ???? then prpoerly in your system (windows XP) need to install the  extral language support tool by enabling the following options,

 Control Panel -> Regional and Language Option ->  Languages -> Install files for complex script and right-to-left languages (including Thai) 

 2. While fecthing the row using PHP, for displaying the Multilanguage content properly, you must need to include the Meta tag like,

 <META HTTP-EQUIV=”Content-Type” CONTENT=”text/html; charset=utf-8″>
 if needed the add mysql_query(‘SET character_set_results=utf8’) in the php code before fecthing the reocrd.

MySQL Storage Engines

One of the greatest things about MySQL, other than being free, widely supported and fast, is the flexibility of choosing different storage engines for different tables.

Overview

MyISAM:
The default engine. No transactions support, average data reliability. Offers great performance for read heavy applications. Most web services and data warehousing applications use MyISAM heavily.

HEAP:
All in-memory. Very fast for data retrieval, however due to being stored only in memory – all data is lost on shutdown. Great for temporary tables.

Archive:
Used for storing large amounts of data without indexes in a small footprint.

Merge:
Collection of MyISAM tables logically merged together to provide a single view.

InnoDB:
Transaction-safe storage engine, best suited for write heavy environments thanks to row-level locking. Offers good built-in recovery and solid data reliability. InnoDB engine was acquired by Oracle on 2005.

NDB:
A clustered engine – data is automatically split and replicated across several machines, a.k.a data nodes. Best suited for applications that require high performance lookups with the highest possible degree of uptime and availability. Originally designed by Ericsson for the Telco market, NDB offers the highest levels of data reliability (99.999%). NDB works well in read heavy environments. For write heavy environments with multiple concurrent writes, consider InnoDB.

The biggest disadvantadge of NDB, is that by design your entire database must fit in memory. If your database size times 2 is too big to fit in memory, NDBCluster is not for you.To make it easier to follow the unique characteristics of each storage engine, I created this magic
quadrant diagram:

 

 

Examples:

Below are some examples of using the best storage engine for different tasks:

Search Engine – NDBCluster

Web stats logging – Flat file for the logging with an offline processing demon processing and writing all stats into InnoDB tables.

Financial Transactions – InnoDB

Session data – MyISAM or NDBCluster

Localized calculations – HEAP

Dictionary – MyISAM

Important notes about MyISAM tables:

1. Your tables will get corrupted eventually! Plan accordingly.

Tar the entire database directory daily and setup MySQL replication to a slave for an up-to-the-minute live backup.

2. Turn on auto-repair by adding this flag to your my.cnf file:
myisam-recover=backup,force Or consider running a check-all-tables-and-email-me cronjob daily: See our MySQL Table Maintenance automation.

3. Super fast for read (select) operations.

4. Concurrent writes lock the entire table. Switch everything to offline processing where you can, to serialize writes without taking the database down. (Offline processing is golden and applies to all table types)

Important notes about HEAP/Memory tables:

While this type of table offers super fast retrieval, it only works well for small temporary tables. If you try to load too much data into a Memory table, MySQL will start swapping information to disk and then you lose the benefits of an all-memory storage.

Important notes about InnoDB tables:

1. ACID transactions support. Row-level locking (compared to table level locking with MyISAM) means faster concurrent writes.

2. Doing a “SELECT Count(*) FROM table” without specifying any indexes is very slow on InnoDB and requires a full table scan. (With MyIsam this operation doesn’t cost anything because MyIsam stores an internal record counter with each table).

If you need to “SELECT COUNT(*)” often on InnoDB tables, create MySQL insert/delete triggers that will increment/decrement a counter whenever records are added or deleted from the table.

3. Backup:

Doing a tar/rsync backup where you simply copy all files is not possible with InnoDB.

MySQLDump backup is too slow with InnoDB. (If you insist on using it, turn on these flags: –opt –compress)

The only viable fast backup option, which can also be used to populate new slave machines, is InnoDB Hot Backup.

4. Recovery:

InnoDB has built-in recovery that works 99% of the times automatically. Never try to move .frm or .ibd files around as a way of “helping” the database to recover. If the built-in recovery doesn’t work, switch to your slave server and restore the primary from backup.

5. LOAD DATA INFILE is too slow with InnoDB. Consider using MyIsam tables for LOAD DATA operations.

6. InnoDB is less forgiving than MyIsam when it comes to queries on non indexes. InnoDB is going to “School” you into ensuring every single query and update statement runs on an index. Issue no index queries and you’ll pay dearly in execution time.

7. Never ever change my.cnf INnoDB log file size while the database is running. You’ll corrupt the log sequence number beyond repair.

8. To maximize InnoDB MySQL database performance, start with these my.cnf settings:

innodb_open_files = 500
innodb_file_per_table
innodb_buffer_pool_size = 250M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency =8
innodb_lock_wait_timeout = 500
interactive_timeout = 20
back_log = 75
table_cache = 300
thread_cache = 32
thread_concurrency = 8
wait_timeout = 30
connect_timeout = 10

9. If InnoDB crashes and the built-in recovery mechanism is unable to roll-back transactions, your database will not start. This is very important to understand. With MyISAM, even if a table gets corrupted, you can still start the database and everything will work normally. InnoDB will simply refuse to start until you restore the entire database from backup. Make sure you understand this principle and backup religiously.

Scalability:

Every successful web application eventually outgrows the capacity and throughput of a single database machine.

At that point you typically have two options – Replication or NDBCluster.

As always the choice depends on the needs of your application.

For read-heavy environments, use an NDBCluster or setup replication for n MyISAM slave read-only machines.

For write-heavy environments, InnoDB on an active/passive replication setup is typically the best choice. You may also want to experiment with an NDBCluster. An NDBCluster is generally going to be slower than InnoDB in
write-heavy environments, but it offers a higher level of availability.

Create new table exactly like another one

Lets say you have a table at your mysql. Now you want to create a new table which will have exactly the same table structure of old one. Using the LIKE keyword of mysql you can easily create you desired table.

mysql> CREATE TABLE new_table LIKE old_table;

This will create a table “new_table” which will have exactly the same structure of “old_table”. Remember, the data will not copy to the “new_table”. You have to write other command to do that. The command for creating the table with data will be,

mysql> CREATE TABLE new_table SELECT * FROM old_table;

This will create the “new_table” which will have the same structure “old_table”, and will have all the data into it. You can filter the data with WHERE condition at SELECT query. for example,

mysql> CREATE TABLE new_table SELECT * FROM old_table WHERE id > 10;

You can copy the data using INSERT command after creating a table using LIKE keyword. The mysql query will be,

mysql> INSERT INTO new_table SELECT * FROM old_table;

Mysql: How to dump only the table data

How to download the table data by removing the create table format?

You can easily integrate your mysqldump command with -t parameter to exclude the create table part from your mysql dumping. The -t parameter will remove the drop table, and create table portion from your dumped data. And it will only consist the raw data as insert into format.

mysqldump -uUSERNAME -pPASSWORD DATABASE_NAME TABLE_NAME -t > TABLE_NAME.SQL

Here,
USERNAME = your mysql username;
PASSWORD = your mysql password;
DATABASE_NAME = your database name;
TABLE_NAME = the table you want to dump only the data.

From the above mysql dump command, the -t parameter is used to exclude the create table part from the dumped data. When you’ll open your .sql file after dump, you’ll find that it has the rows from your table.

The -t parameter is used when you have two same tables at your two different machines, and you want to migrate only the data. So the drop/create table won’t require by you. That’s why the -t to remove those part, and you can easily load the dumped file into your mysql table safely.

Example:

mysqldump -uroot test test1 -t > table.sql

Output:

LOCK TABLES `test1` WRITE;
/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
INSERT INTO `test1` VALUES (‘name\’s’,’desc\’s’),(‘test’,’test;test;asdasd;akdajh;test’);
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;
UNLOCK TABLES;

MYSQL Q&A

1.        What is DDL, DML and DCL?

– If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

2.        How do you get the number of rows affected by query?

– SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

3.        If the value in the column is repeatable, how do you find out the unique values?

 – Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;

4.        How do you return the a hundred books starting from 25th?

 – SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.

5.        You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?

SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase “Found 13,450,600 results, displaying 1-10”. Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.

6.        How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

7.        How would you select all the users, whose phone number is null?

                SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

8.        What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)

9.        It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

10.     How do you find out which auto increment was assigned on the last insert? – SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

11.      

12.     What does –i-am-a-dummy flag to do when starting MySQL?

Makes the MySQL  engine refuse UPDATE and DELETE commands where the
     WHERE clause is not present.

13.     On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do?

What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

14.     When would you use ORDER BY in DELETE statement?

When you’re not deleting by row ID. Such as in DELETE FROM techinterviews_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techinterviews_com_questions.

15.     How can you see all indexes defined for a table?

SHOW INDEX FROM techinterviews_questions;

16.     How would you change a column from VARCHAR(10) to VARCHAR(50)? – ALTER TABLE techinterviews_questions CHANGE techinterviews_content techinterviews_CONTENT VARCHAR(50).

17.     How would you delete a column?

– ALTER TABLE techinterviews_answers DROP answer_user_id.

18.     How would you change a table to InnoDB?

         – ALTER TABLE techinterviews_questions ENGINE innodb;

19.     When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
   

1.        VARCHARs with length less than 4 become CHARs

2.        CHARs with length more than 3 become VARCHARs.

3.        NOT NULL gets added to the columns declared as PRIMARY KEYs

4.        Default values such as NULL are specified for each column

20.     How do I find out all databases starting with ‘tech’ to which I have access to?

– SHOW DATABASES LIKE ‘tech%’;

21.     How do you concatenate strings in MySQL?

– CONCAT (string1, string2, string3)

22.     How do you get a portion of a string?

– SELECT SUBSTR(title, 1, 10) from techinterviews_questions;

23.     What’s the difference between CHAR_LENGTH and LENGTH?

– The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

24.     How do you convert a string to UTF-8?

– SELECT (techinterviews_question USING utf8);

25.     What do % and _ mean inside LIKE statement?

– % corresponds to 0 or more characters, _ is exactly one character.

26.     What does + mean in REGEXP?

 – At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

27.     How do you get the month from a timestamp?

– SELECT MONTH(techinterviews_timestamp) from techinterviews_questions;

28.     How do you offload the time/date handling to MySQL?

– SELECT DATE_FORMAT(techinterviews_timestamp, ‘%Y-%m-%d’) from techinterviews_questions; A similar TIME_FORMAT function deals with time.

29.     How do you add three minutes to a date?

-ADDDATE(techinterviews_publication_date, INTERVAL 3 MINUTE)

30.     What’s the difference between Unix timestamps and MySQL timestamps?

 – Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

31.     How do you convert between Unix timestamps and MySQL timestamps?

 – UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

32.     What are ENUMs used for in MySQL?

– You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

33.     How are ENUMs and SETs represented internally?

          As unique integers representing the powers of two, due to storage optimizations

Reference Links:

 http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

http://www.tutorialspoint.com/mysql/mysql-useful-functions.htm

http://www.tutorialspoint.com/php/php_function_reference.htm

MySql : List of Previous Months

We are going  to see here, listing out the number of previous months from the current month.
Below function will retrieve the list of months depends on the input give by the user. The code is,

DELIMITER $$
DROP FUNCTION IF EXISTS `DEMO_DB`.`month_list`$$

CREATE DEFINER=`root`@`localhost

` FUNCTION `month_list`(numMonths tinyint) RETURNS text CHARSET latin1
BEGIN
DECLARE l_counter int default 0;
declare retValue text;
SET retValue=”;
TestLoop: loop
 if l_counter >= numMonths then
     leave TestLoop;
  end if;
                 
              SET retValue= CONCAT(retValue,MONTHNAME(DATE_ADD(NOW(), INTERVAL -l_counter MONTH)),’,’);
  set l_counter = l_counter + 1;
 end loop TestLoop;
SET retValue = TRIM(TRAILING ‘,’ FROM retValue);
return retValue;
    END$$
DELIMITER ;
Input :
                  SELECT month_list(3);
 

 

Output:
             
March,February,January
 Run the function in your mysql(phpmyadmin) to store the function on any database after that just execute the function as seen above given input.
 

 

Useful Queries

we will see some use ful queries on MySQL,

1.Finding 2nd Maximum Number,

“select column A from tableA order by columnA desc limit 1,1”

2.Finding 2nd Minimum Number,

“select column A from tableA order by columnA asc limit 1,1”

3.Finding Saturday and Sunday in a year, in below code you can a procedure to extract saturday and sunday in a year,

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`testa`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `testa`()
begin
declare l_functionReturnDate datetime;
declare i_TRFE_Start_Dt datetime;
declare i_TRFE_End_Dt datetime;
declare TripDate datetime;
declare l_curMonth varchar(2);
declare l_curyear varchar(4);
declare date_t char(10);
set i_TRFE_Start_Dt=now();
set i_TRFE_End_Dt=’2009-03-28 12:51:01′;
while i_TRFE_Start_Dt < i_TRFE_End_Dt do
set l_curyear=substring(i_TRFE_Start_Dt,1,4);

set l_curMonth=month(i_TRFE_Start_Dt);

set date_t=day(i_TRFE_Start_Dt);

set l_functionReturnDate=concat(l_curyear,’-‘,l_curMonth,’-‘,date_t);
select fc_GetWeekdayDate (l_functionReturnDate,’Saturday’,1);
set i_TRFE_Start_Dt = date_add(i_TRFE_Start_Dt, interval 7

day);
end while;
end$$

DELIMITER ;

******************************

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`fn_GetWeekdayDate`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetWeekdayDate`(
TheDate  Datetime,
DayName  varchar(10),
DayNum   Tinyint
) RETURNS datetime
BEGIN
DECLARE InitialDate     Datetime;
DECLARE ReturnDate      Datetime;
DECLARE Dow int;

SET InitialDate =

CASE WHEN Date_Format(TheDate, ‘%W’) = DayName THEN TheDate

WHEN Date_Format(Date_Add(TheDate, INTERVAL 1 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 1 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 2 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 2 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 3 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 3 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 4 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 4 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 5 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 5 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 6 DAY), ‘%W’) =

DayName THEN Date_Add(TheDate, INTERVAL 6 DAY)
ELSE TheDate
END;

SET ReturnDate = CASE WHEN DayNum IS NULL or DayNum IN (0,1) Then

InitialDate
ELSE

Date_Add(InitialDate, INTERVAL (7*(DayNum-1)) DAY)
END;

/* uncomment this if you want to return null instead
of allowing crossover months

SET ReturnDate = CASE WHEN Month(TheDate) = Month(ReturnDate) THEN

ReturnDate
ELSE NULL
END;
*/

RETURN ReturnDate;
END$$

DELIMITER ;

MYSQL

MySQL is the world’s most popular open source database software, with over 100 million copies of its software downloaded or distributed throughout its history. With its superior speed, reliability, and ease of use, MySQL has become the preferred choice for Web, Web 2.0, SaaS, ISV, Telecom companies and forward-thinking corporate IT Managers because it eliminates the major problems associated with downtime, maintenance and administration for modern, online applications.

Many of the world’s largest and fastest-growing organizations use MySQL to save time and money powering their high-volume Web sites, critical business systems, and packaged software — including industry leaders such as Yahoo!, Alcatel-Lucent, Google, Nokia, YouTube, Wikipedia, and Booking.com.

The MYSQL versions are get updated as per the future requirement. Please check the lasted MYSQL version from the below site.

mysql1