SQL语句创建库表及查询练习

一、新建数据库，名为practice

CREATE DATABASE if NOT EXISTS practice;

二、新建四张数据表

表1 students

USE practice;CREATE TABLE IF NOT EXISTS students(
学号CHAR(7) NOT NULL PRIMARY KEY,
姓名VARCHAR(20),
班级IDCHAR(7),
性别CHAR(2) DEFAULT '女',
语文INT,
数学INT,
英语INT,
物理 INT);

表2 classes

CREATE TABLE IF NOT EXISTS classes(
班级IDchar(7) not null PRIMARY KEY,
班级名称 VARCHAR(7)
);

表3 teachers

CREATE TABLE teachers(
教师IDCHAR(5) PRIMARY KEY,
姓名VARCHAR(20),
任教学科 VARCHAR(20)
);

表4 teandclass 关联表，关联teachers和classes

CREATE TABLE teandclass(
教师IDCHAR(5),
班级ID char(7)
);

三 分别给4张表添加数据

表1 添加数据

INSERT INTO studentsVALUES('lshh001','宋江','class06','男',94,64,68,89),
('lshh002','卢俊义','class06','女',66,75,61,95),
('lshh003','吴用','class06','男',81,81,97,91),
('lshh004','公孙胜','class03','女',65,50,90,77),
('lshh005','关胜','class05','男',66,70,86,99),
('lshh006','林冲','class05','男',66,100,69,63),
('lshh007','秦明','class06','男',77,53,95,94),
('lshh008','呼延灼','class01','男',64,57,84,63),
('lshh009','花荣','class01','男',53,67,99,80),
('lshh010','柴进','class02','男',88,69,62,72),
('lshh011','李应','class03','男',99,65,81,74),
('lshh012','朱仝','class01','男',50,75,61,51),
('lshh013','鲁智深','class06','女',96,87,66,79),
('lshh014','武松','class02','男',66,56,85,77),
('lshh015','董平','class01','男',61,97,92,89),
('lshh016','张清','class01','男',80,60,72,90),
('lshh017','杨志','class06','男',77,90,71,75),
('lshh018','徐宁','class05','男',68,79,81,74),
('lshh019','索超','class04','男',65,72,54,59),
('lshh020','戴宗','class02','女',79,74,87,78),
('lshh021','刘唐','class01','女',91,100,71,52),
('lshh022','李逵','class02','男',55,65,56,96),
('lshh023','史进','class04','女',78,96,77,71),
('lshh024','穆弘','class05','女',89,64,59,66),
('lshh025','雷横','class01','男',74,100,62,99),
('lshh026','李俊','class01','女',95,82,78,69),
('lshh027','阮小二','class02','男',85,57,100,63),
('lshh028','张横','class06','男',51,69,89,53),
('lshh029','阮小五','class02','女',84,96,59,80),
('lshh030','张顺','class01','女',53,98,57,91),
('lshh031','阮小七','class04','男',59,86,80,95),
('lshh032','杨雄','class04','女',64,81,74,88),
('lshh033','石秀','class03','男',92,76,74,60),
('lshh034','解珍','class06','男',94,95,73,60),
('lshh035','解宝','class02','女',52,50,72,82),
('lshh036','燕青','class01','男',97,52,54,99),
('lshh037','朱武','class01','男',55,74,99,50),
('lshh038','黄信','class04','女',60,66,64,84),
('lshh039','孙立','class02','女',81,94,57,100),
('lshh040','宣赞','class03','男',78,52,91,100),
('lshh041','郝思文','class01','女',95,95,62,54),
('lshh042','韩滔','class05','男',100,77,70,76),
('lshh043','彭玘','class05','女',84,82,63,85),
('lshh044','单廷圭','class02','男',61,99,79,71),
('lshh045','魏定国','class03','女',96,75,99,55),
('lshh046','萧让','class02','男',70,71,52,73),
('lshh047','裴宣','class06','女',50,75,59,80),
('lshh048','欧鹏','class06','男',82,52,83,96),
('lshh049','邓飞','class01','女',77,81,79,72),
('lshh050','燕顺','class02','男',70,72,63,56),
('lshh051','杨林','class03','男',86,74,68,86),
('lshh052','凌振','class06','男',72,92,77,62),
('lshh053','蒋敬','class02','女',90,92,82,74),
('lshh054','吕方','class02','女',55,73,59,59),
('lshh055','郭盛','class06','女',98,76,57,91),
('lshh056','安道全','class03','女',63,55,68,70),
('lshh057','皇甫端','class03','女',98,56,71,86),
('lshh058','王英','class05','男',99,58,59,61),
('lshh059','扈三娘','class06','男',75,51,59,81),
('lshh060','鲍旭','class06','男',70,93,50,51),
('lshh061','樊瑞','class02','女',78,84,94,91),
('lshh062','孔明','class04','女',98,65,57,51),
('lshh063','孔亮','class01','女',59,79,67,77),
('lshh064','项充','class03','男',97,77,94,70),
('lshh065','李衮','class04','男',68,81,60,73),
('lshh066','金大坚','class03','女',77,84,62,57),
('lshh067','马麟','class04','女',65,90,88,62),
('lshh068','童威','class04','男',61,64,52,56),
('lshh069','童猛','class04','男',62,94,93,74),
('lshh070','孟康','class05','男',62,87,85,69),
('lshh071','侯健','class03','男',90,51,69,96),
('lshh072','陈达','class05','女',87,51,65,63),
('lshh073','杨春','class05','男',62,86,73,70),
('lshh074','郑天寿','class06','女',63,54,81,59),
('lshh075','陶宗旺','class02','女',65,68,61,60),
('lshh076','宋清','class06','女',83,85,93,83),
('lshh077','乐和','class02','男',69,67,61,55),
('lshh078','龚旺','class02','女',85,73,67,72),
('lshh079','丁得孙','class06','男',62,82,76,52),
('lshh080','穆春','class04','女',81,62,79,84),
('lshh081','曹正','class01','女',72,78,100,76),
('lshh082','宋万','class05','男',57,55,95,81),
('lshh083','杜迁','class03','男',65,60,86,58),
('lshh084','薛永','class05','男',54,66,88,80),
('lshh085','李忠','class01','男',82,85,75,54),
('lshh086','周通','class02','男',62,93,100,94),
('lshh087','汤隆','class06','女',73,50,93,68),
('lshh088','杜兴','class06','女',83,82,78,96),
('lshh089','邹渊','class06','女',85,59,64,55),
('lshh090','邹润','class06','女',64,54,86,65),
('lshh091','朱贵','class03','男',58,90,90,69),
('lshh092','朱富','class03','女',64,91,74,78),
('lshh093','施恩','class01','女',59,65,99,80),
('lshh094','蔡福','class05','女',52,71,51,61),
('lshh095','蔡庆','class04','女',69,55,59,62),
('lshh096','李立','class06','女',51,67,84,87),
('lshh097','李云','class06','男',70,95,51,51),
('lshh098','焦挺','class06','女',51,52,66,57),
('lshh099','石勇','class03','女',74,58,89,56),
('lshh100','孙新','class05','男',55,94,55,100),
('lshh101','顾大嫂','class05','女',75,52,71,74),
('lshh102','张青','class01','男',86,72,53,94),
('lshh103','孙二娘','class06','男',55,73,52,54),
('lshh104','王定六','class02','女',50,62,82,84),
('lshh105','郁保四','class06','男',57,62,88,82),
('lshh106','白胜','class05','男',63,51,57,63),
('lshh107','时迁','class01','女',88,79,78,67),
('lshh108','段景住','class03','女',70,73,53,79);

