Ivan's Blog

Lets learn new things!

[SQL] Information schema

MySQL: How to rename a database 1. For InnoDB, create the new empty database, then rename each table in turn into the new database: [code language=”sql”]RENAME TABLE old_db.table TO new_db.table;[/code] This also work fine in MyISAM 2. Create the destination database before running the script generated from the command. [code language=”sql”]SELECT concat(‘RENAME TABLE $source.’,table_name, ‘ TO $destination.’,table_name, ‘;’) FROM information_schema.TABLES … Read More

[SQL]Some Common SQL Query

Create a temperature table with these columns id, temperature, time, date. SQL Query to find second highest temperature from the table: [code language=”sql”] select MAX(temperature) from temperature WHERE temperature NOT IN (select MAX(temperature) from temperature ); [/code] Another way: [code language=”sql”] SELECT `temperature` FROM `temperature` ORDER BY `temperature`.`temperature` DESC LIMIT 1,1 [/code] SQL Query to find maximum temperature of per … Read More

[SQL] Join

INNER JOIN gets all records from one table that have some related entry in a second table LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL RIGHT JOIN is like the above but gets all records in … Read More