MySQL entry, mysql entry classic

MySQL entry, mysql entry classic

This chapter describes the Relational Database Management System (RDBMS) of MySQL and the Structured Query Language (SQL) used by MySQL ). The basic terms and concepts that should be mastered are listed, and the sample database used in the example is described.sampdbAnd provides a tutorial to demonstrate how to use 'mysql to create a database and interact with it.

If you do not know much about the database system, or are not sure whether you need to learn it, or even whether you need to use it, start with this chapter. If you do not know anything about MySQL or SQL and need an entry guide, start with this chapter. Readers who have some experience with MySQL or other database systems can skip this chapter. However, in order for you to use the databasesampdbThe purpose and content are more familiar. I hope you can read section 1.2.

1.1 usage of MySQL

This section describes the usage of the MySQL database system. It describes what MySQL can do and how it can help you ". If you understand the purpose of the database (maybe you are thinking about a problem, here you just want to find the answer to "How to use MySQL to solve it ), you can directly read section 1.2.

In essence, the database system is an efficient way to manage a large amount of list information. The information may come from a variety of sources. It may be research data, business records, customer needs, sports statistics, sales reports, personal information, personnel files, bug reports, or student scores. The time to take advantage of the power of the database system is that the information to be organized and managed is so large or complex that manual processing of all records will become very heavy. Databases are essential for large companies that process millions of transactions each day. However, even a small company with only one person may maintain a lot of information and even use a single database to manage it. Suppose there are the following situations.

• When you work in a dental clinic, you need to manage patient tracking records: When to visit, what to do, next appointment information, insurance information, etc.
• You have collected years of research data and need to analyze them for publication. You need to extract the extracted information from a large amount of raw data and extract the selected observed subset for detailed statistical analysis.
• You are a teacher and need to follow up the students' scores and attendance. At the end of each exam, you must record the scores of each student. Although it is easy to record the score to the score book, the subsequent score analysis is complicated. You 'd like to avoid sorting the scores for each exam to determine the score curve. You are also reluctant to add up the scores of each student to determine the final score at the end of the exam. It is also boring to count the attendance of every student.
• You are a secretary in an organization (possibly a professional group, a club, a symphony orchestra, or a fitness club), responsible for maintaining the list of Members of the Organization. Each year, you generate a printed directory for all Members. The directory is managed by the text processing software and you have to edit and update whenever the member information changes. You are very tired of maintaining the directory, because it limits your use, mainly because: it is difficult to sort the directory entries in different ways; you cannot easily select the specified part of each item, such as listing personal names and phone numbers. You cannot easily find a group of Members. If you need to update members as soon as possible. If there is a solution, you can read these entries every month to find the jobs for members who need to update their membership. You have heard of "paperless office" and know that it is the result of the development of electronic records, but you have not seen any benefits it brings. Although the membership record is electronic, it is ironic that, apart from printing the list in paper, it is difficult to use it for others!

The information involved in these scenarios is large and small. However, they all share a common feature, that is, these jobs can be done manually, but using a database system for management is more efficient.

What specific benefits do you expect when using a database system like MySQL? This depends on your special needs and needs, and, as shown in the above example, the specific benefits are different. However, in general, people who do not use the database management system need to use file cabinets to process tasks. In fact, the database system is like a huge file cabinet with a built-in complex file system. Compared with the manual management of records, the electronic management of records has many advantages. Let's take a look at the dental clinic scenario described above. MySQL can help you with the following in terms of document system capabilities used to manage patient records.

(1) Shorten the record archiving time. You don't have to pull a drawer in the file cabinet to find a place to store new records. You only need to submit it to MySQL. MySQL will find the correct place to store this record for you.

(2) Shorten the record retrieval time. When searching records, you do not have to manually search for the records you want. To send a reminder to patients who haven't checked in recent time, you can ask MySQL to help you find these records. Of course, this is different from asking you to tell another person "Please help confirm which patients did not attend the examination in the last six months. In fact, what you "read" is a strange "Spell ":

SELECT last_name, first_name, last_visit FROM patient  WHERE last_visit < DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

If you have never seen similar content before, it may seem quite scary at first. However, the effect is quite attractive: You don't have to spend an hour to review your records. You only need a second to get the desired results. In any case, you don't need to get used to this strange expression. After reading this chapter, you will understand its true meaning.

