Usage of Any DBs

[bitfishxyz]

Depend on MySQL 8.0 、 PostgreSQL 11.0

data types

Attributes of any data types:

• How many bytes ?
• Is it signed ?
• Can it be NULL ?
• Can it be changed ?
• Auto increase ?

default attributes

MySQL	PostgreSQL
UNSIGNED、can be NULL 、can be changed	---

Declaration of data types

MySQL

• Integer
  • Bit(n) : n bits
  • TINYINT: 1 btye
  • SMALLINT: 2 bytes
  • MEDIUMINT: 3 bytes
  • INT: 4 bytes
  • BIGINT: 8 bytes
• No-Integer
  • DECIMAL(m,n):store a number with (m-n)-max-length integer part and n-max-length pointed part.
• String
  • CHAR(n): fixed-width, using this if data's length is stable
  • VARCHAR(n): use one byte or two bytes to store length, using this if data's length is not stable

[bitfishxyz]

Actions

	MySQL	PostgreSQL
Create table	CREATE TABLE table_name (column_name column_type);
Delete table	DROP TABLE table_name ;
Insert	INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
Select	SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M]
Update	UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
Delete	DELETE FROM table_name [WHERE Clause]

[bitfishxyz]

Examples

MySQL

mysql > CREATE TABLE person3(age BIT(3));
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO person3(age) VALUES (7);
Query OK, 1 row affected (0.14 sec)

mysql> INSERT INTO person3(age) VALUES (9);
ERROR 1406 (22001): Data too long for column 'age' at row 1

mysql> UPDATE person3 SET age = NULL WHERE age = 7;
Query OK, 2 rows affected (0.09 sec)

DELETE FROM person3 WHERE age = 3;
Query OK, 1 row affected (0.03 sec)