Below are some of the most common commands you will use when working with mysql from command line.
Auto-incrementing rows:
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("change"),("change2"),("change3");
Selecting a database:
mysql> USE database;
List databases:
mysql> SHOW DATABASES;
Currently selected database:
mysql> SELECT DATABASE();
Maximum value:
mysql> SELECT MAX(col_name) AS label FROM table;
Adding a column to an already-created table:
mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
Removing a column:
mysql> ALTER TABLE tbl DROP COLUMN col;
List tables in a database:
mysql> SHOW TABLES;
Describe the format of a table:
mysql> DESCRIBE table;
Create a database:
mysql> CREATE DATABASE db_name;
Create a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use n for NULL)
Insert one row at a time:
mysql> INSERT INTO table_name VALUES ('Yourdata', 'Yourdata', 'Yourdata');
(Use NULL for NULL)
Retrieve information:
mysql> 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";
Reload a new data set into an existing table:
mysql> SET AUTOCOMMIT=1; # used for recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
Fix all records with a certain value:
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value_you_want_changed";
Select specific columns:
mysql> SELECT column_name FROM table;
Retrieve unique output records:
mysql> SELECT DISTINCT column_name FROM table;
Sort columns:
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
Date calculations:
mysql> 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> SELECT * FROM table WHERE rec LIKE "blah%";
(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "Change_to_what_you_want";
Extended Regular Expression Matching:
mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
Count Rows:
mysql> SELECT COUNT(*) FROM table;
Group and Count:
mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each ‘owner’)
Select from multiple tables:
mysql> SELECT you_change, comment FROM change, event WHERE you_change = you_change2;
Batch mode:
mysql> mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;
Backing up a database with mysqldump:
mysql> mysqldump --opt -u username -p database > database_backup.sql
(Use ‘mysqldump –opt –all-databases > all_backup.sql’ to backup everything.)