MYSQL example database _ MySQL

A mysql sample database this section describes a sample database, which may be used in all parts of this book. This database provides a reference example when you learn how to put MySQL into work. We will give an example from the two situations described above:
■ Secretariat plan of the institution. We need more explicit information than the "institution", so now we can construct one with the following features: it is composed of a group of people who gather together for the purpose of studying the history of the United States (for the moment, they cannot find a better name, which is also called the historical alliance of the United States ). Updated on a regular basis on the basis of membership dues
Membership qualifications. Membership dues constitute the fund for the alliance's activities, such as the publication of the newspaper "American editor ". This alliance also has a small Web site, but there are not many features developed. So far, this site only provides some basic information, such as the nature of the group, who the owner is, and who can participate.
■ Credit retention plan. During the credit period, you need to manage the subjects, record the scores, and assign the score level. Then determine the final score level and hand it together with the attendance rate to the school office. Now let's further consider these situations according to the following two requirements:
■ You must determine what information you want to obtain from the database, that is, what you want to achieve.
■ You must plan what to input to the database and what data to save. Maybe, before thinking about what data to input to the database, you should reverse think about what data to output from the database. Before data can be retrieved, data must be sent to the database. However, database-based methods are driven by your goals. these methods are more closely related to the information you want to extract from the database than to the database. Unless you plan to use this information later, you will certainly not waste time and effort inputting them into the database.
1.2.1 In the early stage of the US historical alliance solution, you are the secretary of the alliance and maintain the membership list using the word processing document. In this way, a printed name and address record can still be generated, but it will be limited when you use this information to do other things. Assume that you intend to do the following:
■ You want to use this name and address to generate outputs in different formats and only provide the information required for the corresponding purpose. One of the goals is to generate printed name and address records every year, which is required by the alliance in the past and you plan to continue printing. In addition, you may consider assigning the information in the name and address records for some other purposes, such as at the annual banquet of the alliance.
A list of current members is provided in the provided project list. This application involves different information sets. The printed name and address records use all the content of each member entry. For banquet program tickets, you only need to extract the member name (it is sometimes not easy to use a word processor ).
■ Search for members whose names and addresses meet certain conditions. For example, you want to know which members need to be updated soon. In addition, the search-related applications are generated by maintaining the keyword list of each member. These keywords describe an aspect of the history of the United States that each member is particularly interested in (such as civil war, economic recession, civil rights, or the life of Thomas Jefferson ). Members sometimes ask you for a list of members with similar interests, and you will be happy to meet these requirements.
■ You want to use the name and address directories online on the alliance's Web site. This is good for members and you. If you can convert a proper automatic process of name/address recruitment to a Web page, the online version of the name/address record can maintain the latest information in a more timely manner than the printed version. In addition, if the online name and address records can be searched, members can conveniently find information themselves. For example, if a member wants to know about other members interested in the civil war, he can find these members by himself instead of asking for help, and you don't have to spend time doing this. We clearly know that databases are not the most exciting thing in the world, so we do not intend to be crazy.
It is claimed that using databases can promote creative thinking. However, when you stop looking at information as something you must fight against (this is indeed true when processing documents with words ), and start to think of it as something that is relatively easy to manipulate (as you want to do with MySQL, your ability to propose a new method that uses or represents information will be liberated to some extent. for example, the following examples are some new methods:
■ If the information in the database can be moved to the Web site in the form of online name and address records, you may make the information flow in other ways. For example, if a member can edit his/her own entries online and update the database, you do not have to edit all the entries by yourself. this helps make the information in the name and address records more accurate.
■ If you store Email addresses in the database, you can use them to send emails to members who haven't updated their own entries for a long time. The sent messages can display the content of their entries to these members, ask them to view them, and then instruct them how to use the utility provided by the Web site to make the necessary changes.
■ The database not only associates with the member table, but also makes the Web site more useful. For example, the Alliance published a newspaper "American Chronicle", with a layout for children in each issue containing historical questions. Recent issues have been concentrated on the biography of the president of the United States. The website of the alliance can also be included in the layout of the child, so that the questions are online. By placing the questions retrieved from the database and asking the Web server to query randomly given questions, this layout may even become interactive.
At this point, you may have remembered the usage of many databases, which makes you somewhat unable to control yourself. Before going back to reality, you start to ask some special questions:
■ Is this ambitious? Do I have to do a lot of work during preparation? Of course, if you just want not to do it, everything is very simple. I do not pretend that the above implementation is trivial. However, at the end of this book, all of the things we described were done. You only need to remember one thing, and there is no need to finish all things at a time. We will break down the work and make only part of it each time.
■ Can MySQL complete all these tasks? No, it cannot. For example, MySQL does not have direct Web capabilities. Although MySQL itself cannot complete everything we discuss, we can get tools that work with MySQL to improve and expand MySQL capabilities. We will use the Perl scripting language and the DBI (Database Interface) Perl module to write scripts for accessing the MySQL database. Perl provides excellent text processing capabilities, allowing you to process query results in a highly flexible manner to produce output in various formats. For example, we can use Perl to generate name and address records in the multi-information text format (rt f). This is a format that can be read by all word processors. We can also use another scripting language PHP. PHP is especially suitable for compiling Web applications, and it works with databases. This allows you to run MySQL queries on the Web page and generate a new page containing the database query results. PHP works well with Apache (the world's most popular Web server), which makes it easy to complete such tasks as giving a search window and displaying search results. MySQL is well integrated with these tools, and provides you with the flexibility to combine them in your own way, you can choose to implement your ideas. Instead of being limited to the so-called "integration" features that are widely promoted, the actual work is just a fixed combination of each other.
■ In the end, there is a big problem, that is, how much is the cost of all these things? First, the alliance's budget is limited. The answer is, there is no need to spend any money, which may surprise you. If you are familiar with General database systems, you will know that they are generally very expensive. However, MySQL is generally free of charge. In some environments, it is true that
A license is required and $200 is required if the number of users is unlimited. (For more information about licenses, see the preface. For more information, see the MySQL Reference Guide .) Other tools we will use (Perl, DBI, PHP, Apache) are also free of charge. Therefore, everything is taken into consideration and can be very cheap to form a useful system. The operating system for developing this database depends on you. All the software we introduced can run on UNIX, most of which can run on Windows. We recommend that you run MySQL and other tools in UNIX. They are all originated in UNIX before being transferred to Windows. This indicates that their Windows versions have a short maturity and have not been thoroughly tested and used.
Now, let's consider other cases of using the sample database.
1.2.2 credit retention plan
The initial idea is that, as a teacher, he has the responsibility to save credits. The instructor hopes to transfer the manual operation of the credit processing from the credit book to MySQL for electronic representation. In this case, what you want to get from the database is included in the credit book:
■ Credits are recorded for each test or test. For tests, credits are sorted to determine the score range of each character (A, B, C, D, and F.
■ Calculate the total score of each student at the end of the credit period, sort the total score, and determine the score level based on them. The total score may involve weight calculation, because it is probably expected to make the score of the Test greater than the test and score weight.
■ Provide attendance information to the school office at the end of each credit period. The objective is to avoid manual sorting and collection of total credits and attendance records. In other words, MySQL is expected to sort credits at the end of the credit period and calculate the total score and number of missing courses for each student. To achieve this goal, you need a student register in the class, scores for each test and test, and dates when students are absent.
1.2.3 how can the sample database meet the requirements?
If you are not interested in historical alliances or credit retention, you may wonder why these examples are required? The answer is that these examples are not an aim, but they are used to explain what can be done using MySQL and its related tools. In addition, you can see how the queries of the sample database are applied to the problems you want to solve. Assuming that you work at the dental clinic mentioned above, you will see many dental queries in this book. For example, determining which members of the historical alliance need to update their membership immediately is similar to determining which patients are not visiting the dentist recently. Both of them are date-based queries. Therefore, once you have learned how to write member update queries, you can use this technology to write more interested delayed appointment patient queries.