示例表
mysql> DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | char(10) | NO | | NULL | |
| pirates | char(10) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| post | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
一、创建计算字段
1.拼接字段
将 name , sex 两列进行合并。并通过 AS 关键字进行给新列赋予别名。
mysql> SELECT Concat(name, '(', sex, ')') AS new_column
-> FROM one_piece;
2.执行算数计算
通过 quantity (数量)、 price (价格)来计算 total_price (总价)
mysql> SELECT quantity, price,
-> quantity * price AS total_price
-> FROM test
二、函数
常用文本处理函数
使用 LENGTH(str) 获取字符串的长度。
mysql> SELECT name, LENGTH(name) AS length
-> FROM one_piece;
日期和时间处理函数
查询在 2000年 出生的人员信息。
mysql> SELECT *
-> FROM test
-> WHERE YEAR(brithday)=2000;
数值处理函数
以 ABS() 函数为例
sql> SELECT ABS(-1);
+---------+
| ABS(-1) |
+---------+
| 1 |
+---------+
三、数据聚集
聚集函数
1.AVG() 函数
查询平均 age 。
mysql> SELECT AVG(age) AS avg_age
-> FROM one_piece
2.COUNT() 函数
两种使用方式:
- COUNT(*) 对表中行的数目进行计数,包括空值。
- COUNT(column) 对特定列中非 NULL 行进行计数。
mysql> SELECT COUNT(*) AS num_person
-> FROM one_piece;
mysql> SELECT COUNT(name) AS num_name
-> FROM one_piece;
3.MAX() & MIN() 函数
当 column 列为数值列, MAX(column) / MIN(column) 返回 column 列中的最大值 / 最小值。
当 column 列为文本数据, MAX(column) / MIN(column) 返回 column 列数据排序后的最后一行 / 最前面的行。
4.SUM() 函数
SUM() 用来返回指定列值的和(总计)(忽略列值为 NULL 的行)。
mysql> SELECT SUM(price * quantity) AS total_price
-> FROM test
1.AVG() 函数
组合聚集函数
计算 one_piece 表中数据的条数,年龄的最小值、最大值和平均值。
mysql> SELECT COUNT(*) AS num_person,
-> MIN(age) AS age_min,
-> MAX(age) AS age_max,
-> AVG(age) AS age_avg
-> FROM one_piece;
四、数据分组
数据分组
使用分组将数据分为多个逻辑组, 对每个组进行聚集计算。 例:统计各个海贼团( pirates )的人数。
mysql> SELECT pirates, COUNT(*) AS num_person
-> FROM one_piece
-> GROUP BY pirates;
group by 注意事项:
- GROUP BY 可以嵌套使用。
- GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外,SELECT 语句中的每一列都必须在 GROUP BY 子句 中给出。
- 如果分组列中包含具有 NULL 值的行,则 NULL 将作为一个分组返回。 如果列中有多行 NULL 值,它们将分为一组。
- GROUP BY 子句必须出现在 WHERE 子句之后,ORDER BY 子句之前。
过滤分组
使用 HAVING 子句在数据分组后进行过滤。
查询海贼团人数在500人以上的 海贼团名称 及 人数。
mysql> SELECT pirates, COUNT(*) AS num_person
-> FROM one_piece
-> GROUP BY pirates
-> HAVING COUNT(*) >= 500;
WHERE 与 HAVING 的主要区别:
- WHERE 在数据分组前进行过滤,HAVING 在数据分组后进行过滤。
SELECT 子句顺序:
五、子查询
利用子查询进行过滤
现在查询 草帽海贼团 的排名信息。
mysql> SELECT rank
-> FROM rank_info
-> WHERE id IN (SELECT id
-> FROM one_piece
-> WHERE pirates = '草帽海贼团');
注意:
- 在 SELECT 语句中,子查询总是从内向外处理。
- 作为子查询的 SELECT 语句只能查询单个列。检索多个列会报错。
作为计算字段使用子查询
查询海贼团排名和任务信息,首先从 one_piece 表中根据 id 检索出排名信息,再统计每个冒险团的人数。
mysql> SELECT rank,
-> (SELECT COUNT(*)
-> FROM one_piece AS oe
-> WHERE oe.id = ro.id) AS num_person
-> FROM rank_info AS ro
-> ORDER BY rank;
注意:上面的例子中使用的是 oe.id 和 ro.id ,而不是直接使用 id ,因为在两个表中都有 id 列,在有可能混淆列名时必须使用这种语法。
六、表联结
自联结
假如现在有人不知道 乔巴 所属的海贼团, 想要知道 乔巴 所属海贼团的所有成员名称与赏金。 先看一下子查询的方式:
mysql> SELECT name, bounty
-> FROM one_piece
-> WHERE pirates = (SELECT pirates
-> FROM one_piece
-> WHERE name = '乔巴');
接下来使用自联结的方式:
mysql> SELECT c1.name, c1.bounty
-> FROM Customers AS c1, Customers AS c2
-> WHERE c1.pirates = c2.pirates
-> AND c2.name = '乔巴';
通常情况下,自联结的方式比子查询的方式要快很多。
等值联结
联结是一种机制,用来在一条 SELECT 语句 中关联表,因此称为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。联结不是物理实体。换句话说,它在实际的数据库表 中并不存在。它只在查询执行期间存在。
两表 table1, table2 中数据如下:
table1 table2
+------+------+------+ +------+------+------+
| A | B | C | | C | D | E |
+------+------+------+ +------+------+------+
| 1 | 2 | 3 | | 2 | 3 | 4 |
| 4 | 5 | 6 | | 6 | 7 | 8 |
+------+------+------+ +------+------+------+
现在通过表联结,获取两个表中的数据。
mysql> SELECT *
-> FROM table1 AS t1, table2 AS t2
-> WHERE t1.C = t2.C;
+------+------+------+------+------+------+
| A | B | C | C | D | E |
+------+------+------+------+------+------+
| 4 | 5 | 6 | 6 | 7 | 8 |
+------+------+------+------+------+------+
注意:上例中WHERE 中限制了联结条件,如果没有条件的话,返回的结果就是两表的笛卡尔积,返回 6 × 9 共 54条数据
内联结
上面的联结准确来说是等值联结,也可以称为内联结,它还有另一种语法。返回的结果以上面相同。
mysql> SELECT *
-> FROM table1 AS t1 INNER JOIN table2 AS t2
-> ON t1.C = t2.C;
+------+------+------+------+------+------+
| A | B | C | C | D | E |
+------+------+------+------+------+------+
| 4 | 5 | 6 | 6 | 7 | 8 |
+------+------+------+------+------+------+
自然联结
自然连接是一种特殊的等值连接,它在两个关系表中自动比较相同的属性列,无须添加连接条件,并且在结果中消除重复的属性列。
mysql> SELECT *
-> FROM table1 AS t1 NATURAL JOIN table2 t2;
+------+------+------+------+------+
| C | A | B | D | E |
+------+------+------+------+------+
| 6 | 4 | 5 | 7 | 8 |
+------+------+------+------+------+
外联结
左外联结
左外联结,左表( table1 )的记录将会全部表示出来,而右表( table2 )只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL 。
mysql> SELECT *
-> FROM table1 AS t1 LEFT JOIN table2 AS t2
-> ON t1.C = t2.C;
+------+------+------+------+------+------+
| A | B | C | C | D | E |
+------+------+------+------+------+------+
| 4 | 5 | 6 | 6 | 7 | 8 |
| 1 | 2 | 3 | NULL | NULL | NULL |
+------+------+------+------+------+------+
右外联结
右外联结,右表( table2 )的记录将会全部表示出来,而右左表( table1 )只会显示符合搜索条件的记录。左表记录不足的地方均为 NULL 。
mysql> SELECT *
-> FROM table1 AS t1 RIGHT JOIN table2 AS t2
-> ON t1.C = t2.C;
+------+------+------+------+------+------+
| A | B | C | C | D | E |
+------+------+------+------+------+------+
| 4 | 5 | 6 | 6 | 7 | 8 |
| NULL | NULL | NULL | 2 | 3 | 4 |
+------+------+------+------+------+------+