MySQL Commands

Logging in options

The first two lines below are equivalent, but use the third if you plan to load a data file from the mysql
command line after login, and the last if you also want to see details of any warnings that you might 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 options

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 of SQL commands (in the current directory)

mysql> SOURCE filename.sql;

Show available databases

mysql> SHOW DATABASES;

Use (that is, work in) a particular database (typically, your uxx database)

mysql> USE database_name [semicolon optional]

Load an external CSV file into the database

mysql> LOAD DATA LOCAL INFILE 'name.ext' INTO TABLE table_name; (TAB field separator assumed)
mysql> 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 using one of the above
commands, make sure that the csv file is in Unix format (just line feeds at the ends of lines),
and also ensure that there are no blank lines at the end of the file.

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)

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

Update values in a row of a table.

mysql> 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;

Load 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

Run 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