· 8 years ago · Jul 16, 2018, 09:00 PM
1===========================================
2
3
4sql
5- ddl data definition language
6- create , alter , drop
7- dml data maininpulation
8- insert, update , delete , select
9
10
11WEEK4 HOW TO USE SELECT WITH THIS COMMAND BELOW :
12
13- select
14- where
15- >,>=,<,<=,<> or !=
16- and
17- or
18order by
19distinct
20limit
21like
22in
23between
24as
25
26aggregate function
27- count
28- max
29- min
30- sum
31- avg
32group by
33having
34===========================================
35
36fIRST CREATE DATABASE JUST COPY COMMAND :
37
38
39-----------------------------------------------
40drop database if exists lab7db;
41
42create database lab7db;
43
44use lab7db;
45
46drop table if exists employees;
47
48create table employees(employeeid int primary key, firstname varchar(255), lastname varchar(255), email varchar(255), dob date, phone char(11), country varchar(255), children int);
49
50insert into employees values(1, 'John', 'Osupile', 'John.Osupile@yahoo.com', '1988-04-02', '017-6745481', 'Brunei',5);
51insert into employees values(2, 'Steven', 'Frecky', 'goldfish@fishhere.net', '1974-04-04', '012-6745441', 'Nigeria',2);
52insert into employees values(3, 'Agang', 'Maroba', 'am@heroindomain.org', '1988-04-02', '013-2079370', 'Brunei',3);
53insert into employees values(4, 'Elsie', 'Frecky', 'elsie@superdiva.co.uk', '1980-10-20', '019-3305184', 'Brunei',4);
54
55
56----------------------------------------------
57
58
59
60
61THIS ALL COMMAND USE IN LAB :
62
63
64
65
66mysql> select * from employees;
67+------------+-----------+----------+------------------------+------------+-----
68--------+---------+----------+
69| employeeid | firstname | lastname | email | dob | phon
70e | country | children |
71+------------+-----------+----------+------------------------+------------+-----
72--------+---------+----------+
73| 1 | John | Osupile | John.Osupile@yahoo.com | 1988-04-02 | 017-
746745481 | Brunei | 5 |
75| 2 | Steven | Frecky | goldfish@fishhere.net | 1974-04-04 | 012-
766745441 | Nigeria | 2 |
77| 3 | Agang | Maroba | am@heroindomain.org | 1988-04-02 | 013-
782079370 | Brunei | 3 |
79| 4 | Elsie | Frecky | elsie@superdiva.co.uk | 1980-10-20 | 019-
803305184 | Brunei | 4 |
81+------------+-----------+----------+------------------------+------------+-----
82--------+---------+----------+
834 rows in set (0.00 sec)
84
85
86mysql> select * from employees where employeeid=1;
87+------------+-----------+----------+------------------------+------------+-----
88--------+---------+----------+
89| employeeid | firstname | lastname | email | dob | phon
90e | country | children |
91+------------+-----------+----------+------------------------+------------+-----
92--------+---------+----------+
93| 1 | John | Osupile | John.Osupile@yahoo.com | 1988-04-02 | 017-
946745481 | Brunei | 5 |
95+------------+-----------+----------+------------------------+------------+-----
96--------+---------+----------+
971 row in set (0.03 sec)
98
99
100mysql> select firstname,lastname from employees;
101+-----------+----------+
102| firstname | lastname |
103+-----------+----------+
104| John | Osupile |
105| Steven | Frecky |
106| Agang | Maroba |
107| Elsie | Frecky |
108+-----------+----------+
1094 rows in set (0.00 sec)
110
111
112mysql> select firstname from employees;
113+-----------+
114| firstname |
115+-----------+
116| John |
117| Steven |
118| Agang |
119| Elsie |
120+-----------+
1214 rows in set (0.00 sec)
122
123
124mysql> select firstname from employees where employeeid=1;
125+-----------+
126| firstname |
127+-----------+
128| John |
129+-----------+
1301 row in set (0.00 sec)
131
132
133
134mysql> select * from employees \G
135*************************** 1. row ***************************
136employeeid: 1
137 firstname: John
138 lastname: Osupile
139 email: John.Osupile@yahoo.com
140 dob: 1988-04-02
141 phone: 017-6745481
142 country: Brunei
143 children: 5
144*************************** 2. row ***************************
145employeeid: 2
146 firstname: Steven
147 lastname: Frecky
148 email: goldfish@fishhere.net
149 dob: 1974-04-04
150 phone: 012-6745441
151 country: Nigeria
152 children: 2
153*************************** 3. row ***************************
154employeeid: 3
155 firstname: Agang
156 lastname: Maroba
157 email: am@heroindomain.org
158 dob: 1988-04-02
159 phone: 013-2079370
160 country: Brunei
161 children: 3
162*************************** 4. row ***************************
163employeeid: 4
164 firstname: Elsie
165 lastname: Frecky
166 email: elsie@superdiva.co.uk
167 dob: 1980-10-20
168 phone: 019-3305184
169 country: Brunei
170 children: 4
1714 rows in set (0.00 sec)
172
173mysql> select firstname from employees where children <=4;
174+-----------+
175| firstname |
176+-----------+
177| Steven |
178| Agang |
179| Elsie |
180+-----------+
1813 rows in set (0.00 sec)
182
183mysql> select firstname from employees where children <4;
184+-----------+
185| firstname |
186+-----------+
187| Steven |
188| Agang |
189+-----------+
1902 rows in set (0.00 sec)
191
192mysql> select firstname from employees where children >3;
193+-----------+
194| firstname |
195+-----------+
196| John |
197| Elsie |
198+-----------+
1992 rows in set (0.00 sec)
200
201mysql> select firstname from employees where country != "bruni";
202+-----------+
203| firstname |
204+-----------+
205| John |
206| Steven |
207| Agang |
208| Elsie |
209+-----------+
2104 rows in set (0.00 sec)
211
212
213mysql> select firstname from employees where country != "brunei" and children <4
214;
215+-----------+
216| firstname |
217+-----------+
218| Steven |
219+-----------+
2201 row in set (0.00 sec)
221
222mysql> select firstname from employees where country = "brunei" and children <4;
223
224+-----------+
225| firstname |
226+-----------+
227| Agang |
228+-----------+
2291 row in set (0.00 sec)
230
231mysql> select firstname from employees where country != "brunei";
232+-----------+
233| firstname |
234+-----------+
235| Steven |
236+-----------+
2371 row in set (0.00 sec)
238
239mysql> select firstname from employees where country = "brunei" or children <4;
240+-----------+
241| firstname |
242+-----------+
243| John |
244| Steven |
245| Agang |
246| Elsie |
247+-----------+
2484 rows in set (0.02 sec)
249
250
251mysql> select firstname from employees where country = "brunei" or children <4 o
252rder by firstname;
253+-----------+
254| firstname |
255+-----------+
256| Agang |
257| Elsie |
258| John |
259| Steven |
260+-----------+
2614 rows in set (0.00 sec)
262
263mysql> select * from empolyees order by firstname desc;
264ERROR 1146 (42S02): Table 'lab7db.empolyees' doesn't exist
265
266
267mysql> select * from employees order by firstname desc;
268+------------+-----------+----------+------------------------+------------+-----
269--------+---------+----------+
270| employeeid | firstname | lastname | email | dob | phon
271e | country | children |
272+------------+-----------+----------+------------------------+------------+-----
273--------+---------+----------+
274| 2 | Steven | Frecky | goldfish@fishhere.net | 1974-04-04 | 012-
2756745441 | Nigeria | 2 |
276| 1 | John | Osupile | John.Osupile@yahoo.com | 1988-04-02 | 017-
2776745481 | Brunei | 5 |
278| 4 | Elsie | Frecky | elsie@superdiva.co.uk | 1980-10-20 | 019-
2793305184 | Brunei | 4 |
280| 3 | Agang | Maroba | am@heroindomain.org | 1988-04-02 | 013-
2812079370 | Brunei | 3 |
282+------------+-----------+----------+------------------------+------------+-----
283--------+---------+----------+
2844 rows in set (0.00 sec)
285
286mysql> select * from employees order by firstname asc;
287+------------+-----------+----------+------------------------+------------+-----
288--------+---------+----------+
289| employeeid | firstname | lastname | email | dob | phon
290e | country | children |
291+------------+-----------+----------+------------------------+------------+-----
292--------+---------+----------+
293| 3 | Agang | Maroba | am@heroindomain.org | 1988-04-02 | 013-
2942079370 | Brunei | 3 |
295| 4 | Elsie | Frecky | elsie@superdiva.co.uk | 1980-10-20 | 019-
2963305184 | Brunei | 4 |
297| 1 | John | Osupile | John.Osupile@yahoo.com | 1988-04-02 | 017-
2986745481 | Brunei | 5 |
299| 2 | Steven | Frecky | goldfish@fishhere.net | 1974-04-04 | 012-
3006745441 | Nigeria | 2 |
301+------------+-----------+----------+------------------------+------------+-----
302--------+---------+----------+
3034 rows in set (0.00 sec)
304
305
306mysql> select distinct country from employees;
307+---------+
308| country |
309+---------+
310| Brunei |
311| Nigeria |
312+---------+
3132 rows in set (0.04 sec)
314
315mysql> select country from employees;
316+---------+
317| country |
318+---------+
319| Brunei |
320| Nigeria |
321| Brunei |
322| Brunei |
323+---------+
3244 rows in set (0.00 sec)
325
326
327mysql> select * from employees limit 0,1;
328+------------+-----------+----------+------------------------+------------+-----
329--------+---------+----------+
330| employeeid | firstname | lastname | email | dob | phon
331e | country | children |
332+------------+-----------+----------+------------------------+------------+-----
333--------+---------+----------+
334| 1 | John | Osupile | John.Osupile@yahoo.com | 1988-04-02 | 017-
3356745481 | Brunei | 5 |
336+------------+-----------+----------+------------------------+------------+-----
337--------+---------+----------+
3381 row in set (0.00 sec)
339
340mysql> select * from employees limit 0,2;
341+------------+-----------+----------+------------------------+------------+-----
342--------+---------+----------+
343| employeeid | firstname | lastname | email | dob | phon
344e | country | children |
345+------------+-----------+----------+------------------------+------------+-----
346--------+---------+----------+
347| 1 | John | Osupile | John.Osupile@yahoo.com | 1988-04-02 | 017-
3486745481 | Brunei | 5 |
349| 2 | Steven | Frecky | goldfish@fishhere.net | 1974-04-04 | 012-
3506745441 | Nigeria | 2 |
351+------------+-----------+----------+------------------------+------------+-----
352--------+---------+----------+
3532 rows in set (0.00 sec)
354
355
356mysql> select * from employees limit 2,2;
357+------------+-----------+----------+-----------------------+------------+------
358-------+---------+----------+
359| employeeid | firstname | lastname | email | dob | phone
360 | country | children |
361+------------+-----------+----------+-----------------------+------------+------
362-------+---------+----------+
363| 3 | Agang | Maroba | am@heroindomain.org | 1988-04-02 | 013-2
364079370 | Brunei | 3 |
365| 4 | Elsie | Frecky | elsie@superdiva.co.uk | 1980-10-20 | 019-3
366305184 | Brunei | 4 |
367+------------+-----------+----------+-----------------------+------------+------
368-------+---------+----------+
3692 rows in set (0.00 sec)
370
371mysql> select * from employees limit 2,3;
372+------------+-----------+----------+-----------------------+------------+------
373-------+---------+----------+
374| employeeid | firstname | lastname | email | dob | phone
375 | country | children |
376+------------+-----------+----------+-----------------------+------------+------
377-------+---------+----------+
378| 3 | Agang | Maroba | am@heroindomain.org | 1988-04-02 | 013-2
379079370 | Brunei | 3 |
380| 4 | Elsie | Frecky | elsie@superdiva.co.uk | 1980-10-20 | 019-3
381305184 | Brunei | 4 |
382+------------+-----------+----------+-----------------------+------------+------
383-------+---------+----------+
3842 rows in set (0.00 sec)
385
386
387
388
389mysql> select firstname from employees where firstname like '%n';
390+-----------+
391| firstname |
392+-----------+
393| John |
394| Steven |
395+-----------+
3962 rows in set (0.02 sec)
397
398mysql> select firstname from employees where firstname like '%ev';
399Empty set (0.00 sec)
400
401mysql> select firstname from employees where firstname like '%ev%';
402+-----------+
403| firstname |
404+-----------+
405| Steven |
406+-----------+
4071 row in set (0.00 sec)
408
409mysql> select firstname from employees where firstname like '%n%';
410+-----------+
411| firstname |
412+-----------+
413| John |
414| Steven |
415| Agang |
416+-----------+
4173 rows in set (0.00 sec)
418
419mysql> select firstname from employees where firstname like '%a%';
420+-----------+
421| firstname |
422+-----------+
423| Agang |
424+-----------+
4251 row in set (0.00 sec)
426
427mysql> select firstname from employees where firstname like 'a%';
428+-----------+
429| firstname |
430+-----------+
431| Agang |
432+-----------+
4331 row in set (0.00 sec)
434
435mysql> select firstname from employees where firstname like 'e%';
436+-----------+
437| firstname |
438+-----------+
439| Elsie |
440+-----------+
4411 row in set (0.00 sec)
442
443
444mysql> select firstname from employees where country= "kazakhstan" or country="m
445alaysia" country= "brunei"';
446 '> ;
447 '> '
448 -> ;
449ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
450corresponds to your MySQL server version for the right syntax to use near 'count
451ry= "brunei"';
452;
453'' at line 1
454mysql> select firstname from employees where country= "kazakhstan" or country="m
455alaysia" country= "brunei";
456ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
457corresponds to your MySQL server version for the right syntax to use near 'count
458ry= "brunei"' at line 1
459mysql> select firstname from employees where country= "kazakhstan" or country="m
460alaysia" or country= "brunei";
461+-----------+
462| firstname |
463+-----------+
464| John |
465| Agang |
466| Elsie |
467+-----------+
4683 rows in set (0.00 sec)
469
470mysql> select firstname from employees where country in ("kazakhstan" , "malaysi
471a" , "brunei");
472+-----------+
473| firstname |
474+-----------+
475| John |
476| Agang |
477| Elsie |
478+-----------+
4793 rows in set (0.00 sec)
480
481mysql> select firstname from employees where children >=2 and children <=4;
482+-----------+
483| firstname |
484+-----------+
485| Steven |
486| Agang |
487| Elsie |
488+-----------+
4893 rows in set (0.00 sec)
490
491mysql> select firstname from employees where children 2 between 4;
492ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
493corresponds to your MySQL server version for the right syntax to use near '2 bet
494ween 4' at line 1
495mysql> select firstname from employees where children between 2 and 4;
496+-----------+
497| firstname |
498+-----------+
499| Steven |
500| Agang |
501| Elsie |
502+-----------+
5033 rows in set (0.00 sec)
504
505
506mysql> select count (employeeid) from employees where country = "burnei";
507ERROR 1630 (42000): FUNCTION lab7db.count does not exist. Check the 'Function Na
508me Parsing and Resolution' section in the Reference Manual
509mysql> select count(employeeid) from employees where country = "burnei";
510+-------------------+
511| count(employeeid) |
512+-------------------+
513| 0 |
514+-------------------+
5151 row in set (0.02 sec)
516
517
518mysql> select count(employeeid) from employees where country = "brunei";
519+-------------------+
520| count(employeeid) |
521+-------------------+
522| 3 |
523+-------------------+
5241 row in set (0.00 sec)
525
526
527mysql> select count(employeeid) as total_of_staff from where country = "brunei";
528
529ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
530corresponds to your MySQL server version for the right syntax to use near 'where
531 country = "brunei"' at line 1
532mysql> select count(employeeid) as total_of_staff from employees where country =
533 "brunei";
534+----------------+
535| total_of_staff |
536+----------------+
537| 3 |
538+----------------+
5391 row in set (0.00 sec)
540
541
542mysql> select max(children) from employees;
543+---------------+
544| max(children) |
545+---------------+
546| 5 |
547+---------------+
5481 row in set (0.00 sec)
549
550
551mysql> select min(children) from employees;
552+---------------+
553| min(children) |
554+---------------+
555| 2 |
556+---------------+
5571 row in set (0.00 sec)
558
559
560
561
562mysql> select firstname from employees where children =(;
563ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
564corresponds to your MySQL server version for the right syntax to use near '' at
565line 1
566mysql> select firstname from employees where children =(;
567ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
568corresponds to your MySQL server version for the right syntax to use near '' at
569line 1
570mysql> select firstname from employees where children =(;
571ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
572corresponds to your MySQL server version for the right syntax to use near '' at
573line 1
574
575
576mysql> select firstname from employees where children =(
577 -> select min(children) from employees);
578+-----------+
579| firstname |
580+-----------+
581| Steven |
582+-----------+
5831 row in set (0.00 sec)
584
585
586mysql> select sum(children) from employees;
587+---------------+
588| sum(children) |
589+---------------+
590| 14 |
591+---------------+
5921 row in set (0.02 sec)
593
594mysql> select avarge(children) from employees;
595ERROR 1305 (42000): FUNCTION lab7db.avarge does not exist
596
597
598mysql> select avg(children) from employees;
599+---------------+
600| avg(children) |
601+---------------+
602| 3.5000 |
603+---------------+
6041 row in set (0.00 sec)
605
606
607mysql> select country, count(employeeid) from employees group by country;
608+---------+-------------------+
609| country | count(employeeid) |
610+---------+-------------------+
611| Brunei | 3 |
612| Nigeria | 1 |
613+---------+-------------------+
6142 rows in set (0.00 sec)
615
616
617mysql> select country, count(employeeid) from employees group by having count(em
618ployeeid) >2;
619ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
620corresponds to your MySQL server version for the right syntax to use near 'havin
621g count(employeeid) >2' at line 1
622mysql> select country, count(employeeid) from employees group by country having
623count(employeeid) >2;
624+---------+-------------------+
625| country | count(employeeid) |
626+---------+-------------------+
627| Brunei | 3 |
628+---------+-------------------+
6291 row in set (0.00 sec)
630
631
632mysql> select country, count(employeeid) from employees group by country having
633 tot > 2;
634ERROR 1054 (42S22): Unknown column 'tot' in 'having clause'
635mysql> select country, count(employeeid) as tot from employees group by country
636having tot > 2;
637+---------+-----+
638| country | tot |
639+---------+-----+
640| Brunei | 3 |
641+---------+-----+
6421 row in set (0.00 sec)