Oracle数据库基础命令操作

Oracle数据库

基础命令

登录用户：conn 用户名;后输入密码，或者直接conn 用户名/密码;

在权限为管理员用户前提下：

创建用户：create user 用户名 identified by 密码;

修改密码：alter user 用户名 identified by 密码;

授权：grant 授权内容/角色 to 授权用户;

收回权限：revoke 权限内容/角色 to 收权限用户;

显示当前用户：show user;

数据定义语言ddl （create alter drop）

create建表命令：create table 表名(表内数据结构);

在一张表内，拥有一个主键约束primary key，即该项为表内唯一标志，不可重复，还可拥有多个非空约束not null，即该项在表内不能为空，必须有数据写入

例：

 create table student(
     id number(4) primary key,
     name varchar2(20) not null,
     sex char(3),
     brithday date,
     bonus number(7,2)
 );

此即创建表，完成之后可以通过select * from 表名;查看表内所有内容，或者通过desc 表名;查看指定表的数据结构;

然后就是向表中插入数据（该部分会在dml中展开，这里只用基础形式）

插入基础形式：insert into 表名 values(按照数据结构一一对应，用,隔开);

例：

 insert into student values(1001,'老王','男','29-9月-20',null);
 insert into student values(1004,'老李','男','29-8月-20',441,200);
 insert into student values(1003,'老钱','男','29-7月-20',3002,300);
 insert into student values(1002,'老孙','男','29-6月-20',200,499);

在插入完成数据之后，基础表创建完成，不光有这种先创建表结构，再插入表数据的建表方式，还有直接复制另一张表内容的建表方式

例：

 --复制一张表,同时复制表结构和数据
 create table student_copy as select * from student;
 --复制表，只要表结构，不要数据，可以利用条件过滤掉数据
 create table student_copy02 as select * from student where 1<>1;--这里的1<>1即为1!=1，旨在让后面的条件不成立，这样就无法选中任何数据，就会只把表头复制过来创建新表copy02
 --复制表，但是只复制几个字段
 create table student_copy03 as select name,bonus from student;

alter修改表结构命令：alter table 表名 操作命令 命令对象;

与delete命令最大的不同在于，alter操作的对象为表结构，即字段内容而非表内数据

例：

--向表中添加字段classId
 alter table student add (classId number(3));
 ​
 --删除字段 alter table 名 drop column 字段名（单个）
 alter table student drop column classId;
 --删除多个字段
 alter table student drop (classId,bonus);
 ​
 --修改字段的长度
 alter table student modify (name varchar2(50));
 ​
 --添加多个字段
 alter table student add (test01 number(1),test02 varchar2(20));
 ​
 --改表格的名
 alter table student rename to student_new;
 alter table student_new rename to student;
 ​
 --改字段的名
 alter table student rename column test02 to score;

drop删除表命令：drop table 表名；区分delete，truncate，drop，其中drop是连同表结构一起删除，而delete和truncate只删除其中数据部分

例：

 --truncate删除全表数据，不可以指定条件，不保留数据的索引，占用空间，不可以回滚
 truncate table student_copy01;
 --只删除数据，可以指定删除条件where，保留数据的索引，占用空间，可以回滚数据
 delete from student_copy02;
 --drop直接删除表结构，啥都不剩
 drop table student;

数据操纵语言dml（insert update delete）

insert插入数据命令：insert into 表名 数据内容；

还是先创建新表（要删除同名旧表才能创建新表）

例：

 create table student (
 编号 number(4) primary key,
 姓名 varchar2(13) not null,
 年龄 number(3),
 性别 varchar2(8),
 电话 number(10),
 出生日期 date,
 成绩  number(4,2)
 );

然后进行数据插入，例：

 --基础插入方式，数据上必须与表头一一对应，除结构中要求的主键和非空项外，其他项可为null
 insert into student values(123,'王某',3,'男',123,'21-9月-20',23.42);
 insert into student values(124,'张某',4,'男',null,'21-9月-20',24.62);
 --也可以直插入其中的几个值，其他部分为空，这样插入顺序可以随意改变，但是要求前后对应
 insert into student(编号,姓名) values(233,'李某');
 insert into student(姓名,编号) values('钱某',2333);
 --和create同理，插入数据时也可以从其他表中直接复制过来
 create table student_copy as select *from student where 1<>1;
 insert into student_copy select * from student;

