mysql经典题目学习

创建五个表：create table 系

(系编号  smallint not null ,

系名称   char(12),

primary key (系编号))

create table 课程(

课程编号 char(8)  not null,

课程名称  char(16),

学时     smallint,

系编号   smallint,

primary key (课程编号),

foreign key  (系编号) references 系(系编号))

create table 教工(

教工号 smallint,

姓名 char(8),

性别 char(2),

职称 char(10),

工资 numeric(8,2),

系编号  smallint,

primary key (教工号),

foreign key (系编号)references 系(系编号))

create table 学生(

学号 char(6),

姓名 char(8),

性别 char(2),

出生年月  varchar(20),

系编号  smallint,

primary key (学号),

foreign key (系编号) references 系(系编号))

create table 成绩(

学号 char(6)  not null,

  课程编号 char(8)  not null,

  分数 numeric(4,1),

primary key (学号 ,课程编号),

 foreign key (学号) references 学生(学号),

 foreign key (课程编号) references 课程(课程编号))

 

 

给表插入数据

insert into 系 values(101,'数学')

insert into 系 values(102,'计算机')

insert into 系 values(103,'外语')

insert into 系 values(104,'经济')

insert into 课程 values('c101','数学',68,101)

insert into 课程 values('c102','英语',85,103)

insert into 课程 values('c103','计算机',102,102)

insert into 课程 values('c104','经济学',51,104)

insert into 教工 values('2101','葛小平','女','教授',3420.00,101)

insert into 教工 values('2203','李长江','男','副教授',3190.00,102)

insert into 教工 values('2405','姜立伟','男','副教授',3140.00,104)

insert into 教工 values('2104','张丽丽','女','讲师',2243.00,101)

insert into 教工 values('2302','康立华','女','教授',3740.00,103)

insert into 教工 values('2205','王伟平','男','讲师',2130.00,102)

insert into 学生 values('991022','田平平','女','08/05/1980',101)

insert into 学生 values('992124 ','郭黎明','男','03/04/1981',102)

insert into 学生 values('994021 ','何明慧','女','04/12/1982',104)

insert into 学生 values('991223 ','姜明明','男','12/05/1980',101)

insert into 学生 values('993012 ','何漓江','男','10/05/1979',103)

insert into 学生 values('992104 ','康纪平','女','03/04/1981',102)

insert into 学生 values('994125 ','康嘉家','男','07/05/1980',104)

insert into 学生 values('991134 ','包立琪','女','03/14/1981',101)

insert into 学生 values('994115 ','王海洋','男','04/13/1982',104)

insert into 学生 values('991354 ','王立平','女','12/05/1981',101)

insert into 成绩 values('991022','c101',88)

insert into 成绩 values('991022','c102',67)

insert into 成绩 values('992124','c101',77)

insert into 成绩 values('992124','c102',95)

insert into 成绩 values('992124','c103',45)

insert into 成绩 values('994021','c104',87)

insert into 成绩 values('994021','c102',78)

insert into 成绩 values('994021','c103',67)

insert into 成绩 values('991223','c101',66)

insert into 成绩 values('991223','c102',89)

insert into 成绩 values('993012','c102',93)

insert into 成绩 values('993012','c103',84)

查询操作

（1）列出所有教授的姓名和工资：
SELECT 姓名,工资,职称 FROM 教工
WHERE 职称 = '教授';
（2）列出教授的所有信息。
SELECT * FROM 教工
WHERE 职称 = '教授';
（3）列出教工表中的系编号并消除重复的元组。
SELECT DISTINCT 系编号 FROM 教工
（4）已知学分=学时/17，计算每一门课程的学分数。
SELECT *,学时/17 AS 学分 FROM 课程
（5）显示教授的工资和提高10%的工资额。
SELECT 工资 ,工资*1.10  AS 新工资
FROM 教工 WHERE 职称 ='教授'
（6）显示田平平同学出生100天的日期。

（7）列出教工表中工资在3000元以上的名单。
SELECT 姓名 FROM 教工 WHERE 工资>3000
（8）列出学生表中1980年1月1日之后出生的学生名单。
         SELECT 姓名
         FROM  学生
         WHERE 出生年月>= '1980-1-1'
