Change timeouts in my.ini (mysql ini file in xampp) change the following values to: SET GLOBAL connect_timeout = 600;SET GLOBAL net_read_timeout = 600; Now restart mysql in xampp. Put .sql…
Rename the folder mysql/data to mysql/data_old (you can use any name)Create a new folder mysql/dataCopy the content that resides in mysql/backup to the new mysql/data folderCopy all your database folders that are in mysql/data_old to mysql/data (skipping the mysql, performance_schema, and phpmyadmin folders from data_old)Finally copy the ibdata1 file from mysql/data_old and…
Change a table name ALTER TABLE old_table RENAME new_table; Move a table from one database to another CREATE TABLE new_database_name.new_table_name LIKE original_database_name.original_table_name; INSERT new_database_name.new_table_name SELECT * FROM original_database_name.original_table_name; Difference between…
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table (using the foreign key) will…
Notes taken from Brian Green's excellent video on normalization on youtube : https://www.youtube.com/watch?v=QqlPXKxN6LQ Normalisation is a process of removing anomalies from a database. These anomalies include: insertion, update and deletion…
CREATE TABLE devices ( id INT AUTO_INCREMENT PRIMARY KEY, uniqueID int NOT NULL UNIQUE, firstName VARCHAR(100), secondName VARCHAR(100) ); INSERT INTO devices (firstName, secondName)VALUES ('James','Froggatt'); ... now INSERT INTO devices…
Large cities and towns from https://www.citypopulation.de/Europe.html Regular expressions used in Notepad++ to speed up formatting data for my SELECT queries. ^ = beginning of new line $ = end of…
Go to https://planet.openstreetmap.org/ and download the latest openstreetmap .pbf file. It's big so you will have to wait a while. 2) Download OSMConvert - we need this software to crop…
This data is purchased from www.worldcitiesdatabase.com. There are some errors in the instructions so this is the original version with my adjustments so that it works. These instructions are also…