MySQL入门手册

首页 > 代码库 > MySQL入门手册

2024-07-28 12:22:57 222人阅读

本文内容摘自MySQL5.6官方文档，主要选取了在实践过程中所用到的部分文字解释，力求只摘录重点，快速学会使用MySQL，本文所贴代码地方就是我亲自练习过的代码，凡本文没有练习过的代码都没有贴在此处，如果读者想自己尝试，可以查看官方文档，文中给出了原官方文档的对应链接以供查阅。

本文地址：http://www.cnblogs.com/yhLinux/p/4019386.html

http://dev.mysql.com/doc/refman/5.6/en/tutorial.htmlThis chapter provides a tutorial introduction to MySQL by showing how to use the mysql client program to create and use a simple database.

This chapter describes the entire process of setting up and using a database.

1. 连接服务器

2. 输入查询

3. 创建并使用一个数据库

　　3.1. 创建与选择一个数据库

　　3.2. 创建表

　　3.3. 给表填充数值

　　3.4. 从表中检索信息

4. 获取数据库及表的信息

5. 以批处理方式使用mysql

6. 常见查询示例

7. 与Apache联合使用

3.1 Connecting to and Disconnecting from the Server

To connect to the server, you will usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you will also need to specify a host name. Contact your administrator to find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Once you know the proper parameters, you should be able to connect like this:

shell> mysql -h host -u user -pEnter password: ********

If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply use the following:

shell> mysql -u user -p

$ mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

Most examples in the following sections assume that you are connected to the server. They indicate this by the mysql> prompt.

3.2 Entering Queries

This section describes the basic principles of entering commands, using several queries you can try out to familiarize yourself with how mysql works.

mysql> SELECT VERSION(), CURRENT_DATE;+------------+--------------+| VERSION()  | CURRENT_DATE |+------------+--------------+| 5.6.21-log | 2014-10-14   |+------------+--------------+1 row in set (0.05 sec)mysql>

Here is another query. It demonstrates that you can use mysql as a simple calculator:

mysql> SELECT SIN(PI()/4), (3+2)*5;+--------------------+---------+| SIN(PI()/4)        | (3+2)*5 |+--------------------+---------+| 0.7071067811865475 |      25 |+--------------------+---------+

The queries shown thus far have been relatively short, single-line statements. You can even enter multiple statements on a single line. Just end each one with a semicolon:

mysql> SELECT VERSION(); SELECT NOW();+------------+| VERSION()  |+------------+| 5.6.21-log |+------------+1 row in set (0.30 sec)+---------------------+| NOW()               |+---------------------+| 2014-10-16 14:27:13 |+---------------------+1 row in set (0.19 sec)mysql>

A command need not be given all on a single line, so lengthy commands that require several lines are not a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)

Here is a simple multiple-line statement:

mysql> SELECT    -> USER()    -> ,    -> CURRENT_DATE;+----------------+--------------+| USER()         | CURRENT_DATE |+----------------+--------------+| root@localhost | 2014-10-16   |+----------------+--------------+

If you decide you do not want to execute a command that you are in the process of entering, cancel it by typing \c:

mysql> SELECT    -> USER()    -> \C          ;;小写的才对ERROR: Usage: \C charset_name | charset charset_name    -> \cmysql>

The following table shows each of the prompts you may see and summarizes what they mean about the state that mysql is in.

Prompt	Meaning
`mysql>`	Ready for new command.
`->`	Waiting for next line of multiple-line command.
`‘>`	Waiting for next line, waiting for completion of a string that began with a single quote (“`‘`”).
`">`	Waiting for next line, waiting for completion of a string that began with a double quote (“`"`”).
`>	Waiting for next line, waiting for completion of an identifier that began with a backtick (“`”).
`/*>`	Waiting for next line, waiting for completion of a comment that began with `/*`.

When you see a ‘> or "> prompt, it means that you have entered a line containing a string that begins with a “‘” or “"” quote character, but have not yet entered the matching quote that terminates the string. This often indicates that you have inadvertently left out a quote character. For example:

mysql> SELECT * FROM my_table WHERE name = ‘Smith AND age < 30;    ‘>

Instead of wondering why this query takes so long, notice the clue provided by the ‘> prompt. It tells you that mysql expects to see the rest of an unterminated string. (Do you see the error in the statement? The string ‘Smith is missing the second single quotation mark.)

At this point, what do you do? The simplest thing is to cancel the command. However, you cannot just type \c in this case, because mysql interprets it as part of the string that it is collecting. Instead, enter the closing quote character (so mysql knows you‘ve finished the string), then type \c:

mysql> SELECT * FROM my_table WHERE name = ‘Smith AND age < 30;    ‘> ‘\cmysql>

3.3 Creating and Using a Database

Suppose that you have several pets in your home (your menagerie) and you would like to keep track of various types of information about them. You can do so by creating tables to hold your data and loading them with the desired information. Then you can answer different sorts of questions about your animals by retrieving data from the tables. This section shows you how to perform the following operations:

Create a database
Create a table
Load data into the table
Retrieve data from the table in various ways
Use multiple tables

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

mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+

The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out.

If the test database exists, try to access it:

mysql> USE testDatabase changed

USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you like; it does no harm.) The USE statement is special in another way, too: it must be given on a single line.

The menagerie database is simple (deliberately), but it is not difficult to think of real-world situations in which a similar type of database might be used.

3.3.1 Creating and Selecting a Database

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. for a variety of reasons, the recommended best practice is always to use the same lettercase that was used when the database was created.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this command:

mysql> USE menagerieDatabase changed

Your database needs to be created only once, but you must select it for use each time you begin a mysql session.

You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

shell> mysql -h host -u user -p menagerieEnter password: ********

Important

menagerie in the command just shown is not your password. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -pmypassword, not as -p mypassword). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

Note

You can see at any time which database is currently selected using SELECT DATABASE().

mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || menagerie          || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| menagerie  |+------------+1 row in set (0.00 sec)mysql>

3.3.2 Creating a Table

Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:

$ mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.6.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> SHOW TABLES;ERROR 1046 (3D000): No database selectedmysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || menagerie          || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| NULL       |+------------+1 row in set (0.00 sec)mysql> USE menagerieDatabase changedmysql> SHOW TABLES;
Empty set (0.00 sec)

mysql>

You want a table that contains a record for each of your pets. This can be called the pet table, ..... You can probably think of other types of information that would be useful in the pet table, but the ones identified so far are sufficient: name, owner, species, sex, birth, and death.

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);Query OK, 0 rows affected (0.34 sec)mysql> SHOW TABLES;+---------------------+| Tables_in_menagerie |+---------------------+| pet                 |+---------------------+

To verify that your table was created the way you expected, use a 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    |       |+---------+-------------+------+-----+---------+-------+

3.3.3 Loading Data into a Table

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in ‘YYYY-MM-DD‘ format; this may be different from what you are used to.)

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

Because you are beginning with an empty table, an easy way to populate it is to create a text file containing a row for each of your animals, then load the contents of the file into the table with a single statement.

You could create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N). For example, the record for Whistler the bird would look like this (where the whitespace between values is a single tab character):

Whistler        Gwen    bird    \N      1997-12-09      \N

我的pet.txt文件内容：

Fluffy    Haroid    cat    f    1993-02-04    \NClaws    Gwen    cat    m    1994-03-17    \NBuffy    Haroid    cat    f    1989-05-13    \NFang    Benny    dog    m    1990-08-27    \NBrowser    Diane    dog    m    1979-08-31    1995-07-29Chirpy    Gwen    bird    f    1998-09-11    \NWhistler    Gwen    bird    \N    1997-12-09    \NSlim    Benny    snake    m    1996-04-29    \N

To load the text file pet.txt into the pet table, use this statement:(这里给出完整的操作过程)

$ mysql -u root -pmysql> SHOW DATABASES;mysql> SELECT DATABASE();mysql> USE menageriemysql> SELECT DATABASE();mysql> SHOW TABLES;mysql> DESCRIBE pet;mysql> LOAD DATA LOCAL INFILE ‘/home/hostName/test/pet.txt‘ INTO TABLE pet;           ##hostName主机用户名Query OK, 8 rows affected (0.23 sec)Records: 8  Deleted: 0  Skipped: 0  Warnings: 0mysql>

You can specify the column value separator and end of line marker explicitly in the LOAD DATA statement if you wish, but the defaults are tab and linefeed. These are sufficient for the statement to read the file pet.txt properly.

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. Suppose that Diane gets a new hamster named “Puffball.” You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet    -> VALUES (‘Puffball‘, ‘Diane‘, ‘hamster‘, ‘f‘, ‘1999-03-30‘, NULL);Query OK, 1 row affected (0.09 sec)

String and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value. You do not use \N like you do with LOAD DATA.

From this example, you should be able to see that there would be a lot more typing involved to load your records initially using several INSERT statements rather than a single LOAD DATA statement.

3.3.4 Retrieving Information from a Table

The SELECT statement is used to pull information from a table. The general form of the statement is:

SELECT what_to_selectFROM which_tableWHERE conditions_to_satisfy;

1.选择所有数据：

