SQL command reference: MySQL vs. SQLite3

Start here

Getting into SQL from the command line

Every session follows the same arc: connect, choose a database, look around, query, leave. The examples below use a small movie rental database with a MOVIE table, and show the MySQL command line first, then SQLite3.

1. Connect

MySQL. Open a terminal and run the client with your username. -p makes it ask for your password, so the password never appears on screen or in your command history. Adding a database name at the end connects straight to that database:

C:\> mysql -u root -p
Enter password: ********
mysql>

C:\> mysql -u root -p movie_rental      -- connect and choose movie_rental in one step

The mysql> prompt means you are connected and ready for a statement. If Windows says 'mysql' is not recognized, the client is not on your PATH yet; run it by its full path, such as "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe".

SQLite3. Name the database file after the program. If the file exists, it opens; if not, SQLite creates it empty:

C:\> sqlite3 movie_rental.db
SQLite version 3.45.1
sqlite>

There is no username or password; anyone who can read the file can open it. The sqlite> prompt means you are ready.

2. Show the databases, then use one

Before you can work with tables, the program needs to know which database they live in.

-- MySQL
mysql> SHOW DATABASES;
mysql> USE movie_rental;
Database changed

-- SQLite3: the file you opened IS the database; to list attached ones:
sqlite> .databases

In MySQL, USE selects the database for the rest of the session. The message Database changed confirms it. In SQLite you don't need a USE, because the file you opened at the start is already the current database.

3. Show the tables

-- MySQL
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_movie_rental   |
+--------------------------+
| CUSTOMER                 |
| MOVIE                    |
| RENTAL                   |
+--------------------------+

-- SQLite3
sqlite> .tables
CUSTOMER  MOVIE     RENTAL

4. Describe a table

Describing a table shows its columns, their data types, and whether each one can be left empty (NULL). Use it before you write a query, so you know the exact column names to type.

-- MySQL
mysql> DESCRIBE MOVIE;
+--------------+--------------+------+
| Field        | Type         | Null |
+--------------+--------------+------+
| movie_id     | int          | NO   |
| title        | varchar(100) | NO   |
| genre        | varchar(30)  | YES  |
| rating       | varchar(8)   | YES  |
| release_date | date         | YES  |
| rental_price | decimal(5,2) | YES  |
+--------------+--------------+------+

-- SQLite3 has no DESCRIBE. Two ways to get the same information:
sqlite> .schema MOVIE
sqlite> PRAGMA table_info(MOVIE);

5. Select rows, using LIMIT

A SELECT * on a large table can print thousands of rows. LIMIT caps the number of rows returned, which is the safe way to peek at data:

-- MySQL and SQLite3 both accept this form
SELECT * FROM MOVIE LIMIT 5;

-- MySQL also accepts "LIMIT offset, count": skip 5 rows, then show 5
mysql> SELECT * FROM MOVIE LIMIT 5, 5;

-- SQLite spells the same thing "LIMIT count OFFSET offset"
sqlite> SELECT * FROM MOVIE LIMIT 5 OFFSET 5;

Note that LIMIT is not part of standard SQL; it is a clause both systems added. Oracle and SQL Server use other spellings, such as FETCH FIRST 5 ROWS ONLY and TOP 5.

6. Stop using a database and switch to another

To move to a different database, just tell the program to use it. The old one is no longer current the moment the new one is chosen.

-- MySQL: there is no "unuse" command; switching is the way out
mysql> USE employee_db;
Database changed

-- SQLite3: .open closes the current file and opens another
sqlite> .open employee.db

MySQL always has a current database once you have chosen one, so you cannot "deselect" it without choosing another. If you want to be sure which one you are in, run SELECT DATABASE();. SQLite's .open closes the file you were using, so any changes not yet committed in a transaction are rolled back.

7. Cancel a statement

Sometimes you start typing a statement and realize it's wrong, or a query runs far longer than expected. There are two different situations, and the keys for each are different.

