1️⃣ SHOW

  • Description: Displays databases, tables, or table structure.

  • Syntax:

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM table_name;

  • Example:

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM students;

  • Output (sample):

+----------------+
| Database       |
+----------------+
| information_schema |
| school_db      |
| test           |
+----------------+

  • Summary: Useful to explore existing databases and table structure.




2️⃣ USE

  • Description: Selects a database to work with.

  • Syntax:

USE database_name;

  • Example:

USE school_db;

  • Output (sample):

Database changed

  • Summary: Sets the default database for all subsequent operations.

3️⃣ CREATE

  • Description: Creates databases or tables.

  • Syntax:

CREATE DATABASE database_name;
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

  • Example:

CREATE DATABASE school_db;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    grade VARCHAR(10)
);

  • Output (sample):

Query OK, 1 row affected

  • Summary: Essential for defining database structure before inserting data.

4️⃣ DESCRIBE

  • Description: Shows table structure (columns, types, keys).

  • Syntax:

DESCRIBE table_name;

  • Example:

DESCRIBE students;

  • Output (sample):

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| grade | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

  • Summary: Quick way to inspect table columns and constraints.

5️⃣ SELECT

  • Description: Retrieves data from tables.

  • Syntax:

SELECT column1, column2 FROM table_name WHERE condition ORDER BY column LIMIT number;

  • Example:

SELECT * FROM students;
SELECT name, age FROM students WHERE age > 18 ORDER BY name ASC;

  • Output (sample):

+----+---------+-----+-------+
| id | name    | age | grade |
+----+---------+-----+-------+
| 1  | Sharmila| 20  | BCA   |
| 2  | Amit    | 18  | BSc   |
+----+---------+-----+-------+

  • Summary: Most used command for querying and filtering data.

6️⃣ INSERT

  • Description: Adds new records to a table.

  • Syntax:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

  • Example:

INSERT INTO students (name, age, grade) VALUES ('Priya', 19, 'BCom');

  • Output (sample):

Query OK, 1 row affected

  • Summary: Use for adding new data to your tables.

7️⃣ UPDATE

  • Description: Modifies existing records in a table.

  • Syntax:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

  • Example:

UPDATE students SET age = 21 WHERE name = 'Sharmila';

  • Output (sample):

Query OK, 1 row affected

  • Summary: Always include WHERE to avoid updating all rows.

8️⃣ DELETE

  • Description: Removes records from a table.

  • Syntax:

DELETE FROM table_name WHERE condition;

  • Example:

DELETE FROM students WHERE name = 'Amit';

  • Output (sample):

Query OK, 1 row affected

  • Summary: Can remove single or all rows; careful without WHERE.

9️⃣ JOIN

  • Description: Combines data from multiple tables.

  • Syntax:

SELECT columns 
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

  • Example:

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(50)
);

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

  • Output (sample):

+---------+-------------+
| name    | course_name |
+---------+-------------+
| Sharmila| Database    |
| Priya   | Networking  |
+---------+-------------+

  • Summary: Use JOINs to relate tables; INNER, LEFT, RIGHT, FULL joins available.

🔟 Loading & Jumping (LIMIT & OFFSET)

  • Description: Load subsets of rows, useful for pagination.

  • Syntax:

SELECT * FROM table_name LIMIT number OFFSET number;
-- OR
SELECT * FROM table_name LIMIT start, count;

  • Example:

SELECT * FROM students LIMIT 5;          -- Load first 5 rows
SELECT * FROM students LIMIT 5 OFFSET 5; -- Skip 5, then load next 5
SELECT * FROM students LIMIT 5,5;        -- Same as above

  • Output (sample):

+----+---------+-----+-------+
| id | name    | age | grade |
+----+---------+-----+-------+
| 6  | Priya   | 19  | BCom  |
| 7  | Ravi    | 20  | BSc   |
+----+---------+-----+-------+

  • Summary: Essential for large datasets and implementing pagination.


Comments

Post a Comment

Popular posts from this blog

Advantages of Waterfall Model The classical waterfall model is an idealistic model for software development. It is very simple, so it can be considered the basis for other software development life cycle models. Below are some of the major advantages of this SDLC model. Easy to Understand: The Classical Waterfall Model is very simple and easy to understand. Individual Processing: Phases in the Classical Waterfall model are processed one at a time. Properly Defined: In the classical waterfall model, each stage in the model is clearly defined. Clear Milestones: The classical Waterfall model has very clear and well-understood milestones. Properly Documented: Processes, actions, and results are very well documented. Reinforces Good Habits: The Classical Waterfall Model reinforces good habits like define-before-design and design-before-code. Working: Classical Waterfall Model works well for smaller projects and projects where requirements are well understood. Disadvantages of Waterfal...
Read more
Image
                                                                The Waterfall Model The Waterfall Model is a Traditional Software Development Methodology. It was first introduced by Winston W. Royce in 1970. It is a linear and sequential approach to software development that consists of several phases. This classical waterfall model is simple and idealistic. It is important because most other Types of Software Development Life Cycle Models are a derivative of this. In this article, we will see the Waterfall Model in detail. What is the SDLC Waterfall Model? The waterfall model is a Software Development Model used in the context of large, complex projects, typically in the field of information technology. It is characterized by a structured, sequential approach to Project Management and Software Development. The Waterfall Model is...
Read more
Image
Features of Waterfall Model: Following are the features of the waterfall model: 1.Sequential Approach : The waterfall model involves a sequential approach to software development, where each phase of the project is completed before moving on to the next one. 2.Document-Driven:   The waterfall model depended on documentation to ensure that the project is well-defined and the project team is working towards a clear set of goals. 3.Quality Control:  The waterfall model places a high emphasis on quality control and testing at each phase of the project, to ensure that the final product meets the requirements and expectations of the stakeholders. 4.Rigorous Planning:  The waterfall model involves a careful planning process, where the project scope, timelines, and deliverables are carefully defined and monitored throughout the project lifecycle.            Overall, the waterfall model is used in situations where there is a need for a highly str...
Read more