Today

강의

MySQL 데이터베이스 한번에 끝내기 SQL Full Tutorial Course using MySQL Database (3회차 완료)

스터디 내용

• SQL 실습 강좌 복습

결과

수, 날짜

select format(123123123.1231, 4);
select floor(10.95), ceil(10.95), round(10.95);
select sqrt(4), pow(2,3), exp(3), log(3);
select sin(pi()/2), cos(pi()), tan(pi()/4);

select abs(-3) as 절대값, rand(), round(rand()*100, 0);

select now();
select curdate();
select curtime();
select now(), date(now()), month(now()), hour(now());
select now(), monthname(now()), dayname(now());
select now(), dayofmonth(now()), dayofweek(now()), dayofyear(now());
select date_format(now(), '%Y-%m-%d');

SQL 고급

create, alter

create table city2 as select * from city;

select * from city2;

drop database pjk;
create database pjk;
use pjk;

create table test (
id int not null primary key,
col1 int null,
col2 varchar(45) not null,
col3 varchar(30) not null);

create table test2 (
id int not null primary key,
col1 int null,
col2 varchar(45) not null,
col3 varchar(30) not null);

select * from test2;

alter table test2 add col4 int null;
select * from test2;
desc test2;

alter table test2 modify col4 varchar(40) null;
desc test2;

alter table test2 drop col4;

create index Col1Idx on test (col1);
show index from test;

create unique index Col2Idx on test (col2);
show index from test;

alter table test add fulltext Col3Idx(col3);
show index from test;

삭제

alter table test drop index Col3Idx;
Drop index Col2Idx on test;

show index from test;

view

create view testview as select col1, col2 from test;
select * from testview;

alter view testview as select col1, col2, col3 from test;
select * from testview;

drop view testview;

use world;

select * from city join country on city.CountryCode = country.Code join countrylanguage on city.CountryCode = countrylanguage.CountryCode;

drop view allview;

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

use pjk;

insert into test value(1,123,1.1,'test');

select * from test;

select * from test2;

insert into test2 select from test;
select from test2;

update

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

delete

delete from test where id = 1; # 롤백 가능. 테이블 용량 자체는 줄지 않음. 휴지통에 넣은 것.
select * from test;

truncate

truncate table test; # 테이블은 놓고 데이터 완전 삭제, 복구 안됨.

drop table

drop table test; # drop 되돌릴 수 없음.

drop database pjk;

Tomorrow

• SQLD 2과목 풀이

Summary

• 시험 전까지 최소 7번은 실습 반복 후 들어가야겠다.