Postgresql 쿼리 예제

CREATE DATABASE

• create database pawn;

CREATE TABLE

CREATE TABLE books (                                    id integer UNIQUE,                                    title text NOT NULL,                                    author_id integer,                                    subject_id integer,                                    CONSTRAINT books_id_pkey PRIMARY KEY(id)                                 ); UNIQUE : 일반적으로 NULL이 들어가지만 중복되는 값을 넣으려 하면 실패함. NOT NULL : PRIMARY KEY를 적용하면 자동으로 추가됨. NULL을 가질 수 없음. PRIMARY KEY: 이 constraint는 UNIQUE + NOT NULL 을 뜻함.

ALTER

— 컬럼 추가

ALTER TABLE books ADD publication date;

— 컬럼 삭제

ALTER TABLE books DROP publication;

— 컬럼 default값 추가

ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval(‘books_idx’);

— 컬럼 default값 제거

ALTER TABLE books ALTER id DROP DEFAULT;

— 컬럼 NOT NULL 세팅

ALTER TABLE books ALTER COLUMN id SET NOT NULL;

— 컬럼 NOT NULL 제거

ALTER TABLE books ALTER COLUMN id DROP NOT NULL;

— 테이블 이름 변경

ALTER TABLE books RENAME TO literature;

— 컬럼명 변경

ALTER TABLE books RENAME COLUMN in_stock TO is_in_stock;

— 컬럼 데이터 타입 변경

ALTER TABLE books ALTER COLUMN publication TYPE text;

— constraint 추가

ALTER TABLE editions ADD CONSTRAINT foreign_book FOREIGN KEY (book_id) REFERENCES books (id);

ALTER TABLE editions ADD CONSTRAINT hard_or_paper_back CHECK (type=’p’ OR type=’h’);

— constraint 변경 (변경은 없고 DROP -> ADD 해야 함)

ALTER TABLE editions DROP CONSTRAINT editions_type_check;

ALTER TABLE editions ADD CONSTRAINT editions_type_check CHECK (type=ANY(ARRAY[0::smallint, 1::smallint, 2::smallint]));

— 테이블 소유자 변경

ALTER TABLE employees OWNER TO corwin;

 

SELECT

— 오늘날짜까지 지나온 날짜 수

select now() – create_time as days from senior.user;

–월별통계

select count(*) as count, to_char(create_time, ‘YYYY-MM’) as time_cbk

from senior.user

group by to_char(create_time, ‘YYYY-MM’);

— 일별통계

select count(*) as count, to_char(create_time, ‘YYYY-MM-DD’) as time_cbk

from senior.user

where create_time >= ‘2015-06-01’ AND create_time < ‘2015-07-01’

group by to_char(create_time, ‘YYYY-MM-DD’);

— 전체 SELECT

SELECT * FROM books;

— 선택적 SELECT

SELECT id, author_id, title FROM books;

— 표현식 사용 1

SELECT 2+2, pi(), ‘PostgreSQL is more than calculator!’;

— 표현식 사용 2

SELECT 2+2 AS “2 plus 2”, pi AS “the pi function”, ‘PostgreSQL is more than a calculator!’ AS comments;

— 다중 테이블 SELECT

SELECT books.id, title, authors.id, last_name FROM books, authors WHERE books.author_id=authors.id;

— sub-query SELECT

SELECT ‘test’ AS test, id FROM (SELECT * FROM books) AS example_sub_query;

— Aliasing

SELECT b.id, title, a.id, last_name FROM books AS b, authors AS a WHERE b.author_id=a.id;

SELECT the_books_id, title, id, last_name FROM books AS b (the_book_id), authors WHERE author_id=id;

— DISTINCT

SELECT DISTINCT author_id FROM books;

SELECT DISTINCT ON (author_id), author_id, title FROM books;

— WHERE

SELECT * FROM books WHERE subject_id=4;

SELECT title FROM books WHERE subject_id=4 AND author_id=7805;

SELECT title FROM books WHERE subject_id=4 OR subject_id=0;

SELECT * FROM books WHERE author_id=1866 AND subject_id=15 OR subject_id=3;

SELECT * FROM books WHERE author_id=1866 AND (subject_id=15 OR subject_id=3);

— CROSS JOIN

— 아래 2개의 SELECT는 동일한 결과

SELECT b.id, title, a.id, last_name FROM books AS b, authors AS a WHERE b.author_id=a.id;

SELECT b.id, title, a.id, last_name FROM books AS b CROSS JOIN authors AS a WHERE b.author_id=a.id;

— INNER JOIN

— 아래 2개의 SELECT는 동일한 결과

SELECT title, last_name, first_name FROM books, authors WHERE (books.author_id=authors.id) AND last_name=’Geisel’;

