Commands

Create a database, list them, switch into one

CREATE DATABASE 2026boards;
SHOW DATABASES;
USE 2026boards;

• CREATE DATABASE 2026boards makes a new, empty database.
• SHOW DATABASES lists every database the current connection can see, useful right after creating one to confirm it actually landed.
• USE 2026boards switches into it. Every statement from here on runs against 2026boards until a different USE switches elsewhere.

A real table, most of the constraints at once

create table employee(
    -> code integer primary key,
    -> name varchar(30) not null,
    -> desig varchar(30) not null,
    -> salary decimal(10,2) check(salary>10000),
    -> mob char(10) unique,
    -> doj date,
    -> state varchar(20),
    -> gender char default 'M' 
    -> );

• code INTEGER PRIMARY KEY, the unique identifier for each employee.
• name and desig (designation), VARCHAR(30) NOT NULL, variable-length text that's required, can't be left empty.
• salary DECIMAL(10, 2) CHECK (salary > 10000), up to 8 digits before the decimal and 2 after, and the CHECK rejects any row trying to insert 10000 or less.
• mob CHAR(10) UNIQUE, a fixed-length 10-character mobile number, no two employees can share the same one.
• doj DATE, date of joining, in YYYY-MM-DD.
• state VARCHAR(20), no constraint at all here, plain and optional.
• gender CHAR DEFAULT 'M', a single character (CHAR with no length is just CHAR(1)), defaulting to 'M' when an INSERT doesn't specify it.

Checking a table's structure with DESC

mysql> desc employee;
 
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| code   | int           | NO   | PRI | NULL    |       |
| name   | varchar(30)   | NO   |     | NULL    |       |
| desig  | varchar(30)   | NO   |     | NULL    |       |
| salary | decimal(10,2) | YES  |     | NULL    |       |
| mob    | char(10)      | YES  | UNI | NULL    |       |
| doj    | date          | YES  |     | NULL    |       |
| state  | varchar(20)   | YES  |     | NULL    |       |
| gender | char(1)       | YES  |     | M       |       |
+--------+---------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

DESC <table> (short for DESCRIBE) prints back the table's actual structure, every column, its type, and every constraint applied to it, all in one table. Worth reading the same table from the CREATE TABLE above against this output side by side:

• Null = NO on code, name, desig is exactly NOT NULL (and PRIMARY KEY implies NOT NULL on its own, code never had it written explicitly).
• Key = PRI marks code as the primary key, Key = UNI marks mob as unique, both showing up here even though neither said the word "key" in the CREATE TABLE beyond PRIMARY KEY / UNIQUE.
• Default = M is exactly the DEFAULT 'M' from gender, and note gender char(1) here confirms the earlier point, a bare CHAR really is CHAR(1).
• Every other column shows Default = NULL, since none of them had a DEFAULT specified.

Listing tables in the current database

SHOW TABLES;

The table-level equivalent of SHOW DATABASES, lists every table that exists in whichever database the connection is currently USE'd into. Since it's scoped to the current database, employee only shows up here after USE 2026boards has actually run.

Populating employee, the working dataset

insert into employee values(101, 'Om Sharma', 'Manager', 50000, '12345', '2020-12-23', 'Uttarakhand', 'M');
insert into employee values(201, 'Devesh Sharma', 'Co-Manager', 75000, '2345', '2020-12-23', 'UP', 'M');
insert into employee values(301, 'Arnav', 'Asst. Manager', 44000, '123456', '2020-12-23', 'UP', 'M');
insert into employee values(104, 'Devansh', 'Clerk', 31000, '1234567', '2023-4-12', 'TN', 'F');
insert into employee values(105, 'Shubham', 'Asst. Clerk', 61000, '12345678', '2026-4-01', 'MH', 'F');
insert into employee values(106, 'Atulk', 'Clerk', 80000, '123456780', '2019-7-19', 'Goa', 'M');
insert into employee values(107, 'Anand', 'Clerk', 90000, '1234567890', '2022-9-30', 'Bihar', 'M');
insert into employee values(111, 'Chinmayee', 'MTS', 100000, '9234567890', '2022-10-30', 'GJ', 'F');
 
select * from employee;

+------+---------------+---------------+-----------+------------+------------+-------------+--------+
| code | name          | desig         | salary    | mob        | doj        | state       | gender |
+------+---------------+---------------+-----------+------------+------------+-------------+--------+
|  101 | Om Sharma     | Manager       |  50000.00 | 12345      | 2020-12-23 | Uttarakhand | M      |
|  104 | Devansh       | Clerk         |  31000.00 | 1234567    | 2023-04-12 | TN          | F      |
|  105 | Shubham       | Asst. Clerk   |  61000.00 | 12345678   | 2026-04-01 | MH          | F      |
|  106 | Atulk         | Clerk         |  80000.00 | 123456780  | 2019-07-19 | Goa         | M      |
|  107 | Anand         | Clerk         |  90000.00 | 1234567890 | 2022-09-30 | Bihar       | M      |
|  111 | Chinmayee     | MTS           | 100000.00 | 9234567890 | 2022-10-30 | GJ          | F      |
|  201 | Devesh Sharma | Co-Manager    |  75000.00 | 2345       | 2020-12-23 | UP          | M      |
|  301 | Arnav         | Asst. Manager |  44000.00 | 123456     | 2020-12-23 | UP          | M      |
+------+---------------+---------------+-----------+------------+------------+-------------+--------+
8 rows in set (0.00 sec)

This is the dataset later queries in this series build on, worth keeping in mind for what it's doing:

• Every INSERT writes all 8 columns positionally, in the exact order they were defined in CREATE TABLE, no column names needed since nothing's being skipped.
• SELECT * doesn't come back in insertion order, it's sorted by code (101, 104, 105, 106, 107, 111, 201, 301), because code is the PRIMARY KEY, and MySQL's default storage engine (InnoDB) physically clusters rows by primary key order.
• salary was inserted as a plain integer (50000), but comes back as 50000.00, matching the column's real type, DECIMAL(10, 2) always shows both digits after the decimal, regardless of how the value was typed in.
• Dates typed with single-digit month/day, '2023-4-12', come back normalized to the full YYYY-MM-DD form, 2023-04-12. MySQL stores and displays DATE consistently, the input just has to be unambiguous.

Inserting into specific columns only

insert into employee (code, name, desig, salary) values (113, "Vansh", "Peon", 18000);

Query OK, 1 row affected (0.00 sec)

Every earlier INSERT here supplied all 8 columns positionally, this one names just 4, (code, name, desig, salary), and provides values for only those. The rest, mob, doj, state, gender, get whatever the column defines for a missing value, NULL for the ones without a DEFAULT, 'M' for gender since that one has DEFAULT 'M'.

• Naming columns explicitly is what makes a partial insert possible at all, positional INSERT ... VALUES (...) with no column list has to supply every column, in order, no skipping.
• The values still have to line up with the named columns in the same order, code → 113, name → "Vansh", and so on, the column list and the value list are matched position by position, just a shorter list than the full table.
• This only works because none of the skipped columns are NOT NULL without a DEFAULT. If mob had been declared NOT NULL, this exact insert would fail.