SQL语句练习

最新推荐文章于 2022-03-22 16:41:25 发布

~无关风月~

最新推荐文章于 2022-03-22 16:41:25 发布

阅读量2.4k

点赞数 1

分类专栏： MySQL 文章标签： mysql 面试数据库

本文链接：https://blog.csdn.net/zxm1306192988/article/details/80093985

版权

MySQL 专栏收录该内容

12 篇文章 2 订阅

订阅专栏

sql面试题（学生表课程表成绩表_教师表）
这里写图片描述

建表

CREATE TABLE `t_student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(32) DEFAULT NULL,
  `student_age` int(11) DEFAULT NULL,
  `student_sex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_course` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(32) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_score` (
  `score_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT '0',
  `course_id` int(11) DEFAULT NULL,
  `score_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`score_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_teacher` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DEFALUT 定义列的默认值
AUTO_INCREMENT=1 主键自动增长，从1开始增长

插入测试数据语句

INSERT INTO `t_student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `t_student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `t_student` VALUES ('3', '张三', '17', '男');
INSERT INTO `t_student` VALUES ('4', '李四', '18', '女');
INSERT INTO `t_student` VALUES ('5', '王五', '17', '男');
INSERT INTO `t_student` VALUES ('6', '赵六', '19', '女');

INSERT INTO `t_course` VALUES ('1', '语文', '1');
INSERT INTO `t_course` VALUES ('2', '数学', '2');
INSERT INTO `t_course` VALUES ('3', '英语', '3');
INSERT INTO `t_course` VALUES ('4', '物理', '4');

INSERT INTO `t_score` VALUES ('1', '1', '1', '56');
INSERT INTO `t_score` VALUES ('2', '1', '2', '78');
INSERT INTO `t_score` VALUES ('3', '1', '3', '67');
INSERT INTO `t_score` VALUES ('4', '1', '4', '58');
INSERT INTO `t_score` VALUES ('5', '2', '1', '79');
INSERT INTO `t_score` VALUES ('6', '2', '2', '81');
INSERT INTO `t_score` VALUES ('7', '2', '3', '92');
INSERT INTO `t_score` VALUES ('8', '2', '4', '68');
INSERT INTO `t_score` VALUES ('9', '3', '1', '91');
INSERT INTO `t_score` VALUES ('10', '3', '2', '47');
INSERT INTO `t_score` VALUES ('11', '3', '3', '88');
INSERT INTO `t_score` VALUES ('12', '3', '4', '56');
INSERT INTO `t_score` VALUES ('13', '4', '2', '88');
INSERT INTO `t_score` VALUES ('14', '4', '3', '90');
INSERT INTO `t_score` VALUES ('15', '4', '4', '93');
INSERT INTO `t_score` VALUES ('16', '5', '1', '46');
INSERT INTO `t_score` VALUES ('17', '5', '3', '78');
INSERT INTO `t_score` VALUES ('18', '5', '4', '53');
INSERT INTO `t_score` VALUES ('19', '6', '1', '35');
INSERT INTO `t_score` VALUES ('20', '6', '2', '68');
INSERT INTO `t_score` VALUES ('21', '6', '4', '71');

INSERT INTO `t_teacher` VALUES ('1', '叶平');
INSERT INTO `t_teacher` VALUES ('2', '贺高');
INSERT INTO `t_teacher` VALUES ('3', '杨艳');
INSERT INTO `t_teacher` VALUES ('4', '周磊');

问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号；

SELECT a.student_id FROM
(SELECT student_id,score_number FROM t_score WHERE course_id=001) a,
(SELECT student_id,score_number FROM t_score WHERE course_id=002) b
WHERE a.score_number>b.score_number AND a.student_id=b.student_id;

子查询

SELECT a.student_id 
from t_score AS a
WHERE a.course_id=001 AND a.score_number>(
SELECT b.score_number FROM t_score AS b
WHERE b.course_id=002 AND a.student_id=b.student_id 
);

SELECT a.student_id 
from t_score AS a
WHERE a.course_id=001 AND student_id IN(
SELECT student_id FROM t_score AS b
WHERE b.course_id=002 AND a.score_number>b.score_number AND a.student_id=b.student_id 
);

内连接

SELECT a.student_id 
FROM t_score AS a
INNER JOIN t_score AS b
ON a.course_id=001 AND b.course_id=002 AND a.score_number>b.score_number AND a.student_id=b.student_id;

等值连接

SELECT a.student_id 
from t_score AS a,t_score AS b
WHERE a.course_id=001 AND b.course_id=002 AND a.score_number>b.score_number AND a.student_id=b.student_id ;

交叉连接

SELECT a.student_id 
FROM t_score AS a
CROSS JOIN t_score AS b
ON a.course_id=001 AND b.course_id=002 AND a.score_number>b.score_number AND a.student_id=b.student_id;

这里写图片描述

INNER JOIN 和等值连接可以有相同的效果，但
INNER JOIN可以不等： select * from t1 inner join t2 on t1.id<>t2.id;
从集合论角度看：等值连接是内连接的子集

CROSS JOIN 做笛卡尔积可以不加 ON 后面的条件，RIGHT JOIN和LEFT JOIN必须加
CROSS JOIN 可以和INNER JOIN 以及等值连接有相同的效果，但join的方式不同，cross join生成的是先生成笛卡尔集，然后on连接条件被视为了filter用于数据过滤，inner join是直接基于join condition做连接，生成的join集合就是最终的输出结果，产生的中间数据更小。实际上MySQL优化器会将这两条查询都优化成同一种join方式。

2、查询平均成绩大于60分的同学的学号和平均成绩；

SELECT student_id,AVG(score_number)
FROM t_score
GROUP BY student_id HAVING AVG(score_number)>60;

group by() 对数据进行分组，对执行完group by之后的组进行聚合函数计算，计算每一个组的值。
group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中
group by 会根据分组依据，没项只列一行
最后用having 去掉不符合条件的组。
如果根据多列进行分组，用 group by all

聚合函数是对一组值进行计算并返回单一的值的函数，通常与select语句中的group by 子句一同使用。
avg()、max()、min()、sum()、count()

Having与Where的区别
where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

3、查询所有同学的学号、姓名、选课数、总成绩；

SELECT t_student.student_id,t_student.student_name,COUNT(t_score.course_id),SUM(t_score.score_number)
FROM t_student 
LEFT OUTER JOIN t_score ON t_student.student_id=t_score.student_id
GROUP BY student_id ;

SELECT t_student.student_id,t_student.student_name,COUNT(t_score.course_id),SUM(t_score.score_number)
FROM t_student,t_score 
WHERE t_student.student_id=t_score.student_id
GROUP BY student_id ;

在上面语句中，实际上是创建了两张表的笛卡尔积，低效利用数据库资源，有些数据库系统会识别出 WHERE连接并自动转换为 INNER JOIN

4、查询姓“李”的老师的个数；

SELECT COUNT(DISTINCT(teacher_name)) AS 个数
FROM t_teacher 
WHERE teacher_name LIKE '李%';

加DISTINCT() 是为了防止有重复行

5、查询没学过“叶平”老师课的同学的学号、姓名；

SELECT student_id,student_name
FROM t_student
WHERE student_id NOT IN 
(SELECT DISTINCT(student_id) 
FROM t_score,t_course,t_teacher 
WHERE t_score.course_id=t_course.course_id 
      AND t_course.teacher_id=t_teacher.teacher_id 
      AND t_teacher.teacher_name='叶平');

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT t_student.student_id,t_student.student_name
FROM t_student,t_score
WHERE t_student.student_id=t_score.student_id 
      AND t_score.course_id=001 
      AND EXISTS 
         (SELECT * FROM t_score AS t_score2 WHERE t_score.student_id=t_score2.student_id AND t_score2.course_id=002)
;

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT student_id,student_name FROM t_student WHERE student_id IN
(
SELECT student_id FROM t_score,t_course,t_teacher WHERE t_score.course_id=t_course.course_id AND t_course.teacher_id=t_teacher.teacher_id AND t_teacher.teacher_name='叶平'
GROUP BY student_id HAVING COUNT(t_course.course_id)=
(SELECT COUNT(course_id) FROM t_course,t_teacher WHERE t_teacher.teacher_id=t_course.teacher_id AND t_teacher.teacher_name='叶平')
);

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT student_id,student_name FROM t_student WHERE student_id IN
(
SELECT student_id FROM t_score WHERE course_id=002 AND score_number < (SELECT score_number from t_score AS t_score2 WHERE t_score.student_id=t_score2.student_id AND t_score2.course_id=001)
);

SELECT student_id,student_name FROM 
(
SELECT t_student.student_id,t_student.student_name,score_number,
(SELECT score_number FROM t_score AS t_score2 WHERE t_score2.student_id=t_student.student_id AND t_score2.course_id=002)score_number2 
from t_student,t_score 
WHERE t_student.student_id=t_score.student_id AND course_id=001
)derived_table 
WHERE score_number2<score_number;

9、查询所有课程成绩都小于60分的同学的学号、姓名；
考虑到从t_score表统计每门课都小于60分的同学的id比较困难，运用反向思维，查询成绩大于等于60分同学的id，不在这里面的即为每门课都小于60分的同学

SELECT student_id,student_name FROM t_student WHERE student_id NOT IN
(
SELECT DISTINCT(student_id) FROM t_score  WHERE score_number>=60
)
;

10、查询没有学全所有课的同学的学号、姓名；

SELECT student_id,student_name FROM t_student WHERE student_id IN(
SELECT student_id FROM t_score GROUP BY student_id HAVING COUNT(course_id)<(SELECT COUNT(course_id) FROM t_course)
);

SELECT t_student.student_id,student_name FROM t_student,t_score WHERE t_student.student_id=t_score.student_id 
GROUP BY t_student.student_id 
HAVING COUNT(course_id)<(SELECT COUNT(course_id) FROM t_course);

11、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT(t_student.student_id),student_name FROM t_student,t_score WHERE t_student.student_id=t_score.student_id AND course_id IN
(
SELECT course_id FROM t_score WHERE student_id=001
);

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
此题与上题的唯一区别就是排除学号为“001”的同学本身。

SELECT DISTINCT(t_student.student_id),student_name FROM t_student,t_score WHERE t_student.student_id=t_score.student_id AND course_id IN
(
SELECT course_id FROM t_score WHERE student_id=001
) AND t_student.student_id!=001;

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

UPDATE t_score SET score_number=(
SELECT * from (SELECT AVG(t_score2.score_number) FROM t_score AS t_score2, t_course AS t_course2,t_teacher AS t_teacher2 WHERE t_score2.course_id=t_course2.course_id AND t_course2.teacher_id=t_teacher2.teacher_id AND teacher_name='叶平')ta)
WHERE t_score.course_id=(SELECT course_id FROM t_course WHERE teacher_id=(SELECT teacher_id from t_teacher where teacher_name='叶平'));

此题不好，如果叶平这个老师教多门课就无法更改

14、查询也选了“002”号的同学学习课程的其他同学学号和姓名；

SELECT t_score.student_id,t_student.student_name FROM t_score,t_student WHERE 
t_score.course_id IN
(SELECT course_id FROM t_score WHERE student_id=002) 
AND
t_score.student_id=t_student.student_id
AND
t_score.student_id!=002
GROUP BY student_id
HAVING COUNT(*)=(SELECT COUNT(*) FROM t_score WHERE student_id=002)

15、删除学习“叶平”老师课的SC表记录；

DELETE t_score FROM t_score,t_course,t_teacher WHERE t_score.course_id=t_course.course_id AND t_course.teacher_id=t_teacher.teacher_id AND teacher_name='叶平'

16、向t_score表中插入一些记录，这些记录字段如下：没有上过编号“003”课程的同学学号、2、002 号课的平均成绩；

INSERT INTO 
t_score(student_id,course_id,score_number) 
VALUES(
SELECT 
student_id,002,(SELECT AVG(score_number) FROM t_score WHERE course_id=002) 
FROM t_student WHERE student_id NOT IN (SELECT student_id FROM t_score WHERE course_id=003)
);

17、按平均成绩从高到低显示所有学生的四门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,物理,有效课程数,有效课程平均分。

SELECT 
student_id AS 学生ID,
(SELECT score_number FROM t_score WHERE t_score.student_id=t.student_id AND t_score.course_id=(SELECT course_id FROM t_course WHERE course_name='语文'))AS 语文,
(SELECT score_number FROM t_score WHERE t_score.student_id=t.student_id AND t_score.course_id=(SELECT course_id FROM t_course WHERE course_name='数学'))AS 数学,
(SELECT score_number FROM t_score WHERE t_score.student_id=t.student_id AND t_score.course_id=(SELECT course_id FROM t_course WHERE course_name='英语')) AS 外语,
(SELECT score_number FROM t_score WHERE t_score.student_id=t.student_id AND t_score.course_id=(SELECT course_id FROM t_course WHERE course_name='物理')) AS 物理,
COUNT(*) AS 有效课程数,
AVG(t.score_number) AS 有效课程平均分
FROM t_score AS t
GROUP BY student_id
ORDER BY AVG(t.score_number) DESC;

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT course_id,MAX(score_number),MIN(score_number) 
FROM t_score 
GROUP BY course_id;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t_course.course_id AS 课程号,course_name AS 课程名,IFNULL(AVG(score_number),0) AS 平均成绩,
100*SUM(CASE WHEN IFNULL(score_number,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM t_score,t_course
WHERE t_score.course_id=t_course.course_id
GROUP BY t_course.course_id
ORDER BY avg(score_number) ASC,100*SUM(CASE WHEN IFNULL(score_number,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC;

20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 语文（001），数学（002），外语（003）

SELECT 
SUM(CASE WHEN course_id=001 THEN score_number ELSE 0 END)/SUM(CASE WHEN course_id=001 THEN 1 ELSE 0 END) AS 语文平均分,
100*SUM(CASE WHEN course_id=001 AND score_number>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN course_id=001 THEN 1 ELSE 0 END) AS 语文及格百分比,
SUM(CASE WHEN course_id=002 THEN score_number ELSE 0 END)/SUM(CASE WHEN course_id=002 THEN 1 ELSE 0 END) AS 数学平均分,
100*SUM(CASE WHEN course_id=002 AND score_number>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN course_id=002 THEN 1 ELSE 0 END) AS 数学及格百分比,
SUM(CASE WHEN course_id=003 THEN score_number ELSE 0 END)/SUM(CASE WHEN course_id=003 THEN 1 ELSE 0 END) AS 外语平均分,
100*SUM(CASE WHEN course_id=003 AND score_number>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN course_id=003 THEN 1 ELSE 0 END) AS 外语及格百分比
FROM t_score;

SELECT 
(SELECT SUM(score_number) FROM t_score WHERE course_id=001)/(SELECT count(course_id) FROM t_score WHERE course_id=001) AS 语文平均分,
100*(SELECT COUNT(*) FROM t_score WHERE course_id=001 AND score_number>=60)/(SELECT count(course_id) FROM t_score WHERE course_id=001) AS 语文及格百分比,
(SELECT SUM(score_number) FROM t_score WHERE course_id=002)/(SELECT count(course_id) FROM t_score WHERE course_id=002) AS 数学平均分,
100*(SELECT COUNT(*) FROM t_score WHERE course_id=002 AND score_number>=60)/(SELECT count(course_id) FROM t_score WHERE course_id=002) AS 数学及格百分比,
(SELECT SUM(score_number) FROM t_score WHERE course_id=003)/(SELECT count(course_id) FROM t_score WHERE course_id=003) AS 外语平均分,
100*(SELECT COUNT(*) FROM t_score WHERE course_id=003 AND score_number>=60)/(SELECT count(course_id) FROM t_score WHERE course_id=003) AS 外语及格百分比
;

21、查询不同老师所教不同课程平均分从高到低显示

SELECT teacher_name AS 教师姓名,t_teacher.teacher_id AS 教师ID,t_course.course_name AS 课程名称,t_course.course_id AS 课程ID,AVG(t_score.score_number) AS 平均成绩
FROM t_score,t_course,t_teacher
WHERE t_score.course_id=t_course.course_id AND t_course.teacher_id=t_teacher.teacher_id
GROUP BY t_teacher.teacher_id
ORDER BY AVG(t_score.score_number) DESC;

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：语文（001），数学（002），外语（003），显示方式：[学生ID],[学生姓名],语文,外语,数学,平均成绩

SELECT DISTINCT
t_student.student_id AS 学生ID,
student_name AS 学生姓名,
T1.score_number AS 语文,
T2.score_number AS 数学,
T3.score_number AS 外语,
(IFNULL(T1.score_number,0)+IFNULL(T2.score_number,0)+IFNULL(T3.score_number,0))/3 AS 平均分
FROM t_student,t_score 
LEFT JOIN t_score AS T1 ON t_score.student_id=T1.student_id AND T1.course_id=001
LEFT JOIN t_score AS T2 ON t_score.student_id=T2.student_id AND T2.course_id=002
LEFT JOIN t_score AS T3 ON t_score.student_id=T3.student_id AND T3.course_id=003                    
WHERE t_student.student_id=t_score.student_id 
ORDER BY IFNULL(T1.score_number,0)+IFNULL(T2.score_number,0)+IFNULL(T3.score_number,0) DESC
LIMIT 2,4
;

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
SELECT
*
FROM
table LIMIT 5,10; // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql>
SELECT
*
FROM
table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目：
mysql>
SELECT
*
FROM
table LIMIT 5; //检索前 5 个记录行

//换句话说，LIMIT n 等价于 LIMIT 0,n。

23、统计列印各科成绩,各分数段的人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT t_score.course_id AS 课程ID, course_name AS 课程名称,
SUM(CASE WHEN score_number BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS `[100-85]`,
SUM(CASE WHEN score_number BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS `[85-70]`,
SUM(CASE WHEN score_number BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS `[70-60]`,
SUM(CASE WHEN score_number < 60 THEN 1 ELSE 0 END) AS `[<60]`
FROM t_score,t_course
WHERE t_score.course_id=t_course.course_id
GROUP BY t_score.course_id
;

24、查询学生平均成绩及其名次

SELECT a.student_id,a.num AS 平均成绩,COUNT(*) AS 名次
FROM
(SELECT t_score.student_id,AVG(t_score.score_number) AS num
FROM t_score
GROUP BY t_score.student_id) a,
(SELECT t_score.student_id,AVG(t_score.score_number) AS num
FROM t_score
GROUP BY t_score.student_id) b
WHERE a.num<=b.num
GROUP BY a.student_id
ORDER BY 名次 ASC

使用联接查询（笛卡尔积）
参考：https://blog.csdn.net/acmain_chm/article/details/4095531

25、查询每门课程分数最高的学生的姓名

SELECT course_name,student_name
FROM t_score,t_student,t_course
WHERE t_score.student_id=t_student.student_id AND t_score.course_id=t_course.course_id 
AND score_number=(SELECT MAX(score_number) FROM t_score AS t_score2 WHERE t_score2.course_id=t_score.course_id)

26、查询各科成绩前三名的记录:(不考虑成绩并列情况)

比一条记录的成绩大的相同课程的数量小于3，那么他一定是这个课程的分数的前三名

SELECT student_id AS 学生ID,course_id AS 课程ID,score_number AS 分数 
FROM t_score 
WHERE(
SELECT count(*) FROM t_score AS t_score2  WHERE t_score2.course_id=t_score.course_id AND t_score2.score_number>t_score.score_number
)<3 
ORDER BY course_id;

SELECT a.student_id AS 学生ID,a.course_id AS 课程ID,a.score_number AS 分数  
FROM t_score a,t_score b
WHERE
a.course_id=b.course_id AND a.score_number<=b.score_number
GROUP BY a.score_id
HAVING COUNT(b.score_id)<=3
ORDER BY a.course_id

参考：https://blog.csdn.net/come_on_air/article/details/72902592

27、查询每门课程被选修的学生数

SELECT course_id,COUNT(course_id) 
FROM t_score
GROUP BY course_id

28、查询出只选修了一门课程的全部学生的学号和姓名

SELECT t_student.student_id,student_name
FROM t_score,t_student
WHERE t_student.student_id=t_score.student_id
GROUP BY student_id
HAVING COUNT(course_id)=1

29、查询同名同性学生名单，并统计同名人数

SELECT student_name,COUNT(*)
FROM t_student
GROUP BY student_name
HAVING COUNT(*)>1

30、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT student_name,student_age
FROM t_student
WHERE (DATE_FORMAT(NOW(), '%Y')-student_age)=1981;

取当前的年份：DATE_FORMAT(NOW(), ‘%Y’)

31、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT t_score.course_id,course_name,AVG(score_number) FROM t_score,t_course
GROUP BY t_score.course_id
ORDER BY AVG(score_number),t_score.course_id DESC

32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT t_score.student_id,student_name,AVG(score_number) 
FROM t_score,t_student
WHERE t_score.student_id=t_student.student_id
GROUP BY t_score.student_id
HAVING AVG(score_number)>85

33、查询课程名称为“数学”，且分数低于60的学生姓名和分数

SELECT student_name,IFNULL(score_number,0) AS 分数
FROM t_score,t_student,t_course
WHERE t_score.student_id=t_student.student_id AND t_score.course_id=t_course.course_id AND course_name='数学' and t_score.score_number<60;

34、查询所有学生的选课情况

SELECT student_name,t_score.student_id,course_name
FROM t_score,t_student,t_course
WHERE t_score.student_id=t_student.student_id AND t_score.course_id=t_course.course_id 
ORDER BY t_score.student_id;

35、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT student_name,course_name,score_number
FROM t_score,t_student,t_course
WHERE t_score.student_id=t_student.student_id AND t_score.course_id=t_course.course_id AND score_number>=70
ORDER BY student_name,score_number DESC;

36、查询不及格的课程，并按课程号从大到小排列

SELECT t_score.course_id,course_name,score_number
FROM t_score,t_course
WHERE t_score.course_id=t_course.course_id AND score_number<70
ORDER BY t_score.course_id DESC;

37、求选了课程的学生人数

SELECT COUNT(DISTINCT(student_id))
FROM t_score;

38、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

SELECT student_name,score_number
FROM t_student,t_score,t_course,t_teacher
WHERE t_student.student_id=t_score.student_id AND t_score.course_id=t_course.course_id AND t_course.teacher_id=t_teacher.teacher_id
AND teacher_name='叶平'
AND score_number=(SELECT MAX(score_number) FROM t_score AS t_score2 WHERE t_score2.course_id=t_score.course_id);

39、查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT a.student_id,a.course_id,a.score_number
FROM t_score a,t_score b
WHERE a.score_number=b.score_number AND a.course_id!=b.course_id

40、统计每门课程的学生选修人数（超过3人的课程才统计）。要求输出课程号、课程名称和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT t_score.course_id,course_name,COUNT(student_id) AS 人数
FROM t_score,t_course
WHERE t_score.course_id=t_course.course_id
GROUP BY course_id
HAVING COUNT(student_id)>3
ORDER BY COUNT(student_id),t_score.course_id DESC

41、查询全部学生都选修的课程的课程号和课程名

SELECT t_score.course_id,course_name
FROM t_score,t_course
WHERE t_score.course_id=t_course.course_id 
GROUP BY t_score.course_id
HAVING COUNT(t_score.course_id)=(SELECT COUNT(*) FROM t_student)

42、查询两门以上不及格课程的同学的学号及其平均成绩

SELECT student_id,AVG(IFNULL(score_number,0)) AS 平均成绩
FROM t_score
WHERE t_score.student_id IN
(
SELECT student_id FROM t_score WHERE score_number<60 GROUP BY student_id HAVING count(*)>=2
) 
GROUP BY student_id

43、删除“002”同学的“001”课程的成绩

DELETE FROM t_score WHERE student_id=002 AND course_id=001

DQL语言 Data Query Language 数据查询语言

经典子查询练习

工作、雇员、部门、地址

这里写图片描述

建表并插入数据：

USE `myemployees`;

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;

/*Data for the table `departments` */

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) DEFAULT NULL,
  `email` varchar(25) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `job_id` varchar(10) DEFAULT NULL,
  `salary` double(10,2) DEFAULT NULL,
  `commission_pct` double(4,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `dept_id_fk` (`department_id`),
  KEY `job_id_fk` (`job_id`),
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;

/*Data for the table `employees` */

insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL,
  `job_title` varchar(35) DEFAULT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

/*Data for the table `jobs` */

insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);

/*Table structure for table `locations` */

DROP TABLE IF EXISTS `locations`;

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `street_address` varchar(40) DEFAULT NULL,
  `postal_code` varchar(12) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `state_province` varchar(25) DEFAULT NULL,
  `country_id` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;

/*Data for the table `locations` */

insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');

# 1. 查询工资最低的员工信息: last_name, salary

#①查询最低的工资
SELECT MIN(salary)
FROM employees

#②查询last_name,salary，要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

# 2. 查询平均工资最低的部门信息

#方式一：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep

#③查询哪个部门的平均工资=②

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
    SELECT MIN(ag)
    FROM (
        SELECT AVG(salary) ag,department_id
        FROM employees
        GROUP BY department_id
    ) ag_dep

);

#④查询部门信息

SELECT d.*
FROM departments d
WHERE d.`department_id`=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
        SELECT MIN(ag)
        FROM (
            SELECT AVG(salary) ag,department_id
            FROM employees
            GROUP BY department_id
        ) ag_dep

    )

);

#方式二：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

#③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) 
    LIMIT 1
);




# 3. 查询平均工资最低的部门信息和该部门的平均工资
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;
#③查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) 
    LIMIT 1

) ag_dep
ON d.`department_id`=ag_dep.department_id;



# 4. 查询平均工资最高的 job 信息
#①查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

#②查询job信息
SELECT * 
FROM jobs
WHERE job_id=(
    SELECT job_id
    FROM employees
    GROUP BY job_id
    ORDER BY AVG(salary) DESC
    LIMIT 1

);
# 5. 查询平均工资高于公司平均工资的部门有哪些?

#①查询平均工资
SELECT AVG(salary)
FROM employees

#②查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#③筛选②结果集，满足平均工资>①

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
    SELECT AVG(salary)
    FROM employees

);

# 6. 查询出公司中所有 manager 的详细信息.
#①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees

#②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id =ANY(
    SELECT DISTINCT manager_id
    FROM employees

);