同时，还有一个虚拟的表dual，你无论查询什么内容都会返回相应内容，它什么都有又什么都没有，select命令在dql中展开，这里也用基础格式select 内容 from 表名；

例：

 select '好好学习' from dual;--大概就是这么个效果
 --知道了dual，也可以利用union all把查询的这些结果变成一个集合，然后用这个集合进行插入
 insert into student(编号,姓名,年龄)
 select 1333,'z某',3 from dual
 union all
 select 1222,'s某',3 from dual
 union all
 select 1111,'d某',3 from dual;

更新数据命令update：update 表名 set 更新数据内容；

删除数据命令delete：delete from 表名；

例：

 update student set 年龄=年龄+2;
 update student set 出生日期='30-3月-2012' where 编号='2333';
 update student set 成绩=94,电话=3332 where 编号=1111;
 delete from student where 年龄=4;
 delete from student where 成绩=94;
 delete from student;

数据查询语言dql（select）

数据查询命令select：select 内容 from 表名；

例：

 --查询工作为salesman
 select * from emp where job='SALESMAN';
 --查询工作为salesman并且部门为30的人
 select * from emp where job='SALESMAN' and deptno=30;
 --查询工作为salesman或者部门号为30的人
 select * from emp where job='SALESMAN' or deptno=30;
 ​
 --like查询，模糊查询以S开头的人的信息，百分号表示模糊部分，为通配，匹配0个或者多个字符，比如查询J开头的人
 select * from emp where ename like 'J%';
 --查询姓名中有O的员工嗷
 select * from emp where ename like '%O%';
 ​
 --类似的还有下划线，先插入两个人
 insert into emp(empno,ename) values (1001,'张三');
 insert into emp(empno,ename) values (1002,'张三四');
 --查询姓张的员工且他的名字只有两个字，
 select * from emp where ename like '张_';
 --以此类推，三个字
 select * from emp where ename like '张__';
 ​
 --区间查询，查询1月到9月入职的人
 select * from emp where hiredate between '1-1月-81' and '30-9月-81';
 --查询工资在2k到5k之间的人
 select * from emp where sal between '2000' and '5000';
 select * from emp where sal>=2000 and sal<=5000;
 ​
 --null和非空查询
 select * from emp where comm is null;
 select * from emp where comm is not null;
 ​
 --查询工资加奖金时，如何把null计算为0，nvl和nvl2
 --使用nvl函数，如果为空则是0，非空则是comm奖金数量
 select empno,ename,sal,comm,sal+nvl(comm,0) from emp;
 --再给个别名更加完善
 select empno,ename,sal,comm,sal+nvl(comm,0) as 收入 from emp;
 --还有一种nvl2，如果comm为空，那么为工资值，非空则为工资加奖金值，注意数据位置
 select ename,sal,nvl2(comm,sal+comm,sal) from emp;
 ​
 --去重查询，指定重复项则不显示
 select distinct job from emp;
 ​
 --排序，查询员工信息，按照薪资排序
 select * from emp order by sal;--默认升序
 select * from emp order by sal asc;
 --降序，按照总薪资排列，查询全部信息，其中emp.*为emp中所有数据
 select emp.*,nvl2(comm,sal+comm,sal) total from emp order by total desc;
 ​
 --筛选
 select * from emp where sal=1600 or sal=1300;
 select * from emp where sal in(1600,1300);
 --反向筛选
 select * from emp where sal!=1600 and sal!=1300;
 select * from emp where sal not in(1600,1300);
 ​
 --聚合函数
 select max(sal) from emp;
 select min(sal) from emp;
 select avg(sal) from emp;--平均工资
 select sum(sal) from emp;--工资总和
 select count(*) from emp;--统计信息数量
 ​
 --字符串拼接||
 select 'hello'||'world' from dual;
 --使用concat一个效果
 select concat('hello','world') from dual;
 ​
 --转化大写小写
 select upper('wahaha') from dual;
 select lower('WAHAHA') from dual;
 --将emp表中的ename字段数据查询为小写
 select lower(ename) from emp;
 ​
 --截取字符串
 --括号内后两位为从哪里开始截取，截取长度
 select substr('92345678',2,5) from dual;
 --不给截取长度默认截取到最后
 select substr('12345678',5)from dual;
 --负数从右边开始数
 select substr('92345678',-5,2) from dual;
 ​
 --求字符串的长度
 select length('4231') from dual;
 ​
 --替换
 select replace('abcddbabd','ab','*')from dual;
 ​
 --查找，括号内后两位为从第几位开始找，第几次出现，如果为负数和截取同理，从后面开始数
 select instr('abcccabcccab','ab',-3,2)from dual;
 ​
 --去除空白，为了更加直观的显示出空白是否去除，和length函数一起使用（空格占长度一格的）
 --leading去左边，trailing去右边，both去两边，为默认值，
 select length(trim(leading from' abc  ')) from dual;
 select length(trim(trailing from' abc  ')) from dual;
 select length(trim(both from' abc  ')) from dual;
 select length(trim(' abc  ')) from dual;
 --也可以指定参数去除
 select trim(leading 'a' from 'aaaaaAaAAAaa') from dual;
 ​
 --以上只能去除一个字符量或者空格，如果想要干掉一个字符串，也是分别对应左右，使用ltrim和rtrim
 select length(ltrim('  abc '))from dual;
 select length(rtrim('  abc '))from dual;
 --参数位置与前面相反啊，注意
 select ltrim('abcabcabccccccccccccefg','abc')from dual;
 --他的逻辑很怪，只要一串中有一处匹配，并且一串全是由所选定的指定字符组成，就会全部删除
 select rtrim('helloabaacaaaaacabbccbcbcbaaaaabc','abc')from dual;
 ​
 --绝对值 abs
 select abs(-24.5) from dual;
 ​
 --四舍五入 round
 select round(24.555)from dual;
 --精确到小数点后多少位，不指定默认到整数
 select round(24.555,2)from dual;
 --为负数精确到小数点左边多少位
 select round(24.555,-1)from dual;
 ​
 --截取 trunc，与上面四舍五入同理，区别可能就是截取为“非四舍五入”
 select trunc(24.5)from dual;
 select trunc(29.555555555,2)from dual;
 select trunc(29.555555555,-1)from dual;
 ​
 --sysdate 返回当前的日期，只有年月日
 select sysdate from dual;
 --systimestamp 返回时间戳，问就是啥都有
 select systimestamp from dual;
 --add_months（d1,n1）在日期d1上加上n个月之后的新日期
 select add_months(sysdate,12) from dual;
 select add_months(sysdate,-12) from dual;
 --last_day返回日期所在的月份的最后一天
 select last_day('20-5月-21') from dual;
 --months_between返回两个日期相差的月数,他是前减后
 select months_between('20-4月-23','21-3月-89')from dual;
 --EXTRACT(啥 from 时间)提取日期中的特定部分，可以为year month day hour minute second
 select extract(second from systimestamp)from dual;
 ​
 --to_char将日期转化为指定格式的字符串
 --to_date将字符串格式的日期转化为日期
 select to_char(sysdate,'yy-MM-dd day hh-mi-ss')from dual;
 select to_date('21-09-30 星期四 12-32-32','yy-MM-dd day  hh-mi-ss')from dual;
 ​
 --分组查询 group by
 --统计每个部门的总人数，显示部门号，总人数，进行一次分组
 select count(*),deptno from emp
 group by deptno
 order by deptno;
 ​
 --每个部门，每个职务人数，进行两个分组
 select count(*),job,deptno from emp
 group by deptno,job
 order by deptno;
 ​
 --统计1到12月入职的人数，结合上面的extract
 select count(*),extract(month from hiredate) month from emp
 group by extract(month from hiredate)
 order by extract(month from hiredate);
 ​
 --显示平均工资大于2k的部门，显示部门号，平均工资
 select deptno,avg(sal) from emp
 group by deptno
 having avg(sal)>2000
 order by deptno;--这里一定要注意！！分组之后再过滤不用where用having
 ​
 --查询30号部门职务人数大于2的，显示职务人数和名称，条件非一个则用and，同理或者用or
 select deptno,job,count(*) from emp
 group by deptno,job
 having deptno=30 and count(*)>2;
 ​
 --如何按照指定列删除所有重复数据
 --我们先创建一个copy表复制dept中的数据
 create table copy as select * from dept;
 --再插入一次dept中的数据
 insert into copy select * from dept;
 --在插入一个新的数据，因为distinct只适用于完全一样的内容，所以只有部分一样这种情况无法删除
 insert into copy values (10,'aaa','bbb');
 --准备工作做完了，有一个叫做rowid的东西，是一条记录的唯一标识，类似于主键，不过他是一个伪列
 select rowid,copy.* from copy order by deptno;
 --伪列可以用来保证唯一性，先按照重复的数据来分组，然后每条中选择一条数据，保证唯一性，最后删除没有选中的数据
 --比如这样，按照名称为唯一性进行删除，只显示不一样的名称，括号中选中了所有数据，按照名称分组之后，每一组的最小的rowid伪列，然后删除除了这些伪列之外的内容，就可以实现结果
 delete from copy where rowid not in
 (select min(rowid) from copy group by dname);
 ​
 --关联查询
 --直接匹配，笛卡尔积
 select emp.*,dept.* from emp,dept;
 --查询员工的基本信息，部门名称，那么将部门表和员工表关联，不再显示部门编号而是名称
 select emp.*,dept.dname from emp,dept 
 where emp.deptno=dept.deptno
 order by dname;
 --内连接查询，只显示满足条件的所有数据inner join on，实现一样的效果
 select emp.*,dept.dname from emp
 inner join dept
 on dept.deptno=emp.deptno;
 ​
 --再举一次例子
 --查询员工的工资，姓名，工资等级
 select emp.ename,emp.sal,salgrade.grade from emp
 inner join salgrade
 on emp.sal between salgrade.losal and salgrade.hisal;
 --省略inner join
 select emp.ename,emp.sal,salgrade.grade from emp,salgrade
 where emp.sal between salgrade.losal and salgrade.hisal;
 ​
 --还可以接着套娃条件查询
 --查询SMITH这个人的信息，所在的部门名称，部门的地址，工资等级
 select emp.ename,emp.job,dept.loc,salgrade.grade from emp
 inner join dept on dept.deptno=emp.deptno
 inner join salgrade on emp.sal between salgrade.losal and salgrade.hisal
 where emp.ename='SMITH';
 ​
 --外连接查询，左外连接，以左边的表为主，查询时左边的表的数据无论是否满足条件，都要显示出来，右边的表就只显示满足条件的，右外连接就相反同理
 --比如我们先插入一个张三
 insert into emp values(2123,'张三',null,null,null,null,null,null);
 --内连接就查不到刚刚插入的张三，因为内连接一定要满足条件才显示，而张三没有deptno
 ​
 --来了啊，左外连接啊
 select emp.*,dept.dname from emp--谁写在前面,左外连接，谁就是左边的表，谁就大
 left join dept on dept.deptno=emp.deptno;
 ​
 --没想到吧，还有个右外连接
 select emp.*,dept.* from emp--反过来了啊，没有张三了，但是有40号部门，因为40号部门没人
 right join dept on dept.deptno=emp.deptno;
 ​
 --估计也猜着了，还有个全外连接，全显示
 select emp.*,dept.* from emp
 full join dept on dept.deptno=emp.deptno;--也叫满外连接，这次就是张三和40号部门全都有
 ​
 --还有种自连接，一张表自己和自己连接
 --查询员工信息以及其领导的信息,显示员工信息，薪资，其领导的姓名，薪资
 select emp.ename,emp.sal,m.ename,m.sal from emp
 inner join emp m on emp.mgr=m.empno;
 --实质上是将原来的表虚拟出一张新的表来，再跟这张表连接，虚拟出来的这张表成为行内视图
 ​
 --分页
 --查询emp表，显示前五条数据 rownum oracle提供的伪列，表示行号，一定是从1开始
 --当查询出一条记录的时候，自动添加行号
 select rownum,emp.* from emp;
 select rownum,emp.* from emp where rownum<=5;--可行
 select rownum,emp.* from emp where rownum=5;--惊奇的发现，除了第一行都不能查
 --简而言之，他只能从1开始或者只查1，如何解决？行内视图！
 select rownum,emp.* from emp;
 select * from (select rownum,emp.* from emp);--我查的不是你，我查的是我自己啊,这里创了个虚表给我自己查（行内视图）
 select e.*,rownum from (select rownum rmm,emp.* from emp) e where rmm =3;--实现效果了
 ​
 --这玩意还有个变种方法，也就是我们的分页了
 select * from(
 select rownum rn,emp.* from emp
 where rownum<=10)
 where rn >=5;