Showing posts with label MYSQL Interview Questions. Show all posts
Showing posts with label MYSQL Interview Questions. Show all posts

Thursday, 12 October 2017

What is Mysql


What is Mysql?
MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.
Today, Its most widely used in Web Development.




What are Client programs?
Communicating with the server to manipulate information.The following list of client programs:
  • MySQL Query Browser and MySQL Administrator.
  • All mysql is a command-line program.


What are three layer of Mysql Architecture?
Following are 3 layer of Mysql Architecture
  1. Application Layer: It represents the interface for all type of users, The user who is interacting with databases.
    • Sophisticated User
    • Specialized User
    • Naive User
    • Database Administrators
  2. Logical Layer: Core functionality of RDBMS is represented in the logical layer.
  3. Physical Layer: It is responsible for the storage of variety of information and that are following.
    • Data Files: It store user data
    • Data dictionaryr: store metadata about the structure of the database.
    • Statistical Data: Statistical information about the data
    • Log Information: Keep the track of query information.



Question: In which language it was written?
C/C++


Question: What is current Stable Version ?
5.7.21 / 15 January 2018;


Question: How to change the collation of column, table and database?
ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;




Tuesday, 24 May 2016

MySQL Interview Questions and Answers for 3 year experience

MySQL Interview Questions and Answers for 3 year experience

Question: How can I enable Slow MySQL's query log?
Just set the global variable as following:
SET GLOBAL slow_query_log = 'ON';



Question: How to trim the white space from values?
 UPDATE tablename set name = TRIM(name);



Question: How to add more values in enum field?
ALTER TABLE tablename MODIFY COLUMN country ENUM('india','usa','uk','china');



Question: How do I check if an index exists on a table field in MySQL?
SHOW INDEX FROM tablename



Question: What is the maximum length of data I can put in a BLOB column in MySQL?
A BLOB - 65535 bytes maximum
A MEDIUMBLOB - 16777215 bytes maximum
A LONGBLOB - 4294967295 bytes maximum


Question: How to check empty field in Mysql
select * from where 1 AND (email != "" AND email IS NOT NULL);



Question: How to display current connection info?
SELECT USER();



Question: How to display List of database with current connection?
SELECT DATABASE();



Question: How to get count of distinct record?
 select count(distinct email) from users



Question: How to get count of distinct record?
 UPDATE users SET   email = NULL WHERE  id = 1000;



Question: How to copy data from one table to another table?
INSERT INTO table2 (id,uid,changed,status,assign_status) SELECT id,uid,now(),'Pending','Assigned' FROM table1



Question: How to get a list of MySQL views?
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';



Question: How to Copy table without copying data?
CREATE TABLE users_bck SELECT * FROM users WHERE 1=0;



Question: How to Repair all tables?
mysqlcheck -A --auto-repair;



Question: How to increase MySQL connection timeout?
Open MySQL file and increase the following
ini_set('mysql.connect_timeout', 600);//20 min
ini_set('default_socket_timeout', 600); //20 MIn



Question: Which MySQL datatype for an IP address?
As per me, It should be Int(11) unsigned.


Question: How to get last 30 days record?
SELECT  * FROM    users WHERE   created_date < DATE_ADD(NOW(), INTERVAL +1 MONTH);

Here created_date id datetime field in which we store the insertion date.


Question: Differences between utf8 and latin1?
In latin1 each character is exactly one byte long.
In utf8 a character can consist of more than one byte.



Question: How to convert the data type from one to another?
Convert String to DateTime
SELECT CONVERT('2017-04-06 08:25:57', DATETIME); //'2017-04-06 08:25:57'

Convert String to Time
SELECT CONVERT('06:07:58', TIME); //06:07:58

Convert Number to String
SELECT CONVERT(125, CHAR);  //125




Question: How to convert the Convert Character Sets from one to another?
SELECT CONVERT('hello user, how are you?' USING utf8); //



