50个常用sql语句实例(学生表课程表成绩表教师表)

合集下载

Sql语句之查询所有学生所有科目分数及总分

Sql语句之查询所有学⽣所有科⽬分数及总分
昨天练Sql语句,数据库建了四个表分别是,学⽣表,课程表,成绩表以及教师表(教师表不在讨论范围),突然想到以前⾼中时代⽼师⼿上的那张成绩表,然后我就寻思着能不能⽤Sql语句把表打印出来,以下是我的思考过程:
话不多说,先上三张表的图
如下图1.1学⽣表(Student表)
S#-学号 Sname-姓名 Sage-年龄 Ssex-性别
图1.1 Student表
如下图1.2课程表(Course表)
C#-课程号 Cname-课程名 T#-教师号
图1.2课程表 Course表
如下图1.3成绩表（CS表）
S#-学⽣号 C#-课程号 score-成绩
图 1.3课程表Course表
⾸先,我初步查询了所有⼈所有科⽬成绩
Sql语句如下:
查询结果如下:
成绩是打印出来了,可是有点不好看,重复的数据太多了,于是继续寻思精简⼀点的。

如果要精简，那么学科就不能是Cname了，⽽是将⼀个个学科打出来，就像成绩单，各科成绩名和列名在同⼀列，于是乎我想到了case then else end
sql语句如下：
然⽽结果并没有Group by,结果如下：
想来想去我就⽤表的联结join，然后成功了。

sql语句如下：
结果如下：
哦！忘记排名了：
重写sql如下：
结果如下：
另外有⼏科⽬成绩没有加进来，所以总分有些加了不够。

差不多就是这样了。

sql-hive笔试题整理1（学生表-成绩表-课程表-教师表）

sql-hive笔试题整理1（学⽣表-成绩表-课程表-教师表）题记：⼀直在写各种sql查询语句，最长的有⼀百多⾏，⾃信什么需求都可以接，可。

，想了想，可能⼀直在固定的场景下写，平时也是以满⾜实际需求为⽬的，竟不知道应试的题都是怎么出的，⼜应该怎么做。

遂找来⼀些笔试题来练习。

有四张表如下格式存储：--1、查询“001”课程⽐“002”课程成绩⾼的所有学⽣的学号；select t1.s# from(select s#,c#,score from sc where c# = 001) t1 inner join (select s#,c#,score from sc where c# = 002) t2on t1.s#=t2.s#where t1.score > t2.score--2、查询平均成绩⼤于60分的同学的学号和平均成绩；select s#,avg(score)from scgroup by s# having avg(score)>60--3、查询所有同学的学号、姓名、选课数、总成绩；select student.s#,student.sname,count(sc.c#),sum(sc.score)from student left join sc on student.s#=sc.s#group by student.s#,student.sname--4、查询姓“李”的⽼师的个数；select count(t#)from teacherwhere tname like '李%'--5、查询没学过“叶平”⽼师课的同学的学号、姓名；select t1.s#,t1.snamefrom student t1left join sc t2 on t1.s#=t2.s#left join course t3 on t2.c#=t3.c#left join teacher t4 on t3.t#=t4.t#where t4.tname != '叶平'--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；select t0.s#,t0.snamefrom student t0inner join (select s#,c#,score from sc where c# = 001) t1on t0.s#=t1.s#inner join (select s#,c#,score from sc where c# = 002) t2on t0.s#=t2.s#--7、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；select t1.s#,t1.snamefrom student t1left join sc t2 on t1.s#=t2.s#left join course t3 on t2.c#=t3.c#left join teacher t4 on t3.t#=t4.t#where t4.tname = '叶平'--8、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；select t0.s#,t0.snamefrom student t0inner join (select s#,c#,score from sc where c# = 001) t1on t0.s#=t1.s#inner join (select s#,c#,score from sc where c# = 002) t2on t0.s#=t2.s#where t2.score<t1.score--9、查询所有课程成绩⼩于60分的同学的学号、姓名；select t1.s#,t1.snamefrom student t1inner join (select s#,count(c#),count(if(score<60,c#,null)) from sc group by s# having count(c#) = count(if(score<60,c#,null))) t2on t1.s#=t2.s#--10、查询没有学全所有课的同学的学号、姓名；select t1.s#,t1.snamefrom student t1 inner join sc t2 on t1.s#=t2.s#group by t1.s#,t1.snamehaving count(c#) < (select count(c#) from course)--11、查询⾄少有⼀门课与学号为“1001”的同学所学相同的同学的学号和姓名；select distinct t1.s#,t1.snamefrom student t1 inner join sc t2 on t1.s#=t2.s#left semi join (select c# from sc where s#=1001) t3 on t2.c#=t3.c#--12、查询⾄少学过学号为“001”同学所有⼀门课的其他同学学号和姓名；--13、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；select t1.s#,t3.snamefrom sc t1inner join sc t2on t1.c#=t2.c#left join student t3on t1.s#=t3.s#where t2.s#=1002group by t1.s#having count(distinct t1.c#) = count(distinct t2.c#)--14、查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分select c#,max(score),min(score)from scgroup by c#--15、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序select c#,ave,count_g/count_allfrom (select c#,average(score) ave,count(if(score>=60,s#,null)) count_g,count(s#) count_allfrom scgroup by c#) t1order by ave,count_g/count_all desc--16、查询如下课程平均成绩和及格率的百分数(⽤"1⾏"显⽰): 企业管理（001），马克思（002），OO&UML （003），数据库（004）select c#,ave,count_g/count_allfrom(select c#,average(score) ave,count(if(score>=60,s#,null)) count_g,count(s#) count_allfrom scwhere c# in (001,002,003,004)group by c#) t1--17、查询不同⽼师所教不同课程平均分从⾼到低显⽰select ame,t3.tname,avg(t1.score)from sc t1inner join course t2 on t1.c#=t2.c#inner join teacher t3 on t2.t#=t3.t#group by ame,t3.tnameorder by avg(t1.score) desc--18、查询如下课程成绩第 3 名到第 6 名的学⽣成绩单：企业管理（001），马克思（002），UML （003），数据库（004）select * from(select t1.s#,t1.sname,t2.c#,t2.score,row_number() over(partition by t2.c# order by t2.score desc) numfrom student t1inner join sc t2 on t1.s#=t2.s#where c# in (001,002,003,004)) twhere t.num >=3 and t.num<=6--19、统计列印各科成绩,各分数段⼈数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]select t1.c#,ame,case when t1.score >=85 then [100-85] when (t1.score <85 and t1.score >=70) then [85-70] when (t1.score <70 and t1.score >=60) then [70-60] when t1.score <60 then [ <60] end as score, count(t1.s#)from sc t1inner join course t2 on t1.c#=t2.c#group by t1.c#,ame--20、查询学⽣平均成绩及其名次select t1.s#,t1.sname,avg(t2.score),row_number() over(order by avg(t2.score)) as rankingfrom student t1inner join sc t2 on t1.s#=t2.s#--21、查询各科成绩前三名的记录:(不考虑成绩并列情况)select * from(select t1.s#,t1.sname,t2.c#,t2.score,row_number() over(partition by t2.c# order by t2.score desc) numfrom student t1inner join sc t2 on t1.s#=t2.s#) twhere t.num <=3--22、查询每门课程被选修的学⽣数select c#,count(s#)from sc group by c#--23、查询出只选修了⼀门课程的全部学⽣的学号和姓名select t1.s#,t2.snamefrom sc t1inner join student t2 on t1.s#=t2.s#where count(t1.c#)=1group by t1.s#,t2.sname--24、查询男⽣、⼥⽣⼈数select ssex,count(s#)from student group by ssex--25、查询姓“张”的学⽣名单select s#,snamefrom studentwhere sname like '张%'--26、查询同名同性学⽣名单，并统计同名⼈数select sname,count(s#)from studentwhere count(s#)>1group by sname--27、1981年出⽣的学⽣名单(注：Student表中Sage列的类型是datetime)select s#,snamefrom studentwhere datediff(year,today,sage)=1981--28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 select c#,avg(score) from scgroup by c# order by avg(score),c# desc--29、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩select t1.s#,t2.sname,avg(t1.score)from sc t1 inner join student t2 on t1.s#=t2.s#group by t1.s# having avg(t1.score)>85--30、查询课程名称为“数据库”，且分数低于60的学⽣姓名和分数select t0.sname,t1.scorefrom student t0inner join sc t1 on t0.s#=t1.s#inner join course t2 on t1.c#=t2.c#where ame = '数据库' and t1.score<60--31、查询所有学⽣的选课情况；select t1.s#,t1.sname,t2.c#,amefrom student t1left join sc t2 on t1.s#=t2.s#inner join course t3 on t2.c#=t3.c#--32、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数；select t1.sname,ame,t2.scorefrom student t1inner join sc t2 on t1.s#=t2.s#inner join course t3 on t2.c#=t3.c#where t2.score>70--33、查询不及格的课程，并按课程号从⼤到⼩排列select c#,avg(score)from scgroup by c#order by avg(score) deschaving avg(score)<60--34、查询课程编号为003且课程成绩在80分以上的学⽣的学号和姓名；select t1.s#,t1.snamefrom student t1inner join sc t2 on t1.s#=t2.s#inner join course t3 on t2.c#=t3.c#where t3.c#=003 and t2.score>80--35、求选了课程的学⽣⼈数select count(distinct s#)from sc--36、查询选修“叶平”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩select t1.sname,max(t2.score)from student t1 inner join sc t2 on t1.s#=t2.s#inner join course t3 on t2.c#=t3.c#inner join teacher t4 on t3.t#=t4.t#where t4.tname='叶平'group by t1.sname--37、查询各个课程及相应选修⼈数select c#,count(s#)from scgroup by c#--38、查询不同课程成绩相同的学⽣的学号、课程号、学⽣成绩select t1.s#,t1.c#,t2.c#,t1.scorefrom sc t1inner join sc t2 on t1.s#=t2.s#where t1.score=t2.score and t1.c#<>t2.c#--39、查询每门课程成绩最好的前两名select c#,s# from(select c#,s#,score,row_number() over(partition by c# order by score desc) numfrom sc) twhere t.num=2--40、统计每门课程的学⽣选修⼈数（超过10⼈的课程才统计）。

学生各门课程成绩统计SQL语句大全

