锐客网

  1. 首页 > 睿知 > >

如何学习SQL语言？( 三 )

生活知识经验知识


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,SC where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;
34、查询课程名称为“数据库” 。且分数低于60的学生姓名和分数
Select Sname,isnull(score,0) from Student,SC,Course where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;
35、查询所有学生的选课情况；
【如何学习SQL语言？】SELECT SC.S#,SC.C#,Sname,Cname FROM SC,Student,Course where SC.S#=Student.S# and SC.C#=Course.C# ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT distinct student.S#,student.Sname,SC.C#,SC.score FROM student,Sc WHERE 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,score from Student,SC,Course C,Teacher where 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 t1 WHERE score IN (SELECT TOP 2 score FROM SC WHERE t1.C#= C# ORDER BY score DESC ) ORDER BY t1.C#;
44、统计每门课程的学生选修人数（超过10人的课程才统计） 。要求输出课程号和选修人数 。查询结果按人数降序排列 。查询结果按人数降序排列 。若人数相同 。按课程号升序排列
select C# as 课程号,count(*) as 人数 from sc group by C# order by count(*) desc,c#
45、检索至少选修两门课程的学生学号
select S# from sc group by s# having count(*) > = 2
46、查询全部学生都选修的课程的课程号和课程名
select C#,Cname from Course where C# in (select c# from sc group by c#)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
48、查询两门以上不及格课程的同学的学号及其平均成绩
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;
49、检索“004”课程分数小于60 。按分数降序排列的同学学号
select S# from SC where C#='004'and score <60 order by score desc;
50、删除“002”同学的“001”课程的成绩
delete from Sc where S#='001'and C#='001';
其他观点：
[root@localhost ~]# mysql -u root -p
Enter password:
mysql> exit
Bye
二、创建数据库
语法：CREATE DATABASE <数据库名>；
mysql> create DATABASE lemon;
三、显示数据库
语法：show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| future|
| lemon|
| mysql
| test|
| test_mysql|
+--------------------+
四、删除数据库
语法：drop database <数据库名>;
mysql> drop DATABASE test;
五、选择数据库
语法：use <数据库名>;
mysql> use lemon;
Database changed
六、创建表
语法：create table <表名> ( <字段名1> <类型1> 。<字段名2> <类型2> 。...,<字段名n> <类型n>);
create table student(
sno int(8) not null primary key auto_increment comment '学号',
sname varchar(20) NOT NULL comment '姓名',
ssex varchar(4) NOT NULL comment '性别',
sclass int(8) NOT NULL comment '班级',
sage int(4) NOT NULL comment '年龄'
)DEFAULT CHARSET=utf8;
七、显示数据表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student |
| student1 |
+----------------+

如何学习SQL语言？

推荐阅读


上一篇：绝地求生大逃杀中,AKM这把突击步枪使用起来,灵敏度多少合适？

下一篇：JavaScript：对事件作出反应