mysql 相关练习题

/*
    自己查询自己  把一张表看成是两张表。 表的设计。


SELECT
    *
FROM
    depart;

SELECT
    d1. NAME '部门',
    d2. NAME '分部门'
FROM
    depart d1
INNER JOIN depart d2 ON d1.id = d2.did;

SELECT
    d1. NAME,
    d2. NAME
FROM
    depart d1
INNER JOIN depart d2 ON d1.did = d2.id;



SELECT s.name,s.age,g.gname ,z.id  from student s INNER JOIN grade g INNER JOIN zhongjian z ON z.tid=z.gid;  
*/
-- 学生表
create table students(
    sno VARCHAR(3) not NULL,
    sname VARCHAR(4) not NULL,
    ssex VARCHAR(2) not NULL,
  sbirthday datetime,
    class VARCHAR(5))

--  
CREATE table courses(
    cno VARCHAR(5) not null,
    cname varchar(10) not null,
    tno VARCHAR(10) not null)

CREATE table scores (
    sno VARCHAR(3) not null,
    cno VARCHAR(5) not null,
    degree NUMERIC(10,1) not null)

-- 老师表
create table teachers(
    tno VARCHAR(3) not null,
    tname VARCHAR(4) not null, tsex VARCHAR(2) not null,
    tbirthday datetime not null,prof varchar(6),
    depart VARCHAR (10) not null)


insert into students (sno,sname,ssex,sbirthday,class)VALUES(108,'曾华','男','1977-09-01',95033);
insert into students (sno,sname,ssex,sbirthday,class)VALUES(105,'匡明','男','1975-10-02',95031);
insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'王丽','女','1976-01-23',95033);
insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'李军','男','1976-01-23',95033);
insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'王芳','女','1975-02-10',95031);
insert into students (sno,sname,ssex,sbirthday,class)VALUES(107,'陆军','男','1974-06-03',95031);


insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825);
insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825);
insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825);
insert into courses(cno ,cname,tno)VALUES('3-105','计算机导论',825);

insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);
insert into scores(sno ,cno,degree)VALUES (103,'3-245',86);

insert into teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES(804,'李晨','男','1958-12-02','副教授','计算机系');


-- 2791693327

select * from students;
select * from courses;
select * from scores;
select * from teachers;

-- 1.查询student表中的所有记录的sname ssex, class 列？
SELECT  sname, ssex,class from students;


-- 2.查询教师所有的单位 既不重复的depart 列？  去重 关键字  distinct

SELECT DISTINCT depart from teachers;

-- 3.查询student 表的所有记录？
SELECT * from students;

-- 4.查询score表中成绩在60 到 80之间的所以记录？ between
SELECT * from scores where degree BETWEEN 60 and 80;

-- 5.查询score中成绩 为85 86 88 的记录？ in ()关键字
select * from scores WHERE degree in(85,86,88);


-- 6. 查询students 表中 95031 班 或者 性别为女 的同学记录？or 关键字
SELECT * from  students WHERE class='95031' or ssex='女';

-- 7.以class 降序查询 students 表的所以记录？ 关键字  ORDER BY DESC 降序！ 
SELECT * from students ORDER BY class DESC;

-- 8.以 con 升序，degree降序查询 score 表的所有记录。order by 默认状态下是 升序
select * from scores ORDER BY sno, degree DESC;

-- 9.查询‘95031’ 班的学生人数 分组查询  SELECT COUNT(expr) AS ‘名字’ from 表 WHERE 条件（xx=xx;）！
SELECT COUNT(1) AS '95031班级的学生数量' from students where class='95031'; 

-- 10.查询score 表中的最高分的学生学号和课程号。分数降序查询 由高到低 分组时只取第一页！（联想子查询 ，一个查询的结果是另一个查询的条件）
-- 1.先查询score表中  的最高分，然后由此得到 最高分的学生学号和课程号。
SELECT cno,sno from scores ORDER BY degree DESC  LIMIT 1;-- 这种简单快捷
SELECT max(degree) from  scores;-- 利用聚合函数 max（列名）获取最大值
SELECT cno,sno from scores where degree=(SELECT max(degree) from  scores); -- 这种相对麻烦。

-- 简单的聚合函数 最大值  最小值  平均值 求和值
SELECT max(class) from students;
select avg(class) from students;
SELECT min(class) from students;
SELECT sum(class) from students;
SELECT COUNT(class)FROM students;-- 对表中数据的的个数求和。

-- 11.查询3-105 号课程的平均分
SELECT avg(degree) from scores where cno='3-105';

-- 12.查score表 至少有5名学生选修的并以3开头的平均分数 LIKE ‘x%’ 模糊查询
-- 结果集是分组查询后,再次进行筛选,不能使用where, 分组后再次过滤,关键字 having
SELECT cno,avg(degree) FROM scores WHERE cno like '3%' GROUP BY cno HAVING COUNT(sno)>=5;
SELECT cno,avg(degree) FROM scores WHERE cno like '3%' GROUP BY cno HAVING count(sno)>=5;

-- 13.在表scors 查询最低分大于70，最高分小于90的sno列 分组查询 group by 列名
--    结果集是分组查询后,再次进行筛选,不能使用where, 分组后再次过滤,关键字 having
SELECT sno FROM scores GROUP BY sno HAVING max(degree)<90 and min(degree)>70 LIMIT 5;

-- 14.查询所有学生的sname cno  degree 列                     INNER JOIN 显示内连接  on 是限制条件
 SELECT sname,cno,degree FROM students INNER JOIN scores ON(students.sno=scores.sno)ORDER BY sname;
 SELECT sname,cno,degree FROM students INNER JOIN scores on(students.sno=scores.sno)ORDER BY sname;
 SELECT sname,cno,degree from students inner JOIN scores on (students.sno=scores.sno) order by sname;

-- 15.查询所有学生的sname cname 和 degree列。 三张表 一起查。 INNER JOIN 用两次  限制条件是 外键
SELECT sname,cname,degree FROM students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(scores.cno=courses.cno)ORDER BY sname;
SELECT sname,cname,degree FROM students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(scores.cno=courses.cno)ORDER BY sname;

-- 16.查询所有学生的sno cname degeree 列  排序查询 ORDER BY 关键字 默认是升序 ASC         降序  DESC
     SELECT sno,cname,degree FROM scores INNER JOIN courses ON(courses.cno=scores.cno)ORDER BY sno DESC;
   SELECT sno,cname,degree FROM scores INNER JOIN courses ON(courses.cno=scores.cno)ORDER BY sno;
     SELECT sno,cname,degree FROM scores INNER JOIN courses ON(courses.cno=scores.cno)ORDER BY sno DESC;

-- 17.查询 ‘95033’班所选课程的的平均分。分组查询 GROUP BY 被分组的列名 
--    必须跟随聚合函数
SELECT cname,avg(degree)
from students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(courses.cno=scores.cno)WHERE class='95033' 
GROUP BY courses.cno ORDER BY cname DESC;

SELECT cname,avg(degree)
FROM students INNER JOIN scores ON(students.sno=scores.sno)INNER JOIN courses ON(courses.cno=scores.cno) WHERE class='95033'
GROUP BY courses.cno ORDER BY cname desc;