이수안컴퓨터연구소MySQL 데이터베이스 한번에 끝내기
1.SQL 기본
- DML -data manipulation language :데이터 조작할 때 씀 (선택,삽입 수정)
- DDL - data definition language : 데이블, 인덱스, 만들때 개체들을 생성 삭제할때 쓰는거
- DCL - data control language :관한을 주거나 권한을 제외시킬때 하는 것.
실행할때는 번개/ctrl + enter
1)SHOW DATABASES
(현재 서버에 어떤 db가 있는지 보기)
2)USE world
(사용할 데이터 베이스 지정)
3)SHOW TABLES
(테이블이 뭐있는지 보여줘)
4)SHOW TABLES STATUS
(테이블의 상태를 보여줘)
5)DESCRIBE city
(특정 테이블의 어떤 열로 되어있는지- 테이블에 대한 속성 정보)
6)country 테이블과 country language 테이블 정보를 둘다보려면 DESCRIBE라고 하고 줄여서
DESCRIBE city;
DESC country;
DESC countrylanguage; #세미콜론은 sql 쿼리가 끝났다라는 의미
7)SELECT는 데이터 베이스에서 가장많이 사용하는 것.
(데이터 가져와)
SELECT # 각 테이블에 대한 전체 테이블을 확인할 수 있다.
* #(asterisk) all을 의미한다. 전체 컬럼을 보여줘라 도시이름만 보고싶으면 NAME을 *위에 써주기 <SELECT Name, Population FROM city;> 위치바꾸면 열 순서도 바뀜
FROM city;
8)WHERE
(특정조건으로 원하는 데이터만 보고 싶을 때)
SELECT
*
FROM city
WHERE Population >= 8000000;
#800만 인구수 이상인 도시들만 보여줘 할때 유용하게 씀 where
SELECT
*
FROM city
WHERE Population < 8000000
AND Population > 7000000;
조건 연산자는 &를 통해 관계연산자를 써서 조건과 관계연산자를 조합해서 데이터를 내가 원하는 조건에 따라 그걸 만족하는 데이터를 보여줘 할때 쓸 수 있다.
한국에 있는 도시들 보기/ 미국에 있는 도시들 보기/ 한국에 있는 도시들 중에 인구 수가 1,000,000이상인 도시 보기
SELECT*
FROM city
WHERE CountryCode = 'KOR' #국가 코드 countrycode
AND Population >=1000000; #백만 이상인 도시를 보여줘
9)BETWEEN
(연속적인 범위를 지정할때 비트윈 &로 지정할 수 있다.)
SELECT*
FROM city
AND Population BETWEEN 7000000 AND 8000000
SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';
However, if a movie has a name of simply ‘J’, it would actually match. This is because BETWEEN goes up to the second value — up to ‘J’. So the movie named ‘J’ would be included in the result set but not ‘Jaws’.
10)IN
(내가 정한것만 보고싶다.)
3개의 도시만 보기
SELECT*
FROM city
WHERE Name IN('Seoul', 'NewYork', 'Tokyo')
한국 미국 일본의 도시만 보기
SELECT*
FROM city
WHERE CountryCode IN('KOR' , 'USA', 'JPN')
11)LIKE
(문자열이 어떤 특정 내용을 포함하는지 안하는지)
📌 언더바와 % 기억하기 -> You might need a space between 'word' and '%' !
SELECT*
FROM city
WHERE CountryCode LIKE 'KO_' #KO랑 KOR인지 헷갈리면 언더바_를 쓰면 된다.
WHERE Name LIKE 'Tel %' #%기호는 어떤것들도 볼수있다. 예를들어서 Tel로 시작되는 도시가 뭔지 기억이 안날때
11)Sub Query
(특정 쿼리안에 하나의 쿼리가 또있는)
SELECT
FROM city
WHERE CountryCode = ( SELECT CountryCode
FROM city
WHERE Name = 'Seoul'); #CountryCode가 KOR인 애들 다 불러오기
12)ANY
(서브쿼리의 여러개의 결과 중 한가지만 만족해도 가능)
(=SOME)
(=ANY 는 IN과 같다.)
SELECT
FROM city
WHERE Population > ANY ( SELECT CountryCode
FROM city
WHERE District = 'New York');
# 6개 주에서 가장 인구수가 작은 주보다 크면 다 출력
12)ALL
(서브쿼리의 여러개의 결과를 만족시켜야 함 -> 가장 높은 값을 만족하는)
SELECT
FROM city
WHERE Population > ALL ( SELECT CountryCode
FROM city
WHERE District = 'New York');
#뉴욕(인구수 젤 많음)의 인구수보다 인구수가 많은 도시를 보여줘
13) ORDER BY
(결과가 출력되는 순서를 조절하는 구문)
내림차순
SELECT
FROM city
ORDER BY Population DESC #내림차순
오름차순 (ASC 생략가능)
SELECT
FROM city
ORDER BY Population #오름차순
혼합
SELECT
FROM city
ORDER BY CountryCode ASC, Population DESC #코드는 어센딩, 인구는 디센딩 (혼합)
📌 >인구수로 내림차순하여 한국에 있는 도시보기 국가 면적 크기로 내림차순하여 나라보기
SELECT
FROM city
WHERE CountryCode = 'KOR' #한국에 있는 도시를 인구별로 내림차순
ORDER BY Population DESC;
SELECT*
FROM country
ORDER BY SurfaceArea DESC; #국가에 대한 테이블에서 국가면적을 내림차순으로 쫙
#DESC country;
14) DISTINCT
(중복된 것은 1개씩만 보여주면서 출력)
SELECT DISTINCT CountryCode
FROM city; #도시에 코드가 여러개 있다. 한국만해도 도시별 KOR이 여러개
15) LIMIT
(출력 개수를 제한)
SELECT DISTINCT CountryCode
FROM city;
ORDER BY Population DESX
LIMIT 10
워크벤치에서도 테이블에 수백만개의 데이터가 있다... 그럼 셀렉터하면 난리난다.
워크벤치에서도 리밋이 있어서 제한 가능..... 우리가 리밋을 안써도 워크벤치가 알아서 리밋을 건다.
LIMIT always goes at the very end of the query. Also, it is not supported in all SQL databases.
16) GROUP BY
(그룹으로 묶어주는 역할)
SELECT CountryCode, MAX(Population) #여러개의 countrycode로 묶어주는데 그것에 대한 집계함수 사용가능.
#제일 큰거 보여줘 max, 작은 min, 평균 avg --> 집계함수쓰면 된다.
FROM city
GROUP BY ContryCode #그룹으로 묶어준다. countrycode 기준으로 묶어서 보여줘!
SELECT CountryCode, AVG(Population) AS 'Average' #컬럼 이름을 average로 바꾸기
#원래는 AVG(Population)로 보임.
FROM city
GROUP BY ContryCode #그룹으로 묶어준다. countrycode 기준으로 묶어서 보여줘!
17) 도시는 몇개인가? 도시들의 평균 인구수는?
SELECT COUNT(*) #asterisk는 all을 의미한다.
#즉 city 테이블 전체의 개수를 구하면 도시가 몇개인지 나온다.
FROM city
SELECT AVG(Population)
FROM city
18) HAVING
(WHERE과 비슷한 개념으로 조건 제한)
groupby 했을때 가장 인구수가 높은걸 보여줘
SELECT CountryCode,MAX(Population)
FROM city
GROUP BY CountryCode
groupby 했을때 조건을 걸고싶다. where과 같이 select에 조건을 걸어주는데 집계함수일때 해빙. --> 반드시 group by 뒤에 와야됨.
SELECT CountryCode,MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000
19) ROLLUP
(총합 또는 중간합계가 필요한 경우 사용)
SELECT CountryCode,Name, SUM(Population)
FROM city
GROUP BY CountryCode, Name WITH ROLLUP
20)JOIN
(join은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현)(여러개의 테이블을 한번 조합해보자)
SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code
#city의 countrycode와 countrycode가 같은걸 기준으로 조인을해줘.
SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code
#city의 countrycode와 countrycode가 같은걸 기준으로 조인을해줘.
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode
#3개의 테이블이 조인된 형태로 결과를 출력한다.
21) CASE
A CASE statement allows us to create different outputs (usually in the SELECT statement). It is SQL’s way of handling if-then logic.
Suppose we want to condense the ratings in movies to three levels:
- If the rating is above 8, then it is Fantastic.
- If the rating is above 6, then it is Poorly Received.
- Else, Avoid at All Costs.
SELECT name,
CASE
WHEN imdb_rating > 8 THEN 'Fantastic'
WHEN imdb_rating > 6 THEN 'Poorly Received'
ELSE 'Avoid at All Costs'
END
FROM movies;
- Each WHEN tests a condition and the following THEN gives us the string if the condition is true.
- The ELSE gives us the string if all the above conditions are false.
- The CASE statement must end with END.
SELECT name,
CASE
WHEN imdb_rating > 8 THEN 'Fantastic'
WHEN imdb_rating > 6 THEN 'Poorly Received'
ELSE 'Avoid at All Costs'
END AS 'Review'
FROM movies;
2. mysql의 내장함수
문자열/수학/날짜와시간함수
1) LENGTH()
SELECT LENGTH('asdfasdf')
8
2) CONCAT()
SELECT CONCAT('My', 'sql Op', 'en Source'),
CONCAT('My', NULL 'en Source'); #문자열 중 하나라도 NULL값이면 null로 반환한다.
3) LOCATE()
SELECT LOCATE('abc', 'abaabcaba')
4
프로그래밍 언어들이 인덱스 셀때 첫번째가 0인데 mysql은 1이다.
4) LEFT(), RIGHT()
SELECT
LEFT('MySQL is an open source relational database management system', 5),
RIGHT('MySQL is an open source relational database management system', 6);
왼쪽에서 5개 -> MySQL
오른쪽에서 6개 -> system
5) LOWER(), UPPER()
SELECT
LOWER('MySQL is an open source relational database management system'),
UPPER('MySQL is an open source relational database management system');
소문자
대문자
6) REPLACE
SELECT REPLACE('MSSQL', 'MS' My')
mssql에서 ms라는 문자열을 my라는 문자로 바꿔줘!
7) TRIM()
문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거한다.
TRIM()함수에서 사용할수있는 지정자
-BOTH:전달받은 문자열의 양끝에 존재하는 특정 문자를 제거(기본설정)
-LEADING: 전달받은 문자열 앞에 존재하는 특정문자를 제거
-TRAILING: 전달받은 문자열 뒤에 존재하는 특정 문자를 제거
SELECT TRIM(' MySQL '),
TRIM(LEADING '#' FROM '####MySQL##'),
TRIM(TRAILING '#' FROM '###MySQL##');
공백을 주었지만 trim이라는 함수는 공백을 없애버림
앞에 #제거
뒤에 #제거
8) FORMAT()
숫자 타입의 데이터를 세 자리마다 쉼표를 사용하는 '#,###,###.##'형식으로 변환
SELECT FORMAT(123123123123.123123,3);
뒷부분 인자는 소숫점 몇개? 3개.
123,123,123,123.123
9) FLOOR(), CEIL(), ROUNT()
SELECT FLOOR(10.95), CEIL(10.95), ROUND(10.95)
내림, 올림, 반올림
10, 11, 11
10) SQRT(), POW(), EXP(), LOG()
SQRT() : 양의 제곱근
POW() : 첫번째 인수로는 밑수를 전달하고. 두번째 인수로는 지수를 전달하여 거듭제곱 계산
EXP() : 인수로 지수를 전달받아, e의 거듭 제곱을 계산
LOG() : 자연로그 값을 계산
SELECT SQRT(4), POW(2,3), EXP(3), LOG(3)
2, 8, 20.08555369231, 1.0986122886681098
11) SIN(), COS(), TAN()
SELECT SIN(PI()/2), COS(PI()), TAN(PI()/4)
PI()라는 함수도 있다..
12) ABS(), RAND()
ABS():절대값을 반환
RAND(): 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성
SELECT ABS(-3), RAND(), ROUND(RAND()*100, 0);
RAND랜덤함수를 그냥 쓰면 안되고 실수값을 줘야되는데 range를 주기 힘드니 round로 함.
📌13) NOW(), CURDATE(). CURTIME()
NOW(): 현재 날짜와 시간을 반환, 반환되는 값은 'YYYY-MM-DD-HH:MM:SS' 또는 'YYYYMMDDHHMMSS'
CURDATE(): 현재 날짜를 반환, 이때 반환되는 값은 'YYYY-MM-DD' 또는 붙여서
CURTIME(): 현재 시각을 반환, 이때 반환되는 값은 'HH:MM:SS'또는 HHMMSS 형태
현재 시간과 날짜 합친게 now()
14) DATE(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()
DATE(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()
날짜, 월, 일, 시간, 분, 초
15) MONTHNAME(), DAYNAME()
현재 시간의 월 이름, 요일 이름 알려줘
16) DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()
DAYOFWEEK(): 일자가 해당주에서 몇번째 날인지, 1부터 7사이의 값을 반환 (일요일 =1, 토요일 =7)
DAYOFMONTH(): 일자가 해당월에서 몇번째 날인지, 0-31사이의 값 반환
DAYOFYEAR(): 일자가 해당 연도에서 몇번째 날인지, 1-366 사이의 값 반환
17) DATE_FORMAT()
전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환
SELECT
DATE_FORMAT(NOW(), '%D &y %a %d %m %n %j')
'STUDY > SQL' 카테고리의 다른 글
SQL 문제를 풀어보자 (0) | 2021.05.09 |
---|---|
SQL 입문하기 - 1주차 (0) | 2021.05.09 |
[생활코딩] 관계형 데이터 모델링 (26-32강의 : 물리적데이터 모델링/역정규화) (0) | 2021.05.09 |
[생활코딩] 관계형 데이터 모델링 (22-25강의 : 정규화) (0) | 2021.05.09 |
[생활코딩] 관계형 데이터 모델링 (15-21강의 : 논리적 데이터 모델링) (0) | 2021.05.09 |