10.27周测

最新推荐文章于 2024-11-29 19:29:06 发布

原创最新推荐文章于 2024-11-29 19:29:06 发布 · 165 阅读

0 ·

CC 4.0 BY-SA版权

作业专栏专栏收录该内容

9 篇文章

订阅专栏

本文通过具体的SQL语句展示了如何创建学生和成绩表，并提供了查询学生信息、成绩统计等实用示例，帮助读者掌握SQL的基本操作。

CREATE TABLE student (id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,sex VARCHAR(4),birth YEAR,department VARCHAR(20),address VARCHAR(50));

CREATE TABLE score (id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,stu_id INT(10) NOT NULL,c_name VARCHAR(20),grade INT(10));

SELECT * FROM student;

SELECT * FROM student LIMIT 1, 3;

SELECT id,name,department FROM student;

SELECT * FROM student WHERE department IN ('计算机系','英语系');

SELECT id,name,sex,2017-birth,department,address FROM student WHERE 2017-birth BETWEEN 22 AND 27;

SELECT department,COUNT(id) FROM student GROUP BY department;

SELECT c_name,MAX(grade) FROM score GROUP BY c_name;

SELECT c_name,grade FROM score WHERE stu_id = (SELECT id FROM student WHERE `name` = '李四');

SELECT st.id,name,sex,birth,department,address,c_name,grade FROM student st,score sc WHERE st.id = sc.stu_id;

SELECT st.id,name,SUM(grade) FROM student st,score sc WHERE st.id = sc.stu_id GROUP BY st.id;

SELECT c_name,AVG(grade) FROM score GROUP BY c_name;

SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name = '计算机' AND grade < 95);

SELECT * FROM student WHERE id = ANY(SELECT stu_id FROM score WHERE stu_id IN(SELECT stu_id FROM score WHERE c_name = '计算机')AND c_name = '英语');

SELECT stu_id,grade FROM score WHERE c_name = '计算机' ORDER BY grade DESC;

SELECT id FROM student UNION (SELECT stu_id FROM score);

SELECT st.id,`name`,sex,birth,department,address,c_name,grade FROM student st,score sc WHERE (`name` LIKE '张%' OR `name` LIKE '王%') AND st.id = sc.stu_id;

SELECT st.id,`name`,sex,birth,department,address,c_name,grade FROM student st,score sc WHERE address LIKE '湖南%' AND st.id = sc.stu_id;