MySQL7

Error_Fe
于 2024-04-15 17:03:54 发布
阅读量235
点赞数 3
文章标签： sql
本文链接：https://blog.csdn.net/Error_Fe/article/details/137787565
版权
文章详细展示了在MySQL环境中执行的一系列数据库操作，包括创建数据库、切换数据库、数据表操作（如JOIN和LEFTJOIN）、查询学生课程成绩以及员工薪资数据。
摘要由CSDN通过智能技术生成
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> set names gbk;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> use lty;
Database changed
mysql> SOURCE D:/360安全浏览器下载/sy92.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, 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, 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, 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, 0 rows affected (0.00 sec)

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

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

mysql> use xscj;
Database changed
mysql> SOURCE D:/360安全浏览器下载/sy91.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.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.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.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> SELECT xs.学号, xs.姓名, xs.专业名, kc.课程名, xs_kc.成绩
    -> FROM xs
    -> LEFT JOIN xs_kc ON xs.学号 = xs_kc.学号
    -> LEFT JOIN kc ON xs_kc.课程号 = kc.课程号
    -> WHERE xs.专业名 = '计算机';
+--------+--------+--------+----------------+------+
| 学号      | 姓名       | 专业名     | 课程名              | 成绩    |
+--------+--------+--------+----------------+------+
| 081101 | 王林       | 计算机       | 计算机基础               |   80 |
| 081101 | 王林       | 计算机       | 程序设计与语言               |   78 |
| 081102 | 程明       | 计算机       | 计算机基础               |   91 |
| 081102 | 程明       | 计算机       | 计算机网络               |   80 |
| 081102 | 程明       | 计算机       | 软件工程               |   76 |
| 081103 | 王燕       | 计算机       | NULL           | NULL |
| 081104 | 韦平平    | 计算机       | 计算机网络               |   65 |
+--------+--------+--------+----------------+------+
7 rows in set (0.01 sec)

mysql> SELECT xs.学号, xs.姓名, kc.课程名, xs_kc.成绩
    -> FROM xs
    -> LEFT JOIN xs_kc ON xs.学号 = xs_kc.学号
    -> LEFT JOIN kc ON xs_kc.课程号 = kc.课程号
    -> WHERE kc.课程名 = '计算机基础';
+--------+--------+------------+------+
| 学号      | 姓名       | 课程名          | 成绩    |
+--------+--------+------------+------+
| 081101 | 王林       | 计算机基础           |   80 |
| 081102 | 程明       | 计算机基础           |   91 |
| 081201 | 王敏       | 计算机基础           |   76 |
| 081203 | 严红      | 计算机基础           |   85 |
| 081303 | 孙祥欣       | 计算机基础           |   63 |
| 081304 | 孙研       | 计算机基础           |   65 |
+--------+--------+------------+------+
6 rows in set (0.01 sec)

mysql> SELECT kc.课程名, AVG(xs_kc.成绩) AS 平均分
    -> FROM kc
    -> LEFT JOIN xs_kc ON kc.课程号 = xs_kc.课程号
    -> WHERE kc.课程名 = '计算机基础'
    -> GROUP BY kc.课程名;
+------------+---------+
| 课程名          | 平均分       |
+------------+---------+
| 计算机基础           | 76.6667 |
+------------+---------+
1 row in set (0.00 sec)

mysql> use lty;
Database changed
mysql> SELECT e.`编号`, e.`姓名`, d.`部门名称`, s.`收入`
    -> FROM `employees` e
    -> JOIN `departments` d ON e.`部门号` = d.`部门号`
    -> JOIN `salary` s ON e.`编号` = s.`编号`
    -> WHERE e.`部门号` = '5';
+--------+--------+----------+---------+
| 编号       | 姓名       | 部门名称         | 收入        |
+--------+--------+----------+---------+
| 102201 | 刘明       | 市场部        | 2569.88 |
| 102208 | 朱俊     | 市场部        |    1980 |
| 111006 | 张石兵      | 市场部        | 1987.01 |
+--------+--------+----------+---------+
3 rows in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(e.`姓名`) AS 名单, d.`部门名称`, SUM(s.`收入`) AS 总收入
    -> FROM `employees` e
    -> JOIN `departments` d ON e.`部门号` = d.`部门号`
    -> JOIN `salary` s ON e.`编号` = s.`编号`
    -> WHERE e.`部门号` = '5';
+------------------+----------+-------------------+
| 名单                 | 部门名称         | 总收入                  |
+------------------+----------+-------------------+
| 刘明,朱俊,张石兵            | 市场部        | 6536.889892578125 |
+------------------+----------+-------------------+
1 row in set (0.01 sec)

mysql> SELECT e.`编号`, e.`姓名`, e.`学历`, d.`部门名称`, s.`收入`
    -> FROM `employees` e
    -> JOIN `departments` d ON e.`部门号` = d.`部门号`
    -> JOIN `salary` s ON e.`编号` = s.`编号`
    -> WHERE e.`学历` = '本科' AND s.`收入` > 2000;
+--------+--------+------+----------+---------+
| 编号       | 姓名       | 学历    | 部门名称         | 收入        |
+--------+--------+------+----------+---------+
| 302566 | 李玉珉       | 本科     | 研发部        |  2980.7 |
| 308759 | 叶凡      | 本科     | 研发部        | 2531.98 |
| 102201 | 刘明       | 本科     | 市场部        | 2569.88 |
+--------+--------+------+----------+---------+
3 rows in set (0.00 sec)