mysql小题

建立数据库及表的数据：

CREATE table if not exists student(
    sid int primary key,
    stu_name varchar(20) not null,
    stu_sex enum('男','女') DEFAULT '男',
    stu_age varchar(10) not null,
    createtime datetime
)ENGINE=INNODB;

CREATE table if not exists class(
    id int primary key,
    cls_name varchar(50) not null
)ENGINE=INNODB;

CREATE table if not exists course(
    cid int primary key,
    cname varchar(30) not null,
    clevel int not null,
    tid int
)ENGINE=INNODB;

-- ALTER table course add CONSTRAINT fk_tid FOREIGN key(tid) REFERENCES teacher(tid) on UPDATE CASCADE on DELETE RESTRICT;

CREATE table if not exists score(
    score int primary key,
    id int not null,
    cid int not null,
    sid int not null
)ENGINE=INNODB;

-- ALTER table score add CONSTRAINT fk_id FOREIGN key(id) REFERENCES class(id) on UPDATE CASCADE on DELETE RESTRICT;
-- ALTER table score add CONSTRAINT fk_cid FOREIGN key(cid) REFERENCES course(cid) on UPDATE CASCADE on DELETE RESTRICT;
-- ALTER table score add CONSTRAINT fk_sid FOREIGN key(sid) REFERENCES student(sid) on UPDATE CASCADE on DELETE RESTRICT;

CREATE table if not exists teacher(
    tid int primary key,
    tname varchar(10) not null,
    tlevel varchar(20) not null
)ENGINE=INNODB;

insert into student(sid,stu_name ,stu_sex ,stu_age) value
(01,'张六','男',17),
(02,'赵二','女',16),
(07,'李四','男',23),
(05,'王三','女',19);

INSERT into teacher(tid,tname,tlevel) VALUE
(03,'张老师','中级'),
(07,'吴老师','高级'),
(09,'陈老师','教授'),
(04,'李老师','院士');

INSERT into course(cid,cname,clevel) VALUE
(09,'高数',1),
(07,'马列',2),
(01,'编程',2),
(02,'艺术',1);

INSERT into class(id,cls_name) VALUE
(07,'大一'),
(09,'大二'),
(02,'大三'),
(04,'大四');

查询练习：

-- 1、学数学的学生
SELECT s_id,s_name from student where s_id IN
( SELECT s_id from score where c_id IN(SELECT c_id from course where c_name='数学'));

-- 2、选修课程的学生
SELECT s_id,s_name from student where s_id NOT IN
( SELECT s_id from score where c_id IN(SELECT c_id from course));

-- 3、学语文的学生成绩
SELECT s_id,s_score from score where c_id IN(SELECT c_id from course where c_name='语文') ORDER BY s_score desc,s_id ASC;

-- 4、查询及格学生学号---
SELECT DISTINCT s_id from score where c_id IN(SELECT c_id from course) and s_score>=60;

-- 5、查询语文前三名学号
SELECT s_id FROM score where c_id=(SELECT c_id from course where c_name='语文') ORDER BY s_score desc LIMIT 3;

-- 6、查询课程平均分
SELECT c_id,avg(s_score) from score where c_id IN(SELECT c_id from course)  GROUP BY c_id;

-- 7、查询课程总数
SELECT c_id,count(c_id) from course;

-- 8、查询课程号
SELECT DISTINCT c_id,count(c_id) from course;

-- 9、查询选修两门及以上的学生学号--
SELECT s_id,count(*) from score  GROUP BY s_id having count(*)>=2;

-- 10、查询学英语的学生人数--
SELECT count(*) from score where c_id =(SELECT c_id from course where c_name='英语');

-- 查询‘李’姓老师数量
SELECT t_name,count(*) from teacher where t_name like '李%' GROUP BY t_name;

-- 查询平均成绩在60分以上的同学 
SELECT student.s_id,student.s_name,ROUND(avg(score.s_score),1) av from score,student where student.s_id=score.s_id group by score.s_id having av>=60;

-- 查询01课程比02课程高的学生编号和分数--
SELECT s_name,s_score from student where s_id in()
 (s_id in(SELECT s_id,s_score from score where c_id=01))=
(s_id in(SELECT s_id,s_score from score where c_id=02)) and 
(s_id in(SELECT s_id from score where c_id=01))>(s_id in(SELECT s_id from score where c_id=02));
-- 查询没有学过张三老师课程的所有学生名字
SELECT st.s_id,st.s_name,te.t_name FROM student st,teacher te,score sc,course co 
where st.s_id=sc.s_id and sc.c_id=co.c_id and co.t_id=te.t_id and te.t_name not like '张三';

最后编辑于：2020.05.28 21:15:30