SQL的数据定义功能

创建基本表

SET NOCOUNT ON
GO
DROP DATABASE IF EXISTS JX
GO
CREATE DATABASE JX
GO

USE JX
GO

---------BEGIN-----------
--*****创建一张与SC表结构完全相同的基本表SC_LIKE，定义含主属性Sno、Cno和Tno的主码PK_SCT*****
CREATE table SC_LIKE (

    Sno char(5)  not null,
    Cno char(3) not null,
    Tno char(5) not null,
    Grade decimal(5,1) null,
    constraint PK_SCT primary key (sno,cno,tno)
)



-----------END----------
GO
SELECT NAME,usertype,LENGTH,ISNULLABLE FROM SYSCOLUMNS WHERE ID=OBJECT_ID('SC_LIKE')
SELECT name,type,type_desc FROM sys.objects where name='PK_SCT' or name='SC_LIKE'
GO 

修改基本表

SET NOCOUNT ON
GO

USE JX
GO

SET NOCOUNT ON
GO
---------BEGIN-----------
----*****1、*为选课表SC增加主码约束PK_SCT 主码为(Sno,Cno,Tno)******
alter table SC 
add constraint PK_SCT primary key (Sno,Cno,Tno)


GO
----*****2、修改教师表Teacher的Tno为CHAR(4)******
alter table Teacher
alter column tno  char(4)

GO
----*****3、删除学生表Student的Sparent列******
alter table Student
drop column sparent 

GO
----*****4、为学生表Student增加列Total Decimal(4,1)，默认值为0******
alter table student 
add  Total Decimal(4,1)

GO

-----------END----------
--*******************************************************************
---***下面是结果评测部分，请勿删除或修改******
SELECT name,type,type_desc FROM sys.objects where name='PK_SCT' or name='SC'
SELECT NAME,usertype,LENGTH,ISNULLABLE FROM SYSCOLUMNS WHERE ID=OBJECT_ID('Teacher') AND NAME='Tno'
SELECT NAME,usertype,LENGTH,ISNULLABLE FROM SYSCOLUMNS WHERE ID=OBJECT_ID('Student')

索引的定义和维护

 SET NOCOUNT ON
GO

USE JX
GO

SET NOCOUNT ON
GO

---------BEGIN-----------
----*****1、在Course表的Cno字段创建降序排序的聚集索引Index_Cno******
create clustered index  Index_Cno on Course(cno,desc)


GO
----*****2、在Student表的Sname列上创建降序唯一索引Index_Sname******
create unique index Index_Sname on Student (Sname desc)

GO
----*****3、在Teacher表创建一个“姓名”升序和“系名”降序的字组合索引Index_TX******
create index Index_TX on Teacher(Tname,Tdept desc)

GO
----*****4、在SC表的Grade字段上创建一个降序索引Index_Grade******
create index Index_Grade on SC(Grade desc)

GO
----*****5、删除(4)创建的索引Index_Grade******
drop index SC.Index_Grade

GO
-----------END----------
--*******************************************************************
---***下面是结果评测部分，请勿删除或修改******
SELECT name,rows,indid FROM sysindexes WHERE name IN('Index_Cno','Index_Sname','Index_TX')
SELECT * FROM Course
SELECT Tdept,Tname FROM Teacher
INSERT INTO Student(Sno,Sname) VALUES('34','李强')