数据库第一次实验创建数据表参考代码

create database databasename  
on   
  ( name=wsb0077240_data,  
    filename='e:\database\databasename_data.mdf',  
    size=5,  
    maxsize=10,  
    filegrowth= 1 )  
log on  
 ( name=wsb0077240_log,  
   filename='e:\database\databasename_log.ldf',  
   size=3,  
   maxsize=5,  
   filegrowth=1)  
Go     
  
use databasename  
  
create table employee(  
emp_no      char(5)         Not null    primary key,  
emp_name    char(10)    Not null,  
sex      char(1)     null,  
constraint emp_sexchk check(sex in ('m','f')),  
dept         char(10)    null,  
title        char(10)    null,  
date_hired     datetime  null,  
birthday        datetime    Null,  
salary            int                  null,  
addr             char(50)   null,  
Mod_date         datetime   Default(getdate()),  
constraint emp_bh check(emp_no like ('E[0-9][0-9][0-9][0-9]'))  
)  
  
create table customer(  
cust_id       char(5)   Not null         primary key,  
cust_name     char(20)   null,    
addr          char(40)   null,    
tel_no        char(13)   null,    
zip       char(6)   null      
)  
  
create table product(  
prod_id          char(5)    Not null    primary key,  
prod_name        char(20)   Not null      
)  
  
create table sales(  
order_no          int                 Not null  primary key,  
cust_id       char(5)    null,    
sale_id       char(5)    null,    
tot_amt  numeric(9,2)      Not    null,  
order_date    datetime  null,  
ship_date         datetime null,  
invoice_no     char(10)  null,  
foreign Key (cust_id) References customer(cust_id),  
foreign Key (sale_id) References employee(emp_no),  
constraint sale_un unique (invoice_no)  
)  
  
create table sale_item(  
order_no               int             Not null,  
prod_id            char(5)  Not null,     
qty            int  Not null,  
unit_price       numeric(7,2)   Not null,  
order_date       datetime   null,  
constraint zhukey primary key(order_no,prod_id),  
foreign Key (prod_id) References product(prod_id),  
foreign Key (order_no) References sales(order_no)  
)