学⽣各门课程成绩统计SQL语句⼤全学⽣成绩表(stuscore)：姓名：name课程：subject分数：score学号：stuid张三数学891张三语⽂801张三英语701李四数学902李四语⽂702李四英语802创建表SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[stuscore]( [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [subject] [varchar](50) COLLATE Chinese_PRC_CI_A问题：1.计算每个⼈的总成绩并排名(要求显⽰字段：姓名，总成绩)2.计算每个⼈的总成绩并排名(要求显⽰字段: 学号，姓名，总成绩)3.计算每个⼈单科的最⾼成绩(要求显⽰字段: 学号，姓名，课程，最⾼成绩)4.计算每个⼈的平均成绩（要求显⽰字段: 学号，姓名，平均成绩）5.列出各门课程成绩最好的学⽣(要求显⽰字段: 学号，姓名,科⽬，成绩)6.列出各门课程成绩最好的两位学⽣(要求显⽰字段: 学号，姓名,科⽬，成绩)7.如下：学号姓名语⽂数学英语总分平均分8．列出各门课程的平均成绩（要求显⽰字段：课程，平均成绩）9．列出数学成绩的排名（要求显⽰字段：学号，姓名，成绩，排名）10．列出数学成绩在2-3名的学⽣（要求显⽰字段：学号，姓名,科⽬，成绩）11．求出李四的数学成绩的排名12．如下：课程不及格（0-59）个良（60-80）个优（81-100）个13．如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)答案：1.计算每个⼈的总成绩并排名select name,sum(score) as allscore from stuscore group by name order by allscore2.计算每个⼈的总成绩并排名select distinct ,t1.stuid,t2.allscore from stuscore t1,( select stuid,sum(score) as allscore from stuscore group by stuid)t2where t1.stuid=t2.stuidorder by t2.allscore desc3. 计算每个⼈单科的最⾼成绩select t1.stuid,,t1.subject,t1.score from stuscore t1,(select stuid,max(score) as maxscore from stuscore group by stuid) t2where t1.stuid=t2.stuid and t1.score=t2.maxscore4.计算每个⼈的平均成绩select distinct t1.stuid,,t2.avgscore from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid) t2where t1.stuid=t2.stuid5.列出各门课程成绩最好的学⽣select t1.stuid,,t1.subject,t2.maxscore from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2where t1.subject=t2.subject and t1.score=t2.maxscore6.列出各门课程成绩最好的两位学⽣select distinct t1.* from stuscore t1 where t1.id in (select top 2 stuscore.id from stuscore where subject = t1.subject order by score desc) order by t1.subject7.学号姓名语⽂数学英语总分平均分select stuid as 学号,name as 姓名,sum(case when subject='语⽂' then score else 0 end) as 语⽂,sum(case when subject='数学' then score else 0 end) as 数学,sum(case when subject='英语' then score else 0 end) as 英语,sum(score) as 总分,(su8.列出各门课程的平均成绩select subject,avg(score) as avgscore from stuscoregroup by subject9.列出数学成绩的排名declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by score descdeclare @id intset @id=0;update @tmp set @id=@id+1,pm=@idselect * from @ select DENSE_RANK () OVER(order by score desc) as row,name,subject,score,stuid from stuscore where subject='数学'order by score desc10. 列出数学成绩在2-3名的学⽣select t3.* from(select top 2 t2.* from (select top 3 name,subject,score,stuid from stuscore where subject='数学'order by score desc) t2 order by t2.score) t3 order by t3.score desc11. 求出李四的数学成绩的排名declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by score descdeclare @id intset @id=0;update @tmp set @id=@id+1,pm=@idselect * from @12. 课程不及格（-59）良（-80）优（-100）select subject, (select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格,(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,(select count(*) from stuscore where score >80 and su13. 数学:张三(50分),李四(90分),王五(90分),赵六(76分)declare @s varchar(1000)set @s=''select @s =@s+','+name+'('+convert(varchar(10),score)+'分)' from stuscore where subject='数学' set @s=stuff(@s,1,1,'')print '数学:'+@s。

SQL语句大全实例

表操作例1 对于表的教学管理数据库中的表STUDENTS ,可以定义如下：CREATE TABLE STUDENTSSNO NUMERIC 6, 0 NOT NULLSNAME CHAR 8 NOT NULLAGE NUMERIC3,0SEX CHAR2BPLACE CHAR20PRIMARY KEYSNO例2 对于表的教学管理数据库中的表ENROLLS ,可以定义如下： CREATE TABLE ENROLLSSNO NUMERIC6,0 NOT NULLCNO CHAR4 NOT NULLGRADE INTPRIMARY KEYSNO,CNOFOREIGN KEYSNO REFERENCES STUDENTSSNOFOREIGN KEYCNO REFERENCES COURSESCNOCHECK GRADE IS NULL OR GRADE BETWEEN 0 AND 100例3 根据表的STUDENTS 表,建立一个只包含学号、姓名、年龄的女学生表;CREATE TABLE GIRLAS SELECT SNO, SNAME, AGEFROM STUDENTSWHERE SEX=' 女';例4 删除教师表TEACHER ;DROP TABLE TEACHER例5 在教师表中增加住址列;ALTER TABLE TEACHERSADD ADDR CHAR50例6 把STUDENTS 表中的BPLACE 列删除,并且把引用BPLACE 列的所有视图和约束也一起删除;ALTER TABLE STUDENTSDROP BPLACE CASCADE例7 补充定义ENROLLS 表的主关键字;ALTER TABLE ENROLLSADD PRIMARY KEY SNO,CNO ；视图操作虚表例9 建立一个只包括教师号、姓名和年龄的视图FACULTY ; 在视图定义中不能包含ORDER BY 子句CREATE VIEW FACULTYAS SELECT TNO, TNAME, AGEFROM TEACHERS例10 从学生表、课程表和选课表中产生一个视图GRADE_TABLE , 它包括学生姓名、课程名和成绩;CREATE VIEW GRADE_TABLEAS SELECT SNAME,CNAME,GRADEFROM STUDENTS,COURSES,ENROLLSWHERE ＝AND=例11 删除视图GRADE_TABLEDROP VIEW GRADE_TABLE RESTRICT索引操作例12 在学生表中按学号建立索引;CREATE UNIQUE INDEX STON STUDENTS SNO,ASC例13 删除按学号所建立的索引;DROP INDEX ST数据库模式操作例14 创建一个简易教学数据库的数据库模式TEACHING_DB ,属主为ZHANG ;CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG例15 删除简易教学数据库模式TEACHING_DB ; 1 选用CASCADE ,即当删除数据库模式时,则本数据库模式和其下属的基本表、视图、索引等全部被删除; 2 选用RESTRICT ,即本数据库模式下属的基本表、视图、索引等事先已清除,才能删除本数据库模式,否则拒绝删除;DROP SCHEMA TEACHING_DB CASCADE单表操作例16 找出 3 个学分的课程号和课程名;SELECT CNO, CNAMEFROM COURSESWHERE CREDIT ＝3例17 查询年龄大于22 岁的学生情况;SELECTFROM STUDENTSWHERE AGE ＞22例18 找出籍贯为河北的男生的姓名和年龄;SELECT SNAME, AGEFROM STUDENTSWHERE BPLACE ＝' 河北' AND SEX ＝' 男'例19 找出年龄在20 ～23 岁之间的学生的学号、姓名和年龄,并按年龄升序排序; ASC 升序或DESC 降序声明排序的方式,缺省为升序;SELECT SNO, SNAME, AGEFROM STUDENTSWHERE AGE BETWEEN 20 AND 23ORDER BY AGE例20 找出年龄小于23 岁、籍贯是湖南或湖北的学生的姓名和性别;条件比较运算符＝、＜和逻辑运算符AND 与,此外还可以使用的运算符有：＞大于、＞＝大于等于、＜＝小于等于、＜＞不等于、NOT 非、OR 或等;谓词LIKE 只能与字符串联用,常常是“ ＜列名＞LIKE pattern” 的格式;特殊字符“_” 和“%” 作为通配符;谓词IN 表示指定的属性应与后面的集合括号中的值集或某个查询子句的结果中的某个值相匹配,实际上是一系列的OR 或的缩写;谓词NOT IN 表示指定的属性不与后面的集合中的某个值相匹配;谓词BETWEEN 是“ 包含于… 之中” 的意思;SELECT SNAME, SEXFROM STUDENTSWHERE AGE ＜23 AND BPLACE LIKE' 湖％'或SELECT SNAME, SEXFROM STUDENTSWHERE AGE ＜23 AND BPLACE IN ' 湖南' , ' 湖北'例22 找出学生表中籍贯是空值的学生的姓名和性别;在SQL 中不能使用条件：＜列名＞＝NULL ;在SQL 中只有一个特殊的查询条件允许查询NULL 值：SELECT SNAME, SEXFROM STUDENTSWHERE BPLACE IS NULL多表操作例23 找出成绩为95 分的学生的姓名;子查询SELECT SNAMEFROM STUDENTSWHERE SNO ＝SELECT SNOFROM ENROLLSWHERE GRADE ＝95例24 找出成绩在90 分以上的学生的姓名;SELECT SNAMEFROM STUDENTSWHERE SNO INSELECT SNOFROM ENROLLSWHERE GRADE ＞90或SELECT SNAMEFROM STUDENTSWHERE SNO ＝ANYSELECT SNOFROM ENROLLSWHERE GRADE ＞90例25 查询全部学生的学生名和所学课程号及成绩;连接查询SELECT SNAME, CNO, GRADEFROM STUDENTS, ENROLLSWHERE ＝例26 找出籍贯为山西或河北,成绩为90 分以上的学生的姓名、籍贯和成绩;当构造多表连接查询命令时,必须遵循两条规则;第一,连接条件数正好比表数少 1 若有三个表,就有两个连接条件；第二,若一个表中的主关键字是由多个列组成,则对此主关键字中的每一个列都要有一个连接条件也有少数例外情况SELECT SNAME, BPLACE, GRADEFROM STUDENTS, ENROLLSWHERE BPLACE IN ‘ 山西’ , ‘ 河北’AND GRADE ＞＝90 AND =例28 查出课程成绩在80 分以上的女学生的姓名、课程名和成绩; FROM 子句中的子查询SELECT SNAME,CNAME, GRADEFROM SELECT SNAME, CNAME , GRADEFROM STUDENTS, ENROLLS,COURSESWHERE SEX ＝' 女'AS TEMP SNAME, CNAME,GRADEWHERE GRADE ＞80表达式与函数的使用例29 查询各课程的学时数;算术表达式由算术运算符＋、－、、／与列名或数值常量所组成;SELECT CNAME,COURSE_TIME ＝CREDIT16FROM COURSES例30 找出教师的最小年龄;内部函数：SQL 标准中只使用COUNT 、SUM 、AVG 、MAX 、MIN 函数,称之为聚集函数Set Function ; COUNT 函数的结果是该列统计值的总数目, SUM 函数求该列统计值之和, AVG 函数求该列统计值之平均值, MAX 函数求该列最大值, MIN 函数求该列最小值;SELECT MINAGEFROM TEACHERS例31 统计年龄小于等于22 岁的学生人数;统计SELECT COUNTFROM STUDENTSWHERE AGE < ＝22例32 找出学生的平均成绩和所学课程门数;SELECT SNO, AVGGRADE, COURSES ＝COUNTFROM ENROLLSGROUP BY SNO例34 找出年龄超过平均年龄的学生姓名;SELECT SNAMEFROM STUDENTSWHERE AGE ＞SELECT AVGAGEFROM STUDENTS例35 找出各课程的平均成绩,按课程号分组,且只选择学生超过 3 人的课程的成绩; GROUP BY 与HAVINGGROUP BY 子句把一个表按某一指定列或一些列上的值相等的原则分组,然后再对每组数据进行规定的操作;GROUP BY 子句总是跟在WHERE 子句后面,当WHERE 子句缺省时,它跟在FROM 子句后面;HAVING 子句常用于在计算出聚集之后对行的查询进行控制;SELECT CNO, AVGGRADE, STUDENTS ＝COUNTFROM ENROLLSGROUP BY CNOHAVING COUNT >= 3相关子查询例37 查询没有选任何课程的学生的学号和姓名;当一个子查询涉及到一个来自外部查询的列时,称为相关子查询Correlated Subquery ;相关子查询要用到存在测试谓词EXISTS 和NOT EXISTS ,以及ALL 、ANY SOME 等;SELECT SNO, SNAMEFROM STUDENTSWHERE NOT EXISTSSELECTFROM ENROLLSWHERE =例38 查询哪些课程只有男生选读;SELECT DISTINCT CNAMEFROM COURSES CWHERE ' 男' ＝ALLSELECT SEXFROM ENROLLS , STUDENTSWHERE = AND=例39 要求给出一张学生、籍贯列表,该表中的学生的籍贯省份,也是其他一些学生的籍贯省份;SELECT SNAME, BPLACEFROM STUDENTS AWHERE EXISTSSELECTFROM STUDENTS BWHERE = AND< >例40 找出选修了全部课程的学生的姓名;本查询可以改为：查询这样一些学生,没有一门课程是他不选修的;SELECT SNAMEFROM STUDENTSWHERE NOT EXISTSSELECTFROM COURSESWHERE NOT EXISTSSELECTFROM ENROLLSWHERE ＝AND ＝关系代数运算例41 设有某商场工作人员的两张表：营业员表SP_SUBORD 和营销经理表SP_MGR ,其关系数据模式如下：SP_SUBORD SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICESP_MGR SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE其中,属性SALPERS_ID 为工作人员的编号, SALPERS_NAME 为工作人员的姓名, MANAGER_ID 为所在部门经理的编号, OFFICE 为工作地点;若查询全部商场工作人员,可以用下面的SQL 语句：SELECT FROM SP_SUBORDUNIONSELECT FROM SP_MGR或等价地用下面的SQL 语句：SELECTFROM TABLE SP_SUBORD UNION TABLE SP_MGR2 INTERSECTSELECT FROM SP_SUBORDINTERSECTSELECT FROM SP_MGR或等价地用下面的SQL 语句：SELECTFROM TABLE SP_SUBORD INTERSECT TABLE SP_MGR或用带ALL 的SQL 语句：SELECT FROM SP_SUBORDINTERSECT ALLSELECT FROM SP_MGR或SELECTFROM TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR3 EXCEPTSELECT FROM SP_MGREXCEPTSELECT FROM SP_SUBORD或等价地用下面的SQL 语句：SELECTFROM TABLE SP_MGR EXCEPT TABLE SP_ SUBORD或用带ALL 的SQL 语句：EXCEPT ALLSELECT FROM SP_SUBORD例42 查询籍贯为四川、课程成绩在80 分以上的学生信息及其成绩;自然连接SELECT FROM STUDENTSWHERE BPLACE=‘ 四川’NATURAL JOINSELECT FROM ENROLLSWHERE GRADE >=80例列出全部教师的姓名及其任课的课程号、班级;外连接与外部并外连接允许在结果表中保留非匹配元组,空缺部分填以NULL ;外连接的作用是在做连接操作时避免丢失信息;外连接有3 类：1 左外连接Left Outer Join ;连接运算谓词为LEFT OUTER JOIN ,其结果表中保留左关系的所有元组;2 右外连接Right Outer Join ;连接运算谓词为RIGHT OUTER JOIN ,其结果表中保留右关系的所有元组;3 全外连接Full Outer Join ;连接运算谓词为FULL OUTER JOIN ,其结果表中保留左右两关系的所有元组;SELECT TNAME, CNO, CLASSFROM TEACHERS LEFT OUTER JOIN TEACHING USING TNOSQL 的数据操纵例44 把教师李映雪的记录加入到教师表TEACHERS 中;插入INSERT INTO TEACHERSVALUES1476 , ' 李映雪' , 44 , ' 副教授'例45 成绩优秀的学生将留下当教师;INSERT INTO TEACHERS TNO , TNAMESELECT DISTINCT SNO , SNAMEFROM STUDENTS , ENROLLSWHERE ＝AND GRADE ＞＝90例47 把所有学生的年龄增加一岁;修改UPDATE STUDENTSSET AGE ＝AGE+1例48 学生张春明在数据库课考试中作弊,该课成绩应作零分计;UPDATE ENROLLSSET GRADE ＝0WHERE CNO ＝'C1' AND' 张春明' ＝SELECT SNAMEFROM STUDENTSWHERE =例49 从教师表中删除年龄已到60 岁的退休教师的数据;删除WHERE AGE ＞＝60SQL 的数据控制例50 授予LILI 有对表STUDENTS 的查询权;表／视图特权的授予一个SQL 特权允许一个被授权者在给定的数据库对象上进行特定的操作;授权操作的数据库对象包括：表/ 视图、列、域等;授权的操作包括：INSERT 、UPDATE 、DELETE 、SELECT 、REFERENCES 、TRIGGER 、UNDER 、USAGE 、EXECUTE 等;其中INSERT 、UPDATE 、DELETE 、SELECT 、REFERENCES 、TRIGGER 有对表做相应操作的权限,故称为表特权;GRANT SELECT ON STUDENTSTO LILIWITH GRANT OPTION例51 取消LILI 的存取STUDENTS 表的特权;REVOKE ALLON STUDENTSFROM LILI CASCADE不断补充中：1. 模糊查找:它判断列值是否与指定的字符串格式相匹配;可用于char、varchar、text、ntext、datetime 和smalldatetime等类型查询;可使用以下通配字符：百分号%：可匹配任意类型和长度的字符,如果是中文,请使用两个百分号即%%;下划线_：匹配单个任意字符,它常用来限制表达式的字符长度;方括号：指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个;^：其取值也相同,但它要求所匹配对象为指定字符以外的任一个字符;例如：限制以Publishing结尾,使用LIKE '%Publishing'限制以A开头：LIKE 'A%'限制以A开头外：LIKE '^A%'2.更改表格ALTER TABLE table_nameADD COLUMN column_name DATATYPE说明：增加一个栏位没有删除某个栏位的语法;ALTER TABLE table_nameADD PRIMARY KEY column_name说明：更改表得的定义把某个栏位设为主键;ALTER TABLE table_nameDROP PRIMARY KEY column_name说明：把主键的定义删除;by在select 语句中可以使用group by 子句将行划分成较小的组,然后,使用聚组函数返回每一个组的汇总信息,另外,可以使用having子句限制返回的结果集;group by 子句可以将查询结果分组,并返回行的汇总信息Oracle 按照group by 子句中指定的表达式的值分组查询结果;在带有group by 子句的查询语句中,在select 列表中指定的列要么是group by 子句中指定的列,要么包含聚组函数select maxsal,job emp group by job;注意maxsal,job的job并非一定要出现,但有意义查询语句的select 和group by ,having 子句是聚组函数唯一出现的地方,在where 子句中不能使用聚组函数;select deptno,sumsal from emp where sal>1200 group by deptno having sumsal>8500 order by deptno;当在gropu by 子句中使用having 子句时,查询结果中只返回满足having条件的组;在一个sql语句中可以有where子句和having子句;having 与where 子句类似,均用于设置限定条件where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行;having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组;查询每个部门的每种职位的雇员数select deptno,job,count from emp group by deptno,job;4.外连接与内连接有时候,即使在连接的表中没有相应的行,用户可能想从一张表中看数据,Oracle提供了外连接实现该功能;内连接是指连接查询只显示完全满足连接条件的记录,即等值连接,外连接的查询结果是内连接查询结果的扩展;外连接不仅返回满足连接条件的所有记录而且也返回了一个表中那些在另一个表中没有匹配行的记录;外连接的操作符是“+”;“+”号放在连接条件中信息不完全的那一边即没有相应行的那一边;运算符“+”影响NULL行的建立;建一行或多行NULL来匹配连接的表中信息完全的行;外连接运算符“+”只能出现在where子句中表达式的一边;假如在多张表之间有多个连接条件,外连接运算符不能使用or,in逻辑运算符与其它条件组合;假如emp表中deptno=10的ename为空值,dept表中deptno=20的loc为空值：1.selectename,,locfromemp,deptwhere+=;如果在中有的数值在中没有值,则在做外连接时,结果中ename会产生一个空值;=102.selectename,,locfromemp,deptwhere=+;如果在中有的数值在中没有值,则在做外连接时,结果中loc会产生一个空值;;=205.自连接自连接是指同一张表的不同行间的连接;该连接不受其他表的影响;用自连接可以比较同一张表中不同行的某一列的值;因为自连接查询仅涉及到某一张表与其自身的连接;所以在from子句中该表名出现两次,分别用两个不同的别名表示,两个别名当作两张不同的表进行处理,与其它的表连接一样,别名之间也使用一个或多个相关的列连接;为了区分同一张表的不同行的列,在名前永别名加以限制;select,managerfromemp worker,emp managerwhere=;6.集合运算基合运算符可以用于从多张表中选择数据;①UNION运算用于求两个结果集合的并集两个结果集合的所有记录,并自动去掉重复行;select ename,sal from account where sal>2000unionselect ename,sal from research where sal>2000unionselect ename,sal from sales where sal>2000;注：ename,sal 是必须一致的;②UNION ALL运算用于求两个结果集合的并集两个结果集中的所有记录,并且不去掉重复行;select ename,sal from account where sal>2000unionselect ename,sal from research where sal>2000unionselect ename,sal from sales where sal>2000;③INTERSECT运算intersect运算返回查询结果中相同的部分;各部门中有哪些相同的职位select Job from accountintersectselect Job from researchintersectselect Job from sales;④MINUS运算minus返回两个结果集的差集;在第一个结果集中存在的,而在第二个结果集中不存在的行; 有那些职位是财务部中有,而在销售部门中没有select Job from accountminusselect Job from sales;。

50个经典sql语句总结

一个项目涉及到的50个Sql语句(整理版)--1.学生表Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号--3.教师表Teacher(T,Tname) --T 教师编号,Tname 教师姓名--4.成绩表SC(S,C,score) --S 学生编号,C 课程编号,score 分数*/--创建测试数据create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')insert into Student values('04' , N'李云' , '1990-08-06' , N'男')insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')create table Course(C varchar(10),Cname nvarchar(10),T varchar(10))insert into Course values('01' , N'语文' , '02')insert into Course values('02' , N'数学' , '01')insert into Course values('03' , N'英语' , '03')create table Teacher(T varchar(10),Tname nvarchar(10))insert into Teacher values('01' , N'张三')insert into Teacher values('02' , N'李四')insert into Teacher values('03' , N'王五')create table SC(S varchar(10),C varchar(10),score decimal(18,1))insert into SC values('01' , '01' , 80)insert into SC values('01' , '02' , 90)insert into SC values('01' , '03' , 99)insert into SC values('02' , '01' , 70)insert into SC values('02' , '02' , 60)insert into SC values('02' , '03' , 80)insert into SC values('03' , '01' , 80)insert into SC values('03' , '02' , 80)insert into SC values('03' , '03' , 80)insert into SC values('04' , '01' , 50)insert into SC values('04' , '02' , 30)insert into SC values('04' , '03' , 20)insert into SC values('05' , '01' , 76)insert into SC values('05' , '02' , 87)insert into SC values('06' , '01' , 31)insert into SC values('06' , '03' , 34)insert into SC values('07' , '02' , 89)insert into SC values('07' , '03' , 98)go--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数--1.1、查询同时存在"01"课程和"02"课程的情况select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c where a.S = b.S and a.S = c.S and b.C = '01' and c.C = '02' and b.score > c.score--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student aleft join SC b on a.S = b.S and b.C = '01'left join SC c on a.S = c.S and c.C = '02'where b.score > isnull(c.score,0)--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数--2.1、查询同时存在"01"课程和"02"课程的情况select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c where a.S = b.S and a.S = c.S and b.C = '01' and c.C = '02' and b.score < c.score--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student aleft join SC b on a.S = b.S and b.C = '01'left join SC c on a.S = c.S and c.C = '02'where isnull(b.score,0) < c.score--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select a.S , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_scorefrom Student a , sc bwhere a.S = b.Sgroup by a.S , a.Snamehaving cast(avg(b.score) as decimal(18,2)) >= 60order by a.S--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩--4.1、查询在sc表存在成绩的学生信息的SQL语句。

SQL语句大全实例

SQL语句实例表操作例1 对于表的教学管理数据库中的表STUDENTS ，可以定义如下：CREATE TABLE STUDENTS(SNO NUMERIC (6, 0) NOT NULLSNAME CHAR (8) NOT NULLAGE NUMERIC(3,0)SEX CHAR(2)BPLACE CHAR(20)PRIMARY KEY(SNO))例2 对于表的教学管理数据库中的表ENROLLS ，可以定义如下： CREATE TABLE ENROLLS(SNO NUMERIC(6,0) NOT NULLCNO CHAR(4) NOT NULLGRADE INTPRIMARY KEY(SNO,CNO)FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)FOREIGN KEY(CNO) REFERENCES COURSES(CNO)CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)))例3 根据表的STUDENTS 表，建立一个只包含学号、姓名、年龄的女学生表。

