View source

From Kb

You do not have permission to do that, for the following reasons:

• The action you have requested is limited to users in the group user.
• You must confirm your e-mail address before editing pages. Please set and validate your e-mail address through your user preferences.

You can view and copy the source of this page:

*[[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 <pre> 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 </pre> 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: <pre> 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 </pre>

Return to SQL Query Examples.