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