CREATE TABLE GIRLAS SELECT SNO, SNAME, AGEFROM STUDENTSWHERE SEX=' 女';例4 删除教师表TEACHER 。

DROP TABLE TEACHER例5 在教师表中增加住址列。

ALTER TABLE TEACHERSADD (ADDR CHAR(50))例6 把STUDENTS 表中的BPLACE 列删除，并且把引用BPLACE 列的所有视图和约束也一起删除。

ALTER TABLE STUDENTSDROP BPLACE CASCADE例7 补充定义ENROLLS 表的主关键字。

ALTER TABLE ENROLLSADD PRIMARY KEY (SNO,CNO) ；视图操作（虚表）例9 建立一个只包括教师号、姓名和年龄的视图FACULTY 。

SQL语句创建学生信息数据库表的示例-学生信息数据库表

用SQL语句创建如下三个根本表：学生表(Student).课程表（Course）.学生选课表（SC）,构造如下所示Student表构造Create table Student(Sno varchar(7) primary key,Sname varchar(10) not null,Ssex char (2) check(Ssex=‘男’or Ssex=’女’),Sage int check(Sage between 15 and 45),Sdept varchar(20)default(‘盘算机系’))Course表构造Create table course(Cno varchar(10) primary key,Cname varchar(20) not null,Ccredit int check(Sctedit>0),Semester int check(Semester>0),Period int check(Period>0))SC表构造Create table SC(Sno varchar(7) foreign key references student(Sno), Cno varchar(10) foreign key references course(Cno), Grade int check(Grade between 0 and 100),Primary key (Sno,Cno))1．查询学生选课表中的全体数据.SELECT *FROM SCgo2．查询盘算机系学生的姓名.年纪.Select Sname,SageFrom StudentWhere Sdept=’盘算机系’3．查询成绩在70～80分之间的学生的学号.课程号和成绩. Select Sno,Cno,GradeFrom Course,ScWhere o=o and sc.Grade between 70 and 80 4．查询盘算机系年纪在18～20之间且性别为“男”的学生的姓名和年纪.Select Sname,SageFrom StudentWhere Sage between 18 and 20 and Ssex=’男’and Sdept=’盘算机系’go5．查询课程号为“C01”的课程的最高分数.Select top 1 Grade select max(Grade) as 最高分From Sc from ScWhere Cno=’C01’ where Cno=’C01’Order by Grade desc order by Grade desc6．查询盘算机系学生的最大年纪和最小年纪.Select max(Sage) as 年纪最大,min(Sage) as 年纪最小From StudentWhere Sdept=’盘算机系’7．统计每个系的学生人数.Select count(Sdept) as学生人数,SdeptFrom StudentGroup by Sdept8．统计每门课程的选课人数和测验最高分.Select count(Sno) as选课人数,c.Sno,max(Grade) as最高分9．统计每个学生的选课门数和测验平均成绩,并按学号的升序显示成果.Select sno,avg(grade) as ’平均成绩’,count (cno) as ’选课门数’From scGroup by snoOrder by sno10．查询总成绩超出200分的学生,请求列出学号.总成绩. Select sno,sum(grade)From scGroup by snoHaving sum(grade)>20011．查询选修了课程“C02”的学生的姓名和地点系.Select sname,sdeptFrom student s1,sc s2Where s1.sno=s2.sno and o=’c02’12．查询成绩在80分以上的学生的姓名.课程号和成绩,并按成绩的降序分列成果.From student s1,sc s2Where s1.sno=s2.sno and grade >80Order by grade desc13．查询哪些课程没有人选修.请求列出课程号和课程名. Group by o,ameHaving count(s.sno)=014．用子查询实现如下查询：(1)查询选修了课程“C01”的学生的姓名和地点系. Select sname,sdept ,snoFrom studentWhere sno in (Select snoFrom scWhere cno=’c01’)(2)查询信息系成绩在80分以上的学生的学号.姓名. Select sno,snameFrom studentWhere sdept=’外语系’and sno in(Select snoFrom scWhere grade>80)(3)查询盘算机系测验成绩最高的学生的姓名. Select s1.sname from studentsWhere sdept=’盘算机系’ and sno in(select sno from scWhere grade in(select max(Grade)from sc))15．删除选课成绩小于50分的学生的选课记载.Delete from scWhere grade<70Select* from sc—验证16．将所有选修了课程“C01”的学生的成绩加10分：Update scSet grade=grade+10Where cno=’c01’17．将盘算机系所有选修了课程“盘算机文化基本”课程的学生的成绩加10分.Select*from scUpdate scSet grade=grade+10Where cno in(select cno from courseWhere cname=’盘算机文化基本’)18．创建查询学生的学号.姓名.地点系.课程号.课程名.课程学分的视图.Select* from courseSelect* from studentsSelect* from scCreate view 学生根本信息AsSelect students.sno,sname,sdept,o,cname,ccredit From course,sc,students19．创建查询每个学生的平均成绩的视图,请求列出学生学号及平均成绩.Create view s_avgAsSelect sno,avg(Grade)as 平均成绩 from scGroup by sno20．创建查询每个学生的选课学分的视图,请求列出学生学号及总学分.Create view s_scAsSelect students.sno,sum(ccredit)as 总学分 from Students,sc,course21．用SQL语句创建一个名为f_1的函数,该函数可以或许求出3到100之间的所有素数之和.Create function f_1()Returns intAsBeginDeclare @a int,@b int,@i int,@sum intSet @i=3Set @sum=0While @i<101BeginSet @b=0While @a<=@i/2BeginIf @i%@a=0BeginSet @b=1BreakEndSet @a=@a+1EndIf @b=0 @b为0解释之前没有比@i小的数字可以把@i整除BeginSet @sum=@sum+@iEndSet @i=@i+1EndReturn @sumEndGoSelect dbo.f_1()22．用SQL语句创建一个名为f_2的函数,该函数可以或许求出随意率性两个数的最大值.Create function f_2(@x1 int,@x2 int)returns intAsBeginDeclare @max intIf @x1>@x2Return @maxEndSelect dbo.f_2(2,6)23．用SQL语句创建一个名为pro_get_stu_information的存储进程,该存储进程可以或许依据用户指定的 Sno（学号）求出与该学号对应的学生姓名.课程名.成绩.Create procedure pro_get_stu_information @m char(6) outputAsSelect sname,cname,grade from students,sc,courseWhere students.sno=sc.sno and o=o and sc.sno=@mExec pro_get_stu_information’0603002’24．为“学生”表创建一个依附于“学号”的独一的.非集合的索引Create unique nonclustered index stu_int on students(sno) 25．经由过程游标逐行读取“学生”表的记载Declare stu_cur cursor forSelect * from students for read onlyOpen stu_curFetch stu_curClose stu_curDeallocate stu_cur。

