/*
* DDL : Database Definition Language
* - 데이터베이스의 테이블 구조 생성, 삭제,변경
* 테이블 생성 : create table
* 테이블 삭제 : drop table
* 테이블 변경 : alter table
*
* DML : Database Manplation Language
* - 데이터 생성, 변경 ,삭제
* 데이터 생성 : insert into
* 데이터 변경 : update
* 데이터 삭제 : delete
* 데이터 조회 : select
* */
-- 테이블생성
-- 테이블 목록 조회 : information_schema.tables
DESC information_schema.tables;
-- hrdb2019 데이터베이스의 모든 테이블 조회
SELECT * FROM information_schema.tables -- 오라클 : user_ 시작하는 데이터 딕셔너리 테이블
WHERE TABLE_SCHEMA = 'hrdb2019';
-- 툴에 의존하지 않는 기술을 가진다!
-- 테이블 생성
-- 형식 : create table [테이블명] (
-- 컬럼명 데이터타입(크기) [제약사항], ...)
DESC employee;
-- emp 테이블 생성 : emp_id (문자,10), emp_name(문자,20), hire_date(date), salary (숫자)
-- ddl은 메모리에 저장이 된후 변경이 되는게 아니라 바로바로 실행이 되는거아니다
USE hrdb2019;
CREATE TABLE emp(
emp_id char(10), -- 고정형 문자데이터 타입
emp_name varchar(20), -- 가변형 문자데이터 타입
hiredate date,
salary int -- 최대 4바이트 숫자
)
SELECT * FROM information_schema.tables
WHERE TABLE_SCHEMA = 'hrdb2019'
DESC emp;
-- 테이블 생성(복제)
-- 형식 : Create table 테이블명
-- As
-- Select ~~
-- employee 테이블을 구조만 복제해서 emp2 테이블을 생성
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'hrdb2019';
SELECT * FROM employee e;
CREATE TABLE employee_copy
as
SELECT * FROM employee;
CREATE TABLE employee_copy
as
SELECT * FROM employee
WHERE 1 = 0; -- 컬럼만 복제 복제한 테이블에는 원본의 제약사항은 포함되지 않는다
DESC employee_copy;
DESC employee;
SELECT * FROM employee_copy;
SELECT * FROM emp2;
DROP TABLE emp2;
-- 정보시스템 부서의 사원들만 별도의 테이블 employee_sys 테이블에 저장 (카스)
CREATE table employee_sys
AS
SELECT * FROM employee e
WHERE dept_id IN (SELECT dept_id FROM department d WHERE dept_name = '정보시스템');
SELECT * FROM information_schema.tables
WHERE TABLE_SCHEMA = 'hrdb2019'
-- 테이블 삭제
-- drop table 테이블명
DROP TABLE emp;
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'hrdb2019'
DESC emp;
-- 데이터생성(추가)
-- 형식 : insert into 테이블명 {(컬럼리스트)}
-- values (데이터리스트);
SELECT floor(rand()*10000)
SELECT * FROM emp
INSERT INTO emp (emp_id,emp_name,hiredate,salary)
value('hong','홍길똥1',curdate(),1234);
INSERT emp (emp_id,emp_name,hiredate,salary) -- INTO 없어도 반영되는 이유 :OPEN api 들은 모든 api를 사용하는 쿼리들이 다 실행이 된다
value('hong','홍길똥3',curdate(),1234);
INSERT INTO emp (emp_name,hiredate,salary,emp_id)
value('테스트',curdate(),8500,'test');
INSERT INTO emp
value('test','테스트',curdate(),null ) -- 컬럼리스트 생략시 DESC 구조로 체크
ROLLBACK; -- SET autocommit(true)
COMMIT;
-- constraint(제약사항) : insert,update,delete
-- 중복체크 : unique
-- null 체크 : not null
-- 기본키 제약(unique + not null) : primary key
-- 참조키 제약 : foreign key - 관련있는 다른 테이블의 primary key를 참조하는 제약
-- defalult, check
-- 테이블 생성시 제약사항 추가
-- emp2 테이블에 제약사항 : emp_id(문자 4,기본키제약),emp_name(문자,notnull)
DROP TABLE emp2;
CREATE TABLE emp2(
emp_id char(4) PRIMARY KEY,
emp_name varchar(10) NOT NULL,
hire_date date,
salary int
);
SELECT * FROM emp2;
INSERT INTO emp2
value('tttt' ,'아무독',curdate(),floor(rand()*10000))
-- 제약사항 조회
DESC information_schema.TABLE_constraints;
SELECT * FROM information_schema.table_constraints WHERE TABLE_SCHEMA = 'hrdb2019'
DESC emp2
-- emp3 테이블에 제약사항 :
-- 제약사항 이름 규칡 : 테이블명_컬럼명_제약사항명
-- emp_id(문자4, 기본키제약), emp_name(문자10, not null)
CREATE TABLE emp3(
emp_id char(4) ,
emp_name varchar(10)NOT null,
hire_date date,
salary int,
CONSTRAINT emp3_emp PRIMARY KEY(emp_id)
)
DESC emp3
-- 제약사항 체크
SELECT * from information_schema.table_constraints WHERE table_name = 'emp3';
SELECT * FROM emp3
DROP TABLE emp3
INSERT INTO emp3
values('hall','김촌홀',curdate(),5000);
-- 참조키 제약 설정: 두 개이상의 참조관계가 설정되어 있는 경우
-- 형식 : constraint 제약명 foreign key(참조컬럼) 참조할 테이블명(컬럼:pk)
-- [학사관리 시스템 설계]
-- 학생은 반드시 하나이상의 과목을 수강해야 한다. (학생이 중복임 이 경우 중복되는 컬럼을 참조키로 설정해야함)
-- 교수는 반드시 하나이상의 과목을 강의해야 한다.
-- 과목 테이블 생성 : subject
-- 컬럼 : 과목id(sub_id), 과목명(sub_name), 등록일자(sdate)
-- 과목id를 기본키로 설정
CREATE TABLE subject(
sub_id char(4),
sub_name varchar(30) NOT NULL,
sdate date,
CONSTRAINT subject_sub_id_pk PRIMARY KEY(sub_id)
);
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'hrdb2019';
DESC subject;
-- 학생 테이블 생성 : student
-- 컬럼 : 학번(sid:문자4), 학생명(sname), 학과(dept),과목id(sub_id), 이메일(email)
-- 학번은 기본키로 설정, 수강과목은 과목테이블을 참조한다.
CREATE TABLE student(
sid char(4),
sname varchar(20) NOT NULL,
dept varchar(20),
sub_id char(4),
email varchar(20),
CONSTRAINT student_sid_pk PRIMARY key(sid),
CONSTRAINT student_sub_id_fk FOREIGN key(sub_id) REFERENCES subject(sub_id)
);
SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'student';
SELECT * FROM information_schema.table_constraints WHERE TABLE_SCHEMA = 'student';
DESC student
-- 교수 테이블 생성 : professor
-- 컬럼 : 교수id(pid), 교수명(pname), 폰번호(phone), 과목id(sub_id)
CREATE TABLE professor(
pid char(4),
pname varchar(20),
phone varchar(20),
sub_id char(4),
CONSTRAINT professor_pid_pk PRIMARY KEY (pid),
CONSTRAINT professor_sub_id_fk FOREIGN KEY (sub_id) REFERENCES subject(sub_id)
)
DESC professor
-- 참조관계에서의 데이터 생성(추가)
-- 가장 데이터추가 되어져야 하는 테이블
-- subject
DESC subject
INSERT INTO subject(sub_id,sub_name,sdate) values('S001','html',curdate() );
INSERT INTO subject(sub_id,sub_name,sdate) values('S002','mysql',curdate() );
INSERT INTO subject(sub_id,sub_name,sdate) values('S003','node',curdate() );
INSERT INTO subject(sub_id,sub_name,sdate) values('S004','react',curdate() );
INSERT INTO subject(sub_id,sub_name,sdate) values('S005','미국어',curdate() );
COMMIT;
SELECT * FROM subject s
-- 학생 데이터 생성
DESC student ;
INSERT INTO student values('ST01','홍길동','컴퓨터공학과','S002','hong@naver.com');
INSERT INTO student values('ST04','테스트','경영학과','S003','hong@naver.com');
INSERT INTO student values('ST05','청길동','철학과','S003','blue@naver.com');
SELECT * FROM student s ;
-- 교수 데이터 생성
desc professor;
INSERT INTO professor values('P001','공유','010-1234-4587','S001');
INSERT INTO professor values('P002','리사','010-1234-4587','S002');
INSERT INTO professor values('P003','제니','010-1234-4587','S003');
INSERT INTO professor values('P004','김아무개','010-1234-4487','S004');
INSERT INTO professor values('P005','박누락씨','010-1234-4487','S005');
SELECT * FROM professor p ;
-- 홍길동 학생이 수강하고 있는 과목의 교수명을 출력해주세요
SELECT p.pname FROM professor p INNER JOIN student s2 ON p.sub_id = s2.sub_id
WHERE s2.sname = '홍길동'
-- 테스트학생이 수강하고있는 과목을 모두 출력
-- 서브쿼리
SELECT * FROM subject s WHERE s.sub_id in (SELECT s2.sub_id FROM student s2 WHERE s2.sname= '테스트');
SELECT * FROM subject s INNER JOIN student s2 ON s.sub_id = s2.sub_id
WHERE sname = '테스트';
-- 테스트학생이 수강하고있는 과목, 학생명을 모두 출력
SELECT s.sub_name,s2.sname FROM subject s INNER JOIN student s2
ON s.sub_id = s2.sub_id
WHERE s2.sname = '테스트';
-- 김아무개 교수가 강의하는 과목의 학생들을 모두 출력
SELECT * FROM professor p ,subject s ,student s2
WHERE p.sub_id = s.sub_id
AND s.sub_id = s2.sub_id;
SELECT * FROM student s INNER JOIN professor p ON s.sub_id = p.sub_id
WHERE p.pname = '김아무개';
-- 과목별 과목명,교수명 ,수강인원과 수강학생의 정보 출력
SELECT * FROM subject s ,student s2
WHERE s.sub_id = s2.sub_id
COMMIT;
-- 내답변
SELECT group_concat(DISTINCT p.pname) AS 교수이름 ,s2.sub_name, group_concat(DISTINCT s.sname) AS 수강학생,
count(s2.sub_name) AS 수강인원
FROM student s INNER JOIN subject s2
ON s.sub_id = s2.sub_id
right OUTER JOIN professor p ON s.sub_id = p.sub_id
GROUP BY s2.sub_name ;
SELECT * FROM professor p
DESC subject
DESC subject
SELECT s.sub_name FROM subject s
SELECT * FROM student s
DROP TABLE subject
USE hrdb2019;
SELECT group_concat(DISTINCT p.pname) AS 교수이름 ,s2.sub_name, group_concat(DISTINCT s.sname) AS 수강학생,
count(s2.sub_name) AS 수강인원
FROM student s INNER JOIN subject s2
ON s.sub_id = s2.sub_id
right OUTER JOIN professor p ON s.sub_id = p.sub_id
GROUP BY s2.sub_name ;
-- 과목별 과목명,교수명 ,수강인원과 수강학생의 정보 출력
SELECT s.sub_id,s.sub_name,group_concat(DISTINCT p.pname) 수강교수, count(s2.sname) 수강인원, group_concat(DISTINCT s2.sname) 수강학생
FROM subject s INNER JOIN professor p on s. sub_id = p.sub_id
LEFT OUTER JOIN student s2 on p.sub_id = s2.sub_id GROUP BY s.sub_id