语句
DROP TABLE
IF
EXISTS `score`;
DROP TABLE
IF
EXISTS `student`;
DROP TABLE
IF
EXISTS `class`;
DROP TABLE
IF
EXISTS `course`;
CREATE TABLE class (
`cid` INT ( 10 ) UNSIGNED auto_increment COMMENT '班级id',
`caption` VARCHAR ( 4 ) NOT NULL COMMENT '班级名称',
PRIMARY KEY ( `cid` ) USING BTREE
) ENGINE = INNODB DEFAULT charset = utf8;
DROP TABLE
IF
EXISTS `student`;
CREATE TABLE student (
`sid` INT ( 10 ) UNSIGNED auto_increment PRIMARY KEY COMMENT '学生id',
`sname` VARCHAR ( 255 ) NOT NULL COMMENT '学生姓名',
`gender` VARCHAR ( 255 ) NOT NULL COMMENT '性别',
`age` TINYINT ( 2 ) NOT NULL COMMENT '年龄',
class_id INT UNSIGNED NOT NULL,
CONSTRAINT fk_student_class FOREIGN KEY ( `class_id` ) REFERENCES class ( `cid` )
) ENGINE = INNODB DEFAULT charset = utf8;
DROP TABLE
IF
EXISTS `teacher`;
CREATE TABLE teacher ( `tid` INT ( 10 ) UNSIGNED auto_increment PRIMARY KEY COMMENT '老师id', `tname` VARCHAR ( 255 ) NOT NULL COMMENT '老师姓名' ) ENGINE = INNODB DEFAULT charset = utf8;
DROP TABLE
IF
EXISTS `course`;
CREATE TABLE course (
`cid` INT ( 10 ) UNSIGNED auto_increment PRIMARY KEY COMMENT '学生id',
`cname` VARCHAR ( 255 ) NOT NULL COMMENT '学生姓名',
teacher_id INT UNSIGNED NOT NULL,
CONSTRAINT fk_course_teacher FOREIGN KEY ( `teacher_id` ) REFERENCES course ( `cid` )
) ENGINE = INNODB DEFAULT charset = utf8;
DROP TABLE
IF
EXISTS `score`;
CREATE TABLE score (
sid INT auto_increment PRIMARY KEY,
student_id INT UNSIGNED NOT NULL,
course_id INT UNSIGNED NOT NULL,
number TINYINT ( 2 ) UNSIGNED NOT NULL,
UNIQUE uq_user_host ( `student_id`, `course_id` ),# 联合唯一索引
CONSTRAINT fk_student_score FOREIGN KEY ( `student_id` ) REFERENCES student ( `sid` ),
CONSTRAINT fk_sourse__score FOREIGN KEY ( `course_id` ) REFERENCES course ( `cid` )
) ENGINE = INNODB DEFAULT charset = utf8;
INSERT INTO class ( caption )
VALUES
( "一班" ),
( "二班" ),
( "三班" ),
( "四班" ),
( "五班" ),
( "六班" ),
( "七班" ),
( "八班" );
INSERT INTO student ( sname, gender, age, class_id )
VALUES
( "小明", '男', 40, 1 ),
( "小黑", '女', 30, 2 ),
( "小王", '男', 30, 2 ),
( "大狗", '男', 20, 1 ),
( "大黄", '女', 25, 3 ),
( "标签", '男', 20, 3 ),
( "学渣", '女', 26, 1 ),
( "嘿嘿", '女', 70, 1 ),
( "哈哈", '男', 45, 4 ),
( "么么", '女', 29, 5 ),
( "刘德华", '男', 60, 6 );
INSERT INTO teacher ( tname )
VALUES
( "钢铁侠" ),
( "美国队长" ),
( "雷神" ),
( "奇异博士" ),
( "绯红女巫" ),
( "幻世" ),
( "黑豹" ),
( "黑寡妇" ),
( "鹰眼" );
INSERT INTO course ( cname, teacher_id )
VALUES
( "python", 1 ),
( "Linux", 2 ),
( "Zabbix", 1 ),
( "MYSQL", 3 ),
( "Nginx", 1 ),
( "Php", 3 ),
( "JAVA", 4 ),
( "haproxy", 4 );
INSERT INTO score ( student_id, course_id, number )
VALUES
( 1, 2, 20 ),
( 1, 3, 43 ),
( 1, 4, 92 ),
( 1, 1, 45 ),
( 1, 5, 45 ),
( 2, 1, 20 ),
( 2, 4, 90 ),
( 2, 3, 79 ),
( 3, 3, 66 ),
( 3, 1, 70 ),
( 3, 2, 60 ),
( 4, 2, 99 ),
( 4, 3, 70 ),
( 4, 4, 92 ),
( 4, 1, 45 ),
( 5, 2, 60 ),
( 5, 3, 43 ),
( 5, 4, 20 ),
( 5, 1, 45 ),
( 5, 5, 95 );
-- 所有成绩大约60的
-- SELECT * from score where number > 60;
-- 查询每位老师和所教的课程数量
-- select teacher_id,count(cid) from course GROUP BY teacher_id;
-- 查询每位老师教的课程列表
-- select * from course
-- left join teacher on course.teacher_id=teacher.tid
-- 查询每个学生的信息和班级
-- select * from student
-- left join class ON student.class_id=class.cid
-- 查询学生男女总数
-- select gender,count(sid) from student GROUP BY gender;
-- 临时表 查询平均成绩大于60分的学生学号和平均成绩
-- select student_id,avg(number) as 平均成绩 from score GROUP BY student_id having avg(number) >60
-- 查询平均成绩大于60分的学生学号和姓名和平均成绩
-- SELECT
-- B.student_id,
-- student.sname,
-- B.平均成绩
-- FROM
-- ( SELECT student_id, avg( number ) AS 平均成绩 FROM score GROUP BY student_id HAVING avg( number ) > 60 ) AS B
-- LEFT JOIN student ON B.student_id = student.sid
-- 查询所有同学的学号，姓名，选课数，总成绩
-- select student.sid,student.sname,count(student_id),sum(number) from score left JOIN student on score.student_id=student.sid GROUP BY student.sid
-- 查询姓小的学生的个数
-- SELECT COUNT(1) from student WHERE sname LIKE "小%"
-- 雷神所教的课程id
-- SELECT course.cid FROM course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname= "雷神"
-- 查询没学过 雷神 的同学的学号和姓名
-- SELECT student.sid,student.sname FROM student WHERE student.sid not in(
-- SELECT student_id FROM score WHERE course_id in(
-- SELECT course.cid
-- FROM course
-- LEFT JOIN teacher ON
-- course.teacher_id=teacher.tid
-- WHERE teacher.tname= "雷神"
-- )
-- GROUP BY student_id
-- )
# 查询Linux成绩比Zabbix成绩高的学生号
-- SELECT
-- A.student_id
-- FROM
-- ( SELECT * FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = "Linux" ) AS A
-- INNER JOIN
-- ( SELECT * FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = "Zabbix" ) AS B ON A.student_id = B.student_id
-- WHERE
-- A.number > B.number
-- 查询学过id为1和2课程的同学学号和姓名
-- SELECT score.student_id,student.sname from score
-- LEFT JOIN student on score.student_id=student.sid
-- WHERE course_id=1 or course_id=2
-- GROUP BY student_id HAVING COUNT(course_id) >1
-- 查询学过钢铁侠老师的所有课程的同学学号和姓名
-- SELECT score.student_id,student.sname
-- FROM
-- score
-- LEFT JOIN student on score.student_id=student.sid
-- WHERE
-- course_id IN ( SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "钢铁侠" )
-- GROUP BY
-- student_id
-- HAVING
-- COUNT( course_id ) = ( SELECT COUNT( cid ) FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "钢铁侠" )
-- 查询有课程小于60分的同学的学号和姓名 # DISTINCT去重(效率不高/尽量不用)
-- SELECT student_id FROM score WHERE number < 60 GROUP BY student_id
-- SELECT sid,sname FROM student WHERE sid in(
-- SELECT DISTINCT student_id from score
-- WHERE number < 60)
-- 查询没学全所有课程的同学
-- SELECT
-- student.sname,
-- student_id,
-- COUNT( 1 )
-- FROM
-- score
-- LEFT JOIN student ON score.student_id = student.sid
-- GROUP BY
-- student_id
-- HAVING
-- COUNT( 1 ) < ( SELECT COUNT( cid ) FROM course )
--
