SQL Komutları

Giriş

SQL komutları şöyle gruplanabilir

1. DDL – Data Definition Language

2. DQL – Data Query Language

3. DML – Data Manipulation Language

4. DCL – Data Control Language

Şeklen şöyle

INFORMATION_SCHEMA.COLUMNS Sistem Tablosu

Giriş

Açıklaması şöyle

It's a part of the SQL-92 standard, and it's implemented by most major database engines (with the notable exception of Oracle).

data_type Sütunu

Örnek

Eğer OID tipindeki sütunları görmek istersek şöyle yaparız

SELECT * FROM information_schema.columns WHERE data_type = 'oid';

table_name Sütunu

Örnek

Şöyle yaparız

SELECT column_name, column_external_name, ordinal_position, is_nullable, data_type
FROM information_schema.columns WHERE table_name='...'

CREATE INDEX - Partial Index

Giriş

Açıklaması şöyle

If you know that your query will always ignore columns with defined values, you can make use of a partial index to save space and make it faster.

Örnek - Equals

Şöyle yaparız

CREATE INDEX app_user_address_is_main ON app_user_address (user_id)
    WHERE is_main = TRUE;

Açıklaması şöyle

In this query, you’ll be creating an index that only indexes rows if the column is_main equals TRUE.

Örnek - Equals

Şöyle yaparız

CREATE INDEX balances_total_balance_nan_idx ON balances_snapshots ((true))
WHERE total_balance = 'NaN';

Örnek - B- Tree Index + Equals

Şöyle yaparız

CREATE INDEX id_btree ON sample_data USING BTREE(id) WHERE id = 200000;

Örnek - Not Equals

Şöyle yaparız

CREATE INDEX ON f (parent_sha256) WHERE parent_sha256 <> sha256;

CREATE UNIQUE INDEX

Burada CREATE INDEX yerine CREATE UNIQUE index kullanılıyor

Örnek

Şöyle yaparız

CREATE UNIQUE INDEX ON users (user_id)

Örnek

Şöyle yaparız. 

CREATE UNIQUE INDEX event_unique_ower_uqx    -- index name
    ON mytable (event_id) 
    WHERE (status = 'owner') ;

JDBC PGSimpleDataSource Sınıfı

Giriş

Şu satırı dahil ederiz

import org.postgresql.ds.PGSimpleDataSource;

PGXADataSource yazısına bakılabilir

Açıklaması şöyle

The PGSimpleDataSource, which is the default DataSource implementation in PostgreSQL, uses the underlying Driver to acquire a physical connection which establishes a TCP connection to the database server.
And when the close method is called on the JDBC Connection object, the underlying Socket and TCP connection are terminated.

Maven

Şöyle yaparız.

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.4-1200-jdbc4</version>
</dependency>

constructor

Örnek

şöyle yaparız

PGSimpleDataSource dataSource = new PGSimpleDataSource();
dataSource.setDatabaseName("high_performance_java_persistence");
dataSource.setServerName("localhost");
dataSource.setUser("postgres");
dataSource.setPassword("admin");

Örnek

Şöyle yaparız

import org.postgresql.ds.common.BaseDataSource;

PostgreSQLContainer container = ...;

BaseDataSource dataSource = new PGSimpleDataSource();
dataSource.setUrl(container.getJdbcUrl());
dataSource.setUser(container.getUsername());
dataSource.setPassword(container.getPassword());
dataSource.setDatabaseName(container.getDatabaseName());

Örnek

Şöyle yaparız

import org.postgresql.ds.PGSimpleDataSource;
import org.postgresql.ds.common.BaseDataSource;
import org.testcontainers.containers.PostgreSQLContainer;

import javax.sql.CommonDataSource;
import javax.sql.DataSource;

// PGSimpleDataSource is both javax.sql.DataSource and javax.sql.CommonDataSource
CommonDataSource createDataSource() {
  PostgreSQLContainer postgreSQLContainer = ...;

  BaseDataSource dataSource = new PGSimpleDataSource();
  dataSource.setUrl(postgreSQLContainer.getJdbcUrl());
  dataSource.setUser(postgreSQLContainer.getUsername());
  dataSource.setPassword(postgreSQLContainer.getPassword());
  dataSource.setDatabaseName(postgreSQLContainer.getDatabaseName());

  return dataSource;
}

CREATE INDEX - Balanced Tree (B-Tree)

Balanced Tree (B-Tree)

Açıklaması şöyle

The B-Tree index type uses a balanced tree structure to speed up equality and range queries on columns of all data types. Since B-Tree index entries are sorted, they are sometimes used to retrieve table rows in order, thereby avoiding manually sorting them after retrieval.

This is the default index type and also the most commonly used.

B+ Tree vs B Tree

Açıklaması şöyle. Yani B Tree dense bile aslında PostgreSQL B+ Tree kullanıyor

Postgresql has an index with the type “B-tree” which is implemented based on B-tree. More specifically, on one of its variations — B-tree+ (keys to rows are stored only in leaf nodes).

Açıklaması şöyle

The most common index used in a relational database system is the B+ Tree one. Like the B-Tree index, the B+ Tree is a self-balanced ordered tree data structure.

Both the B-Tree and the B+Tree start from a Root node and may have Internal Nodes and Leaf Nodes. However, unlike the B-Tree, the B+ Tree stores all the keys in the leaf nodes, and the adjacent Leaf nodes are linked via pointers, which simplifies range scans.

Without an index, whenever we are looking for a given column value, we’d need to scan all the table records and compare each column value against the provided one. The larger the table, the more pages will have to be scanned in order to find all the matching records.

On the other hand, if the column value is highly selective (e.g., a small number of records match that column value), using a B+Tree index allows us to locate a column value much faster since fewer pages will be needed to be scanned.

Sorted Olması

Açıklaması şöyle

Your Postgres table was fast for months. Then one week, inserts that took 1ms started taking 10ms, autovacuum couldn't keep up, and nothing in your code changed.

Here's the part most people get wrong about why: it's not because "Postgres doesn't scale." It's because Postgres's indexes are B-Trees, and every B-Tree index has to stay sorted, on every single write. Two indexes on a table means one cheap heap append plus two separate random writes, every insert. Update a row (thanks to MVCC), and you pay that same cost again, plus a dead tuple autovacuum has to clean up later.

Cassandra and friends made the opposite bet: an LSM-Tree engine that never sorts anything at write time; it just appends and sorts later in the background (compaction). Writes stay fast forever. The cost shows up on reads instead.

Neither engine is "better." They're both just honouring a trade they made before you wrote a single query. I wrote up the mechanics, the actual production symptoms each one causes, and when "just switch databases" is the wrong fix.

Page Büyüklüğü

Açıklaması şöyle. 8 KB büyüklüğünde. Bu da büyük bir M sayısının oluşmasına sebep oluyor

As mentioned in the post, each node is located on one block (page in terms of PostgreSQL, usually 8KB). In practice, a node contains a lot (M = hundreds) keys. As a result, the depth (number of levels) of the B-Tree is quite small, about 4–5 for very large tables. Just imagine, B-tree allows us to get a needed key by using only 4–5 disk read operations.

Şeklen şöyle. Burada M = 3 ve row bilgisi son yaprak ta saklanıyor.

Search bu equality şeklen şöyle

Search by range şeklen şöyle. N>=11 olan tüm değerleri arıyoruz. Arama hep sağa doğru gidiyor

Kullanım

Örnek
Şöyle yaparız.

CREATE INDEX idx ON adsets (date, platform);

Örnek
Şöyle yaparız. Burada USING BTREE ile indeksin tipi belirtiliyor. Normalde gerekli değil

CREATE INDEX id_idx ON fake_data USING BTREE(id);

Bu index aynı zamanda pattern_ops ile de kullanılabilir. 

- text_pattern_ops : text için

- varchar_pattern_ops : varchar için

- bpchar_pattern_ops : char için

değerlerini alabilir.

PostgreSQL'de Primary Index Yoktur

MySQL'de secondary index satırın tüm verisini içeren bir primary index'e işaret eder. PostgreSQL böyle değil. Direkt heap'teki bir yere işaret eder. Şeklen şöyle. 

SQL DISTINCT

Giriş

Açıklaması şöyle

- SQL Distinct statement returns distinct values for a given column.

- SQL Distinct returns distinct combination of columns when used with multiple columns.

Örnek

Şeklen şöyle. Burada DISTINCT iki tane sütunla kullanıldığı için bunların kombinasyonunu aldığı görülebilir.

Örnek

Elimizde şöyle bir tablo olsun

Emp_id Dept_id Job_id
1      24      117
2      24      117
3      24      118
4      25      117

Bu tabloyu şöyle sorgulayalım

SELECT Dept_id, Job_id FROM Employees

Şu sonucu alırız.

24 117
24 117
24 118
25 117

24 ile başlayan satırların çift olduğu görülebilir. Bunlardan kurtulmak için şöyle yaparız.

SELECT DISTINCT Dept_id, Job_id FROM Employees

Bu durumda şu sonucu alırız.

24 117
24 118
25 117




INNER JOIN USING

Giriş

Açıklaması şöyle

The USING clause works for Oracle, PostgreSQL, MySQL, and MariaDB. SQL Server doesn’t support the USING clause, so you need to use the ON clause instead.

Örnek

Şöyle yaparız

SELECT * FROM post
  INNER JOIN post_comment USING(post_id)
  ORDER BY post_id, post_comment_id

Çıktısı şöyle

| post_id | title     | post_comment_id | review    |
|---------|-----------|-----------------|-----------|
| 1       | Java      | 1               | Good      |
| 1       | Java      | 2               | Excellent |
| 2       | Hibernate | 3               | Awesome   |

Eğer INNER JOIN ON kullansaydık şöyle yaparız

SELECT * FROM post
  INNER JOIN post_comment ON post.post_id = post_comment.post_id
  ORDER BY post.post_id, post_comment_id

Çıktısı şöyle. İki tane post_id sütunu geldi. Birisi pos tablosundan, diğeri de post_comment tablosundan

| post_id | title     | post_comment_id | review    | post_id |
|---------|-----------|-----------------|-----------|---------|
| 1       | Java      | 1               | Good      | 1       |
| 1       | Java      | 2               | Excellent | 1       |
| 2       | Hibernate | 3               | Awesome   | 2       |