VMware ESXi and vSphere Cluster Management

MySQL SQL Command Syntax: Statements, Keywords, Case Sensitivity, and Basic Queries

Learn MySQL SQL syntax fundamentals: statements, clauses, keywords, semicolons, identifiers, case sensitivity, database selection, and basic SELECT queries.

What SQL commands are

SQL, or Structured Query Language, is a declarative language for working with relational data. You describe the result or change you want, and the database system determines how to carry it out.

SQL can define database structures, query data, modify rows, and manage database objects. MySQL is a database management system that implements SQL and adds MySQL-specific commands, behavior, and tools.

A statement is a complete SQL instruction submitted to the server. Statements are built from keywords and one or more clauses. A clause is a component with a particular role, such as choosing columns, identifying a table, filtering rows, or limiting results.

SELECT first_name, city
FROM customers
WHERE city = 'Paris'
ORDER BY last_name
LIMIT 10;

This statement uses English-like words to describe the operation. SELECT chooses columns, FROM identifies the source table, WHERE filters rows, ORDER BY requests an order, and LIMIT restricts the number of returned rows.

Keywords are language words with defined meanings. Names created by you are called identifiers. Database names, table names, column names, and aliases are identifiers.

SELECT first_name
FROM customers;

In this example, SELECT and FROM are keywords. first_name is a column identifier, and customers is a table identifier.

Basic MySQL statement structure

The exact structure depends on the statement. A simple query commonly places clauses in this order:

SELECT — choose columns or expressions.
FROM — choose the source table or tables.
WHERE — optionally filter rows.
ORDER BY — optionally request predictable sorting.
LIMIT — optionally restrict the number of rows.

SELECT customer_id, first_name, last_name
FROM customers
WHERE city = 'Paris'
ORDER BY last_name
LIMIT 10;

Whitespace and line breaks usually do not change the meaning of a statement. They make the statement easier to read. Whitespace inside quoted values is different: it is part of the value and must not be changed casually.

For multi-line queries, use one major clause per line and indent lists or conditions. This makes missing clauses, misplaced words, and punctuation easier to spot.

Syntax rule: Clause order is not merely a formatting preference. For example, ORDER BY normally appears before LIMIT. A syntactically incorrect order can produce error 1064.

SQL keywords and capitalization conventions

Common MySQL keywords include SELECT, FROM, USE, CREATE, WHERE, INSERT, UPDATE, DELETE, LIMIT, and JOIN. Each has a defined role in SQL syntax.

MySQL keywords are normally case-insensitive, so these statements generally have the same meaning:

SELECT first_name FROM customers;
select first_name from customers;

A widely used convention is to write SQL keywords in uppercase and object names in lowercase:

SELECT first_name, last_name
FROM customers
ORDER BY last_name
LIMIT 10;

This capitalization is a style convention. It improves consistency and readability, but uppercase keywords are not usually required for MySQL to understand the statement.

Common MySQL SQL keywords and their roles

SELECT — retrieves columns or expressions. Example: SELECT first_name FROM customers;

FROM — identifies the source table. Example: FROM customers

WHERE — filters rows. Example: WHERE city = 'Paris'

ORDER BY — requests sorting. Example: ORDER BY last_name

LIMIT — restricts returned rows. Example: LIMIT 10

USE — selects the active database. Example: USE sql_syntax_lab;

CREATE DATABASE — creates a database. Example: CREATE DATABASE sql_syntax_lab;

CREATE TABLE — creates a table. Example: CREATE TABLE customers (...);

SHOW — displays server or object information. Example: SHOW TABLES;

INSERT — adds rows.

UPDATE — changes existing rows.

DELETE — removes rows.

Semicolons and multiple statements

In the MySQL command-line client, a semicolon normally marks the end of a SQL statement. When the client receives the semicolon, it sends the completed statement to the MySQL server for execution.

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

You can enter multiple statements in sequence. Each statement needs its own terminator.

