physon可以做网站,绍兴seo包年排行榜,精美旅游网站案例,西安产品设计公司有哪些目录一、准备数据1、创建数据库2、创建学生表3、创建教师表4、创建课程表5、创建成绩表6、添加数据二、查询练习1、查询 student 表的所有行2、查询 student 表中的 name、sex 和 class 字段的所有行3、查询 teacher 表中不重复的 department 列4、查询 score 表中成绩在60-80之…
目录一、准备数据1、创建数据库2、创建学生表3、创建教师表4、创建课程表5、创建成绩表6、添加数据二、查询练习1、查询 student 表的所有行2、查询 student 表中的 name、sex 和 class 字段的所有行3、查询 teacher 表中不重复的 department 列4、查询 score 表中成绩在60-80之间的所有行区间查询和运算符查询5、查询 score 表中成绩为 85, 86 或 88 的行6、查询 student 表中 95031 班或性别为 女 的所有行7、以 class 降序的方式查询 student 表的所有行8、以 c_no 升序、degree 降序查询 score 表的所有行9、查询 95031 班的学生人数10、查询 score 表中的最高分的学生学号和课程编号子查询或排序查询11、计算分组平均值12、分组条件与模糊查询13、多表查询-114、多表查询-215、三表关联查询16、子查询加分组求平均分17、子查询 - 118、子查询 - 219、YEAR 函数与带 IN 关键字查询20、多层嵌套子查询21、多表查询22、子查询 - 323、UNION 和 NOTIN 的使用24、ANY 表示至少一个 - DESC ( 降序 )25、表示所有的 ALL26、复制表的数据作为条件查询27、子查询 - 428、条件加组筛选29、NOTLIKE 模糊查询取反30、YEAR 与 NOW 函数31、MAX 与 MIN 函数32、多段排序33、子查询 - 534、MAX 函数与子查询35、子查询 - 636、子查询 - 737、子查询 - 838、按等级查询参考一、准备数据
创建数据库-》创建学生表-》创建教师表-》创建课程表-》创建成绩表
1、创建数据库
-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;2、创建学生表
由学号、姓名、性别、出生年月日、所在年级组成。学号为主键。
-- 创建学生表
CREATE TABLE student (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(10) NOT NULL,birthday DATE, -- 生日class VARCHAR(20) -- 所在班级
);3、创建教师表
由教师编号、姓名、性别、出生年月日、职称、所在部门组成教师编号为主键。
-- 创建教师表
CREATE TABLE teacher (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(10) NOT NULL,birthday DATE,profession VARCHAR(20) NOT NULL, -- 职称department VARCHAR(20) NOT NULL -- 部门
);4、创建课程表
由课程号、课程名称、教师编号组成课程号为主键教师编号为外键。
-- 创建课程表
CREATE TABLE course (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,t_no VARCHAR(20) NOT NULL, -- 教师编号-- 表示该 tno 来自于 teacher 表中的 no 字段值FOREIGN KEY(t_no) REFERENCES teacher(no)
);5、创建成绩表
由学生学号、课程号、成绩组成。 学生学号、课程号均为外键。两者联合为主键。 因为一个学生要修多门课程每个课程都有一个成绩。如果只设学号or课程号为主键都会存在无法区分问题。
-- 成绩表
CREATE TABLE score (s_no VARCHAR(20) NOT NULL, -- 学生编号c_no VARCHAR(20) NOT NULL, -- 课程号degree DECIMAL, -- 成绩-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值FOREIGN KEY(s_no) REFERENCES student(no), FOREIGN KEY(c_no) REFERENCES course(no),-- 设置 s_no, c_no 为联合主键PRIMARY KEY(s_no, c_no)
);6、添加数据
添加学生表数据、添加教师表数据、添加课程表数据、添加添加成绩表数据。
-- 添加学生表数据
INSERT INTO student VALUES(101, 曾华, 男, 1977-09-01, 95033);
INSERT INTO student VALUES(102, 匡明, 男, 1975-10-02, 95031);
INSERT INTO student VALUES(103, 王丽, 女, 1976-01-23, 95033);
INSERT INTO student VALUES(104, 李军, 男, 1976-02-20, 95033);
INSERT INTO student VALUES(105, 王芳, 女, 1975-02-10, 95031);
INSERT INTO student VALUES(106, 陆军, 男, 1974-06-03, 95031);
INSERT INTO student VALUES(107, 王尼玛, 男, 1976-02-20, 95033);
INSERT INTO student VALUES(108, 张全蛋, 男, 1975-02-10, 95031);
INSERT INTO student VALUES(109, 赵铁柱, 男, 1974-06-03, 95031);-- 添加教师表数据
INSERT INTO teacher VALUES(804, 李诚, 男, 1958-12-02, 副教授, 计算机系);
INSERT INTO teacher VALUES(856, 张旭, 男, 1969-03-12, 讲师, 电子工程系);
INSERT INTO teacher VALUES(825, 王萍, 女, 1972-05-05, 助教, 计算机系);
INSERT INTO teacher VALUES(831, 刘冰, 女, 1977-08-14, 助教, 电子工程系);-- 添加课程表数据
INSERT INTO course VALUES(3-105, 计算机导论, 825);
INSERT INTO course VALUES(3-245, 操作系统, 804);
INSERT INTO course VALUES(6-166, 数字电路, 856);
INSERT INTO course VALUES(9-888, 高等数学, 831);-- 添加添加成绩表数据
INSERT INTO score VALUES(103, 3-105, 92);
INSERT INTO score VALUES(103, 3-245, 86);
INSERT INTO score VALUES(103, 6-166, 85);
INSERT INTO score VALUES(105, 3-105, 88);
INSERT INTO score VALUES(105, 3-245, 75);
INSERT INTO score VALUES(105, 6-166, 79);
INSERT INTO score VALUES(109, 3-105, 76);
INSERT INTO score VALUES(109, 3-245, 68);
INSERT INTO score VALUES(109, 6-166, 81);二、查询练习
1、查询 student 表的所有行
SELECT * FROM student;2、查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;3、查询 teacher 表中不重复的 department 列
如果单纯使用
SELECT department FROM teacher;会发现有重复
-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;4、查询 score 表中成绩在60-80之间的所有行区间查询和运算符查询
-- 查询 score 表中成绩在60-80之间的所有行区间查询和运算符查询
-- BETWEEN xx AND xx: 查询区间, AND 表示 并且
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree 60 AND degree 80;5、查询 score 表中成绩为 85, 86 或 88 的行
-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);6、查询 student 表中 ‘95031’ 班或性别为 ‘女’ 的所有行
-- 查询 student 表中 95031 班或性别为 女 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class 95031 or sex 女;7、以 class 降序的方式查询 student 表的所有行
-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序从高到低
-- ASC默认: 升序从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;8、以 c_no 升序、degree 降序查询 score 表的所有行
-- 以 c_no 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;9、查询 “95031” 班的学生人数
-- 查询 95031 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class 95031;10、查询 score 表中的最高分的学生学号和课程编号子查询或排序查询
首先找到最高分在哪儿然后把他选择出来
-- (SELECT MAX(degree) FROM score): 子查询算出最高分
SELECT s_no, c_no FROM score WHERE degree (SELECT MAX(degree) FROM score);降序排序然后选择第一条数据
-- 排序查询
-- LIMIT r, n: 表示从第r行开始查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;11、计算分组平均值
查询每门课的平均成绩
-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no 3-105;
SELECT AVG(degree) FROM score WHERE c_no 3-245;
SELECT AVG(degree) FROM score WHERE c_no 6-166;-- GROUP BY: 分组查询 这样就不需要一行一行地打了
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;12、分组条件与模糊查询
查询 score 表中至少有 2 名学生选修并以 3 开头的课程的平均分数。 首先查询score表。
SELECT * FROM score;
-- c_no 课程编号
---------------------
| s_no | c_no | degree |
---------------------
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
---------------------分析表发现至少有 2 名学生选修的课程是 3-105 、3-245 、6-166 以 3 开头的课程是 3-105 、3-245 。也就是说我们要查询所有 3-105 和 3-245 的 degree 平均分。
首先把 c_no, AVG(degree) 通过分组查询出来
-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
--------------------
| c_no | AVG(degree) |
--------------------
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
--------------------再查询出至少有 2 名学生选修的课程
-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(c_no) 2并且是以 3 开头的课程
-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询% 是一个通配符匹配 3 后面的任意字符。
AND c_no LIKE 3%;把前面的SQL语句拼接起来后面加上一个 COUNT(*)表示将每个分组的个数也查询出来
-- 把前面的SQL语句拼接起来
-- 后面加上一个 COUNT(*)表示将每个分组的个数也查询出来。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) 2 AND c_no LIKE 3%;
------------------------------
| c_no | AVG(degree) | COUNT(*) |
------------------------------
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
------------------------------13、多表查询-1
查询所有学生的 name以及该学生在 score 表中对应的 c_no 和 degree 。
从student表中选出学号和姓名 从score表中选出学号、班级号、和分数。
SELECT no, name FROM student;
----------------
| no | name |
----------------
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆军 |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |
----------------SELECT s_no, c_no, degree FROM score;
---------------------
| s_no | c_no | degree |
---------------------
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
---------------------通过分析可以发现只要把 score 表中的 s_no 字段值替换成 student 表中对应的 name 字段值就可以了。
-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score
WHERE student.no score.s_no;
--------------------------
| name | c_no | degree |
--------------------------
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
--------------------------14、多表查询-2
查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列。 只有 score 关联学生的 no 因此只要查询 score 表就能找出所有和学生相关的 no 和 degree
SELECT s_no, c_no, degree FROM score;
---------------------
| s_no | c_no | degree |
---------------------
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
---------------------然后观察 course 表
------------------------
| no | name |
------------------------
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
------------------------只要把 score 表中的 c_no 替换成 course 表中对应的 name 字段值就可以了。
-- 增加一个查询字段 name分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no course.no;
-------------------------------
| s_no | c_name | degree |
-------------------------------
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
-------------------------------15、三表关联查询
查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 。 sname来自student表 cname来自course表 degree来自score表 只有 score 表中关联学生的学号和课堂号我们只要围绕着 score 这张表查询就好了。
-- 由于字段名存在重复使用 表名.字段名 as 别名 代替。
SELECT student.name as s_name, course.name as c_name, degree
FROM student, score, course
WHERE student.NO score.s_no
AND score.c_no course.no;16、子查询加分组求平均分
查询 95031 班学生每门课程的平均成绩。 分为三个步骤 1、找到95031班的学生
SELECT no FROM student WHERE class 950312、以上面的学生号为查找对象在分数表中将学生号、课程号、分数打印出来 在 score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩
-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class 95031);
---------------------
| s_no | c_no | degree |
---------------------
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
---------------------3、以课程号分组算平均成绩
SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class 95031)
GROUP BY c_no;
--------------------
| c_no | AVG(degree) |
--------------------
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
--------------------17、子查询 - 1
查询在 3-105 课程中所有成绩高于 109 号同学的记录。 where的条件有两个1、课程号为3-105 2、在本门课程中分数高于109号同学的分数
SELECT * FROM score
WHERE c_no 3-105
AND degree (SELECT degree FROM score WHERE s_no 109 AND c_no 3-105);18、子查询 - 2
查询所有成绩高于 109 号同学的 3-105 课程成绩记录。 不限制课程号只要成绩大于109号同学的3-105课程成绩就可以
SELECT * FROM score
WHERE degree (SELECT degree FROM score WHERE s_no 109 AND c_no 3-105);19、YEAR 函数与带 IN 关键字查询
查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。
-- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));20、多层嵌套子查询
查询 ‘张旭’ 教师任课的学生成绩表。 1、首先找到教师编号
SELECT NO FROM teacher WHERE NAME 张旭2、通过 course 表找到该教师课程号
SELECT NO FROM course WHERE t_no ( SELECT NO FROM teacher WHERE NAME 张旭 );3、通过筛选出的课程号查询成绩表
SELECT * FROM score WHERE c_no (SELECT no FROM course WHERE t_no ( SELECT no FROM teacher WHERE NAME 张旭 )
);反复套娃。。。
21、多表查询
查询某选修课程多于5个同学的教师姓名。 1、首先在 teacher 表中根据 no 字段来判断该教师的同一门课程是否有至少5名学员选修
-- 查询 teacher 表
SELECT no, name FROM teacher;
-------------
| no | name |
-------------
| 804 | 李诚 |
| 825 | 王萍 |
| 831 | 刘冰 |
| 856 | 张旭 |
-------------
SELECT name FROM teacher WHERE no IN (-- 在这里找到对应的条件
);2、查看和教师编号有有关的表的信息
SELECT * FROM course;
-- t_no: 教师编号
------------------------------
| no | name | t_no |
------------------------------
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
------------------------------3、在 score 表中将 c_no 作为分组并且限制 c_no 持有至少 5 条数据
-- 在此之前向 score 插入一些数据以便丰富查询条件。
INSERT INTO score VALUES (101, 3-105, 90);
INSERT INTO score VALUES (102, 3-105, 91);
INSERT INTO score VALUES (104, 3-105, 89);
-- 查询 score 表
SELECT * FROM score;
---------------------
| s_no | c_no | degree |
---------------------
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
---------------------SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) 5;
-------
| c_no |
-------
| 3-105 |
-------4、根据筛选出来的课程号找出在某课程中拥有至少5名学员的教师编号
SELECT t_no FROM course WHERE no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) 5
);
------
| t_no |
------
| 825 |
------5、在 teacher 表中根据筛选出来的教师编号找到教师姓名
SELECT name FROM teacher WHERE no IN (-- 最终条件SELECT t_no FROM course WHERE no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) 5)
);22、子查询 - 3
查询 “计算机系” 课程的成绩表。 1、先找出 course 表中所有 计算机系 课程的编号
-- 通过 teacher 表查询所有 计算机系 的教师编号
SELECT no, name, department FROM teacher WHERE department 计算机系
---------------------------
| no | name | department |
---------------------------
| 804 | 李诚 | 计算机系 |
| 825 | 王萍 | 计算机系 |
----------------------------- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (SELECT no FROM teacher WHERE department 计算机系
);
-------
| no |
-------
| 3-245 |
| 3-105 |
-------
2、根据这个编号查询 score 表
-- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (SELECT no FROM course WHERE t_no IN (SELECT no FROM teacher WHERE department 计算机系)
);
---------------------
| s_no | c_no | degree |
---------------------
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
---------------------23、UNION 和 NOTIN 的使用
查询 计算机系 与 电子工程系 中的不同职称的教师。 首先观察所有教师发现题目中要求我们找到的是804 and 856. 1、首先找计算机系里面的讲师这个讲师的职称不在电子工程系里面。
-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department 计算机系 AND profession NOT IN (SELECT profession FROM teacher WHERE department 电子工程系
)2、另外一个反过来
SELECT * FROM teacher WHERE department 电子工程系 AND profession NOT IN (SELECT profession FROM teacher WHERE department 计算机系
);3、使用union把两个集合拼接起来注意第一句话不要有分号
SELECT * FROM teacher WHERE department 计算机系 AND profession NOT IN (SELECT profession FROM teacher WHERE department 电子工程系
)
-- 合并两个集
UNION
SELECT * FROM teacher WHERE department 电子工程系 AND profession NOT IN (SELECT profession FROM teacher WHERE department 计算机系
);24、ANY 表示至少一个 - DESC ( 降序 )
查询课程 3-105 且成绩 至少 高于 3-245 的 score 表并且按照degree从高到低排序 1、首先把课程为3-105 和3-245 的成绩表选出来看看
SELECT * FROM score WHERE c_no 3-105;
---------------------
| s_no | c_no | degree |
---------------------
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
---------------------SELECT * FROM score WHERE c_no 3-245;
---------------------
| s_no | c_no | degree |
---------------------
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
---------------------2、在 3-105 成绩中只要有一个大于从 3-245 筛选出来的任意行就符合条件。最后根据降序查询结果。 这里其实意思就是大于3-245成绩最小值即可
SELECT * FROM score WHERE c_no 3-105 AND degree ANY(SELECT degree FROM score WHERE c_no 3-245
) ORDER BY degree DESC;
---------------------
| s_no | c_no | degree |
---------------------
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
---------------------25、表示所有的 ALL
查询课程 3-105 且成绩高于 3-245 的 score 表。 也就是说在 3-105 每一行成绩中都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no 3-105 AND degree ALL(SELECT degree FROM score WHERE c_no 3-245
);
---------------------
| s_no | c_no | degree |
---------------------
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
---------------------26、复制表的数据作为条件查询
查询某课程成绩比该课程平均成绩低的 score 表。 1、首先查询每个课程的平均分
-- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
--------------------
| c_no | AVG(degree) |
--------------------
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
--------------------2、查询 score 表
SELECT degree FROM score;
--------
| degree |
--------
| 90 |
| 91 |
| 92 |
| 86 |
| 85 |
| 89 |
| 88 |
| 75 |
| 79 |
| 76 |
| 68 |
| 81 |
--------3、将表 b 作用于表 a 中查询数据。ab两表均为score的别名
SELECT * FROM score a WHERE degree ((SELECT AVG(degree) FROM score b WHERE a.c_no b.c_no)
);
---------------------
| s_no | c_no | degree |
---------------------
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
---------------------27、子查询 - 4
查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department 。
SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
-------------------------
| name | department |
-------------------------
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
-------------------------28、条件加组筛选
查询 student 表中至少有 2 名男生的 class 。
-- 查看学生表信息
SELECT * FROM student;
----------------------------------------
| no | name | sex | birthday | class |
----------------------------------------
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
------------------------------------------ 只查询性别为男然后按 class 分组并限制 class 行大于 1。
SELECT class FROM student WHERE sex 男 GROUP BY class HAVING COUNT(*) 1;
-------
| class |
-------
| 95033 |
| 95031 |
-------29、NOTLIKE 模糊查询取反
查询 student 表中不姓 “王” 的同学记录。
-- NOT: 取反
-- LIKE: 模糊查询
SELECT * FROM student WHERE name NOT LIKE 王%;
----------------------------------------
| no | name | sex | birthday | class |
----------------------------------------
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
----------------------------------------30、YEAR 与 NOW 函数
查询 student 表中每个学生的姓名和年龄。 使用函数 YEAR(NOW()) 计算出当前年份减去出生年份后得出年龄。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
-----------------
| name | age |
-----------------
| 曾华 | 42 |
| 匡明 | 44 |
| 王丽 | 43 |
| 李军 | 43 |
| 王芳 | 44 |
| 陆军 | 45 |
| 王尼玛 | 43 |
| 张全蛋 | 44 |
| 赵铁柱 | 45 |
| 张飞 | 45 |
-----------------31、MAX 与 MIN 函数
查询 student 表中最大和最小的 birthday 值。
SELECT MAX(birthday), MIN(birthday) FROM student;
------------------------------
| MAX(birthday) | MIN(birthday) |
------------------------------
| 1977-09-01 | 1974-06-03 |
------------------------------32、多段排序
以 class 和 birthday 从大到小的顺序查询 student 表。
SELECT * FROM student ORDER BY class DESC, birthday;
----------------------------------------
| no | name | sex | birthday | class |
----------------------------------------
| 110 | 张飞 | 男 | 1974-06-03 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
----------------------------------------33、子查询 - 5
查询 “男” 教师及其所上的课程。 先从教师表中找教师再把查询结果作为课程表的查询依据
SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex 男);
---------------------------
| no | name | t_no |
---------------------------
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
---------------------------34、MAX 函数与子查询
查询最高分同学的 score 表。
-- 找出最高成绩该查询只能有一个结果
SELECT MAX(degree) FROM score;
-- 根据上面的条件筛选出所有最高成绩表
-- 该查询可能有多个结果假设 degree 值多次符合条件。
SELECT * FROM score WHERE degree (SELECT MAX(degree) FROM score);
---------------------
| s_no | c_no | degree |
---------------------
| 103 | 3-105 | 92 |
---------------------35、子查询 - 6
查询和 “李军” 同性别的所有同学 name 。
-- 首先将李军的性别作为条件取出来
SELECT sex FROM student WHERE name 李军;
-----
| sex |
-----
| 男 |
-----
-- 根据性别查询 name 和 sex
SELECT name, sex FROM student WHERE sex (SELECT sex FROM student WHERE name 李军
);
----------------
| name | sex |
----------------
| 曾华 | 男 |
| 匡明 | 男 |
| 李军 | 男 |
| 陆军 | 男 |
| 王尼玛 | 男 |
| 张全蛋 | 男 |
| 赵铁柱 | 男 |
| 张飞 | 男 |
----------------36、子查询 - 7
查询和 李军 同性别且同班的同学 name 。
SELECT name, sex, class FROM student WHERE sex (SELECT sex FROM student WHERE name 李军
) AND class (SELECT class FROM student WHERE name 李军
);
-----------------------
| name | sex | class |
-----------------------
| 曾华 | 男 | 95033 |
| 李军 | 男 | 95033 |
| 王尼玛 | 男 | 95033 |
-----------------------37、子查询 - 8
查询所有选修 “计算机导论” 课程的 “男” 同学成绩表。 需要的 “计算机导论” 和性别为 “男” 的编号可以在 course 和 student 表中找到。
SELECT * FROM score WHERE c_no (SELECT no FROM course WHERE name 计算机导论
) AND s_no IN (SELECT no FROM student WHERE sex 男
);
---------------------
| s_no | c_no | degree |
---------------------
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
---------------------38、按等级查询
建立一个 grade 表代表学生的成绩等级并插入数据
CREATE TABLE grade (low INT(3),upp INT(3),grade char(1)
);INSERT INTO grade VALUES (90, 100, A);
INSERT INTO grade VALUES (80, 89, B);
INSERT INTO grade VALUES (70, 79, C);
INSERT INTO grade VALUES (60, 69, D);
INSERT INTO grade VALUES (0, 59, E);SELECT * FROM grade;
-------------------
| low | upp | grade |
-------------------
| 90 | 100 | A |
| 80 | 89 | B |
| 70 | 79 | C |
| 60 | 69 | D |
| 0 | 59 | E |
-------------------2、查询所有学生的 s_no 、c_no 和 grade 列。 思路是使用区间 ( BETWEEN ) 查询判断学生的成绩 ( degree ) 在 grade 表的 low 和 upp 之间。
SELECT s_no, c_no, grade FROM score, grade
WHERE degree BETWEEN low AND upp;
--------------------
| s_no | c_no | grade |
--------------------
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | C |
| 105 | 6-166 | C |
| 109 | 3-105 | C |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
--------------------参考
一天学会MySQL 某Github
