MySQL Basics

MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL). It is commonly used for web applications and is a central component of the LAMP stack (Linux, Apache, MySQL, PHP/Perl/Python).

Key Concepts

• Database: A collection of related data stored in tables.
• Table: A collection of related data entries consisting of rows and columns.
• Row: A single data item in a table.
• Column: A set of data values of a particular type, one for each row in the table.
• Primary Key: A unique identifier for a row in a table.
• Foreign Key: A field (or collection of fields) in one table that uniquely identifies a row of another table.

Common SQL Statements

1. Creating a Database

CREATE DATABASE database_name;

2. Selecting a Database

USE database_name;

3. Creating a Table

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY, 
    column2 datatype, 
    column3 datatype
);

4. Inserting Data into a Table

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

5. Querying Data

SELECT column1, column2
FROM table_name
WHERE condition;

6. Updating Data

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

7. Deleting Data

DELETE FROM table_name
WHERE condition;

Example Workflow

Create a Database

CREATE DATABASE school;

Select the Database

USE school;

Create a Table

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

Insert Data into the Table

INSERT INTO students (name, age, grade)
VALUES ('John Doe', 15, '10th'),
       ('Jane Smith', 14, '9th');

Query the Table

SELECT * FROM students;

Update Data

UPDATE students
SET grade = '11th'
WHERE name = 'John Doe';

Delete Data

DELETE FROM students
WHERE name = 'Jane Smith';

Data Types

Common data types in MySQL include:

• INT: Integer numbers.
• VARCHAR(size): Variable-length string.
• TEXT: Large text.
• DATE: Date values (YYYY-MM-DD).
• FLOAT: Floating point numbers.

Constraints

Constraints are used to specify rules for the data in a table:

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are unique.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table.
• FOREIGN KEY: Prevents actions that would destroy links between tables.
• CHECK: Ensures that the values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column if no value is specified.