Oracle随堂笔记

1、知识点四：多表查询 emp dept分为4种1、等连接2、外连接3、自连接4、子查询3-1、等连接问题：显示员工姓名及所在部门的名称分析：姓名-emp.ename部门名称-dept.dname关系：emp(deptno) dept(deptno)- -显示员工姓名及所在部门的名称select emp.ename , dept.dname from emp , deptwhere emp.deptno = dept.deptno3-2、外连接问题：查询所有部门名称和对应的员工姓名，若该部门没有员工，只显示部门名称分析：部门名称dept.dname员工姓名 emp.enameOracle中使用(+

2、)表示外连接，可以理解 (+)所在字段的对侧(以二分开左右两部分)为主要显示信息- -查询所有部门名称和对应的员工姓名，若该部门没有员工，只显示部门名称select dept.dname , emp.ename from emp , deptwhere emp.deptno (+) = dept.deptno3-3、自连接问题：显示员工姓名及其上级员工的姓名分析：员工姓名 emp.ename上级员工姓名 select emp1.ename下级员工姓名,emp2.ename 上级员工姓名 from emp emp1 , emp emp2where emp2.empno = emp

3、1.mgr- -显示员工姓名及其上级员工的姓名，进行外连接3-4、子查询问题：查询工资高于公司平均工资的所有员工分析：公司的平均工资select avg(sal) from emp;select * from empwhere sal > ( select avg ( sal ) from emp )补充:Oracle数据类型-合理划分空间1 字符类型Char-固定长度-12000个字节varchar和varchar2-可变长度-14000个字符long 长字符类型2GB2 数值类型Integer-整型类型Number（长度，精度）Number （4,1） -999.9999.9Numb

4、er（2） -99993 日期类型date 一般日期类型-显示到秒timestamp 日期时间-精确到秒后8 位4 二进制类型- 音乐，图片和文件LOB 12000字节VLOB 14000字节5 大对象类型CLOB字符大对象类型4GBBLOB 二进制大对象类型2GBFLOB文件大对象类型4GBOracle的数据库对象七大对象：用户、表、约束、序列、视图、同义词和索引1、创建用户?2、授权?授权 咆建用户grant create session to nacy identified by密码3、锁定账户Alter user 用户名 account lock4、解锁账户?5、修改用户?,6、撤销授