USE sql_syntax_lab;
SELECT customer_id, first_name
FROM customers
LIMIT 5;

If you forget the semicolon, the client assumes that the statement is not complete and displays a continuation prompt, commonly ->.

mysql> SELECT first_name FROM customers
    ->

At this point the query has not executed. Add the semicolon to finish it, or cancel the unfinished input with \c.

Identifiers and case sensitivity

An identifier is a name assigned to a database object. Examples include database names, table names, column names, aliases, indexes, and views.

Keyword case and identifier case are separate issues. MySQL can accept select as well as SELECT, but whether Customers and customers refer to the same table can depend on the operating system and MySQL server configuration.

Table-name behavior commonly differs between Windows and Unix-like systems. A table name that appears to work with different capitalization on one system may fail after being moved to a case-sensitive system. Database and table name handling can also be affected by server settings.

For portability, use a consistent lowercase convention for database and table names, and spell identifiers exactly as they were created. Lowercase names without spaces or punctuation are usually easiest to use.

SQL case rules and naming recommendations

SQL keywords: Usually case-insensitive. Use uppercase for readability.

Database names: Behavior can depend on the platform and configuration. Use lowercase names consistently.

Table names: Can be case-sensitive, especially on Unix-like systems. Use lowercase names and exact spelling.

Column names: Usually use consistent lowercase spelling. Do not assume every identifier has identical case behavior.

Aliases: Choose readable names and keep their spelling consistent.

String values: Case and spaces are data. For example, 'Paris' and 'paris' may represent different values in comparisons.

Backticks are MySQL identifier delimiters. They are useful when an identifier contains spaces or special characters, or conflicts with a reserved word.

SELECT `order`
FROM `sales records`;

Prefer renaming objects to simple names such as order_number and sales_records instead of depending on backticks throughout your queries.

Starting the MySQL command-line client

The mysql client is a command-line program used to connect to a MySQL server and submit SQL statements. A successful connection usually displays information about the server and then the mysql> prompt.

mysql -u learner -p

The -u option supplies the user name. The -p option tells the client to prompt for the password, rather than placing the password directly in the command line.

mysql -h localhost -u learner -p

Here, -h localhost explicitly identifies the server host. The client prompt indicates that you can enter SQL.

Client commands are instructions to the command-line program, while SQL statements are sent to the MySQL server. For example, exit and \c are client commands; SELECT and USE are SQL statements.

mysql> exit

You can also leave the client with quit or the appropriate terminal end-of-input shortcut.

Selecting a database

A database is a named container for tables and other objects. Before using an unqualified table name, select the database for the current client session.

USE sql_syntax_lab;

A successful command normally produces:

Database changed

Verify the active database with:

SELECT DATABASE();

If no database has been selected, the result is NULL. You can also avoid selecting a database by using a fully qualified table name, which includes the database and table separated by a period.

SELECT customer_id, first_name
FROM sql_syntax_lab.customers
LIMIT 5;

Fully qualified names are useful when a session works with more than one database or when you want the source database to be explicit.

Creating and inspecting a practice database

The following sequence creates a small practice database and table. You need suitable privileges to create database objects.

CREATE DATABASE sql_syntax_lab;
USE sql_syntax_lab;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  city VARCHAR(50)
);

INT stores integer values, VARCHAR(50) stores text up to 50 characters, and PRIMARY KEY identifies each row uniquely.

Inspect databases visible to your account:

SHOW DATABASES;

After selecting the practice database, list its tables:

SHOW TABLES;

These commands help verify that you are connected to the expected server, that the database exists, and that the table name is spelled correctly.

Reading data with SELECT

SELECT is the core command for retrieving rows. A table is a structured collection of rows and columns. Each row is one record, and each column is a named attribute stored for each record.

Use an asterisk to request every column:

SELECT *
FROM customers;

The client displays the returned data as a tabular result set. It commonly prints column headings, rows, a separator, and a message such as 3 rows in set.

