sql查询语句：查询公司员工薪水、岗位等问题
show databases;
use esupermarket;
show tables;
select * from tbl_product limit 2,5;
select id,productno,title,price,sellpoint,status,image1,image2,image3,image4,image5 from tbl_product limit 5,5;
select count(id) from tbl_product;
select
ta.id,ta.`name`,ta.age,tb.source
from
tablea ta, tableb tb
where ta.id = tb.id;
select
ta.id,ta.`name`,ta.age,tb.source
from tablea ta
left join tableb tb
on ta.id = tb.id;
select
ta.id,ta.`name`,ta.age,tb.source
from tablea ta
right join tableb tb
on ta.id = tb.id;
/* 查询当前使用数据库版本, */
select database();
/* 显示当前库有哪些表 */
show tables;
show databases;
use test2;
show tables from esupermarket;
desc tablea;
show create table tablea;
select database();
use bjpowernode;
show tables;
desc dept;
show tables;
select empno, ename from emp;
select * from emp;
/* 计算员工年薪 */
select empno, ename, job, sal*12 as '年薪'
from emp;
/* 过滤掉大于1000的员工 */
/* 列出员工的编号，姓名和年薪 */
select empno, ename, job, sal
from emp
where sal < 1000;
/* 条件查询 */
select empno, ename, job,sal
from emp
where sal = 5000;
/* 查询职位为manager的员工 */
select empno,ename,job
from emp
where job = 'manager';
select *
from emp
where sal != 5000;
select *
from emp
where sal != '5000';
select *
from emp
where sal < '3000';
/* 以下显示1981-02-20以后的记录*/
select *
from emp
where hiredate > '1981-02-20';
/* 以下显示1981-02-20以前的记录*/
select *
from emp
where hiredate < '1981-02-20';
/* 查询薪水1600到3000之间的员工 */
select *
from emp
where sal>1600 and sal < 3000;
select *
from emp
where sal between 1600 and 3000;
select empno, ename
from emp
where ename between 'a' and 'd';
show tables;
desc emp;
/* 查询员工津贴为null的记录 */
/* 因为null类型比较特殊，必须使用 is 来比较 */
select *
from emp
where comm is null;
/* 查询员工津贴不为null的记录 */
select *
from emp
where comm is not null;
/* 查询工作岗位为“manager”并且薪水大于2500的员工 */
select *
from emp
where job between 'manager' and sal < 2500;
/* 查询出 job 为 salesman 和 job为manager的员工 */
select *
from emp
where job = 'manager' or job = 'clerk';
/* 查询薪水大于1800，并且部门编号为20或30的员工 */
select
empno,ename,deptno,sal
from
emp
where
sal > '1800'
and
(deptno = '20' or deptno = '30');
select
empno,ename,deptno,sal
from
emp
where
sal > '1800'
having
deptno
in
('20','30');
/* 查询出job为 salesman 和 job为 manager 的员工 */
/* in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些 */
select
*
from
emp
where
job
in
('salesman','manager');
/* 查询出薪水为1600和3000的员工 */
select
empno, ename, sal
from
emp
where
sal
in
('1600','3000');
select
empno, ename, sal
from
emp
where
sal = '1600' or sal = '3000';
/* 查询出薪水在1600和3000的之间员工 */
select
empno, ename, sal
from
emp
where
sal between '1600' and '3000';
/* 内连接 */
select
e.empno,e.ename,d.dname,e.comm
from
emp e ,dept d
where
e.deptno = d.deptno
having
e.comm is null;
/* 模糊查询like */
select
*
from
emp
where
ename like '%s%';
/* 查询姓名中第二个字符为a的所有员工 */
select
empno, ename
from
emp
where
ename like '_a%';
/* 查询ename所有倒数第二个字母为e的 */
select
empno, ename
from
emp
where
ename like '%e_';
/* 查询姓名中第三个字符为n的所有员工姓名 */
select
empno,ename
from
emp
where
ename like '__n%';
update
emp
set
ename ='ki_ng'
where
empno = '7839';
update
emp
set
ename = 'jo%nn'
where
empno = '7566';
/* 转义查询包含通配符 escape */
select
empno,ename,sal
from
emp
where
ename like '%/_%'
escape "/";
select
empno,ename,sal
from
emp
where
ename like '%/%%'
escape '/';
/* 按照薪水由小到大排序（系统默认由小到大） */
select
empno,ename,sal
from
emp
where
job = 'manager'
order by
sal asc;
/* 多个字段排序 */
/* 按照 job 和薪水倒序排序,首先按照job降序排列.然后在按照sal降序排列 */
select
e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from
emp e, dept d
where
d.deptno = '20'
order by
e.job desc,e.sal asc;
/* 将大写转换成小写 */
select
empno,lower(ename) as ename,sal
from
emp;
/* 查询员工姓名中第二个字母为a的所有员工 */
select
empno,ename
from
emp
where
substr(ename,'2','1') = 'a';
select
empno,ename,sal,mgr
from
emp
where
substr(mgr,'2','2') = '69';
select
empno,ename,sal,mgr
from
emp
where
mgr like '_69%';
/* 取得员工姓名长度 */
select
empno,ename,length(ename) as enamelength
from
emp;
/* 取得工作岗位为manager的所有员工 */
/* 使用trim()函数,去除" manager "前后的空格,可以取得查询结果 */
select
empno,ename,sal,job
from
emp
where
job = trim('manager');
select rand();
select
empno,ename,sal,job
from
emp
order by sal desc;
select
empno,ename,sal,job
from
emp
order by sal asc;
select * from emp;
select
deptno,sum(sal) as sal_total
from
emp
group by
deptno
order by
sum(sal) asc;
select
deptno,ename,sal
from
emp
group by
deptno,ename,sal
order by
deptno,sal asc;
/* 去重关键字 */
select distinct job from emp;
select
*
from
emp
where
false;
select
*
from
emp
where
true;
show databases;
use bjpowernode;
show tables;
/* ifnull 空值处理函数 */
select
ename,sal,comm,(sal + ifnull(comm,0))*12 as '年收入'
from
emp;
/* 没有补助的员工 将补助100 计算年薪 */
select
ename,sal,comm,ifnull(comm,100) as '补助',(sal + ifnull(comm,100))*12 as '年薪'
from
emp;
select
*
from
emp
where
hiredate = '1981-02-20';
/* where过滤 年月日 y要大写 d m 小写 */
select
ename,hiredate
from
emp
where
hiredate = str_to_date('17-12-1980','%d-%m-%y');
/* 创建表格语句 */
create table t_student(
id int(4),
name varchar(32),
birth date
);
select * from t_student;
insert into
t_student
values
('1','lisi','1925-12-22');
insert into
t_student
values
('2','zhangsan',str_to_date('12-31-2001','%m-%d-%y'));
insert into
t_student
values
('4','wangwu',str_to_date('31-12-2010','%d-%m-%y'));
/* data_format函数主要用在数据库查询操作中。实际工作中，客户需要日期以特定格式展示的时候，需要使用该函数 */
/* 匹配工作岗位，当为manager时，薪水上调10%，当为salesman时，薪水上调50%，其它岗位薪水不变 */
select
empno, ename, job, sal, (case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end) as 'newsal'
from emp;
select * from emp;
/* 匹配部门列表 当部门编号为10 去20部门 20部门去30部门 其他不变 */
select
empno,ename,job,sal,deptno,(case deptno when '10' then deptno+10 when '20' then deptno+10 else deptno end) newdeptno
from emp;
select
count(comm)
from
emp;
select
sum(sal+comm)
from
emp;
select
sum(sal + ifnull(comm,0)) as 'sal+comm'
from
emp;
/* 取得最晚入职的员工 */
select
*
from
emp
where
hiredate = (
select
max(hiredate)
from
emp
);
select
distinct
e.deptno, d.dname, e.job
from
emp e, dept d
where
e.deptno = d.deptno
order by
e.deptno;
/* 查询该公司有几个工作岗位 */
select
count(distinct job) as jobnum
from
emp;
/* 按照工作岗位分组，然后对每一组求最大值。sql语句中肯定包含 group by、max(sal)； */
select
job,
max(sal)
from
emp
group by
job;
/* 找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的； */