50个经典SQL语句

50个经典SQL语句Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') bwhere a.score>b.score and a.s#=b.s#;2、查询平均成绩大于60分的同学的学号和平均成绩；select S#,avg(score)from scgroup by S# having avg(score) >60;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#,Sname4、查询姓“李”的老师的个数；select count(distinct(Tname))from Teacherwhere Tname like '李%';5、查询没学过“叶平”老师课的同学的学号、姓名；select Student.S#,Student.Snamefrom Studentwhere 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、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select S#,Snamefrom Studentwhere 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# and Tname='叶平'));8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；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') score2from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;9、查询所有课程成绩小于60分的同学的学号、姓名；select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);10、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；select distinct SC.S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in (select C# from SC where S#='001');13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；update SC set score=(select avg(SC_2.score)from SC SC_2where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；select S# from SC where C# in (select C# from SC where S#='1002')group by S# having count(*)=(select count(*) from SC where S#='1002');15、删除学习“叶平”老师课的SC表记录；Delect SCfrom course ,Teacherwhere Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、号课的平均成绩；Insert SC select S#,'002',(Select avg(score)from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分SELECT S# as 学生ID,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩FROM SC AS tGROUP BY S#ORDER BY avg(t.score)18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.C# = R.C# andL.score = (SELECT MAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.C# = IL.C# and IM.S#=IL.S#GROUP BY IL.C#)ANDR.Score = (SELECT MIN(IR.score)FROM SC AS IRWHERE R.C# = IR.C#GROUP BY IR.C#);19、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECT t.C# AS 课程号,max(ame)AS 课程名,isnull(AVG(score),0) AS 平均成绩,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数FROM SC T,Coursewhere t.C#=course.C#GROUP BY t.C#ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）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 ELSE 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# = '003' AND score >= 60 THEN 1 ELSE 0END)/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 数据库及格百分数FROM SC21、查询不同老师所教不同课程平均分从高到低显示SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(ame) AS 课程名称,AVG(Score) AS 平均成绩FROM SC AS T,Course AS C ,Teacher AS Zwhere T.C#=C.C# and C.T#=Z.T#GROUP BY C.C#ORDER BY AVG(Score) DESC22、查询如下课程成绩第3 名到第6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩SELECT DISTINCT top 3SC.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 T1ON SC.S# = T1.S# AND T1.C# = '001'LEFT JOIN SC AS T2ON SC.S# = T2.S# AND T2.C# = '002'LEFT JOIN SC AS T3ON SC.S# = T3.S# AND T3.C# = '003'LEFT JOIN SC AS T4ON SC.S# = T4.S# AND T4.C# = '004'WHERE student.S#=SC.S# andISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITH TIESISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)FROM scLEFT JOIN sc AS T1ON sc.S# = T1.S# AND T1.C# = 'k1'LEFT JOIN sc AS T2ON sc.S# = T2.S# AND T2.C# = 'k2'LEFT JOIN sc AS T3ON sc.S# = T3.S# AND T3.C# = 'k3'LEFT JOIN sc AS T4ON sc.S# = T4.S# AND 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 < 60 THEN 1 ELSE 0 END) AS [60 -]FROM SC,Coursewhere SC.C#=Course.C#GROUP BY SC.C#,Cname;24、查询学生平均成绩及其名次SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT S#,AVG(score) AS 平均成绩FROM SCGROUP BY S#) AS T1WHERE 平均成绩> T2.平均成绩) as 名次,S# as 学生学号,平均成绩FROM (SELECT S#,AVG(score) 平均成绩FROM SCGROUP BY S#) AS T2ORDER BY 平均成绩desc;25、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;26、查询每门课程被选修的学生数select c#,count(S#) from sc group by C#;27、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Sname,count(C#) AS 选课数from SC ,Studentwhere SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;28、查询男生、女生人数Select count(Ssex) as 男生人数from Student group by Ssex having Ssex='男';Select count(Ssex) as 女生人数from Student group by Ssex having Ssex='女'；29、查询姓“张”的学生名单SELECT Sname FROM Student WHERE Sname like '张%';30、查询同名同性学生名单，并统计同名人数select Sname,count(*) from Student group by Sname having count(*)>1;;31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)select Sname, CONVERT(char (11),DATEPART(year,Sage)) as agefrom studentwhere CONVERT(char(11),DATEPART(year,Sage))='1981';32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩select Sname,SC.S# ,avg(score)from Student,SCwhere Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数Select Sname,isnull(score,0)from Student,SC,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# and ame='数据库'and score <60;35、查询所有学生的选课情况；SELECT SC.S#,SC.C#,Sname,CnameFROM SC,Student,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# ;36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；SELECT distinct student.S#,student.Sname,SC.C#,SC.scoreFROM student,ScWHERE SC.score>=70 AND SC.S#=student.S#;37、查询不及格的课程，并按课程号从大到小排列select c# from sc where scor e <60 order by C# ;38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';39、求选了课程的学生人数select count(*) from sc;40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );41、查询各个课程及相应的选修人数select count(*) from sc group by C#;42、查询不同课程成绩相同的学生的学号、课程号、学生成绩select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;43、查询每门功成绩最好的前两名SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;44、统计每门课程的学生选修人数（超过10人的课程才统计）。

50个常用sql语句实例(学生表_课程表_成绩表_教师表)

Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,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.tableswhere table_name='Temp_Table')drop table Temp_Tablegoselect 排名=identity(int,1,1),* INTO Temp_Table from Studentgoselect * from Temp_Tablegodrop database [ ] --删除空的没有名字的数据库问题：1、查询“”课程比“”课程成绩高的所有学生的学号；select a.S# from (select s#,score from SC where C#='001') a,(select s#,scorefrom SC where C#='002') bwhere a.score>b.score and a.s#=b.s#;2、查询平均成绩大于分的同学的学号和平均成绩；select S#,avg(score)from scgroup by S# having avg(score) >60;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#,Sname4、查询姓“李”的老师的个数；select count(distinct(Tname))from Teacherwhere Tname like '李%';5、查询没学过“叶平”老师课的同学的学号、姓名；select Student.S#,Student.Snamefrom Studentwhere 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 whereStudent.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select S#,Snamefrom Studentwhere 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 whereTeacher.T#=Course.T# 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 <score;9、查询所有课程成绩小于分的同学的学号、姓名；select S#,Snamefrom Studentwhere S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);10、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名；select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名；select distinct SC.S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in (select C# from SC where S#='001');13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；update SC set score=(select avg(SC_2.score)from SC SC_2where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；select S# from SC where C# in (select C# from SC where S#='1002') group by S# having count(*)=(select count(*) from SC whereS#='1002');15、删除学习“叶平”老师课的SC表记录；Delect SCfrom course ,Teacherwhere Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“”课程的同学学号、、号课的平均成绩；Insert SC select S#,'002',(Select avg(score)from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分SELECT S# as 学生ID,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩FROM SC AS tGROUP BY S#ORDER BY avg(t.score)18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.C# = R.C# andL.score = (SELECT MAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.C# = IL.C# and IM.S#=IL.S#GROUP BY IL.C#)ANDR.Score = (SELECT MIN(IR.score)FROM SC AS IRWHERE R.C# = IR.C#GROUP BY IR.C#);19、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECT t.C# AS 课程号,max(ame)AS 课程名,isnull(AVG(score),0) AS 平均成绩,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数FROM SC T,Coursewhere t.C#=course.C#GROUP BY t.C#ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC20、查询如下课程平均成绩和及格率的百分数(用"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 ELSE 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# = '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 数据库及格百分数FROM SC21、查询不同老师所教不同课程平均分从高到低显示SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(ame) AS 课程名称,AVG(Score) AS 平均成绩FROM SC AS T,Course AS C ,Teacher AS Zwhere T.C#=C.C# and C.T#=Z.T#GROUP BY C.C#ORDER BY AVG(Score) DESC22、查询如下课程成绩第名到第名的学生成绩单：企业管理（），马克思（），UML （），数据库（）[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩SELECT DISTINCT top 3SC.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 T1ON SC.S# = T1.S# AND T1.C# = '001'LEFT JOIN SC AS T2ON SC.S# = T2.S# AND T2.C# = '002'LEFT JOIN SC AS T3ON SC.S# = T3.S# AND T3.C# = '003'LEFT JOIN SC AS T4ON SC.S# = T4.S# AND T4.C# = '004'WHERE student.S#=SC.S# andISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITH TIESISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)FROM scLEFT JOIN sc AS T1ON sc.S# = T1.S# AND T1.C# = 'k1'LEFT JOIN sc AS T2ON sc.S# = T2.S# AND T2.C# = 'k2'LEFT JOIN sc AS T3ON sc.S# = T3.S# AND T3.C# = 'k3'LEFT JOIN sc AS T4ON sc.S# = T4.S# AND 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 < 60 THEN 1 ELSE 0 END) AS [60 -] FROM SC,Coursewhere SC.C#=Course.C#GROUP BY SC.C#,Cname;24、查询学生平均成绩及其名次SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT S#,AVG(score) AS 平均成绩FROM SCGROUP BY S#) AS T1WHERE 平均成绩> T2.平均成绩) as 名次,S# as 学生学号,平均成绩FROM (SELECT S#,AVG(score) 平均成绩FROM SCGROUP BY S#) AS T2ORDER BY 平均成绩desc;25、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;26、查询每门课程被选修的学生数select c#,count(S#) from sc group by C#;27、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Sname,count(C#) AS 选课数from SC ,Studentwhere SC.S#=Student.S# group by SC.S# ,Student.Sname havingcount(C#)=1;28、查询男生、女生人数Select count(Ssex) as 男生人数from Student group by Ssex having Ssex='男';Select count(Ssex) as 女生人数from Student group by Ssex having Ssex='女'；29、查询姓“张”的学生名单SELECT Sname FROM Student WHERE Sname like '张%';30、查询同名同性学生名单，并统计同名人数select Sname,count(*) from Student group by Sname having count(*)>1;;31、年出生的学生名单(注：Student表中Sage列的类型是datetime)select Sname, CONVERT(char (11),DATEPART(year,Sage)) as agefrom studentwhere CONVERT(char(11),DATEPART(year,Sage))='1981';32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;33、查询平均成绩大于的所有学生的学号、姓名和平均成绩select Sname,SC.S# ,avg(score)from Student,SCwhere Student.S#=SC.S# group by SC.S#,Sname havingavg(score)>85;34、查询课程名称为“数据库”，且分数低于的学生姓名和分数Select Sname,isnull(score,0)from Student,SC,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# and ame='数据库'and score <60;35、查询所有学生的选课情况；SELECT SC.S#,SC.C#,Sname,CnameFROM SC,Student,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# ;36、查询任何一门课程成绩在分以上的姓名、课程名称和分数；SELECT distinct student.S#,student.Sname,SC.C#,SC.scoreFROM student,ScWHERE SC.score>=70 AND SC.S#=student.S#;37、查询不及格的课程，并按课程号从大到小排列select c# from sc where scor e <60 order by C# ;38、查询课程编号为且课程成绩在分以上的学生的学号和姓名；select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';39、求选了课程的学生人数select count(*) from sc;40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC whereC#=C.C# );41、查询各个课程及相应的选修人数select count(*) from sc group by C#;42、查询不同课程成绩相同的学生的学号、课程号、学生成绩select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;43、查询每门功成绩最好的前两名SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;44、统计每门课程的学生选修人数（超过人的课程才统计）。

