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/datawith
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?
- GRANT the permission
- open my.ini search following and comment.
bind-address = 127.0.0.1
- 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;
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
