SQL 练习题学生表： Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出

学生表：
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
课程表：
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
教师表：
Teacher(t_id,t_name) –教师编号,教师姓名
成绩表：
Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

表名和字段
–1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

–2.课程表

Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

–3.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

–4.成绩表

Score(s_id,c_id,s_score) –学生编号,课程编号,分数

测试数据

学生表

CREATE TABLE `Student`(  
`s_id` VARCHAR(20),  
`s_name` VARCHAR(20) NOT NULL DEFAULT '',  
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',  
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',  
PRIMARY KEY(`s_id`)  
);

课程表

CREATE TABLE `Course`(  
`c_id` VARCHAR(20),  
`c_name` VARCHAR(20) NOT NULL DEFAULT '',  
`t_id` VARCHAR(20) NOT NULL,  
PRIMARY KEY(`c_id`)  
);

教师表

CREATE TABLE `Teacher`(  
`t_id` VARCHAR(20),  
`t_name` VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(`t_id`)  
);

成绩表

CREATE TABLE `Score`(  
`s_id` VARCHAR(20),  
`c_id` VARCHAR(20),  
`s_score` INT(3),  
PRIMARY KEY(`s_id`,`c_id`)  
);

插入学生表测试数据

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

课程表测试数据

insert into Course values('01' , '语文' , '02');  
insert into Course values('02' , '数学' , '01');  
insert into Course values('03' , '英语' , '03');

教师表测试数据

insert into Teacher values('01' , '张三');  
insert into Teacher values('02' , '李四');  
insert into Teacher values('03' , '王五');

成绩表测试数据

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

查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

select s.s_id,a.s_score,b.s_score from student as s inner join (select s_id,s_score from score where c_id='01') as a on s.s_id=a.s_id inner join (select s_id,s_score from score where c_id='02')as b on a.s_id=b.s_id where a.s_score>b.s_score;

查询平均成绩大于60分的学生的学号和平均成绩

select s_id,avg(s_score) as avg_score from score group by s_id having avg_score>60;

查询姓“猴”的老师的个数（不重要）

select count(t_name) from teacher where t_name like '猴%';

查询没学过“张三”老师课的学生的学号、姓名（重点）

select st.s_id,st.s_name from student as st where s_id not in (select s.s_id from score as s inner join course as co on s.c_id=co.c_id inner join teacher as t on co.t_id=t.t_id where t_name='张三');

先根据score、course、teacher三表根据张三名称查询出学生id ，在加上student表查询学生信息

查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

select st.s_id,st.s_name from student as st where s_id in (
select sc.s_id from score as sc inner join course as c on sc.c_id=c.c_id
inner join teacher as t where t.t_name='张三');

查询没有学全所有课的学生的学号、姓名(重点)

select st.s_id,st.s_name from student as st inner join score as s on st.s_id=s.s_id group by st.s_id,st.s_name having count(c_id) <(select count(distinct c_id) from course);

#先查询学生和分数表 select count(distinct c_id) from course查询总课程

查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

select sc.c_id from score where s_id='01';   
select s.s_id,s.s_name from student as s inner join score as sc on s.s_id=sc.s_id where sc.c_id in (select c_id from score where s_id='01') and s.s_id!='01';

#先根据01学号查询学生选修的课程

查询两门课及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

select s.s_id,s.s_name,avg(s_score) from student as s inner join score as sc on s.s_id=sc.s_id where sc.s_id in (select s_id from score where s_score<60 group by s_id having count(distinct c_id)>=2) group by s.s_id,s.s_name;

检索"01"课程分数小于60，按分数降序排列的学生信息

select s.* from student as s inner join score as sc on s.s_id=sc.s_id where sc.c_id='01' and sc.s_score<60 order by sc.s_score desc ;

查询学生的总成绩并进行排名

select s_id ,sum(s_score) as 总成绩 from score group by s_id order by 总成绩 desc ;

查询每门课程被选修的学生数

select s.c_id,c.c_name, count(s_id) from score as s,course as c where s.c_id=c.c_id group by c_id,c.c_name; 
select s.c_id,c.c_name,count(s_id) from score as s inner join course as c on s.c_id=c.c_id group by c_id,c.c_name;

查询出只有两门课程的全部学生的学号和姓名

select s_id,s_name from student where s_id in ( select s_id from score group by s_id having count(distinct c_id)=2); 
select s.s_id,s_name from student as s inner join score as sc on s.s_id=sc.s_id group by s.s_id, s_name having count(c_id)=2;

查询名字中含有'风'字的学生信息

select * from student where s_name like '%风%'; 
-- %：匹配任意字符，可以出现零次或多次。 
select * from student where s_name like '_风%'; 
-- 匹配任意一个字符，只能出现一次。

查询1990年出生的学生的名单（重点）

select * from student where year(s_birth)=1990;

查询平均成绩大于等于85的所有学生的学号，姓名和平均成绩