sql查询50题

sql查询50题/*⼀个项⽬涉及到的50个Sql语句问题及描述：--1.学⽣表Student(S#,Sname,Sage,Ssex) --S# 学⽣编号,Sname 学⽣姓名,Sage 出⽣年⽉,Ssex 学⽣性别--2.课程表Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号--3.教师表Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名--4.成绩表SC(S#,C#,score) --S# 学⽣编号,C# 课程编号,score 分数select * from Studentselect * from Courseselect * from Teacherselect * from SC*/--创建测试数据create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')insert into Student values('04' , N'李云' , '1990-08-06' , N'男')insert into Student values('05' , N'周梅' , '1991-12-01' , N'⼥')insert into Student values('06' , N'吴兰' , '1992-03-01' , N'⼥')insert into Student values('07' , N'郑⽵' , '1989-07-01' , N'⼥')insert into Student values('08' , N'王菊' , '1990-01-20' , N'⼥')create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))insert into Course values('01' , N'语⽂' , '02')insert into Course values('02' , N'数学' , '01')insert into Course values('03' , N'英语' , '03')create table Teacher(T# varchar(10),Tname nvarchar(10))insert into Teacher values('01' , N'张三')insert into Teacher values('02' , N'李四')insert into Teacher values('03' , N'王五')create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))insert into SC values('01' , '01' , 80)insert into SC values('01' , '02' , 90)insert into SC values('01' , '03' , 99)insert into SC values('02' , '01' , 70)insert into SC values('02' , '02' , 60)insert into SC values('02' , '03' , 80)insert into SC values('03' , '01' , 80)insert into SC values('03' , '02' , 80)insert into SC values('03' , '03' , 80)insert into SC values('04' , '01' , 50)insert into SC values('04' , '02' , 30)insert into SC values('04' , '03' , 20)insert into SC values('05' , '01' , 76)insert into SC values('05' , '02' , 87)insert into SC values('06' , '01' , 31)insert into SC values('06' , '03' , 34)insert into SC values('07' , '02' , 89)insert into SC values('07' , '03' , 98)go--1、查询"01"课程⽐"02"课程成绩⾼的学⽣的信息及课程分数--1.1、查询同时存在"01"课程和"02"课程的情况--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显⽰为null)(以下存在相同内容时不再解释)--2、查询"01"课程⽐"02"课程成绩低的学⽣的信息及课程分数--2.1、查询同时存在"01"课程和"02"课程的情况--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况--3、查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩--4、查询平均成绩⼩于60分的同学的学⽣编号和学⽣姓名和平均成绩--4.1、查询在sc表存在成绩的学⽣信息的SQL语句。

sql 语句经典题目

sql 语句经典题目SQL（Structured Query Language）是用于管理关系数据库的标准编程语言。

以下是一些经典的 SQL 题目，可以帮助你提高 SQL 技能：1. 学生成绩查询假设有一个学生数据库，包括学生、课程和成绩等信息。

