sql server和mysql server sql语句对比

sql server
/* 1.创建EmployeeInfo(雇员信息)表 */
use myshop
if exists(select * from sysobjects where name='EmployeeInfo')
return
create table EmployeeInfo
(
  Eid    int primary key not null,
  EName  nvarchar(50) not null,
  Sex    char(2) check (Sex in('男', '女')) default '女' not null,
  Age    tinyint check (Age > 18) not null,
  -- 唯一值
  Card   char(18) unique not null,
  Phone  nvarchar(11),
  Adress nvarchar(50),
  -- 默认值为系统日期
  ToDate datetime default(getdate()) not null,
  Job    nvarchar(8) not null
)

	/* 2.创建UserLogin(用户登录)表 */
if exists(select * from sysobjects where name='UserLogin')
return
create table UserLogin
(
  Uid int foreign key not null,
  Eid int foreign key references EmployeeInfo(Eid) not null,
  UPassword nvarchar(50) not null
)


	/* 2.创建MemberInfo(会员信息)表 */
if exists(select * from sysobjects where name='MemberInfo')
return
create table Memberinfo
(
  Mid int primary key not null,
  MName nvarchar(30) not null,
  Sex char(2)check(Sex in('男','女')) default '女' not null,
  Age smallint check(Age > 18) not null,
  Card char(18) unique not null,
  Phone nvarchar(11),
  Adress nvarchar(50),
  OnDate datetime default(getdate()) not null,
  WMid char(8) unique
)


	/* 3.创建ProductInfo(产品信息)表 */
if exists(select * from sysobjects where name='ProductInfo')
return
create table ProductInfo
(
  Pid nvarchar(20) primary key not null,
  PName nvarchar(30) not null,
  Price real check(Price > 0) not null,
  JFen smallint check(JFen > 0) not null,
  Ptype nvarchar(30) not null
)

         /* 4.创建ProductToInfo（产品入库登记）表 */
if exists(select * from sysobjects where name='ProductToInfo')
return
create table ProductToInfo
(
  ToId int IDENTITY (1, 1) primary key not null,
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  Num smallint check (Num > 0) not null,
  ToDate datetime default(getdate()) not null,
  ForWho nvarchar(50) not null
)


	/* 5.创建Stcok(库存信息)表 */
if exists(select * from sysobjects where name='Stcok')
return
create table Stcok
(
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  -- 库存数量需要大于等于0
  Num smallint check (Num >= 0) not null
)
	/* 6.创建SellInfo(产品销售信息)表 */
if exists(select * from sysobjects where name='SellInfo')
return
create table SellInfo
(
  SIid int IDENTITY (1, 1) primary key not null,
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  Num smallint check (Num > 0) not null,
  OutDate datetime default(getdate()) not null
)
	/* 7.创建temp表 */
if exists(select * from sysobjects where name='temp')
return
create table temp
(
  Pid nvarchar(20) foreign key references ProductInfo(Pid) not null,
  Pname nvarchar(30) not null,
  Price real check (Price > 0) not null,
  Num smallint check (Num > 0) not null
)

mysql server

use myshop


/* 1.创建EmployeeInfo(雇员信息)表 */
delimiter //
create table EmployeeInfo(
  Eid    int primary key not null,
  EName  nvarchar(50) not null,
  Sex    char(2)  default '女' not null,
  Age    tinyint  not null,
  -- 唯一值
  Card   char(18) unique not null,
  Phone  nvarchar(11),
  Adress nvarchar(50),
  -- 默认值为系统日期
  ToDate timestamp not null default current_timestamp, 
  Job    nvarchar(8) not null,
  check (Sex in('男', '女')),
  check (Age > 18)
)
//
	/* 2.创建UserLogin(用户登录)表 */
delimiter //
create table UserLogin
(
  Uid int  AUTO_INCREMENT  primary key not null,
  UPassword nvarchar(50) not null,
  Eid int,
  foreign key (Eid) references EmployeeInfo(Eid)
)
//
	/* 3.创建MemberInfo(会员信息)表 */
delimiter //
create table Memberinfo
(
  Mid int primary key not null,
  MName nvarchar(30) not null,
  Sex char(2) default '女' not null,
  Age smallint  not null,
  Card char(18) unique not null,
  Phone nvarchar(11),
  Adress nvarchar(50),
  OnDate timestamp not null default current_timestamp, 
  WMid char(8) unique,
  check(Sex in('男','女')),
  check(Age > 18)
)
//

	/* 4.创建ProductInfo(产品信息)表 */
delimiter //
create table ProductInfo
(
  Pid nvarchar(20) primary key not null,
  PName nvarchar(30) not null,
  Price real  not null,
  JFen smallint  not null,
  Ptype nvarchar(30) not null,
  check(Price > 0),
  check(JFen > 0)
)
//
         /* 5.创建ProductToInfo（产品入库登记）表 */
delimiter //
create table ProductToInfo
(
  ToId int AUTO_INCREMENT primary key not null,
  Pid nvarchar(20) not null,
  Num smallint  not null,
  ToDate timestamp not null default current_timestamp, 
  ForWho nvarchar(50) not null,
  foreign key (Pid) references ProductInfo(Pid) ,
  check (Num > 0)
)
//

	/* 6.创建Stcok(库存信息)表 */
delimiter //
create table Stcok
(
  Sid int AUTO_INCREMENT primary key not null,
  Pid nvarchar(20)  not null,
  -- 库存数量需要大于等于0
  Num smallint  not null,
  foreign key (Pid) references ProductInfo(Pid),
  check (Num >= 0)
)
//
	/* 7.创建SellInfo(产品销售信息)表 */
delimiter //
create table SellInfo
(
  SIid int AUTO_INCREMENT primary key not null,
  Pid nvarchar(20)  not null,
  Num smallint not null,
  OutDate timestamp not null default current_timestamp, 
  foreign key (Pid) references ProductInfo(Pid),
  check (Num > 0) 
)
//
	/* 8.创建temp表 */
delimiter //
create table temp
(
  id int AUTO_INCREMENT primary key not null,
  Pid nvarchar(20)  not null,
  Pname nvarchar(30) not null,
  Price real not null,
  Num smallint  not null,
  foreign key (Pid) references ProductInfo(Pid),
  check (Price > 0) ,
  check (Num > 0)
)
//