orcale数据库

oracle数据库

---

SQL语句的分类

1. DQL：数据查询操作
2. DML：数据库更新操作（增删查改）
3. DDL：数据库对象操作（数据表，约束）
4. DCL：数据库的维护（授权，解锁）
5. TCL：数据库事务操作（提交，回滚）

---

基本操作

--查看用户下的表
select * from tab;
--创建用户
create user username  identified by password;
--用户授权（对于普通用户：授予connect, resource权限对于DBA管理用户：授予connect，resource, dba权限。）
grant connect ,resource to usename; --grant 是关键字+ 权限名 to 用户
--修改密码
alter user username identified by newPassWord;
--锁定账户
alter user username account lock ;--锁定
alter user username account unlock;--解锁
--回收权限
revoke connect ,resource from username;
--删除用户
drop user username;--有内容需要在用户后面加上 cascade

---

基本查询语句

**--查表中所有数据**
select * form tableName;
**--取别名**
select ename as name from emp;
**--去重查询**
select distinct deduplication field name from emp;
**--排序查询 (desc:降序，asc：升序)**
select ename from emp order by sal desc;
**--条件查询** 
select * from  emp where query condition;
-**-分组查询** group by 
select job,avg(),count(*),sal() from emp group by  deptno; 
**--获取当前系统的时间**
select sysdate from dual;
**--获取当前系统时间戳**
select systimestamp from dual;

---

基本函数

**--大小写转换**
upper(column data) ;lower(column data);
**--首字母大写**
initcap(column data);
**--字符串长度**
length(column);
**--字符串替换**
replace(column data,replace data,new data);
**--字符串截取**
substr(column,start position);
substr(column,start position,length);
**--保留小数(四舍五入)**
round(column value,keep decimal places);
**--截取小数位**
trunc(column value,keep decimal places);
**--数值判断**
NVL(column value ,replace);--nvl(NULL,0);
**--取模**
mod(column|number,column);
-------------------------------------------------------------------
**--时间计算**
**--两个日期间的相隔月份**
months_between(date one ,date two);
**--在指定的日期上增加月份**
add_months(date,months);
**--指定日期的最后一天**
last_day(date);
**--获取指定日期的下个周几对应的日期**
last_day(date,which day);
-------------------------------------------------------------------
**--转换函数
--日期转成字符串**
to_char(column,|date|number,convert format);
**--字符串转成日期**
to_date(string,time format);

---

关联查询（多表）

1. **内连接：**做内连接得到的是a和b的关联关系的列同时存在的进行连接，内连后a和b关联列相同的a中数据和b中数据合在一起形成新的表数据。

   

   **--a表和b表做内连接**
   select * from a inner join b on (a.bNO = b.NO);
   **--简练写法**
   select * from a,b where a.bNO = b.NO;
   

2. 外连接（左连接，右连接）

   --左连接
   select * from a left join b on(a.bNO = b.NO);
   --右连接
   select * from a right join b on(a.bNO = b.NO);
   

1. 全连接(a和ｂ两个表数据都会显示出来)

   --全外连接（full join）
   select * from a full join b on(a.bNO =b.NO);
   

   

---

子查询（在查询语句中嵌套语句）

1. where中使用子查询

   --低于平均工资的员工信息
   select * from emp where sal<(select avg(sal) from emp);
   

2. having中使用子查询

   --查询高于平均工资的职位名称，职位人数，平均工资
   select job,count(*),avg(sal) from emp 
   group by job 
   having avg(sal)>(select avg(sal) from emp);
   

3. from中使用子查询

   --查询每个部门名称，位置，部门人数
   select 
   

   ---

4. 子查询的三个操作符 （in，any ，all)

- IN操作：与子查询的内容相同
- ANY操作:
  - =ANY   与IN操作相同
  - <ANY   比子查询最大值小
  - `>ANY`   比子查询最小值大
- ALL操作：
  - `>ALL`比子查询返回的最大值大
  - <ALL   比子查询返回的最小值小

---

练习集合

	-1、查询员工姓名和工资;
