How to select a specified number of values starting with each alphabets in mysql.

Suppose you have a table named 'names', which has fields id and name, and you need to select two or three names from each alphabet and display it in a page. For example you have to select 3 names from the table, which contains the names starting with all the alphabets from a-z. ie, You will need to display 26 * 3 = 72 names.

Here is the answer,

SELECT 
 letter, 
 name 
FROM (
 SELECT 
  LEFT( name, 1 ) AS letter, 
  name, 
  @num := IF( @prev = LEFT( name, 1 ),@num +1, 1 ) AS row_num, 
  @prev := LEFT( name, 1 ) AS previous
 FROM 
  names,(
   SELECT 
    @num  :=0, 
    @prev := ''
  )  PHPQA
 ORDER BY name
 ) QAPHP
WHERE row_num <=3

You can find its live example on following page.
 http://sqlfiddle.com/#!2/abfd8/3
http://sqlfiddle.com/#!2/4887b/1

Mysql optimization by configuring the query cache
In Ubuntu (debian) the query cache settings can be changed in the following file /etc/mysql/my.cnf
 Use the following command to edit the file

 nano /etc/mysql/my.cnf or vi /etc/mysql/my.cnf 

In this file you can find following settings.

#  Query Cache Configuration
#   
query_cache_limit       = 1M
query_cache_size        = 16M
#

Query Cache options SQL_CACHE

SELECT SQL_CACHE id, name FROM students;

SQL_NO_CACHE

 SELECT SQL_NO_CACHE id, name FROM students; 

We can check whether cache enabled in mysql database

 mysql> SHOW VARIABLES LIKE 'have_query_cache'; 

We can set Query_cache by queries

mysql>SET GLOBAL query_cache_size = 41984; 

If "query_cache_size " has value '0', then query cache is disabled, in the mysql environment. Also need to take care about the setting the query_cache_size with huge value. that will affect the system. For more information about the query caching you can visit http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

Lets have a look on How to use the Stored Procedures in Mysql. We can use these stored procedures in PHP. The following example shows how to use mysql stored functions in PHP scripts. Defining a stored function

DELIMITER $$

DROP PROCEDURE IF EXISTS `tester`.`GetAllProducts`$$
CREATE PROCEDURE `tester`.GetAllProducts(IN t INT,IN age INT)
 BEGIN
 UPDATE students SET age = age WHERE id = t;
 INSERT INTO students (name,age,sex) VALUES ('Jayan',35,'M');
 END $$
DELIMITER ;

Calling a stored function from PHP

$res = mysql_query('call GetAllProducts(2,30)');

if ($res === FALSE) {
    die(mysql_error());
} else {
    echo "@";
    print_r($res);
}

Let us have a look on How to use the Stored Functions in Mysql. Here I am trying to show you how to use mysql stored functions in PHP scrips. Hope you all know, what is Mysql Stored Functions. Its like normal mysql functions, its written by the user. It will return results as a normal mysql functions. It can be called in normal sql statements. Defining a stored function

DELIMITER $$

DROP FUNCTION IF EXISTS `tester`.`sf_test`$$
CREATE FUNCTION `tester`.`sf_test` ()
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE tot_count INT;
    select count(*) INTO tot_count from students;
    RETURN tot_count;
END$$

DELIMITER ;

Calling a stored function from PHP

$res = mysql_query('select sf_test()');

if ($res === FALSE) {
    die(mysql_error());
} else {
    echo "@";
    print_r($res);
}

How to find out the CREATE TABLE statement of a selected table.

The 'SHOW CREATE TABLE' command will show the CREATE TABLE statement of a selected table. It will be very helpful for the database backup actions.

SHOW CREATE TABLE `tablename`

It will return two colomns one is its table name and other is the 'CREATE TABLE' statement. 