mysql> SELECT * FROM pet;+----------+--------+---------+------+------------+------------+| name     | owner  | species | sex  | birth      | death      |+----------+--------+---------+------+------------+------------+| Fluffy   | Haroid | cat     | f    | 1993-02-04 | NULL       || Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       || Buffy    | Haroid | cat     | f    | 1989-05-13 | NULL       || Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       || Browser  | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 || Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       || Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       || Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       || Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |+----------+--------+---------+------+------------+------------+

For example, you may happen to think that the birth date for Bowser doesn‘t seem quite right. Consulting your original pedigree papers, you find that the correct birth year should be 1989, not 1979.

There are at least two ways to fix this:（这里只使用UPDATE方法，另一方法是用DELETE与LOAD DATA，见3.3.4.1 Selecting All Data）

mysql> UPDATE pet SET birth = ‘1989-08-31‘ WHERE name = ‘Browser‘;

2. 选择特定行3.3.4.2 Selecting Particular Rows

You can select only particular rows from your table. For example, if you want to verify the change that you made to Bowser‘s birth date, select Bowser‘s record like this:

mysql> SELECT * FROM pet WHERE name = ‘browSer‘;+---------+-------+---------+------+------------+------------+| name    | owner | species | sex  | birth      | death      |+---------+-------+---------+------+------------+------------+| Browser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |+---------+-------+---------+------+------------+------------+

String comparisons normally are case-insensitive不分大小写, so you can specify the name as ‘bowser‘, ‘BOWSER‘, and so forth. The query result is the same.

As shown in the preceding section, it is easy to retrieve an entire table. Just omit the WHERE clause from the SELECT statement. But typically you don‘t want to see the entire table, particularly when it becomes large. Instead, you‘re usually more interested in answering a particular question, in which case you specify some constraints on the information you want. Let‘s look at some selection queries in terms of questions about your pets that they answer.

mysql> SELECT * FROM pet WHERE name = ‘Bowser‘;mysql> SELECT * FROM pet WHERE birth >= ‘1998-1-1‘;mysql> SELECT * FROM pet WHERE species = ‘dog‘ AND sex = ‘f‘;mysql> SELECT * FROM pet WHERE species = ‘snake‘ OR species = ‘bird‘;mysql> SELECT * FROM pet WHERE (species = ‘cat‘ AND sex = ‘m‘)    -> OR (species = ‘dog‘ AND sex = ‘f‘);

3. 选择特定列3.3.4.3 Selecting Particular Columns

If you do not want to see entire rows from your table, just name the columns in which you are interested, separated by commas. For example, if you want to know when your animals were born, select the name and birth columns:

mysql> SELECT name, birth FROM pet;mysql> SELECT owner FROM pet;mysql> SELECT DISTINCT owner FROM pet;mysql> SELECT name, species, birth FROM pet    -> WHERE species = ‘dog‘ OR species = ‘cat‘;

4. 排列rows：

You may have noticed in the preceding examples that the result rows are displayed in no particular order. It is often easier to examine query output when the rows are sorted in some meaningful way. To sort a result, use an ORDER BY clause.

Here are animal birthdays, sorted by date:

mysql> SELECT name, birth FROM pet ORDER BY birth;

On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive大小写无关 fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.

The default sort order is ascending升序, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by:

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

You can sort on multiple columns, and you can sort different columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query:

mysql> SELECT name, species, birth FROM pet    -> ORDER BY species, birth DESC;

5. 日期计算：3.3.4.5 Date Calculations

To determine how many years old each of your pets is, use the TIMESTAMPDIFF() function.

mysql> SELECT name, birth, CURDATE(),    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age    -> FROM pet;+----------+------------+------------+------+| name     | birth      | CURDATE()  | age  |+----------+------------+------------+------+| Fluffy   | 1993-02-04 | 2014-10-17 |   21 || Claws    | 1994-03-17 | 2014-10-17 |   20 || Buffy    | 1989-05-13 | 2014-10-17 |   25 || Fang     | 1990-08-27 | 2014-10-17 |   24 || Browser  | 1989-08-31 | 2014-10-17 |   25 || Chirpy   | 1998-09-11 | 2014-10-17 |   16 || Whistler | 1997-12-09 | 2014-10-17 |   16 || Slim     | 1996-04-29 | 2014-10-17 |   18 || Puffball | 1999-03-30 | 2014-10-17 |   15 |+----------+------------+------------+------+

按name排列一下：

