sql语法-面试篇
查询语句
175. 组合两个表
FirstName, LastName, City, State
select a.FirstName 'FirstName',a.LastName 'LastName',b.City 'City',b.State 'State'
from Person a
left join Address b
on a.PersonId=b.PersonId
from子句中on条件主要用来连接表，其他不属于连接表的条件可以使用where子句来指定；
join连接分为三种，1内连接，2外连接，3交叉连接；
1：inner join ，默认，所以可以省略inner关键字
2：left outer join ，左外连接，结果表中除了匹配行外，还包括左表有而右表中不匹配的行，对于这样的行，右表选择列置为null right outer join ，右外连接，结果表中除了匹配行外，还包括右表有而左表中不匹配的行，对于这样的行，左表选择列置为null natural join，自然连接，分为natural left outer join和natural right outer join，语义定义与inner join相同
3：cross join，交叉连接，实际上就是将两个表进行笛卡尔积运算，结果表的行数等于两表行数之积
176. 第二高的薪水
select
ifnull( //判断不存在的情况
(select distinct Salary //去重
from Employee
order by Salary desc
limit 1 offset 1
),NULL)as SecondHighestSalary
limit 与 offset：从下标0开始
offset X 是跳过X个数据
limit Y 是选取Y个数据
limit X,Y 中X表示跳过X个数据，读取Y个数据
例如： select * from table limit 2,1; // 跳过2个数据，读取1个数据
常与order by使用：如,
select distinct Salary
from Employee
order by Salary desc
limit 1 offset 1; //从Employee（员工）表中，读取第二高的salary（薪水），排序后，跳过第一个下标，读取一个元素
178. 分数排名
# Write your MySQL query statement below
select a.Score as Score,
(select count(distinct b.Score)
from Scores as b where b.Score >= a.Score ) as 'Rank'
from Scores as a
order by Score desc
以下例题的表结构
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80025
Source Host : localhost:3306
Source Schema : stutext
Target Server Type : MySQL
Target Server Version : 80025
File Encoding : 65001
Date: 02/08/2021 10:30:32
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`s_score` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`t_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');
SET FOREIGN_KEY_CHECKS = 1;
bili-1
查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
select c.s_name "姓名",a.s_id "学号" from
(
select * from score where c_id='01'
)a join
(
select * from score where c_id='02'
)b on a.s_id=b.s_id
join student c
on c.s_id=a.s_id
where a.s_score>b.s_score
bili-2
-- 查询平均成绩大于60分的学生的学号和平均成绩**
select s_id,avg(s_score) as c
from score
group by score.s_id having avg(s_score) > 60
order by c desc
bili-3
-- 查询所有学生的学号、姓名、选课数、总成绩
select a.s_id,a.s_name,count(distinct c_id),sum(ifnull(s_score,0))
from student as a
left join score as b
on a.s_id =b.s_id
group by s_id
bili-4
-- 查询没学过“张三”老师课的学生的学号、姓名
-- 先找学过的id,然后在not in集合就可以了
select s_id
from student
where s_id not in (
select s.s_id
from score s
join course c
on s.c_id = c.c_id
join teacher t
on c.t_id = t.t_id
where t.t_name = '张三'
)
bili-5
-- 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select s_id , s_name
from student
where s_id in(
select a.s_id from (
(select * from score where c_id = '01') a
join
(select * from score where c_id = '02') b
on a.s_id = b.s_id
)
)
bili-6
-- 查询所有课程成绩小于60分的学生的学号、姓名
select s_id,s_name
from student
where s_id in (
select s_id
from score
group by s_id
having max(s_score) < 60
)
bili-7
-- 查询没有学全所有课的学生的学号、姓名
-- 连接表的时候需要left join student 避免一个都没选的情况
select a.s_id,a.s_name
from student a
left join score b
on a.s_id = b.s_id
group by a.s_id
having count(distinct b.c_id) <(
select count(distinct c_id) from course)
bili-8
-- 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
select a.s_id,a.s_name
from student as a
join(
select distinct s_id
from score
where c_id in(
select c_id
from score
where s_id = '01') and s_id != '01') as b
on a.s_id = b.s_id
bili-9
-- 查询和“01”号同学所学课程完全相同的其他同学的学号
-- id in (选课数相同) and id not in(存在和01不同选课的)
select s_id,s_name
from student
where s_id in(
select s_id
from score where s_id != '01'
group by s_id
having count(distinct c_id) = ( select count(distinct c_id) from score where s_id = '01')
) and s_id not in (
select s_id
from score
where c_id not in(
select c_id
from score
where s_id = '01')
)
bili-10
-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id,a.s_name,avg(b.s_score)
from student as a
join score b
on a.s_id = b.s_id
where s_score < 60
group by s_id
having count(distinct c_id) >= 2
bili-11
-- 检索"01"课程分数小于60，按分数降序排列的学生信息
select a.*
from student as a
join score as b
on a.s_id = b.s_id
where b.c_id = '01' and b.s_score < 60
order by b.s_score desc
bili-12
-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s_id,
max(case when c_id = '01' then s_score else null end) "语文",
max(case when c_id = '02' then s_score else null end) "数学",
max(case when c_id = '03' then s_score else null end) "英语",
avg(s_score) "平均成绩"
from score
group by s_id
order by avg(s_score) desc
bili-13
-- 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
select a.c_id,
b.c_name,
max(s_score),
min(s_score),
avg(s_score),
sum(case when a.s_score < 60 then 1 else 0 end) / count(s_id)
from score as a
join course as b on a.c_id = b.c_id
group by a.c_id
bili-14
-- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
select b.c_id,b.c_name,
count(case when a.s_score <= 100 and a.s_score >85 then 1 else null end) '[100-85]',
count(case when a.s_score <= 85 and a.s_score > 70 then 1 else null end) '[85-70]'
from score as a
join course as b
on a.c_id = b.c_id
group by b.c_id
bili-15
-- 查询学生平均成绩及其名次
select s_id , avg(s_score),row_number () over (order by avg(s_score) desc )
from score
group by
bili-16
-- 查询每门课程被选修的学生数
select b.c_id,count(a.s_id)
from student as a
join score as b
on a.s_id = b.s_id
group by b.c_id
bili-17
-- 查询出只有两门课程的全部学生的学号和姓名
select a.s_name
from student as a
join score as b
on a.s_id = b.s_id
group by a.s_id
having count(b.c_id) = 2
bili-18
-- 查询男生、女生人数
select s_sex,count(s_id)
from student
group by s_sex
select
sum(case when s_sex = '男' then 1 else 0 end) "男",
sum(case when s_sex = '女' then 1 else 0 end) "女"
from student
bili-19 时间
-- 查询1990年出生的学生名单
select *
from student
where year(s_birth) = 1990
select month('1990-01-01')
select month('19900101')
select month('1990/01/01')
select month('90-01-01')
select curdate() -- 当前时间
bili-20
-- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select c_id,avg(s_score) as _avg
from score
group by c_id
order by _avg asc , c_id desc
bili-21
-- 查询个学生的年龄
select s_name,floor(datediff(curdate(),s_birth)/365)
from student
floor -> 向下取整
round -> 进位,向上取整
bili-22
-- 查询本月过生日的人
select *
from student
where month(s_birth) = month(curdate())
-- year 年份
-- data 天份
-- now() 时间,到秒
创建+优化
创建表
create table student(
id int(4) auto_increment,
name varchar(5),
detp varchar(5),
primary key(id)
)default charset = utf-8 auto_increment = 1 engine = MyISAM;
索引
创建索引
| 单值索引 | 唯一索引 | 符复合索引 | |
|---|---|---|---|
1: create 索引类型 索引名 on 表(字段) |
create index _detp on student(name); |
create uniqeue _detp on student(name); |
create index _detp on student(name,id); |
2:alter table (表名) add 索引类型 索引名(字段) |
alter table student add index _name(name); |
alter table student add unique_name(name); |
alter table student add index _name(name, id); |
都是DDL,不需要提交
如果一个字段是primary key,则该字段是默认索引
删除索引
drop index 索引名 on 表名;
drop index _name on student;
查询索引
show index from 表名 ;
show index from 表名 \G;
SQL性能
a:分析sql执行计划 :explain
浙公网安备 33010602011771号