如何通过MySQL练习题一提升你的MySQL技能？

MySQL练习题（一）

数据库设计与创建

1、创建数据库：创建一个名为mysqlpractice的数据库。

2、创建表：在mysqlpractice数据库中创建三个表，分别为DEPT、EMP和SALGRADE。

DEPT表结构如下：

     CREATE TABLE DEPT (
         DEPTNO int(2) NOT NULL,
         DNAME VARCHAR(14),
         LOC VARCHAR(13),
         PRIMARY KEY (DEPTNO)
     );

EMP表结构如下：

     CREATE TABLE EMP (
         EMPNO int(4) NOT NULL,
         ENAME VARCHAR(10),
         JOB VARCHAR(9),
         MGRiNT(4),
         HIREDATE DATE DEFAULT NULL,
         SAL DOUBLE(7,2),
         COMM DOUBLE(7,2),
         PRIMARY KEY (EMPNO),
         FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
     );

SALGRADE表结构如下：

     CREATE TABLE SALGRADE (
         GRADE INT,
         LOSAL INT,
         HISAL INT
     );

数据插入与查询

3、插入数据：向上述三个表中插入初始数据。

   -插入到DEPT表
   INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES 
   (10, 'ACCOUNTING', 'NEW YORK'),
   (20, 'RESEARCH', 'DALLAS'),
   (30, 'SALES', 'CHICAGO'),
   (40, 'OPERATIONS', 'BOSTON');
   -插入到EMP表
   INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES 
   (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
   (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
   (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
   (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
   (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
   (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
   (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
  (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20),
  (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
  (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
  (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20),
  (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
  (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
  (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

4、查询语句练习：执行以下查询操作，并理解每个查询的目的。

查询所有部门的名称和地点：

     SELECT DNAME, LOC FROM DEPT;

查询工资高于1500的员工信息：

     SELECT * FROM EMP WHERE SAL > 1500;

查询在’ACCOUNTING’部门工作的员工的姓名和职位：

     SELECT ENAME, JOB FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING');

查询每个部门的员工数量：

     SELECT DEPTNO, NAMme(COUNT(*)) AS EMP_COUNT FROM EMP GROUP BY DEPTNO;

查询平均工资最高的部门：

     SELECT DEPTNO FROM EMP GROUP BY DEPTNO ORDER BY AVG(SAL) DESC LIMIT 1;

查询所有经理的信息：

     SELECT * FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP);

查询员工入职日期在1981年之后的详细信息：

     SELECT * FROM EMP WHERE HIREDATE > '1981-01-01';

查询当前不在任何项目中工作的员工信息：

     SELECT * FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT EMPNO FROM PROJECT);

查询工资等级在’SALGRADE’表中的员工信息：

     SELECT E.* FROM EMP E, SALGRADE S
     WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

查询每个部门的最高工资：

     SELECT DEPTNO, WAGE FROM (SELECT DEPTNO, SAL AS WAGE FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP))) AS TEMP GROUP BY DEPTNO;

查询受雇于1981年之前的员工名单：

     SELECT * FROM EMP WHERE HIREDATE < '1981-01-01';

查询所有部门的地理位置：

     SELECT DNAME, LOC FROM DEPT;

查询工资范围在500到1500之间的员工信息：

     SELECT * FROM EMP WHERE SAL BETWEEN 500 AND 1500;

查询各个部门的工资总和：

     SELECT DEPTNO, SUM(SAL) AS TOTAL_SALARY FROM EMP GROUP BY DEPTNO;

查询每个部门平均薪资最高的部门：

     SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= ALL (SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);

查询入职时间最晚的三名员工：

     SELECT * FROM EMP ORDER BY HIREDATE DESC LIMIT 3;

查询各部门工资最低的员工：

     SELECT A.* FROM EMP A WHERE A.SAL = (SELECT MIN(B.SAL) FROM EMP B WHERE A.DEPTNO = B.DEPTNO);

查询所有部门名称及其员工人数多于5人的部门：

     SELECT D.DNAME FROM DEPT D WHERE (SELECT COUNT(*) FROM EMP E WHERE E.DEPTNO = D.DEPTNO) > 5;

查询工资比’KING’高的员工：

     SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'KING');

查询未被指派项目的员工：

     SELECT * FROM EMP E WHERE NOT EXISTS (SELECT * FROM PROJECT P WHERE E.EMPNO = P.EMPNO);

查询工资等于部门平均工资的员工：

     SELECT * FROM EMP E1 WHERE SAL = (SELECT AVG(SAL) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO);

查询员工姓名包含字母’A’的员工信息：

     SELECT * FROM EMP WHERE ENAME LIKE '%A%';

查询所有销售代表的信息：

     SELECT * FROM EMP WHERE JOB = 'SALESMAN';

查询每个部门中工资最高的员工信息：

     SELECT T1.* FROM EMP T1 JOIN (SELECT DEPTNO, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP))) AS T2 ON T1.DEPTNO = T2.DEPTNO AND T1.SAL = T2.SAL;

查询员工编号为奇数的员工信息：

     SELECT * FROM EMP WHERE EMPNO % 2 <>= 1;

查询入职年份为1982年的员工信息：

     SELECT * FROM EMP WHERE YEAR(HIREDATE) = 1982;

查询所有部门名称以’A’开头的部门：

     SELECT * FROM DEPT WHERE DNAME LIKE 'A%';

查询所有部门名称以’R’结尾的部门：

     SELECT * FROM DEPT WHERE DNAME LIKE '%R';

查询所有部门名称以’R’开头并且地点在纽约的部门：

     SELECT * FROM DEPT WHERE DNAME LIKE 'R%' AND LOC = 'NEW YORK';

查询所有部门名称第二个字母为’A’的部门：

     SELECT * FROM DEPT WHERE DNAME = '_A%';

根据员工姓名查询员工所属的部门名称：

     SELECT D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME = 'SCOTT';

根据员工姓名查询该员工的工作内容：

     SELECT JOB FROM EMP WHERE ENAME = 'SCOTT';

根据员工姓名查询该员工入职的年份：

     SELECT YEAR(HIREDATE) AS YEAR_HIRED FROM EMP WHERE ENAME = 'SCOTT';

根据员工姓名查询该员工的直接上级是谁：

     SELECT MGR FROM EMP WHERE ENAME = 'SCOTT';

根据员工姓名查询该员工是否参与过项目管理：

     SELECT * FROM PROJECT P, (SELECT * FROM EMP E WHERE P.EMPNO = E.EMPNO AND E.ENAME = 'SCOTT');

根据员工姓名查询该员工的工资水平：

     SELECT SAL FROM EMP WHERE ENAME = 'SCOTT';

根据员工姓名查询该员工的奖金水平：

     SELECT COMM FROM EMP WHERE ENAME = 'SCOTT';

根据员工姓名查询该员工是否享有公司股票期权计划：

     SELECT * FROM STOCK S, (SELECT * FROM EMP E WHERE S.EMPNO = E.EMPNO AND E.ENAME = 'SCOTT');

根据员工姓名查询该员工的家庭住址：

     SELECT LOC FROM DEPT D,JOIN EMP E ON D.DEPTNO = E.DEPTNO WHERE E.ENAME = 'SCOTT';

根据员工姓名查询该员工的电话号码：

     SELECT PHONO FROM EMP PHE.PHONE = P.PHONE AND E.ENAME = 'SCOTT';

根据员工姓名查询该员工的电子邮件地址：

     SELECT MAIL FROM EMP MHE.MAIL = M.MAIL AND E.ENAME = 'SCOTT';

根据员工姓名查询该员工的学历背景：

     SELECT EDUCATION FROM EMP HEducation = E.EDUCATION AND E.ENAME = 'SCOTT';

根据员工姓名查询该员工的技能专长：

     SELECT SKILLS FROM EMP SKILLS = SKILLS AND E.ENAME = 'SCOTT';

根据员工姓名查询该员工的工作经历：

     SELECT * FROM JOB_HISTORY JHJOIN EMP E ON JH.EMPNO = E.EMPNO AND E.ENAME = 'SCOTT';

根据员工姓名查询该员工的培训记录：

     SELECT * FROM TRAINING TJOIN EMP E ON T.EMPNO = E.EMPNO AND E.ENAME = 'SCOTT';