MySQL8-CSDN博客

本文链接：https://blog.csdn.net/Error_Fe/article/details/138081758
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 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 D:/360安全浏览器下载/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.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 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.01 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 yggl;
Query OK, 1 row affected (0.00 sec)

mysql> use yggl;
Database changed
mysql> source D:/360安全浏览器下载/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.01 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;
+--------+------+---------+
| 编号       | 姓名     | 收入        |
+--------+------+---------+
| 108991 | 钟敏     | 3259.98 |
+--------+------+---------+
1 row in set (0.00 sec)
mysql> SELECT AVG(收入) AS 平均工资 FROM salary;
+--------------------+
| 平均工资                  |
+--------------------+
| 2290.9845909705527 |
+--------------------+
1 row in set (0.01 sec)

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.01 sec)

mysql> SELECT AVG(s.收入) AS 男性平均工资
    -> FROM employees e
    -> JOIN salary s ON e.编号 = s.编号
    -> WHERE e.性别 = 1; -- 1 代表男性
+--------------------+
| 男性平均工资                  |
+--------------------+
| 2210.8989868164062 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT e.编号, e.姓名, e.性别, s.收入
    -> FROM employees e
    -> JOIN salary s ON e.编号 = s.编号
    -> WHERE e.性别 = 0 -- 0 代表女性
    -> AND s.收入 > (SELECT AVG(s.收入) FROM employees e JOIN salary s ON e.编号 = s.编号 WHERE e.性别 = 1);
+--------+------+------+---------+
| 编号       | 姓名     | 性别    | 收入        |
+--------+------+------+---------+
| 020018 | 李丽     |    0 | 2347.68 |
| 108991 | 钟敏     |    0 | 3259.98 |
+--------+------+------+---------+
2 rows 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.01 sec)

mysql> SELECT kc.课程号, kc.课程名, xs_kc.成绩
    -> FROM kc
    -> LEFT JOIN xs_kc ON kc.课程号 = xs_kc.课程号
    -> WHERE xs_kc.学号 IS NULL
    -> ;
+--------+----------+------+
| 课程号     | 课程名        | 成绩    |
+--------+----------+------+
| 201    | 数据结构       | NULL |
+--------+----------+------+
1 row in set (0.00 sec)
mysql> use yggl;
Database changed
mysql> SELECT e1.编号, e1.姓名, e1.学历, e1.出生日期,
    ->        e2.编号, e2.姓名, e2.学历, e2.出生日期
    -> FROM employees e1
    -> JOIN employees e2 ON e1.出生日期 = e2.出生日期
    -> WHERE e1.学历 = '本科' AND e2.学历 != '本科'
    ->   AND e1.编号 != e2.编号
    -> LIMIT 1;
+--------+------+------+------------+--------+------+------+------------+
| 编号       | 姓名     | 学历    | 出生日期           | 编号       | 姓名     | 学历    | 出生日期           |
+--------+------+------+------------+--------+------+------+------------+
| 102201 | 刘明     | 本科     | 1972-10-18 | 210678 | 林涛     | 大专    | 1972-10-18 |
+--------+------+------+------------+--------+------+------+------------+
1 row in set (0.00 sec)