MariaDB online editor

Write, Run & Share MariaDB queries online using OneCompiler's MariaDB online editor for free. It's one of the robust, feature-rich online editors for MariaDB. Getting started with the OneCompiler's MariaDB editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'MariaDB' and start writing queries to learn and test online without worrying about tedious process of installation.

About MariaDB

MariaDB is a community-developed, open-source relational database management system forked from MySQL. It maintains high compatibility with MySQL while adding performance improvements, new storage engines like Aria and ColumnStore, and enhanced security features. MariaDB is used by major organizations and is the default database in many Linux distributions, offering a reliable, drop-in replacement for MySQL.

Syntax help

CREATE TABLE

Creates a new table with specified columns, data types, and constraints. MariaDB supports all common data types including INT, VARCHAR, TEXT, DATE, DATETIME, and DECIMAL. Use PRIMARY KEY for unique identifiers, NOT NULL for required fields, and DEFAULT for automatic values. Foreign keys establish relationships between tables.

CREATE TABLE EMPLOYEE (
  empId INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  dept VARCHAR(20),
  salary DECIMAL(10,2),
  hire_date DATE DEFAULT CURRENT_DATE
);

-- Table with auto-increment and foreign key
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  emp_id INT,
  amount DECIMAL(10,2),
  order_date DATETIME DEFAULT NOW(),
  FOREIGN KEY (emp_id) REFERENCES EMPLOYEE(empId)
);

INSERT

Adds new rows to a table. Specify column names and values for clarity, or provide values for all columns in order. INSERT supports multiple value sets in one statement for efficient batch loading. Use INSERT IGNORE to skip rows that would cause duplicate key errors, or ON DUPLICATE KEY UPDATE to upsert data.

INSERT INTO EMPLOYEE (empId, name, dept, salary)
VALUES (1, 'Clark', 'Sales', 50000);

INSERT INTO EMPLOYEE (empId, name, dept, salary)
VALUES (2, 'Dave', 'Accounting', 45000);

INSERT INTO EMPLOYEE (empId, name, dept, salary)
VALUES (3, 'Ava', 'Sales', 55000);

-- Insert multiple rows at once
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES
  (4, 'Bob', 'Engineering', 65000),
  (5, 'Eve', 'HR', 52000);

-- Insert with default values
INSERT INTO EMPLOYEE (empId, name) VALUES (6, 'Charlie');

-- Insert from select
INSERT INTO employee_backup SELECT * FROM EMPLOYEE;

SELECT

Retrieves data from tables with powerful filtering, sorting, and pagination. Use WHERE for conditions, ORDER BY for sorting, and LIMIT with OFFSET for pagination. MariaDB supports all SQL operators, pattern matching with LIKE, and range queries with BETWEEN. SELECT is the most versatile SQL statement for data retrieval.

-- Select all
SELECT * FROM EMPLOYEE;

-- Select specific columns
SELECT name, salary FROM EMPLOYEE;

-- Select with condition
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';

-- Select with multiple conditions
SELECT * FROM EMPLOYEE WHERE dept = 'Sales' AND salary > 50000;

-- Select with pattern matching
SELECT * FROM EMPLOYEE WHERE name LIKE 'A%';

-- Select with ordering
SELECT * FROM EMPLOYEE ORDER BY salary DESC;

-- Select with limit and offset (pagination)
SELECT * FROM EMPLOYEE LIMIT 10 OFFSET 20;

-- Select with BETWEEN
SELECT * FROM EMPLOYEE WHERE salary BETWEEN 45000 AND 60000;

UPDATE

Modifies existing rows in a table. Always use a WHERE clause to target specific rows - without it, all rows will be updated. UPDATE supports calculations, subqueries, and joins for complex modifications. Use LIMIT to restrict the number of updated rows in large tables.

-- Update single column
UPDATE EMPLOYEE SET dept = 'Marketing' WHERE empId = 1;

-- Update multiple columns
UPDATE EMPLOYEE SET dept = 'Sales', salary = 52000 WHERE empId = 2;

