SQL语句练习

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)$
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:设计师小姐姐 返回首页