MySQL 官方文档学习笔记——教程
5

mysql简易教程(官方文档学习笔记)

查询版本号

mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| VERSION()    | CURRENT_DATE |
+--------------+--------------+
| 5.7.1-m4-log | 2012-12-25   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

分配用户权限

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

远程连接

您的数据库只需要创建一次,但您必须在每次开始mysql 会话时选择它。您可以通过发布USE如示例中所示的语句来完成此操作 。或者,您可以在调用mysql时在命令行上选择数据库。只需在您可能需要提供的任何连接参数之后指定其名称即可。例如:

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

创建数据库

create database python_23_1 charset=utf8;

创建表格

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

插入数据

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

更新数据

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

查询语句

# 条件查询
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

# 条件查询
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

# 选择字段显示
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

# 去重
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

mysql> SELECT name, species, birth FROM pet
    -> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+

查询排序

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

# 默认排序顺序是升序排列,最小值排在第一位。要按反向(降序)顺序排序,请将DESC关键字添加到要排序 的列的名称中
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

"""您可以对多个列进行排序,并且可以按不同方向排序不同的列。例如,要按照升序排列动物类型,
然后按降序排列动物类型中的出生日期(首先是最年轻的动物),"""
mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

计算日期

# 要确定每只宠物有多少岁,请使用该 TIMESTAMPDIFF()功能。它的论据是你想要表达结果的单位,
# 以及两个取得差异的日期。以下查询显示了每个宠物的出生日期,当前日期和年数
mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+
null
# 查询使用death IS NOT NULL而不是death <> NULL因为 NULL是使用通常的比较运算符无法比较的特殊值。
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  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+
只显示年份、月份、日期

如果你想知道下个月哪些动物有生日,该怎么办?对于这种计算,年和日是无关紧要的; 你只是想提取birth列的月份部分 。MySQL提供了用于提取日期的部分,如一些功能 YEAR(), MONTH()和 DAYOFMONTH()。 MONTH()这里是合适的功能。要查看它的工作原理,请运行一个简单的查询,以显示两者的值birth和 MONTH(birth):

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

模式匹配

为了找到名字开头b:

mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

要查找以下列结尾的名字fy:

mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

查找包含以下内容的名称w

mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

要查找包含正好包含五个字符的名称,请使用_模式字符的五个实例:

mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

使用正则匹配

以下列表描述了扩展正则表达式的一些特征:

  1. . 匹配任何单个字符。

  2. 一个字符类[...]匹配括号内的任何字符。例如, [abc]匹配a, b或c。要命名一系列字符,请使用短划线。[a-z] 匹配任何字母,而[0-9] 匹配任何数字。

  3. 匹配零个或多个前面的事物的实例。例如,x 匹配任意数量的x字符, [0-9]匹配任意数量的数字,并.匹配任意数量的任何数字。

  4. 如果模式匹配正在测试的值中的任何位置,则正则表达式模式匹配会成功。(这与LIKE模式匹配不同,只有模式匹配整个值才能成功。)

  5. 要锚定一个模式,以便它必须匹配被测试值^的开始或$结束,请在模式的开始或结尾使用。

  6. 为了演示扩展正则表达式的工作原理,LIKE先前显示的 查询在这里被重写以供使用REGEXP。

要查找以名字开头的名字b,请使用 ^以匹配名称的开头:

mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

要强制REGEXP比较区分大小写,请使用BINARY 关键字使其中一个字符串成为二进制字符串。该查询仅b在名称的开头匹配小写字母:

SELECT * FROM pet WHERE name REGEXP BINARY '^b';

要查找以结尾的名字fy,请使用 $以匹配名称的结尾:

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

要查找包含a的名称w,请使用以下查询:

mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

要查找包含正好包含五个字符的名称,请使用 ^$匹配名称的开头和结尾以及.中间的五个实例 :

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

您还可以使用 (“ repeat- times ”)运算符来编写以前的查询 : {n}n

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

使用count计数

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

此前,您检索了拥有宠物的人的姓名。COUNT()如果你想找出每个拥有者有多少宠物,你可以使用:

查询用于GROUP BY将每个记录分组owner。使用的 COUNT()结合 GROUP BY是在各种分组表征您的数据非常有用。以下示例显示了进行动物普查操作的不同方法。

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

每种动物的数量:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

每个性别的动物数量:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

每种物种和性别组合的动物数量:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

使用时无需检索整个表格 COUNT()。例如,以前的查询只在狗和猫上执行,看起来像这样:

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

或者,如果你只想知道每个性别的动物数量:

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

多张表

创建表格

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

重txt文件中添加数据 注意:txt文件中的日期必须是: 1993-06-23格式

 LOAD DATA LOCAL INFILE '路径/pet.txt' INTO TABLE event;

联表查询

mysql> SELECT pet.name,
    -> TIMESTAMPDIFF(YEAR,birth,date) 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         |
+--------+------+-----------------------------+