A statement you are still typing (the prompt shows -> in MySQL or ...> in SQLite, and nothing has run yet). In MySQL, type \c on a new line and press Enter; it throws away everything typed so far and returns you to the mysql> prompt:

mysql> SELECT title
    -> FROM MOVIE
    -> WHERE rating = 'NC-17'  -- wrong, I meant something else
    -> \c
mysql>                       -- the half-typed statement is gone

SQLite's shell has no equivalent "throw it away" command. The simplest way out is to finish the statement with a semicolon, which runs it, and then move on; a statement that returns nothing or fails just prints an error and leaves the database unchanged. If the shell is stuck, press Ctrl+C.

A statement that is already running and taking too long. In either program, press Ctrl+C. This stops the query on the spot. Any changes it had already made are not kept unless the statement was inside a transaction you then commit, so be careful with long-running UPDATE or DELETE statements.

8. Quit

Quitting ends your session and returns you to the operating system's command prompt. Anything you committed is already saved in the database; anything left uncommitted inside a transaction is rolled back.

mysql> quit;         -- or exit;  or  \q
sqlite> .quit       -- or .exit

Both programs also quit on the end-of-input key: Ctrl+D on macOS and Linux, or Ctrl+Z followed by Enter on Windows. The typed commands above are clearer, and they work the same on every system.

Reference

The command table

Rows marked Same SQL are standard statements typed identically in both programs. Everything else is a command for the program itself, not for the database.

SQL statements and the client-program commands you use alongside them.

Command	Short summary	MySQL command	SQLite3 command
Creating and choosing a database
CREATE DATABASE	Creates a new, empty database on the server.	CREATE DATABASE store;	No separate command. A database is a file, created the first time you open a name that does not exist: sqlite3 store.db
USE	Makes a database the current one, so table names resolve against it.	USE store;	.open store.db
SHOW DATABASES	Lists every database on the server, or every attached database in SQLite.	SHOW DATABASES;	.databases
Inspecting tables
SHOW TABLES	Lists the tables in the current database.	SHOW TABLES;	.tables
DESCRIBE	Shows a table's columns, their types, and whether they can be NULL.	DESCRIBE customer;	PRAGMA table_info(customer);
SHOW CREATE TABLE	Shows the full CREATE TABLE statement a table was built from.	SHOW CREATE TABLE customer;	.schema customer
Defining structure (DDL)
CREATE TABLE	Defines a new table: its columns, types, and constraints.	CREATE TABLE customer (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40));	CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT);
ALTER TABLE	Changes an existing table: add, rename, or drop a column, or rename the table.	ALTER TABLE customer ADD COLUMN email VARCHAR(80);	ALTER TABLE customer ADD COLUMN email TEXT;
DROP TABLE	Deletes a table and all of its rows, permanently.	DROP TABLE customer;	DROP TABLE customer;
CREATE INDEX	Builds a lookup structure on one or more columns to speed up searches.	CREATE INDEX idx_name ON customer (name);	CREATE INDEX idx_name ON customer (name);
Changing data (DML)
INSERT	Adds one or more new rows to a table.	Same SQL: INSERT INTO customer (id, name) VALUES (1, 'Maria');
UPDATE	Changes values in existing rows that match a condition.	Same SQL: UPDATE customer SET name = 'Maya' WHERE id = 1;
DELETE	Removes rows that match a condition. Without WHERE, removes every row.	Same SQL: DELETE FROM customer WHERE id = 1;
Querying data
SELECT	Reads rows from one or more tables. The most used statement in SQL.	Same SQL: SELECT name FROM customer WHERE id = 1;
Transactions
BEGIN / START TRANSACTION	Starts a group of statements that succeed or fail together.	START TRANSACTION;	BEGIN;
COMMIT	Makes the changes in the current transaction permanent.	Same SQL: COMMIT;
ROLLBACK	Undoes every change made since the transaction began.	Same SQL: ROLLBACK;
Users and permissions (MySQL only)
CREATE USER / GRANT / REVOKE	Creates login accounts and controls what each account may do.	CREATE USER 'ana'@'localhost' IDENTIFIED BY 'secret'; GRANT SELECT ON store.* TO 'ana'@'localhost';	Not applicable. SQLite has no user accounts; access is controlled by file permissions.
Running scripts and getting help
SOURCE / .read	Runs every statement in a .sql file.	SOURCE setup.sql;	.read setup.sql
HELP	Lists the commands the client understands.	help; (or \h)	.help
QUIT / EXIT	Leaves the client program.	quit; (or exit;, or \q)	.quit (or .exit)

