DB_ psql 쉘 사용 : 명령어

>>psql?

데이터베이스 관리시스템(DBMS)

postgreSQL (기기, 장치) → psql쉘(리모콘 프로토콜) → 사용자

 

<psql 쉘 실행>

1.  SQL Shell 에서 실행하기

 'psql -U postgres를 입력하고, 미리 설정한 암호를 입력하면 psql쉘을 실행할 수 있다.

이걸로 켜도 되고

비밀번호&amp;nbsp; postgres

2.  cmd 에서 실행하기

psql -U postgres

//비번입력 엔터 //나가려면 q

\l

 

---

## 오류 

https://m.blog.naver.com/ilsan_ilsan/221493158294

C:\windows\system32 추가해주기

 

 

---

<명령어>

\l //데이터베이스 리스트보기? \c 경로이름 //그 데이터 베이스 경로로 이동 \dt 테이블 리스트보기

데이터베이스 생성

CREATE DATABASE DB_name;
CREATE DATABASE testDB

데이터베이스 삭제

DROP DATABASE 이름;

테이블 생성

CREATE TABLE 새 테이블 이름(칼럼명1 데이터타입, 칼럼명2 데이터타입, 칼럼명3 데이터타입...);
CREATE TABLE develop_book(
book_id INTERGER,
date DATE
, name VARCHAR(80)
);

//book_id -> 칼럼명
//INTERGER -> 자료형 int
//data ->칼럼명
//DATE -> 자료형
//name 칼럼명
//VARCHAR(80) 자료형 (크기)

테이블 조회

SELECT칼럼명FROM 테이블명
SELECT*FROM table_name;

* 는 전체를 의미한다

 

일부 조회

SELECT*FROM table_name LIMIT 2 OFFSET 1;

LIMIT n개까지만 보여준다.
OFFSET은 n번부터 보여준다.

 

테이블 삭제

DROP TABLE prod_info;

테이블 수정

ALTER TABLE prod_info RENAME TO ord_info;
prod_info에서 ord_info로 수정하겠다.

 

 

 

>>> CRUD

INSERT INTO (테이블에 칼럼 만들기 ——줄row)

INSERT INTO develop_book VALUES(1,'2019-12-17','mongoDB');
//1 id int
//2019-12-17 ''문자열  날짜
//mongoDB 문자열 이름
//테이블에 정보 입력하는 과정이다.
INSERT INTO develop_book VALUES(2,'2019-12-25','"자바"');

//순서모를떄 순서 지정하려면 데이터의 이름 먼저 괄호에 넣고 그다음 벨류 (데이터)
INSERT INTO develop_book (book_id,date,name) VALUES(3,'2020-01-03','HTML/CSS');


//혹은 한번에 insert 하기
INSERT INTO develop_book VALUES
(7,'2020-03-01','jAVA'),
(8,'2020-03-11','KK'),
(9,'2020-04-03','"HBD"');

'문자를 입력하고 싶을 때는 '' 로 두번 '를 입력한다.

 

SELECT (data 보기)

LMIT 1;  반환하는 로우의 개수를 지정
OFFSET 1; 반환하는 로우의 시작시점지정
ORDER BY date ASC; 반환하는 로우의 작은수 부터 정렬할떄 (default):오름차순
ORDER BY date DESC; 반환하는 로우의 큰수 부터 정렬할떄 :내림차순
WHERE 반환하는 로우의  지정한 로우만 조회되도록 필터기능

**book_store-#  //지금 어디 데이터 베이스에 들어와있는지 중요하다.**
SELECT* FROM develop_book
LIMIT5;

book_store=# SELECT* FROM develop_book
book_store-# LIMIT 6
book_store-# OFFSET 1; //시작 지점 지정해주기(index 0부터 시작)

book_store=# SELECT* FROM develop_book
book_store-# ORDER BY book_id ASC;

SELECT date,name FROM develop_book
ORDER BY 2,1;