# 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少

#①查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1


#②查询①结果的那个部门的最低工资

SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary)
    LIMIT 1


);
# 8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#①查询平均工资最高的部门编号
SELECT 
    department_id 
FROM
    employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 1 

#②将employees和departments连接查询，筛选条件是①
    SELECT 
        last_name, d.department_id, email, salary 
    FROM
        employees e 
        INNER JOIN departments d 
            ON d.manager_id = e.employee_id 
    WHERE d.department_id = 
        (SELECT 
            department_id 
        FROM
            employees 
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC 
        LIMIT 1) ;

DML语言 Data Manipulation Language 数据操作语言

#1. 显示表my_employees的结构
DESC my_employees;

#2. 向my_employees表中插入数据
#方式一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100);

#方式二：
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION;

#3.将3号员工的last_name修改为“drelxer”
UPDATE my_employees SET last_name='drelxer' WHERE id = 3;

#4.将所有工资少于900的员工的工资修改为1000
UPDATE my_employees SET salary=1000 WHERE salary<900;

#5.将userid 为Bbiri的user表和my_employees表的记录全部删除
DELETE u,e
FROM users u
JOIN my_employees e ON u.`userid`=e.`Userid`
WHERE u.`userid`='Bbiri';


#6.删除所有数据
DELETE FROM my_employees;
DELETE FROM users;

