Introduction
MySQL is one of the most popular and flexible database management system platforms available. Knowing MySQL and its querying features is important, no matter your experience level as a developer or where you are in the database industry. This article will explore the foundations of MySQL querying, covering simple to complex methods that allow you to connect with your databases easily.
Some basic SQL queries
SELECT This method is used to get information from a table's one or more columns. Following the SELECT keyword, you provide the columns you wish to get, separated by commas.
SELECT column1, column2 FROM tablename;
SELECT * FROM tablename;
WHERE Records can be filtered using this clause depending on specific requirements. You can only obtain the rows that match the given criteria.
SELECT *FROM tablename
WHERE columnname = value;
INSERT To add new records (rows) to a table, use this statement. The values you wish to add to each column and the name of the table are also specified.
INSERT INTO tablename (column1, column2)
VALUES (value1, value2);
UPDATE A table's existing records can be changed using this statement. The table name, the columns you wish to change, and the new values are all specified.
UPDATE tablename
SET column1 = newvalue
WHERE condition;
DELETE Depending on specific requirements, this statement is used to remove records from a table. You can use it to remove particular rows that fit the requirements.
DELETE FROM tablename
WHERE condition;
JOIN This joins rows from one or more tables together according to a shared column. With just one query, you can obtain data from several tables.
SELECT *
FROM table1
JOIN table2 ON table1.columnname = table2.columnname;
GROUP BY Rows with the same values are grouped together into summary rows using this clause. It is frequently used to calculate values on grouped data together with aggregate functions such as COUNT, SUM, AVG, etc.
SELECT columnname, COUNT(*)
FROM tablename
GROUP BY columnname;
ORDER BY Depending on one or more columns, this clause can be used to sort the result set either ascending (ASC) or descending (DESC).
SELECT *
FROM tablename
ORDER BY columnname ASC;
LIMIT This has the function of limiting how many rows are returned in a result set. It is frequently used to increase query efficiency by obtaining only a subset of rows, or for pagination.
SELECT *
FROM tablename
LIMIT 10;
DISTINCT This term pulls distinct rows out of a column. Duplicate rows are removed from the result set.
SELECT DISTINCT columnname
FROM tablename;
Data Manipulation Functions
COUNT This function counts the number of rows that satisfy a certain criteria or the total number of rows in a table.
SELECT COUNT(*)
FROM tablename;
MAX/MIN The maximum and minimum values from a column can be obtained, respectively, using these functions.
SELECT MAX(columnname), MIN(columnname)
FROM tablename;
AVG The average value of a numeric column can be determined with this function.
SELECT AVG(columnname)
FROM tablename;
SUM The total of the values in a numeric column can be determined using this function.
SELECT SUM(columnname)
FROM tablename;
Constraints
PRIMARY KEY The primary key constraint is responsible for uniquely identifying every record within a table. It guarantees that every table row has a distinct identity.
CREATE TABLE tablename (
columnname INT PRIMARY KEY,
...
);
FOREIGN KEY By referencing the primary key or unique key of another table, this constraint creates a relationship between tables.
CREATE TABLE tablename1 (
columnname INT,
FOREIGN KEY (columnname) REFERENCES table_name2(columnname)
);
INDEX By based an index on one or more columns, this technique helps to speed up data retrieval operations on a table. Based on the indexed columns, it enables the database engine to find rows rapidly.
CREATE INDEX indexname
ON tablename (columnname);
Conclusion
The SQL cheat sheet offers a quick reference for effectively carrying out crucial database practices. It includes a wide range of operations, from simple commands like SELECT, INSERT, UPDATE, and DELETE for data retrieval, addition, and modification to more complex techniques like JOIN for merging data from various tables and GROUP BY for data summary. The cheat sheet also contains constraints like PRIMARY KEY, FOREIGN KEY, and INDEX, as well as data manipulation methods like COUNT, MAX, MIN, AVG, and SUM, which are essential for maintaining the accuracy of data and maximising database performance.
FAQs
Q 1. What is the difference between SELECT and WHERE clauses?
Ans. SELECT- It specifies the columns you want to retrieve data from.
WHERE- It filters rows based on specified conditions, allowing you to retrieve only the rows that meet the criteria.
Q 2. How do I add new records to a table using INSERT statement?
Ans. Use the INSERT statement followed by the table name and the values you want to insert into each column.
Q 3. Can you explain how the UPDATE statement works?
Ans.The UPDATE statement modifies existing records in a table. You specify the table name, the columns you want to update, and the new values for those columns, along with optional conditions to filter the rows to be updated.
Q 4. When should I use the DELETE statement?
Ans.The DELETE statement is used to remove records from a table. It's typically used when you need to remove specific rows that meet certain criteria.
Q 5. What is the purpose of the JOIN clause?
Ans.JOIN is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables with a single query.
Q 6. How does the GROUP BY clause work?
Ans. GROUP BY is used to group rows with the same values into summary rows. It's often used in combination with aggregate functions like COUNT, SUM, AVG, etc., to perform calculations on grouped data.
Q 7. What does the LIMIT clause do?
Ans. LIMIT is used to restrict the number of rows returned by a query. It's commonly used for pagination or to improve query performance by retrieving only a subset of rows.
Q 8. When should I use the DISTINCT keyword?
Ans.DISTINCT is used to retrieve unique values from a column. It removes duplicate rows from the result set, returning only distinct values.
Q 9. What is the PRIMARY KEY constraint used for?
Ans.The PRIMARY KEY constraint ensures that each record in a table has a unique identifier. It uniquely identifies each row in the table and prevents duplicate or null values.
Q 10. How does the INDEX constraint improve performance?
Ans. INDEX creates an index on one or more columns, which helps speed up data retrieval operations on a table. It allows the database engine to quickly locate rows based on the indexed columns, improving query performance.