SQL中创建数据库的方法.docx

SQL中创建数据库的方法 --------创建数据库 ----use master ----GO ----IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = test1) ----DROP DATABASE test1 ----GO ----CREATE DATABASE test1 ------创建教师表 --use test1 --go --if exists (select name from test1.dbo.sysobjects where name =teacher) --drop table teacher --create table teacher --( --tno int not null primary key, --tname nvarchar(15) --) --go ----插入数据到教师表中 --insert into teacher(tno,tname) values(1,小屋); --insert into teacher(tno,tname) values(2,DAVA); --insert into teacher(tno,tname) values(3,刘局); --insert into teacher(tno,tname) values(4,张厚); ---- ----创建学生表 ----use test1 ---- go ---- if exists(select name from test1.dbo.sysobjects where name =student) ---- drop table studnet ----create table student ---- ( ---- sno int not null primary key, ---- sname nvarchar(15) not null, ---- sage datetime not null, ---- ssex char(2) not null ------ ) ------go ------插入数据 ----INSERT INTO student(sno,sname,sage,ssex) VALUES(1,张三,1980-1-23,男) ----INSERT INTO student(sno,sname,sage,ssex) VALUES(2,李四,1982-12-12,男) ----INSERT INTO student(sno,sname,sage,ssex) VALUES(3,张飒,1981-9-9,男) ----INSERT INTO student(sno,sname,sage,ssex) VALUES(4,莉莉,1983-3-23,女) ----INSERT INTO student(sno,sname,sage,ssex) VALUES(5,王弼,1982-6-21,男) ----INSERT INTO student(sno,sname,sage,ssex) VALUES(6,王丽,1984-10-10,女) ----.创建课程表 --CREATE TABLE [dbo].[course]( -- [cno] [int] NOT NULL PRIMARY KEY, -- [cname] [nvarchar](20) NOT NULL, -- [tno] [int] NOT NULL --) ----创建外键,已经存在两张表，我想用sql语句建立这两张表的主外键关系 ----ALTER TABLE 表名1 add constraint 约束名 foreign key（字段) references 表名2（字段） --alter table course add constraint course_to_student foreign key(tno) references teacher(tno); --ALTER TABLE [dbo].[course] WITH CHECK ADD --CONSTRAINT [FK_course_teacher] FOREIGN KEY([tno]) --REFERENCES [dbo].[teacher] ([tno]) ----插入数据 --insert into course(cno,cname,tno) values(1,企业管理,3) --insert into course(cno,cname,tno) values(2,马克思,1) --insert into course(cno,cname,tno) values(3,UML,2) --insert in