(3) Flexible retrieval and sorting. You do not need to retrieve the records in the order they are stored (for example, by patient name. MySQL can extract records in any order you want, such as by name, insurance company name, last visit time, etc.

(4) Flexible output format. After finding the records you are interested in, you do not need to manually copy the information. MySQL will generate a list for you. Sometimes you may just want to print this information; sometimes you may want to use them in another program. For example, after you generate a list of patients who failed to return to the clinic after the deadline, you can send the information to a text processing software to print out the notification you want to send to those patients. Maybe you are interested in summary information like "selected record statistics. You do not have to collect statistics yourself. MySQL will generate summary information for you.

(5) Simultaneous access records of multiple users. For a paper record, if two people want to view a record at the same time, one of them must wait for another person to return the record before viewing it. MySQL allows both users to access this record at the same time.

(6) Remote Access and electronic transmission of records. If you want to use paper records, you have to manually store them or copy them and send them to you. Electronic records provide the possibility of remote access records or electronic transfer records. If your dental group has many branches, people in these organizations can access your information locally. You no longer need to deliver these copies via express delivery. If someone needs to record but does not have the same database software as you, you can select the desired records and send the content to them over the network.

If you have used a database management system, you must have a deep understanding of the advantages described just now, and may also be thinking about how to go beyond the limitations of the conventional "replace file cabinet" application. There are many organizations that combine databases and websites. This is a good way. Assume that your company has a commodity inventory database. Every time a customer calls to ask whether there is any goods in the warehouse and what the price is, the service desk staff will use it. This is a more traditional way of using databases. However, if your company builds a website for customers to visit, you can provide a new service: Create a search page so that customers can view entries, to determine the price, availability, and inventory of goods. If online ordering is supported, customers can purchase your product without having to leave the house. This gives customers the information they want, while the database automatically searches for inventory information based on Questions to provide the information. The customer immediately obtains the information he cares about, and does not have to wait while listening to annoying recordings, or be limited by the service center's commuting time. In addition, every time a customer uses your company's website, it means that there will be less calls, and this is something that needs to be handled by a person who pays at the service desk. In this case, the website may be able to pay for itself.

However, you can further play the role of the database. Web-based inventory search requests can not only provide information to customers, but also to your company. These queries let you know what products customers are looking for, and the query results will let you know whether they can meet their needs. To some extent, if you do not have what the customer wants, you may miss the business. Therefore, it is very meaningful to record the inventory search information, through which you can understand what the customer is looking for and whether you still have inventory. Then, you can adjust the inventory based on the information and provide better services to customers.

After talking about MySQL for a long time, how does MySQL work? The best way to find the answer is to try it yourself. Therefore, we need a database that can be operated.

1.2 example Database

This section describes the sample database used in this book. When you learn how to use MySQL, you can use the example provided by this database. This database is designed for the two situations described previously.

• "Organization Secretary" scenario. The Agency has some characteristics: its members are very interested in the history of the United States (because there is no better name, it is called the "US History alliance "). All members must pay a certain amount of fees on a regular basis to maintain their membership. The fee will be used for some normal expenses, such as the publication newsletter Chronicles of U. S. Past ). The Alliance operates a small website, but it has not been fully developed yet, and you really want to reverse this situation.
• "Score assessment" scenario. As a teacher, you are responsible for all kinds of tests and quizzes during the evaluation period, and record scores and scores. Then, you need to determine the final score and hand them together with the attendance information to the school office.

Next, we will further analyze the needs of these two scenarios.

• You must decide what content in the database is what you want-that is, what you want to achieve.
• You must decide what content you want to put into the database-that is, what data you want to track.

Before "putting what into the database", consider "what content should be obtained from the Database", which seems to put the cart before the horse. After all, we all think this way: You need to input data before you can retrieve it. However, the way you use the database depends on what your goal is. In addition, the content to be retrieved from the database is more closely related to those targets than the content to be put into the database. Only after you plan to use this information in the future will you want to spend time and energy putting it into the database.

1.2.1 US History Alliance project

The scenario of this project is: you are the Alliance Secretary and are using a text processing document to maintain the membership list. There is certainly no problem with the process of "generating printed directories. However, when you want to obtain more information, you are limited. You have several goals to achieve.

