mysql面试题

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

name	kecheng	fenshu
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

-- 建表语句
CREATE TABLE `mst_stu` (
  `name` varchar(255) DEFAULT NULL,
  `kecheng` varchar(255) DEFAULT NULL,
  `fenshu` 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分,先根据用户分组,看每个人的最低分
select name,min(fenshu) from mst_stu group by name;
+--------+-------------+
| name   | min(fenshu) |
+--------+-------------+
| 张三   |          75 |
| 李四   |          76 |
| 王五   |          81 |
+--------+-------------+
-- 在使用 having 筛选出最低分大于80的
select name,min(fenshu) as min_f from mst_stu group by name having min_f > 80;

-- 最终只需要学生姓名
select name from (select name,min(fenshu) as min_f from mst_stu group by name having min_f > 80) as s;

查询后一天 temperature 比前一天高的date

查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

id	date	temperature
1	2013-04-01	20
2	2013-04-02	25
3	2013-04-03	21
4	2013-04-04	24

-- 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 m2.date from mst_weather as m1 join mst_weather as m2 where datediff(m2.date,m1.date) = 1 and m2.temperature > m1.temperature;

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

zhuobo_id	level	gap
123	8	20
123	9	40
123	9	30
246	6	30
246	6	20

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%

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;

怎么把这样一个表

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

CREATE TABLE `mst_year` ( 
    `year` int,`month` int,`amount` float 
) 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 round(amount,1) ELSE 0 END) as 'm1',
sum(CASE  WHEN month = 2 THEN round(amount,1) ELSE 0 END) as 'm2',
sum(CASE  WHEN month = 3 THEN round(amount,1) ELSE 0 END) as 'm3',
sum(CASE  WHEN month = 4 THEN round(amount,1) ELSE 0 END) as 'm4'
from mst_year group by year;

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

这道题的SQL语句怎么写？

-- 先按题设计AB表,并查看结果
CREATE TABLE `mst_a` (  `key` varchar,`value` varchar);
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,`value` varchar);
INSERT INTO `mst_a` VALUES ('D', 'ddd');
INSERT INTO `mst_a` VALUES ('E', 'eee');
INSERT INTO `mst_a` VALUES ('A', 'abc');
-- 查看当前两个表的数据
mysql> select * from mst_a;
+------+-------+
| key  | value |
+------+-------+
| A    | aaa   |
| B    | bbb   |
| C    | ccc   |
+------+-------+
3 rows in set (00 sec)

mysql> select * from mst_b;
+------+-------+
| key  | value |
+------+-------+
| D    | ddd   |
| E    | eee   |
| A    | abc   |
+------+-------+
3 rows in set (00 sec)
-- 有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value

-- 分析,先查询符合条件的数据,(B的key在A中也有)
select mst_a.*,mst_b.* from mst_a join mst_b on mst_a.key = mst_b.key;
+------+-------+------+-------+
| key  | value | key  | value |
+------+-------+------+-------+
| A    | aaa   | A    | abc   |
+------+-------+------+-------+
1 row in set (00 sec)

-- 更新数据 update mst_b set value = ? where key = ?
-- 根据以上的更新语句需要的数据,针对性的获取,先获取第二个问号中的key
select mst_b.key from mst_a join mst_b on mst_a.key = mst_b.key;
+------+
| key  |
+------+
| A    |
+------+
-- 在获取需要更新的值?也就是a表中的对应key的value
select mst_a.value,b.key  
from mst_a,(select mst_b.key from mst_a join mst_b on mst_a.key = mst_b.key) b
where mst_a.key = b.key;
-- 在将两次sql结果嵌套到对应的update中
-- 注意事项:
-- update 后面可以做任意的查询，这个作用等同于from
-- update 时，更新的表不能在set和where中用于子查询
-- update 时，可以对多个表进行更新（sqlserver不行）
update mst_b as up, (select mst_a.value,b.key  
from mst_a,(select mst_b.key from mst_a join mst_b on mst_a.key = mst_b.key) b
where mst_a.key = b.key) b
    set up.value = b.value
    where up.key = b.key;

