MySQL Tutorial Day 3
Introduction:
MySQL is a widely used open-source relational database system that enables developers to store, retrieve, and manage data efficiently. Whether you're developing a small application or a large-scale system, understanding the basics of MySQL syntax is crucial. In this tutorial, we will walk through key MySQL concepts such as creating databases and tables, understanding data types, using primary and foreign keys, and modifying or deleting tables.
Topics Covered:
1. CREATE DATABASE, USE, and DROP DATABASE
2. CREATE TABLE syntax
3. MySQL Data Types (INT, VARCHAR, DATE, etc.)
4. Primary Keys and Foreign Keys
5. ALTER TABLE (ADD, MODIFY, DROP columns)
6. DROP TABLE and RENAME TABLE
1. CREATE DATABASE, USE, and DROP DATABASE
CREATE DATABASE is used to create a new database.
Syntax:
CREATE DATABASE database_name;
Example:
CREATE DATABASE school;
USE lets you select a specific database to work with.
Example:
USE school;
DROP DATABASE deletes the entire database and all its tables permanently.
Syntax:
DROP DATABASE database_name;
Example:
DROP DATABASE school;
2. CREATE TABLE Syntax
After selecting a database using USE, you can create a table with defined columns and data types.
Syntax:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
Example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
birth_date DATE,
grade INT
);
3. Data Types in MySQL (INT, VARCHAR, DATE, etc.)
MySQL supports a variety of data types. Here are some commonly used ones:
- INT: Stores whole numbers (positive/negative).
- VARCHAR(n): Variable-length character string with a maximum of n characters.
- DATE: Stores date values (YYYY-MM-DD format).
- DECIMAL(m, d): Fixed-point number with m digits and d digits after the decimal point.
- BOOLEAN / TINYINT(1): Stores true/false as 1/0.
- TEXT: Stores large amounts of character data (longer than VARCHAR).
Example Table Using Various Data Types:
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10, 2),
created_at DATE,
available BOOLEAN
);
4. Primary Keys and Foreign Keys
Primary Key: Uniquely identifies each record in a table.
Foreign Key: A field that refers to the primary key in another table to enforce referential integrity.
Example with Primary and Foreign Key:
-- Create a parent table
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);
-- Create a child table with a foreign key
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
This setup ensures that each employee belongs to a valid department.
5. ALTER TABLE (ADD, MODIFY, DROP column)
The ALTER TABLE command lets you change an existing table's structure.
Add a new column:
ALTER TABLE students ADD email VARCHAR(100);
Modify a column (e.g., increase VARCHAR length):
ALTER TABLE students MODIFY name VARCHAR(150);
Drop a column:
ALTER TABLE students DROP COLUMN grade;
6. DROP TABLE and RENAME TABLE
DROP TABLE deletes a table and all of its data permanently.
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE employees;
RENAME TABLE changes the name of a table.
Syntax:
RENAME TABLE old_name TO new_name;
Example:
RENAME TABLE products TO store_items;
Conclusion:
This tutorial introduced some of the most fundamental operations in MySQL, from creating and deleting databases to working with tables and keys. With the basics of data types, table relationships, and table modification under your belt, you're ready to build more advanced queries and relationships between datasets. Practicing these commands regularly will help reinforce your understanding and build confidence in using MySQL for real-world projects.
0 Comments