SQL数据库实战（含建表数据和查询案例）

迈不过友情╰ 2022-06-06 12:26 255阅读 0赞

Oracle数据库安装的时候会自带一个练习用数据库（其中包含employee表，后来版本中此表改名为emp）；  
首先在安装过程中应该有个选项“是否安装实例表”（完全安装模式下默认是选择的），需要选择才有此表；

此表归属于scott账户，scott用户默认口令为tiger  
如果发现scott账户已过期（Oracle 10g中经常发生），或口令不正确，可以通过以下方法进行设置

--账户解锁  
    alter user scott account unlock;  
    --重设scott账户口令为tiger  
    alter user scott identified by tiger;

如果想通过这个例子练手，下面是对应的建表语句，读者可以自行创建数据库进行练习（特别是Mysql用户）。对应的练习题，网上有很多，在这里博主总结了一些教材中的案例由于练习，如有错误请指出。

## 建表语句： ##

**附:实例表建表语句（适用于Oracle）**

SET TERMOUT ON  
    PROMPT Building demonstration tables. Please wait.  
    SET TERMOUT OFF  
      
    DROP TABLE EMP;  
    DROP TABLE DEPT;  
    DROP TABLE BONUS;  
    DROP TABLE SALGRADE;  
    DROP TABLE DUMMY;  
      
    CREATE TABLE EMP  
    (EMPNO NUMBER(4) NOT NULL,  
    ENAME VARCHAR2(10),  
    JOB VARCHAR2(9),  
    MGR NUMBER(4),  
    HIREDATE DATE,  
    SAL NUMBER(7, 2),  
    COMM NUMBER(7, 2),  
    DEPTNO NUMBER(2));  
      
    INSERT INTO EMP VALUES  
    (7369, 'SMITH', 'CLERK', 7902,  
    TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);  
    INSERT INTO EMP VALUES  
    (7499, 'ALLEN', 'SALESMAN', 7698,  
    TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);  
    INSERT INTO EMP VALUES  
    (7521, 'WARD', 'SALESMAN', 7698,  
    TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);  
    INSERT INTO EMP VALUES  
    (7566, 'JONES', 'MANAGER', 7839,  
    TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);  
    INSERT INTO EMP VALUES  
    (7654, 'MARTIN', 'SALESMAN', 7698,  
    TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);  
    INSERT INTO EMP VALUES  
    (7698, 'BLAKE', 'MANAGER', 7839,  
    TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);  
    INSERT INTO EMP VALUES  
    (7782, 'CLARK', 'MANAGER', 7839,  
    TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);  
    INSERT INTO EMP VALUES  
    (7788, 'SCOTT', 'ANALYST', 7566,  
    TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);  
    INSERT INTO EMP VALUES  
    (7839, 'KING', 'PRESIDENT', NULL,  
    TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);  
    INSERT INTO EMP VALUES  
    (7844, 'TURNER', 'SALESMAN', 7698,  
    TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);  
    INSERT INTO EMP VALUES  
    (7876, 'ADAMS', 'CLERK', 7788,  
    TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);  
    INSERT INTO EMP VALUES  
    (7900, 'JAMES', 'CLERK', 7698,  
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);  
    INSERT INTO EMP VALUES  
    (7902, 'FORD', 'ANALYST', 7566,  
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);  
    INSERT INTO EMP VALUES  
    (7934, 'MILLER', 'CLERK', 7782,  
    TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);  
      
    CREATE TABLE DEPT  
    (DEPTNO NUMBER(2),  
    DNAME VARCHAR2(14),  
    LOC VARCHAR2(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');  
      
    CREATE TABLE BONUS  
    (ENAME VARCHAR2(10),  
    JOB VARCHAR2(9),  
    SAL NUMBER,  
    COMM NUMBER);  
      
    CREATE TABLE SALGRADE  
    (GRADE NUMBER,  
    LOSAL NUMBER,  
    HISAL NUMBER);  
      
    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);  
      
    CREATE TABLE DUMMY  
    (DUMMY NUMBER);  
      
    INSERT INTO DUMMY VALUES (0);  
      
    COMMIT;  
      
    SET TERMOUT ON  
    PROMPT Demonstration table build is complete.  
      
    EXIT

**实例建表语句（适用于MySQL）**

DROP TABLE employee;  
    DROP TABLE dept;  
    DROP TABLE bonus;  
    DROP TABLE salgrade;  
    DROP TABLE dummy;  
      
    CREATE TABLE employee  
    (  
      employeeno DECIMAL(4) NOT NULL,  
      ename VARCHAR(10),  
      job VARCHAR(9),  
      mgr DECIMAL(4),  
      hiredate DATE,  
      sal DECIMAL(7, 2),  
      comm DECIMAL(7, 2),  
      deptno DECIMAL(2)  
    );  
      
    INSERT INTO employee VALUES  
    (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);  
    INSERT INTO employee VALUES  
    (7499, 'ALLEN', 'SALESMAN', 7698,'1981-2-20', 1600, 300, 30);  
    INSERT INTO employee VALUES  
    (7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);  
    INSERT INTO employee VALUES  
    (7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20);  
    INSERT INTO employee VALUES  
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);  
    INSERT INTO employee VALUES  
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);  
    INSERT INTO employee VALUES  
    (7782, 'CLARK', 'MANAGER', 7839, '1981-7-9', 2450, NULL, 10);  
    INSERT INTO employee VALUES  
    (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-9', 3000, NULL, 20);  
    INSERT INTO employee VALUES  
    (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);  
    INSERT INTO employee VALUES  
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);  
    INSERT INTO employee VALUES  
    (7876, 'ADAMS', 'CLERK', 7788, '1983-1-12', 1100, NULL, 20);  
    INSERT INTO employee VALUES  
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);  
    INSERT INTO employee VALUES  
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);  
    INSERT INTO employee VALUES  
    (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);  
      
    CREATE TABLE dept  
    (deptno DECIMAL(2),  
    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');  
      
    CREATE TABLE bonus  
    (ename VARCHAR(10),  
    job VARCHAR(9),  
    sal DECIMAL,  
    comm DECIMAL);  
      
    CREATE TABLE salgrade  
    (grade DECIMAL,  
    losal DECIMAL,  
    hisal DECIMAL);  
      
    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);  
      
    CREATE TABLE dummy  
    (dummy DECIMAL);  
      
    INSERT INTO dummy VALUES (0);

