[Setup] SQL Execution Environment

SQLite is a lightweight database that requires no server installation. It is ideal for learning or quick experiments.

OS	Installation Method
Windows	Download sqlite-tools from sqlite.org and extract it. Add the folder path to the PATH environment variable.
macOS	SQLite comes pre-installed on macOS. No additional installation is needed.

Run the following command in a terminal to launch SQLite's interactive mode.

sqlite3 test.db

You can type and run SQL in interactive mode.

Create a table.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

Insert data.

INSERT INTO users (name, age) VALUES ('Ayanami Rei', 14);
INSERT INTO users (name, age) VALUES ('Ikari Shinji', 14);

Retrieve data.

SELECT * FROM users;

Result:

1|Ayanami Rei|14
2|Ikari Shinji|14

To exit interactive mode, type .quit.

.quit

MySQL is one of the most widely used databases. It is frequently adopted in web application development. If you are on macOS and plan to use Homebrew, make sure it is installed first. If not, follow the instructions on the official website (https://brew.sh/) to install it.

OS	Installation Method
Windows	Download and run MySQL Installer from the official MySQL website.
macOS	After installing Homebrew, run brew install mysql in a terminal.

After installation, start the server.

brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

Connect to MySQL from a terminal. This starts interactive mode. The -p option prompts you for a password.

mysql -u root -p
Enter password: ********

The password will not be displayed on screen as you type (this is a security feature). Right after installing with Homebrew, the password may not be set. In that case, press Enter without typing anything, or connect with mysql -u root.

※ If you see Can't connect to local MySQL server through socket when connecting, the server may not be running. See here for details.

Create and select a database.

CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)
USE mydb;
Database changed

From here, standard SQL can be executed.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT
);
Query OK, 0 rows affected (0.03 sec)

To exit interactive mode, type exit.

exit
Bye

To stop the server, run the following command.

brew services stop mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)

To uninstall MySQL, if you also want to remove all database data, you need to check the data directory location before uninstalling. Run the following in interactive mode. If you uninstall MySQL first, you will no longer be able to check the data directory path, so make sure to follow the steps in order.

SELECT @@datadir;
/opt/homebrew/var/mysql/

To uninstall MySQL, run the following command.

brew uninstall mysql

Remove the data directory using the path you confirmed earlier.

rm -rf /opt/homebrew/var/mysql

PostgreSQL is a powerful and reliable database. It is well suited for complex queries and large-scale systems. If you are on macOS and plan to use Homebrew, make sure it is installed first. If not, follow the instructions on the official website (https://brew.sh/) to install it.

OS	Installation Method
Windows	Download and run the installer from the official PostgreSQL website.
macOS	After installing Homebrew, run brew install postgresql in a terminal.

To install a specific version, append @ followed by the major version number.

brew install postgresql@17

After installation, start the server.

brew services start postgresql
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

※ If you installed a specific version, you may see the following error.

brew services start postgresql
Error: Formula `postgresql@18` is not installed.

In that case, start the server with the version you installed.

brew services start postgresql@17
==> Successfully started `postgresql@17` (label: homebrew.mxcl.postgresql@17)

Connect to PostgreSQL from a terminal. This starts interactive mode.

psql -U postgres

※ If you see connection to server on socket "/tmp/.s.PGSQL.5432" failed, the server may not be running. See here. If you see role "postgres" does not exist, see here.

Create a database and connect to it.

CREATE DATABASE mydb;
CREATE DATABASE
\c mydb
You are now connected to database "mydb" as user "postgres".

Table creation and data operations are nearly the same as MySQL.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INTEGER
);
CREATE TABLE

To exit interactive mode, type \q.

\q

To stop the server, run the following command.

brew services stop postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)

To uninstall PostgreSQL, first stop the server. Check whether it is running.

brew services list
Name          Status  User  File
postgresql@17 started _root

If it shows started, stop it.

brew services stop postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)

Next, check the version.

psql --version
psql (PostgreSQL) 17.3

If you also want to remove all database data, check the data directory location before uninstalling. Run the following in interactive mode. If you uninstall PostgreSQL first, you will no longer be able to check the data directory path, so make sure to follow the steps in order.

SHOW data_directory;
/opt/homebrew/var/postgresql@17

To uninstall, run the following command. Adjust the version number (e.g., postgresql@17) to match your installation.

brew uninstall postgresql@17

Remove the data directory using the path you confirmed earlier.

rm -rf /opt/homebrew/var/postgresql@17

In SQL tutorials and documentation, keywords are typically written in uppercase.

SELECT name, salary FROM employees WHERE department = 'Sales';

This convention dates back to the 1970s when SQL was first created. Computers at that time could only handle uppercase characters — programs were entered via punch cards, and languages like COBOL and FORTRAN were also written entirely in uppercase. Since the SQL standard specification uses uppercase for keywords, the convention of "uppercase keywords, lowercase table/column names" became the norm.

However, SQL itself is case-insensitive. The following three statements all produce the same result.

SELECT name FROM employees;
select name from employees;
Select Name From Employees;

