📄 select.out
字号:
---- SELECT---- btree index-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1--SELECT onek.* WHERE onek.unique1 < 10 ORDER BY onek.unique1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx(10 rows)---- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1--SELECT onek.unique1, onek.stringu1 WHERE onek.unique1 < 20 ORDER BY unique1 using >; unique1 | stringu1 ---------+---------- 19 | TAAAAA 18 | SAAAAA 17 | RAAAAA 16 | QAAAAA 15 | PAAAAA 14 | OAAAAA 13 | NAAAAA 12 | MAAAAA 11 | LAAAAA 10 | KAAAAA 9 | JAAAAA 8 | IAAAAA 7 | HAAAAA 6 | GAAAAA 5 | FAAAAA 4 | EAAAAA 3 | DAAAAA 2 | CAAAAA 1 | BAAAAA 0 | AAAAAA(20 rows)---- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2--SELECT onek.unique1, onek.stringu1 WHERE onek.unique1 > 980 ORDER BY stringu1 using <; unique1 | stringu1 ---------+---------- 988 | AMAAAA 989 | BMAAAA 990 | CMAAAA 991 | DMAAAA 992 | EMAAAA 993 | FMAAAA 994 | GMAAAA 995 | HMAAAA 996 | IMAAAA 997 | JMAAAA 998 | KMAAAA 999 | LMAAAA 981 | TLAAAA 982 | ULAAAA 983 | VLAAAA 984 | WLAAAA 985 | XLAAAA 986 | YLAAAA 987 | ZLAAAA(19 rows) ---- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |-- sort +1d -2 +0nr -1--SELECT onek.unique1, onek.string4 WHERE onek.unique1 > 980 ORDER BY string4 using <, unique1 using >; unique1 | string4 ---------+--------- 999 | AAAAxx 995 | AAAAxx 983 | AAAAxx 982 | AAAAxx 981 | AAAAxx 998 | HHHHxx 997 | HHHHxx 993 | HHHHxx 990 | HHHHxx 986 | HHHHxx 996 | OOOOxx 991 | OOOOxx 988 | OOOOxx 987 | OOOOxx 985 | OOOOxx 994 | VVVVxx 992 | VVVVxx 989 | VVVVxx 984 | VVVVxx(19 rows) ---- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |-- sort +1dr -2 +0n -1--SELECT onek.unique1, onek.string4 WHERE onek.unique1 > 980 ORDER BY string4 using >, unique1 using <; unique1 | string4 ---------+--------- 984 | VVVVxx 989 | VVVVxx 992 | VVVVxx 994 | VVVVxx 985 | OOOOxx 987 | OOOOxx 988 | OOOOxx 991 | OOOOxx 996 | OOOOxx 986 | HHHHxx 990 | HHHHxx 993 | HHHHxx 997 | HHHHxx 998 | HHHHxx 981 | AAAAxx 982 | AAAAxx 983 | AAAAxx 995 | AAAAxx 999 | AAAAxx(19 rows) ---- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |-- sort +0nr -1 +1d -2--SELECT onek.unique1, onek.string4 WHERE onek.unique1 < 20 ORDER BY unique1 using >, string4 using <; unique1 | string4 ---------+--------- 19 | OOOOxx 18 | VVVVxx 17 | HHHHxx 16 | OOOOxx 15 | VVVVxx 14 | AAAAxx 13 | OOOOxx 12 | AAAAxx 11 | OOOOxx 10 | AAAAxx 9 | HHHHxx 8 | HHHHxx 7 | VVVVxx 6 | OOOOxx 5 | HHHHxx 4 | HHHHxx 3 | VVVVxx 2 | OOOOxx 1 | OOOOxx 0 | OOOOxx(20 rows)---- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |-- sort +0n -1 +1dr -2--SELECT onek.unique1, onek.string4 WHERE onek.unique1 < 20 ORDER BY unique1 using <, string4 using >; unique1 | string4 ---------+--------- 0 | OOOOxx 1 | OOOOxx 2 | OOOOxx 3 | VVVVxx 4 | HHHHxx 5 | HHHHxx 6 | OOOOxx 7 | VVVVxx 8 | HHHHxx 9 | HHHHxx 10 | AAAAxx 11 | OOOOxx 12 | AAAAxx 13 | OOOOxx 14 | AAAAxx 15 | VVVVxx 16 | OOOOxx 17 | HHHHxx 18 | VVVVxx 19 | OOOOxx(20 rows)---- test partial btree indexes---- As of 7.2, planner probably won't pick an indexscan without stats,-- so ANALYZE first.--ANALYZE onek2;---- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1--SELECT onek2.* WHERE onek2.unique1 < 10; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx(10 rows)---- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1--SELECT onek2.unique1, onek2.stringu1 WHERE onek2.unique1 < 20 ORDER BY unique1 using >; unique1 | stringu1 ---------+---------- 19 | TAAAAA 18 | SAAAAA 17 | RAAAAA 16 | QAAAAA 15 | PAAAAA 14 | OAAAAA 13 | NAAAAA 12 | MAAAAA 11 | LAAAAA 10 | KAAAAA 9 | JAAAAA 8 | IAAAAA 7 | HAAAAA 6 | GAAAAA 5 | FAAAAA 4 | EAAAAA 3 | DAAAAA 2 | CAAAAA 1 | BAAAAA 0 | AAAAAA(20 rows)---- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2--SELECT onek2.unique1, onek2.stringu1 WHERE onek2.unique1 > 980; unique1 | stringu1 ---------+---------- 981 | TLAAAA 982 | ULAAAA 983 | VLAAAA 984 | WLAAAA 985 | XLAAAA 986 | YLAAAA 987 | ZLAAAA 988 | AMAAAA 989 | BMAAAA 990 | CMAAAA 991 | DMAAAA 992 | EMAAAA 993 | FMAAAA 994 | GMAAAA 995 | HMAAAA 996 | IMAAAA 997 | JMAAAA 998 | KMAAAA 999 | LMAAAA(19 rows)SELECT two, stringu1, ten, string4 INTO TABLE tmp FROM onek;---- awk '{print $1,$2;}' person.data |-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |-- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data---- SELECT name, age FROM person*; ??? check if differentSELECT p.name, p.age FROM person* p; name | age ---------+----- mike | 40 joe | 20 sally | 34 sandra | 19 alex | 30 sue | 50 denise | 24 sarah | 88 teresa | 38 nan | 28 leah | 68 wendy | 78 melissa | 28 joan | 18 mary | 8 jane | 58 liza | 38 jean | 28 jenifer | 38 juanita | 58 susan | 78 zena | 98 martie | 88 chris | 78 pat | 18 zola | 58 louise | 98 edna | 18 bertha | 88 sumi | 38 koko | 88 gina | 18 rean | 48 sharon | 78 paula | 68 julie | 68 belinda | 38 karen | 48 carina | 58 diane | 18 esther | 98 trudy | 88 fanny | 8 carmen | 78 lita | 25 pamela | 48 sandy | 38 trisha | 88 vera | 78 velma | 68 sharon | 25 sam | 30 bill | 20 fred | 28 larry | 60 jeff | 23 cim | 30 linda | 19(58 rows)---- awk '{print $1,$2;}' person.data |-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |-- sort +1nr -2--SELECT p.name, p.age FROM person* p ORDER BY age using >, name; name | age ---------+----- esther | 98 louise | 98 zena | 98 bertha | 88 koko | 88 martie | 88 sarah | 88 trisha | 88 trudy | 88 carmen | 78 chris | 78 sharon | 78 susan | 78 vera | 78 wendy | 78 julie | 68 leah | 68 paula | 68 velma | 68 larry | 60 carina | 58 jane | 58 juanita | 58 zola | 58 sue | 50 karen | 48 pamela | 48 rean | 48 mike | 40 belinda | 38 jenifer | 38 liza | 38 sandy | 38 sumi | 38 teresa | 38 sally | 34 alex | 30 cim | 30 sam | 30 fred | 28 jean | 28 melissa | 28 nan | 28 lita | 25 sharon | 25 denise | 24 jeff | 23 bill | 20 joe | 20 linda | 19 sandra | 19 diane | 18 edna | 18 gina | 18 joan | 18 pat | 18 fanny | 8 mary | 8(58 rows)⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -