MYSQL语句

最新推荐文章于 2024-10-12 22:35:37 发布

weixin_46008622

最新推荐文章于 2024-10-12 22:35:37 发布

阅读量85

点赞数

文章标签： java mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_46008622/article/details/115653639

版权

查询语句

#创建雇员表
CREATE TABLE emp(
	empno  INT,
	ename  VARCHAR(50),
	job  VARCHAR(50),
	mgr  INT,
	hiredate  DATE,
	sal  DECIMAL(7,2),
	comm  DECIMAL(7,2),
	deptno  INT
) ;
#添加数据
INSERT INTO emp values(7369,'朱元璋','职员',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'周星驰','销售员',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'范冰冰','销售员',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'赵四','经理',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'李冰冰','销售员',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'李小龙','经理',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'江小白','经理',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'猪刚鬣','分析师',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'王思聪','总裁',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'刘大锤','销售员',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'令狐冲','职员',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'林平之','职员',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'雄霸','分析师',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'吴奇隆','职员',7782,'1982-01-23',1300,NULL,10);

1.查询全部的员工按照薪资升序排序显示
select * from emp order by sal;
2.查询20号部门的员工信息
select * from emp where deptno=20
3.查询薪资小于1500的销售员
select * from emp where sal <1500 and job = '销售员'
4.查询30号部门薪资大于1500的销售员
select * from emp where sal>1500 and job='销售员' and deptno=30
5.查询不是从事职员的员工只显示名字
select ename from emp where job !='职员'
6.查询名字为3个字的员工
select * from emp where ename like '__';
7.查询名字中包含"冰'字的员工
select * from emp where ename like '%冰%'
8.查询名字为两个字的分析师
select * from emp where ename like '_' and job = '分析师’
9.查询薪资在2500到3000的员工。
select * from emp where sal between 2500 and 3000;
10查询全部的销售员按照工资降序显示。
select * from emp where job = '销售员' order by sal desc;


1.统计各部门平均工资，显示部门号与平均工资
select deptno, avg(sal) from emp group by deptno;
2.统计各部门总工资，显示部门号与总工资
select deptno, sum(sal) from emp group by deptno;
3.统计各职位最高工资，显示职位与最高工资
select job, max(sal) from emp group by job;
4.统计各职位平均工资，显示职位与平均工资，再过滤出平均工资高于2500的统计信息。
select job , avg(sal) from emp group by job having avg(sal)>2500
5.统计各部门薪资超过2000的人数。显示部门与人数。
(提示没有超过2000的先剔除，再分组统计）需要使用where和group by)
select deptno,count(*) from emp where sal>2000 group by deptno;