【SQL】语句练习

时间：2024-12-17 20:52:42

1. 更新

1.1单表更新

例1: 所有薪水低于30000的员工薪水增加10%

SQL命令： update employee set salary=salary*1.1 where salary < 30000;

1.2多表更新

例1: 将下图两表张三的语文成绩从95修改为80

SQL命令： update exam set score=80 where subject ="语文" and scode in (select code from student where name="张三"); 

2.查询

2.1单表查询

题型1: 基本选择查询
1.查询所有员工的name和salary

SQL命令： select name,salary from employee;

题型2: 条件查询
1.查询salary高于5w的员工姓名和薪水

SQL命令： select name,salary from employee where salary>50000;

2.查询数学成绩和英语成绩都大于80分的学生姓名

SQL命令：select name from student_05 where m_score > 80 and english_score>80;

3.找出在运营部且薪水在2w-7w之间的员工姓名和薪水

SQL命令： select name,salary from employee where department="运营部" and salary between 20000 and 70000;

4. 匹配员工name中以"李"开头的name和部门

SQL命令： select name,department from employee where name like '李%';

5. 查询name为张三或李四的信息

SQL命令： select * from student where name ="张三" or name="李四";

题型3: 分组和聚合函数结合
1.查询每个部门的平均薪水/部门最高薪水/员工人数

SQL命令求平均薪水： select department,avg(salary)  as avg_salary from employee group by department;

SQL命令求最高薪水：select department,max(salary) as max_salary from employee group by department;

SQL命令求员工人数：select department,count(*) from employee group by department;

2.查询每个学生的总成绩

SQL命令求总成绩：select name,sum(score) as sum_score from student_02 group by name;

例4: 下表中查询至少有一科分数大于80的学生总数

SQL命令1： select count(distinct(name)) from student_02 where score > 80;

SQL命令2:   select count(distinct CASE WHEN score>80 THEN name END) as num from student_02;

例5: 下表中查询学生每门课程分数大于80分的学生姓名

SQL命令1: select name from student_02 group by name having min(score)>80;

SQL命令2: select distinct name from student_02 where name not in (select distinct name from student_02 where score<80);

例6: 查询出同一学生的课程1分数小于课程2分数的学生ID

思想：自连接，其中关键是学生自身比

SQL命令： select s_id from student_04 as s inner join student_04 as s1 on s.s_id=s1.s_id where s.cid=1 and s1.cid=2 and s.score< s1.score;

例8: 查询薪水最高的2位的姓名和薪水，按照薪水降序排列

SQL命令： select name,salary from employee order by salary desc limit 2 ;

例9: 显示姓名，薪水和新列salary level.低于2w显示为low，2w-4w为medium

SQL命令： select name,salary, CASE WHEN salary<20000 THEN 'low' WHEN salary between 20000 and 40000 THEN 'medium' ELSE 'high' END as salary_level from employee;

例10: 查询薪水第2高的

SQL命令： select salary from salary order by salary desc limit 1,1;

例11:查询出学过课程1和3的学生ID

SQL命令： select s_id from student_04 where cid in (1,3) group by s_id having count(distinct cid)=2;

例12： 查询每科的平均成绩并按照平均成绩升序，如果一样，则按照课程id降序

SQL命令： select cid,avg(score) as avg_score from student_04 group by cid order by avg_score,cid desc;

-----------------------------------------------------------------------------------

2.2多表查询

例1:  求出每个部门的名称以及平均薪水

SQL命令(以departments为主)：select d.department_name,avg(e.salary) from employee as e join departments ad d on e.department_id=d.department_id group by d.department_name

例2: 显示所有部门的名称和部门员工数包括没有员工的部门

SQL命令： select d.department_name,count(e.employee_id) from departments d left join employee e on d.department_id=e.department_id group by d.department_name;

例3: 

求1： 用户user_id,nickname和消费总额前5名

思想：以shop_record为主表，将三张表连接分别获取user_info的user_id,nickname以及commodity_info的price; 通过group by分组保证唯一用户

SQL命令：select u.user_id,u.nickname,sum(s.num*c.price)  as total from shop_record as s join user_info as u on s.user_id=u.id join commodity_info as c on s.c_id=c.id group by u.user_id,u.nickname order by total desc  limit 5;

例4:查询出每个学生的最高分以及课程名

思想： 通过自连接实现，其中关键是学生自身比

SQL命令： select s.name,s.course,s.score from student_02 as s join (select name,max(score) as max_score from student_02 group by name) as s1 on s.name=s1.name and s.score=s1.max_score;

-----------------------------------------------------------------------------------

2.3子查询

例1: 找出salary高于部门平均薪水的所有员工姓名

SQL命令： select name from employee e where salary >(select avg(e2.salary) from employee e2 where e2.department_id=e.department_id);

        外层查询员工姓名；内层查询每个员工所在部门的平均薪水

3.删除

例1: 删除employee表中所有部门为【研发部】的员工记录

SQL命令： delete from employee where department="研发部";

扩展：

  • 窗口函数

    • RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )  为每行分配一个排名，遇到同排名则跳过
      • 参数PARTITION,用于将数据按指定列分组，每个分组内的排名是独立计算的。类似于 GROUP BY，但它不会减少结果集的行数。
      • 例题：查询出每个学生的最高分以及课程名
        • SQL命令： 

相关文章