Skip to main content

Sql

Postgres​

Metacommands​

psql that begins with an unquoted backslash \ is a psql meta-command

\d table_name
\dt table

Alter​

ALTER TABLE table_name
    ALTER COLUMN column_name datatype;

Foreign Key​

CREATE TABLE table1_name(id int primary key,
    f_id int REFERENCE table2_name(id));

CREATE TABLE table1_name(id int primary key,
    f_id int,
    constraint fk_table1_table2
        foreign key (f_id)
        REFERENCES table2_name(id)
    );

ALTER TABLE table table1_name
    ADD constraint fk_table1_table2
    foreign key f_id
    REFERENCES table1_name(id);

Rename​

Table​

ALTER TABLE from_table_name
    RENAME TO to_table_name;

Aggregations​

Count​

The following outputs the same result and semantically are same:

  1. SELECT COUNT(login) FROM student WHERE login LIKE '%@avverma.com'
  2. SELECT COUNT(*) FROM student WHERE login LIKE '%@avverma.com'
  3. SELECT COUNT(1) FROM student WHERE login LIKE '%@avverma.com'

Although the same results, COUNT(1) has better performance because:

  1. COUNT(login) - Copies the login attributes of each tuple and performs sum.
  2. COUNT(*) - Copies the all attributes of each tuple and performs sum.
  3. COUNT(1) - Increments by 1 for each tuple.

Spring Boot + Flyway​

Dependencies (start.spring.io)​

  1. Spring Data JPA
  2. PostgreSQL Driver
  3. Flyway Migration

Migration File​

Create V1__Init_Schemas.sql under resources/db/migration Create database in postgres. Set url, username, port in properties file

spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/instrumentdb
spring.datasource.username=postgres
spring.datasource.password=postgres

# Default database within postgres
spring.datasource.url=jdbc:postgresql://127.0.0.1:5432
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.flyway.schemas=instrument

That's it, Spring Auto Configuration upon detecting postgres drivers, Flyway etc. will apply the migrations automatically on boot.

Associations​

Association TypeUnidirectional ExampleUnidirectional(Non Foreign key Owner)Bidirectional ExampleRemark
OneToOne
class Address {
@OneToOne()
 @JoinColumn(id="fk_user_id")
 User user; 
}
class Person {
 @OneToOne(mappedBy="user") 
 @JoinColumn(id="fk_address_id") 
 Address address; 
}
@JoinColumn is an optional override if the foreign is not named user_id by default
OneToMany
class Post { 
 @ManyToOne 
 User user;
}
class User { 
 @OneToMany 
 @JoinColumn("user_id") 
 List<Post> posts = new ArrayList<>();
}
class User { 
 @OneToMany(mappedBy="user") 
 List<Post> posts = new ArrayList<>();
}
ManyToMany
class Customer { 
 @ManyToMany 
 Set<Order> orders; 
}
class Order { 
 @ManyToMany(mappedBy="orders") 
 Set<Customer> customer;
}
There exists a relation table. We can create an Entity for the relation table too if there are extra attributes

Postgres Terminal​

Start the service: systemctl status postgresql.service

psql REPL:​

sudo -u postgres psql

Create db: CREATE DATABASE <db_name>;

Change db: \connect dbname or \c dbname

List db: \list or \l

List Schema: \dn

Select Schema:

SHOW search_path
SET search_path TO <schema_name>

Self Referential​

An employee table with a manager column that points back to itself.

postgres=# SELECT * from employee;
 id |     name      | manager
----+---------------+---------
  1 | Gandalf       |
  2 | Legolas       |       1
  3 | Aragorn       |       1
  5 | Elrond        |       2
  6 | Bilbo Baggins |
  7 | Frodo Baggins |       6
  8 | Sammy Sam     |       7

Get the list of employees and their managers

postgres=# SELECT e.name, manager.name
FROM employee e
LEFT JOIN employee manager
ON e.manager = manager.id;
     name      |     name
---------------+---------------
 Gandalf       |
 Legolas       | Gandalf
 Aragorn       | Gandalf
 Elrond        | Legolas
 Bilbo Baggins |
 Frodo Baggins | Bilbo Baggins
 Sammy Sam     | Frodo Baggins

MySQL​

Connect to remote machine's MySQL​

mysql -u root -h docker.hackthebox.eu -P 3306 -p

List Database​

SHOW databases
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
# SCHEMA_NAME column of SCHEMATA table within INFORMATION_SCHEMA database has the list of all the databases

Select Database​

USE <database_name>

List tables​

SELECT TABLE_NAME,TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES
# TABLES within INFORMATION_SCHEMA database has the list of all the tables
# TABLE_SCHEMA is the database name to which the table belongs to

List Columns​

select COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA from INFORMATION_SCHEMA.COLUMNS where table_name=<table_name>
# COLUMNS within INFORMATION_SCHEMA database has the list of all columns for a table

Schema of Table​

DESCRIBE <table_name>

User privileges​

SELECT grantee, privilege_type FROM information_schema.user_privileges WHERE user="root"

File​

SELECT LOAD_FILE("/var/www/html/search.php")

Global Variables​

SELECT variable_name, variable_value FROM information_schema.global_variables where variable_name="secure_file_priv"