ALTER TABLE table_name ENGINE=InnoDB
innodb_file_per_table=1;
innodb_file_format = Barracuda;
innodb_log_file_size = 256M
ALTER TABLE tablename AUTO_INCREMENT = 1
SELECT GROUP_CONCAT(id SEPARATOR ', ') FROM users WHERE city = 'india' GROUP BY city;
BIT
utf8mb4
CREATE TABLE foo (
`modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)FLUSH TABLES
DELIMITER $$ CREATE PROCEDURE getUsersAscending() BEGIN SELECT first_name, last_name, email FROM users ORDER BY first_name; END$$ DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `getUsersDetails`(IdSearch INT)
BEGIN
SELECT
first_name,
last_name,
email
FROM
users
WHERE id=IdSearch;
END$$
DELIMITER ;SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE Db = 'db_name';
call getUsersAscending();Call With argument
call getUsersDetails(10);
DROP PROCEDURE IF EXISTS getUsersAscending;
show status where `variable_name` = 'Threads_connected';
SHOW VARIABLES LIKE "max_connections";
show processlist;
SHOW STATUS WHERE `variable_name` = 'Max_used_connections';
SET GLOBAL max_connections = 512;
max_connections = 512
service mysqld restart
show status like 'Connections%';
ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
SET GLOBAL slow_query_log = 'ON';
UPDATE tablename set name = TRIM(name);
ALTER TABLE tablename MODIFY COLUMN country ENUM('india','usa','uk','china');
SHOW INDEX FROM tablename
select * from where 1 AND (email != "" AND email IS NOT NULL);
SELECT USER();
SELECT DATABASE();
select count(distinct email) from users
UPDATE users SET email = NULL WHERE id = 1000;
INSERT INTO table2 (id,uid,changed,status,assign_status) SELECT id,uid,now(),'Pending','Assigned' FROM table1
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
CREATE TABLE users_bck SELECT * FROM users WHERE 1=0;
mysqlcheck -A --auto-repair;
ini_set('mysql.connect_timeout', 600);//20 min
ini_set('default_socket_timeout', 600); //20 MIn
SELECT * FROM users WHERE created_date < DATE_ADD(NOW(), INTERVAL +1 MONTH);
SELECT CONVERT('2017-04-06 08:25:57', DATETIME); //'2017-04-06 08:25:57'
SELECT CONVERT('06:07:58', TIME); //06:07:58
SELECT CONVERT(125, CHAR); //125
SELECT CONVERT('hello user, how are you?' USING utf8); //
SELECT STR_TO_DATE('18,05,2017','%d,%m,%Y'); //2017-05-18
datadir=E:/wamp/bin/mysql/mysql5.5.24/datawith
datadir=F:/wamp/bin/mysql/mysql5.5.24/data
bind-address = 127.0.0.1
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*/
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;
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
ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `mydb`.`hotel` ENGINE=INNODB AUTO_INCREMENT=8 COMMENT='' ROW_FORMAT=DEFAULT CHARSET=utf8 COLLATE=utf8_icelandic_ci;
ALTER DATABASE `mydb` CHARACTER SET latin1 COLLATE latin1_general_ci;
set @start = 1, @finish = 10;
select @start, @finish
SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;
select * from users where upper(name) = upper('Hello');
update `users` set phone='000000000' where phone is NULL
SELECT DATEDIFF('2010-10-22', '2010-10-19');
SELECT COALESCE(column_name, 0) AS column_name FROM table_name;
SELECT User FROM mysql.user;
SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')FROM hobbies GROUP BY person_id
1 cricket, footbal 1 volleyball, footbal, wrestling 1 cricket, volleyball
SELECT * FROM users WHERE name REGEXP '^[aeiou]|ok$';
INSERT INTO `users` (`id`,`name`) VALUES (NULL, 'this is test\'s message');
INSERT INTO `users` (`id`,`name`) VALUES (NULL, 'this is test\'s message'); SELECT LAST_INSERT_ID();
ALTER TABLE table_name DROP INDEX index_name.
ALTER TABLE tbl_name AUTO_INCREMENT = 1000;
CHECK TABLE table_name;
SHOW DATABASES;
SELECT NOW();
update `users` set phone='000000000' where phone is NULL
SELECT DATEDIFF('2010-10-22', '2010-10-19');
ALTER TABLE tablename AUTO_INCREMENT = 1
character-set-server=utf8 collation-server=utf8_bin
ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
SELECT * FROM `users` WHERE BINARY `name` = 'raJ'
$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
}
mysql_create_db('db_name');
REPAIR tablename; REPAIR tablename quick; REPAIR tablename extended;
$sqlQuery = mysql_query("SELECT id, name FROM users");
$num = mysql_num_rows($sqlQuery);
ALTER TABLE table_name AUTO_INCREMENT = 100000;
SELECT VERSION();
select NOW() //2015-06-03 19:17:23
select CURRENT_DATE() //2015-06-03
SHOW DATABASES
SELECT * FROM user LIMIT 0,100
$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60*24));
$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60));
$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60));
$array = array('','s','d');
echo $array[array_rand($array)];
SHOW VARIABLES LIKE 'max_allowed_packet';
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB'
update `users` set phone='000000000' where phone is NULL
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();
}
$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
}
$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
}
update tab1
left join table2 tab2 on
tab1.name = tab2.name
set
update_field = if(start_dates > end_dats, 'valid', 'Not-valie')
OPTIMIZE TABLE `users`
REPAIR TABLE users REPAIR TABLE users QUICK REPAIR TABLE users EXTENDED
SELECT group_concat( city, '--' ) AS all_city FROM `cities`WHERE 1 LIMIT 0 , 30Output:
SELECT city from cities INTO OUTFILE 'city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
CREATE TABLE `mytable` (`id` INT NULL ,`created_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,PRIMARY KEY ( `id` ) ) ENGINE = InnoDB;
ALTER TABLE `mytable` ADD UNIQUE `unique_index`(`user`, `email`, `address`);
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;
ALTER TABLE users AUTO_INCREMENT = 1
CREATE TABLE users_new LIKE users; //Copy the structure INSERT users_new SELECT * FROM users; //copy the table rows
select * from users group by concat(email,"-",ssn);
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
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
SHOW TABLE STATUS WHERE Name = 'users';
RENAME TABLE `mytable` TO `geob2cdev`.`mytable1` ;
ALTER TABLE `mytable1` CHANGE `created_at` `created_at1` TIMESTAMP;
INSERT INTO `users` (`id`,`name`,create_time) VALUES (NULL, 'this is test\'s message',now());
select * from users where upper(name) = upper('Hello') order by name asc, id desc
DROP PROCEDURE storedProcedureName
DROP PROCEDURE IF EXISTS storedProcedureName
![SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlxV_TXTC1EDBhC_FDMORSmgdN7WoC9TfJDMM-FvPB3-g32gbMKyCGYOwnLRNUQ397krCiE4B93gyolGWQbFeRPg0XYKft0dY4hifNRXw2EyoQfrGnb1y-dGGPDK1fw7TiQS3b_RBUfPGo/s1600/Integrity+constraint+violation+1062+Duplicate+entry+2269548+for+key+PRIMARY.png "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY")
INSERT INTO users (name) VALUES ('name1'); //skip the id field which is auto-increment
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 queryOPTIMIZE TABLE `users`