· 8 years ago · Aug 27, 2018, 10:04 AM
1#HOW TO LOGIN TO MySQL Server using root account protected by Ericsson12# password
2mysql -h localhost -u root -p
3
4
5#Exercise #1
6/* (Comment is staring here
7
8 HOW TO CREATE A DATABASE
9
10 and is ending right here*/
11
12CREATE DATABASE IF NOT EXISTS MyDB;
13SHOW DATABASES;
14
15
16
17SYSTEM clear;
18#Exercise #2
19/*
20 Show me warnings
21*/
22CREATE DATABASE IF NOT EXISTS MyDB;
23SHOW WARNINGS;
24
25
26SYSTEM clear;
27#Exercise #3
28/*
29
30 HOW TO DELETE A DATABASE
31
32*/
33DROP DATABASE IF EXISTS MyDB;
34SHOW DATABASES;
35
36
37
38SYSTEM clear;
39#Exercise #4
40/*
41 More warnings
42*/
43DROP DATABASE IF EXISTS MyDB;
44SHOW WARNINGS;
45
46
47
48SYSTEM clear;
49#Exercise #5
50CREATE DATABASE IF NOT EXISTS MyTraining;
51SHOW DATABASES;
52
53
54SYSTEM clear;
55#Exercise #6
56/*
57 HOW TO CREATE USERS
58*/
59CREATE USER 'student'@'localhost' IDENTIFIED BY 'Ericsson&89';
60GRANT ALL ON MyTraining.* TO 'student'@'localhost';
61FLUSH PRIVILEGES;
62exit
63#TRY to connect to MySQL server using new created account student
64...
65
66
67SYSTEM clear;
68#Exercise #7
69/*
70
71 HOW TO SELECT A DATABASE
72
73*/
74USE MyTraining;
75
76
77
78SYSTEM clear;
79#Exercise #8
80/*
81
82 HOW TO DISCOVER WHAT IS THE CuRRENT DATABASE
83
84*/
85SELECT database();
86
87
88
89SYSTEM clear;
90#Exercise #9
91/*
92
93 HOW TO DISCOVER WHAT IS THE CURRENT USER
94
95*/
96SHOW GRANTS;
97
98
99
100SYSTEM clear;
101#Exercise #10
102/*
103
104 HOW TO CREATE A TABLE WITH ONE COLUMN
105*/
106CREATE TABLE MyFirstTable (id smallint);
107
108
109
110SYSTEM clear;
111#Exercise #11
112/*
113
114 HOW TO DISCOVER ALL TABLES INSIDE A DATABASE
115*/
116SHOW TABLES;
117
118
119
120SYSTEM clear;
121#Exercise #12
122/*
123
124 HOW TO VIEW THE TABLE STRUCTURE
125*/
126DESCRIBE MyFirstTable;
127
128
129
130SYSTEM clear;
131#Exercise #13
132/*
133
134 HOW TO DELETE AN ENTIRE TABLE
135*/
136DROP TABLE MyFirstTable;
137SHOW TABLES;
138
139
140
141SYSTEM clear;
142#Exercise #14
143/*
144
145 HOW TO RENAME AN EXISTING TABLE
146*/
147CREATE TABLE MySecondTable (id smallint);
148SHOW TABLES;
149RENAME TABLE MySecondTable TO PeopleTable;
150SHOW TABLES;
151
152
153
154SYSTEM clear;
155#Exercise #15
156/*
157
158 HOW TO ADD ANOTHER COLUMN TO AN EXISTING TABLE
159*/
160ALTER TABLE PeopleTable ADD COLUMN FirstName varchar(50);
161DESCRIBE PeopleTable;
162
163
164
165SYSTEM clear;
166#Exercise #16
167/*
168
169 HOW TO ADD A COLUMN TO AN EXISTING TABLE IN A GIVEN POSITION
170*/
171ALTER TABLE PeopleTable ADD COLUMN LastName varchar(50) after id;
172DESCRIBE PeopleTable;
173
174
175SYSTEM clear;
176#Exercise #17
177/*
178
179 HOW TO MODIFY A COLUMN NAME
180*/
181ALTER TABLE PeopleTable CHANGE COLUMN id PeopleID smallint;
182DESCRIBE PeopleTable;
183
184
185SYSTEM clear;
186#Exercise #18
187/*
188
189 HOW TO DELETE A COLUMN
190*/
191ALTER TABLE PeopleTable ADD COLUMN MiddleName varchar(50);
192DESCRIBE PeopleTable;
193ALTER TABLE PeopleTable DROP COLUMN MiddleName;
194DESCRIBE PeopleTable;
195
196
197SYSTEM clear;
198#Exercise #19
199/*
200
201 HOW TO ADD NOT NULL CONSTRAINT TO A COLUMN, DEFAULT VALUES AND MODIFY/ADD MULTIPLE COLUMNS AT ONCE
202*/
203ALTER TABLE PeopleTable MODIFY PeopleID smallint NOT NULL,
204MODIFY FirstName varchar(50) NOT NULL,
205MODIFY LastName varchar(50) NOT NULL,
206ADD COLUMN BirthDay DATE DEFAULT '1901-01-01',
207ADD COLUMN BirthPlace varchar(50);
208DESCRIBE PeopleTable;
209
210
211
212SYSTEM clear;
213#Exercise #20
214/*
215
216 HOW TO ADD A PRIMARY KEY CONSTRAINT AND UNSIGNED/AUTO_INCREMENT OPTIONS TO A COLUMN
217*/
218ALTER TABLE PeopleTable MODIFY PeopleID int unsigned auto_increment,
219ADD CONSTRAINT PRIMARY KEY (PeopleID);
220DESCRIBE PeopleTable;
221
222
223
224SYSTEM clear;
225#Exercise #21
226/*
227
228 HOW TO DELETE A PRIMARY KEY CONSTRAINT AND ADD A COMPOSITE PRIMARY KEY
229*/
230ALTER TABLE PeopleTable DROP PRIMARY KEY,
231ADD CONSTRAINT PRIMARY KEY (PeopleID,LastName,FirstName);
232DESCRIBE PeopleTable;
233
234
235SYSTEM clear;
236#Exercise #22
237/*
238
239 HOW TO RETRIEVE ALL THE VALUES FROM A TABLE
240*/
241SELECT * FROM PeopleTable;
242
243
244
245SYSTEM clear;
246#Exercise #23
247/*
248
249 HOW TO INSERT VALUES TO A SINGLE ROW INTO A TABLE
250*/
251INSERT INTO PeopleTable (LastName,FirstName,Birthday,BirthPlace) values
252('Moise','Marius','1976-07-06','Adjud');
253SELECT * FROM PeopleTable;
254
255
256
257SYSTEM clear;
258#Exercise #24
259/*
260
261 HOW TO INSERT VALUES TO MULTIPLE ROWS INTO A TABLE
262*/
263INSERT INTO PeopleTable (LastName,FirstName,Birthday,BirthPlace) values
264('McEnroe','John Patrick','1959-02-16','Wiesbaden'),
265('Young','Angus McKinnon','1955-03-31','Glasgow'),
266('Moise','Marius',DEFAULT,DEFAULT);
267SELECT * FROM PeopleTable;
268
269
270
271SYSTEM clear;
272#Exercise #25
273/*
274
275 HOW TO FILTER THE DATA USING CONDITIONS AND EXPRESSIONS
276*/
277SELECT * FROM PeopleTable
278WHERE LastName='Moise';
279
280SELECT * FROM PeopleTable
281WHERE PeopleID=1 OR BirthPlace='Glasgow';
282
283SELECT FirstName, LastName, BirthPlace FROM PeopleTable
284WHERE LastName LIKE '%ois%' AND month(BirthDay) = 7;
285
286
287SYSTEM clear;
288#Exercise #26
289/*
290
291 AND/OR PRECEDENCE
292*/
293SELECT FirstName, LastName, PeopleID FROM PeopleTable
294WHERE PeopleID>1 OR year(BirthDay)<=1970 AND FirstName LIKE '%hn%';
295
296
297SELECT FirstName, LastName, PeopleID FROM PeopleTable
298WHERE (PeopleID>1 OR year(BirthDay)<=1970) AND FirstName LIKE '%hn%';
299
300
301SELECT FirstName, LastName, PeopleID FROM PeopleTable
302WHERE PeopleID>1 OR (year(BirthDay)<=1970 AND FirstName LIKE '%hn%');
303
304
305
306SYSTEM clear;
307#Exercise #27
308/*
309
310 HOW TO LIMIT THE OUTPUT
311*/
312SELECT * FROM PeopleTable
313LIMIT 2,2;
314
315SELECT * FROM PeopleTable
316LIMIT 3;
317
318
319
320SYSTEM clear;
321#Exercise #28
322/*
323
324 HOW TO SORT
325*/
326SELECT * FROM PeopleTable
327ORDER BY LastName ASC;
328
329SELECT * FROM PeopleTable
330ORDER BY year(BirthDay) DESC;
331
332INSERT INTO PeopleTable (LastName,FirstName,BirthDay,BirthPlace) values
333('Jobs','Steve','1955-02-24','San Francisco');
334
335SELECT * FROM PeopleTable
336ORDER BY year(BirthDay) DESC, LastName ASC;
337
338
339
340SYSTEM clear;
341#Exercise #29
342/*
343
344 HOW TO ELIMINATE DUPLICATES
345*/
346SELECT DISTINCT LastName from PeopleTable;
347
348
349
350SYSTEM clear;
351#Exercise #30
352/*
353
354 HOW TO COMBINE MULTIPLE SELECT QUERIES WITH UNION
355*/
356SELECT LastName FROM PeopleTable WHERE LastName LIKE '%ois%';
357
358SELECT FirstName FROM PeopleTable WHERE year(BirthDay) <= 1976;
359
360SELECT LastName FROM PeopleTable WHERE LastName LIKE '%ois%' UNION ALL
361SELECT FirstName FROM PeopleTable WHERE year(BirthDay) <= 1976;
362
363SELECT LastName FROM PeopleTable WHERE LastName LIKE '%ois%' UNION
364SELECT FirstName FROM PeopleTable WHERE year(BirthDay) <= 1976;
365
366
367
368SYSTEM clear;
369#Exercise #31
370/*
371
372 HOW TO COMBINE MULTIPLE SELECT QUERIES WITH IN (SUBQUERY)
373*/
374SELECT PeopleID, LastName FROM PeopleTable
375WHERE BirthDay IN
376(SELECT BirthDay FROM PeopleTable WHERE BirthPlace IS NOT NULL);
377
378
379SYSTEM clear;
380#Exercise #32
381/*
382
383 HOW TO USE BETWEEN
384*/
385SELECT PeopleID, LastName, FirstName FROM PeopleTable
386WHERE year(BirthDay) BETWEEN 1930 AND 1955;
387
388SELECT PeopleID, LastName, FirstName FROM PeopleTable
389WHERE year(BirthDay) NOT BETWEEN 1930 AND 1955;
390
391
392SYSTEM clear;
393#Exercise #33
394/*
395
396 HOW TO USE AGGREGATION FUNCTIONS (MAX,MIN,SUM,AVG,COUNT)
397*/
398SELECT count(LastName) FROM PeopleTable
399WHERE LastName='Moise';
400
401SELECT count(PeopleID) FROM PeopleTable
402WHERE year(BirthDay)<1970 and year(BirthDay)!=1901;
403
404SELECT LastName, count(LastName) FROM PeopleTable
405GROUP BY LastName;
406
407SELECT count(LastName), year(BirthDay) FROM PeopleTable
408GROUP BY year(BirthDay);
409
410SELECT MIN(BirthDay) FROM PeopleTable;
411SELECT LastName FROM PeopleTable WHERE BirthDay = (SELECT MIN(BirthDay) FROM PeopleTable WHERE BirthDay != '1901-01-01');
412
413SELECT MAX(BirthDay) FROM PeopleTable;
414SELECT LastName FROM PeopleTable WHERE BirthDay = (SELECT MAX(BirthDay) FROM PeopleTable);
415
416SELECT AVG(year(BirthDay)) FROM PeopleTable;
417
418
419
420SYSTEM clear;
421#Exercise #34
422/*
423
424 HOW TO ADD A FOREIGN KEY
425*/
426
427ALTER TABLE PeopleTable ADD FOREIGN KEY (CityID) REFERENCES CityTable (CityID) ON UPDATE CASCADE ON DELETE SET NULL;
428
429DESCRIBE PeopleTable;
430
431SELECT * FROM PeopleTable WHERE CityID=1;
432UPDATE CityTable SET CityID=11 WHERE CityName='Adjud';
433SELECT * FROM PeopleTable WHERE CityID=1 OR CityID=11;
434
435SELECT * FROM PeopleTable WHERE CityID=2;
436DELETE FROM CityTable WHERE CityName='Wiesbaden';
437SELECT * FROM PeopleTable WHERE PeopleID=2;
438
439
440
441SYSTEM clear;
442#Exercise #35
443/*
444
445 INNER JOIN
446*/
447UPDATE CityTable SET CityName='Valencia' WHERE CityID=4;
448
449SELECT PeopleTable.LastName, PeopleTable.FirstName, CityTable.CityName FROM
450PeopleTable INNER JOIN CityTable
451ON PeopleTable.CityID=CityTable.CityID;
452
453
454
455SYSTEM clear;
456#Exercise #36
457/*
458
459 CROSS JOIN
460*/
461SELECT PeopleTable.LastName, PeopleTable.FirstName, CityTable.CityName FROM PeopleTable CROSS JOIN CityTable;
462
463
464
465SYSTEM clear;
466#Exercise #37
467/*
468
469 LEFT JOIN
470*/
471SELECT PeopleTable.LastName, PeopleTable.FirstName, CityTable.CityName
472FROM PeopleTable LEFT JOIN CityTable
473USING(CityID);
474
475
476
477SYSTEM clear;
478#Exercise #38
479/*
480
481 LEFT OUTER JOIN - TBD
482*/
483SELECT PeopleTable.LastName, PeopleTable.FirstName, CityTable.CityName
484FROM PeopleTable LEFT JOIN CityTable
485USING(CityID)
486...
487;
488
489
490
491SYSTEM clear;
492#Exercise #39
493/*
494
495 RIGHT JOIN
496*/
497SELECT PeopleTable.LastName, PeopleTable.FirstName, CityTable.CityName
498FROM PeopleTable RIGHT JOIN CityTable
499USING(CityID);
500
501
502
503SYSTEM clear;
504#Exercise #40
505/*
506
507 RIGHT OUTER JOIN - TBD
508*/
509SELECT PeopleTable.LastName, PeopleTable.FirstName, CityTable.CityName
510FROM PeopleTable RIGHT JOIN CityTable
511USING(CityID)
512...
513;
514
515
516SYSTEM clear;
517#Exercise #41
518/*
519
520 FULL OUTER JOIN - TBD
521*/
522
523
524SYSTEM clear;
525#Exercise #42
526/*
527 HOW TO EXPORT DATA FROM A TABLE TO A CSV FILE
528*/
529SELECT * from CityTable
530INTO OUTFILE '/var/lib/mysql-files/CityTable.csv'
531FIELDS ENCLOSED BY '"'
532TERMINATED BY ','
533LINES TERMINATED BY '\r\n';
534
535
536
537SYSTEM clear;
538#Exercise #43
539/*
540 HOW TO IMPORT DATA TO A TABLE FROM A CSV FILE
541*/
542LOAD DATA INFILE '/var/lib/mysql-files/CityTable.csv'
543INTO TABLE CityTable
544FIELDS ENCLOSED BY '"'
545TERMINATED BY ','
546LINES TERMINATED BY '\r\n';
547
548
549
550
551
552
553//////////////////////////////////////////////////////////////////////
554
555SYSTEM clear;
556#Exercise #1
557/*
558
559 HOW TO USE ARITHMETIC OPERATORS
560*/
561SELECT 10+2, 2-10, 2*10, - 2, 2/10, 2 DIV 10, 2 MOD 10;
562
563
564
565SYSTEM clear;
566#Exercise #2
567/*
568
569 HOW TO USE MATH FUNCTIONS
570*/
571SELECT CONV(25,10,2), CONV(25,10,16), CONV(19,16,2);
572
573SELECT PI(), FLOOR(PI()), CEIL(PI()), ROUND(PI()), ROUND(PI(),2);
574
575SELECT SQRT(7), FLOOR(SQRT(7)), CEIL(SQRT(7)), ROUND(SQRT(7)), ROUND(SQRT(7),3);
576
577SELECT RAND(), RAND(), RAND(), RAND();
578
579SELECT POW(2,2), POW(-2,2), POW(2,-2);
580
581SELECT LOG(2,16), LOG2(16);
582
583SELECT LOG(10,1000000), LOG10(1000000);
584
585
586
587SYSTEM clear;
588#Exercise #3
589/*
590
591 HOW TO USE STRING FUNCTIONS
592*/
593SELECT UPPER(LastName), LOWER(FirstName),
594CONCAT(LastName,FirstName),
595CONCAT_WS(',',LastName,FirstName)
596FROM PeopleTable;
597
598SELECT * FROM PeopleTable WHERE SOUNDEX(LastName) = SOUNDEX('Moises');
599
600SELECT * FROM PeopleTable WHERE SOUNDEX(FirstName) = SOUNDEX('Stivi');
601
602
603SYSTEM clear;
604#Exercise #4
605/*
606
607 HOW TO CREATE A TABLE AND INSERT VALUES FROM AN EXISTING TABLE
608*/
609CREATE TABLE CityTable AS
610(SELECT BirthPlace AS CityName FROM PeopleTable);
611
612SELECT * FROM CityTable;
613
614
615SYSTEM clear;
616#Exercise #5
617/*
618
619 HOW TO UPDATE VALUES FROM AN EXISTING TABLE (ANOTHER SUBQUERY)
620*/
621ALTER TABLE PeopleTable ADD COLUMN CityID mediumint unsigned;
622UPDATE PeopleTable SET CityID = ( SELECT CityID FROM CityTable WHERE CityName = 'Adjud') WHERE BirthPlace = 'Adjud';
623
624SELECT * FROM PeopleTable;
625
626
627
628SYSTEM clear;
629#Exercise #6
630/*
631
632 HOW TO UPDATE VALUES FROM AN EXISTING TABLE (INNER JOIN)
633*/
634UPDATE PeopleTable INNER JOIN CityTable ON PeopleTable.BirthPlace = CityTable.CityName SET PeopleTable.CityID = CityTable.CityID;
635
636SELECT * FROM PeopleTable;
637
638
639
640http://www.mysqltutorial.org/
641
642/////////////////////////////////////////////////////////////////////////////
643
644-- MySQL dump 10.13 Distrib 8.0.11, for Linux (x86_64)
645--
646-- Host: localhost Database: MyTraining
647-- ------------------------------------------------------
648-- Server version 8.0.11
649
650/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
651/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
652/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
653 SET NAMES utf8mb4 ;
654/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
655/*!40103 SET TIME_ZONE='+00:00' */;
656/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
657/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
658/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
659/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
660
661--
662-- Current Database: `MyTraining`
663--
664
665CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyTraining` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;
666
667USE `MyTraining`;
668
669--
670-- Table structure for table `PeopleTable`
671--
672
673DROP TABLE IF EXISTS `PeopleTable`;
674/*!40101 SET @saved_cs_client = @@character_set_client */;
675 SET character_set_client = utf8mb4 ;
676CREATE TABLE `PeopleTable` (
677 `PeopleID` int(10) unsigned NOT NULL AUTO_INCREMENT,
678 `LastName` varchar(50) NOT NULL,
679 `FirstName` varchar(50) NOT NULL,
680 `BirthDay` date DEFAULT '1901-01-01',
681 `BirthPlace` varchar(50) DEFAULT NULL,
682 PRIMARY KEY (`PeopleID`,`LastName`,`FirstName`)
683) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
684/*!40101 SET character_set_client = @saved_cs_client */;
685
686--
687-- Dumping data for table `PeopleTable`
688--
689
690LOCK TABLES `PeopleTable` WRITE;
691/*!40000 ALTER TABLE `PeopleTable` DISABLE KEYS */;
692INSERT INTO `PeopleTable` VALUES (1,'Moise','Marius','1976-07-06','Adjud'),(2,'Moise','Marius','1901-01-01',NULL),(3,'Jobs','Steve','1955-02-24','San Francisco'),(100,'McEnroe','John Patrick','1959-02-16','Wiesbaden'),(101,'Young','Angus McKinnon','1955-03-31','Glasgow');
693/*!40000 ALTER TABLE `PeopleTable` ENABLE KEYS */;
694UNLOCK TABLES;
695/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
696
697/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
698/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
699/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
700/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
701/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
702/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
703/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
704
705-- Dump completed on 2018-05-22 3:41:11