这个查询有几点需要注意:

  • 该FROM子句加入两个表,因为查询需要从两者中提取信息。

  • 从多个表合并(加入)信息时,您需要指定一个表中的记录如何与另一个表中的记录相匹配。这很容易,因为他们都有name专栏。该查询使用一个ON子句根据这些name值匹配两个表中的记录。

    该查询使用一个INNER JOIN来组合这些表。一个INNER JOIN或者从表许可证行当且仅当两个表满> 足所规定的条件,以显示在结果 ON子句。在这个例子中, ON子句指定 name列中的 pet表必须的匹配 name列 event表。如果一个名称出现在一个表中,而另一个不出现,则该行不会出现在结果中,因为该ON 子句中的条件失败。

  • 因为name列出现在两个表中,所以在引用列时必须明确指出哪个表是您的意思。这是通过将表名添加到列名中来完成的。

您不需要有两个不同的表来执行连接。如果要将表中的记录与同一表中的其他记录进行比较,有时将自己的表加入表是非常有用的。例如,要在您的宠物中找到育种配对,您可以将其pet与自己一起加入表以生成候选对的类似物种的男性和女性:

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';
+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | cat     |
| Buffy  | f    | Fang   | m    | dog     |
| Buffy  | f    | Bowser | m    | dog     |
+--------+------+--------+------+---------+

获取有关数据库和表格的信息

要找出当前选择哪个数据库,请使用以下 DATABASE()功能:

如果你还没有选择任何数据库,结果是 NULL。

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

要找出默认数据库包含哪些表(例如,当您不确定表的名称时),请使用以下语句:

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

如果你想了解一张表的结构,这个 DESCRIBE陈述是有用的; 它显示有关每个表格列的信息:
使用desc也可以

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

批量处理导入导出

您使用 交互式mysql来输入语句并查看结果。你也可以在批处理模式下运行mysql。为此,将要运行的语句放在一个文件中,然后告诉 mysql从文件读取其输入:

shell> mysql < batch-file

如果你在Windows下运行mysql,并在文件中有一些导致问题的特殊字符,你可以这样做:

C:\> mysql -e "source batch-file"

如果您需要在命令行中指定连接参数,则该命令可能如下所示:

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

当你以这种方式使用mysql时,你正在创建一个脚本文件,然后执行脚本。

如果您希望脚本继续运行,即使其中的某些语句出现错误,您也应该使用 --force命令行选项。

为什么要使用脚本?这有几个原因:

  • 如果您反复运行查询(例如每天或每周),将其作为脚本可以避免每次执行时重新输入查询。

  • 您可以通过复制和编辑脚本文件从现有的查询生成新的查询。

  • 批处理模式在开发查询时也很有用,特别是对于多行语句或多语句序列。如果你犯了一个错误,你不必重新键入一切。只需编辑你的脚本来纠正错误,然后告诉mysql再次执行它。

  • 如果您的查询产生大量输出,则可以通过传呼机运行输出,而不是通过滚动屏幕的顶部来滚动输出:
shell> mysql < batch-file | more
  • 您可以捕获文件中的输出进行进一步处理:
shell> mysql < batch-file > mysql.out
  • 您可以将脚本分发给其他人,以便他们也可以运行这些语句。

  • 有些情况下不允许交互式使用,例如,当您从cron作业运行查询时。在这种情况下,您必须使用批处理模式。

在批处理模式下 运行mysql时,默认输出格式与交互式使用时不同(更简洁) 。例如,SELECT DISTINCT species FROM pet当mysql以交互方式运行时,输出如下所示 :

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

在批处理模式下,输出如下所示:

species
bird
cat
dog
hamster
snake

如果您想以批处理模式获得交互式输出格式,请使用mysql -t。要向输出回显执行的语句,请使用mysql -v。

您还可以使用命令或 命令从mysql提示符使用脚本: source.

mysql> source filename;
mysql> \. filename

常见查询的例子

启动命令行工具mysql并选择一个数据库:

shell> mysql your-database-name

您可以使用以下语句创建并填充示例表:

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));
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);

发表声明后,表格应具有以下内容

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

列的最大值

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

查询指定字段最大值的行

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

其他解决方案是使用a LEFT JOIN或按照价格降序对所有行进行排序,并使用MySQL特定的LIMIT子句仅获得第一行:

# 左连接查询
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

# 排序
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

查找每篇文章的最高价格。

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

对于每篇文章,找到价格最昂贵的经销商或经销商。

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 |
+---------+--------+-------+
  • 上面的让人难以理解,这个更容易理解一点
SELECT article, dealer, price 
FROM   shop s1 
WHERE  price in (select max(price) from shop group by article);

使用自定定义的变量

要查找价格最高和最低的商品,可以这样做:

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.01 sec)

mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
2 rows in set (0.00 sec)

每天计算访问次数

以下示例显示了如何使用位组函数来计算用户访问网页每月的天数

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,23),(2000,2,23);

要确定这些访问在每个月中有多少天不同,请使用以下查询:

mysql> select * from t1;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2000 |    01 |   01 |
| 2000 |    01 |   20 |
| 2000 |    01 |   30 |
| 2000 |    02 |   02 |
| 2000 |    02 |   23 |
| 2000 |    02 |   23 |
+------+-------+------+
6 rows in set (0.00 sec)

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 |
+------+-------+------+   
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

  • 请注意单词拼写,以及中英文排版,参考此页
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 Emoji 自动补全来咯,可用的 Emoji 请见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!