select st.s_id,st.s_name, avg(s_score) as avg_score from score as s inner join student as st on s.s_id=st.s_id group by s_id having avg_score>=85;

查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select c_id, avg(s_score) as avg_score from score group by c_id order by avg_score,c_id desc ;

查询名称为'数学'，且分数低于60的学生的姓名和分数

select st.s_name,sc.s_score from course as c inner join score as sc on c.c_id=sc.c_id inner join student as st on st.s_id=sc.s_id where c.c_name='数学' and sc.s_score<60;

查询课程成绩在70分以上课程名称，分数和学生姓名

select c.c_name,s.s_score,st.s_name from student as st inner join score as s on st.s_id=s.s_id left join course as c on c.c_id=s.c_id where s.s_score>70;

查询不及格的课程并按课程号从大到小排列

select s.s_id, c.c_name,s.s_score from score as s inner join course as c on s.c_id=c.c_id where s.s_score<60 order by c.c_id;

查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

select st.s_id,st.s_name from student as st inner join score as s on st.s_id=s.s_id where s.c_id='03' and s.s_score>80;
select st.s_id,st.s_name from student as st,score as s where st.s_id=s.s_id and s.c_id='03' and s.s_score>80;

求每门课程的学生人数

select c_id,count(distinct s_id) from score group by c_id;

查询选修了全部课程的学生信息

select s_id from score group by s_id having count(c_id)=(select count(distinct c_id) from course);

检索至少选修两门课程的学生学号

select s_id from score group by s_id having count(c_id)>=2;

统计每门课程的学生选修人数（超过5人的课程才统计）

select c_id,count(distinct s_id) as cnt from score group by c_id having cnt>5;

查询选修'张三'老师所授课程的学生中成绩最高的学生姓名及其成绩

select st.s_name,sc.s_score from student as st inner join score as sc on st.s_id=sc.s_id inner join course as c on sc.c_id=c.c_id inner join teacher as t on t.t_id=c.t_id where t.t_name='张三' order by sc.s_score desc limit 0,1;

从表t1(有字段Id,name,sex,score)中取出sex为男，score列前十名的name字段

select name from t1 where sex='男' order by score desc limit 10

limit用法： limit m,n m表示查询数据的其实下标 n表示查询数量
limit n 从起始位置开始的n条数据

获取每个班级的最后一名

select ss.* from stu_score ss join (select bj,min(score) as min from stu_score group by bj) as tmp on ss.bj=tmp.bj and ss.score=tmp.min;

思路：
1.求出每个班级的最低分
2.拿刚才的结果表与原表联查，条件为班级号相等同时分数相等，即可以查到每个班的最低分对应的人

表user,字段有：id(非自增，不连续)，age,name,sex,addTime。求创建时间(addTime) 在2020-05-22至2020-06-17之间所有数据

select * from user where addTime between '2020-05-22' and '2020-06-17' between..and...包括2个边界值

向user表中添加一条数据，包括创建时间

insert into user values(10,21,'tom','男','2021-12-12')

基于表user,查出表中第10条至第20条连续分页数据的sql语句

select * from user limit 9,11   
10的索引值9 条数为20-10+1
-- LIMIT (pageNo-1) * pageSize, pageSize; 
-- 结束数据索引：分页显式公式：（当前页数-1）* 每页条数，每页条数

查询所有学生的学号，姓名，选课数，总成绩

select s.sno,sname,count(cno), as '选课数',sum(score) as '总成绩' from student as s left join score as sc on s.sno=sc.sno group by s.sno;
-- 学生表和成绩表外连接查询，选择条件学号相等，要根据学生表中的学号进行分组，统计每个学生的总成绩和选课数

查询没有学完所有课程的学生的学号，姓名

select s.sno,s.sname from student as s left join score as sc on s.sno=sc.sno group by s.sno having count(cno)<(select count(*) from course1);
1. 获取总课程数 select count(*) from course1; 
2. 查询成绩表中的课程数小于总课程的 
3. 查询每个学生的选课数，然后筛选选课数< 总课程数

分页查询分页查询employees表，每5行一页，返回第2页的数据

select * from employees order by emp_no limit 5,5;
limit x,y x:偏移量 y:要获取的个数 
limit 5,5; 偏移量为5，取5条记录 
limit y offset x 
limit 5 offset 5; 取5条记录，偏移量为5

limit分页公式

limit分页公式：curPage是当前第几页；pageSize是一页多少条记录 
limit (curPage-1)*pageSize,pageSize 
用的地方：sql语句中
select * from student limit(curPage-1)*pageSize,pageSize; 
总页数公式：
totalRecord是总记录数；
pageSize是一页分多少条记录 
int totalPageNum = (totalRecord +pageSize - 1) / pageSize; 
用的地方：前台UI分页插件显示分页码 查询总条数：totalRecord是总记录数

牛客

