Editing MYSQL (section)

=== MSQL CHEAT SHEET  ===

03/08/2010 COPY WORK DATA TO HOME PC . See notes on laptop at C:\Data\EasyPHP-5.3.2\mysql\bin\-command-lst.txt 

'''Start mysql:'''

mysql -h localhost -u root

'''Selecting a database:''' 

mysql&gt; USE database;    e.g. use dec

<br> '''Listing databases:''' 

mysql&gt; SHOW DATABASES; 

'''Listing tables in a db:''' 

mysql&gt; show tables; 

'''Describing the format of a table:''' 

mysql&gt; DESCRIBE table; 

'''Creating a database:''' 

mysql&gt; CREATE DATABASE db_name; 

'''Creating a table:''' 

mysql&gt; CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql&gt; CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE); 

'''Duplicate a table'''&nbsp; - kbh 30/06/10<br> 

CREATE TABLE zz_items_30_06_10 SELECT * FROM items<br>'''Load tab-delimited data into a table:''' 

mysql&gt; LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name; (Use \n for NULL) 

'''Inserting one row at a time:''' 

mysql&gt; INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31'); (Use NULL for NULL) 

'''Retrieving information (general):''' 

mysql&gt; SELECT from_columns FROM table WHERE conditions; All values: SELECT * FROM table; Some values: SELECT * FROM table WHERE rec_name = "value"; Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2"; 

'''Reloading a new data set into existing table:''' 

mysql&gt; SET AUTOCOMMIT=1; # used for quick recreation of table mysql&gt; DELETE FROM pet; mysql&gt; LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table; 

'''Fixing all records with a certain value:''' 

mysql&gt; UPDATE table SET column_name = "new_value" WHERE record_name = "value"; 

'''Selecting specific columns:''' 

mysql&gt; SELECT column_name FROM table; 

'''Retrieving unique output records:''' 

mysql&gt; SELECT DISTINCT column_name FROM table; 

'''Sorting:''' 

mysql&gt; SELECT col1, col2 FROM table ORDER BY col2; Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC; 

'''Date calculations:''' 

mysql&gt; SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table]; MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day. 

'''Pattern Matching:''' 

mysql&gt; SELECT * FROM table WHERE rec LIKE "%blah%"; (% is wildcard - arbitrary # of chars) Find 5-char values: SELECT * FROM table WHERE rec like "_____"; (_ is any single character) 

'''String Editing/Manipulation''' 

# update items_import set Code = replace (Code, "&amp;", "@") WHERE Id &gt; 0; 
# UPDATE `items_import` SET `Code` = CONCAT('CRO_',LEFT(`Name`,4)) WHERE `Supplier` = 'Crowson' 
# FIND DUPLICATES [SELECT id, Code, count(Code) FROM items WHERE `Supplier` = 'Crowson' GROUP BY Code HAVING count(Code) &gt; 1]

<br> 

'''Extended Regular Expression Matching:''' 

mysql&gt; SELECT * FROM table WHERE rec RLIKE "^b$"; (. for char, [...] for char class, * for 0 or more instances ^ for beginning, {n} for repeat n times, and $ for end) (RLIKE or REGEXP) To force case-sensitivity, use "REGEXP BINARY" 

'''Counting Rows:''' 

mysql&gt; SELECT COUNT(*) FROM table; 

'''Grouping with Counting:''' 

mysql&gt; SELECT owner, COUNT(*) FROM table GROUP BY owner; (GROUP BY groups together all records for each 'owner') 

'''Selecting from multiple tables:''' 

(Example) mysql&gt; SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name; (You can join a table to itself to compare by using 'AS') 

'''Currently selected database:''' 

mysql&gt; SELECT DATABASE(); 

'''Maximum value:''' 

mysql&gt; SELECT MAX(col_name) AS label FROM table; 

'''Auto-incrementing rows:''' 

mysql&gt; CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL); mysql&gt; INSERT INTO table (name) VALUES ("tom"),("dick"),("harry"); 

'''Adding a column to an already-created table:''' 

mysql&gt; ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name; 

'''Removing a column:''' 

mysql&gt; ALTER TABLE tbl DROP COLUMN col; (Full ALTER TABLE syntax available at mysql.com.) 

'''Batch mode (feeding in a script):''' 

# mysql -u user -p &lt; batch_file

(Use -t for nice table layout and -vvv for command echoing.) Alternatively: mysql&gt; source batch_file; 

'''Backing up a database with mysqldump:''' 

# mysqldump --opt -u username -p database &gt; database_backup.sql

(Use 'mysqldump --opt --all-databases &gt; all_backup.sql' to backup everything.) (More info at MySQL's docs.)

'''Restoring database from a mysqldump:'''
# mysql -uroot -p < partial_database_backup.sql