· 10 years ago · Apr 30, 2016, 05:06 PM
11. Login to MySQL
2002
3003 a. mysql5 -u mysqladmin -p
4004
5005 2. quit
6006
7007 a. Quit MySQL
8008
9009 3. show databases;
10010
11011 a. Display all databases
12012
13013 4. CREATE DATABASE test2;
14014
15015 a. Create a database
16016
17017 5. USE test2;
18018
19019 a. Make test2 the active database
20020
21021 6. SELECT DATABASE();
22022
23023 a. Show the currently selected database
24024
25025 7. DROP DATABASE IF EXISTS test2;
26026
27027 a. Delete the named database
28028
29029 b. Slide about building tables (2)
30030
31031 8. CREATE TABLE student(
32032 first_name VARCHAR(30) NOT NULL,
33033 last_name VARCHAR(30) NOT NULL,
34034 email VARCHAR(60) NULL,
35035 street VARCHAR(50) NOT NULL,
36036 city VARCHAR(40) NOT NULL,
37037 state CHAR(2) NOT NULL DEFAULT "PA",
38038 zip MEDIUMINT UNSIGNED NOT NULL,
39039 phone VARCHAR(20) NOT NULL,
40040 birth_date DATE NOT NULL,
41041 sex ENUM('M', 'F') NOT NULL,
42042 date_entered TIMESTAMP,
43043 lunch_cost FLOAT NULL,
44044 student_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
45045 );
46046
47047 a. VARCHAR(30) : Characters with an expected max length of 30
48048
49049 b. NOT NULL : Must contain a value
50050
51051 c. NULL : Doesn't require a value
52052
53053 d. CHAR(2) : Contains exactly 2 characters
54054
55055 e. DEFAULT "PA" : Receives a default value of PA
56056
57057 f. MEDIUMINT : Value no greater then 8,388,608
58058
59059 g. UNSIGNED : Can't contain a negative value
60060
61061 h. DATE : Stores a date in the format YYYY-MM-DD
62062
63063 i. ENUM('M', 'F') : Can contain either a M or F
64064
65065 j. TIMESTAMP : Stores date and time in this format YYYY-MM-DD-HH-MM-SS
66066
67067 k. FLOAT: A number with decimal spaces, with a value no bigger than 1.1E38 or smaller than -1.1E38
68068
69069 l. INT : Contains a number without decimals
70070
71071 m. AUTO_INCREMENT : Generates a number automatically that is one greater then the previous row
72072
73073 n. PRIMARY KEY (SLIDE): Unique ID that is assigned to this row of data
74074
75075 I. Uniquely identifies a row or record
76076
77077 II. Each Primary Key must be unique to the row
78078
79079 III. Must be given a value when the row is created and that value can�t be NULL
80080
81081 IV. The original value can�t be changed It should be short
82082
83083 V. It�s probably best to auto increment the value of the key
84084
85085 o. Atomic Data & Table Templating
86086
87087 As your database increases in size, you are going to want everything to be organized, so that it can perform your queries quickly. If your tables are set up properly, your database will be able to crank through hundreds of thousands of bits of data in seconds.
88088
89089 How do you know how to best set up your tables though? Just follow some simple rules:
90090
91091 Every table should focus on describing just one thing. Ex. Customer Table would have name, age, location, contact information. It shouldn�t contain lists of anything such as interests, job history, past address, products purchased, etc.
92092 After you decide what one thing your table will describe, then decide what things you need to describe that thing. Refer to the customer example given in the last step.
93093
94094 Write out all the ways to describe the thing and if any of those things requires multiple inputs, pull them out and create a new table for them. For example, a list of past employers.
95095
96096 Once your table values have been broken down, we refer to these values as being atomic. Be careful not to break them down to a point in which the data is harder to work with. It might make sense to create a different variable for the house number, street name, apartment number, etc.; but by doing so you may make your self more work? That decision is up to you?
97097
98098 p. Some additional rules to help you make your data atomic: Don�t have multiple columns with the same sort of information. Ex. If you wanted to include a employment history you should create job1, job2, job3 columns. Make a new table with that data instead.
99099
100100 Don�t include multiple values in one cell. Ex. You shouldn�t create a cell named jobs and then give it the value: McDonalds, Radio Shack, Walmart,� Normalized Tables
101101
102102 q. What does normalized mean?
103103
104104 Normalized just means that the database is organized in a way that is considered standardized by professional SQL programmers. So if someone new needs to work with the tables they�ll be able to understand how to easily.
105105
106106 Another benefit to normalizing your tables is that your queries will run much quicker and the chance your database will be corrupted will go down.
107107
108108 r. What are the rules for creating normalized tables:
109109
110110 The tables and variables defined in them must be atomic Each row must have a Primary Key defined. Like your social security number identifies you, the Primary Key will identify your row.
111111
112112 You also want to eliminate using the same values repeatedly in your columns. Ex. You wouldn�t want a column named instructors, in which you hand typed in their names each time. You instead, should create an instructor table and link to it�s key.
113113
114114 Every variable in a table should directly relate to the primary key. Ex. You should create tables for all of your customers potential states, cities and zip codes, instead of including them in the main customer table. Then you would link them using foreign keys. Note: Many people think this last rule is overkill and can be ignored!
115115
116116 No two columns should have a relationship in which when one changes another must also change in the same table. This is called a Dependency. Note: This is another rule that is sometimes ignored.
117117
118118 ------------ Numeric Types ------------
119119
120120 TINYINT: A number with a value no bigger than 127 or smaller than -128
121121 SMALLINT: A number with a value no bigger than 32,768 or smaller than -32,767
122122 MEDIUM INT: A number with a value no bigger than 8,388,608 or smaller than -8,388,608
123123 INT: A number with a value no bigger than 2^31 or smaller than 2^31 � 1
124124 BIGINT: A number with a value no bigger than 2^63 or smaller than 2^63 � 1
125125 FLOAT: A number with decimal spaces, with a value no bigger than 1.1E38 or smaller than -1.1E38
126126 DOUBLE: A number with decimal spaces, with a value no bigger than 1.7E308 or smaller than -1.7E308
127127
128128 ------------ String Types ------------
129129
130130 CHAR: A character string with a fixed length
131131 VARCHAR: A character string with a length that�s variable
132132 BLOB: Can contain 2^16 bytes of data
133133 ENUM: A character string that has a limited number of total values, which you must define.
134134 SET: A list of legal possible character strings. Unlike ENUM, a SET can contain multiple values in comparison to the one legal value with ENUM.
135135
136136 ------------ Date & Time Types ------------
137137
138138 DATE: A date value with the format of (YYYY-MM-DD)
139139 TIME: A time value with the format of (HH:MM:SS)
140140 DATETIME: A time value with the format of (YYYY-MM-DD HH:MM:SS)
141141 TIMESTAMP: A time value with the format of (YYYYMMDDHHMMSS)
142142 YEAR: A year value with the format of (YYYY)
143143
144144 9. DESCRIBE student;
145145
146146 a. Show the table set up
147147
148148 10. INSERT INTO student VALUES('Dale', 'Cooper', 'dcooper@aol.com',
149149 '123 Main St', 'Yakima', 'WA', 98901, '792-223-8901', "1959-2-22",
150150 'M', NOW(), 3.50, NULL);
151151
152152 a. Inserting Data into a Table
153153
154154 b. INSERT INTO student VALUES('Harry', 'Truman', 'htruman@aol.com',
155155 '202 South St', 'Vancouver', 'WA', 98660, '792-223-9810', "1946-1-24",
156156 'M', NOW(), 3.50, NULL);
157157
158158 INSERT INTO student VALUES('Shelly', 'Johnson', 'sjohnson@aol.com',
159159 '9 Pond Rd', 'Sparks', 'NV', 89431, '792-223-6734', "1970-12-12",
160160 'F', NOW(), 3.50, NULL);
161161
162162 INSERT INTO student VALUES('Bobby', 'Briggs', 'bbriggs@aol.com',
163163 '14 12th St', 'San Diego', 'CA', 92101, '792-223-6178', "1967-5-24",
164164 'M', NOW(), 3.50, NULL);
165165
166166 INSERT INTO student VALUES('Donna', 'Hayward', 'dhayward@aol.com',
167167 '120 16th St', 'Davenport', 'IA', 52801, '792-223-2001', "1970-3-24",
168168 'F', NOW(), 3.50, NULL);
169169
170170 INSERT INTO student VALUES('Audrey', 'Horne', 'ahorne@aol.com',
171171 '342 19th St', 'Detroit', 'MI', 48222, '792-223-2001', "1965-2-1",
172172 'F', NOW(), 3.50, NULL);
173173
174174 INSERT INTO student VALUES('James', 'Hurley', 'jhurley@aol.com',
175175 '2578 Cliff St', 'Queens', 'NY', 11427, '792-223-1890', "1967-1-2",
176176 'M', NOW(), 3.50, NULL);
177177
178178 INSERT INTO student VALUES('Lucy', 'Moran', 'lmoran@aol.com',
179179 '178 Dover St', 'Hollywood', 'CA', 90078, '792-223-9678', "1954-11-27",
180180 'F', NOW(), 3.50, NULL);
181181
182182 INSERT INTO student VALUES('Tommy', 'Hill', 'thill@aol.com',
183183 '672 High Plains', 'Tucson', 'AZ', 85701, '792-223-1115', "1951-12-21",
184184 'M', NOW(), 3.50, NULL);
185185
186186 INSERT INTO student VALUES('Andy', 'Brennan', 'abrennan@aol.com',
187187 '281 4th St', 'Jacksonville', 'NC', 28540, '792-223-8902', "1960-12-27",
188188 'M', NOW(), 3.50, NULL);
189189
190190 11. SELECT * FROM student;
191191
192192 a. Shows all the student data
193193
194194 12. CREATE TABLE class(
195195 name VARCHAR(30) NOT NULL,
196196 class_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
197197
198198 a. Create a separate table for all classes
199199
200200 13. show tables;
201201
202202 a. Show all the tables
203203
204204 14. INSERT INTO class VALUES
205205 ('English', NULL), ('Speech', NULL), ('Literature', NULL),
206206 ('Algebra', NULL), ('Geometry', NULL), ('Trigonometry', NULL),
207207 ('Calculus', NULL), ('Earth Science', NULL), ('Biology', NULL),
208208 ('Chemistry', NULL), ('Physics', NULL), ('History', NULL),
209209 ('Art', NULL), ('Gym', NULL);
210210
211211 a. Insert all possible classes
212212
213213 b. select * from class;
214214
215215 15. CREATE TABLE test(
216216 date DATE NOT NULL,
217217 type ENUM('T', 'Q') NOT NULL,
218218 class_id INT UNSIGNED NOT NULL,
219219 test_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
220220
221221 a. class_id is a foreign key
222222
223223 I. Used to make references to the Primary Key of another table
224224
225225 II. Example: If we have a customer and city table. If the city table had a column which listed the unique primary key of all the customers, that Primary Key listing in the city table would be considered a Foreign Key.
226226
227227 III. The Foreign Key can have a different name from the Primary Key name.
228228
229229 IV. The value of a Foreign Key can have the value of NULL.
230230
231231 V. A Foreign Key doesn�t have to be unique
232232
233233 16. CREATE TABLE score(
234234 student_id INT UNSIGNED NOT NULL,
235235 event_id INT UNSIGNED NOT NULL,
236236 score INT NOT NULL,
237237 PRIMARY KEY(event_id, student_id));
238238
239239 a. We combined the event and student id to make sure we don't have
240240 duplicate scores and it makes it easier to change scores
241241
242242 b. Since neither the event or the student ids are unique on their
243243 own we are able to make them unique by combining them
244244
245245 17. CREATE TABLE absence(
246246 student_id INT UNSIGNED NOT NULL,
247247 date DATE NOT NULL,
248248 PRIMARY KEY(student_id, date));
249249
250250 a. Again we combine 2 items that aren't unique to generate a
251251 unique key
252252
253253 18. Add a max score column to test
254254
255255 a. ALTER TABLE test ADD maxscore INT NOT NULL AFTER type;
256256
257257 b. DESCRIBE test;
258258
259259 19. Insert Tests
260260
261261 a. INSERT INTO test VALUES
262262 ('2014-8-25', 'Q', 15, 1, NULL),
263263 ('2014-8-27', 'Q', 15, 1, NULL),
264264 ('2014-8-29', 'T', 30, 1, NULL),
265265 ('2014-8-29', 'T', 30, 2, NULL),
266266 ('2014-8-27', 'Q', 15, 4, NULL),
267267 ('2014-8-29', 'T', 30, 4, NULL);
268268
269269 b. select * FROM test;
270270
271271 20. ALTER TABLE score CHANGE event_id test_id
272272 INT UNSIGNED NOT NULL;
273273
274274 a. Change the name of event_id in score to test_id
275275
276276 b. DESCRIBE score;
277277
278278
279279 21. Enter student scores
280280
281281 a. INSERT INTO score VALUES
282282 (1, 1, 15),
283283 (1, 2, 14),
284284 (1, 3, 28),
285285 (1, 4, 29),
286286 (1, 5, 15),
287287 (1, 6, 27),
288288 (2, 1, 15),
289289 (2, 2, 14),
290290 (2, 3, 26),
291291 (2, 4, 28),
292292 (2, 5, 14),
293293 (2, 6, 26),
294294 (3, 1, 14),
295295 (3, 2, 14),
296296 (3, 3, 26),
297297 (3, 4, 26),
298298 (3, 5, 13),
299299 (3, 6, 26),
300300 (4, 1, 15),
301301 (4, 2, 14),
302302 (4, 3, 27),
303303 (4, 4, 27),
304304 (4, 5, 15),
305305 (4, 6, 27),
306306 (5, 1, 14),
307307 (5, 2, 13),
308308 (5, 3, 26),
309309 (5, 4, 27),
310310 (5, 5, 13),
311311 (5, 6, 27),
312312 (6, 1, 13),
313313 (6, 2, 13),
314314 # Missed this day (6, 3, 24),
315315 (6, 4, 26),
316316 (6, 5, 13),
317317 (6, 6, 26),
318318 (7, 1, 13),
319319 (7, 2, 13),
320320 (7, 3, 25),
321321 (7, 4, 27),
322322 (7, 5, 13),
323323 # Missed this day (7, 6, 27),
324324 (8, 1, 14),
325325 # Missed this day (8, 2, 13),
326326 (8, 3, 26),
327327 (8, 4, 23),
328328 (8, 5, 12),
329329 (8, 6, 24),
330330 (9, 1, 15),
331331 (9, 2, 13),
332332 (9, 3, 28),
333333 (9, 4, 27),
334334 (9, 5, 14),
335335 (9, 6, 27),
336336 (10, 1, 15),
337337 (10, 2, 13),
338338 (10, 3, 26),
339339 (10, 4, 27),
340340 (10, 5, 12),
341341 (10, 6, 22);
342342
343343 22. Fill in the absences
344344
345345 a. INSERT INTO absence VALUES
346346 (6, '2014-08-29'),
347347 (7, '2014-08-29'),
348348 (8, '2014-08-27');
349349
350350 23. SELECT * FROM student;
351351
352352 a. Shows everything in the student table
353353
354354 24. SELECT FIRST_NAME, last_name
355355 FROM student;
356356
357357 a. Show just selected data from the table (Not Case Sensitive)
358358
359359 25. RENAME TABLE
360360 absence to absences,
361361 class to classes,
362362 score to scores,
363363 student to students,
364364 test to tests;
365365
366366 a. Change all the table names SHOW TABLES;
367367
368368 26. SELECT first_name, last_name, state
369369 FROM students
370370 WHERE state="WA";
371371
372372 a. Show every student born in the state of Washington
373373
374374 27. SELECT first_name, last_name, birth_date
375375 FROM students
376376 WHERE YEAR(birth_date) >= 1965;
377377
378378 a. You can compare values with =, >, <, >=, <=, !=
379379
380380 b. To get the month, day or year of a date use MONTH(), DAY(), or YEAR()
381381
382382 27. SELECT first_name, last_name, birth_date
383383 FROM students
384384 WHERE MONTH(birth_date) = 2 OR state="CA";
385385
386386 a. AND, && : Returns a true value if both conditions are true
387387
388388 b. OR, || : Returns a true value if either condition is true
389389
390390 c. NOT, ! : Returns a true value if the operand is false
391391
392392 28. SELECT last_name, state, birth_date
393393 FROM students
394394 WHERE DAY(birth_date) >= 12 && (state="CA" || state="NV");
395395
396396 a. You can use compound logical operators
397397
398398 29. SELECT last_name
399399 FROM students
400400 WHERE last_name IS NULL;
401401
402402 SELECT last_name
403403 FROM students
404404 WHERE last_name IS NOT NULL;
405405
406406 a. If you want to check for NULL you must use IS NULL or IS NOT NULL
407407
408408 30. SELECT first_name, last_name
409409 FROM students
410410 ORDER BY last_name;
411411
412412 a. ORDER BY allows you to order results. To change the order use
413413 ORDER BY col_name DESC;
414414
415415 31. SELECT first_name, last_name, state
416416 FROM students
417417 ORDER BY state DESC, last_name ASC;
418418
419419 a. If you use 2 ORDER BYs it will order one and then the other
420420
421421 32. SELECT first_name, last_name
422422 FROM students
423423 LIMIT 5;
424424
425425 a. Use LIMIT to limit the number of results
426426
427427 33. SELECT first_name, last_name
428428 FROM students
429429 LIMIT 5, 10;
430430
431431 a. You can also get results 5 through 10
432432
433433 34. SELECT CONCAT(first_name, " ", last_name) AS 'Name',
434434 CONCAT(city, ", ", state) AS 'Hometown'
435435 FROM students;
436436
437437 a. CONCAT is used to combine results
438438
439439 b. AS provides for a way to define the column name
440440
441441 35. SELECT last_name, first_name
442442 FROM students
443443 WHERE first_name LIKE 'D%' OR last_name LIKE '%n';
444444
445445 a. Matchs any first name that starts with a D, or ends with a n
446446
447447 b. % matchs any sequence of characters
448448
449449 36. SELECT last_name, first_name
450450 FROM students
451451 WHERE first_name LIKE '___y';
452452
453453 a. _ matchs any single character
454454
455455 37. SELECT DISTINCT state
456456 FROM students
457457 ORDER BY state;
458458
459459 a. Returns the states from which students are born because DISTINCT
460460 eliminates duplicates in results
461461
462462 38. SELECT COUNT(DISTINCT state)
463463 FROM students;
464464
465465 a. COUNT returns the number of matchs, so we can get the number
466466 of DISTINCT states from which students were born
467467
468468 39. SELECT COUNT(*)
469469 FROM students;
470470
471471 SELECT COUNT(*)
472472 FROM students
473473 WHERE sex='M';
474474
475475 a. COUNT returns the total number of records as well as the total
476476 number of boys
477477
478478 40. SELECT sex, COUNT(*)
479479 FROM students
480480 GROUP BY sex;
481481
482482 a. GROUP BY defines how the results will be grouped
483483
484484 41. SELECT MONTH(birth_date) AS 'Month', COUNT(*)
485485 FROM students
486486 GROUP BY Month
487487 ORDER BY Month;
488488
489489 a. We can get each month in which we have a birthday and the total
490490 number for each month
491491
492492 42. SELECT state, COUNT(state) AS 'Amount'
493493 FROM students
494494 GROUP BY state
495495 HAVING Amount > 1;
496496
497497 a. HAVING allows you to narrow the results after the query is executed
498498
499499 43. SELECT
500500 test_id AS 'Test',
501501 MIN(score) AS min,
502502 MAX(score) AS max,
503503 MAX(score)-MIN(score) AS 'range',
504504 SUM(score) AS total,
505505 AVG(score) AS average
506506 FROM scores
507507 GROUP BY test_id;
508508
509509 a. There are many math functions built into MySQL. Range had to be quoted because it is a reserved word.
510510
511511 b. You can find all reserved words here http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html
512512
513513 44. The Built in Numeric Functions (SLIDE)
514514
515515 ABS(x) : Absolute Number: Returns the absolute value of the variable x.
516516
517517 ACOS(x), ASIN(x), ATAN(x), ATAN2(x,y), COS(x), COT(x), SIN(x), TAN(x) :Trigonometric Functions : They are used to relate the angles of a triangle to the lengths of the sides of a triangle.
518518
519519 AVG(column_name) : Average of Column : Returns the average of all values in a column. SELECT AVG(column_name) FROM table_name;
520520
521521 CEILING(x) : Returns the smallest number not less than x.
522522
523523 COUNT(column_name) : Count : Returns the number of non null values in the column. SELECT COUNT(column_name) FROM table_name;
524524
525525 DEGREES(x) : Returns the value of x, converted from radians to degrees.
526526
527527 EXP(x) : Returns e^x
528528
529529 FLOOR(x) : Returns the largest number not grater than x
530530
531531 LOG(x) : Returns the natural logarithm of x
532532
533533 LOG10(x) : Returns the logarithm of x to the base 10
534534
535535 MAX(column_name) : Maximum Value : Returns the maximum value in the column. SELECT MAX(column_name) FROM table_name;
536536
537537 MIN(column_name) : Minimum : Returns the minimum value in the column. SELECT MIN(column_name) FROM table_name;
538538
539539 MOD(x, y) : Modulus : Returns the remainder of a division between x and y
540540
541541 PI() : Returns the value of PI
542542
543543 POWER(x, y) : Returns x ^ Y
544544
545545 RADIANS(x) : Returns the value of x, converted from degrees to radians
546546
547547 RAND() : Random Number : Returns a random number between the values of 0.0 and 1.0
548548
549549 ROUND(x, d) : Returns the value of x, rounded to d decimal places
550550
551551 SQRT(x) : Square Root : Returns the square root of x
552552
553553 STD(column_name) : Standard Deviation : Returns the Standard Deviation of values in the column. SELECT STD(column_name) FROM table_name;
554554
555555 SUM(column_name) : Summation : Returns the sum of values in the column. SELECT SUM(column_name) FROM table_name;
556556
557557 TRUNCATE(x) : Returns the value of x, truncated to d decimal places
558558
559559 45. SELECT * FROM absences;
560560
561561 DESCRIBE scores;
562562
563563 SELECT student_id, test_id
564564 FROM scores
565565 WHERE student_id = 6;
566566
567567 INSERT INTO scores VALUES
568568 (6, 3, 24);
569569
570570 DELETE FROM absences
571571 WHERE student_id = 6;
572572
573573 a. Look up students that missed a test
574574
575575 b. Look up the specific test missed by student 6
576576
577577 c. Insert the make up test result
578578
579579 d. Delete the record in absences
580580
581581 46. ALTER TABLE absences
582582 ADD COLUMN test_taken CHAR(1) NOT NULL DEFAULT 'F'
583583 AFTER student_id;
584584
585585 a. Use ALTER to add a column to a table. You can use AFTER
586586 or BEFORE to define the placement
587587
588588 47. ALTER TABLE absences
589589 MODIFY COLUMN test_taken ENUM('T','F') NOT NULL DEFAULT 'F';
590590
591591 a. You can change the data type with ALTER and MODIFY COLUMN
592592
593593 48. ALTER TABLE absences
594594 DROP COLUMN test_taken;
595595
596596 a. ALTER and DROP COLUMN can delete a column
597597
598598 49. ALTER TABLE absences
599599 CHANGE student_id student_id INT UNSIGNED NOT NULL;
600600
601601 a. You can change the data type with ALTER and CHANGE
602602
603603 50. SELECT *
604604 FROM scores
605605 WHERE student_id = 4;
606606
607607 UPDATE scores SET score=25
608608 WHERE student_id=4 AND test_id=3;
609609
610610 a. Use UPDATE to change a value in a row
611611
612612 51. SELECT first_name, last_name, birth_date
613613 FROM students
614614 WHERE birth_date
615615 BETWEEN '1960-1-1' AND '1970-1-1';
616616
617617 a. Use BETWEEN to find matches between a minimum and maximum
618618
619619 52. SELECT first_name, last_name
620620 FROM students
621621 WHERE first_name IN ('Bobby', 'Lucy', 'Andy');
622622
623623 a. Use IN to narrow results based on a predefined list of options
624624
625625 53. SELECT student_id, date, score, maxscore
626626 FROM tests, scores
627627 WHERE date = '2014-08-25'
628628 AND tests.test_id = scores.test_id;
629629
630630 a. To combine data from multiple tables you can perform a JOIN
631631 by matching up common data like we did here with the test ids
632632
633633 b. You have to define the 2 tables to join after FROM
634634
635635 c. You have to define the common data between the tables after WHERE
636636
637637 54. SELECT scores.student_id, tests.date, scores.score, tests.maxscore
638638 FROM tests, scores
639639 WHERE date = '2014-08-25'
640640 AND tests.test_id = scores.test_id;
641641
642642 a. It is good to qualify the specific data needed by proceeding
643643 it with the tables name and a period
644644
645645 b. The test_id that is in scores is an example of a foreign key, which
646646 is a reference to a primary key in the tests table
647647
648648 55. SELECT CONCAT(students.first_name, " ", students.last_name) AS Name,
649649 tests.date, scores.score, tests.maxscore
650650 FROM tests, scores, students
651651 WHERE date = '2014-08-25'
652652 AND tests.test_id = scores.test_id
653653 AND scores.student_id = students.student_id;
654654
655655 a. You can JOIN more then 2 tables as long as you define the like
656656 data between those tables
657657
658658 56. SELECT students.student_id,
659659 CONCAT(students.first_name, " ", students.last_name) AS Name,
660660 COUNT(absences.date) AS Absences
661661 FROM students, absences
662662 WHERE students.student_id = absences.student_id
663663 GROUP BY students.student_id;
664664
665665 a. If we wanted a list of the number of absences per student we
666666 have to group by student_id or we would get just one result
667667
668668 57. SELECT students.student_id,
669669 CONCAT(students.first_name, " ", students.last_name) AS Name,
670670 COUNT(absences.date) AS Absences
671671 FROM students LEFT JOIN absences
672672 ON students.student_id = absences.student_id
673673 GROUP BY students.student_id;
674674
675675 a. If we need to include all information from the table listed
676676 first "FROM students", even if it doesn't exist in the table on
677677 the right "LEFT JOIN absences", we can use a LEFT JOIN.
678678
679679 58. SELECT students.first_name,
680680 students.last_name,
681681 scores.test_id,
682682 scores.score
683683 FROM students
684684 INNER JOIN scores
685685 ON students.student_id=scores.student_id
686686 WHERE scores.score <= 15
687687 ORDER BY scores.test_id;
688688
689689 a. An INNER JOIN gets all rows of data from both tables if there
690690 is a match between columns in both tables
691691
692692 b. Here I'm getting all the data for all quizzes and matching that
693693 data up based on student ids
694694
695695 59. One-to-One Relationship (SLIDE)
696696
697697 a. In this One-to-One relationship there can only be one social security number per person. Hence, each social security number can be associated with one person. As well, one person in the other table only matches up with one social security number.
698698
699699 b. One-to-One relationships can be identified also in that the foreign keys never duplicate across all rows.
700700
701701 c. If you are confused by the One-to-One relationship it is understandable, because they are not often used. Most of the time if a value never repeats it should remain in the parent table being customer in this case. Just understand that in a One-to-One relationship, exactly one row in a parent table is related to exactly one row of a child table.
702702
703703 60. One-to-Many Relationship
704704
705705 a. When we are talking about One-to-Many relationships think about the table diagram here. If you had a list of customers chances are some of them would live in the same state. Hence, in the state column in the parent table, it would be common to see a duplication of states. In this example, each customer can only live in one state so their would only be one id used for each customer.
706706
707707 b. Just remember that, a One-to-Many relationship is one in which a record in the parent table can have many matching records in the child table, but a record in the child can only match one record in the parent. A customer can choose to live in any state, but they can only live in one at a time.
708708
709709 61. Many-to-Many Relationship
710710
711711 a. Many people can own many different products. In this example, you can see an example of a Many-to-Many relationship. This is a sign of a non-normalized database, by the way. How could you ever access this information:
712712
713713 b. If a customer buys more than one product, you will have multiple product id�s associated with each customer. As well, you would have multiple customer id�s associated with each product.
714- See more at: http://www.newthinktank.com/2014/08/mysql-video-tutorial/#sthash.sqDDXI8z.dpuf