5、权?7、删除账户?知识点一：用户 -User -账户、管理员-一切对象的宿主知识点二：表 -table -维护使用sql语句完成数据存储1、创建表 class student2、修改表student添力口 telephone和address两个字段-创建 class 表classid , classname 】 create table class (I classid number ( 2), classname varchar2 (10)-studentstuid,stuname,stusex,stuReg,classid create table student( stuid numbe

6、r ( 2), stuname varchar2 (10), stusex char ( 2) default '男'check(stusex in (男，女),stuReg date default sysdate , -date timestamp classid number ( 2) )-修改表student 添加telephone 和address 两个字段alter table studentadd (telephonevarchar2 (11),3、修改student表中telephone这个字段 number(8)类型address varchar2 ( 50)

7、- 修改student 表中telephone这个字段number(8)类型alter table studentmodify (telephonenumber (8)-创建表的过程中添加主键约束（列级约束）create table class1 （4、修改student表，删除stuname字段-修改student 表，删除address字段alter table student dropcolumn address5、修改字段名称-修改student表，把字段名telephone改成 phonealter table student renamecolumn telephoneto pho

8、ne6、给表改名字-修改student 表名为sturename studentto stu7、删除表? ? ? ?知识点三：约束 -constraint嗷据完整性和一致性大体分类：实体完整性-减少数据冗余-主键约束域完整性-数据的准确性-check约束引用完整性-数据的一致性-外键约束Oracle 约束象 5 大分类：主键（primary key）、非空（not null）、唯一性（unique）、检查（check）、外键（foreign key） 创建约束的两种方式：create table添加约束、alter table添加约束1、如何添加主键约束主键约束不允许为空classid num

9、ber ( 2) primary key , classname varchar2 (10) )-向已创建的表中添加主键约束alter table class add primary key (classid )-删除表中主键约束1.查找主键名称select* from user constraints2.通过主键名称删除主键alter table classl drop constraintSYS C0011510-添加主键约束的过程中自定义主键约束的名称 alter table class1 add constraint pk classid primary key ( classid )

10、-删除表中自定义名称的主键约束 ? ? ? ? ? -创建表的时候添加自定义名称的主键约束(表 级别约束) create table class2 ( classid number ( 2),classname varchar2 (10), constraint pk_classid2 primary key (classid ) )2、唯一约束unique允许数据为空，而且可以多行数 据为空-向已经创建的表中添加唯一性约束altertableclassaddunique (classname );-创建表的过程中添加唯一性约束(列级约束)SYS C0011516- -创建表的过程中添加唯一性

11、约束(表级约束) create table class2 ( classid number ( 2),classname varchar2 ( 30), constraintpk_classid primarykey (classid ), constraintun classnameunique (classname ) )- -向已经创建的表中添加唯一性约束(自定义名 称) alter table class add constraint un classnameunique (classname );- -删除表中唯一性约束alter table class1 drop constrai

12、nt un classname3、非空null(default)列级约束-向已经创建的表中添加非空约束alter table stu modify (stuname not null )- -向已经创建的表中删除非空约束alter table stu modify (stuname null ) -创建表的过程中添加非空约束(只有列级约束) create table stul ( stuid number (2) primary key , stuname varchar2 ( 10) not null , stusex varchar2 (4), stureg date ,classid n

13、umber ( 2) ) Default 约束它的用法和not null 是一样的 -向已经创建的表中添加default 约束 alter table stu modify (stusex default default null )-向已经创建的表中删除default 约束 alter table stu modify (stuname- -创建表的过程中添加default 约束(只有列级约束)create table stu1 (stuidnumber (2) primary key,stuname varchar2 (10) not null , stusex varchar2 (4)

14、default '男 stureg date ,classid number ( 2) )4、check 约束- -向已经创建的表中添加check约束alter table stu1 add check (stusex in (男，女)- -创建表的过程中添加check约束(列级约束) create table stu1 ( stuidnumber (2) primary key ,stuname varchar2 ( 10) not null , stusex varchar2 ( 4) check(stusex in (男，女)， stureg date ,classid numb

15、er ( 2) )- -创建表的过程中去创建check约束(表级约束) create table stu2 ( stuid number (2) primary key ,stuname varchar2 ( 10) not null , stusex varchar2 (4) default '男', stureg date default sysdate ,classid number ( 2),constraint ck_stusex check (stusex in(男，女)constraint notNull_stuname check(stuname is not

16、null); constraint default_stusex check(stusex defalt男');)-不提倡这么做-删除创建的check约束 ?5、外键约束向已经创建的表中添加外键约束 alter table stu1 addconstraint fk_classid foreign key (classid ) references class (classid ) -创建表的过程中添加外键约束(列级约束) create table stu3 ( stuid number (2) primary key , stuname varchar2 ( 10) not null

17、 , stusex varchar2 (4) default '男' check ( stusex in ('男'，'女')， stureg date default sysdate , classid number ( 2) references class (classid ) )-创建表的过程中添加外键约束（表级约束） create table stu3 （stuidnumber （2） primary key,stuname varchar2 （10） not null ,stusex varchar2 （4） default '

18、男' check （ stusex in （'男'，'女'）， stureg date default sysdate , classid number （ 2）,constraintfk1 classid foreignkey （ classid ） references class （classid ） ）注意:如果报错ORA-01950: 对表空间'USERS' 无权限步骤一：conn system/123456步骤二：alter user zhangsan0323 quota unlimited on users;总结：添加约束【

19、主键、唯一、 check、foreign keyAlter table 表名 add constraint 自定义约束名primary key（段名）|unique（段名）| check（.| 段名 between 0 and 100| 段名 in（男，女）foreign key（段名）references主键表名（主键段）添加约束【非空、缺省】Altert table 表名 modify （列名非空|缺省）注意：select * from user_constraints;select * from user_cols_cons知识点四：序列（sequence）来实现字段的自增长特性dba

20、sqlserver identifiedmysql auto_increment语法：create sequence序歹U名seq_描述start with 起始值 10increment by 步长 3maxvalue 最大值 20minvalue 最小值nocycle不循环cycle循环cache缓存1的数字问题1 :建立序列，从1开始每次增加1问题2:如何得到序列值?问题3:建立序列，从10开始每次增加3个 最大值20,循环？问题 4 ： minvalue 和 startwith 关系？1 . create sequence seq_1start with 1increment by 1

21、2 .create sequence seq_1（默认从1开始每次增加1,不循环）序列名.currval:获得序列当前值select seq_1.currval fromdual序列名.nextval:实现序列自增加select seq_1.nextval fromdual-建立序列，从10开始每次增加3个，最大值20 ,循环create sequence seq_3start with 10increment by 3minvalue 10maxvalue 20cyclecache 21. start with 不能小于 minvalue 的值2 .如果 minvalue 小于 start

22、with ,序列从 start with 开始, 进入循环后从 minvalue值开始至maxvalue3 .如果不设定 minvalue循环后从1开始问题5:序列的增长超过了 maxvalue的结果？Cycle参数存在，则从minvalue开始循环Nocycle参数存在（默认值），则报错误删除序列：drop sequence seq_1修改序列？ alter sequence序列名 注忌：startwith不能修改删除序列重新建立-利用序列完成class表中classid的数据插入功能- -步骤一：创建一个表create table newclass (classid number (4)

23、primary key , classname varchar (20) not null )- -步骤二：创建序列create sequence seq_classid- -步骤三：利用序列向表中插入三组数据(小班，中班，大班) insert into newclass (classid , classname )values ( seq_classid.nextval ,'大班')知识点五：视图view -简化查询、提高安全性语法 ：create or replace view v_名as复杂的select语句查询使用： select * from v_名【视图定义名称】w

24、here group by having order by注意：有权限才能创建视图grant create view to用户 不在resource角色的权限里步骤一：在system用户下给用户赋予创建视图的系统权限grant create view to scott;步骤一：定义视图- -希望去查询员工姓名和部门名称，以及员工的工资等级作为一个视 图create or replace view v e d sasselect emp.ename , dept.dname , salgrade.gradefrom emp , dept , salgradewhere emp.deptno =

25、dept.deptno and emp.sal > salgrade.losal and emp.sal <salgrade.hisal步骤二：使用视图select * from v e d s where grade = 4;select * from v e d sorder by grade desc步骤三：删除视图drop view v e d s;知识点六：同义词（synonym）对象table的别名 语法： create public synonym 名 for 对象 分类：公有-授权用户可以使用，私有-创建用户可以使用问题1 ：创建scott用户下的emp表的同义词问题2 :公有同义词创建私有同义词create synonym sy_e f