Learning Objectives – after this chapter you will be able to:
- Explain why databases are used for state management and data storage in web applications.
- Describe MySQL/MariaDB, its features, history and relational structure.
- Choose suitable MySQL data types and storage engines.
- Create databases, tables and users in phpMyAdmin and with SQL.
- Write SQL statements: CREATE, INSERT, SELECT (WHERE, ORDER BY, LIMIT, LIKE, IN, BETWEEN, joins, GROUP BY), UPDATE, DELETE, ALTER and DROP.
Server-Side State with Database Support
In addition to client-side and server-side options such as cookies and sessions, a database can be used to maintain state on a web site. It is used to store user-specific information when the amount of information is large, and it is especially useful for maintaining long-term state, or state that must survive even if the server is restarted. Database support is used in combination with cookies or session state – for example, an e-commerce site keeps the shopping cart, orders and customer profiles in a relational database and identifies the user with a session.
Advantages of a database for state management:
- Security – the user name and password entered on the login page can be checked against the database, which determines whether the user has the rights to access each page of the site.
- Consistency – an online bookstore must keep track of the daily transactions. Database facilities such as transactions, referential integrity (foreign keys) and triggers keep the data consistent.
- Personalisation – the database can store each user's preferences (language, theme, favourite categories), so the site can quickly look them up and present content accordingly.
Disadvantages:
- Database support involves more complex software configuration (a database server, users, privileges, backups).
- Performance can suffer if the relational data model is poorly designed or queries are not indexed.
What is MySQL?
MySQL, the most popular open source SQL database management system, is developed, distributed and supported by Oracle Corporation. It is written in C and C++.
- MySQL is a database management system. A database is a structured collection of data – anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access and process the data you need a database management system such as MySQL Server.
- MySQL is a relational database management system (RDBMS). A relational database stores data in separate tables rather than in one big storeroom, which adds speed and flexibility. The SQL part of MySQL stands for Structured Query Language, the most common standardised language for accessing databases, defined by the ANSI/ISO SQL Standard (SQL-92, SQL:1999, SQL:2003 and later versions).
- MySQL software is open source. Anyone can download, use and modify it. It is licensed under the GNU General Public License (GPL); a commercial licence is available for companies that do not want to use the GPL.
- The MySQL server is very fast, reliable, scalable and easy to use, and it works in client/server or embedded systems: a multi-threaded SQL server supports different back ends, client programs and libraries, administrative tools and many programming interfaces (APIs).
- A large amount of contributed software is available, so almost every programming language supports MySQL. The official pronunciation is "My Ess Que Ell" (not "my sequel").
History of MySQL
MySQL was created by the Swedish company MySQL AB, founded by David Axmark, Allan Larsson and Michael "Monty" Widenius; the first version appeared in 1995. The developers originally wanted to use the mSQL database with their own fast low-level (ISAM) routines, found it not fast or flexible enough, and wrote a new SQL interface with almost the same API as mSQL. MySQL is named after Monty's daughter My; the dolphin in the logo is called Sakila, a name chosen in a "Name the Dolphin" contest. Sun Microsystems bought MySQL AB in 2008, and Oracle acquired Sun in 2010.
MariaDB was started in 2009 by Monty Widenius as a community fork of MySQL (named after his younger daughter, Maria). It is a drop-in replacement for MySQL and is what XAMPP installs (MariaDB 10.4 in XAMPP 8.2.12). Everything in this book works the same way on MySQL and MariaDB.
MySQL structure
Because MySQL is a relational database, you separate information into tables. Each table consists of columns (fields), each representing one piece of information – for example a customer's first name in one field and last name in another – and rows (records), one per customer. Fields hold different types of data: text, numbers, dates and so on.
Tables are linked through a common field. Imagine a table with each customer's name, address and customer id, and another table with the customer id and the orders he or she placed. The common field, the customer id, links the two tables, so you can see all information about a customer at once. The common field is the primary key of the first table and a foreign key in the second.
MySQL Data Types
| Data type | Description |
|---|---|
| CHAR(length) | Fixed-length text of 0–255 characters; shorter values are padded. |
| VARCHAR(length) | Variable-length text; the maximum length is given in parentheses (up to 65,535 bytes per row in MySQL 5.0.3 and later; old versions: 255). |
| TINYTEXT / TEXT / MEDIUMTEXT / LONGTEXT | Text of up to 255 / 65,535 / 16,777,215 / 4,294,967,295 characters. |
| TINYINT | Integer from -128 to 127 (0–255 UNSIGNED). |
| SMALLINT | Integer from -32,768 to 32,767. |
| MEDIUMINT | Integer from -8,388,608 to 8,388,607. |
| INT | Integer from -2,147,483,648 to 2,147,483,647 (0–4,294,967,295 UNSIGNED). The number in INT(11) is only a display width. |
| BIGINT | Integer from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. |
| DECIMAL(length, dec) | Exact fixed-point number; a price up to 999.99 is DECIMAL(5,2). Use for money. |
| FLOAT / DOUBLE | Approximate floating-point numbers. |
| DATE | A date as YYYY-MM-DD. |
| TIME | A time as hh:mm:ss. |
| DATETIME | Date and time as YYYY-MM-DD hh:mm:ss. |
| TIMESTAMP | Date and time, stored in UTC; can be filled automatically with the current time. |
| YEAR | A year in 4-digit format. |
| ENUM('opt1', 'opt2', …) | Only one of the listed values (up to 65,535 options), e.g. ENUM('Male','Female','Other'). |
| SET('a', 'b', …) | Zero or more of the listed values. |
| TINYBLOB / BLOB / MEDIUMBLOB / LONGBLOB | Binary data (images, files) of the same sizes as the TEXT types; compared byte by byte (case-sensitive). |
| BOOLEAN | A synonym for TINYINT(1) – 0 is false, 1 is true. |
Column attributes
- NOT NULL – a value is required. DEFAULT x – the value used when none is given.
- AUTO_INCREMENT – MySQL numbers the rows automatically (usually for the primary key).
- PRIMARY KEY – uniquely identifies each row. UNIQUE – no two rows may have the same value.
- FOREIGN KEY … REFERENCES – links to the primary key of another table (InnoDB).
Types of Tables and Storage Engines
There are two kinds of tables: transaction-safe tables (TSTs) and non-transaction-safe tables (NTSTs). Transaction-safe tables can roll back changes and recover after a crash. Non-transaction-safe tables are faster and use less memory, but every change is permanent immediately. MySQL stores tables using storage engines:
| Engine | Description |
|---|---|
| InnoDB | The default engine since MySQL 5.5 (and in MariaDB). Supports transactions (TST), row-level locking – so many users can change different rows at the same time – foreign keys and crash recovery. Use it for almost every table. |
| MyISAM | The default engine of old MySQL versions (before 5.5). Fast for reading, supports full-text indexes, but has no transactions (NTST) and locks the whole table on writes. |
| MEMORY | Keeps the table in RAM – extremely fast but the data is lost when the server stops; for temporary data only. Does not support BLOB/TEXT columns. |
| MERGE | Treats several identical MyISAM tables as one (NTST). |
| ARCHIVE / CSV | Compressed storage for logs; tables stored as CSV files. |
| Aria | MariaDB's crash-safe replacement for MyISAM (used for system tables). |
| BDB | BerkeleyDB – an early transaction-safe engine mentioned in old books; removed in MySQL 5.1. |
MySQL Syntax and Commands
Although you can work with MySQL from a command prompt (the MariaDB monitor, opened with Shell → mysql -u root in XAMPP), and from PHP, the SQL syntax is the same everywhere. SQL keywords are usually written in CAPITAL letters – not required, but it separates SQL syntax from table and column names.
| Command | Purpose |
|---|---|
| CREATE | Creates new databases and tables. |
| ALTER | Modifies existing tables. |
| SELECT | Chooses the data you want. |
| INSERT INTO | Puts values into a table. |
| UPDATE | Modifies data already in a table. |
| DELETE | Erases data from a table. |
| DESCRIBE | Shows the structure of a table. |
| DROP | Deletes an entire table or database. |
| SHOW | Lists databases, tables, columns, processes … |
Creating a Database in phpMyAdmin
- Start Apache and MySQL in the XAMPP Control Panel and open
http://localhost/phpmyadmin/. - Click New in the left panel (or the Databases tab). Type the database name
phpbook, choose the collation utf8mb4_general_ci, and click Create. - In "Create new table" enter the table name and the number of columns, click Create (or Go), then fill in the name, type, length, default,
A_I(auto-increment) and index (PRIMARY) of each column and click Save. - Use the Insert tab to add rows and the Browse tab to see them.
- Every action you do with the mouse is shown as an SQL statement – a good way to learn SQL. You can type any statement yourself in the SQL tab and click Go.
The rest of this chapter shows the SQL statements and the output of the MariaDB monitor. You can type them in phpMyAdmin's SQL tab or in the XAMPP Shell.
Creating the Database and Tables
SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [(none)]> CREATE DATABASE phpbook CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; Query OK, 1 row affected MariaDB [(none)]> SHOW DATABASES LIKE 'php%'; +-----------------+ | Database (php%) | +-----------------+ | phpbook | +-----------------+ 1 row in set MariaDB [(none)]> USE phpbook; Database changed
SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> CREATE TABLE courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL UNIQUE,
duration VARCHAR(20),
fee DECIMAL(8,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB;
Query OK, 0 rows affected
MariaDB [phpbook]> CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
roll INT NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
city VARCHAR(30),
course_id INT,
email VARCHAR(100),
gender ENUM('Male','Female','Other'),
joined_on DATE,
FOREIGN KEY (course_id) REFERENCES courses(course_id)
) ENGINE=InnoDB;
Query OK, 0 rows affected
MariaDB [phpbook]> SHOW TABLES;
+-------------------+
| Tables_in_phpbook |
+-------------------+
| courses |
| students |
+-------------------+
2 rows in set
MariaDB [phpbook]> DESCRIBE students;
+-----------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| roll | int(11) | NO | UNI | NULL | |
| name | varchar(50) | NO | | NULL | |
| city | varchar(30) | YES | | NULL | |
| course_id | int(11) | YES | MUL | NULL | |
| email | varchar(100) | YES | | NULL | |
| gender | enum('Male','Female','Other') | YES | | NULL | |
| joined_on | date | YES | | NULL | |
+-----------+-------------------------------+------+-----+---------+----------------+
8 rows in setInserting Data – INSERT INTO
Syntax
INSERT INTO tablename (field1, field2, …) VALUES ("value1", "value2", …);SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> INSERT INTO courses (course_name, duration, fee) VALUES
('Core PHP', '3 months', 4500.00),
('Advance PHP', '2 months', 6000.00),
('MySQL', '1 month', 2500.00),
('Laravel', '2 months', 8000.00);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [phpbook]> INSERT INTO students (roll, name, city, course_id, email, gender, joined_on) VALUES
(101, 'Asha Verma', 'Bhopal', 1, 'asha@example.com', 'Female', '2026-07-01'),
(102, 'Ravi Kumar', 'Indore', 2, 'ravi@example.com', 'Male', '2026-07-03'),
(103, 'Neha Joshi', 'Bhopal', 1, 'neha@example.com', 'Female', '2026-07-10'),
(104, 'John Mathew', 'Jabalpur',3, NULL, 'Male', '2026-08-01'),
(105, 'Kiran Patel', 'Indore', 1, 'kiran@example.com', 'Other', '2026-08-15'),
(106, 'Imran Khan', 'Bhopal', NULL, 'imran@example.com', 'Male', '2026-09-02');
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [phpbook]> SELECT COUNT(*) AS total_students FROM students;
+----------------+
| total_students |
+----------------+
| 6 |
+----------------+
1 row in setQuerying the Database – SELECT
After inserting data you want to retrieve it. The SELECT statement chooses data that fits your criteria. The typical syntax is:
Syntax
SELECT [fieldnames] AS [alias] FROM [tablename] WHERE [criteria] ORDER BY [fieldname to sort on] [DESC] LIMIT [offset, maxrows]
- SELECT fieldnames – the fields you want to retrieve;
*means all fields. - AS – gives a column or expression an alias (another name) that you can use later, e.g. in ORDER BY. (An alias cannot be used in the WHERE clause, because WHERE is evaluated before the column values are computed.)
- FROM – the table or tables you are pulling the data from.
- WHERE – your criteria for filtering the data.
- ORDER BY – sorts the results on a field; add DESC for descending order (ASC is the default).
- LIMIT – limits the number of rows returned and optionally skips an offset:
LIMIT 9, 10shows rows 10 to 19. Useful for showing one page of records at a time with a "next page" link.
SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> SELECT * FROM students; +----+------+-------------+----------+-----------+-------------------+--------+------------+ | id | roll | name | city | course_id | email | gender | joined_on | +----+------+-------------+----------+-----------+-------------------+--------+------------+ | 1 | 101 | Asha Verma | Bhopal | 1 | asha@example.com | Female | 2026-07-01 | | 2 | 102 | Ravi Kumar | Indore | 2 | ravi@example.com | Male | 2026-07-03 | | 3 | 103 | Neha Joshi | Bhopal | 1 | neha@example.com | Female | 2026-07-10 | | 4 | 104 | John Mathew | Jabalpur | 3 | NULL | Male | 2026-08-01 | | 5 | 105 | Kiran Patel | Indore | 1 | kiran@example.com | Other | 2026-08-15 | | 6 | 106 | Imran Khan | Bhopal | NULL | imran@example.com | Male | 2026-09-02 | +----+------+-------------+----------+-----------+-------------------+--------+------------+ 6 rows in set MariaDB [phpbook]> SELECT roll, name AS student_name, city FROM students WHERE city = 'Bhopal' ORDER BY name; +------+--------------+--------+ | roll | student_name | city | +------+--------------+--------+ | 101 | Asha Verma | Bhopal | | 106 | Imran Khan | Bhopal | | 103 | Neha Joshi | Bhopal | +------+--------------+--------+ 3 rows in set MariaDB [phpbook]> SELECT name, joined_on FROM students ORDER BY joined_on DESC LIMIT 0, 3; +-------------+------------+ | name | joined_on | +-------------+------------+ | Imran Khan | 2026-09-02 | | Kiran Patel | 2026-08-15 | | John Mathew | 2026-08-01 | +-------------+------------+ 3 rows in set
Condition clauses
Use the following conditions after WHERE (% is the wildcard for LIKE):
| Condition | Meaning |
|---|---|
| field = value | equal |
| field > value, field < value | greater / less than |
| field >= value, field <= value | greater / less than or equal |
| field != value, field <> value | not equal |
| field BETWEEN value1 AND value2 | within a range (inclusive) |
| field NOT BETWEEN value1 AND value2 | outside a range |
| field LIKE value | pattern match: % any characters, _ one character |
| field NOT LIKE value | does not match the pattern |
| field IS NULL / IS NOT NULL | no value / has a value |
| field IN (value1, value2, …) | equal to one of the values |
| field NOT IN (value1, value2, …) | equal to none of the values |
SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> SELECT name, city FROM students WHERE name LIKE '%an%';
+-------------+--------+
| name | city |
+-------------+--------+
| Kiran Patel | Indore |
| Imran Khan | Bhopal |
+-------------+--------+
2 rows in set
MariaDB [phpbook]> SELECT name, joined_on FROM students WHERE joined_on BETWEEN '2026-07-01' AND '2026-07-31';
+------------+------------+
| name | joined_on |
+------------+------------+
| Asha Verma | 2026-07-01 |
| Ravi Kumar | 2026-07-03 |
| Neha Joshi | 2026-07-10 |
+------------+------------+
3 rows in set
MariaDB [phpbook]> SELECT name, city FROM students WHERE city IN ('Indore', 'Jabalpur') AND gender = 'Male';
+-------------+----------+
| name | city |
+-------------+----------+
| Ravi Kumar | Indore |
| John Mathew | Jabalpur |
+-------------+----------+
2 rows in set
MariaDB [phpbook]> SELECT name FROM students WHERE email IS NULL OR course_id IS NULL;
+-------------+
| name |
+-------------+
| John Mathew |
| Imran Khan |
+-------------+
2 rows in setAggregate functions and GROUP BY
SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> SELECT city, COUNT(*) AS students FROM students GROUP BY city ORDER BY students DESC; +----------+----------+ | city | students | +----------+----------+ | Bhopal | 3 | | Indore | 2 | | Jabalpur | 1 | +----------+----------+ 3 rows in set MariaDB [phpbook]> SELECT MIN(fee) AS lowest, MAX(fee) AS highest, AVG(fee) AS average, SUM(fee) AS total FROM courses; +---------+---------+-------------+----------+ | lowest | highest | average | total | +---------+---------+-------------+----------+ | 2500.00 | 8000.00 | 5250.000000 | 21000.00 | +---------+---------+-------------+----------+ 1 row in set
Selecting from multiple tables (joins)
You can retrieve information from two or more tables at once:
Syntax
SELECT table1.field, table2.field FROM table1, table2 WHERE table1.field = table2.field; -- or SELECT table1.field, table2.field FROM table1 LEFT JOIN table2 ON table1.field = table2.field;
SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> SELECT s.name, c.course_name, c.fee FROM students s, courses c WHERE s.course_id = c.course_id ORDER BY s.roll; +-------------+-------------+---------+ | name | course_name | fee | +-------------+-------------+---------+ | Asha Verma | Core PHP | 4500.00 | | Ravi Kumar | Advance PHP | 6000.00 | | Neha Joshi | Core PHP | 4500.00 | | John Mathew | MySQL | 2500.00 | | Kiran Patel | Core PHP | 4500.00 | +-------------+-------------+---------+ 5 rows in set MariaDB [phpbook]> SELECT s.name, c.course_name FROM students s LEFT JOIN courses c ON s.course_id = c.course_id WHERE s.city = 'Bhopal'; +------------+-------------+ | name | course_name | +------------+-------------+ | Asha Verma | Core PHP | | Neha Joshi | Core PHP | | Imran Khan | NULL | +------------+-------------+ 3 rows in set
An ordinary (inner) join shows only students that have a matching course; the LEFT JOIN keeps every student from the left table and shows NULL where there is no matching course (Imran).
Changing and Deleting Data
Quick reference: data manipulation commands
CREATE DATABASE databasename;
CREATE TABLE tablename (field1 type, field2 type, …, PRIMARY KEY (field));
ALTER TABLE tablename ADD|MODIFY|DROP COLUMN …;
RENAME TABLE oldtablename TO newtablename;
INSERT INTO tablename (field1, field2, …) VALUES ("value1", "value2", …);
UPDATE tablename SET field1 = value1, field2 = value2 … WHERE condition;
DELETE FROM tablename WHERE condition;
DROP TABLE tablename;
DROP DATABASE databasename;SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> UPDATE students SET city = 'Ujjain', email = 'john@example.com' WHERE roll = 104; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [phpbook]> ALTER TABLE students ADD COLUMN mobile VARCHAR(10) AFTER email; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 MariaDB [phpbook]> UPDATE students SET mobile = '9876543210' WHERE roll = 101; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [phpbook]> SELECT roll, name, city, email, mobile FROM students WHERE roll IN (101, 104); +------+-------------+--------+------------------+------------+ | roll | name | city | email | mobile | +------+-------------+--------+------------------+------------+ | 101 | Asha Verma | Bhopal | asha@example.com | 9876543210 | | 104 | John Mathew | Ujjain | john@example.com | NULL | +------+-------------+--------+------------------+------------+ 2 rows in set MariaDB [phpbook]> DELETE FROM students WHERE roll = 106; Query OK, 1 row affected MariaDB [phpbook]> SELECT COUNT(*) FROM students; +----------+ | COUNT(*) | +----------+ | 5 | +----------+ 1 row in set
An UPDATE or DELETE without a WHERE clause changes or deletes every row of the table. Always write the WHERE clause first – and keep backups (phpMyAdmin → Export).
Creating a Database User
Applications should not connect as root. Older notes used a separate user "ict" with password "bhopal" in the classroom. Create a user and give it privileges only on the application database:
SQL SESSION SQL typed in the MariaDB monitor (XAMPP Shell → mysql -u root) or the phpMyAdmin SQL tab
MariaDB [phpbook]> CREATE USER 'ict'@'localhost' IDENTIFIED BY 'bhopal'; Query OK, 0 rows affected MariaDB [phpbook]> GRANT SELECT, INSERT, UPDATE, DELETE ON phpbook.* TO 'ict'@'localhost'; Query OK, 0 rows affected MariaDB [phpbook]> SHOW GRANTS FOR 'ict'@'localhost'; +------------------------------------------------------------------------------------------------------------+ | Grants for ict@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `ict`@`localhost` IDENTIFIED BY PASSWORD '*7AA01D6737C52565EDF7656017801BFF3534EA48' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `phpbook`.* TO `ict`@`localhost` | +------------------------------------------------------------------------------------------------------------+ 2 rows in set
In phpMyAdmin the same is done in User accounts → Add user account.
Key Takeaways
- Databases keep large, long-term, consistent state; they are combined with sessions/cookies.
- MySQL is an open source relational DBMS (Oracle); MariaDB, its community fork, ships with XAMPP.
- Data lives in tables of columns and rows, linked by primary and foreign keys.
- Choose types carefully: INT, DECIMAL for money, VARCHAR/TEXT for text, DATE/DATETIME for dates, ENUM for fixed choices.
- InnoDB (default) supports transactions, row locking and foreign keys; MyISAM does not.
- SQL: CREATE, INSERT, SELECT … WHERE … ORDER BY … LIMIT, joins, GROUP BY, UPDATE, DELETE, ALTER, DROP.
- Use phpMyAdmin or the MariaDB monitor, and give applications their own limited user.
Question / Answer (15 questions)
Answer : To store large amounts of user-specific data permanently – data that must survive server restarts and be available across sessions – such as user accounts, preferences, carts and orders.
Answer : Security (login credentials and rights are checked against the database), consistency (transactions, referential integrity and triggers) and personalisation (user preferences can be stored and looked up quickly).
Answer : An open source relational database management system that uses SQL, developed and supported by Oracle Corporation and written in C and C++.
Answer : Data is stored in separate tables that are related to each other through common fields (keys), instead of one big store, which gives speed and flexibility.
Answer : Structured Query Language – the standard language (defined by ANSI/ISO) used to create, query and modify relational databases.
Answer : MySQL is named after My, the daughter of co-founder Monty Widenius. MariaDB is a community fork of MySQL started by Widenius in 2009 (named after his daughter Maria); it is a drop-in replacement and is installed by XAMPP.
Answer : CHAR(n) always stores n characters, padding shorter values; VARCHAR(n) stores only the actual characters (up to n) plus a length byte, saving space for values of varying length.
Answer : DECIMAL(length, decimals), e.g. DECIMAL(8,2), because it stores exact values; FLOAT/DOUBLE are approximate and can cause rounding errors.
Answer : A primary key uniquely identifies each row of a table; a foreign key is a column in another table that refers to that primary key, linking the two tables.
Answer : Transaction-safe tables (InnoDB) can roll back uncommitted changes and recover after crashes; non-transaction-safe tables (MyISAM, MEMORY) apply every change permanently and immediately, but are lighter.
Answer : InnoDB supports transactions, row-level locking for many simultaneous users, foreign keys and crash recovery; it has been MySQL's default engine since version 5.5.
Answer : SELECT lists the fields (or *), AS gives aliases, FROM names the tables, WHERE filters rows, ORDER BY sorts (DESC for descending) and LIMIT offset, count restricts the number of rows returned.
Answer : 10 rows starting after the first 9, i.e. records 10 to 19 – used for paging.
Answer : An inner join returns only rows that have matching values in both tables; a LEFT JOIN returns all rows of the left table and fills the columns of the right table with NULL where there is no match.
Answer : Every row of the table is updated or deleted, so the WHERE clause should always be checked before running such statements.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The notes on this page are the author’s own compilation from the e-book PHP: Basics to Advance. Every program was run on Apache with PHP 8 and MariaDB, and the output shown is the real result of that run. The works listed below were consulted and are cited for attribution and further reading. Product names and trademarks belong to their respective owners.
- Oracle Corporation. (n.d.). MySQL 8.0 Reference Manual – What is MySQL? https://dev.mysql.com/doc/refman/8.0/en/what-is-mysql.html
- Oracle Corporation. (n.d.). MySQL 8.0 Reference Manual – Data Types. https://dev.mysql.com/doc/refman/8.0/en/data-types.html
- MariaDB Foundation. (n.d.). Data Types. MariaDB Knowledge Base. https://mariadb.com/kb/en/library/data-types/
- MariaDB plc. (n.d.). InnoDB Storage Engine. https://mariadb.com/docs/storage-engines/innodb/
- MariaDB Foundation. (n.d.). SELECT. MariaDB Knowledge Base. https://mariadb.com/kb/en/select/
- phpMyAdmin contributors. (n.d.). phpMyAdmin Documentation. https://docs.phpmyadmin.net/