数据库技术之mysql面试题

目录

 

程序题

⽤⼀条SQL语句查询出每门课都⼤于80分的学⽣姓名

查询比昨天温度高的id

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

行转列

简答题

---

程序题

⽤⼀条SQL语句查询出每门课都⼤于80分的学⽣姓名

-- 建表语句
​
CREATE TABLE `mst_stu` (
​
 `name` varchar(255) DEFAULT NULL,
​
 `course` varchar(255) DEFAULT NULL,
​
 `score` int(255) DEFAULT NULL
​
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
​
-- 数据
​
INSERT INTO `mst_stu` VALUES ('张三', '语⽂', 81);
​
INSERT INTO `mst_stu` VALUES ('张三', '数学', 75);
​
INSERT INTO `mst_stu` VALUES ('李四', '语⽂', 76);
​
INSERT INTO `mst_stu` VALUES ('李四', '数学', 90);
​
INSERT INTO `mst_stu` VALUES ('王五', '语⽂', 81);
​
INSERT INTO `mst_stu` VALUES ('王五', '数学', 100);
​
INSERT INTO `mst_stu` VALUES ('王五', '英语', 90);

- 互斥思维：每门课都大于80，转化为某学生的最低成绩需要大于80
​
- 1.先根据学员分组，看每个人的最低分
SELECT name,min(score) FROM mst_stu GROUP BY name
​
- 2.再使用分组过滤 having 筛选出最低分大于80
SELECT name,min(score) FROM mst_stu GROUP BY name HAVING min(score)>80;

- 答案
SELECT name FROM mst_stu GROUP BY name HAVING min(score)>80;
​

 

查询比昨天温度高的id

​

查找与之前（昨天的）⽇期相⽐温度更⾼的所有⽇期的 Id。

​

-- 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, '2022-04-01', 20);
​
INSERT INTO `mst_weather` VALUES (2, '2022-04-02', 25);
​
INSERT INTO `mst_weather` VALUES (3, '2022-04-03', 21);
​
INSERT INTO `mst_weather` VALUES (4, '2022-04-04', 24);

- 这种表内做比较的,需要将表进行自联结
SELECT s1.id
from mst_weather as s1
join mst_weather as s2
on datediff(s1.date,s2.date)=1 and 
s1.temperature >s2.temperature;

 

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

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

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

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%');

--  使用case-when进行行转列
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;
​
-- 使用if进行行转列
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;
​

 

怎么把这样⼀个表

查成这样⼀个结果

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);

select year,
max(if( month='1',amount,0)) as 'm1',
max(if(month=2,amount,0)) as 'm2',
max(if( month=3,amount,0)) as 'm3',
max(if (month=4,amount,0)) as 'm4'
from mst_year
GROUP BY year; 

 

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

这道题的SQL语句怎么写？

-- 先查询出哪个key符合要求
select mst_a.key,mst_a.value from mst_a join mst_b on mst_a.key = mst_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
    ) as b
set up.value = b.value where up.key = b.key
​

注意事项

• update 后面是可以进行任何查询语句,这个作用等同于 from

• update 更新表,不能在set和where中用于子查询

• update 也可以对多个表进行更新 (sqlserver不行)

设计表，关系如下：教师、班级、学⽣、科室、科室与教师为⼀对多关系，教师与班级为多对多关系，班级与学⽣为⼀对多关系，科室中需体现层级关系。

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

教师 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);

 

根据上述表关系

查询教师id=4的学⽣数

-- 已知教师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);

查询科室id=2的下级部⻔数

-- 科室id=3的下级部⻔的pid也就等于科室id,因此查询当前科室的⽗级id = 2的即可
​
select count(*) from mst_ks where pid = 2;

查询所带学⽣最多的教师id

-- 先查询出每个⽼师带的学⽣数
​
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)，请计算每个⼈得消费⾦额、消费频次、购买产品数量、第⼀次购买时间和最后⼀次购买时间。

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');

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

create table `mst_OrderDetail`(`OrderDetailID` int PRIMARY KEY AUTO_INCREMENT,`OrderID` int,`SKU` char(5),`Qutity` int);
​
insert into mst_OrderDetail values(1,1,'SKU1',1),(2,2,'SKU1',2),(3,2,'SKU2',1),(4,3,'SKU2',2),(5,3,'SKU3',2),(6,4,'SKU6',1),(7,5,'SKU4',2);

(3)请结合Orderinfo表与OrderDetail表，找出购买了SKU1⼜购买SKU2产品的⼈。

​

现有⼀张房源表。

字段有房源基础信息以及⼩区id，⼩区name字段。

求sql查出top10的房源量的⼩区，按房源量降序排序。

select ⼩区id,⼩区name,count(*) as 房源量 from 房源表 group by ⼩区id,⼩区name order by 房源量 desc limit 10;

简答题

说⼀下你熟悉的数据库，这个数据库有什么特点？

mysql 关系型数据库 开源中最常⽤的数据库,有多种表引擎,⽀持事务,⽀持表关系 redis ⾮关系型数据库,键值对的存储⽅式,并且数据存储在内存中,有多种不同的数据类型 mongodb ⾮关系型数据库,基于分布式⽂件存储的数据库,是nosql中最像关系型数据库的

