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)的主键
先插入主表数据再插入从数据
建立外键之前最好将外键列设为非空
检查约束、唯一约束、非空约束他们是列级别的约束
主键约束、外键约束是行级别约束
外键约束最终的目的:最大限度的减少脏数据、重复数据的大量出现
重复的数据叫做数据冗余
插入:先插入主表数据,后插入从表数据
删除:先删除从表数据,后删除主表数据