SELECT * FROM (SELECT c_id FROM student st INNER JOIN score sc ON sc.s_id =st.s_id where st.s_id ="01") tb1,(select * from student st,score sc WHERE sc.s_id=st.s_id) tb2 WHERE tb1.s_id=tb2.s_id哪里有错误

时间: 2024-04-27 12:22:43 浏览: 84

数据库表数据的查询操作.docx

在本实验中，主要涉及了MySQL数据库管理系统中的数据查询操作，包括对数据库表`student`和`score`的SELECT语句应用。以下是根据实验要求详细解释的各个知识点： 1. **SELECT语句基本语法**：SELECT语句用于从数据库中检索指定的数据。基本语法是`SELECT column1, column2... FROM table_name;`。 2. **查询所有记录**：使用`SELECT * FROM table_name;`可以获取表中的所有记录。`*`代表所有列。 3. **查询指定范围的记录**：例如，要获取`student`表的第二到第四条记录，可以使用`LIMIT`子句，如`SELECT * FROM student LIMIT 1, 3;`，其中1是起始位置，3是记录数。 4. **选择特定列**：`SELECT Stu_no, name, department FROM student;`将只返回学号、姓名和院系信息。 5. **使用IN和OR关键字**：查询特定条件下的记录，如计算机系和英语系的学生，可以写为`WHERE department IN ('计算机系', '英语系')`或`WHERE department = '计算机系' OR department = '英语系'`。 6. **BETWEEN AND与比较运算符**：查询18至22岁学生，使用`BETWEEN`是`WHERE birthday BETWEEN '1999-01-01' AND '2003-12-31'`；使用AND是`WHERE birthday >= '1999-01-01' AND birthday <= '2003-12-31'`。 7. **GROUP BY与聚合函数COUNT**：`SELECT department, COUNT(*) AS sum_of_department FROM student GROUP BY department;`用于计算每个院系的学生人数，并给计数列取别名。 8. **查询最高分**：`SELECT C_no, MAX(grade) FROM SC GROUP BY C_no;`返回每个科目的最高分。 9. **特定学生的考试信息**：`SELECT c_name, grade FROM SC WHERE Stu_no = 905;`可查询李五一的考试科目和成绩。 10. **连接查询**：`SELECT student.*, SC.* FROM student INNER JOIN SC ON student.Stu_no = SC.Stu_no;`将所有学生信息与考试信息连接在一起。 11. **计算学生总成绩**：这需要使用子查询和聚合函数SUM，例如`SELECT name, (SELECT SUM(grade) FROM SC WHERE SC.Stu_no = student.Stu_no) AS total_grade FROM student;` 12. **计算平均成绩**：`SELECT C_no, AVG(grade) FROM SC GROUP BY C_no;`返回每个科目的平均成绩。 13. **条件查询**：`SELECT * FROM student WHERE Stu_no IN (SELECT Stu_no FROM SC WHERE C_no = '计算机' AND grade < 95);`找出计算机成绩低于95的学生。 14. **同时参加多科目考试的学生**：使用`INNER JOIN`和`GROUP BY`以及`HAVING`，例如`SELECT student.Stu_no, student.name FROM student INNER JOIN SC ON student.Stu_no = SC.Stu_no GROUP BY student.Stu_no HAVING COUNT(DISTINCT SC.C_no) = 2 AND (SC.C_no = '计算机' OR SC.C_no = '英语');` 15. **排序查询**：`SELECT Stu_no FROM student WHERE Stu_no IN (SELECT Stu_no FROM SC WHERE C_no = '计算机') ORDER BY grade DESC;`按计算机成绩降序排列。 16. **合并查询结果**：使用`UNION`操作符，如`SELECT Stu_no FROM student UNION SELECT Stu_no FROM SC;`合并两个表的学号。 17. **复合条件查询**：`SELECT name, department, c_name, grade FROM student s INNER JOIN SC sc ON s.Stu_no = sc.Stu_no WHERE s.name LIKE '张%' OR s.name LIKE '王%';`查找姓张或姓王的学生信息。 18. **筛选特定地区学生**：`SELECT name, age, department, c_name, grade FROM (SELECT name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, * FROM student) s INNER JOIN SC sc ON s.Stu_no = sc.Stu_no WHERE s.address LIKE '%湖南%';`查询所有来自湖南的学生信息。以上就是实验中涉及的MySQL查询操作，包括基础查询、条件查询、聚合函数、连接查询、排序和组合查询等。通过这些操作，可以实现对数据库中数据的多样化查询和分析。

这个查询语句本身没有语法错误，但是可能存在逻辑错误。具体来说，它将两张表进行了笛卡尔积，然后筛选出选课与学号为"01"的同学相同的学生信息。这样做的结果是会返回符合条件的学生信息，但是可能会有重复的记录，因为同一学生可能会有多门课程与学号为"01"的同学所学相同。如果要避免重复的记录，可以使用 DISTINCT 关键字去重，或者使用 GROUP BY 子句对结果进行分组。

阅读全文

