Category: MySQL

  • Copying tables in MySQL

    There are multiple ways this can be done. I will mention two here. Method 1 In MySQL, run these statements: /*This will create table2 with the schema of table1*/ CREATE TABLE `table2` LIKE `table1` /*This will copy the data from table1 to table2*/ INSERT INTO `table2` SELECT * FROM `table1` Method 2 On the command…

  • Get just the fields names of a table in MySQL

    If you do: describe `table_name` Mysql will give you table information which will contain field names, their types, etc. What if you want just the field names and nothing else? You can run this query and do just that: SELECT column_name FROM INFORMATION_SCHEMA.columns WHERE table_name = ‘tablename’ AND table_schema = ‘dbname’;

  • Importing a CSV file into a table in MySQL

    Here’s how you would import data into MySQL using a query /* If the CSV file was created in Windows, then replace ‘\n’ with ‘\r\n’ */ LOAD DATA LOCAL INFILE ‘/path/to/csv_file.csv’ INTO TABLE `table_name` FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY “\”” LINES TERMINATED BY ‘\n’ (field1, field2, field3);

  • Cool things you can do with MySQL

    Run a query from command line # Use the -e flag shell>mysql -e “select * from table” Tell MySQL to output the results of a query in HTML or XML # On command line you would write the following to # tell MySQL to output the results in HTML format shell>mysql -e “select * from…