Connect and run your first commands

Connect from the command line

The mysql program is the classic client. Open a terminal (Command Prompt, PowerShell, Terminal) and run:

mysql -u root -p

-u root names the user and -p asks the client to prompt for the password. Typing the password on the same line works too, but then it is saved in your shell history, so avoid it. When the connection succeeds you see a welcome message and the prompt changes to:

mysql>

Useful connection options:

If you prefer a graphical tool, MySQL Workbench, DBeaver and the VS Code SQL extensions all send the same SQL to the same server. Everything in this course works in any of them.

Statements end with a semicolon

At the prompt you type SQL. The client does not send it until it sees a semicolon:

SELECT 'Hello, MySQL' AS greeting
;

Because the statement could be split over several lines, a missing semicolon is the most common beginner problem. When the prompt changes to ->, the client is still waiting for the end of the statement. Type ; and press Enter to finish it, or \c to cancel it.

Keywords such as SELECT and FROM are not case sensitive, so select works too. This course writes keywords in capitals so they stand out from table and column names. Names are a different matter: on Linux, table names are case sensitive, while on Windows they are not. Always write a name exactly as it was created.

Look around the server

The server can hold many databases. Three commands let you explore, once the sample database from the next lesson is loaded:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shop               |
| sys                |
+--------------------+

information_schema, mysql, performance_schema and sys belong to MySQL itself. Do not drop them. shop is the one you will create. Choose a database with USE:

USE shop;
SHOW TABLES;

+----------------+
| Tables_in_shop |
+----------------+
| categories     |
| customers      |
| departments    |
| employees      |
| order_items    |
| orders         |
| products       |
+----------------+

To see the columns of one table, use DESCRIBE:

DESCRIBE categories;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Read it like this: id is an integer, may not be NULL, and is the primary key (PRI). name is text of up to 50 characters and may not be NULL.

Running a script file

Instead of typing, you can run a file of statements. From the system shell:

mysql -u root -p < setup.sql

The < feeds the file into the client. From inside the client, source setup.sql does the same. You will use this in the next lesson to load the sample data.

Quitting and fixing mistakes

Type exit or quit to leave the client. The server keeps running. If you press the up arrow, the client brings back earlier statements, which is handy for correcting a typo. For a long statement, a text editor with the .sql extension and the source command is easier than retyping.

Mistakes happen, and MySQL's messages are usually clear. An error such as ERROR 1146 (42S02): Table 'shop.custmers' doesn't exist points straight at the misspelt name.

SELECT * FROM custmers;

ERROR 1146 (42S02): Table 'shop.custmers' doesn't exist

Common mistakes

• Forgetting the semicolon and wondering why nothing runs.
• Forgetting USE and getting ERROR 1046: No database selected.
• Mixing up the shell and the MySQL prompt. mysql -u root -p is typed in the system shell; SELECT 1; is typed after the mysql> prompt.
• Typing a password on the command line where other users or shell history can see it.

Exercise

Connect to your server, list the databases and tables as shown above and describe the products table. How many columns does it have, and which one is the primary key?

USE shop;
DESCRIBE products;

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | int           | NO   | PRI | NULL    |       |
| name        | varchar(100)  | NO   |     | NULL    |       |
| category_id | int           | NO   | MUL | NULL    |       |
| price       | decimal(10,2) | NO   |     | NULL    |       |
| stock       | int           | NO   |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+