SELECT
|
less customer.dat
|
1 |
Joe |
23 |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
|
ORDER BY
|
less customer.dat |sort -k 1.10-,1.12
|
5 |
Susan |
18 |
1 |
Joe |
23 |
3 |
Lin |
34 |
2 |
Mika |
45 |
4 |
Sara |
56 |
|
WHERE
|
less customer.dat |awk {if (substr($0,3,5) == Susan ) print $0}
|
|
INNER JOIN
|
sort -k 2 purchaseorder.dat > srt_purchaseorder.dat join -1 1 -2 2 customer.dat srt_purchaseorder.dat
|
1 |
Joe |
23 |
2 |
Biography |
1 |
Joe |
23 |
3 |
Fiction |
2 |
Mika |
45 |
4 |
Biography |
3 |
Lin |
34 |
1 |
Fiction |
3 |
Lin |
34 |
5 |
Fiction |
4 |
Sara |
56 |
6 |
Fiction |
|
LEFT OUTER JOIN
|
sort -k 2 purchaseorder.dat > srt_purchaseorder.dat join -a1 -1 1 -2 2 customer.dat srt_purchaseorder.dat
|
1 |
Joe |
23 |
2 |
Biography |
1 |
Joe |
23 |
3 |
Fiction |
2 |
Mika |
45 |
4 |
Biography |
3 |
Lin |
34 |
1 |
Fiction |
3 |
Lin |
34 |
5 |
Fiction |
4 |
Sara |
56 |
6 |
Fiction |
5 |
Susan |
18 |
NULL |
NULL |
|
GROUP BY
|
join -1 1 -2 2 customer.dat srt_purchaseorder.dat > sum.dat awk BEGIN { FS=OFS=SUBSEP= }{arr[$2,$3]++ }END {for (i in arr) print i,arr[i]} sum.dat
|
Joe |
23 |
1 |
Mika |
45 |
2 |
Lin |
34 |
2 |
Sara |
56 |
1 |
|
UPDATE
|
less customer.dat |awk {if (substr($0,3,5) == Susan ) print substr($0,0,9) 59 ; else print $0 }
|
1 |
Joe |
26 |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
|
INSERT
|
cat customer.dat new_cust.dat
|
1 |
Joe |
23 |
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
6 |
Terry |
50 |
|
DELETE
|
less customer.dat |awk {if (substr($0,1,1) != 1 ) print $0
|
2 |
Mika |
45 |
3 |
Lin |
34 |
4 |
Sara |
56 |
5 |
Susan |
18 |
|