MariaDB Exercises
// MySQL 계정(수퍼유저)에서 사용자 생성(movie)
sudo -u root mysql // root(수펴유저) 계정으로 mysql 실행
또는 아래 방식으로
su - root
mysql // Linux root 계정에서 mysql을 실행하면 수퍼유저로 로그인
MariaDB[none]>CREATE USER 'movie'@'localhost' IDENTIFIED BY 'futuresoft#00'; // 수퍼유저에서 사용자 계정 movie 생성, localhost에서만 접근
MariaDB[none]>CREATE USER 'movie'@'%' IDENTIFIED BY 'futuresoft#00'; // 수퍼유저에서 사용자 계정 movie 생성, any hosts에서 접근
MariaDB[none]>use mysql
MariaDB[mysql]>SELECT host, user, password FROM user; // 사용자 movie 계정이 생성됨
// Database 생성(root 계정 혹은 createdb 권한이 있는 사용자 계정)
CREATE DATABASE movielens DEFAULT CHARACTER SET utf8mb4;
SHOW DATABASES;
// 사용자 계정(movie)에 생성된 데이터베이스 접근 권한 부여
GRANT ALL PRIVILEGES ON movielens.* TO 'movie'@'localhost'; // localhost에만 권한 부여
GRANT ALL PRIVILEGES ON movielens.* TO 'movie'@'%'; // any hosts에 권한 부여
FLUSH PRIVILEGES;
QUIT
// 새로 생성한 계정(movie)과 데이터베이스(movielens)로 다시 접속
mysql -hlocalhost -P3306 -umovie -pfuturesoft#00 -Dmovielens
mysql -hcms.futuresoft.co.kr -P3306 -umovie -pfuturesoft#00 -Dmovielens
// Movielens Table Schema and Sample Data Load
movielens-mysql.sql // 영화 및 평점관련 테이블 생성 후 샘플 데이터 insert
mysql -hlocalhost -P3306 -umovie -pfuturesoft#00 -Dmovielens < movielens-mysql.sql
(혹은 mysql 로그인후 MariaDB[mysql]>source movielens-mysql.sql 로 sql 파일 실행)
genre(영화분류)
movie(영화정보)
muser(고객정보)
occupation(직업정보)
movierating(영화평점)
moviegenre(영화장르)
// Schema와 Sample Data에 일부 내용 보완
ALTER TABLE user RENAME TO muser; // user 테이블명을 muser로 변경
ALTER TABLE muser ADD COLUMN name VARCHAR(40) NOT NULL DEFAULT ' '; // 이름 필드 추가
UPDATE muser SET name = CONCAT('고객', CONVERT(id, CHAR)); // 이름 값 설정
UPDATE muser SET age = 17 WHERE age < 18; // 나이 설정 안된 회원 정보 임의 설정
UPDATE movie SET year = 2001 WHERE year = 0; // 상영년도 설정 안된 데이터 값 임의 설정
ALTER TABLE genre COMMENT = '영화분류';
ALTER TABLE movie COMMENT = '영화정보';
ALTER TABLE moviegenre COMMENT = '영화장르';
ALTER TABLE movierating COMMENT = '영화평점';
ALTER TABLE muser COMMENT = '고객정보';
ALTER TABLE occupation COMMENT = '직업분류';
// 기본 Query 예제
SELECT * FROM genre; // 결과에 모든 필드를 표시, Default 정렬 순서는 PK 순서
SELECT COUNT(*) FROM genre; // 전체 레코드의 갯수 출력
SELECT COUNT(name) FROM genre; // 특정 필드값이 NULL이 아닌 레코드 갯수 출력
SELECT id AS 분류ID, name AS 분류명 FROM genre; // 필드명을 임으로 변경해서 표시(default는 필드정의 명칭)
SELECT name FROM genre; // 결과에 name 필드만 표시
SELECT substr(name, 1, 8) FROM genre;
SELECT substr(name, 3) FROM genre;
SELECT name FROM genre WHERE substr(name, 1, 2) = 'Th'; // 문자의 일부를 완전일치 검색
SELECT name FROM genre WHERE substr(name, 1, 2) LIKE 'Th%'; // Th 로 시작하는 레코드 출력
SELECT id, name FROM genre ORDER BY name ASC; // Ascending Order
SELECT id, name FROM genre ORDER BY name DESC; // Descending Order
SELECT id, name FROM genre WHERE id =1;
SELECT id, name FROM genre WHERE id =1 AND name = 'Action'; // 모든 조건이 만족하는 레코드 출력
SELECT id, name FROM genre WHERE id =1 OR name = 'Comedy'; // 어느 조건이건 만족하는 레코드 출력
SELECT id, name FROM genre WHERE id =1 AND name = 'Comedy';
SELECT id, name FROM genre WHERE name LIKE 'A%' ORDER BY name ASC; // A로 시작하는 레코드 출력
SELECT id, name FROM genre WHERE name LIKE '%e' ORDER BY name ASC; // e로 끝나는 레코드 출력
SELECT id, name FROM genre WHERE name LIKE '%me%' ORDER BY name ASC; // me가 포함된 레코드 출력
SELECT id, name, year FROM movie LIMIT 10; // 결과중 10건만 출력
SELECT id, name, year FROM movie WHERE year = 2001; // 숫자 필드 질의
SELECT id, name, year FROM movie WHERE name = 'Jumanji'; // 문자 필드 질의
SELECT id, name, year FROM movie WHERE year BETWEEN 1995 AND 1998; // 특정값 사이 결과 출력
SELECT id, name, year FROM movie WHERE year >= 2000; // 특정 값 이상 결과 출력
SELECT DISTINCT year FROM movie ORDER BY year DESC; // 특정 필드에서 고유한 값의 목록 출력
SELECT COUNT(DISTINCT year) FROM movie; // 중복 함수 결과 - 건수 표시
SELECT movieid, genreid FROM moviegenre;
SELECT id, name, gender, age FROM muser;
SELECT id, name, gender, age FROM muser WHERE id <= 10 ORDER BY id ASC;
SELECT id, name, gender, age FROM muser WHERE id > 100 ORDER BY id ASC;
SELECT * FROM muser WHERE id IN (1, 100, 1000);
SELECT * FROM muser WHERE id IN (SELECT userid FROM movierating WHERE rating >= 4); // Subquery for 고객정보
SELECT * FROM movie WHERE id IN (SELECT movieid FROM movierating WHERE rating < 3); // Subquery for 영화정보
SELECT userid, movieid, rating FROM movierating;
SELECT SUM(rating), MIN(rating), MAX(rating), AVG(rating), STDDEV(rating) FROM movierating;
SELECT userid, movieid, rating FROM movierating WHERE userid=6 AND movieid=1806;
SELECT movieid, ROUND(AVG(rating), 2) FROM movierating GROUP BY movieid; // 여러 결과를 묶어서 하나로 표시(연산 함수와 함께)
SELECT movieid, MAX(rating), MIN(rating), ROUND(AVG(rating), 2) AS avr FROM movierating GROUP BY movieid; // 수치연산 함수 적용
SELECT movieid, rating FROM movierating ORDER BY rating ASC , movieid ASC; // 복수 필드로 정렬
SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid ORDER BY average DESC LIMIT 10; // 숫자 표시형식 지정
SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid HAVING AVG(rating) > 4.50 ORDER BY average DESC; // 영화별
SELECT userid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY userid HAVING AVG(rating) > 4.68 ORDER BY average DESC; // 고객별
SELECT id, name FROM occupation;
// SELECT name FROM muser WHERE birthdate ='2020-10-20' // 날짜형에 대한 일치 검색
// SELECT name FROM muser WHERE birthdate BETWEEN '1980-01-01' AND '2000-12-31' // 날짜형에 대한 구간 검색
// JOIN Query 예제
상영된 영화의 년도별 상영 건수를 최신 년도 순으로 보여라
SELECT
year AS 상영연도,
count(*) AS 영화편수
FROM movie
GROUP BY year
ORDER BY year DESC;
년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화기준)
SELECT
a.year AS 상영연도,
a.id AS 영화ID,
b.id AS 장르ID,
SUBSTR(a.name,1, 30) AS 영화제목,
b.name AS 영화분류
FROM movie a
LEFT JOIN (
SELECT c.movieid,
g.name,
g.id
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid = g.id) b
ON a.id = b.movieid
ORDER BY a.year DESC, a.name ASC;
// movie를 기준으로 moviegenr
년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화평가기준)
SELECT
a.year AS 상영연도,
a.id AS 영화ID,
b.id AS 장르ID,
SUBSTR(a.name,1, 30) AS 영화제목,
b.name AS 영화분류
FROM movie a
RIGHT JOIN (
SELECT
c.movieid,
g.name,
g.id
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid =g.id) b
ON a.id = b.movieid
ORDER BY a.year DESC, a.name ASC;
// moviegenre를 기준으로 moviegenre에 RIGHT JOIN함. moviegenre에 movieid 3건이 movie에 없음
// postgresSQL은 null이 맨처음에 표시되나 MySQL은 맨끝 결과에 표시됨
SELECT *
FROM moviegenre
WHERE movieid NOT IN
(
SELECT id FROM movie
);
SELECT * FROM movie WHERE id = 2821; // movielens에 있는 영화 2821이 movie에 없음
MariaDB [movielens]> SELECT * FROM movie WHERE id = 2821;
Empty set (0.000 sec)
영화정보와 영화별 장르정보에 모두 포함된 레코드만 보여라.(아래 2개의 결과건수와 movie 기준 LEFT JOIN 건수가 동일)
SELECT *
FROM movie a
INNER JOIN moviegenre g
ON a.id = g.movieid;
SELECT *
FROM moviegenre g
INNER JOIN movie a
ON a.id = g.movieid;
상영된 영화를 년도별 장르별 건수를 년도와 장르순으로 보여라
SELECT
a.year AS 상영연도,
b.name AS 영화분류,
count(*) AS 상영건수
FROM movie a
LEFT JOIN (
SELECT c.movieid,
g.name
FROM moviegenre c
LEFT JOIN genre g
ON c.genreid = g.id) b
ON a.id = b.movieid
GROUP BY a.year, b.name
ORDER BY a.year DESC, b.name ASC;
고객들 명단을 직업과 함께 표시하라
SELECT
a.name AS 고객명,
a.gender AS 성별,
a.age AS 나이,
b.name AS 직업
FROM muser a
LEFT JOIN (
SELECT id,
name
FROM occupation) b
ON a.occupationid = b.id
ORDER BY a.name ASC, a.age ASC;
고객들의 연령별 인원수를 집계하라.
SELECT b.agerange AS 연령대,
LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 인원수
FROM (
SELECT
CASE
WHEN age < 20 THEN '20 이하'
WHEN age < 30 THEN '20~29'
WHEN age < 40 THEN '30~39'
WHEN age < 50 THEN '40~49'
WHEN age >= 50 THEN '50 이상'
END AS agerange
FROM muser) b
GROUP BY b.agerange
ORDER BY b.agerange ASC;
연령대별 평점 평균을 계산하라
SELECT
a.agerange AS 연령대,
LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 인원수,
ROUND(AVG(a.rating),2) AS 평점
FROM (
SELECT
CASE
WHEN d.age < 20 THEN '20 이하'
WHEN d.age < 30 THEN '20~29'
WHEN d.age < 40 THEN '30~39'
WHEN d.age < 50 THEN '40~49'
WHEN d.age >= 50 THEN '50 이상'
END AS agerange,
d.rating
FROM (
SELECT b.userid,
c.age,
c.name,
b.movieid,
b.rating
FROM movierating b
LEFT JOIN muser c
ON b.userid = c.id) d
) a
GROUP BY a.agerange
ORDER BY a.agerange ASC;
성별 평점 평균을 계산하라
SELECT
a. gender AS 성별,
LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 인원수,
ROUND(AVG(a.rating), 2) AS 평점
FROM (
SELECT
CASE d.gender
WHEN 'M' THEN '남성'
WHEN 'F' THEN '여성'
END AS gender,
d.rating
FROM (
SELECT b.userid,
c.gender,
b.movieid,
b.rating
FROM movierating b
LEFT JOIN muser c ON
b.userid = c.id) d
) a
GROUP BY a.gender
ORDER BY a.gender ASC;
영화별 평가가 100건 이상인 영화에 대한 평점 평균을 계산하고 평점이 높은 순 10건을 표시하라
SELECT
a.name AS 영화제목,
LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 평가수,
ROUND(AVG(a.rating),2) AS 평점
FROM (
SELECT b.movieid,
c.name,
b.rating
FROM movierating b
LEFT JOIN movie c ON
b.movieid = c.id) a
GROUP BY a.name
HAVING COUNT(*) > 100
ORDER BY AVG(a.rating) DESC LIMIT 10;