Advanced MySQL Commands with Examples
1. DISTINCT Clause
To remove duplicate rows based on a column, you can use the DISTINCT keyword:
SELECT DISTINCT age FROM students;
2. BETWEEN Operator
To filter rows within a certain range, you can use the BETWEEN operator:
SELECT * FROM students WHERE age BETWEEN 20 AND 25;
3. NOT BETWEEN Operator
If you want to select rows that fall outside of a certain range, use the NOT BETWEEN operator:
SELECT * FROM students WHERE age NOT BETWEEN 20 AND 25;
4. IN Operator
You can use the IN operator to select rows where a column matches any value in a list:
SELECT * FROM students WHERE name IN ('Amit', 'Geeta');
5. NOT IN Operator
If you want to exclude rows based on a list of values, use the NOT IN operator:
SELECT * FROM students WHERE name NOT IN ('Amit', 'Geeta');
6. LIKE Operator and Pattern Matching
The LIKE operator can be used for pattern matching. You can use percentage signs (%) and underscores (_) as wildcards:
To find names that start with ‘A’:
SELECT * FROM students WHERE name LIKE 'A%';
To find names where the second character is ‘m’:
SELECT * FROM students WHERE name LIKE '_m%';
Understanding the ALTER Command in MySQL
1. Adding a Column
To add a new column to a table:
ALTER TABLE students ADD email VARCHAR(50);
2. Modifying a Column
To modify an existing column:
ALTER TABLE students MODIFY email VARCHAR(100);
3. Dropping a Column
To delete an existing column from a table:
ALTER TABLE students DROP COLUMN email;
4. Changing the Data Type of a Column
To change the data type of a column:
ALTER TABLE students CHANGE age age DOUBLE;
5. Adding a Primary Key
To add a primary key:
ALTER TABLE students ADD PRIMARY KEY (id);
6. Adding a Unique Constraint
To add a unique constraint:
ALTER TABLE students ADD UNIQUE (name);
7. Adding an Index
To add an index to a column:
ALTER TABLE students ADD INDEX (name);
More Essential MySQL Commands: UPDATE, DELETE, and DROP
1. UPDATE Command
The UPDATE command modifies existing records in a table.
Update a Single Column
UPDATE students SET age=21 WHERE name='Amit';
Update Multiple Columns
UPDATE students SET age=21, email='amit@example.com' WHERE name='Amit';
Update All Rows
UPDATE students SET age=20;
2. DELETE Command
The DELETE command removes records from a table.
Delete Specific Rows
DELETE FROM students WHERE name='Amit';
Delete All Rows
DELETE FROM students;
3. DROP Command
The DROP command is used to delete objects from the database.
Drop a Table
DROP TABLE students;
Drop a Database
DROP DATABASE studentDB;
Sorting Records with ORDER BY
1. Sorting by a Single Column
To sort records by name in alphabetical order:
SELECT * FROM students ORDER BY name ASC;
Note: The default sort order is ascending (ASC). You can also use DESC for descending order.
2. Sorting by Multiple Columns
You can also sort by more than one column. For example, to sort by name and then by age:
SELECT * FROM students ORDER BY name ASC, age DESC;
This will sort the records alphabetically by name, and in cases where names are identical, it will then sort those records by age in descending order.