sql语句（一）()-sql

发表于： 3月 17, 2023 3月 17, 2023
分类：编程
标签： sql

sql语句（一）()

表结构：

（末尾有完整sql语句）
c（cno,cname,tno) 课程表
s (sno,sname,age,sex) 学生表
sc (sno,cno,score) 学生-课程表
t (tno,tname,title) 教师表

1.检索

小建议，三个表及以上，就用多表连接把，然后加上DISTINCT

1./查询年龄小于17岁的女学生的学号和姓名/

select sno,sname from  s 
where age < 17 and sex="f"

2./检索男学生所学课程的课程号和课程名。/

方法一：

select cno,cname from c
where cno in(
	select cno from sc
	where sno in(
		select sno from s
		where sex="m"))

方法二：

select DISTINCT c.cno, c.cname from c,sc,s
where c.cno=sc.cno and s.sno=sc.sno and sex="m"

3./检索男学生所学课程的任课老师的工号和姓名。/

select DISTINCT t.tno,t.tname from t,c,s,sc
where t.tno=c.tno and c.cno=sc.cno and s.sno=sc.sno

4./检索至少选修两门课程的学生学号。/

方法一：

select sno from sc
group by sno
having count(sno)>=2

方法二：

select DISTINCT x.sno from sc as x,sc as y
where x.sno=y.sno and x.cno!=y.cno

5./检索至少有学号为S2和S4学生选修的课程的课程号。/

select DISTINCT x.cno from sc as x,sc as y
where x.sno="s2" and y.sno="s4" and x.cno=y.cno

6./检索王同学不学的课程的课程号。/

方法一：

select cno from c
where cno not in(/*王的课程*/
	select cno from sc
	where sc.sno in(/*王的学号*/
		select sno from s
		where sname = "王"))

方法二：

select cno from c
where not exists(
	select * from s,sc
	where s.sno=sc.sno and sc.cno=c.cno and s.sname="王")

7./检索全部学生都选修的课程的课程号与课程名。/

方法一：

select cno,cname from c
where cno in(/*在sc表中，课程号出现的次数*/
	select cno from sc
	group by cno
	having count(cno)=(/*学生人数*/
		select count(*) from s))

方法二：

select cno,cname from c
where not exists(
	select * from s
	where not exists(
		select * from sc

8./检索选修课程包含张xx所授全部课程的学生学号/

张老师一共教了两门课，这两门课都选的学生

方法一：

select sno from sc
where cno in(/*张老师教的课*/
	select cno from c
	where tno in(/*张老师的老师号*/
		select tno from t 
		where tname ="张xx"))
group by sno
having count(sno)=(/*张老师教几门课*/
	select count(*) from c
	where tno in(
		select tno from t
		where tname="张xx"))

方法二：

SELECT DISTINCT sno from sc as x
where not EXISTS(
SELECT * from c,t 
where c.tno=t.tno and tname="张xx" and not EXISTS
(SELECT * FROM sc as y where y.sno=x.sno and y.cno=c.cno))

2.检索（简单）

1./* 统计有学生选修的课程门数。*/

select count(DISTINCT cno) from sc

2./求选修C4课程的女学生的平均年龄。/

方法一：

select avg(age) from s
where sex="f" and sno in(
	select sno from sc
	where cno="c4" )

方法二：

select avg(age) from s,sc
where s.sno=sc.sno and cno="c4" and sex="f"

3./求张老师所授课程的每门课程的平均成绩。/

select c.cno,avg(score) from t,c,s,sc
where c.cno=sc.cno and s.sno=sc.sno and t.tno=c.tno and t.tname="张xx"
group by c.cno

4./统计每门课程的学生选修人数（超过2人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同,按课程号升序排列。/

select cno,count(cno) from sc
group by cno
having count(cno)>2
order by 2 desc,cno

5./检索学号比王同学大，而年龄比他小的学生姓名。/

select sname from s
where age<(
	select age from s
	where sname="王") 
	and sno >(
	select sno from s
	where sname="王")

6./在表SC中检索成绩为空值的学生学号和课程号。/

select sno,cno from sc
where score is null

7./检索姓名以L打头的所有学生的姓名和年龄。/

select sname,age from s
where sname like "L%"

8./求年龄大于女同学平均年龄的男学生姓名和年龄。/

select sname,age from s
where sex="m" and age>(
	select avg(age) from s
	where sex="f")

9./求年龄大于所有女同学年龄的男学生姓名和年龄。/

select sname,age from s
where sex="m" and age>all(
	select age from s
	where sex="f")

3.增删改查

1./向关系C中插一个课程元组(C8,’vC++,T6′)。/

insert into c values('c8','VC++','t6')

2./检索所授每门课程的平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY (TNAME）中。/

insert into FACULTY(tname)
select DISTINCT t.tname from c,sc,t
where c.cno=sc.cno and c.tno=t.tno and c.cno in(
	select cno from sc
	where score>80)

3./在SC中删除尚无成绩的选课元组。/

delete from sc 
where score is null

4./把选修张老师课程的女同学的选课元组全部删去。/

SELECT * from c,s,sc,t??
where c.cno=sc.cno and s.sno=sc.sno and c.tno=t.tno
and t.tname="张xx" and s.sex="f"

5./把html课不及格的成绩全改为60 分。/

update sc
set score =60
where cno in(
	select cno from c
	where cname like "html%") and score<60

6./把低于所有课程总平均成绩的女同学成绩提高5%。/

update sc
set score =score*1.05
where score<(
	select avg(score) from sc) 
and sno in(
	select sno from s
	where sex="f")

7./在表SC中修改C4课程的成绩，当成绩小于等于70分时提高5%，当成绩大于70分时提高4%。(用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现>。/

第一种方法：
/顺序不可颠倒/

update sc
set score = score*1.04
where cno="c4" and score >70
update sc
set score = score*1.05
where cno="c4" and score <=70

第二种方法：

update sc
set score =score*case
	when score>70 then 1.04
	else 1.05 
	end
where cno="c4"

8./在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。/

update sc
set score=score*1.05
where score<(
	select avg(score) from sc)

c（cno,cname,tno) 课程表

INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c1', 'java课', 't1');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c2', 'c语言课', 't2');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c3', 'android课', 't3');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c4', 'html课', 't4');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c5', '编译原理', 't5');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c6', 'python', 't1');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c8', 'VC++', 't6');