-- Update with calculation
UPDATE EMPLOYEE SET salary = salary * 1.10 WHERE dept = 'Engineering';

-- Update with subquery
UPDATE EMPLOYEE SET salary = (SELECT AVG(salary) FROM EMPLOYEE)
WHERE empId = 3;

-- Update with LIMIT
UPDATE EMPLOYEE SET salary = salary + 1000 WHERE dept = 'Sales' LIMIT 2;

DELETE

Removes rows from a table based on conditions. Always include a WHERE clause to avoid deleting all data. For removing all rows quickly while keeping the table structure, use TRUNCATE instead. DELETE with LIMIT restricts the number of rows removed, useful for batch deletions in large tables.

-- Delete specific row
DELETE FROM EMPLOYEE WHERE empId = 1;

-- Delete with condition
DELETE FROM EMPLOYEE WHERE dept = 'Sales' AND salary < 45000;

-- Delete with subquery
DELETE FROM EMPLOYEE
WHERE salary < (SELECT AVG(salary) FROM (SELECT salary FROM EMPLOYEE) AS temp);

-- Delete with limit
DELETE FROM EMPLOYEE WHERE dept = 'Inactive' LIMIT 100;

-- Remove all data (keeps table structure)
TRUNCATE TABLE EMPLOYEE;

JOINs

Combines rows from multiple tables based on related columns. INNER JOIN returns only matching rows, LEFT JOIN includes all left table rows with NULLs for non-matches, RIGHT JOIN does the opposite, and CROSS JOIN produces a Cartesian product. JOINs are essential for querying normalized database schemas.

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(30),
  location VARCHAR(50)
);

INSERT INTO departments VALUES (1, 'Sales', 'New York');
INSERT INTO departments VALUES (2, 'Engineering', 'San Francisco');
INSERT INTO departments VALUES (3, 'HR', 'Chicago');

-- Inner join
SELECT e.name, e.salary, d.dept_name, d.location
FROM EMPLOYEE e
INNER JOIN departments d ON e.dept = d.dept_name;

-- Left join (all employees, even without matching dept)
SELECT e.name, d.dept_name
FROM EMPLOYEE e
LEFT JOIN departments d ON e.dept = d.dept_name;

-- Right join (all departments, even without employees)
SELECT e.name, d.dept_name
FROM EMPLOYEE e
RIGHT JOIN departments d ON e.dept = d.dept_name;

-- Multiple joins
SELECT e.name, d.dept_name, o.amount
FROM EMPLOYEE e
LEFT JOIN departments d ON e.dept = d.dept_name
LEFT JOIN orders o ON e.empId = o.emp_id;

Aggregations

MariaDB provides aggregate functions for data analysis: COUNT, SUM, AVG, MIN, MAX, and GROUP_CONCAT. Use GROUP BY to aggregate by categories, HAVING to filter grouped results (unlike WHERE which filters before grouping). Aggregations are fundamental for reporting, dashboards, and statistical analysis.

-- Basic aggregates
SELECT
  COUNT(*) as total_employees,
  SUM(salary) as total_salary,
  AVG(salary) as avg_salary,
  MIN(salary) as min_salary,
  MAX(salary) as max_salary
FROM EMPLOYEE;

-- Group by department
SELECT dept, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM EMPLOYEE
GROUP BY dept;

-- Having clause (filter groups)
SELECT dept, COUNT(*) as emp_count
FROM EMPLOYEE
GROUP BY dept
HAVING COUNT(*) > 1;

-- GROUP_CONCAT (combine values)
SELECT dept, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as employees
FROM EMPLOYEE
GROUP BY dept;

-- Group by with rollup (subtotals)
SELECT dept, COUNT(*) as cnt, SUM(salary) as total
FROM EMPLOYEE
GROUP BY dept WITH ROLLUP;

Subqueries

MariaDB supports subqueries in SELECT, FROM, WHERE, and HAVING clauses. Subqueries can return single values, lists, or entire result sets. Correlated subqueries reference outer query columns for row-by-row processing. Use EXISTS for existence checks and IN for list membership tests.

