< SQL 요약 정리 >

//======================================================================

* SELECT 칼럼들,... FROM 테이블
    WHERE 칼럼='문자값', 칼럼<=수,  칼럼 LIKE='%문자%' ,... (AND|OR)
    ORDER BY 칼럼 ASC(작은수,오름)|DESC(큰수,내림)
    LIMIT 0,100 (첫번째 부터~100번째 까지)

* INSERT INTO 테이블 VALUES ( 값들,...)
  INSERT INTO 테이블(칼럼들,...) VALUES ( 값들,...)

* UPDATE 테이블 SET 칼럼=값,...
    WHERE 칼럼=값,...

* DELETE FROM 테이블
    WHERE 칼럼=값,...

//======================================================================

http://www.databasedesign.co.uk/sqlselectshortsummary.htm

SQL SELECT statement - short summary

Function
Example

select
from
select * from customer
select c_no, sname from customer

distinct
select distinct c_no from invoice

order by
select * from customer order by sname
select * from customer order by city, balance desc

where
select * from customer where city = ‘London’ and balance <= cred_lim

between
select * from invoice where inv_date between #10-dec-99# and #14-1-00#

like
select * from customer where sname like ‘Dz*’

in
select * from customer where city in (‘London’, ‘Leeds’)

avg,count,max,
min,sum,var,
stddev
select sum(balance) from customer
select count(*) from customer
select sum(balance) as TotalBalance from customer
select sum(balance), max(cred_lim) from customer

group by
select city, sum(balance) from customer group by city
select city, max(balance) as [Highest balance for this city] from customer group by city

having
select city, sum(balance) group by city having sum(balance) > 500

top
select top 2 * from customer order by balance desc
select top 1 city, sum(balance) from customer group by city order by sum(balance) desc
select top 20 percent * from customer order by balance desc

inner join
select a.c_no, sname, inv_no, amount from customer as a inner join invoice as b on a.c_no = b.c_no where city = ‘London’ and balance > 100

left join
select a.c_no, sname, inv_no, amount from customer as a left join invoice as b on a.c_no = b.c_no where city = ‘London’ and balance > 100

subquery
select * from customer where city = (select city from customer where sname = ‘Sallaway’)
select * from customer where c_no not in (select c_no from invoice)

any, all
select * from employee where salary < any (select salary from employee)
select * from employee where salary >= all (select salary from employee)

exists, not exists
select * from customer where not exists (select * from invoice where customer.c_no = invoice.c_no)

union
select * from violinplayers union select * from pianoplayers

from a query
select * from query1 where city = ‘London’

select into
select * into temp1 from customer where city = ‘London’
select * into customer in ‘accts1.mdb’ from customer

crosstab query
transform sum(weeklysales.s_value) as sumofs_value
select employee.e_name from employee inner join (category inner join weeklysales on category.c_no = weeklysales.c_no) on employee.e_no = weeklysales.e_no group by employee.e_name
pivot category.c_name