oracle去重命令_Oracle简单命令

1 基本操作

1.用户操作

-- 普通用户切换管理员conn sys as sysdba

-- 创建用户 create user 用户名 identified by 密码；create user wanglong identified by 123456;

-- 创建用户并指定表空间create user 用户名 identified by 密码 default tablespace 表空间名称;create user wanglong identified by 123456 default tablespace j1009_tablespace;

-- 给用户授权 dba权限才能授权-- grant 权限1,权限2.....权限n to 用户；grant connect,resource,dba to wanglong;

-- 撤销权限-- revoke 权限1,权限2.....权限n from 用户；revoke connect,resource,dba from wanglong;

-- 删除用户drop user wanglong;

-- 查询所有用户信息-- username 表示用户名称 default_tablespace 表示用户操作的表空间select username ,default_tablespace from dba_users;

-- 查询当前用户下的表select table_name,tablespace_name from user_tables;

2.表空间操作

-- create tablespace 表空间名称 datafile '表空间的磁盘路径' size 数据文件大小;-- 注意 datafile 表示表空间管理的数据文件-- size用来设置表空间数据文件的大小 此时m表示MB-- 一个表空间对应一个或者多个数据文件create tablespace j1009_tablespace datafile 'D:\app\caojie\oradata\orcl\j1009.dbf' size 128m;

-- 删除表空间语法： drop tablespace 表空间名称;(磁盘上文件还在)drop tablespace J1009ABC_TABLESPACE;

-- 彻底删除表空间，包括磁盘上的数据drop tablespace j1009_tablespace including contents and datafiles;

3.对表操作-DDL

-- 为了规范起见所有的表都使用tb_开头create table tb_student(

id number(11),

stu_name varchar2(30),

stu_age number(3),

stu_sex varchar2(10)

);

-- 删除表语法：-- drop table 表名称;drop table tb_student;

-- drop table history;-- 清除回收站 永久删除purge recyclebin;

-- 查看表结构 desc 表名称;desc tb_students123;

-- 添加列 alter table 表名称 add 列名称 数据类型;alter table tb_students123 add stu_hobby varchar2(20);

-- 修改列数据类型 alter table 表名称 modify 列名称 新的数据类型;alter table tb_students123 modify stu_hobby varchar2(50);

-- 修改列名称 alter table 表名称 rename column 旧列名称 to 新列名称;alter table tb_students123 rename column stu_hobby to stu_hobbys;

-- 删除某一列 alter table 表名称 drop column 列名称;alter table tb_students123 drop column stu_hobbys;

创建表空间必须要指定数据文件和数据文件的大小，表空间由一个或者多个数据文件组成

创建用户，可以自己指定表空间，也可以使用默认的表空间(USERS)

对表进行DDL操作(create、drop、alter)

如何查看表的结构 desc 表名称

注意：表空间、用户通常是DBA在sys用户下创建的insert向表中插入数据

-- insert into 表名称(列名称1，列名称2，...列名称n) values(值1，值2，...值n);insert into tb_students123 (id,stu_name,)values(112,'徐华')；

-- 插入所有列的数据 插入所有列顺序必须跟表结构顺序保持一致-- insert into 表名称 values(所有的列对应的值)insert into tb_students123 values(5,'李明',18,'M');

-- 检查是否插入成功 select * from 表名称;select * from tb_students123;update更新表中的数据

-- update 表名称 set 列 = 值；update tb_sudents123 set stu_name ='徐杰';

-- 此方式会将表中所有记录的stu_name设置为“徐杰”-- 将ID为111的stu_name设置为“徐杰”；-- 语法 ：update 表名称 set 列 = 值 where 条件；update tb_students123 set stu_name ='徐杰' where id =111;delete删除表中的数据 只会删除表数据，不会删除表结构。

-- 删除ID-- 语法： delete from 表名称 where 条件;delete from tb_students123 where id=5;

-- 删除表中所有的数据-- 语法：delete from 表名称；delete from tb_students123;执行完DML语句(insert、delete、update)必须执行commit; 才算真正执行完毕。

4.表别名和列别名

-- 好处查询可视化更加友好，显示的查询结果就是列别名select empno as 员工编号,ename as 员工姓名,job as 职位 ,hiredate as 入职日期,sal as 工资

from emp;

-- 可以简化：将as去掉select empno 员工编号,ename 员工姓名 from emp e;sql中的算术运算符和比较、逻辑运算符