select ename ,sal from emp;
-2、查询员工的所有信息 表示所有的列(在实际开发中不能使用)
select * from emp;
-1、查询所有员工的年薪(月薪*12)
select sal*12 as annualSalary from emp; 
-2、查询所有员工的月薪(扣除社保-800)
select sal-800 from emp;
-3、查询所有的员工的时薪.
select round(sal/21/8) from emp;
-1查询所有的部门   （按照一个字段进行去重）
select distinct deptno from emp;
-2按照工资、部门、职位 （按照多个字段进行去重）
select distinct sal,deptno,job from emp;
-按照员工工资进行降序排序
select ename,sal from emp order by sal desc;
-按照员工工资进行降序排序,如果工资相等按照入职日期升序排序
select ename,sal from emp order by sal  desc,hiredate asc;
-1、查询工资大于2000的员工信息
select * from emp where sal >2000;
-2、查询不是销售的员工信息
select  * from emp where job <> 'SALESMAN';
-3、查询SMITH的员工信息
select * from emp where ename = 'SMITH';
- 4、查询1981年以后入职，且工资大于1000的员工信息
select * from emp where hiredate > to_date('1981-01-01','yyyy-dd-mm') or sal >1000;
-5、查询不是30部门的员工信息
select * from emp where deptno<>30;
-6、查询10、30部门的员工信息
select * from emp where deptno<>20;
-7、查询工资在1500到220的员工信息
select * from  emp  where sal between 1500 and 2200;
 -8、查询20,30部门的员工信息
select * from emp where deptno=20 or deptno =30;
- 9、查询编号为7566和7369的员工信息
select * from emp where empno in(7566,7369);
-10、查询编号不是7566和7369的员工信息
select * from  emp where   empno not in (7566,7369) ;
- 11、查询没有绩效的员工信息
select * from emp where comm is null;
-12、查询有绩效的员工信息
select * from emp where comm is not null;
-13、查询名字中带'S'的员工
select * from emp where ename like '%S%';
-15、查询姓名中第三个和第四个字符为'IT'的员工信息
select * from EMP WHERE ename like '___TI%';
- 1、选择部门30中的所有员工
select ename,deptno from emp where deptno =30;
-2、列出所有办事员(CLERK)的姓名、编号和部门编号
select ename,empno,deptno from emp where job = 'CLERK';
-3、找出佣金高于薪金的60%的员工
select * from emp where comm>sal*0.6;
-4、找出部门10中的部门经理和部门20中的办事员的详细资料
select * from emp where deptno in(10,20);
-5、既不是经理又不是办事员其薪金大于2000的员工;
select * from emp where job!='MANAGER' and job !='CLERK' and sal >2000;
-6、找出收取佣金的员工不同工作
select distinct job from emp where comm is not null;
-7、找出不收取佣金或收取佣金小于100的员工
select * from emp where comm is null or comm<100;
-8、显示不带有R的员工
select * from emp where ename not like '%R%';
-9、显示姓名字段的任何位置包含”A”的所有员工的姓名，并且按照基本工资排序，如果工资相等按照入职时间排序，入职时间相等按职位排序
- sum函数求和
-max函数求最大值
-min函数求最小值
-avg函数求平均值
select * from emp where ename like '%A%'  order by sal asc ,hiredate asc;
-count函数求个数  *表示所有列  count函数只会统计不为null的数据;
-1、查询每个职位的名称、人数、平均工资
select job,count(*),avg(sal) from emp group by job;
-2、查询每个部门编号，每个部门的人数，最高工资，最低工资
select deptno,count(*),max(sal),min(sal) from emp group by deptno; 
-3、查询平均工资高于2000的职位名称以及平均工资
select job,avg(sal) from emp group by job having  avg(sal)>2000;
-计算两个时间相差的月份
select months_between(sysdate,to_date('2021-10-20','yyyy-mm-dd'))  from dual;

