[Translation] MySQL5.0 user manual-Chapter 3: Quick Start

[Translation] MySQL5.0 user manual-Chapter 3: Quick Start
Some time ago, I translated some of the content in chapter 1, but the popularity is not high. It may be because the topic in Chapter 1 cannot raise everyone's interest.
The second chapter describes how to install MySQL. kai has started to translate this chapter and I will not repeat it.
So I decided to translate Chapter 3 to get something substantive. I hope you will be welcomed.

Directory


3.1. Connect to the server and disconnect
3.2. Enter the query statement
3.3. Create and use a database
3.3.1. Create and select a database
3.3.2. Create a table
3.3.3. load data into a table
3.3.4. extract data from the table
3.4. obtain information about the database table
3.5. Use mysql in batch mode
3.6. Common examples
3.6.1. query the maximum value of a column
3.6.2. query the data of the row where the maximum value of a column is located
3.6.3. query the maximum value of each group in a column
3.6.4. query the data of the row with the maximum value of each group in a column
3.6.5. Use custom Variables
3.6.6. Use a foreign key
3.6.7. query by two keys
3.6.8. Calculate the daily traffic volume
3.6.9. Use auto Increment
3.7. query data in Twin Project
3.7.1. Search for all twins that are not separated
3.7.2. display a table showing the status of each pair of twins
3.8. Use MySQL in Apache
Chapter provides a tutorial to demonstrate how to create and use a simple database through the mysql client program. Mysql is an interactive program that allows you to connect to a MySQL Server, run queries, and view results. Mysql can also run in batches: you can write the query statement to a file in advance, and then let mysql execute the content of the file. Both methods of using mysql are described here.


Start mysql with the -- help option to see which options it provides:


Shell> mysql -- help
This chapter assumes that mysql has been installed on your machine and an available MySQL Server can be connected. Otherwise, contact your MySQL administrator. (If you are an administrator, read the relevant chapters in this Manual, such as Chapter 5 and Database Administration .)


This chapter describes the whole process of creating and using a database. If you are only interested in accessing an existing database, you can skip the chapter about creating a database and creating a table.


This chapter is essentially a tutorial, so many details have been omitted. For more information, see the relevant sections of this Manual.


3.1. Connect to the server and disconnect
When using mysql to connect to the server, you usually need to provide a MySQL user name, and you may need a password. If the server runs on a remote host, you also need to specify the host name. Contact your administrator to find out the connection parameters (that is, the host, user name, and password) You should use ). Once you know the correct parameters, you should connect as follows:


Shell> mysql-h host-u user-p
Enter password :********
Host and user represent the host name running on the MySQL server and the user name of your MySQL account. replace them with the appropriate values. * ******* Indicates your password. when mysql displays Enter password: prompt, Enter it.


If the connection is successful, you will see some introduction information after the mysql> prompt:


Shell> mysql-h host-u user-p
Enter password :********
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 25338 to server version: 5.0.19-standard


Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.


Mysql>
Mysql> the prompt indicates that mysql is ready. You can enter the command.


If you log on to a machine running the MySQL server, you can omit the host name as follows:


Shell <mysql-u user-p
If you get the following ERROR message ERROR 2002 (HY000) when trying to connect: Can't connect to local MySQL server through socket '/tmp/mysql. sock '(2), which indicates that the MySQL server daemon (Unix) or service (Windows) is not running. Contact the administrator or check Chapter 2 and Installing MySQL to solve the problem.


For help with Common problems encountered during logon, see Section A.2, "Common Errors When Using MySQL Programs ".


After installing MySQL, you can allow anonymous logon on the local machine. If this happens on your machine, you can run mysql without any parameters to connect to the server:


Shell> mysql
After the connection is successful, you can enter the QUIT (or \ q) command to disconnect from the server:


Mysql> QUIT
Bye
In Unix systems, you can also press Control-D to disconnect.


Most of the examples in the following sections assume that you have already connected to the server, which is specified at the mysql> prompt.
3.2. Enter the query statement
Make sure you have connected to the server according to the previous method. When connecting to the server, you do not have to select the database to operate. From this point, it is more important to learn how to issue a query than directly jump to create a table, load data, and read data. This chapter describes the basic principles of input commands. Input several query statements to understand how mysql works.


