SQL数据库系统概论

数据库系统概论

一 . 建商店表

代码如下

if exists (select * from sys.databases where name = 'eshop')
 begin
 use master
 drop database eshop
 end


create database eshop
use eshop

create table members
 (
    M_account varchar(20) primary key,
    M_name   varchar(20) not null,
    M_birth  varchar(20),
    M_sex  char(2),
    M_address varchar(50),
    M_salary decimal(7,1),
    M_password varchar(20) not null
  )


 create table products
   (
      P_no varchar(20) primary key,
      P_name varchar(50) not null,
      P_date datetime,
      P_quantity int not null,
      P_price smallmoney not null,
      P_information varchar(500)
   )

create table orders
   (
     M_account varchar(20) not null,
     P_no varchar(20) not null,
     O_quantity   int not null,
     O_date datetime not null,
     O_confirm_state bit not null,
     O_pay_state  bit not null,
     O_send_state bit not null,
     primary key(M_account,P_no),
     foreign key(M_account) references members(M_account),
     foreign key(P_no) references products(P_no)
   )

--会员表添加记录
INSERT INTO members VALUES('liuzc518','刘志成','1972-05-18','男','湖南株洲',3500.0,'liuzc518')
INSERT INTO members VALUES('liuzc','刘爱平','1974-07-18','男','江西南昌',4500.0,'liuzc')
INSERT INTO members VALUES('zhao888','赵爱云','1972-02-12','男','湖南株洲',5500.0,'zhao888')
INSERT INTO members VALUES('wangym','王咏梅','1974-08-06','女','湖南长沙',4000.0,'wangym0806')
INSERT INTO members VALUES('jinjin','津津有味','1982-04-14','女','北京市',8200.0,'jinjin')
INSERT INTO members VALUES('lfz','刘法治','1976-08-26','男','天津市',4500.0,'lfz0826')
INSERT INTO members VALUES('zhangzl','张自梁','1975-04-20','男','湖南株洲',4300.0,'zhangzl')

--产品表添加记录
INSERT INTO products VALUES('0130810324','清华同方电脑','2005-12-11',7,8000.0,'优惠多多')
INSERT INTO products VALUES('0140810330','洗衣粉','2005-05-31',1000,8.6,'特价销售')
INSERT INTO products VALUES('0140810332','红彤彤腊肉','2005-5-20',43,15.0,'是一种卫生食品')
INSERT INTO products VALUES('0140810333','力士牌香皂','2005-05-06',22,6.0,'是一种清洁用品')
INSERT INTO products VALUES('0240810330','电动自行车','2005-05-31',10,1586.0,'价廉物美')
INSERT INTO products VALUES('0240810333','自行车','2005-05-31',10,586.0,'价廉物美')
INSERT INTO products VALUES('0910810001','爱国者MP3','2005-05-31',100,450.0,'价廉物美')
INSERT INTO products VALUES('0910810002', '商务通','2005-05-20',10,850.0,'价廉物美')
INSERT INTO products VALUES('0910810003','名人好记星','2005-05-31',100,550.0,'价廉物美')
INSERT INTO products VALUES('0910810004','奥美嘉U盘','2005-05-31',100,350.0,'价廉物美')


--订单表添加记录
INSERT INTO orders VALUES('liuzc','0130810324',1,'2005-10-09',0,0,0)
INSERT INTO orders VALUES('liuzc','0910810004',2,'2005-10-09',1,1,0)
INSERT INTO orders VALUES('liuzc','0910810001',1,'2005-10-09',1,1,0)
INSERT INTO orders VALUES('wangym','0910810001',1,'2005-08-09',1,0,0)
INSERT INTO orders VALUES('jinjin','0910810004',1,'2005-08-09',1,1,1)
INSERT INTO orders VALUES('lfz','0910810001',1,'2005-08-09',0,0,0)
INSERT INTO orders VALUES('lfz','0910810004',2,'2005-08-09',1,1,1)
INSERT INTO orders VALUES('zhao888','0240810333',2,'2005-06-06',1,1,0)