sqlquery1.sql

这是我写的产品管理系统

use master
go
--用来检查其中是否有销售管理系统XiaoShou数据库的名字
if exists (select * from sysdatabases where name = 'XiaoShou')
begin
	drop database XiaoShou
end
else
begin
--创建数据库
	create database XiaoShou
end
go
--使用数据库
use XiaoShou
go
--创建部门表(t_part)
create table t_part
(
	P_id int identity(1001,1) check(P_id >= 1001 and P_id <= 9999) primary key,
	P_Name varchar(20) not null,
	P_remark text not null
)
go
--创建员工信息表(t_emp)
create table t_emp
(
	ID int identity(1001,1) check(ID >= 1001 and ID <= 9999) primary key,
	T_realname varchar(20) not null,
	T_pid int references t_part(P_id) not null,
	T_sex bit not null,
	T_address varchar(20) not null,
	T_age int check (T_age >=20 and T_age <= 100) not null,
	T_tel varchar(20) not null check(T_tel like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or T_tel like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
go
--创建产品信息表(t_products)
create table t_products
(
	D_id int primary key check(D_id >= 1001 and D_id <= 9999),
	D_name	varchar(20) not null,
	D_price	money not null,
	D_remark text not null
)
go
--创建销售纪录表(t_ji)
create table t_ji
(
	P_id int references t_part(P_id) not null,
	ID	int references t_emp(ID) not null,
	D_id int references t_products(D_id) not null,
	S_count	int check(S_count >0) default(0),
	S_name Varchar(20),
	S_date datetime
)
go
--创建客户信息表(t_kf)
create table t_kf
(
	K_id int primary key check(K_id >= 1001 and K_id <= 9999),
	K_name varchar(20)	,
	K_address varchar(20),
	K_tel varchar(20) check(K_tel like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or K_tel like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
	K_remark text
)
go
--创建用户表(t_user)
create table t_user
(
	T_id int identity(1001,1) primary key check(T_id >= 1001 and T_id <= 9999) ,
	T_username varchar(20) not null,
	T_password	varchar(20) not null --check(T_password like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
go
--向其中添加相关的信息
--向表t_part中添加一条信息
insert into t_part values('张三','武汉软件有限公司');
select * from t_part
--向表t_emp中添加一条信息
insert into t_emp values('李四',1001,1,'武汉',20,'02788431982');
select * from t_emp
--向表t_products中添加数据
insert into t_products values(1001,'羽绒服','320','用来冬天保暖');
select * from t_products
--向表t_ji中添加数据
insert into t_ji values(1001,1001,1001,1,'李动',getDate());
select * from t_ji
--向表t_kf中添加数据
insert into t_kf values(1001,'李涛','宜昌','13256987456','我的东西一定是很好的');
select * from t_kf;
--向表t_user中添加数据
insert into t_user values('赵钱','123456');
select * from t_user