SQL _Server建库语句_sqlserver建库语句-CSDN博客

本文详细介绍如何使用SQL创建数据库及表结构，并通过具体案例演示数据表的创建、约束设置及数据插入等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.创建数据库

名为“STUDENT2”的数据库，包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“STUDENT2_DATA”，操作系统文件名为“STUDENT2_DATA.MDF”，初始容量大小为5M，最大容量为20M，文件的增长量为20%。事务日志文件的逻辑文件名为“STUDENT2_LOG”，物理文件名为“STUDENT2_LOG.LDF”，初始容量大小为5M，最大容量为10M，文件增长量为2M。数据文件与事务日志文件都放在D盘根目录。
(需在D盘先建立DATA文件夹)

CREATE DATABASE DATA_STU
ON   PRIMARY
 (NAME = 'STUDENT2_DATA', 
FILENAME = 'D:\DATA\STUDENT2_DATA.MDF' , 
SIZE = 5MB, 
MAXSIZE = 20MB, 
FILEGROWTH = 20%)
LOG ON 
(NAME ='STUDENT2_LOG', 
FILENAME = 'D:\DATA\STUDENT2_LOG. LDF', 
SIZE = 5MB, 
MAXSIZE = 10MB,
FILEGROWTH = 2MB)

2.创建数据表

IF OBJECT_ID('students') IS NOT NULL   
            DROP TABLE students
create table students
(
        sno char(9) PRIMARY KEY,
        sname varchar(20) unique,
        ssex char(4) check(Ssex = '男' or Ssex = '女') default('男'),
        sage nchar(10),
        sdept char(20) 
      );
IF OBJECT_ID('course') IS NOT NULL   
            DROP TABLE course
create table course
(
    cno    char(4) primary key,
    cname  nvarchar(20) not null,
    ch     tinyint,
    credit tinyint
)
IF OBJECT_ID('sc') IS NOT NULL   
            DROP TABLE sc
create table sc
(
    sno char(9),
    cno char(4),
    score int,
    primary key(sno,cno),
    foreign key(sno) references students(sno),
    foreign key(cno) references course(cno)
)

2.创建数据库2

/*创建数据库inventory*/
create database inventory
on primary
(
name='inventory_data',
filename='D:\DATA\inventory_data.MDF',
size=10MB,
maxsize=unlimited,
filegrowth=20%
)
log on
(
name='inventory_log',
filename='D:\DATA\inventory_log.LDF',
size=2MB,
maxsize=5MB,
filegrowth=1MB
)

/*使用数据库inventory*/

use inventory
go

/*创建数据表goods*/
create table goods
(
gno   char(6)  primary key,
gname  nvarchar(10)   not null,
price   float   not null ,
product   nvarchar(30)   not null,
)

/*创建数据表store*/
create table store
(
stno   char(3)   primary key  ,
address   nvarchar(30)   not null,
telephone   varchar(11)   not null  ,
/*建立check约束*/
capacity   smallint    constraint ck_store_capacity  check (capacity<=300)
)

/*创建数据表invent*/
create table invent 
(
/*建立外建约束*/
stno   char(3)   not null   constraint fk_invent_stno foreign key references 

store(stno),
gno   char(6)  not null    constraint fk_invent_gno foreign key references goods

(gno),
number   int   ,

constraint pk_invent_StnoGno  primary key(stno,gno),
)

/*创建数据表manager*/
create table manager
(
mno   char(3)  primary key ,
mname   nvarchar(10)  not null,
sex   nchar(1)   constraint ck_manager_sex check (sex in('男','女')),
birthday   datetime    constraint ck_manager_birthday  check (birthday between 

'1957-1-1' and '2010-1-1'),
stno   char(3)   constraint fk_manager_stno  foreign key references store(stno),
)