SELECT title, last_name, first_name FROM books AS b INNER JOIN authors AS a WHERE last_name=’Geisel’;

— USING

SELECT title, last_name, first_name FROM books INNER JOIN authors AS a (author_id) USING (author_id) WHERE last_name=’Geisel’;

— NATURAL

SELECT title, last_name, first_name FROM books NATURAL INNER JOIN authors AS a (author_id) WHERE last_name=’Geisel’;

— OUTER JOIN

SELECT title, isbn FROM books LEFT OUTER JOIN editions ON (books.id=editions.book_id);

SELECT title, isbn FROM books RIGHT OUTER JOIN editions ON (books.id=editions.book_id);

SELECT title, isbn FROM books FULL OUTER JOIN editions ON (books.id=editions.book_id);

— 다중 INNER JOIN

SELECT a.last_name, p.name AS publisher, e.isbn, s.subject

   FROM ((((authors AS a INNER JOIN books AS b

       ON (a.id = b.author_id))

   INNER JOIN editions AS e ON (e.book_id = b.id))

   INNER JOIN publishers AS p ON (p.id = e.publisher_id))

   INNER JOIN subjects AS s ON (s.id = b.subject_id));

— GROUP BY

SELECT COUNT(e.isbn) AS “number of books”,

            p.name AS publisher

            FROM editions AS e INNER JOIN publishers AS p

                      ON (e.publisher_id = p.id)

            GROUP BY p.name;

— HAVING

SELECT COUNT(e.isbn) AS “number of books”,

            p.name AS publisher

            FROM editions AS e INNER JOIN publishers AS p

                      ON (e.publisher_id = p.id)

            GROUP BY publisher

            HAVING COUNT(e.isbn) > 1;

— ORDER BY

SELECT isbn, edition, publication FROM editions ORDER BY publication ASC;

— ORDER BY (multiple)

SELECT edition, publication FROM editions ORDER BY edition ASC, publication DESC;

— DISTINCT with ORDER BY (아래 두 query의 결과는 같음)

— 1.

SELECT DISTINCT ON (edition) edition, publication FROM editions ORDER BY edition ASC, publication DESC;

— 2.

SELECT edition, max(publication) FROM editions GROUP BY edition;

— LIMIT and OFFSET

— LIMIT

SELECT isbn, title, publication FROM editions NATURAL JOIN books AS b (book_id) ORDER BY publication DESC LIMIT 5;

— LIMIT + OFFSET

SELECT isbn, title, publication FROM editions NATURAL JOIN books AS b (book_id) ORDER BY publication DESC LIMIT 5 OFFSET 2;

— UNION : all non-distinct rows into a single data set

SELECT title FROM books

   UNION

   SELECT last_name FROM authors

   LIMIT 11;

— INTERSECT : any rows not found in both data sets to be omitted

SELECT isbn FROM editions

   INTERSECT

   SELECT isbn FROM shipments

       GROUP BY isbn

       HAVING count(id) > 2;

— EXCEPT : any rows found in both data sets to be omitted from the returned data set

SELECT last_name, first_name

   FROM authors

   EXCEPT

   SELECT last_name, first_name

       FROM authors AS a (author_id)     

       NATURAL INNER JOIN books

   ORDER BY first_name ASC;

— sub-query 결과값 비교

(SELECT title FROM books ORDER BY title DESC LIMIT 7)

   EXCEPT

   (SELECT title FROM books ORDER BY title ASC LIMIT 11)

   ORDER BY title DESC;

— case

SELECT isbn,

   CASE WHEN cost > 20 THEN ‘over $20.00 cost’

            WHEN cost = 20 THEN ‘$20.00 cost’

            ELSE ‘under $20.00 cost’

   END AS cost_range

   FROM stock

   LiMIT 8;

— case with sub-queries

SELECT isbn,

   CASE WHEN cost > 20 THEN ‘N/A – (Out of price range)’

            ELSE (SELECT title FROM books b JOIN editions e

                                                               ON (b.id = e.book_id)

                                                   WHERE e.isbn = stock.isbn)

   END AS cost_range

   FROM stock

   ORDER BY cost_range ASC

   LIMIT 8;

— SELECT INTO

SELECT * INTO stock_backup FROM stock;

 

INSERT

— 일반적 방법

INSERT INTO books (id, title, author_id, subject_id) VALUES (41472, ‘Practical PostgreSQL’, 1212, 4);

— 선택적 INSERT

INSERT INTO books (subject_id, author_id, id, title) VALUES (4, 7805, 41473, ‘Programming Python’);

— 다른 테이블로부터 가져오기