Question: How to get datetime when it was in another format?
SELECT STR_TO_DATE('18,05,2017','%d,%m,%Y'); //2017-05-18

Monday, 23 May 2016

MySQL Interview questions and answers for 2 year experience

MySql Interview questions and answers for 2 year experience

Question: What is current stable version of MySQL
Version: 5.7.12 / Dated: 11 April 2016


Question: How to change MySQL data directory? Wamp Server Directory: E:\wamp\www
MySQL Directory Path: E:\wamp\bin\mysql\mysql5.5.24
Open mysql configuration file. (In My Case file: my.ini in E:\wamp\bin\mysql\mysql5.5.24).
Search for "datadir" Replace
datadir=E:/wamp/bin/mysql/mysql5.5.24/data
with
datadir=F:/wamp/bin/mysql/mysql5.5.24/data




Question: From where I can change the MySQL setting?
open my.ini in E:\wamp\bin\mysql\mysql5.5.24


Question: How to allow remote connection to mysql?
  1. GRANT the permission
  2. open my.ini search following and comment.
    bind-address = 127.0.0.1
  3. restart the MySQL server


Question: How to get MySQL Results as comma separated?
SELECT GROUP_CONCAT(id) as city_comma FROM `cities` where country_code='US';
/*1,3,7,15,19,21,30,31,32,51,54,60,61,65,67,70,73,7*/



