JAVA中SQL查询语句大全,select多表查询,各种查询

web基础 专栏收录该内容
1 篇文章 0 订阅

以员工表:emp 为例

idnamegenderbirthdaydeptjobsalbonus
编号姓名性别生日部门职位薪资奖金
  1. 基本查询
    – 查询emp表中的所有员工信息
select * from emp;

– 查询emp表中的所有员工的姓名、薪资、奖金

 select name,sal,bonus from emp;

– 查询emp表中的所有部门, 剔除重复的记录, 提示: distinct用于剔除重复值

 select distinct dept from emp; 
  1. where子句查询
    对表中的所有记录进行筛选、过滤使用where子句,下面的运算符可以在 WHERE 子句中使用:
    (1) between x and y //(在x~y之间的值)。
    (2) like // 模糊搜索,可配合 “%” 和 “_” 等符号。
    (3) “%” //表示通配,表示0或多个字符。
    (4) 下划线 //表示一个字符串。
    (5) and //表示并的关系,当两边的条件都为true时结果才为true,
    (6) or //表示或的关系,当两边只要有一边为true,结果就为true
    (7) not //表示对条件取反。
    (8) > 大于 <小于 =等于 <>不等于或!= >=大于等于 <=小于等于
    (9) in //指定针对某个列的多个可能值
    (10)as //定义别名

– 查询emp表中薪资大于3000的所有员工,显示姓名、薪资

select name,sal from emp where sal>3000; 

– 查询emp表中总薪资(薪资+奖金)大于3500的所有员工,显示姓名、总薪资

select name, sal+bonus from emp where (sal+bonus)>3000; 
-- 或
select name, sal+bonus as 总薪资 from emp where (sal+bonus)>3000;

提示:as用于定义别名(仅在查询的结果中作为列的表头显示)

– 查询emp表中薪资在3000和4500之间的员工,显示姓名和薪资

select name,sal from emp where sal between 3000 and 4500; 

– 查询emp表中薪资为1400、1600、1800的员工,显示姓名和薪资

select name,sal from emp where sal in(1400,1600,1800);

– 查询emp表中姓名中以"刘"开头的员工,显示姓名。

select name  from emp where name like '刘%';

– 查询emp表中姓名以"刘"开头并且不超过2个字的员工,显示姓名。

select  name  from emp where name like '刘_';

– 查询emp表中姓名中包含"涛"员工,显示所有字段。

select * from emp where name like '%涛%';

“%” 表示通配,表示0或多个字符。"_"表示一个字符串

– 查询emp表中薪资大于4000和薪资小于2000的员工,显示姓名、薪资。

select name,sal from emp where sal<2000 or sal >4000;

– 查询emp表中薪资大于3000并且奖金小于600的员工,显示姓名、薪资、奖金。

select name,sal,bonus from emp where sal>3000 and bonus<600;
  1. 排序查询
    对查询的结果进行排序使用 order by关键字。
    order by 排序的列 XXX asc 升序
    order by 排序的列 XXX desc 降序
    – 对emp表中所有员工的薪资进行升序(从低到高)排序,显示姓名、薪资。
 select name ,sal  from  emp  order  by  sal  asc;

– 对emp表中所有员工的总薪资进行降序(从高到低)排序,显示姓名、总薪资。

select name, sal+bonus as 总薪资 from emp order by (sal+bonus) desc;
  1. 分组查询
    对所查询的记录可以根据某一列进行分组, 分组使用group by。
    – 将员工按照部门进行分组
 select * from emp group by dept;

– 对emp表按照部门进行分组, 并统计每个部门的人数, 显示部门和对应人数

select dept 部门名称, count(*) 部门人数 from emp group by dept;

– 对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)

select max(sal) 总薪资 from emp group by dept;
  1. 聚合函数查询
    1、max()或min() – 求所查询记录中指定列的最大值或最小值
    2、count() – 求所查询记录中指定列的记录数
    3、sum() – 求所查询记录中指定列的总和
    4、avg() – 求所查询记录中指定列的平均值

– 查询emp表中最高薪资

select max(sal) as 最高薪资 from emp;

– 查询emp表中最高总薪资

select max(sal+bonus) as 最高总薪资 from emp;

– 统计emp表中薪资大于3000的员工个数

