MySQL 정리

강의자료: https://www.youtube.com/watch?v=vgIc4ctNFbc

SHOW DATABASES

• 현재 서버에 어떤 DB가 있는지 보기

USE

• 사용할 데이터베이스 지정
• 지정해 놓은 후 특별히 다시 USE문 사용하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL 문은 지정 DB에서 수행
• USE database_name;
• 또는 Workbench에서 직접 선택해 사용 가능(네비게이터->스키마->데이터베이스 선택)

SHOW TABLE

• SHOW TABLES;
• 데이터베이스 world이 갖고있는 테이블 이름 보기

DESCRIBE(DESC)

• city 테이블에 무슨 열이 있는지 확인
  • DESCRIBE city;
  • DESC city;
• country 테이블과 countrylanguage 테이블 정보 보기
  • DESC country;
  • DESC countrytablelanguage;

SELECT

• 요구하는 데이터를 가져오는 구문
• SELECT select_expr [FROM table_references] [WHERE where_condition] [GROUP BY {col_name | expr | position}] [HAVING where_condition] [ORDER BY {col_name | expr | position}]

SELECT *

• 테이블에 있는 모든 열 다 가져옴

SELECT FROM WHERE

• SELECT 필드이름 FROM 테이블이름 WHERE 조건식;
• SELECT Name, Population FROM city WHERE Population > 10000000;
• 한국에 있는 도시들 보기
  • SELECT * FROM city WHERE CountryCode=’KOR’;
• 한국에 있는 도시들 중에 인구수가 1,000,000 이상인 도시 보기
  • SELECT * FROM city WHERE CountryCode=’KOR’ AND Population >= 1000000;

MySQL 함수 및 연산자

https://dev.mysql.com/doc/refman/8.0/en/functions.html

BETWEEN

• 데이터가 숫자로 구성되어 있어 연속전인 값은 BETWEEN…AND 사용 가능
• SELECT * FROM city WHERE Population BETWEEN 7000000 AND 8000000;

IN

• SELECT * FROM city WHERE Name IN (‘Seoul’, ‘New York’);
• 한국, 미국, 일본의 도시들 보기
  • SELECT * FROM city WHERE CountryCode IN (‘KOR’, ‘USA’, ‘JPN’);

LIKE

• 문자열의 내용 검색
• %: 문자 뒤에 무엇이든 허용
• _: 한 글자와만 매치
• SELECT * FROM city WHERE CountryCode LIKE ‘KO_’;

Sub Query

• 쿼리문 안의 쿼리. 서브쿼리 결과가 둘 이상이면 에러발생
• 서울이 있는 나라의 국가코드와 같은 국가코드를 갖고있는 모든 결과 출력
• SELECT * FROM city WHERE CountryCode = (SELECT CountryCode FROM city WHERE Name = ‘Seoul’);

ANY

• 서브쿼리의 여러개의 결과 중 한 가지만 만족해도 가능
• SOME은 ANY와 동일한 의미
• =ANY는 IN과 동일한 의미
• SELECT * FROM city WHERE Population >ANY (SELECT Population FROM city WHERE Name = ‘Seoul’);

ALL

• 서브쿼리의 여러 개의 결과를 모두 만족시켜야 함
• SELECT * FROM city WHERE Population >ALL (SELECT Population FROM city WHERE Name = ‘Seoul’);

ORDER BY

• 결과가 출력되는 순서
• 오름차순 ASC, 내림차순 DESC, 아무것도 안쓰면 기본으로 ASC
• SELECT * FROM city ORDER BY Population DESC;
• 혼합구문도 가능
• 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;

DISTINCT

• 중복된 것은 1개씩만 보여주면서 출력
• 국가코드 중복없이 하나씩만 출력하기
  • SELECT DISTINCT CountryCode FROM city;

LIMIT

• 출력개수 제한
• SELECT * FROM city ORDER BY Population DESC LIMIT 10;

GROUP BY

• 그룹으로 묶어줌, 집계함수 함께 사용
• AVG()-평균, MIN()-최소값, MAX()-최대값, COUNT()-행의 개수, COUNT(DISTINCT)-중복 제외된 행의 개수, STDEV()표준편차, VARIANCE()-분산
• AS쓰면 열 이름 새로 정할 수 있음
• 국가코드 기준으로 그룹화해서 국가코드별로 인구수 최대값 집계하면서 그 열의 값은 Population으로 하기
  • SELECT CountryCode, MAX(Population) AS ‘Population’ FROM city GROUP BY CountryCode;
