从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件

09 - MYSQL建表 增删改查 练习

发布:蔺要红08-10分类: MYSQL





语句
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 )
-- 	
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站