-- Subquery in WHERE (single value)
SELECT * FROM EMPLOYEE
WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE);

-- Subquery with IN
SELECT * FROM EMPLOYEE
WHERE dept IN (SELECT dept FROM EMPLOYEE WHERE salary > 50000);

-- Correlated subquery
SELECT e1.name, e1.salary, e1.dept
FROM EMPLOYEE e1
WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE e2 WHERE e2.dept = e1.dept);

-- EXISTS subquery
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM EMPLOYEE e WHERE e.dept = d.dept_name);

-- Subquery in FROM (derived table)
SELECT dept, avg_sal
FROM (
  SELECT dept, AVG(salary) as avg_sal
  FROM EMPLOYEE
  GROUP BY dept
) AS dept_stats
WHERE avg_sal > 50000;

CASE Expression

CASE provides conditional logic in SQL queries, similar to if-else statements. Use searched CASE for complex conditions or simple CASE for value matching. CASE expressions work in SELECT, WHERE, ORDER BY, and even inside aggregate functions for conditional aggregation.

SELECT name, salary,
  CASE
    WHEN salary >= 60000 THEN 'Senior'
    WHEN salary >= 45000 THEN 'Mid-Level'
    ELSE 'Junior'
  END as level
FROM EMPLOYEE;

-- Simple CASE
SELECT name,
  CASE dept
    WHEN 'Sales' THEN 'Revenue Team'
    WHEN 'Engineering' THEN 'Tech Team'
    ELSE 'Support Team'
  END as team
FROM EMPLOYEE;

-- CASE in aggregation
SELECT
  COUNT(CASE WHEN dept = 'Sales' THEN 1 END) as sales_count,
  COUNT(CASE WHEN dept = 'Engineering' THEN 1 END) as eng_count,
  SUM(CASE WHEN salary >= 50000 THEN salary ELSE 0 END) as high_earner_total
FROM EMPLOYEE;

-- CASE in ORDER BY
SELECT * FROM EMPLOYEE
ORDER BY CASE dept
  WHEN 'Engineering' THEN 1
  WHEN 'Sales' THEN 2
  ELSE 3
END;

String Functions

MariaDB provides extensive string functions for text manipulation. CONCAT joins strings, SUBSTRING extracts portions, UPPER/LOWER change case, TRIM removes whitespace, and REPLACE substitutes text. REGEXP enables powerful pattern matching beyond simple LIKE queries.

SELECT
  CONCAT(name, ' - ', dept) as employee_info,
  UPPER(name) as upper_name,
  LOWER(dept) as lower_dept,
  LENGTH(name) as name_length,
  SUBSTRING(name, 1, 3) as short_name,
  REPLACE(dept, 'Sales', 'Revenue') as new_dept
FROM EMPLOYEE;

-- String trimming
SELECT TRIM('  hello  ') as trimmed,
       LTRIM('  hello') as left_trimmed,
       RTRIM('hello  ') as right_trimmed;

-- Pattern matching with REGEXP
SELECT * FROM EMPLOYEE WHERE name REGEXP '^[A-D]';

-- Find position in string
SELECT name, LOCATE('a', LOWER(name)) as a_position FROM EMPLOYEE;

Date Functions

MariaDB has comprehensive date and time functions. NOW() and CURDATE() return current datetime and date. DATE_ADD and DATE_SUB perform date arithmetic. EXTRACT, YEAR, MONTH, DAY retrieve components. DATE_FORMAT creates custom date strings for display.

SELECT
  NOW() as current_datetime,
  CURDATE() as current_date,
  CURTIME() as current_time,
  DATE_ADD(CURDATE(), INTERVAL 7 DAY) as next_week,
  DATE_SUB(CURDATE(), INTERVAL 1 MONTH) as last_month,
  YEAR(CURDATE()) as year,
  MONTH(CURDATE()) as month,
  DAY(CURDATE()) as day;

-- Date formatting
SELECT name, DATE_FORMAT(hire_date, '%M %d, %Y') as formatted_date
FROM EMPLOYEE
WHERE hire_date IS NOT NULL;

