Cool SQL Commands: A Quick Reference
When working with MySQL, I often find myself searching for solutions to recurring challenges. To save time and help others, here’s a quick reference for some of the most useful SQL commands I’ve discovered:
1. Creating a Unique Combination of Entries in a Table
Use this command to ensure that a combination of columns has unique values in your table:
ALTER TABLE table_name ADD UNIQUE `unique_index`(`column_1`, `column_2`);
This is helpful when you want to avoid duplicate entries based on a combination of columns.
2. Adding a Calculated Column
You can create a new column that dynamically calculates its value based on other columns:
ALTER TABLE table_name ADD column_name VARCHAR(20) AS (column_1 + column_2 + column_3);
Replace column_1 + column_2 + column_3 with your desired expression. This can include concatenation, arithmetic operations, or other MySQL functions.
3. Inserting into a Table with Multiple Columns
When inserting specific values into multiple columns, use this:
INSERT INTO table_name (column_1, column_2) VALUES (“value_1”, “value_2”);
This ensures you specify exactly which columns to populate, preventing errors when your table has additional columns.
4. Finding Duplicate Records in a Table
Quickly identify duplicate rows based on a specific column:
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
5. Updating Multiple Rows with Conditional Values
Update specific rows based on a condition:
UPDATE table_name
SET column_1 = “new_value”
WHERE column_2 = “some_condition”;
6. Deleting Duplicate Records While Keeping One
Remove duplicates but retain one instance of each:
DELETE t1
FROM table_name t1
JOIN table_name t2
ON t1.column_name = t2.column_name
WHERE t1.id > t2.id;
7. Adding a New Column with a Default Value
Add a new column to a table and set a default value for all existing and future rows:
ALTER TABLE table_name ADD column_name INT DEFAULT 0;
8. Renaming a Column
Rename an existing column in your table:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
9. Joining Two Tables and Displaying Data
Perform an inner join to display matching rows from two tables:
SELECT t1.column_name, t2.column_name
FROM table_1 t1
JOIN table_2 t2
ON t1.common_column = t2.common_column;
10. Exporting a Table to a CSV File
Export a table’s data to a CSV file directly from MySQL:
SELECT *
FROM table_name
INTO OUTFILE ‘/path/to/file.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘n’;
11. Counting the Number of Rows in a Table
Get the total number of rows in a table:
SELECT COUNT(*)
FROM table_name;
12. Checking for Null Values
Find rows where a column is NULL:
SELECT *
FROM table_name
WHERE column_name IS NULL;
Final Thoughts
These commands have saved me countless hours of frustration. Bookmark this page and use it as a quick reference for your SQL adventures. Got any cool SQL tricks? Share them in the comments!
