前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL 系列教程之(十五)SQL 面试题精讲

MySQL 系列教程之(十五)SQL 面试题精讲

原创
作者头像
ruochen
修改2021-08-18 10:35:03
45.9K0
修改2021-08-18 10:35:03
举报
文章被收录于专栏:若尘的技术专栏

用一条SQL语句查询出每门课都大于80分的学生姓名

name

kecheng

fenshu

张三

语文

81

张三

数学

75

李四

语文

76

李四

数学

90

王五

语文

81

王五

数学

100

王五

英语

90

代码语言:txt
复制
-- mst_Weather 
CREATE TABLE `mst_weather` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `temperature` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
INSERT INTO `mst_weather` VALUES (1, '2013-04-01', 20);
INSERT INTO `mst_weather` VALUES (2, '2013-04-02', 25);
INSERT INTO `mst_weather` VALUES (3, '2013-04-03', 21);
INSERT INTO `mst_weather` VALUES (4, '2013-04-04', 24);
-- 当前表做join,比较日期同时要比较温度
select s1.id,s1.date from mst_Weather s1 
join mst_Weather s2 
on datediff(s1.date,s2.date) = 1 and s1.temperature > s2.temperature;

查询每个主播的最大level以及对应的最小gap(注意:不是每个主播的最大level和最小gap)

zhuobo_id

level

gap

123

8

20

123

9

40

123

9

30

246

6

30

246

6

20

