One of the most widely used and adaptable database management system platforms is MySQL. Regardless of your position in the database industry or amount of development experience, it is imperative that you are familiar with MySQL and its querying features. The fundamentals of MySQL querying will be covered in this tutorial, along with easy to use techniques for connecting to your databases.
A few simple SQL queries
CHOOSE Information from one or more columns of a table can be obtained using this technique. You enter the desired columns, separated by commas, after the SELECT keyword.
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.