< 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 |
반응형
'Code > Database (DB)' 카테고리의 다른 글
| FireFox DB(sqlite) 정리 (0) | 2017.02.14 |
|---|---|
| 손상된 SQLite 데이터베이스 파일 복구 (0) | 2015.07.18 |
| MDB 파일 관리 - SQL 명령 (0) | 2015.01.15 |
| SQLite 성능 테스트(auto_vacuum 설정) (0) | 2014.01.22 |
| SQLite 사용법 정리 (0) | 2012.08.18 |