• You want to output directories in different formats and customize the information according to different applications. One goal is to generate printed directories every year. This is a constant demand of the Alliance and it should continue to be implemented. You may also think of other purposes of the directory information, such as providing a printed list of the latest members to attendees at the Alliance's annual meeting. The two applications involve different information. The print directory program needs to use all the content of each member entry. In the Annual Meeting program, you only need to extract the member's name (this task cannot be easily completed using the word processing software ).
• You want to search for members that meet different conditions in the directory. For example, you want to know which members need to be updated recently. You also need another search application to maintain the list of keywords of each member. These keywords describe some historical American periods of special interest to all members, such as Civil War, Depression, civil right) the life stories of President Thomas Jefferson. Sometimes, some Members ask you to provide them with a list of other Members that share their interests, and you are willing to meet these requirements.
• You want to publish the directory to the Alliance's website. This will benefit you and all Members. If you can convert a directory to a Web page through some automated processes, the online version of the directory will always be up-to-date, which is not feasible in the form of paper. If the online directory supports the search function, members can easily search for information on their own. For example, if a member wants to know who else is interested in the "North-South war", he can search for it on his own, and you don't have to take the time to deal with it.

Databases are not the most exciting tools in the world, so I will not advocate unrealistic words like "using databases can inspire people's creative thinking. However, if you no longer regard information as a burden (as you thought when using text processing documents ), but think of it as something that can be processed relatively easily (just as you want to use MySQL to handle it), then you will naturally release your potential, find more new methods to use this information.

• If the information in the database can be put on the website as an online directory, you can also transfer the information in other ways. For example, it allows members to modify their own data online and update the data to the database. In this way, you do not have to take charge of all the edits, and this can make the information in the directory more accurate.
• If you store email addresses in the database, you can use them to send emails to Members to remind them to update their information in time. The email content displays the current information of the Members. Ask the members to check the information and prompt them how to use the functions provided by the website to complete necessary modifications.
• The database can also expand the purpose of the Alliance's website in many ways, not limited to the membership list. The Consortium newsletter Chronicles of U. S. Past has a children's column, each of which contains a knowledge history test. The focus of recent issues is the biography of the president of the United States. You can also set up a children's area on the Alliance's website to place those quiz questions on it. Maybe this area can also be interactive. For example, you can put the tested information into the database, so that the Web server can query the answer to the question in the database and then present it to the visitor.

Okay! At this moment, the number of database applications that you think of may make you forget. After a moment, return to reality, and you start to ask some practical questions, such:

• Think too much? Will it take a lot of work to achieve it?

Of course, it is easy to simply do nothing, and these ideas are not important to the specific implementation. However, after reading this book, you can meet all the requirements we just listed. But remember, there is no need to complete all the requirements at once. The entire work should be divided into several requirements, and only one requirement can be solved at a time.

• Can MySQL achieve all these goals?

No, it cannot, at least it alone. For example, MySQL does not have a built-in Web development tool. However, you can combine it with other tools to supplement and extend its functions.

We will use the script language Perl and its Database Interface (DBI) module to compile the script program for accessing the MySQL Database. The Perl language has powerful text processing capabilities. It can process database query results in an extremely flexible manner and generate a variety of outputs. For example, we can use Perl to generate a member list in Rich Text Format (RTF) Format, which can be read by various Text processing software. In addition, you can also generate a directory in HTML format for Web browsers.