表2 添加数据

INSERT into classesVALUES('class01','高三1班'),
('class02','高三2班'),
('class03','高三3班'),
('class04','高三4班'),
('class05','高三5班'),
('class06','高三6班');

表3 添加数据

INSERT into teachersVALUES('tea01','张三','语文'),
('tea02','李四','语文'),
('tea03','王五','数学'),
('tea04','赵六','数学'),
('tea05','横七','英语'),
('tea06','竖八','英语'),
('tea07','同九','物理'),
('tea08','易拾','物理');

表4 插入数据

INSERT into teandclassVALUES('tea01','class01'),
('tea01','class03'),
('tea01','class05'),
('tea02','class02'),
('tea02','class04'),
('tea02','class06'),
('tea03','class01'),
('tea03','class03'),
('tea03','class05'),
('tea04','class02'),
('tea04','class04'),
('tea04','class06'),
('tea05','class01'),
('tea05','class03'),
('tea05','class05'),
('tea06','class02'),
('tea06','class04'),
('tea06','class06'),
('tea07','class01'),
('tea07','class03'),
('tea07','class05'),
('tea08','class02'),
('tea08','class04'),
('tea08','class06');

四 查询

查询1 查询名为“横七”的老师任教的班级名称

SELECT t.`姓名`,c.`班级名称`  FROM teachers t  
INNER JOIN teandclass tc ON t.`教师ID` = tc.`教师ID`  INNER JOIN classes c ON tc.`班级ID` = c.`班级ID`  WHERE t.`姓名` = '横七';

