MySQL Commands

Logging in

The first two below are equivalent, use the third if you plan to load data from the mysql command line after login, and the last if you also want to see details of any warnings that you get.

$ mysql -p
$ mysql -p -u uxx
$ mysql -p -u uxx --local-infile uxx
$ mysql -p -u uxx --local-infile uxx --show-warnings

Logging out

mysql> exit
mysql> quit
mysql> Ctrl + D

Execute an operating system command from within MySQL

mysql> \! ls [for example]

Note that after logging in, all MySQL commands and qualifiers are case-insensitive,
but database names and table names are case-sensitive. To help distinguish the two,
on this page commands and qualifiers are shown in all caps.

Run an external file (in the current directory) containing one or more SQL commands

mysql> SOURCE filename.sql;

Show available databases

mysql> SHOW DATABASES;

Use (work in) a particular database

mysql> USE database_name [semicolon optional]

Load an external CSV file into the database

LOAD DATA LOCAL INFILE 'name.ext' INTO TABLE table_name; (TAB field separator assumed)
LOAD DATA LOCAL INFILE 'name.ext' INTO TABLE table_name FIELDS TERMINATED BY ',';

Note: If you are going load a csv file into a MySQL table on a Linux server, make sure that the csv file is in Unix format (just line feeds at the ends of lines).

Show all the tables in the current database

mysql> SHOW TABLES;

Display the fields in a table, and their properties

mysql> DESCRIBE table_name;

Show all contents of a particular table

mysql> SELECT * FROM table_name;
mysql> SELECT * FROM table_name \G [for a more readable vertical format]
mysql> PAGER less [semicolon optional; to see select output one page at a time]

Show all table contents satisfying certain condition(s)

mysql> SELECT * FROM table_name WHERE condition(s);

Show table contents from specified columns satisfying certain condition(s)

SELECT column_name1 [, ...] FROM table_name1 WHERE condition(s);

Updates values in a row of a table.

UPDATE table_name SET column_name1=value1 [, ...] WHERE column_name=value;

Delete all table contents satisfying certain condition(s)

mysql> DELETE FROM table_name WHERE condition(s);

Delete all table contents, but leave the table itself

mysql> DELETE FROM table_name;

Drop table and all of its contents

mysql> DROP TABLE table_name;

Reset auto_increment value for an empty table

mysql> ALTER TABLE table_name auto_increment=1; [for example]

Rename a table

mysql> RENAME TABLE old_name TO new_name;

Loads in and executes a file of MySQL commands.

mysql> SOURCE filename.sql;

The above command works OK if filename.sql is creating a table, but if filename.sql is loading data from an external (local) file into an existing table, the command will generate an error unless you have logged into mysql with the following longer-form login command:

$ mysql -p -u uxx --local-infile uxx

Runs a file of MySQL commands from the Linux command line.

$ mysql -p -u uxx < filename.sql;

The above command works OK if filename.sql is creating a table, but if filename.sql is loading data from an external (local) file into an existing table, the corresponding command should look like this:

$ mysql -p -u uxx --local-infile uxx < filename.sql;

Access MySQL via phpMyAdmin at the following web address.

ps.cs-smu.ca/phpmyadmin