金典 SQL笔记(1)
时间：2021-07-01 10:21:17
帮助过：24人阅读
CREATE USER [sasa] FOR LOGIN [sasa] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** 对象: StoredProcedure [dbo].[SP_Select] 脚本日期: 06/25/2015 10:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[SP_Select]
@OName varchar(100)
As
Declare @Str Varchar(1000),
@dbname varchar(40)
set @dbname=db_name()
Set @Str=‘Select * from ‘+@dbname+‘.dbo.‘+@OName
Exec (@Str)
GO
/****** 对象: StoredProcedure [dbo].[sp_syscolumns] 脚本日期: 06/25/2015 10:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_syscolumns] --Exec sp_syscolumns ‘eemployee‘
@Object NVARCHAR(1000)
As
/*
Function:取得一个对象中的所有列的项目(主要针对表)
Remark: Create By Deam L 2013/4/7
*/
Begin
Set nocount on
Declare @Name NVARCHAR(1000)
Select @Name=Isnull(@Name+‘,‘,‘‘)+name From syscolumns Where id=object_id(@Object)
Print @Name
Set nocount off
END
GO
/****** 对象: StoredProcedure [dbo].[前言] 脚本日期: 06/25/2015 10:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <CXP,,>
-- Create date: <2014-10-8 09:11:56,,>
-- Description: <获取OA系统当前申请的采购流程,,>
-- =============================================
CREATE PROCEDURE [dbo].[前言]
AS
GO
/****** 对象: StoredProcedure [dbo].[c_CreateSqlBaseTable] 脚本日期: 06/25/2015 10:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[c_CreateSqlBaseTable]
AS
BEGIN
--T_Person为记录人员的数据表 其中主键字段FName为人员姓名,FAge为年龄,FRemark为备注信息,
--T_Debt为债务信息.其中主键为FNumber为债务编号,FAmount为欠债金额,FPerson为欠债人姓名,
--FPerson与T_person中FName字段建立了外键关联关系
CREATE TABLE T_Person(FName VARCHAR(20),FAge INT,FRemark VARCHAR(20),primary KEY(FName))
CREATE TABLE T_Debt(FNumber VARCHAR(20),FAmount NUMERIC(10,2) NOT NULL
,FPerson VARCHAR(20),PRIMARY KEY(FNumber),FOREIGN KEY(FPerson) REFERENCES T_Person(FName))
--插入范例数据
INSERT INTO T_Person(FName,FAge,FRemark) VALUES(‘Tom‘,18,‘USA‘)
INSERT INTO T_Person(FName,FAge,FRemark) VALUES(‘Jim‘,20,‘USA‘)
INSERT INTO T_Person(FName,FAge,FRemark) VALUES(‘Lili‘,22,‘China‘)
INSERT INTO T_Person(FName,FAge,FRemark) VALUES(‘XiaoWang‘,17,‘China‘)
INSERT INTO T_Person(FName,FAge,FRemark) VALUES(‘Kimisushi‘,18,‘Korea‘)
INSERT INTO T_Person(FAge,FName) VALUES(22,‘LXF‘)
INSERT INTO T_Person VALUES(‘lurenl‘,23,‘China‘) --不推荐此写法,容易出错
INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES(‘1‘,300,‘Jim‘)
INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES(‘2‘,300,‘Jim‘)
INSERT INTO T_Debt(FNumber,FAmount,FPerson) VALUES(‘3‘,100,‘Tom‘)
END
GO
/****** 对象: Table [dbo].[T_Person] 脚本日期: 06/25/2015 10:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_Person](
[FName] [varchar](20) NOT NULL,
[FAge] [int] NULL,
[FRemark] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[FName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[T_Debt] 脚本日期: 06/25/2015 10:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_Debt](
[FNumber] [varchar](20) NOT NULL,
[FAmount] [numeric](10, 2) NOT NULL,
[FPerson] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[FNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: ForeignKey [FK__T_Debt__FPerson__07020F21] 脚本日期: 06/25/2015 10:40:21 ******/
ALTER TABLE [dbo].[T_Debt] WITH CHECK ADD FOREIGN KEY([FPerson])
REFERENCES [dbo].[T_Person] ([FName])
GO
金典 SQL笔记(1)
标签：sql 主键 数据 设置