-- Date difference
SELECT name, DATEDIFF(CURDATE(), hire_date) as days_employed
FROM EMPLOYEE;

-- Filter by date parts
SELECT * FROM EMPLOYEE
WHERE YEAR(hire_date) = 2024 AND MONTH(hire_date) >= 6;

Views

A view is a virtual table based on a SELECT query. Views simplify complex queries, provide abstraction layers, and can restrict access to sensitive columns. MariaDB views can be simple (directly updatable) or complex (read-only). Use views to encapsulate business logic and standardize data access.

-- Create view
CREATE VIEW sales_employees AS
SELECT empId, name, salary
FROM EMPLOYEE
WHERE dept = 'Sales';

-- Query the view
SELECT * FROM sales_employees WHERE salary > 50000;

-- View with calculations
CREATE VIEW employee_stats AS
SELECT
  dept,
  COUNT(*) as emp_count,
  AVG(salary) as avg_salary,
  MAX(salary) as max_salary
FROM EMPLOYEE
GROUP BY dept;

SELECT * FROM employee_stats;

-- Drop view
DROP VIEW sales_employees;
DROP VIEW employee_stats;

Common Table Expressions (CTEs)

CTEs (WITH clause) define temporary named result sets within a query. They improve readability for complex queries and enable recursive queries for hierarchical data. CTEs are referenced like tables but exist only for the duration of the query. Use them to break down complex logic into understandable steps.

-- Simple CTE
WITH high_earners AS (
  SELECT * FROM EMPLOYEE WHERE salary > 50000
)
SELECT * FROM high_earners WHERE dept = 'Sales';

-- Multiple CTEs
WITH
dept_stats AS (
  SELECT dept, AVG(salary) as avg_sal FROM EMPLOYEE GROUP BY dept
),
high_salary_depts AS (
  SELECT dept FROM dept_stats WHERE avg_sal > 50000
)
SELECT e.* FROM EMPLOYEE e
JOIN high_salary_depts h ON e.dept = h.dept;

-- CTE for ranking
WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rank_in_dept
  FROM EMPLOYEE
)
SELECT * FROM ranked WHERE rank_in_dept <= 3;

Window Functions

Window functions perform calculations across related rows without collapsing results like GROUP BY. ROW_NUMBER, RANK, DENSE_RANK assign rankings. LAG and LEAD access adjacent rows. Running totals and moving averages use SUM/AVG with window frames. These are powerful for analytics and reporting.

SELECT name, dept, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as dept_rank,
  SUM(salary) OVER (PARTITION BY dept) as dept_total,
  AVG(salary) OVER () as company_avg
FROM EMPLOYEE;

-- Running total
SELECT name, salary,
  SUM(salary) OVER (ORDER BY empId) as running_total
FROM EMPLOYEE;

-- Compare with previous row
SELECT name, salary,
  LAG(salary, 1) OVER (ORDER BY empId) as prev_salary,
  salary - LAG(salary, 1) OVER (ORDER BY empId) as difference
FROM EMPLOYEE;

-- Percentile ranking
SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) as percentile
FROM EMPLOYEE;

ALTER & DROP

ALTER TABLE modifies table structure - add, modify, or drop columns and constraints. DROP TABLE removes tables permanently. RENAME changes table names. Use ALTER with caution on production tables as some operations lock the table. Always backup data before structural changes.

-- Add column
ALTER TABLE EMPLOYEE ADD email VARCHAR(100);

-- Modify column
ALTER TABLE EMPLOYEE MODIFY name VARCHAR(100);

-- Drop column
ALTER TABLE EMPLOYEE DROP COLUMN email;

-- Rename column
ALTER TABLE EMPLOYEE CHANGE dept department VARCHAR(20);

-- Add index
ALTER TABLE EMPLOYEE ADD INDEX idx_dept (dept);

-- Add constraint
ALTER TABLE EMPLOYEE ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Rename table
ALTER TABLE EMPLOYEE RENAME TO employees;

-- Drop table
DROP TABLE IF EXISTS employees;