-- 再次查询结果
mysql> select * from mst_a;
+------+-------+
| key  | value |
+------+-------+
| A    | aaa   |
| B    | bbb   |
| C    | ccc   |
+------+-------+
3 rows in set (00 sec)

mysql> select * from mst_b;
+------+-------+
| key  | value |
+------+-------+
| D    | ddd   |
| E    | eee   |
| A    | aaa   |
+------+-------+
3 rows in set (00 sec)

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

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

教师 mst_Teacher
+-----+--------------+
| Tid | Tname        |
+-----+--------------+
|   1 | 张三老师     |
|   2 | 李四老师     |
|   3 | 王五老师     |
+-----+--------------+
CREATE TABLE `mst_teacher` (
  `Tid` int PRIMARY KEY AUTO_INCREMENT,
  `Tname` varchar(10)
  );
insert into mst_teacher VALUES(1,'张三老师'),(2,'李四老师'),(3,'王五老师');
班级 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
+-----+------+------+
| pid | Tid  | Cid  |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    2 |    1 |
|   3 |    3 |    1 |
|   4 |    1 |    2 |
|   5 |    2 |    2 |
|   6 |    3 |    2 |
|   7 |    1 |    3 |
|   8 |    2 |    3 |
|   9 |    3 |    3 |
CREATE TABLE `mst_tc` (
  `pid` int PRIMARY KEY AUTO_INCREMENT,
  `Tid` int,`Cid` int 
  );
