using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CreateStudentTable
{
class Program
{
static void Main(string[] args)
{
string connection = "Data Source=MXC-PC; Initial Catalog=DataStudentsDataContext; Trusted_Connection = true";
DataStudentDataContext linq = new DataStudentDataContext(connection);
if (!linq.DatabaseExists())
{
linq.CreateDatabase();
}
string[] surNames = new string[] { "郑", "胡", "张", "李", "王", "赵", "刘", "钱", "孙", "周" };
string[] names = new string[] { "一", "二", "三", "四", "五", "六", "七", "八", "九", "十" };
int number = 20120001;
Random r = new Random();
// for (int i = 0; i < 20; i++ )
// {
// student stu = new student();
// stu.id = Guid.NewGuid().ToString("N");
// stu.grade = r.Next(1, 5);
// stu.number = (decimal)number++;
// stu.name = surNames[r.Next(surNames.Length)] + names[r.Next(names.Length)];
// stu.age = r.Next(16, 24);
// stu.result = r.Next(101);
// stu.sex = r.Next(2) == 0 ? "男" : "女";
//
// linq.student.InsertOnSubmit(stu);
// linq.SubmitChanges();
// }
var sts = from st in linq.student
orderby st.grade, st.sex, st.result descending
group st by new { st.grade, st.sex } into gst
select new { gst.Key, gst };
sts.ToList().ForEach(gs => { gs.gst.ToList().ForEach(st => Console.WriteLine(st)); });
//
Console.WriteLine("成绩合格的男生信息如下:");
var stssel = from st in linq.student
orderby st.result
where st.result >= 60 && st.sex=="男"
select st;
stssel.ToList().ForEach(st => Console.WriteLine(st));
Console.WriteLine("成绩不合格的学生信息如下:");
var stsDel = from st in linq.student
where st.result < 60
select st;
stsDel.ToList().ForEach(st => Console.WriteLine(st));
linq.student.DeleteAllOnSubmit(stsDel);
linq.SubmitChanges();
Console.WriteLine("姓王的学生信息如下:");
var stsUpd = from st in linq.student
where st.name.StartsWith("王")
select st;
stsUpd.ToList().ForEach(st => Console.WriteLine(st.ToString()));
Console.WriteLine("修改姓王的学生成绩");
foreach (var st in stsUpd)
{
if (st.result > 90)
{
st.result = 100;
}
else if (st.result < 60)
{
st.result = 60;
}
else
{
st.result += 10;
}
}
linq.SubmitChanges();
Console.WriteLine("修改后姓王的学生成绩");
stsUpd = from st in linq.student
where st.name.StartsWith("王")
select st;
stsUpd.ToList().ForEach(st => Console.WriteLine(st.ToString()));
Console.WriteLine("降序排列数据中各年级男生的成绩");
var stsSort = from st in linq.student
orderby st.grade
where st.sex == "男"
group st by st.grade into gst
select new
{
gst.Key,
sts = gst.OrderByDescending(p => p.result)
};
foreach (var gst in stsSort)
{
Console.WriteLine("{0}年级的男生信息如下:", gst.Key);
gst.sts.ToList().ForEach(p => Console.WriteLine(p.ToString()));
}
Console.WriteLine("统计各年级学生成绩总分和平均成绩:");
var stsSta = from st in linq.student
group st by st.grade into gst
select new
{
gst.Key,
sum = gst.Sum(p => p.result),
avg = gst.Average(p => p.age)
};
foreach (var gst in stsSta)
{
Console.WriteLine("{0}年级成绩总分为:{1}平均年龄为{2}", gst.Key, gst.sum, gst.avg);
}
Console.WriteLine("统计男生和女生成绩的最高分和最低分");
var stsBG = from st in linq.student
group st by st.sex into gst
select new
{
gst.Key,
max = gst.Max(p => p.result),
min = gst.Min(p => p.result)
};
foreach (var gst in stsBG)
{
Console.WriteLine("{0}最高费:{1} 最低分:{2}", gst.Key, gst.max, gst.min);
}
Console.WriteLine("查询数据库第11条到第15条的学生信息:");
var stsSkip = (from st in linq.student select st).Skip(10).Take(5);
stsSkip.ToList().ForEach(p => Console.WriteLine(p.ToString()));
}
}
}