Question: How to get MySQL tables size for particular database?
SELECT table_name "Table Name",sum( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",sum( data_free )/ 1024 / 1024 "Free Space(MB)" FROM information_schema.TABLES  where table_schema='mydb' GROUP BY table_name;

How to get MySQL tables size for particular database



Question: Can we join same table twice ?
Yes, We can do.
SELECT t.phone1, t.phone2, 
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2
FROM Table1 t
JOIN Table2 t1 ON t1.phone = t.phone1
JOIN Table2 t2 ON t2.phone = t.phone2



Question: How to change the collation of column, table and database?
Change the collation of column
ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Change the collation of Table
ALTER TABLE `mydb`.`hotel`  ENGINE=INNODB AUTO_INCREMENT=8 COMMENT='' ROW_FORMAT=DEFAULT CHARSET=utf8 COLLATE=utf8_icelandic_ci;

Change the collation of Database
ALTER DATABASE `mydb` CHARACTER SET latin1 COLLATE latin1_general_ci;



Question: How to declare a variable in MySQL?
How to set variable
set @start = 1, @finish = 10;  

How to use static variables
select @start, @finish   



Question: How to get records with max value for each group of grouped results
SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;


Question: How to write case-insensitive query ?
Make the both in uppercase and search like below
select * from users where upper(name) = upper('Hello');




Question: How to update the column where NULL value set?
update `users` set phone='000000000' where  phone is NULL




Question: How to get number of days difference between two date?
SELECT DATEDIFF('2010-10-22', '2010-10-19');



Question: How to replace null with 0 in MySQL?
SELECT COALESCE(column_name, 0) AS column_name FROM table_name;

IF column_name have NULL value, It will return 0
IF column_name have Non-NULL value, It will return column value.



Question: Why we should not use mysql_connect functions?
You should not use mysql_* functions like mysql_query(), mysql_connect() or mysql_real_escape_string(). Because Its already deprecated in PHP5.5 and not in active development.
Instead of this, you can use mysqli OR PDO functions.


Question: How to get a list of MySQL user accounts?
SELECT User FROM mysql.user;



Question: Which MySQL data type to use for storing boolean values?
You can use bit.


Question: How to concatenate multiple rows column into one?
You can use GROUP_CONCAT
SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')FROM hobbies GROUP BY person_id

Output
1 cricket, footbal
1 volleyball, footbal, wrestling
1 cricket, volleyball




Thursday, 3 March 2016

Advanced MySQL Interview Questions and Answers for Experienced

Advanced MySQL Interview Questions and Answers for Experienced

Question: What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.
Check Important Points about Heap Tables?
  • BLOB or TEXT fields are not allowed.
  • Only comparison operators can be used like =, <,>, = >,=<.
  • AUTO_INCREMENT is not supported by HEAP tables.
  • Indexes should be NOT NULL.



Question: What are the advantages of MySQL over Oracle?
  • MySQL is open source software whereas Oracle is not.
  • MySQL is easily portable.
  • Administration is supported using MySQL Query Browser
  • MySQL is lightweight applications as compare to Oracle.

Question: Difference between FLOAT and DOUBLE?
1. Floating point numbers are stored in FLOAT whereas Double are stored in DOUBLE.
2. Float takes 4 bytes whereas DOUBLE takes eight bytes.
3. FLOAT is for single-precision whereas DOUBLE is for double-precision numbers.
4. Float have accuracy up to eight place whereas DOUBLE upto 18 placeS.


Question: What is the usage of ENUMs in MySQL?
ENUM is a string object used to specify set of predefined values. When we add/update record(s), Enum field will save only single value from predefined values.


Question: What is REGEXP in MySQL?
It is regular expression is a used for complex search using pattern.
See Example
SELECT * FROM users WHERE name REGEXP '^[aeiou]|ok$';


Question: What are the drivers in MySQL?
  • PHP Driver
  • JDBC Driver
  • ODBC Driver
  • C WRAPPER
  • PYTHON Driver
  • PERL Driver
  • RUBY Driver
  • CAP11PHP Driver
  • Ado.net5.mxj


Question: What is the difference between primary key and candidate key?
Primary Key:
Every row of a table is identified uniquely by primary key and there is only one primary key fore each table.

Candidate keys:

These are those keys which is candidate for primary key of a table means a key which full fill all the requirements of primary key.


Question: What does myisamchk do?
Myisamchk compress the MyISAM tables, which reduces the disk or memory usage.


Question: What is the difference between MyISAM Static and MyISAM Dynamic?
MyISAM static will have fixed width for all the fields. Also Its easier to restore in case of corruption.
MyISAM Dynamic will have variable width like TEXT,BLOB.


Question: What are Federated tables?
A Federated Table is a table which points to a table in an other MySQL database instance (Might be on same OR Different server).


Question: What is timestamp meaning in MySQL?
timestamp is datatype in MySQL. If we create a filed with timestamp datatype, it will auto-update with current date/time when record is added/updated.


Question: What happens when auto_increment on integer column reaches the max_value in databases?
It stops incrementing anf through following error.
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine.



Question: How can you list all indexes in a table?
SHOW INDEX FROM user;


Question: What is ISAM?
ISAM stands for Indexed Sequential Access Method, a method for indexing data for fast retrieval.


Question: What is the different between NOW() and CURRENT_DATE()?
NOW () is used to show current year,month,date, hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.


Question: How many triggers are allowed in MySQL table?
Following are the triggers which are allowed in MySQL table.
  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE and
  • AFTER DELETE



Question: How to insert if record have containing single quotes?
Escape it with Slash like below
INSERT INTO `users` (`id`,`name`) VALUES (NULL,  'this is test\'s message');




Question: How to get Last insertID in MYSQL ?
Use LAST_INSERT_ID() MySQL Function
INSERT INTO `users` (`id`,`name`) VALUES (NULL,  'this is test\'s message');
SELECT LAST_INSERT_ID();




Question: How many columns can we create for index?
16


Question: What is Query to delete a Index?
ALTER TABLE table_name DROP INDEX index_name.


Question: How to update auto increment value to 1000?
ALTER TABLE tbl_name AUTO_INCREMENT = 1000;


Question: What is command to check table is exist?
CHECK TABLE table_name;


Question: What is command to display all databases?
SHOW DATABASES;


Question: What is command to display current Date and Time ?
SELECT NOW();


Question: What are the objects you can use with CREATE statement?
  • DATABASE
  • TABLE
  • TRIGGER
  • USER
  • VIEW
  • EVENT
  • FUNCTION
  • INDEX 
  • PROCEDURE.



Question: What are the nonstandard string types?
TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT


Question: What is SQLyog?
The SQLyog program is Software which provide GUI tool for MySQL.


Question: What are string datatype in MySQL?
  • CHAR
  • VARCHAR
  • TINY TEXT
  • TEXT
  • MEDIUM TEXT
  • LONG TEXT
  • BINARY
  • VARBINARY
  • TINYBLOB
  • MEDIUMBLOG
  • BLOB
  • LONGBLOB
  • ENUM
  • SET



Question: How to get the next auto-increment id in mysql?
Use LAST_INSERT_ID() from your MySQL query.

Question: How to update the column where NULL value set?
update `users` set phone='000000000' where  phone is NULL




Question: How to get number of days difference between two date?
SELECT DATEDIFF('2010-10-22', '2010-10-19');



Question: How to reset the autoincrement?
ALTER TABLE tablename AUTO_INCREMENT = 1




Thursday, 18 February 2016

How can I make SQL Query case sensitive string comparison on MySQL?

How can I make SQL Query case sensitive string comparison on MySQL

In Websites we display the data which fetch from database using string based conditions.
OR
Login Feature / Change password Featured based on string based conditions.
OR
Show the user detils behalf on user slug like "http://example.com/users/user-slug-value"


If you want to make your SQL Query as case sensitive, We have following 3 ways.

  1. Do at MySQL Server Level
    Set Following in /etc/mysql/my.cnf (Works for >MySQL 5.5 only)
    character-set-server=utf8
    collation-server=utf8_bin
  2. Set the column as Case sensitive,For Example
    ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
  3. Set the Query as case sensitive, For Example
    SELECT *  FROM `users` WHERE BINARY `name` = 'raJ'



Wednesday, 3 June 2015

PHP Mysql Interview Questions and Answers for fresher and experienced

PHP Mysql Interview Questions and Answers for fresher and experienced


Question: How many ways we can retrieve the data in the result set of MySQL using PHP?
Following are different ways
$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    //$row have results
}

