mysql题目 以下语句错误的是什么意思_MySQL经典50题练习常见错误（1-10题）

表头和字段

这是一个简单的数据库表结构

–1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

–2.课程表

Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

–3.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

–4.成绩表

Score(s_id,c_id,s_score) –学生编号,课程编号,分数

建测试数据表

--建表

--学生表

CREATE TABLE `Student`(

`s_id` VARCHAR(20),

`s_name` VARCHAR(20) NOT NULL DEFAULT '',

`s_birth` VARCHAR(20) NOT NULL DEFAULT '',

`s_sex` VARCHAR(10) NOT NULL DEFAULT '',

PRIMARY KEY(`s_id`)

);

--课程表

CREATE TABLE `Course`(

`c_id` VARCHAR(20),

`c_name` VARCHAR(20) NOT NULL DEFAULT '',

`t_id` VARCHAR(20) NOT NULL,

PRIMARY KEY(`c_id`)

);

--教师表

CREATE TABLE `Teacher`(

`t_id` VARCHAR(20),

`t_name` VARCHAR(20) NOT NULL DEFAULT '',

PRIMARY KEY(`t_id`)

);

--成绩表

CREATE TABLE `Score`(

`s_id` VARCHAR(20),

`c_id` VARCHAR(20),

`s_score` INT(3),

PRIMARY KEY(`s_id`,`c_id`)

);

建表错误回顾：这里的表名和字段名是不需要引号的

然后就是注意在设置字段类型时注意选择合适的类型，这个表中的时间字段也用了varchar类型，如果做一些筛选需求的时候就需要转换类型了

最后记得每个表都要设置主键的：PRIMARY KEY()

有的字段在插入数据时没有数据，在设置字段时用NOT NULL DEFAULT '' 可以自动补充null数据

插入数据

--插入学生表测试数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

--教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--成绩表测试数据

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

插入数据错误回顾：插入数据的语法是： INSERT INTO table VALUES()

插入每条数据时，数值类型不需要用引号，文本格式数据需要用引号

下面进入测试题：

在写SQL语句的时候使用#非常容易就可以注释掉一行，来添加备注或不运行这行语句，如果要注释掉一块可以使用/*这里是SQL语句块*/，也可以选中要运行的语句右键然后运行选中语句，这样非常容易能查找语句的错误

#1、查询’01‘课程比’02‘课程成绩低的学生的信息及课程分数

SELECT

a.*, b.s_score AS 01_score,

c.s_score AS 02_score

FROM

student AS a

LEFT JOIN score AS b ON a.s_id = b.s_id

AND b.c_id = '01'

OR b.c_id is NULL #感谢 @卢华超 的提醒把 = NULL修改为is NULL

JOIN score AS c ON a.s_id = c.s_id

AND c.c_id = '02'

WHERE

b.s_score < c.s_score

第一题常见错误：这一题的思路是在学生表后添加01课程和02课程的成绩，然后再比较01课程和02课程的成绩

容易犯的错误是没有考虑到null的情况

在这题中参考答案上SQL函数都用的小写，虽然SQL不区分大小写，但日常练习我都写成大写，方便读语句与纠错

然后参考答案中给表起别名的时候忽略了as函数，虽然是可以的，但日常书写我都加上as

同时书写语句时最好不要一行从头写到位，关键词换行会更方便阅读

2、#查询’01‘课程比’02‘课程成绩高的学生信息及课程分数

SELECT

a.*, b.s_score AS 01_score,

c.s_score AS o2_score

FROM

student AS a

JOIN score AS b ON a.s_id = b.s_id

AND b.c_id = '01'

LEFT JOIN score AS c ON a.s_id = c.s_id

AND c.c_id = '02'

OR c.c_id = NULL

WHERE

b.s_score > c.s_score

第二道题与第一道题是一个思路表的连接这里用了内连接JOIN与左连接LEFT JOIN，FROM中的表用逗号连接与JION是一样的效果

