Learn MySQL Syntax
Ajesh Rana
Published on 02-08-2024
Introduction
SQL (Structured Query Language) stands as a foundational tool for interacting with relational databases. Understanding SQL not only helps in querying and manipulating data but also in designing efficient database schemas. This series of blog posts will delve into various SQL concepts and functionalities, ranging from basic operations to advanced techniques. We will explore SQL commands like SELECT, JOIN, and ALTER, and examine their applications in real-world scenarios. Additionally, we'll compare SQL with No-SQL databases to provide a comprehensive understanding of their respective use cases. Whether you're a beginner or an experienced developer, these insights will enhance your database skills and broaden your knowledge of both traditional and modern data management practices.
1. Database
A database is an organized collection of structured data, typically stored and accessed electronically. Databases allow data to be easily stored, managed, and retrieved.
2. Database Management System (DBMS)
DBMS is software that enables users to create, manage, and interact with databases. Examples include MySQL, PostgreSQL, and SQLite.
3. Types of DBMS
DBMS can be categorized into four types: Hierarchical, Network, Relational (RDBMS), and Object-oriented.
4. RDBMS vs. Non-RDBMS
RDBMS stores data in structured tables with relationships, while Non-RDBMS (NoSQL) databases use different data models like key-value, document-oriented, and graph databases.
5. SQL
SQL (Structured Query Language) is a standardized language used to interact with relational databases, enabling data retrieval, insertion, updating, and deletion.
6. MySQL
MySQL is an open-source relational database management system that uses SQL to manage data. It is widely used in web applications due to its reliability, flexibility, and performance.
7. Data Manipulation Language (DML)
DML commands are used to manipulate data within a database. Common DML commands include SELECT, INSERT, UPDATE, and DELETE.
8. Data Query Language (DQL)
DQL is primarily focused on querying data from a database using the SELECT statement.
9. Data Control Language (DCL)
DCL commands like GRANT and REVOKE are used to control access to data in a database.
10. Transaction Control Language (TCL)
TCL commands like COMMIT and ROLLBACK manage transactions within a database, ensuring data integrity.
11. Data Types
SQL supports various data types including INTEGER, VARCHAR, DATE, and BOOLEAN. These define the type of data that can be stored in a table column.
12. Constraints and Their Types
Constraints ensure the validity of data within a database. Common constraints include NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.
13. Uses of Constraints in Table Creation
Constraints can be applied during table creation to enforce rules on the data. For example, a PRIMARY KEY constraint ensures that each row has a unique identifier.
14. Primary Key vs. Foreign Key
A PRIMARY KEY uniquely identifies each record in a table, while a FOREIGN KEY establishes a relationship between two tables. If a FOREIGN KEY is linked, you can't drop or truncate the table it references.
15. Create Database
CREATE DATABASE mydatabase;
16. Create Table
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);
17. Insert Data into Table (Two Methods)
Method 1:
INSERT INTO employees (id, name, department)
VALUES (1, 'John Doe', 'HR');
Method 2 (Insert without specifying columns if all columns are filled):
INSERT INTO employees
VALUES (2, 'Jane Doe', 'IT');
18. Update Query for Existing Data
UPDATE employees
SET department = 'Finance'
WHERE id = 1;
19. Alter Command to Add Column
ALTER TABLE employees
ADD email VARCHAR(100);
20. Alter Command to Drop Column
ALTER TABLE employees
DROP COLUMN email;
21. Alter Command to Rename/Modify Column
-- Rename column
ALTER TABLE employees
RENAME COLUMN department TO dept_name;
-- Modify column
ALTER TABLE employees
MODIFY COLUMN dept_name VARCHAR(150);
22. Delete Command for Existing Data
DELETE FROM employees
WHERE id = 2;
23. Drop Table
DROP TABLE employees;
24. Truncate Table
TRUNCATE TABLE employees;
25. Drop Database
DROP DATABASE mydatabase;
26. USE
The USE command is used to select a specific database to work with.
USE mydatabase;
27. Show Databases
The SHOW DATABASES command displays a list of all databases available in the MySQL server.
SHOW DATABASES;
29. Select Command Examples
SELECT name FROM employees;
SELECT name, department FROM employees;
SELECT * FROM employees;
SELECT DISTINCT department FROM employees;
30. WHERE Clause Examples
SELECT * FROM employees WHERE department = 'HR';
SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;
SELECT * FROM employees WHERE department = 'HR' OR department = 'Finance';
SELECT * FROM employees WHERE salary IN (60000, 80000);
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
-- Any position
SELECT * FROM employees WHERE name LIKE '%Smith%';
-- First position
SELECT * FROM employees WHERE name LIKE 'S%';
-- Last position
SELECT * FROM employees WHERE name LIKE '%n';
31. Operators and Their Types
SQL operators are used to perform operations on data. They can be classified into various types like arithmetic, comparison, logical, and bitwise operators.
32. Arithmetic Operators
SELECT salary + 10000 AS NewSalary FROM employees;
33. Comparison Operators
SELECT * FROM employees WHERE salary > 60000;
34. Logical Operators
SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;
35. Bitwise Operators
SELECT 1 & 2;
36. Using the LIMIT Clause
SELECT * FROM employees LIMIT 5;
37. Using the OFFSET Clause
SELECT * FROM employees LIMIT 5 OFFSET 10;
38. Using the ORDER BY Clause
-- Order by ascending
SELECT * FROM employees ORDER BY salary ASC;
-- Order by descending
SELECT * FROM employees ORDER BY salary DESC;
39. How to Import an Excel File into MySQL
Importing an Excel file into MySQL can be done using the MySQL Workbench or by converting the Excel file to CSV and using the LOAD DATA INFILE command.
40. UPPER() Function
SELECT UPPER(name) FROM employees;
41. LOWER() Function
SELECT LOWER(name) FROM employees;
42. LENGTH() Function
SELECT LENGTH(name) FROM employees;
43. CONCAT() Function
SELECT CONCAT(first_name, ' ', last_name) FROM employees;
44. REPLACE() Function
SELECT REPLACE(name, 'John', 'Jonathan') FROM employees;
45. NOW() Function
SELECT NOW();
46. FORMAT() Function
SELECT FORMAT(salary, 2) FROM employees;
47. TRIM() Function
SELECT TRIM(' name ') FROM employees;
48. SUBSTRING() Function
SELECT SUBSTRING(name, 1, 3) FROM employees;
49. COUNT() Function
SELECT COUNT(*) FROM employees;
50. SUM() Function
SELECT SUM(salary) FROM employees;
51. AVG() Function
SELECT AVG(salary) FROM employees;
52. MIN() Function
SELECT MIN(salary) FROM employees;
53. MAX() Function
SELECT MAX(salary) FROM employees;
54. ROUND() Function
SELECT ROUND(salary, 2) FROM employees;
55. GROUP BY Clause
SELECT department, COUNT(*) FROM employees GROUP BY department;
56. HAVING Clause
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10 ORDER BY COUNT(*) DESC;
57. SQL Query Order Execution
SELECT *
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY salary DESC
LIMIT 10;
58. Working with Time and Date Functions
SELECT * FROM hr_data WHERE YEAR(doj) > 24;
59. Displaying Time Zone Information
SHOW TIMEZONE;
60. Selecting Current Date and Time
SELECT NOW();
61. Selecting Time of Day
SELECT TIMEOFDAY();
62. Selecting Current Time
SELECT CURRENT_TIME;
63. Selecting Current Date
SELECT CURRENT_DATE;
64. Using the EXTRACT() Function
SELECT EXTRACT(YEAR FROM doj) FROM employees;
65. Common Table Expressions (CTE)
WITH EmployeeCTE AS (
SELECT department, COUNT(*) AS dept_count
FROM employees
GROUP BY department
)
SELECT * FROM EmployeeCTE WHERE dept_count > 5;
66. Views: Virtual Tables
CREATE VIEW EmployeeView AS
SELECT first_name, last_name, department
FROM employees;
67. Triggers
CREATE TRIGGER employee_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_changes (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);
END;
68. Stored Procedure
Stored procedures allow you to store precompiled SQL statements in the database, making them reusable and efficient.
CREATE PROCEDURE GetEmployeeData()
BEGIN
SELECT * FROM employees WHERE department = 'HR';
END;
69. Call Stored Procedure
Calling a stored procedure executes the precompiled SQL code.
CALL GetEmployeeData();
70. Drop Stored Procedure
Dropping a stored procedure removes it from the database.
DROP PROCEDURE IF EXISTS GetEmployeeData;
71. Commit and Rollback
Commit permanently saves the changes made in a transaction, while rollback undoes them.
BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE department = 'HR';
COMMIT;
72. Normalization
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.
73. 1NF, 2NF, 3NF
These are the first three normal forms used in database normalization to ensure efficient data storage.
74. Data Modification: Three Types of Anomalies
Update, deletion, and insertion anomalies can occur if a database is not properly normalized.
75. ER Model
The Entity-Relationship (ER) model is a conceptual framework for representing the data structure of a database.
76. SELECT + WHERE
The WHERE clause is used to filter records based on a specified condition.
SELECT * FROM employees WHERE department = 'HR';
77. SELECT + WHERE + AND
AND is used to filter records based on multiple conditions.
SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;
78. SELECT + WHERE + OR
OR is used to filter records where at least one of the conditions is true.
SELECT * FROM employees WHERE department = 'HR' OR salary > 50000;
79. SELECT + WHERE + NOT
NOT is used to filter records that do not match the specified condition.
SELECT * FROM hr WHERE CTC = 10 AND NOT CTC = 11;
80. SELECT + WHERE + Comparison Operator
Comparison operators are used in the WHERE clause to filter records based on conditions like =, >, <, etc.
SELECT * FROM employees WHERE salary > 50000;
81. SELECT + COUNT + DISTINCT
COUNT(DISTINCT) returns the number of unique non-null values.
SELECT COUNT(DISTINCT project) FROM hr_data;
82. SELECT + WHERE + IN
IN is used to filter records based on a list of specified values.
SELECT * FROM employees WHERE department IN ('HR', 'Finance');
83. SELECT + WHERE + NOT IN
NOT IN is used to filter records that do not match any value in a specified list.
SELECT * FROM employees WHERE department NOT IN ('HR', 'Finance');
84. SELECT + WHERE + BETWEEN
BETWEEN is used to filter records within a certain range.
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;
85. SELECT + WHERE + NOT BETWEEN
NOT BETWEEN is used to filter records that do not fall within a certain range.
SELECT * FROM employees WHERE salary NOT BETWEEN 50000 AND 100000;
86. SELECT + WHERE + IS NULL
IS NULL is used to filter records that have NULL values.
SELECT * FROM hr WHERE sal IS NULL;
87. SELECT + WHERE + IS NOT NULL
IS NOT NULL is used to filter records that do not have NULL values.
SELECT * FROM hr WHERE sal IS NOT NULL;
88. SELECT + COUNT + WHERE + IS NULL
COUNT with IS NULL is used to count the number of records with NULL values.
SELECT COUNT(*) FROM hr WHERE sal IS NULL;
89. Alias
An alias is used to give a table or a column a temporary name.
SELECT first_name AS 'First Name', last_name AS 'Last Name' FROM employees;
90. SELECT + CONCAT + AS
CONCAT is used to combine multiple strings, and AS is used to assign an alias to the result.
SELECT CONCAT(first_name, ' ', last_name) AS 'Full Name' FROM hr_data;
91. LIMIT + OFFSET
LIMIT is used to specify the number of records to return, while OFFSET is used to skip a specified number of records.
SELECT * FROM employees LIMIT 10 OFFSET 20;
92. MIN + Alias
MIN is used to find the smallest value in a column, and Alias is used to rename the result.
SELECT MIN(salary) AS 'Lowest Salary' FROM employees;
93. MAX + Alias
MAX is used to find the largest value in a column, and Alias is used to rename the result.
SELECT MAX(salary) AS 'Highest Salary' FROM employees;
94. SUM + Alias
SUM is used to calculate the total of a numeric column, and Alias is used to rename the result.
SELECT SUM(salary) AS 'Total Salary' FROM employees;
95. AVG + Alias
AVG is used to calculate the average value of a numeric column, and Alias is used to rename the result.
SELECT AVG(salary) AS 'Average Salary' FROM employees;
96. ORDER BY
ORDER BY is used to sort the result set of a query by one or more columns.
SELECT * FROM employees ORDER BY salary DESC;
97. JOIN + GROUP BY
JOIN is used to combine rows from two or more tables based on a related column, and GROUP BY is used to group rows that have the same values in specified columns into summary rows.
SELECT department, COUNT(*) AS 'Number of Employees'
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY department;
98. LIKE
LIKE is used to search for a specified pattern in a column.
SELECT * FROM employees WHERE first_name LIKE 'J%';
99. NOT LIKE
NOT LIKE is used to exclude records that match a specified pattern in a column.
SELECT * FROM employees WHERE first_name NOT LIKE 'J%';
100. Wildcards
Wildcards are used with the LIKE operator to search for patterns. Common wildcards include % (matches any sequence of characters) and _ (matches any single character).
SELECT * FROM employees WHERE first_name LIKE 'J_n%';
101. ALTER Command Used for Rename Column
ALTER TABLE can be used to rename a column in a table.
ALTER TABLE employees RENAME COLUMN old_column TO new_column;
102. ALTER Command Used for Rename Table
ALTER TABLE can be used to rename an existing table.
ALTER TABLE hr RENAME TO hr1;
103. Backup Database
Backup commands vary by database system, but they are generally used to create a copy of the database for recovery purposes.
-- Example for MySQL
mysqldump -u username -p database_name > backup.sql;
104. Restore Database
Restore commands are used to restore a database from a backup.
-- Example for MySQL
mysql -u username -p database_name < backup.sql;
105. Primary Key Add in Create Table
PRIMARY KEY is used to uniquely identify each record in a table.
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
106. Foreign Key Add in Create Table
FOREIGN KEY is used to link records in one table with records in another table.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
107. Drop Primary Key
ALTER TABLE can be used to drop the primary key constraint from a table.
ALTER TABLE hr DROP PRIMARY KEY;
108. Drop Foreign Key
ALTER TABLE can be used to drop a foreign key constraint from a table.
ALTER TABLE hr DROP FOREIGN KEY sal;
109. Drop Index Constraint
ALTER TABLE can be used to drop an index constraint from a table.
ALTER TABLE hr DROP INDEX sal;
110. Drop Check Constraint
ALTER TABLE can be used to drop a check constraint from a table.
ALTER TABLE hr DROP CHECK sal;
111. Comments
Comments can be added to SQL code to explain or document it. They are not executed as part of the query.
-- This is a single-line comment
/* This is a
multi-line comment */
112. Index Create
INDEX is used to retrieve data quickly by creating an index on one or more columns.
CREATE INDEX idx_salary ON employees(salary);
113. Index Drop
To remove an index from a table, you can use the DROP INDEX command. This is useful for optimizing performance or modifying the database schema.
ALTER TABLE employees DROP INDEX idx_salary;
114. Join and Types
Joins are used to combine rows from two or more tables based on a related column. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and SELF JOIN.
-- Example of INNER JOIN
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
115. Inner Join
INNER JOIN returns only the rows where there is a match in both tables being joined.
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
116. Right Join
RIGHT JOIN returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
117. Full Outer Join
FULL OUTER JOIN returns all records when there is a match in either left or right table. Records that do not match will have NULL values for columns from the table without a match.
SELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.id;
118. Left Join
LEFT JOIN returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
119. Union
UNION is used to combine the results of two or more SELECT statements. It returns only distinct values by default.
SELECT name FROM employees
UNION
SELECT name FROM managers;
120. Union All
UNION ALL combines the results of two or more SELECT statements, including all duplicate values.
SELECT name FROM employees
UNION ALL
SELECT name FROM managers;
121. Self Join
SELF JOIN is used to join a table with itself. It is useful for comparing rows within the same table.
SELECT a.name AS Employee, b.name AS Manager
FROM employees a
INNER JOIN employees b ON a.manager_id = b.id;
122. Subquery
A subquery is a query nested inside another query. It is used to perform operations that require multiple steps or comparisons.
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
123. CASE Expression
The CASE expression is used to create conditional logic in SQL queries. It allows you to return different values based on certain conditions.
SELECT name,
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary BETWEEN 30000 AND 70000 THEN 'Medium'
ELSE 'High'
END AS SalaryRange
FROM employees;
125. Aggregate Function in Windows
Aggregate functions in window functions are used to perform calculations across a set of table rows that are somehow related to the current row.
SELECT name, salary,
SUM(salary) OVER (PARTITION BY department_id) AS DepartmentTotal
FROM employees;
126. Ranking Function in Windows
Ranking functions in window functions assign a rank to each row within the partition of a result set. Examples include ROW_NUMBER(), RANK(), and DENSE_RANK().
SELECT name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS RowNum
FROM employees;
127. Values/Analytics Function in Windows
Values and analytics functions in window functions provide advanced analytical capabilities, such as calculating moving averages, running totals, and more.
SELECT name, salary,
LAG(salary, 1) OVER (ORDER BY hire_date) AS PreviousSalary
FROM employees;
128. SQL vs No-SQL
SQL (Structured Query Language) and No-SQL (Not Only SQL) databases serve different purposes. SQL databases use structured data and relational models, while No-SQL databases handle unstructured data and offer flexibility for various data models.
-- SQL Example
SELECT * FROM employees;
-- No-SQL Example (using MongoDB)
db.employees.find({});
Conclusion
SQLs is the most important and widely used language to query a database in relational databases. This guide explained, Key terms and differences between SQL and NoSQL databases as well as some practical advice to start running SQL queries on real examples. Understanding these concepts will make you better in managing things related to the database and perform well on interviews.
