Section 4.3 MySQL Fundamentals

Learning Objectives

After you have completed this section, you should be able to

• install MySQL on a computer.
• work with MySQL to create and manipulate databases.

Activities

• Carefully read the learning objectives.
• Download MySQL and install it on your computer. Read the manual on the MySQL web page if necessary.
• Become familiar with the MySQL manual at https://dev.mysql.com/doc/
• See the tutorial at https://www.analysisandsolutions.com/code/mysql-tutorial.htm
• Watch the related section of the presentation for Chapter 18;
• Read section 18.5, MySQL of Chapter 18
• Find answers to the study questions below.
• Log into MySQL server and create the database and tables for the library you designed in previous unit. It should have at least two tables: book and borrower.

Study Questions

1. What is MySQL?
2. Where can you download MySQL for your system?
3. How do you configure your web server so that it can work with MySQL?
4. How does a web server work with MySQL?
5. How do you set up a user for MySQL to run as?
6. How do you set up your path for MySQL?
7. How do you set the root password?
8. Why do you need to delete the anonymous user?
9. How do you start the MySQL server?
10. How do you set the MySQL server up to run automatically?
11. How do you log in to MySQL?
12. How do you create databases in MySQL?
13. What is MySQL’s privilege system?
14. What are privileges?
15. How do you use the GRANT command to set up a user’s privilege?
16. How many levels of privilege are there in MySQL’s privilege system?
17. How do you take away a privilege from a user using the REVOKE command?
18. Which privileges are for users?
19. Which privileges are for administrators?
20. What are special privileges in MySQL?
21. How do you choose the right database to use?
22. How do you create database tables in MySQL?
23. When creating a database table, how do you set a primary key in MySQL?
24. When creating a database table, how do you set the type for a column?
25. How do you view the tables in a database?
26. What are the differences between the SHOW and DESCRIBE commands?
27. What are MySQL identifiers?
28. How are databases and tables mapped to the underlying file structure?
29. What data types can a column have?
30. How do you insert data into a database in MySQL?
31. How do you retrieve data from a database in MySQL?
32. How do you retrieve data from a database with specific criteria?
33. How do you retrieve data from multiple tables in MySQL?
34. What is the left join in MySQL?
35. How do you find rows/records that don’t match?
36. What is a Cartesian product or full join?
37. What is a cross-join?
38. What is an inner join?
39. What is an equi-join?
40. How do you retrieve data in a particular order?
41. What functions are provided in MySQL for aggregating data?
42. How do you group retrieved data?
43. How do you choose which rows to return from retrieved data?
44. How do you update data in a database?
45. Can you alter tables after creation? If yes, how?
46. How do you delete records from a database?
47. How do you drop tables in MySQL?
48. How do you drop a whole database in MySQL?

Summary

Read the questions and answers you find at http://dev.mysql.com/doc/refman/5.1/en/faqs.html and http://www.mysqlfaqs.net/