MySQL 系列教程之（十五）SQL 面试题精讲

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

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;

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;

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;

-- 先按题设计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

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

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;

limit 3

order by age desc 

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

 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文件及位置点信息

 grant select,insert,update,delete on B.*,A.c,A.d to 'zhansgan'@'%' identified by '123456'

复杂查询简单化
   不用嵌套,不用join,多表查询拆分
针对语句的查询字段 设计索引
   给经常作为where条件的字段添加索引 
   必要时添加 联合索引

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.delete 语句

B.turncate 语句

C.drop 语句

D.commit 语句

A.WHERE NAME ! NULL

B.WHERE NAME NOT NULL

C.WHERE NAME IS NOT NULL

D.WHERE NAME!=NULL

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

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

C.返回单表中数据子集的查询语言

D.嵌入到另一个查询语句之中的查询语句

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

A.UPDATE S Age

B.DELETE Age from S

C.ALTER TABLE S ‘Age’

D.ALTER TABLE S DROP Age

 char 

    定长  长度范围 0-255 超出指定长度无法存储,

 varchar

    变长  长度范围 0-65535 超出指定长度无法存储

 text

    文本 长度范围 0-65535 不需要指定长度 

    text和以上两个字段的最大区别就是 text只能使用前缀索引

UTF8

    收录地球上能想到的所有字符，而且还在不断扩充,同时兼容ASCII字符

    存储空间为 1-4个字节

- utf8mb3`：阉割过的`utf8`字符集，只使用1～3个字节表示字符。
- `utf8mb4`：正宗的`utf8`字符集，使用1～4个字节表示字符。