MySQL: Data Types, Constraints, CREATE
What is SQL?
SQL (Structured Query Language, often said "sequel") is the standard language to talk to relational databases. With SQL we create tables, put data in, change it and ask questions (queries) like "show the students of Jaipur with marks above 80". MySQL is a free, open-source RDBMS that understands SQL.
To practise, install MySQL Community Server (with MySQL Shell or Workbench) from dev.mysql.com, or use a portable package like XAMPP. Every SQL statement ends with a semicolon ;.
Categories of SQL commands
Creating and using a database
CREATE DATABASE school; SHOW DATABASES; USE school;
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+
USE school; makes school the current database, so all tables you create go into it.
MySQL data types
| Data type | Stores | Example value |
|---|---|---|
| CHAR(n) | Fixed-length text, n up to 255 | 'M', '302012' |
| VARCHAR(n) | Variable-length text | 'Aarav Sharma' |
| INT | Whole numbers | 101, -25 |
| FLOAT | Approximate decimal numbers | 3.14 |
| DECIMAL(p, s) | Exact decimals: p digits, s after point | DECIMAL(7,2) → 12345.67 |
| DATE | Date in 'YYYY-MM-DD' format | '2009-04-15' |
CHAR(10)
- 'Ram' is stored as 'Ram' + 7 spaces
- Always 10 characters of space
- Slightly faster for fixed-size data
VARCHAR(10)
- 'Ram' uses only 3 characters (+ length info)
- Saves space for varying text
- Best for names, cities, addresses
Constraints
Constraints are rules on columns that keep data correct.
| Constraint | Rule |
|---|---|
| NOT NULL | Column cannot be left empty |
| UNIQUE | No two rows can have the same value |
| DEFAULT | Value used when none is given |
| PRIMARY KEY | UNIQUE + NOT NULL; identifies each row |
| FOREIGN KEY | Value must exist in the referenced table's primary key |
| CHECK | Value must satisfy a condition (MySQL 8.0.16+) |
CREATE TABLE
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ... );
CREATE TABLE STUDENT ( RollNo INT PRIMARY KEY, Name VARCHAR(30) NOT NULL, Gender CHAR(1), DOB DATE, Class VARCHAR(5) DEFAULT 'XI-A', Marks DECIMAL(5,1) CHECK (Marks BETWEEN 0 AND 100), City VARCHAR(20) ); DESCRIBE STUDENT;
+--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | RollNo | int | NO | PRI | NULL | | | Name | varchar(30) | NO | | NULL | | | Gender | char(1) | YES | | NULL | | | DOB | date | YES | | NULL | | | Class | varchar(5) | YES | | XI-A | | | Marks | decimal(5,1) | YES | | NULL | | | City | varchar(20) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+
DESCRIBE (or DESC) shows the structure: Field = column name, Null = can it be empty, Key = PRI for primary key, Default = default value.
Table with a foreign key
CREATE TABLE GUARDIAN ( GID INT PRIMARY KEY, GName VARCHAR(30) NOT NULL, Phone CHAR(10) UNIQUE, RollNo INT, FOREIGN KEY (RollNo) REFERENCES STUDENT(RollNo) ); DESCRIBE GUARDIAN;
+--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | GID | int | NO | PRI | NULL | | | GName | varchar(30) | NO | | NULL | | | Phone | char(10) | YES | UNI | NULL | | | RollNo | int | YES | MUL | NULL | | +--------+-------------+------+-----+---------+-------+
SHOW, DESCRIBE and DROP
SHOW TABLES;
+------------------+ | Tables_in_school | +------------------+ | GUARDIAN | | STUDENT | +------------------+
DROP TABLE GUARDIAN; deletes the table and all its data permanently. DROP DATABASE school; deletes the entire database. Always double-check before dropping. A parent table (STUDENT) cannot be dropped while a child table (GUARDIAN) still refers to it.
Summary & practice
- SQL is the language of relational databases; MySQL is an RDBMS.
- DDL: CREATE, ALTER, DROP. DML: INSERT, UPDATE, DELETE. DQL: SELECT.
- Types: CHAR, VARCHAR, INT, FLOAT, DECIMAL, DATE.
- Constraints: NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY, FOREIGN KEY, CHECK.
Write CREATE TABLE commands for: (1) BOOK(BookID primary key, Title not null, Author, Price decimal with 2 places, Qty default 1). (2) ISSUE(IssueID primary key, BookID foreign key to BOOK, IssueDate date, ReturnDate date).