The following is a simple command that requires the server to tell you its version number and current date. Enter the command at mysql> prompt and press Enter:


Mysql> select version (), CURRENT_DATE;
+ ---------------- + -------------- +
| VERSION () | CURRENT_DATE |
+ ---------------- + -------------- +
| 5.0.7-beta-Max | 2005-07-11 |
+ ---------------- + -------------- +
1 row in set (0.01 sec)
Mysql>
The queries are described as follows:


A command usually consists of an SQL statement and a semicolon. (In some special cases, the semicolon can also be omitted. For example, QUIT is one of them, and it will be encountered in other cases .)
When you issue a command, mysql sends it to the server for execution and displays the result. Then, mysql> prompt indicates that you can enter the next command.
Mysql displays the query result in a table. The first row is the identifier of each column, and the following row is the query result. Generally, the column marker is the column name of the data you retrieve from the database. If the value you query is the calculation result of an expression (as shown in the following example ), mysql uses this expression as a flag for this column.
Mysql shows how many rows of data are returned and how long it takes to execute this query. This gives you a preliminary impression on the server performance. These values are inaccurate because they represent the clock time (instead of the CPU time or machine time), which is affected by the server load and network latency. (For the sake of conciseness, the line "rows in set" is sometimes not displayed in the following example .)
Keywords are case-insensitive. The following statements are equivalent:
Mysql> select version (), CURRENT_DATE;
Mysql> select version (), current_date;
Mysql> SeLeCt vErSiOn (), current_DATE;
The following query statement describes how to use mysql as a simple calculator:


Mysql> select sin (PI ()/4), (4 + 1) * 5;
+ ------------------ + --------- +
| SIN (PI ()/4) | (4 + 1) * 5 |
+ ------------------ + --------- +
| 1, 0.70710678118655 | 25 |
+ ------------------ + --------- +
1 row in set (0.02 sec)
So far, we have demonstrated the simplicity of single-line statements. In fact, you can also enter multiple statements in the same line. Each statement is separated by a semicolon:


Mysql> select version (); select now ();
+ ---------------- +
| VERSION () |
+ ---------------- +
| 5.0.7-beta-Max |
+ ---------------- +
1 row in set (0.00 sec)


+ --------------------- +
| NOW () |
+ --------------------- +
| 17:59:36 |
+ --------------------- +
1 row in set (0.00 sec)
A command does not have to be input in one line. It is no problem to divide a statement into multiple lines. mysql uses a semicolon to indicate the end of a statement, rather than a row as a statement. (In other words, mysql accepts very free input, which is executed only when all the input is known as a semicolon .)


This is a simple multi-line statement:


Mysql> SELECT
-> USER ()
->,
-> CURRENT_DATE;
+ --------------- + -------------- +
| USER () | CURRENT_DATE |
+ --------------- + -------------- +
| Jon @ localhost |
+ --------------- + -------------- +
In this example, you can note that after you enter an incomplete command, the mysql prompt is changed from mysql> to->, indicating that the command is not complete. A prompt is a friend of ours who provides valuable feedback. Through this information, we can know what input mysql expects.


If you do not want to execute a part of a command, Type \ c to cancel it:


Mysql> SELECT
-> USER ()
-> \ C
Mysql>
Here, pay attention to the prompt changes. After \ c is input, the prompt is changed back to mysql>. It provides feedback that mysql can accept new commands.


The following table summarizes the meaning of all mysql prompts:


