第13章脚本

USE [master]GO/****** Object:  Database [User]    Script Date: 11/27/2016 18:54:14 ******/CREATE DATABASE [User] ON  PRIMARY ( NAME = N'User', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\User.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'User_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\User_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE [User] SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [User].[dbo].[sp_fulltext_database] @action = 'enable'endGOALTER DATABASE [User] SET ANSI_NULL_DEFAULT OFFGOALTER DATABASE [User] SET ANSI_NULLS OFFGOALTER DATABASE [User] SET ANSI_PADDING OFFGOALTER DATABASE [User] SET ANSI_WARNINGS OFFGOALTER DATABASE [User] SET ARITHABORT OFFGOALTER DATABASE [User] SET AUTO_CLOSE OFFGOALTER DATABASE [User] SET AUTO_CREATE_STATISTICS ONGOALTER DATABASE [User] SET AUTO_SHRINK OFFGOALTER DATABASE [User] SET AUTO_UPDATE_STATISTICS ONGOALTER DATABASE [User] SET CURSOR_CLOSE_ON_COMMIT OFFGOALTER DATABASE [User] SET CURSOR_DEFAULT  GLOBALGOALTER DATABASE [User] SET CONCAT_NULL_YIELDS_NULL OFFGOALTER DATABASE [User] SET NUMERIC_ROUNDABORT OFFGOALTER DATABASE [User] SET QUOTED_IDENTIFIER OFFGOALTER DATABASE [User] SET RECURSIVE_TRIGGERS OFFGOALTER DATABASE [User] SET  DISABLE_BROKERGOALTER DATABASE [User] SET AUTO_UPDATE_STATISTICS_ASYNC OFFGOALTER DATABASE [User] SET DATE_CORRELATION_OPTIMIZATION OFFGOALTER DATABASE [User] SET TRUSTWORTHY OFFGOALTER DATABASE [User] SET ALLOW_SNAPSHOT_ISOLATION OFFGOALTER DATABASE [User] SET PARAMETERIZATION SIMPLEGOALTER DATABASE [User] SET READ_COMMITTED_SNAPSHOT OFFGOALTER DATABASE [User] SET HONOR_BROKER_PRIORITY OFFGOALTER DATABASE [User] SET  READ_WRITEGOALTER DATABASE [User] SET RECOVERY FULLGOALTER DATABASE [User] SET  MULTI_USERGOALTER DATABASE [User] SET PAGE_VERIFY CHECKSUMGOALTER DATABASE [User] SET DB_CHAINING OFFGOEXEC sys.sp_db_vardecimal_storage_format N'User', N'ON'GOUSE [User]GO/****** Object:  Table [dbo].[QQUser]    Script Date: 11/27/2016 18:54:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[QQUser]([QQID] [bigint] NOT NULL,[PassWord] [varchar](50) NULL,[LastLogTime] [datetime] NULL,[Online] [int] NULL,[Level] [int] NOT NULL, CONSTRAINT [PK_QQUser] PRIMARY KEY CLUSTERED ([QQID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后一次登录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QQUser', @level2type=N'COLUMN',@level2name=N'LastLogTime'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'在线状态,0表示在线,1表示离线,2表示隐身' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QQUser', @level2type=N'COLUMN',@level2name=N'Online'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QQUser', @level2type=N'COLUMN',@level2name=N'Level'GOINSERT [dbo].[QQUser] ([QQID], [PassWord], [LastLogTime], [Online], [Level]) VALUES (8855678, N'guest0221', CAST(0x0000A16C010F7430 AS DateTime), 2, -1)INSERT [dbo].[QQUser] ([QQID], [PassWord], [LastLogTime], [Online], [Level]) VALUES (54789625, N'add512#&', CAST(0x0000A16701189614 AS DateTime), 2, 0)INSERT [dbo].[QQUser] ([QQID], [PassWord], [LastLogTime], [Online], [Level]) VALUES (88662753, N'admin0219', CAST(0x0000A16A015C7ED8 AS DateTime), 0, 0)/****** Object:  Table [dbo].[BaseInfo]    Script Date: 11/27/2016 18:54:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[BaseInfo]([QQID] [bigint] NOT NULL,[NickName] [varchar](50) NULL,[Sex] [int] NOT NULL,[Age] [int] NULL,[Province] [varchar](50) NULL,[City] [varchar](50) NULL,[Address] [varchar](50) NULL,[Phone] [char](50) NULL, CONSTRAINT [PK_BaseInfo] PRIMARY KEY CLUSTERED ([QQID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'昵称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BaseInfo', @level2type=N'COLUMN',@level2name=N'NickName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别,0表示男,1表示女' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BaseInfo', @level2type=N'COLUMN',@level2name=N'Sex'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BaseInfo', @level2type=N'COLUMN',@level2name=N'Age'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'省份' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BaseInfo', @level2type=N'COLUMN',@level2name=N'Province'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'城市' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BaseInfo', @level2type=N'COLUMN',@level2name=N'City'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'详细地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BaseInfo', @level2type=N'COLUMN',@level2name=N'Address'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'联系方式' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BaseInfo', @level2type=N'COLUMN',@level2name=N'Phone'GOINSERT [dbo].[BaseInfo] ([QQID], [NickName], [Sex], [Age], [Province], [City], [Address], [Phone]) VALUES (8855678, N'被淹死的鱼', 1, 38, N'北京市', N'北京', N'解放中路6号院106室', N'65794968876143                                    ')INSERT [dbo].[BaseInfo] ([QQID], [NickName], [Sex], [Age], [Province], [City], [Address], [Phone]) VALUES (54789625, N'蝴蝶飞飞', 1, 16, N'北京市', N'北京', N'亚运村', N'37547388157668                                    ')INSERT [dbo].[BaseInfo] ([QQID], [NickName], [Sex], [Age], [Province], [City], [Address], [Phone]) VALUES (88662753, N'秋芙蓉', 0, 20, N'河南市', N'南阳', N'方城博望', N'88715783657725                                    ')/****** Object:  Table [dbo].[Relation]    Script Date: 11/27/2016 18:54:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Relation]([QQID] [bigint] NOT NULL,[RelationQQID] [bigint] NULL,[RelationStatus] [int] NULL) ON [PRIMARY]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户A的QQ号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Relation', @level2type=N'COLUMN',@level2name=N'QQID'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关系用户B的QQ号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Relation', @level2type=N'COLUMN',@level2name=N'RelationQQID'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户关系:0表示用户B是用户A的好友,1表示用户B是用户A的黑名单' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Relation', @level2type=N'COLUMN',@level2name=N'RelationStatus'GOINSERT [dbo].[Relation] ([QQID], [RelationQQID], [RelationStatus]) VALUES (54789625, 88662753, 0)INSERT [dbo].[Relation] ([QQID], [RelationQQID], [RelationStatus]) VALUES (54789625, 8855678, 0)/****** Object:  Default [DF_QQUser_PassWord]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[QQUser] ADD  CONSTRAINT [DF_QQUser_PassWord]  DEFAULT ('密码') FOR [PassWord]GO/****** Object:  Default [DF_QQUser_Omline]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[QQUser] ADD  CONSTRAINT [DF_QQUser_Omline]  DEFAULT ((0)) FOR [Online]GO/****** Object:  Default [DF_QQUser_Level]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[QQUser] ADD  CONSTRAINT [DF_QQUser_Level]  DEFAULT ((0)) FOR [Level]GO/****** Object:  Default [DF_BaseInfo_Sex]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[BaseInfo] ADD  CONSTRAINT [DF_BaseInfo_Sex]  DEFAULT ((0)) FOR [Sex]GO/****** Object:  Default [DF_Relation_RelationStatus]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[Relation] ADD  CONSTRAINT [DF_Relation_RelationStatus]  DEFAULT ((0)) FOR [RelationStatus]GO/****** Object:  Check [CK_QQUser]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[QQUser]  WITH CHECK ADD  CONSTRAINT [CK_QQUser] CHECK  ((len([PassWord])>(6)))GOALTER TABLE [dbo].[QQUser] CHECK CONSTRAINT [CK_QQUser]GO/****** Object:  Check [CK_QQUser_1]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[QQUser]  WITH CHECK ADD  CONSTRAINT [CK_QQUser_1] CHECK  (([Online]=(0) OR [Online]=(1) OR [Online]=(2)))GOALTER TABLE [dbo].[QQUser] CHECK CONSTRAINT [CK_QQUser_1]GO/****** Object:  Check [CK_BaseInfo]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[BaseInfo]  WITH CHECK ADD  CONSTRAINT [CK_BaseInfo] CHECK  (([Sex]=(0) OR [Sex]=(1)))GOALTER TABLE [dbo].[BaseInfo] CHECK CONSTRAINT [CK_BaseInfo]GO/****** Object:  Check [CK_BaseInfo_1]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[BaseInfo]  WITH CHECK ADD  CONSTRAINT [CK_BaseInfo_1] CHECK  (([Age]>(1) AND [Age]<(100)))GOALTER TABLE [dbo].[BaseInfo] CHECK CONSTRAINT [CK_BaseInfo_1]GO/****** Object:  Check [CK_Relation]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[Relation]  WITH CHECK ADD  CONSTRAINT [CK_Relation] CHECK  (([RelationStatus]=(0) OR [RelationStatus]=(1)))GOALTER TABLE [dbo].[Relation] CHECK CONSTRAINT [CK_Relation]GO/****** Object:  ForeignKey [FK_Relation_QQUser]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[Relation]  WITH CHECK ADD  CONSTRAINT [FK_Relation_QQUser] FOREIGN KEY([QQID])REFERENCES [dbo].[QQUser] ([QQID])GOALTER TABLE [dbo].[Relation] CHECK CONSTRAINT [FK_Relation_QQUser]GO/****** Object:  ForeignKey [FK_Relation_QQUser1]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[Relation]  WITH CHECK ADD  CONSTRAINT [FK_Relation_QQUser1] FOREIGN KEY([RelationQQID])REFERENCES [dbo].[QQUser] ([QQID])GOALTER TABLE [dbo].[Relation] CHECK CONSTRAINT [FK_Relation_QQUser1]GO/****** Object:  ForeignKey [FK_Relation_QQUser2]    Script Date: 11/27/2016 18:54:15 ******/ALTER TABLE [dbo].[Relation]  WITH CHECK ADD  CONSTRAINT [FK_Relation_QQUser2] FOREIGN KEY([QQID])REFERENCES [dbo].[QQUser] ([QQID])GOALTER TABLE [dbo].[Relation] CHECK CONSTRAINT [FK_Relation_QQUser2]GO