一、SQL的简单查询语句
#查询所有信息
SELECT * from emp;
#查询限制条件deptno = 10的所有员工信息
select* from emp where deptno = 10;
#查询empno,ename
select empno,ename from emp;
select empno as 员工编号,ename as 员工姓名 from emp;#别名
select empno 员工编号,ename 员工姓名 from emp;#别名,可省略as
#查询10号部门工资大于2000的员工信息
select * from emp where deptno = 10 and sal >2000;
#查询员工编号为7369,7788,7521的员工信息
select * from emp where empno = 7369 or empno = 7788 or empno = 7521;
select * from emp where empno in (7369,7788,7521)#集合查询
#查询emp表的工资在1000到2000之间的员工信息
select * from emp where sal>=1000 and sal <2000;
select * from emp where sal between 1000 and 2000;
#查询emp中职位信息
select job from emp;#有缺陷,有重复
select distinct job from emp;#distinct 去重操作
#查询emp中没有奖金的员工信息
select * from emp where comm is null or comm = 0;
#查询员工的名称(模糊查询),%占0到多个字符,_代表一位字符
select * from emp where ename like 's%'#查询员工名字为S开头
select * from emp where ename like '%n'#查询员工名字为n结尾
select * from emp where ename like '%l%'#查询员工名字包含l
select * from emp where ename like '_l%'#查询员工名字第二个字母为L
#查询排序(order by 字段 asc/desc,默认asc升序)
#按照工资升序排序,工资一样的时候按照部门降序排序
select * from emp order by sal,empno desc;
#限制结果查询,只查询前5条
select * from emp limit 5;#只查询前5条,默认从第一条,0的位置查询
select * from emp limit 1,5;#只查询前5条,从第二条查询,1的位置查询
#每个部门的部门编号和工资工资
select deptno 部门编号,avg(sal) 平均工资 from emp group by deptno;
#部门的平均工资>2000的部门编号以及其平均工资,where中不能使用聚合函数
select deptno 部门编号,avg(sal) 平均工资 from emp group by deptno having avg(sal);
注:
1.where和having之间的区别:
where和having实现条件过滤
where使用在group by之前,having在group by 之后
where不能使用聚合函数,having能够使用聚合函数
二、SQL的高级查询语句
1.内连接
- 内连接
left [outer] join··· on···
- 连接表都匹配的记录才会出现最终的结果集
- 连接与表的先后顺序无关
#查询所有员工的名称,薪水以及部门名称
select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;
select ename,sal,dname from emp inner join dept on emp.deptno = dept.deptno;
select ename,sal,dname from emp inner join dept using(deptno);
2.外连接
- 连接与表的先后顺序有关,以其中一张表为驱动表,与另一张表的每条记录匹配;如果不能匹配,则以null填充,驱动表的数据会全部显示
- 左外连接
left [outer] join··· on···
,以left 前的表作为驱动表 - 右外连接
left [outer] join ···on···
,以right后的表作为驱动表
以左外连接为例,以dept为驱动表
#查询所有的员工名称、薪水以及部门名称
select * from dept left join emp on emp.deptno = dept.deptno;
3.自连接
#查询员工及其领导名称,emp
select * from emp e1 ,emp e2 where e1.mgr = e2.empno;
#展示没有领导的员工信息
select * from emp e1 left join emp e2 on e1.mgr = e2.empno;
三、查询案例
数据准备
- 需求:创建员工表
表名emp
表中字段:
eid 员工id,int
ename 姓名,varchar
sex 性别, char
salary 薪资,double
hire_date 入职时间,date
dept_name 部门名称,varchar
#创建员工表
create table emp(
eid int,
ename varchar(20),
sex char(12),
salary double,
hire_date date,
dept_name varchar(20)
);
#添加数据
insert into emp values(1,'孙悟空','男',7200,'2013-02-04', '教学部');
insert into emp values(2,'猪八戒','男',3600,'2010-12-02','教学部');
insert into emp values(3,'唐僧','男',9000,'2008-08-08','教学部');
insert into emp values(4,'白骨精','女', 5000,'2015-10-O7','市场部');
insert into emp values(5,'蜘蛛精','女', 5000,'2011-03-14','市场部');
insert into emp values(6,'玉免精','女',200,'2000-03-14','市场部');
insert into emp values(7,'林熊玉','女',10000,'2019-10-07','财务部');
insert into emp values(8,'黄蓉','女',3500,'2011-09-14','财务部');
insert into emp values(9,'吴承恩','男',20000,'2000-03-14',NULL);
insert into emp values(10,'孙悟饭','男',10,'2020-03-14','财务部');
insert into emp values(11,'兔八哥','女',300,'2010-03-14','财务部');
简单查询
- 执行顺序
from --> where --> group by --> having --> select --> order by
查询不会对数据库中的数据进行修改,只能一种显示数据的方式select
语法格式
select 列名 from 表名
查询emp中的数据
# 需求1:查询emp中的所有数据
select * from emp;
# 需求2:查询部分字段
select ename,salary from emp;
# 需求3:查询部分字段,并将字段重新命名
select ename as '员工姓名',salary as '工资' from emp;
select ename '员工姓名',salary '工资' from emp;#as可去掉
字段去重操作
#查询emp中一共有哪些部门
select distinct dept_name from emp;
条件查询
语法格式
select 列名 from 表名 where 条件表达式
查询emp中的数据
# 需求1:查询emp中的部门为空的数据
select * from emp where dept_name is null;
# 需求2:查询员工名字中有王字的员工姓名和工资
select ename,salary from emp where ename like '%王%';
聚合函数查询
查询emp中的数据
# 需求1:查询emp中的员工的总数
select count(*) from emp;
select count(1) from emp;
select count(eid) from emp;#eid是唯一识别字段,没有空值,count字段计数不计空值
# 需求2:查询员工总工资(sum)、最高工资(max)、最小薪水(min)、薪水的平均值(avg)
select sum(salary),max(salary),min(salary),avg(salary) from emp;
分组函数查询
- 分组函数使用要求
1.分组函数往往和聚合函数搭配使用
2.group by分组的字段必须出现在select的位置
3.前面select位置显示的内容只能是group by分组的字段和聚合函数,不能出现其他字段
查询emp中的数据
# 需求1:查询emp中的男女员工的平均工资
#按性别分组,求各组的平均工资
select sex,avg(salary) from emp group by sex;
提示:后续待更新