SQL Query Examples

From Kb

Revision as of 01:46, 3 April 2013 by Jonathan (Talk | contribs)
(diff) ←Older revision | Current revision (diff) | Newer revision→ (diff)
Jump to: navigation, search


Show how the table was created:

SHOW CREATE  TABLE `locations`;

Remove all data and reset auto increment: TRUNCATE TABLE table_name

  mysql> CREATE TABLE Authors (
    -> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(100),
    -> EMail VARCHAR(100)
    -> );

  mysql> update FreudBitStyle set bitstyletitle='Double Flute Straight Bits';
  mysql> UPDATE leads_transfers SET transfer_status=NULL, last_attempt=NULL WHERE transfer_id IN (x, y, n...)

  mysql> delete from table where [expression];



Select results straight to file:
  /usr/local/mysql/bin/mysql -h hostname -u username -p database -e "[SQL QUERY GOES HERE]" > results.sql.csv


To import data from a CSV file.

 mysql>  LOAD DATA INFILE '/csvtest1.csv' INTO TABLE FreudItems
   -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY
   -> '\n';
 LOAD DATA INFILE '/freuditems2export-listprice.csv' INTO TABLE FreudItems
 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY
 '\n';
 LOAD DATA INFILE '/tmp/uccu_bms_new_additions.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Specify field columns

 LOAD DATA INFILE '/path/to/file.csv' INTO TABLE hooks
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' (column3, column1, column7);

Backup/Restore/Export/Import


(http://www.mysql.com/doc/m/y/mysqldump.html)

mysqldump -u -p [database name] > databasename.sql (--opt)

eg. mysqldump -u root -p twig > twig.sql

move it to the new machine, and do:

mysql -u -p [database name] < databasename.sql

eg. mysql -u root -p twig < twig.sql

database_name must already be setup in mysql


SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 FROM test_table;
SELECT c.customers_password as password, 
 INTO OUTFILE '/tmp/user_export.csv'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 FROM customers c, address_book a
SELECT *
INTO OUTFILE '/tmp/twig_contact_export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM twig_contacts
WHERE username='jonathan'
SELECT * 
INTO OUTFILE '/tmp/twig_contact_export.csv'
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '^'
LINES TERMINATED BY '%'
FROM twig_contacts
WHERE username='jonathan'
SELECT `users`.*
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM `users`, `users_posts`
WHERE `users`.`id` = `users_posts`.`user_id` AND `users_posts`.`posts_id` = 5


Select from a table the data that has not counterpart in a related table:

SELECT s.id, s.name
FROM superhero s
LEFT JOIN superheros_powers ON superheros_powers.power_id = s.id
WHERE superheros_powers.power_id IS NULL