#7.清空表my_employees
TRUNCATE TABLE my_employees;

删除单表的记录
语法：delete from 表名【where 筛选条件】【limit 条目数】

delete 和 truncate 删除表的区别
1.truncate删除后，如果再插入，标识列从1开始
delete删除后，如果再插入，标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚

DDL语言 Data Definition Language 数据库定义语言

库的管理
一、创建库
create database 【if not exists】库名【 character set 字符集名】;
二、修改库
alter database 库名 character set 字符集名;
三、删除库
drop database 【if exists】库名;

修改库名
需要先停止服务：net stop mysql
到mysql server 的安装目录：C:\ProgramData\MySQL\MySQL Server 5.6\data 找到数据库对应的文件夹，重命名
重启服务：net start mysql

表的管理
一、创建表 ★
create table 【if not exists】表名(
字段名字段类型【约束】,
字段名字段类型【约束】,
。。。
字段名字段类型【约束】

)
二、修改表
1.添加列
alter table 表名 add column 列名类型【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名新类型【新约束】;
3.修改列名
alter table 表名 change column 旧列名新列名类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】新表名;
三、删除表
drop table【if exists】表名;
四、复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;

数据类型
一、数值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
②如果超出了范围，会报out or range异常，插入临界值
③长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

2、浮点型
定点数：decimal(M,D)
浮点数:
float(M,D) 4
double(M,D) 8
特点：
①M代表整数部位+小数部位的个数，D代表小数部位
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数

