Database notes: PostgreSql and SQL language

Notes Symbol List:

" * ": important knowledge notification
" $ ": means the instruction with command line
" R " : the resources
" ! " : pay attention to

Part 1: psql cml

install postgresql app and adapt configuration

*After download the postgresql by app or by brew, you need to configure the psql cml by using .zshrc file.

*.zshrc file is a hidden file which located on the home directory and automatically execute when you open the terminal.

$ You could use the “ls -a” to display the file .zshrc on terminal. Then using the editor, eg: vi, emacs, vim, to open the file and add command line:

" export PATH:$PATH:/…/…/bin " into the .zshrc file.
Then using the cml " source .zshrc " to activate the .zshrc file.

psql command line and sql command line

1. psql: open the psql on terminal
   help: list all command line for different help

2. \ ? (no space) : help of the psql cml

3. \l :list all database on the terminal

4. \d: list all relations on the terminal (all tables and other information)
   \d table_name : display all columns on the terminal

5. \dt: list relations which only includes tables

6. \i: execute the file (sql file) $: \i /path/example.sql

7. how to connect to a database
   with two ways:
   a. using cml \c database name (within the psql)
   b. using the cml psql [option] database_name (on the terminal)

Part 2: SQL Language

syntax learning

(add semicolon when you’re using the terminal)

• How to create a databse:
  CREATE DATABASE database_name;
• How to create a table:

CREATE TABLE table_name (
id BIGSERIAL [option] (eg: NOT NULL PRIMARY KEY),
variable_name DATATYPE [option] (eg: NOT NULL)
);

• How to drop a database or a table:
  DROP DATABASE database_name; ! : not use it if nessecary
  DROP TABLE table_name;

• How to insert data into table:
  INSERT INTO table_name (
  variable name,
  variable name
  )
  VALUES (‘A’,‘B’);
  

• SELECT [option] FROM table_name;
  This line will list all contents (contents of option)

• ORDER BY variable_name [option] (default is ASC, you also can spesify DESC)

• WHERE*
  This syntax could add condition to filter the data

data type

• CHAR, VARCAHR and TEXT

CHAR(n): char(n) is used to store limited characters. If n is not defined, the default for char(n) is char(n).

VARCHAR(n): varchar(n) is used to store limited characters. If n is not defined, the default for varchar(n) is unlimited length.

TEXT: text is used to store unlimited characters

*The performance of varchar(n) (without specifying n) and text is same.

Resources

R: a generator web link