MySQL usage _ MySQL

This chapter introduces the usage of MySQL relational database management system (RDBMS) and its Structured Query Language (SQL ). This article provides the basic terms and concepts that should be mastered, introduces the sample database used in this book, and provides guidance on how to use MySQL to create a database and access it.

If you are not familiar with the database, you may not be sure whether a database is required or whether a database can be used. Or, if you do not know anything about MySQL or SQL and need an entry-level guidance, read this chapter carefully. Readers who have experience with MySQL or database systems may wish to skip this chapter. However, whether it is a beginner or not, you should read 1. section 2 "a sample database", because the database given in this section is the best example familiar with the usage and content of the database, which will be used repeatedly in this book.
1.1MySQL usage
This section describes the application scenarios of MySQL. Provides a general overview of what MySQL can do and how it is used. If you do not need to understand the purpose of the database, you may have an idea in your mind to solve the problem, just want to know how to use MySQL to help solve it, you can skip to Section 1.2 "a sample database ". A database system is essentially a means of managing information lists. The information may come from different places. For example, it can represent research data, business records, customer requests, sports data statistics, sales reports, personal interests information, personal records, problem reports or student scores. Although the database system can process a wide range of information, you won't just use it for use. If a job is easy, there is no reason to just import the database into the job to use the database. A grocery order is a good example: listing a shopping list, drawing a cross on it after purchase, and then throwing it away, it is very impossible to use a database for this matter. Even if you have a laptop, you only use notepad for the grocery ticket, instead of enabling the database.
The power of the database system can be displayed only when the organization and management information is huge or complex and the manual processing is extremely heavy. Of course, large companies that process millions of businesses each day can use databases. However, even small companies that only involve individual personal interests who maintain information may need databases. It is hard to imagine the benefits of using databases before information becomes difficult to manage. Consider the following situations:
■ There are several employees in your woodworking shop. Employees and payroll records need to be kept to know who paid and when, and these records must be summarized to report income to the tax authority. You also need to understand what your company hires and what you make for each job.
■ You have a warehouse network for auto parts. you need to know which Warehouse has given parts so that you can fill in customer orders.
■ As a toy seller, pay special attention to the prevalence of purchased items. You need to know the current sales curve of an item to estimate whether you need to increase inventory (for more popular items ), or reduce its inventory (so as not to store a lot of bad sales stuff ).
■ A large amount of research data collected for years of research needs to be analyzed for publication. We hope to process a large amount of raw data to obtain conclusive information, and select a subset of observed samples for more detailed statistical analysis.
■ You are a welcome Speaker who will give speeches at various gatherings throughout the country, such as at graduation ceremonies, business meetings, City gatherings, and administrative conferences. After so many lectures, it is difficult for me to remember where I spoke, so I am very willing to save the record of the past lectures to help prepare for future speeches. If you go back to a place where you have previously made a speech, you certainly do not want to make a speech similar to the previous one. all the places you have made have a record to help you avoid repetition. You must pay attention to the popularity of your speech. (The speech "why do I like cats" you gave at the "metropolis dog's nest" club is not very successful, so I do not want to make the same mistake when I go there again .)
■ You are a teacher and need to know the credits and attendance information. Each time you take a test or test, you must record the credits of the students. It is easy to write your exam scores in the workbook, but it will be difficult to use it later. Therefore, when the final score is not determined for the term, you would rather not sort credits than summarize the credits of each student. It is not a simple task to calculate the absence of absenteeism for each student.
■ You are the secretary of an organization, which has a huge address book for member names. (The so-called organization can be any organization, such as a professional group, club, orchestra or fan club .) Every year, you need to edit it with a word processor based on the changes in member information, and then print an address list for each member. You get tired of maintaining this address book in this way because it is limited to what you can do with it. It is difficult to sort entries in different ways, and it is not convenient to select specific parts of each entry (for example, a list composed of only names and phone numbers ). Or a group of members, such as those who need to update their membership soon. if possible, you should cancel searching for all items every month to find out which members need to send additional instructions. In addition, you are not willing to edit the address book on your own, but the group does not have that much budget. ask someone to have a problem. You have heard of "paperless office", which is a way to save records electronically, but you do not see any benefits. Currently, the membership record is electronic, but it is ironic that there is not much to worry about except the printing of the address book. In the above cases, some involve a large amount of information, and some involve a small amount of information. Their common features are that the tasks involved can be manually completed, but it is much more effective to use a database system. What kind of results do you want to see when using a database system like MySQL? This depends on your specific needs. as shown in the above example, the effect of the difference is quite large. We will consider a common situation, but also a representative database application. Generally, database management systems are used to process tasks such as file cabinets. In a sense, a database is like a large file cabinet, but a built-in file orchestration system. Electronic processing records have many advantages over manual processing records. For example, if you work in an office facility that stores customer records, MySQL can help you in some ways:
■ Reduce the recording and archiving time. You do not have to view all the drawers in the cupboard to find new records. You just need to put the recording into the file orchestration system and make the file orchestration system put the record in the correct position for you.
■ Reduce the record retrieval time. When searching for records, you do not need to search for each record to find the record containing the required information. Assume that you work in a dental clinic. If you want to send a reminder to all patients who have not recently performed a diagnosis, you only need to ask the file orchestration system to find the appropriate records. Of course, this is different from asking others to do it. You only need to say, "Please confirm which patients have not been there in the last six months ."
When using the database, you need to send out a strange "spell ":
SELECT last_name, first_name, last_visit FROM patient
WHERE last_visit If you have never seen something similar, it may be quite scary, but it should be attractive to get results in a second or two far better than an hour. (No matter what, don't worry too much. These "spells" won't take long. In fact, you only need to read this chapter to fully understand its meaning .)
■ Flexible search sequence. You do not need to view the records in a fixed sequence (for example, search by name ). The file orchestration system can be required to identify records in any sequence, such as recording records by surname, insurance company name, and last visit date.
■ Flexible output format. You do not need to manually copy the information of the records you are interested in. The file orchestration system can generate a list for you. Sometimes, you only need to print this information. Sometimes you may want to use this information in other programs. (For example, you can send the information to a word processor and send a notification to the patient after generating a list of patients with misaligned dental appointments .) Or you are only interested in the summary information, such as the number of selected records. You do not have to count them yourself; the file orchestration system can automatically generate a summary.
■ Simultaneous access records of multiple users. If two people want to search for a record at the same time, one of them must wait until the other person finds the record. MySQL provides the ability for multiple users to search at the same time, so that two people can access records at the same time.
■ Record remote access and electronic transmission. A paper record must be available at hand, or someone needs to copy the record and send it to you. Electronic records can be remotely accessed or transmitted electronically. If your dental specialists work in multiple clinics, they can access your records from their locations without sending them a fast email. If a person to be recorded does not have the same software as your database software but has an email, you can select the desired record and send it in an electronic document.
If you have used a database management system before and have already understood the above advantages of the database, you may wonder how to surpass the purpose of replacing the file cabinet. Currently, the database system can be used to provide services that were not available until recently. For example, many organizations use databases in a way that combines with the Web, which was previously impossible.
If your company has an inventory database, when the customer asks whether there is an item in the warehouse and its price is what, the service desk staff will use this database, this is a traditional application of databases. However, if your company provides customers with a Web site that is accessible, you can provide another service: a search page that allows customers to determine the price and availability of items. This provides customers with the information they need and allows them to automatically search for the information of items stored in the inventory. The customer can get the information immediately, without having to listen to the sound recorded in advance, or being limited by whether the service center is working. For each customer who uses your Web site, the cost is less than that of the service desk staff's transfer call. (Maybe the website has already paid for it .)
There is also a better way to use the database than above. Web-based inventory query requests not only provide information to customers, but also provide information for yourself. This query request tells you what the customer is looking for, and the query results let you know whether they can meet their requests. You may lose business opportunities in areas that cannot meet customer needs. Therefore, it is meaningful to record information about inventory search, such as what the customer is looking for and whether the inventory is there. Then, you can use this information to adjust your inventory to better serve customers.
Another new use of the database is to make title ads on Web pages. I don't like them as much as you do, but the fact is that this is a very popular MySQL application. you can use MySQL to store ads and then retrieve them for the display of Web servers. In addition, MySQL can also be used for tracking, which advertisements are involved in such tracking, how many times they are displayed, and which site has accessed them.
Therefore, the best way to know how to use MySQL is to try it by yourself. for this purpose, you should have an experimental database.