SQL
有A、B两张表,A表有5笔数据,B表有8笔数据。
(1)A表内关联B表时,结果最多和最少分别为几笔数据?
答案:当A表内关联B表时,最多和最少的结果分别为5笔和0笔。
解析:最多的结果是在A表的每一笔数据都有对应的匹配项时获得的,这样可以得到5笔结果。
最少的结果是在A表中的每一笔数据都没有匹配项时获得的,这样可以得到0笔结果。
(2)A表左关联B表时,结果最多和最少分别为几笔数据?
答案:当A表左关联B表时,最多和最少的结果分别为13笔和5笔。
解析:最多的结果是在A表的每一笔数据都有对应的匹配项时获得的,这样可以得到5笔结果。同时,由于左关联会将A表中的所有数据都包含在结果集中,因此还需要包括A表中没有匹配项的8笔数据,因此总共可以得到13笔结果。
最少的结果是在A表中的每一笔数据都没有匹配项时获得的,这样可以得到5笔结果。由于左关联会将A表中的所有数据都包含在结果集中,因此即使B表中有匹配项,也不会增加结果集的数量。因此最少结果为A表的5笔数据。
有一张学生表成绩表:
运行以下SQL得上表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student_score
-- ----------------------------
DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
`student` varchar(20) DEFAULT NULL,
`class` varchar(20) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`mark` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student_score
-- ----------------------------
INSERT INTO `student_score` VALUES ('张三', 'A班', '语文', '70');
INSERT INTO `student_score` VALUES ('张三', 'A班', '数学', '80');
INSERT INTO `student_score` VALUES ('张三', 'A班', '英语', '91');
INSERT INTO `student_score` VALUES ('李四', 'A班', '语文', '83');
INSERT INTO `student_score` VALUES ('李四', 'A班', '数学', '58');
INSERT INTO `student_score` VALUES ('李四', 'A班', '英语', '50');
INSERT INTO `student_score` VALUES ('王五', 'B班', '语文', '83');
INSERT INTO `student_score` VALUES ('王五', 'B班', '数学', '76');
INSERT INTO `student_score` VALUES ('王五', 'B班', '英语', '80');
INSERT INTO `student_score` VALUES ('赵六', 'B班', '语文', '88');
INSERT INTO `student_score` VALUES ('赵六', 'B班', '数学', '58');
INSERT INTO `student_score` VALUES ('赵六', 'B班', '英语', '91');
(1)查询存在不及格(成绩低于60分)的学生信息
select * from student_score where mark < 60
(2)查询所有课程的最高分数
select MAX(mark) from student_score
(3)查询每门课程的最高分数
select course,MAX(mark) from student_score group by course
(4)查询没门课程第一名的学生信息
select t1.* from student_score t1
where t1.mark=(select MAX(t2.mark) from student_score t2
where t1.course=t2.course)
(5)查询语文平均分高于数学平均分的班级
select class from student_score
group by class
having AVG(case when course='语文' then mark else null end)
>
AVG(case when course='数学' then mark else null end)
这里使用HAVING子句,对于每个班级,分别计算出语文和数学的平均分,然后比较两者大小,筛选出语文平均分高于数学平均分的班级。
(6)查询每个班所有课程都及格(成绩大于等于60)的学生姓名
select student,class from student_score
group by student,class
having COUNT(case when mark<60 then 1 else null end)=0
这里使用了COUNT()函数和HAVING子句,对于每个班级和学生姓名,分别计算出不及格的科目数量,如果为0,则说明该学生所有课程都及格,被筛选出来。
有一张交易日志表,如下所示:
运行下面SQL得上表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for trans_log
-- ----------------------------
DROP TABLE IF EXISTS `trans_log`;
CREATE TABLE `trans_log` (
`user_id` int(11) DEFAULT NULL,
`trans_data` date DEFAULT NULL,
`amount` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of trans_log
-- ----------------------------
INSERT INTO `trans_log` VALUES ('1', '2023-04-01', '475.5');
INSERT INTO `trans_log` VALUES ('1', '2023-04-02', '96.76');
INSERT INTO `trans_log` VALUES ('1', '2023-04-04', '374.2');
INSERT INTO `trans_log` VALUES ('2', '2023-04-01', '421.6');
INSERT INTO `trans_log` VALUES ('2', '2023-04-02', '85.6');
INSERT INTO `trans_log` VALUES ('2', '2023-04-03', '75.1');
INSERT INTO `trans_log` VALUES ('2', '2023-04-04', '678.1');
INSERT INTO `trans_log` VALUES ('2', '2023-04-08', '564.8');
INSERT INTO `trans_log` VALUES ('2', '2023-04-09', '123.6');
INSERT INTO `trans_log` VALUES ('3', '2023-04-13', '463.82');
INSERT INTO `trans_log` VALUES ('3', '2023-04-15', '566.21');
INSERT INTO `trans_log` VALUES ('3', '2023-04-17', '78.6');
请找出该表中有连续3天(包含3天)以上交易行为的用户ID和连续交易的天数。
解析:可以通过自联结(self-join)和日期差值来解决这个问题。具体步骤如下:
- 对于每个用户,按照日期排序;
- 自联结表,连接前一天和后一天的数据;
- 计算日期差值,如果差值为1,则说明连续交易;
- 对于连续交易的记录进行分组,计算连续天数;
- 最后只需要筛选出连续天数大于等于3的记录即可。
以下是对应的 SQL 语句:
SELECT t1.user_id, COUNT(*) as '连续天数'
FROM trans_log t1
JOIN trans_log t2 ON t1.user_id = t2.user_id
AND DATEDIFF(t1.trans_data, t2.trans_data) BETWEEN 1 AND 2
JOIN trans_log t3 ON t2.user_id = t3.user_id
AND DATEDIFF(t2.trans_data, t3.trans_data) = 1
GROUP BY t1.user_id, t1.trans_data
HAVING COUNT(*) >= 3;
有如下SQL语句需要调优,请写出优化后的语句
select count (distinct id) from tablenames
insert overwrite table student_stat partition(tp)
select s_age,max(s_birth) stat,'max' tp
from student_txt group by s_age
union all
select s_age,min(s_birth) stat, 'min' tp
from student_txt group by s_age;
优化后:
SELECT COUNT(id) FROM (SELECT DISTINCT id FROM tablenames)
INSERT OVERWRITE TABLE student_stat PARTITION(tp)
SELECT s_age, MAX(s_birth) AS stat, 'max' AS tp
FROM student_txt
GROUP BY s_age
HAVING MAX(s_birth) = (
SELECT MAX(s_birth)
FROM student_txt
)
UNION ALL
SELECT s_age, MIN(s_birth) AS stat, 'min' AS tp
FROM student_txt
GROUP BY s_age
HAVING MIN(s_birth) = (
SELECT MIN(s_birth)
FROM student_txt
);
这个语句的改进之处在于使用了 HAVING 语句来筛选出最大/最小值,而不是使用两个分组查询。这样可以避免不必要的计算,提高查询效率。
Linux
(1)文件授权命令
chmod [who] operator [permission] file
其中,who表示权限范围,operator表示加减权限,permission表示具体权限,file表示要授权的文件名。
例如,将文件test.txt授权给所有用户可读可写可执行权限可以使用以下命令:
chmod a+rwx test.txt
(2)a.txt文件为b.txt
cp a.txt b.txt
(3)文件夹test.txt拷贝至/home下
cp -r test.txt /home
其中,-r参数表示递归复制整个文件夹及其子文件夹。
(4)查看日志文件app.log
cat app.log
(5)如何判断两个文件里面的内容是否一样
diff file1 file2
其中,file1和file2分别表示要比较的两个文件名。如果输出为空,则说明两个文件内容完全一致。如果有不同之处,则会详细列出这些不同之处。