We also need to use another scripting language PHP. The PHP language is especially suitable for compiling Web applications and it is also easy to interact with databases. With this feature, you can start MySQL queries on the Web page, and then generate a new page containing the database query results. There are many Web servers that support the PHP language (including the world's most popular Web server Apache), so something like "rendering a search form and displaying search results, it is a piece of cake ".

MySQL can be well integrated with these tools, so you can freely choose a combination to achieve your goal. Don't trust those integrated kit components too much. They generally advocate that they have "integrated" functions, but they can play a better role only when they work together.

• Finally, there is another important question: how much is the total cost? After all, the Alliance's budget is limited.

The answer may be incredible, but in fact it may not have any cost. If you know something about the database system, you should know that they are usually very expensive. In contrast, MySQL is generally free of charge. Even in an enterprise environment that requires technical support and maintenance commitment, the cost of using MySQL as a database system is very low. (For more details, visit www.mysql.com .) Other tools we will use (including Perl, DBI, PHP, and Apache) are free of charge. Therefore, you can build a useful system at a very low cost.

You can choose the operating system used for database development. Almost all the software we will discuss can run on UNIX (which refers to bsd unix, Linux, Mac OS X, etc.) and Windows. A few exceptions are generally UNIX or Windows-specific shell scripts or batch processing scripts.

1.2.2 score evaluation items

Now let's take a look at another example database. The scenario of this project is: you are a teacher responsible for performance evaluation. You want to convert the score processing work from manual operations using the workbook to electronic representation using MySQL. In this case, the way to obtain information from the database is hidden in the way you use the inventory book.

• You must record scores for each quiz or exam. For exams, you also need to sort scores to view them and determine the boundary of each letter score (including A, B, C, D, and F.
• At the end of the period, you need to calculate the total scores of each student, sort the total scores, and determine the scores based on this. The total score may need to be weighted, because you may need to make the exam more weighted than the test.
• At the end of the semester, you also need to provide attendance information for students to the school office.

The final goal is to avoid manual sorting and manual summarization of scores and attendance. In other words, you want MySQL to sort scores and complete all calculations related to the total score and number of absences of each student at the end of the period. To achieve these goals, you need a list of students in the class, scores for each exam and test, and the absence dates for all students.

1.2.3 how to use the sample database

If you are not interested in the "US History alliance" and "Performance Evaluation" projects, you may wonder "what else will be appropriate for you ". The answer is "endless ". In fact, these two projects show you what you can do with MySQL and related tools. Imagine how the sample database query is applied

The specific problems you want to solve. Suppose you work in the dental clinic I mentioned earlier. Although this book does not see many dental-related queries, many of the queries found here can be applied to patient record maintenance, office records, and other work. For example, "determining which Members in the Alliance need to update their membership qualifications" is very similar to "determining which patients will be returned in the future. They are all date-based queries. Therefore, as long as you have learned the "membership Update" query, you can use this technique to compile the query for "patient who has not returned for a long time, this will bring more gains.

1.3 Basic Database terms

You may have noticed that although this is a database book, you have not encountered many obscure technical terms so far. In fact, although we have a rough description of how to use the sample database, I did not mention what the database looks like. However, since we need to design a database and implement it, we can no longer avoid talking about related terms. This is the main content of this section. The terms described in this section are all used in this book, so I hope you will be familiar with them. Fortunately, many concepts of relational databases are simple. The reason why people like relational databases is that their basic concepts are simple and easy to understand.

1.3.1 structure terminology

In the database field, MySQL is divided into relational database management systems (RDBMS ). Let's take a look at it.

• A DataBase (DB in RDBMS) is a warehouse used to store information. It has simple structure and rules.

  • Datasets in the database are organized into tables ).

  • Each table consists of multiple rows and columns.

  • Each row in a table is called a record ).

  • A record can contain multiple pieces of information. Each column in the table corresponds to one of them.

• Management System (MS in RDBMS) is a software. We can use it to insert, retrieve, modify, or delete data) record.

• Relational (R in RDBMS) indicates that this is a special DBMS. Its strength lies in finding the common elements in the two tables, associate the information (matching) stored in the two tables. RDBMS is powerful in that it can easily extract data from these tables, and combine the information in the relevant tables to generate answers to answer questions that cannot be answered only by a single table. (In fact, the formal definition of "relationship" is different from the way I use it in this book. To this end, I would like to apologize to those purists first. However, my definition is more helpful to express the purpose of RDBMS .)

How does a relational database organize data into a table? How can we associate the information of different tables? The following is an example. Suppose you are running a website and it has a banner advertisement service. You have signed contracts with a number of companies that want to publish advertisements to meet their need to display advertisements when people visit your website. Every time a visitor clicks a page, you provide a page with advertisement embedded to the visitor's browser, so that you can get a little fee from the company where the advertisement is published. This is the so-called "click" advertisement ". To represent this information, you need to use three tables (see Figure 1-1 ). 1stcompanyThe table consists of the company name, number, address, and phone number. 2ndadThe table consists of the advertisement number, the number of the company that owns the advertisement, and the number of charges for each click. 3rdhitTable 'ad clicks and AD click dates need to be recorded.

Figure 1-1 Table of banner advertisements

Some questions can be answered using only one table. For example, if you want to know how many companies have signed a contract with you, you only need to countcompanyThe total number of rows in the table. Similarly, you only need to check the number of clicks in a specified period of time.hitYou can. Other questions may be complicated. You need to query multiple tables to obtain the answer. For example, if you want to know how many times each advertisement of Pickles (Pickles, Inc.) was clicked on December 31, July 14, you need to use all three tables as follows.

(1) IncompanyIn the table, find the company name (Pickles, Inc.) To find the company number (14 ).

(2) InadIn the table, use the company number to find the matched rows, so that you can determine the numbers of all the relevant ads. Finally, find two advertisements, numbered 48 and 101.

