/*
自己查询自己 把一张表看成是两张表。 表的设计。
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;
浙公网安备 33010602011771号