Lab+-+MySQL+-+phpMyAdmin
MySQL Database Overview
MySQL Database: A popular open-source relational database management system used for various applications.
phpMyAdmin: A web-based tool for managing MySQL databases, allowing users to interact with the database through a graphical user interface.
Accessing MySQL Database via csLamp Server
Steps to Access
Navigate to:
cslamp.adds.brad.ac.uk/dbuser/Enter your username and password (DB password created during registration with csLamp).
Database Structure
You have only one database corresponding to your username.
You can create multiple tables within your single database.
Accessing MySQL Database via Xampp Server
Starting the Server
Open XAMPP Control Panel.
Start both Apache Server and MySQL Database Server.
Web Access
In your web browser, type the following:
localhostfor default port 80.localhost:your-port-numberfor a custom port.
Select the phpMyAdmin tab.
Database Structure
This server allows multiple databases storage with the ability to create multiple tables per database.
SQL Commands: More than just SELECT
Common SQL Operations
CREATE: Create new tables and structures in the database.
Example:
CREATE TABLE students ( StudentID int, LastName varchar(20), FirstName varchar(20), Image blob );
INSERT: Add new records to a table.
Example:
INSERT INTO Students (StudentID, FirstName, LastName, Image) VALUES (309112442, "Robert", "Smith", LOAD_FILE('/~/LittleBobby.png'));
UPDATE: Modify existing records in a table.
Example:
UPDATE Students SET LastName="Tables" WHERE CustomerName="Robert";
DELETE: Remove records from a table.
Example:
DELETE FROM Students WHERE FirstName="Robert";
DROP: Remove a table from the database.
Example:
DROP TABLE Students;
Field Constraints in MySQL Tables
Types of Constraints
NOT NULL: Specifies that a column cannot store NULL values.
UNIQUE: Ensures that each row in the column has a unique value.
PRIMARY KEY: Combines NOT NULL and UNIQUE to uniquely identify each record.
FOREIGN KEY: Maintains referential integrity between tables (values match across tables).
CHECK: Enforces specific conditions for column values.
DEFAULT: Specifies a default value for a column if none is provided.
Creating Tables with Constraints
Example of Creating Tables
Students Table:
CREATE TABLE Students ( StudentID int NOT NULL AUTO_INCREMENT, FirstName varchar(20) NOT NULL, LastName varchar(20) NOT NULL, Image blob, PRIMARY KEY (StudentID), CHECK (StudentID > 0) );Teacher Assistants Table:
CREATE TABLE TeacherAssistants ( TeacherID int NOT NULL, TeachingSubject varchar NOT NULL, StudentID int, PRIMARY KEY (TeacherID), FOREIGN KEY (StudentID) REFERENCES Students(StudentID) );