学生表为`students`，包含列 `id`, `name`, 和 `age`。

课程表为 `courses`，包含列`id`, `name`。

成绩表为 `scores`，包含列 `student_id`, `course_id`, 和`score`。

查询每个学生的姓名、年龄和所有课程的成绩。

2. 员工工资查询假设有一个员工数据库，包括员工、部门和工资等信息。

员工表为`employees`，包含列 `id`, `name`, 和 `department_id`。

部门表为`departments`，包含列 `id`, `name`。

工资表为 `salaries`，包含列`employee_id`, `salary`。

查询每个员工的姓名、所属部门和工资。

3. 商品销售统计假设有一个商品销售数据库，包括商品、销售和购买者等信息。

商品表为 `products`，包含列 `id`, `name`, 和 `price`。

销售表为 `sales`，包含列 `product_id`, `seller_id`, 和 `buyer_id`。

查询每种商品的名称、单价和销售数量，并按销售数量降序排列。

4. 电影评分查询假设有一个电影数据库，包括电影、导演和评分等信息。

电影表为`movies`，包含列 `id`, `title`, 和 `director_id`。

导演表为 `directors`，包含列 `id`, `name`。

评分表为 `ratings`，包含列 `movie_id`, 和 `rating`。

查询每个导演所执导的电影的评分，并计算每个导演的平均评分。

5. 博客文章分类假设有一个博客文章数据库，包括文章、类别和作者等信息。

SQL语句创建学生信息数据库表的示例-学生信息数据库表

用SQL语句创建如下三个基本表：学生表(Student)、课程表（Course）、学生选课表（SC），结构如下所示Student表结构Create table Student(Sno varchar(7) primary key,Sname varchar(10) not null,Ssex char (2) check(Ssex=‘男’or Ssex=’女’),Sage int check(Sage between 15 and 45),Sdept varchar(20) default(‘计算机系’))Course表结构Create table course(Cno varchar(10) primary key,Cname varchar(20) not null,Ccredit int check(Sctedit>0),Semester int check(Semester>0),Period int check(Period>0))SC表结构Create table SC(Sno varchar(7) foreign key references student(Sno),Cno varchar(10) foreign key references course(Cno),Grade int check(Grade between 0 and 100),Primary key (Sno,Cno))1．查询学生选课表中的全部数据。

SELECT *FROM SCgo2．查询计算机系学生的姓名、年龄。

Select Sname,SageFrom StudentWhere Sdept=’计算机系’3．查询成绩在70～80分之间的学生的学号、课程号和成绩。

Select Sno,Cno,GradeFrom Course,ScWhere o=o and sc.Grade between 70 and 804．查询计算机系年龄在18～20之间且性别为“男”的学生的姓名和年龄。

SQL语句大全

SQL语句大全第一篇：SQL语句大全SQL练习一、设有如下的关系模式，试用SQL语句完成以下操作：学生(学号，姓名，性别，年龄，所在系)课程(课程号，课程名，学分，学期，学时)选课(学号，课程号，成绩)1．求选修了课程号为“C2”课的学生学号2．求选修了课程号为“C2”课的学生学号和姓名3．求没有选修课程号为“C2”课的学生学号4．求选修了课程号为“C2”，又选修了课程号为“C3”课的学生学号5.求选修了课程号为“C2”或“C3”课的学生学号6.求选修了全部课程的学生学号7.求至少选修了学号为“*****”的学生所学过的所有课程的学生的学号和姓名。

8.查询学生选课表中的全部数据9．查询计算机系学生的姓名、年龄10．查询成绩在70—80分之间的学生的学号、课程号和成绩11．查询计算机系年龄在18—20之间且性别为“男”的学生的姓名和年龄12．查询成绩在80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。

13．查询哪些课程没有人选修，要求列出课程号和课程名。

14.查询数学系成绩在80分以上的学生的学号，姓名15.查询课程号为“C02”的课程的最高分数。

16.查询计算机系学生的最大年龄和最小年龄。

17.统计每个系的学生人数。

18.统计每门课程的选课人数和考试最高分。

19.统计每个学生的选课门数和考试总成绩，并按选课门数的升序显示结果。

20.查询总成绩超过200分的学生，要求列出学号、总成绩。

21.用子查询实现如下查询：（1）查询选修了课程号“C01”的学生的姓名和所在系。

（2）查询数学系成绩在80分以上的学生的学号和姓名。

（3）查询计算机系考试成绩最高的学生的姓名。

22.删除选课成绩小于60分的学生的选课记录。

23.将所有选修了课程“C01”的学生的成绩加10分。

24.将计算机系所有选修了课程“计算机文化基础”课程的学生的成绩加10分。

25.创建查询学生的学号、姓名、所在系、课程号、课程名、课程学分的视图。

SQL语句三表50题

Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,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.tableswhere table_name='Temp_Table')drop table Temp_Tablegoselect 排名=identity(int,1,1),* INTO Temp_Table from Studentgoselect * from Temp_Tablegodrop database [ ] --删除空的没有名字的数据库问题：1、查询“”课程比“”课程成绩高的所有学生的学号；select a.S# from (select s#,score from SC where C#='001') a,(select s#,scorefrom SC where C#='002') bwhere a.score>b.score and a.s#=b.s#;2、查询平均成绩大于分的同学的学号和平均成绩；select S#,avg(score)from scgroup by S# having avg(score) >60;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#,Sname4、查询姓“李”的老师的个数；select count(distinct(Tname))from Teacherwhere Tname like '李%';5、查询没学过“叶平”老师课的同学的学号、姓名；select Student.S#,Student.Snamefrom Studentwhere 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.Snamefrom Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select S#,Sname from Studentwhere 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#)=(selectcount(C#) from Course,Teacher where Teacher.T#=Course.T# 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') score2from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;9、查询所有课程成绩小于分的同学的学号、姓名；select S#,Sname from Student where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);10、查询没有学全所有课的同学的学号、姓名；select Student.S#,Student.Sname from Student,SCwhere Student.S#=SC.S#group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名；select S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in select C# from SC where S#='1001';12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名；select distinct SC.S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in (select C# from SC where S#='001');13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；update SC set score=(select avg(SC_2.score)from SC SC_2where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；select S# from SC where C# in (select C# from SC where S#='1002')group by S# having count(*)=(select count(*) from SC where S#='1002');15、删除学习“叶平”老师课的SC表记录；Delect SC from course ,Teacherwhere Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“”课程的同学学号、、号课的平均成绩；Insert SC select S#,'002',(Select avg(score) from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');16、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分SELECT S# as 学生ID,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语,COUNT(*) AS 有效课程数, A VG(t.score) AS 平均成绩FROM SC AS tGROUP BY S#ORDER BY avg(t.score)17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.C# = R.C# andL.score = (SELECT MAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.C# = IL.C# and IM.S#=IL.S#GROUP BY IL.C#)ANDR.Score = (SELECT MIN(IR.score)FROM SC AS IRWHERE R.C# = IR.C#GROUP BY IR.C#);18、按各科平均成绩从低到高和及格率的百分数从高到低顺序SELECT t.C# AS 课程号,max(ame)AS 课程名,isnull(AVG(score),0) AS 平均成绩,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数FROM SC T,Coursewhere t.C#=course.C#GROUP BY t.C#ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC19、查询如下课程平均成绩和及格率的百分数(用"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 ELSE 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# = '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 数据库及格百分数FROM SC20、查询不同老师所教不同课程平均分从高到低显示SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(ame) AS 课程名称,A VG(Score) AS 平均成绩FROM SC AS T,Course AS C ,Teacher AS Zwhere T.C#=C.C# and C.T#=Z.T#GROUP BY C.C# ORDER BY A VG(Score) DESC22、查询如下课程成绩第名到第名的学生成绩单：企业管理（），马克思（），UML （），数据库（）[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩SELECT DISTINCT top 3SC.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 T1ON SC.S# = T1.S# AND T1.C# = '001'LEFT JOIN SC AS T2ON SC.S# = T2.S# AND T2.C# = '002'LEFT JOIN SC AS T3ON SC.S# = T3.S# AND T3.C# = '003'LEFT JOIN SC AS T4ON SC.S# = T4.S# AND T4.C# = '004'WHERE student.S#=SC.S# andISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITH TIESISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)FROM scLEFT JOIN sc AS T1ON sc.S# = T1.S# AND T1.C# = 'k1'LEFT JOIN sc AS T2ON sc.S# = T2.S# AND T2.C# = 'k2'LEFT JOIN sc AS T3ON sc.S# = T3.S# AND T3.C# = 'k3'LEFT JOIN sc AS T4ON sc.S# = T4.S# AND 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 < 60 THEN 1 ELSE 0 END) AS [60 -] FROM SC,Coursewhere SC.C#=Course.C#GROUP BY SC.C#,Cname;24、查询学生平均成绩及其名次SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT S#,A VG(score) AS 平均成绩FROM SCGROUP BY S#) AS T1WHERE 平均成绩> T2.平均成绩) as 名次,S# as 学生学号,平均成绩FROM (SELECT S#,A VG(score) 平均成绩FROM SCGROUP BY S# ) AS T2ORDER BY 平均成绩desc;25、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;26、查询每门课程被选修的学生数select c#,count(S#) from sc group by C#;27、查询出只选修了一门课程的全部学生的学号和姓名select SC.S#,Student.Sname,count(C#) AS 选课数from SC ,Studentwhere SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;28、查询男生、女生人数Select count(Ssex) as 男生人数from Student group by Ssex having Ssex='男';Select count(Ssex) as 女生人数from Student group by Ssex having Ssex='女'；29、查询姓“张”的学生名单SELECT Sname FROM Student WHERE Sname like '张%';30、查询同名同性学生名单，并统计同名人数select Sname,count(*) from Student group by Sname having count(*)>1;;31、年出生的学生名单(注：Student表中Sage列的类型是datetime)select Sname, CONVERT(char (11),DA TEPART(year,Sage)) as agefrom studentwhere CONVERT(char(11),DA TEPART(year,Sage))='1981';32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;33、查询平均成绩大于的所有学生的学号、姓名和平均成绩select Sname,SC.S# ,avg(score)from Student,SCwhere Student.S#=SC.S# group by SC.S#,Sname havingavg(score)>85;34、查询课程名称为“数据库”，且分数低于的学生姓名和分数Select Sname,isnull(score,0)from Student,SC,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# and ame='数据库'and score <60;35、查询所有学生的选课情况；SELECT SC.S#,SC.C#,Sname,CnameFROM SC,Student,Coursewhere SC.S#=Student.S# and SC.C#=Course.C# ;36、查询任何一门课程成绩在分以上的姓名、课程名称和分数；SELECT distinct student.S#,student.Sname,SC.C#,SC.scoreFROM student,ScWHERE SC.score>=70 AND SC.S#=student.S#;37、查询不及格的课程，并按课程号从大到小排列select c# from sc where scor e <60 order by C# ;38、查询课程编号为且课程成绩在分以上的学生的学号和姓名；select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';39、求选了课程的学生人数select count(*) from sc;40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );41、查询各个课程及相应的选修人数select count(*) from sc group by C#;42、查询不同课程成绩相同的学生的学号、课程号、学生成绩select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C#<>B.C# ;43、查询每门功成绩最好的前两名SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDER BY score DESC)ORDER BY t1.C#;44、统计每门课程的学生选修人数（超过人的课程才统计）。