$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_assoc($sqlQuery)) {
    //$row have results
}

$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_object($sqlQuery)) {
   //$row have results
}



Question: How can we create a database using PHP and MySQL?
mysql_create_db('db_name');



Question: How can we repair a MySQL table?
Use any of below as per requirement:
REPAIR tablename; 
REPAIR tablename quick;
REPAIR tablename extended;



Question: How can we find the number of rows in a result set using PHP?
$sqlQuery = mysql_query("SELECT id, name FROM users");
$num = mysql_num_rows($sqlQuery);



Question: How to update auto increment value for 100000?
ALTER TABLE table_name AUTO_INCREMENT = 100000;



Question: How to get the current version number of MYSQL?
SELECT VERSION();



Question: What is difference between between NOW() and CURRENT_DATE()?
Now gives the current date, hour and minutes
select NOW()
//2015-06-03 19:17:23  



CURRENT_DATE gives the current date only.
select CURRENT_DATE()
//2015-06-03

Question: List all databases?
SHOW DATABASES



Question: What is mysql query to show the first 100 records?
SELECT * FROM user LIMIT 0,100


Question: How many TRIGGERS allows per table in mysql?
Following are 6 triggers in mysql for table.
1. BEFORE INSERT,
2. AFTER INSERT,
3. BEFORE UPDATE,
4. AFTER UPDATE,
5. BEFORE DELETE
6. AFTER DELETE



Question: What is difference between COMMIT and ROLLBACK?
COMMIT: Mostly it is used in transaction and commit means all process are completed successfully. Once commit done you can not revert.
ROLLBACK: Mostly it is used in transaction and ROLLBACK means all process are NOT completed successfully. So revert the db changes automatically.


