banner
Jan 13, 2015
20 Views
Comments Off on MySQL usefull queries & points
0 0

MySQL usefull queries & points

Written by
banner

Here I want to share some useful MySQL queries , tips & points
1. To get last executed queries in MySQL :
Ans:

You can get last executed queries in MySQL by command :
First on "general_log" by-
set global general_log='ON';

Then:
set global log_output='TABLE';
after that you can get results by query :
select * from mysql.general_log;


2. How to insert data in a table from another table ?
Ans:
Yes , insert data in a table from another table like the following way :
Here is the first table on which , I want to insert data:

CREATE TABLE `demo1` (
`id` INT(10) NULL AUTO_INCREMENT,
`field1` VARCHAR(50) NULL DEFAULT NULL,
`filed2` INT NULL DEFAULT NULL,
`field3` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
);


Now, here is the second table :

CREATE TABLE `demo2` (
`demo2field1` INT(11) NULL DEFAULT NULL,
`demo2field2` VARCHAR(50) NULL DEFAULT NULL,
`demo2field3` VARCHAR(50) NULL DEFAULT NULL
);

And here is the query to insert :

INSERT INTO demo1(field1,filed2,field3) (SELECT demo2field2,demo2.demo2field1,demo2.demo2field3 FROM demo2 WHERE demo2.demo2field1=1);

3. How to create a Table from another Table ?
Ans:
It’s much more easy , here is example :

Syntax: CREATE TABLE LIKE ;
e.g. CREATE TABLE demo2 LIKE demo1;

In here demo2 table will be created with structure demo1.

4. How to get last auto increment id after execution of a query ?
Ans:

SELECT LAST_INSERT_ID();

5. Can you run multiple MySQL servers on a single machine?
Ans: Yes

6.What is the maximum size of a row in a MyISAM table?
Ans: 65,534

7. What is the correct order of clauses in the select statement?
Ans: select,where,group by,having,order by

8.What type of lock will deny users any access to a table?
Ans: EXCLUSIVE

9. Use “regexp” instead of “LIKE” operator.
Ans : Yes , regexp is more efficient , time effective instead of LIKE operator . The syntax is same just use regexp

e.g. SELECT * FROM demo1 where field1 regexp 'searchtext';

10. Store date & time in separate field .
Ans: When you are trying build an application and using background MySQL , and also create a table used to store date & time of an user’s log or any purpose , use date as separate field and time in separate field in mysql table .

Share this:
Article Categories:
Interview Question · MySQL · Web Development
banner

Hi guys, This is the place where developers are sharing there thoughts....

Comments are closed.

Social Widgets powered by AB-WebLog.com.