sql语句大全（详细）

sql语句大全（详细）sql语句大全（详细）数据库操作1.查看所有数据库show databases;2.查看当前使用的数据库select database();3.创建数据库create databases 数据库名 charset=utf8;4.删除数据库drop database 数据库名5.使用数据句库use database 数据库名6.查看数据库中所有表show tables;表的操作1.查看表结构desc 表名2.创建表结构的语法create table table_name(字段名数据类型可选的约束条件）；demo：创建班级和学生表create table classes(id int unsigned auto_increment primary key not null, name varchar(10));create table students(id int unsigned primary key auto_increment not null, name varchar(20) default '',age tinyint unsigned default 0,height decimal(5,2),gender enum('男','女','人妖','保密'),cls_id int unsigned default 0)3.修改表–添加字段alter table 表名 add 列名类型demo：alter table students add birthday datetime;4.修改表–修改字段–重命名版alert table 表名 change 原名新名类型及约束demo：alter table syudents change birthday birth datetime not null;5.修改表–修改字段–不重命名alter table 表名 modify 列名类型及约束demo : alter table students modify birth date nout noll;6.删除表–删除字段alter table 表名 drop 列名demo :later table students drop birthday;7.删除表drop table 表名demo:drop table students;8.查看表的创建语句–详细过程show create table 表名demo : show create tabele students;查询基本使用1.查询所有列select * from 表名例：select * from classes;2.查询指定列select 列1，列2，...from 表名；例：select id,name from classes;增加说明：主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null) ; 字段默认值 default 来占位，插入成功后以实际数据为准1.全列插入：值的顺序与表结构字段的顺序完全一一对应此时字段名列表不用填写insert into 表名 values (...)例：insert into students values(0,’郭靖',1,'蒙古','2016-1-2');2.部分列插入：值的顺序与给出的列顺序对应此时需要根据实际的数据的特点填写对应字段列表insert into 表名 (列1,...) values(值1,...)例：insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信3.全列多行插入insert into 表名 values(...),(...)...;例：insert into classes values(0,'python1'),(0,'python2');4.部分列多行插入insert into 表名(列1,...) values(值1,...),(值1,...)...;例：insert into students(name) values('杨康'),('杨过'),('小龙女');修改update 表名 set 列1=值1,列2=值2... where 条件例：update students set gender=0,hometown='北京' where id=5;删除delete from 表名 where 条件例：delete from students where id=5;逻辑删除，本质就是修改操作update students set isdelete=1 where id=1;as关键字1.使用 as 给字段起别名select id as 序号, name as 名字, gender as 性别 from students;2.可以通过 as 给表起别名select s.id,,s.gender from students as s;条件语句查询where后面支持多种运算符，进行条件的处理比较运算符逻辑运算符模糊查询范围查询空判断比较运算符等于: =大于: >大于等于: >=小于等于: <=不等于: != 或 <>例1：查询编号大于3的学生select * from students where id > 3;例2：查询编号不大于4的学生select * from students where id <= 4;例3：查询姓名不是“黄蓉”的学生select * from students where name != '黄蓉';例4：查询没被删除的学生select * from students where is_delete=0;逻辑运算符andornot例5：查询编号大于3的女同学select * from students where id > 3 and gender=0;例6：查询编号小于4或没被删除的学生select * from students where id < 4 or is_delete=0;模糊查询like%表示任意多个任意字符_表示一个任意字符例7：查询姓黄的学生select * from students where name like '黄%';例8：查询姓黄并且“名”是一个字的学生select * from students where name like '黄_';例9：查询姓黄或叫靖的学生select * from students where name like '黄%' or name like '%靖';范围查询分为连续范围查询和非连续范围查询in表示在一个非连续的范围内例10：查询编号是1或3或8的学生select * from students where id in(1,3,8);between … and …表示在一个连续的范围内例11：查询编号为3至8的学生select * from students where id between 3 and 8;例12：查询编号是3至8的男生select * from students where (id between 3 and 8) and gender=1;空判断判断为空例13：查询没有填写身高的学生select * from students where height is null;注意： 1. null与’'是不同的 2. is null判非空is not null例14：查询填写了身高的学生select * from students where height is not null;例15：查询填写了身高的男生select * from students where height is not null and gender=1;优先级优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符and比or先运算，如果同时出现并希望先算or，需要结合()使用排序排序查询语法：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]语法说明:将行数据按照列1进行排序，如果某些行列1 的值相同时，则按照列2 排序，以此类推asc从小到大排列，即升序desc从大到小排序，即降序默认按照列值从小到大排列（即asc关键字）例1：查询未删除男生信息，按学号降序select * from students where gender=1 and is_delete=0 order by id desc;例2：查询未删除学生信息，按名称升序select * from students where is_delete=0 order by name;例3：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时按照身高从高–>矮排序select * from students order by age desc,height desc;分页select * from 表名 limit start=0,count说明从start开始，获取count条数据start默认值为0也就是当用户需要获取数据的前n条的时候可以直接写上xxx limit n;例1：查询前3行男生信息select * from students where gender=1 limit 0,3;关于分页的一个有趣的推导公式已知：每页显示m条数据，当前显示第n页求总页数：此段逻辑后面会在python项目中实现查询总条数p1使用p1除以m得到p2如果整除则p2为总数页如果不整除则p2+1为总页数获取第n页的数据的SQL语句求解思路第n页前有n-1页所在第n页前已经显示的数据的总量是(n-1)*m由于数据的下标从0开始所以第n页前所有的网页的下标是0,1,…,(n-1)*m-1所以第n页的数据起始下标是(n-1)*m获取第n页数据的SQL语句select * from students where is_delete=0 limit (n-1)*m,m注意：在sql语句中limit后不可以直接加公式聚合函数总数count(*) 表示计算总行数，括号中写星与列名，结果是相同的例1：查询学生总数select count(*) from students;最大值max(列) 表示求此列的最大值例2：查询女生的编号最大值select max(id) from students where gender=2;最小值min(列) 表示求此列的最小值例3：查询未删除的学生最小编号select min(id) from students where is_delete=0;求和sum(列) 表示求此列的和例4：查询男生的总年龄select sum(age) from students where gender=1;–平均年龄select sum(age)/count(*) from students where gender=1;平均值avg(列) 表示求此列的平均值例5：查询未删除女生的编号平均值select avg(id) from students where is_delete=0 andgender=2;分组group bygroup by + group_concat()group_concat(字段名)根据分组结果，使用group_concat()来放置每一个分组中某字段的集合group by + 聚合函数通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作group by + havinghaving 条件表达式：用来过滤分组结果having作用和where类似，但having只能用于group by 而where是用来过滤表数据group by + with rollupwith rollup的作用是：在最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果。

学生表_课程表_成绩表_教师表_50个常用sql语句

成绩表scscscore学生编号c课程编号score分数创建测试数据createtablestudentsvarchar10snamenvarchar10sagedatetimessexnvarchar10insertstudentvalues?01?studentvalues?02?studentvalues?03?studentvalues?04?studentvalues?05?studentvalues?06?studentvalues?07?studentvalues?08?createtablecoursecvarchar10cnamenvarchar10tvarchar10insertcoursevalues?01??02?insertcoursevalues?02??01?insertcoursevalues?03??03?createtableteachertvarchar10tnamenvarchar10insertteachervalues?01?n?张三?insertteachervalues?02?n?李四?insertteachervalues?03?createtablescsvarchar10cvarchar10scoredecimal181insertscvalues?01?80insertscvalues?01?90insertscvalues?01?99insertscvalues?02?70insertscvalues?02?60insertscvalues?02?80insertscvalues?03?80insertscvalues?03?80insertscvalues?03?80insertscvalues?04?50insertscvalues?04?30insertscvalues?04?20insertscvalues?05?76insertscvalues?05?87insertscvalues?06?31insertscvalues?06?34insertscvalues?07?89insertscvalues?07?98go1查询01课程比02课程成绩高的学生的信息及课程分数11查询同时存在01课程和02课程的情况selectbscore课程?01?的分数cscore课程?02?的分数fromstudentwhereascscore12查询同时存在01课

sqlite sql建表语句

sqlite sql建表语句1. 创建学生表：CREATE TABLE student (id INT PRIMARY KEY,name VARCHAR(50),age INT,gender VARCHAR(10),major VARCHAR(50));2. 创建课程表：CREATE TABLE course (id INT PRIMARY KEY,name VARCHAR(50),teacher VARCHAR(50),credit FLOAT);3. 创建成绩表：CREATE TABLE score (id INT PRIMARY KEY,student_id INT,course_id INT,score FLOAT,FOREIGN KEY(student_id) REFERENCES student(id), FOREIGN KEY(course_id) REFERENCES course(id));4. 创建部门表：CREATE TABLE department (id INT PRIMARY KEY,name VARCHAR(50),location VARCHAR(50));5. 创建员工表：CREATE TABLE employee (id INT PRIMARY KEY,name VARCHAR(50),age INT,gender VARCHAR(10),department_id INT,FOREIGN KEY(department_id) REFERENCES department(id) );6. 创建订单表：CREATE TABLE orders (id INT PRIMARY KEY,customer_id INT,order_date DATE,total_amount FLOAT,FOREIGN KEY(customer_id) REFERENCES customer(id) );7. 创建产品表：CREATE TABLE product (id INT PRIMARY KEY,name VARCHAR(50),price FLOAT);8. 创建供应商表：CREATE TABLE supplier (id INT PRIMARY KEY,name VARCHAR(50),contact VARCHAR(50),address VARCHAR(100));9. 创建销售表：CREATE TABLE sales (id INT PRIMARY KEY,product_id INT,quantity INT,order_id INT,FOREIGN KEY(product_id) REFERENCES product(id), FOREIGN KEY(order_id) REFERENCES orders(id) );10. 创建文章表：CREATE TABLE article (id INT PRIMARY KEY,title VARCHAR(100),author VARCHAR(50),content TEXT,create_date DATE。

MysqlSql语句练习题（50道）

MysqlSql语句练习题（50道）表名和字段–1.学⽣表Student(s_id,s_name,s_birth,s_sex) –学⽣编号,学⽣姓名, 出⽣年⽉,学⽣性别–2.课程表Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号–3.教师表Teacher(t_id,t_name) –教师编号,教师姓名–4.成绩表Score(s_id,c_id,s_score) –学⽣编号,课程编号,分数测试数据--建表--学⽣表CREATE TABLE `Student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT '',`s_birth` VARCHAR(20) NOT NULL DEFAULT '',`s_sex` VARCHAR(10) NOT NULL DEFAULT '',PRIMARY KEY(`s_id`));--课程表CREATE TABLE `Course`(`c_id` VARCHAR(20),`c_name` VARCHAR(20) NOT NULL DEFAULT '',`t_id` VARCHAR(20) NOT NULL,PRIMARY KEY(`c_id`));--教师表CREATE TABLE `Teacher`(`t_id` VARCHAR(20),`t_name` VARCHAR(20) NOT NULL DEFAULT '',PRIMARY KEY(`t_id`));--成绩表CREATE TABLE `Score`(`s_id` VARCHAR(20),`c_id` VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`));--插⼊学⽣表测试数据insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');insert into Student values('06' , '吴兰' , '1992-03-01' , '⼥');insert into Student values('07' , '郑⽵' , '1989-07-01' , '⼥');insert into Student values('08' , '王菊' , '1990-01-20' , '⼥');--课程表测试数据insert into Course values('01' , '语⽂' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');--教师表测试数据insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');--成绩表测试数据insert into Score values('01' , '01' , 80);insert into Score values('01' , '02' , 90);insert into Score values('01' , '03' , 99);insert into Score values('02' , '01' , 70);insert into Score values('02' , '02' , 60);insert into Score values('02' , '03' , 80);insert into Score values('03' , '01' , 80);insert into Score values('03' , '02' , 80);insert into Score values('03' , '03' , 80);insert into Score values('04' , '01' , 50);insert into Score values('04' , '02' , 30);insert into Score values('04' , '03' , 20);insert into Score values('05' , '01' , 76);insert into Score values('05' , '02' , 87);insert into Score values('06' , '01' , 31);insert into Score values('06' , '03' , 34);insert into Score values('07' , '02' , 89);insert into Score values('07' , '03' , 98);表数据如下student 学⽣表：s_id s_name s_birth s_sex01赵雷1990-01-01男02钱电1990-12-21男03孙凤1990-05-20男04李云1990-08-06男05周梅1991-12-12⼥06吴兰2017-12-13⼥07郑⽵1989-07-01⼥08王菊1990-01-20⼥09赵雷1990-01-21⼥10赵雷1990-01-22男score 分数表：s_id c_id s_score010180010290010399020170020260020380030180030280030380040150040230040320050176050387060131060334070389070198course 课程表c_id c_name t_id01语⽂0202数学0103英语03teacher ⽼师表：t_id t_name01张三02李四03王五-- 准备条件,去掉 sql_mode 的 ONLY_FULL_GROUP_BY 否则此种情况下会报错:-- Expression #1 of select list is not in group by clause and contains nonaggregated column 'userinfo.-- 原因：-- MySQL 5.7.5和up实现了对功能依赖的检测。