Question: What is SAVEPOINT?
The SAVEPOINT statement is used to set a savepoint with a name in transaction. used for roll back the transaction to the named savepoint specified instead of all the changes.


Question: How to find the number of days between two dates?
$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60*24));


Question: How to find the number of hours between two dates?
$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60));


Question: How to find the number of minutes between two dates?

$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60));


Question: How do I get random item from an array?

$array = array('','s','d');
echo $array[array_rand($array)];


Question: How to update the max_allowed_packet mysql variable?
You can check the value of max_allowed_packet with following query.
SHOW VARIABLES LIKE 'max_allowed_packet'; 

For Update, You have to change in configuration mysql.
File Location in My Wamp Server: D:\wamp\bin\mysql\mysql5.6.17\my.ini
Now search with max_allowed_packet and update, as per requirement.


Question: List all the tables whose engine is InnoDB?
SELECT table_name FROM INFORMATION_SCHEMA.TABLES  WHERE ENGINE = 'InnoDB'




Question: How to update the column where NULL value set?
update `users` set phone='000000000' where  phone is NULL





Monday, 1 June 2015

Differences between Stored Procedures and Functions in MYSQL

Differences between Stored Procedures and Functions in MYSQL

  1. Stored Procedure can return zero or n values whereas function can return one value which is mandatory.
  2. Functions can be called from procedure whereas procedures cannot be called from function.
  3. Procedures can have input/output parameters for it whereas functions can have only input parameters.
  4. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
  5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
  6. We can go for transaction management in procedure whereas we can't go in function.
  7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

Wednesday, 8 April 2015

PHP MySQL interview questions and answers for experienced

PHP MySQL interview questions and answers for experienced




Question: What are 3 different ways to connect  with MySQL?
Following are 3 API with which we can connect to MySQL
1. MySQL
2. MySQLI
3. PDO

We should use MySQLI because MySQLI is imporoved version of MySQL and it have lot of functions which are absent in MySQL.


Question: What is the best collation to use for MySQL?
utf8_general_ci: It is used for fast sorting but it is less accurate.
utf8_unicode_ci: It is used for better accuracy but little slow as compare to utf8_general_ci.

You can also use for specific languages like utf8_swedish_ci.


Question: Give an example of Transaction with commit and rollback?
try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown which we are handling at bottom
    $db->query('1 query exe');
    $db->query('2 query exe');
    $db->query('3 query exe');
    
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown by try
    //This function have all the things which you want to rollback.

    $db->rollback();
}


Question: How to use "MySQL in clause" for "string search" and "number search"?
When we are searching with number: We need not to add single quote
$sql=mysql_query('SELECT * FROM `users` WHERE id IN ( 1, 2, 34 )');
while($data = mysql_fetch_array($sql)){
    //write here your code
}
When we are searching with string:Its compulsory to add single OR double quote.
$sql=mysql_query("SELECT * FROM `users` WHERE email in('myemail@gmail.com','anotheremail@gmail.com','testemail@gmail.com')");
while($data = mysql_fetch_array($sql)){
    //write here your code
}


Question: How to get the closed 10 locations between with lat long?
You can try below MySQL as its working for me:
$sql='SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance 
FROM city HAVING distance < 25 ORDER BY distance asc limit 0,10';

while($data = mysql_fetch_array($sql)){
    //write here your code
}



Question: How to use update query based on SELECT Query?
You have to use the join for this, See below format:
update tab1
left join table2 tab2 on
    tab1.name = tab2.name
set
    update_field = if(start_dates > end_dats, 'valid', 'Not-valie')



Queston: How to optimize table?
OPTIMIZE TABLE `users` 



Question: How to repair table?
you can use any of below:
REPAIR TABLE users
REPAIR TABLE users QUICK
REPAIR TABLE users EXTENDED






Monday, 6 April 2015

