MySQL, MariaDB & SQL Server

# Docker
docker run -d \
  --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=devpassword \
  -e MYSQL_DATABASE=myapp \
  -p 3306:3306 \
  mysql:8.0 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

mysql -h 127.0.0.1 -u root -p myapp

-- Always use utf8mb4 for emoji + full Unicode support
CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE myapp;

CREATE TABLE users (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid        CHAR(36)        NOT NULL UNIQUE DEFAULT (UUID()),
  email       VARCHAR(320)    NOT NULL,
  display_name VARCHAR(100),
  plan        ENUM('free','pro','enterprise') NOT NULL DEFAULT 'free',
  metadata    JSON,
  created_at  DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at  DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                              ON UPDATE CURRENT_TIMESTAMP(6),
  UNIQUE KEY uk_email (email),
  INDEX idx_plan (plan),
  INDEX idx_created (created_at)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  status      ENUM('pending','processing','shipped','delivered','cancelled')
              NOT NULL DEFAULT 'pending',
  total_cents INT UNSIGNED NOT NULL,
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  INDEX idx_user_status (user_id, status),
  INDEX idx_created (created_at),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Composite: put equality columns first, range last
-- Query: WHERE user_id = ? AND status = ? AND created_at > ?
CREATE INDEX idx_orders_lookup ON orders(user_id, status, created_at);

-- Covering index: includes all columns in SELECT
CREATE INDEX idx_orders_covering ON orders(user_id, status)
  INCLUDE (id, total_cents, created_at);  -- MySQL 8.0.13+

-- Prefix index for long strings
CREATE INDEX idx_url_prefix ON pages(url(255));

-- Invisible index — test removal impact without dropping
ALTER TABLE orders ALTER INDEX idx_created INVISIBLE;
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';
ALTER TABLE orders ALTER INDEX idx_created VISIBLE;

-- EXPLAIN to check index usage
EXPLAIN FORMAT=JSON
SELECT * FROM orders
WHERE user_id = 99 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

-- Store flexible product attributes
CREATE TABLE products (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku   VARCHAR(100) NOT NULL UNIQUE,
  name  VARCHAR(255) NOT NULL,
  attrs JSON
);

-- Insert JSON
INSERT INTO products (sku, name, attrs) VALUES
  ('LAPTOP-001', 'ThinkPad X1',
   JSON_OBJECT('ram_gb', 32, 'storage_gb', 1000, 'weight_kg', 1.12));

-- Query JSON paths
SELECT name, attrs->>'$.ram_gb' AS ram
FROM products
WHERE attrs->>'$.ram_gb' > 16;

-- Generated column + index on JSON field
ALTER TABLE products ADD COLUMN ram_gb INT
  GENERATED ALWAYS AS (attrs->>'$.ram_gb') STORED;
CREATE INDEX idx_ram ON products(ram_gb);

-- Now this uses the index:
SELECT * FROM products WHERE ram_gb >= 32;

-- Primary (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce-gtid-consistency = ON

-- Replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary-host',
  SOURCE_USER='replication',
  SOURCE_PASSWORD='reppass',
  SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

DELIMITER //
CREATE PROCEDURE GetUserOrders(
  IN p_user_id BIGINT UNSIGNED,
  IN p_status  VARCHAR(20),
  IN p_limit   INT
)
BEGIN
  SELECT
    o.id,
    o.status,
    o.total_cents,
    o.created_at
  FROM orders o
  WHERE o.user_id = p_user_id
    AND (p_status IS NULL OR o.status = p_status)
  ORDER BY o.created_at DESC
  LIMIT p_limit;
END //
DELIMITER ;

CALL GetUserOrders(99, 'delivered', 10);

-- Sequences (cleaner than AUTO_INCREMENT for distributed systems)
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXT VALUE FOR order_seq;

-- Temporal tables (automatic history tracking)
CREATE TABLE prices (
  product_id INT,
  price DECIMAL(10,2),
  PERIOD FOR app_time(valid_from, valid_to)
) WITH SYSTEM VERSIONING;

-- Query history at a point in time
SELECT * FROM prices FOR SYSTEM_TIME AS OF '2025-12-01 12:00:00';

-- Dynamic columns (semi-structured, like JSONB)
CREATE TABLE sensor_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT,
  data BLOB
);
INSERT INTO sensor_data (device_id, data) VALUES (1,
  COLUMN_CREATE('temperature', 23.5, 'humidity', 65, 'pressure', 1013));
SELECT COLUMN_GET(data, 'temperature' AS DOUBLE) FROM sensor_data WHERE id = 1;

# galera.cnf — on each of 3 nodes
[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://node1,node2,node3"
wsrep_cluster_name="my_cluster"
wsrep_node_address="THIS_NODE_IP"
wsrep_sst_method=rsync
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

# Docker
docker run -d \
  --name sqlserver-dev \
  -e ACCEPT_EULA=Y \
  -e SA_PASSWORD=DevPassword1! \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2022-latest

# Connect with sqlcmd
sqlcmd -S localhost -U SA -P DevPassword1! -Q "SELECT @@VERSION"

-- SQL Server uses T-SQL — key differences from standard SQL
USE myapp;
GO

-- Table creation
CREATE TABLE dbo.Users (
  Id          BIGINT          IDENTITY(1,1) PRIMARY KEY,
  ExternalId  UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
  Email       NVARCHAR(320)   NOT NULL,
  DisplayName NVARCHAR(100),
  Plan        NVARCHAR(20)    NOT NULL DEFAULT 'free'
              CHECK (Plan IN ('free','pro','enterprise')),
  Metadata    NVARCHAR(MAX),     -- store JSON as NVARCHAR
  CreatedAt   DATETIME2(7)    NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt   DATETIME2(7)    NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UQ_Users_Email UNIQUE (Email)
);

-- TOP instead of LIMIT
SELECT TOP 20 * FROM dbo.Orders
WHERE UserId = 99
ORDER BY CreatedAt DESC;

-- OFFSET-FETCH pagination
SELECT * FROM dbo.Orders
ORDER BY CreatedAt DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- String functions
SELECT CONCAT(FirstName, ' ', LastName) AS FullName,
       LEN(Email) AS EmailLen,
       SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS Username
FROM dbo.Users;

-- IIF (inline conditional)
SELECT Id, IIF(Plan = 'pro', 'Premium', 'Standard') AS Tier FROM dbo.Users;

-- TRY_CAST — safe type conversion
SELECT TRY_CAST(Metadata AS NVARCHAR(MAX)) FROM dbo.Products;

-- JSON stored in NVARCHAR(MAX)
INSERT INTO dbo.Products (Sku, Name, Attrs) VALUES
  ('LAPTOP-001', 'ThinkPad X1',
   N'{"ram_gb": 32, "storage_gb": 1000, "weight_kg": 1.12}');

-- Extract values
SELECT
  Name,
  JSON_VALUE(Attrs, '$.ram_gb')     AS RamGB,
  JSON_VALUE(Attrs, '$.weight_kg')  AS WeightKg
FROM dbo.Products
WHERE JSON_VALUE(Attrs, '$.ram_gb') = '32';

-- ISJSON validation
SELECT * FROM dbo.Products WHERE ISJSON(Attrs) = 1;

-- FOR JSON — output rows as JSON
SELECT Id, Name, Plan
FROM dbo.Users
FOR JSON PATH, ROOT('users');

-- OPENJSON — parse JSON array into rows
SELECT * FROM OPENJSON(
  N'[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]'
) WITH (id INT, name NVARCHAR(50));

-- Create AG listener
CREATE AVAILABILITY GROUP [MyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [myapp]
REPLICA ON
  'NODE1' WITH (
    ENDPOINT_URL = 'TCP://node1:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
  ),
  'NODE2' WITH (
    ENDPOINT_URL = 'TCP://node2:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
  );

# Create with Azure CLI
az sql server create \
  --name myapp-sql \
  --resource-group myRG \
  --location eastus \
  --admin-user sqladmin \
  --admin-password $SQL_PASS \
  --enable-public-network false   # VNet only

az sql db create \
  --server myapp-sql \
  --resource-group myRG \
  --name myapp \
  --edition GeneralPurpose \
  --family Gen5 \
  --capacity 4 \
  --zone-redundant true \
  --backup-storage-redundancy Geo \
  --ledger-on          # immutable tamper-proof audit log

// .NET connection string
var conn = new SqlConnectionStringBuilder {
    DataSource    = "myapp-sql.database.windows.net",
    InitialCatalog = "myapp",
    Authentication = SqlAuthenticationMethod.ActiveDirectoryManagedIdentity,
    Encrypt        = true,
}.ConnectionString;