Sql Server 复习资料总结

Sql Server 复习资料总结

/*Sql server 复习 创建数据库 */
 create database HNIUEAM

on primary

(name='HNIUEAM_primary',filename='f:\Mydb\HNIUEAM.mdf',size=4MB,maxsize=20MB,filegrowth=2MB),

filegroup Old

(name='file1',filename='f:\Mydb\HNIUEAM1.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup First

(name='file2',filename='f:\Mydb\HNIUEAM2.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup Second

(name='file3',filename='f:\Mydb\HNIUEAM3.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup Third

(name='file4',filename='f:\Mydb\HNIUEAM4.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup Fourth

(name='file5',filename='f:\Mydb\HNIUEAM5.ndf',size=1MB,maxsize=10MB,filegrowth=1MB)

log on

(name='HNIUEAM_log',filename='f:\Mydb\HNIUEAM.ldf',size=5MB,maxsize=10MB,filegrowth=1MB)

use HNIUEAM
 --创建教材信息表

create table T_BookInfo

(Book_id varchar(8) constraint pk_Book_id primary key,

Book_name varchar(50) not null,

Book_isbn varchar(17) not null,

Book_author varchar(20) not null,

Book_publisher varchar(50) not null,

Book_datetime datetime not null,

Book_price float not null,

Book_rkm text)


--创建供应商表

create table T_Supplier

(Supplier_id varchar(8) primary key,

Supplier_name varchar(50) not null,

Supplier_people varchar(8) not null,

Supplier_address varchar(50),

Supplier_phone varchar(15),

Supplier_postcode varchar(6),

Supplier_rkm text)

--创建订单信息表

create table T_Order

(Order_id varchar(8) primary key,

Supplier_id varchar(8) not null,

Order_datetime datetime not null,

Order_status bit not null

)

--创建订单详情表

create table T_OrderDetail

(OrderDet_id varchar(8) primary key,

Order_id varchar(8) not null,

Book_id varchar(8) not null,

OrderDet_num int not null,

OrderDet_status bit not null

)

 alter table T_BookInfo drop constraint pk_Book_id
 
 alter table T_BookInfo add constraint un_T_BookInfo unique(Book_isbn)
&n