Mysql-高级查询语句
Mysql-高级查询语句
本博客中数据来源于：mysql官方练习数据库。
该数据库结构为：
1.控制语句
1.1DISTINCT
distinct用于记录该字段有多少不同的字段。如下所示，title表中第二个字段title有多条重复记录。
使用DISTINCT语句可以方便查询该字段有多少不同的值。
mysql> SELECT distinct title FROM `titles`;
+--------------------+
| title |
+--------------------+
| Assistant Engineer |
| Senior Staff |
| Engineer |
| Staff |
| Technique Leader |
| Senior Engineer |
| Manager |
+--------------------+
7 rows in set (0.18 sec)
配合count使用，可以计算该列不同值的个数。
mysql> SELECT count(distinct title) FROM `titles`;
+-----------------------+
| count(distinct title) |
+-----------------------+
| 7 |
+-----------------------+
1 row in set (0.09 sec)
1.2 WHERE
使用where语句可以查看限定条件下的数据。
employees表中记录不同员工的雇佣日期。
通过where语句可以筛选出所有雇佣日期为1986-06-26的雇员
mysql> select * from employees where hire_date = '1986-06-26';
+--------+------------+----------------+-----------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+----------------+-----------------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 21891 | 1960-08-25 | Shuzo | Bernatsky | M | 1986-06-26 |
| 27425 | 1958-11-06 | Maik | Gunderson | M | 1986-06-26 |
| 29250 | 1952-08-11 | Radoslaw | Besancenot | M | 1986-06-26 |
| 32297 | 1955-03-24 | Chandrasekaran | Loncour | M | 1986-06-26 |
| 35301 | 1956-01-31 | Basim | Worfolk | F | 1986-06-26 |
| 35565 | 1957-12-04 | Chiradeep | Yeung | F | 1986-06-26 |
| 41692 | 1961-08-08 | True | Tokunaga | M | 1986-06-26 |
————————————————————————————————————————————————————————————————————————————————
| 478432 | 1962-08-10 | Florian | Beeson | F | 1986-06-26 |
| 478983 | 1958-08-21 | Hugo | Litvinov | F | 1986-06-26 |
| 483397 | 1956-05-05 | Xuedong | Vanwelkenhuysen | M | 1986-06-26 |
| 499666 | 1955-12-03 | Martine | Pollock | M | 1986-06-26 |
+--------+------------+----------------+-----------------+--------+------------+
83 rows in set (0.07 sec)
配合and语句可以筛选出雇佣日期为1986-06-26且性别（gender）为F（女性）的雇员。
mysql> select * from employees where hire_date = '1986-06-26' AND gender = 'F';
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-------------+--------+------------+
| 35301 | 1956-01-31 | Basim | Worfolk | F | 1986-06-26 |
| 35565 | 1957-12-04 | Chiradeep | Yeung | F | 1986-06-26 |
| 50733 | 1952-06-07 | Hironoby | Nivat | F | 1986-06-26 |
| 52077 | 1964-12-14 | Rosella | Streit | F | 1986-06-26 |
————————————————————————————————————————————————————————————————————————
| 458304 | 1963-12-01 | Shuzo | Kirkerud | F | 1986-06-26 |
| 464413 | 1964-02-16 | Mart | Lorho | F | 1986-06-26 |
| 478432 | 1962-08-10 | Florian | Beeson | F | 1986-06-26 |
| 478983 | 1958-08-21 | Hugo | Litvinov | F | 1986-06-26 |
+--------+------------+------------+-------------+--------+------------+
35 rows in set (0.06 sec)
配和or语句可以筛选两个条件符合其中一个的记录。如雇佣日期为1986-06-26或1986-09-17的雇员。
mysql> select * from employees where hire_date = '1986-06-26' or hire_date = '1986-09-17';
+--------+------------+----------------+-----------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+----------------+-----------------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 17761 | 1958-04-19 | Adin | Matheson | F | 1986-09-17 |
| 21891 | 1960-08-25 | Shuzo | Bernatsky | M | 1986-06-26 |
| 23606 | 1956-06-15 | Theirry | Zirintsis | M | 1986-09-17 |
| 23680 | 1962-07-14 | Mary | Navazio | F | 1986-09-17 |
————————————————————————————————————————————————————————————————————————
| 28104 | 1953-08-12 | Dmitry | Marsiglia | F | 1986-09-17 |
| 29250 | 1952-08-11 | Radoslaw | Besancenot | M | 1986-06-26 |
| 29615 | 1958-12-30 | Atreye | Conry | M | 1986-09-17 |
————————————————————————————————————————————————————————————————————————
| 499666 | 1955-12-03 | Martine | Pollock | M | 1986-06-26 |
+--------+------------+----------------+-----------------+--------+------------+
175 rows in set (0.06 sec)
当需要多个条件同时筛选，可以通过括号将需要同时筛选的条件括起来。下面这个例子则是将雇佣日期为'1986-06-26'且性别为F的员工或雇佣日期为1986-09-17'且性别为M的员工都筛选出来。
mysql> select * from employees where (hire_date = '1986-06-26' AND gender = 'F') OR (hire_date= '1986-09-17' AND gender = 'M');
+--------+------------+-------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+-------------+-------------+--------+------------+
| 23606 | 1956-06-15 | Theirry | Zirintsis | M | 1986-09-17 |
| 27972 | 1955-07-22 | Chaosheng | Stanger | M | 1986-09-17 |
| 29615 | 1958-12-30 | Atreye | Conry | M | 1986-09-17 |
| 32120 | 1962-12-16 | Mingdong | Byoun | M | 1986-09-17 |
| 35301 | 1956-01-31 | Basim | Worfolk | F | 1986-06-26 |
| 35565 | 1957-12-04 | Chiradeep | Yeung | F | 1986-06-26 |
| 37675 | 1955-10-01 | Chikara | Marshall | M | 1986-09-17 |
——————————————————————————————————————————————————————————————————————————
| 475938 | 1965-01-14 | Shigeaki | Lanteri | M | 1986-09-17 |
| 478432 | 1962-08-10 | Florian | Beeson | F | 1986-06-26 |
| 478641 | 1964-05-05 | Pramod | Litvinov | M | 1986-09-17 |
| 478983 | 1958-08-21 | Hugo | Litvinov | F | 1986-06-26 |
| 481538 | 1963-07-24 | Percy | Szmurlo | M | 1986-09-17 |
| 482602 | 1954-06-18 | Aamer | Lagarias | M | 1986-09-17 |
| 485137 | 1960-12-09 | Bingning | Krohn | M | 1986-09-17 |
| 488143 | 1964-10-26 | Constantine | McClure | M | 1986-09-17 |
| 490587 | 1955-10-22 | Janalee | VanScheik | M | 1986-09-17 |
| 496210 | 1960-07-02 | Alagu | Szmurlo | M | 1986-09-17 |
+--------+------------+-------------+-------------+--------+------------+
86 rows in set (0.07 sec)
1.3 通配符与like
通过通配符与like可以模糊查询符合需求的记录。如下面这个查询语句可以查找出所有first_name为Chao的员工。
mysql> select * from employees where first_name like 'Chao%';
+--------+------------+------------+------------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+------------------+--------+------------+
| 11829 | 1952-08-01 | Chaosheng | Pettis | F | 1999-02-17 |
| 12923 | 1953-09-05 | Chaosheng | Journel | F | 1990-01-02 |
| 13524 | 1955-11-06 | Chaoyi | Porotnikoff | F | 1985-07-27 |
| 15343 | 1955-05-21 | Chaosheng | Luce | F | 1985-03-12 |
| 16019 | 1955-11-30 | Chaoyi | Heystek | F | 1985-08-01 |
——————————————————————————————————————————————————————————————————————————
| 497563 | 1957-10-29 | Chaoyi | Manders | M | 1990-12-02 |
| 497796 | 1953-10-15 | Chaoyi | Kushnir | M | 1988-07-19 |
| 498761 | 1958-10-09 | Chaoyi | Kaiserswerth | M | 1986-01-01 |
| 498894 | 1955-02-10 | Chaoyi | Bahr | F | 1986-09-21 |
| 499450 | 1963-04-23 | Chaosheng | Pargaonkar | F | 1988-07-13 |
| 499876 | 1959-03-19 | Chaosheng | Orlowska | M | 1988-02-20 |
+--------+------------+------------+------------------+--------+------------+
452 rows in set (0.05 sec)
百分号%代表零或多个字符，而下划线_代表一个字符。
常见的通配符使用方法如下。
- A_Z :A与Z之间有一个字符
- ABC% :以ABC开头的字符串后有零或多个字符
- %XYZ :以XYZ结尾的字符串，前有零或多个字符
- %AN%:AN的前后有零或多个字符
- _AN%:AN前有一个字符，AN后有零或多个字符
1.4 ORDER BY
ORDER BY 用于对字段的排序，默认是升序。如使用order by语句对员工的生日进行升序排序。（出生早的在前面）
mysql> select * from employees order by birth_date limit 100;
+--------+------------+------------+--------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+--------------+--------+------------+
| 406121 | 1952-02-01 | Supot | Remmele | M | 1989-01-27 |
| 91374 | 1952-02-01 | Eishiro | Kuzuoka | M | 1992-02-12 |
——————————————————————————————————————————————————————————————————————————
| 87461 | 1952-02-01 | Moni | Decaestecker | M | 1986-10-06 |
| 207658 | 1952-02-01 | Kiyokazu | Whitcomb | M | 1988-07-26 |
| 498106 | 1952-02-02 | Shai | Calkin | M | 1990-12-02 |
| 405962 | 1952-02-02 | Moto | Staudhammer | M | 1995-02-20 |
| 409040 | 1952-02-02 | Mayuko | Pardalos | M | 1991-07-03 |
| 94615 | 1952-02-02 | Toshimi | Bruckman | M | 1989-04-29 |
| 93928 | 1952-02-02 | Tomoyuki | Axelband | F | 1987-09-08 |
| 244265 | 1952-02-02 | Pantung | Halevi | M | 1993-11-12 |
——————————————————————————————————————————————————————————————————————————
| 239891 | 1952-02-02 | Percy | Tischendorf | M | 1986-11-10 |
| 455745 | 1952-02-02 | Moni | Giveon | F | 1994-01-17 |
| 462774 | 1952-02-02 | Moie | Chinal | F | 1997-09-20 |
| 203054 | 1952-02-02 | Hausi | Krohn | M | 1994-11-23 |
| 463921 | 1952-02-02 | Lalit | Bahr | M | 1987-05-27 |
| 469911 | 1952-02-02 | Mizuhito | Szemeredi | F | 1990-02-24 |
| 107344 | 1952-02-02 | Kiyomitsu | Gelosh | F | 1989-12-03 |
| 206240 | 1952-02-03 | Oksana | Zykh | M | 1988-03-22 |
| 406833 | 1952-02-03 | Aiichiro | Kobuchi | M | 1990-11-23 |
| 70603 | 1952-02-03 | Otilia | Parhami | M | 1991-01-28 |
| 61129 | 1952-02-03 | Rildo | Bain | M | 1992-04-03 |
——————————————————————————————————————————————————————————————————————————
| 93975 | 1952-02-03 | Gererd | Calkin | M | 1988-04-18 |
| 221356 | 1952-02-03 | Pradeep | Hagimont | M | 1987-09-24 |
| 294298 | 1952-02-03 | Vesna | Coney | M | 1999-06-28 |
+--------+------------+------------+--------------+--------+------------+
100 rows in set (0.07 sec)
使用DESC则为倒序排序。
mysql> select * from employees order by birth_date DESC limit 100;
+--------+------------+----------------+-----------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+----------------+-----------------+--------+------------+
| 457832 | 1965-02-01 | Jackson | Zielinski | F | 1985-04-04 |
| 424584 | 1965-02-01 | Kagan | Dredge | M | 1994-02-26 |
——————————————————————————————————————————————————————————————————————————
| 284045 | 1965-02-01 | Fun | Seiwald | M | 1994-06-24 |
| 294996 | 1965-02-01 | Tomoyuki | Vigier | F | 1991-07-08 |
| 437369 | 1965-02-01 | Kazuhide | Biran | F | 1995-01-25 |
| 93278 | 1965-02-01 | Magdalena | Penn | F | 1987-04-27 |
| 418860 | 1965-02-01 | Ymte | Dalton | F | 1992-11-22 |
| 109598 | 1965-02-01 | Stamatina | Auyong | M | 1988-07-05 |
| 40785 | 1965-01-31 | Youpyo | Castellani | M | 1998-09-04 |
| 97661 | 1965-01-31 | Avishai | Hegner | M | 1993-05-08 |
——————————————————————————————————————————————————————————————————————————
| 235269 | 1965-01-31 | Keiichiro | Feldmann | F | 1989-06-28 |
| 37719 | 1965-01-31 | Nakhoon | Buescher | M | 1986-07-28 |
| 52770 | 1965-01-31 | Shugo | Yurek | M | 1986-01-06 |
+--------+------------+----------------+-----------------+--------+------------+
100 rows in set (0.07 sec)
2.函数
2.1 数学函数
数学函数主要是对数字的处理。
现有这样一张表（自建）
mysql> select * from test_math;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 15 | 20 | 30 |
| 17 | 10 | 3 |
| -2 | 10 | 25 |
+------+------+------+
3 rows in set (0.01 sec)
函数abs()返回该字段的绝对值。
mysql> select abs(num1) from test_math;
+-----------+
| abs(num1) |
+-----------+
| 15 |
| 17 |
| 2 |
+-----------+
3 rows in set (0.00 sec)
函数rand ()返回0-1的随机值。
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.5209844825053443 |
+--------------------+
1 row in set (0.00 sec)
函数mod(x,y)返回x/y的余数。
mysql> select num1,num2,mod(num1,num2) from test_math;
+------+------+----------------+
| num1 | num2 | mod(num1,num2) |
+------+------+----------------+
| 15 | 20 | 15 |
| 17 | 10 | 7 |
| -2 | 10 | -2 |
+------+------+----------------+
3 rows in set (0.00 sec)
函数power (x,y)返回x的y次方(第一行e23,代表10的23次方，由于数字过大，采用了科学计数法)
mysql> select num1,num2,power(num1,num2) from test_math;
+------+------+-----------------------+
| num1 | num2 | power(num1,num2) |
+------+------+-----------------------+
| 15 | 20 | 3.3252567300796506e23 |
| 17 | 10 | 2015993900449 |
| -2 | 10 | 1024 |
+------+------+-----------------------+
3 rows in set (0.00 sec)
函数greatest (xl,x2. . .)返回集合中的最大数。
mysql> select num1,num2,num3,greatest(num1,num2,num3) from test_math;
+------+------+------+--------------------------+
| num1 | num2 | num3 | greatest(num1,num2,num3) |
+------+------+------+--------------------------+
| 15 | 20 | 30 | 30 |
| 17 | 10 | 3 | 17 |
| -2 | 10 | 25 | 25 |
+------+------+------+--------------------------+
3 rows in set (0.00 sec)
函数least (xl,x2. . .)返回集合中的最小数。
mysql> select num1,num2,num3,least(num1,num2,num3) from test_math;
+------+------+------+-----------------------+
| num1 | num2 | num3 | least(num1,num2,num3) |
+------+------+------+-----------------------+
| 15 | 20 | 30 | 15 |
| 17 | 10 | 3 | 3 |
| -2 | 10 | 25 | -2 |
+------+------+------+-----------------------+
3 rows in set (0.00 sec)
其余常用的数学函数还有：
round (X)
返回离x最近的整数
round(x, y)
保留x的y位小数四舍五入后的值
sqrt(x)
返回x的平方根
truncate (x, y)
返回数字x截断为y位小数的值
ceil (x)
返回大于或等于x的最小整数
floor (x)
返回小于或等于x的最大整数
2.2 聚合函数
主要涉及对字段数据的处理，依旧使用test_math进行演示。
mysql> select * from test_math;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 15 | 20 | 30 |
| 17 | 10 | 3 |
| -2 | 10 | 25 |
+------+------+------+
3 rows in set (0.01 sec)
函数avg ()求指定列的平均值
mysql> select AVG(num3) from test_math;
+-----------+
| AVG(num3) |
+-----------+
| 19.3333 |
+-----------+
1 row in set (0.00 sec)
函数min()求指定列的最小值
mysql> select min(num3) from test_math;
+-----------+
| min(num3) |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
函数MAX()求指定列的最大值
mysql> select max(num3) from test_math;
+-----------+
| max(num3) |
+-----------+
| 30 |
+-----------+
1 row in set (0.00 sec)
函数sum (x)返回指定列的和
mysql> select sum(num3) from test_math;
+-----------+
| sum(num3) |
+-----------+
| 58 |
+-----------+
1 row in set (0.00 sec)
2.3 字符串函数
字符串函数顾名思义就是针对字符串进行处理的函数
现有表
mysql> select * from test_char;
+-------+-------+----------------+
| char1 | char2 | char3 |
+-------+-------+----------------+
| KY | 02 | zengguoqiang |
| KY | 11 | changzeyou |
| CT | 83 | wudifenghuolun |
+-------+-------+----------------+
3 rows in set (0.00 sec)
函数concat (x, y)将x和y拼接为一个字符，xy可以是字段，也可以是符号
mysql> select char1,char2,concat(char1,char2),concat('2022-',char1) from test_char;
+-------+-------+---------------------+-----------------------+
| char1 | char2 | concat(char1,char2) | concat('2022-',char1) |
+-------+-------+---------------------+-----------------------+
| KY | 02 | KY02 | 2022-KY |
| KY | 11 | KY11 | 2022-KY |
| CT | 83 | CT83 | 2022-CT |
+-------+-------+---------------------+-----------------------+
3 rows in set (0.00 sec)
函数substr (x,y, z)用于截取字符串，x代表字段，y代表截取位置，z代表截取长度。
mysql> select char3,substr(char3,2,4) from test_char;
+----------------+-------------------+
| char3 | substr(char3,2,4) |
+----------------+-------------------+
| zengguoqiang | engg |
| changzeyou | hang |
| wudifenghuolun | udif |
+----------------+-------------------+
3 rows in set (0.00 sec)
函数length (x)可以获取字符串长度。
mysql> select char3,length(char3) from test_char;
+----------------+---------------+
| char3 | length(char3) |
+----------------+---------------+
| zengguoqiang | 12 |
| changzeyou | 10 |
| wudifenghuolun | 14 |
+----------------+---------------+
3 rows in set (0.00 sec)
函数replace(x, y, z)用于替换字符串内容，x代表被替换的字符串，y代表被替换的内容，z代表替换的内容。这三个可以是字符串也可是字段名称。
mysql> select *,replace(char3,'uo',char2) from test_char;
+-------+-------+----------------+---------------------------+
| char1 | char2 | char3 | replace(char3,'uo',char2) |
+-------+-------+----------------+---------------------------+
| KY | 02 | zengguoqiang | zengg02qiang |
| KY | 11 | changzeyou | changzeyou |
| CT | 83 | wudifenghuolun | wudifengh83lun |
+-------+-------+----------------+---------------------------+
3 rows in set (0.00 sec
其余常用字符串函数还有:
upper (x)
将字符串x的所有字母变成大写字母
lower (x)
将字符串x的所有字母变成小写字母
left(x,y)
返回字符串x的前y个字符
right (x,y)
返回字符串x的后y个字符
repeat (x, y)
将字符串x重复y次
space (x)
返回x个空格
3. 连接（join）
mysql在涉及多表关联字段查询的时候可以通过join来实现指定内容的查找，mysql连接种类大致分为：
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
现有两表
mysql> select * from LEFT_tb1;
+--------+----------+
| ssh_id | ssh_name |
+--------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | yefan |
| 7 | admin |
+--------+----------+
4 rows in set (0.00 sec)
mysql> select * from RIGHT_tb;
+--------+-----------+---------------+------------+
| ssh_id | ssh_count | ssh_ip | last_log |
+--------+-----------+---------------+------------+
| 1 | 20 | 192.168.80.30 | 2022-06-04 |
| 2 | 33 | 192.168.80.50 | 2022-05-10 |
| 3 | 45 | 192.168.80.20 | 2022-05-25 |
| 4 | 12 | 192.168.80.11 | 2022-05-03 |
| 5 | 1 | 100.0.0.1 | 2022-04-26 |
+--------+-----------+---------------+------------+
5 rows in set (0.00 sec)
3.1 INNER JOIN（内连接)
通过INNER JOIN可以查看表LEFT_tb1与RIGHT_tb关联字段ssh_id的对应记录。
mysql> select l.ssh_id,l.ssh_name,r.ssh_count,r.ssh_ip,r.last_log from LEFT_tb1 l INNER JOIN RIGHT_tb r on l.ssh_id = r.ssh_id;
+--------+----------+-----------+---------------+------------+
| ssh_id | ssh_name | ssh_count | ssh_ip | last_log |
+--------+----------+-----------+---------------+------------+
| 1 | zhangsan | 20 | 192.168.80.30 | 2022-06-04 |
| 2 | lisi | 33 | 192.168.80.50 | 2022-05-10 |
| 3 | yefan | 45 | 192.168.80.20 | 2022-05-25 |
+--------+----------+-----------+---------------+------------+
3 rows in set (0.00 sec)
INNER JOIN关系类似于上图，只获取交叉数据。
3.2 LEFT JOIN（左连接）
MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。无对应数据字段用NULL填充。
mysql> select l.ssh_id,l.ssh_name,r.ssh_count,r.ssh_ip,r.last_log from LEFT_tb1 l LEFT JOIN RIGHT_tb r on l.ssh_iid = r.ssh_id;
+--------+----------+-----------+---------------+------------+
| ssh_id | ssh_name | ssh_count | ssh_ip | last_log |
+--------+----------+-----------+---------------+------------+
| 1 | zhangsan | 20 | 192.168.80.30 | 2022-06-04 |
| 2 | lisi | 33 | 192.168.80.50 | 2022-05-10 |
| 3 | yefan | 45 | 192.168.80.20 | 2022-05-25 |
| 7 | admin | NULL | NULL | NULL |
+--------+----------+-----------+---------------+------------+
4 rows in set (0.00 sec)
3.3 RIGHT JOIN（右连接）
MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。
mysql> select l.ssh_id,l.ssh_name,r.ssh_count,r.ssh_ip,r.last_log from LEFT_tb1 l RIGHT JOIN RIGHT_tb r on l.ssh__id = r.ssh_id;
+--------+----------+-----------+---------------+------------+
| ssh_id | ssh_name | ssh_count | ssh_ip | last_log |
+--------+----------+-----------+---------------+------------+
| 1 | zhangsan | 20 | 192.168.80.30 | 2022-06-04 |
| 2 | lisi | 33 | 192.168.80.50 | 2022-05-10 |
| 3 | yefan | 45 | 192.168.80.20 | 2022-05-25 |
| NULL | NULL | 12 | 192.168.80.11 | 2022-05-03 |
| NULL | NULL | 1 | 100.0.0.1 | 2022-04-26 |
+--------+----------+-----------+---------------+------------+
5 rows in set (0.00 sec)
