SQL
QUERY SELECT
1️⃣ Basic SELECT Query
SELECT * FROM table_name;
🔹 Retrieves all columns from the table.
2️⃣ Selecting Specific Columns
SELECT column1, column2 FROM table_name;
🔹 Retrieves only the specified columns.
3️⃣ Using WHERE Clause (Filter Data)
SELECT * FROM table_name WHERE column_name = 'value';
🔹 Fetch records where column_name matches 'value'.
4️⃣ Sorting Results (ORDER BY)
SELECT * FROM table_name ORDER BY column_name ASC; -- Ascending Order
SELECT * FROM table_name ORDER BY column_name DESC; -- Descending Order
🔹 Sorts data in ascending or descending order.
5️⃣ Filtering with Multiple Conditions (AND/OR)
SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';
SELECT * FROM table_name WHERE column1 = 'value1' OR column2 = 'value2';
🔹 Uses AND (both conditions must be true) or OR (either condition can be true).
6️⃣ Using LIKE (Pattern Matching)
SELECT * FROM table_name WHERE column_name LIKE 'A%'; -- Starts with 'A'
SELECT * FROM table_name WHERE column_name LIKE '%B'; -- Ends with 'B'
SELECT * FROM table_name WHERE column_name LIKE '%XYZ%'; -- Contains 'XYZ'
🔹 Searches for patterns in text columns.
7️⃣ Limiting Results (LIMIT)
SELECT * FROM table_name LIMIT 5;
🔹 Fetches only the first 5 records.
8️⃣ Count Total Rows
SELECT COUNT(*) FROM table_name;
🔹 Returns the total number of rows in the table.
9️⃣ Using GROUP BY (Aggregate Data)
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
🔹 Groups results by column_name and counts occurrences.
🔟 Joining Two Tables (INNER JOIN)
SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.common_column = t2.common_column;
🔹 Combines data from two tables based on a common column.
QUERY INSERT
1️⃣ Basic INSERT Query (Single Row)
INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 'value3');
🔹 Inserts a single row into the table.
2️⃣ Insert Multiple Rows
INSERT INTO table_name (column1, column2, column3)
VALUES
('value1', 'value2', 'value3'),
('value4', 'value5', 'value6'),
('value7', 'value8', 'value9');
🔹 Inserts multiple rows in one query.
3️⃣ Insert Without Specifying Column Names (Not Recommended)
INSERT INTO table_name
VALUES ('value1', 'value2', 'value3');
🔹 This works only if you provide values for all columns in the correct order.
4️⃣ Insert Data from Another Table
INSERT INTO new_table (column1, column2)
SELECT column1, column2 FROM old_table WHERE condition;
🔹 Copies specific columns from
old_table into new_table.5️⃣ Insert with Default Values
INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', DEFAULT, 'value3');
🔹 Uses DEFAULT for columns where no value is provided.
6️⃣ Insert with Auto-Increment Primary Key
INSERT INTO table_name (column2, column3)
VALUES ('value2', 'value3');
🔹 If column1 is an
AUTO_INCREMENT primary key, it will automatically get the next number.7️⃣ Insert Data with a Subquery
INSERT INTO employees (name, salary, department_id)
SELECT 'John Doe', 50000, id FROM departments WHERE name = 'Sales';
🔹 Fetches
department_id dynamically while inserting a new employee.8️⃣ Insert with CURRENT_TIMESTAMP
INSERT INTO logs (event_name, created_at)
VALUES ('User Login', CURRENT_TIMESTAMP);
🔹 Saves the current date and time during insertion.
QUERY INSERT
1️⃣ Basic UPDATE Query (Single Row)
UPDATE table_name
SET column1 = 'new_value1', column2 = 'new_value2'
WHERE condition;
🔹 Updates specific columns in a row based on a condition.
2️⃣ Update Multiple Columns
UPDATE table_name
SET column1 = 'new_value1', column2 = 'new_value2', column3 = 'new_value3'
WHERE condition;
🔹 Updates multiple columns at once.
3️⃣ Update Multiple Rows (Without Unique Condition)
UPDATE table_name
SET column_name = 'new_value' WHERE column_name IN ('value1', 'value2');
🔹 Updates multiple records that match the given values.
4️⃣ Update Without WHERE (Updates All Rows) ❗⚠ (Be Careful!)
UPDATE table_name SET column_name = 'new_value';
🔹 Updates all rows in the table.
✅ Example: Set all properties to "Under Review"
⚠ Warning: This will change every row in the table.
5️⃣ Update Using Another Table’s Data
UPDATE table1 t1
JOIN table2 t2 ON t1.common_column = t2.common_column
SET t1.column1 = t2.column1_value
WHERE condition;
🔹 Updates records based on another table’s data.
6️⃣ Update with Arithmetic Operations
UPDATE table_name
SET column_name = column_name + 100
WHERE condition;
🔹 Increments/decrements values dynamically.
7️⃣ Update with Current Date/Time
UPDATE table_name
SET column_name = CURRENT_TIMESTAMP
WHERE condition;
🔹 Stores the current date and time when updating.
Comments
Post a Comment