Interaction skills with mysql

1.5 interaction skills with mysql
This section describes how to interact with the mysql client program more effectively and with less input workload. This section describes how to connect to the server more easily, and how to avoid entering the query from the beginning each time.
1.5.1 simplify the connection process
When activating mysql, you may need to specify connection parameters such as the host name, user name, or password. Running a program requires a lot of input work, which will soon be annoying. There are several ways to minimize the input work, making the connection easier. They are:
■ Use option files to store connection parameters.
■ Repeat commands using shell commands.
■ Define the mysql command line shortcut using the shell alias or script.
1. Use the option File
MySQL allows you to store connection parameters in an option file since MySQL 3.22. Then, you do not need to repeat these parameters when running mysql. It can be used only when you have typed them on the command line. These parameters can also be used by other MySQL clients, for example, mysqlimport. This also means that the option file reduces the typing workload when using these programs. To use the option file method to specify the connection parameter, you can create a parameter named ~ /. My. cnf is a file named. my. cnf in the main directory ). The option file is a non-formatted text file, so it can be created using any text editor. The file content is as follows:

The [client] line marks the beginning of the guest group. All the rows followed by it are prepared for the MySQL client program to obtain the option value, these rows continue until the end of the file or the beginning of another different parameter group. When connecting to the server, replace serverhost, yourname, and yourpass with the specified host name, user name, and password. For the author,. my. cnf is as follows:

Only [client] rows are required. The Rows defining parameter values are optional. You can specify only the required parameters. For example, if your MySQL username is the same as the UNIX username, the user line is not required. After the. my. cnf file is created, set the access mode to a specific value to prevent others from reading it:

In Windows, the content of the Option file is the same, but its name is different from c: \ my. cnf), and the chmod command is not called. Because option files are not added to MySQL before version 3.22, they cannot be used in earlier versions. Especially in Windows, you cannot use the option file with the shared MySQL distribution package, because it is based on MySQL3.21. The option file works well in the registered MySQL Windows version. Otherwise, you can obtain the updated support option file client from the MySQLWeb site. For more information about the option file, see Appendix E "MySQL program reference ".
2. The shell program's command history, such as c s h, tcsh, and bash, will record your command in a history list and allow you to repeat the commands in the list. If such a shell program is used, its history list can help eliminate the need to type complete commands. For example, if mysql is recently called, run the following command again: %! My, "!" Tell the shell to search for the entire command history. Find the command that recently started with "my" and release it as you typed in. Some Shell programs can also use the up arrow and down arrow keys, perhaps Ctrl-P and Ctrl-N) to move up and down the history list. You can use this method to select the desired command and then press Enter to execute it. Tcsh and bash have this function, and other Shell programs may also. You can refer to the corresponding shell program to find more details about the usage history list.
3. If the shell provides the alias function by using the shell alias or script, you can set a command shortcut key that allows you to call a long command by entering a short name. For example, in csh or tcsh, you can use the alias command to set an alias named samp_db, as shown below:

The syntax in bash is slightly different:

You can define an alias to make these two commands equivalent:

Obviously, the first one is better than the second one. To make these aliases take effect upon every login, you can put an alias command in the external shell setup file, such as csh into. cshrc, bash into. bash_profile ). Other shortcut keys are used to create shell scripts that execute mysql with appropriate options. In UNIX, the script file equivalent to the samp_db alias is as follows:

If I name this script samp_db and use chmod + x samp_db for execution, I can type samp_db to run mysql and connect it to my database. In Windows, you can use a batch command file to complete the same job. Name the file samp_db.bat and put the following lines in it:

This batch of command files can be executed by typing samp_db at the DOS console prompt, or by double-clicking its Windows icon. If you access multiple databases or connect to multiple hosts, you can define several aliases or scripts, each of which uses different options to call mysql.
1.5.2 publish a query with less input
Mysql is a very useful program for interacting with databases, but its interface is most suitable for short and single-row queries. Of course, mysql does not care whether a query is divided into multiple rows, but it is difficult to type long queries. Inputting a query is not very interesting, even for a short query. You are willing to retype it unless you find an error.
There are several tips to avoid unnecessary typing or retyping:
■ Use the mysql input row editing function.
■ Use copy and paste.
■ Run mysql in batches.
■ Use existing data to create new records to avoid entering INSERT statements.
1. Use mysql input line editor
Mysql has a built-in GNU Readline library that allows you to edit input rows. You can process the currently entered rows, or call up the previously entered rows and re-execute them as they are or execute them after further modification ). This is very convenient when you Enter a row and find an error. You can return the row and correct it before pressing Enter. If a wrong query is input, you can call the query and edit it to solve the problem, and then submit it again. If you type the entire query on a row, this is the easiest way .) Table 1-4 lists some very useful editing sequences. In addition to this table, there are also many input editing commands. The Internet search engine should be able to find the online version of the R e a d l I n e manual. This manual is also included in the Readline distribution package, available at the http://www.gnu.org/gn u Web site.

The following example describes a simple use of input editing. Assume that the following query is entered using mysql:

If you have noticed that "p r e s I d e n t" is incorrectly spelled as "per s I d e n t" before pressing Enter ", you can press the left arrow or Ctrl-B to move the cursor multiple times to the left of "s. Then, press Delete to Delete "er" twice, type "r e" to correct the error, and press Enter to publish the query. If you do not notice the wrong spelling, press E n ter. After an error message is displayed in mysql, press the up arrow or Ctrl-P to bring up the row and edit it.
The input line editing does not work in mysql Windows, but you can get a free cygwin_32 client distribution package from the MySQLWeb site. The mysqlc program in the package is the same as that in mysql, But it supports input-line editing commands.
2. query by copying and pasting
If you are working in a window, you can save the query text that is considered useful in a file and use the copy and paste operations to easily publish these commands. The process is as follows:
1) Activate mysql in the Te l n e t window or DOS control window.
2) open the file that contains the query in a document window. For example, I use B e d I t in Mac OS and v I in xterm Window in X Window System in UNIX .)
3) In order to execute a query stored in the file, select and copy it. Switch to the Telnet window or DOS console and paste the query to mysql. This process seems a bit annoying to write, but it is a very easy way to quickly enter the query. In actual use, you do not need to enter the query. This method also allows you to edit queries in the document window, and allows you to copy and paste existing queries to construct a new query. For example, if you often select rows from a specific table but want to view output results stored in different ways, you can save a list of different order by clauses in the document window, copy and paste the clause you want to use for any specific query. You can also copy and paste files from Telnet to query files in other directions ). When you enter rows in mysql, they are saved in the. mysql_history file in your home directory. If you manually enter a query that you want to save for future use, you can exit mysql and open it in an editor. mysql _ h I s t o r y, and then from. mysql_history: copy and paste the queried file.
3. Running mysql in batches does not necessarily have to run mysql interactively. Mysql can read input from a file in non-interactive batch mode. This is useful for regularly running queries, because you do not want to re-type this query every time you run it. You only need to put it into a file at a time, and then let mysql execute the content of the file as needed. Suppose there is a query for the interests column of the member table to find the members of the historical alliance who are interested in a certain aspect of the history of the United States. For example, if you are looking for members who are interested in the project, you can write a semicolon at the end of the query as follows, so that mysql can know where the query statement ends ):

To use this query to find members interested in Thomas Jefferson, you can edit this query file to change the depression to Je fferson and run mysql again. As long as this query is not frequently used, it works well. If it is frequently used, a better method is required. A more flexible way to use this query is to put it into a shell script, which retrieves a parameter from the script command line and uses it to change the query text. In this way, the query parameters are determined so that the keyword of interest can be specified when the script is run. To understand how this works, write a small shell script I n ter e s t s. s h as follows:

The second line ensures that there is a keyword on the command line; it displays a short message or exits. All content between <q u e RY_INPUT and the last q u e RY_INPUT is the mysql input. In the query text, the shell replaces $1 with a keyword from the command line. In shell script, $1, $2... is the command parameter .) This allows the corresponding query to reflect the keyword specified on the command line when the script is executed. Before running this script, make it executable:

