mysql cheatsheet

http://www.mysqltutorial.org/mysql-cheat-sheet.aspx
https://dzone.com/refcardz/essential-postgresql
https://dzone.com/refcardz/essential-mysql

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value]

[AUTO_INCREMENT]
If you don’t declare the storage engine explicitly, MySQL will use InnoDB by default.

InnoDB became the default storage engine since MySQL version 5.5. The InnoDB table type brings many benefits of relational database management system such as ACID transaction, referential integrity, and crash recovery. In previous versions, MySQL used MyISAM as the default storage engine.
PRIMARY KEY (col1,col2,...)

Home / PostgreSQL Tutorial / PostgreSQL CREATE TABLE

PostgreSQL CREATE TABLE

?

 Summary: in this tutorial, you will learn how to use the PostgreSQL CREATE TABLE statement to create new tables.

PostgreSQL CREATE TABLE syntax

To create a new table in PostgreSQL, you use the CREATE TABLE statement. The following illustrates the syntax of the CREATE TABLE statement:

1 2 3 4

CREATE TABLE table_name ( column_name TYPE column_constraint, table_constraint table_constraint ) INHERITS existing_table_name;

Let’s examine the syntax of the CREATE TABLE statement  in more detail.

• First, you specify the name of the new table after the CREATE TABLE clause. The TEMPORARYkeyword is for creating a temporary table, which we will discuss in the temporary table tutorial.
• Next, you list the column name, its data type, and column constraint. You can have multiple columns in a table, each column is separated by a comma (,). The column constraint defines the rules for the column e.g.,  NOT NULL.
• Then, after the column list, you define a table-level constraint that defines rules for the data in the table.
• After that, you specify an existing table from which the new table inherits. It means the new table contains all columns of the existing table and the columns defined in the CREATE TABLEstatement. This is a PostgreSQL’s extension to SQL.

PostgreSQL column constraints

The following are the commonly used column constraints in PostgreSQL:

• NOT NULL – the value of the column cannot be NULL.
• UNIQUE – the value of the column must be unique across the whole table. However, the column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.
• PRIMARY KEY – this constraint is the combination of NOT NULL and UNIQUE constraints. You can define one column as PRIMARY KEY by using column-level constraint. In case the primary key contains multiple columns, you must use the table-level constraint.
• CHECK – enables to check a condition when you insert or update data. For example, the values in the price column of the product table must be positive values.
• REFERENCES – constrains the value of the column that exists in a column in another table. You use REFERENCES to define the foreign key constraint.