INSERT INTO books (id, title, author_id, subject_id) SELECT nextval(‘books_idx’), title, author_id, subject_id FROM book_queue WHERE approved;

— 파일로부터 복사 (ASCII)

COPY subjects FROM ‘/tmp/subjects.sql’ USING DELIMITERS ‘,’ WITH NULL AS ‘null’;

— 파일로부터 복사 (binary)

COPY BINARY subjects FROM ‘/tmp/subjects.sql’;

— 테이블 정보를 파일로

COPY books TO ‘filename’;

 

UPDATE

— simple UPDATE

UPDATE stock SET retail=25.95 WHERE isbn=’0590445065′;

— updaing entire columns

UPDATE stock SET retail=(cost*((retail/cost)+0.1::numeric));

— updating several columns

UPDATE publishers SET name=’O’Reilly & Associates’, address=’O’Reilly & Associates, Inc. 101 Moriss St, Sebastopol, CA 95472′ WHERE id=113;

— update with several sources

UPDATE stock SET retail=stock_backup.retail FROM stock_backup WHERE stock.isbn=stock_backup.isbn;

 

DELETE

— deleting all table rows

DELETE FROM stock_backup;

— deleting rows

DELETE FROM stock WHERE stock=0;

 

SUB-QUERY

— simple sub-query

SELECT title FROM books WHERE author_id=(SELECT id FROM authors WHERE last_name=’Geisel’ AND first_name=’Theodor Seuss’);

— sub-query using IN

SELECT title FROM books WHERE author_id IN (SELECT id FROM authors WHERE last_name ~ ‘^[A-E]’);

— multi-column sub-query using IN

SELECT isbn, cost, retail FROM stock

   WHERE (isbn, stock)

   IN (SELECT isbn, 0 FROM editions

       WHERE type=’p’);

 

view

— 원본

SELECT COUNT(*) AS num_shipped, MAX(ship_date), title

   FROM shipments

   JOIN editions USING (isbn)

   NATURAL JOIN books AS b (book_id)

   GROUP BY b.title

   ORDER BY num_shipped DESC;

— view 만들기

CREATE VIEW recent_shipments

   AS SELECT count(*) AS num_shipped, MAX(ship_date), title

       FROM shipments

           JOIN editions USING (isbn)

           NATURAL JOIN books AS b (book_id)

           GROUP BY b.title

           ORDER BY num_shipped DESC;

— view 사용

SELECT * FROM recent_shipments;

SELECT * FROM recent_shipments ORDER BY max DESC LIMIT 3;

— view 삭제

DROP VIEW recent_shipments;

 

FOREIGN KEY

CREATE TABLE cities (

   city        varchar(80),

   location  point

);

— weather 테이블의 city는 cities 테이블의 city 컬럼을 참조한다.

CREATE TABLE weather (

   city        varchar(80) references cities (city),

   temp_lo  int,

   temp_hi  int,

   prcp       real,

   date       date

);

— insert 시에 weather.city에 넣으려는 값이 cities.city에 없으면 insert는 실패한다.

— 아래의 경우 만약 cities.city에 Berkeley가 없으면 그 아래의 메세지와 같은 에러 메세지를 출력한다.

INSERT INTO weather VALUES (‘Berkeley’, 45, 53, 0.0, ‘1994-11-28’);

ERROR : <unnamed> referential integrity violation

                – key referenced from weather not found in cities

 

상속

CREATE TABLE cities (name text, population real, altitude int);

— cities를 상속한 capitals라는 테이블을 만듬

CREATE TABLE capitals (state char(2)) INHERITS (cities);

— capitals에 정보를 입력하면 cities에도 추가됨

INSERT INTO capitals (name, population, altitude, state) VALUES (‘Berkely’, 200000, 333, ‘CA’);

— cities와 capitals 모두를 뒤지는 결과를 가져옴

SELECT name, altitude FROM cities WHERE altitude > 500;

— cities만 뒤진다

SELECT name, altitude FROM ONLY cities WHERE altitude > 500;

 

기타

— csv로 덤프

copy (select * from senior.user) to ‘e:/ttt.csv’ with CSV header

— 어제  날짜 얻기

( now() – ‘1 day’::interval)::date

— 백업

mysqldump -h HOST_NAME -u USER_NAME -p DB_NAME > BACKUP_FILE_NAME

ex) mysqldump -h mydb.mydomain.com -u my_id -p my_dbname > C:my_db.sql

— 복원

mysql -h HOST_NAME -u USER_NAME -p DB_NAME < BACKUP_FILE_NAME

ex) mysql -h mydb.mydomain.com -u my_id -p my_dbname < C:my_db.sql

 

참조 사이트

• http://bloodguy.tistory.com/category/DataBase?page=10