Certainly! Here's a list of MySQL commands from basic to advanced concepts for managing and querying databases:
1. **CREATE DATABASE**: Creates a new database.
```sql
CREATE DATABASE database_name;
```
2. **USE**: Selects a database to work with.
```sql
USE database_name;
```
3. **CREATE TABLE**: Creates a new table with specified columns and data types.
```sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
```
4. **INSERT INTO**: Adds new rows to a table.
```sql
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
```
5. **SELECT**: Retrieves data from one or more database tables.
```sql
SELECT column1, column2 FROM table_name;
```
6. **WHERE**: Filters data based on a specified condition.
```sql
SELECT column1, column2 FROM table_name WHERE column1 = 'value';
```
7. **UPDATE**: Modifies existing rows in a table based on a condition.
```sql
UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'condition';
```
8. **DELETE**: Removes rows from a table based on a condition.
```sql
DELETE FROM table_name WHERE column1 = 'value';
```
9. **ALTER TABLE**: Modifies an existing table (adding columns, changing data types, etc.).
```sql
ALTER TABLE table_name ADD column_name datatype;
```
10. **DROP TABLE**: Deletes an existing table.
```sql
DROP TABLE table_name;
```
11. **CREATE INDEX**: Creates an index on a table column to improve query performance.
```sql
CREATE INDEX idx_column ON table_name (column);
```
12. **JOIN**: Combines rows from two or more tables based on a related column between them.
```sql
SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column = table2.column;
```
13. **UNION**: Merges the results of two or more SELECT queries into a single result set.
```sql
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
```
14. **GROUP BY**: Groups rows based on specified columns, often used with aggregate functions.
```sql
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;
```
15. **HAVING**: Filters the results of a GROUP BY based on specified conditions.
```sql
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > 10;
```
16. **SUBQUERIES**: A query nested inside another query, used for complex filtering or data retrieval.
```sql
SELECT column1 FROM table1 WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
```
17. **Stored Procedures**: Precompiled and stored MySQL code that can be executed multiple times.
```sql
DELIMITER //
CREATE PROCEDURE sp_name()
BEGIN
-- SQL statements here
END //
DELIMITER ;
```
18. **Transactions**: Groups multiple commands into a single unit of work, ensuring all or none of them are executed.
```sql
START TRANSACTION;
-- SQL statements here
COMMIT;
```
19. **Views**: Virtual tables created from SQL SELECT queries that can be treated as regular tables.
```sql
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
```
20. **Window Functions**: Calculate values based on a specified window (subset) of rows within a result set.
```sql
SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS running_total FROM table_name;
```
These are some essential MySQL commands that cover a range of functionalities from basic database management to more advanced querying and optimization techniques. Remember to practice using these commands to become proficient in MySQL.
0 Comments