第四周总结

最新推荐文章于 2022-12-11 14:21:34 发布

海染蓝了天

最新推荐文章于 2022-12-11 14:21:34 发布

阅读量98

点赞数

本文链接：https://blog.csdn.net/weixin_43939659/article/details/97611566

版权

分组查询
作用通过哪个或那些字段进行分组
用法 group by；
计算出不同岗位的最高薪水
select
deptno,job,max(sal) as maxsal
from
emp
group by
deptno,job;
找出每个工作岗位的最高薪水，除MANAGER之外
select
job,max(sal) as maxsal
from
emp
where
job <> ‘MANAGER’
group by
job;
找出每个工作岗位的平均薪水，要求显示出平均薪水大于2000的
select
job,avg(sal) as avgsal
from
emp
group by
job
having
avgsal > 2000;
having也是过滤条件必须的有group by
顺序 select from where group by having order by
查询员工名称和部门名称
多表查询
年代分类
SQL92 select ename,dname from emp as e,dept as d where e.deptno = d.deptno;
SQL99
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
连接方式
1.内连接省略了一个关键字inner
等值连接
查询员工名称和部门名称

select
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;

非等值连接
查询出员工薪水所对应的薪水等级显示员工名称，薪水，薪水等级

select															
	e.ename,e.sal,s.grade						
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SIMITH |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

自连接把一张表看成两张表使用
查询出员工所对应的领导名称:显示员工名称和领导名称


select
	a.ename,b.ename as leadername
from
	emp a
join
	emp b
on
	a.mgr = b.empno;
+--------+------------+
| ename  | leadername |
+--------+------------+
| SIMITH | FORD       |
| ALLEN  | BLAKE      |
| WARD   | BLAKE      |
| JONES  | KING       |
| MARTIN | BLAKE      |
| BLAKE  | KING       |
| CLARK  | KING       |
| SCOTT  | JONES      |
| TURNER | BLAKE      |
| ADAMS  | SCOTT      |
| JAMES  | BLAKE      |
| FORD   | JONES      |
| MILLER | CLARK      |
+--------+------------+

2.外连接省略了一个关键字 outer
外连接查询的结果条数>= 内连接查询的结果条数
左外连接/连接

select
	a.ename,b.ename as leadername
from
	emp a
left outer join
	emp b
on
	a.mgr = b.empno;

右外连接/有连接

select
	e.ename,d.dname
from
	emp e
right outer join
	dept d
on
	e.deptno = d.deptno;

区分内外连接关键字right lift
3.全连接
查询出员工的部门名称，员工的领导名称和薪水等级

select
	d.dname,
	e.ename,
	b.ename as leadername,
	s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	emp b
on
	e.mgr = b.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

子查询
找出薪水比公司平均薪水高的员工，要求显示员工名和薪水

select ename,sal from emp where sal > (select avg(sal) as avgsal from emp);

找出每个部门的平均薪水并且要求显示平均薪水的薪水等级

select
	t.avgsal,s.grade
from
	(select
		e.deptno,avg(e.sal) as avgsal
	from
		emp e
	group by
		e.deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;
+-------------+-------+
| avgsal      | grade |
+-------------+-------+
| 2175.000000 |     4 |
| 1566.666667 |     3 |
| 2916.666667 |     4 |
+-------------+-------+

查询出工作岗位为manager和salesaman的员工

select ename,job from emp where job = 'MANAGER' or job ='SALESMAN';
select ename,job from emp where job in ('MANAGER','SALESMAN');
UNION合并相加结果集
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

union字段名一样字段个数要一样
limit用法开始长度，截取长度

 select ename,sal from emp order by sal desc limit 4,4;
+--------+---------+
| ename  | sal     |
+--------+---------+
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
+--------+---------+
分页
页码 pageNo			pageSize:3
								limit (pageNo-1)*pageSize,pageSize

创建表
create table t_表名/tb1_表名(
字段名称字段类型字段长度字段约束,
字段名称字段类型字段长度字段约束,
字段名称字段类型字段长度字段约束,
字段名称字段类型字段长度字段约束
);
常用的数据类型
char char(n) 定长字符串存储空间大小固定
varchar varchar(n) 变长字符串变长字符串存储空间等于实际数据空间
int 4个字节
bigint 8个字节
float
double
date 8个字节
BLOB 二进制大对象
CLOB 字符大对象

创建表

create table t_student(
	no int(4),
	name varchar(32),
	gender char(1),
	birth date,
	email varchar(128)
);

删除表

drop table t_student;
drop table if exists t_student;

表的复制

create table 表名 as 查询语句
create table emp_bak as select * from emp;

DML语句: insert update delete
insert语法: insert into 表名（字段名,字段名,字段名,字段名） values（字段值,字段值,字段值,字段值);