mysql> SELECT name, birth, CURDATE(),    -> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age    -> FROM pet ORDER BY name;+----------+------------+------------+------+| name     | birth      | CURDATE()  | age  |+----------+------------+------------+------+| Browser  | 1989-08-31 | 2014-10-17 |   25 || Buffy    | 1989-05-13 | 2014-10-17 |   25 || Chirpy   | 1998-09-11 | 2014-10-17 |   16 || Claws    | 1994-03-17 | 2014-10-17 |   20 || Fang     | 1990-08-27 | 2014-10-17 |   24 || Fluffy   | 1993-02-04 | 2014-10-17 |   21 || Puffball | 1999-03-30 | 2014-10-17 |   15 || Slim     | 1996-04-29 | 2014-10-17 |   18 || Whistler | 1997-12-09 | 2014-10-17 |   16 |+----------+------------+------------+------+

To sort the output by age rather than name, just use a different ORDER BY clause:

mysql> SELECT name, birth, CURDATE(),    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age    -> FROM pet ORDER BY age;

A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is NULL. Then, for those with non-NULL values, compute the difference between the death and birth values:

mysql> SELECT name, birth, death,     -> TIMESTAMPDIFF(YEAR, birth, death) AS age    -> FROM pet WHERE death IS NOT NULL ORDER BY age;+---------+------------+------------+------+| name    | birth      | death      | age  |+---------+------------+------------+------+| Browser | 1989-08-31 | 1995-07-29 |    5 |+---------+------------+------------+------+

The query uses death IS NOT NULL rather than death <> NULL because NULL is a special value that cannot be compared using the usual comparison operators. This is discussed later. See Section 3.3.4.6, “Working with NULL Values”.

What if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides several functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and MONTH(birth):

mysql> SELECT name, birth, MONTH(birth) FROM pet;

Finding animals with birthdays in the upcoming month is also simple. Suppose that the current month is April. Then the month value is 4 and you can look for animals born in May (month 5) like this:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

There is a small complication if the current month is December. You cannot merely add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1).

You can write the query so that it works no matter what the current month is, so that you do not have to use the number for a particular month. DATE_ADD() enables you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays:

mysql> SELECT name, birth FROM pet    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

A different way to accomplish the same task is to add 1 to get the next month after the current one after using the modulo function (MOD) 求模 to wrap the month value to 0 if it is currently 12:

mysql> SELECT name, birth FROM pet    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

6. 处理NULL值3.3.4.6 Working with NULL Values

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;+----------+-----------+----------+----------+| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |+----------+-----------+----------+----------+|     NULL |      NULL |     NULL |     NULL |+----------+-----------+----------+----------+

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

This special treatment of NULL is why, in the previous section, it was necessary to determine which animals are no longer alive using death IS NOT NULL instead of death <> NULL.

Two NULL values are regarded as equal in a GROUP BY.

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

A common error when working with NULL is to assume that it is not possible to insert a zero or an empty string into a column defined as NOT NULL, but this is not the case. These are in fact values, whereas NULL means “not having a value.” You can test this easily enough by using IS [NOT] NULL as shown:

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, ‘‘ IS NULL, ‘‘ IS NOT NULL;+-----------+---------------+------------+----------------+| 0 IS NULL | 0 IS NOT NULL | ‘‘ IS NULL | ‘‘ IS NOT NULL |+-----------+---------------+------------+----------------+|         0 |             1 |          0 |              1 |+-----------+---------------+------------+----------------+

Thus it is entirely possible to insert a zero or empty string into a NOT NULL column, as these are in fact NOT NULL. See Section B.5.5.3, “Problems with NULL Values”.

7. 模式匹配3.3.4.7 Pattern Matching

To find names beginning with “b”:

mysql> SELECT * FROM pet WHERE name LIKE ‘b%‘;

To find names ending with “fy”:

mysql> SELECT * FROM pet WHERE name LIKE ‘%fy‘;

To find names containing a “w”:

mysql> SELECT * FROM pet WHERE name LIKE ‘%w%‘;

To find names containing exactly five characters, use five instances of the “_” pattern character:

mysql> SELECT * FROM pet WHERE name LIKE ‘_____‘;

To demonstrate how extended regular expressions work, the LIKE queries shown previously are rewritten here to use REGEXP.

To find names beginning with “b”, use “^” to match the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP ‘^b‘;

If you really want to force a REGEXP comparison to be case sensitive, use the BINARY keyword to make one of the strings a binary string. This query matches only lowercase “b” at the beginning of a name:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY ‘^b‘;

To find names ending with “fy”, use “$” to match the end of the name:

mysql> SELECT * FROM pet WHERE name REGEXP ‘fy$‘;

To find names containing a “w”, use this query:

mysql> SELECT * FROM pet WHERE name REGEXP ‘w‘;

To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the name, and five instances of “.” in between:

