/* 自己查询自己 把一张表看成是两张表。 表的设计。 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;