学生、老师、课程、选课表常用sql示例

学⽣、⽼师、课程、选课表常⽤sql⽰例 1、建表与准备数据（1）学⽣表 studentDROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`sid` int(11) DEFAULT NULL,`sname` varchar(20) DEFAULT NULL,`sage` int(11) DEFAULT NULL,`ssex` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('1', '刘⼀', '18', '男');INSERT INTO `student` VALUES ('2', '钱⼆', '19', '⼥');INSERT INTO `student` VALUES ('3', '张三', '17', '男');INSERT INTO `student` VALUES ('4', '李四', '18', '⼥');INSERT INTO `student` VALUES ('5', '王五', '17', '男');INSERT INTO `student` VALUES ('6', '赵六', '19', '⼥'); （2）⽼师表 teacherDROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`tid` int(11) DEFAULT NULL,`tname` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of teacher-- ----------------------------INSERT INTO `teacher` VALUES ('1', '叶平');INSERT INTO `teacher` VALUES ('2', '贺⾼');INSERT INTO `teacher` VALUES ('3', '杨艳');INSERT INTO `teacher` VALUES ('4', '周磊'); （3）课程表 courseDROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`cid` int(11) DEFAULT NULL,`cname` varchar(255) DEFAULT NULL,`tid` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES ('1', '语⽂', '1');INSERT INTO `course` VALUES ('2', '数学', '2');INSERT INTO `course` VALUES ('3', '英语', '3');INSERT INTO `course` VALUES ('4', '物理', '4'); （4）选课表 scDROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (`sid` int(11) DEFAULT NULL,`cid` int(20) DEFAULT NULL,`score` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of sc-- ----------------------------INSERT INTO `sc` VALUES ('1', '1', '60');INSERT INTO `sc` VALUES ('1', '2', '78');INSERT INTO `sc` VALUES ('1', '3', '67');INSERT INTO `sc` VALUES ('1', '4', '58');INSERT INTO `sc` VALUES ('2', '1', '79');INSERT INTO `sc` VALUES ('2', '2', '81');INSERT INTO `sc` VALUES ('2', '3', '92');INSERT INTO `sc` VALUES ('2', '4', '68');INSERT INTO `sc` VALUES ('3', '1', '91');INSERT INTO `sc` VALUES ('3', '2', '47');INSERT INTO `sc` VALUES ('3', '3', '88');INSERT INTO `sc` VALUES ('3', '4', '56');INSERT INTO `sc` VALUES ('4', '2', '88');INSERT INTO `sc` VALUES ('4', '3', '90');INSERT INTO `sc` VALUES ('4', '4', '93');INSERT INTO `sc` VALUES ('5', '1', '46');INSERT INTO `sc` VALUES ('5', '3', '78');INSERT INTO `sc` VALUES ('5', '4', '53');INSERT INTO `sc` VALUES ('6', '1', '35');INSERT INTO `sc` VALUES ('6', '2', '68');INSERT INTO `sc` VALUES ('6', '4', '71'); 2、sql⽰例（01）查询“1”课程⽐“2”课程成绩⾼的所有学⽣的学号；SELECT a.sid FROM(SELECT * FROM sc WHERE cid='1') a,(SELECT * FROM sc WHERE cid='2') bWHERE a.sid = b.sid AND a.score>b.scoreSELECT a.sid FROM sc a,sc b WHERE a.sid = b.sid AND a.cid='1' AND b.cid='2' AND a.score > b.score （02）查询平均成绩⼤于60分的同学的学号和平均成绩；SELECT sid,AVG(score) FROM sc GROUP BY sid HAVING AVG(score) > 60 （03）查询所有同学的学号、姓名、选课数、总成绩；SELECT a.sid,a.sname,b.num,b.total FROM student a,(SELECT sid,COUNT(1) num,SUM(score) total FROM sc GROUP BY sid) bWHERE a.sid = b.sid;SELECT a.sid,a.sname,COUNT(sc.cid),SUM(score) FROM student a LEFT JOIN sc ON a.sid = sc.sid GROUP BY a.sid,a.sname （04）查询没学过“叶平”⽼师课的同学的学号、姓名；SELECT student.sid,student.sname FROM student WHERE student.sid not in(SELECT sid FROM sc,course,teacher WHERE sc.cid = course.cid and course.tid = teacher.tid and teacher.tname ='叶平'); （05）查询学过编号“1”并且也学过编号“2”课程的同学的学号、姓名；SELECT s.sid,s.sname FROM student s,sc a,sc b WHERE a.sid = b.sid AND a.sid = s.sid AND a.cid = '1' and b.cid = '2'SELECT student.sid,student.sname FROM sc,student WHERE sc.sid = student.sid and sc.cid = '1' AND EXISTS(SELECT * FROM sc sc2 WHERE sc2.sid = sc.sid and sc2.cid = '2') （06）查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；SELECT sid,sname FROM student WHERE sid in (SELECT sc.sid FROM teacher t,course c,sc WHERE t.tid = c.tid AND c.cid = sc.cid AND t.tname = '叶平'GROUP BY sc.sid HAVING COUNT(sc.cid) = (SELECT COUNT(1) FROM course c,teacher t WHERE c.tid = t.tid AND t.tname = '叶平') ) （07）查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；SELECT s.sid,s.sname FROM sc a,sc b,student s WHERE a.sid = b.sid AND a.sid = s.sidAND a.cid='2' AND b.cid = '1' AND a.score < b.scoreSELECT s.sid,s.sname FROM sc,student s WHERE sc.cid = '2' AND sc.sid = s.sid AND EXISTS(SELECT * FROM sc sc2 WHERE sc.sid = sc2.sid AND sc2.cid = '1' AND sc.score < sc2.score) （08）查询所有课程成绩⼩于60分的同学的学号、姓名；SELECT * FROM student WHERE sid not in (SELECT sc.sid FROM sc WHERE sc.score >=60) （09）查询没有学全所有课的同学的学号、姓名；SELECT sid,sname FROM student WHERE sid in (SELECT sc.sid FROM sc GROUP BY sc.sid HAVING COUNT(1)<(SELECT count(1) from course))SELECT sc.sid,sname FROM sc,student WHERE sc.sid = student.sid GROUP BY sc.sid,snameHAVING COUNT(1)<(SELECT count(1) from course) （10）查询⾄少有⼀门课与学号为“1001”的同学所学相同的同学的学号和姓名；SELECT sid,sname from student WHERE sid in (SELECT sc.sid FROM sc WHERE sc.sid !='1' AND sc.cid in (SELECT sc.cid FROM sc WHERE sc.sid = '1'))SELECT distinct s.sid,s.sname from student s,sc WHERE s.sid = sc.sid AND s.sid != '1'AND sc.cid in (SELECT cid FROM sc WHERE sc.sid = '1') （11）#查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；SELECT sid,sname from student WHERE sid in (SELECT sid from (SELECT * from sc WHERE sc.sid != '1' AND EXISTS(SELECT * FROM sc sc2 WHERE sc.cid = sc2.cid AND sc2.sid = '1')) a GROUP BY sid HAVING COUNT(1)=(SELECT count(1) FROM sc WHERE sid = '1' )) （12）（13）（14）（15）（16）（17）（18）（19）（20）（21）（22）（23）（24）（25）。

SQL语句

针对student、class、course、score四张表完成以下查询（将查询窗口截图）：
一、简单查询
1、查询student表中所有同学的信息。

2、查询所有开设课程的课程名称和考核方式。

3、查询所有选课学生的学号（如一个同学同时选修了多门课程，学号只显示一次）。

4、查询所有学时在[40,60]范围内的课程的课程编号和课程名称。

5、查询所有学生党员的学号和姓名。

6、查询所有姓张的学生的姓名和性别。

7.查询所有学生的姓名及年龄，并按照年龄从小到大的顺序显
8查询入学成绩排在前3位的学生姓名，并列的都显示。

9查询所有10月份出生的学生人数
10、查询没有先修课的课程名称。

11、查询入学成绩的最高分和最低分，列明分别为最高分和最低分。

12、统计男女生的入学平均成绩，显示性别和平均成绩两列。

13、查询选修了两门以上（包括两门）课程的学生的学号。

1、查询所有选课学生的姓名和选修课程的编号
2、查询所有学生的姓名、课程名称、成绩，包括为选课的同学（即选课的同学显示姓
名、课程名、成绩，未选课的同学只显示姓名）
3、查询同时选修了管理学和计算机文化基础两门课程的学生名单。

4、查询入学成绩高于所有男同学的女生姓名。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

50个常用sql语句实例(学生表 课程表 成绩表 教师表)

Sql语句之查询所有学生所有科目分数及总分

sql-hive笔试题整理1（学生表-成绩表-课程表-教师表）

学生各门课程成绩统计SQL语句大全

SQL语句大全实例

50个经典sql语句总结

SQL语句大全实例

SQL语句创建学生信息数据库表的示例-学生信息数据库表

50个经典SQL语句

50个常用sql语句实例(学生表_课程表_成绩表_教师表)

sql查询50题

sql 语句经典题目

SQL语句创建学生信息数据库表的示例-学生信息数据库表

SQL语句大全

SQL语句三表50题

sql语句大全（详细）

学生表_课程表_成绩表_教师表_50个常用sql语句

sqlite sql建表语句

MysqlSql语句练习题（50道）

学生、老师、课程、选课表常用sql示例

SQL语句

50个常用sql语句实例(学生表课程表成绩表教师表)