MySQL Tutorial for Beginners with a Small Inventory Database

A connection has a host, a database account and an active database context. The account’s privileges determine which commands it may run. You can select an authorised existing practice database with USE database_name; creating a database requires additional permission. Installation and account setup are operational tasks, while SELECT is a query sent after the connection is established.

Do not place account passwords inside saved SQL examples. An application should read its connection configuration through an appropriate secret or environment mechanism and use a limited account. A reporting job may need SELECT without receiving DROP or account-management privileges. Connection success is not evidence that the correct database has been selected.

The inventory example declares an integer generated primary key, a unique item code, a decimal price and a nonnegative stock quantity. The id provides technical row identity, while code provides a separate business uniqueness rule. DECIMAL(10,2) expresses exact decimal intent for the price in MySQL; quantity is a count, so it uses INTEGER.

InnoDB provides transaction and foreign-key support for common application tables. AUTO_INCREMENT generates omitted ids but does not promise consecutive numbers without gaps. A UNIQUE label still matters even with a generated key, because two technical ids should not allow accidental duplicate business codes. CHECK enforcement depends on a supported MySQL version; the modern target here enforces the quantity rule.

SELECT chooses the columns a reader needs, WHERE restricts the records, and ORDER BY makes the displayed order intentional. quantity<5 is the replenishment rule in this lesson; it is not a universal stock policy. price*quantity calculates each row’s current known stock value without changing saved fields. Use an alias that identifies the calculation.

A LIMIT applied after ordering can produce a small queue of items, but a stable key should resolve tied priorities. Backticks are MySQL’s usual identifier quotes, whereas single quotes delimit text values. The example avoids reserved names so quoted identifiers are unnecessary. Do not copy PostgreSQL’s default double-quoted naming style into MySQL without considering SQL mode.

INSERT adds inventory records, UPDATE can adjust a saved quantity, and DELETE removes records. Preview the intended UPDATE or DELETE population with SELECT using the same key condition. A stock adjustment often belongs with another write, such as recording its movement, in one InnoDB transaction so the related changes succeed or fail together.

START TRANSACTION begins the unit, COMMIT confirms it and ROLLBACK abandons uncommitted work. Autocommit means ordinary statements can commit individually unless grouped explicitly. Many MySQL DDL commands cause implicit commits, so table creation should not be treated as just another reversible row update. Check the operation’s documented transaction behaviour rather than applying one rollback assumption to every SQL statement.

Bind item codes and thresholds as values through the driver’s prepared-statement interface. A bound parameter does not quote a dynamic table name, and it does not make LIKE wildcards literal. For generated ids, use the insert result or a same-connection mechanism such as LAST_INSERT_ID rather than reading the largest id from the table.

MySQL supports CTEs and window functions in modern versions, but it has no native FULL OUTER JOIN. Date arithmetic commonly uses DATE_ADD and INTERVAL syntax. ONLY_FULL_GROUP_BY helps reject ambiguous summaries; do not disable it merely because a query selects an ungrouped label. These distinctions matter when translating a general SQL example into a MySQL application.

An index on a selective lookup such as code can support efficient item retrieval, while an index on quantity may or may not benefit a low-stock report depending on the population and plan. EXPLAIN shows the chosen access strategy. An index is not automatically useful just because its column appears somewhere in the query.

Measure with realistic row counts and distributions, and include the write cost of additional indexes. Keep source records and report totals reconcilable. If price later becomes nullable or multiple currencies are introduced, the inventory-value expression needs a new completeness or currency policy; a query that runs successfully can still answer an outdated business question.

Run these statements in an existing disposable MySQL database. The three records include a stocked item, an out-of-stock item and a low-stock item. Explicit ids keep the expected output fixed while the table definition demonstrates how future omitted ids would be generated.

CREATE TABLE mysql_inventory (
 id INTEGER PRIMARY KEY AUTO_INCREMENT,
 code VARCHAR(20) NOT NULL UNIQUE,
 price DECIMAL(10,2) NOT NULL,
 quantity INTEGER NOT NULL CHECK(quantity>=0)
) ENGINE=InnoDB;
INSERT INTO mysql_inventory (id,code,price,quantity) VALUES
 (1,'PEN',1.50,20),(2,'BOOK',4.00,0),(3,'BAG',12.00,3);

Calculate the known inventory value across all three items, and show the number of different records included. Keep the output as one summary row.

SELECT COUNT(*) AS item_count,SUM(price*quantity) AS stock_total
FROM mysql_inventory;

item_count | stock_total
3 | 66.00

PEN contributes twenty units at 1.50, or thirty. BOOK contributes zero, and BAG contributes thirty-six. The total is sixty-six across three rows. SUM measures value rather than item identity, while COUNT measures records rather than total units. If a new item duplicates a price, its value should still contribute; SUM(DISTINCT price) would not be an appropriate inventory valuation.

See the MySQL tutorial, InnoDB transaction model and MySQL join grammar for their individual server rules.