3、#查询平均成绩大于等于60分的同学的学生编号和学生姓名及平均成绩

SELECT

b.s_id,

s_name,

ROUND(AVG(a.s_score), 2) AS avg_score

FROM

student AS b

JOIN score AS a ON b.s_id = a.s_id

GROUP BY

b.s_id

HAVING

avg_score >= 60;

#下面是参考答案

SELECT

b.s_id,

b.s_name,

ROUND(AVG(a.s_score), 2) AS avg_score

FROM

student b

JOIN score a ON b.s_id = a.s_id

GROUP BY

b.s_id,

b.s_name

HAVING

ROUND(AVG(a.s_score), 2) >= 60;

回顾：第三题我对参考答案做了优化，最后HAVING的条件判断因为在SELECT中已经列出了平均数并起了别名，我直接用别名avg_score >= 60进行了条件判断

关键词的运行顺序是FROM-->GROUP BY-->SELECT-->HAVING

在这个语句关键词GROUP BY 一个s_id对应一个s_name，优化后的语句只对b.s_id进行分组就可以了

4、#查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩(包含有成绩和无成绩的)

#方法一：HABING条件加or avg_score IS NULL，查询结果中avg_score会是null

SELECT

b.s_id,

b.s_name,

ROUND(AVG(a.s_score), 2) AS avg_score

FROM

student AS b

LEFT JOIN score AS a ON b.s_id = a.s_id

GROUP BY

b.s_id,

b.s_name

HAVING

ROUND(AVG(a.s_score), 2) < 60

OR avg_score IS NULL

#参考答案方法二：用 UNION 连接一个语句合并查询结果

SELECT

b.s_id,

b.s_name,

ROUND(AVG(a.s_score), 2) AS avg_score

FROM

student AS b

LEFT JOIN score AS a ON b.s_id = a.s_id

GROUP BY

b.s_id,

b.s_name

HAVING

ROUND(AVG(a.s_score), 2) < 60

UNION

SELECT

a.s_id,

a.s_name,

0 AS avg_score

FROM

student AS a

WHERE

a.s_id NOT IN (

SELECT DISTINCT

s_id

FROM

score

);

回顾：对参考答案中的UNION后关联的语句进行了优化，在HAVING条件中用OR加了判断条件avg_score IS NULL代替参考答案中UNION语句，这样计算器对原表进行判断就行了，不用再运行一条语句耗费资源，语句短了也更不容易出错

这个语句中注意where与having判断条件的区别，where条件是未对表格进行计算的字段进行筛选，having则是对group by后的表再求了平均数的字段进行筛选

如果这个语句只有select与from得到的表只有一条数据，这条数据中的平均数对所有成绩都进行了平均

对于null的条件判断不能用=或！=，只能用is或is not(注意不是not is)

5、#查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT

b.s_id,

b.s_name,

COUNT(a.s_score) AS sum_course,

SUM(a.s_score) AS sum_score

FROM

student AS b

LEFT JOIN score AS a ON b.s_id = a.s_id

GROUP BY

b.s_id,

b.s_name;

#下面的查询公式是网页上的参考答案

SELECT

a.s_id,

a.s_name,

count(b.c_id) AS sum_course,

sum(b.s_score) AS sum_score

FROM

student a

LEFT JOIN score b ON a.s_id = b.s_id

GROUP BY

a.s_id,

a.s_name;

这个上下语句是一样的，我也用了参考答案中的思路，只不过对表的别名与参考答案不一样

6、#查询’李‘姓老师的数量

SELECT

COUNT(t_id) AS '老师数量'

FROM

teacher

WHERE

t_name LIKE '李%';

这个是很简单的语句，只需要熟悉下like关键词和替换字符的使用，还有其他替换字符‘_’只代表一个字符

8、#查询没学过’张三‘老师授课的同学的信息

第七题与第八题属于一样的，就只列个第八题

SELECT

*

FROM

student

WHERE