mysql> SELECT * FROM pet WHERE name REGEXP ‘^.....$‘;

You could also write the previous query using the {n} (“repeat-n-times”) operator:

mysql> SELECT * FROM pet WHERE name REGEXP ‘^.{5}$‘;

Section 12.5.2, “Regular Expressions”, provides more information about the syntax for regular expressions.

8. 列的统计3.3.4.8 Counting Rows

Databases are often used to answer the question, “How often does a certain type of data occur in a table?”

COUNT(*) counts the number of rows, so the query to count your animals looks like this:

mysql> SELECT COUNT(*) FROM pet;

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;

Number of animals per combination of species and sex:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

mysql> SELECT species, sex, COUNT(*) FROM pet    -> WHERE species = ‘dog‘ OR species = ‘cat‘    -> GROUP BY species, sex;mysql> SELECT species, sex, COUNT(*) FROM pet    -> WHERE sex IS NOT NULL    -> GROUP BY species, sex;

If you name columns to select in addition to the COUNT() value, a GROUP BY clause should be present that names those same columns. Otherwise, the following occurs:

If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs:

mysql> SET sql_mode = ‘ONLY_FULL_GROUP_BY‘;Query OK, 0 rows affected (0.00 sec)mysql> SELECT owner, COUNT(*) FROM pet;ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...)with no GROUP columns is illegal if there is no GROUP BY clause

If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is indeterminate. The server is free to select the value from any row:

mysql> SET sql_mode = ‘‘;Query OK, 0 rows affected (0.00 sec)mysql> SELECT owner, COUNT(*) FROM pet;+--------+----------+| owner  | COUNT(*) |+--------+----------+| Harold |        8 | +--------+----------+1 row in set (0.00 sec)

See also Section 12.19.3, “MySQL Handling of GROUP BY”.

9. 使用多个表。3.3.4.9 Using More Than one Table

The pet table keeps track of which pets you have. If you want to record other information about them, such as events in their lives like visits to the vet or when litters are born, you need another table.

Given these considerations, the CREATE TABLE statement for the event table might look like this:

mysql> SHOW DATABASES;mysql> SELECT DATABASE();mysql> USE menagerie;mysql> SHOW TABLES;mysql> CREATE TABLE event (name VARCHAR(20), date DATE,    -> type VARCHAR(15), remark VARCHAR(255));

As with the pet table, it is easiest to load the initial records by creating a tab-delimited text file containing the following information.

name	date	type	remark
Fluffy	1995-05-15	litter	4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Gave him a new chew toy
Claws	1998-03-17	birthday	Gave him a new flea collar
Whistler	1998-12-09	birthday	First birthday

我的event.txt(注意，Fang那行的 remark \N之后多了一个空格，造成LOAD DATA将其识别为N空格，而不是NULL):

Fluffy    1995-05-15    litter    4 kittens, 3 female, 1 maleBuffy    1993-06-23    litter    5 puppies, 2 female, 3 maleBuffy    1994-06-19    litter    3 puppies, 3 femaleChirpy    1999-03-21    vet    needed beak straightenedSlim    1997-08-03    vet    broken ribBowser    1991-10-12    kennel    \NFang    1991-10-12    kennel    \N Fang    1998-08-28    birthday    Gave him a new chew toyClaws    1998-03-17    birthday    Gave him a new flea collarWhistler    1998-12-09    birthday    First birthday

Load the records like this:

mysql> LOAD DATA LOCAL INFILE ‘/home/hostName/test/event.txt‘ INTO TABLE event;mysql> SELECT * FROM event;+----------+------------+----------+-----------------------------+| name     | date       | type     | remark                      |+----------+------------+----------+-----------------------------+| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male || Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male || Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         || Chirpy   | 1999-03-21 | vet      | needed beak straightened    || Slim     | 1997-08-03 | vet      | broken rib                  || Bowser   | 1991-10-12 | kennel   | NULL                        || Fang     | 1991-10-12 | kennel   | N                           || Fang     | 1998-08-28 | birthday | Gave him a new chew toy     || Claws    | 1998-03-17 | birthday | Gave him a new flea collar  || Whistler | 1998-12-09 | birthday | First birthday              |+----------+------------+----------+-----------------------------+mysql> UPDATE event SET remark = NULL WHERE name = ‘Fang‘ AND type = ‘kennel‘;mysql> SELECT * FROM event;+----------+------------+----------+-----------------------------+| name     | date       | type     | remark                      |+----------+------------+----------+-----------------------------+| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male || Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male || Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         || Chirpy   | 1999-03-21 | vet      | needed beak straightened    || Slim     | 1997-08-03 | vet      | broken rib                  || Bowser   | 1991-10-12 | kennel   | NULL                        || Fang     | 1991-10-12 | kennel   | NULL                        || Fang     | 1998-08-28 | birthday | Gave him a new chew toy     || Claws    | 1998-03-17 | birthday | Gave him a new flea collar  || Whistler | 1998-12-09 | birthday | First birthday              |+----------+------------+----------+-----------------------------+

