Oracle表约束及复杂查询(国外英文资料).doc

Oracle表约束及复杂查询(国外英文资料) Grant the resource to zy; Grant dba to zy; -- the constraints of the table Non-null constraint: the constraint of a field is not null Drop table t_user; The create table t_user ( T_number int the not null, T_name varchar2 (255). ); The only constraint of 1.2 is the constraint that a certain field can only be a non-repeatable value Alter table t_user add constraint t_number_unique unique (t_number); Insert into t_user values (1001, 3); Insert into t_user values (1002, li 4); Insert into t_user values (1003, wang 5); Check constraint: check that the value of a field meets the preset conditions Alter table t_user add constraint t_number_check check (t_number 2000); Insert into t_user values (2001, zhao 6); -1.4 primary key constraint: in a table, the only field that distinguishes the two records is the default non-empty and unique Alter table t_user add t_id int; Alter table t_user add constraint t_user_pk primary key (t_id); Select * from t_user; - 1.5 foreign key constraint: the link between the data of the two tables The create table t_department ( T_id int primary key, T_name varchar2 (255). ) Alter table t_user add t_did int; Add a field, and t_did indicates the department id that the employee belongs to Foreign key: the field t_did of the employee table is the primary key t_id of the external key reference department table Alter table t_user add constraint t_user_depart_fk foreign key (t_did) References t_department (t_id); Select * from t_department; Insert into t_department values (1, rd); Insert into t_department values (2, marketing); Commit; Insert into t_user values (1004, xiao li, 4, 1) Update t_user set t_did = 2 where t_id = 3; Commit; Delete from t_user where t_id = 3 -- delete department Update t_user set t_did = null where t_did = 1 Delete from t_department where t_id = 1 Basic syntax and query for SQL -- 2.1 basic query statements Select * | [distinct] columnname | expression [arg1, arg2...] The from tablename; -- unlock Scot