MySQL学习——SQL查询语句(连接查询&子查询)(三)

时间:2022-04-14 23:47:58

一:连接查询:

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

1.1 内连接查询

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

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

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

mysql>
mysql> show create table department\G
*************************** 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 employee\G
*************************** 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>