代码语言:txt
复制
CREATE TABLE `mst_zhubo` (
  `zhubo_id` int(11) NOT NULL,
  `level` int(255) DEFAULT NULL,
  `gap` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `mst_zhubo` VALUES (123, 8, 20);
INSERT INTO `mst_zhubo` VALUES (123, 9, 40);
INSERT INTO `mst_zhubo` VALUES (123, 9, 30);
INSERT INTO `mst_zhubo` VALUES (246, 6, 30);
INSERT INTO `mst_zhubo` VALUES (246, 6, 20);
--先查询出每个主播最大的level
select zhubo_id,max(level) from mst_zhubo GROUP BY zhubo_id;
-- 再查询出每个主播所有符合最大level的数据
select * from mst_zhubo where (zhubo_id,level) in (select zhubo_id,max(level) from mst_zhubo GROUP BY zhubo_id) ;
-- 再查询当前符合条件的数据中 gap最小的数据
select zhubo_id,level,min(gap) 
from mst_zhubo where (zhubo_id,level) in (select zhubo_id,max(level) from mst_zhubo GROUP BY zhubo_id)
GROUP BY zhubo_id,level;

下表是每个课程class_id对应的年级(共有primary、middle、high三个),以及某种比率rate

class_id

grade

rate

abc123

primary

70%

abc123

middle

65%

abc123

high

72%

hjkk86

primary

69%

hjkk86

middle

63%

hjkk86

high

74%

请写出SQL查询出如下形式的表:

class_id

primary

middle

high

abc123

70%

65%

72%

hjkk86

69%

63%

74%

代码语言:txt
复制
CREATE TABLE `mst_class` (
  `class_id` varchar(255) NOT NULL,
  `grade` varchar(255) DEFAULT NULL,
  `rate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `mst_class` VALUES ('abc123', 'primary', '70%');
INSERT INTO `mst_class` VALUES ('abc123', 'middle', '65%');
INSERT INTO `mst_class` VALUES ('abc123', 'high', '72%');
INSERT INTO `mst_class` VALUES ('hjkk86', 'primary', '69%');
INSERT INTO `mst_class` VALUES ('hjkk86', 'middle', '63%');
INSERT INTO `mst_class` VALUES ('hjkk86', 'high', '74%');
-- 按照class_id进行分组,由于使用分组,则必须使用聚合函数,因此此处使用max()函数进行即可,然后使用case...when....then 进行行转列
select class_id,
max(CASE  WHEN grade = 'primary' THEN rate ELSE 0 END) as 'primary',
max(CASE  WHEN grade = 'middle' THEN rate ELSE 0 END) as 'middle',
max(CASE  WHEN grade = 'high' THEN rate ELSE 0 END) as 'high'
from mst_class group by class_id;

select class_id,
max(IF(grade = 'primary',rate,0) )as 'primary',
max(IF(grade = 'middle',rate,0)) as 'middle',
max(IF(grade = 'high',rate,0)) as 'high'
from mst_class 
GROUP BY class_id;

怎么把这样一个表

year

month

amount

1991

1

1

1991

2

2

1991

3

3

1991

4

4

1992

1

1

1992

2

2

1992

3

3

1992

4

4

查成这样一个结果

year

m1

m2

m3

m4

1991

1

2

3

4

1992

1

2

3

4

代码语言:txt
复制
CREATE TABLE `mst_year` ( 
    `year` int,`month` int,`amount` int 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `mst_year` VALUES (1991, 1, 1);
INSERT INTO `mst_year` VALUES (1991, 2, 2);
INSERT INTO `mst_year` VALUES (1991, 3, 3);
INSERT INTO `mst_year` VALUES (1991, 4, 4);
INSERT INTO `mst_year` VALUES (1992, 1, 1);
INSERT INTO `mst_year` VALUES (1992, 2, 2);
INSERT INTO `mst_year` VALUES (1992, 3, 3);
INSERT INTO `mst_year` VALUES (1992, 4, 4);


-- 同上一个问题类似,按照 year 进行分组,使用case...when....then 进行行转列,
select year,
sum(CASE  WHEN month = 1 THEN amount ELSE 0 END) as 'm1',
sum(CASE  WHEN month = 2 THEN amount ELSE 0 END) as 'm2',
sum(CASE  WHEN month = 3 THEN amount ELSE 0 END) as 'm3',
sum(CASE  WHEN month = 4 THEN amount ELSE 0 END) as 'm4'
from mst_year group by year;

有两个表A和B,均有key和value两个字段,如果B的key在A中也有,就把B的value换为A中对应的value

这道题的SQL语句怎么写?

代码语言:txt
复制
-- 先按题设计AB表,并查看结果
CREATE TABLE `mst_a` (  `key` varchar(10),`value` varchar(10));
INSERT INTO `mst_a` VALUES ('A', 'aaa');
INSERT INTO `mst_a` VALUES ('B', 'bbb');
INSERT INTO `mst_a` VALUES ('C', 'ccc');
CREATE TABLE `mst_b` (  `key` varchar(10),`value` varchar(10));
INSERT INTO `mst_b` VALUES ('D', 'ddd');
INSERT INTO `mst_b` VALUES ('E', 'eee');
INSERT INTO `mst_b` VALUES ('A', 'abc');


-- 注意事项:
-- update 后面可以做任意的查询,这个作用等同于from
-- update 时,更新的表不能在set和where中用于子查询
-- update 时,可以对多个表进行更新(sqlserver不行)

-- update mst_b set value = ? where key = ?
-- update mst_a as up,(?)b set value = up.value where key = b.key

update mst_b as up,(
    select mst_a.key,mst_a.value
    from mst_a join mst_b on mst_a.key = mst_b.key;
) b 
set up.value=b.value where up.key = b.key

设计表,关系如下:教师、班级、学生、科室、科室与教师为一对多关系,教师与班级为多对多关系,班级与学生为一对多关系,科室中需体现层级关系。

1.写出各张表的逻辑字段

代码语言:txt
复制
教师 mst_Teacher
+-----+-----------+------+
| Tid | Tname     | Kid  |
+-----+-----------+------+
|   1 | 王老师    |    1 |
|   2 | 张老师    |    2 |
|   3 | 孙老师    |    3 |
|   4 | 李老师    |    3 |
|   5 | 伊老师    |    4 |
+-----+-----------+------+
CREATE TABLE `mst_teacher` (
  `Tid` int PRIMARY KEY AUTO_INCREMENT,
  `Tname` varchar(10),
  `Kid` int
  );
insert into mst_teacher VALUES(1,'王老师',1),(2,'张老师',2),(3,'孙老师',3),
    (4,'李老师',3),(5,'伊老师',4);
班级 mst_cla
+-----+-------+
| Cid | Cname |
+-----+-------+
|   1 | 1班   |
|   2 | 2班   |
|   3 | 3班   |
+-----+-------+
CREATE TABLE `mst_cla` (
  `Cid` int PRIMARY KEY AUTO_INCREMENT,
  `Cname` varchar(10)
  );
insert into mst_cla VALUES(1,'1班'),(2,'2班'),(3,'3班');
教师&班级  mst_tc
+----+------+------+
| id | Tid  | Cid  |
+----+------+------+
|  1 |    3 |    1 |
|  2 |    3 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    1 |
|  5 |    4 |    2 |
|  6 |    4 |    3 |
+----+------+------+
CREATE TABLE `mst_tc` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `Tid` int,`Cid` int 
  );
insert into mst_tc VALUES(1,3,1),(2,3,2),(3,3,3),(4,4,1),(5,4,2),(6,4,3);
学生 mst_St        
+-----+--------+------+
| SId | Sname  | Cid  |
+-----+--------+------+
|   1 | 赵雷   |    1 |
|   2 | 钱电   |    1 |
|   3 | 孙风   |    1 |
|   4 | 李云   |    2 |
|   5 | 周梅   |    2 |
|   6 | 吴兰   |    3 |
|   7 | 郑竹   |    3 |
+-----+--------+------+
CREATE TABLE `mst_St` (
  `SId` int PRIMARY KEY AUTO_INCREMENT,
  `Sname` varchar(20),`Cid` int 
  );
insert into mst_St VALUES(1,'赵雷',1),(2,'钱电',1),(3,'孙风',1),(4,'李云',2),(5,'周梅',2),(6,'吴兰',3),(7,'郑竹',3);

科室 mst_ks
+-----+-------------+------+
| Kid | Kname       | Pid  |
+-----+-------------+------+
|   1 | 校长室      |    0 |
|   2 | 教学处      |    1 |
|   3 | ui办公室    |    2 |
|   4 | h5办公室    |    2 |
+-----+-------------+------+
CREATE TABLE `mst_ks` (
  `Kid` int PRIMARY KEY AUTO_INCREMENT,
  `Kname` varchar(20),`Pid` int 
  );
insert into mst_ks VALUES(1,'校长室',0),(2,'教学处',1),(3,'ui办公室',2),(4,'h5办公室',2);

根据上述表关系

代码语言:txt
复制
查询教师id=4的学生数
代码语言:txt
复制
-- 已知教师Id就可以先到教师与班级的关系表中获取当前老师所带的班级
select cid from mst_tc where Tid = 4;
-- 获取所带班级后,到学生表中获取所带班级中的学员数即可
select count(*) from mst_St where Cid in (select cid from mst_tc where Tid = 4);
代码语言:txt
复制
查询科室id=2的下级部门数
代码语言:txt
复制
-- 科室id=3的下级部门的pid也就等于科室id,因此查询当前科室的父级id = 2的即可
select count(*) from mst_ks where pid = 2;
代码语言:txt
复制
查询所带学生最多的教师id
代码语言:txt
复制
-- 先查询出每个老师带的学生数
select t.tid,t.tname,count(stu.sid)
from mst_teacher as t join mst_tc  on t.tid = mst_tc.tid 
join mst_st as stu on stu.cid  = mst_tc.cid
group by t.tid,t.tname;

+-----+-----------+----------------+
| tid | tname     | count(stu.sid) |
+-----+-----------+----------------+
|   3 | 孙老师    |              7 |
|   4 | 李老师    |              5 |
+-----+-----------+----------------+

-- 在以上结果的基础上,按照学生数排序,取一个
select t.tid,t.tname,count(stu.sid) as num
from mst_teacher as t join mst_tc  on t.tid = mst_tc.tid 
join mst_st as stu on stu.cid  = mst_tc.cid
group by t.tid,t.tname order by num desc limit 1;

(1)某奶粉品牌有以下销售数据(订单表Orderinfo),请计算每个人得消费金额、消费频次、购买产品数量、第一次购买时间和最后一次购买时间。

CustomerID

OrderID

Sales

Quantity

OrderDate

A

01

100

1

2017-03-01

A

02

420

3

2017-03-15

B

03

300

4

2017-03-02

B

04

1000

1

2017-04-01

C

05

500

3

2017-05-03

C

06

200

1

2017-05-04

……

代码语言:txt
复制
create table `mst_Orderinfo`(`CustomerID` char(5),`OrderID` int PRIMARY KEY AUTO_INCREMENT,`Sales` int,`Quantity` int,`OrderDate` date);
insert INTO mst_Orderinfo VALUES
    ('A',01,100,1,'2017-03-01'),
    ('A',02,420,3,'2017-03-15'),
    ('B',03,300,4,'2017-03-02'),
    ('B',04,1000,1,'2017-04-01'),
    ('C',05,500,3,'2017-05-03'),
    ('C',06,200,1,'2017-05-04');
--计算每个人得消费金额、消费频次、购买产品数量、第一次购买时间和最后一次购买时间。
select CustomerID,
sum(Sales) as '消费金额',
count(CustomerID) as '消费频次',
sum(Quantity) as '购买产品数量',
min(OrderDate) as '第一次购买时间',
max(OrderDate) as '最后一次购买时间'
from mst_Orderinfo group by CustomerID;

(2)该奶粉品牌还有一张订单明细表(OrderDetail ),请结合上题得订单表,计算出每个SKU被多少客户购买了。

OrderDetailID

OrderID

SKU

Qutity

01

01

SKU1

1

02

02

SKU1

2

03

02

SKU2

1

04

03

SKU2

2

05

03

SKU3

2

06

04

SKU6

1

07

05

SKU4

2

……

按照要求写出SQL语句:

(1)查询前3条纪录。

代码语言:txt
复制
limit 3

(2)查询所有纪录,并按照字段age降序排列。

代码语言:txt
复制
order by age desc 

请描述MySQL中left join和inner join的区别。

代码语言:txt
复制
join 和 inner join 都是一样的,会连接两个表中存在关系字段的数据,和 where的关联查询一样
left join 和 right join一样都是以左表(或右表)数据为基准,去查询另外一个表的数据,如果没有对应字段的数据,则补充为null

请列出你最常使用的mysql版本,mysql默认端口号是多少?请写出你最常用的mysql数据库备份和恢复命令。

代码语言:txt
复制
 5.7, 
 端口 3306 
 127.0.0.1 localhost  本机的IP

--数据导入

mysql -uroot -p < F:\mysql-5.7.27-winx64\bf\ops.sql 

--数据备份 导出

mysqldump -uroot -p -B -F -R -x --master-data=2 ops >F:\mysql-5.7.27-winx64\bf\ops.sql

-----------------
参数说明:
-B:指定数据库
-F:刷新日志
-R:备份存储过程等
-x:锁表
--master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

数据库A:datahn,其中具有表c、表d。 数据库B:datapubhn,现想要在数据库B中对数据库A中表c与表d中的字段进行增删改查。如何授权?

代码语言:txt
复制
 grant select,insert,update,delete on B.*,A.c,A.d to 'zhansgan'@'%' identified by '123456'

对于处理高复杂sql,数据库查询特别慢,你有什么高招?

代码语言:txt
复制
复杂查询简单化
   不用嵌套,不用join,多表查询拆分
针对语句的查询字段 设计索引
   给经常作为where条件的字段添加索引 
   必要时添加 联合索引
  • 以下哪条语句在数据库sql书写时最为高效( ) B
代码语言:txt
复制
A.select * from emp where dep>3
代码语言:txt
复制
B.select * from emp where dep>=4
代码语言:txt
复制
C.select * from emp where dep≥4
代码语言:txt
复制
D.select * from emp where dep>=3 and dep≠4
  • 数据库中执行删除操作,若要删除表中的所有行,建议使用( ) B
代码语言:txt
复制
A.delete 语句
代码语言:txt
复制
B.turncate 语句
代码语言:txt
复制
C.drop 语句
代码语言:txt
复制
D.commit 语句
  • 在查询语句的select子句中尽量避免使用__来表示全部列名。 *
  • 查找条件为:姓名不是NULL的纪录( ) C
代码语言:txt
复制
A.WHERE NAME ! NULL
代码语言:txt
复制
B.WHERE NAME NOT NULL
代码语言:txt
复制
C.WHERE NAME IS NOT NULL
代码语言:txt
复制
D.WHERE NAME!=NULL
  • 在SQL语言中,子查询是( ) D
代码语言:txt
复制
A.选取单表中字段子集的查询语句
代码语言:txt
复制
B.选取多表中字段子集的查询语句
代码语言:txt
复制
C.返回单表中数据子集的查询语言
代码语言:txt
复制
D.嵌入到另一个查询语句之中的查询语句
  • 以下能够删除一列的是( ) B
代码语言:txt
复制
A.alter table emp remove addcolumn
代码语言:txt
复制
B.alter table emp drop column addcolumn
代码语言:txt
复制
C.alter table emp delete column addcolumn
代码语言:txt
复制
D.alter table emp delete addcolumn
  • 学生关系模型S(S#,Sname,Sex,Age),S的属性分别表示学生的学号、姓名、性别、年龄。要在表S中删除一个属性”年龄”,可选用的SQL语句是( ) D
代码语言:txt
复制
A.UPDATE S Age
代码语言:txt
复制
B.DELETE Age from S
代码语言:txt
复制
C.ALTER TABLE S ‘Age’
代码语言:txt
复制
D.ALTER TABLE S DROP Age
  • 对一个超过200个汉字的内容,应用一个____型的字段来存放。
代码语言:txt
复制
 char 
代码语言:txt
复制
    定长  长度范围 0-255 超出指定长度无法存储,
代码语言:txt
复制
 varchar
代码语言:txt
复制
    变长  长度范围 0-65535 超出指定长度无法存储
代码语言:txt
复制
 text
代码语言:txt
复制
    文本 长度范围 0-65535 不需要指定长度 
代码语言:txt
复制
    text和以上两个字段的最大区别就是 text只能使用前缀索引
  • Mysql数据库GD2312、utf8\utf8mb4字符集的区别___。 GD2312 收录了6763个汉字,其它文字符号 682个,兼容ASCII字符 占用空间: 如果是ASCII字符,则占一个字节编码 如果是否其它则采用2个字节编码
代码语言:txt
复制
UTF8
代码语言:txt
复制
    收录地球上能想到的所有字符,而且还在不断扩充,同时兼容ASCII字符
代码语言:txt
复制
    存储空间为 1-4个字节
代码语言:txt
复制
- utf8mb3`:阉割过的`utf8`字符集,只使用1~3个字节表示字符。
- `utf8mb4`:正宗的`utf8`字符集,使用1~4个字节表示字符。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档