select count(*) from emp where sal>3000;

– 统计emp表中所有员工的总薪资(不包含奖金)

select sum(sal) as 员工总薪资 from emp;

– 统计emp表员工的平均薪资(不包含奖金)

select avg(sal) as 员工平均薪资 from emp;

!!重要提示:
a) 可以使用count(*)统计记录行数
b) 多个聚合函数可以一起查询
– 例如:根据部门进行分组,统计每个部门员工人数和平均薪资

select dept, count(*) 员工人数, avg(sal) 平均薪资 from emp group by dept;

c) 聚合函数不能用在where子句中
d) 在没有分组的情况下,聚合函数不能和其他普通字段一起查询
– 例如: 查询emp表中薪资最高的员工姓名, 下面的写法是错的:
select name, max(sal) from emp;–结果是错的
– 正确的查询:

select name, sal from emp where sal=(
	select max(sal) from emp
);-- 子查询
  1. 其他函数
    数值函数
    (1)ceil(数值) – 向上取整
    (2)floor(数值) – 向下取整
    (3)round(数值) – 向下取整
    (4)rand(数值) – 随机数
    – emp表中所有员工薪资上涨15.47%, 向上取整。
  select name,sal, ceil(sal*1.1547) from emp;

日期函数
(1)curdate() – 返回当前日期(年月日)
(2)curtime() – 返回当前时间(时分秒)
(3)now() – 返回当前日期+时间(年月日 时分秒)
(4)date_add()、date_sub() – 增加/减少日期
(5)year()、month()、day()、hour()、minute()、second(),分别用来获取日期中的年、月、日、时、分、秒

– 查询系统当前时间。

select now();

– 查询emp表中所有员工的年龄,显示姓名、年龄。

select name,year(curdate()) - year(birthday) 年龄 from emp;

– 查询emp表中所有在1993和1995年出生的,显示姓名、出生日期。

select name,birthday from emp where year(birthday) between 1993 and 1995;
  1. 外键和表关系
    **外键:**唯一标识其他表中的一条记录,用来通知数据库两张表列与列之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键。
    例如:员工表的部门id列(dept_id)和部门表的id列具有一 一对应的关系, 其中dept_id就是外键。
    外键作用: 确保数据库数据的完整性和一致性
    添加外键: 例如:foreign key(dept_id) references dept(id)

表关系:
一对多(多对一)
(1)一个班级中可能会有多个学生(1~)
(2)一个学生只能属于一个班级(11),两者合并结果还是1

因此,班级表和学生表是一对多的关系
对于一对多的两张表,可以在多的一方添加列,保存一的一方的主键,从而保存两张表之间的关系
一对一
(1)一个班级对应一个教室(1~1)
(2)一个教室也只对应一个班级(11),两者合并结果还是11
因此,班级表和教室表是一对一的关系
对于一对一关系的两张表,可以在任意一张表中添加列,保存另一张表的主键,从而保存两张表之间的关系
多对多
(1)一个学生对应多个老师(1~)
(2)一个老师也对应多个学生(1*),两者合并结果是*

因此,学生表和老师表是多对多的关系
对于多对多的关系,可以拆分成两张一对多的关系,无法在两张表中添加列保存关系,但我们可以添加一张第三方的表(专门保存两张表的关系),保存两张表的主键,从而保存两张表的关系。
在这里插入图片描述
多表连接查询
连接查询:将两张或者两张以上的表,按照指定条件查询,将结果显示在一张表中。
多张表查询的语法:

select... 
from A, B... 
where...

如果表名过长,可以为表添加别名以方便书写

select... 
from A a, B b... 
where...

上面小写的a和b就是A和B表的别名:

– 查询部门和员工两张表

select * 
from dept,emp;

上面查询的结果中存在大量错误的数据, 如果想正确显示部门及部门对应的员工,可以通过where子句从中筛选正确的数据.
– 查询部门和部门下的员工。

select * 
from dept d,emp e 
where d.id=e.dept_id;
或
select * 
from dept d inner join emp e on d.id=e.dept_id;

上面的查询(inner join…on…)方式也叫做内连接查询
外连接查询
1.左外连接查询
显示左侧表中的所有记录,如果在右侧表中没有对应的记录,则显示为null
语法:

select ...
from a left join b on(a.id=b.xid)

