MySQL6-优快云博客

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

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

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

mysql> SOURCE /D:/360安全浏览器下载/sy81.sql;
ERROR:
Failed to open file '\D:\360安全浏览器下载\sy81.sql', error: 22
mysql> SOURCE D:/360安全浏览器下载/sy81.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, 4 rows affected (0.00 sec)
Records: 4  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, 9 rows affected (0.00 sec)
Records: 9  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, 21 rows affected (0.00 sec)
Records: 21  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, 4 rows affected (0.00 sec)
Records: 4  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, 42 rows affected (0.00 sec)
Records: 42  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> CREATE DATABASE yggl;
Query OK, 1 row affected (0.00 sec)

mysql> use yggl;
Database changed
mysql> SOURCE D:/360安全浏览器下载/sy82.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.02 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.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, 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, 11 rows affected (0.00 sec)
Records: 11  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               |
| yggl               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use lty;
Database changed
mysql> SELECT xs1.学号, xs1.姓名, xs_kc.课程号, xs_kc.成绩
    -> FROM xs1
    -> LEFT JOIN xs_kc ON xs1.学号 = xs_kc.学号;
+--------+--------+--------+------+
| 学号      | 姓名       | 课程号     | 成绩    |
+--------+--------+--------+------+
| 081101 | 王林       | 101    |   80 |
| 081101 | 王林       | 102    |   78 |
| 081101 | 王林       | 206    |   76 |
| 081102 | 程明       | 102    |   78 |
| 081102 | 程明       | 206    |   78 |
| 081103 | 王燕       | 101    |   62 |
| 081103 | 王燕       | 102    |   70 |
| 081103 | 王燕       | 206    |   81 |
| 081104 | 韦平平    | 101    |   90 |
| 081104 | 韦平平    | 102    |   84 |
| 081104 | 韦平平    | 206    |   65 |
+--------+--------+--------+------+
11 rows in set (0.01 sec)

mysql> SELECT xs1.学号, xs1.姓名, xs_kc.课程号, xs_kc.成绩
    -> FROM xs1
    -> INNER JOIN xs_kc ON xs1.学号 = xs_kc.学号
    -> WHERE xs_kc.课程号 = '101' AND xs_kc.成绩 >= 80;
+--------+--------+--------+------+
| 学号      | 姓名       | 课程号     | 成绩    |
+--------+--------+--------+------+
| 081101 | 王林       | 101    |   80 |
| 081104 | 韦平平    | 101    |   90 |
+--------+--------+--------+------+
2 rows in set (0.00 sec)

mysql> SELECT xs_kc.课程号, courses.课程名, xs_kc.学号, xs_kc.成绩
    -> FROM xs_kc
    -> LEFT JOIN courses ON xs_kc.课程号 = courses.课程号;
ERROR 1146 (42S02): Table 'lty.courses' doesn't exist
mysql> SELECT xs_kc.课程号, IFNULL(courses.课程名, '未知课程') AS 课程名, xs_kc.学号, xs_kc.成绩
    -> FROM xs_kc
    -> LEFT JOIN courses ON xs_kc.课程号 = courses.课程号;
ERROR 1146 (42S02): Table 'lty.courses' doesn't exist
mysql> select xs.学号,姓名,课程号,成绩 from xs join xs_kc where xs.学号=xs_kc.学号;
+--------+--------+--------+------+
| 学号      | 姓名       | 课程号     | 成绩    |
+--------+--------+--------+------+
| 081101 | 王林       | 101    |   80 |
| 081101 | 王林       | 102    |   78 |
| 081101 | 王林       | 206    |   76 |
| 081102 | 程明       | 102    |   78 |
| 081102 | 程明       | 206    |   78 |
| 081103 | 王燕       | 101    |   62 |
| 081103 | 王燕       | 102    |   70 |
| 081103 | 王燕       | 206    |   81 |
| 081104 | 韦平平    | 101    |   90 |
| 081104 | 韦平平    | 102    |   84 |
| 081104 | 韦平平    | 206    |   65 |
| 081106 | 李方方      | 101    |   65 |
| 081106 | 李方方      | 102    |   71 |
| 081106 | 李方方      | 206    |   80 |
| 081107 | 李明       | 101    |   78 |
| 081107 | 李明       | 102    |   80 |
| 081107 | 李明       | 206    |   68 |
| 081108 | 林一帆      | 101    |   85 |
| 081108 | 林一帆      | 102    |   64 |
| 081108 | 林一帆      | 206    |   87 |
| 081109 | 张强明      | 101    |   66 |
| 081109 | 张强明      | 102    |   83 |
| 081109 | 张强明      | 206    |   70 |
| 081110 | 张蔚      | 101    |   95 |
| 081110 | 张蔚      | 102    |   90 |
| 081110 | 张蔚      | 206    |   89 |
| 081111 | 赵琳       | 101    |   91 |
| 081111 | 赵琳       | 102    |   70 |
| 081111 | 赵琳       | 206    |   76 |
| 081113 | 严红      | 101    |   63 |
| 081113 | 严红      | 102    |   79 |
| 081113 | 严红      | 206    |   60 |
| 081201 | 王敏       | 101    |   80 |
| 081202 | 王林       | 101    |   65 |
| 081204 | 马琳琳       | 101    |   91 |
| 081210 | 李红庆       | 101    |   76 |
| 081216 | 孙祥欣       | 101    |   81 |
| 081218 | 孙研       | 101    |   70 |
| 081220 | 吴薇华      | 101    |   82 |
| 081221 | 刘燕敏       | 101    |   76 |
| 081241 | 罗林琳       | 101    |   90 |
+--------+--------+--------+------+
41 rows in set (0.00 sec)

