MySQL Commands Tutorial

The MySQL reference manual separates commands into "administrative commands" and "statements." The administrative commands are ones that the MySQL client interprets. Statements are commands (queries) that are sent to the MySQL server and are used to manipulate data and administer the server. Commands are not case sensitive, but MySQL convention is to capitalize the complete command and use lowercase letters to indicate databases, tables and columns.

Administrative Commands

The first command you must know in order to access the MySQL client is the operating system shell command "mysql -u username -p". This command is typed at the command prompt of your operating system, and connects you to the MySQL server. Once you have connected to the server, you will be presented with a "mysql>" prompt. This is where you will type the commands used by MySQL.

The complete list of administrative commands can be found by typing "help" or "\h" at the command prompt or in the MySQL Reference Manual (see Resources) and include "clear" (clear the command), "exit" (exit MySQL), "source" (execute a SQL script file), and "use" (use a database).

To connect to the database that you want to use, you will type the "USE database;" command (replace "database" with the exact name of the database). All database, table, and column names are case sensitive within MySQL. So, if you named the database "test," you cannot access it by typing "TEST" or "Test."

Note that the "USE database;" command ends with a semicolon. Almost every command used by MySQL ends with a semicolon. If you type the command without the semicolon and press "Enter," you will be presented with the "->" prompt. This prompt says that MySQL is waiting for more input from you. Simply type the missing semicolon and the command will execute.

Statements

Most of the commands you use in MySQL will be what the MySQL References Manual calls "statements" or "queries". They include the commands to create tables, manipulate data, and administer the database. The complete list of SQL statements can be found in Chapter 12 of the MySQL Reference Manual (see Resources). This article will cover some of the more common SQL commands.

To show the tables that are currently on a database, use the "SHOW TABLES;" command. This command will list a single column with each table as a row within the column. You can also use the show command to show the columns in a table, with the syntax "SHOW COLUMNS FROM table;" (replace "table" with the actual name of the table).

To create a new table, use the "CREATE TABLE" statement. For example:

CREATE TABLE address(fname VARCHAR(20), lname VARCHAR(20), staddress VARCHAR(50), zip INT(7));

This statement creates a table with the name "address" and column named "fname," "lname,""staddress," and "zip." The other capitalized words indicate the data type of each column.

To insert data into the table, use the INSERT statement with the syntax:

INSERT INTO address VALUES ('Kris', 'Miller', '301 Anywhere St', 17598);

As you can see, the values that you want to insert into the table are enclosed in parentheses and string (text) values are enclosed in single quotes. The values must be entered in the same order as the columns are laid out in the CREATE TABLE statement.

The SELECT statement allows you to access the data from the tables with the syntax:

SELECT * FROM address;

The * wildcard tells MySQL to return all the records from the table address. The SELECT statement can be used with many other keywords, and can include UNION statements and subqueries. For more information on the SELECT statement, see Resources.