CREATE DATABASE

-- ===== Create =====
CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- ===== Show =====
SHOW DATABASES;
SHOW CREATE DATABASE shop;

-- ===== Use =====
USE shop;

-- ===== Drop (DESTRUCTIVE) =====
DROP DATABASE IF EXISTS old_shop;

-- ===== Charset + collation matter =====
-- utf8mb4 (NOT utf8) for full Unicode + emoji.
-- utf8mb4_0900_ai_ci on MySQL 8+ — accent-insensitive, case-insensitive.
-- utf8mb4_bin if you need binary-exact comparisons.

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- ===== Users =====
-- Modern MySQL 8 syntax (no GRANT ... IDENTIFIED BY anymore):
CREATE USER 'app_dev'@'%' IDENTIFIED BY 'devpw';
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'ropw';

-- ===== Grants =====
GRANT ALL PRIVILEGES ON shop.* TO 'app_dev'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_dev'@'%';
GRANT SELECT ON shop.* TO 'app_readonly'@'%';

-- Specific objects:
GRANT EXECUTE ON PROCEDURE shop.recalc_totals TO 'app_dev'@'%';

-- Required for the changes to take effect:
FLUSH PRIVILEGES;

-- ===== List + revoke =====
SHOW GRANTS FOR 'app_dev'@'%';
REVOKE INSERT, UPDATE ON shop.* FROM 'app_dev'@'%';

-- ===== Drop user =====
DROP USER 'old_user'@'%';

-- ===== Permission tiers (suggested) =====
-- ddl_admin   ALL on the db; deploy-time migrations only
-- app_writer  SELECT, INSERT, UPDATE, DELETE; the running app
-- app_reader  SELECT only; reporting / analytics
-- backup      SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT

-- ===== Per-host restrictions =====
-- '%' means any host. Restrict where you can:
CREATE USER 'app_dev'@'10.0.0.%' IDENTIFIED BY 'devpw';
-- Or use TLS-required:
ALTER USER 'app_dev'@'%' REQUIRE SSL;

-- ===== Default schemas in a real install =====
-- mysql              system tables (auth, system)
-- information_schema metadata views
-- performance_schema observability
-- sys                helper views

-- ===== Patterns to internalise =====
-- - utf8mb4 + utf8mb4_0900_ai_ci on day one
-- - Distinct DB users per environment + per role
-- - Least privilege; REVOKE generously
-- - REQUIRE SSL on production users

-- ===== Pitfalls =====
-- - GRANT ALL to the app user (no separation of DDL vs DML)
-- - Default 'root'@'%' with weak password and open network
-- - Using utf8 (3-byte) instead of utf8mb4
-- - Mixing case-sensitive + case-insensitive collations in joins

CREATE DATABASE myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
USE myapp;