MySQL — Data Type and Basic SQL Statements

4 min readMay 11, 2021

Will cover aspects like basic columns data type and SQL statements

I will be using MySQL running on docker container. If you haven’t installed MySQL and wanted to use it with docker, can check this.

Data Types

This post only cover some basic MySQL data types, such as:

Numeric
Date and Time
String

There are other data types like spatial and JSON data type.

Numeric

Consists of Integer, Fixed-Point, Floating-Point and Bit-Value types.

Integer

Integer type can be SIGNED or UNSIGNED (default is SIGNED )

TINYINT : range from -128 — 127 or 0 — 255

BOOL , BOOLEAN , TINYINT(1) : TRUE is alias for 1 and FALSE is alias for 0

SMALLINT: range from -32,768 — 32,767 or 0 — 65,535 (16 bits)

MEDIUMINT: range from -8,388,608 — 8,388,607 or 0 — 16,777,215 (24 bits)

INT: range from -2,147,483,648 — 2,147,483,647 or 0 — 4,294,967,295 (32 bits)

BIGINT: range from -9,223,372,036,854,775,808 — 9,223,372,036,854,775,807 or 0 — 18,446,744,073,709,551,615 (64 bits). SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

Fixed-Point

This type is for representing a number with specific precision, for example monetary data. Example:

salary DECIMAL(5, 2)

Meaning that salary column has range -999.99 — 999.99

Floating-Point, can use either FLOAT or DOUBLE
Bit-Value

This type is used to store bit values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Date and Time

There is type_name(fsp)

where type_name is TIME, DATETIME, TIMESTAMP
fsp range is 0 to 6 (seconds digit precision), default 0

DATE: YYYY-MM-DD format, 1000–01–01 to 9999–12–31

DATETIME: YYYY-MM-DD hh:mm:ss[.fraction] format, 1000–01–01 00:00:00.000000 to 9999–12–31 23:59:59.999999

TIMESTAMP: 1970–01–01 00:00:01.000000 UTC to 2038–01–19 03:14:07.999999 UTC

TIME: hh:mm:ss[.fraction] format, -838:59:59.000000 to 838:59:59.000000

YEAR: YYYY format

Can auto initialize to current timestamp
- For TIMESTAMP and DATETIME
- For create, DEFAULT CURRENT_TIMESTAMP
- For update, ON UPDATE CURRENT_TIMESTAMP
- Synonyms, CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP()

String

Such as CHAR, VARCHAR, TEXT, ENUM, SET and can specify column collation

c1 VARCHAR(20) CHARACTER SET utf8: create column c1 with character set of utf8
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs: create column c2 with character set of latin1 and case sensitive
CHARACTER SET has synonym CHARSET
ASCII has synonym CHARACTER SET latin1
UNICODE has synonym CHARACTER SET ucs2
CHARSET binary makes CHAR to BINARY , VARCHAR to VARBINARY and TEXT to BLOB

CHAR : fixed-length string, right-padded with spaces default length is 1, can be 0 to 255

VARCHAR : variable-length string, length can be 0 to 65,535

TEXT : maximum length is 65,535

BINARY : similar to char but store binary string

VARBINARY: similar to varchar but store binary string

BLOB : maximum length is 65,535 binary string

SQL Statements

There are several SQL statements, but in this post will only describe about:

Data Definition Statements, operations like create, modify/alter, drop table, etc.
Data Manipulation Statements, operations like insert, update delete, select, etc.

Data Definition Statements

Table Creation
Backtick (`) is for escaping reserved word. Let say you want table with name transaction , since it is reserved word can’t create without the backtick.

CREATE TABLE `profile` (
  `id` SERIAL,
  `username` CHAR(30) CHARSET latin1 COLLATE latin1_general_cs UNIQUE,
  `bio` VARCHAR(50) CHARSET utf8,
  `about_me` TEXT NOT NULL,
  `age` INT UNSIGNED,
  `weight` DECIMAL(4, 2),
  `height` DECIMAL(5, 2),
  `bmi` FLOAT,
  `birth_date` DATE,
  `created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

Alter Table
Can add new column at certain position (after birth_date column).
Can rename existing column.
Can modify column data type.
Also, can drop a column.

ALTER TABLE `profile` ADD COLUMN `new_column` INT NOT NULL DEFAULT 0 AFTER `birth_date`;
ALTER TABLE `profile` RENAME COLUMN `new_column` TO `new_column_2`;
ALTER TABLE `profile` MODIFY `new_column_2` INT UNSIGNED;
ALTER TABLE `profile` DROP COLUMN `new_column_2`;

Drop Table

DROP TABLE `profile`;

Other Useful Commands

SHOW TABLES;            # show all tables of current used database
DESCRIBE `profile`;     # show table details

Data Manipulation Statements

Consists of operations like insert, update, select and update rows of a table.

INSERT

Can insert without specify the columns, but need to be ordered like in table column definition.


INSERT INTO `profile` VALUES (
 DEFAULT,
 'username',
 'my bio',
 'this is me',
 20,
 80.19,
 175.2,
 NULL,
 '1990–12–01',
 DEFAULT
);

Can also insert with specific column

INSERT INTO `profile` (`about_me`, `username`) VALUES (
 'this is about me',
 'username2'
);

SELECT

SELECT * FROM `profile`;               # select all columns
SELECT id, username FROM `profile`;    # select specific columns

UPDATE

UPDATE `profile` SET `about_me` = '';  # update all rows
UPDATE `profile`
SET `about_me` = 'this is me'
WHERE id = 2;                          # update row with id = 2

DELETE

DELETE FROM `profile` WHERE id = 2;  # delete row with id = 2
DELETE FROM `profile`;               # delete all rows
TRUNCATE TABLE `profile`;            # delete all rows too

That’s all about some basics in MySQL, see you again in another MySQL series.

Reference

https://dev.mysql.com/doc/