（9）列出学生表中在1980年1月1日之后出生的男同学名单。
            SELECT 姓名
            FROM  学生
            WHERE 出生年月>= '1980-1-1' AND 性别='男'
（10）列出教工表中教授或副教授中工资低于3200元的名单。
SELECT  姓名 FROM 教工
WHERE 职称 IN('教授','副教授') 
AND 工资<3200
（11）显示男学生的姓名和所在的系名称。
SELECT  姓名 ,系名称
FROM 学生,系
WHERE 学生.`系编号` = 系.`系编号`
AND 性别 = '男'
（12）列出教工中比姜立伟工资低的姓名和工资。
第一种做法
SELECT 姓名,工资 
FROM 教工 
WHERE 工资 < 
    (SELECT 工资 FROM 教工 WHERE 姓名 = '姜立伟')
第二种做法
SELECT e1.姓名,e1.工资 
FROM 教工 AS e1 , 教工 AS e2
WHERE e2.姓名 = '姜立伟'
        AND e1.工资 <e2.工资
（13）查出所有不是教授的教工姓名。
SELECT 姓名 FROM 教工
WHERE 职称 <> '教授'
（14）检索工资在2000元到3000元范围内的职工信息。
SELECT  * FROM 教工 
SELECT * FROM 教工 WHERE 工资 BETWEEN 2000 AND 3000;  
（15）按学号的升序，显示系编号等于101的学生信息：
SELECT * FROM 学生
WHERE 系编号 = '101'
ORDER BY 学号
（16）求教工表中教授工资的平均值。
SELECT AVG(工资) AS 教授平均工资 FROM 教工
WHERE 职称 = '教授'
（17）求所有教工的工资总和。
SELECT SUM(工资) FROM 教工
（18）找出教工中最高工资和最低工资。
SELECT MAX(工资)AS 最高工资,MIN(工资) AS 最低工资
FROM 教工 
（19）显示学生中最早和最晚的出生日期。