=

<> 서로다르다

≥ >이거랑=

SELECT * FROM develop_book
WHERE book_id = 1;

SELECT * FROM develop_book
WHERE book_id <> 1;

SELECT * FROM develop_book
WHERE '2020-01-03' IN (
SELECT date FROM develop_book
WHERE book_id =3
);
//서브쿼리

book_store=# SELECT * FROM develop_book
book_store-# WHERE date = '2020-01-03';
//boolean

서브쿼리란?

하나의 쿼리문 안에 포함되어 있는 또 하나의 쿼리문을 말합니다.
서브쿼리는 메인쿼리가 서브쿼리를 포함하는 종속적인 관계입니다.
여러번의 쿼리를 수행해야만 얻을 수 있는 결과를
하나의 중첩된 SQL문장으로 간편하게 결과를 얻을 수 있게 해줍니다.
쉽게 말해서 쿼리문안에 쿼리문을 또 쓰는 것

 

수정하기 UPDATE

UPDATE develop_book
SET name = 'java'
WHERE book_id =2
RETURNING *;

UPDATE develop_book
SET book_id = 4
WHERE name = 'HTML/CS'
RETURNING *;

UPDATE develop_book
SET name = 'HBD'
WHERE book_id = 9
RETURNING *;

00 AS 01 : 00을 01로 바꾸겠다.

SELECT name AS title FROM develop_book;

CREATE TABLE develop_book_2 AS
SELECT * FROM develop_book
ORDER BY book_id ASC;

//순서 유일한 방법 order by

DROP TABLE develop_book;
//drop = 삭제

ALTER TABLE develop_book_2
RENAME TO develop_book;

//테이블 이름 수정

\\dt 
//테이블 조회 . 확인.

테이블 row 삭제하기

DELETE FROM develp_book WHERE book_id =6;

테이블의 상세 정보조회

\\d book_info

 

>>>>유니크 레퍼런스 pk fk 

drop table if exists contact_info;

create tabele contact_info(
cont_id numeric(3) UNIQUE not null
name varchar(15) not null,
tel integer[] not null,
email varchar
)

INSERT INTO contact_info (name, tel, email) VALUES
('name',Array[1,2],'example@naver.com'),
('name',Array[1,2],'example@naver.com'),
('name',Array[1,2],'example@naver.com')

SERIAL primary key

drop table if exists contact_info;

create table contact_info(
cont_id SERIAL primary key,
name varchar(15) not null,
tel integer[] not null,
email varchar
)

DELETE FROM contact_info WHERE cont_id =1;

SELECT * FROM contact_info;

UNIQUE

NOT NULL

과목 : 부모 테이블

CREATE TABLE subj(
	sub_id NUMERIC(5) UNIQUE  NOT NULL, // not null primary key
	sub_name VARCHAR(20) NOT NULL
)

INSERT INTO subj
VALUES 
(00001, 'mathematics'),
(00002, 'science'),
(00003,'programming');

자식테이블

CREATE TABLE teacher (
	teac_id NUMERIC(5) NOT NULL PRIMARY KEY,
	teac_name VARCHAR(20) NOT NULL,
	subj_code NUMERIC(5) REFERENCES subj(sub_id),
	teac_certifi_date DATE
)

INSERT INTO teacher VALUES
(00011,'정선생',00001,'2017-03-11'),
(00021,'홍선생',00002,'2017-04-12'),
(00031,'박선생',00003,'2017-04-13'),
(00041,'한선생',00004,'2018-05-20');

오류

ERROR: 오류: "teacher" 테이블에서 자료 추가, 갱신 작업이 "teacher_subj_code_fkey" 참조키(foreign key) 제약 조건을 위배했습니다 DETAIL: (subj_code)=(4) 키가 "subj" 테이블에 없습니다.

SQL state: 23503

