【SQL】语句练习

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: 查询所有员工的name和salary

SQL命令： select name,salary from employee;

例2: 查询salary高于5w的员工姓名和薪水

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

例3: 查询每个部门的平均薪水/部门最高薪水

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

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

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

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

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

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;

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

例6: 匹配员工name中以"李"开头的name和部门

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

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

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

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