Suppose that you want to find out the ages at which each pet had its litters.

mysql> SELECT pet.name,    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,    -> remark    -> FROM pet INNER JOIN event    ->   ON pet.name = event.name    -> WHERE event.type = ‘litter‘;+--------+------+-----------------------------+| name   | age  | remark                      |+--------+------+-----------------------------+| Fluffy |    2 | 4 kittens, 3 female, 1 male || Buffy  |    4 | 5 puppies, 2 female, 3 male || Buffy  |    5 | 3 puppies, 3 female         |+--------+------+-----------------------------+

You need not have two different tables to perform a join. Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table. For example, to find breeding pairs among your pets, you can join the pet table with itself to produce candidate pairs of males and females of like species:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species    -> FROM pet AS p1 INNER JOIN pet AS p2    ->   ON p1.species = p2.species AND p1.sex = ‘f‘ AND p2.sex = ‘m‘;

3.4 Getting Information About Databases and Tables

What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)?

You have previously seen SHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || menagerie          || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| menagerie  |+------------+

To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this command:

mysql> SHOW TABLES;+---------------------+| Tables_in_menagerie |+---------------------+| event               || pet                 |+---------------------+

If you want to find out about the structure of a table, the DESCRIBE statement is useful; it displays information about each of a table‘s columns:

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    |       |+---------+-------------+------+-----+---------+-------+

3.5 Using mysql in Batch Mode

In the previous sections, you used mysql interactively to enter queries and view the results. You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:

shell> mysql -h host -u user -p < batch-fileEnter password: ********

我的batch-file：select.batchmode

SHOW DATABASES;SELECT DATABASE();USE menagerie;SHOW TABLES;SELECT DISTINCT species FROM pet;

$ mysql -u root -p < /home/hostName/test/select.batchmode Enter password: Databaseinformation_schemamenageriemysqlperformance_schematestDATABASE()NULLTables_in_menagerieeventpetspeciescatdogbirdsnakehamsterovonel@ovonel-usa:~$ mysql -u root -p -t < /home/hostName/test/select.batchmode    ###-t互动输出格式Enter password: +--------------------+| Database           |+--------------------+| information_schema || menagerie          || mysql              || performance_schema || test               |+--------------------+.......$ mysql -u root -p -t -vvv < /home/ovonel/test/select.batchmode      ###-vvv显示执行的命令Enter password: --------------SHOW DATABASES--------------+--------------------+| Database           |+--------------------+| information_schema || menagerie          || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)
.........

3.6 Examples of Common Queries

Here are examples of how to solve some common problems with MySQL.

Some of the examples use the table shop to hold the price of each article (item number) for certain traders (dealers). Supposing that each trader has a single fixed price per article, then (article商品, dealer商人) is a primary key for the records.

Start the command-line tool mysql and select a database:

首先创建一个market

$ mysql -u root -pmysql> CREATE DATABASE market;mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || market             || menagerie          || mysql              || performance_schema || test               |+--------------------+mysql> USE market;Database changedmysql> SHOW TABLES;Empty set (0.00 sec)
mysql> quit

开始操作：

$ mysql -u root -p market    ###使用market数据库Enter password: mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| market     |+------------+1 row in set (0.00 sec)mysql> CREATE TABLE shop (    ->     article INT(4) UNSIGNED ZEROFILL DEFAULT ‘0000‘ NOT NULL,    ->     dealer  CHAR(20)                 DEFAULT ‘‘     NOT NULL,    ->     price   DOUBLE(16,2)             DEFAULT ‘0.00‘ NOT NULL,    ->     PRIMARY KEY(article, dealer));Query OK, 0 rows affected (0.33 sec)mysql> INSERT INTO shop VALUES    ->     (1,‘A‘,3.45),(1,‘B‘,3.99),(2,‘A‘,10.99),(3,‘B‘,1.45),    ->     (3,‘C‘,1.69),(3,‘D‘,1.25),(4,‘D‘,19.95);Query OK, 7 rows affected (0.10 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> SELECT * FROM shop;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0001 | A      |  3.45 ||    0001 | B      |  3.99 ||    0002 | A      | 10.99 ||    0003 | B      |  1.45 ||    0003 | C      |  1.69 ||    0003 | D      |  1.25 ||    0004 | D      | 19.95 |+---------+--------+-------+7 rows in set (0.01 sec)

