select
select * from 테이블명;
테이블 구조 참조
desc 테이블명;
열 지정
select no, name from 테이블명;
행지정 where
select * from sample where no=2;
select * from sample where name='a';
select * from sample where name is null;
<> 아닌 행
select * from sample no <>2;
and
select * from sample where a<>0 and b<>0;
or
select * from sample where a<>0 or b<>0;
% 임의의 문자열
_ 임의의 문자 하나
like
select * from sample where text like 'sql%';
이스케이프
select * from s where text like '%\%%';
정렬(desc내림차순 asc오름차순)
select * from s order by age desc
복수열 정렬
select * from s order by a asc, b desc;
limit(mysql)
select * from s limit 3;
top(sql server)
select top 3 * from s;
rownum(오라클)
select * from s where rownum<=3;
오프셋: 시작위치
select * from s limit 3 offset 0;
계산
select * , price*quantity "금액" from s;
where 에서 계산(별명 사용 불가-처리순서)
select * , price*quantity "금액" from where price*quantity>=2000;
null 연산결과는 null
order by에서 계산 (별명사용가능)
select * , price*quantity amount from s order by amount desc;
처리순서 where> select> order by
round 반올림 첫째자리까지 나타내
select amount ,round(amount,1) from s;
문자열결합(concat, ,||,+)
select concat(quantity,unit) from s;
날짜확인
select current_timestamp
1일후
select current_date + interval 1 day;
case
select case when a is null then 0 else a end "a(null=0)" from s;
coalesce
select coalesce(a,0) from s;
검색 case
select
case
when a=1 then '남자'
when a=2 then '여자'
else '미지정'
end as "성별" from s;
단순 case(null 불가)
select
casea
when 1 then '남자'
when 2 then '여자'
else '미지정'
end as "성별" from s;
insert
insert into s values(1,'abc','2022-06-12');
insert into s (no,a,b) values(1,'abc',null);
delete
delete from s where no=3;
update
update s set a='bcd' where no=1;
count null 무시
select count(*) from s;
distinct
select distinct name from s;
sum null무시
select sum(quantity) from s;
avg
select avg(case when quantity is null then 0 else quantity end) as a from s;
group by
select sum(quantity) from m group by name having sum(quantity)=1;
서브쿼리
delete from s where a=(select min(a) from s); mysql에서 불가
select (select count (*) from s) as a1, (select count (*) from s) as a2;
update s set a= (select max(a) from s);
select * from (select * from s) t;
오라클 limit 대체
select * from (select * from s order by desc) t where rownum <=2;
테이블 행 복사
insert into s1 select * from s2;
(not) exists 상관서브쿼리
update s set a='o' where exists (select * from s2 where s2.no2 = s.no);
in
select * from s where no in (select no2 from s2);
데이터베이스 객체: 테이블, 뷰, 인덱스...
create
create table t (no integer not null, a varchar(30));
삭제
drop table t;
truncate table t;
delete from t;
alter
alter table t add age integer;
열 속성 변경
alter table t modify col varchar(10);
열 이름 변경
alter table t change col col2 varchar(10);
열삭제
alter table t drop col;
제약정의
create table t (a integer not null, constraint keyname primary key (a));
제약추가
alter table t modify a varchar(10) not null;
alter table t add constraint keyname primary key(a);
제약 삭제
alter table t modify a varchar(10);
alter table t drop constraint keyname;
alter table t drop primary key;
인덱스 작성
create index iname on t(no);
인덱스 삭제
drop index iname;
drop index iname on t;
explain 인덱스 사용? 실행계획 확인
explain select * from t where no =1;
view 작성
create view vname as select * from t;
select * from vname;
열 지정 view 작성
create view vname(a,b,c) as select aa,bb,cc from t;
view 삭제
drop view vname;
<union> 열내용 일 중복제거 <-> union all
select no1 as n from a
union
select no2 as n from b order by n;
교차결합 cross join
select * from a,b;
inner join
select * from a,b
where a.no=b.no
and a.no=3;
->
select a.no, b.name
from a inner join b
on a.no=b.no
where a.no=3;
outer
select a.no, b.name
from a left join b
on a.no=b.no
where a.no>3;