– 查询所有部门和部门下的员工,如果部门下没有员工,显示null

select * 
from dept d left join emp e on d.id=e.dept_id;

以上结果会显示(左侧表)所有部门,如果某部门下没有员工,(右侧表)则显示为null
2. 右外连接查询
显示右侧表中的所有记录,如果在左侧表中没有对应的记录,则显示为null
语法:

select ...
from a right join b on(a.id=b.xid)

– 查询部门和所有员工,如果员工没有所属部门,显示null

select * 
from dept d right join emp e on d.id=e.dept_id;

以上结果会显示(右侧表)所有员工,如果员工没有所属部门,(左侧表)则显示为null
3. 子查询
所谓的子查询,其实就是将一个查询得出的结果,作为另外一个查询的条件。
格式:

select...
from...
where...(select...from...)

(1)列出薪资比’王小二’高的所有员工,显示姓名、薪资
– 先查询出’王小二’的薪资

select sal from emp where name='王小二';

– 再查询比王小二薪资(2450)高的员工

select name, sal 
from emp 
where sal>( select sal from emp where name='王小二');

(2)列出与’赵六’从事相同职位的所有员工,显示姓名、职位、部门。
– 先关联, 查询员工及员工对应的部门

select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id;

– 再查询’赵六’的职位

select name, job from emp where name='赵六';

– 最后筛选, 筛选出和’赵六’相同职位的员工

select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id and job=(select job from emp where name='赵六');

(3)列出薪资高于在’大数据部’(已知部门编号为30)就职的所有员工的薪资的员工姓名和薪资、部门名称。
– 查询出’大数据部’的最高薪资

select max(sal) from emp where dept_id=30;

– 关联查询, 查询员工的姓名,薪资, 部门名称

select e.name, e.sal, d.name from emp e, dept d where e.dept_id=d.id and sal>(select max(sal) from emp where dept_id=30);

多表查询
1、**(左外连接)**列出所有部门和部门下的员工,如果部门下没有员工, 显示为null。

select d.id, d.name, e.name, e.dept_id 
from dept d left join emp e on e.dept_id=d.id;

2、**(关联查询)**列出在’销售部’任职的员工,假定不知道’销售部’的部门编号。
– 先查询员工及员工所属部门

select e.name, e.dept_id, d.id, d.name 
from emp e, dept d 
where e.dept_id=d.id;

– 再筛选过滤,查询部门名称为’销售部’的员工

select e.name, d.id, d.name 
from emp e, dept d 
where e.dept_id=d.id and d.name='销售部';

3、(自连接查询)列出所有员工及其直接上级,显示员工姓名、上级编号,上级姓名
– 先查询员工表(emp e1)

select name, topid from emp e1;

– 再查询上级表(还是员工表,emp e2)

select id, name from emp e2;

– 最后查询员工及其员工的直接上级

select e1.name,e1.topid, e2.id, e2.name 
from emp e1, emp e2 
where e1.topid = e2.id;

4、(分组、聚合函数)列出最低薪资大于1500的各种职位,显示职位和该职位最低薪资
– 先查询出各种职位的最低薪资

select job, min(sal) 最低薪资 
from emp group by job;

提示:对分组后的记录筛选过滤请使用having替换where,并且having书写在最后
– 再查询出最低薪资>1500的职位

select job, min(sal) 最低薪资 
from emp group by job 
having min(sal)>1500;

5、(分组、聚合函数查询)列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量,平均薪资。

select dept_id, count(*) 员工数量, avg(sal) 平均薪资 
from emp group by dept_id;

6、(分组、关联、聚合函数查询)查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
– 先关联查询, 查询出员工和员工对应的部门

select d.id, d.name, d.loc, e.name
from emp e, dept d 
where e.dept_id=d.id;

– 再根据部门进行分组, 统计每个部门的员工数量

select d.id, d.name, d.loc, count(*) 员工数量 
from emp e, dept d 
where e.dept_id=d.id group by e.dept_id;

7、(自连接查询)列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
– 关联查询

select e1.id, e1.name, d.name 
from emp e1, emp e2, dept d 
where e1.dept_id=d.id and e1.topid=e2.id and e1.hdate < e2.hdate;
评论 14 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页

打赏作者

奋斗中的小码农

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值