3.6.1 The Maximum Value for a Column “What is the highest item number?”

mysql> SELECT MAX(article) AS article FROM shop;+---------+| article |+---------+|       4 |+---------+

3.6.2 The Row Holding the Maximum of a Certain Column

Task: Find the number, dealer, and price of the most expensive article.

SELECT article, dealer, priceFROM   shopWHERE  price=(SELECT MAX(price) FROM shop);+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0004 | D      | 19.95 |+---------+--------+-------+

其他两种方法LEFT JOIN, LIMIT：

mysql> SELECT s1.article, s1.dealer, s1.price    -> FROM shop s1    -> LEFT JOIN shop s2 ON s1.price < s2.price    -> WHERE s2.article IS NULL;mysql> SELECT article, dealer, price    -> FROM shop    -> ORDER BY price DESC    -> LIMIT 1;

3.6.3 Maximum of Column per Group

Task: Find the highest price per article.

mysql> SELECT article, MAX(price) AS price    -> FROM   shop    -> GROUP BY article;+---------+-------+| article | price |+---------+-------+|    0001 |  3.99 ||    0002 | 10.99 ||    0003 |  1.69 ||    0004 | 19.95 |+---------+-------+

3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

mysql> SELECT article, dealer, price    -> FROM   shop s1    -> WHERE  price=(SELECT MAX(s2.price)    ->               FROM shop s2    ->               WHERE s1.article = s2.article);+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0001 | B      |  3.99 ||    0002 | A      | 10.99 ||    0003 | C      |  1.69 ||    0004 | D      | 19.95 |+---------+--------+-------+

The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.10.7, “Correlated Subqueries”). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause or a LEFT JOIN.

mysql> SELECT s1.article, dealer, s1.price    -> FROM shop s1    -> JOIN (    ->   SELECT article, MAX(price) AS price    ->   FROM shop    ->   GROUP BY article) AS s2    ->   ON s1.article = s2.article AND s1.price = s2.price;mysql> SELECT s1.article, s1.dealer, s1.price    -> FROM shop s1    -> LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price    -> WHERE s2.article IS NULL;

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and the s2 rows values will be NULL. See Section 13.2.9.2, “JOIN Syntax”.

3.6.5 Using User-Defined Variables

You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See Section 9.4, “User-Defined Variables”.)

For example, to find the articles with the highest and lowest price you can do this:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;+------------------------+------------------------+| @min_price:=MIN(price) | @max_price:=MAX(price) |+------------------------+------------------------+|                   1.25 |                  19.95 |+------------------------+------------------------+1 row in set (0.00 sec)mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0003 | D      |  1.25 ||    0004 | D      | 19.95 |+---------+--------+-------+

Note

It is also possible to store the name of a database object such as a table or a column in a user variable and then to use this variable in an SQL statement; however, this requires the use of a prepared statement. See Section 13.5, “SQL Syntax for Prepared Statements”, for more information.

3.6.6 Using Foreign Keys

In MySQL, InnoDB tables support checking of foreign key constraints. See Chapter 14, The InnoDB Storage Engine, and Section 1.8.2.4, “Foreign Key Differences”.

It is extremely important to realize when using this syntax that:

MySQL does not perform any sort of CHECK to make sure that col_name actually exists in tbl_name (or even that tbl_name itself exists).
MySQL does not perform any sort of action on tbl_name such as deleting rows in response to actions taken on rows in the table which you are defining; in other words, this syntax induces no ON DELETE or ON UPDATE behavior whatsoever. (Although you can write an ON DELETE or ON UPDATE clause as part of the REFERENCES clause, it is also ignored.)
This syntax creates a column; it does not create any sort of index or key.

You can use a column so created as a join column, as shown here:

