MySQL Basics

What is a Database? 

A database is a container that stores related data in an organized way. In MySQL, a database holds one or more tables. Think of it like: 

Folder analogy :  A database is like a folder. Each table is a file inside that folder. The rows in the table are like the content inside each file. 

Excel analogy : A database is like an Excel workbook. Each table is a separate sheet inside that workbook. Each row in the table is like a row in Excel.

----------------------------------------------------------------------------------------------------------------------

Data Types in SQL :

1. INT : Stores integer type, used for whole numbers

2. VARCHAR(100) : Stores variable length string, upto 100 characters.

3. Date : Stores date values

4. BOOLEAN : Stores TRUE or FALSE

5. ENUM : A string object with a value chosen from a list of permitted values. eg. gender                   ENUM('Male', 'Female', 'Other')

Contraints :

1. AUTO_INCREMENT Automatically generates a unique number for each row. 

2.PRIMARY KEY : Uniquely identifies each row in the table. 

3. NOT NULL : Ensures a column cannot have a NULL value. 

4. UNIQUE : Ensures all values in a column are different. 

5. DEFAULT : Sets a default value for a column if no value is provided. 

----------------------------------------------------------------------------------------------------------------------

1. DDL (Data Definition Language) commands are used to define and manage the structure of database objects like tables, schemas, and indexes.

1.  CREATE A DATABASE

CREATE DATABASE College;

 CREATE A TABLE

CREATE TABLE users 

                ( id INT AUTO_INCREMENT PRIMARY KEY,

                   name VARCHAR(100) NOT NULL, 

                   email VARCHAR(100) UNIQUE NOT NULL, 

                   gender ENUM('Male', 'Female', 'Other'), 

                   date_of_birth DATE);

2. DROP THE DATABASE

DROP DATABASE College;

3. TRUNCATE

TRUNCATE TABLE Students;

It is used to remove all records from table and keep the skeleton of the table

4. RENAME TABLE 

RENAME TABLE Students TO candidates;

This rename an existing table

5. ALTER TABLE

    You can modify an existing table using ALTER TABLE

    Add a Column

        ALTER TABLE candidates ADD_COLUMN is_active BOOLEAN DEFAULT TRUE;

    Drop a Column

        ALTER TABLE candidates DROP COLUMN is_active;

    Modify a Column Type

        ALTER TABLE candidates MODIFY COLUMN name VARCHAR(150);

Command	Purpose

CREATE

==>          Create objects

ALTER

==>          Modify structure

DROP           ==>

Delete objects

TRUNCATE

==>          Remove all data

RENAME

==>          Rename objects

----------------------------------------------------------------------------------------------------------------------

2. DML (Data Manipulation Language)

Used to work with data inside tables

Main Commands:

• INSERT → Add new data
• UPDATE → Modify existing data
• DELETE → Remove data

INSERT INTO students (id, name) VALUES (1, 'Rahul');

UPDATE students SET name = 'Amit' WHERE id = 1;

DELETE FROM students WHERE id = 1;

----------------------------------------------------------------------------------------------------------------------

3. DQL (Data Query Language)

Selecting Data from Tables:

Select All Columns

SELECT * FROM Students;

This fetches every columns and every row from the students table.

SELECT name, email FROM Students;

This only fetches name and email columns from all rows.

----------------------------------------------------------------------------------------------------------------------

 4. DCL (Data Control Language)

Used to control access/permissions

Main Commands:

• GRANT → Give permission
• REVOKE → Remove permission

GRANT SELECT ON students TO user1;
REVOKE SELECT ON students FROM user1;

----------------------------------------------------------------------------------------------------------------------

5. TCL (Transaction Control Language)

Used to manage transactions

Main Commands:

• COMMIT → Save changes
• ROLLBACK → Undo changes
• SAVEPOINT → Set a checkpoint

COMMIT;
ROLLBACK;
SAVEPOINT sp1;

----------------------------------------------------------------------------------------------------------------------