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 C:\\Users\\Administrator\\Desktop\\文件接收柜\\sy1.sql;
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Failed to open file 'C:\\Users\Administrator\Desktop\文件接收柜\sy1.sql', error: 2
mysql> source C:/Users/Administrator/Desktop/文件接收柜/sy1.sql;
ERROR:
Failed to open file 'C:\Users\Administrator\Desktop\文件接收柜\sy1.sql', error: 2
mysql> \. C:/Users/Administrator/Desktop/文件接收柜/sy1.sql;
ERROR:
Failed to open file 'C:\Users\Administrator\Desktop\文件接收柜\sy1.sql;', error: 2
mysql> SOURCE C:/Users/Administrator/Desktop/文件接收柜/sy.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, 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, 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> SELECT xs.学号, xs.姓名, zy.专业名, SUM(kc.学分) AS 总学分
-> FROM xs
-> JOIN zy ON xs.专业号 = zy.专业号
-> JOIN xk ON xs.学号 = xk.学号
-> JOIN kc ON xk.课程号 = kc.课程号
-> GROUP BY xs.学号, xs.姓名, zy.专业名;
ERROR 1300 (HY000): Invalid utf8 character string: '\xD1\xA7\xBA'
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT xs.学号, xs.姓名, zy.专业名, SUM(kc.学分) AS 总学分
-> FROM xs
-> JOIN zy ON xs.专业号 = zy.专业号
-> JOIN xk ON xs.学号 = xk.学号
-> JOIN kc ON xk.课程号 = kc.课程号
-> GROUP BY xs.学号, xs.姓名, zy.专业名;
ERROR 1146 (42S02): Table 'lty.zy' doesn't exist
mysql> SELECT 学号, 姓名, 专业名, 总学分
-> FROM xs;
+--------+--------+----------+--------+
| 学号 | 姓名 | 专业名 | 总学分 |
+--------+--------+----------+--------+
| 081101 | 王林 | 计算机 | 50 |
| 081102 | 程明 | 计算机 | 50 |
| 081103 | 王燕 | 计算机 | 50 |
| 081104 | 韦平平 | 计算机 | 50 |
| 081106 | 李方方 | 计算机 | 50 |
| 081107 | 李明 | 计算机 | 54 |
| 081108 | 林一帆 | 计算机 | 52 |
| 081109 | 张强明 | 计算机 | 50 |
| 081110 | 张蔚 | 计算机 | 50 |
| 081111 | 赵琳 | 计算机 | 50 |
| 081113 | 严红 | 计算机 | 48 |
| 081201 | 王敏 | 通信工程 | 42 |
| 081202 | 王林 | 通信工程 | 40 |
| 081204 | 马琳琳 | 通信工程 | 42 |
| 081206 | 李计 | 通信工程 | 42 |
| 081210 | 李红庆 | 通信工程 | 44 |
| 081216 | 孙祥欣 | 通信工程 | 42 |
| 081218 | 孙研 | 通信工程 | 42 |
| 081220 | 吴薇华 | 通信工程 | 42 |
| 081221 | 刘燕敏 | 通信工程 | 42 |
| 081241 | 罗林琳 | 通信工程 | 50 |
+--------+--------+----------+--------+
21 rows in set (0.00 sec)
mysql> SELECT 学号, 姓名,
-> CASE
-> WHEN 性别 = 0 THEN '女'
-> WHEN 性别 = 1 THEN '男'
-> ELSE '未知'
-> END AS 性别
-> FROM xs;
+--------+--------+------+
| 学号 | 姓名 | 性别 |
+--------+--------+------+
| 081101 | 王林 | 男 |
| 081102 | 程明 | 男 |
| 081103 | 王燕 | 女 |
| 081104 | 韦平平 | 男 |
| 081106 | 李方方 | 男 |
| 081107 | 李明 | 男 |
| 081108 | 林一帆 | 男 |
| 081109 | 张强明 | 男 |
| 081110 | 张蔚 | 女 |
| 081111 | 赵琳 | 女 |
| 081113 | 严红 | 女 |
| 081201 | 王敏 | 男 |
| 081202 | 王林 | 男 |
| 081204 | 马琳琳 | 女 |
| 081206 | 李计 | 男 |
| 081210 | 李红庆 | 男 |
| 081216 | 孙祥欣 | 男 |
| 081218 | 孙研 | 男 |
| 081220 | 吴薇华 | 女 |
| 081221 | 刘燕敏 | 女 |
| 081241 | 罗林琳 | 女 |
+--------+--------+------+
21 rows in set (0.00 sec)
mysql> SELECT 学号, 姓名, YEAR(出生日期) AS 出生年
-> FROM xs;
+--------+--------+--------+
| 学号 | 姓名 | 出生年 |
+--------+--------+--------+
| 081101 | 王林 | 1994 |
| 081102 | 程明 | 1995 |
| 081103 | 王燕 | 1993 |
| 081104 | 韦平平 | 1994 |
| 081106 | 李方方 | 1994 |
| 081107 | 李明 | 1994 |
| 081108 | 林一帆 | 1993 |
| 081109 | 张强明 | 1993 |
| 081110 | 张蔚 | 1995 |
| 081111 | 赵琳 | 1994 |
| 081113 | 严红 | 1993 |
| 081201 | 王敏 | 1993 |
| 081202 | 王林 | 1993 |
| 081204 | 马琳琳 | 1993 |
| 081206 | 李计 | 1993 |
| 081210 | 李红庆 | 1993 |
| 081216 | 孙祥欣 | 1993 |
| 081218 | 孙研 | 1994 |
| 081220 | 吴薇华 | 1994 |
| 081221 | 刘燕敏 | 1993 |
| 081241 | 罗林琳 | 1994 |
+--------+--------+--------+
21 rows in set (0.00 sec)
mysql> SELECT 学号, AVG(成绩) AS 平均成绩
-> FROM cj
-> WHERE 学号 = '081101'
-> GROUP BY 学号;
ERROR 1146 (42S02): Table 'lty.cj' doesn't exist
mysql> SELECT 学号, AVG(成绩) AS 平均成绩
-> FROM xs
-> WHERE 学号 = '081101'
-> GROUP BY 学号;
ERROR 1054 (42S22): Unknown column '成绩' in 'field list'
mysql> SELECT xs.学号, AVG(xs_kc.成绩) AS 平均成绩
-> FROM xs
-> JOIN xs_kc ON xs.学号 = xs_kc.学号
-> WHERE xs.学号 = '081101'
-> GROUP BY xs.学号;
+--------+----------+
| 学号 | 平均成绩 |
+--------+----------+
| 081101 | 78.0000 |
+--------+----------+
1 row in set (0.01 sec)
mysql> SELECT 课程号, MAX(成绩) AS 最高分
-> FROM xs_kc
-> WHERE 课程号 = '102';
+--------+--------+
| 课程号 | 最高分 |
+--------+--------+
| 102 | 90 |
+--------+--------+
1 row in set (0.01 sec)
mysql> SELECT xs_kc.学号, COUNT(xs_kc.学号) AS '90分以上人数', GROUP_CONCAT(xs.姓名) AS '学生名单'
-> FROM xs_kc
-> JOIN xs ON xs_kc.学号 = xs.学号
-> WHERE xs_kc.成绩 >= 90
-> GROUP BY xs_kc.学号;
+--------+--------------+-----------+
| 学号 | 90分以上人数 | 学生名单 |
+--------+--------------+-----------+
| 081104 | 1 | 韦平平 |
| 081110 | 2 | 张蔚,张蔚 |
| 081111 | 1 | 赵琳 |
| 081204 | 1 | 马琳琳 |
| 081241 | 1 | 罗林琳 |
+--------+--------------+-----------+
5 rows in set (0.00 sec)
mysql> show database;
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 'database' at line 1
mysql> SELECT xs_kc.`课程号`, GROUP_CONCAT(xs_kc.`学号`) AS `90分以上学生名单`
-> FROM xs_kc
-> WHERE xs_kc.`成绩` >= 90
-> GROUP BY xs_kc.`课程号`;
+--------+------------------------------------+
| 课程号 | 90分以上学生名单 |
+--------+------------------------------------+
| 101 | 081104,081110,081111,081204,081241 |
| 102 | 081110 |
+--------+------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT GROUP_CONCAT(xs_kc.`学号`) AS `90分以上学生名单`
-> FROM xs_kc
-> WHERE xs_kc.`成绩` >= 90;
+-------------------------------------------+
| 90分以上学生名单 |
+-------------------------------------------+
| 081104,081110,081110,081111,081204,081241 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT 学生学号, COUNT(学生学号) AS '90分以上人数'
-> FROM xs_kc
-> WHERE 成绩 >= 90
-> GROUP BY 学生学号;
ERROR 1054 (42S22): Unknown column '学生学号' in 'field list'
mysql> SELECT GROUP_CONCAT(xs_kc.`学号`) AS 学号, COUNT(*) AS 人数
-> FROM xs_kc
-> WHERE xs_kc.`成绩` >= 90;
+-------------------------------------------+------+
| 学号 | 人数 |
+-------------------------------------------+------+
| 081104,081110,081110,081111,081204,081241 | 6 |
+-------------------------------------------+------+
1 row in set (0.00 sec)
MySQL3
于 2024-03-25 17:30:04 首次发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)