2025.01.11.토 TIL

//Spring 기초 6주차 DB 실습
CREATE TABLE student
(
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '학생 식별자',
    name VARCHAR(100) COMMENT '이름',
    age INT COMMENT '나이',
    grade VARCHAR(10) COMMENT '등급'
);

CREATE TABLE major
(
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '전공 식별자',
    name VARCHAR(50) NOT NULL COMMENT '이름',
    tutor VARCHAR(50) NOT NULL COMMENT '튜터'
);

CREATE TABLE enrollment
(
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '등록 식별자',
    student_id BIGINT COMMENT '수강생 식별자',
    major_id BIGINT COMMENT '전공 식별자',
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (major_id) REFERENCES major(id)
);

ALTER TABLE student ADD COLUMN email VARCHAR(100) NOT NULL;

ALTER TABLE major MODIFY COLUMN name VARCHAR(100);

ALTER TABLE major ADD CONSTRAINT UNIQUE(tutor);

INSERT INTO student (name, age, grade, email) VALUES('학생1', 20, 'AAA', 'a@a.com');
INSERT INTO student (name, age, grade, email) VALUES('학생2', 21, 'AAA', 'b@b.com');
INSERT INTO student (name, age, grade, email) VALUES('학생3', 22, 'A', 'c@c.com');
INSERT INTO student (name, age, grade, email) VALUES('학생4', 23, 'BBB', 'd@d.com');
INSERT INTO student (name, age, grade, email) VALUES('학생5', 23, 'BB', 'e@e.com');
INSERT INTO student (name, age, grade, email) VALUES('학생6', 23, 'B', 'f@f.com');

INSERT INTO major (name, tutor) VALUES('Spring', '황원욱');
INSERT INTO major (name, tutor) VALUES('NodeJS', '김스파');
INSERT INTO major (name, tutor) VALUES('React', '홍정기');
INSERT INTO major (name, tutor) VALUES('Vue', '김르타');

INSERT INTO enrollment (student_id, major_id) VALUES(1, 1);
INSERT INTO enrollment (student_id, major_id) VALUES(2, 2);

SELECT * FROM student;

SELECT * FROM student WHERE id = 1;

SELECT name, age FROM student WHERE id = 1;

UPDATE student SET age = 21 WHERE name = '학생1';

SELECT * FROM student WHERE name = '학생1';

DELETE FROM student WHERE id = 4;

SELECT * FROM student;

• SQL
  • 데이터베이스 → 테이블 → 컬럼
  • Query Flow
    1. 어떤 테이블
    2. 어떤 컬럼
    3. 어떤 조건
    4. 어떤 함수
  • count, group by