socct数据库

qq_2372430565

已于 2022-07-18 19:49:53 修改

阅读量960

点赞数

文章标签： mysql

于 2022-04-11 15:55:13 首次发布

本文链接：https://blog.csdn.net/qq_46001323/article/details/124101343

版权

本文通过创建和插入dept, emp, salgrade表的数据，展示了MySQL的基本操作，包括表的创建、数据插入以及复杂的查询练习。查询练习涵盖了员工信息、部门信息、薪资等级等，涉及子查询、联接、聚合函数等多个方面，充分体现了数据库查询的灵活性和实用性。

摘要由CSDN通过智能技术生成

mysql一天学会 link

创建dept,emp,salgrade表

CREATE TABLE dept
(
  DEPTNO INT PRIMARY KEY,
  DNMAE VARCHAR(14),
  LOC VARCHAR(13)
);

CREATE TABLE emp
(
 EMPNO INT(4) PRIMARY KEY,
 ENAME VARCHAR(10),
 JOB VARCHAR(9),
 MGR INT(4),
 HIREDATE DATE,
 SAL DOUBLE,
 COMM DOUBLE,
 DEPTNO INT,
FOREIGN KEY (DEPTNO) REFERENCES dept(DEPTNO));

CREATE TABLE  salgrade
(
  GRADE INT PRIMARY KEY,
  LOSAL INT,
  HISAL INT);

插入emp数据

INSERT INTO emp VALUES

(7369,'SMITH','CLERK',7902,DATE('1980-12-17'),800,NULL,20);

INSERT INTO emp VALUES

(7499,'ALLEN','SALESMAN',7698,DATE('1981-2-20'),1600,300,30);

INSERT INTO emp VALUES

(7521,'WARD','SALESMAN',7698,DATE('1981-2-22'),1250,500,30);

INSERT INTO emp VALUES

(7566,'JONES','MANAGER',7839,DATE('1981-4-2'),2975,NULL,20);

INSERT INTO emp VALUES

(7654,'MARTIN','SALESMAN',7698,DATE('1981-9-28'),1250,1400,30);

INSERT INTO emp VALUES

(7698,'BLAKE','MANAGER',7839,DATE('1981-5-1'),2850,NULL,30);

INSERT INTO emp VALUES

(7782,'CLARK','MANAGER',7839,DATE('1981-6-9'),2450,NULL,10);

INSERT INTO emp VALUES

(7788,'SCOTT','ANALYST',7566,DATE('1987-4-19'),3000,NULL,20);

INSERT INTO emp VALUES

(7839,'KING','PRESIDENT',NULL,DATE('1981-11-17'),5000,NULL,10);

INSERT INTO emp VALUES

(7844,'TURNER','SALESMAN',7698,DATE('1981-9-8'),1500,0,30);

INSERT INTO emp VALUES

(7876,'ADAMS','CLERK',7788,DATE('1987-5-23'),1100,NULL,20);

INSERT INTO emp VALUES

(7900,'JAMES','CLERK',7698,DATE('1981-12-3'),950,NULL,30);

INSERT INTO emp VALUES

(7902,'FORD','ANALYST',7566,DATE('1981-12-3'),3000,NULL,20);

INSERT INTO emp VALUES

(7934,'MILLER','CLERK',7782,DATE('1982-1-23'),1300,NULL,10);

插入dept数据

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');

插入salgrade数据

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，列出至少有四个员工的所有部门。

SELECT d.DNMAE FROM emp e LEFT JOIN dept d using(DEPTNO)  GROUP BY DNMAE HAVING count(e.ENAME) >=4 ;

-- 02，列出新金比“SMITH"多的所有员工。(大于 最大薪水SMITH员工)

 SELECT * FROM emp WHERE SAL > (SELECT SAL FROM emp WHERE ENAME = 'SMITH');
 
 SELECT * FROM emp e,(SELECT SAL FROM emp WHERE ENAME = 'SMITH') s WHERE e.SAL> s.SAL;

-- 03,列出所有员工的姓名及其直接上级的姓名。

SELECT e.ENAME 上级,e.EMPNO,y.ENAME 员工,y.MGR FROM emp e ,emp y WHERE e.EMPNO=y.MGR;

-- 04,列出受雇日期早于其直接上级的所有员工。

 SELECT e.ENAME 上级,e.EMPNO,y.ENAME 员工,y.MGR ,e.HIREDATE 上级 ,y.HIREDATE 员工 FROM emp e ,emp y WHERE e.EMPNO=y.MGR  AND e.HIREDATE > y.HIREDATE; 

-- 05,列出部门名称和这些部门门的员工信息，包括那些没有员工的部门。