-- + - * /-- %在java中表示算术运算符取模(取余数)，在Oracle中%不是算术运算符-- Oracle的DQL语句算术运算符最好定义在from关键字之前select sal as 调整之前的工资, sal+100 as 调整之后的工资

from emp;

-- 比较运算符-- = <= >= != <>-- 注意：1 Oracle 比较运算符没有== ，判断两个表达式是否相等全部使用 =-- 2 Java中使用!=表示不等于，Oracle也有!= 但是不建议使用，那么Oracle使用<>作为不等于-- 场景查询员工编号不等于7788的所有员工信息select empno 员工编号,ename 员工姓名,job 职位,sal 工资，hiredate 入职日期

from emp

where empno<>7788;

-- 逻辑运算符-- and &&-- or ||-- not !-- 场景：查询员工部门编号为20，并且工资大于2000的员工信息select empno,ename,sal,job,hiredate,deptno

from emp

where deptno=20 and sal>2000;

-- 场景：查询员工所在部门不是30的员工信息select empno,ename,sal,job,hiredate,deptno

from emp

where not deptno=30;区间查询

-- 查询工资1000~2000区间的员工信息select * from emp where sal>1000 and sal<2000;

select * from emp where sal between 1000 and 2000;集合查询

-- 关键字 in 和 not in-- 场景：查询员工编号为7788,7369，7934的员工信息-- 所有的员工编号是一个集合，我们需要从集合中挑选3个编号select empno,ename,sal,job,hiredate,deptno

from emp

where empno in (7788,7369,7934);

-- 等价select empno,ename,sal,job,hiredate,deptno

from emp

where empno =7788 or empno=7369 or empno =7934;

-- 场景：查询员工编号不是为7788,7369，7934的员工信息-- not 非select empno,ename,sal,job,hiredate,deptno

from emp

where empno not in(7788,7369,7934);过滤重复的行

-- distinct 去重-- 场景：去掉重复的部门编号select distinct deptno from dept;模糊查询

-- 关键字 like-- 通配符 % 匹配任意多个字符-- 场景：查询员工姓名以J开头的员工信息，前面精确后面模糊，模糊的部分使用%表示select * from emp where ename like 'J%';

-- 小结：模糊查询分为三种(前面精确后面模糊、前面模糊后面精确、前后模糊中间精确)-- 注意：like一定在列名称的后面聚合函数

-- Oracle聚合函数用来做 汇总 类似java的方法-- count 计算总数-- 场景:统计emp表的总行数select count(1) 总行数 from emp;

-- sum 计算和-- 场景：统计emp表所有员工的工资总和select sum(sal) 工资总和 from emp;

-- max 计算最大值-- 场景：统计emp表最高工资select max(sal) 最高工资 from emp;

-- min 计算最小值-- 场景：统计emp表最低工资select min(sal) 最低工资 from emp;

-- avg 计算平均值-- 场景: 统计emp表所有员工的平均工资select avg(sal) 平均工资 from emp;

-- 小结：聚合函数用于对表中的数据做汇总，查询结果为单行单列-- 全家福-- 场景：统计emp表的最低工资、最高工资、平均工资、工资总和、员工人数select min(sal) 最低工资,max(sal) 最高工资,avg(sal) 平均工资,sum(sal) 工资总和,count(1) 员工人数

from emp;子查询

-- 查询工资高于平均工资切部门不为10的员工信息select * from emp where sal>(select avg(sal)from emp) and not deptno=10;

-- 小结：子查询作为父查询的条件，写在父查询where后面，此时子查询的结果集是单行单列分组和汇总

-- Oracle数据库中，group by 跟除了聚合函数以外的任意列,group by关键字后面不能使用列别名-- 场景：对部门进行分组，查询每个部门的平均工资select deptno 部门编号,avg(sal) 平均工资 from emp group by deptno;having关键字

-- 筛选数据，having只能写在group by后面，不能放在group by 前面select deptno 部门编号,avg(sal)平均工资 from emp group by deptno having avg(sal)>2000;排序

-- order by 列名称(支持别名) asc(升序) |desc(降序)select deptno 部门编号,avg(sal)平均工资 from emp group by deptno having avg(sal)>2000 order by 平均工资 desc;

-- 多个列排序select empno ,ename ,hiredate,sal,deptno

from emp

order by deptno asc , sal desc;

2.数据完整性

create table tb_student (

id number(11),

stu_name varchar2(20) not null,

stu_age date,

stu_mobile number(20),

stu_sex varcahr2(10),

stu_score number(5,2)

);