• 도시는 몇개인가?
  • SELECT COUNT(*) FROM city;
• 도시들의 평균 인구수는?
  • SELECT AVG(Population) FROM city;

HAVING

• WHERE 과 비슷한 조건제한인데, 집계함수에 대해서 조건제한하는 것. 따라서 반드시 GROUP BY 뒤에 쓰임
• SELECT CountryCode, Max(Population) FROM city GROUP BY CountryCode HAVING MAX(Population) > 8000000;

ROLLUP

• 총합 또는 중간합계를 보여줌
• GROUP BY 절과 함께 WITH ROLLUP문 사용
• SELECT CountryCode, Name, SUM(Population) FROM city GROUP BY CountryCode, Name WITH ROLLUP;

JOIN

• 데이터베이스 내의 여러 테이블에서 가져온 레코드(테이블)들을 조합하여 하나의 테이블이나 결과집합으로 표현. 조건을 줄때 ON 사용
• city 테이블을 country테이블과 조인, 근데 city의 국가코드와 country의 국가코드가 같은걸 기준으로 조인할 것
  • SELECT * FROM city JOIN country ON city.CountryCode = country.Code;
• city, country, countrylanguage 테이블 3개 조인
  • SELECT * FROM city JOIN country ON city.CountryCode = country.Code JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode;

MySQL 내장함수

• 문자열 함수
• 수학 함수
• 날짜와 시간 함수

LENGTH()

• 전달받은 문자열의 길이를 반환
• SELECT LENGTH(‘abc’)

CONCAT()

• 전달받은 문자열 결합해 하나로 반환, 그중 하나라도 NULL이면 NULL 반환
• SELECT CONCAT(‘hello’, ‘world’)

LOCATE()

• 문자열 내에서 찾는 문자열이 처음으로 나타나는 위치를 반환
• 찾는게 없으면 0 반환
• 문자열 시작인덱스는 0이 아니라 1
• SELECT LOCATE(‘abc’, ‘avcabcaaa’)

LEFT(), RIGHT()

• LEFT(): 문자열의 왼쪽부터 지정한 개수만큼의 문자를 반환
• RIGHT(): 문자열의 오른쪽부터 지정한 개수만큼의 문자를 반환
• SELECT LEFT(‘abcabcabcabc’, 3)

LOWER(), UPPER()

• LOWER(): 문자열의 문자를 모두 소문자로 변경
• UPPER(): 문자열의 문자를 모두 대문자로 변경
• SELECT LOWER(‘ABCABC’)

REPLACE()

• 문자열에서 특정 문자열을 대체 문자열로 교체
• MSSQL을 MySQL로 교체
  • SELECT REPLACE(‘MSSQL’, ‘MS’, ‘My’)

TRIM()