insert into t_student(no,name,gender,birth,email) values(1,'zhangsan','1',str_to_date('1949-10-01','%Y-%m-%d'),'zhangsan@126.com');

insert into t_student(no,name,gender,birth,email) values(2,'lisi','0','1949-11-11','lisi@126.com');

insert into t_student(no,name,gender,birth,email) values(3,'hanmeimei','0',str_to_date('11-11-1950','%m-%d-%Y'),'hanmeimei@126.com');

insert into t_student(no,name,gender) values(4,'lilei','1');

insert into t_student values(5,'jerry','1','1949-01-01','jerry@126.com'); 省略前边最好不要用

update 语法：update 表名 set 字段名称 = 字段值,字段名称 = 字段值,字段名称 = 字段值 where 限制条件;

update t_student set birth = str_to_date('1951-10-10','%Y-%m-%d') where no = 4;
update t_student set gender = 0, email = 'lilei' where no = 4;

delete语法

delete from t_student;//删除表中所有的数据
delete fromt_student where no = 4；

默认值

create table t_student(
	no int(4),
	name varchar(32),
	gender char(1) default '1',
	birth date,
	email varchar(128)
);
insert into t_student(no,name) values(1,'zhangsan');
+------+----------+--------+-------+-------+
| no   | name     | gender | birth | email |
+------+----------+--------+-------+-------+
|    1 | zhangsan | 1      | NULL  | NULL  |
+------+----------+--------+-------+-------+

没有给gender赋值但是也有值 创建表的时候有了默认值

在这里插入图片描述
答案

package 算法;

import java.util.Arrays;
import java.util.Scanner;

public class 三个数之和接近目标值 {
	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		String[] temp = null;
		temp = sc.nextLine().split(" ");
		int[] nums = new int[temp.length];
		for(int i=0;i<nums.length;i++) {
			nums[i] = Integer.valueOf(temp[i]);
		}
		int target = sc.nextInt();
		System.out.println(threenum(nums,target));
		
	}
	public static int threenum(int[] nums,int target) {
		Arrays.sort(nums);
		int a = nums[0] + nums[1] + nums[2];
		for(int i=0;i<nums.length-2;i++) {
			int j = i+1;
			int k = nums.length-1;
			while(j<k) {
				int temp = nums[i] + nums[j] + nums[k];
				if(Math.abs(temp-target)<Math.abs(a-target)) {
					a = temp;
				}
				if(temp<target) {
					j++;
				}else if(temp>target) {
					k--;
				}else {
					return temp;
				}
			}
		}
		return a;
	}
}

做题思路
这个题感觉在哪见过后来想起来以前发算法题的时候在力扣见过这个题，当时就不咋会就又挑了一个，这个题开始的时候只输入数组和目标值然后在输入的时候用了不知道数组长度的输入方式
先对数组进行排序然后将前三个数加起来当做初始化，然后用循环j代表当前i下一个元素 k代表最后一个元素因为用sort从小到大排过序，然后在用temp存储nums[i]+nums[j]+nums[k];的大小加绝对值比较Math.abs(temp-target)，Math.abs(a-target)大小。temp的值和目标值比大小如果小就j++
如果大就k-- 相等的话就返回temp.

海染蓝了天

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第四周总结

分组查询作用通过哪个或那些字段进行分组用法 group by；计算出不同岗位的最高薪水selectdeptno,job,max(sal) as maxsalfromempgroup bydeptno,job;找出每个工作岗位的最高薪水，除MANAGER之外selectjob,max(sal) as maxsalfromempwherejob <> ‘M...
复制链接

扫一扫