2.1检查约束

-- 创建检查约束语法-- chk_student_score 约束名称 chk表示checker _表名称_列名称-- chk_student_score表示：为tb_students表的stu_score列添加检查约束-- (stu_score>=0) 表达式alter table 表名称 add constraint约束名称 check 表达式；

alter table tb_students add constraint chk_student_score check (stu_score>=0);

-- 删除约束条件alter table tb_student drop constraint chk_student_score;

-- 重新建立检查约束alter table tb_students add constraint chk_student_score check (stu_score>=0 and stu_score<=150);

2.2非空约束

-- 设置非空约束语法：-- alter table 表名称 modify 列名称 数据类型 not null;alter table tb_students modify stu_age date not null;

-- 需要将字符串转换为dateinsert into tb_students(id,stu_name,stu_score,stu_sex,stu_age)values(10,'Tom李',99,'F',to_date('2010-10-19','YYYY-MM-DD'));

-- to_date(参数1，参数2) 是Oracle自带的系统函数，将参数1按照指定的格式(参数2)，转换为日期

2.3唯一约束

-- 唯一约束语法-- alter table 表名称 add constraint 约束名称 unique(列名称);alter table tb_student add constraint un_student_mobile unique (stu_mobile);

-- 小结：唯一约束可以为空，但是不能将相同的手机号码插入到tb_student表的stu_mobile,因为它违反了唯一约束的条件(手机号码不能重复)

2.4主键约束

特征：非空并且唯一,每一行的主键列必须确保该行在表中的唯一性

-- alter table 表名称 add constraint 约束名称 primary key (主键列)；alter table tb_students add constraint pk_student_id primary key(id);

--insert into tb_student(id,stu_name,stu_age,stu_mobile,stu_sex,stu_score)

values(105,'jackson',to_date('2010-10-19','YYYY-MM-DD'),'13475468795','F',55);

-- 不能将空值插入到主键

2.5外键约束

班级表和学生表之间建立一个联系(关系)，多个学生对应一个班级。那么需要为学生表的class_id建立一个外键约束，去关联班级表的主键

-- 创建班级表create table tb_class(id number(11),class_name varchar2(30));

-- 班级表创建主键alter table tb_class add constraint pk_class_id primary key(id);

-- 此时为从表tb_students的class_id建立外键去引用(关联) tb_classes表的主键alter table tb_students add constraint fk_student_classid foreign key(class_id) references tb_class(id);

-- 没有外键的表叫做主表，有外键的表叫做从表 -- 在多的一端建立外键去关联一的一端主键 (多个学生对应一个班级) -- 注意：tb_classes表没有主键的情况下创建外键约束就会报错 ORA-02270: 此列列表的唯一关键字或主键不匹配 -- 先插入主表数据再插入从表数据

insert into tb_student(

id,stu_name,stu_age,stu_mobile,stu_sex,stu_score,class_id)

values(106,'jerry',to_date('2010-10-19','YYYY-MM-DD'),'13475468765','F',55,1);

-- ORA-02291: 违反完整约束条件 (SCOTT.FK_STUDENT_CLASSID) - 未找到父项关键字

-- 先想clsaa插入数据insert into tb_class values(1,'j1009');

-- 成功插入上面数据insert into tb_student(

id,stu_name,stu_age,stu_mobile,stu_sex,stu_score,class_id)

values(106,'jerry',to_date('2010-10-19','YYYY-MM-DD'),'13475468765','F',55,1);

小结：

检查约束： check 关键字 能够再插入数据据之前对数据的有效性进行判断

非空约束 not null 关键字 再创建表和alter table上面都可以添加非空约束，一旦将某个列设置为非空约束那么该列在插入的时候不能为空

唯一约束 unique 关键 可以为空，但是某个列不能出现重复的数据

主键约束 primary key 非空并且唯一 主键列的数据必须确保在表中的唯一性(例如：身份证号)

外键约束 foreign key 在多张表之间建立数据的完整性，通常在从表(tb_students)建立外键去关联主表(tb_classes)的主键

先插入主表数据再插入从数据

建立外键之前最好将外键列设为非空

检查约束、唯一约束、非空约束他们是列级别的约束

主键约束、外键约束是行级别约束

外键约束最终的目的：最大限度的减少脏数据、重复数据的大量出现

重复的数据叫做数据冗余

插入：先插入主表数据，后插入从表数据

删除：先删除从表数据，后删除主表数据