Modern editors provide syntax highlighting (color-coding), making it easy to distinguish keywords even when written in lowercase. Whether to use uppercase or lowercase is a matter of team or project convention. This site follows the SQL standard convention and uses uppercase for keywords throughout.

There are also services that let you try SQL directly from a browser without installation. They are recommended if you want to get familiar with SQL syntax before setting up a local environment.

Service	Features
DB Fiddle	Supports MySQL, PostgreSQL, and SQLite. Write table definitions on the left and queries on the right, then run them.
SQLite Online	An online editor focused on SQLite. The simple interface lets you try SQL immediately.

Online services are convenient, but data is not saved. For serious learning or development, setting up a local environment is recommended.

Here are some useful tools for creating SQL files (.sql) and managing databases.

Tool	Features
VSCode	Offers a wide range of SQL extensions for syntax highlighting, code completion, and database connections. Free. The full name is "Visual Studio Code".
Sublime Text	A lightweight, extremely fast editor. Its simple interface lets you focus on coding. Available on Windows / macOS / Linux.
Hidemaru Editor	A legendary Japanese text editor first released in 1993. Lightweight, fast, and equipped with a powerful macro system. Windows only, one-time purchase.
DBeaver	A GUI tool that supports many databases including MySQL, PostgreSQL, and SQLite. It lets you browse and edit tables visually. Free.

These days, VSCode seems to be the most widely used editor, but the webmaster personally loves simple and lightweight editors, so he currently uses Sublime Text. Feel free to use this as a reference.

A note about Hidemaru Editor: first released in 1993, Hidemaru is lightweight, features powerful search and replace with regular expressions, and can be customized with macros. In modern terms, macros are similar to add-ons — and Hidemaru had this capability back in the 1990s, a remarkably forward-thinking design that makes it a truly wonderful editor.

The webmaster himself used Hidemaru Editor for programming from the Windows 3.1 era through Windows 7, and owes a great deal to it over the years. Its developer, Hideo Saito, continues to update Hidemaru to this day — it runs perfectly on Windows 11 and other 64-bit versions of Windows. You can purchase Hidemaru Editor from here — it is a one-time purchase for around 4,000 yen (about $27), and having it on a Windows machine can be surprisingly handy.

(´-`).｡oO(The webmaster wanted to give a special shout-out to Mr. Saito, whom he has relied on for many years...)

(´-`).｡oO(Over 30 years of use and the total cost was just around 4,000 yen... it is hard to find anything else with such incredible value...)

(´-`).｡oO(Well then, please continue with the setup. Good luck with the environment configuration...)

If the terminal shows sqlite3: command not found, mysql: command not found, or psql: command not found, the PATH may not be configured correctly. Follow the steps below to check and set it up.

1. Locate the command

Check where the command you want to use is installed.

which sqlite3
which mysql
which psql

If not found, check the common installation paths.

SQLite:

ls /usr/bin/sqlite3
ls /usr/local/bin/sqlite3

MySQL:

ls /usr/local/mysql/bin/mysql
ls /opt/homebrew/bin/mysql

PostgreSQL:

ls /usr/local/bin/psql
ls /opt/homebrew/bin/psql
ls /usr/local/pgsql/bin/psql

2. Check your shell

echo $SHELL

If the output is /bin/zsh, edit ~/.zshrc. If it is /bin/bash, edit ~/.bashrc.

3. Add the path to PATH

Once you know the installation path, add it to your shell configuration file.

For macOS (zsh) — MySQL example:

echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

For Linux (bash) — PostgreSQL example:

echo 'export PATH="/usr/local/pgsql/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc

On Windows, go to "System Properties" → "Environment Variables" → "Path" and add the installation folder for your database client (e.g., for MySQL: C:\Program Files\MySQL\MySQL Server 8.0\bin).

If the command is found but you cannot connect, the database server may not be running. SQLite is file-based and does not require a server, but MySQL and PostgreSQL need the server to be running.

MySQL: Socket Error

If the following error is displayed, the MySQL server is not running.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Check the server status.

brew services list

If MySQL shows stopped or error, start it with the following command.

brew services start mysql

After starting, try connecting again.

mysql -u root -p

If you still cannot connect, the socket file path may be incorrect. Check the socket file MySQL is actually using.

mysql_config --socket

If the displayed path differs from /tmp/mysql.sock, specify it with the --socket option when connecting.

mysql -u root -p --socket=/var/run/mysqld/mysqld.sock

MySQL: Access Denied Error

If the following error is displayed, the password is incorrect or the user does not exist.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Right after installing with Homebrew, the root user may not have a password. Try connecting without one.

mysql -u root

If that works, set a password.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

PostgreSQL: Connection Refused Error

If the following error is displayed, the PostgreSQL server is not running.

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: Connection refused

Check the server status and start it.

brew services list
brew services start postgresql

After starting, try connecting again.

psql -U postgres

PostgreSQL: Role Does Not Exist Error

If the following error is displayed, the role (account) for the specified username does not exist.

FATAL: role "postgres" does not exist

When installed with Homebrew, the default role is your macOS username. Try connecting with the following command.

psql -d postgres

To create the postgres role, run the following.

createuser -s postgres