mysql> SELECT xs.学号, xs.姓名, xs_kc.课程号, xs_kc.成绩
    -> FROM xs
    -> JOIN xs_kc ON xs.学号 = xs_kc.学号
    -> WHERE xs_kc.课程号 = 101 AND xs_kc.成绩 >= 80;
+--------+--------+--------+------+
| 学号      | 姓名       | 课程号     | 成绩    |
+--------+--------+--------+------+
| 081101 | 王林       | 101    |   80 |
| 081104 | 韦平平    | 101    |   90 |
| 081108 | 林一帆      | 101    |   85 |
| 081110 | 张蔚      | 101    |   95 |
| 081111 | 赵琳       | 101    |   91 |
| 081201 | 王敏       | 101    |   80 |
| 081204 | 马琳琳       | 101    |   91 |
| 081216 | 孙祥欣       | 101    |   81 |
| 081220 | 吴薇华      | 101    |   82 |
| 081241 | 罗林琳       | 101    |   90 |
+--------+--------+--------+------+
10 rows in set (0.00 sec)

mysql> SELECT kc.课程号, kc.课程名, xs_kc.学号, xs_kc.成绩
    -> FROM kc
    -> LEFT JOIN xs_kc ON kc.课程号 = xs_kc.课程号;
+--------+----------------+--------+------+
| 课程号     | 课程名              | 学号      | 成绩    |
+--------+----------------+--------+------+
| 101    | 计算机基础               | 081101 |   80 |
| 101    | 计算机基础               | 081103 |   62 |
| 101    | 计算机基础               | 081104 |   90 |
| 101    | 计算机基础               | 081106 |   65 |
| 101    | 计算机基础               | 081107 |   78 |
| 101    | 计算机基础               | 081108 |   85 |
| 101    | 计算机基础               | 081109 |   66 |
| 101    | 计算机基础               | 081110 |   95 |
| 101    | 计算机基础               | 081111 |   91 |
| 101    | 计算机基础               | 081113 |   63 |
| 101    | 计算机基础               | 081201 |   80 |
| 101    | 计算机基础               | 081202 |   65 |
| 101    | 计算机基础               | 081203 |   87 |
| 101    | 计算机基础               | 081204 |   91 |
| 101    | 计算机基础               | 081210 |   76 |
| 101    | 计算机基础               | 081216 |   81 |
| 101    | 计算机基础               | 081218 |   70 |
| 101    | 计算机基础               | 081220 |   82 |
| 101    | 计算机基础               | 081221 |   76 |
| 101    | 计算机基础               | 081241 |   90 |
| 102    | 程序设计与语言               | 081101 |   78 |
| 102    | 程序设计与语言               | 081102 |   78 |
| 102    | 程序设计与语言               | 081103 |   70 |
| 102    | 程序设计与语言               | 081104 |   84 |
| 102    | 程序设计与语言               | 081106 |   71 |
| 102    | 程序设计与语言               | 081107 |   80 |
| 102    | 程序设计与语言               | 081108 |   64 |
| 102    | 程序设计与语言               | 081109 |   83 |
| 102    | 程序设计与语言               | 081110 |   90 |
| 102    | 程序设计与语言               | 081111 |   70 |
| 102    | 程序设计与语言               | 081113 |   79 |
| 206    | 离散数学             | 081101 |   76 |
| 206    | 离散数学             | 081102 |   78 |
| 206    | 离散数学             | 081103 |   81 |
| 206    | 离散数学             | 081104 |   65 |
| 206    | 离散数学             | 081106 |   80 |
| 206    | 离散数学             | 081107 |   68 |
| 206    | 离散数学             | 081108 |   87 |
| 206    | 离散数学             | 081109 |   70 |
| 206    | 离散数学             | 081110 |   89 |
| 206    | 离散数学             | 081111 |   76 |
| 206    | 离散数学             | 081113 |   60 |
| 208    | 数据结构             | NULL   | NULL |
| 209    | 操作系统             | NULL   | NULL |
| 210    | 计算机原理              | NULL   | NULL |
| 212    | 数据库原理             | NULL   | NULL |
| 301    | 计算机网络               | NULL   | NULL |
| 302    | 软件工程               | NULL   | NULL |
+--------+----------------+--------+------+
48 rows in set (0.00 sec)

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

