子查询

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.53 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database xscj;
Query OK, 1 row affected (0.00 sec)

mysql> use xscj;
Database changed
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> source e:/sy101.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 23 rows affected (0.00 sec)
Records: 23  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| xscj               |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database yygl;
Query OK, 1 row affected (0.00 sec)

mysql> use yygl;
Database changed
mysql> source e:/sy102.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select employees.编号,姓名,收入 from employees join salary on salary.编号=employees.编号order by salary.收入 desc limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'by salary.收入 desc limit 1' at line 1
mysql> select employees.编号,姓名,收入 from employees join salary on salary.编号=employees.编号 order by salary.收入 desc limit 1;
+--------+------+---------+
| 编号       | 姓名     | 收入        |
+--------+------+---------+
| 108991 | 钟敏     | 3259.98 |
+--------+------+---------+
1 row in set (0.00 sec)

mysql> select avg(收入) as 平均工资 from salary;
+--------------------+
| 平均工资                  |
+--------------------+
| 2290.9845909705527 |
+--------------------+
1 row in set (0.00 sec)

mysql> select employees.编号,姓名,收入 from employees e join salary s on employees.编号 = salary.编号 where salary.收入 > (select avg(收入) from salary);
ERROR 1054 (42S22): Unknown column 'employees.编号' in 'field list'
mysql> select e.编号,e.姓名,s.收入 from employees e join salary s on e.编号 = s.编号 where s.收入 > (select avg(收入) from salary);
+--------+--------+---------+
| 编号       | 姓名       | 收入        |
+--------+--------+---------+
| 020010 | 王向容       |    2860 |
| 020018 | 李丽       | 2347.68 |
| 102201 | 刘明       | 2569.88 |
| 108991 | 钟敏       | 3259.98 |
| 302566 | 李玉珉       |  2980.7 |
| 308759 | 叶凡      | 2531.98 |
+--------+--------+---------+
6 rows in set (0.00 sec)

mysql> select 编号,姓名,收入,性别
    -> from salary
    -> join employees
    -> using(编号)
    -> where 性别=0 and 收入 > all(select 收入 from salary
    -> join employees
    -> using (编号)
    -> where 性别=1);
+--------+------+---------+------+
| 编号       | 姓名     | 收入        | 性别    |
+--------+------+---------+------+
| 108991 | 钟敏     | 3259.98 |    0 |
+--------+------+---------+------+
1 row in set (0.00 sec)

mysql> use xscj;
Database changed
mysql> select xs.学号,xs.姓名,xs.性别, avg(xs_kc.成绩) as 平均分
    -> from xs
    -> join xs_kc on xs.学号 = xs_kc.学号
    -> where xs.性别 = 1
    -> group by xs.学号,xs.姓名,xs.性别
    -> having avg(xs_kc.成绩) < (select avg(xs_kc.成绩)
    -> from xs
    -> join xs_kc on xs.学号 = xs_kc.学号
    -> where xs.性别=0);
+--------+--------+------+---------+
| 学号      | 姓名       | 性别    | 平均分       |
+--------+--------+------+---------+
| 081104 | 韦平平    |    1 | 65.0000 |
| 081301 | 李计       |    1 | 71.0000 |
| 081303 | 孙祥欣       |    1 | 72.0000 |
+--------+--------+------+---------+
3 rows in set (0.00 sec)

mysql>