MySQL Language Basics

This post will detail the rules of MySQL with code examples for each. In later posts, we will dive into how to use these features with examples. You can also use this post for future reference.

Table Of Contents

• Syntax
• Annotation
• Keys
• Insert
• Update
• Select
• Delete

Syntax

• Each query should end with a ; semicolon.

SELECT * FROM Customer;

Annotation

• Comments will be removed before sending the query to the server.

-- Single line comment
/* Multi line
comments */

Defining Structures

• Before you can work with data, a structure has to be made.
• This ensures the data is organised and goes in the right place.
• Each database has multiple tables. Each table has multiple rows. Each row has multiple fields.
• Commonly, the first field in a row is a unique ID.

-- Create a new database.
CREATE DATABASE DatabaseName;

-- Specify which database to create tables in.
USE DatabaseName;

CREATE TABLE TableName (
    TableNameID INT,  -- Unique ID field, as integer.
    FieldName2 VARCHAR(255),  -- Variable length (up to 255) string field.
    FieldName3 FLOAT,  -- Float field.
    FieldName4 DATE,  -- Date field (calendar date).
    FieldName5 TIMESTAMP,  -- Timestamp field.
    FieldName6 CHAR(255),  -- Fixed length (exactly 255) string field.
    FieldName7 BOOL  -- Boolean field.
);

Keys

• A use of database management software is to improve organisation.
• Primary keys are the unique fields of rows in a table.
• Foreign keys are references to valid primary keys in another table.

CREATE TABLE TableName2 (
    TableName2ID INT,  -- primary key
    TableNameID INT,  -- foreign key for other table 'TableName'
    PRIMARY KEY (TableName2ID),
    FOREIGN KEY (TableName1ID) REFERENCES TableName (TableNameID)
)

Insert

• Adds a whole new row to a table.

INSERT INTO TableName
   (TableNameID, FieldName1, FieldName2)
VALUES
   (1, 'hello', 12.5);

Update

• Updates certain fields in one or more rows in a table.

UPDATE TableName SET FieldName2=0.5 WHERE FieldName1='hello';

Select

• Obtains the value of certain fields of one or more rows in a table.

-- Selects all fields from all rows.
SELECT * FROM TableName;

-- Selects only FieldName1 from all rows.
SELECT FieldName1 FROM TableName;

-- Selects only FieldName1 from a row with a unique ID of 1.
SELECT FieldName1 FROM TableName WHERE TableNameID = 1;

Delete

• Removes one or more rows from a table.

-- Deletes all rows from table.
DELETE FROM TableName;

-- Deletes multiple rows whose FieldName2 is less than 7.5.
DELETE FROM TableName WHERE FieldName2 < 7.5;

-- Deletes only the row with a unique ID of 1.
DELETE FROM TableName WHERE TableNameID = 1;