二、字符型
char、varchar、binary、varbinary、enum、set、text、blob

char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略

三、日期型
year年
date日期
time时间
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

常见约束
一、常见的约束
NOT NULL：非空，该字段的值必填
UNIQUE：唯一，该字段的值不可重复
DEFAULT：默认，该字段的值不用手动插入有默认值
CHECK：检查，mysql不支持
PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
FOREIGN KEY：外键，该字段的值引用了另外的表的字段

主键、外键、唯一约束会自动生成索引。

添加列级约束：
语法：直接在字段名和类型后面追加约束类型即可。
只支持：默认、非空、主键、唯一。

添加表级约束：
语法：在各个字段的最下面写
支持：主键、外键、唯一、检查。

例：

CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(if)
);

主键和唯一
1、区别：
①、一个表至多有一个主键，但可以有多个唯一
②、主键不允许为空，唯一可以为空
2、相同点
都具有唯一性
都支持组合键，但不推荐
这里写图片描述

外键：
1、用于限制两个表的关系，从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3、主表的被引用列要求是一个key（一般就是主键或唯一键）
4、插入数据，需先插入主表，再插从表；删除数据需先删从表，再删主表

删除数据，先删除从表
可以通过以下两种方式来删除主表的记录
方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

二、创建表时添加约束
create table 表名(
字段名字段类型 not null,#非空
字段名字段类型 primary key,#主键
字段名字段类型 unique,#唯一
字段名字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表（被引用列）

)
注意：
支持类型可以起约束名
列级约束除了外键不可以
表级约束除了非空和默认可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

