DB 특강 실습 정리

DB 특강 실습 내용 정리

# mysql 접속 (커맨드창)
mysql -uroot -p
> 비밀번호 입력

# 데이터베이스 확인
SHOW DATABASES;

# mysql 데이터 베이스 
USE mysql; 

# 선택된 데이터베이스 확인
SELECT DATABASE();

# 현재 유저 확인
SELECT USER();

# user 테이블 살펴보기
SELECT Host, `User` FROM USER; 

# sparta 계정 삭제 (sparta 계정 있을시)
DROP USER sparta@localhost;

# 사용자 추가
CREATE USER 'sparta'@'localhost' identified BY '12345678';

# 패스워드 정책 에러시(mysql8)
set global validate_password.policy=LOW;

# 데이터 베이스 생성
CREATE DATABASE cs_special_lecture;  

# 데이터 베이스 확인
SHOW DATABASES;

# 특정 데이터베이스의 모든 테이블에 모든 권한을 줌
grant all privileges on cs_special_lecture.* to 'sparta'@'localhost';


# 권한 반영(변경된 내용을 메모리에 반영)
FLUSH PRIVILEGES;


# 유저 변경 
# 유저 변경 (cli 환경에서 입력, dbeaver 에서는 Edit Connection 설정)
mysql -usparta -p12345678;


# 현재 유저 확인
SELECT USER();


# 현재 데터베이 확인
SELECT DATABASE();


# 데이터베이스 선택
USE cs_special_lecture;


-- 테이블 생성
-- 도시 테이블 생성
CREATE TABLE city(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(50) NOT NULL 
);


-- 학생테이블 생성
CREATE TABLE student(
    id int PRIMARY KEY AUTO_INCREMENT,
    //  AUTO_INCREMENT : 자동으로 숫자가 증가하도록 만든다ㅣ
    
    name varchar(50) NOT NULL,
    email varchar(50) UNIQUE,
    english_grade int,
    math_grade int,
    city_id int, 
    FOREIGN KEY (city_id)
    REFERENCES city(id) ON UPDATE CASCADE // 요 테이블이 city 테이블의 id를 참조함.
)


-- 데이터 삽
-- 도시 데이터 삽입
INSERT INTO
    city (name)
VALUES
    ('서울'),
    ('부산'),
    ('인천'),
    ('뉴욕'),    
    ('도쿄')           

    
-- 학생 데이터 삽입 
INSERT INTO 
    student (name, email, english_grade, math_grade, city_id)
VALUES
  ("정영훈", "jyha@gmail.com"      ,  70, 70  , 1),
  ("이재관", "leejaekwan@naver.com",  70, 70  , 2),
  ("홍길동", "hjd@naver.com"       ,  80, NULL, 3),
  ("김민수", NULL                  ,  90, 90  , 3),
  ("한동주", NULL                  ,  90, 90  , 5),
  ("우태현", NULL                  ,  70, 70  , 5)
  
  
-- 데이터 출력
-- 학생 데이터 모든 컬럼 출력
SELECT * FROM student s; 


-- email이 jyha@gmail.com 인 학생 이름, 이메일 출력
SELECT name, email  
FROM student s 
WHERE email = "jyha@gmail.com";

// "정영훈", "jyha@gmail.com" 출력됨

--------------------------------------------------------------

-- email이 jyha@gmail.com 이 아닌 학생들 이름, 이메일 출력
SELECT name, email  
FROM student s 
WHERE email != "jyha@gmail.com";

// "이재관", "leejaekwan@naver.com"
// "홍길동", "hjd@naver.com" 출력됨.

-----------------------------------------

-- email이 없는 학생들 이름만 출력(1)
SELECT name 
FROM student s 
WHERE email != NULL; // 아무것도 안 나옴.

------------------------------------------------

-- email이 없는 학생들 이름만 출력(2)
SELECT name 
FROM student s 
WHERE email is NULL;

// "김민수", "한동주", "우태현" 출력

cf. is not NULL 이라면?
--> "정영훈", "이재관", "홍길동" 출력

----------------------------------------------------------------------

-- email이 네이버 사용 하는 학생 모든 컬럼 출력 (MySQL Wildcards)
SELECT *
FROM student s 
WHERE email LIKE '%@naver%';

// %이 무엇이냐 : 와일드키! => 모든 문자가 와도 상관없다. 개수도 상관없다!
// "이재관", "leejaekwan@naver.com" / "홍길동", "hjd@naver.com" 출력
------------------------------------------------------------------------


-- email컬럼에 @ 앞 글자가 3글자인 학생 모든 컬럼 출 (MySQL Wildcards)
SELECT *
FROM student s 
WHERE email LIKE '___@%';

// "홍길동", "hjd@naver.com" 출력
--------------------------------------------------------------------------


-- 학생테이블 데이터 삭제
-- id가 6인 학생 데이터 삭제
DELETE FROM student 
WHERE id = 6;

// "우태현", NULL                  ,  70, 70  , 5 날아감.
----------------------------------------------------------------------
-- 학생 데이터 모두 삭제
DELETE FROM student 


-- 학생 데이터 삽입 
INSERT INTO 
    student (name, email, english_grade, math_grade, city_id)
VALUES
  ("정영훈", "jyha@gmail.com"      ,  70, 70  , 1),
  ("이재관", "leejaekwan@naver.com",  70, 70  , 2),
  ("홍길동", "hjd@naver.com"       ,  80, NULL, 3),
  ("김민수", NULL                  ,  90, 90  , 3),
  ("한동주", NULL                  ,  90, 90  , 5),
  ("우태현", NULL                  ,  70, 70  , 5)

----------------------------------------------------------------------------

-- 통계함수
-- 영어, 수학 점수 총점 출력
SELECT 
    SUM(english_grade) AS total_english_grade,
    SUM(math_grade) AS total_math_grade  
FROM student s

위 식  출력 결과

 

-- city_id그룹별 영어, 수학 점수 총점 출력
SELECT 
    city_id,
    SUM(english_grade) AS total_english_grade,
    SUM(math_grade) AS total_math_grade  
FROM student s 
GROUP BY city_id

위 식 출력 결과

 

-- city_id그룹이 3 영어, 수학 점수 총점 출력
SELECT 
    city_id,
    SUM(english_grade) AS total_english_grade,
    SUM(math_grade) AS total_math_grade  
FROM student s 
GROUP BY city_id
HAVING city_id = 3

위 식 출력 결과

 

-- 영어, 수학 점수 평균 출력
SELECT 
    AVG(english_grade) AS english_grade_avg,
    AVG(math_grade) AS math_grade_avg,  
    SUM(math_grade) / COUNT(*) AS math_grade_avg  
FROM student s 

SELECT 390 / 5;

SELECT 390 / 6;

위 식 출력 결과

 

-- LEFT JOIN
-- 학생 테이블에서 학생이 살고 있는 도시이름 출력
-- 도시 번호는 나오나 도시 이름이 출력 X
SELECT * FROM student s;


-- LEFT JOIN 으로 student, city 테이블을 합쳐줌 조건은 student.city_id = city.id
SELECT *
FROM student s 
LEFT JOIN city c ON s.city_id = c.id;

위 식 연관관계 모형

 

-- DB DUMP (커맨드창에서)
mysqldump -uroot -p12345678 cs_special_lecture > cs_special_lecture.sql

-- DB RESTORE (커맨드창에서)
mysql -uroot -p12345678 test < cs_special_lecture.sql