数据库建表字段

-- CREATE TABLE class (
-- cid INT(25)auto_increment PRIMARY KEY,
-- caption VARCHAR(50) not NULL
-- )CHARSET utf8;

-- CREATE TABLE student (
-- sid INT(25)auto_increment PRIMARY KEY,
-- sname VARCHAR(50)NOT NULL,
-- gender enum('男','女')DEFAULT '男',
-- class_id INT(25) NOT NULL DEFAULT 1,
-- CONSTRAINT fk_class (class_id) REFERENCES class(cid)
-- )CHARSET utf8;

-- CREATE TABLE teacher (
-- tid INT(25)auto_increment PRIMARY KEY,
-- tname VARCHAR(50) not NULL
-- )CHARSET utf8;

-- CREATE TABLE course (
-- cid INT(25)auto_increment PRIMARY KEY,
-- cname VARCHAR(50)NOT NULL,
-- teacher_id INT(25) NOT NULL DEFAULT 1,
-- CONSTRAINT fk_teacher (teacher_id)REFERENCES teacher(tid)
-- )CHARSET utf8;

-- CREATE TABLE score(
-- sid INT(25)auto_increment PRIMARY KEY,
-- student_id INT(25) NOT NULL DEFAULT 1,
-- course_id int (25) NOT NULL DEFAULT 1,
-- number INT(25) NOT NULL DEFAULT 60,
-- CONSTRAINT fk_student (student_id)REFERENCES student(sid),
-- CONSTRAINT fk_course (course_id)REFERENCES course(cid)
-- )CHARSET utf8;

-- INSERT INTO class(caption) VALUES('三年二班');
-- INSERT INTO class(caption) VALUES('一年三班');
-- INSERT INTO class(caption) VALUES('三年一班');

-- INSERT INTO student(sname,gender,class_id) VALUES('钢蛋','女',1);
-- INSERT INTO student(sname,gender,class_id) VALUES('铁锤','女',1);
-- INSERT INTO student(sname,gender,class_id) VALUES('山炮','男',2);

-- insert into teacher (tname) VALUES('波多');
-- insert into teacher (tname) VALUES('苍空');
-- insert into teacher (tname) VALUES('饭岛');

-- insert into course(cname,teacher_id)VALUES('生物',1);
-- insert into course(cname,teacher_id)VALUES('体育',1);
-- insert into course(cname,teacher_id)VALUES('物理',2);
-- insert into course(cname,teacher_id)VALUES('交配',3);

-- insert into score(student_id,course_id,number)VALUES(1,1,60);
-- insert into score(student_id,course_id,number)VALUES(1,2,59);
-- insert into score(student_id,course_id,number)VALUES(2,2,100);

-- 1
SELECT DISTINCT student.sname,student.sid
from student LEFT JOIN score on student.sid=score.student_id
WHERE score.number>60;

-- 2
SELECT teacher.*,COUNT(course.teacher_id) as teachNUM
FROM teacher LEFT JOIN course on teacher.tid=course.teacher_id
GROUP BY teacher.tid;

-- 3
SELECT sid as studentID,sname,gender,cid as classNUM ,caption
FROM class RIGHT JOIN student on class.cid = student.class_id;

-- 4
SELECT gender,COUNT(sid) as genderNUM
FROM student
GROUP BY gender;

-- 5
SELECT student.sid,score.number,student.sname
FROM student LEFT JOIN score ON student.sid=score.student_id LEFT JOIN course ON score.course_id=course.cid
WHERE course.cid=1;

-- 6
SELECT student.sid,score.number as avgSCORE
FROM student LEFT JOIN score ON student.sid=score.student_id
WHERE score.number>60;

-- 7
SELECT count(tid) as num_of_family_name_is_LI
FROM teacher
WHERE teacher.tname like '李%';

-- 8
SELECT student.sid,student.sname
FROM student LEFT JOIN score ON student.sid=score.student_id
WHERE score.number<60;

-- 9
DELETE from score where course.cid in(
SELECT cid from course
FROM teacher LEFT JOIN course on teacher.tid =course.teacher_id
WHERE teacher.tname='叶平');

-- 10
SELECT course_id AS 课程ID,max(score.number) AS 最高分,min(score.number) AS 最低分
FROM score JOIN course on course.cid=score.course_id
GROUP BY course.cid;

-- 11
SELECT course.cname,count(score.student_id) AS 选择数量
FROM score LEFT JOIN course on course.cid=score.course_id
GROUP BY course.cname;

-- 12
SELECT sname
FROM student
WHERE sname like '张%';

-- 13
SELECT course.cname,avg(score.number)
FROM course join score on course.cid=score.course_id
GROUP BY course.cname;

-- 14
SELECT student.sid,sname,score.number
from student LEFT join score on score.student_id=student.sid
WHERE score.number>85;

-- 15
SELECT student.sid,student.sname
FROM student JOIN score on score.student_id=student.sid
WHERE score.course_id=3 and score.number>85;

-- 16
SELECT course.cname,count(student.sid)
FROM student RIGHT JOIN score ON student.sid=score.student_id LEFT JOIN course ON score.course_id=course.cid
GROUP BY cname;

-- 17
SELECT student.sid
FROM student JOIN score on score.student_id = student.sid
where course.cid=4
ORDER BY score.number DESC;

-- 18
DELETE from score WHERE score.student_id in(
SELECT student.sid
FROM student JOIN score on score.student_id = student.sid
WHERE student.sid=2 AND score.sid=1)