So far, we have worked with tables that already existed. Now we will create our own. CREATE TABLE defines not only the table name, but also its columns and the kind of values each column can store.
CREATE TABLE users (
id INT,
name VARCHAR(100),
email VARCHAR(200),
city VARCHAR(100)
);This creates users with four columns. The type after each column tells the database what it is intended to hold. INT suits integer IDs; VARCHAR suits variable-length text such as names, emails, and cities.
Why do columns need types?
Types help the database store and process values correctly. Prices need arithmetic, dates need date comparisons, and text needs searching and sorting. Saving everything as text makes later work harder and less reliable.
INT — whole numbers: IDs, quantity, age
VARCHAR(n) — variable text: name, email
DECIMAL(10, 2) — exact decimals: prices, money
DATE — dates: birthdays, order dates
DATETIME — date and time: created moment
BOOLEAN — true or false: active flag
DECIMAL(10, 2) commonly means up to ten digits, two after the decimal point. Money needs exact values, so DECIMAL is generally preferable to ordinary floating point. Exact type names vary by database, but the concepts are similar.
Use the table after creation
After creating products, use INSERT to add rows and SELECT to inspect them. Every query and modification from earlier chapters depends on a correctly defined table structure.
CREATE TABLE products (
id INT,
name VARCHAR(100),
price DECIMAL(10, 2),
category VARCHAR(100)
);
INSERT INTO products (id, name, price, category)
VALUES (1, 'Keyboard', 80.00, 'Electronics');
SELECT * FROM products;Modify a structure with ALTER TABLE
Requirements change. If users later needs a phone number, ALTER TABLE can add it. Existing rows normally receive NULL because no historical value exists. Type changes, removed columns, and constraint changes vary more between database products, so consult your system's documentation before complex changes.
ALTER TABLE users
ADD phone VARCHAR(30);Treat DROP with extra care. ALTER TABLE users DROP COLUMN phone removes a column; DROP TABLE users removes both structure and all data. This differs from DELETE, which removes rows. Practice structural changes only in a rebuildable learning database.
Build a table yourself
Name the table, then add fields one at a time. The schema card and SQL update immediately. Field names may use letters, numbers, and underscores, but cannot begin with a number.
INTEGERCREATE TABLE products ( id INTEGER PRIMARY KEY );
The schema cards and CREATE TABLE update with the design.Design products and orders for a small shop
In a personal practice database, create products with at least id, name, price, and category, plus orders with id, user_id, order_date, and status. Choose suitable types, then add stock INT to products. Do not add constraints yet—we will improve the design next.