sql考试简单题目.docVIP

创建数据库 create datebase 库名 on [primary] (name=逻辑文件名， filename=物理文件名， maxsize=最大存储空间， size=初始存储空间， filegrowth=每次增加存储空间 n 或 n%)， filegroup 文件组名 log on 例 create datebase xsdaon (name= data1, filename=’e:\xsda\data1.mdf’, size=5, maxsize=, filegrowth=2) log on (name=xsda_log, filename=’e:\xsda\xsda.ldf’,size=2, filgegrowth=2) go 建表和关联，主外键，级联 CREATE TABLE table-name ( {column_definition/column_name as computed_column_expression }[,…n] [table_constraint] ) [ON {filegroup_name / DEFAULT}] [TEXTIMAGE_ON {filegroup_name / DEFAULT}] column_definition::= column_name data_type [IDENTITY [(seed , increment)]] [column_constraint][…n] 例 use market go create table orders (orderid int identity(1,1) constraint pk_orderid primary key, goodsname varchar(20) not null references goods(name) on delete cascade, customerid int not null foreign key references customers(customerid) on delete no action, quantity int not null constraint ck_quantity check(quantity0) , ordersum money not null, orderdate datetime default getdate() ) 三、触发器 create trigger 触发器名 on 表名/视图名 [with encryption] for / after/ instead of [insert] [,update] [,delete] as sql 语句 1、Xsda表中不能有“会计” 专业的学生 Create trigger xsda_insert On xsda After insert As If (select 专业 from inserted)=’会计’ Begin Print ‘不能输入会计专业的学生’ Rollback tran end 2、级联删除 使用约束 create table xsxk ( xh char(4) not null foreign key reference xsda(xh) on delete cascade, kh char(3) not null grade int ) (2)使用触发器 create trigger xsda_delete on xsda after delete as delete xsxk from xsxk a join deleted b on a.xh=b.xh (或 delete xsxk where xh in (select xh from deleted)) 3 在子表中禁止插入主表中不存在的记录 使用约束 create table xsxk ( xh char(4) foreign key reference xsda(xh) kh char(3) not null grade int ) (2) 使用触发器 create trigger xsxk_insert on xsxk after insert as if (select count(*) from xsda a join inserted b on a.xh=b.xh) = 0 begin print ‘没有这个学生’ rollback end 4 在子表中插入记录时，禁止插入不满足主表中某些条件的记录(该问题用约束很难实现) create trigger xsxk