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.
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.
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) );
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;
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;
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;
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;
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;
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;
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 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;
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;
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;
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;
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 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 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;