Common Syntax

Table - SELECT

SELECT * FROM [table_name]
WHERE [condition]
GROUP BY [column]
HAVING [condition]
ORDER BY [column] ASC|DESC;

SELECT * FROM [table_name];
SELECT * FROM [table_name] ORDER BY [column];
SELECT [column1], [column2], ... FROM [table_name];
SELECT [column] FROM [table_name];
SELECT DISTINCT [column] FROM [table_name];
SELECT COUNT(DISTINCT [column]) FROM [table_name];
SELECT * FROM [column] WHERE [condition];
                              condition : [column][operator][value]
                              column = 'value'   /* equal */
                              column > 'value'   /* greater than */
                              column < 'value'   /* less than */
                              column >= 'value'  /* greater than or equal */
                              column <= 'value'  /* less than or equal */
                              column <> 'value'  /* not equal */
                              column != 'value'  /* not equal */
                              column BETWEEN 'value1' AND 'value2'        
                              column LIKE 'pattern'	
                              column IN ('value1', 'value2', ...)
                              column1 = 'value1' AND column2 = 'value2' AND ...
                              column1 = 'value1' OR column2 = 'value2' OR ...
                              NOT column = 'value'
                              IS NULL;
                              IS NOT NULL;


Table - CREATE

CREATE TABLE [table_name] (
    [column1] [datatype],
    [column2] [datatype],
   ....
);

CREATE TABLE [new_table_name] AS
    SELECT [column1], [column2], ...
    FROM [existing_table_name]
    WHERE [condition];


Table - DROP

DROP TABLE [table_name];


Table - ALTER

ALTER TABLE [table_name]
ADD [column_name] [datatype];


Table - INSERT

INSERT INTO [table_name] ([column1], [column2], ...)
VALUES ( 'value1',  'value2', ...);


Table - UPDATE

UPDATE [table_name]
SET [column1] = 'value1', [column2]= 'value2', ...
WHERE [condition];


Table - DELETE

DELETE FROM [table_name]
WHERE [condition];


Database - CREATE

CREATE DATABASE [databasename];


Database - BACKUP

BACKUP DATABASE [databasename]
TO DISK = 'filepath';


Database - DROP

DROP DATABASE [databasename];


–





Database Management System

Data Quality

Data Integrity

  • Entity Integrity
  • Referential Integrity : Bijection
  • Domain Integrity

Relational DBMS

Postgre

Server On/Off
Windows 
pg_ctl -D "C:\Program Files\PostgreSQL\[version]\data" [start|stop|restart]
Linux : /etc/postgresql/[version]/main/postgresql.conf 
$ sudo service [start|stop|restart] postgresql
macOS 
$ brew service [start|stop|restart] postgresql


Access
DB Access
$ sudo -iu postgresql
$ psql


Object-Oriented DBMS

NoSQL and NewSQL DBMS

Development Environment

DBeaver

Shortcuts

ctrl + enter
ctrl + shift + enter


pgAdmin4

pgAdmin4 configuration
Windows : %CommonProgramFiles%\pgadmin\config_system.py
Linux : /etc/pgadmin/config_system.py
macOS : /Library/Preferences/pgadmin/config_system.py


　

　

　

Reference
  1. Postgre : https://tableplus.com/blog/2018/10/how-to-start-stop-restart-postgresql-server.html
  2. Postgre : https://www.pgadmin.org/download/pgadmin-4-python/
Related Articles
Edit