ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Constraint and Set operation

Constraint and Set operation

原创 Linux操作系统 作者:zhaoyu728 时间:2019-05-30 07:51:05 0 删除 编辑

SQL> desc member
名称 是否为空? 类型
----------------------------------------------------------------------------------- -------- ------
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(100)
CITY VARCHAR2(30)
PHONE VARCHAR2(15)
JOIN_DATE NOT NULL DATE
MEMBER_ID NOT NULL NUMBER(10)

SQL> alter table member add constraint member_uk unique(member_id);
alter table member add constraint member_uk unique(member_id)
*
第 1 行出现错误:
ORA-02261: 表中已存在这样的唯一关键字或主键


SQL> alter table title_copy add constraint title_copy_fk foreign key(title_id) references title(title_id);
alter table title_copy add constraint title_copy_fk foreign key(title_id) references title(title_id)
*
第 1 行出现错误:
ORA-02275: 此表中已经存在这样的引用约束条件

SQL> run
1 create table rental
2 (book_date date default sysdate primary key,
3 member_id number(10),
4 copy_id number(10),
5 act_ret_date date,
6 exp_ret_date date default sysdate+2,
7 title_id number(10),
8 constraint rental_fk1 foreign key (member_id) references member(member_id),
9 constraint rental_fk2 foreign key (copy_id) references title_copy(copy_id),
10* constraint rental_fk3 foreign key (title_id) references title_copy(title_id))

表已创建。

SQL> create sequence member_id_seq
2 increment by 1
3 start with 101
4 nomaxvalue
5 nocycle
6 nocache;

序列已创建。
SQL> insert into title(title_id,title,description) values(title_id_seq.nextval,'Willie and Christmas too','2005-08-10');


SQL> select deptno from emp
2 where deptno in
3 (select deptno from emp
4 union
5 select deptno from dept);

DEPTNO
----------
10
10
10
20
20
20
20
20
20
30
30

DEPTNO
----------
30
30
30
30

已选择15行。
SQL> select title_id from title
2 intersect
3 select title_id from title_copy;

TITLE_ID
----------
98
99
101
102
The order by clause:Can appear only at the very end of the statement
Will accept the column name,aliases from the first select statement,or the positional notation

Matching the SELECT statement:
SQL> select deptno,to_char(null) Loc,hiredate from emp
2 union
3 select deptno,Loc,to_date(null) hiredate from dept;

DEPTNO LOC HIREDATE
---------- ------------- --------------
10 NEW YORK
10 09-6月 -81
10 17-11月-81
10 23-1月 -82
20 DALLAS
20 17-12月-80
20 02-4月 -81
20 03-12月-81
20 19-4月 -87
20 23-5月 -87
20 02-8月 -05

DEPTNO LOC HIREDATE
---------- ------------- --------------
30 CHICAGO
30 20-2月 -81
30 22-2月 -81
30 01-5月 -81
30 08-9月 -81
30 28-9月 -81
30 03-12月-81
40
110 SHANG HAI
120 BEI JING

已选择21行。

Controlling the Order of Rows:
SQL> col a_dumy noprint
SQL> select 'string' as "My stream",3 a_dumy from dual
2 union
3 select 'I''d like to teach',1 from dual
4 union
5 select 'The world to',2 from dual order by 2;

My stream
-----------------
I'd like to teach
The world to
string

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8382469/viewspace-259838/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2006-12-03

  • 博文量
    36
  • 访问量
    28420