$ mysql -u root -pEnter password: mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || market             || menagerie          || mysql              || performance_schema || test               |+--------------------+mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| NULL       |+------------+1 row in set (0.00 sec)mysql> USE market;mysql> SHOW TABLES;CREATE TABLE person (    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,    name CHAR(60) NOT NULL,    PRIMARY KEY (id));mysql> DESCRIBE person;+-------+----------------------+------+-----+---------+----------------+| Field | Type                 | Null | Key | Default | Extra          |+-------+----------------------+------+-----+---------+----------------+| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || name  | char(60)             | NO   |     | NULL    |                |+-------+----------------------+------+-----+---------+----------------+CREATE TABLE shirt (    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,    style ENUM(‘t-shirt‘, ‘polo‘, ‘dress‘) NOT NULL,    color ENUM(‘red‘, ‘blue‘, ‘orange‘, ‘white‘, ‘black‘) NOT NULL,    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),    PRIMARY KEY (id));mysql> DESCRIBE shirt;+-------+---------------------------------------------+------+-----+---------+----------------+| Field | Type                                        | Null | Key | Default | Extra          |+-------+---------------------------------------------+------+-----+---------+----------------+| id    | smallint(5) unsigned                        | NO   | PRI | NULL    | auto_increment || style | enum(‘t-shirt‘,‘polo‘,‘dress‘)              | NO   |     | NULL    |                || color | enum(‘red‘,‘blue‘,‘orange‘,‘white‘,‘black‘) | NO   |     | NULL    |                || owner | smallint(5) unsigned                        | NO   |     | NULL    |                |+-------+---------------------------------------------+------+-----+---------+----------------+INSERT INTO person VALUES (NULL, ‘Antonio Paz‘);mysql> SELECT * FROM person;+----+-------------+| id | name        |+----+-------------+|  1 | Antonio Paz |+----+-------------+mysql> SELECT @last := LAST_INSERT_ID();+---------------------------+| @last := LAST_INSERT_ID() |+---------------------------+|                         1 |+---------------------------+INSERT INTO shirt VALUES(NULL, ‘polo‘, ‘blue‘, @last),(NULL, ‘dress‘, ‘white‘, @last),(NULL, ‘t-shirt‘, ‘blue‘, @last);mysql> SELECT * FROM shirt;+----+---------+-------+-------+| id | style   | color | owner |+----+---------+-------+-------+|  1 | polo    | blue  |     1 ||  2 | dress   | white |     1 ||  3 | t-shirt | blue  |     1 |+----+---------+-------+-------+INSERT INTO person VALUES (NULL, ‘Lilliana Angelovska‘);mysql> SELECT * FROM person;+----+---------------------+| id | name                |+----+---------------------+|  1 | Antonio Paz         ||  2 | Lilliana Angelovska |+----+---------------------+mysql> SELECT @last := LAST_INSERT_ID();+---------------------------+| @last := LAST_INSERT_ID() |+---------------------------+|                         2 |+---------------------------+INSERT INTO shirt VALUES(NULL, ‘dress‘, ‘orange‘, @last),(NULL, ‘polo‘, ‘red‘, @last),(NULL, ‘dress‘, ‘blue‘, @last),(NULL, ‘t-shirt‘, ‘white‘, @last);mysql> SELECT * FROM shirt;+----+---------+--------+-------+| id | style   | color  | owner |+----+---------+--------+-------+|  1 | polo    | blue   |     1 ||  2 | dress   | white  |     1 ||  3 | t-shirt | blue   |     1 ||  4 | dress   | orange |     2 ||  5 | polo    | red    |     2 ||  6 | dress   | blue   |     2 ||  7 | t-shirt | white  |     2 |+----+---------+--------+-------+mysql> SELECT s.* FROM person p INNER JOIN shirt s    ->    ON s.owner = p.id    ->  WHERE p.name LIKE ‘Lilliana%‘    ->    AND s.color <> ‘white‘;+----+-------+--------+-------+| id | style | color  | owner |+----+-------+--------+-------+|  4 | dress | orange |     2 ||  5 | polo  | red    |     2 ||  6 | dress | blue   |     2 |+----+-------+--------+-------+

When used in this fashion, the REFERENCES clause is not displayed in the output of SHOW CREATE TABLE or DESCRIBE:

mysql> SHOW CREATE TABLE shirt\G*************************** 1. row ***************************       Table: shirtCreate Table: CREATE TABLE `shirt` (  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `style` enum(‘t-shirt‘,‘polo‘,‘dress‘) NOT NULL,  `color` enum(‘red‘,‘blue‘,‘orange‘,‘white‘,‘black‘) NOT NULL,  `owner` smallint(5) unsigned NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin11 row in set (0.03 sec)

（完）

3.6.7 Searching on Two Keys

3.6.8 Calculating Visits Per Day

3.6.9 Using AUTO_INCREMENT

3.7 Using MySQL with Apache

There are programs that let you authenticate your users from a MySQL database and also let you write your log files into a MySQL table.

You can change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file:

LogFormat         "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",          \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

To load a log file in that format into MySQL, you can use a statement something like this:

LOAD DATA INFILE ‘/local/access_log‘ INTO TABLE tbl_nameFIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘ ESCAPED BY ‘\\‘

The named table should be created to have columns that correspond to those that the LogFormat line writes to the log file.

MySQL入门手册

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > MySQL入门手册

MySQL入门手册

3.7 Using MySQL with Apache

看完仍有疑问？有类似问题直接问程序猿