查询结果

查询2 查询名为“横七”的老师任教的学科

SELECT 姓名,任教学科FROM teachersWHERE 姓名='横七';

查询结果

查询3 查询名为“横七”的老师任教的班级的科目的学生的学生成绩

SELECT   
    s.`姓名`,  
    s.`学号`,  
    s.`性别`,  
    s.`班级ID`,  
    CASE   
        WHEN t.`任教学科` = '英语' THEN s.`英语`  
        WHEN t.`任教学科` = '语文' THEN s.`语文`  
        WHEN t.`任教学科` = '数学' THEN s.`数学`  
        WHEN t.`任教学科` = '物理' THEN s.`物理`  
        ELSE NULL 
    END AS `成绩`  FROM   
    students s    
INNER JOIN   
    teandclass tc ON s.`班级ID` = tc.`班级ID`   INNER JOIN   
    teachers t ON tc.`教师ID` = t.`教师ID`    WHERE   
    t.`姓名` = '横七'  ORDER BY   
    s.`班级ID`,s.`学号`;

查询结果

共51条记录

查询4 查询名为“横七”的老师任教学科的班级的平均成绩

SELECT   
 t.`姓名`,c.`班级名称`,AVG(CASE     
        WHEN t.`任教学科` = '英语' THEN s.`英语`    
        WHEN t.`任教学科` = '语文' THEN s.`语文`    
        WHEN t.`任教学科` = '数学' THEN s.`数学`    
        WHEN t.`任教学科` = '物理' THEN s.`物理`    
        ELSE NULL   
     END) AS `平均成绩`FROM   
    students s    
INNER JOIN   
    teandclass tc ON s.`班级ID` = tc.`班级ID`   INNER JOIN   
    teachers t ON tc.`教师ID` = t.`教师ID`INNER JOIN   
    classes c ON c.`班级ID` = s.`班级ID` 				WHERE   
    t.`姓名` = '横七' GROUP BY s.`班级ID` ORDER BY   
    s.`班级ID`;

查询结果

查询5 查询“教师ID”为“tea03”的老师任教学科的班级的平均成绩

SELECT   
  t.`教师ID`,t.`姓名`,c.`班级名称`,AVG(CASE     
        WHEN t.`任教学科` = '英语' THEN s.`英语`    
        WHEN t.`任教学科` = '语文' THEN s.`语文`    
        WHEN t.`任教学科` = '数学' THEN s.`数学`    
        WHEN t.`任教学科` = '物理' THEN s.`物理`    
        ELSE NULL   
     END) AS `平均成绩`FROM   
    students s    
INNER JOIN   
    teandclass tc ON s.`班级ID` = tc.`班级ID`   INNER JOIN   
    teachers t ON tc.`教师ID` = t.`教师ID`INNER JOIN   
    classes c ON c.`班级ID` = s.`班级ID` 				WHERE   
    t.`教师ID` = 'tea03' GROUP BY s.`班级ID` ORDER BY   
    s.`班级ID`;

查询结果

查询6 查询“教师ID”为“tea01”的老师任教学科的班级的最高分

SELECT   
  t.`教师ID`,t.`姓名`,c.`班级名称`,MAX(CASE     
        WHEN t.`任教学科` = '英语' THEN s.`英语`    
        WHEN t.`任教学科` = '语文' THEN s.`语文`    
        WHEN t.`任教学科` = '数学' THEN s.`数学`    
        WHEN t.`任教学科` = '物理' THEN s.`物理`    
        ELSE NULL   
     END) AS `最高分`FROM   
    students s    
INNER JOIN   
    teandclass tc ON s.`班级ID` = tc.`班级ID`   INNER JOIN   
    teachers t ON tc.`教师ID` = t.`教师ID`INNER JOIN   
    classes c ON c.`班级ID` = s.`班级ID` 				WHERE   
    t.`教师ID` = 'tea01' GROUP BY s.`班级ID` ORDER BY   
    s.`班级ID`;

查询结果

查询7 查询各班男女同学各科成绩的平均分，并且按照班级（默认升序）、性别（降序）排序

SELECT 班级ID,性别,AVG(语文)AS 语文平均分,AVG(数学)AS 数学平均分,AVG(英语)AS 英语平均分,AVG(物理)AS 物理平均分FROM studentsGROUP BY 班级ID,性别ORDER BY 班级ID,性别 DESC;

查询结果