sql指令集课件

1、ORACLE学习笔记创建表空间1. 创建表空间的指令。名称：user1；地址；初始大小；是否自动扩展；扩展大小；最大尺寸create tablespace user1 datafile 'f:oraclewuzherenuser1_data.dbf' size 10m autoextend on（off） next 2m maxsize 30m（unlimited）2. 查看表空间创建成功的指令select file_name, tablespace_name from dba_data_files order by file_name3. 查看表空间。select table

2、space_name,status,allocation_type from dba_tablespaces4. 删除表空间 user1（同时删除文件加including）drop tablespace user1 including contents and datafiles5. 查看所有的表空间信息select tablespace_name,status,allocation_type from dba_tablespaces6. 查询每个用户的默认表空间select user_id,username,default_tablespace from dba_users7. 修改数据库的

3、默认表空间（user1）alter database default tablespace user18. 修改表空间名称（user1user10），不对数据文件产生影响，不可对系统表空间进行重命名。alter tablespace user1 rename to user10创建数据表（主要介绍sql语言）1. 创建数据表（t_user）create table t_user (user_id number not null, user_name varchar2(20) not null, user_email varchar2(20)2. 查看数据表所属的表空间（未成功=为选定行）sel

4、ect table_name,tablespace_name from user_tables where table_name='t_user'3. 查看数据表信息（t_user）describe t_user4. 修改数据表结构（t_user的user_emailemail）alter table t_user rename column user_email to email5. 修改列的属性（1个或多个）alter table t_user modify (user_name varchar2(20), user_email varchar2(30)6. 为表添加列al

5、ter table t_user add(beizhu varchar2(10)7. 删除表中的某列（需要增加关键字column）alter table t_user drop column remark8. 修改表名（表本身的属性，不可轻易修改一个表名，表明为操作标识，会影响已有程序的运行）alter table t_user rename to t_users9. 删除数据表drop table t_user10. 删除表约束drop table t_user cascade constraints临时表会话级临时表1. 创建会话级临时表（当前事务提交时，保留数据）create globa

6、l temporary table tmp_user_session (user_id int,user_name varchar2(20),user_email varchar2(30) on commit preserve rows2. 向临时表中插入数据insert into tmp_user_session (user_id,user_name,user_email) values (1,'alex','alex')3. 查表中数据，验证是否插入正确（tmp_user_session）select * from tmp_user_session4. 提交

7、数据修改，结束会话commit会话级临时表仅存在当前会话中，一旦会话结束，数据库将自动清理其中的数据（相当于使用turncate table命令），各会话之间的数据也是相互独立、互不影响的。事务级临时表1. 创建事务级临时表，并插入两条测试数据create global temporary table tut (id int, name varchar2(20), email varchar2(30) on commit delete rows;insert into tut (id,name,email) values (1,'boy','boy');inse

8、rt into tut (id,name,email) values (2,'girl','girl')2. 提交数据，结束事务rollback事务级临时表的数据仅在当前事务有效，一旦事务结束，所有数据会自动清空。查看临时表在数据库中的信息1. 查询表空间信息（注意大小写）临时表的表空间为空select table_name,tablespace_name from user_tables where table_name='T_USER' or table_name='TUT' or table_name='TUS

9、9;2. 查询临时表的不同（名称，是否临时表，类型）select table_name,temporary,duration from user_tables where table_name='T_USER' or table_name='TUS' or table_name='TUT'临时表的应用场景1.大表分割 2.解决并行问题 3.作为数据缓存特殊的表dual1. 查询dual表dual 仅有一列 dummy 字符长度可变字符串 允许为空DESC DUALdual表应用场景1. 获得当前日期（sysdate是系统函数，返回当前日期）sel

10、ect sysdate from dual2. 进行数学运算select 3*4+12 as result from dual3. 实现序列运算每执行一次，序列自动加一select t_user_sql.nextval from dual约束主键约束1. 创建主键约束create table student(id number primary key,name varchar2(20),birthday date, address varchar2(50),phone varchar2(20)2. 查看主键约束1.查看约束方法一：对象右键VIEWKEYS方法二：select table_nam

11、e,constraint_name,constraint_type,status from user_constraints where table_name='STUDENT'（表名信息，约束名信息，约束类型，当前状态）2.如何获得主键的作用列select constraint_name,table_name,column_name from user_cons_columns where constraint_name='SYS_C005145'（约束名信息，表名信息，约束所在列）3. 测试主键约束insert into student (id,name,b

12、irthday,address) values (1,'张三',to_date('1998-12-01','YYYY-MM-DD'),'人民路')insert into student (id,name,birthday,address) values (2,'李四',to_date('1999-06-03','YYYY-MM-DD'),'人民路')无法成功插入，存在主键约束4. 显式命名主键约束constraint pk_studentcreate table st

13、udent(id number constraint pk_student primary key,name varchar2(20),birthday date, address varchar2(50),phone varchar2(20)5. 创建主键的另一种写法主键描述与其他列的描述是并列关系，逗号隔开create table st(id number,name varchar2(20),birthday date, address varchar2(50),phone varchar2(20),constraint pk_st primary key(name)6. 创建多列主键多个

14、列名之间逗号隔开create table st(id number,name varchar2(20),birthday date, address varchar2(50),phone varchar2(20),constraint pk_st primary key(name,birthday,address)修改表的主键约束表只能具有一个主键1. 为表添加主键未创建alter table st modify (id number primary key)2. 为表添加多列主键逗号隔开alter table st add constraint pk_st primary key (name

15、,birthday,address)3. 删除主键方法一：删除表的主键alter table ST drop primary key方法二：删除约束的语法来删除表的主键(表的约束有若干个，指定约束名称)alter table st drop constraint pk_st4. 启用禁用主键禁用：alter table st disable primary key插入两条信息insert into st (id,name,birthday,address) values (1,'张三',to_date('1998-12-01','YYYY-MM-DD&#

16、39;),'人民路','#39;);insert into st (id,name,birthday,address) values (2,'张三',to_date('1998-06-30','YYYY-MM-DD'),'人民路','#39;);启用：alter table st enable primary keyORA-02437:无法验证(SYSTEM.PK_ST)-违反主键修改：update st set name='李四'

17、 where id=25. 重命名主键查询oracle自动分配主键名称：select constraint_name ,table_name,constraint_type,status from user_constraints where table_name='ST'rename关键字重命名主键：alter table st rename constraint sys_c005152 to pk_st;主键应用场景1. 对于完整性要求比较高的数据表都应建立主键2. 对于经常按照某列进行查询的数据表，应该考虑建立主键3. 考虑是否对外键有利外键约束两表之间的相互依存性创建外

18、键约束1. 建立外键建立表cts和ods：create table cts(id number primary key,name varchar2(10),address varchar2(20),phone varchar2(20);create table ods(o_id number primary key,id number,g_name varchar2(20);建立ods到cts的外键关联：(注意大小写)alter table ODS add constraint fk_oc foreign key (id) references CTS (id);2. 查看外键信息(表名，约束

19、名，约束类型，与该约束关联的其他约束名)select table_name, constraint_name, constraint_type, r_constraint_name from user_constraints where table_name='ODS'查询关联约束信息：select * from user_constraints where constraint_name='SYS_C005153'3. 验证外键约束的有效性(只向子表插入信息)insert into ods (o_id,id,g_name) values (1,1,'F

20、ABRAK');ORA-02291: 违反完整约束条件 (SYSTEM.FK_OC) - 未找到父项关键字先父表后子表：insert into cts(id,name,address,phone) values (1,'王厚胜','哈工大','#39;);insert into ods (o_id,id,g_name) values (1,1,'FABRAK');4. 修改字表数据验证外键约束的有效性update ods set g_name='fuck' where o_id=1;运行、查

21、询正常update ods set id=3 where o_id=1;ORA-02291: 违反完整约束条件 (SYSTEM.FK_OC) - 未找到父项关键字当修改非外键列的数据时，不会受外键影响；但是当修改外键列为不合理数据时，外键约束禁止该动作的运行。5. 修改父表数据验证外键约束的有效性修改父表数据：update cts set id=3 where id=1;ORA-02292: 违反完整约束条件 (SYSTEM.FK_OC) - 已找到子记录父表的主键列，是子表的外键关联列，修改造成目标值与原值不同，造成子表数据丢失，故报错。删除父表：delete from cts where

22、id=1;ORA-02292: 违反完整约束条件 (SYSTEM.FK_OC) - 已找到子记录删除整个表：drop table CTS; ORA-02449: 表中的唯一/主键被外键引用即使数据表中没有数据亦不能在删除子表之前删除父表，因为外键约束已经将子表与父表绑定在一起，父表将无法自由删除。外键约束的两个方面对数据的约束，父表的数据和子表的数据必须保持一致对表之间关系的约束，子表依附于附表存在，一旦建立关联关系，父表将不能在删除子表前删除。级联更新与级联删除1. 级联更新创建表：create table customs(c_id number primary key,c_name var

23、char2(50),c_add varchar2(50),c_phone varchar2(20),email varchar2(20);create table orders(o_id number primary key,c_id number,goods varchar2(20);创建延迟校验的外键约束(是否延迟执行；deferred事务结尾检查约束，immediate每条语句结尾检查约束）alter table orders add constraint fk_o_d foreign key (c_id) references customs(c_id) deferrable init

24、ially deferred;插入数据：insert into customs(c_id ,c_name ,c_add ,c_phone ,email) values (1,'王鹏','哈工大机电学院','#39;,'wangpeng');insert into orders(o_id ,c_id ,goods) values (1,1,'皮鞋');修改延迟校验外键约束表的建设(执行一次提交动作)保持提交数据的一致性update customs set c_id=3 where c_id=1;upd

25、ate orders set c_id=3 where c_id=1;commit;查看更新结果：select c_id,c_name,c_add,c_phone,email contrator from customs;select o_id,c_id,goods from orders;2. 级联删除创建外键约束时指定延迟校验，并且延迟校验的时机为事务提交，可通过先删除主表数据，然后删除子表数据的方式实现级联跟新的效果（on delete cascade）创建级联删除外键约束：alter table orders add constraint fk foreign key (c_id) r

26、eferences customs(c_id) on delete cascade;删除主表中的记录，子表自动删除：delete from customs where c_id =3;修改外键属性1. 重命名外键（rename）alter table orders rename constraint fk to fuck;2. 禁用启用外键禁用：alter table orders modify constraint fuck disable;启用：alter table orders modify constraint fuck enable;检查状态：select constraint_n

27、ame,status from user_constraints where constraint_name='FUCK'3. 是否对已有数据进行校验(novalidatevalidate)alter table orders modify constraint fuck enable novalidate;novalidate只是一种临时状态4. 删除外键使用删除约束的统一语法：alter table drop constraintalter table orders drop constraint fuck外键使用严格遵循父子关系的数据表应该使用外键将应用程序中的父子关系转

28、移到外键约束不要过分使用外键唯一性约束唯一的标识一行(主键设计为表示惟一一条记录，唯一性约束保证列自身值的唯一性)创建唯一性约束unique1. 创建唯一性约束创建表时添加唯一性约束：create table users(id number primary key,name varchar2(50),address varchar2(50),phone varchar2(20),email varchar2(20) unique);2. 查看唯一性约束select table_name,constraint_name,constraint_type,status from user_const

29、raints where table_name='USERS'查询约束作用列：select constraint_name,column_name from user_cons_columns where constraint_name='SYS_C005161'3. 验证唯一性约束插入两条记录：insert into users(id ,name ,address ,phone ,email) values (1,'王朋','哈工大机电学院','#39;,' peng3182555 

30、9;);insert into users(id ,name ,address ,phone ,email) values (2,'赵娟','东北林业大学','#39;,'peng3182555');ORA-00001: 违反唯一约束条件修改唯一性约束1. 添加唯一性约束alter table users add constraint u_phone unique (phone);alter table users add constraint u_name unique (name);alter table us

31、ers modify constraint 2. 删除唯一性约束alter table users drop constraint u_phone;alter table users drop constraint u_name;3. 重命名唯一性约束（自动分派为SYS_C005161）alter table users rename constraint SYS_C005161 to u_email;4. 禁用启用唯一性约束启用：alter table users enable constraint u_name; alter table users modify constraint u_

32、name enable;禁用：alter table users disable constraint u_name; alter table users modify constraint u_name disable;唯一性约束的使用可建立在列或列的组合上，作为逐渐约束的补充，在业务逻辑上保证记录的唯一性往往采用唯一性约束来实现检查约束创建检查约束（关键字check） 创建检查约束create table student(id number primary key,name varchar2(10),subject varchar2(20),score number constraint

33、c_s check(score between 0 and 100);尝试插入合法数据：insert into student values(1,'王朋','数学',100);insert into student values(2,'王鹏','语文',50);insert into student values(3,'邓健','化学',0);非法数据：insert into student values(4,'倪海波','外语',101);ORA-02290: 违反

34、检查约束条件 利用检查约束限制数据格式create table emplyees(id number primary key,name varchar2(10),grade varchar2(10),salary number,constraint check_s check(grade in('one','two','three')and(grade='one' and salary<=800 or grade='two' and salary<=500 or grade='three'

35、; and salary<=400);尝试输入合法数据：insert into emplyees values(1,'王朋','one',730)insert into emplyees values(2,'王鹏','two',500)insert into emplyees values(3,'赵娟','three',340)非法数据：insert into emplyees values(4,'邓健','three'); ORA-00947: 没有足够的值修

36、改检查约束1. 添加alter table emplyees add constraint c_sa check(salary<=1000);2. 删除alter table emplyees drop constraint c_sa;3. 重命名（c_nacheck_na）alter table emplyees rename constraint c_na to check_na;4. 禁用启用禁用：alter table emplyees disable constraint c_sa;alter table emplyees modify constraint c_sa disa

37、ble;启用：alter table emplyees enable constraint c_sa;alter table emplyees modify constraint c_sa enable;检查约束的使用通过很灵活的约束条件完成约束任务，但不能过多使用检查约束，尤其是复杂的检查约束，因更新数据库时会进行约束检查浪费大量资源。默认值约束创建默认值约束对象为列，每列只能有一个默认值约束（关键字default）1. 创建默认值create table po(id number,name varchar(30),price number,status varchar2(3) defaul

38、t 'act');2. 查看默认值约束信息（user_tab_columns包含所有用户列的信息）select table_name,column_name,data_type,data_length,data_default from user_tab_columns where table_name='PO' and column_name='STATUS'3. 默认值的使用insert into po(id ,name ,price) values(1,'王朋',100);insert into po(id ,price)

39、values(1,100);未设置验证,只输入指定项亦是可以的。4. 使用函数作为默认值创建使用sysdate函数的表create table sss(id number primary key,name varchar2(20) unique,price number check(price between 1 and 50),s_time date default sysdate);尝试插入数据：insert into sss(id ,name ,price) values (1,'王朋',30);insert into sss(id ,name ,price) value

40、s (1,'王鹏',30);ORA-00001: 违反唯一约束条件insert into sss(id ,name ,price) values (1,'王朋',30);insert into sss(id ,name ,price) values (2,'王朋',30);ORA-00001: 违反唯一约束条件insert into sss(id ,name ,price) values (1,'王朋',30);insert into sss(id ,name ,price) values (2,'王鹏',60);

41、ORA-02290: 违反检查约束条件修改默认值约束1. 添加默认值约束（不能使用add constraint命令，只能是修改列的同时添加默认值）alter table sss modify price number default 1;2. 删除默认值约束alter table sss modify price number default null;3. 修改默认值alter table sss modify s_time default trunc(sysdate,'dd');视图存储查询，但不会存储数据（物化视图除外），可进行查询、插入、更新和删除数据关系视图 内嵌视图

42、 对象视图 物化视图关系视图建立关系视图建立关系视图创建测试数据表：create table emply(id number primary key, f_name varchar2(4),l_name varchar2(4),province varchar2(10),city varchar2(10),salary number);插入数据：insert into emply values(1,'张','军','广东','深圳',3000);insert into emply values(2,'刘','

43、;新','广东','广州',4000);insert into emply values(3,'王','一帆','广西','桂林',3800);insert into emply values(4,'周','峰','江苏','苏州',4000);insert into emply values(5,'徐','佳林','浙江','杭州',2900);创建员工视图（cr

44、eate view）：（视图定义，连接符）create view vw_emply as select id,f_name | l_name as em_name,province |'-'| city as location from emply;查看视图定义（user_views查询所有用户的视图定义）select text from user_views where view_name='VW_EMPLY'查看试图内容select * from vw_emply;修改删除关系视图1. 修改视图尝试修改视图（增加salary）create view vw_e

45、mply as select id,f_name | l_name as em_name,province |'-'| city as location,salary from emply;ORA-00955: 名称已由现有对象使用应用以下方法(关键字create or replace view)：create or replace vw_emply as select id,f_name | l_name as em_name,province |'-'| city as location,salary from emply; ORA-00922: 选项缺失或

46、无效create or replace view vw_emply as select id,f_name | l_name as em_name,province |'-'| city as location,salary from emply;2. 删除视图drop view vw_emply;联接视图相对于多个表而言的视图创建视图：create table emply(id number primary key,f_name varchar2(4),l_name varchar2(4),salary number);create table em_salary(s_id

47、number,s_price number,sale_by number);插入数据：insert into emply values (2,'王','朋',400);insert into em_salary(s_id, s_price, sale_by) values (2,100,2);创建自动结算视图：create or replace view vw_em as select e.id as id,e.f_name | e.l_name as vw_name,e.salary*s.s_price as vw_goal from emply e,em_s

48、alary s where e.id=s.sale_by编译视图查看视图状态信息（user_object包括数据库中所有对象信息，包括表、视图、约束等）select object_name,status from user_objects where object_name='VW_EM'修改表的结构：alter table emply add(age number);查询：INVALID无效，因为基础表发生变化，需重新编译视图重新编译视图：alter view vw_em compile;（亦可以执行一次对视图的查询操作）修改数据表的结构才会影响视图的有效性，修改数据并不会影

49、响数据的有效性，但是并不是修改数据结构重新编译后就可以正常运行，例如当重命名基础表的列名，而该列又在视图定义中。使用force选项强制创建视图视图的基础表未创建前，仍希望可以创建基于不存在表的视图，可使用force强制创建。创建：create or replace view vw_em as select book_name,author from book;ORA-00942: 表或视图不存在create or replace force view vw_em as select book_name,author from book;Warning: View created with co

50、mpilation errors查询是否创建成功：select object_name,status from user_objects where object_name='VW_EM' and object_type='VIEW'视图虽然创建，但仍不可用，当基础表创建后，视图方可正常使用。利用视图更新数据（数据必须是直接从基础表中获得的）ORA-01779试图修改的列在基础表中的映射并没有唯一性约束。ORA-01733试图修改列为虚列，没有基础表的列与之对应。查看视图列的可更新情况：select table_name,column_name,updatabl

51、e,insertable deletable from user_updatable_columns where table_name=vw_books; 对查询结果进行操作，尽管某些数据的列项为空with check option 选项（创建视图时常用选项之一，启用该选项，则数据库保证视图在数据更新之后与更新之前的结果集相同）创建基础表，并插入数据：create table book(id number,name varchar2(10),author varchar2(10),press varchar2(20),p_date date);insert into book values(1

52、,'围城','钱钟书','人民文学出版社',to_date('2006-4-9','yyyy-mm-dd');创建视图：create or replace view vw_book as select * from book where id=3;通过视图更新数据：update vw_book set id=4 where name=平凡的世界;查询视图为空,因为视图的查询条件限制了查询内容选择with check option选项create or replace view vw_book as select *

53、 from book where id=3 with check option;update vw_book set id=4;ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规关系视图 保障数据安全性 数据整合 数据透明性内嵌视图内嵌视图的使用1. 利用内嵌视图进行查询（可使用别名）2. 利用内嵌视图更新数据表3. 删除和插入内嵌视图 内嵌视图、临时表、关系视图的使用场景内嵌视图并不是真正创建视图，故不会进行大量的I/O操作，节省大量数据库资源，若临时使用另外的查询结果时建议使用内嵌视图，当背多处使用（可复用性高）应考虑使用临时表或者关系视图。 分辨内嵌视图

54、与子查询在sql语句中，代替了表的位置的查询即为内嵌视图，例如from或into关键字之后，注意内嵌视图的定义必须用小括号括起来。对象视图（虽然可以从对象中获取数据，就像数据真实存储于对象中一样。但是在对象的概念之下，数据仍然是存储于关系表中的）要创建对象，首先要建立对象类型创建对象employee：create type employee is object (employee_id number, employee_name varchar2(20), location varchar2(50), salary number)创建对象视图：create or replace view ov

55、_employees of employee with object oid(employee_id) as select employee_id, last_name | first_name, province | city,salary from employees 查询对象视图：desc ov_employees;查询对象视图信息：select view_type,view_name,oid_textfrom user_viewswhere view_name='ov_employees'ORA-01730: 指定的列名数无效注意对象与源表得的一一对应基础表中获得的各列

56、数据将和对象中的属性按照定义顺序一一对应，不能颠倒，否则：ORA-00932，并且，数据类型需要一致，否则将报错。对象试图可以进行增删改查操作，类似于关系视图。关系视图、内嵌视图和对象视图，实际都是通过定制查询，并利用查询定义获取数据，三种视图不会直接存储数据，每次操作时，都会进行编译。物化视图该视图实际存储数据对于大数据表的处理显得重要，可将统计结果存储在物化视图中，节省数据库资源和时间，但不适合统计更新频繁的数据。物化视图的使用1创建实验表，并加入数据：create table sales(id number primary key,name varchar2(10),price numb

57、er,quantity number,s_month varchar2(4),sale_by number);insert into sales values(6,'皮鞋',100,4,'3月',2);创建物化视图create materialized view mv_sales创建物化视图，注意无replacebuild immediate立即编译refresh on commit基础表修改后自动更新视图enable query rewrite启用查询重写功能as select s_month,sum(price*quantity) from salesgro

58、up by s_month;统计要求查询数据：select * from mv_sales;2.查看物化视图信息（user_mviews和user_objects）select mview_name,query from user_mviews where mview_name='MV_SALES'select object_name,object_type,status from user_objects where object_name='MV_SALES'物化视图创建视图的同时也创建了一个同名的物理表，而物理表才是真正存储数据的地方物化视图的数据加载bu

59、ild immediate该项用于立即加载物化视图的数据，亦即在创建物化视图的同时，立即根据定义从基础表中获取数据，并将数据添加到物化视图中，选项build deffered则表示延迟加载数据。使用延迟加载是必要的，尤其是在基础表数据量巨大，数据使用高峰期时，后造成客户端的延迟。为在后续的开发中使用物化视图可采用延迟加载数据的策略，类似于创建一个空表，并不向表中插入数据，但可以成功的在开发中引用。物化视图的数据更新插入数据，commit后，物化视图自动更新物化视图的自动更新与关系视图的自动更新不同。物化视图的自动更新，是将更新后的数据存储起来，而关系视图则每次都会查询基础表；但物化视图的自动更新对于基础表的大量频繁更新是不利的，因为频繁的更新会占用大量数据库资源，因此物化视图应使用在读取频繁、更新较少的情况下。查