Categories
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 line, run these commands:

# Create a dump from MySQL.
# The -d flag will only copy the structure/schema of the tables. If you want
# to copy the data along with the schema then dont use -d flag
#
# In here, two MySQL commands are piped so the output from mysqldump is sent 
# to mysql command
mysqldump -d -u username -pPassword  db_name | mysql -u username -pPassword db_new_name 
Categories
MySQL

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';
Categories
MySQL

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);


Categories
MySQL

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 table" --html

# This is for XML
shell> mysql -e "select * from table" --xml

# This is for batched results
shell> mysql -e "select * from table" --batch