-在指定的日期上增加或者减少月份
select add_months(sysdate,3)  from dual;
-指定日期的最后一天
select last_day(sysdate) from dual;
-获取指定日期的下个周几对应的日期。注意：(1表示星期日，7表示星期六)
select next_day(sysdate,3)from dual;
-将字符串转换成日期
select to_char(sysdate,'yyyy.mm.dd') from dual;
-将字符串转成数字
select to_number('10000') from dual;
-1、查询smith的个人信息
select * from emp where ename = 'SMITH'; 
-2、查询姓名和工资,姓名显示小写
select lower(ename),sal from emp;
-3、查询姓名和职位,职位首字母大写
select ename,initcap(job) from emp;
-4、查询所有员工姓名，和姓名长度
select ename,length(ename) from emp;
-5、查询员工里面名字小于等于4个字的员工信息
select * from emp where length(ename)<4;
-6、查询所有员工的名字的前三个字符
select substr(ename,3) from emp;
-7、查询显示所有员工的名字的后三个字符
select substr(ename,-3) from emp;
-8、查询所有员工工作时长(单位：月)
select months_between(sysdate,hiredate)  from emp;
- 9、计算我们在这儿的毕业时间
select add_months(sysdate,5) from dual;
-10、查询月底(25号以后)入职的员工
select * from emp where hiredate > LAST_DAY(SYSDATE-5);
-11、查询所有的员工的姓名和入职日期，入职日期显示格式为'2021-10-20'
select ename ,to_char(hiredate,'yyyy-mm-dd') from emp;
-12、计算每个部门的平均工资(不包含小数)
select deptno ,round(avg(sal)) from emp group by deptno;
-13、查询所有的员工工资(工资=工资+绩效)
select ename ,sal,sal+nvl(comm,0) from emp;
-查询员工的姓名、工资、入职日期、部门名称、部门位置
select ename,sal,hiredate,dname,loc from emp join  dept on emp.deptno = dept.deptno;
-查询所有员工以及所有的部门信息
select ename,sal,hiredate,dname,loc from emp join dept on emp.deptno = dept.deptno
select ename,sal,hiredate,dname,loc from emp left join dept on emp.deptno = dept.deptno
union 
select ename,sal,hiredate,dname,loc from emp right join dept on emp.deptno = dept.deptno
-1、查询员工的编号、姓名、职位、领导姓名、领导职位
SELECT e.EMPNO, e.ENAME, e.JOB, m.ENAME AS MANAGER_NAME, m.JOB AS MANAGER_JOB
FROM EMP e
LEFT JOIN EMP m ON e.MGR = m.EMPNO;
-2、查询每个雇员编号、姓名、职位、基本工资、工资等级
select * from salgrade
select e.empno,e.ename,e.job,e.sal,m.grade from emp e join salgrade m on e.sal between m.losal and m.hisal;
-3、查询每个雇员的编号、姓名、基本工资、部门名称、工资等级
select * from emp;
select * from dept;
select * from salgrade;
select empno,ename,sal,dname,grade from emp, dept, SALGRADE
where emp.deptno = dept.deptno and emp.sal between salgrade.losal and salgrade.hisal;

- 1、低于平均工资的员工信息
select ename,sal from emp where sal<(select round(avg(sal)) from emp);
- 2、查询公司最早的员工
select * from emp where hiredate = (select  min(hiredate) from emp);
-3、查询与WARD部门相同，工资相等的员工信息
 
SELECT * FROM EMP WHERE (JOB,SAL) = 
(SELECT JOB,SAL FROM EMP WHERE ENAME = 'WARD') 
AND ENAME <> 'WARD';

-1、查询与king同一个部门的员工信息
SELECT *FROM EMPWHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'KING');
-2、查询工资高于20部门所有员工的员工信息(高于最高)
select * from emp where sal>all (select max(sal) from emp where deptno=20); 
-3、查询工资高于20部门员工的员工信息(高于最低)
select * from emp where sal>all (select min(sal) from emp where deptno=20); 
- 1、查询高于平均工资的职位名称、职位人数、平均工资
select job,count(*),round(avg(sal)) from emp 
where sal >(select avg(sal) from emp)
group by job;
- 1、请找出在'NEW YORK'工作的所有员工
select * from emp join dept on emp.deptno =dept.deptno where loc ='NEW YORK';
- 2、查询工资比‘SMITH’或‘ALLEN’高的所有员工的编号、姓名、部门名称

