all topics

Basic Queries

Create, insert, fetch

The three statements everything else builds on: make a table, put rows in it, get rows back out.

sql
-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
 
-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');
 
-- fetch
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';
  • CREATE TABLE defines the shape once: column names, their types, and any constraints. PRIMARY KEY marks empId as the column that uniquely identifies each row, NOT NULL means name and dept can never be left empty.
  • INSERT INTO ... VALUES (...) adds one row at a time here, in the same order the columns were defined: empId, then name, then dept.
  • SELECT * FROM EMPLOYEE WHERE dept = 'Sales' reads rows back out. * means every column, WHERE dept = 'Sales' filters down to just the rows matching that condition, here, Clark and Ava, not Dave.

More realistic column types

sql
create table employee
    -> ( eid integer,
    -> ename char(20),
    -> sex char(1),
    -> grade char(2),
    -> gross decimal);
  • The -> at the start of each line isn't part of the SQL itself, it's the MySQL command-line client's continuation prompt, showing up because the statement spans multiple lines before the closing ;.
  • char(20) is a fixed-length string, always exactly 20 characters, padded with spaces if the actual value is shorter. sex char(1) and grade char(2) use the same type just sized down for a single character or a two-letter code.
  • decimal is for exact numeric values, salary figures like gross being the obvious case, where the small rounding error float/double can introduce actually matters.
  • No PRIMARY KEY or NOT NULL here, unlike the EMPLOYEE table above, so this table allows duplicate eids and empty columns. Worth adding those constraints back once the table is meant for real use.