（20）查询学生的总人数。
SELECT COUNT(*) FROM 学生   
2、复杂查询
（21）如果要统计成绩表中所有选课的学生人数，即不管一名学生选了几门课程，都只计算一次
SELECT COUNT(*) AS 学生人数 FROM (SELECT DISTINCT 学号 FROM 成绩) AS sc
（22）统计学生表中男生和女生的人数。
SELECT 性别, COUNT(*) FROM 学生 GROUP BY  性别
（23）统计成绩表中，每一门课程的平均成绩。
SELECT 课程编号, AVG(分数) FROM 成绩 GROUP BY 课程编号
（24）查询教工表中每一种职称的最高工资和最低工资。
SELECT 职称,MAX(工资) AS 最高工资,MIN(工资) AS 最低工资
FROM 教工
GROUP BY 职称
（25）统计成绩表中选修人数超过2以上的课程编号和人数。 
SELECT 课程编号,COUNT(学号) AS 人数
FROM 成绩 
GROUP BY 课程编号
HAVING COUNT(学号)>2
（26）列出成绩表中分数在60以上、选课数大于2且平均分超过70的学号、选课数目和平均分。
SELECT 学号,COUNT(课程编号),AVG(分数)
FROM 成绩
WHERE 分数 >60
GROUP BY 学号
HAVING COUNT(课程编号)>2
AND AVG(分数)>70
(27)统计“数学”系学生的人数；
SELECT COUNT(*) FROM 学生
GROUP BY 系编号
HAVING 系编号 = '101'
(28)统计各系学生的人数，结果按升序排列；
SELECT COUNT(*) FROM 学生
GROUP BY 系编号
ORDER BY COUNT(*)  --喜欢你就写asc，不喜欢就不写，反正是默认
(29)按系统计各系学生的平均分数，结果按降序排列；
SELECT 课程编号,AVG(分数) FROM 成绩
GROUP BY 课程编号
ORDER BY AVG(分数) DESC
(30)查询每门课程的课程名；
SELECT 课程名称 FROM 课程
GROUP BY 课程名称
(31)查询无先修课的课程的课程名和学时数；
-- 子查询
SELECT 课程名称,学时
FROM 课程
WHERE `课程编号` NOT IN (
    SELECT 课程编号
    FROM 成绩
)
(32)统计无先修课的课程的学时总数；
SELECT 课程名称,SUM(学时)
FROM 课程
GROUP  BY 课程编号
HAVING `课程编号` NOT IN (
    SELECT 课程编号
    FROM 成绩
)
(33)统计每位学生选修课程的门数、学分及其平均成绩；
SELECT COUNT(*),AVG(分数),SUM(学时/17)
FROM 成绩 ,课程
WHERE 成绩.课程编号 = 课程.课程编号
GROUP BY 学号
(34)统计选修每门课程的学生人数及各门课程的平均成绩；
SELECT 课程编号,COUNT(课程编号),AVG(分数)
FROM 成绩
GROUP BY 课程编号
(35)找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排
列；
-- 多表连接，也可以不这么复杂
SELECT 系.系名称,AVG(分数) 
FROM 成绩,课程,系
WHERE 成绩.`课程编号` = 课程.`课程编号`
AND 课程.系编号 = 系.`系编号`
GROUP BY 系.系编号
ORDER BY AVG(分数)
(36)查询选修了“c101”或“c102”号课程的学生学号和姓名；
SELECT 学号,姓名
FROM 学生,课程
WHERE 学生.`系编号` = 课程.`系编号`
AND 课程编号 IN ('c101','c102')
-- 用or 也是可以的，但是如果很多个的话还是用in吧。
(37)查询选修了“c101”和“c102”号课程的学生学号和姓名；
SELECT s1.学号,s1.姓名
FROM (SELECT 学生.系编号,学号,姓名 FROM 学生,课程 WHERE 学生.`系编号` = 课程.`系编号` AND 课程.课程编号 = 'c101') AS s1,
     (SELECT 学生.系编号,学号,姓名 FROM 学生,课程 WHERE 学生.`系编号` = 课程.`系编号` AND 课程.课程编号 = 'c101') AS s2
WHERE  s1.学号  = s2.学号     
(38)查询选修了课程名为“计算机”且成绩在60分以下的学生的学号、姓名和成绩；
SELECT 学生.学号,学生.姓名,分数
FROM 成绩, 课程,学生
WHERE 成绩.`学号` = 学生.`学号`
AND 成绩.课程编号 = 课程.`课程编号`
AND 课程.`课程名称` = '计算机'
AND 成绩.`分数`<60
(39)查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；
SELECT 学生.`学号` ,学生.姓名,课程名称,课程.课程编号,成绩.`分数`
FROM 学生,成绩,课程
WHERE 成绩.`学号` = 学生.`学号`
AND 成绩.`课程编号` = 课程.课程编号

(40)查询没有选修课程的学生的基本信息；
(41)查询选修了3门及以上课程的学生学号；
SELECT 学生.学号 FROM 学生,(SELECT 学号, COUNT(*) FROM 成绩 GROUP BY 学号 HAVING COUNT(学号)>2)AS s
WHERE 学生.`学号` = s.学号 
(42)查询选修课程成绩至少有一门在80分以上的学生学号；
 SELECT  DISTINCT 学号 FROM 成绩
 WHERE 分数 >80
(43)查询选修课程成绩均在80分以上的学生学号；
SELECT DISTINCT 学号 FROM 成绩
WHERE 学号 NOT IN
(SELECT 学号 FROM 成绩 WHERE 分数<80 GROUP BY 学号)
(44)查询选修课程平均成绩在80分以上的学生学号；
SELECT 学号,AVG(分数) FROM 成绩 GROUP BY 学号 HAVING AVG(分数)>80;

小白作者瞎吹牛:数据库的经典题目，由于表格创建的时候生日使用字符串格式,所以无法做那个日期比较的题目了。上述题目有更好的方法或者其他的方法可以通过评论大家互相讨论一下。