oracle数据库
SQL语句的分类
- DQL:数据查询操作
- DML:数据库更新操作(增删查改)
- DDL:数据库对象操作(数据表,约束)
- DCL:数据库的维护(授权,解锁)
- 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);
关联查询(多表)
-
**内连接:**做内连接得到的是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;
-
外连接(左连接,右连接)
--左连接 select * from a left join b on(a.bNO = b.NO); --右连接 select * from a right join b on(a.bNO = b.NO);
-
全连接(a和b两个表数据都会显示出来)
--全外连接(full join) select * from a full join b on(a.bNO =b.NO);
子查询(在查询语句中嵌套语句)
-
where中使用子查询
--低于平均工资的员工信息 select * from emp where sal<(select avg(sal) from emp);
-
having中使用子查询
--查询高于平均工资的职位名称,职位人数,平均工资 select job,count(*),avg(sal) from emp group by job having avg(sal)>(select avg(sal) from emp);
-
from中使用子查询
--查询每个部门名称,位置,部门人数 select
-
子查询的三个操作符 (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)
);