Role과 사용자 만들기 / select 연습

실습: 1.

“testuser”라는 database 사용자 만들기

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '1111';

“testuser”에게 “mywork”database에대한 모든 권한 할당 (workbench)

GRANT ALL PRIVILEGES ON mywork.* TO 'testuser'@'localhost';

결과

“testuser”사용자의 모든 권한보기

SHOW GRANTS FOR 'testuser'@'localhost';

testuser connection test result

권한 제거

REVOKE ALL PRIVILEGES ON mywork.* FROM 'testuser'@'localhost';

testuser에 "mywork" 데이타베이스 삭제 됨.

역할 만들고 권한 주기

권한에 사용자 넣고 적용하기

GRANT testrole TO 'testuser'@'localhost';
SET DEFAULT ROLE ALL TO 'testuser'@'localhost';

testuser 화면에 mywork 데이터베이스 다시 생성 됨.

역할 제거하기

REVOKE testrole FROM 'testuser'@'localhost';

testuser 화면에서 mywork 데이터베이스 삭제 됨.

---

select문 연습

world 데이터베이스 city 테이블 정보보기

city 정보 보기

select id, name from city;

# 모든 컬럼
select * from city;

where 절 연습

SELECT *
  FROM city
 WHERE countrycode = 'KOR';

LIKE 연산자

 SELECT *
  FROM city
 WHERE countrycode = 'KOR'
   AND district LIKE 'K%';
   
   --컬럼 'district'값 중 k단어로 시작하는 값만 출력 됨.

 SELECT *
  FROM city
 WHERE countrycode = 'KOR'
   AND district LIKE '%K';
   
   --컬럼 'district'값 중 k단어로 끝나는 값만 출력 됨.

 SELECT *
  FROM city
 WHERE countrycode = 'KOR'
   AND district LIKE '%ong%';
   
   -- 가운데 distriict 칼럼에서 ong값만 출력

AND 연산자 일부러 거짓으로 만들어 테스트 해보기 (예상 값 : 아무것도 출력 안됨)

 SELECT *
  FROM city
 WHERE countrycode = 'KOR'
   AND 2>3;

 SELECT *
  FROM city
 WHERE countrycode = 'KOR'
   AND NOT 2>3;

---

IN 연산자

SELECT *
  FROM city
 WHERE countrycode = 'KOR'
   AND district IN ('seoul', 'kyonggi');
   
   -- 경기, 서울만 출력

다른 표현 방식

SELECT *
    FROM city
  WHERE countrycode = 'KOR'
   AND (district = 'seoul' or district = 'kyonggi');

---

인구가 1억명이 넘는 조건 출력

SELECT code, name, continent, region, population
  FROM country
 WHERE population > 100000000;

4천5백만 ~ 5천5백만 사이 출력

SELECT code, name, continent, region, population
  FROM country
 WHERE population >= 45000000
   AND population <= 55000000

다른 표현

SELECT code, name, continent, region, population
  FROM country
 WHERE population BETWEEN 45000000 AND 55000000 ;

---

mywork 데이터베이스로 where절 연습

use mywork;

실습 1

# 2018년 개봉한 한국 영화 출력하기
select movie_name, release_date, countries
    from box_office
    where release_date >= '2018-01-01'
    AND release_date <= '2018-12-31'
    and countries = '한국';

실습 2

# 2019년 개봉 영화 중 관객수가 500만 명 이상인 영화 조회하기
select movie_name, release_date, countries
    from box_office
    where audience_num >= 5000000
    and release_date BETWEEN '2019-01-01' and '2019-12-31';

실습 3

# 2019년 개봉 영화 중 관객수가 500만 명 이상이거나 매출액이 400억 원 이상인 영화 조회하기
select movie_name, release_date, countries, sale_amt, audience_num
from box_office
where audience_num >= 5000000
and sale_amt >= 40000000000
and release_date BETWEEN '2019-01-01' and '2019-12-31';

실습 4

# mywork 데이터베이스에 있는 box_office 테이블에서 2012년 제작됐지만, 2019년에 개봉된 영화를 조회하는 쿼리를 작성하세요.
select movie_name, release_date, years
from box_office
where years = 2012
and release_date BETWEEN '2019-01-01' and '2019-12-31';

---

order by

#오름차순
SELECT code, name, continent, region, population
  FROM country
 WHERE population > 100000000
 ORDER BY population ASC;
 
 #내림차순
 SELECT code, name, continent, region, population
  FROM country
 WHERE population > 100000000
 ORDER BY population DESC;

컬럼 지정 순서 지정

 SELECT name, continent, region 
  FROM country
 WHERE population > 50000000
 ORDER BY continent, region;
 
 # continent 오름차순 후 region 오름차순 적용

 

SELECT *
  FROM box_office
 WHERE release_date BETWEEN '2019-01-01' AND '2019-12-31'
 ORDER BY sale_amt DESC
 LIMIT 5;

SELECT *
  FROM box_office
 WHERE years = 2019
 ORDER BY screen_num DESC
 LIMIT 10

 

self check

#1 world 데이터베이스의 countrylanguage 테이블에는 국가별 사용 언어 데이터가 들어 있습니다. 
#이 테이블의 percentage 칼럼에는 해당 언어가 사용되는 비율 값이 들어 있는데, 
#99% 이상인 건을 국가 순으로 조회하는 쿼리를 작성하세요.
select *
from world.countrylanguage
where Percentage >= 99
ORDER BY CountryCode ;

#2 world 데이터베이스에 접속된 상태일 때, 
#mywork 데이터베이스에 있는 box_office 테이블에서 
#2019년 제작된 영화 중 순위(ranks)가 1위에서 10위까지인 영화를 순위별로 조회하는 쿼리를 작성하세요.
select * 
from mywork.box_office
where release_date BETWEEN '2019-01-01' and '2019-12-31'
ORDER BY ranks
LIMIT 10;

#3 mywork 데이터베이스로 이동해 box_office 
#테이블에서 2019년 제작된 영화 중 영화 유형(movie_type 칼럼)이 장편이 아닌 영화를 
#순위(ranks)대로 조회하는 쿼리를 작성하세요.
select ranks, movie_name, movie_type
from mywork.box_office
where release_date BETWEEN '2019-01-01' and '2019-12-31'
and movie_type = '장편'
ORDER BY ranks
LIMIT 10;

#4 box_office 테이블에서 2019년 제작된 영화 중 스크린수 기준 상위 10개 영화를 조회하는 쿼리를 작성하세요.
select screen_num, movie_name, movie_type
from mywork.box_office
where release_date BETWEEN '2019-01-01' and '2019-12-31'
ORDER BY screen_num desc
LIMIT 10;