(3)adThe ID of each row matched in the table ishitIn the table, locate all matched rows whose dates are within the specified date range, and then count the number of matched rows. The final query result is: 48No.There are 3 matching ads; 101No.There are two matching ads.

These sounds complicated! However, this is what relational database systems are best. This complexity is only a superficial phenomenon, because each step described just now is a simple matching operation: Associate the row values of one table with the row values of another table by matching. This simple operation can be changed in a variety of ways to answer a variety of questions, such as how many different advertisements each company has put on? Which company is the most popular advertisement? What is the benefit of each advertisement? During the current settlement period, how much advertising fee should companies pay for you?

With these relational database theories, you can read the subsequent content of this book. We do not have to understand Third Normal Form) and Entity-Relationship digrams. (If you want to know about them, read C. J. Date or E. F. Codd .)

1.3.2 query language terminology

To interact with MySQL, you need to use a language named SQL. All mainstream database systems support SQL, but the implementations of different service providers are different. SQL supports many different statements, allowing you to interact with the database in a very interesting and practical way.

Like other computer languages, those who are new to SQL often find it strange. For example, when creating a table, you must tell MySQL what the table structure looks like. Many people will think of associating a table with a chart or image. However, MySQL is not like this. Therefore, when creating a table, you must inform MySQL of something similar to the following:

CREATE TABLE company  (    company_name VARCHAR(30),    company_num  INT,    address      VARCHAR(30),    phone        VARCHAR(12)  );

If you are not familiar with SQL, you may be afraid of such statements. But rest assured that you can learn how to use SQL skillfully without being a programmer. With your in-depth understanding of SQLCREATE TABLEIt is no longer a group of weird expressions, but a powerful tool that helps to describe information.

1.3.3 architecture terminology of MySQL

When using MySQL, you actually need at least two programs, because MySQL uses the "Client/Server" architecture. One program is the MySQL server, that is, mysqld. It runs on the machine where the database is stored. It is mainly responsible for listening to client requests on the network, accessing the database content based on these requests, and then providing the client with the information they query. Another program is the MySQL client, which is mainly responsible for connecting to the database server and initiating queries to inform the server of the desired information.