/*插入模拟数据*/
insert into goods values('bx-179','冰箱',3200,'青岛海尔');
insert into goods values('bx-340','冰箱',2568,'北京雪花');
insert into goods values('ds-001','电视',1580,'四川长虹');
insert into goods values('ds-018','电视',2980,'青岛海尔');
insert into goods values('ds-580','电视',6899,'南京熊猫');
insert into goods values('kt-060','空调',3560,'青岛海尔');
insert into goods values('kt-330','空调',4820,'青岛海信');
insert into goods values('xyj-01','洗衣机',980,'无锡小天鹅');
insert into goods values('xyj-30','洗衣机',858,'南京熊猫');

insert into store values('001','1号楼','89000001',67);
insert into store values('002','1号楼','89000002',78);
insert into store values('003','2号楼','89000003',56);
insert into store values('004','2号楼','89000004',77);
insert into store values('005','3号楼','89000005',80);
insert into store values('006','3号楼','89000006',65);


insert into invent values('001','ds-001',20); 
insert into invent values('001','ds-018',16); 
insert into invent values('002','bx-179',12); 
insert into invent values('003','bx-340',10); 
insert into invent values('003','ds-018',8); 
insert into invent values('003','xyj-30',21); 
insert into invent values('004','bx-179',5); 

insert into manager values('101','张力','男','1989-2-3 0:00:00','001');
insert into manager values('102','李明','男','1979-7-23 0:00:00','001');
insert into manager values('103','王辉','男','1978-9-18 0:00:00','002');
insert into manager values('104','张凤玉','女','1978-9-12 0:00:00','002');
insert into manager values('105','刘晓宏','男','1990-5-25 0:00:00','003');
insert into manager values('106','郑文杰','男','1972-9-6 0:00:00','003');
insert into manager values('107','明宇','男','1989-4-2 0:00:00','004');
insert into manager values('108','詹虎新','男','1970-7-29 0:00:00','004');
insert into manager values('109','李品慧','女','1973-9-28 0:00:00','005');
insert into manager values('110','刘丽华','男','1980-5-3 0:00:00','005');
insert into manager values('111','王文宇','男','1980-5-23 0:00:00','006');
insert into manager values('112','王玮','女','1978-8-13 0:00:00','006');

3.建立学生课程数据库

CREATE DATABASE XSKC
ON PRIMARY
(NAME='xskc_data',
FILENAME='d:\DATA\XSKC_DATA.MDF',
SIZE=10MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=20%
)
LOG ON
(NAME='xskc_log',
FILENAME='d:\DATA\XSKC_LOG.LDF',
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB
) 
use XSKC
GO

create table student
( 
    sno char(9) primary key,
    sname char(20) not null,
    ssex char(2) check(ssex='男' or ssex='女') default '男',
    sage smallint,
    sdept char(20)
)
create table course
(
     cno char(4) primary key,
    cname char(40) ,
    cpno char(4),
    ccredit smallint,
)
create table sc
( 
    sno char(9),
    cno char(4),
    grade smallint check(grade>=80 and grade<=90),
    constraint pk_js primary key(sno,cno),
    constraint fk_course_sc foreign key(cno) references course(cno),
    constraint fk_student_sc foreign key(sno) references student(sno),
)
insert into student
values ('201215121','李勇','男',20,'cs')
insert into student
values ('201215122','刘晨','女',19,'cs')
insert into student
values ('201215123','王敏','女',18,'ma')
insert into student
values ('201515125','张立','男',19,'is')

insert into course
values('1','数据库','5',4)
insert into course
values('2','数学',null,2)
insert into course
values('3','信息系统','1',4)
insert into course
values('4','操作系统','6',3)
insert into course
values('5','数据结构','7',4)
insert into course
values('6','数值分析',NULL,4)
insert into course
values('7','PASCAL','6',4)

insert into sc
values('201215121','1',82)
insert into sc
values('201215121','2',85)
insert into sc
values('201215121','3',88)
insert into sc
values('201215122','2',90)
insert into sc
values('201215122','3',80)