Syntax

Database Operations

• Create a database:

CREATE DATABASE $DB_NAME;

• Use a database:

USE $DB_NAME;

• Drop a database:

DROP DATABASE $DB_NAME;

Table Operations

• Create a table:

CREATE TABLE $TABEL_NAME (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

• Alter a table:

ALTER TABLE $TABEL_NAME ADD age INT;

• Drop a table:

DROP TABLE $TABEL_NAME;

Data Manipulation (CRUD)

• Create:

INSERT INTO $TABEL_NAME (name, email) VALUES ('John', '[e-mail]');

• Read:

SELECT * FROM $TABEL_NAME;
SELECT name FROM users WHERE id = 1;

• Update:

UPDATE $TABEL_NAME SET email = '[e-mail]' WHERE id = 1;

• Delete:

DELETE FROM $TABEL_NAME WHERE id = 1;

Constraints

PRIMARY KEY

Ensures each record in a table is unique and identifiable.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

FOREIGN KEY

Establishes a relationship between two tables.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

UNIQUE

Ensures all values in a column are different (no duplicates).

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

NOT NULL

Ensures a column cannot contain NULL values.

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

CHECK

Ensures that values in a column meet a specific condition.

CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  balance DECIMAL(10,2) CHECK (balance >= 0)
);

DEFAULT

Sets a default value for a column if no value is specified.

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  theme VARCHAR(20) DEFAULT 'light',
  notifications BOOLEAN DEFAULT TRUE
);

Example

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2) DEFAULT 0.00,
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);