三、修改表时添加或删除约束
1、非空
添加非空
alter table 表名 modify column 字段名字段类型 not null;
删除非空
alter table 表名 modify column 字段名字段类型 ;
2、默认
添加默认
alter table 表名 modify column 字段名字段类型 default 值;
删除默认
alter table 表名 modify column 字段名字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

四、自增长列
特点：
1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
auto_increment_increment
如果要更改起始值：手动插入值
如果要更改步长：更改系统变量
set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key

一、创建表时设置自增长列
create table 表(
字段名字段类型约束 auto_increment
)
二、修改表时设置自增长列
alter table 表 modify column 字段名字段类型约束 auto_increment
三、删除自增长列
alter table 表 modify column 字段名字段类型约束

视图

#一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';

#二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;


SELECT d.*,m.mx_dep
FROM departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id`;

存储过程

#一、创建存储过程实现传入用户名和密码，插入到admin表中

CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))
BEGIN
    INSERT INTO admin(admin.username,PASSWORD)
    VALUES(username,loginpwd);
END $

#二、创建存储过程实现传入女神编号，返回女神名称和女神电话

CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))

BEGIN
    SELECT b.name ,b.phone INTO NAME,phone
    FROM beauty b
    WHERE b.id = id;

END $
#三、创建存储存储过程或函数实现传入两个女神生日，返回大小

CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
    SELECT DATEDIFF(birth1,birth2) INTO result;
END $
#四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $

CALL test_pro4(NOW(),@str)$
SELECT @str $

#五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神  格式的字符串
如 传入 ：小昭
返回： 小昭 AND 张无忌
DROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
    SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str
    FROM boys bo
    RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
    WHERE b.name=beautyName;


    SET str=
END $

CALL test_pro5('柳岩',@str)$
SELECT @str $



#六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
DROP PROCEDURE test_pro6$
CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT)
BEGIN
    SELECT * FROM beauty LIMIT startIndex,size;
END $

CALL test_pro6(3,5)$

流程控制

/*一、已知表stringcontent
其中字段：
id 自增长
content varchar(20)

向该表插入指定个数的，随机的字符串
*/
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(20)

);
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE startIndex INT;#代表初始索引
    DECLARE len INT;#代表截取的字符长度
    WHILE i<=insertcount DO
        SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26
        SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1）
        INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
        SET i=i+1;
    END WHILE;

END $

CALL test_randstr_insert(10)$