请⽤sql描述，如何给表中插⼊数据？如何更新数据？ insert into 表名(字段列表) values(参数列表) update 表名 set 字段 = 新值,.... where ....

你常⽤的mysql引擎有哪些？各引擎间有什么区别？ myisam 存储由三个⽂件组成,⼀个存储表结构,⼀个存储数据,⼀个存储索引 不⽀持事务,⾮聚簇索引 innodb 存储由两个⽂件组成,⼀个存储表结构,⼀个存储数据和索引 ⽀持事务,主索引是聚簇索引,并且表中始终存在主键(即便在建表是没有创建主键)

 

MySQL数据库中现有如下表，名为tbl:

 

按照要求写出SQL语句:

(1)查询前3条纪录。

limit 3

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

order by age desc

请描述MySQL中left join和inner join的区别。 join 和 inner join 都是⼀样的,会连接两个表中存在关系

字段的数据 和 where的关联查询⼀样 left join 和 right join⼀样 都是以左表(或右表)数据为基准,去查询

另外⼀个表的数据, 如果没有对应字段的数据,则补充为null

请写出你知道MySQL储存引擎和区别。

请列出你最常使⽤的mysql版本，mysql默认端⼝号是多少？请写出你最常⽤的mysql数据库备份和恢复

命令。 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中的字段进⾏增删改查。如何授权？ grant select,insert,update,delete on B.*,A.c,A.d to

'zhansgan'@'%' identifified by '123456'

对于处理⾼复杂sql，数据库查询特别慢，你有什么⾼招？ 复杂查询简单化 不⽤嵌套,不⽤join,多表查询

拆分 针对语句的查询字段 设计索引 给经常作为where条件的字段添加索引 必要时添加 联合索引

以下哪条语句在数据库sql书写时最为⾼效( ) B

A.select * from emp where dep>3

B.select * from emp where dep>=4

C.select * from emp where dep≥4

D.select * from emp where dep>=3 and dep≠4

数据库中执⾏删除操作，若要删除表中的所有⾏，建议使⽤( ) B

A.delete 语句

B.turncate 语句

C.drop 语句

D.commit 语句

2在查询语句的select⼦句中尽量避免使⽤__来表示全部列名。 *

SQL中增加、删除、修改对应的命令是什么？

查找条件为：姓名不是NULL的纪录( ) C

A.WHERE NAME ! NULL

B.WHERE NAME NOT NULL

C.WHERE NAME IS NOT NULL

D.WHERE NAME!=NULL

在SQL语⾔中，⼦查询是( ) D

A.选取单表中字段⼦集的查询语句

B.选取多表中字段⼦集的查询语句

C.返回单表中数据⼦集的查询语⾔

D.嵌⼊到另⼀个查询语句之中的查询语句

以下能够删除⼀列的是( ) B

A.alter table emp remove addcolumn

B.alter table emp drop column addcolumn

C.alter table emp delete column addcolumn

D.alter table emp delete addcolumn

学⽣关系模型S(S#,Sname,Sex,Age),S的属性分别表示学⽣的学号、姓名、性别、年龄。要在表S中删除

⼀个属性”年龄”,可选⽤的SQL语句是( ) D

A.UPDATE S Age

B.DELETE Age from S

C.ALTER TABLE S ‘Age’

D.ALTER TABLE S DROP Age

补全语句:select vend_id,count(*) as num_prods from products group by____;

SELECT distinct 部⻔名称，count(sid)，sum(个⼈⼯资) FROM ⼯资表 GROUP BY 部⻔名称 having

count (sid) > 10;

查询结果是___。

⽤SELECT进⾏模糊查询时，可以使⽤匹配符，但要在条件值中使⽤__或%等通配符来配合查询。

MySQL是⼀种(多⽤户、单⽤户)的数据库管理系统。

对⼀个超过200个汉字的内容，应⽤⼀个型的字段来存放。

char 定⻓ ⻓度范围 0-255 超出指定⻓度⽆法存储,

varchar 变⻓ ⻓度范围 0-65535 超出指定⻓度⽆法存储

text ⽂本 ⻓度范围 0-65535 不需要指定⻓度 text和以上两个字段的最⼤区别就是 text只能使⽤前缀索引

add index index_name(text(10))

 

查看当前数据库中表名语句是_____;

Mysql数据库GD2312、utf8\utf8mb4字符集的区别___。

GD2312 收录了6763个汉字,其它⽂字符号682个,兼容ASCII字符 占⽤空间: 如果是ASCII字符,则占⼀个字节编码 如果是否其它则采⽤2个字节编码

UTF8 收录地球上能想到的所有字符，⽽且还在不断扩充,同时兼容ASCII字符 存储空间为 1-4个字节

• utf8mb3 ：阉割过的 utf8`字符集，只使⽤1～3个字节表示字符。

• utf8mb4 ：正宗的 utf8 字符集，使⽤1～4个字节表示字符。