MySql02

目录

一、基本查询

二、过滤查询

三、排序

四、案例

---

一、基本查询

增删改查

以员工表t_mysql_employees为例

增：

insert into t_mysql_employees(first_name,last_name,
email,phone_number,salary,
commission_pct,manager_id,hiredate)
 values('li','si','2810119421@qq.com',
'13085452993',100000,0.25,100,now());

删：

delete from t_mysql_employees where phone_number = '13085452993'

改：

update t_mysql_employees set last_name = 'wen' where phone_number = '13085452993';

查：

select * from t_mysql_employees;

二、过滤查询

1、按条件表达式筛选

案例1：查询工资大于12000的员工

select * from t_mysql_employees where salary > 12000;

案例2：查询部门编号不等于90的员工名和部门编号

select last_name,department_id from t_mysql_employees
where department_id <> 90;
select last_name,department_id from t_mysql_employees
where not(department_id = 90);

查询结果

 二、按逻辑表达式删选
1、查询工资在10000到20000之间的员工姓名、工资以及奖金

select last_name,salary,commission_pct from 
t_mysql_employees where salary between 10000 and 20000;

效果

 2、查询部门编号不是在90到110之间、或者工资高于150000的员工信息

select * from t_mysql_employees where 
not(department_id between 90 and 110) or salary > 15000;

结果

 模糊查询
1、like
案例1：查询员工名字包含字符a的员工信息

select * from t_mysql_employees where last_name like '%a%';

案例二、查询员工第三个字符为e， 第五个字符为啊的员工名和工资

select * from t_mysql_employees where last_name like
'_e_a%';

between and 
案例一:查询员工编号在100到120之间的员工信息
 

select * from t_mysql_employees where employee_id
between 100 and 120;

3、in
案例：查询员工工种编号是IT_PROG、AD_VP、
AD_PRES中的一个一个名和工种编号

select * from t_mysql_employees where job_id
in ('IT_PROG','AD_VP','AD_PRES');

4、案例 is null
案例一、查询没有奖金的员工名和奖金率

select last_name,commission_pct from t_mysql_employees
where commission_pct is not null;

案例二、查询没有奖金的员工和奖金率

select last_name,commission_pct from t_mysql_employees
where commission_pct is null;

三、排序

ORDER BY 子句
1、按单个字段排序
案例：按员工表薪资排序

select * from t_mysql_employees ORDER BY salary;

select * from t_mysql_employees ORDER BY salary asc;

select * from t_mysql_employees GROUP BY salary;

2、添加筛选条件在排序
案例：查询部门编号>=90的员工信息、并按员工编号降序

select * from t_mysql_employees where department_id >= 90 ORDER BY employee_id desc;

 

3、按表达式排序
案例：查询员工信息、按年薪降序

select em.salary*12*(1+IFNULL(commission_pct,0)),
em.* from t_mysql_employees em ORDER BY
 em.salary*12*(1+IFNULL(commission_pct,0)) desc; 

4、按别名排序
案例：查询员工信息、按年薪升序

select count(1) as a from t_mysql_employees;
select first_name,last_name from t_mysql_employees;

5、按函数排序
案例：查询员工名、并且按名字的长度降序

select last_name,CONCAT(first_name,last_name),
LENGTH(CONCAT(first_name,last_name)) from t_mysql_employees ORDER BY LENGTH(CONCAT(first_name,last_name)) desc;

四、案例

查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

select a.*,b.score,c.score from t_student a,
t_score b,t_score c where a.sid=b.sid and 
a.sid=c.sid and b.cid='01' and c.cid='02' and b.score > c.score

查询同时存在" 01 "课程和" 02 "课程的情况

select a.*,b.cid,c.cid from t_student a,t_score b,
t_score c where a.sid=b.sid and a.sid=c.sid and b.cid='01' and c.cid='02' 

查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

select a.*,b.cid,c.cid from t_student a,t_score b,t_score c 
where a.sid=b.sid and a.sid=c.sid and b.cid='01' and c.cid not in('02')

 

查询不存在" 01 "课程但存在" 02 "课程的情况

select a.*,b.cid,c.cid from t_student a,t_score b,t_score c 
where a.sid=b.sid and a.sid=c.sid and c.cid not in('01') and b.cid='02' 
 

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

select * from t_student a,t_score b where a.sid=b.sid and a.sid in(select c.sid from t_score c where c.score<60 GROUP BY c.sid HAVING COUNT(*)>1)

查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select a.cid,cname,max(a.score)'最高分',min(a.score)'最低分',
avg(a.score)'平均分',((select count(sid) from
 t_score where score>=60 and cid=b.cid )/(select count(sid)
 from t_score where cid=b.cid)) '及格率' from t_score a
inner join t_course b on a.cid = b.cid
group by b.cid;