s (sno,sname,age,sex) 学生表

INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s1', '潘', 15, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s2', '赵', 13, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s3', '陈 ', 12, 'm');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s4', '王', 20, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s5', '朱', 3, 'm');

sc (sno,cno,score) 学生-课程表

INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s1', 'c1', '101.85000000000001');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s1', 'c6', '94.5');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c1', '46.2');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c2', '102.9');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c3', '105');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s3', 'c1', '45');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s3', 'c2', '32');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c1', '45.15');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c2', '45.15');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c4', '69.45750000000001');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c1', '56');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c3', '43');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c6', '90');

t (tno,tname,title) 教师表

INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t1', '张xx', '高级教师');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t2', '王xx', '教授');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t3', '吴x', '教授');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t4', '方xx', '老师');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t5', '周xx', '老师');

————————

表结构：

（末尾有完整sql语句）
c（cno,cname,tno) 课程表
s (sno,sname,age,sex) 学生表
sc (sno,cno,score) 学生-课程表
t (tno,tname,title) 教师表

1.检索

小建议，三个表及以上，就用多表连接把，然后加上DISTINCT

1./查询年龄小于17岁的女学生的学号和姓名/

select sno,sname from  s 
where age < 17 and sex="f"

2./检索男学生所学课程的课程号和课程名。/

方法一：

select cno,cname from c
where cno in(
	select cno from sc
	where sno in(
		select sno from s
		where sex="m"))

方法二：

select DISTINCT c.cno, c.cname from c,sc,s
where c.cno=sc.cno and s.sno=sc.sno and sex="m"

3./检索男学生所学课程的任课老师的工号和姓名。/

select DISTINCT t.tno,t.tname from t,c,s,sc
where t.tno=c.tno and c.cno=sc.cno and s.sno=sc.sno

4./检索至少选修两门课程的学生学号。/

方法一：

select sno from sc
group by sno
having count(sno)>=2

方法二：

select DISTINCT x.sno from sc as x,sc as y
where x.sno=y.sno and x.cno!=y.cno

5./检索至少有学号为S2和S4学生选修的课程的课程号。/

select DISTINCT x.cno from sc as x,sc as y
where x.sno="s2" and y.sno="s4" and x.cno=y.cno

6./检索王同学不学的课程的课程号。/

方法一：

select cno from c
where cno not in(/*王的课程*/
	select cno from sc
	where sc.sno in(/*王的学号*/
		select sno from s
		where sname = "王"))

方法二：

select cno from c
where not exists(
	select * from s,sc
	where s.sno=sc.sno and sc.cno=c.cno and s.sname="王")

7./检索全部学生都选修的课程的课程号与课程名。/

方法一：

select cno,cname from c
where cno in(/*在sc表中，课程号出现的次数*/
	select cno from sc
	group by cno
	having count(cno)=(/*学生人数*/
		select count(*) from s))

方法二：

