navicat使用+SQL校招题目笔记

目录

一、 navicat连接数据库与表的建立

二、 校招SQL题目

B：对表进行拆分合并（结构重组）

---

语法参考：

(1条消息) SQL基本语法入门 看这里就够了_Mr_Fei的博客-CSDN博客_sql基本语法基础语法

题目参考：

互联网校招SQL笔试经典50题及答案解析 - 知乎 (zhihu.com)本博客答案很多与这个里面的思路不同，可以参考不同思路

一、 navicat连接数据库与表的建立

安装激活好了之后首先连接MYSQL

 随便起一个用户名，然后输入安装MYSQL时设置的密码

连接成功之后双击会变绿，点击新建数据库，字符集选择utf8的

 

 数据库建立成功双击会变绿，而且可以有下拉列表。

可以点击表，新建表，通过表格操作录入表信息，也可以直接打开命令列界面

 这里以学生表为例，输入语句直接建立表格

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
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' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

--学生表Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--课程表Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号

--教师表Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名

--成绩表SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数

二、 校招SQL题目

A：使用聚合函数与分组

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

思路：使用avg聚合函数，用group by having对聚合结果做筛选

#1、查询平均成绩大于60分的同学的学号和平均成绩；
select sid, avg(score) as avg_score from sc
group by sid
having avg(score)>60;

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

#3、查询所有同学的学号、姓名、选课数、总成绩
select sc.sid as studentID, sname, count(cid),sum(score)
from sc inner join student on sc.sid=student.sid 
group by sc.sid;

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

#4、查询姓“李”的老师的个数；
select count(distinct tid) as num_li from teacher 
where tname like "李%"

模糊查询通配符，【%】 替代一个或多个字符；【_ 】仅替代一个字符

需要注意有计数的，count要加distinct

4、查询所有课程成绩小于60分的同学的学号、姓名；

错误求解：【错误原因：只要是有课程小于60分就显示了，实际上本题的意思应该是所有课都小于60分，即最高分小于60分】
select distinct student.sid,sname from student
inner join sc on sc.sid=student.sid
where sc.score<60;

select t1.sid, sname from
(select sid, max(score) from sc
group by sid 
having max(score)<60)t1
inner join student on t1.sid=student.sid

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

思路：找到01选的所有课，筛选出来包含这些课的cid的sid，然后求一个distinct

select sid, sname from student where sid in
(select distinct sid from sc where cid in (select cid from sc where sid="01"))

6、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

思路：选择和01相同的课程，然后这些相同课程的书目也相同，所以得到完全相同

#查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
select student.sid, sname from student inner join
(select distinct sid, count(cid)from sc where cid in 
(select cid from sc where sid="01")
group by sid having count(cid)=3)t1 
on t1.sid=student.sid

这里这个思路是有问题的，非常冗杂。这里参考知乎原题给出的答案：

B：对表进行拆分合并（结构重组）

1、查询“01”课程比“02”课程成绩高的所有学生的学号；

思路：将SC表中课程1的分数作为一列，课程2作为一列，然后采用where条件查询就可以

 

  答案：

#1、查询“01”课程比“02”课程成绩高的所有学生的学号；
SELECT t1.sid as sid from
  (select * from sc where cid='01')t1
  inner join (select * from sc where cid='02')t2
  on t1.sid=t2.sid
where t1.score>t2.score;

做错的原因：最上面的查询写成了select sid where，但是这时候已经是在下面连接完成的视图里面查找sid了，必须指明哪个表（即多个表中重复出现的列名需要指明是哪个表，并重命名一下）

值得注意：新建视图并且命名视图，直接参与连接查询的语法，使用（）+命名

2、查询没学过“张三”老师课的同学的学号、姓名

多层嵌套查询，理清楚逻辑就好，注意括号个数

#5、查询没学过“张三”老师课的同学的学号、姓名
select sid, sname from student
where sid not in 
(select sc.sid as sid from sc where cid=
(select cid from course where tid=
(select tid from teacher where tname="张三")))

3、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名

思路：筛选所有01，02课程号，按照学号分组，count学号个数=2，然后在和学生表连接

#查询学过“01”并且也学过编号“02”课程的同学的学号、姓名
select distinct student.sid, sname, count(t1.cid)from student inner join 
(select sid,cid from sc
where cid="01"or cid="02")t1
on student.sid=t1.sid
group by student.sid
having count(t1.cid)=2

注意点：where cid="01"or"02")跑出来的结果就只有01，这种or的条件句要注意并列的是查询条件，并不是等号右边的结果

4、查询学过“张三”老师所教的课的同学的学号、姓名；

#7、查询学过“张三”老师所教的课的同学的学号、姓名；
select sid, sname from student where sid in
(select sid from sc where cid in 
(select cid from course where tid in
(select tid from teacher where tname="张三")))

注意点：查询结果是多行的一定要注意where条件跟的不是【=】而是【 in】

5、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名

#8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
select student.sid, sname from student where sid in
(select t1.sid from
(select score, sid from sc where cid="01")t1
inner join (select score, sid from sc where cid="02")t2
on t1.sid=t2.sid
where t1.score<t2.score)

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

没选全所有课程：

having count(cid)<max(count(cid))错了，having count(cid)<max(count(cid))也错了

正确写法：

#查询没有学全所有课的同学的学号、姓名
select student.sid, sname from student inner join
(select sid, count(cid) from sc 
group by sid having count(cid)<(select count(distinct cid)from sc))t1 
on student.sid=t1.sid