SQL, data types and constraints

SQL (Structured Query Language) is the query language used by major relational database management systems (RDBMS) such as MySQL, Oracle and SQL Server. Its statements use descriptive English words, and we say what data we want, not how to get it.

Rules for writing SQL in MySQL (at the mysql> prompt):

• SQL keywords are not case sensitive: SELECT and select are the same. (Whether table names are case sensitive depends on the operating system, so it is safest to type names consistently.)
• End every statement with a semicolon (;).
• A long statement can run over several lines; MySQL shows -> for each continuation line until it reaches the semicolon.

Common data types:

• CHAR(n): fixed-length text of n characters (n from 0 to 255). Shorter values are padded with spaces on the right.
• VARCHAR(n): variable-length text up to n characters (n up to 65535); it uses only as much space as the actual text.
• INT: whole numbers, taking 4 bytes (from -2147483648 to 2147483647); BIGINT holds larger numbers.
• FLOAT: numbers with decimal points.
• DATE: dates in the format 'YYYY-MM-DD', for example '2002-02-28'.

Constraints restrict the values a column may hold:

• NOT NULL: the column cannot be left empty (NULL means missing, unknown or not applicable).
• UNIQUE: all values in the column must be different.
• DEFAULT: a value used when none is given.
• PRIMARY KEY: identifies each row uniquely (it is NOT NULL and UNIQUE together).
• FOREIGN KEY: refers to the primary key of another table.

Choosing types: a 12-digit Aadhaar number or GUID is stored as CHAR(12): it always has 12 digits and is never used in arithmetic. A phone number of 10 digits can be CHAR(10).

Example (new): for a BOOK table, AccessionNo could be INT PRIMARY KEY, Title VARCHAR(50) NOT NULL, ISBN CHAR(13) UNIQUE (always 13 characters, never added up), Price FLOAT, and DateAdded DATE.