| blob | 32e96fa27b7b96ec85d716c682d927a8332fef34 |
1 drop table if exists t1,t2,t3,t4;
2 CREATE TABLE t1 (
3 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
4 Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
5 );
6 INSERT INTO t1 VALUES (9410,9412);
7 select period from t1;
8 period
9 9410
10 select * from t1;
11 Period Varor_period
12 9410 9412
13 select t1.* from t1;
14 Period Varor_period
15 9410 9412
16 CREATE TABLE t2 (
17 auto int not null auto_increment,
18 fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
19 companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
20 fld3 char(30) DEFAULT '' NOT NULL,
21 fld4 char(35) DEFAULT '' NOT NULL,
22 fld5 char(35) DEFAULT '' NOT NULL,
23 fld6 char(4) DEFAULT '' NOT NULL,
24 UNIQUE fld1 (fld1),
25 KEY fld3 (fld3),
26 PRIMARY KEY (auto)
27 );
28 select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
29 fld3
30 imaginable
31 select fld3 from t2 where fld3 like "%cultivation" ;
32 fld3
33 cultivation
34 select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
35 fld3 companynr
36 concoct 58
37 druggists 58
38 engrossing 58
39 Eurydice 58
40 exclaimers 58
41 ferociousness 58
42 hopelessness 58
43 Huey 58
44 imaginable 58
45 judges 58
46 merging 58
47 ostrich 58
48 peering 58
49 Phelps 58
50 presumes 58
51 Ruth 58
52 sentences 58
53 Shylock 58
54 straggled 58
55 synergy 58
56 thanking 58
57 tying 58
58 unlocks 58
59 select fld3,companynr from t2 where companynr = 58 order by fld3;
60 fld3 companynr
61 concoct 58
62 druggists 58
63 engrossing 58
64 Eurydice 58
65 exclaimers 58
66 ferociousness 58
67 hopelessness 58
68 Huey 58
69 imaginable 58
70 judges 58
71 merging 58
72 ostrich 58
73 peering 58
74 Phelps 58
75 presumes 58
76 Ruth 58
77 sentences 58
78 Shylock 58
79 straggled 58
80 synergy 58
81 thanking 58
82 tying 58
83 unlocks 58
84 select fld3 from t2 order by fld3 desc limit 10;
85 fld3
86 youthfulness
87 yelped
88 Wotan
89 workers
90 Witt
91 witchcraft
92 Winsett
93 Willy
94 willed
95 wildcats
96 select fld3 from t2 order by fld3 desc limit 5;
97 fld3
98 youthfulness
99 yelped
100 Wotan
101 workers
102 Witt
103 select fld3 from t2 order by fld3 desc limit 5,5;
104 fld3
105 witchcraft
106 Winsett
107 Willy
108 willed
109 wildcats
110 select t2.fld3 from t2 where fld3 = 'honeysuckle';
111 fld3
112 honeysuckle
113 select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
114 fld3
115 honeysuckle
116 select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
117 fld3
118 honeysuckle
119 select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
120 fld3
121 honeysuckle
122 select t2.fld3 from t2 where fld3 LIKE 'h%le';
123 fld3
124 honeysuckle
125 select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
126 fld3
127 select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
128 fld3
129 explain select t2.fld3 from t2 where fld3 = 'honeysuckle';
130 id select_type table type possible_keys key key_len ref rows Extra
131 1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
132 explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle';
133 id select_type table type possible_keys key key_len ref rows Extra
134 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
135 explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle';
136 id select_type table type possible_keys key key_len ref rows Extra
137 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
138 explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle';
139 id select_type table type possible_keys key key_len ref rows Extra
140 1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
141 explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle';
142 id select_type table type possible_keys key key_len ref rows Extra
143 1 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index
144 explain select fld3 from t2 ignore index (fld3,not_used);
145 ERROR 42000: Key 'not_used' doesn't exist in table 't2'
146 explain select fld3 from t2 use index (not_used);
147 ERROR 42000: Key 'not_used' doesn't exist in table 't2'
148 select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
149 fld3
150 honeysuckle
151 honoring
152 explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
153 id select_type table type possible_keys key key_len ref rows Extra
154 1 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index
155 select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
156 fld1 fld3
157 148504 Colombo
158 068305 Colombo
159 000000 nondecreasing
160 select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes';
161 fld1 fld3
162 232605 appendixes
163 1232605 appendixes
164 1232606 appendixes
165 1232607 appendixes
166 1232608 appendixes
167 1232609 appendixes
168 select fld1 from t2 where fld1=250501 or fld1="250502";
169 fld1
170 250501
171 250502
172 explain select fld1 from t2 where fld1=250501 or fld1="250502";
173 id select_type table type possible_keys key key_len ref rows Extra
174 1 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index
175 select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
176 fld1
177 250501
178 250502
179 250505
180 250601
181 explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502;
182 id select_type table type possible_keys key key_len ref rows Extra
183 1 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index
184 select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%';
185 fld1 fld3
186 218401 faithful
187 018007 fanatic
188 228311 fated
189 018017 featherweight
190 218022 feed
191 088303 feminine
192 058004 Fenton
193 038017 fetched
194 018054 fetters
195 208101 fiftieth
196 238007 filial
197 013606 fingerings
198 218008 finishers
199 038205 firearm
200 188505 fitting
201 202301 Fitzpatrick
202 238008 fixedly
203 012001 flanking
204 018103 flint
205 018104 flopping
206 188007 flurried
207 013602 foldout
208 226205 foothill
209 232102 forgivably
210 228306 forthcoming
211 186002 freakish
212 208113 freest
213 231315 freezes
214 036002 funereal
215 226209 furnishings
216 198006 furthermore
217 select fld3 from t2 where fld3 like "L%" and fld3 = "ok";
218 fld3
219 select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly");
220 fld3
221 Chantilly
222 select fld1,fld3 from t2 where fld1 like "25050%";
223 fld1 fld3
224 250501 poisoning
225 250502 Iraqis
226 250503 heaving
227 250504 population
228 250505 bomb
229 select fld1,fld3 from t2 where fld1 like "25050_";
230 fld1 fld3
231 250501 poisoning
232 250502 Iraqis
233 250503 heaving
234 250504 population
235 250505 bomb
236 select distinct companynr from t2;
237 companynr
238 00
239 37
240 36
241 50
242 58
243 29
244 40
245 53
246 65
247 41
248 34
249 68
250 select distinct companynr from t2 order by companynr;
251 companynr
252 00
253 29
254 34
255 36
256 37
257 40
258 41
259 50
260 53
261 58
262 65
263 68
264 select distinct companynr from t2 order by companynr desc;
265 companynr
266 68
267 65
268 58
269 53
270 50
271 41
272 40
273 37
274 36
275 34
276 29
277 00
278 select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%";
279 fld3 period
280 obliterates 9410
281 offload 9410
282 opaquely 9410
283 organizer 9410
284 overestimating 9410
285 overlay 9410
286 select distinct fld3 from t2 where companynr = 34 order by fld3;
287 fld3
288 absentee
289 accessed
290 ahead
291 alphabetic
292 Asiaticizations
293 attitude
294 aye
295 bankruptcies
296 belays
297 Blythe
298 bomb
299 boulevard
300 bulldozes
301 cannot
302 caressing
303 charcoal
304 checksumming
305 chess
306 clubroom
307 colorful
308 cosy
309 creator
310 crying
311 Darius
312 diffusing
313 duality
314 Eiffel
315 Epiphany
316 Ernestine
317 explorers
318 exterminated
319 famine
320 forked
321 Gershwins
322 heaving
323 Hodges
324 Iraqis
325 Italianization
326 Lagos
327 landslide
328 libretto
329 Majorca
330 mastering
331 narrowed
332 occurred
333 offerers
334 Palestine
335 Peruvianizes
336 pharmaceutic
337 poisoning
338 population
339 Pygmalion
340 rats
341 realest
342 recording
343 regimented
344 retransmitting
345 reviver
346 rouses
347 scars
348 sicker
349 sleepwalk
350 stopped
351 sugars
352 translatable
353 uncles
354 unexpected
355 uprisings
356 versatility
357 vest
358 select distinct fld3 from t2 limit 10;
359 fld3
360 abates
361 abiding
362 Abraham
363 abrogating
364 absentee
365 abut
366 accessed
367 accruing
368 accumulating
369 accuracies
370 select distinct fld3 from t2 having fld3 like "A%" limit 10;
371 fld3
372 abates
373 abiding
374 Abraham
375 abrogating
376 absentee
377 abut
378 accessed
379 accruing
380 accumulating
381 accuracies
382 select distinct substring(fld3,1,3) from t2 where fld3 like "A%";
383 substring(fld3,1,3)
384 aba
385 abi
386 Abr
387 abs
388 abu
389 acc
390 acq
391 acu
392 Ade
393 adj
394 Adl
395 adm
396 Ado
397 ads
398 adv
399 aer
400 aff
401 afi
402 afl
403 afo
404 agi
405 ahe
406 aim
407 air
408 Ald
409 alg
410 ali
411 all
412 alp
413 alr
414 ama
415 ame
416 amm
417 ana
418 and
419 ane
420 Ang
421 ani
422 Ann
423 Ant
424 api
425 app
426 aqu
427 Ara
428 arc
429 Arm
430 arr
431 Art
432 Asi
433 ask
434 asp
435 ass
436 ast
437 att
438 aud
439 Aug
440 aut
441 ave
442 avo
443 awe
444 aye
445 Azt
446 select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10;
447 a
448 aba
449 abi
450 Abr
451 abs
452 abu
453 acc
454 acq
455 acu
456 Ade
457 adj
458 select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10;
459 substring(fld3,1,3)
460 aba
461 abi
462 Abr
463 abs
464 abu
465 acc
466 acq
467 acu
468 Ade
469 adj
470 select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10;
471 a
472 aba
473 abi
474 Abr
475 abs
476 abu
477 acc
478 acq
479 acu
480 Ade
481 adj
482 create table t3 (
483 period int not null,
484 name char(32) not null,
485 companynr int not null,
486 price double(11,0),
487 price2 double(11,0),
488 key (period),
489 key (name)
490 );
491 create temporary table tmp engine = myisam select * from t3;
492 insert into t3 select * from tmp;
493 insert into tmp select * from t3;
494 insert into t3 select * from tmp;
495 insert into tmp select * from t3;
496 insert into t3 select * from tmp;
497 insert into tmp select * from t3;
498 insert into t3 select * from tmp;
499 insert into tmp select * from t3;
500 insert into t3 select * from tmp;
501 insert into tmp select * from t3;
502 insert into t3 select * from tmp;
503 insert into tmp select * from t3;
504 insert into t3 select * from tmp;
505 insert into tmp select * from t3;
506 insert into t3 select * from tmp;
507 insert into tmp select * from t3;
508 insert into t3 select * from tmp;
509 alter table t3 add t2nr int not null auto_increment primary key first;
510 drop table tmp;
511 SET SQL_BIG_TABLES=1;
512 select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
513 namn
514 Abraham Abraham
515 abrogating abrogating
516 admonishing admonishing
517 Adolph Adolph
518 afield afield
519 aging aging
520 ammonium ammonium
521 analyzable analyzable
522 animals animals
523 animized animized
524 SET SQL_BIG_TABLES=0;
525 select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
526 concat(fld3," ",fld3)
527 Abraham Abraham
528 abrogating abrogating
529 admonishing admonishing
530 Adolph Adolph
531 afield afield
532 aging aging
533 ammonium ammonium
534 analyzable analyzable
535 animals animals
536 animized animized
537 select distinct fld5 from t2 limit 10;
538 fld5
539 neat
540 Steinberg
541 jarring
542 tinily
543 balled
544 persist
545 attainments
546 fanatic
547 measures
548 rightfulness
549 select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
550 fld3 count(*)
551 affixed 1
552 and 1
553 annoyers 1
554 Anthony 1
555 assayed 1
556 assurers 1
557 attendants 1
558 bedlam 1
559 bedpost 1
560 boasted 1
561 SET SQL_BIG_TABLES=1;
562 select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
563 fld3 count(*)
564 affixed 1
565 and 1
566 annoyers 1
567 Anthony 1
568 assayed 1
569 assurers 1
570 attendants 1
571 bedlam 1
572 bedpost 1
573 boasted 1
574 SET SQL_BIG_TABLES=0;
575 select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
576 fld3 repeat("a",length(fld3)) count(*)
577 circus aaaaaa 1
578 cited aaaaa 1
579 Colombo aaaaaaa 1
580 congresswoman aaaaaaaaaaaaa 1
581 contrition aaaaaaaaaa 1
582 corny aaaaa 1
583 cultivation aaaaaaaaaaa 1
584 definiteness aaaaaaaaaaaa 1
585 demultiplex aaaaaaaaaaa 1
586 disappointing aaaaaaaaaaaaa 1
587 select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
588 companynr rtrim(space(512+companynr))
589 37
590 78
591 101
592 154
593 311
594 447
595 512
596 select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3;
597 fld3
598 explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
599 id select_type table type possible_keys key key_len ref rows Extra
600 1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort
601 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index
602 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
603 id select_type table type possible_keys key key_len ref rows Extra
604 1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
605 1 SIMPLE t3 ref period period 4 test.t1.period 4181
606 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
607 id select_type table type possible_keys key key_len ref rows Extra
608 1 SIMPLE t3 index period period 4 NULL 1
609 1 SIMPLE t1 ref period period 4 test.t3.period 4181
610 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
611 id select_type table type possible_keys key key_len ref rows Extra
612 1 SIMPLE t1 index period period 4 NULL 1
613 1 SIMPLE t3 ref period period 4 test.t1.period 4181
614 select period from t1;
615 period
616 9410
617 select period from t1 where period=1900;
618 period
619 select fld3,period from t1,t2 where fld1 = 011401 order by period;
620 fld3 period
621 breaking 9410
622 select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001;
623 fld3 period
624 breaking 1001
625 explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period;
626 id select_type table type possible_keys key key_len ref rows Extra
627 1 SIMPLE t2 const fld1 fld1 4 const 1
628 1 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1
629 select fld3,period from t2,t1 where companynr*10 = 37*10;
630 fld3 period
631 breaking 9410
632 Romans 9410
633 intercepted 9410
634 bewilderingly 9410
635 astound 9410
636 admonishing 9410
637 sumac 9410
638 flanking 9410
639 combed 9410
640 subjective 9410
641 scatterbrain 9410
642 Eulerian 9410
643 Kane 9410
644 overlay 9410
645 perturb 9410
646 goblins 9410
647 annihilates 9410
648 Wotan 9410
649 snatching 9410
650 concludes 9410
651 laterally 9410
652 yelped 9410
653 grazing 9410
654 Baird 9410
655 celery 9410
656 misunderstander 9410
657 handgun 9410
658 foldout 9410
659 mystic 9410
660 succumbed 9410
661 Nabisco 9410
662 fingerings 9410
663 aging 9410
664 afield 9410
665 ammonium 9410
666 boat 9410
667 intelligibility 9410
668 Augustine 9410
669 teethe 9410
670 dreaded 9410
671 scholastics 9410
672 audiology 9410
673 wallet 9410
674 parters 9410
675 eschew 9410
676 quitter 9410
677 neat 9410
678 Steinberg 9410
679 jarring 9410
680 tinily 9410
681 balled 9410
682 persist 9410
683 attainments 9410
684 fanatic 9410
685 measures 9410
686 rightfulness 9410
687 capably 9410
688 impulsive 9410
689 starlet 9410
690 terminators 9410
691 untying 9410
692 announces 9410
693 featherweight 9410
694 pessimist 9410
695 daughter 9410
696 decliner 9410
697 lawgiver 9410
698 stated 9410
699 readable 9410
700 attrition 9410
701 cascade 9410
702 motors 9410
703 interrogate 9410
704 pests 9410
705 stairway 9410
706 dopers 9410
707 testicle 9410
708 Parsifal 9410
709 leavings 9410
710 postulation 9410
711 squeaking 9410
712 contrasted 9410
713 leftover 9410
714 whiteners 9410
715 erases 9410
716 Punjab 9410
717 Merritt 9410
718 Quixotism 9410
719 sweetish 9410
720 dogging 9410
721 scornfully 9410
722 bellow 9410
723 bills 9410
724 cupboard 9410
725 sureties 9410
726 puddings 9410
727 fetters 9410
728 bivalves 9410
729 incurring 9410
730 Adolph 9410
731 pithed 9410
732 Miles 9410
733 trimmings 9410
734 tragedies 9410
735 skulking 9410
736 flint 9410
737 flopping 9410
738 relaxing 9410
739 offload 9410
740 suites 9410
741 lists 9410
742 animized 9410
743 multilayer 9410
744 standardizes 9410
745 Judas 9410
746 vacuuming 9410
747 dentally 9410
748 humanness 9410
749 inch 9410
750 Weissmuller 9410
751 irresponsibly 9410
752 luckily 9410
753 culled 9410
754 medical 9410
755 bloodbath 9410
756 subschema 9410
757 animals 9410
758 Micronesia 9410
759 repetitions 9410
760 Antares 9410
761 ventilate 9410
762 pityingly 9410
763 interdependent 9410
764 Graves 9410
765 neonatal 9410
766 chafe 9410
767 honoring 9410
768 realtor 9410
769 elite 9410
770 funereal 9410
771 abrogating 9410
772 sorters 9410
773 Conley 9410
774 lectured 9410
775 Abraham 9410
776 Hawaii 9410
777 cage 9410
778 hushes 9410
779 Simla 9410
780 reporters 9410
781 Dutchman 9410
782 descendants 9410
783 groupings 9410
784 dissociate 9410
785 coexist 9410
786 Beebe 9410
787 Taoism 9410
788 Connally 9410
789 fetched 9410
790 checkpoints 9410
791 rusting 9410
792 galling 9410
793 obliterates 9410
794 traitor 9410
795 resumes 9410
796 analyzable 9410
797 terminator 9410
798 gritty 9410
799 firearm 9410
800 minima 9410
801 Selfridge 9410
802 disable 9410
803 witchcraft 9410
804 betroth 9410
805 Manhattanize 9410
806 imprint 9410
807 peeked 9410
808 swelling 9410
809 interrelationships 9410
810 riser 9410
811 Gandhian 9410
812 peacock 9410
813 bee 9410
814 kanji 9410
815 dental 9410
816 scarf 9410
817 chasm 9410
818 insolence 9410
819 syndicate 9410
820 alike 9410
821 imperial 9410
822 convulsion 9410
823 railway 9410
824 validate 9410
825 normalizes 9410
826 comprehensive 9410
827 chewing 9410
828 denizen 9410
829 schemer 9410
830 chronicle 9410
831 Kline 9410
832 Anatole 9410
833 partridges 9410
834 brunch 9410
835 recruited 9410
836 dimensions 9410
837 Chicana 9410
838 announced 9410
839 praised 9410
840 employing 9410
841 linear 9410
842 quagmire 9410
843 western 9410
844 relishing 9410
845 serving 9410
846 scheduling 9410
847 lore 9410
848 eventful 9410
849 arteriole 9410
850 disentangle 9410
851 cured 9410
852 Fenton 9410
853 avoidable 9410
854 drains 9410
855 detectably 9410
856 husky 9410
857 impelling 9410
858 undoes 9410
859 evened 9410
860 squeezes 9410
861 destroyer 9410
862 rudeness 9410
863 beaner 9410
864 boorish 9410
865 Everhart 9410
866 encompass 9410
867 mushrooms 9410
868 Alison 9410
869 externally 9410
870 pellagra 9410
871 cult 9410
872 creek 9410
873 Huffman 9410
874 Majorca 9410
875 governing 9410
876 gadfly 9410
877 reassigned 9410
878 intentness 9410
879 craziness 9410
880 psychic 9410
881 squabbled 9410
882 burlesque 9410
883 capped 9410
884 extracted 9410
885 DiMaggio 9410
886 exclamation 9410
887 subdirectory 9410
888 Gothicism 9410
889 feminine 9410
890 metaphysically 9410
891 sanding 9410
892 Miltonism 9410
893 freakish 9410
894 index 9410
895 straight 9410
896 flurried 9410
897 denotative 9410
898 coming 9410
899 commencements 9410
900 gentleman 9410
901 gifted 9410
902 Shanghais 9410
903 sportswriting 9410
904 sloping 9410
905 navies 9410
906 leaflet 9410
907 shooter 9410
908 Joplin 9410
909 babies 9410
910 assails 9410
911 admiring 9410
912 swaying 9410
913 Goldstine 9410
914 fitting 9410
915 Norwalk 9410
916 analogy 9410
917 deludes 9410
918 cokes 9410
919 Clayton 9410
920 exhausts 9410
921 causality 9410
922 sating 9410
923 icon 9410
924 throttles 9410
925 communicants 9410
926 dehydrate 9410
927 priceless 9410
928 publicly 9410
929 incidentals 9410
930 commonplace 9410
931 mumbles 9410
932 furthermore 9410
933 cautioned 9410
934 parametrized 9410
935 registration 9410
936 sadly 9410
937 positioning 9410
938 babysitting 9410
939 eternal 9410
940 hoarder 9410
941 congregates 9410
942 rains 9410
943 workers 9410
944 sags 9410
945 unplug 9410
946 garage 9410
947 boulder 9410
948 specifics 9410
949 Teresa 9410
950 Winsett 9410
951 convenient 9410
952 buckboards 9410
953 amenities 9410
954 resplendent 9410
955 sews 9410
956 participated 9410
957 Simon 9410
958 certificates 9410
959 Fitzpatrick 9410
960 Evanston 9410
961 misted 9410
962 textures 9410
963 save 9410
964 count 9410
965 rightful 9410
966 chaperone 9410
967 Lizzy 9410
968 clenched 9410
969 effortlessly 9410
970 accessed 9410
971 beaters 9410
972 Hornblower 9410
973 vests 9410
974 indulgences 9410
975 infallibly 9410
976 unwilling 9410
977 excrete 9410
978 spools 9410
979 crunches 9410
980 overestimating 9410
981 ineffective 9410
982 humiliation 9410
983 sophomore 9410
984 star 9410
985 rifles 9410
986 dialysis 9410
987 arriving 9410
988 indulge 9410
989 clockers 9410
990 languages 9410
991 Antarctica 9410
992 percentage 9410
993 ceiling 9410
994 specification 9410
995 regimented 9410
996 ciphers 9410
997 pictures 9410
998 serpents 9410
999 allot 9410
1000 realized 9410
1001 mayoral 9410
1002 opaquely 9410
1003 hostess 9410
1004 fiftieth 9410
1005 incorrectly 9410
1006 decomposition 9410
1007 stranglings 9410
1008 mixture 9410
1009 electroencephalography 9410
1010 similarities 9410
1011 charges 9410
1012 freest 9410
1013 Greenberg 9410
1014 tinting 9410
1015 expelled 9410
1016 warm 9410
1017 smoothed 9410
1018 deductions 9410
1019 Romano 9410
1020 bitterroot 9410
1021 corset 9410
1022 securing 9410
1023 environing 9410
1024 cute 9410
1025 Crays 9410
1026 heiress 9410
1027 inform 9410
1028 avenge 9410
1029 universals 9410
1030 Kinsey 9410
1031 ravines 9410
1032 bestseller 9410
1033 equilibrium 9410
1034 extents 9410
1035 relatively 9410
1036 pressure 9410
1037 critiques 9410
1038 befouled 9410
1039 rightfully 9410
1040 mechanizing 9410
1041 Latinizes 9410
1042 timesharing 9410
1043 Aden 9410
1044 embassies 9410
1045 males 9410
1046 shapelessly 9410
1047 mastering 9410
1048 Newtonian 9410
1049 finishers 9410
1050 abates 9410
1051 teem 9410
1052 kiting 9410
1053 stodgy 9410
1054 feed 9410
1055 guitars 9410
1056 airships 9410
1057 store 9410
1058 denounces 9410
1059 Pyle 9410
1060 Saxony 9410
1061 serializations 9410
1062 Peruvian 9410
1063 taxonomically 9410
1064 kingdom 9410
1065 stint 9410
1066 Sault 9410
1067 faithful 9410
1068 Ganymede 9410
1069 tidiness 9410
1070 gainful 9410
1071 contrary 9410
1072 Tipperary 9410
1073 tropics 9410
1074 theorizers 9410
1075 renew 9410
1076 already 9410
1077 terminal 9410
1078 Hegelian 9410
1079 hypothesizer 9410
1080 warningly 9410
1081 journalizing 9410
1082 nested 9410
1083 Lars 9410
1084 saplings 9410
1085 foothill 9410
1086 labeled 9410
1087 imperiously 9410
1088 reporters 9410
1089 furnishings 9410
1090 precipitable 9410
1091 discounts 9410
1092 excises 9410
1093 Stalin 9410
1094 despot 9410
1095 ripeness 9410
1096 Arabia 9410
1097 unruly 9410
1098 mournfulness 9410
1099 boom 9410
1100 slaughter 9410
1101 Sabine 9410
1102 handy 9410
1103 rural 9410
1104 organizer 9410
1105 shipyard 9410
1106 civics 9410
1107 inaccuracy 9410
1108 rules 9410
1109 juveniles 9410
1110 comprised 9410
1111 investigations 9410
1112 stabilizes 9410
1113 seminaries 9410
1114 Hunter 9410
1115 sporty 9410
1116 test 9410
1117 weasels 9410
1118 CERN 9410
1119 tempering 9410
1120 afore 9410
1121 Galatean 9410
1122 techniques 9410
1123 error 9410
1124 veranda 9410
1125 severely 9410
1126 Cassites 9410
1127 forthcoming 9410
1128 guides 9410
1129 vanish 9410
1130 lied 9410
1131 sawtooth 9410
1132 fated 9410
1133 gradually 9410
1134 widens 9410
1135 preclude 9410
1136 evenhandedly 9410
1137 percentage 9410
1138 disobedience 9410
1139 humility 9410
1140 gleaning 9410
1141 petted 9410
1142 bloater 9410
1143 minion 9410
1144 marginal 9410
1145 apiary 9410
1146 measures 9410
1147 precaution 9410
1148 repelled 9410
1149 primary 9410
1150 coverings 9410
1151 Artemia 9410
1152 navigate 9410
1153 spatial 9410
1154 Gurkha 9410
1155 meanwhile 9410
1156 Melinda 9410
1157 Butterfield 9410
1158 Aldrich 9410
1159 previewing 9410
1160 glut 9410
1161 unaffected 9410
1162 inmate 9410
1163 mineral 9410
1164 impending 9410
1165 meditation 9410
1166 ideas 9410
1167 miniaturizes 9410
1168 lewdly 9410
1169 title 9410
1170 youthfulness 9410
1171 creak 9410
1172 Chippewa 9410
1173 clamored 9410
1174 freezes 9410
1175 forgivably 9410
1176 reduce 9410
1177 McGovern 9410
1178 Nazis 9410
1179 epistle 9410
1180 socializes 9410
1181 conceptions 9410
1182 Kevin 9410
1183 uncovering 9410
1184 chews 9410
1185 appendixes 9410
1186 appendixes 9410
1187 appendixes 9410
1188 appendixes 9410
1189 appendixes 9410
1190 appendixes 9410
1191 raining 9410
1192 infest 9410
1193 compartment 9410
1194 minting 9410
1195 ducks 9410
1196 roped 9410
1197 waltz 9410
1198 Lillian 9410
1199 repressions 9410
1200 chillingly 9410
1201 noncritical 9410
1202 lithograph 9410
1203 spongers 9410
1204 parenthood 9410
1205 posed 9410
1206 instruments 9410
1207 filial 9410
1208 fixedly 9410
1209 relives 9410
1210 Pandora 9410
1211 watering 9410
1212 ungrateful 9410
1213 secures 9410
1214 poison 9410
1215 dusted 9410
1216 encompasses 9410
1217 presentation 9410
1218 Kantian 9410
1219 select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;
1220 fld3 period price price2
1221 admonishing 1002 28357832 8723648
1222 analyzable 1002 28357832 8723648
1223 annihilates 1001 5987435 234724
1224 Antares 1002 28357832 8723648
1225 astound 1001 5987435 234724
1226 audiology 1001 5987435 234724
1227 Augustine 1002 28357832 8723648
1228 Baird 1002 28357832 8723648
1229 bewilderingly 1001 5987435 234724
1230 breaking 1001 5987435 234724
1231 Conley 1001 5987435 234724
1232 dentally 1002 28357832 8723648
1233 dissociate 1002 28357832 8723648
1234 elite 1001 5987435 234724
1235 eschew 1001 5987435 234724
1236 Eulerian 1001 5987435 234724
1237 flanking 1001 5987435 234724
1238 foldout 1002 28357832 8723648
1239 funereal 1002 28357832 8723648
1240 galling 1002 28357832 8723648
1241 Graves 1001 5987435 234724
1242 grazing 1001 5987435 234724
1243 groupings 1001 5987435 234724
1244 handgun 1001 5987435 234724
1245 humility 1002 28357832 8723648
1246 impulsive 1002 28357832 8723648
1247 inch 1001 5987435 234724
1248 intelligibility 1001 5987435 234724
1249 jarring 1001 5987435 234724
1250 lawgiver 1001 5987435 234724
1251 lectured 1002 28357832 8723648
1252 Merritt 1002 28357832 8723648
1253 neonatal 1001 5987435 234724
1254 offload 1002 28357832 8723648
1255 parters 1002 28357832 8723648
1256 pityingly 1002 28357832 8723648
1257 puddings 1002 28357832 8723648
1258 Punjab 1001 5987435 234724
1259 quitter 1002 28357832 8723648
1260 realtor 1001 5987435 234724
1261 relaxing 1001 5987435 234724
1262 repetitions 1001 5987435 234724
1263 resumes 1001 5987435 234724
1264 Romans 1002 28357832 8723648
1265 rusting 1001 5987435 234724
1266 scholastics 1001 5987435 234724
1267 skulking 1002 28357832 8723648
1268 stated 1002 28357832 8723648
1269 suites 1002 28357832 8723648
1270 sureties 1001 5987435 234724
1271 testicle 1002 28357832 8723648
1272 tinily 1002 28357832 8723648
1273 tragedies 1001 5987435 234724
1274 trimmings 1001 5987435 234724
1275 vacuuming 1001 5987435 234724
1276 ventilate 1001 5987435 234724
1277 wallet 1001 5987435 234724
1278 Weissmuller 1002 28357832 8723648
1279 Wotan 1002 28357832 8723648
1280 select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37;
1281 fld1 fld3 period price price2
1282 018201 relaxing 1001 5987435 234724
1283 018601 vacuuming 1001 5987435 234724
1284 018801 inch 1001 5987435 234724
1285 018811 repetitions 1001 5987435 234724
1286 create table t4 (
1287 companynr tinyint(2) unsigned zerofill NOT NULL default '00',
1288 companyname char(30) NOT NULL default '',
1289 PRIMARY KEY (companynr),
1290 UNIQUE KEY companyname(companyname)
1291 ) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
1292 select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1293 companynr companyname
1294 00 Unknown
1295 29 company 1
1296 34 company 2
1297 36 company 3
1298 37 company 4
1299 40 company 5
1300 41 company 6
1301 50 company 11
1302 53 company 7
1303 58 company 8
1304 65 company 9
1305 68 company 10
1306 select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;
1307 companynr companyname
1308 00 Unknown
1309 29 company 1
1310 34 company 2
1311 36 company 3
1312 37 company 4
1313 40 company 5
1314 41 company 6
1315 50 company 11
1316 53 company 7
1317 58 company 8
1318 65 company 9
1319 68 company 10
1320 select * from t1,t1 t12;
1321 Period Varor_period Period Varor_period
1322 9410 9412 9410 9412
1323 select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505;
1324 fld1 fld1
1325 250501 250501
1326 250502 250501
1327 250503 250501
1328 250504 250501
1329 250505 250501
1330 250501 250502
1331 250502 250502
1332 250503 250502
1333 250504 250502
1334 250505 250502
1335 250501 250503
1336 250502 250503
1337 250503 250503
1338 250504 250503
1339 250505 250503
1340 250501 250504
1341 250502 250504
1342 250503 250504
1343 250504 250504
1344 250505 250504
1345 250501 250505
1346 250502 250505
1347 250503 250505
1348 250504 250505
1349 250505 250505
1350 insert into t2 (fld1, companynr) values (999999,99);
1351 select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1352 companynr companyname
1353 99 NULL
1354 select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
1355 count(*)
1356 1199
1357 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
1358 id select_type table type possible_keys key key_len ref rows Extra
1359 1 SIMPLE t2 ALL NULL NULL NULL NULL 1200
1360 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists
1361 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
1362 id select_type table type possible_keys key key_len ref rows Extra
1363 1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1364 1 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists
1365 select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1366 companynr companyname
1367 select count(*) from t2 left join t4 using (companynr) where companynr is not null;
1368 count(*)
1369 1200
1370 explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
1371 id select_type table type possible_keys key key_len ref rows Extra
1372 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1373 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
1374 id select_type table type possible_keys key key_len ref rows Extra
1375 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1376 delete from t2 where fld1=999999;
1377 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
1378 id select_type table type possible_keys key key_len ref rows Extra
1379 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1380 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1381 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
1382 id select_type table type possible_keys key key_len ref rows Extra
1383 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1384 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1385 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
1386 id select_type table type possible_keys key key_len ref rows Extra
1387 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1388 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
1389 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
1390 id select_type table type possible_keys key key_len ref rows Extra
1391 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1392 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1393 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
1394 id select_type table type possible_keys key key_len ref rows Extra
1395 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1396 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1397 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
1398 id select_type table type possible_keys key key_len ref rows Extra
1399 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1400 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1401 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
1402 id select_type table type possible_keys key key_len ref rows Extra
1403 1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1404 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1405 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
1406 id select_type table type possible_keys key key_len ref rows Extra
1407 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12
1408 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1409 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
1410 id select_type table type possible_keys key key_len ref rows Extra
1411 1 SIMPLE t4 ALL NULL NULL NULL NULL 12
1412 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where
1413 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
1414 id select_type table type possible_keys key key_len ref rows Extra
1415 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1416 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1417 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
1418 id select_type table type possible_keys key key_len ref rows Extra
1419 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where
1420 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1421 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
1422 id select_type table type possible_keys key key_len ref rows Extra
1423 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where
1424 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1425 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1426 companynr companynr
1427 37 36
1428 41 40
1429 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
1430 id select_type table type possible_keys key key_len ref rows Extra
1431 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary
1432 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer
1433 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
1434 fld1 companynr fld3 period
1435 038008 37 reporters 1008
1436 038208 37 Selfridge 1008
1437 select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1438 fld1 companynr fld3 period
1439 038008 37 reporters 1008
1440 038208 37 Selfridge 1008
1441 select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009;
1442 fld1 companynr fld3 period
1443 038008 37 reporters 1008
1444 038208 37 Selfridge 1008
1445 select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909);
1446 period
1447 9410
1448 select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6)));
1449 period
1450 9410
1451 select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1;
1452 fld1
1453 250501
1454 250502
1455 250503
1456 250505
1457 select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606);
1458 fld1
1459 250502
1460 250503
1461 select fld1 from t2 where fld1 between 250502 and 250504;
1462 fld1
1463 250502
1464 250503
1465 250504
1466 select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ;
1467 fld3
1468 label
1469 labeled
1470 labeled
1471 landslide
1472 laterally
1473 leaflet
1474 lewdly
1475 Lillian
1476 luckily
1477 select count(*) from t1;
1478 count(*)
1479 1
1480 select companynr,count(*),sum(fld1) from t2 group by companynr;
1481 companynr count(*) sum(fld1)
1482 00 82 10355753
1483 29 95 14473298
1484 34 70 17788966
1485 36 215 22786296
1486 37 588 83602098
1487 40 37 6618386
1488 41 52 12816335
1489 50 11 1595438
1490 53 4 793210
1491 58 23 2254293
1492 65 10 2284055
1493 68 12 3097288
1494 select companynr,count(*) from t2 group by companynr order by companynr desc limit 5;
1495 companynr count(*)
1496 68 12
1497 65 10
1498 58 23
1499 53 4
1500 50 11
1501 select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1502 count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1503 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1504 explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>"";
1505 id select_type table type possible_keys key key_len ref rows filtered Extra
1506 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where
1507 Warnings:
1508 Note 1003 select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> ''))
1509 select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3;
1510 companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1)
1511 00 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087
1512 29 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026
1513 34 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069
1514 select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1515 companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1516 37 1 1 5987435 5987435 5987435 5987435.0000
1517 37 2 1 28357832 28357832 28357832 28357832.0000
1518 37 3 1 39654943 39654943 39654943 39654943.0000
1519 37 11 1 5987435 5987435 5987435 5987435.0000
1520 37 12 1 28357832 28357832 28357832 28357832.0000
1521 37 13 1 39654943 39654943 39654943 39654943.0000
1522 37 21 1 5987435 5987435 5987435 5987435.0000
1523 37 22 1 28357832 28357832 28357832 28357832.0000
1524 37 23 1 39654943 39654943 39654943 39654943.0000
1525 37 31 1 5987435 5987435 5987435 5987435.0000
1526 select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10;
1527 companynr t2nr count(price) sum(price) min(price) max(price) avg(price)
1528 37 1 1 5987435 5987435 5987435 5987435.0000
1529 37 2 1 28357832 28357832 28357832 28357832.0000
1530 37 3 1 39654943 39654943 39654943 39654943.0000
1531 37 11 1 5987435 5987435 5987435 5987435.0000
1532 37 12 1 28357832 28357832 28357832 28357832.0000
1533 37 13 1 39654943 39654943 39654943 39654943.0000
1534 37 21 1 5987435 5987435 5987435 5987435.0000
1535 37 22 1 28357832 28357832 28357832 28357832.0000
1536 37 23 1 39654943 39654943 39654943 39654943.0000
1537 37 31 1 5987435 5987435 5987435 5987435.0000
1538 select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ;
1539 companynr count(price) sum(price) min(price) max(price) avg(price)
1540 37 12543 309394878010 5987435 39654943 24666736.6667
1541 78 8362 414611089292 726498 98439034 49582766.0000
1542 101 4181 3489454238 834598 834598 834598.0000
1543 154 4181 4112197254950 983543950 983543950 983543950.0000
1544 311 4181 979599938 234298 234298 234298.0000
1545 447 4181 9929180954 2374834 2374834 2374834.0000
1546 512 4181 3288532102 786542 786542 786542.0000
1547 select distinct mod(companynr,10) from t4 group by companynr;
1548 mod(companynr,10)
1549 0
1550 9
1551 4
1552 6
1553 7
1554 1
1555 3
1556 8
1557 5
1558 select distinct 1 from t4 group by companynr;
1559 1
1560 1
1561 select count(distinct fld1) from t2;
1562 count(distinct fld1)
1563 1199
1564 select companynr,count(distinct fld1) from t2 group by companynr;
1565 companynr count(distinct fld1)
1566 00 82
1567 29 95
1568 34 70
1569 36 215
1570 37 588
1571 40 37
1572 41 52
1573 50 11
1574 53 4
1575 58 23
1576 65 10
1577 68 12
1578 select companynr,count(*) from t2 group by companynr;
1579 companynr count(*)
1580 00 82
1581 29 95
1582 34 70
1583 36 215
1584 37 588
1585 40 37
1586 41 52
1587 50 11
1588 53 4
1589 58 23
1590 65 10
1591 68 12
1592 select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr;
1593 companynr count(distinct concat(fld1,repeat(65,1000)))
1594 00 82
1595 29 95
1596 34 70
1597 36 215
1598 37 588
1599 40 37
1600 41 52
1601 50 11
1602 53 4
1603 58 23
1604 65 10
1605 68 12
1606 select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr;
1607 companynr count(distinct concat(fld1,repeat(65,200)))
1608 00 82
1609 29 95
1610 34 70
1611 36 215
1612 37 588
1613 40 37
1614 41 52
1615 50 11
1616 53 4
1617 58 23
1618 65 10
1619 68 12
1620 select companynr,count(distinct floor(fld1/100)) from t2 group by companynr;
1621 companynr count(distinct floor(fld1/100))
1622 00 47
1623 29 35
1624 34 14
1625 36 69
1626 37 108
1627 40 16
1628 41 11
1629 50 9
1630 53 1
1631 58 1
1632 65 1
1633 68 1
1634 select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr;
1635 companynr count(distinct concat(repeat(65,1000),floor(fld1/100)))
1636 00 47
1637 29 35
1638 34 14
1639 36 69
1640 37 108
1641 40 16
1642 41 11
1643 50 9
1644 53 1
1645 58 1
1646 65 1
1647 68 1
1648 select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10;
1649 sum(fld1) fld3
1650 11402 Romans
1651 select name,count(*) from t3 where name='cloakroom' group by name;
1652 name count(*)
1653 cloakroom 4181
1654 select name,count(*) from t3 where name='cloakroom' and price>10 group by name;
1655 name count(*)
1656 cloakroom 4181
1657 select count(*) from t3 where name='cloakroom' and price2=823742;
1658 count(*)
1659 4181
1660 select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name;
1661 name count(*)
1662 cloakroom 4181
1663 select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name;
1664 name count(*)
1665 extramarital 4181
1666 gazer 4181
1667 gems 4181
1668 Iranizes 4181
1669 spates 4181
1670 tucked 4181
1671 violinist 4181
1672 select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1673 fld3 count(*)
1674 spates 4181
1675 select companynr|0,companyname from t4 group by 1;
1676 companynr|0 companyname
1677 0 Unknown
1678 29 company 1
1679 34 company 2
1680 36 company 3
1681 37 company 4
1682 40 company 5
1683 41 company 6
1684 50 company 11
1685 53 company 7
1686 58 company 8
1687 65 company 9
1688 68 company 10
1689 select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname;
1690 companynr companyname count(*)
1691 29 company 1 95
1692 68 company 10 12
1693 50 company 11 11
1694 34 company 2 70
1695 36 company 3 215
1696 37 company 4 588
1697 40 company 5 37
1698 41 company 6 52
1699 53 company 7 4
1700 58 company 8 23
1701 65 company 9 10
1702 00 Unknown 82
1703 select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name;
1704 fld1 count(*)
1705 158402 4181
1706 select sum(Period)/count(*) from t1;
1707 sum(Period)/count(*)
1708 9410.0000
1709 select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr;
1710 companynr count sum diff func
1711 37 12543 309394878010 0.0000 464091
1712 78 8362 414611089292 0.0000 652236
1713 101 4181 3489454238 0.0000 422281
1714 154 4181 4112197254950 0.0000 643874
1715 311 4181 979599938 0.0000 1300291
1716 447 4181 9929180954 0.0000 1868907
1717 512 4181 3288532102 0.0000 2140672
1718 select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg;
1719 companynr avg
1720 154 983543950.0000
1721 select companynr,count(*) from t2 group by companynr order by 2 desc;
1722 companynr count(*)
1723 37 588
1724 36 215
1725 29 95
1726 00 82
1727 34 70
1728 41 52
1729 40 37
1730 58 23
1731 68 12
1732 50 11
1733 65 10
1734 53 4
1735 select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc;
1736 companynr count(*)
1737 41 52
1738 58 23
1739 68 12
1740 50 11
1741 65 10
1742 53 4
1743 select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4;
1744 fld4 fld1 count(price) sum(price) min(price) max(price) avg(price)
1745 teethe 000001 1 5987435 5987435 5987435 5987435.0000
1746 dreaded 011401 1 5987435 5987435 5987435 5987435.0000
1747 scholastics 011402 1 28357832 28357832 28357832 28357832.0000
1748 audiology 011403 1 39654943 39654943 39654943 39654943.0000
1749 wallet 011501 1 5987435 5987435 5987435 5987435.0000
1750 parters 011701 1 5987435 5987435 5987435 5987435.0000
1751 eschew 011702 1 28357832 28357832 28357832 28357832.0000
1752 quitter 011703 1 39654943 39654943 39654943 39654943.0000
1753 neat 012001 1 5987435 5987435 5987435 5987435.0000
1754 Steinberg 012003 1 39654943 39654943 39654943 39654943.0000
1755 balled 012301 1 5987435 5987435 5987435 5987435.0000
1756 persist 012302 1 28357832 28357832 28357832 28357832.0000
1757 attainments 012303 1 39654943 39654943 39654943 39654943.0000
1758 capably 012501 1 5987435 5987435 5987435 5987435.0000
1759 impulsive 012602 1 28357832 28357832 28357832 28357832.0000
1760 starlet 012603 1 39654943 39654943 39654943 39654943.0000
1761 featherweight 012701 1 5987435 5987435 5987435 5987435.0000
1762 pessimist 012702 1 28357832 28357832 28357832 28357832.0000
1763 daughter 012703 1 39654943 39654943 39654943 39654943.0000
1764 lawgiver 013601 1 5987435 5987435 5987435 5987435.0000
1765 stated 013602 1 28357832 28357832 28357832 28357832.0000
1766 readable 013603 1 39654943 39654943 39654943 39654943.0000
1767 testicle 013801 1 5987435 5987435 5987435 5987435.0000
1768 Parsifal 013802 1 28357832 28357832 28357832 28357832.0000
1769 leavings 013803 1 39654943 39654943 39654943 39654943.0000
1770 squeaking 013901 1 5987435 5987435 5987435 5987435.0000
1771 contrasted 016001 1 5987435 5987435 5987435 5987435.0000
1772 leftover 016201 1 5987435 5987435 5987435 5987435.0000
1773 whiteners 016202 1 28357832 28357832 28357832 28357832.0000
1774 erases 016301 1 5987435 5987435 5987435 5987435.0000
1775 Punjab 016302 1 28357832 28357832 28357832 28357832.0000
1776 Merritt 016303 1 39654943 39654943 39654943 39654943.0000
1777 sweetish 018001 1 5987435 5987435 5987435 5987435.0000
1778 dogging 018002 1 28357832 28357832 28357832 28357832.0000
1779 scornfully 018003 1 39654943 39654943 39654943 39654943.0000
1780 fetters 018012 1 28357832 28357832 28357832 28357832.0000
1781 bivalves 018013 1 39654943 39654943 39654943 39654943.0000
1782 skulking 018021 1 5987435 5987435 5987435 5987435.0000
1783 flint 018022 1 28357832 28357832 28357832 28357832.0000
1784 flopping 018023 1 39654943 39654943 39654943 39654943.0000
1785 Judas 018032 1 28357832 28357832 28357832 28357832.0000
1786 vacuuming 018033 1 39654943 39654943 39654943 39654943.0000
1787 medical 018041 1 5987435 5987435 5987435 5987435.0000
1788 bloodbath 018042 1 28357832 28357832 28357832 28357832.0000
1789 subschema 018043 1 39654943 39654943 39654943 39654943.0000
1790 interdependent 018051 1 5987435 5987435 5987435 5987435.0000
1791 Graves 018052 1 28357832 28357832 28357832 28357832.0000
1792 neonatal 018053 1 39654943 39654943 39654943 39654943.0000
1793 sorters 018061 1 5987435 5987435 5987435 5987435.0000
1794 epistle 018062 1 28357832 28357832 28357832 28357832.0000
1795 Conley 018101 1 5987435 5987435 5987435 5987435.0000
1796 lectured 018102 1 28357832 28357832 28357832 28357832.0000
1797 Abraham 018103 1 39654943 39654943 39654943 39654943.0000
1798 cage 018201 1 5987435 5987435 5987435 5987435.0000
1799 hushes 018202 1 28357832 28357832 28357832 28357832.0000
1800 Simla 018402 1 28357832 28357832 28357832 28357832.0000
1801 reporters 018403 1 39654943 39654943 39654943 39654943.0000
1802 coexist 018601 1 5987435 5987435 5987435 5987435.0000
1803 Beebe 018602 1 28357832 28357832 28357832 28357832.0000
1804 Taoism 018603 1 39654943 39654943 39654943 39654943.0000
1805 Connally 018801 1 5987435 5987435 5987435 5987435.0000
1806 fetched 018802 1 28357832 28357832 28357832 28357832.0000
1807 checkpoints 018803 1 39654943 39654943 39654943 39654943.0000
1808 gritty 018811 1 5987435 5987435 5987435 5987435.0000
1809 firearm 018812 1 28357832 28357832 28357832 28357832.0000
1810 minima 019101 1 5987435 5987435 5987435 5987435.0000
1811 Selfridge 019102 1 28357832 28357832 28357832 28357832.0000
1812 disable 019103 1 39654943 39654943 39654943 39654943.0000
1813 witchcraft 019201 1 5987435 5987435 5987435 5987435.0000
1814 betroth 030501 1 5987435 5987435 5987435 5987435.0000
1815 Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000
1816 imprint 030503 1 39654943 39654943 39654943 39654943.0000
1817 swelling 031901 1 5987435 5987435 5987435 5987435.0000
1818 interrelationships 036001 1 5987435 5987435 5987435 5987435.0000
1819 riser 036002 1 28357832 28357832 28357832 28357832.0000
1820 bee 038001 1 5987435 5987435 5987435 5987435.0000
1821 kanji 038002 1 28357832 28357832 28357832 28357832.0000
1822 dental 038003 1 39654943 39654943 39654943 39654943.0000
1823 railway 038011 1 5987435 5987435 5987435 5987435.0000
1824 validate 038012 1 28357832 28357832 28357832 28357832.0000
1825 normalizes 038013 1 39654943 39654943 39654943 39654943.0000
1826 Kline 038101 1 5987435 5987435 5987435 5987435.0000
1827 Anatole 038102 1 28357832 28357832 28357832 28357832.0000
1828 partridges 038103 1 39654943 39654943 39654943 39654943.0000
1829 recruited 038201 1 5987435 5987435 5987435 5987435.0000
1830 dimensions 038202 1 28357832 28357832 28357832 28357832.0000
1831 Chicana 038203 1 39654943 39654943 39654943 39654943.0000
1832 select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3;
1833 companynr fld3 sum(price)
1834 512 boat 786542
1835 512 capably 786542
1836 512 cupboard 786542
1837 512 decliner 786542
1838 512 descendants 786542
1839 512 dopers 786542
1840 512 erases 786542
1841 512 Micronesia 786542
1842 512 Miles 786542
1843 512 skies 786542
1844 select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr;
1845 companynr count(*) min(fld3) max(fld3) sum(price) avg(price)
1846 00 1 Omaha Omaha 5987435 5987435.0000
1847 36 1 dubbed dubbed 28357832 28357832.0000
1848 37 83 Abraham Wotan 1908978016 22999735.1325
1849 50 2 scribbled tapestry 68012775 34006387.5000
1850 select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1;
1851 t3.companynr+0 t2nr fld3 sum(price)
1852 37 1 Omaha 5987435
1853 37 11401 breaking 5987435
1854 37 11402 Romans 28357832
1855 37 11403 intercepted 39654943
1856 37 11501 bewilderingly 5987435
1857 37 11701 astound 5987435
1858 37 11702 admonishing 28357832
1859 37 11703 sumac 39654943
1860 37 12001 flanking 5987435
1861 37 12003 combed 39654943
1862 37 12301 Eulerian 5987435
1863 37 12302 dubbed 28357832
1864 37 12303 Kane 39654943
1865 37 12501 annihilates 5987435
1866 37 12602 Wotan 28357832
1867 37 12603 snatching 39654943
1868 37 12701 grazing 5987435
1869 37 12702 Baird 28357832
1870 37 12703 celery 39654943
1871 37 13601 handgun 5987435
1872 37 13602 foldout 28357832
1873 37 13603 mystic 39654943
1874 37 13801 intelligibility 5987435
1875 37 13802 Augustine 28357832
1876 37 13803 teethe 39654943
1877 37 13901 scholastics 5987435
1878 37 16001 audiology 5987435
1879 37 16201 wallet 5987435
1880 37 16202 parters 28357832
1881 37 16301 eschew 5987435
1882 37 16302 quitter 28357832
1883 37 16303 neat 39654943
1884 37 18001 jarring 5987435
1885 37 18002 tinily 28357832
1886 37 18003 balled 39654943
1887 37 18012 impulsive 28357832
1888 37 18013 starlet 39654943
1889 37 18021 lawgiver 5987435
1890 37 18022 stated 28357832
1891 37 18023 readable 39654943
1892 37 18032 testicle 28357832
1893 37 18033 Parsifal 39654943
1894 37 18041 Punjab 5987435
1895 37 18042 Merritt 28357832
1896 37 18043 Quixotism 39654943
1897 37 18051 sureties 5987435
1898 37 18052 puddings 28357832
1899 37 18053 tapestry 39654943
1900 37 18061 trimmings 5987435
1901 37 18062 humility 28357832
1902 37 18101 tragedies 5987435
1903 37 18102 skulking 28357832
1904 37 18103 flint 39654943
1905 37 18201 relaxing 5987435
1906 37 18202 offload 28357832
1907 37 18402 suites 28357832
1908 37 18403 lists 39654943
1909 37 18601 vacuuming 5987435
1910 37 18602 dentally 28357832
1911 37 18603 humanness 39654943
1912 37 18801 inch 5987435
1913 37 18802 Weissmuller 28357832
1914 37 18803 irresponsibly 39654943
1915 37 18811 repetitions 5987435
1916 37 18812 Antares 28357832
1917 37 19101 ventilate 5987435
1918 37 19102 pityingly 28357832
1919 37 19103 interdependent 39654943
1920 37 19201 Graves 5987435
1921 37 30501 neonatal 5987435
1922 37 30502 scribbled 28357832
1923 37 30503 chafe 39654943
1924 37 31901 realtor 5987435
1925 37 36001 elite 5987435
1926 37 36002 funereal 28357832
1927 37 38001 Conley 5987435
1928 37 38002 lectured 28357832
1929 37 38003 Abraham 39654943
1930 37 38011 groupings 5987435
1931 37 38012 dissociate 28357832
1932 37 38013 coexist 39654943
1933 37 38101 rusting 5987435
1934 37 38102 galling 28357832
1935 37 38103 obliterates 39654943
1936 37 38201 resumes 5987435
1937 37 38202 analyzable 28357832
1938 37 38203 terminator 39654943
1939 select sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1= t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008;
1940 sum(price)
1941 234298
1942 select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1;
1943 fld1 sum(price)
1944 038008 234298
1945 explain select fld3 from t2 where 1>2 or 2>3;
1946 id select_type table type possible_keys key key_len ref rows Extra
1947 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
1948 explain select fld3 from t2 where fld1=fld1;
1949 id select_type table type possible_keys key key_len ref rows Extra
1950 1 SIMPLE t2 ALL NULL NULL NULL NULL 1199
1951 select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
1952 companynr fld1
1953 34 250501
1954 34 250502
1955 select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502;
1956 companynr fld1
1957 34 250501
1958 34 250502
1959 select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000;
1960 companynr count sum
1961 00 82 10355753
1962 29 95 14473298
1963 34 70 17788966
1964 37 588 83602098
1965 41 52 12816335
1966 select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ;
1967 companynr
1968 00
1969 29
1970 34
1971 37
1972 41
1973 select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40;
1974 companynr companyname count(*)
1975 68 company 10 12
1976 50 company 11 11
1977 40 company 5 37
1978 41 company 6 52
1979 53 company 7 4
1980 58 company 8 23
1981 65 company 9 10
1982 select count(*) from t2;
1983 count(*)
1984 1199
1985 select count(*) from t2 where fld1 < 098024;
1986 count(*)
1987 387
1988 select min(fld1) from t2 where fld1>= 098024;
1989 min(fld1)
1990 98024
1991 select max(fld1) from t2 where fld1>= 098024;
1992 max(fld1)
1993 1232609
1994 select count(*) from t3 where price2=76234234;
1995 count(*)
1996 4181
1997 select count(*) from t3 where companynr=512 and price2=76234234;
1998 count(*)
1999 4181
2000 explain select min(fld1),max(fld1),count(*) from t2;
2001 id select_type table type possible_keys key key_len ref rows Extra
2002 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2003 select min(fld1),max(fld1),count(*) from t2;
2004 min(fld1) max(fld1) count(*)
2005 0 1232609 1199
2006 select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742;
2007 min(t2nr) max(t2nr)
2008 2115 2115
2009 select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78;
2010 count(*) min(t2nr) max(t2nr)
2011 4181 4 41804
2012 select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20;
2013 t2nr count(*)
2014 9 1
2015 19 1
2016 29 1
2017 39 1
2018 49 1
2019 59 1
2020 69 1
2021 79 1
2022 89 1
2023 99 1
2024 109 1
2025 119 1
2026 129 1
2027 139 1
2028 149 1
2029 159 1
2030 169 1
2031 179 1
2032 189 1
2033 199 1
2034 select max(t2nr) from t3 where price=983543950;
2035 max(t2nr)
2036 41807
2037 select t1.period from t3 = t1 limit 1;
2038 period
2039 1001
2040 select t1.period from t1 as t1 limit 1;
2041 period
2042 9410
2043 select t1.period as "Nuvarande period" from t1 as t1 limit 1;
2044 Nuvarande period
2045 9410
2046 select period as ok_period from t1 limit 1;
2047 ok_period
2048 9410
2049 select period as ok_period from t1 group by ok_period limit 1;
2050 ok_period
2051 9410
2052 select 1+1 as summa from t1 group by summa limit 1;
2053 summa
2054 2
2055 select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1;
2056 Nuvarande period
2057 9410
2058 show tables;
2059 Tables_in_test
2060 t1
2061 t2
2062 t3
2063 t4
2064 show tables from test like "s%";
2065 Tables_in_test (s%)
2066 show tables from test like "t?";
2067 Tables_in_test (t?)
2068 show full columns from t2;
2069 Field Type Collation Null Key Default Extra Privileges Comment
2070 auto int(11) NULL NO PRI NULL auto_increment #
2071 fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2072 companynr tinyint(2) unsigned zerofill NULL NO 00 #
2073 fld3 char(30) latin1_swedish_ci NO MUL #
2074 fld4 char(35) latin1_swedish_ci NO #
2075 fld5 char(35) latin1_swedish_ci NO #
2076 fld6 char(4) latin1_swedish_ci NO #
2077 show full columns from t2 from test like 'f%';
2078 Field Type Collation Null Key Default Extra Privileges Comment
2079 fld1 int(6) unsigned zerofill NULL NO UNI 000000 #
2080 fld3 char(30) latin1_swedish_ci NO MUL #
2081 fld4 char(35) latin1_swedish_ci NO #
2082 fld5 char(35) latin1_swedish_ci NO #
2083 fld6 char(4) latin1_swedish_ci NO #
2084 show full columns from t2 from test like 's%';
2085 Field Type Collation Null Key Default Extra Privileges Comment
2086 show keys from t2;
2087 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
2088 t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE
2089 t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE
2090 t2 1 fld3 1 fld3 A NULL NULL NULL BTREE
2091 drop table t4, t3, t2, t1;
2092 CREATE TABLE t1 (
2093 cont_nr int(11) NOT NULL auto_increment,
2094 ver_nr int(11) NOT NULL default '0',
2095 aufnr int(11) NOT NULL default '0',
2096 username varchar(50) NOT NULL default '',
2097 hdl_nr int(11) NOT NULL default '0',
2098 eintrag date NOT NULL default '0000-00-00',
2099 st_klasse varchar(40) NOT NULL default '',
2100 st_wert varchar(40) NOT NULL default '',
2101 st_zusatz varchar(40) NOT NULL default '',
2102 st_bemerkung varchar(255) NOT NULL default '',
2103 kunden_art varchar(40) NOT NULL default '',
2104 mcbs_knr int(11) default NULL,
2105 mcbs_aufnr int(11) NOT NULL default '0',
2106 schufa_status char(1) default '?',
2107 bemerkung text,
2108 wirknetz text,
2109 wf_igz int(11) NOT NULL default '0',
2110 tarifcode varchar(80) default NULL,
2111 recycle char(1) default NULL,
2112 sim varchar(30) default NULL,
2113 mcbs_tpl varchar(30) default NULL,
2114 emp_nr int(11) NOT NULL default '0',
2115 laufzeit int(11) default NULL,
2116 hdl_name varchar(30) default NULL,
2117 prov_hdl_nr int(11) NOT NULL default '0',
2118 auto_wirknetz varchar(50) default NULL,
2119 auto_billing varchar(50) default NULL,
2120 touch timestamp NOT NULL,
2121 kategorie varchar(50) default NULL,
2122 kundentyp varchar(20) NOT NULL default '',
2123 sammel_rech_msisdn varchar(30) NOT NULL default '',
2124 p_nr varchar(9) NOT NULL default '',
2125 suffix char(3) NOT NULL default '',
2126 PRIMARY KEY (cont_nr),
2127 KEY idx_aufnr(aufnr),
2128 KEY idx_hdl_nr(hdl_nr),
2129 KEY idx_st_klasse(st_klasse),
2130 KEY ver_nr(ver_nr),
2131 KEY eintrag_idx(eintrag),
2132 KEY emp_nr_idx(emp_nr),
2133 KEY wf_igz(wf_igz),
2134 KEY touch(touch),
2135 KEY hdl_tag(eintrag,hdl_nr),
2136 KEY prov_hdl_nr(prov_hdl_nr),
2137 KEY mcbs_aufnr(mcbs_aufnr),
2138 KEY kundentyp(kundentyp),
2139 KEY p_nr(p_nr,suffix)
2140 ) ENGINE=MyISAM;
2141 INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2142 INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2143 INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2144 INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2145 INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007');
2146 INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. möchte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2147 INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
2148 SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie;
2149 Kundentyp kategorie
2150 Privat (Private Nutzung) Mobilfunk
2151 Warnings:
2152 Warning 1052 Column 'kundentyp' in group statement is ambiguous
2153 drop table t1;