Essential MySQL Commands and Operations

Posted on Mar 3, 2025 in Computing and Communications

Listing Databases

To list all databases:

mysql> SHOW DATABASES;

Listing Tables in a Database

To list all tables in the currently selected database:

mysql> SHOW TABLES;

Describing a Table’s Format

To describe the format of a table:

mysql> DESCRIBE table;

Creating a Database

To create a new database:

mysql> CREATE DATABASE db_name;

Creating a Table

To create a new 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);

Loading Data into a Table

To load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;

(Use \n for NULL)

Inserting Rows

To insert one row at a time:

mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');

(Use NULL for NULL)

Retrieving Information

General syntax:

mysql> SELECT from_columns FROM table WHERE conditions;
  • All values: SELECT * FROM table;
  • Specific values: SELECT * FROM table WHERE rec_name = "value";
  • Multiple criteria: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading Data into an Existing Table

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

Updating Records

To fix all records with a certain value:

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

Selecting Specific Columns

mysql> SELECT column_name FROM table;

Retrieving Unique Records

mysql> SELECT DISTINCT column_name FROM table;

Sorting Results

  • Ascending:
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Descending:
mysql> 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 the day.

Pattern Matching

  • Using wildcards:
mysql> SELECT * FROM table WHERE rec LIKE "blah%";

(% is a wildcard for any number of characters)

Finding 5-character values:
mysql> SELECT * FROM table WHERE rec LIKE "_____";

(_ represents any single character)

Regular Expression Matching

mysql> SELECT * FROM table WHERE rec RLIKE "^b$";

(. for any character, [...] for character 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> SELECT COUNT(*) FROM table;

Grouping and Counting

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;

(GROUP BY groups together all records for each ‘owner’)

Selecting from Multiple Tables

Example:

mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;

(You can join a table to itself for comparisons by using AS)

Currently Selected Database

mysql> SELECT DATABASE();

Maximum Value

mysql> SELECT MAX(col_name) AS label FROM table;

Auto-Incrementing Rows

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

Adding a Column

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

Removing a Column

mysql> ALTER TABLE tbl DROP COLUMN col;

Full ALTER TABLE syntax is available at mysql.com.

Batch Mode

Feeding in a script:

# mysql -u user -p < batch_file

(Use -t for a table layout and -vvv for command echoing.)

Alternatively:

mysql> source batch_file;

Backing up a Database

Using mysqldump:

# mysqldump --opt -u username -p database > database_backup.sql

(Use mysqldump --opt --all-databases > all_backup.sql to back up everything.)

More info at MySQL’s docs.