- 3、查询工资比‘SMITH’或‘ALLEN’ 高的所有员工的最高和低工资
SELECT EMPNO, ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH')
AND SAL > (SELECT SAL FROM EMP WHERE ENAME = 'ALLEN');
- 4、查询每个部门的名称、人数、平均工资
SELECT DNAME, COUNT(*), AVG(SAL)
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME;
- 5、查询每个部门的编号、名称、位置、部门人数、平均工作年限
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, COUNT(*), AVG(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12)
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC;
-、查询工资高于在30部门的所有员工的姓名、工资、部门名称、部门人数
SELECT EMP.ENAME, EMP.SAL, DEPT.DNAME, COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30)
AND DEPT.DEPTNO != 30
GROUP BY EMP.ENAME, EMP.SAL, DEPT.DNAME;
-2、查询工资比‘SMITH’或‘ALLEN’ 高的所有员工的编号、姓名、部门名称、领导姓名、最高、低工资
SELECT E1.EMPNO, E1.ENAME, D.DNAME, E2.ENAME, MAX(E1.SAL), MIN(E1.SAL)
FROM EMP E1
JOIN DEPT D ON E1.DEPTNO = D.DEPTNO
JOIN EMP E2 ON E1.MGR = E2.EMPNO
WHERE E1.SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH')
AND E1.SAL > (SELECT SAL FROM EMP WHERE ENAME = 'ALLEN')
GROUP BY E1.EMPNO, E1.ENAME, D.DNAME, E2.ENAME;

---

DML操作

--新增
insert into tableName (
fieldname(fieldname1,...) values(value,...)
);

--修改
update tableName set
 modification = modificationvalue where filterCondition ;

--删除
delete from  tableName where deleteCondition;

---

DDL操作

--oracle中常见的数据类型
--字符串类型
char(length);--固定长度
varchar(length); --不固定

--数值类型
number(M,N);-- M表示总长,N表示小数点位数
integer; -- 整数
float;--浮点数

--日期类型
date; --存储格式为时分秒
timestamp; --比date精度高，精确到纳秒

---

DDL表的操作

--创建表
create table tableName(
	column Numbertype(constraint);
...
);

--删除表
drop table student purge; --清除掉了回收站数据

--复制表
select table tableName as 子查询;
-- 将所有30部门的员工保存到emp30表中
CREATE TABLE emp30  AS  SELECT * FROM emp WHERE deptno = 30;
-- 复制表结构
CREATE TABLE empnull  AS  SELECT * FROM emp  WHERE 1=2;

--修改表
--增加列
alter table tableName add columnName datatype
--在student中添加birthday 字段
alter table student add birthday varchar2(100);

--修改列数据
alter table tableName modify columnName datatype;
alter table student modify birthday date;

--删除列
alter table tableName drop column columnName;
-- 删除student中birthday列
alter table student drop column birthday;

---

表的约束

1.数据类型
2.非空类型
3，唯一类型
4.主键类型
5.外键类型（外键是用来控制数据库中数据的完整性的，就是当你对一个表的数操作时
，和它关联的一个或多个表的数据同时发生改变。）
6.检查约束
-- 2、非空约束(NN)  NOT NULL
CREATE TABLE STUDENT(
	SID NUMBER , 
    NAME VARCHAR2(20) NOT NULL,
    AGE NUMBER
);
-- 3、唯一约束(UN)   UNIQUE
CREATE TABLE STUDENT(
	SID NUMBER , 
    NAME VARCHAR2(20) UNIQUE,
    AGE NUMBER
);
CREATE TABLE STUDENT(
		SID NUMBER PRIMARY KEY,
		NAME VARCHAR2(20) ,
		AGE NUMBER
);

CREATE TABLE TYPE(
	TID NUMBER PRIMARY KEY,
	TNAME VARCHAR2(100),
	INFO VARCHAR2(200)	
);

CREATE TABLE PRODUCT(
	PID NUMBER PRIMARY KEY,
	PNAME VARCHAR2(100),
	PRICE NUMBER,
	CREATETIME DATE,
	TID NUMBER,
	CONSTRAINT FK_TID FOREIGN KEY(TID) references TYPE(TID)
);

-- 6、检查约束(CK) CHECK

CREATE TABLE STUDENT23(
	SID NUMBER , 
    NAME VARCHAR2(20) ,
    AGE NUMBER CHECK(AGE BETWEEN 0 AND 180)
);