SQL195 查找最晚入职员工的所有信息

select * from employees order by hire_date desc  limit 0,1;

SQL196 查找入职员工时间排名倒数第三的员工所有信息

select * from employees where hire_date =( select distinct hire_date from employees order by hire_date desc limit 2,1);

LIMIT 2,1表示从第3行开始，取1行数据。其中，2表示从第3行开始，1表示取1行数据。

SQL197 查找当前薪水详情以及部门编号dept_no

select s.emp_no,s.salary,s.from_date,s.to_date,dm.dept_no from dept_manager as dm left join salaries as s on dm.emp_no=s.emp_no order by s.emp_no;

SQL198 查找所有已经分配部门的员工的last_name和first_name以及dept_no

select e.last_name,e.first_name,d.dept_no from employees as e inner join dept_emp as d on e.emp_no=d.emp_no

SQL201 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

select emp_no,count(salary) as t from salaries group by emp_no having t >15;

SQL202 找出所有员工当前薪水salary情况

select distinct salary from salaries group by salary order by salary desc;

SQL204 获取所有非manager的员工emp_no

select emp_no from employees as e left join dept_manager as d on e.emp_no=d.emp_no where dept_no is null; 
select emp_no from employees where emp_no not in(select emp_no from dept_manager);

SQL205 获取所有员工当前的manager

select de.emp_no,dm.emp_no as manager from dept_manager as dm left join dept_emp as de on dm.dept_no=de.dept_no where de.emp_no !=dm.emp_no;

SQL209 查找employees表emp_no与last_name的员工信息

select * from employees where mod(emp_no,2)=1 and last_name <> 'Mary' order by hire_date desc;
-- 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列，

SQL210 统计出当前各个title类型对应的员工当前薪水对应的平均工资

select title, avg(salary) as avg_s from titles as t left join salaries as s on t.emp_no=s.emp_no group by title;

SQL211 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

-- 薪水第二多的员工有多个 
select emp_no,salary from salaries where salary=( select salary from salaries order by salary desc limit 1,1);

SQL213 查找所有员工的last_name和first_name以及对应的dept_name

select last_name,first_name,dept_name from employees as e left join dept_emp as de on e.emp_no=de.emp_no left join departments as d on d.dept_no=de.dept_no;

SQL216 统计各个部门的工资记录数

select d.dept_no,d.dept_name,count(salary) as sum from departments as d 
inner join dept_emp as de on d.dept_no=de.dept_no inner join salaries as s 
on s.emp_no=de.emp_no group by d.dept_no,d.dept_name order by dept_no;

SQL223 使用join查询方式找出没有分类的电影id以及名称

select f.film_id,title from film as f left join film_category as fc on f.film_id=fc.film_id where fc.category_id is null;

SQL223 使用join查询方式找出没有分类的电影id以及名称

select f.film_id,title from film as f left join film_category as fc on f.film_id=fc.film_id where fc.category_id is null;

SQL226 将employees表的所有员工的last_name和first_name拼接起来作为Name

select concat(last_name,' ',first_name) from employees;

SQL228 批量插入数据

insert into actor (actor_id,first_name,last_name,last_update) values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'), (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

SQL229 批量插入数据，不使用replace操作

insert ignore into actor (actor_id,first_name,last_name,last_update) values ("3","ED","CHASE","2006-02-15 12:34:33");
-- mysql中常用的三种插入数据的语句: # insert into表示插入数据，数据库会检查主键，如果出现重复会报错； 
-- replace into表示插入替换数据，需求表中有PrimaryKey， 
-- 或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样； 
-- insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

SQL230 创建一个actor_name表

create table actor_name( first_name varchar(45) not null comment '名字', last_name varchar(45) not null comment '姓氏' ); insert into actor_name(first_name,last_name) select first_name,last_name from actor;

SQL232 针对actor表创建视图actor_name_view

create table actor_name( first_name varchar(45) not null comment '名字', last_name varchar(45) not null comment '姓氏' ); insert into actor_name(first_name,last_name) select first_name,last_name from actor;

SQL233 针对上面的salaries表emp_no字段创建索引idx_emp_no

create index idx_emp_no on salaries(emp_no);

SQL234 在last_update后面新增加一列名字为create_date

alter table actor add (create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00');;

SQL237 将所有to_date为9999-01-01的全部更新为NULL

update titles_test set from_date='2001-01-01',to_date=null where to_date='9999-01-01'

SQL238 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

update titles_test set emp_no=replace(emp_no,10001,10005) where id=5;

SQL239 将titles_test表名修改为titles_2017

alter table titles_test rename to titles_2017;

SQL249 分页查询employees表，每5行一页，返回第2页的数据

select * from employees limit 5,5;
-- 分析 limit x,y x:偏移量 y: 要获取的个数 
-- limit y offset x 
-- 分页显式公式：（当前页数-1）* 每页条数，每页条数