1.然后新建一张学生表（T_User），拥有两个字段：id、姓名，学生表的id匹配表A里的学生id，查询语句通过连接实现（不考虑内外或者交叉连接的问题了），代码如下：

select UName,
(select AVG(Grades) from UserInfo where GName = '语文' and UName = tu.name) as '语文',
(select AVG(Grades) from UserInfo where GName = '数学' and UName = tu.name) as '数学',
(select AVG(Grades) from UserInfo where GName = '英语' and UName = tu.name) as '英语' 
from UserInfo as ui join T_User as tu
on ui.UName = tu.name
group by ui.UName,tu.name;

2.查询不同班级，不同课程的平均成绩，按照平均成绩从大到小排序
class表（id，class_name）;

student表（id,student_name,class_id）

score表（student_id,course_name,points）


查询不同班级不同课程的平均分，从大到小排序。

select 
c.class_name,s.course_name ,avg(points) 
from class c 
left join student stu on c.id=stu.class_id
left join score s on s.student_id = stu.id 
group by c.id,s.course_name 
order by avg(points) desc;


UNION
union:对两个结果集进行并集操作，重复数据只显示一次
union All:对两个结果集进行并集操作，重复数据全部显示

public class Student {
		 String name;
		 int age;
		public Student(String name,int age) {
			this.name=name;
			this.age=age;
		}
		
}

public class text {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Student[] a = new Student[5];
		a[0] = new Student("小红",20);
		a[1] = new Student("小明",18);
		a[2] = new Student("小蓝",20);
		a[3] = new Student("小绿",21);
		a[4] = new Student("小花",19);
		for(int i = 0; i<a.length;i++) {
			System.out.println(a[i].name+" "+a[i].age);
		}
	}

}

题目：某中学有若干学生(学生对象放在一个List中)，每个学生有一个姓名属性、班级名称属性(String)和考试成绩 属性(int)，某次考试结束后，每个学生都获得了一个 考试成绩。请打印出每个班级的总分和平均分。

学生类：为了方便这里只提供了get方法。

public class Student {
	private String name;
	private String className;
	private int grade;

	public String getName() {
		return name;
	}
	public String getClassName() {
		return className;
	}
	public int getGrade() {
		return grade;
	}
	
	public Student(String name, String className, int grade) {
		this.name = name;
		this.className = className;
		this.grade = grade;
	}
}

测试类

public class TestStudent {
	public static void main(String[] args) {
		// 给每个对象设置属性
		Student student1 = new Student("曹操", "魏国", 100);
		Student student2 = new Student("司马懿", "魏国", 98);
		Student student3 = new Student("张飞", "蜀国", 89);
		Student student4 = new Student("关羽", "蜀国", 89);
		Student student5 = new Student("赵云", "蜀国", 97);
		Student student6 = new Student("刘备", "蜀国", 96);
		Student student7 = new Student("荀彧", "魏国", 99);
		Student student8 = new Student("张郃", "魏国", 88);

		// 将对象放入list
		List<Object> list = new ArrayList<Object>();
		list.add(student1);
		list.add(student2);
		list.add(student3);
		list.add(student4);
		list.add(student5);
		list.add(student6);
		list.add(student7);
		list.add(student8);

		Student stu;
		// 定义两个存分数的数组
		List<Integer> wei = new ArrayList<Integer>();
		List<Integer> shu = new ArrayList<Integer>();

		// 将每个班的分数放到一个数组
		for (int i = 0; i < list.size(); i++) {
			stu = (Student) list.get(i);
			if (stu.getClassName().equals("魏国")) {
				wei.add(stu.getGrade());
			} else {
				shu.add(stu.getGrade());
			}
		}

		// 总分
		double shuSum = 0;
		double weiSum = 0;

		for (int i = 0; i < shu.size(); i++) {
			shuSum += shu.get(i);
			weiSum += wei.get(i);
		}
		System.out.println("魏国分数：" + wei);
		System.out.println("魏国的总分：" + weiSum + " 魏国的平均分：" + weiSum / wei.size());
		System.out.println("蜀国分数：" + shu);
		System.out.println("蜀国的总分：" + shuSum + " 蜀国的平均分：" + shuSum / shu.size());
	}
}