select cno,cname from c
where not exists(
	select * from s
	where not exists(
		select * from sc

8./检索选修课程包含张xx所授全部课程的学生学号/

张老师一共教了两门课，这两门课都选的学生

方法一：

select sno from sc
where cno in(/*张老师教的课*/
	select cno from c
	where tno in(/*张老师的老师号*/
		select tno from t 
		where tname ="张xx"))
group by sno
having count(sno)=(/*张老师教几门课*/
	select count(*) from c
	where tno in(
		select tno from t
		where tname="张xx"))

方法二：

SELECT DISTINCT sno from sc as x
where not EXISTS(
SELECT * from c,t 
where c.tno=t.tno and tname="张xx" and not EXISTS
(SELECT * FROM sc as y where y.sno=x.sno and y.cno=c.cno))

2.检索（简单）

1./* 统计有学生选修的课程门数。*/

select count(DISTINCT cno) from sc

2./求选修C4课程的女学生的平均年龄。/

方法一：

select avg(age) from s
where sex="f" and sno in(
	select sno from sc
	where cno="c4" )

方法二：

select avg(age) from s,sc
where s.sno=sc.sno and cno="c4" and sex="f"

3./求张老师所授课程的每门课程的平均成绩。/

select c.cno,avg(score) from t,c,s,sc
where c.cno=sc.cno and s.sno=sc.sno and t.tno=c.tno and t.tname="张xx"
group by c.cno

4./统计每门课程的学生选修人数（超过2人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同,按课程号升序排列。/

select cno,count(cno) from sc
group by cno
having count(cno)>2
order by 2 desc,cno

5./检索学号比王同学大，而年龄比他小的学生姓名。/

select sname from s
where age<(
	select age from s
	where sname="王") 
	and sno >(
	select sno from s
	where sname="王")

6./在表SC中检索成绩为空值的学生学号和课程号。/

select sno,cno from sc
where score is null

7./检索姓名以L打头的所有学生的姓名和年龄。/

select sname,age from s
where sname like "L%"

8./求年龄大于女同学平均年龄的男学生姓名和年龄。/

select sname,age from s
where sex="m" and age>(
	select avg(age) from s
	where sex="f")

9./求年龄大于所有女同学年龄的男学生姓名和年龄。/

select sname,age from s
where sex="m" and age>all(
	select age from s
	where sex="f")

3.增删改查

1./向关系C中插一个课程元组(C8,’vC++,T6′)。/

insert into c values('c8','VC++','t6')

2./检索所授每门课程的平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY (TNAME）中。/

insert into FACULTY(tname)
select DISTINCT t.tname from c,sc,t
where c.cno=sc.cno and c.tno=t.tno and c.cno in(
	select cno from sc
	where score>80)

3./在SC中删除尚无成绩的选课元组。/

delete from sc 
where score is null

4./把选修张老师课程的女同学的选课元组全部删去。/

SELECT * from c,s,sc,t??
where c.cno=sc.cno and s.sno=sc.sno and c.tno=t.tno
and t.tname="张xx" and s.sex="f"

5./把html课不及格的成绩全改为60 分。/

update sc
set score =60
where cno in(
	select cno from c
	where cname like "html%") and score<60

6./把低于所有课程总平均成绩的女同学成绩提高5%。/

update sc
set score =score*1.05
where score<(
	select avg(score) from sc) 
and sno in(
	select sno from s
	where sex="f")

7./在表SC中修改C4课程的成绩，当成绩小于等于70分时提高5%，当成绩大于70分时提高4%。(用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现>。/

第一种方法：
/顺序不可颠倒/

update sc
set score = score*1.04
where cno="c4" and score >70
update sc
set score = score*1.05
where cno="c4" and score <=70

第二种方法：

update sc
set score =score*case
	when score>70 then 1.04
	else 1.05 
	end
where cno="c4"

8./在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。/

update sc
set score=score*1.05
where score<(
	select avg(score) from sc)

c（cno,cname,tno) 课程表

INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c1', 'java课', 't1');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c2', 'c语言课', 't2');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c3', 'android课', 't3');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c4', 'html课', 't4');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c5', '编译原理', 't5');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c6', 'python', 't1');
INSERT INTO `c`(`cno`, `cname`, `tno`) VALUES ('c8', 'VC++', 't6');

s (sno,sname,age,sex) 学生表

INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s1', '潘', 15, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s2', '赵', 13, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s3', '陈 ', 12, 'm');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s4', '王', 20, 'f');
INSERT INTO `s`(`sno`, `sname`, `age`, `sex`) VALUES ('s5', '朱', 3, 'm');

sc (sno,cno,score) 学生-课程表

INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s1', 'c1', '101.85000000000001');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s1', 'c6', '94.5');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c1', '46.2');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c2', '102.9');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s2', 'c3', '105');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s3', 'c1', '45');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s3', 'c2', '32');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c1', '45.15');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c2', '45.15');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s4', 'c4', '69.45750000000001');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c1', '56');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c3', '43');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES ('s5', 'c6', '90');

t (tno,tname,title) 教师表

INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t1', '张xx', '高级教师');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t2', '王xx', '教授');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t3', '吴x', '教授');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t4', '方xx', '老师');
INSERT INTO `t`(`tno`, `tname`, `title`) VALUES ('t5', '周xx', '老师');