insert into mst_tc VALUES
    (1,1,1),(2,2,1),(3,3,1),(4,1,2),(5,2,2),(6,3,2),(7,1,3),(8,2,3),(9,3,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  | Tid  |
+-----+-----------+------+------+
|   1 | 校长室    |    0 |    1 |
|   2 | 教务处    |    1 |    1 |
|   3 | 教学处    |    1 |    2 |
|   4 | 语文      |    3 |    1 |
|   5 | 数学      |    3 |    2 |
|   6 | 英语      |    3 |    3 |
+-----+-----------+------+------+
CREATE TABLE `mst_ks` (
  `Kid` int PRIMARY KEY AUTO_INCREMENT,
  `Kname` varchar(20),`Pid` int ,`Tid` int 
  );
insert into mst_ks VALUES(1,'校长室',0,1),(2,'教务处',1,1),(3,'教学处',1,2),(4,'语文',3,1),(5,'数学',3,2),(6,'英语',3,3);

根据上述表关系

查询教师id=1的学生数

-- 已知教师Id就可以先到教师与班级的关系表中获取当前老师所带的班级
select cid from mst_tc where Tid = 1;
-- 获取所带班级后,到学生表中获取所带班级中的学员数即可
select count(*) from mst_St where Cid in (select cid from mst_tc where Tid = 1);

查询科室id=3的下级部门数

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

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

-- 先查询出每个老师带的学生数
select mst_teacher.tid,mst_teacher.tname,count(mst_st.sid) as count_stu
from mst_teacher,mst_tc,mst_St
where mst_teacher.tid = mst_tc.tid
and mst_tc.cid = mst_st.cid
group by mst_teacher.tid,mst_teacher.tname;
+-----+--------------+-----------+
| tid | tname        | count_stu |
+-----+--------------+-----------+
|   1 | 张三老师     |         7 |
|   2 | 李四老师     |         7 |
|   3 | 王五老师     |         7 |
+-----+--------------+-----------+
-- 因为目前所有老师带的班级都一样,所以数据结果都是一样的,因此可以改变一个老师所带的科目后在查询,比如语文和数学是一个老师带的,再次查询
select mst_teacher.tid,mst_teacher.tname,count(mst_st.sid) as count_stu
from mst_teacher,mst_tc,mst_St
where mst_teacher.tid = mst_tc.tid
and mst_tc.cid = mst_st.cid
group by mst_teacher.tid,mst_teacher.tname;
+-----+--------------+-----------+
| tid | tname        | count_stu |
+-----+--------------+-----------+
|   2 | 李四老师     |        14 |
|   3 | 王五老师     |         7 |
+-----+--------------+-----------+

(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
……

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
……

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);
-- 分析:如果要统计出每个sku被多少客户购买了.就需要先把两个表做join,观察
select * from mst_Orderinfo o1,mst_OrderDetail o2
where oOrderID = oOrderID;

-- 计算出每个SKU被多少客户购买了,需要以sku分组,去统计用户id并去重
select SKU,count(distinct CustomerID) from mst_Orderinfo o1,mst_OrderDetail o2
where oOrderID = oOrderID
group by SKU;
+------+----------------------------+
| SKU  | count(distinct CustomerID) |
+------+----------------------------+
| SKU1 |                          1 |
| SKU2 |                          2 |
| SKU3 |                          1 |
| SKU4 |                          1 |
| SKU6 |                          1 |
+------+----------------------------+

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

-- 先查询出购买过SKU1和SKU2的用户
select * from mst_Orderinfo o1
join mst_OrderDetail o2 on oOrderID = oOrderID
where oSKU in ('SKU1','SKU2');
+------------+---------+-------+----------+------------+---------------+---------+------+--------+
| CustomerID | OrderID | Sales | Quantity | OrderDate  | OrderDetailID | OrderID | SKU  | Qutity |
+------------+---------+-------+----------+------------+---------------+---------+------+--------+
| A          |       1 |   100 |        1 | 2017-03-01 |             1 |       1 | SKU1 |      1 |
| A          |       2 |   420 |        3 | 2017-03-15 |             2 |       2 | SKU1 |      2 |
| A          |       2 |   420 |        3 | 2017-03-15 |             3 |       2 | SKU2 |      1 |
| B          |       3 |   300 |        4 | 2017-03-02 |             4 |       3 | SKU2 |      2 |
+------------+---------+-------+----------+------------+---------------+---------+------+--------+

-- 在根据用户分组,筛选购买了不同的sku的数等于2 的
select oCustomerID from mst_Orderinfo o1 
join mst_OrderDetail o2 on oOrderID = oOrderID
where oSKU in ('SKU1','SKU2')
GROUP BY oCustomerID
HAVING COUNT(DISTINCT oSKU) = 2
+------------+
| CustomerID |
+------------+
| A          |
+------------+

现有一张房源表。

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

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

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

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

请用sql描述，如何给表中插入数据？如何更新数据？

你常用的mysql引擎有哪些？各引擎间有什么区别？

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

id	name	age	sex
1	zhangsan	25	1
2	lisi	22	0
3	wangwu	35	0
4	zhaoliu	24	1
5	tianqi	32	1

按照要求写出SQL语句:

(1)查询前3条纪录。

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

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

left join 左连接,以左边的表为基础

inner join 自连接\内连接以公共部分为基础

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

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

mysql 3306

mongodb 27017

redis 6379

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

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

1.优化sql语句==避免子查询,避免使用join连接

2.适当使用索引

3.优化数据库结构

4.优化系统配置

5.优化服务器结构

以下哪条语句在数据库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

数据库修改用户时，用户的什么属性不能修改？( )

A.密码

B.名称

C.表空间

D.临时表空间

数据库中执行删除操作，若要删除表中的所有行，建议使用( 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______vend_id__;

SELECT distinct 部门名称，count(员工编号)，sum(个人工资) FROM 工资表 GROUP BY 部门名称 having count (员工编号>10);查询结果是___。

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

MySQL是一种___多用户 _(多用户、单用户)的数据库管理系统。

对一个超过200个汉字的内容，应用一个varchar或者text型的字段来存放。

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

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