SELECT * FROM (SELECT c_id FROM student st INNER JOIN score sc ON sc.s_id =st.s_id where st.s_id ="01") tb1,(select * from student st,score sc WHERE sc.s_id=st.s_id) tb2 WHERE tb1.s_id=tb2.s_id哪里有错误

相关推荐

学生_课程_成绩_教师50个常用sql

经典SQL面试题.doc

SELECT * FROM (SELECT c_id FROM student st INNER JOIN score sc ON sc.s_id =st.s_id where st.s_id ="01") tb1,(select DISTINCT (st.s_id),st.s_name from student st,score sc WHERE sc.s_id=st.s_id) tb2 WHERE tb1.s_id=tb2.s_id

SELECT DISTINCT tb2.* FROM (SELECT c_id FROM student st INNER JOIN score sc ON sc.s_id =st.s_id where st.s_id ="01") tb1,(select * from student st,score sc WHERE sc.s_id=st.s_id) tb2 WHERE tb1.s_id=tb2.s_id > 1060 - Duplicate column name 's_id' > 查询时间: 0s

SELECT DISTINCT tb2.s_name FROM (SELECT c_id FROM student st INNER JOIN score sc ON sc.s_id =st.s_id where st.s_id ="01") tb1,(select st.s_id,st.s_name,sc.c_id,sc.s_score from student st inner join score sc on sc.s_id=st.s_id) tb2 WHERE tb1.c_id=tb2.c_id

select * from student st inner join score sc on st.s_id=sc.s_id where sc.c_id="01"and sc.c_id !="02" -- -- -- 10、查询没有学全所有课程的同学的信息 修改符合这个语句

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 SELECT st.s_name, sc.s_score,sc.c_id FROM student st INNER JOIN score sc ON st.s_id = sc.s_id WHERE sc.c_id=01 < ALL (SELECT s_score FROM score WHERE c_id = 02)and sc.c_id !=02 and sc.c_id !=03 帮我修改

8、查询没学过"张三"老师讲授的任一门课程的学生姓名 select * from student st inner join score sc on st.s_id =sc.s_id inner join course co on sc.c_id=co.c_id inner join teacher te on co.t_id=te.t_id where te.t_name ="张三"

select s_name from student, (select st.s_name from score sc inner JOIN student st ON st.s_id =sc.s_id INNER JOIN course c ON sc.c_id=c.c_id) demo1 where student.s_name = demo1.s_name报错Column 's_name' in field list is ambiguous

SELECT st.s_name, MIN(sc.s_score) AS min_score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id AND sc.s_score < 60 GROUP BY st.s_name HAVING COUNT(*) >= 2;如果有学生没有参加考试

select group_concat(sc2.c_id) from student st2 left join score sc2 on sc2.s_id=st2.s_id where st2.s_id ='01'

SELECT student.s_name FROM student, (SELECT DISTINCT st.s_name, sc.c_id FROM score sc INNER JOIN student st ON st.s_id = sc.s_id INNER JOIN course c ON sc.c_id = c.c_id) demo1 WHERE student.s_name = demo1.s_name学生表中有没有课程的学生,怎么修改sql能查询出来

select s.* from student s inner join sc ,course c ,teacher t on sc.SNO =s.SNO and c.CNO =sc.CNO and t.TNO =c.TNO where t.TNAME = '张三' order by sc.SCORE desc limit 1,1 这串sql有什么问题，怎么解决

select s.SId,Sname,avg(score) average from Student s inner join(select SId,score,count(*) count1 from SC111 c where score <60 group by SId,score having count(*)>=2) ddd on s.SId=ddd.SId

设计存储过程S_C_SC，求出某课程的考试学生姓名和成绩；并执行验证

USE teaching SELECT A.sno, sname, C.cno, cname, score FROM student A ,sc B , course C where B.cno=C.cno and A.sno=B.sno AND score>=90 该程序的功能是：

SQL 错误 [1055] [42000]: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'rlstudy.s.SCORE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

MYSQL_MSS_ORACLE经典SQL

最新推荐

计算机基础知识及应用技术总结

基于51单片机RFID智能门禁系统红外人流量计数统计.zip

平尾装配工作平台运输支撑系统设计与应用

管理建模和仿真的文件

MATLAB遗传算法探索：寻找随机性与确定性的平衡艺术

如何在S7-200 SMART PLC中使用MB_Client指令实现Modbus TCP通信？请详细解释从连接建立到数据交换的完整步骤。

MAX-MIN Ant System：用MATLAB解决旅行商问题

"互动学习：行动中的多样性与论文攻读经历"

【实战指南】MATLAB自适应遗传算法调整：优化流程全掌握

在Spring AOP中，如何实现一个环绕通知并在方法执行前后插入自定义逻辑？

select * from student st inner join score sc on st.s_id=sc.s_id where sc.c_id="01"and sc.c_id !="02" -- -- -- 10、查询没有学全所有课程的同学的信息修改符合这个语句

select s.SId,Sname,avg(score) average from Student s inner join(select SId,score,count() count1 from SC111 c where score <60 group by SId,score having count()>=2) ddd on s.SId=ddd.SId