50道基础sql语句

sql语句练习50题

 

创建表cmd命令dos命令直接粘贴即可

注意中文一般没有办法存到表中，需要自行更改中文 

如果在cmd命令中进行查询语句

1. 语句中有中文 将表改成utf8格式 命令为 set names utf8; 

2.语句后面必须加英文状态下的分号（;）

cmd命令=dos命令

文章中的@符号 是说明 语句有两种方法都可以查询出来

Student(Sid,Sname,Sage,Ssex) 学生表

 

create table Student(Sid varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));

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' , '女');

 

Course(Cid,Cname,Tid) 课程表

 

create table Course(Cid varchar(10),Cname nvarchar(10),Tid varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

 

Teacher(Tid,Tname) 教师表

 

create table Teacher(Tid varchar(10),Tname nvarchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

insert into Teacher values('04' , '叶平');

 

 

SC(Sid,Cid,score) 成绩表

 

create table SC(Sid varchar(10),Cid 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);

 

 

 

                           

Student(Sid,Sname,Sage,Ssex) 学生表

Course(Cid,Cname,Tid) 课程表

SC(Sid,Cid,score) 成绩表

Teacher(Tid,Tname) 教师表

 

 

基础sql   50道

1.查询学生赵雷的出生年月

select sage from student where sname ="赵雷"

2.查询学生钱电的出生年月

select sage from student where sname ="钱电"

3.查询学生孙风的出生年月

select sage from student where sname ="孙风"

4.查询出生年月是1990-08-06的学生姓名

select sname from student where sage ="1990-08-06"

5.查询出生年月是1991-12-01的学生姓名

select sname from student where sage ="1991-12-01"

6.查询出生年月是1992-03-01的学生姓名

select sname from student where sage ="1992-03-01"

7.查询出生年月是1989-07-01的学生姓名

select sname from student where sage ="1989-07-01"

8.查询出生年月是1990-01-20的学生姓名

select sname from student where sage ="1990-01-20"

9.查询一共有几个女生

Select count(ssex) from student where ssex=”女”

10.查询一共有几个男生

Select count(ssex) from student where ssex=”男”

11.查询学习语文的学生姓名

select DISTINCT(sname) from teacher JOIN course on teacher.tid = course.tid JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid and cname = "语文";

12.查询学习数学的学生姓名

select DISTINCT(sname) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid and cname = "数学";

13.查询学习英语的学生姓名

select DISTINCT(sname) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid and cname = "英语";

14.查询王五老师的学生姓名

select DISTINCT(sname) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid and tname = "王五";

15.查询张三老师的学生姓名

select DISTINCT(sname) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid and tname = "张三";

16.查询李四老师的学生姓名

select DISTINCT(sname) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid and tname = "李四";

17.查询王菊的性别

Select ssex from student where sname =”王菊”

18.查询孙风的性别

Select ssex from student where sname =”孙风”

19.查询赵雷的语文成绩

@select score from  course JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where sname="赵雷" and cname="语文"

@select sc.score from sc where sid=(select sid from student where sname="赵雷") and cid=(select cid from course where cname="语文")

20.查询钱电的数学成绩

select score from  course JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where sname="钱电" and cname="数学"

21.查询谁的语文成绩最高

@select score from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where cname="语文" ORDER BY score desc limit 1;

@select sname,mac(Score) from  teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where cname="语文"

22.查询谁的数学成绩最低

@select score from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where cname="数学" ORDER BY score asc limit 1;

@select sname,min(score) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where cname="数学"

23.查询英语成绩的平均值

select avg(score) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where cname="英语"

24.查询孙风的英语成绩

select score from  course JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where sname="孙风" and cname="英语"

25.查询所有成绩中有几个低于60分

Select count(score) from sc where score<60

26.查询所有成绩中有几个及格分数（80分以上）

Select count(score) from sc where score>80

27.查询赵雷有几科成绩及格

select count(score) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where sname="赵雷" and score>80

28.查询钱电有几科成绩及格

select scount(score) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where sname="赵雷" and score>80

29.查询孙风有哪几科成绩不及格

select scount(score) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where sname="孙风" and score<80

30.查询60到80之间的语文成绩有几个

select count(score) from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where cname="语文" and score BETWEEN 80 and 60

31.查询语文成绩升序排列

select score from course  JOIN sc on course.cid = sc.cid where cname="语文" ORDER BY score desc

32. 查询英语成绩降序排列

select score from course  JOIN sc on course.cid = sc.cid where cname="英语" ORDER BY score asc

33.查询查询所有成绩的平均值

select avg(score) from sc

34.查询所有成绩的总值

select sum(score) from sc

35.查询钱电哪一科成绩最低

select cname from teacher JOIN course on teacher.tid = course.tid JOIN sc on course.cid = sc.cid  JOIN student on sc.sid = student.sid where sname="钱电" ORDER BY score desc limit 1

36.查询周梅哪一科成绩最高

select cname from teacher JOIN course on teacher.tid = course.tid  JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where sname="周梅" ORDER BY score asc limit 1

37.查询80分的学生名称以及科目

select sname,cname from teacher JOIN course on teacher.tid = course.tid JOIN sc on course.cid = sc.cid JOIN student on sc.sid = student.sid where score>80

38.将钱电的语文成绩改成80

update sc set score='80' where Sid=(select Sid from student where Sname='欠点')  and  Cid= (select Cid from course where Cname='语文');

39.将吴兰的数学成绩改成60

update sc set score='80' where Sid=(select Sid from student where Sname='吴兰')  and  Cid= (select Cid from course where Cname='数学');

40.将郑竹的三科成绩查询出来

select sname,cname,score from teacher JOIN course on teacher.tid = course.tid JOIN sc on course.cid = sc.cid jOIN student on sc.sid = student.sid where sname="郑竹"

41. 将王菊的三科成绩查询出来

select sname,cname,score from teacher JOIN course on teacher.tid = course.tid JOIN sc on course.cid = sc.cid jOIN student on sc.sid = student.sid where sname="王菊"

42.将钱电的英语成绩改成100

update sc set score='80' where Sid=(select Sid from student where Sname='钱电')  and  Cid= (select Cid from course where Cname='英语');

43.将吴兰的性别改成男

UPDATE student set ssex= "男" where sname = "吴兰"

44. 将钱电的性别改成女

UPDATE student set ssex= "女" where sname = "钱电"

45.将英语成绩增加20分

update sc set score=score+20 where Cid= (select Cid from course where Cname='英语');

46.将语文成绩降低20分

update sc set score=score-20 where Cid= (select Cid from course where Cname='语文');

47.删除赵雷的学生信息

DELETE FROM student where sname = "赵雷"

48.删除80分一下的成绩

Delete from score where score=80

49.删除张三老师的记录

Delete from teacher where tname=”张三”

50.删除王菊的记录删除

Delete from student where sname = “王菊”