JAVA中SQL查询语句大全,select多表查询,各种查询 |
您所在的位置：网站首页 › java一对一费用 › JAVA中SQL查询语句大全,select多表查询,各种查询 |
JAVA中SQL查询语句大全,select多表查询,各种查询
|
以员工表:emp 为例 idnamegenderbirthdaydeptjobsalbonus编号姓名性别生日部门职位薪资奖金 基本查询 – 查询emp表中的所有员工信息 select * from emp;– 查询emp表中的所有员工的姓名、薪资、奖金 select name,sal,bonus from emp;– 查询emp表中的所有部门, 剔除重复的记录, 提示: distinct用于剔除重复值 select distinct dept from emp; 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) > 大于 =大于等于 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 sal4000;– 查询emp表中薪资大于3000并且奖金小于600的员工，显示姓名、薪资、奖金。 select name,sal,bonus from emp where sal>3000 and bonus3000;– 统计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)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; 外键和表关系 **外键：**唯一标识其他表中的一条记录，用来通知数据库两张表列与列之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键。 例如：员工表的部门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 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; |
多表连接查询 连接查询：将两张或者两张以上的表，按照指定条件查询，将结果显示在一张表中。 多张表查询的语法：【本文地址】