Most MySQL releases include database server programs and client programs. (When using the RPM package in Linux, there will be a separate server RPM package and client RPM package, so you should install two packages .) Use the appropriate client program according to your actual situation. The most common client program is mysql, which is an interactive client program. You can use it to initiate a query and view the results. There are also two main client programs for management, namely mysqldump and mysqladmin. The former is mainly used to export the table content to a file; the latter is mainly used to check the working status of the database server and execute some database management-related tasks, such as notifying the server to close. The MySQL release version also contains some other client programs. In addition, MySQL also provides a client development library. If its standard client program cannot meet your application requirements, you can use this library to write your own program. This development library can be directly used by C language programs. If you are not familiar with the C language, you can also select programming interfaces for other languages (such as Perl, PHP, Python, Java and Ruby.

The client programs discussed in this book are based on command line. You can also try MySQL Workbench, a Graphical User Interface (GUI)-based tool that provides point-to-point (point-to-point-
And-click. For more information about this tool, visit the http://www.mysql.com/products/
Tools /.

MySQL's "Client/Server" architecture has the following benefits.

• The server enforces concurrency control to prevent two users from simultaneously modifying the same record. All client requests must pass through the server, so the server is responsible for arranging the order of processing them. Even if multiple clients access the same table at the same time, they do not need to negotiate with each other first. They only need to send their own requests to the server, and then the server determines the order in which these requests are executed.
• You cannot log on to the machine where the database is located. MySQL works in the network environment, so you can run the MySQL client program anywhere, which can be connected to the server through the network. Distance is not a problem! You can access the server anywhere in the world. For example, if your server is located in Australia, you can still access your database even if you travel to Iceland with a laptop. Does this mean that anyone can see your data on the Internet? The answer is "no ". MySQL has a flexible security mechanism. You can set that only authorized users can access it. In addition, you can further restrict the operations of these people. For example, Sally of the finance department should have the permission to view and update (modify) records, while Phil of the service desk should only have the permission to view records. In short, you can refine this access permission control to everyone. If you only want to run a self-owned system, you can set the access permission to allow only client programs on the server to connect.

Differences between MySQL and mysql

To avoid confusion, we use MySQL to refer to the complete MySQL RDBMS.mysqlThe name of a specific client program. Although they are pronounced the same, they are differentiated by differences in uppercase and lowercase letters and fonts.

Speaking of pronunciation, MySQL was read as "my-ess-queue-ell ". For details, refer to MySQL Reference Manual. In addition, there are two types of SQL reading methods: "sequel" and "ess-queue-ell". The specific reading method is determined by the person who reads it. This book assumes that the pronunciation of SQL is "ess-queue-ell ".

1.4 MySQL tutorial

Now you have all the prerequisites. Perform the following operations on MySQL.

This section provides a tutorial with multiple examples to help you familiarize yourself with MySQL. The main content includes creating a sample database and multiple tables. You can perform insert, search, delete, and modify operations on the information in these tables to interact with the example database. In this process, you will learn the following content.

• Understand basic SQL knowledge that MySQL can understand. (MySQL is different from the SQL statements used by other RDBMS, so you 'd better view the content in this section quickly, check whether the SQL Implementation of MySQL is different from the version you are familiar .)
• Learn how MySQL's standard client program communicates with the MySQL server. As mentioned in the previous section, MySQL adopts the "Client/Server" architecture. The server runs on the machine where the database is located, while the client connects to the server over the network. This tutorial mainly depends on the client programmysqlFirst, it reads the SQL query statements you entered, sends them to the server for execution, and then displays the execution results in front of you. ClientmysqlIt can run on all platforms supported by MySQL and provides the most direct way to interact with the server. However, some examples UsemysqlimportOrmysqlshow.

Name the example databasesampdb. If someone occupies this name on your system, or your MySQL administrator assigns you another name, you need to change the name. In either casesampdbReplace it with the name of the database you actually use.

Even if you have multiple users in your system and each has their own example database, the table names in all examples in this section can be directly used. In MySQL, as long as everyone uses their own database, you can use the same table name without any problems. MySQL restricts tables in their respective databases to prevent mutual interference.

1.4.1 obtain the release package of the Sample Database

This tutorial uses the "Sample Database Release package" (also known as"sampdbDatabase Release "). This release contains many query statements and data used to install the sample database. You can find the methods and installation steps for the release package in Appendix. After the release package is decompressed, it will automatically create a subdirectory named sampdb, which contains many files you need. By the way, it is recommended that you switch to a new directory every time you operate the example in the database.

If you want to run MySQL programs conveniently in any directory, you 'd better includebinDirectory and add it to the search path of the command interpreter. The specific operation method is to add the path name of the directory to the environment variable by referring to section A.3.3 of this book.PATH.

1.4.2 basic configuration requirements

For each example in this tutorial, in addition to getting the Sample Database Release package, you must also meet the following basic requirements.

• MySQL software must be installed.
• You must have a MySQL account that can connect to the database server.
• There must be a database for operation.

The required software includes the MySQL client and MySQL server. The client program must be installed on the machine you want to use. The server can be installed on your machine, which is not mandatory. As long as you have the permission to connect to it, the server can be located anywhere. If your Internet Service Provider (ISP) provides the MySQL Service, you can also apply for it. To obtain and install MySQL on your own, see Appendix.

In addition to the MySQL software, you must have a MySQL account to connect to the server and create a sample database and its tables. (If you already have a MySQL account, you can use it directly. However, we recommend that you create another account dedicated to book learning .)

At this moment, we have encountered the problem of "first chicken or eggs": to create a MySQL account for connecting to the server, you must first connect to the server. Normally, you needrootLog on as a user and then executeCreate user statementAndGRANTStatement to create a new MySQL account and assign it database permissions. If you have installed the MySQL server on your machine and are running, you can userootConnect to the server, and then create a new example Database Administrator account whose username issampadmThe password issecret. You can also change the name and password to what you want to use. However, you must modify the names and passwords used later in this book.

% **mysql -p -u root  **Enter password: ******  mysql> **CREATE USER 'sampadm'@'localhost' IDENTIFIED BY 'secret';  **Query OK, 0 rows affected (0.04 sec)  mysql> **GRANT ALL ON sampdb.* TO 'sampadm'@'localhost';  **Query OK, 0 rows affected (0.01 sec)

CommandmysqlThere is an option-p, Which enablesmysqlPrompt InputrootThe user's MySQL password. As shown in the preceding example, the entered password is displayed as an asterisk **. Assume that you haverootThe user has set a password. If you have not setEnter Password: Press Enter when prompted. However, ifrootIf the user has no password, this is a major security vulnerability. You should set one for it as soon as possible. Want to know