Reference

Why MySQL needs the semicolon

The semicolon tells the client program that the statement you typed is finished. Without it, the client does not know whether you are done or still typing.

The mysql command line client reads your input one line at a time. When you press Enter with no semicolon, it has no way to tell a finished statement from one that continues on the next line, so it waits and shows a continuation prompt:

mysql> SELECT name
    -> FROM customer
    -> WHERE id = 1;
+-------+
| name  |
+-------+
| Maya  |
+-------+

The -> prompt means "still reading." The semicolon on the last line is what finally sends the whole statement to the server in one piece. This is why the same statement can be spread across as many lines as you like, and why several statements can be typed on one line, each ended by its own semicolon.

The client also accepts \g as a terminator and \G to print a result vertically, but the semicolon is by far the most common.

What about SQLite?

The premise needs a correction before the explanation. The SQLite3 shell also waits for a semicolon before running a SQL statement; its continuation prompt is ...> instead of ->. The real difference is in the dot commands like .tables, which are not SQL at all. The shell runs those the moment you press Enter, and they never take a semicolon:

sqlite> .tables
customer  orders
sqlite> SELECT name
   ...> FROM customer
   ...> WHERE id = 1;
Maya

And if you use SQLite through Python's sqlite3 module, as the Python-based practice notebooks on this site do, the semicolon is optional. cursor.execute("SELECT ...") takes a single statement as a string, so there is no terminator to wait for, and a trailing semicolon inside the string is simply ignored.

Reference

MySQL command line vs. MySQL Workbench

Both programs talk to the same MySQL server and accept the same SQL. They differ in how you interact with it.

The two ways to work with a MySQL server.

Feature	MySQL command line (mysql)	MySQL Workbench
What it is	A text-only program you run in a terminal window.	A graphical desktop application made by Oracle, the company that owns MySQL.
How you run SQL	Type a statement, end it with a semicolon, press Enter.	Type statements into a query tab and run them with a button or shortcut; you can run the whole tab or only the highlighted part.
How results look	Plain text rows printed under your command.	A spreadsheet-style grid you can scroll, sort, and export.
Browsing the database	You type SHOW TABLES; and DESCRIBE yourself.	A navigator panel shows every schema, table, column, and index with a click.
Designing tables	Written by hand as CREATE TABLE statements.	A visual table editor, and an ER diagram tool that draws tables and relationships.
Editing data	Done with UPDATE and DELETE statements.	You can also edit cells directly in the result grid.
Automation and scripting	Excellent. Pipe a file in with mysql ... < setup.sql, or put commands in a shell script.	Possible, but built for interactive work rather than unattended scripts.
Where it runs	On any machine with the MySQL client installed, including over SSH on a server with no display.	Needs a graphical desktop, so it runs on your own computer.
Learning curve	Steeper at first, but the SQL you learn transfers directly to any database.	Gentler to start, with buttons and wizards that hide the SQL behind them.

In short: use the command line when you want to see exactly what is being sent to the server, when you are scripting, or when you are working on a server you can only reach through a terminal. Use Workbench when you want to browse, design, and visually inspect a database, and when you want the results in a grid you can scroll through without retyping anything.