s1(java)项目实战（固定资产管理）（SQL_sever）

最新推荐文章于 2022-04-25 09:01:02 发布

goushaoping04

最新推荐文章于 2022-04-25 09:01:02 发布

阅读量790

点赞数

分类专栏： SQL 文章标签： java sql null go manager table

本文链接：https://blog.csdn.net/goushaoping04/article/details/1504375

版权

SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

该博客介绍了如何在SQL Server中创建名为'asset'的数据库，详细展示了数据库的创建和配置过程，以及相关表的删除与重建。包括Action, Asset, ChildKind, Employee, FatherKind和Manager六张表的设计，每张表的字段及约束条件，如主键和外键关系。" 112788381,10526154,信贷与经济危机：生产过剩的根源,"['经济学', '金融', '经济模型', '宏观经济', '债务管理']

摘要由CSDN通过智能技术生成

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'asset')
DROP DATABASE [asset]
GO

CREATE DATABASE [asset] ON (NAME = N'asset_Data', FILENAME = N'D:/41y01/project/asset_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'asset_Log', FILENAME = N'D:/41y01/project/asset_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'asset', N'autoclose', N'false'
GO

exec sp_dboption N'asset', N'bulkcopy', N'false'
GO

exec sp_dboption N'asset', N'trunc. log', N'true'
GO

exec sp_dboption N'asset', N'torn page detection', N'false'
GO

exec sp_dboption N'asset', N'read only', N'false'
GO

exec sp_dboption N'asset', N'dbo use', N'false'
GO

exec sp_dboption N'asset', N'single', N'false'
GO

exec sp_dboption N'asset', N'autoshrink', N'true'
GO

exec sp_dboption N'asset', N'ANSI null default', N'false'
GO

exec sp_dboption N'asset', N'recursive triggers', N'false'
GO

exec sp_dboption N'asset', N'ANSI nulls', N'false'
GO

exec sp_dboption N'asset', N'concat null yields null', N'false'
GO

exec sp_dboption N'asset', N'cursor close on commit', N'false'
GO

exec sp_dboption N'asset', N'default to local cursor', N'false'
GO

exec sp_dboption N'asset', N'quoted identifier', N'false'
GO

exec sp_dboption N'asset', N'ANSI warnings', N'false'
GO

exec sp_dboption N'asset', N'auto create statistics', N'true'
GO

exec sp_dboption N'asset', N'auto update statistics', N'true'
GO

use [asset]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ChildKind_FatherKind]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ChildKind] DROP CONSTRAINT FK_ChildKind_FatherKind
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Action]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Action]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Asset]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Asset]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChildKind]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ChildKind]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FatherKind]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FatherKind]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Manager]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Manager]
GO

CREATE TABLE [dbo].[Action] (
[asset_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[action_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[manager_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[asset_use] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Asset] (
[asset_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[asset_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[asset_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[asset_price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[asset_buytime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[asset_status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[asset_user] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[childkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ChildKind] (
[fatherkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[childkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[childkind_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employee] (
[employee_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[employee_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[employee_post] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FatherKind] (
[fatherkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[fatherkind_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Manager] (
[manager_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[manager_username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[manager_password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Asset] WITH NOCHECK ADD
CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
(
[asset_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChildKind] WITH NOCHECK ADD
CONSTRAINT [PK_ChildKind] PRIMARY KEY CLUSTERED
(
[childkind_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FatherKind] WITH NOCHECK ADD
CONSTRAINT [PK_MainKind] PRIMARY KEY CLUSTERED
(
[fatherkind_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Manager] WITH NOCHECK ADD
CONSTRAINT [PK_Manager] PRIMARY KEY CLUSTERED
(
[manager_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChildKind] ADD
CONSTRAINT [FK_ChildKind_FatherKind] FOREIGN KEY
(
[fatherkind_id]
) REFERENCES [dbo].[FatherKind] (
[fatherkind_id]
)
GO