MySql Chapter-1

🧠 1. What is DBMS (Database Management System)?

A DBMS is software that interacts with end-users, applications, and the database itself to capture and analyze data.

🔹 Key Functions:

Data storage, retrieval, and update
User management and authentication
Transaction management (ACID properties)
Backup and recovery
Data abstraction and security

🔹 Examples:

Relational DBMS: MySQL, PostgreSQL, Oracle
NoSQL DBMS: MongoDB, Redis, Cassandra

🆚 2. SQL vs NoSQL Databases

Feature	SQL (Relational)	NoSQL (Non-Relational)
Data Format	Tables with rows and columns	Document, Key-Value, Graph, Column
Schema	Fixed schema (structured)	Dynamic schema (flexible)
Scalability	Vertical (add resources to one server)	Horizontal (add more servers)
Examples	MySQL, PostgreSQL, Oracle	MongoDB, Firebase, Cassandra
Query Language	SQL	No fixed language, uses JSON-style queries
Best Use Case	Banking, ERP, structured data	Real-time apps, IoT, big data

🔹 Example:

SQL:

SELECT * FROM users WHERE age > 30;

NoSQL (MongoDB):

db.users.find({ age: { $gt: 30 } });

🐬 3. Introduction to MySQL

MySQL is a popular open-source relational database based on SQL. It is widely used for:

Web applications (e.g., WordPress)
Data warehousing
E-commerce and analytics

🔹 MySQL Key Features:

Supports transactions
Foreign key constraints
High performance and reliability
Open-source with enterprise support

🔹 Create a Database

CREATE DATABASE mydb;

🔹 Use a Database

USE mydb;

🔹 Show Databases

SHOW DATABASES;

🔹 Drop a Database

DROP DATABASE mydb;

🔹 Create Table

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🔹 Alter Table

Add a Column:

ALTER TABLE users ADD gender VARCHAR(10);

Modify Column:

ALTER TABLE users MODIFY age SMALLINT;

Rename Column:

ALTER TABLE users CHANGE name full_name VARCHAR(100);

Drop Column:

ALTER TABLE users DROP COLUMN gender;

Rename Table:

RENAME TABLE users TO customers;

🔹 Drop Table

DROP TABLE users;

🔹 Describe Table Structure

DESCRIBE users;

🔍 6. SELECT (Basic Query)

🔹 Select All Rows

SELECT * FROM users;

🔹 Select Specific Columns

SELECT name, age FROM users;

🔹 Where Condition

SELECT * FROM users WHERE age > 25;

🔹 Order By

SELECT * FROM users ORDER BY age DESC;

🔹 Limit Results

SELECT * FROM users LIMIT 5;

🔹 Aliases

SELECT name AS full_name FROM users;

✍️ 7. INSERT Query (Basic)

🔹 Insert One Record

INSERT INTO users (name, email, age) 
VALUES ('Suraj Kumar', 'suraj@example.com', 24);

🔹 Insert Multiple Records

INSERT INTO users (name, email, age) VALUES
('Karan', 'karan@example.com', 28),
('Asha', 'asha@example.com', 32);

🔧 Bonus: UPDATE and DELETE

🔹 Update Records

UPDATE users SET age = 25 WHERE name = 'Suraj Kumar';

🔹 Delete Records

DELETE FROM users WHERE age < 20;