SQL 기초

SQL

관계형 데이터베이스 표준 언어로서 가장 많이 사용되는 데이터 언어

- SYSTEM R’이라는 실험용 DBMS를 위한 데이터 언어로 IBM 연구소에서 처음 개발

-현재는 미국 표준(ANSI)과 국제 표준(ISO) 관계형 데이터베이스 표준 언어로 승인

ORACLE, MS SQL-Server, MySQL 등 거의 모든 관계형 DBMS가 지원

 

SQL을 구성하는 3가지 부속 언어의 분류와 관련 주요 기능

MySQL 개요

 세계에서 가장 많이 사용하는 오픈소스 RDBMS, 1995년에 최초 개발  코드가 공개되어 있어 누구나 다운로드하여 사용할 수 있는 데이터베이스 관리 시스템

 무료임에도 불구하고 처리 속도가 빠르고 설치도 쉬움
 오라클, MS-SQL Server와 함께 가장 많이 사용하는 DBMS 중 하나

 2010년에 오라클에 인수되었지만 현재도 오픈소스 정책은 계속됨

 

# 작업 대상 데이터 베이스 변경
USE univDB;
# 현재 사용 데이터베이스 확인
SELECT database();
# univDB안의 생성 테이블 목록 확인
SHOW TABLES;
# 학생 테이블 생성 정보 확인
DESC 학생;

 

SELECT 

# 전체 학생의 이름과 주소를 검색
SELECT 이름, 주소
FROM 학생;

# 전체 학생의 소속학과 정보를 중복 없이 검색하시오.
SELECT DISTINCT 소속학과
FROM 학생;

 

WHERE 절

# 학생 중에서 2학년 이상인 "컴퓨터"학과 학생의 이름, 학년, 소속학과, 휴대폰번호 정보를 검색
SELECT 이름, 학년, 소속학과, 휴대폰번호
FROM 학생
WHERE 학년>=2 AND 소속학과 = "컴퓨터";

# 1,2,3학년 학생이거나 "컴퓨터"학과에 소속되지 않은 학생의 이름, 학년, 소속학과
# 휴대폰번호 정보를 검색하시오
SELECT 이름, 학년, 소속학과, 휴대폰번호
FROM 학생
WHERE (학년 >= 1 AND 학년 <=3) OR NOT(소속학과 ="컴퓨터");

SELECT 이름, 학년, 소속학과, 휴대폰번호
FROM 학생
WHERE (학년 BETWEEN 1 AND 3) OR NOT (소속학과="컴퓨터");

 

ORDER BY 절

# "컴퓨터"학과나 "정보통신"학과의 학생의 이름과 학년, 소속학과 정보를 학년의 오름차순
# (학년이 낮은 학생부터 높은 학생 순서)으로 검색하시오
SELECT 이름, 학년, 소속학과
FROM 학생
WHERE 소속학과 = "컴퓨터" OR 소속학과 = "정보통신"
ORDER BY 학년 ASC;

# 전체 학생의 모든 정보를 검색하되 학년을 기준으로 먼저 1차 오름차순 정렬하고, 학년이
# 같은 경우에는 이름을 기준으로 2차 내림차순 정렬하여 검색하시오
SELECT *
FROM 학생
ORDER BY 학년 ASC, 이름 DESC;

 

COUNT 별 차이

# 전체 학생수를 검색
SELECT COUNT(*)
FROM 학생;

SELECT COUNT(학번)
FROM 학생;

SELECT COUNT(*) AS 학생수1, COUNT(주소) AS 학생수2, COUNT(DISTINCT 주소) AS 학생수3
FROM 학생;

 

AVG 

# "여"학생의 평균 나이를 검색하시오
SELECT AVG(나이) "여학생 평균나이"
FROM 학생
WHERE 성별="여";

 

MIN, MAX

# 전체 학생의 성별 최고 나이와 최저 나이를 검색하시오
SELECT 성별, MAX(나이) aS "최고 나이",MIN(나이) aS"최저 나이"
FROM 학생
GROUP BY 성별;

#20대 학생만을 대상으로 나이별 학생수를 검색하시오
SELECT 나이, COUNT(*) AS "나이별 학생수"
FROM 학생
WHERE 나이>=20 AND 나이 <30
GROUP BY 나이;

