主码约束:
(1)列级别
create table ***
(snum char(9) constraint snum_pk primary key,
...
)
(2)表级别
create table ***
(snum char(9),
...
constraint snum_pk primary key(snum))
alter table ***
ADD constraint snum_pk primary key(snum)
只有数字约束:
ALTER TABLE student ADD CONSTRAINT sno_stu CHECK(sno LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
某一列为空/不为空,不同情况的约束条件:
写一个比较复杂的view,碰到一种情况
create or replace view view_name
as
select * from
table_a,
table_b,
table_c,
where table_a.col1=table_b.col1(+)
and table_b.col2='123'
其中table_b的col2的某几行都可能为空,希望在它不为空的条件下,约束“ table_b.col2='123' ” 成立。如何写这种sql
回答:
create or replace view view_name
as
select * from
table_a,
table_b,
table_c,
where table_a.col1=table_b.col1(+)
and nvl(table_b.col2,'123')='123';
--==========--
SQL语句大全
一、基础
1、说明:创建数据库
Create DATABASE database-name
2、说明:删除数据库
drop database dbname
3、说明:备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明:创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、说明:删除新表
drop table tabname