MySQL Interview Questions and Answers for Experienced

MySQL Interview Questions and Answers for Experienced

Question: Can I concatenate multiple MySQL rows into one field?
use GROUP_CONCAT function
For Example
SELECT group_concat( city, '--' ) AS all_city FROM `cities`WHERE 1 LIMIT 0 , 30
Output:
New York--,London--,Chicago--,Frankfurt--,Paris--



Question: How to output MySQL query results in csv format?
Its quite simple.
  • First get the queries with column which you want to write.
  • Seconds Use OUTFILE (filename with location), FIELDS TERMINATED BY (field separator), ENCLOSED BY and LINES TERMINATED (Line Separator) BY for format.

SELECT city from cities INTO OUTFILE 'city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'



Question: How do you set a default value for a MySQL Datetime column?
You can add default time, when data type is timestamp.
See Example:
CREATE TABLE `mytable` (`id` INT NULL ,`created_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,PRIMARY KEY ( `id` ) ) ENGINE = InnoDB;



Question: Difference between utf8_general_ci and utf8_unicode_ci?
utf8_unicode_ci is more accurately as compare to utf8_general_ci.
utf8_unicode_ci is support more lanaguages as compare to utf8_general_ci.
utf8_general_ci is faster at comparisons and sorting as compare to utf8_general_ci.



Question: How to make multiple column as unique?
use UNIQUE Constraint for creating multiple column as unique.
For Example
ALTER TABLE `mytable` ADD UNIQUE `unique_index`(`user`, `email`, `address`);



Question: How to find duplicate email records in MySQL?
SELECT u1.first_name, u1.last_name, u1.email FROM users as u1
INNER JOIN (
    SELECT email FROM users GROUP BY email HAVING count(id) > 1
    ) u2 ON u1.email = u2.email;



Question: How can you reset AUTO_INCREMENT for user table?
ALTER TABLE users AUTO_INCREMENT = 1



Question: How to make duplicate table in MySQL?
CREATE TABLE users_new LIKE users; //Copy the structure
INSERT users_new SELECT * FROM users; //copy the table rows



Question: How to Group by multiple column(i.e email and ssn)?
select * from users group by concat(email,"-",ssn);


Question: MySQL: @variable vs. variable. Whats the difference?
@variable are user-defined variable used in statements which retain its values during the same session is active.
variable are procedure variable used in store procedure only. It never retain its value. Each time store procedure called its value set to null automatic.



Question: What is query to show list of Stored Procedures?
SHOW PROCEDURE STATUS;


Question: What is query to show list of functions?
SHOW FUNCTION STATUS;


Question: How to check last queries executed on MySQL?
check mysql.log


Question: How to get the sizes of the tables of a mysql database?
SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "geob2cdev" ORDER BY (data_length + index_length) DESC 


Question: How to check specific table details?
SHOW TABLE STATUS WHERE Name = 'users';


Question: How to rename a table?
RENAME TABLE `mytable` TO `geob2cdev`.`mytable1` ;


Question: How to rename a column?
ALTER TABLE `mytable1` CHANGE `created_at` `created_at1` TIMESTAMP; 

Question: How to insert current datetime in column?
Use Now() function
INSERT INTO `users` (`id`,`name`,create_time) VALUES (NULL,  'this is test\'s message',now());




Question: Can we have multiple order by in single Query?
Yes, We can have
select * from users where upper(name) = upper('Hello') order by name asc, id desc




Question: How to remove stored PROCEDURE?
DROP PROCEDURE storedProcedureName



Question: How to remove stored PROCEDURE if exist?
DROP PROCEDURE IF EXISTS storedProcedureName




Thursday, 8 January 2015

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY

Following are few reasons with solutions:

Reason 1:
ID is auto-increment and primary key. id is auto filed when an record is insert.
When we try to insert record with same id which is already exist in table, then such type of error comes.
SOLUTION:
So, write SQL in following ways.
INSERT INTO users (name) VALUES ('name1'); //skip the id field which is auto-increment


Reason 2:
When table is used to frequent insertion of records, like we are storing db logs for each action. Sometimes, it have thousand of thousands records, and field(like id) limit cross the limit. SOLUTION: Increase the limit of id field.
ALTER TABLE `users` CHANGE `id` `id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT; 
Reason 3:
When table is used to frequent insertion of records, like we are storing db logs for each action.Sometimes, it have thousand of thousands records, and table goes to overhead.
SOLUTION: Execute following query
OPTIMIZE TABLE `users`

Monday, 24 November 2014

Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL

Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL

There are three types of privileges in MySql

  1. Administrative privilege(s) enable users to manage operation of MySQL server. These are global because they are not specific to a particular mysql database. 
  2. Database privileges apply to a database and to all objects within it, means apply to specific database. 
  3. Privileges for database object(s) such as table(s), indexe(s), stored routine(s), and view(s). These privileges apply table, index, Stored Procedure, views etc. of a single database.  It can be granted for specific objects within a database OR for all objects of a given type within a database.


Following are  permissible Privileges for GRANT and REVOKE
PrivilegeColumnContext
CREATECreate_privdatabases, tables, or indexes
DROPDrop_privdatabases, tables, or views
GRANT OPTIONGrant_privdatabases, tables, or stored routines
LOCK TABLESLock_tables_privdatabases
REFERENCESReferences_privdatabases or tables
EVENTEvent_privdatabases
ALTERAlter_privtables
DELETEDelete_privtables
INDEXIndex_privtables
INSERTInsert_privtables or columns
SELECTSelect_privtables or columns
UPDATEUpdate_privtables or columns
CREATE TEMPORARY TABLESCreate_tmp_table_privtables
TRIGGERTrigger_privtables
CREATE VIEWCreate_view_privviews
SHOW VIEWShow_view_privviews
ALTER ROUTINEAlter_routine_privstored routines
CREATE ROUTINECreate_routine_privstored routines
EXECUTEExecute_privstored routines
FILEFile_privfile access on server host
CREATE USERCreate_user_privserver administration
PROCESSProcess_privserver administration
RELOADReload_privserver administration
REPLICATION CLIENTRepl_client_privserver administration
REPLICATION SLAVERepl_slave_privserver administration
SHOW DATABASESShow_db_privserver administration
SHUTDOWNShutdown_privserver administration
SUPERSuper_privserver administration






Following are mysql grant permission example:

GRANT SELECT ON databasename.* TO user@'localhost';
/* Give the select permission to user i.e user for database databasename*/

GRANT SELECT ON databasename.* TO user@'localhost' IDENTIFIED BY 'password';
/* Give the select permission to user i.e user for database databasename and create the user*/

GRANT SELECT, INSERT, DELETE ON databasename TO username@'localhost' IDENTIFIED BY 'password';
/* Give the SELECT, INSERT, DELETE permission to user i.e user for database databasename*


select * from mysql.user where User='username';
/*To see a list of the privileges that have been granted to a specific user:*/


GRANT all privileges ON databasename.* TO user@'localhost';
/* Give all permissions to user i.e user for database databasename*/




Thursday, 9 October 2014

Calculate a Sum of Time using MySQL

Calculate a Sum of Time using MySQL

We have Following Table Structure of myvideos

CREATE TABLE IF NOT EXISTS `myvideos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `time` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='3' AUTO_INCREMENT=5 ;


INSERT INTO `myvideos` (`id`, `name`, `time`) VALUES
(1, 'Video 1', '02:02:00'),
(2, 'Video  2', '02:02:00'),
(3, 'Video  3', '02:02:00'),
(4, 'Video  4', '02:04:00');


Problem: How to calculate the sum of time of video?


Solution:
select sec_to_time(sum(time_to_sec(time))) as total_time from myvideos