s_id NOT IN (

SELECT

a.s_id

FROM

student AS a

LEFT JOIN score AS b ON a.s_id = b.s_id

LEFT JOIN course AS c ON b.c_id = c.c_id

LEFT JOIN teacher AS d ON c.t_id = d.t_id

WHERE

d.t_name = '张三'

GROUP BY

b.s_id

);

#下面的SQL是网站上的参考答案

SELECT

*

FROM

student c

WHERE

c.s_id NOT IN (

SELECT

a.s_id

FROM

student a

JOIN score b ON a.s_id = b.s_id

WHERE

b.c_id IN (

SELECT

c_id

FROM

course

WHERE

t_id = (

SELECT

t_id

FROM

teacher

WHERE

t_name = '张三'

)

)

);

回顾：首先解题思路都是先列出语句找到上过张三老师课的学生，然后再用not in 筛选出其他的学生。在找出上过张三老师课我的语句思路是：把这个学生上过的课都列出表，然后筛选出张三老师课的学生。参考答案的思路是：一步一步求出上张三老师课的学生，先求出张三老师的t_id然后求出张三老师教过的课c_id，再用这几个课从score表中找出上这几个课的学生id，最后再从student表中列出这几个学生的信息

在where条件语句中如果是一个元素使用=符合判断就可以，如果是多个元素就需要使用in函数进行判断，或者not in函数判断。

9、#查询学过编号’01‘并且也学过编号’02‘的课程的同学信息

#我的语法有点瑕疵，多出一个sum_course字段

SELECT

*, COUNT(a.s_id) AS sum_course

FROM

student AS a

LEFT JOIN score AS b ON a.s_id = b.s_id

WHERE

b.c_id = '01'

OR b.c_id = '02'

GROUP BY

a.s_id

HAVING

COUNT(a.s_name) = 2;

#下面是网站的参考答案

#注意在a.s_id = b.s_id

AND a.s_id = c.s_id的条件不能写成a.s_id = b.s_id = c.s_id，这样与下面的不是一个逻辑

SELECT

a.*

FROM

student a,

score b,

score c

WHERE

a.s_id = b.s_id

AND a.s_id = c.s_id

AND b.c_id = '01'

AND c.c_id = '02';

回顾：我的解题思路是：用left join 连接student 与 score，这个时候就有个每个学生的一维成绩表，然后筛选出有01和02课程成绩的数据条，再进行学生分组，对条数计数，计数=2的学生就是需要的内容。参考答案的思路是用join连接了三个表，然后进行条件筛选之后的表就相当于符合条件的二维数组，然后列出这几个学生的student数据

参考答案中的注意在a.s_id = b.s_id

AND a.s_id = c.s_id的条件不能写成a.s_id = b.s_id = c.s_id，这俩不是一个逻辑

10、#查询学过编号为’01‘但是没有学过编号是’02‘课程的同学信息

SELECT

*

FROM

student AS a

LEFT JOIN score AS b ON a.s_id = b.s_id

AND b.c_id = '01'

LEFT JOIN score AS c ON a.s_id = c.s_id

AND c.c_id = '02'

WHERE

b.c_id = '01'

AND c.c_id IS NULL

#下面是网页参考答案

SELECT

a.*

FROM

student a

WHERE

a.s_id IN (

SELECT

s_id

FROM

score

WHERE

c_id = '01'

)

AND a.s_id NOT IN (

SELECT

s_id

FROM

score

WHERE

c_id = '02'

)

回顾：还是先写解题思路，我把学生参考01和02课程的情况先列出一个表，然后再从表里进行的条件筛选。参考答案是直接在筛选条件中进行了and判断，然后得到的学生ID，之后列出学生信息就OK了

这个里面重要注意的是不等于的判断函数，！=不等于符号因为在不同版本兼容不一样，所以要使用not in 有的时候用is 或is not等这些

not in 判断后不会列出null值，如果要列出null值需要单独进判断