mysql> use yggl;
Database changed
mysql> select employees.编号,姓名,(salary.收入 - salary.支出) AS 工资 from employees join salary on employees.编号=salary.编号;
+--------+--------+--------------------+
| 编号       | 姓名       | 工资                   |
+--------+--------+--------------------+
| 000001 | 王林       | 1977.7100524902344 |
| 010008 | 伍容华      | 1494.5899963378906 |
| 020010 | 王向容       |               2662 |
| 020018 | 李丽       |  2167.679931640625 |
| 102201 | 刘明       | 2384.2298889160156 |
| 102208 | 朱俊     |               1880 |
| 108991 | 钟敏       |  2978.459991455078 |
| 111006 | 张石兵      | 2268.0999298095703 |
| 210678 | 林涛       |               2119 |
| 302566 | 李玉珉       |  2770.499954223633 |
| 308759 | 叶凡      | 2332.8999786376953 |
| 504209 | 陈林琳       |   1958.14990234375 |
+--------+--------+--------------------+
12 rows in set (0.00 sec)

mysql> select employees.编号,姓名,departments.部门名称 from employees inner join departments on employees.部门号=salary.部门号 where employees.姓名=王林;
ERROR 1054 (42S22): Unknown column '王林' in 'where clause'
mysql> SELECT employees.编号, employees.姓名, departments.部门名称
    -> FROM employees
    -> INNER JOIN departments ON employees.部门号 = departments.部门号
    -> WHERE employees.姓名 = '王林';
+--------+------+------------+
| 编号       | 姓名     | 部门名称           |
+--------+------+------------+
| 000001 | 王林     | 人力资源部          |
+--------+------+------------+
1 row in set (0.00 sec)

mysql> SELECT employees.编号, employees.部门号, departments.部门名
    -> FROM employees
    -> INNER JOIN departments ON employees.部门号 = departments.部门号
    -> WHERE employees.部门号 != 5;
ERROR 1054 (42S22): Unknown column 'departments.部门名' in 'field list'
mysql> -- 假设正确的列名为 "部门名称" 而非 "部门名"
mysql> SELECT employees.编号, employees.部门号, departments.部门名称
    -> FROM employees
    -> INNER JOIN departments ON employees.部门号 = departments.部门号
    -> WHERE employees.部门号 != 5;
+--------+--------+------------+
| 编号       | 部门号      | 部门名称           |
+--------+--------+------------+
| 010008 | 1      | 财务部           |
| 020010 | 1      | 财务部           |
| 020018 | 1      | 财务部           |
| 000001 | 2      | 人力资源部          |
| 108991 | 3      | 经理办公室         |
| 210678 | 3      | 经理办公室         |
| 302566 | 4      | 研发部          |
| 303466 | 4      | 研发部          |
| 308759 | 4      | 研发部          |
| 504209 | 4      | 研发部          |
+--------+--------+------------+
10 rows in set (0.00 sec)

mysql> SELECT s1.编号 AS 编号1, s2.编号 AS 编号2, s1.收入
    -> FROM salary s1
    -> INNER JOIN salary s2 ON s1.收入 = s2.收入 AND s1.编号 <> s2.编号
    -> GROUP BY s1.收入
    -> HAVING COUNT(*) >= 2;
+--------+--------+---------+
| 编号1      | 编号2      | 收入        |
+--------+--------+---------+
| 111006 | 020018 | 2347.68 |
+--------+--------+---------+
1 row in set (0.01 sec)

mysql> SELECT e.编号, e.姓名
    -> FROM employees e
    -> LEFT JOIN salary s ON e.编号 = s.编号
    -> WHERE s.编号 IS NULL;
+--------+--------+
| 编号       | 姓名       |
+--------+--------+
| 303466 | 刘大宝       |
+--------+--------+
1 row in set (0.00 sec)