本文內容摘自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 -hEnter password:host-uuser-p********
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 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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
-> \c
mysql>
The following table shows each of the prompts you may see and summarizes what they mean about the state that mysql is in.
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 test Database 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 menagerie Database 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>Importantmysql -hEnter password:host-uuser-p menagerie********
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.
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 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server 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 its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW TABLES; ERROR 1046 (3D000): No database selected mysql> 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 menagerie Database changed mysql> 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.)
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 \N Claws Gwen cat m 1994-03-17 \N Buffy Haroid cat f 1989-05-13 \N Fang Benny dog m 1990-08-27 \N Browser Diane dog m 1979-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 \N Whistler Gwen bird \N 1997-12-09 \N Slim Benny snake m 1996-04-29 \N
To load the text file pet.txt into the pet table, use this statement:(這裡給出完整的操作過程)
$ mysql -u root -p mysql> SHOW DATABASES; mysql> SELECT DATABASE(); mysql> USE menagerie mysql> 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: 0 mysql>
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:
SELECTwhat_to_selectFROMwhich_tableWHEREconditions_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 { (“repeat-n}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.
我的event.txt(注意，Fang那行的 remark \N之後多了一個空格，造成LOAD DATA將其識別為N空格，而不是NULL):
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 \N 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
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 -hEnter password:host-uuser-p <batch-file********
我的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: Database information_schema menagerie mysql performance_schema test DATABASE() NULL Tables_in_menagerie event pet species cat dog bird snake hamster ovonel@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 -p mysql> CREATE DATABASE market; mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | market | | menagerie | | mysql | | performance_schema | | test | +--------------------+ mysql> USE market; Database changed mysql> 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: 0
mysql> 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, price FROM shop WHERE 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 -p
Enter 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: shirt
Create 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=latin1
1 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 TABLEtbl_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.
一、SQL速成
以下是一些重要的SQL快速參考，有關SQL的語法和在標准SQL上增加的特性，請查詢MySQL手冊。
1．創建表
表是數據庫的最基本元素之一，表與表之間可以相互獨立，也可以相互關聯。創建表的基本語法如下：
create table table_name
(column_name datatype {identity |null|not null},
…)
其中參數table_name和column_name必須滿足用戶數據庫中的識別器(identifier)的要求，參數datatype是一個標准的SQL類型或由用戶數據庫提供的類型。用戶要使用non-null從句為各字段輸入數據。
create table還有一些其他選項，如創建臨時表和使用select子句從其他的表中讀取某些字段組成新表等。還有，在創建表是可用PRIMARY KEY、KEY、INDEX等標識符設定某些字段為主鍵或索引等。
書寫上要注意：
在一對圓括號裡的列出完整的字段清單。
字段名間用逗號隔開。
字段名間的逗號後要加一個空格。
最後一個字段名後不用逗號。
所有的SQL陳述都以分號";"結束。
例：
mysql> CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
2．創建索引
索引用於對數據庫的查詢。一般數據庫建有多種索引方案，每種方案都精於某一特定的查詢類。索引可以加速對數據庫的查詢過程。創建索引的基本語法如下：
create index index_name
on table_name (col_name[(length)],... )
例：
mysql> CREATE INDEX part_of_name ON customer (name(10));
3．改變表結構
在數據庫的使用過程中，有時需要改變它的表結構，包括改變字段名，甚至改變不同數據庫字段間的關系。可以實現上述改變的命令是alter，其基本語法如下：
alter table table_name alter_spec [, alter_spec ...]
例：
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
4．刪除數據對象
很多數據庫是動態使用的，有時可能需要刪除某個表或索引。大多數數據庫對象可以下面的命令刪除：
drop object_name
mysql> DROP TABLE tb1;
5．執行查詢
查詢是使用最多的SQL命令。查詢數據庫需要憑借結構、索引和字段類型等因素。大多數數據庫含有一個優化器(optimizer)，把用戶的查詢語句轉換成可選的形式，以提高查詢效率。
值得注意的是MySQL不支持SQL92標准的嵌套的where子句，即它只支持一個where子句。其基本語法如下：
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]
select_expression,... [INTO {OUTFILE | D......余下全文>>
可以直接看官方手冊，中文版的是 mysql 5.1參考手冊，直接去360就能搜到