SELECT d.DNMAE, e.ENAME FROM emp e RIGHT JOIN dept d using(DEPTNO);

-- 06,列出所有job为“CLERK" (办事员)的姓名及其部门]名称。

SELECT e.ENAME,d.DNMAE FROM emp e JOIN dept d USING(DEPTNO) WHERE e.JOB='CLERK';

-- 07，列出最低薪金大于2500的各种工作。

SELECT JOB,min(SAL) FROM emp GROUP BY JOB HAVING min(SAL)>2500;

-- 08，列出在部门"SALES" (销售部)工作的员工的姓名。

SELECT * FROM emp e LEFT JOIN dept d USING(DEPTNO)  WHERE d.DNMAE = 'SALES';
SELECT e.ENAME,d.DNMAE FROM emp e LEFT JOIN dept d USING(DEPTNO)  WHERE d.DNMAE = 'SALES';

-- 09，列出薪金高于公司平均薪金的所有员工。

SELECT * FROM emp WHERE SAL > (SELECT avg(SAL) FROM emp); 

-- 10,列出与“ALLEN"从事相同:工作的所有员工。

SELECT * FROM emp WHERE JOB =(SELECT JOB FROM emp WHERE ENAME = 'ALLEN') AND ENAME != 'ALLEN';

-- 11,列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

SELECT ENAME,SAL FROM emp WHERE DEPTNO = 30;
SELECT ENAME,SAL FROM emp WHERE SAL in (SELECT SAL FROM emp WHERE DEPTNO = '30') ;

-- 12,列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT ENAME,SAL FROM emp WHERE SAL > (SELECT max(SAL) FROM emp WHERE DEPTNO =30);
SELECT ENAME,SAL FROM emp WHERE SAL > ALL(SELECT SAL FROM emp WHERE DEPTNO =30) ;

-- 13,列出在每个部门工作的员工数量、平均工资和平均服务期限。

select count(*),avg(sal),avg(DATEDIFF(CURRENT_DATE,HIREDATE))from emp group by deptno;

-- 14,列出所有员工的姓名、部门名称和工资。

SELECT e.ENAME,d.DNMAE,e.SAL FROM emp e RIGHT JOIN dept d USING( DEPTNO);

-- 15,列出从事同一种工作但属于不同部门的员工的一 种组合。

SELECT e.ENAME,e.JOB,e.DEPTNO,m.ENAME,m.JOB,d.DEPTNO FROM emp e, emp m JOIN dept d USING (DEPTNO) WHERE   e.JOB=m.JOB and  e.DEPTNO != d.DEPTNO;

-- 16,列出所有部门的详细信息和部门人数。

SELECT d.DNMAE,count(e.EMPNO) FROM emp e JOIN dept d USING(DEPTNO) GROUP BY d.DNMAE

-- 17,列出各种工作的最低工资。

SELECT JOB,min(SAL) FROM emp   GROUP BY JOB ;

-- 18,列出各个部门]的MANAGER(经理)的最低薪金(job为MANAGER)。

SELECT  JOB,min(SAL) FROM emp WHERE JOB='MANAGER';
SELECT  JOB,min(SAL) FROM emp WHERE JOB='MANAGER'GROUP BY DEPTNO ;

-- 19,列出所有员工的年工资，按年薪从低到高排序

SELECT ENAME, (SAL+IFNULL(COMM,0))*12 年薪 FROM emp ORDER BY  (SAL+IFNULL(COMM,0))*12;

-- 20,列出所有job= 'CLERK' 的员工平均薪资

SELECT AVG(SAL) FROM emp WHERE job= 'CLERK';

-- 21 ,列出job= "CLERK'员工的平均薪资按照部门]分组

SELECT DEPTNO,AVG(SAL) FROM emp WHERE job= 'CLERK' GROUP BY DEPTNO;

-- 22,列出job= 'CLERK'员工的平均薪资按照部门分组并且部门编号in(10,30) 按照平均薪资降序排列

SELECT DEPTNO,AVG(SAL) FROM emp WHERE job= 'CLERK' AND DEPTNO in(10,30) GROUP BY DEPTNO ORDER BY AVG(SAL) DESC;

-- 23 ,列出job= CLERK'员工的平均薪资按照部门分组并且部门编号in(20,30) 并且部门员工数量>=2人按照平均薪资降序排列

SELECT DEPTNO,AVG(SAL) FROM emp WHERE job= 'CLERK' AND DEPTNO in(20,30) GROUP BY DEPTNO  HAVING count(EMPNO)>=2 ORDER BY AVG(SAL) ;