CREATE TABLE `tablename` (

 `id` int(10) NOT NULL,

 `title` varchar(256) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

Hope this will helpful for someone ! 

How to find out the Mysql storage engine type of a table.

The following queries will show the engine type of a table

SHOW
TABLE STATUS
WHERE
name= 'tablename';

The Following query will also help you to find out the storage engine.

Each Mysql table's informations are stored in the 'INFORMATION_SCHEMA' table. 

The following query will show the storage engine's of the given databse table.

SELECT 
TABLE_NAME,ENGINE 
FROM information_schema.TABLES
WHERE 
TABLE_SCHEMA = 'dbname'

To show particular table's mysql engine type.

SELECT TABLE_NAME,
ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname'
AND TABLE_NAME = 'tablename'

Hope this will helpful for you all.

The MYSQL joins are explained below with examples

Inner Join( Equi-Join)
In the inner-join the  columns of two tables. It can be used to select certain fields from both tables and only the correct rows will be joined together.

Syntax:

SELECT <column_name> FROM <Table1>, <Table2> WHERE (Table1.column = Table2.column)  

xample :

SQL query:

SELECT test1.id, test1.name, test2.id, test2.favcolor
FROM test1, test2
WHERE (
test1.name = test2.name

)

Id	name	id	favcolor
1	sree	1	black
2	divs	2	white

Cross- Join

Cross-join of two tables takes data of each row in table1 and joins it to the data from each row in table2.

Syntax: SELECT <column_names> FROM <table1>, <table2>

Example :

test1

Id	Name
1	Sree
2	divs
3	dev

test2

Id	favcolor	Name
1	black	Sree
2	white	divs

SQL query:

SELECT test1.id, test1.name, test2.id, test2.favcolor
FROM test1, test2

id	name	id	favcolor
1	sree	1	black
2	divs	1	black
3	dev	1	black
1	sree	2	white
2	divs	2	white
3	dev	2	white

Left Join &Right join

Left Join

according to the match condition.It will show all values from left table whether there is amatching value in right or not

Syntax:

SELECT <column_name> FROM <Table1> [join|LEFT JOIN |RIGHT JOIN ] <Table2> on  (Table1.column = Table2.column)  

for example consider the following two tables

testtable1

eid	eName	eBasicpay
1	sree	200000
2	divs	500000
3	vid	000000

testtable2

id	eid	phone
1	1	987654
2	3	322352362
3	5	1222222

example :

SQL query:

normal join will show a result like this

select eName , eBasicpay, phone

from testtable1     join testtable2   on testtable1.eid  =  testtable2.eid  

eName	eBasicpay	phone
sree	200000	987654
vid	1000000	322352362

The result of left join is    SELECT eName, eBasicpay, phone
FROM testtable1
LEFT JOIN testtable2 ON testtable1.eid = testtable2.eid  

eName	eBasicpay	phone
sree	200000	987654
divs	500000	NULL
vid	1000000	322352362

Right join

according to the match condition.It will show all values from right table whether there is amatching value in left or not

example :

SQL query:

SELECT eName, eBasicpay, phone
FROM testtable1
RIGHT JOIN testtable2 ON testtable1.eid = testtable2.eid     

eName	eBasicpay	phone
sree	200000	987654
vid	500000	322352362
NULL	NULL	1222222

You can use 'USING' clause on the Left /Right Join , if the columns that are carrying out the join on have the same name.

Syntax:

SELECT <column_name>  FROM <Table1>  LEFT JOIN <Table2>  USING (<column_name>) example :

SQL query:

SELECT *
FROM testtsable1
LEFT JOIN testtsable2 using(eid)

eid	eName	eBasicpay	id	eid	phone
1	Sree	200000	1	1	987654
2	div	500000	NULL	NULL	NULL
3	vid	500000	2	3	322352362

Joining three tables   for example take the following table as the third table   testtable3

id	eid	favcolor
1	1	black
2	2	blue
3	5	green

SQL Query
SELECT *
FROM testtsable1
LEFT JOIN testtsable2 ON testtsable1.eid = testtsable2.eid
LEFT JOIN testtsable3 ON testtsable1.eid = testtsable3.eid 

eid	eName	eBasicpay	id	eid	phone	id	eid	favcolor
1	Sree	200000	1	1	987654	1	1	black
2	div	500000	NULL	NULL	NULL	2	2	blue
3	vid	500000	2	3	322352362	NULL	NULL	NULL

Good Luck :)

UNION can be used to combine two or more result sets from select statements into a single result set.

Syntax :

<SELECT statement1> UNION [DISTINCT | ALL] <SELECT statement2> UNION [DISTINCT | ALL]


for example consider the following tables


SELECT * FROM table1;


----------------------------------------------
| name1 | name2 | add1 |
--------------------------------------------
| Princy | Peter | R/220 V st |
--------------------------------------------
| Joban | John | R/456 Mst |
--------------------------------------------


SELECT * FROM table2;
------------------------------------
|Company | Address |
-------------------------------------
|TCS | 24/cChennai|
-------------------------------------
|Cubet | 43C EKM |
------------------------------------


SELECT * FROM table3;

