MYSQL-练习(student_score)

1. 创建student表

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

2. 创建score表

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

3. 查询student表中所有的记录

   SELECT*FROM student

4. 查询student的第二条到第四条记录

   SELECT * FROM student LIMIT 1,3

5. 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

   SELECT id,name,department FROM student

6. 从student表中查询计算机系和英语系的学生的信息（用 IN 关键字）

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

7. 从student表中查询年龄18~22岁的学生信息（用 BETWEEN AND）

   SELECT*FROM student
   WHERE 2013-birth BETWEEN 18 AND 22

8. 从student表中查询每个院系有多少人

   SELECT department,count(*)FROM student
   GROUP BY department

9. 从score表中查询每个科目的最高分

   SELECT c_name,max(grade) FROM score
   GROUP BY c_name

10. 查询李四的考试科目（c_name）和考试成绩（grade）

    SELECT c_name,grade FROM score
    WHERE stu_id=(select id FROM student WHERE name=’李四’)

SELECT c_name,grade
FROM score LEFT JOIN student ON score.stu_id=student.id
WHERE name=’李四’

1. 用连接的方式查询所有学生的信息和考试信息

   SELECT*FROM student ,score
   WHERE student.id=score.stu_id

2. 计算每个学生的总成绩

   SELECT student.id,name,sum(grade)FROM student,score
   WHERE student.id=score.stu_id
   GROUP BY id

3. 计算每个考试科目的平均成绩

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

4. 查询计算机成绩低于95的学生信息

   SELECT*FROM student
   LEFT JOIN score ON student.id=score.stu_id
   WHERE c_name=’计算机’ AND grade<95

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

1. 将计算机考试成绩按从高到低进行排序

   SELECT score.stu_id,grade FROM score
   WHERE c_name=’计算机’
   ORDER BY grade DESC

2. 从student表和score表中查询出学生的学号，然后合并查询结果

   SELECT student.id FROM student
   UNION
   SELECT score.stu_id FROM score

3. 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

   SELECT student.name,department,score.c_name,grade
   FROM student,score
   WHERE student.id=score.stu_id
   AND (name like’张%’or’王%’)

4. 查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

   SELECT student.name,department,address,score.c_name,grade
   FROM student,score
   WHERE student.id=score.stu_id
   AND (address LIKE’湖南%’)

5. 查询同时参加计算机和英语考试的学生的信息

   SELECT a.* FROM student a,Score b,Score c
   WHERE a.id=b.Stu_id
   AND b.C_name=’计算机’
   AND a.id=c.Stu_id
   AND c.C_name=’英语’;