Beginner’s Guide: Essential Table Operations – Create, View, and Delete Tables

Why tables matter

In a database, the database itself is like a folder, while a table corresponds to an Excel sheet that stores concrete data such as users, orders, or products.

Three essential elements of a table

Each table requires a field name , a data type , and constraint rules .

Common data types

int : integer values (e.g., ID, age, quantity)

varchar(n) : variable‑length strings (e.g., name, phone, address)

datetime : date‑time values (e.g., creation time, update time)

decimal(m,n) : fixed‑point numbers for amounts, prices, rates

Typical scenarios

Designing business‑system tables

Building test or practice tables

Inspecting table structures and field information

Practical example – a generic user table

-- Create user information table
CREATE TABLE UserInfo(
    UserID INT PRIMARY KEY IDENTITY(1,1), -- primary key, auto‑increment
    UserName VARCHAR(20) NOT NULL,        -- username, not null
    Phone VARCHAR(11),                    -- phone number
    Age INT,                              -- age
    CreateTime DATETIME DEFAULT GETDATE() -- default current time
);
GO

-- List all tables in the current database
SELECT name FROM sys.tables;

-- Show detailed structure of the user table
sp_help 'UserInfo';

-- Delete the table if it exists
DROP TABLE IF EXISTS UserInfo;
GO

Key points for beginners

PRIMARY KEY : ensures each row is unique; a table can have only one primary key.

IDENTITY(1,1) : auto‑increments the key value, eliminating manual ID entry.

NOT NULL : forces a column to contain a value, preventing empty or dirty data.