Information Security & US & Life & Love & Fashion & Hacking & Passion

Java代码

1. CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
2. day INT(2) UNSIGNED ZEROFILL);
3. INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
4. (2000,2,3),(2000,2,3);
5. 示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：
7. SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
8. GROUP BY year,month;
9. 将返回：
11. +------+-------+------+
12. | year | month | days |
13. +------+-------+------+
14. | 2000 | 01 | 3 |
15. | 2000 | 02 | 2 |
16. +------+-------+------+
17. 该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,3),(2000,2,3);
示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;
将返回：

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+
该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See Section 8.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;mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0003 | D      |  1.25 ||    0004 | D      | 19.95 |+---------+--------+-------+

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)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports.

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> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

If you have not yet selected any database, the result is NULL.

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

The name of the column in the output produced by this statement is always Tables_in_db_name, where db_name is the name of the database. See Section 12.4.5.38, “SHOW TABLES Syntax”, for more information.

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

Field indicates the column name, Type is the data type for the column, NULL indicates whether the column can contain NULL values, Key indicates whether the column is indexed, and Default specifies the column's default value. Extra displays special information about columns: If a column was created with the AUTO_INCREMENT option, the value will be auto_increment rather than empty.

DESC is a short form of DESCRIBE. See Section 12.8.1, “DESCRIBE Syntax”, for more information.

You can obtain the CREATE TABLE statement necessary to create an existing table using the SHOW CREATE TABLE statement. See Section 12.4.5.12, “SHOW CREATE TABLE Syntax”.