50个常用的SQL语句.docx

1、50个常用的SQL语句(1) 数据记录筛选： sql= select * from 数据表 where 字段名=字段值 order by 字段名 desc sql= select * from 数据表 where 字段名 like %字段值% order by 字段名 desc sql= select top 10 * from 数据表 where 字段名 order by 字段名 desc sql= select * from 数据表 where 字段名 in ( 值1 , 值2 , 值3 ) sql= select * from 数据表 where 字段名 between 值1 and 值

2、2 (2) 更新数据记录： sql= update 数据表 set 字段名=字段值 where 条件表达式 sql= update 数据表 set 字段1=值1,字段2=值2 字段n=值n where 条件表达式 (3) 删除数据记录： sql= delete from 数据表 where 条件表达式 sql= delete from 数据表 (将数据表所有记录删除) (4) 添加数据记录： sql= insert into 数据表 (字段1,字段2,字段3 ) valuess (值1,值2,值3 ) sql= insert into 目标数据表 select * from 源数据表 (把源数

3、据表的记录添加到目标数据表) (5) 数据记录统计函数： AVG(字段名) 得出一个表格栏平均值 COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计 MAX(字段名) 取得一个表格栏最大的值 MIN(字段名) 取得一个表格栏最小的值 SUM(字段名) 把数据栏的值相加 引用以上函数的方法： sql= select sum(字段名) as 别名 from 数据表 where 条件表达式 set rs=conn.excute(sql) 用 rs( 别名 ) 获取统的计值，其它函数运用同上。 (6) 数据表的建立和删除： CREATE TABLE 数据表名称(字段1 类型1(长度

4、),字段2 类型2(长度) ) 例：CREATE TABLE tab01(name varchar(50),datetime default now() DROP TABLE 数据表名称 (永久性删除一个数据表) string cnString = 连接字符串 ; SqlConnection cn = new SqlConnection(cnString); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = select * from pubs ; SqlDataReader dr = cmd.E

5、xecuteReader(); while(dr.Read() Response.Write(dr0.ToString(); dr.Close(); int result = cmd.ExecuteNonQuery(); cn.Close(); cmd.Dispose(); cn.Dispose();string cnString = 连接字符串 ; SqlConnection cn = new SqlConnection(cnString); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = s

6、elect * from pubs ; SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read() Response.Write(dr0.ToString(); dr.Close(); cmd.CommandText = update tablename set a= a ; int result = cmd.ExecuteNonQuery(); cn.Close(); cmd.Dispose(); cn.Dispose(); Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S

7、#,C#,score) 成绩表Teacher(T#,Tname) 教师表create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2) 前面加一列序号：ifexists(select table_name from information_schema.tables where table_name=Temp_Table)drop table Temp_Tablegoselect 排名=identity(int,1,1),* INTO Temp_Table from Student goselect

8、* from Temp_Tablego drop database -删除空的没有名字的数据库问题：1、查询“”课程比“”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#=001) a,(select s#,score from SC where C#=002) b where a.scoreb.score and a.s#=b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) 60;

9、3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname) from Teacher where Tname like 李%; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname from S

10、tudent where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=叶平); 6、查询学过“”并且也学过编号“”课程的同学的学号、姓名； select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=001and exists( Select * from SC as SC_2 where SC

11、_2.S#=SC.S# and SC_2.C#=002); 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=叶平 group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T#

12、 and Tname=叶平); 8、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名； Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=002) score2 from Student,SC where Student.S#=SC.S# and C#=001) S_2 where score2 60); 10、查询没有学全所有课的同学的学号、姓名； select Student.

13、S#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) =60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 FROM SC T,Course where t.C#=course.C# GROUP BY t.C# ORDER BY 100 * SUM(CASE WHEN isnull(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 20、查询如下课程平均成绩和及格率的百

14、分数(用1行显示): 企业管理（），马克思（），OO&UML （），数据库（） SELECT SUM(CASE WHEN C# =001 THEN score ELSE 0 END)/SUM(CASE C# WHEN 001 THEN 1 ELSE 0 END) AS 企业管理平均分 ,100 * SUM(CASE WHEN C# = 001 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 001 THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN C# = 002 THEN score ELS

15、E 0 END)/SUM(CASE C# WHEN 002 THEN 1 ELSE 0 END) AS 马克思平均分 ,100 * SUM(CASE WHEN C# = 002 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 002 THEN 1 ELSE 0 END) AS 马克思及格百分数 ,SUM(CASE WHEN C# = 003 THEN score ELSE 0 END)/SUM(CASE C# WHEN 003 THEN 1 ELSE 0 END) AS UML平均分 ,100 * SUM(CASE WHEN C# =

16、003 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 003 THEN 1 ELSE 0 END) AS UML及格百分数 ,SUM(CASE WHEN C# = 004 THEN score ELSE 0 END)/SUM(CASE C# WHEN 004 THEN 1 ELSE 0 END) AS 数据库平均分 ,100 * SUM(CASE WHEN C# = 004 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = 004 THEN 1 ELSE 0 END) AS 数据

17、库及格百分数 FROM SC 21、查询不同老师所教不同课程平均分从高到低显示 SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 FROM SC AS T,Course AS C ,Teacher AS Z where T.C#=C.C# and C.T#=Z.T# GROUP BY C.C# ORDER BY AVG(Score) DESC 22、查询如下课程成绩第名到第名的学生成绩单：企业管理（），马克思（），UML （），数据库（） 学生ID,学

18、生姓名,企业管理,马克思,UML,数据库,平均成绩 SELECT DISTINCT top 3 SC.S# As 学生学号, Student.Sname AS 学生姓名, T1.score AS 企业管理, T2.score AS 马克思, T3.score AS UML, T4.score AS 数据库, ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分 FROM Student,SC LEFT JOIN SC AS T1 ON SC.S# = T1.S# AND T

19、1.C# = 001 LEFT JOIN SC AS T2 ON SC.S# = T2.S# AND T2.C# = 002 LEFT JOIN SC AS T3 ON SC.S# = T3.S# AND T3.C# = 003 LEFT JOIN SC AS T4 ON SC.S# = T4.S# AND T4.C# = 004 WHERE student.S#=SC.S# and ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) NOT IN (SELECT DISTINCT

20、TOP 15 WITH TIES ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) FROM sc LEFT JOIN sc AS T1 ON sc.S# = T1.S# AND T1.C# = k1 LEFT JOIN sc AS T2 ON sc.S# = T2.S# AND T2.C# = k2 LEFT JOIN sc AS T3 ON sc.S# = T3.S# AND T3.C# = k3 LEFT JOIN sc AS T4 ON sc.S# = T4.S# AND

21、T4.C# = k4 ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC); 23、统计列印各科成绩,各分数段人数:课程ID,课程名称,100-85,85-70,70-60, 60 SELECT SC.C# as 课程ID, Cname as 课程名称 ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS 100 - 85 ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS 85 - 70 ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS 70 - 60 ,SUM(CASE WHEN score T2.平均成绩) as 名次, S# as 学生学号,平均成绩 FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) AS T2 ORDER B