SQL and MySQL basics

The essentials

SQL is a query language used by almost all relational database systems. It is easy to learn because its commands look like English, it is non-procedural (you state what you want, not how to get it), and the same commands work across many RDBMS such as MySQL, Oracle and PostgreSQL. MySQL is an open-source RDBMS. SQL keywords are not case sensitive, and in MySQL each statement ends with a semicolon.

Key points

• DDL (Data Definition Language): defines or changes structure. CREATE, ALTER, DROP.
• DML (Data Manipulation Language): changes data. INSERT, UPDATE, DELETE.
• DQL (Data Query Language): retrieves data. SELECT.
• Numeric types: INT (whole numbers), FLOAT (numbers with decimals).
• CHAR(n): fixed length; if fewer than n characters are stored, the value is padded to n characters. VARCHAR(n): variable length up to n characters, using only the space needed.
• DATE: stored in the format 'YYYY-MM-DD'.
• Constraints are rules on data in a column: NOT NULL (value must be given), UNIQUE (no repeated values), DEFAULT (value used when none is given), PRIMARY KEY (unique and not null), FOREIGN KEY (must match the referenced primary key).
• Useful commands: CREATE DATABASE, USE database, SHOW TABLES, DESCRIBE table (or DESC).

Exam tips

• Classify by purpose: anything changing structure is DDL; anything changing rows is DML.
• CHAR is suitable for values of fixed size, such as a 6-character PIN code; VARCHAR suits names of different lengths.

Common mistakes

• Calling SELECT a DML command when the question uses the DDL/DML/DQL split. In that classification it is DQL.
• Thinking UNIQUE stops NULL values. UNIQUE only stops repeated values; NOT NULL stops NULLs.