phkwx5f4u
/
car
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
car/car.sql

472 lines
39 KiB
Raw Permalink Blame History Escape

This file contains invisible Unicode characters!

This file contains invisible Unicode characters that may be processed differently from what appears below. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to reveal hidden characters.

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
Reference in new issue View Git Blame Copy Permalink