USE [master]
GO
/****** Object:  Database [车辆管理系统]    Script Date: 2024/12/29 13:22:48 ******/
CREATE DATABASE [车辆管理系统]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'YAN', FILENAME = N'E:\SQLDate\车辆管理系统.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'YAN_log', FILENAME = N'E:\SQLDate\车辆管理系统_log.ldf' , SIZE = 3072KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [车辆管理系统] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [车辆管理系统].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [车辆管理系统] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [车辆管理系统] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [车辆管理系统] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [车辆管理系统] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [车辆管理系统] SET ARITHABORT OFF 
GO
ALTER DATABASE [车辆管理系统] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [车辆管理系统] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [车辆管理系统] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [车辆管理系统] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [车辆管理系统] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [车辆管理系统] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [车辆管理系统] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [车辆管理系统] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [车辆管理系统] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [车辆管理系统] SET  ENABLE_BROKER 
GO
ALTER DATABASE [车辆管理系统] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [车辆管理系统] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [车辆管理系统] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [车辆管理系统] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [车辆管理系统] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [车辆管理系统] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [车辆管理系统] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [车辆管理系统] SET RECOVERY FULL 
GO
ALTER DATABASE [车辆管理系统] SET  MULTI_USER 
GO
ALTER DATABASE [车辆管理系统] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [车辆管理系统] SET DB_CHAINING OFF 
GO
ALTER DATABASE [车辆管理系统] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [车辆管理系统] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [车辆管理系统] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [车辆管理系统] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'车辆管理系统', N'ON'
GO
ALTER DATABASE [车辆管理系统] SET QUERY_STORE = ON
GO
ALTER DATABASE [车辆管理系统] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO
USE [车辆管理系统]
GO
/****** Object:  User [Employee]    Script Date: 2024/12/29 13:22:48 ******/
CREATE USER [Employee] FOR LOGIN [Employee] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [Analyst]    Script Date: 2024/12/29 13:22:48 ******/
CREATE USER [Analyst] FOR LOGIN [Analyst] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [Admin]    Script Date: 2024/12/29 13:22:48 ******/
CREATE USER [Admin] FOR LOGIN [Admin] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  Table [dbo].[发生]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[发生](
	[驾驶员信息] [char](50) NULL,
	[人员赔偿] [money] NULL,
	[驾驶执照] [char](20) NOT NULL,
	[事故编号] [char](15) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[驾驶执照] ASC,
	[事故编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[view_发生]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 创建视图
CREATE VIEW [dbo].[view_发生]
AS
select *  from 发生;
GO
/****** Object:  Table [dbo].[客户]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[客户](
	[客户编号] [char](20) NOT NULL,
	[名字] [nchar](5) NOT NULL,
	[地址] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[客户编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[品牌]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[品牌](
	[品牌号] [char](20) NOT NULL,
	[品牌名] [nchar](10) NOT NULL,
	[品牌所属公司] [char](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[品牌号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[汽车]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[汽车](
	[驾驶执照] [char](20) NOT NULL,
	[年限] [date] NOT NULL,
	[型号] [char](10) NULL,
	[品牌号] [char](20) NULL,
	[客户编号] [char](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[驾驶执照] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[vw_CustomerCarInfo]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_CustomerCarInfo]
AS
SELECT c.名字, c.地址, a.驾驶执照, a.型号, b.品牌名
FROM 客户 c
JOIN 汽车 a ON c.客户编号 = a.客户编号
JOIN 品牌 b ON a.品牌号 = b.品牌号;
GO
/****** Object:  View [dbo].[vw_AccidentCustomers]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_AccidentCustomers]
AS
SELECT c.名字, c.地址, COUNT(e.事故编号) AS 事故次数
FROM 客户 c
JOIN 汽车 a ON c.客户编号 = a.客户编号
JOIN 发生 e ON a.驾驶执照 = e.驾驶执照
GROUP BY c.名字, c.地址;
GO
/****** Object:  View [dbo].[vw_CustomerPurchaseTime]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_CustomerPurchaseTime]
AS
SELECT c.名字, c.客户编号, MIN(a.年限) AS 最早购车时间
FROM 客户 c
JOIN 汽车 a ON c.客户编号 = a.客户编号
GROUP BY c.名字, c.客户编号;
GO
/****** Object:  View [dbo].[vw_BrandCarCount]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_BrandCarCount]
AS
SELECT b.品牌名, COUNT(a.驾驶执照) AS 汽车数量
FROM 品牌 b
LEFT JOIN 汽车 a ON b.品牌号 = a.品牌号
GROUP BY b.品牌名;
GO
/****** Object:  Table [dbo].[事故]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[事故](
	[事故编号] [char](15) NOT NULL,
	[地点] [char](20) NOT NULL,
	[日期] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[事故编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[vw_AccidentCarDetails]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_AccidentCarDetails]
AS
SELECT o.事故编号, o.地点, o.日期, a.驾驶执照, a.型号, b.品牌名, e.驾驶员信息, e.人员赔偿
FROM 事故 o
JOIN 发生 e ON o.事故编号 = e.事故编号
JOIN 汽车 a ON e.驾驶执照 = a.驾驶执照
JOIN 品牌 b ON a.品牌号 = b.品牌号;
GO
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'赵强  二十岁 单身                                 ', 100.0000, N'430923201501010001  ', N'20150201       ')
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'孙悦 二十一岁 单身                                ', 200.0000, N'430923201501010002  ', N'20150202       ')
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'孙悦 十九岁 单身                                  ', 200.0000, N'430923201501010002  ', N'20150207       ')
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'春花 二十二岁 单身                                ', 300.0000, N'430923201501010003  ', N'20150203       ')
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'夏荷 二十三岁 单身                                ', 400.0000, N'430923201501010004  ', N'20150204       ')
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'秋菊 二十四岁 单身                                ', 500.0000, N'430923201501010005  ', N'20150205       ')
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'孙悦 十九岁 单身                                  ', 200.0000, N'430923201501010007  ', N'20150207       ')
INSERT [dbo].[发生] ([驾驶员信息], [人员赔偿], [驾驶执照], [事故编号]) VALUES (N'孙悦 十九岁 单身                                  ', 10.0000, N'430923201501010008  ', N'20150208       ')
GO
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150101            ', N'赵强   ', N'山东科技大学')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150102            ', N'张志成  ', N'山东科技大学')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150103            ', N'马骏   ', N'山东科技大学')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150104            ', N'周杰   ', N'广州市天河区')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150105            ', N'吴悠   ', N'深圳市南山区')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150106            ', N'郑明   ', N'广州市天河区')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150107            ', N'王芳   ', N'深圳市南山区')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150108            ', N'陈晨   ', N'广州市天河区')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150109            ', N'林娜   ', N'深圳市南山区')
INSERT [dbo].[客户] ([客户编号], [名字], [地址]) VALUES (N'20150110            ', N'刘辉   ', N'广州市天河区')
GO
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'                    ', N'          ', N'                    ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'123456              ', N'大成科技      ', N'东风集团            ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010101          ', N'大飞科技      ', N'小米                ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010102          ', N'东风雪铁龙     ', N'东风集团            ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010103          ', N'东风锐达起亚    ', N'东风集团            ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010104          ', N'东风本田      ', N'东风集团            ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010105          ', N'东风有限      ', N'东风集团            ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010106          ', N'东风乘用车     ', N'东风集团            ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010107          ', N'东方之星      ', N'东方汽车集团        ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010108          ', N'瑞风荣耀      ', N'瑞风汽车工业        ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010109          ', N'启辰之光      ', N'启辰汽车制造        ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015010110          ', N'幻速传奇      ', N'幻速汽车科技        ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'2015011             ', N'科技有限公司    ', N'小米                ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'201507777           ', N'山东科技      ', N'山东有限公司        ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'20160715            ', N'红米小蜜      ', N'小米                ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'20232999            ', N'科技中心      ', N'安全中心            ')
INSERT [dbo].[品牌] ([品牌号], [品牌名], [品牌所属公司]) VALUES (N'666666              ', N'小米        ', N'东风集团            ')
GO
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010001  ', CAST(N'2016-01-01' AS Date), N'AK01      ', N'2015010101          ', N'20150101            ')
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010002  ', CAST(N'2016-01-02' AS Date), N'AK02      ', N'2015010102          ', N'20150102            ')
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010003  ', CAST(N'2016-01-03' AS Date), N'小米      ', N'2015010103          ', N'20150103            ')
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010004  ', CAST(N'2016-01-04' AS Date), N'AK04      ', N'2015010104          ', N'20150104            ')
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010005  ', CAST(N'2016-01-05' AS Date), N'AK04      ', N'2015010105          ', N'20150105            ')
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010006  ', CAST(N'2016-01-06' AS Date), N'AK06      ', N'2015010106          ', N'20150106            ')
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010007  ', CAST(N'2016-01-07' AS Date), N'AK06      ', N'2015010107          ', N'20150107            ')
INSERT [dbo].[汽车] ([驾驶执照], [年限], [型号], [品牌号], [客户编号]) VALUES (N'430923201501010008  ', CAST(N'2016-01-08' AS Date), N'AK08      ', N'2015010108          ', N'20150108            ')
GO
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150201       ', N'第一幼儿园第一街    ', CAST(N'2015-02-01T01:01:00.000' AS DateTime))
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150202       ', N'第一幼儿园第二街    ', CAST(N'2015-02-01T01:02:00.000' AS DateTime))
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150203       ', N'第一幼儿园第三街    ', CAST(N'2015-02-01T01:03:00.000' AS DateTime))
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150204       ', N'第一幼儿园第四街    ', CAST(N'2015-02-01T01:04:00.000' AS DateTime))
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150205       ', N'第一幼儿园第五街    ', CAST(N'2015-02-01T01:05:00.000' AS DateTime))
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150206       ', N'第一幼儿园第六街    ', CAST(N'2015-02-01T01:06:00.000' AS DateTime))
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150207       ', N'第六街              ', CAST(N'2015-02-01T01:06:00.000' AS DateTime))
INSERT [dbo].[事故] ([事故编号], [地点], [日期]) VALUES (N'20150208       ', N'第六街              ', CAST(N'2015-02-01T01:06:00.000' AS DateTime))
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Occurrence_DrivingLicense_FK]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Occurrence_DrivingLicense_FK] ON [dbo].[发生]
(
	[驾驶执照] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Customer_CustomerID]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Customer_CustomerID] ON [dbo].[客户]
(
	[客户编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Customer_Name]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Customer_Name] ON [dbo].[客户]
(
	[名字] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Brand_BrandID]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Brand_BrandID] ON [dbo].[品牌]
(
	[品牌号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Brand_BrandName]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Brand_BrandName] ON [dbo].[品牌]
(
	[品牌名] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Car_BrandID_FK]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Car_BrandID_FK] ON [dbo].[汽车]
(
	[品牌号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Car_DrivingLicense]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Car_DrivingLicense] ON [dbo].[汽车]
(
	[驾驶执照] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_Accident_AccidentID]    Script Date: 2024/12/29 13:22:49 ******/
CREATE NONCLUSTERED INDEX [idx_Accident_AccidentID] ON [dbo].[事故]
(
	[事故编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[发生]  WITH CHECK ADD FOREIGN KEY([驾驶执照])
REFERENCES [dbo].[汽车] ([驾驶执照])
GO
ALTER TABLE [dbo].[发生]  WITH CHECK ADD FOREIGN KEY([事故编号])
REFERENCES [dbo].[事故] ([事故编号])
GO
ALTER TABLE [dbo].[汽车]  WITH CHECK ADD FOREIGN KEY([客户编号])
REFERENCES [dbo].[客户] ([客户编号])
GO
ALTER TABLE [dbo].[汽车]  WITH CHECK ADD FOREIGN KEY([品牌号])
REFERENCES [dbo].[品牌] ([品牌号])
GO
/****** Object:  StoredProcedure [dbo].[pro_发生]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 创建存储过程
CREATE PROCEDURE [dbo].[pro_发生]
AS
select * from 发生;
GO
/****** Object:  StoredProcedure [dbo].[sp_CalculateAveragePurchaseYearForBrand]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_CalculateAveragePurchaseYearForBrand]
    @BrandName NCHAR(10)
AS
BEGIN
    SELECT AVG(YEAR(年限)) AS 平均购置年限
    FROM 汽车 a
    JOIN 品牌 b ON a.品牌号 = b.品牌号
    WHERE b.品牌名 = @BrandName;
END;
GO
/****** Object:  StoredProcedure [dbo].[sp_GetAccidentDriverInfoAndCompensation]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetAccidentDriverInfoAndCompensation]
    @AccidentID CHAR(15)
AS
BEGIN
    SELECT e.驾驶员信息, e.人员赔偿
    FROM 发生 e
    WHERE e.事故编号 = @AccidentID;
END;
GO
/****** Object:  StoredProcedure [dbo].[sp_GetCustomerCars]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetCustomerCars]
    @CustomerID CHAR(20)
AS
BEGIN
    SELECT a.驾驶执照, a.年限, a.型号, b.品牌名
    FROM 汽车 a
    JOIN 品牌 b ON a.品牌号 = b.品牌号
    WHERE a.客户编号 = @CustomerID;
END;
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertCar]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertCar]
    @DrivingLicense CHAR(20),
    @Year DATE,
    @Model CHAR(10),
    @BrandID CHAR(20),
    @CustomerID CHAR(20)
AS
BEGIN
    -- 可以添加数据验证逻辑，例如检查品牌号和客户编号是否存在于相应表中
    INSERT INTO 汽车 (驾驶执照, 年限, 型号, 品牌号, 客户编号)
    VALUES (@DrivingLicense, @Year, @Model, @BrandID, @CustomerID);
END;
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateCustomerAddress]    Script Date: 2024/12/29 13:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpdateCustomerAddress]
    @CustomerID CHAR(20),
    @NewAddress NVARCHAR(20)
AS
BEGIN
    UPDATE 客户
    SET 地址 = @NewAddress
    WHERE 客户编号 = @CustomerID;
END;
GO
USE [master]
GO
ALTER DATABASE [车辆管理系统] SET  READ_WRITE 
GO