# 각 학년별로 2명 이상의 학생을 갖는 학년에 대해서만 학년별 학생수를 검색
SELECT 학년, COUNT(*) AS "학년별 학생수"
FROM 학생
GROUP BY 학년
HAVING COUNT(*) >=2;

 

LIKE 연산자

 

# "이"씨 성을 가진 학생들의 학번과 학생 이름을 검색하시오
SELECT 학번, 이름
FROM 학생
WHERE 이름 LIKE "이__";

# 주소지가 "서울"인 학생의 이름, 주소, 학년을 학년 순(내림차순)으로 검색하시오
SELECT 이름, 주소, 학년
FROM 학생
WHERE 주소 LIKE "%서울%"
ORDER BY 학년 dESC;

 

# NULL

# 휴대폰번호가 등록되지 않은(널 값을 갖는) 학생의 이름과 휴대폰번호를 검색하시오
SELECT 이름, 휴대폰번호
FROM 학생
WHERE 휴대폰번호 IS nULL;

 

 

USE univdb;
show tables;

#"여"학생 이거나 "A"학점을 받은 학생의 학번을 검색하시오
SELECT 학번
FROM 학생
WHERE 성별 = "여"
UNION
SELECT 학번
FROM 수강
WHERE 평가학점 = "A";

# 과목번호가 "c002"인 과목을 수강한 학생의 이름을 검색하시오(기존의 c002인 학생을 알아야함)
SELECT 이름
FROM 학생
WHERE 학번 IN ("s001","s003","s004");

# 과목번호가 "coo2"인 과목을 수강한 학생의 이름을 검색하시오
SELECT 이름
FROM 학생
WHERE 학번 IN (select 학번
FROM 수강
WHERE 과목번호 ="c002");

# "정보보호" 과목을 수강한 학생의 이름을 검색하시오
SELECT 이름
FROM 학생
WHERE 학번 IN (SELECT 학번
FROM 수강
WHERE 과목번호 = (SELECT 과목번호
FROM 과목
WHERE 이름="정보보호"));

# 과목번호가 "c002"인 과목을 수강항 학생의 이름을 검색하시오.
SELECT 이름
FROM 학생
WHERE EXISTS (SELECT *
FROM 수강
WHERE 수강.학번 = 학생.학번 AND 과목번호 = "c002");

# 학생 중에서 한 과목도 수강하지 않은 학생의 이름을 검색하시오
SELECT 이름
FROM 학생
WHERE NOT EXISTS (SELECT *
FROM 수강
WHERE 수강.학번 = 학생.학번);

# 크로스 조인 결과(비교)
SELECT *
FROM 학생, 수강;

SELECT *
FROM 학생 CROSS JOIN 수강;

SELECT count(*)
FROM 학생, 수강;

SELECT count(*)
FROM 학생 CROSS JOIN 수강;

# 전체 학생의 기본 정보와 모든 수강 정보를 검색
SELECT *
FROM 학생, 수강
WHERE 학생.학번 = 수강.학번;

SELECT *
FROM 학생 JOIN 수강 ON 학생.학번 = 수강.학번;

# 학생 중에서 과목번호가 "c002"인 과목을 수강한 학생의 학번의 이름, 과목번호
# 그리고 변환중간성적(학생별 중간 성적의 10% 가산점수)을 검색하시오
SELECT 학생.학번, 이름, 과목번호, 중간성적+(중간성적*0.1) AS 변환중간성적
FROM 학생, 수강
WHERE 학생.학번=수강.학번 AND 과목번호="c002";
SELECT 학생.학번, 이름, 과목번호, 중간성적+(중간성적*0.1) AS 변환중간성적
FROM 학생 JOIN 수강 ON 학생.학번=수강.학번
WHERE 학생.학번=수강.학번 AND 과목번호="c002";

# 학생 중에서 "정보보호" 과목을 수강한 학생의 학번과 이름, 과목번호 를 검색하시오
SELECT 학생.학번, 학생.이름, 수강.과목번호
FROM 학생, 수강, 과목
WHERE 학생.학번=수강.학번 AND 수강.과목번호=과목.과목번호 AND 과목.이름="정보보호";
SELECT 학생.학번, 학생.이름, 수강.과목번호
FROM (학생 JOIN 수강 ON 학생.학번=수강.학번) JOIN 과목 ON 수강.과목번호 = 과목.과목번호
WHERE 과목.이름 = "정보보호";

