MySQL — Data Type and Basic SQL Statements

Data Types

  • Numeric
  • Date and Time
  • String

Numeric

  • Integer
  • Fixed-Point
salary DECIMAL(5, 2)
  • Floating-Point, can use either FLOAT or DOUBLE
  • Bit-Value

Date and Time

  • where type_name is TIME, DATETIME, TIMESTAMP
  • fsp range is 0 to 6 (seconds digit precision), default 0
  • 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

  • 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

SQL Statements

  • 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

  • INSERT

INSERT INTO `profile` VALUES (
DEFAULT,
'username',
'my bio',
'this is me',
20,
80.19,
175.2,
NULL,
'1990–12–01',
DEFAULT
);
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

Reference

A software engineer

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

KafkaFix

How to Get Your Team to Estimate Better

Learning to code at 30: the stuff people don’t talk about.

Tester’s Dictionary: “F” for “Flaky Tests”

CS373 Spring 2022: Jonathan Li

Top Subjects to Study for Interviews of IT Jobs in 2020

Different types of engineering files

Install Kafka Cluster and Zookeeper with High Availability

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store