-------------------------------------------------
|studname1|studname2|address |
-------------------------------------------------
| aarathi | Sharma | 122c/Nst|
--------------------------------------------------
| Sagar | C | 143c/Nst|
------------------------------------------------
|Sree | Nithya | 132c Dst |
-------------------------------------------------

mysql Query :

SELECT name1 , name2, addr1 FROM table1
UNION
SELECT Company, "", Address FROM table2
UNION
SELECT studname1, studname2, address FROM table3;



Result will be

---------------------------------------------------
| name1 | name2 | add1 |
----------------------------------------------------
| Princy | Peter | R/220 V st |
----------------------------------------------------
| Joban | John | R/456 Mst |
----------------------------------------------------
| TCS | | 24/cChennai |
-----------------------------------------------------
|Cubet | | 43C EKM |
-----------------------------------------------------


Union all is the default in UNION and Distinct can be used to avoid the duplication in selection



Good Luck :)

Pattern Matching in mysql

My sql provides two functions LIKE and NOTLIKE for Simple pattern matching

LIKE

Syntaxt for using like is expr

LIKE <pattern >

The Like provides '%' and _ as patterns

For example consider following table

sampletable
---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| Neena |T |
-------------------------------
| James| Mathew |
-------------------------------
|sreejitha|M |
-------------------------------
| Nitha | sree |
------------------------------

different types of patters using like


SELECT * FROM sampletable WHERE fname LIKE 'J%';

This will select first and 3rd row from the above table .

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| James| Mathew |
------------------------------


SELECT * FROM sampletable WHERE fname LIKE '%J%';

output

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| James| Mathew |
-------------------------------
|sreejitha|M |
-------------------------------

Next we can use five '_' to get records in which fname has 5 letters
SELECT * FROM sampletable WHERE fname LIKE '_____';

output

---------------------------------
|fname | lname |
------------------------------
| Joban | John |
-------------------------------
| Neena |T |
-------------------------------
| James| Mathew |
-------------------------------
| Nitha | sree |
------------------------------

REGEXP :

It matches a pattern with regular expression

Following is the pattern which can be used along with REGEXP

* a '.' can be used for any single character.

* s set of character in "[...]" matches any character within the brackets.

* "*" matches zero or more instances of the thing preceding .

* can use "^" at the beginning or "$" at the end of the pattern.

* + 1 or more

* ? 0 or 1

* {n} exactly n

* {n,} n or more

* {n,m} between n and m

* | either, or

* [^...] Any character not listed between the square brackets


some sample queries are shown below.

* for the names containing a "j" we can use the query
SELECT * FROM sampletable WHERE fname REGEXP 'j';

* for the names beginning with J we can write a query as follows
SELECT * FROM sampletable WHERE fname REGEXP '^J';

* for the nameending with 'a' we can write a query like
SELECT * FROM sampletable WHERE fname REGEXP 'a$'

* for the name with four characters we can use the query
SELECT * FROM sampletable WHERE fname REGEXP '^....$';
(or)
SELECT * FROM sampletable WHERE fname REGEXP '^.{4}$'

* Query to find all the names starting with a vowel and ending with 'y'
SELECT * FROM sampletable WHERE fname REGEXP '^[aeiou]|y$';





Enjoy :)

MySQL INDEX

A database index is a data structure that improves the speed of operations in a table.Indexes are also can be considered as a type of tables which keeps primary key or index field and a pointer to each record in to the actual table.The users cannot see or use the indexes defined on tables , they are used by Database Search Engine to locate records very fast.

When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

Indexes help us to find data faster. It can be created on a single column or a combination of columns. A table index helps to arrange the values of one or more columns in a specific order.

* Allow the server to retrieve requested data, in as few I/O operations
* Improve performance
* To find records quickly in the database

for example


CREATE TABLE sampletable (id INT, fname VARCHAR(50), lname VARCHAR(50), INDEX (id))

Simple and Unique Index:
in this type of indexing two rows cannot have the same index value

syntax :CREATE UNIQUE INDEX ON ( column1, column2,...);

CREATE UNIQUE INDEX F_INDEXON tab1 (tab2)


Points to consider for optimizing the MySQL Indexes

* The columns with the most unique and variety of values should be used.
* Smaller the index better the response time.
* For functions that need to be executed frequently, large indexes should be used.
* Avoid use of index for small tables.


Good Luck :)

MySQL stored procedure:

It is a block of code stored on the server which executes a set of MySQL statements which increases performance of application.Once created, stored procedure is compiled and stored in the database catalog. It reduced the traffic between application and database server because instead of sending multiple uncompiled commands statement, application only has to send the stored procedure name and get the result back.It is reusable to any application

disadvantages of stored procedures

Stored procedure make the database server high load in both memory for and processors.
it only contains declarative SQL so it is very difficult to write a procedure with complexity of requirement


CREATE PROCEDURE myproc()
BEGIN
SELECT FROM tab;
END


MySQL Triggers

MySQL trigger is a piece of code that fires whenever an event occures to a table.The event can be a DML statement such as

delete - the trigger fires whenever 'delete' command executes on the table
insert - the trigger fires whenever 'insert' command executes on the table
update - the trigger fires whenever the table is updated

The trigger may be fired before the event occurs or after the event occurs


When creating a trigger you need to specify four pieces of information:

The unique trigger name
associated table
The event that the trigger should respond to (DELETE, INSERT, or UPDATE)
When the trigger should be executed (before or after processing)

CREATE TRIGGER trigger_name
ON table_name
FOR EACH ROW
BEGIN

END

create database tabdb
use tabdb
create table tab1(int val1);
create table tab2(int val2);
create table tab3 ( int val3 auto_increment PRIMARY KEY);


CREATE TRIGGER tabtrig BEFORE INSERT ON tab1
FOR EACH ROW BEGIN
INSERT INTO tab1 SET val1 = NEW.val1;
DELETE FROM tab3 WHERE val3 = NEW.val1;
END;

Advantages of using triggers

Using a trigger You can use them to check for, and prevent, bad data entering the database
you can catch the errors in business logic in the database level.
trigger provides an alternative way to run scheduled tasks. you can handle those tasks before or after changes being made to database tables.
A trigger generally performs the types of tasks described faster than application code, and and can be activated easily and quickly behind the scenes and does not need to be a part of your application code

While trigger is implemented there are some restrictions like following:

it's not allowed to call a stored procedure in a trigger.
It's not allowed to create a trigger for views or temporary table.
It's not allowed to use transaction in a trigger.
'Return' is not possible with a trigger.
triggers for a database table must have unique name. It is allowed that triggers for different tables having the same name but it is
recommended that trigger should have unique name in a specific database.

A stored procedure can only be run by some one or something and that's where the MySQL trigger is used.MySQL triggers are simple, effective, way of managing data in a database - the database user needs to be aware of the entering data and if stored procedures are used then the programming will take care of everything else.

Move mysql database tables to another database.

Back up your mysql database using SSH (to dumb a database)
 
use this command:

mysqldump -u username -p dbname >filename.sql

then the console will ask for password and enter the db password.
The sql file will generate and save into the current folder.
please check your folder whether the sql file generated or not using 'ls' command

load mysql to a database from a sql file

use this command:

mysql -u username -p dbname < filename.sql

then the console will ask for password and enter the db password.
the sql instructions from the sql file will loaded and generated in the database.
please check the database whether the sql is loaded or not.


enjoy mysql querying.........

Relational DBMSes


There is a table of two fields, primary key integer ID and
char(50) VALUE. Before adding the unique index to it, you need to
know, if there are duplicated VALUEs in the table. How you will do it?

Ans: should check the primary key integer is repeating in the
primary key field. If any one value from the field returning the
count greater than 1, we can not add unique index to the the field

What's the difference between INNER JOIN and OUTER JOIN? What
other types of JOINs do you know?

Ans : The INNER JOIN takes data from both tables returns the
specified data exists in both tables. But the OUTER JOIN check both
tables and returns values from the outer table when the criteria mets.

Other major join are LEFT OUTER JOIN, RIGHT OUTER JOIN.

What is a VIEW? What are the advantages and disadvantages of views?

Ans : View is a representation of a sql statement stored in
memory, which can be easily reusable.
Advantages of views:
we can view the data without storing the data into the object.
We can restict the view of a table i.e. can hide some of columns
in the tables.
We can Join two or more tables and show it as one object to user.
Disadvantages of views
we can not use DML operations on views.
When a table is dropped view will becomes inactive.. it depends on
the table objects.
It is an object so it occupies space

What's the main difference between WHERE and HAVING?

Ans: WHERE is a single row function, where as Having is based on groups.
When we use having Having we should use the Group by keyword.

What are subqueries? Does MySQL support them?

A query that is used within another query. For example a
select-statement within the WHERE or HAVING clause of another SQL
statement.
Mysql supporting the subquery system.

please complete the questions through the comments

thanks