原始数据:
[/tmp/test]# cat emp
7369 SMITH CLERK 7902 1980-12-17 800 0 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 0 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 0 30
7782 CLARK MANAGER 7839 1981-06-09 2450 0 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 0 20
7839 KING PRESIDENT 7839 1981-11-17 5000 0 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 0 20
7900 JAMES CLERK 7698 1981-12-03 950 0 30
7902 FORD ANALYST 7566 1981-12-03 3000 0 20
7934 MILLER CLERK 7782 1982-01-23 1300 0 10
[/tmp/test]# cat dept
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1.select * from emp where sal>2000
[/tmp/test]# cat emp | awk '$6>2000'
2.select distinct job from emp
[/tmp/test]# cat emp | awk '{print $3}' | sort -u
[/tmp/test]# cat emp | awk '!a[$3]++{print $3}'
[/tmp/test]# cat emp | awk '{a[$3]=$3}END{for(i in a) print a[i]}'
3.select * from emp order by sal
[/tmp/test]# cat emp | sort -nk6s
[/tmp/test]# cat emp | awk '{a[$6]}END{asorti(a);for(i=1;i<=length(a);i++){print a[i]}}' ##这个需要验证.
4.select * from emp where rownum<3
[/tmp/test]# cat emp | awk 'NR<3';
5.group by,having,sum,count
select job,count(*),sum(sal) from emp group by job
[/tmp/test]# cat emp | awk '{a[$3]+=$6;b[$3]+=1}END{for(i in a) printf "%-10s%-4s%-10s\n", i,b[i],a[i]}'
select job,count(1),sum(sal) from emp group by job having count(1)>2
[/tmp/test]# cat emp | awk '{a[$3]+=$6;b[$3]+=1}END{for(i in a) if(b[i]>2){printf "%-10s%-4s%-10s\n", i,b[i],a[i]}}'
6.like
select * from emp where name like 'W%'
[/tmp/test]# cat emp | awk '$2~/^W/'
select * from emp where name like '%N'
[/tmp/test]# cat emp | awk '$2~/N$/'
select * from emp where job like '%CLERK%'
[/tmp/test]# cat emp | awk '$3~/CLERK/'
7.join
select * from emp,dept where emp.deptno=dept.no
[/tmp/test]# awk 'FNR==NR{a[$1]=$2" "$3;next}{if(a[$8]) print $0,a[$8]}' dept emp
select * from emp,dept where emp.deptno=dept.no and dept.no=20
[/tmp/test]# awk 'FNR==NR{if($1=20){a[$1]=$2" "$3};next}{if(a[$8]) print $0,a[$8]}' dept emp
[/tmp/test]# cat emp
7369 SMITH CLERK 7902 1980-12-17 800 0 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 0 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 0 30
7782 CLARK MANAGER 7839 1981-06-09 2450 0 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 0 20
7839 KING PRESIDENT 7839 1981-11-17 5000 0 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 0 20
7900 JAMES CLERK 7698 1981-12-03 950 0 30
7902 FORD ANALYST 7566 1981-12-03 3000 0 20
7934 MILLER CLERK 7782 1982-01-23 1300 0 10
[/tmp/test]# cat dept
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1.select * from emp where sal>2000
[/tmp/test]# cat emp | awk '$6>2000'
2.select distinct job from emp
[/tmp/test]# cat emp | awk '{print $3}' | sort -u
[/tmp/test]# cat emp | awk '!a[$3]++{print $3}'
[/tmp/test]# cat emp | awk '{a[$3]=$3}END{for(i in a) print a[i]}'
3.select * from emp order by sal
[/tmp/test]# cat emp | sort -nk6s
[/tmp/test]# cat emp | awk '{a[$6]}END{asorti(a);for(i=1;i<=length(a);i++){print a[i]}}' ##这个需要验证.
4.select * from emp where rownum<3
[/tmp/test]# cat emp | awk 'NR<3';
5.group by,having,sum,count
select job,count(*),sum(sal) from emp group by job
[/tmp/test]# cat emp | awk '{a[$3]+=$6;b[$3]+=1}END{for(i in a) printf "%-10s%-4s%-10s\n", i,b[i],a[i]}'
select job,count(1),sum(sal) from emp group by job having count(1)>2
[/tmp/test]# cat emp | awk '{a[$3]+=$6;b[$3]+=1}END{for(i in a) if(b[i]>2){printf "%-10s%-4s%-10s\n", i,b[i],a[i]}}'
6.like
select * from emp where name like 'W%'
[/tmp/test]# cat emp | awk '$2~/^W/'
select * from emp where name like '%N'
[/tmp/test]# cat emp | awk '$2~/N$/'
select * from emp where job like '%CLERK%'
[/tmp/test]# cat emp | awk '$3~/CLERK/'
7.join
select * from emp,dept where emp.deptno=dept.no
[/tmp/test]# awk 'FNR==NR{a[$1]=$2" "$3;next}{if(a[$8]) print $0,a[$8]}' dept emp
select * from emp,dept where emp.deptno=dept.no and dept.no=20
[/tmp/test]# awk 'FNR==NR{if($1=20){a[$1]=$2" "$3};next}{if(a[$8]) print $0,a[$8]}' dept emp
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24237320/viewspace-2124995/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24237320/viewspace-2124995/