Prompt meaning
Mysql> prepare to accept new commands.
-> Wait for the next line of a multi-line command.
'> Wait for the next line of a string starting with single quotes.
"> Wait for the next line of a string starting with single quotes.
'> Wait for the next row of an identifier starting with backtick.
/*> Wait for the next line of comments starting.


In the MySQL 5.0 series, the/*> prompt is provided starting from MySQL 5.0.6.


We often encounter multi-line commands unexpectedly, that is, when we enter a line of statements, we forget to enter a semicolon. In this case, mysql waits for the input in the next line:


Mysql> select user ()
->
If you encounter this situation (you think you have entered a statement but only get a-> prompt), it is very likely that mysql is waiting for a semicolon. If you don't notice the changes in the prompt, you may sit down and wait. Mysql will execute the following semi-colon statement:


Mysql> select user ()
->;
+ --------------- +
| USER () |
+ --------------- +
| Jon @ localhost |
+ --------------- +
'> And "> sometimes appears when you input a string (in other words, MySQL is waiting for unfinished strings ). In MySQL, you can use the '''' or ''character to input a string (for example, 'Hello' or" goodbye "), mysql allows you to input strings with multiple rows. When you see a '> or "> prompt, it means that you have entered the quotation marks starting with the string, and you have not entered the ending quotation marks. If you are entering a multi-line string, good, but is that true? Not all. More often, the '> and "> prompts show that you accidentally missed a quote character. For example:


Mysql> SELECT * FROM my_table WHERE name = 'Smith AND age <30;
'>
If you enter the SELECT statement, press the Enter key, and wait for the result, nothing will appear. "Why is the query so long ?", Note: "> clues provided by the prompt. It tells you that mysql expects to see the rest of an unterminated string. (Do you see an error in the statement? The string "Smith is missing the second quotation mark .)


In this case, what should I do? The simplest way is to cancel the command execution, but you cannot simply enter \ c, because MySQL will think that this is the unfinished part of the string. On the contrary, we should first enter a pair of quotation marks (so that we can tell mysql that we have already completed the string) and then enter \ c:


Mysql> SELECT * FROM my_table WHERE name = 'Smith AND age <30;
'>' \ C
Mysql>
The prompt is changed to mysql>, which means mysql can accept the next command.


'> The prompt is similar to'> and "> the prompt, but it indicates that you have not entered the acktick-quoted identifier.


It is very important to know the meanings of '>, ">, and'> identifiers. If you mistakenly enter an unaborted string, any line you enter next will be ignored by mysql-including the line containing the QUIT command. If you don't know how to stop the quotation marks before entering other commands, this will make you confused.
. 3. Create and use a database
3.3.1. Create and select a database
3.3.2. Create a table
3.3.3. load data into a table
3.3.4. extract data from the table
Now you know how to input commands. It is time to operate the database.


If you have many pets in your home (zoo) and want to track their information, you can create a table and input the required information into the table to extract data from the table, you can answer all kinds of questions about your pet. This section demonstrates how to do the following:


Create a database
Create a table
Input data to the table
Use different methods to obtain data from a table
Use multiple tables
The zoo database here is simple (intentional), but it is hard to imagine that there are many situations in real life that need to use similar databases, for example, A farmer can use a similar database to manage his livestock, or a veterinarian to manage his/her records. The zoo database used here, including the data and query statements, can be found on the MySQL site, while providing tar compression (http://www.mysql.com/Downloads/Contrib/Examples/menagerie.tar.gz) and Zip compression (http://www.mysql.com/Downloads/Contrib/Examples/menagerie.zip) download files in two formats.


Use the SHOW statement to find out which databases exist on the server:


Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
| Tmp |
+ ---------- +
The database list may be different on your machine, but it will basically contain mysql and test Databases. The mysql database is required because it stores the data of user access permissions. The test database is often used as a work zone for users to try it out.


Note that you may not be able to see all DATABASES if you do not have the show databases permission. See Section 13.5.1.3, "GRANT Syntax ".


If the test database exists, try to access it:


Mysql> USE test
Database changed
Note that the USE command is the same as the QUIT command and does not need to end with a semicolon. (It does not affect the end of a semicolon .) The USE statement can only be used as a single row.


You can use the test database (as long as you have the permission to access it) to practice the following example. However, anything you create in this database can be deleted by others who access this database, therefore, you should ask your MySQL administrator to allow you to use a database of your own. If you want to name your database menagerie, the administrator needs to execute the following command:


Mysql> grant all on menagerie. * TO 'Your _ mysql_name '@ 'your _ client_host ';
Your_mysql_name is the MySQL user name assigned to you, and your_client_host is the machine name of the machine on which you connect to the server.


3.3.1. Create and select a database
If the Administrator creates this database when assigning permissions to you, you can directly use it. Otherwise, you need to create it by yourself:


Mysql> create database menagerie;
In Unix systems, database names are case-sensitive (Unlike SQL keywords), so you must always reference your database with menagerie, not Menagerie, MENAGERIE, or other variants. This rule also applies to table names. (In Windows, this restriction does not apply, although you must use the same case to reference databases and tables in the same query. For these reasons, we recommend that you use and create databases and tables in the same case .)


Note: If you get ERROR 1044 (42000): Access denied for user 'monty '@ 'localhost' to database 'menagerie' when attempting to create a database ERROR message, this indicates that your user account does not have the permission to perform this operation. Tell your administrator or check Section 5.8, "The MySQL Access Privilege System ".


After a database is created, it is not automatically used as the current database. You must select it explicitly. To make menagerie the current database, run the following command:


Mysql> USE menagerie;
Database changed
Your database only needs to be created once, but you need to select it each time you start a mysql session. You can USE the USE statement described above to select a database, or you can USE the command line parameter to select a database when running mysql. You only need to specify the database name after all command line parameters:


Shell> mysql-h host-u user-p menagerie
Enter password :********
Please note that menagerie in the above command line is not your password. If you want to provide a password in the-p option in the command line, you must not add any space (for example, yes-pmypassword instead of-p mypassword ). However, it is not recommended to place the password in the command line, because it will directly expose the plaintext to other users using your machine.
3.3.2. Create a table
It is easy to create a database, but it is still empty. You can use show tables to view it:


Mysql> show tables;
Empty set (0.00 sec)
The harder part is to design the database structure: Which tables are contained in the database and which columns are contained in each table.


What we need now is a table that stores pet records. We can name this table pet, and this table should contain at least the name of each animal, however, it is meaningless to include only the name. We need other information. For example, if you have multiple pets in your house, you can designate a director for each animal and record information such as type and gender.


What about age? You must be very concerned about this information, but it is not a good idea to store it in the database, because age changes over time and you need to constantly update your database. A better way is to store the birthdays of animals. When you need an age, you can calculate the current time and birthday. MySQL provides a time calculation function, therefore, it is not difficult to achieve this. There are several benefits to storing birthdays instead of ages:


You can ask the database to remind you when your pet's birthday is approaching (don't think this idea is boring, in commercial applications, in many cases, you need to send birthday greetings to customers every week or every month. They are essentially the same .)
You can calculate the age based on the relative date instead of the current date. For example, if you store the animal's death date in the database, you can easily calculate the size of an animal.
You can also add other useful information to the pet table, but so far this is enough: name, owner, type, gender, birth, and death date.


Use the create table statement to specify the structure of your TABLE:


Mysql> create table pet (name VARCHAR (20), owner VARCHAR (20 ),
-> Species VARCHAR (20), sex CHAR (1), birth DATE, death DATE );
The VARCHAR type is suitable for storing name, owner, and species columns because the data in these columns is longer. This definition does not require that the data length of this column be the same as that of bean, and it does not require 20. You can choose any length between 1 and 65535, as long as you think this length is appropriate. (Note: Before MySQL 5.0.3, the maximum limit is 255 .) If your initial selection is unreasonable and a long field is required in the future, you can use the alter table statement provided by MySQL to modify it.


Gender of animals can be expressed in multiple ways, such as 'M' and 'f' or 'male' and 'female '. Using a single character 'M' and 'F' is the simplest.


It is appropriate to use the DATE data type to save the data in the birth and death columns.


After a table is created, the following output is generated when show tables is used:


Mysql> show tables;
+ --------------------- +
| Tables in menagerie |
+ --------------------- +
| Pet |
+ --------------------- +
Verify that the created table conforms to our design. Use the DESCRIBE statement:


Mysql> DESCRIBE pet;
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
| Name | varchar (20) | YES | NULL |
| Owner | varchar (20) | YES | NULL |
| Species | varchar (20) | YES | NULL |
| Sex | char (1) | YES | NULL |
| Birth | date | YES | NULL |
| Death | date | YES | NULL |
+ --------- + ------------- + ------ + ----- + --------- + ------- +
We can use DESCRIBE at any time, for example, when we forget a column name or its data type.


To obtain