网站根验证文件在哪,一流高职院校建设网站,html免费网站模板,物流公司哪个最便宜Oracle数据库安装的时候会自带一个练习用数据库#xff08;其中包含employee表#xff0c;后来版本中此表改名为emp#xff09;#xff1b; 首先在安装过程中应该有个选项“是否安装实例表”#xff08;完全安装模式下默认是选择的#xff09;#xff0c;需要选择才有此…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 deptno10 OR deptno20;查询经理编号为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之间的员工姓名、部门、工资。
#注意逻辑运算符的优先级notandor,其次根据中文中的逻辑词来写。
SELECT ename,deptno,sal
from employee
WHERE (3000sal and sal5000)and(deptno10 or deptno20)查询入职日期在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.deptnoemployee.deptno;写一个查询显示所有工作在CHICAGO并且奖金不为空的员工姓名工作地点奖金
select ename,loc,comm
from employee,dept
WHERE employee.deptnodept.deptno and ((locCHICAGO) and (comm is not NULL))查询每个员工的姓名和直接上级姓名
SELECT A.ename worker,B.ename boss
FROM employee A INNER JOIN employee B
ON A.mgrB.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.mgrB.employeeno
JOIN dept C
on A.deptnoC.deptno
where loc IN (NEW YORK,CHICAGO)查询所有员工编号姓名部门名称包括没有部门的员工也要显示出来。
SELECT A.ename ,A.employeeno,B.dname
FROM employee A
LEFT JOIN dept B
ON A.deptnoB.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.locCHICAGO使用ON子句显示工作在CHICAGO的员工姓名部门名称工作地点薪资等级
SELECT ename,dname,loc,grade
from employee A
join dept B
on A.deptnoB.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.mgrB.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.mgrB.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.deptnoB.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.deptnoB.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 enameJones)查询工资最低的员工姓名
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.deptnoB.deptno
and sal (SELECT sal
from employee
WHERE enameSMITH )AND loc CHICAGO查询部门人数大于所有部门平均人数的的部门编号部门名称部门人数重点看一定要注意表的连接条件
SELECT A.deptno,dname,count(DISTINCT employeeno)
from employee A,dept B
WHERE A.deptnoB.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 deptno10 )
AND deptno!10查询入职日期比10部门所有员工晚的员工姓名、入职日期不包括10部门员工
SELECT ename,hiredate,deptno
from employee
WHERE hiredate all(SELECT hiredate
from employee
WHERE deptno10 ) AND deptno!10查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名职位不包括10部门员工
#注意可以多属性匹配
SELECT ename, job
FROM employee
WHERE (job ,mgr) in (SELECT job,mgr
FROM employee
WHERE deptno10) 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然后把一些重要的知识点总结出来。
