给定数据库中,包括“学生表”,“课程表”,“选课表”等三个数据表,表结构如下： 学生表：学号（C，6），姓 数据库中有如下三个表：学生表（Sno、SName……），选课...
\u5047\u8bbe\u201c\u5b66\u751f\u2014\u8bfe\u7a0b\u201d\u6570\u636e\u5e93\u4e2d\u5305\u542b\u5b66\u751f\u8868\u3001\u8bfe\u7a0b\u8868\u3001\u5b66\u751f\u9009\u8bfe\u88683\u4e2a\u8868--1select Sname,Sage from Student where Sage<(select Sage from Student where Sno='\u67d0\u4e00\u5b66\u751f') and Sdept='\u6570\u5b66\u7cfb' --2select Sname from Student where Sno in (select Sno from SC)--3select Sname from Student where Sno in (select Sno from SC group by Sno having count(*)=(select count(*) from Course ))--4\u9898\u610f\u4e0d\u6e05\u695a
select a.Sno as \u5b66\u53f7,Sname as \u59d3\u540d,avg(Score) as \u5e73\u5747\u5206from \u5b66\u751f\u8868 a inner join \u9009\u8bfe\u8868 b on a.Sno=b.Snoinner join \u8bfe\u7a0b\u8868 c on b.Cno=c.Cnogroup by a.Sno,Snamehaving avg(Score) > 70 and count(c.Cno) > 2order by \u5e73\u5747\u5206
用LINQ（语言集成查询）完成你说的这些任务很方便，具体的细节问题还得你自己做。下面举出两个例子供你参考，这两个例子用数组代替数据库，对数据库也可用同样的方法。例1，用bool值作关键字区分两组的结果，注意group子句中的表达式产生的结果。
public class Student
{
public string Name { get; set; }
public int ID { get; set; }
public List<int> Scores;
}
public static List<Student> GetStudents()
{
//实例化数据源，注意各成员内部成绩序列的初始化
List<Student> students = new List<Student>
{
new Student {Name="张勇", ID=1, Scores= new List<int> {97, 72, 81, 60}},
new Student {Name="王磊", ID=2 Scores= new List<int> {75, 84, 91, 39}},
new Student {Name="孙敏", ID=3, Scores= new List<int> {88, 94, 65, 85}},
new Student {Name="刘晓", ID=4, Scores= new List<int> {97, 89, 85, 82}},
new Student {Name="扬帆", ID=5, Scores= new List<int> {35, 72, 91, 70}}
};
return students;
}
static void Main()
{
List<Student> students = GetStudents();
//用true或false分组，查询变量是IEnumerable<IGrouping<bool, Student>>类型
var booleanGroupQuery = from student in students
group student by student.Scores.Average() >= 80;
foreach (var studentGroup in booleanGroupQuery)
{
Console.WriteLine(studentGroup.Key == true ? "平均分高于80" : "平均分低于80");
foreach (var student in studentGroup)
Console.WriteLine("{0,4} {1} {2}", student.ID, student.Name, student.Scores.Average());
}
}
输出：
平均分低于80
1 张勇 77.5
2 王磊 72.25
5 扬帆 67
平均分高于80
3 孙敏 83
4 刘晓 88.25
例2，查询人名先排序再分组，可以用姓作为分组关键字。
string[] Name = {"张明","刘新","王宏","刘洋","张媛","张宝","王金贵","刘东","王凯","刘芳"};
var sortedGroups = from name in Name
orderby name
group name by name[0] into newGroup
select newGroup;
foreach (var nameGroup in sortedGroups)
{
Console.WriteLine(nameGroup.Key);
foreach (var name in nameGroup)
Console.WriteLine(" "+name);
}
输出：
刘
刘东 刘芳 刘新 刘洋
王
王宏 王金贵 王凯
张
张宝 张明 张媛
输出结果按姓分类，组内排序。
《C#编程指南》（清华大学出版社2011年1月出版，可网购）第5章和第17章专门讨论LINQ查询更新数据库。
问什么
绛旓細1 select * from 瀛︾敓 where 涓撲笟 in ('璁＄畻鏈','鐢靛瓙')2 select count(*) from 璇剧▼ where 璇剧▼鍙 not in (select 璇剧▼鍙 from 閫夎)
绛旓細鍥1 瀛︾敓-璇剧▼鏁版嵁搴鍐欏嚭瀹屾垚涓嬪垪瑕佹眰鐨凷QL璇彞:1)鍐欏嚭鍒涘缓閫夎SC琛ㄧ殑SQL璇彞(鎻愮ず:鍚勫睘鎬у垪鐨勬暟鎹被鍨嬩笌瀹藉害銆佸叧閿瓧鍒楃殑閫夊彇瑕佸悎鐞,寤虹珛鐩稿簲鐨勭害鏉)2)鏌ヨ鎵鏈夊勾榫勫湪20宀佷互涓嬬殑瀛︾敓濮撳悕涓庡勾榫勩3)缁熻鐢风敓鍜屽コ鐢熺殑浜烘暟銆4)鏌ヨ姣忎釜瀛︾敓鍙婂叾閫変慨璇剧▼鐨勮绋嬪彿,璇剧▼鍚嶃5)鍒犻櫎瀛﹀彿涓衡95001鈥滅殑瀛︾敓鐨勯夎璁板綍銆
绛旓細1create table student(sno varchar(5) not null primary key,sname varchar(10),ssex char(2),sage int,sdept varchar(20)) 2alter table student add scome datetime 3select sno,grade from sg where cno=5 order by grade desc 4select max(grade) as 鏈楂樻垚缁,avg(grade) as 骞冲潎鎴愮哗 ...
绛旓細1) select s.Name from Student s join StuCur c on s.id=c.sid join Course cu on cu.id=c.cid where cu.Name='鑷劧'2) select c.Name from Course c join StuCur sc on c.id=sc.CID join Student s on sc.sid=s.id where s.EntranceTime between '1999-01-01' and '2012...
绛旓細鍔犱竴鍒楀唴瀹逛负褰撳墠骞存湀锛屾牸寮忎负鏃ユ湡鏃堕棿鍨嬶紝鐒跺悗鍦ㄥ悗闈㈢殑涓鍒椾腑鍋氬嚭鐢熷勾鏈堝垪鍜屽綋鍓嶅勾鏈堝垪鐨勫樊鍊艰繍绠楋紝璁剧疆鏍煎紡涓烘暣鏁
绛旓細CREATE TABLE 瀛︾敓琛 (瀛﹀彿 VARCHAR(12) NOT NULL,濮撳悕 VARCHAR(20) NOT NULL,鎬у埆 VARCHAR(10) NOT NULL,骞撮緞 INT CHECK(骞撮緞>=0 AND 骞撮緞<=120),鎵鍦ㄧ郴 VARCHAR(20) NOT NULL,PRIMARY KEY (瀛﹀彿))CREATE TABLE 璇剧▼琛 (璇剧▼鍙 VARCHAR(12) NOT NULL,璇剧▼鍚 VARCHAR(20) NOT NULL,鍏堜慨...
绛旓細1.select c.Cname from Course c,SC s1 where s1.Cno=c.Cno and s1.Sno='95002'2.select Sname Ssex from Student s where s.Sno in (select c.Sno Course c ,SC c1 where c1.Cno=c.Cno='1' )3.select Sno from Student s where s.no in (select s1.Sno from Course...
绛旓細create table sc(sno char(100),cno char(100),grade number );alter table sc add constraint pk_1 primary key(sno,cno)alter table sc add constraint sno_1 foreign key(sno) references student(sno)alter table sc add constraint cno_1 foreign key(cno) references course(cno)select ...
绛旓細createtable 鎴愮哗琛 (瀛﹀彿intconstraintWZ1foreignkeyreferences 瀛︾敓琛(瀛﹀彿),璇剧▼鍙穋har(6)constraintWZ2foreignkeyreferences 璇剧▼琛(璇剧▼鍙),鎴愮哗 tinyintconstraint 鎴愮哗_ckcheck (鎴愮哗>=0 and 鎴愮哗<=100 ))锛2锛夐氳繃insert into鍛戒护鍚戝悇琛ㄤ腑杈撳叆璁板綍锛堟寜鐓ф墍缁欒〃鐨勫唴瀹癸級insert into 瀛︾敓琛...
绛旓細delete from 璇剧▼琛 where 璇剧▼鍙 = '8';update 瀛︾敓琛 set 瀹跺涵浣忓潃='娌冲寳鐭冲搴' where 濮撳悕='鏉庡洓';select 瀛﹀彿, 鎴愮哗 from 鎴愮哗琛 where 鎴愮哗 >= 60 and 鎴愮哗 <= 80;select sum(鎴愮哗) from 鎴愮哗琛 group by 璇剧▼鍙;select 瀛﹀彿, 鎴愮哗 from 鎴愮哗琛 order by 鎴愮哗 desc;select A.瀛﹀彿...
