SQL应用举例new[1].doc

创建数据库 IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = Nxsgl1) DROP DATABASE xsgl1 GO CREATE DATABASE xsgl1 ON (NAME = Nxsgl_Data, FILENAME = NC:\Program Files\Microsoft SQL Server\MSSQL\data\xsgl1_Data.MDF , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = Nxsgl_Log, FILENAME = NC:\Program Files\Microsoft SQL Server\MSSQL\data\xsgl1_Log.LDF , SIZE = 1, FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_AS 创建表 use xsgl1 go use xsgl1 go CREATE TABLE S1 (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, Ssex CHAR(1), Birth smalldatetime , Sdept CHAR(15)) Go CREATE TABLE C1 (Cno CHAR(4) NOT NULL , CName CHAR(10), CONSTRAINT c1 PRIMARY kEY(,Cno), ) Go CREATE TABLE SC1 (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT CONSTRAINT c1 PRIMARY kEY(Sno,Cno), CONSTRAINT c2 FOREIGN KEY(Sno) REFERENCES S(Sno), CONSTRAINT c3 FOREIGN KEY(Cno) REFERENCES C(Cno)) Go CREATE TABLE [dbo].[班级] ( [班级编号] [nvarchar] (4) NOT NULL , [班级名称] [nvarchar] (20) NOT NULL ) GO CREATE TABLE [dbo].[课程] ( [课程号] [nvarchar] (3) NOT NULL , [课程名] [nvarchar] (24) NULL ) GO CREATE TABLE [dbo].[学生] ( [学号] [nvarchar] (7) NOT NULL , [姓名] [nvarchar] (8) NULL , [性别] [nvarchar] (2) NULL , [出生日期] [smalldatetime] NULL , [班级编号] [nvarchar] (4) NULL , [zp] [image] NULL ) GO CREATE TABLE [dbo].[成绩] ( [学号] [nvarchar] (7) NOT NULL , [课程号] [nvarchar] (3) NOT NULL , [成绩] [smallint] NULL ) GO use xsgl1 go TAB ALTER LE [dbo].[班级] WITH NOCHECK ADD CONSTRAINT [PK_班级] PRIMARY KEY CLUSTERED ( [班级编号] ) ON [PRIMARY] GO ALTER TABLE [dbo].[课程] WITH NOCHECK ADD CONSTRAINT [PK_课程] PRIMARY KEY CLUSTERED ( [课程号] ) ON [PRIMARY] GO ALTER TABLE [dbo].[学生] WITH NOCHECK ADD CONSTRAINT [PK_学生] PRIMARY KEY CLUSTERED ( [学号] ) ON [PRIMARY] GO ALTER TABLE [dbo].[学生] ADD CONSTRAINT [FK_学生_班级] FOREIGN KEY ( [班级编号] ) REFERENCES [dbo].[班级] ( [班级编号] ) GO ALTER TABLE [dbo].[成绩] ADD CONSTRAINT [FK_成绩_课程] FOREIGN