population_and_accident=# CREATE TABLE coffee(
population_and_accident(# id INTEGER,
population_and_accident(# coffee_name VARCHAR,
population_and_accident(# );

population_and_accident=# CREATE TABLE costomer (
population_and_accident(# id INTEGER,
population_and_accident(# costomer_name VARCHAR,
population_and_accident(# costomer_gender VARCHAR(1)
population_and_accident(# );

CREATE TABLE purchase (
id INTEGER,
costomer_name VARCHAR NOT NULL REFERENCES costomer(costomer_name),
coffee_name VARCHAR NOT NULL REFERENCES coffee(coffee_name),
time TIME
);

-> 오류 
오류:  참조되는 "costomer" 테이블을 위한 주워진 키와 일치하는 고유 제약 조건이 없습니다
::
postgresql에서 모든 외래 키는 부모 테이블 의 고유 키 를 참조해야 하므로 bar테이블에 unique (name)인덱스 가 있어야 합니다 .

drop table if exists teacher;
drop table if exists subj;
CREATE TABLE subj(
	sub_id NUMERIC(5) UNIQUE  NOT NULL,
	sub_name VARCHAR(20) NOT NULL,
	sub_count NUMERIC(20) NOT NULL,
	UNIQUE (sub_id, sub_name)
);

INSERT INTO subj VALUES
(00001,'mathematics',60),
(00002,'science',42),
(00003,'programmin',70);

CREATE TABLE teacher (
	teac_id NUMERIC(5) NOT NULL PRIMARY KEY,
	teac_name VARCHAR(20) NOT NULL,
	subj_code NUMERIC(5) NOT NULL,
	subj_name VARCHAR(60) NOT NULL,
	teac_certifi_date DATE NOT NULL,
	FOREIGN KEY (subj_code,subj_name) REFERENCES subj (sub_id,sub_name)
);

FOREIGN KEY (subj_code,subj_name) REFERENCES subj (sub_id,sub_name)

foreign(외부) key(현재 테이블의 칼럼1, 칼럼2) ::: 제약조건을 걸 컬럼

references 참조 —>subj 테이블의 (칼럼1,칼럼2) 컬럼을 참조해라.

INSERT INTO teacher VALUES 
	(00011,'정선생',00001,'mathematics','2017-03-11'),
	(00021,'홍선생',00002,'science','2017-04-12'),
	(00031,'박선생',00003,'programmin','2017-04-13')
;

삭제

테이블이 삭제되는 것을 ON DELETE라고 불른다

부모테이블은 자식테이블보다 먼저 삭제될 수 없다.

ON DELETE

1. ON DELETE NO ACTION
2. ON DELETE RESTRICT (tranjection?)

부모테이블이 먼저 안 지워진다

---

1. ON DELETE SET NULL
2. ON DELETE CASCADe
3. ON DELETEDEFAULT

지워야 하는 경우..

ex) ON DELETE CASCADE : 부모의 칼럼 지우면 같이 참조하고 있떤 자식 칼럼이 지워지는 코드 !

DROP TABLE iF EXISTS teacher;
DROP TABLE iF EXISTS subj;

CREATE TABLE subj (
	subj_id NUMERIC(5) NOT NULL PRIMARY KEY,
	subj_name VARCHAR(60) NOT NULL
);

INSERT INTO subj
VALUES 
(00001,'mathematics'),
(00002,'science'),
(00003,'prigraming');

CREATE TABLE teacher (
	teac_id NUMERIC(5) NOT NULL PRIMARY KEY,
	teac_name VARCHAR(20) NOT NULL,
	subj_id NUMERIC(5) REFERENCES subj ON DELETE CASCADE,
	teac_certifi_date DATE	
);

INSERT INTO teacher
VALUES 
(00011,'정선생',00001,'2017-03-11'),
(00021,'홍선생',00002,'2017-04-12'),
(00031,'박선생',00003,'2017-04-13');

subj_id NUMERIC(5) REFERENCES subj ON DELETE CASCADE,

ex)***ON DELETE SET NULL

DROP TABLE iF EXISTS teacher;
DROP TABLE iF EXISTS subj;

CREATE TABLE subj (
	subj_id NUMERIC(5) NOT NULL PRIMARY KEY,
	subj_name VARCHAR(60) NOT NULL
);

INSERT INTO subj
VALUES 
(00001,'mathematics'),
(00002,'science'),
(00003,'prigraming');

CREATE TABLE teacher (
	teac_id NUMERIC(5) NOT NULL PRIMARY KEY,
	teac_name VARCHAR(20) NOT NULL,
	subj_id NUMERIC(5) REFERENCES subj ON DELETE SET NULL,
	teac_certifi_date DATE	
);

INSERT INTO teacher
VALUES 
(00011,'정선생',00001,'2017-03-11'),
(00021,'홍선생',00002,'2017-04-12'),
(00031,'박선생',00003,'2017-04-13');

DELETE FROM subj WHERE subj_id =00002;
SELECT * FROM teacher;

 

날아간것 null 된다.

ex)ON DELETE SET DEFAULT, *** 지워지면 초기값으로 변경된다.

DROP TABLE iF EXISTS teacher;
DROP TABLE iF EXISTS subj;

CREATE TABLE subj (
	subj_id NUMERIC(5) NOT NULL PRIMARY KEY,
	subj_name VARCHAR(60) NOT NULL
);

INSERT INTO subj
VALUES 
(00001,'mathematics'),
(00002,'science'),
(00003,'prigraming');

CREATE TABLE teacher (
	teac_id NUMERIC(5) NOT NULL PRIMARY KEY,
	teac_name VARCHAR(20) NOT NULL,
	subj_id NUMERIC(5) REFERENCES subj ON DELETE SET DEFAULT,
	teac_certifi_date DATE	
);

INSERT INTO teacher
VALUES 
(00011,'정선생',00001,'2017-03-11'),
(00021,'홍선생',00002,'2017-04-12'),
(00031,'박선생',00003,'2017-04-13');

DELETE FROM subj WHERE subj_id =00002;
SELECT * FROM teacher;

CHECK

check 명렁어 뒤에나오는 식이 boolean 타입의 true 를 만족해햐함.

ex

CREATE TABLE order_info (
	order_no INTEGER NOT NULL PRIMARY KEY,
	cust_name VARCHAR(20,)
	price MONEY,
	order_qty INTEGER CHECK (order_qty>0)
);

INSERT INTO order_info VALUES
(1,'sm',1000,-1),
(2,'mary',2000,1),
(3,'sin',3000,0);

ERROR: 오류: 새 자료가 "order_info" 릴레이션의 "order_info_order_qty_check" 체크 제약 조건을 위반했습니다 DETAIL: 실패한 자료: (3, sin, \3,000, 0)

SQL state: 23514

 

 

컴럼 추가

ALTER TABLE  테이블이름
ADD COLUMN  컬럼이름  데이터타입  제약조건

ALTER TABLE teacher
ADD COLUMN teac_xx DATE;

컬럼 수정 '

ALTER TABLE teacher
ALTER COLUMN teac_bith SET NOT NULL;

컬럼 수정 + NOT NULL /// 디폴트 값 줄 수 있다.

ALTER TABLE teacher
ADD COLUMN teac_birth DATE DEFAULT '2020-01-01' NOT NULL;

테이블의 컬럼 삭제

ALTER TABLE teacher
DROP COLUMN teac_bith;

ALTER TABLE book_info DROP COLUMN book_name;

참조ALTER TABLE book_info DROP COLUMN book_name;하는 부모 컬럼 지울 수 없다..

CREATE TABLE book_info (
	book_id INTEGER NOT NULL PRIMARY KEY,
	book_name VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO book_info VALUES
(1,'POSTGRESQL'),
(2,'MONGODB');

CREATE TABLE library (
	id INTEGER NOT NULL PRIMARY KEY,
	name VARCHAR(40) NOT NULL,
	book_name VARCHAR(20) NOT NULL REFERENCES book_info(book_name)
);

INSERT INTO library VALUES
(1,'국립도서관','POSTGRESQL');

ALTER TABLE book_info DROP COLUMN book_name;

///오류 뜸. 레퍼런스 되어있어서 삭제안됨.

CREATE TABLE book_info (
	book_id INTEGER NOT NULL PRIMARY KEY,
	book_name VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO book_info VALUES
(1,'POSTGRESQL'),
(2,'MONGODB');

CREATE TABLE library (
	id INTEGER NOT NULL PRIMARY KEY,
	name VARCHAR(40) NOT NULL,
	book_name VARCHAR(20) NOT NULL REFERENCES book_info(book_name)
);

INSERT INTO library VALUES
(1,'국립도서관','POSTGRESQL');

ALTER TABLE book_info DROP COLUMN book_name CASCADE;

// 제작조건 (해당 개체 library )테이블 개체가 덩달아 삭제됨.

컬럼의 값은 업데이트를 사용하여 수정한다 .!

UPDATE develop_book
SET name = 'java'
WHERE book_id =2
RETURNING *;

컬럼이름 바꾸기

ALTER TABLE book_info
ADD COLUMN name VARCHAR(60) DEFAULT '_' NOT NULL;

ALTER TABLE book_info
RENAME name TO book_name;

DROP TABLE IF EXISTS library;
DROP TABLE IF EXISTS book_info;

CREATE TABLE book_info (
	book_id INTEGER NOT NULL PRIMARY KEY,
	book_name VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO book_info VALUES
(1,'POSTGRESQL'),
(2,'MONGODB');

CREATE TABLE library (
	id INTEGER NOT NULL PRIMARY KEY,
	name VARCHAR(40) NOT NULL,
	book_name VARCHAR(20) NOT NULL REFERENCES book_info(book_name)
);

INSERT INTO library VALUES
(1,'국립도서관','POSTGRESQL');

ALTER TABLE book_info RENAME book_name TO name;
SELECT * FROM book_info;

참조되고있었다면 이름 바뀌면 자동으로 참조 대상의 이름이 바뀐다..

 

ALTER 로 PK 추가하기

CREATE TABLE book (
	id INTEGER NOT NULL,
	name VARCHAR(20) NOT NULL
);

ALTER TABLE book
ADD PRIMARY KEY (id);

SELECT * FROM book;

FK 추가하기

ADD FOREIGN KEY (book_id) REFERENCES book(id);

DROP TABLE IF EXISTS library;

CREATE TABLE library (
	lib_id INTEGER NOT NULL PRIMARY KEY,
	lib_name VARCHAR(40) NOT NULL,
	book_id INTEGER NOT NULL
);

ALTER TABLE library
ADD FOREIGN KEY (book_id) REFERENCES book(id);

ALTER 로 자료형 바꾸기

CREATE TABLE water (
	id SMALLINT NOT NULL PRIMARY KEY,
	name TEXT NOT NULL,
	location_no VARCHAR NOT NULL,
	descroption TEXT
);

INSERT INTO water VALUES(01,'천치','02','백두산 천지');

ALTER TABLE water
ALTER COLUMN id TYPE INTEGER,
ALTER COLUMN descroption TYPE VARCHAR;  -> ok

ALTER TABLE water
ALTER COLUMN location_no TYPE INTEGER;  --> fail

오류 :

ERROR: 오류: "location_no" 칼럼의 자료형을 integer 형으로 형변환할 수 없음 HINT: "USING location_no::integer" 구문을 추가해야 할 것 같습니다.

SQL state: 42804

캐스팅

:: /// cast('01',01) // 문자 → 숫자

ALTER TABLE water
ALTER COLUMN location_no TYPE INTEGER USING location_no::integer;

→ 된다... USING location_no::integer; 캐스팅