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的; */