SQL Query Examples

From Kb

• SQL_Create_Table - Creating a Table
• SQL_Inserting_Data - Inserting Data into a Table
• SQL_Alter_Table - Altering Table Structure
• SQL_Update_Table - Updating Data within a Table
• SQL_Drop_Table - Deleting a Table
• SQL_User_DB_Management - Management of Users and Databases
• SQL_Foreign_Keys - Working with Foreign Keys

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