You do not need to edit the script every time you run it. You just need to tell it what to look for on the command line. As follows:

4. Use existing data to create new records
You can use the INSERT statement to append a new record to the table in one row at a time. However, after you manually type the INSERT statement to create several new records, most people will realize that there should be a better way to append the record. One option is to use a file that only contains DATA values, and then use the load data statement or mysqlimport utility to LOAD records into the file. Generally, you can use data that already exists in a certain format to create a data file. The data information may be contained in the workbook, and may be converted to MySQL in another database. To make the introduction simple, we assume that the data is in the Electronic Table of the desktop micro-computer. To switch the workbook data from a desktop PC to a file under your UNIX account, you can copy and paste the data using Telnet. The specific work is as follows: 1) Open a Telnet connection for a UNIX account. In Mac OS, applications such as Better Telnet or N CSA Telnet can be used. In Windows, you can use a standard Telnet program.
2) Open the workbook, select the data block to be converted, and copy it.
3) In the Telnet window, click the following command to start obtaining data to the file data.txt.

The cat command is waiting for input.
4) paste the data copied from the workbook to the Telnet window. The cat authentication tool writes the certificate to the data.txt file with the key information.
5) After all the pasted data has been written to the file, if the cursor stops at the end of the Data row rather than starting from the new row, press Enter. Then, press Ctrl-d to indicate "End of File ". The cat stops the input file and closes the data.txt file. Now we have obtained the data.txt file containing the selected DATA blocks in the workbook. This file is ready for loading DATA mysqlimport to the database.
Copying and pasting is a quick and easy way to pass data into UNIX files, but it is best suited to smaller datasets. A large amount of data may exceed the system copy buffer. In this case, it is best to save the workbook in the form of a non-formatted text tab. Then, you can use FTP to upload the corresponding files from the microcomputer to the UNIX account. Convert the text mode to a non-binary or image mode) file so that the line terminator is converted to a UNIX line terminator. UNIX uses line breaks, Mac OS uses line breaks, and Windows uses line breaks/line breaks as line terminator .) It can tell load data or mysqlimport what line breaks to find, but in UNIX, it is easier to process files containing line breaks.
After converting the file, check whether there are blank lines at the end. If yes, delete them. Otherwise, when the file is loaded to the database, the blank lines will be converted to blank or malformed records. Files stored in unformatted text from workbooks, or parentheses that can enclose spaces. To remove these parentheses when loading the file into the database, you can use the fields enclosedby clause of load data or the -- fields-enclosed-by option of mysqlimport. For more information, see the corresponding items of load data in Appendix D.
 1.6 where to go
Now we have introduced a lot of knowledge about using MySQL. You already know how to set up a database and create a table. Records can be placed in these tables and retrieved, changed, or deleted in various ways. However, you still have a lot to learn about MySQL. The tutorials in this chapter only provide some simple things. By examining our sample database, we will understand this point. We have created a sample database and its tables and filled them with some initial data. In this process, we understand how to write queries and answer some questions about database information, but there is still much work to be done.
For example, we do not have a convenient interaction method to enter new credit records for the credit retention plan, or to enter member entries in the historical alliance address list. There is no convenient way to edit existing records, and we still cannot generate printed or online URL directories. These tasks and other tasks will be introduced in subsequent chapters, this topic is particularly detailed in chapter 7th "Perl dbi api" and Chapter 8th "php api.
What part of the book will be read next depends on what you are interested in. If you want to know how to complete
For more information about MySQL program design, see the first section. If you plan to become a MySQL administrator for a website, the third part of this book will introduce the management work more. However, I suggest that you read the remaining chapters in the first part to first obtain the general background of using MySQL.
These sections discuss how MySQL processes data, further provide the syntax and query statement usage, and explain how to make the query run faster. Regardless of the environment in which you use MySQL, whether you are running mysql or writing your own program,
As a database administrator, using this content to lay a good foundation will help you stand at a high starting point.