mysql 한번에끝내기

https://www.youtube.com/watch?v=vgIc4ctNFbc 참고함

DML : 데이터 조작(crud) 하는데 사용

DDL : 데이터 정의 (create,drop,alter) 하는데 사용

DCL :데이터 컨트롤(제어) Grant,REVOKE …. (권한)

기본적인 mysql의 world db 사용

SHOW DATABASES :현재 서버에 있는 모든 db 조회

USE world : world db를 사용하겠다.

show tablews ; wolrd의 테이블 보기 status 붙이면 정보까지

describe [tablename] 테이블 정보 보기 (줄여서 desc)

SELECT

where : 조건

관계연산자 사용 가능

or, and , 조건연산자, not, 등

EX)7000000~8000000인구수의 도시 검색

select Name, Population from citywhere population <8000000
and population >7000000;

EX) 한국에있는도시들 보기

select * from city where CountryCode='KOR';

BETWEEN 사이에있는값

EX) 7000000~8000000인구수의 도시 검색

select * from city where population between 7000000 and 8000000;

IN 이산적인 값

select * from city where Name in ('seoul','new york','tokyo');

LIKE 문자열 검색

문자열 뒤에 무엇이든 혀용 한글자일때는 ‘-" , 여러글자일때는 ‘%’ 사용

EX) counttycode가 ‘ko?‘인 거 검색

select * from citywhere countryCode Like 'ko_';

SUB QUERY

쿼리안의 쿼리

EX) 컨트리코드가 (서울이라는 도시의 컨트리코드) 인 정보

 select *  from city
 where countrycode = (select countrycode from city where name ='seoul');

ANY: 서브쿼리의 여러 개의 결과중 하나만 만족해도 가능 (in과 동일)

EX) 인구가 (district가 뉴욕인 모든 city의 인구 중 가장 적은 도시 )보다 높은 정보

 select * from city
where population > ANY  (select population from city where district='New York');

All: ANY와 달리 모든 서브쿼리의 결과를 만족시켜야 함

ORDER BY (정렬 default=ASC) 여러기준 가능

EX) countrycode 기준으로 오름차순, 만일 같으면 인구수기준으로 내림차순

select * from cityorder by countrycode asc, population desc;

DISTINCT( 중복된건 1개만)

select countrycode from city;select distinct countrycode from city;

LIMIT (출력 개수 제한)

EX) 인구수 기준으로 10개만

select * from cityorder by population desclimit 10;

Group By

그룹으로 묶어주는 역활

별칭 사용

집계함수 사용

AVG(),

MIN(),

MAX(),

COUNT(),

COUNT(DISTINCT) 중복제거된 개수

,STDEV() 표준편차

VARIANCE() 분산

EX) 가장 큰 population을 mp, 그리고 countrycode

를 countrycode별로 알려줌

select countrycode, max(population) as mpfrom citygroup by countrycode

HAVING: where과 비슷한 개념으로 조건 제한 (집계함수에 대하여)

무조근 groupby 다음에 나와야 함

EX) max(population)이 800만을 초과되는 countrycode, max(population)

select countrycode, max(population) from citygroup by countrycode 
having MAX(population) >8000000;

ROLLUP:총합 또는 중간합계가 필요한 경우 사용

groupby와 withrollup문

ex) countrycode로 묶는데, 묶은것들의 총합까지 보여줌 (해보면 암)

select countrycode, name, sum(population)from city
group by countrycode, name with rollup

JOIN !!!!!!따로공부

select * from cityjoin country on city.countrycode = country.code
join countrylanguage on city.countrycode=countrylanguage.countrycode

내장함수

length(): 길이

정확히 바이트를 말한다. 왜인진모르겠는데 한글은 3byte다.

select lemngth('aaaaa')  select lemngth('다섯글자임')

concat(): 문자열결합

null있으면 무조건 null됨

select concat('my','sql op','en source');

location() : 문자열이 처음으로 나타나는 위치반환

없으면 0 (1부터계산)

select locate('abc', 'ababababababc')

left() & right() 문자열의 왼,오에서 지정한 개수만큼 반환

select left('mysql is an open source realation database,',5)

LOWER(), UPPER() 대소문자 변경

REPLACE(), 대체 문자열로 교체

select replace('mssql','ms','my');

trim(): 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자 제거 (자동으로 공백)

지정자:

both =양끝

leading = 앞

traling =뒤

select trim('       mysql        '),trim( LEADING '#' from '###mysql###'),
trim( trailing '#' from '###mysql###');

FORMAT() : 숫자타입을 새자리마다 쉼표를 사용하는 형식으로 변경 (문자열로 반환됨), 두번째인수는 반올림할 소수부분

select format(123123123123123.123123123123,3);

floor(), cell(), round() 가능

날짜

now(): 현재날짜, 시간반환

curdate():현재 날짜반환

curtime():현재시간반환

Date_format() 날자와 시간정보 포멧팅

select Date_format(now(),'%d %y %a %d %m %n');

고급(ddl)

city와 같은 city2 테이블 생성

Create table city2 as select * from city;

새로운 db 생성

create database jiheon;use jiheon;

테이블

CREATE TABLE `jiheon`.`test` (  `id` INT NOT NULL,  `col1` INT NULL,
  `col2` FLOAT NULL,  `col3` VARCHAR(45) NULL,  PRIMARY KEY (`id`));

테이블에 컬럼 추가(Alter)

col4 int null로 추가

Alter Table testadd col4 int null;

modify: 컬럼속성 변경

col4를 int에서 varchar로 변경

Alter Table testadd col4 int null;

drop: 컬럼 제거

Alter Table testdrop col4;

인덱스

-테이블에서 원하는데이터를 빠르게 찾기 위해 사용

-검쌕과 질의를 할 때 테이블 전체를 읽지 않기 때문에 빠름

-수정보다는 검색에 유리 (수정할때 인덱스도 수정해야됨)

CREATE INDEX :인덱스만들기

create index col1idxon test(col1)

SHOW INDEDX : 인덱스 정보보기

show index from test;

create unique index :중복값 허용안하는 인덱스

create unique index col2idxon test (col2);show index from test;

Fulltext index

매우 빠르게 테이블의 모든 텍스트 컬럼을 검색

Alter table testadd fulltext col3idx(col3);show index from test;

index 삭제 (2가지방법)

Alter문

Alter table testdrop index col3idx;show index from test;

그냥 drop만 사용

drop index col2idx on test;show index from test;

VIEW

데이터베이스에 존재하는 일종의 가상 테이블

만들기 col1과 col2를 가지는 테스트뷰

CREATE view testview asselect col1, col2from test;select * from testview

Alter문 적용 가능

alter view testview asselect col1, col2, col3from test;select * from testview

삭제

drop view testview

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

create view allview asselect c.name, co.surfacearea, c.population, cl.language
from city c, country co, countrylanguage cl
where c.countrycode = co.code and c.countrycode = cl.countrycode and c.countrycode='kor';
select * from allview;

INSERT

db 전환후

Insert into testvalue(1, 123, 1.1 ,'test');select * from test;

UPDATE

기존값 변경

where 안쓰면 전체행 내용 변경

update testset col1 =1, col2=1.0, col3='test'where id= 1;select * from test

Delete

commit 하지 않았으면 롤백 가능

Delete from testwhere id=1;

Truncate

테이블삭제는 안하지만, 데이터만 삭제 (롤백 불가!)

drop table []

그냥 삭제 저절로 커밋됨

drop database jiheon

db삭제