유데미 취업부트캠프 - 프론트엔드&백엔드 : 15일차 TIL

 

제약 조건

 

PRIMARY KEY (기본 키):

• PRIMARY KEY는 테이블의 각 레코드를 고유하게 식별하는 데 사용됩니다.
• 각 테이블은 하나의 PRIMARY KEY를 가질 수 있습니다.
• PRIMARY KEY는 NULL 값을 가질 수 없습니다.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50),
    Email VARCHAR(50)
);

사용자 테이블에서 사용자 ID를 PRIMARY KEY로 지정하면 각 사용자의 ID가 고유하게 식별됩니다.

 

FOREIGN KEY (외래 키):

• FOREIGN KEY는 다른 테이블의 PRIMARY KEY와 연결되는 필드로, 두 테이블 간의 관계를 설정하며, 보통은 두 개의 데이터베이스 테이블을 잇고자 기본 키와 함께 사용된다. 일종의 테이블을 연결하는 가상의 다리 역할을 한다.
• FOREIGN KEY를 가지고 있는 테이블을 하위 테이블, PRIMARY KEY를 가지고 있는 테이블을 참조 테이블 또는 상위 테이블이라고 한다.
• FOREIGN KEY를 사용하면 두 테이블 간에 참조 무결성을 유지할 수 있습니다.
• FOREIGN KEY를 사용하면 부모 테이블의 PRIMARY KEY 값이나 UNIQUE KEY 값만을 참조할 수 있습니다.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    UserID INT,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

 

위의 예에서 Orders 테이블의 UserID는 Users 테이블의 UserID를 참조하고 있습니다. 이것은 두 테이블 간의 관계를 나타내며, FOREIGN KEY 제약 조건이 Users 테이블의 UserID에 존재하는 값만이 Orders 테이블의 UserID에 들어갈 수 있도록 합니다.

 

 

SERIAL

SERIAL은 일련번호를 자동으로 생성하기 위한 데이터 타입이나 속성이며, 정수형 데이터 타입의 특별한 종류로써 자동으로 증가하는 일련번호를 생성하는 데 사용됩니다. 일반적으로 이는 테이블의 기본 키로 사용되며, 각 행에 대해 고유한 값을 생성합니다.

❗️row가 삭제되었거나, 삽입 명령에 에러가 발생했다면 그 해당 pk는 건너뛰고 다음 pk가 부여된다. 

CREATE TABLE players(
player_id SERIAL PRIMARY KEY,
age SMALLINT NOT NULL
);

 

 

CREATE

데이터베이스를 생성한다.

CREATE TABLE account(
	user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password  VARCHAR(50) NOT NULL,
    email VARCHAR(250) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
    )

 

CREATE TABLE job(
	job_id SERIAL PRIMARY KEY,
    job_name VARCHAR(200) UNIQUE NOT NULL
    )

 

account와 job를 연결하는 중간표 

CREATE TABLE account_job(
	user_id INTEGER REFERENCES account(user_id),
    job_id INTEGER REFERENCES job(job_id),
    hire_date TIMESTAMP
    )

user_id와 job_id는 fkey가 된다!

 

 

 

INSERT

데이터베이스 테이블에 새로운 행을 추가한다.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

//acount 테이블 입력
INSERT INTO account(username,password,email,created_on)
VALUES 
('Jose','password','jose@mail.com',CURRENT_TIMESTAMP)

user_id는 serial로 인해 자동생성되므로, 나머지 col의 값을 insert로 넣어준다.

CURRENT_TIMESTAMP를 이용해, 현재 시간과 날짜를 넣어준다.

 

INSERT INTO account_job(user_id,job_id,hire_date)
VALUES (10,10,CURRENT_TIMESTAMP)

중간표인 account_job에 존재하지 않는 user_id, job_id 값 10을 넣으면 에러가 발생한다

INTEGER REFERENCES 이기 때문에 fkey 제약조건을 위반했기 때문이다.

 

 

 

UPDATE

데이터베이스 테이블의 기존 레코드를 수정하는 데 사용되며, 특정 조건을 충족하는 행의 값을 업데이트하거나, 전체 테이블의 값을 변경할 수 있습니다.  returning 절을 맨뒤에 작성하여, select없이 바로 확인하고 싶은 col 을 작성할수 있다. (변경된 값 출력)

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

 

UPDATE account_job
SET hire_date=account.created_on
FROM account
WHERE account_job.user_id=account.user_id

 

 

ALTER

데이터베이스 객체(테이블, 뷰, 인덱스 등)의 구조를 변경하는 데 사용할 수 있다.

//조건 삭제
ALTER TABLE new_info
ALTER COLUMN people DROP NOT NULL (SET: 조건 추가)

//테이블에 새로운 열 추가
ALTER TABLE table_name
ADD COLUMN new_column_name datatype;

//열의 데이터 타입 변경
ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;

//테이블의 열 이름 변경
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

 

 

---

 

평가시험3

CREATE TABLE students(
student_id serial PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50 NOT NULL, 
homeroom_number integer,
phone VARCHAR(20) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE,
grad_year integer);

CREATE TABLE teachers(
teacher_id serial PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL, 
homeroom_number integer,
department VARCHAR(50),
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) UNIQUE);

phone번호 타입은 숫자보다 문자열을 사용하는것을 권장한다고 한다. (연산필요x)

INSERT INTO students(first_name,last_name, homeroom_number,phone,grad_year)
VALUES ('Mark','Watney',5,'7755551234',2035);

INSERT INTO teachers(first_name,last_name, homeroom_number,department,email,phone)
VALUES ('Jonas','Salk',5,'Biology','jsalk@school.org','7755554321');

 

---

 

조건식

 

CASE

조건에 따라 다른 값을 선택하여 결과를 반환한다.

SELECT
    column1,
    column2,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS alias
FROM table_name;

AS 키워드를 사용하여 결과 열에 별칭을 변경할 수 있다!

• WHEN : 조건이 참일 경우 
• ELSE : 위의 모든 조건이 거짓인 경우 

SELECT 
SUM(
	CASE rating
    WHEN 'R' THEN 1 ELSE 0
)AS r
FROM film

해당값을 1로 변경시켜서 sum으로 카운트해서 R등급이 몇개가 있는지 이렇게 확인해볼 수 있다!

 

COALESCE

Null값을 가진 표를 쿼리할때 유용하다 -> Null값을 다른 수로 치환할때 사용한다.

SELECT item,(price-COALESCE(discount,0)) AS final 
FROM table

null값을 0으로 치환하여, 뺄셈 연산이 가능해진다.

 

CAST

타입 변환이 가능하다

SELECT CHAR_LENGHT(CAST(inventory_id AS VARCHAR)) FROM rental

 

NULLIF

두개의 값을 넣어서, 두 값이 같으면 NULL , 다르면 첫번째 값이 도출된다.

SELECT(
SUM(CASE WHEN department='A' THEN 1 ELSE 0 END)/
NULLIF(SUM(CASE WHEN department='B' THEN 1 ELSE 0 END),0)
) AS department_ratio
FROM depts

두 값이 null,0으로 첫번째 값인 null이 반환된다.

 

VIEW

테이블에서 선택적인 열과 행의 조합을 나타내는 가상 테이블로,

뷰는 실제로 데이터를 저장하지 않지만 데이터베이스에 대한 쿼리를 단순화하고 데이터 모델을 논리적으로 구성하는 데 사용된다.

 

CREATE VIEW HighSalaryEmployees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

SELECT * FROM HighSalaryEmployees;