mysql计算员工的日薪_mysql 综合练习（一）

-- 创建部门表

DROP TABLE IF EXISTS dept;

CREATE TABLE dept(

-- 部门编号

deptno int PRIMARY KEY,

-- 部门名称

dname VARCHAR(14),

-- 部门所在地

loc VARCHAR(13)

);

-- 向部门表插入数据

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

-- 创建员工表

DROP TABLE IF EXISTS emp;

CREATE TABLE emp(

-- 员工编号

empno int PRIMARY KEY,

-- 员工姓名

ename VARCHAR(10),

-- 工作岗位

job VARCHAR(9),

-- 直属领导

mgr int,

-- 入职时间

hiredate DATE,

-- 工资

sal double,

-- 奖金

comm double,

-- 所属部门

deptno int

);

-- 为员工表表添加外键约束

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY EMP(deptno) REFERENCES dept (deptno);

-- 向员工表插入数据

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);

INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);

INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);

INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);

INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);

INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);

INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);

INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);

INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);

INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);

INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);

INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);

INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);

-- 创建工资等级表

DROP TABLE IF EXISTS salgrade;

CREATE TABLE salgrade(

-- 等级

grade int,

-- 最低工资

losal double,

-- 最高工资

hisal double

);

-- 向工资等级表插入数据

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);

– 01、查询部门编号为30的部门的员工详细信息

SELECT * FROM emp WHERE deptno = 30;

– 02、查询从事clerk工作的员工的编号、姓名以及其部门号

SELECT empno,ename,deptno FROM emp WHERE job = ‘clerk’;

– 03、查询奖金多于基本工资的员工的信息

SELECT * FROM emp WHERE comm > sal;

– 04、查询奖金多于基本工资60%的员工的信息

SELECT * FROM emp WHERE comm > sal * 0.6;

– 05、查询部门编号为10的部门经理和部门编号为20的部门中工作为CLERK的职员信息

SELECT * FROM emp WHERE deptno = 10 AND job=‘MANAGER’ OR deptno = 20

AND job = ‘CLERK’;

– 06、查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息

SELECT * FROM emp WHERE deptno = 10 AND job=‘MANAGER’ OR deptno = 20

AND job = ‘CLERK’ OR job NOT IN (‘MANAGER’,‘CLERK’) AND sal > 2000 ;

– 07、查询获得奖金的员工的信息

SELECT * FROM emp WHERE comm > 0 and comm is not null;

– 08、查询奖金少于100或者没有获得奖金的员工的信息

SELECT * FROM emp WHERE comm < 100 OR comm IS NULL;

– 09、查询姓名以A、B、S开头的员工的信息

SELECT * FROM emp WHERE ename LIKE ‘A%’ OR ename LIKE ‘B%’ OR ename

LIKE ‘S%’;

– 10、查询找到姓名长度为6个字符的员工的信息

SELECT * FROM emp WHERE LENGTH(ename) = 6;

//几个下划线就占几个字符

SELECT * FROM emp WHERE ename LIKE ‘______’;

– 11、查询姓名中不包含R字符的员工信息。

SELECT * FROM emp WHERE ename NOT LIKE ‘%R%’;

– 12、查询员工的详细信息并按姓名排序

SELECT * FROM emp ORDER BY ename ASC;

– 13、查询员工的信息并按工作降序工资升序排列

SELECT * FROM emp ORDER BY job DESC , sal ASC;

– 14、计算员工的日薪(按30天计)

//As 取别名，可要可不要

SELECT ename,sal/30 AS '日薪' FROM emp;

– 15、查询姓名中包含字符A的员工的信息

SELECT * FROM emp WHERE ename LIKE ‘’%A%’;

– 16、查询拥有员工的部门的部门名和部门号

SELECT DISTINCT d.dname, d.deptno FROM dept d,emp e WHERE d.deptno =

e.deptno;

Select DISTINCT d.deptno,d.dname from emp e, dept d where e.JOB IS NOT

NULL;

– 17、查询工资多于smith的员工信息。

SELECT *FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename =

‘smith’);

………………**`后面的还没怎么弄懂，会持更新的，感谢关注