## 查询案例（MySQL版本） ##

查询1985年12月31日之前入职的员工姓名及入职日期。（注意日期的写法）
    SELECT ename,hiredate FROM employee WHERE hiredate < '1985-12-31'
    
    查询部门编号不在10部门的员工姓名、部门编号。（这里的！=也可写作<>）
    SELECT ename,deptno FROM employee WHERE deptno!=10;
    
    查询入职日期在82年至85年的员工姓名，入职日期.
    SELECT ename,hiredate FROM employee WHERE hiredate BETWEEN '1982-1-01' AND '1985-12-31'
    
    查询部门编号为10或者20的员工姓名，部门编号。
    SELECT ename,deptno 
    from employee 
    WHERE deptno=10 OR deptno=20;
    
    查询经理编号为7902 7566 7788的员工姓名，经理编号。#注意是圆括号
    SELECT ENAme,mgr 
    FROM employee 
    WHERE mgr IN (7902,7566,7788)
    
    查询员工姓名以W开头的员工姓名。
    SELECT ename 
    from employee 
    WHERE ename LIKE 'W%'
    
    查询员工姓名倒数第2个字符为T的员工姓名。
    SELECT ename 
    FROM employee 
    WHERE ename LIke '%T_'
    
    查询奖金为空的员工姓名，奖金。
    SELECT ename 
    from employee 
    WHERE comm IS NULL
    
    查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
    #注意逻辑运算符的优先级not>and>or,其次根据中文中的逻辑词来写。
    SELECT ename,deptno,sal 
    from employee 
    WHERE (3000<=sal and sal<=5000)and(deptno=10 or deptno=20)
    
    查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
    #注意对于字符串的比较不能采用运算符如！=等，需要用Like关键字等
    SELECT ename,hiredate,job 
    from employee 
    where (hiredate BETWEEN '1981-1-01' AND '1981-12-31')and (job not LIKE 'SALES%')
    
    查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序 并按照部门升序，工资降序排序。
    SELECT ename , deptno,sal 
    from employee 
    WHERE (sal BETWEEN 2000 and 3000) and (deptno!=10) 
    ORDER BY deptno asc, sal DESC
    
    写一个查询，显示所有员工姓名，部门编号，部门名称。
    #N张表至少要N-1个连接条件
    SELECT ename,employee.deptno,dname 
    from dept, employee 
    where dept.deptno=employee.deptno;
    
    写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
    select ename,loc,comm
    from employee,dept
    WHERE employee.deptno=dept.deptno and ((loc='CHICAGO') and (comm is not NULL))
    
    
    查询每个员工的姓名和直接上级姓名？
    SELECT A.ename worker,B.ename boss
    FROM employee A INNER JOIN employee B 
    ON  A.mgr=B.employeeno
    
    查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。
    #from什么join什么的，可以看做是一张表（其实就是一张复杂的表）。下面接着用正常的语法就可以了。
    SELECT A.ename workName,A.employeeno workNum,B.ename bossName,B.employeeno BossNum,loc
    from employee A 
    JOIN employee B 
    ON A.mgr=B.employeeno
    JOIN dept C
    on A.deptno=C.deptno
    where loc IN ('NEW YORK','CHICAGO')
    
    查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。
    SELECT A.ename ,A.employeeno,B.dname 
    FROM employee A 
    LEFT JOIN dept B 
    ON A.deptno=B.deptno;
    
    创建一个员工表和部门表的交叉连接。
    SELECT count(*) 
    from employee A 
    CROSS JOIN dept B
    
    使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
    #using可以作为一个连接条件使用（直接将两张表的公有属性声明在using中）
    SELECT ename,dname,loc 
    from employee A 
    join dept B 
    USING (deptno) 
    WHERE B.loc='CHICAGO'
    
    使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
    SELECT ename,dname,loc,grade 
    from employee A 
    join dept B 
    on A.deptno=B.deptno 
    join salgrade C 
    WHERE A.sal BETWEEN C.losal and C.hisal;
    
    使用左连接，查询每个员工的姓名 查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
    SELECT A.ename worker,B.ename boss 
    from employee A 
    LEFT JOIN employee B 
    on A.mgr=B.employeeno
    
    查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
    #注意声明你是以那张表的deptno作为分组的条件的（表不同，结果也不同）
    SELECT employee.deptno,dname,count(DISTINCT employeeno),MAX(sal),MIN(sal),SUM(sal),AVG(sal) 
    from employee,dept
    GROUP BY employee.deptno
    
    查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
    SELECT count(DISTINCT A.employeeno),B.ename,B.employeeno
    from employee A 
    LEFT JOIN employee B 
    on A.mgr=B.employeeno 
    GROUP BY A.mgr
    
    
    #不能在 WHERE子句中限制组，可以通过 HAVING 子句限制组（经典错题）、
    #原因：根据sql执行顺序，先从where中得到限制条件，然后才执行goupBy语句，之后才执行having语句
    如果再where中直接进行一个限制组的操作，那么后续的group将没有任何意义（毕竟都是分区排序先分区再排序）
    1 SELECT deptno, max(sal)
    2 FROM emp
    3 WHERE max(sal) > 2900
    4 GROUP BY deptno;
    *
    ERROR at line 3:
    ORA-00934: group function is not allowed here
    *
    
    查询部门人数大于2的部门编号，部门名称，部门人数。
    SELECT B.deptno,dname,COUNT(DISTINCT employeeno)
    from dept A,employee B
    where A.deptno=B.deptno
    GROUP BY B.deptno
    HAVING count(DISTINCT employeeno)>2
    
    查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资
    ，并按照部门人数升序排序。
    SELECT B.deptno,dname,COUNT(DISTINCT employeeno),AVG(sal)
    from dept A,employee B
    where A.deptno=B.deptno
    GROUP BY B.deptno
    HAVING count(DISTINCT employeeno)>2 and AVG(sal)>2000
    ORDER BY count(DISTINCT employeeno) ASC
    
    查询工资比Jones工资高的员工信息？
    SELECT * 
    from employee 
    WHERE sal >(SELECT sal 
    from employee 
    WHERE ename='Jones')
    
    查询工资最低的员工姓名？
    SELECT ename 
    from employee 
    WHERE sal in (SELECT min(sal) 
    from employee)
    
    查询入职日期最早的员工姓名，入职日期（分成两步查询，第一步查询基本属性，第二步查询最早入职的日期。子查询结果对应的行就是主查询需要的数据）
    SELECT ename, hiredate 
    from employee 
    where hiredate=
    (SELECT min(hiredate) 
    from employee)
    
    查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
    SELECT ename,sal,dname 
    from employee A,dept B 
    WHERE A.deptno=B.deptno 
    and sal >(SELECT sal 
    from employee 
    WHERE ename='SMITH' )AND loc ='CHICAGO'
    
    查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数（重点看，一定要注意表的连接条件）
    SELECT A.deptno,dname,count(DISTINCT employeeno) 
    from employee A,dept B
    WHERE A.deptno=B.deptno
    GROUP BY A.deptno
    HAVING COUNT(employeeno) > 
    (SELECT count(ename)/count(DISTINCT deptno) from employee)
    
    查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
    SELECT ename,hiredate,deptno 
    from employee 
    WHERE hiredate >ANY(SELECT hiredate 
    from employee 
    WHERE deptno=10 ) 
    AND deptno!=10
    
    查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
    SELECT ename,hiredate,deptno 
    from employee 
    WHERE hiredate > all(SELECT hiredate 
    from employee 
    WHERE deptno=10 ) AND deptno!=10
    
    查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工
    #注意可以多属性匹配
    SELECT  ename, job 
    FROM employee 
    WHERE (job ,mgr) in (SELECT job,mgr 
    FROM employee 
    WHERE deptno=10) AND deptno !=10
    
    向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。
    INSERT INTO dept 
    VALUES(50,'HR','SY')
    
    把员工编号为7782的部门编号修改为20 
    UPDATE emp
    SET deptno = 20
    WHERE empno = 7782;
     
    删除职位是CLERK的员工记录
    DELETE FROM emp
    WHERE job = 'CLERK'; 
     
    INSERT into manegers 
    SELECT *  from  employee WHERE job ='MANAGER'

## 总结 ##

SQL对于大数据来说非常重要，所以这项技能一定要精通。后续会再回顾下SQL，然后把一些重要的知识点总结出来。