MySQL基础知识(练习题)

一、建立数据库：

/*
Navicat MySQL Data Transfer

Source Server         : 192.168.0.91(内网测试机)
Source Server Version : 50717
Source Host           : 192.168.0.91:33060
Source Database       : sqlexam

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2017-11-17 16:11:04
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '三年二班');
INSERT INTO `class` VALUES ('2', '三年三班');
INSERT INTO `class` VALUES ('3', '一年二班');
INSERT INTO `class` VALUES ('4', '二年九班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '生物', '1');
INSERT INTO `course` VALUES ('2', '物理', '2');
INSERT INTO `course` VALUES ('3', '体育', '3');
INSERT INTO `course` VALUES ('4', '美术', '2');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '10');
INSERT INTO `score` VALUES ('2', '1', '2', '9');
INSERT INTO `score` VALUES ('5', '1', '4', '66');
INSERT INTO `score` VALUES ('6', '2', '1', '8');
INSERT INTO `score` VALUES ('8', '2', '3', '68');
INSERT INTO `score` VALUES ('9', '2', '4', '99');
INSERT INTO `score` VALUES ('10', '3', '1', '77');
INSERT INTO `score` VALUES ('11', '3', '2', '66');
INSERT INTO `score` VALUES ('12', '3', '3', '87');
INSERT INTO `score` VALUES ('13', '3', '4', '99');
INSERT INTO `score` VALUES ('14', '4', '1', '79');
INSERT INTO `score` VALUES ('15', '4', '2', '11');
INSERT INTO `score` VALUES ('16', '4', '3', '67');
INSERT INTO `score` VALUES ('17', '4', '4', '100');
INSERT INTO `score` VALUES ('18', '5', '1', '79');
INSERT INTO `score` VALUES ('19', '5', '2', '11');
INSERT INTO `score` VALUES ('20', '5', '3', '67');
INSERT INTO `score` VALUES ('21', '5', '4', '100');
INSERT INTO `score` VALUES ('22', '6', '1', '9');
INSERT INTO `score` VALUES ('23', '6', '2', '100');
INSERT INTO `score` VALUES ('24', '6', '3', '67');
INSERT INTO `score` VALUES ('25', '6', '4', '100');
INSERT INTO `score` VALUES ('26', '7', '1', '9');
INSERT INTO `score` VALUES ('27', '7', '2', '100');
INSERT INTO `score` VALUES ('28', '7', '3', '67');
INSERT INTO `score` VALUES ('29', '7', '4', '88');
INSERT INTO `score` VALUES ('30', '8', '1', '9');
INSERT INTO `score` VALUES ('31', '8', '2', '100');
INSERT INTO `score` VALUES ('32', '8', '3', '67');
INSERT INTO `score` VALUES ('33', '8', '4', '88');
INSERT INTO `score` VALUES ('34', '9', '1', '91');
INSERT INTO `score` VALUES ('35', '9', '2', '88');
INSERT INTO `score` VALUES ('36', '9', '3', '67');
INSERT INTO `score` VALUES ('37', '9', '4', '22');
INSERT INTO `score` VALUES ('38', '10', '1', '90');
INSERT INTO `score` VALUES ('39', '10', '2', '77');
INSERT INTO `score` VALUES ('40', '10', '3', '43');
INSERT INTO `score` VALUES ('41', '10', '4', '87');
INSERT INTO `score` VALUES ('42', '11', '1', '90');
INSERT INTO `score` VALUES ('43', '11', '2', '77');
INSERT INTO `score` VALUES ('44', '11', '3', '43');
INSERT INTO `score` VALUES ('45', '11', '4', '87');
INSERT INTO `score` VALUES ('46', '12', '1', '90');
INSERT INTO `score` VALUES ('47', '12', '2', '77');
INSERT INTO `score` VALUES ('48', '12', '3', '43');
INSERT INTO `score` VALUES ('49', '12', '4', '87');
INSERT INTO `score` VALUES ('52', '13', '3', '87');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '男', '1', '理解');
INSERT INTO `student` VALUES ('2', '女', '1', '钢蛋');
INSERT INTO `student` VALUES ('3', '男', '1', '张三');
INSERT INTO `student` VALUES ('4', '男', '1', '张一');
INSERT INTO `student` VALUES ('5', '女', '1', '张二');
INSERT INTO `student` VALUES ('6', '男', '1', '张四');
INSERT INTO `student` VALUES ('7', '女', '2', '铁锤');
INSERT INTO `student` VALUES ('8', '男', '2', '李三');
INSERT INTO `student` VALUES ('9', '男', '2', '李一');
INSERT INTO `student` VALUES ('10', '女', '2', '李二');
INSERT INTO `student` VALUES ('11', '男', '2', '李四');
INSERT INTO `student` VALUES ('12', '女', '3', '如花');
INSERT INTO `student` VALUES ('13', '男', '3', '刘三');
INSERT INTO `student` VALUES ('14', '男', '3', '刘一');
INSERT INTO `student` VALUES ('15', '女', '3', '刘二');
INSERT INTO `student` VALUES ('16', '男', '3', '刘四');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张磊老师');
INSERT INTO `teacher` VALUES ('2', '李平老师');
INSERT INTO `teacher` VALUES ('3', '刘海燕老师');
INSERT INTO `teacher` VALUES ('4', '朱云海老师');
INSERT INTO `teacher` VALUES ('5', '李杰老师');

 二、 练习

导入上面的数据库，对以下问题做出相应的操作：

数据库：
    导入
    导出
命令：
    mysqldump -uroot -p666 dbname > 123.sql
    mysqldump -uroot -p666 dbname < 123.sql

1、导入数据

mysqldump

2、查看数据表

show tables;
select * from 表名;

3、获取平均数，连表查询，临时表

-- avg获取平均数
    select student_id,avg(num) from score GROUP BY student_id HAVING avg(num) > 60;
-- 连表查询
    select * from socre join on stundent on socre.student_id = stundent.sid;
    
-- 临时表
    select * from (select nid,name,age from tb1 where id > 10) as t;

4、查找姓李的老师以及查找姓李的老师的个数

查找姓李的老师
    select tid,tname from teacher where tname like "李%";
查找姓李的老师的个数
    select COUNT(tid) as c  from teacher where tname like "李%";

5、查询学习课程编号1又学习课程编号2的学生

SELECT B.student_id, COUNT(B.student_id) FROM (SELECT student_id,course_id FROM score WHERE course_id = 1 OR course_id = 2) AS B GROUP BY B.student_id HAVING count(B.student_id) > 1;

6、查询学过李平老师所教的课程

SELECT student_id from score WHERE course_id in (
  SELECT cid from course left JOIN teacher on course.teacher_id = teacher.tid WHERE teacher.tname ="李平老师"
) GROUP BY  student_id

7、查询课程成绩小于60分的学生学号以及姓名

- group by
SELECT student_id,sname from (SELECT student_id from score where num < 60 GROUP BY student_id) as S
LEFT JOIN student on S.student_id = student.sid
            
-  DISTINGCT 去重
SELECT student_id,sname from (SELECT DISTINCT student_id from score WHERE num < 60) as S
LEFT JOIN student on S.student_id = student.sid

8、查询没有选全课程的同学的学号和姓名

SELECT student_id,COUNT(student_id) from score GROUP BY student_id HAVING COUNT(student_id) < (select COUNT(1) as C from course)

9、查询至少有一门课域学号001的同学所学的课程相同的同学的学号和姓名;

SELECT student_id FROM score WHERE student_id != 1 and course_id in (SELECT course_id from score WHERE student_id = 1) GROUP BY student_id

10、查询至少学过学号为001同学所有课程的其他同学的学号和姓名

SELECT student_id,count(student_id) from score where course_id in 
(SELECT course_id from score where student_id=1) GROUP BY student_id
HAVING COUNT(student_id) = (SELECT COUNT(1) from score where student_id=1);

11、查询与学号002所学课程相同的同学

SELECT student_id from score GROUP BY student_id HAVING COUNT(student_id) =
  (SELECT COUNT(1) as a  from score WHERE student_id = 2)

12、删除学习”李平”老师课的score表记录

DELETE  from score WHERE course_id in
  (SELECT cid from course left join teacher on course.teacher_id = teacher.tid WHERE tname="李平老师")

14、按平均成绩从低到高显示所有学生的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

SELECT *FROM (
SELECT student_id,COUNT(course_id),AVG(num) AS a from score
GROUP BY student_id HAVING COUNT(student_id) <= (SELECT COUNT(1) from student)) AS t
ORDER BY a ASC

15、按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分

SELECT *FROM (
SELECT student_id,COUNT(course_id),AVG(num) AS a from score
GROUP BY student_id HAVING COUNT(student_id) <= (SELECT COUNT(1) from student)) AS t

 

发表回复

Your email address will not be published.

名字 *
电子邮件 *
站点