• 문자열의 특정 문자를 제거
• BOTH: 문자열의 양 끝의 특정문자 제거(디폴트)
• LEADING: 문자열의 앞에 존재하는 특정문자 제거
• TRAILING: 문자열의 뒤에 존재하는 특정문자 제거
• 제거할 문자 명시 안하면 자동으로 공백 제거
• SELECT TRIM(‘ MySQL ‘), TRIM(LEADING ‘#’ FROM ‘###MySQL###’), TRIM(TRAILING ‘#’ FROM ‘###MySQL###’);

FORMAT()

• 숫자 타입을 세 자리마다 쉼표로 구분
• 반환 형식은 문자열 타입
• 두번째 인수는 반올림할 소수 부분의 자릿수
• SELECT FORMAT(123123.123123, 3);

FLOOR(), CEIL(), ROUNT()

• FLOOR(): 내림
• CEIL(): 올림
• ROUND(): 반올림
• SELECT FLOOR(10.95), CEIL(10.95), ROUND(10.95)

SQRT(), POW(), EXP(), LOG()

• SQRT(): 양의 제곱근
• POW(): 첫 번째 인수는 밑수, 두번째 인수는 지수 -> 거듭제곱 계산
• EXP(): 지수를 전달받아 e의 거듭제곱 계산
• LOG(): 자연로그 값 계산
• SELECT SQRT(4), POW(2,3), EXP(3), LOG(3)

SIN(), COS(), TAN()

• SELECT SIN(PI()/2), COS(PI()), TAN(PI()/4)

ABS(), RAND()

• ABS(): 절대값
• RAND(): 0.0~1.0사이의 랜덤실수 생성. ROUND로 반올림하고 RAND에 100곱해서 0~100사이의 랜덤수 생성할수도 있음
• SELECT ABS(-3), RAND(), ROUND(RAND()*100, 0)

NOW(), CURDATE(), CURTIME()

• NOW(): 현재 날짜와 시간
• CURDATE(): 현재 날짜
• CURTIME(): 현재 시각

DATE(), MONTH(), DAY(), HOUR(), MINUTE(), SECOUND()

• DATE(): 날짜정보
• MONTH(): 월 정보 0~12
• DAY(): 일 정보, 0~31
• HOUR(): 시간 정보, 0~23
• MINUTE(): 분 정보, 0~59
• SECOUND(): 초 정보, 0~59
• SELECT NOW(), DATE(NOW()), SECOND(NOW())

MONTHNAME(), DATNAME()

• MONTHNAME(): 월에 해당하는 이름 반환
• DATNAME(): 요일에 해당하는 이름 반환
• SELECT NOW(), MONTHNAME(NOW())

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

• DAYOFWEEK(): 일자가 해당 주에서 몇번째 날인지 반환. 1~7, 일~토
• DAYOFMONTH(): 일자가 해당 월에서 몇번째 날인지 반환, 0~31
• DAYOFYEAR(): 일자가 해당 연도에서 몇번째 날인지 반환, 1~366
• SELECT NOW(), DAYOFMONTH(NOW())

DATE_FORMAT()

• 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환
• SELECT DATE_FORMAT(NOW(), ‘%D %y %a %d %m %n %j’)

CREATE TABLE AS SELECT

• city 테이블과 똑같은 city2 테이블 생성
• CREATE TABLE city2 AS SELECT * FROM city;

CREATE DATABASE

• 새로운 데이터베이스 생성, USE로 새 데이터베이스 사용
• workbencch에서 gui로 테이블들 생성 가능
• CREATE DATABASE chaelin;
• USE chaelin;

데이터 타입

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

CREATE TABLE

• workbencch에서 gui로 테이블들 생성 가능
• 또는 직접 써서 가능
• CREATE TABLE test2( id INT NOT NULL PRIMARY KEY, col1 INT NULL, col2 FLOAT NULL, col3 VARCHAR(45) NULL )

ALTER TABLE

• ALTER TABLE 문과 함께 ADD 문을 사용하면 테이블에 컬럼 추가 가능
• MODIFY 사용하면 컬럼 수정 가능
• DROP 사용하면 컬럼 삭제 가능
• ALTER TABLE test2 ADD col4 INT NULL;
• ALTER TABLE test2 MODIFY col4 VARCHAR(20) NULL;
• ALTER TABLE test2 DROP col4;

Index

• 테이블에서 원하는 데이터를 빠르게 찾기 위해 사용
• 검색, 질의 시 테이블 전체를 안읽기 때문에 빠름
• 설정된 컬럼 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우 인덱스도 함께 수정되어야 함
• 인덱스가 있는 테이블은 처리 속도가 느려질 수 있으므로 수정보다는 검색이 많이 쓰이는 테이블에 사용

CREATE INDEX

• 인덱스를 생성
• CREATE INDEX Col1Idx ON test(col1);

SHOW INDEX

• 인덱스 정보보기
• SHOW INDEX FROM test;
• 기본적인 Primary키와 우리가 만든 Col1Idx가 생성된 것을 볼 수 있음

CREATE UNIQUE INDEX

• 중복 값을 허용하지 않는 인덱스
• 다음을 실행하면 Non_unique가 0으로 뜸. 즉 유니크하단것
• CREATE UNIQUE INDEX Col2Idx ON test(col2);

FULLTEXT INDEX

• 일반적인 인덱스와 달리 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색
• 다음을 싫애하면 Index_type에 FULLTEXT라고 뜸
• ALTER TABLE test ADD FULLTEXT Col3Idx(col3);

INDEX 삭제(ALTER)

• ALTER TABLE test DROP INDEX Col3Idx;

INDEX 삭제(DROP INDEX)

• DROP문은 내부적으로 ALTER 문으로 자동 변환되어 명시된 이름의 인덱스를 삭제
• test테이블에 있는 인덱스 삭제
  • DROP INDEX Col2Idx ON test;

VIEW

• 뷰는 데이터베이스의 가상 테이블
• 실제 테이블처럼 행과 열을 가지고 있지만 실제로 데이터 저장 x
• 다른 테이블이나 다른 뷰에 저장된 데이터를 보여주기만 함
• 뷰를 사용해 여러 테이블이나 뷰를 하나의 테이블처럼 볼 수 있음
• 뷰의 장점
  • 특정 사용자에게 테이블 전체가 아닌 필요한 컬럼만 보여줌
  • 복잡한 쿼리 단순화
  • 쿼리 재사용 가능
• 뷰의 단점
  • 한번 정의된 뷰 변경 x
  • 삽입, 삭제, 갱신에 제한사항 많음
  • 자신만의 인덱스 x

CREATE VIEW

• test테이블에 Col1, Col2를 가지는 뷰 생성
• CREATE VIEW testView AS SELECT Col1, Col2 FROM test;
• SELECT * FROM testView;

ALTER VIEW

• 뷰를 수정
• ALTER VIEW testView AS SELECT Col1, Col2, Col3 FROM test;

DROP VIEW

• 뷰를 삭제
• DROP VIEW testView

city, country, countrylanguage 테이블을 조인하고, 한국에 대한 정보만 뷰 생성하기

• USE world
• CREATE VIEW allView AS SELECT city.Name, country.SurfaceArea, city.Population, countrylanguage.Language FROM city JOIN country ON city.CountryCode = country.Code JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode WHERE city.CountryCode=’KOR’;
• SELECT * FROM allView;

INSERT

• 테이블 이름 다음에 나오는 열 생략 가능
• 생략할 경우 VALUE 다음에 나오는 값들의 순서, 개수가 테이블이 정의된 열 순서, 개수와 동일해야 함
• INSERT INTO test VALUE(1, 123, 1.1, “Test”);
• SELECT * FROM test;
• 또는 워크벤치에서 테이블에 gui로 직접 입력

INSERT INTO SELECT

• test 테이블에 있는 내용을 test2 테이블에 삽입(복사)
• INSERT INTO test2 SELECT * FROM test;
• SELECT * FROM test2;

UPDATE

• 기본에 입력되어 있는 값 변경하는 구문
• WHERE 절 생략 가능하나 테이블의 전체 행의 내용 변경됨. 꼭 WHERE절 쓸 것.
• UPDATE test SET col1=1, col2=1.0, col3=’test’ WHERE id=1;
• SELECT * FROM test;

DELETE

• 행 단위로 데이터 삭제
• DELETE FROM 테이블이름 WHERE 조건;
• WHERE 조건 안쓰면 전체 날아감
• 데이터는 지워지지만 테이블 용량은 줄어들지 않고, 원하는 데이터만 지울 수 있고, 삭제후 복구 가능
• DELETE FROM test WHERE id=1;
• SELECT * FROM test;

TRUNCATE

• 용량 줄어들고, 인덱스 등 모두 삭제
• 테이블은 삭제하지않고 데이터만 삭제
• 한꺼번에 다 지워야하고, 삭제 후 복구 불가
• TRUNCATE TABLE test;

DROP TABLE

• 테이블 전체를 삭제, 공간, 객체 삭제
• 삭제 후 복구 불가
• DROP TABLE test;

DROP DATABASE

• 데이터베이스를 삭제
• DROP DATABASE chaelin;

자신만의 연락처 테이블 만들기(이름, 전화번호, 주소, 이메일)

• 데이터타입: https://dev.mysql.com/doc/refman/8.0/en/data-types.html ``` CREATE DATABASE chaelin; USE chaelin; CREATE TABLE information( id INT NOT NULL PRIMARY KEY, myname VARCHAR(40) NULL, phone INT NULL, address VARCHAR(50) NULL, email VARCHAR(40) NULL );

INSERT INTO information VALUE(1, “chaelin”, 01012345678, “서울시”, “abc@abc.com”);

SELECT * FROM information; ```