For exploration, SELECT * is convenient. In production queries, explicit column lists are preferable because they make the required data clear, avoid returning unnecessary columns, and are less affected when a table gains new columns.

SELECT customer_id, first_name, last_name
FROM customers;

Add LIMIT when examining an unfamiliar or large table:

SELECT customer_id, first_name, last_name
FROM customers
LIMIT 10;

A limit without ordering does not guarantee which rows are returned. Tables do not have an implicit reliable order. Use ORDER BY when the order matters.

SELECT first_name, last_name
FROM customers
ORDER BY last_name
LIMIT 10;

Later, use WHERE to filter rows:

SELECT customer_id, first_name, city
FROM customers
WHERE city = 'Paris'
ORDER BY first_name
LIMIT 10;

Safe introductory query habits

Use LIMIT while inspecting an unfamiliar or large table.
Use explicit column names when you already know which fields are required.
Use ORDER BY when a predictable row order is important.
Read every command before executing it.
Be especially careful with UPDATE and DELETE, because a missing or overly broad WHERE clause can affect many rows.
Use a dedicated practice database while learning.

MySQL client prompts and diagnostic cues

mysql> — The client is ready for a new command. Enter a SQL statement or client command.

-> — The current SQL input is unfinished. Complete it or use \c to cancel.

Database changed — The USE statement selected the database successfully.

rows in set — A query returned a result set and the message reports its row count and execution time.

ERROR 1046 — No database is selected. Run USE database_name; or use a fully qualified table name.

ERROR 1064 — A SQL syntax error occurred. Check keyword order, punctuation, and reserved-word identifiers.

ERROR 1146 — The referenced table does not exist in the selected database, or its name does not match.

Common syntax errors and fixes

Continuation prompt instead of execution

If mysql> changes to ->, the client is still collecting input. A missing semicolon is common, but an unclosed quote can have the same symptom.

mysql> SELECT first_name FROM customers
    -> \c
mysql>

The \c client command clears the unfinished statement. You can then re-enter it correctly.

No database selected

ERROR 1046: No database selected means that an unqualified table reference was used without an active database.

USE sql_syntax_lab;
SELECT * FROM customers;

Alternatively:

SELECT *
FROM sql_syntax_lab.customers;

Table not found

ERROR 1146 commonly means that the table name is misspelled, the wrong database is active, or capitalization differs on a case-sensitive server.

SELECT DATABASE();
SHOW TABLES;

Compare the exact table name shown by SHOW TABLES with the name in your query.

Syntax error

ERROR 1064 can result from malformed clause order, missing punctuation, or an identifier that conflicts with a reserved word. Compare the statement with the expected structure, check quotes and semicolons, and rename a problematic identifier when possible. Use backticks only when renaming is not practical.

Unexpectedly many rows

A query using SELECT * without WHERE or LIMIT can return the entire table. During exploration, select only the columns you need and add a limit. Add a filter when you know which rows are relevant.

Different behavior on different machines

Identifier case behavior can vary with the operating system and MySQL configuration. Consistently lowercase database and table names, and match the stored spelling exactly in every query.

Exam-relevant summary

SQL is a declarative language for defining, querying, modifying, and managing relational data.
A statement is a complete instruction; clauses are its functional components.
Keywords such as SELECT and FROM have language-defined roles, while table and column names are identifiers.
Keywords are normally case-insensitive in MySQL, but identifier case behavior can vary.
The semicolon normally terminates a statement in the MySQL command-line client.
USE database_name; selects the active database, while SELECT DATABASE(); verifies it.
A basic query commonly follows SELECT, FROM, WHERE, ORDER BY, and LIMIT order.
Use lowercase database and table names for better portability.
Use LIMIT for exploration and ORDER BY when row order must be predictable.
Use \c to cancel unfinished input and exit to leave the client.

For continued practice, return to MySQL SQL command syntax and test each statement in a dedicated practice database.