板凳-------Mysql cookbook学习 （十一--------8)
12.11 使用连接控制查询输出的顺序
mysql> select * from driver_log order by rec_id;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2006-08-30 | 152 |
| 2 | Suzi | 2006-08-29 | 391 |
| 3 | Henry | 2006-08-29 | 300 |
| 4 | Henry | 2006-08-27 | 96 |
| 5 | Ben | 2006-08-29 | 131 |
| 6 | Henry | 2006-08-26 | 115 |
| 7 | Suzi | 2006-09-02 | 502 |
| 8 | Henry | 2006-09-01 | 197 |
| 9 | Ben | 2006-09-02 | 79 |
| 10 | Henry | 2006-08-30 | 203 |
+--------+-------+------------+-------+
10 rows in set (0.00 sec)mysql> create table tmpp4-> select name, sum(miles) as driver_miles from driver_log group by name;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from tmpp4 order by driver_miles desc;
+-------+--------------+
| name | driver_miles |
+-------+--------------+
| Henry | 911 |
| Suzi | 893 |
| Ben | 362 |
+-------+--------------+
3 rows in set (0.00 sec)mysql> select tmpp4.driver_miles, driver_log.*-> from driver_log inner join tmpp4-> on driver_log.name = tmpp4.name-> order by tmpp4.driver_miles desc, driver_log.trav_date;
+--------------+--------+-------+------------+-------+
| driver_miles | rec_id | name | trav_date | miles |
+--------------+--------+-------+------------+-------+
| 911 | 6 | Henry | 2006-08-26 | 115 |
| 911 | 4 | Henry | 2006-08-27 | 96 |
| 911 | 3 | Henry | 2006-08-29 | 300 |
| 911 | 10 | Henry | 2006-08-30 | 203 |
| 911 | 8 | Henry | 2006-09-01 | 197 |
| 893 | 2 | Suzi | 2006-08-29 | 391 |
| 893 | 7 | Suzi | 2006-09-02 | 502 |
| 362 | 5 | Ben | 2006-08-29 | 131 |
| 362 | 1 | Ben | 2006-08-30 | 152 |
| 362 | 9 | Ben | 2006-09-02 | 79 |
+--------------+--------+-------+------------+-------+
10 rows in set (0.01 sec)mysql> SELECT tmpp4.driver_miles, driver_log.*-> FROM driver_log-> INNER JOIN (-> SELECT name, SUM(miles) AS driver_miles-> FROM driver_log-> GROUP BY name-> ) AS tmpp4-> ON driver_log.name = tmpp4.name -- 这里修正为 tmpp4.name-> ORDER BY tmpp4.driver_miles DESC, driver_log.trav_date;
+--------------+--------+-------+------------+-------+
| driver_miles | rec_id | name | trav_date | miles |
+--------------+--------+-------+------------+-------+
| 911 | 6 | Henry | 2006-08-26 | 115 |
| 911 | 4 | Henry | 2006-08-27 | 96 |
| 911 | 3 | Henry | 2006-08-29 | 300 |
| 911 | 10 | Henry | 2006-08-30 | 203 |
| 911 | 8 | Henry | 2006-09-01 | 197 |
| 893 | 2 | Suzi | 2006-08-29 | 391 |
| 893 | 7 | Suzi | 2006-09-02 | 502 |
| 362 | 5 | Ben | 2006-08-29 | 131 |
| 362 | 1 | Ben | 2006-08-30 | 152 |
| 362 | 9 | Ben | 2006-09-02 | 79 |
+--------------+--------+-------+------------+-------+
10 rows in set (0.00 sec)12.12 在单个查询中整合几个结果集```sql
mysql> -- 正确的删除表命令
mysql> DROP TABLE IF EXISTS prospect;
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> -- 然后重新创建表
mysql> CREATE TABLE prospect (-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> fname VARCHAR(50) NOT NULL, -- 名字-> lname VARCHAR(50) NOT NULL, -- 姓氏-> addr VARCHAR(255) NOT NULL, -- 地址-> PRIMARY KEY (id)-> );
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> -- 插入示例数据
mysql> INSERT INTO prospect (fname, lname, addr) VALUES-> ('Peter', 'Jones', '482 Rush St., Apt. 402'),-> ('John', 'Smith', '123 Main St.'),-> ('Mary', 'Johnson', '789 Oak Ave., Suite 100');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql>
mysql> -- 验证数据
mysql> SELECT * FROM prospect;
+----+-------+---------+-------------------------+
| id | fname | lname | addr |
+----+-------+---------+-------------------------+
| 1 | Peter | Jones | 482 Rush St., Apt. 402 |
| 2 | John | Smith | 123 Main St. |
| 3 | Mary | Johnson | 789 Oak Ave., Suite 100 |
+----+-------+---------+-------------------------+
3 rows in set (0.00 sec)mysql> select * from customer;
ERROR 1146 (42S02): Table 'cookbook.customer' doesn't exist
mysql> CREATE TABLE customer (-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> last_name VARCHAR(50) NOT NULL, -- 姓氏-> first_name VARCHAR(50) NOT NULL, -- 名字-> address VARCHAR(255) NOT NULL, -- 地址-> PRIMARY KEY (id)-> );
Query OK, 0 rows affected (0.08 sec)mysql> INSERT INTO customer (last_name, first_name, address) VALUES-> ('Peterson', 'Grace', '16055 Seminole Ave.'),-> ('Smith', 'John', '123 Main Street'),-> ('Johnson', 'Mary', '456 Oak Avenue'),-> ('Williams', 'Robert', '789 Pine Road'),-> ('Brown', 'Sarah', '321 Elm Street');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> select * from customer;
+----+-----------+------------+---------------------+
| id | last_name | first_name | address |
+----+-----------+------------+---------------------+
| 1 | Peterson | Grace | 16055 Seminole Ave. |
| 2 | Smith | John | 123 Main Street |
| 3 | Johnson | Mary | 456 Oak Avenue |
| 4 | Williams | Robert | 789 Pine Road |
| 5 | Brown | Sarah | 321 Elm Street |
+----+-----------+------------+---------------------+
5 rows in set (0.00 sec)mysql> CREATE TABLE vendor (-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> company VARCHAR(100) NOT NULL, -- 公司名称-> street VARCHAR(255) NOT NULL, -- 街道地址-> PRIMARY KEY (id)-> );
Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO vendor (company, street) VALUES-> ('ReddyParts, Inc.', '38 Industrial Blvd.'),-> ('TechSupply Co.', '125 Commerce Park Drive'),-> ('Global Components Ltd.', '2000 International Way'),-> ('Quality Materials Corp.', '550 Manufacturing Street'),-> ('Acme Distributors', '88 Wholesale Avenue');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> select * from vendor;
+----+-------------------------+--------------------------+
| id | company | street |
+----+-------------------------+--------------------------+
| 1 | ReddyParts, Inc. | 38 Industrial Blvd. |
| 2 | TechSupply Co. | 125 Commerce Park Drive |
| 3 | Global Components Ltd. | 2000 International Way |
| 4 | Quality Materials Corp. | 550 Manufacturing Street |
| 5 | Acme Distributors | 88 Wholesale Avenue |
+----+-------------------------+--------------------------+
5 rows in set (0.00 sec)mysql> select fname, lname, addr from prospect-> union-> select first_name, last_name, address from customer-> union-> select company, '', street from vendor;
+-------------------------+----------+--------------------------+
| fname | lname | addr |
+-------------------------+----------+--------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| John | Smith | 123 Main St. |
| Mary | Johnson | 789 Oak Ave., Suite 100 |
| Grace | Peterson | 16055 Seminole Ave. |
| John | Smith | 123 Main Street |
| Mary | Johnson | 456 Oak Avenue |
| Robert | Williams | 789 Pine Road |
| Sarah | Brown | 321 Elm Street |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| TechSupply Co. | | 125 Commerce Park Drive |
| Global Components Ltd. | | 2000 International Way |
| Quality Materials Corp. | | 550 Manufacturing Street |
| Acme Distributors | | 88 Wholesale Avenue |
+-------------------------+----------+--------------------------+
13 rows in set (0.01 sec)mysql> select fname, lname, addr from prospect-> union all-> select first_name, last_name, address from customer-> union all-> select company, '', street from vendor;
+-------------------------+----------+--------------------------+
| fname | lname | addr |
+-------------------------+----------+--------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| John | Smith | 123 Main St. |
| Mary | Johnson | 789 Oak Ave., Suite 100 |
| Grace | Peterson | 16055 Seminole Ave. |
| John | Smith | 123 Main Street |
| Mary | Johnson | 456 Oak Avenue |
| Robert | Williams | 789 Pine Road |
| Sarah | Brown | 321 Elm Street |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| TechSupply Co. | | 125 Commerce Park Drive |
| Global Components Ltd. | | 2000 International Way |
| Quality Materials Corp. | | 550 Manufacturing Street |
| Acme Distributors | | 88 Wholesale Avenue |
+-------------------------+----------+--------------------------+
13 rows in set (0.00 sec)mysql> select concat(lname, ',', fname) as name, addr from prospect-> union-> select concat(last_name, ',', first_name), address from customer-> union-> select company, street from vendor;
+-------------------------+--------------------------+
| name | addr |
+-------------------------+--------------------------+
| Jones,Peter | 482 Rush St., Apt. 402 |
| Smith,John | 123 Main St. |
| Johnson,Mary | 789 Oak Ave., Suite 100 |
| Peterson,Grace | 16055 Seminole Ave. |
| Smith,John | 123 Main Street |
| Johnson,Mary | 456 Oak Avenue |
| Williams,Robert | 789 Pine Road |
| Brown,Sarah | 321 Elm Street |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| TechSupply Co. | 125 Commerce Park Drive |
| Global Components Ltd. | 2000 International Way |
| Quality Materials Corp. | 550 Manufacturing Street |
| Acme Distributors | 88 Wholesale Avenue |
+-------------------------+--------------------------+
13 rows in set (0.01 sec)mysql> (select concat(lname, ',', fname) as name, addr from prospect)-> union-> (select concat(last_name, ',', first_name), address from customer)-> union-> (select company, street from vendor)-> order by name;
+-------------------------+--------------------------+
| name | addr |
+-------------------------+--------------------------+
| Acme Distributors | 88 Wholesale Avenue |
| Brown,Sarah | 321 Elm Street |
| Global Components Ltd. | 2000 International Way |
| Johnson,Mary | 789 Oak Ave., Suite 100 |
| Johnson,Mary | 456 Oak Avenue |
| Jones,Peter | 482 Rush St., Apt. 402 |
| Peterson,Grace | 16055 Seminole Ave. |
| Quality Materials Corp. | 550 Manufacturing Street |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Smith,John | 123 Main St. |
| Smith,John | 123 Main Street |
| TechSupply Co. | 125 Commerce Park Drive |
| Williams,Robert | 789 Pine Road |
+-------------------------+--------------------------+
13 rows in set (0.01 sec)mysql> (select 1 as sortval, concat(lname, ',' , fname) as name, addr from prospect)-> union-> (select 2 as sortval, concat(last_name, ',', first_name) as name, address from customer)-> union-> (select 3 as sortval, company, street from vendor)-> order by sortval;
+---------+-------------------------+--------------------------+
| sortval | name | addr |
+---------+-------------------------+--------------------------+
| 1 | Jones,Peter | 482 Rush St., Apt. 402 |
| 1 | Smith,John | 123 Main St. |
| 1 | Johnson,Mary | 789 Oak Ave., Suite 100 |
| 2 | Peterson,Grace | 16055 Seminole Ave. |
| 2 | Smith,John | 123 Main Street |
| 2 | Johnson,Mary | 456 Oak Avenue |
| 2 | Williams,Robert | 789 Pine Road |
| 2 | Brown,Sarah | 321 Elm Street |
| 3 | ReddyParts, Inc. | 38 Industrial Blvd. |
| 3 | TechSupply Co. | 125 Commerce Park Drive |
| 3 | Global Components Ltd. | 2000 International Way |
| 3 | Quality Materials Corp. | 550 Manufacturing Street |
| 3 | Acme Distributors | 88 Wholesale Avenue |
+---------+-------------------------+--------------------------+
13 rows in set (0.00 sec)mysql> (select 1 as sortval, concat(lname, ',' , fname) as name, addr from prospect)-> union-> (select 2 as sortval, concat(last_name, ',', first_name) as name, address from customer)-> union-> (select 3 as sortval, company, street from vendor)-> order by sortval, name;
+---------+-------------------------+--------------------------+
| sortval | name | addr |
+---------+-------------------------+--------------------------+
| 1 | Johnson,Mary | 789 Oak Ave., Suite 100 |
| 1 | Jones,Peter | 482 Rush St., Apt. 402 |
| 1 | Smith,John | 123 Main St. |
| 2 | Brown,Sarah | 321 Elm Street |
| 2 | Johnson,Mary | 456 Oak Avenue |
| 2 | Peterson,Grace | 16055 Seminole Ave. |
| 2 | Smith,John | 123 Main Street |
| 2 | Williams,Robert | 789 Pine Road |
| 3 | Acme Distributors | 88 Wholesale Avenue |
| 3 | Global Components Ltd. | 2000 International Way |
| 3 | Quality Materials Corp. | 550 Manufacturing Street |
| 3 | ReddyParts, Inc. | 38 Industrial Blvd. |
| 3 | TechSupply Co. | 125 Commerce Park Drive |
+---------+-------------------------+--------------------------+
13 rows in set (0.00 sec)mysql> (select concat(lname, ',', fname) as name, addr from prospect)-> union-> (select concat(last_name, ',', first_name), address from customer)-> union-> (select company, street from vendor)-> order by rand() limit 1;
+-------------------------+--------------------------+
| name | addr |
+-------------------------+--------------------------+
| Quality Materials Corp. | 550 Manufacturing Street |
+-------------------------+--------------------------+
1 row in set (0.00 sec)mysql> (select concat(lname, ',', fname) as name, addr from prospect order by rand() limit 1)-> union-> (select concat(last_name, ',', first_name), address from customer order by rand() limit 1)-> union-> (select company, street from vendor order by rand() limit 1);
+----------------+-------------------------+
| name | addr |
+----------------+-------------------------+
| Smith,John | 123 Main St. |
| Peterson,Grace | 16055 Seminole Ave. |
| TechSupply Co. | 125 Commerce Park Drive |
+----------------+-------------------------+
3 rows in set (0.00 sec)
12.13 识别并删除失配或独立行
mysql> select * from sales_region order by region_id;
+-----------+------------------------+
| region_id | name |
+-----------+------------------------+
| 1 | London, United Kingdom |
| 2 | Madrid, Spain |
| 3 | Berlin, Germany |
| 4 | Athens, Greece |
+-----------+------------------------+
4 rows in set (0.01 sec)mysql> select * from sales_volume order by region_id, year, quarter;
+----+-----------+------+---------+--------+
| id | region_id | year | quarter | volume |
+----+-----------+------+---------+--------+
| 1 | 1 | 2014 | 1 | 100400 |
| 2 | 1 | 2014 | 2 | 120000 |
| 3 | 3 | 2014 | 1 | 280000 |
| 4 | 3 | 2014 | 2 | 250000 |
| 5 | 5 | 2014 | 1 | 18000 |
| 6 | 5 | 2014 | 2 | 32000 |
+----+-----------+------+---------+--------+
6 rows in set (0.01 sec)mysql> select sales_region.region_id as 'unmatched region row IDs'-> from sales_region left join sales_volume-> on sales_region.region_id = sales_volume.region_id-> where sales_volume.region_id is null;
+--------------------------+
| unmatched region row IDs |
+--------------------------+
| 2 |
| 4 |
+--------------------------+
2 rows in set (0.01 sec)mysql> select sales_volume.region_id as 'unmatched region row IDs'-> from sales_volume left join sales_region-> on sales_volume.region_id = sales_region.region_id-> where sales_region.region_id is null;
+--------------------------+
| unmatched region row IDs |
+--------------------------+
| 5 |
| 5 |
+--------------------------+
2 rows in set (0.00 sec)mysql> select distinct sales_volume.region_id as 'unmatched volume row IDs'-> from sales_volume left join sales_region-> on sales_volume.region_id = sales_region.region_id-> where sales_region.region_id is null;
+--------------------------+
| unmatched volume row IDs |
+--------------------------+
| 5 |
+--------------------------+
1 row in set (0.00 sec)
12.14 为不同数据库间的表执行连接
mysql> select artist.name, painting.title-> from artist inner join painting-> on artist.a_id = painting.a_id;
+----------+-------------------+
| name | title |
+----------+-------------------+
| Da Vinci | The Last Supper |
| Da Vinci | Mona Lisa |
| Van Gogh | Starry Night |
| Van Gogh | The Potato Eaters |
| Renoir | Les Deux Soeurs |
| Van Gogh | The Rocks |
+----------+-------------------+
6 rows in set (0.02 sec)
这是同一个数据库中
mysql> select cookbook.artist.name, cookbook.painting.title-> from cookbook.artist inner join cookbook.painting-> on cookbook.artist.a_id = cookbook.painting.a_id;
+----------+-------------------+
| name | title |
+----------+-------------------+
| Da Vinci | The Last Supper |
| Da Vinci | Mona Lisa |
| Van Gogh | Starry Night |
| Van Gogh | The Potato Eaters |
| Renoir | Les Deux Soeurs |
| Van Gogh | The Rocks |
+----------+-------------------+
6 rows in set (0.00 sec)
12.15 同时使用不同的mysql服务器
mysql> select painting.title-> from artist inner join painting-> on artist.a_id = painting.a_id-> where artist.name = 'Da Vinci';
+-----------------+
| title |
+-----------------+
| The Last Supper |
| Mona Lisa |
+-----------------+
2 rows in set (0.01 sec)需要修改 D:\software\MySql\Data\my.ini 文件
ini
[mysqld]
federated
1. 以管理员身份运行命令提示符
方法1：
按 Win + S，输入 cmd
右键点击 "命令提示符"，选择 "以管理员身份运行"
再次尝试命令：
cmd
net stop MySQL80
net start MySQL80
方法2（如果仍然失败）：
使用 sc 命令（也需要管理员权限）：
cmd
sc stop MySQL80
sc start MySQL80C:\Users\lenovo>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.40 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.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 ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)第一台主机上
C:\Users\lenovo>mysql -u cbuser -p
Enter password: ******mysql> use cookbook;
Database changed
mysql> -- 先删除原有表（如果存在）
mysql> DROP TABLE IF EXISTS fed_painting;
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> -- 重新创建，替换为真实的远程服务器地址
mysql> CREATE TABLE fed_painting (-> a_id INT UNSIGNED NOT NULL,-> p_id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> title VARCHAR(100) NOT NULL,-> state VARCHAR(2) NOT NULL,-> price INT UNSIGNED,-> INDEX(a_id),-> PRIMARY KEY (p_id)-> )-> ENGINE=FEDERATED-> CONNECTION='mysql://cbuser:cbpass@127.0.0.1:3306/cookbook/painting';
Query OK, 0 rows affected (0.01 sec)第二台主机上
mysql> SELECT * FROM artist WHERE name LIKE '%Da Vinci%';
+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
+------+----------+
1 row in set (0.00 sec)mysql> SELECT * FROM fed_painting LIMIT 5;
+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+------+-------------------+-------+-------+
5 rows in set (0.01 sec)mysql> SELECT fp.title-> FROM artist a-> INNER JOIN fed_painting fp ON a.a_id = fp.a_id-> WHERE a.name = 'Da Vinci'; -- 移除末尾空格
+-----------------+
| title |
+-----------------+
| The Last Supper |
| Mona Lisa |
+-----------------+
2 rows in set (0.01 sec)
12.16 在程序中引用连接的输出列名称
mysql> select artist.name, painting.title, states.name, painting.price-> from artist inner join painting inner join states-> on artist.a_id = painting.a_id and painting.state = states.abbrev;
+----------+-------------------+----------+-------+
| name | title | name | price |
+----------+-------------------+----------+-------+
| Da Vinci | The Last Supper | Indiana | 34 |
| Da Vinci | Mona Lisa | Michigan | 87 |
| Van Gogh | Starry Night | Kentucky | 48 |
| Van Gogh | The Potato Eaters | Kentucky | 67 |
| Renoir | Les Deux Soeurs | Nebraska | 64 |
| Van Gogh | The Rocks | Iowa | 33 |
+----------+-------------------+----------+-------+
6 rows in set (0.01 sec)mysql> select-> artist.name as painter, painting.title,-> states.name as state, painting.price-> from artist inner join painting inner join states-> on artist.a_id = painting.a_id and painting.state = states.abbrev;
+----------+-------------------+----------+-------+
| painter | title | state | price |
+----------+-------------------+----------+-------+
| Da Vinci | The Last Supper | Indiana | 34 |
| Da Vinci | Mona Lisa | Michigan | 87 |
| Van Gogh | Starry Night | Kentucky | 48 |
| Van Gogh | The Potato Eaters | Kentucky | 67 |
| Renoir | Les Deux Soeurs | Nebraska | 64 |
| Van Gogh | The Rocks | Iowa | 33 |
+----------+-------------------+----------+-------+
6 rows in set (0.00 sec)mysql> select artist.name, painting.title, states.name, painting.price-> from artist inner join painting inner join states-> on artist.a_id = painting.a_id and painting.state = states.abbrev;
+----------+-------------------+----------+-------+
| name | title | name | price |
+----------+-------------------+----------+-------+
| Da Vinci | The Last Supper | Indiana | 34 |
| Da Vinci | Mona Lisa | Michigan | 87 |
| Van Gogh | Starry Night | Kentucky | 48 |
| Van Gogh | The Potato Eaters | Kentucky | 67 |
| Renoir | Les Deux Soeurs | Nebraska | 64 |
| Van Gogh | The Rocks | Iowa | 33 |
+----------+-------------------+----------+-------+
6 rows in set (0.00 sec)
第13章：统计技术
13.0 引言
13.1 计算描述统计
mysql> select subject, age, sex, score from testscore order by subject;
+---------+-----+-----+-------+
| subject | age | sex | score |
+---------+-----+-----+-------+
| 1 | 5 | M | 5 |
| 2 | 5 | M | 4 |
| 3 | 5 | F | 6 |
| 4 | 5 | F | 7 |
| 5 | 6 | M | 8 |
| 6 | 6 | M | 9 |
| 7 | 6 | F | 4 |
| 8 | 6 | F | 6 |
| 9 | 7 | M | 8 |
| 10 | 7 | M | 6 |
| 11 | 7 | F | 9 |
| 12 | 7 | F | 7 |
| 13 | 8 | M | 9 |
| 14 | 8 | M | 6 |
| 15 | 8 | F | 7 |
| 16 | 8 | F | 10 |
| 17 | 9 | M | 9 |
| 18 | 9 | M | 7 |
| 19 | 9 | F | 10 |
| 20 | 9 | F | 9 |
+---------+-----+-----+-------+
20 rows in set (0.01 sec)mysql> select count(score) as n,-> sum(score) as sum,-> min(score) as min,-> max(score) as max,-> avg(score) as mean,-> stddev_samp(score) as 'std.dev.',-> var_samp(score) as 'variance'-> from testscore;
+----+------+------+------+--------+--------------------+-------------------+
| n | sum | min | max | mean | std.dev. | variance |
+----+------+------+------+--------+--------------------+-------------------+
| 20 | 146 | 4 | 10 | 7.3000 | 1.8381913307436342 | 3.378947368421053 |
+----+------+------+------+--------+--------------------+-------------------+
1 row in set (0.01 sec)mysql> select @mean := avg(score), @std := stddev_samp(score) from testscore;
+---------------------+----------------------------+
| @mean := avg(score) | @std := stddev_samp(score) |
+---------------------+----------------------------+
| 7.3000 | 1.8381913307436342 |
+---------------------+----------------------------+
1 row in set, 2 warnings (0.00 sec)mysql> select score from testscore where abs(score-@mean0) > @std*3;
Empty set (0.00 sec)mysql> select score, count(score) as frequency-> from testscore group by score order by frequency desc;
+-------+-----------+
| score | frequency |
+-------+-----------+
| 9 | 5 |
| 6 | 4 |
| 7 | 4 |
| 4 | 2 |
| 8 | 2 |
| 10 | 2 |
| 5 | 1 |
+-------+-----------+
7 rows in set (0.00 sec)