2018-2-1 有关于数据库表增删改查的操作实例

这里建立了4张表，student、course、score、teacher，现在往表中添加数据，（也就是增）：

student：

insert into student(sno,sname,ssex,sbirthday,class) values
("108","曾华","男","1977-09-01","95033"),
("105","匡明","男","1975-10-02","95031"),
("107","王丽","女","1976-01-23","95033"),
("101","李军","男","1976-02-20","95033"),
("109","王芳","女","1975-02-10","95031"),
("103","陆君","男","1974-06-03","95031");

course：

insert into course values　　　　　　　　　　
("3-105","计算机导论","825"),
("3-245","操作系统","804"),
("6-166","数字电路","856"),
("9-888","高等数学","831");

score：

insert into score values
("103","3-245","86"),
("105","3-245","75"),
("109","3-245","68"),
("103","3-105","92"),
("105","3-105","88"),
("109","3-105","76"),
("101","3-105","64"),
("107","3-105","91"),
("108","3-105","78"),
("101","6-166","85"),
("107","6-166","79"),
("108","6-166","81");

teacher：

("804","李诚","男","1958-12-02","副教授","计算机系"),　　　　　　　　　　
("856","张旭","男","1969-03-12","讲师","电子工程系"),
("825","王萍","女","1972-05-05","助教","计算机系 "),
("831","刘冰","女","1977-08-14","助教","电子工程系");　　　
　　　　
　

 修改数据（改）：

update student set ssex="女" where sname="李军";

查看表（查）：

select * from student;
select * from course;
select * from score;
select * from teacher;

删除数据（删，以行为单位）：

delete from student where sno in(101,103);

查看数据的实例：

#1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
#2、 查询教师所有的单位即不重复的Depart列。
 select distinct depart from teacher; 
#3、 查询Student表的所有记录。
select * from student;
#4、 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree  between 60 and 80;
#5、 查询Score表中成绩为85，86或88的记录。
select * from score where degree in(85,86,88);
#6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class = "95031" or ssex = "女";
 #以Class降序查询Student表的所有记录。
select * from student order by class desc;
# 以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno,degree desc;
 #查询“95031”班的学生人数。
select count(*) from student group by class having class="95033";
#10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select max(degree) from score;
select sno,cno from score where degree=(select max(degree) from score);
# 查询每门课的平均成绩。
select cno,avg(degree) from score group by cno;
#12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score group by cno having cno like"3%" and count(*)>=5 ;
#查询分数大于70，小于90的Sno列。
select sno from score where degree between 70 and 90;
#查询所有学生的Sname、Cno和Degree列。
select student.sname,score.cno,score.degree from student
join score on student.sno=score.sno;
#15、查询所有学生的Sno、Cname和Degree列。
select student.sno,course.cname,score.degree from student
join score on score.sno=student.sno
join course on course.cno=score.cno;
#16、查询所有学生的Sname、Cname和Degree列。
select a.sname,b.cname,c.degree
from student as a,course as b,score as c
where a.sno = c.sno and b.cno = c.cno;
select sname,cname,degree from student join score on student.sno=score.sno  join course on course.cno=score.cno;
#17、 查询“95033”班学生的平均分。
select sno from student where class="95033";
select avg(degree) from score where sno in(select sno from student where class="95033");
#1.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
#20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
#选修多门课的学生
select sno,count(*) from score group by sno;
#找到最高分
select max(degree) from score
select * from score where sno in(select sno from score group by sno)and degree<(select max(degree) from score);
#21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select degree from score where sno="109" and cno="3-105";
select degree from score where degree>(select degree from score where sno="109" and cno="3-105");
#22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select left(sbirthday,4) as date from student;
select sno,sname,sbirthday from student where sbirthday like concat((select left(sbirthday,4) as date from student where sno="108"),"","%");
#23、查询“张旭“教师任课的学生成绩。
#1、从成绩表 查询学生成绩  
#2、条件是这个课程是 “张旭“教师任课
#A)从老师表 中查名字叫 张旭 编号
#B)根据老师编号 在课程表中找到对应的 课程
select  depart from teacher where tname="张旭";
select cno from course where depart=(select  depart from teacher where tname="张旭");
#24、查询选修某课程的同学人数多于5人的教师姓名。
select cno from score group by cno having count(*)>"5";
select tno from course where cno in(select cno from score group by cno having count(*)>"5");
select tname from teacher where tno=(select tno from course where cno in(select cno from score group by cno having count(*)>"5"));
#25、查询95033班和95031班全体学生的记录。
select * from student where class in(95033,95031);
#26、  查询存在有85分以上成绩的课程Cno.
select cno from score where degree>"85";
#27、查询出“计算机系“教师所教课程的成绩表。
select tno from teacher where depart="计算机系";
select cno from course where tno in(select tno from teacher where depart="计算机系");
select * from score where cno in(select cno from course where tno in(select tno from teacher where depart="计算机系"));
#29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select max(degree) from score where cno="3-245";
select * from score where cno="3-105" and degree>(select max(degree) from score where cno="3-245") order by degree desc;
#30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select * from score where cno="3-105" and degree>(select max(degree) from score where cno="3-245");
#31、 查询所有教师和同学的name、sex和birthday.
select sname,ssex,sbirthday from student 
union 
select tname,tsex,tbirthday from teacher;
#32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname,ssex,sbirthday from student where ssex="女"
union 
select tname,tsex,tbirthday from teacher where tsex="女";
#33、 查询成绩比该课程平均成绩低的同学的成绩表。
select * from score group by cno having degree<avg(degree);
#34、 查询所有任课教师的Tname和Depart.
select cno from score group by cno;
select tno from course where cno in(select cno from score group by cno);
select tname,depart from teacher where tno in(select tno from course where cno in(select cno from score group by cno));
#35 、 查询所有未讲课的教师的Tname和Depart. 
select tname,depart from teacher where tno not in(select tno from course where cno in(select cno from score group by cno));
#36、查询至少有2名男生的班号。
select class from student group by class having count(ssex="男")>="2";
#37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like"王%";
#38、查询Student表中每个学生的姓名和年龄。
select sname,(2018-left(sbirthday,4)) as age from student;
#39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from student;
#40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by class desc,(2018-left(sbirthday,4))  desc;
#41、查询“男”教师及其所上的课程。
select tno from teacher where tsex="男";
select teacher.tname,course.cname from teacher,course where course.tno in(select tno from teacher where tsex="男") and course.tno= teacher.tno;
#42、查询最高分同学的Sno、Cno和Degree列。
select max(degree) from score  ;
select * from score where degree=(select max(degree) from score);
#43、查询和“李军”同性别的所有同学的Sname.
select ssex from student where sname="李军";
select sname from student where ssex=(select ssex from student where sname="李军");
#44、查询和“李军”同性别并同班的同学Sname.
select class from student where sname="李军";
select sname from student where ssex=(select ssex from student where sname="李军") and class=(select class from student where sname="李军");
#45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select cno from course where cname="计算机导论";
select sno from student where ssex="男";
select * from score where sno in(select sno from student where ssex="男") and cno in(select cno from course where cname="计算机导论");

2018-2-1 有关于数据库表增删改查的操作实例

标签：order by   姓名   img   exist   class   数据类型   concat   gpo   编号