# 학생 중에서 과목번호가 "c002"인 과목을 수강한 학생의 이름, 과목번호를 검색하시오
SELECT 이름, 과목번호
FROM 학생 AS S, 수강 E
WHERE S.학번=E.학번 AND 과목번호 = "c002";

# 값 넣기

# 컬럼 1 primary key

# 복제 테이블 생성
CREATE TABLE 학생1 AS (SELECT * FROM 학생);
CREATE TABLE 과목1 AS (SELECT * FROM 과목);
CREATE TABLE 수강1 AS (SELECT * FROM 수강);

# 학생1 테이블에 학번 g001, 이름 "김연아2", 주소 "서울 서초", 학년 4, 나이 23, 성별 "여",
# 휴대폰번호 "010-1111-2222", 소속학과 "컴퓨터"인 학생 정보를 입력
INSERT INTO 학생1
VALUES ("g001", "김연아2", "서울 서초", 4, 23, "여", "010-1111-2222", "컴퓨터");

# 기본값 또는 널 값 입력
# 학번 "g002", 이름 "홍길동", 주소"미정", 학년 1, 나이 26, 성별 "남", 휴대폰번호 "NULL",
# 소속학과 "통계"인 학생 정보를 삽입
INSERT INTO 학생1(이름, 주소, 학년, 나이, 성별, 휴대폰번호, 소속학과, 학번)
VALUES ("홍길동", DEFAULT,1,26,"남",NULL,"통계","g002")
# 같은코드
INSERT INTO 학생1(이름, 학년, 나이, 성별, 소속학과, 학번)
VALUES ("홍길동2",1,26,"남","통계","g002");

# 학번 "g003", 이름 "이승엽2", 주소 "미정", 학년3, 나이 30, 성별"남",휴대폰번호"NULL",
# 소속학과 "정보통신"인 학생 정보를 삽입
INSERT INTO 학생1(학년, 나이, 성별, 소속학과, 학번, 이름)
VALUES (3, 30, "남", "정보통신", "g003","이승엽2");

# safe update"를 안전모드 해제
SET SQL_SAFE_UPDATES = 0;


# "이은진" 학생의 학년을 3학년으로 수정하시오.
UPDATE 학생1
SET 학년=3
WHERE 이름="이은진";

SELECT * FROM 학생1 WHERE 이름="이은진";

#모든 학생의 학년을 1씩 증가시키고 소속학과를 "자유전공학부"로 변경하시오
UPDATE 학생1
SET 학년=학년+1, 소속학과="자유전공학부"
WHERE 학년=4;

SELECT * FROM 학생1;

# 수강 내용이 없는 학생의 소속학과를 널 값으로 수정하시오
UPDATE 학생1
SET 소속학과 = NULL
WHERE 학번 NOT IN (SELECT 학번
FROM 수강1 );

SELECT * FROM 학생1;

# 학번이 "s003"인 학생의 수강 내용을 "이은진" 학생이 수강한 것으로 수정하시오
# 즉, 수강 1ㅂ 테이블의 학번을 "s003" 대신에 "이은진"학생의 학번으로 변경
UPDATE 수강1
SET 학번 = (SELECT 학번
FROM 학생1
WHERE 이름="이은진")
WHERE 학번 = "s003";

SELECT * FROM 수강1 WHERE 학번="s003" OR 학번="s007";

#"송윤아" 학생의 모든 정보를 삭제하시오
DELETE FROM 학생1
WHERE 이름="송윤아";

SELECT * FROm 학생1;

# 3학년 모든 학생 정보를 삭제하시오
DELETE FROM 학생1
WHERE 학년=3;

SELECT * FROM 학생1;

#수강자가 2명 미만인 과목에 대한 과목 정보를 모두 삭제하시오
DELETE FROM 과목1
WHERE 과목번호 IN
(SELECT 과목번호
FROM 수강1
GROUP BY 과목번호
HAVING COUNT(*) <2);

SELECT * FROM 과목1;

# 모든 학생 정보를 삭제하시오
DELETE FROM 학생1;

SELECT * FROM 학생1;