Scribd
Upload
4 views2 pages

MySQL Command List With Examples

This document provides a comprehensive list of MySQL commands ranging from basic to advanced, including commands for database creation, table management, data manipulation, and constraints. It also covers advanced topics such as joins, aggregate functions, subqueries, views, indexing, transaction control, and user management. Each command is accompanied by examples to illustrate its usage effectively.

Uploaded by

prashant sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views2 pages

MySQL Command List With Examples

This document provides a comprehensive list of MySQL commands ranging from basic to advanced, including commands for database creation, table management, data manipulation, and constraints. It also covers advanced topics such as joins, aggregate functions, subqueries, views, indexing, transaction control, and user management. Each command is accompanied by examples to illustrate its usage effectively.

Uploaded by

prashant sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MySQL Command List with Examples (Basic to Advanced)

1. Database Commands
Create Database
CREATE DATABASE school;
Show Databases
SHOW DATABASES;
Use Database
USE school;
Drop Database
DROP DATABASE school;

2. Table Commands
Create Table
CREATE TABLE students (id INT, name VARCHAR(50), age INT);
Show Tables
SHOW TABLES;
Describe Table
DESC students;
Drop Table
DROP TABLE students;

3. Data Manipulation Commands


Insert Data
INSERT INTO students VALUES (1, 'Amit', 20);
Select Data
SELECT * FROM students;
Update Data
UPDATE students SET age = 21 WHERE id = 1;
Delete Data
DELETE FROM students WHERE id = 1;

4. Constraints
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary INT CHECK (salary > 0)
);

5. Alter Table
ALTER TABLE students ADD city VARCHAR(30);
ALTER TABLE students MODIFY city VARCHAR(50);
ALTER TABLE students DROP city;

6. Clauses
SELECT * FROM students WHERE age > 18;
SELECT * FROM students ORDER BY name ASC;
SELECT * FROM students LIMIT 5;

7. Aggregate Functions
SELECT COUNT(*) FROM students;
SELECT AVG(age) FROM students;

8. Joins
SELECT * FROM students s INNER JOIN marks m ON [Link] = [Link];

9. Group By & Having


SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 1;

10. Subquery
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

11. Views
CREATE VIEW student_view AS SELECT name, age FROM students;
DROP VIEW student_view;

12. Index
CREATE INDEX idx_name ON students(name);
DROP INDEX idx_name ON students;

13. Transaction Control


SAVEPOINT sp1;
ROLLBACK TO sp1;
COMMIT;

14. User Management


CREATE USER 'admin'@'localhost' IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON school.* TO 'admin'@'localhost';
REVOKE ALL PRIVILEGES ON school.* FROM 'admin'@'localhost';

You might also like