MySQL学习——SQL查询语句（连接查询&子查询）（三）

一：连接查询：

　　连接查询是将俩个或者俩个以上的表按照某个条件连接起来，从中选择需要的数据，连接查询同时查询俩个或者俩个以上的表时使用，当不同的表中存在表示相同意义的字段时，可以通过该字段来连接这几个表，例如，学生表中有course_id字段来表示所学课程的课程号，课程表中有num字段来表示课程号，那么可以通过学生表中的course_id字段与课程表中的num字段来进行连接查询，连接查询包括内连接查询和外连接查询。

1.1 内连接查询

　　内连接查询是一种常用的连接查询，内连接查询可以查询俩个或者以上的表。

　　注：俩个表中表示相同意思的字段可以是指父表的主键和字表的外键；

例：创建俩个示例表employee和department

mysql>
mysql> show create table departmentG
*************************** 1. row ***************************
       Table: department
Create Table: CREATE TABLE `department` (
  `d_id` int(11) DEFAULT NULL,
  `d_name` varchar(20) DEFAULT NULL,
  `function` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table employeeG
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `num` int(11) DEFAULT NULL,
  `d_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  `homeaddr` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| num      | int(11)     | YES  |     | NULL    |       |
| d_id     | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
| sex      | char(4)     | YES  |     | NULL    |       |
| homeaddr | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(11)     | YES  |     | NULL    |       |
| d_name   | varchar(20) | YES  |     | NULL    |       |
| function | varchar(20) | YES  |     | NULL    |       |
| address  | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

插入实验所需数据：

mysql> select * from employee,department;
+------+------+--------+------+------+--------------------+------+-----------+--------------+---------------------+
| num  | d_id | name   | age  | sex  | homeaddr           | d_id | d_name    | function     | address             |
+------+------+--------+------+------+--------------------+------+-----------+--------------+---------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海定区       | 1001 | 科研部    | 研发产品     | 3号楼5层            |
|    1 | 1001 | 张三   |   26 | 男   | 北京市海定区       | 1002 | 生        | 生产产品     | 5号楼1层            |
|    1 | 1001 | 张三   |   26 | 男   | 北京市海定区       | 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
|    2 | 1002 | 李四   |   24 | 女   | 北京市昌平区       | 1001 | 科研部    | 研发产品     | 3号楼5层            |
|    2 | 1002 | 李四   |   24 | 女   | 北京市昌平区       | 1002 | 生        | 生产产品     | 5号楼1层            |
|    2 | 1002 | 李四   |   24 | 女   | 北京市昌平区       | 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
|    3 | 1001 | 王五   |   24 | 男   | 湖南长沙市         | 1001 | 科研部    | 研发产品     | 3号楼5层            |
|    3 | 1001 | 王五   |   24 | 男   | 湖南长沙市         | 1002 | 生        | 生产产品     | 5号楼1层            |
|    3 | 1001 | 王五   |   24 | 男   | 湖南长沙市         | 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
|    4 | 1004 | Aric   |   15 | 男   | England            | 1001 | 科研部    | 研发产品     | 3号楼5层            |
|    4 | 1004 | Aric   |   15 | 男   | England            | 1002 | 生        | 生产产品     | 5号楼1层            |
|    4 | 1004 | Aric   |   15 | 男   | England            | 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
+------+------+--------+------+------+--------------------+------+-----------+--------------+---------------------+
12 rows in set (0.00 sec)

mysql>

查询结果显示employee表和department表的d_id字段都表示部门号，通过d_id字段可以将employee表和department表进行内连接查询，从employee表中查询出num，name，d_id,age和sex这几个字段。

mysql>
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id = department.d_id;
+------+--------+------+------+------+-----------+--------------+
| num  | name   | d_id | age  | sex  | d_name    | function     |
+------+--------+------+------+------+-----------+--------------+
|    1 | 张三   | 1001 |   26 | 男   | 科研部    | 研发产品     |
|    2 | 李四   | 1002 |   24 | 女   | 生        | 生产产品     |
|    3 | 王五   | 1001 |   24 | 男   | 科研部    | 研发产品     |
+------+--------+------+------+------+-----------+--------------+
3 rows in set (0.00 sec)

mysql>

　　

1.2 外连接查询

　　外连接查询可以查询俩个或者俩个以上的表，外连接查询也需要通过制定字段来进行连接，当该字段取值相等时，可以查询出该记录，而且该字段取值不相等的记录也可以查询出来，外连接查询包括左连接查询和右连接查询。

　　格式： SELECT 属性列表 FROM 表名1 LEFT |  RIGHT JOIN 表名2  ON 表名1属性=表名2属性；

1.2.1 左连接查询

　　使用左连接查询employee和department俩表，通过d_id字段进行连接；

mysql>
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee LEFT JOIN department on employee.d_id=department.d_id;
+------+--------+------+------+------+-----------+--------------+
| num  | name   | d_id | age  | sex  | d_name    | function     |
+------+--------+------+------+------+-----------+--------------+
|    1 | 张三   | 1001 |   26 | 男   | 科研部    | 研发产品     |
|    3 | 王五   | 1001 |   24 | 男   | 科研部    | 研发产品     |
|    2 | 李四   | 1002 |   24 | 女   | 生        | 生产产品     |
|    4 | Aric   | 1004 |   15 | 男   | NULL      | NULL         |
+------+--------+------+------+------+-----------+--------------+
4 rows in set (0.00 sec)

mysql>

1.2.2 右连接查询

　　使用右连接查询employee和department俩表，通过d_id字段进行连接；

mysql> select num,name,age,sex,department.d_id,d_name,function from employee RIGHT JOIN department on employee.d_id=department.d_id;
+------+--------+------+------+------+-----------+--------------+
| num  | name   | age  | sex  | d_id | d_name    | function     |
+------+--------+------+------+------+-----------+--------------+
|    1 | 张三   |   26 | 男   | 1001 | 科研部    | 研发产品     |
|    2 | 李四   |   24 | 女   | 1002 | 生        | 生产产品     |
|    3 | 王五   |   24 | 男   | 1001 | 科研部    | 研发产品     |
| NULL | NULL   | NULL | NULL | 1003 | 销售部    | 策划销售     |
+------+--------+------+------+------+-----------+--------------+
4 rows in set (0.00 sec)

mysql>

1.2.3 复合条件查询

　　在连接查询时，也可以增加其他的限制条件，通过多个条件的复合查询，可以使查询结果更加准确。

例如：使用内连接的方式查询employee表和department表，并且employee表中的age字段的值必须大于24.

mysql>
mysql> select num,name,employee.d_id,age,sex,d_name,function
    -> from employee,department
    -> where employee.d_id = department.d_id
    -> and age>24;
+------+--------+------+------+------+-----------+--------------+
| num  | name   | d_id | age  | sex  | d_name    | function     |
+------+--------+------+------+------+-----------+--------------+
|    1 | 张三   | 1001 |   26 | 男   | 科研部    | 研发产品     |
+------+--------+------+------+------+-----------+--------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id = department.d_id having  age>24;
+------+--------+------+------+------+-----------+--------------+
| num  | name   | d_id | age  | sex  | d_name    | function     |
+------+--------+------+------+------+-----------+--------------+
|    1 | 张三   | 1001 |   26 | 男   | 科研部    | 研发产品     |
+------+--------+------+------+------+-----------+--------------+
1 row in set (0.00 sec)

mysql>

例子：使用内连接的方式查询employee表和department表，并且以age字段的升序方式显示查询结果

mysql>
mysql> select num,name,employee.d_id,age,sex,d_name,function
    -> from employee,department
    -> where employee.d_id=department.d_id
    -> order by age ASC;
+------+--------+------+------+------+-----------+--------------+
| num  | name   | d_id | age  | sex  | d_name    | function     |
+------+--------+------+------+------+-----------+--------------+
|    3 | 王五   | 1001 |   24 | 男   | 科研部    | 研发产品     |
|    2 | 李四   | 1002 |   24 | 女   | 生        | 生产产品     |
|    1 | 张三   | 1001 |   26 | 男   | 科研部    | 研发产品     |
+------+--------+------+------+------+-----------+--------------+
3 rows in set (0.00 sec)

mysql>

二：子查询

　　子查询时将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果，可以为外层查询语句提供查询条件，因为在特定的情况下，一个查询语句的条件需要另外一个查询语句来获取，例如现在需要从学生成绩表中查询计算机系学生的各科成绩，那么首先就必须知道哪些课程是计算机系学生选修的，因此必须查询计算机学生选修的课程，然后根据这些课程来查询计算机系学生的各科成绩，通过子查询，可以实现多表之间的查询，子查询中可能包括IN,NOT IN,ANY,ALL,EXISTS和NOT EXSITS等关键字，子查询中还可能包含比较运算符，如‘=’，‘！=’，‘>’等；

2.1带IN关键字的子查询

mysql> select * from employee
    -> where d_id IN
    -> (select d_id from department);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海定区       |
|    2 | 1002 | 李四   |   24 | 女   | 北京市昌平区       |
|    3 | 1001 | 王五   |   24 | 男   | 湖南长沙市         |
+------+------+--------+------+------+--------------------+
3 rows in set (0.00 sec)

mysql>

查询employee表中的记录，这些记录的d_id字段必须没有在department表中出现过。

mysql>
mysql> select * from employee
    -> where d_id NOT IN
    -> (select d_id from department );
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    4 | 1004 | Aric |   15 | 男   | England  |
+------+------+------+------+------+----------+
1 row in set (0.00 sec)

mysql>

2.2 带比较运算符的子查询

　　子查询可以使用比较运算符（=，！=，>,<,>=,<=,<>）等

示例数据表

mysql> select * from computer_stu,scholarship;
+------+------+-------+-------+-------+
| id   | name | score | level | score |
+------+------+-------+-------+-------+
| 1001 | Lily |    85 |     1 |    90 |
| 1001 | Lily |    85 |     2 |    80 |
| 1001 | Lily |    85 |     3 |    70 |
| 1002 | Tom  |    91 |     1 |    90 |
| 1002 | Tom  |    91 |     2 |    80 |
| 1002 | Tom  |    91 |     3 |    70 |
| 1003 | Jim  |    87 |     1 |    90 |
| 1003 | Jim  |    87 |     2 |    80 |
| 1003 | Jim  |    87 |     3 |    70 |
| 1004 | Aric |    77 |     1 |    90 |
| 1004 | Aric |    77 |     2 |    80 |
| 1004 | Aric |    77 |     3 |    70 |
| 1005 | Lucy |    65 |     1 |    90 |
| 1005 | Lucy |    65 |     2 |    80 |
| 1005 | Lucy |    65 |     3 |    70 |
| 1006 | Andy |    99 |     1 |    90 |
| 1006 | Andy |    99 |     2 |    80 |
| 1006 | Andy |    99 |     3 |    70 |
| 1007 | Ada  |    85 |     1 |    90 |
| 1007 | Ada  |    85 |     2 |    80 |
| 1007 | Ada  |    85 |     3 |    70 |
| 1008 | Jeck |    70 |     1 |    90 |
| 1008 | Jeck |    70 |     2 |    80 |
| 1008 | Jeck |    70 |     3 |    70 |
+------+------+-------+-------+-------+
24 rows in set (0.00 sec)

mysql>

从computer_stu表中查询获得一等奖学金的学生的学号，姓名和分数

mysql>
mysql> select id,name,score from computer_stu where score >= (select score from scholarship where level=1);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql>

在department表中查询那些部门没有年龄为24岁的员工；

mysql>  select d_id,d_name from department
    -> where d_id NOT IN
    -> (select d_id from employee where age=24);
+------+-----------+
| d_id | d_name    |
+------+-----------+
| 1003 | 销售部    |
+------+-----------+
1 row in set (0.00 sec)

mysql>

2.3 带EXISTS 关键字的子查询

　　EXISTS关键字表示存在，使用EXISTS关键字时，内层查询不会反悔查询记录，而是返回一个真假值，当返回真是外层查询语句进行查询，当返回假时，外层语句不进行查询或者查询 不出任何结果。

例：如果department表中存在d_id取值为1003的记录，则查询employee表的记录：

mysql>
mysql> select * from employee
    -> where EXISTS
    -> (select d_name from department where d_id =1003)
    -> ;
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海定区       |
|    2 | 1002 | 李四   |   24 | 女   | 北京市昌平区       |
|    3 | 1001 | 王五   |   24 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |   15 | 男   | England            |
+------+------+--------+------+------+--------------------+
4 rows in set (0.00 sec)

mysql>

　　

例：如果department表中存在d_id取值为1004的记录，则查询emloyee表的记录；

mysql>
mysql> select * from employee
    -> where EXISTS
    -> (select d_name from department where d_id=0);
Empty set (0.00 sec)

mysql>

例：如果department表中存在d_id取值为1003的记录，则查询employee表中age大于24的记录；

mysql>
mysql> select * from employee where age>24 and EXISTS
    -> (select d_name from department where d_id=1003);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海定区       |
+------+------+--------+------+------+--------------------+
1 row in set (0.00 sec)

mysql>

2.4 带ANY关键字的子查询

　　ANY关键字表示满足其中任一条件，使用ANY关键字时，是要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句。

例：查询到底哪位同学能获得奖学金：

mysql>
mysql>  select * from computer_stu where score >= ANY
    -> (select score from scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
7 rows in set (0.00 sec)

mysql>

2.5 带ALL关键字的子查询

　　ALL关键字表示满足所有条件，使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

例：从computer_stu表中查询出那些同学可以获得一等奖学金；

mysql>
mysql> select * from computer_stu
    -> where score >= ALL
    -> (select score from scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql>

　　

注：ANY关键字和ALL关键字的使用方式是一样的，但是俩这有很大的区别，使用ANY关键字时，只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句，而ALL关键字刚好相反，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

附：合并查询结果（具有去重功能）

　　合并查询结果是将多个select语句的查询结果合并在一起，因某种情况下，需要将几个select语句查询出来的结果合并起来显示，例如现在需要查询公司甲和公司乙这俩个公司所有的员工信息，然后将俩次的查询结果合并到一起，进行合并操作使用 UNION和UNION ALL关键字。

格式：select 语句1

　　　　UNION |  UNION ALL

　　 select  语句2

　　　　UNION |  UNION ALL

　　…………

例：从department表和employee表中查询d_id字段的取值，然后通过UNION关键字将结果合并到一起。

mysql>
mysql>
mysql> select d_id from department
    -> UNION
    -> select d_id from employee;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
+------+
4 rows in set (0.00 sec)

mysql>