access/sql server笔记(20160818)
来源:互联网 发布:梁朝伟张曼玉 知乎 编辑:程序博客网 时间:2024/06/05 16:14
配置SQL Server,允许远程连接
http://www.cnblogs.com/weizhengLoveMayDay/p/3267756.html
20161117添加:
关系数据库中的一些基本术语。
键码Key:用来标识一列或多列。
主关键字Primary Key:作为表行的唯一标识,一个表中只有一个主关键字,主关键字又称为主键。
候选关键字Candidate Key:它是唯一标识表中一行而又不含多余属性的一个属性集。
外关键字Foreign Key:如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外关键字。由此可见,外关键字表示了两个关系之间的联系,外关键字又称作外键。
公共关键字Common Key:在关系数据库中,关系之间的联系是通过相容或相同的属性或属性组来表示的。如果两个关系中具有相容或相同的属性或属性组,那么这个属性或属性组被称为这两个关系的公共关键字。
chap7 存储过程与触发器
1、参数的定义
2、输入参数
执行带有输入参数的存储过程时,SQL Server 2008提供了两种传递参数的方式。
a.按位置传递
use QPGameUserDB;
exec GSP_GP_EfficacyAccounts '1','2','3','4'
b.通过参数名传递
3、使用默认参数
4、输出参数
为了接受某一存储过程的返回值,需要一个变量来存放返回参数的值,在该存储过程的调用语句中,必须为这个变量加上OUTPUT关键字来声明。
use qptreasuredb;
declare @strErrorDescribe NVARCHAR(128)
exec GSP_GR_EfficacyMobile '80784','DC483E80A7A0BD9EF71D8CF973673924','192.168.0.254','3232235774','','715','216',@strErrorDescribe OUTPUT
select '错误消息为:'+@strErrorDescribe as '结果'
go
http://www.cnblogs.com/weizhengLoveMayDay/p/3267756.html
20161117添加:
关系数据库中的一些基本术语。
键码Key:用来标识一列或多列。
主关键字Primary Key:作为表行的唯一标识,一个表中只有一个主关键字,主关键字又称为主键。
候选关键字Candidate Key:它是唯一标识表中一行而又不含多余属性的一个属性集。
外关键字Foreign Key:如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外关键字。由此可见,外关键字表示了两个关系之间的联系,外关键字又称作外键。
公共关键字Common Key:在关系数据库中,关系之间的联系是通过相容或相同的属性或属性组来表示的。如果两个关系中具有相容或相同的属性或属性组,那么这个属性或属性组被称为这两个关系的公共关键字。
chap7 存储过程与触发器
1、参数的定义
2、输入参数
执行带有输入参数的存储过程时,SQL Server 2008提供了两种传递参数的方式。
a.按位置传递
use QPGameUserDB;
exec GSP_GP_EfficacyAccounts '1','2','3','4'
b.通过参数名传递
3、使用默认参数
4、输出参数
为了接受某一存储过程的返回值,需要一个变量来存放返回参数的值,在该存储过程的调用语句中,必须为这个变量加上OUTPUT关键字来声明。
use qptreasuredb;
declare @strErrorDescribe NVARCHAR(128)
exec GSP_GR_EfficacyMobile '80784','DC483E80A7A0BD9EF71D8CF973673924','192.168.0.254','3232235774','','715','216',@strErrorDescribe OUTPUT
select '错误消息为:'+@strErrorDescribe as '结果'
go
存储过程加密查看.exe
工具使用方法:右键单击加密的存储过程(例如dbo.GetPassWord),然后点击Decrypt in-place菜单项,即可解密该存储过程。
USE QPGameUserDB
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetPassWord]
@UserID_1 [nvarchar](50),
@Email_2 [nvarchar](50),
@Passw_3 [nvarchar](50),
@Passd_4 [nvarchar](50),
@Code_5 [nvarchar](50),
@Adder_6 [nvarchar](50),
@TelMail_8 [nvarchar](50),
@Txt_9 [nvarchar](800),
@PASSOWRD [nvarchar](50)
WITH ENCRYPTION AS
--解密后上面这句变为AS
BEGIN
IF EXISTS (select ID from PassWordList Where UserID=@UserID_1 And IsCut=0)
BEGIN
Delete From PassWordList Where UserID=@UserID_1 And IsCut=0
END
INSERT INTO [PassWordList]
(
[UserID],
[Email],
[Passw],
[Passd],
[Code],
[Adder],
[TelMail],
[Txt],[password])
VALUES
( @UserID_1,
@Email_2,
@Passw_3,
@Passd_4,
@Code_5,
@Adder_6,
@TelMail_8,
@Txt_9, @PASSOWRD)
END
return 0
GO
使用SQL Server Management Studio创建数据库(使用图形化工具)或使用Transact-SQL语句创建数据库(使用CREATE DATABASE语句)
use master
go
if exists (select * from dbo.sysdatabases where name = 'Hotel')
drop database Hotel
GO
create database Hotel--待创建数据库的名称
go
use Hotel
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[user_Info]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bookin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bookin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rooms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rooms]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[roomtype]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[roomtype]
GO
CREATE TABLE [dbo].[user_Info] (
[user_ID] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[user_PWD] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[user_Des] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bookin] (
[bookno] [varchar] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customID] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[roomno] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[indate] [datetime] NULL ,
[discount] [float] NULL ,
[inmemo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[checkdate] [datetime] NULL ,
[ammount] [float] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[rooms] (
[roomNO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[roomtype] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[roomposition] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[roomprice] [float] NULL ,
[putup] [bit],
[roommemo] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[roomtype] (
[typeid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[typename] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[area] [float] NULL ,
[bednum] [int] NULL ,
[haircondition] [bit],
[htelephone] [bit],
[htelevision] [bit],
[htoilet] [bit],
[price] [float] NULL
) ON [PRIMARY]
GO
INSERT user_Info (user_ID, user_PWD) VALUES ('Administrator', NULL)
GO
HotelManageSystem数据设计说明.doc
一,数据库名称: Hotel
1. 用户信息表(user_Info)
字段 数据类型 大小 主/外键 说明
user_ID varchar 15 用户名
user_PWD varchar 10 密码
user_Des varchar 10
2. 客房信息表(rooms)
字段 数据类型 大小 主/外键 说明
roomNO varchar 10 客房编号
roomtype varchar 10 客房类型
roomposition varchar 20 客房位置
roomprice float 客房单价/每天
putup bit 1表示已有顾客入住
roommemo text 备注信息
3. 订房信息表(bookin)
字段 数据类型 大小 主/外键 说明
bookno varchar 14
customname varchar 10 顾客姓名
customID varchar 18 身份证号码
roomno varchar 10 客房编号
indate datetime 入住时间
discount float 折扣%
inmemo text 备注信息
checkdate datetime
ammount float
4. 客房标准表(roomtype)
字段 数据类型 大小 主/外键 说明
typeid varchar 10 标准编号
typename varchar 10 标准名称
area float 房间面积
bednum int 床位数量
haircondition bit 是否有空调
htelephone bit 是否有电话
htelevision bit 是否有电视
htoilet bit 是否有卫生间
price float 住房单价/每天
SQL Server 2008 R2有一个任务-生成脚本的功能,能备份数据库的结构
以下是从数据库FcBallDB生成的脚本FcBallDB.sql,前面几句是笔者手动加的:
use master
go
if exists (select * from dbo.sysdatabases where name = 'FcBallDB')
drop database FcBallDB
GO
create database FcBallDB--待创建数据库的名称
go
USE FcBallDB
GO
/****** Object: Table [dbo].[BetRecords] Script Date: 08/18/2016 21:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BetRecords](
[id] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[Gold] [int] NOT NULL,
[Ball] [int] NOT NULL,
[Settled] [bit] NOT NULL,
[Term] [int] NOT NULL,
CONSTRAINT [PK_BetRecords] PRIMARY KEY CLUSTERED
(
[id] 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
/****** Object: Table [dbo].[Terms] Script Date: 08/18/2016 21:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Terms](
[id] [int] IDENTITY(1,1) NOT NULL,
[term] [int] NOT NULL,
[ball] [int] NOT NULL,
CONSTRAINT [PK_Terms] PRIMARY KEY CLUSTERED
(
[id] 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
/****** Object: StoredProcedure [dbo].[GetRecordsByTerm] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetRecordsByTerm]
(
@term int
)
AS
SET NOCOUNT ON;
SELECT id, UserID, Gold, Ball, Settled, Term FROM dbo.BetRecords
where Term = @term
GO
/****** Object: StoredProcedure [dbo].[GetLastTerm] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetLastTerm]
AS
SET NOCOUNT ON;
SELECT TOP (1) term
FROM Terms
ORDER BY term DESC
GO
/****** Object: StoredProcedure [dbo].[AddTerm] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddTerm]
(
@term int,
@ball int
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Terms] ([term], [ball]) VALUES (@term, @ball);
GO
/****** Object: StoredProcedure [dbo].[AddRecord] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddRecord]
(
@UserID int,
@Term int,
@Gold int,
@Ball int,
@Settled bit
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[BetRecords] ([UserID], [Term], [Gold], [Ball], [Settled]) VALUES (@UserID, @Term, @Gold, @Ball, @Settled);
GO
工具使用方法:右键单击加密的存储过程(例如dbo.GetPassWord),然后点击Decrypt in-place菜单项,即可解密该存储过程。
USE QPGameUserDB
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetPassWord]
@UserID_1 [nvarchar](50),
@Email_2 [nvarchar](50),
@Passw_3 [nvarchar](50),
@Passd_4 [nvarchar](50),
@Code_5 [nvarchar](50),
@Adder_6 [nvarchar](50),
@TelMail_8 [nvarchar](50),
@Txt_9 [nvarchar](800),
@PASSOWRD [nvarchar](50)
WITH ENCRYPTION AS
--解密后上面这句变为AS
BEGIN
IF EXISTS (select ID from PassWordList Where UserID=@UserID_1 And IsCut=0)
BEGIN
Delete From PassWordList Where UserID=@UserID_1 And IsCut=0
END
INSERT INTO [PassWordList]
(
[UserID],
[Email],
[Passw],
[Passd],
[Code],
[Adder],
[TelMail],
[Txt],[password])
VALUES
( @UserID_1,
@Email_2,
@Passw_3,
@Passd_4,
@Code_5,
@Adder_6,
@TelMail_8,
@Txt_9, @PASSOWRD)
END
return 0
GO
使用SQL Server Management Studio创建数据库(使用图形化工具)或使用Transact-SQL语句创建数据库(使用CREATE DATABASE语句)
use master
go
if exists (select * from dbo.sysdatabases where name = 'Hotel')
drop database Hotel
GO
create database Hotel--待创建数据库的名称
go
use Hotel
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[user_Info]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bookin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bookin]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rooms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rooms]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[roomtype]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[roomtype]
GO
CREATE TABLE [dbo].[user_Info] (
[user_ID] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[user_PWD] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[user_Des] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bookin] (
[bookno] [varchar] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[customID] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[roomno] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[indate] [datetime] NULL ,
[discount] [float] NULL ,
[inmemo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[checkdate] [datetime] NULL ,
[ammount] [float] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[rooms] (
[roomNO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[roomtype] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[roomposition] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[roomprice] [float] NULL ,
[putup] [bit],
[roommemo] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[roomtype] (
[typeid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[typename] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[area] [float] NULL ,
[bednum] [int] NULL ,
[haircondition] [bit],
[htelephone] [bit],
[htelevision] [bit],
[htoilet] [bit],
[price] [float] NULL
) ON [PRIMARY]
GO
INSERT user_Info (user_ID, user_PWD) VALUES ('Administrator', NULL)
GO
HotelManageSystem数据设计说明.doc
一,数据库名称: Hotel
1. 用户信息表(user_Info)
字段 数据类型 大小 主/外键 说明
user_ID varchar 15 用户名
user_PWD varchar 10 密码
user_Des varchar 10
2. 客房信息表(rooms)
字段 数据类型 大小 主/外键 说明
roomNO varchar 10 客房编号
roomtype varchar 10 客房类型
roomposition varchar 20 客房位置
roomprice float 客房单价/每天
putup bit 1表示已有顾客入住
roommemo text 备注信息
3. 订房信息表(bookin)
字段 数据类型 大小 主/外键 说明
bookno varchar 14
customname varchar 10 顾客姓名
customID varchar 18 身份证号码
roomno varchar 10 客房编号
indate datetime 入住时间
discount float 折扣%
inmemo text 备注信息
checkdate datetime
ammount float
4. 客房标准表(roomtype)
字段 数据类型 大小 主/外键 说明
typeid varchar 10 标准编号
typename varchar 10 标准名称
area float 房间面积
bednum int 床位数量
haircondition bit 是否有空调
htelephone bit 是否有电话
htelevision bit 是否有电视
htoilet bit 是否有卫生间
price float 住房单价/每天
SQL Server 2008 R2有一个任务-生成脚本的功能,能备份数据库的结构
以下是从数据库FcBallDB生成的脚本FcBallDB.sql,前面几句是笔者手动加的:
use master
go
if exists (select * from dbo.sysdatabases where name = 'FcBallDB')
drop database FcBallDB
GO
create database FcBallDB--待创建数据库的名称
go
USE FcBallDB
GO
/****** Object: Table [dbo].[BetRecords] Script Date: 08/18/2016 21:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BetRecords](
[id] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[Gold] [int] NOT NULL,
[Ball] [int] NOT NULL,
[Settled] [bit] NOT NULL,
[Term] [int] NOT NULL,
CONSTRAINT [PK_BetRecords] PRIMARY KEY CLUSTERED
(
[id] 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
/****** Object: Table [dbo].[Terms] Script Date: 08/18/2016 21:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Terms](
[id] [int] IDENTITY(1,1) NOT NULL,
[term] [int] NOT NULL,
[ball] [int] NOT NULL,
CONSTRAINT [PK_Terms] PRIMARY KEY CLUSTERED
(
[id] 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
/****** Object: StoredProcedure [dbo].[GetRecordsByTerm] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetRecordsByTerm]
(
@term int
)
AS
SET NOCOUNT ON;
SELECT id, UserID, Gold, Ball, Settled, Term FROM dbo.BetRecords
where Term = @term
GO
/****** Object: StoredProcedure [dbo].[GetLastTerm] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetLastTerm]
AS
SET NOCOUNT ON;
SELECT TOP (1) term
FROM Terms
ORDER BY term DESC
GO
/****** Object: StoredProcedure [dbo].[AddTerm] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddTerm]
(
@term int,
@ball int
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Terms] ([term], [ball]) VALUES (@term, @ball);
GO
/****** Object: StoredProcedure [dbo].[AddRecord] Script Date: 08/18/2016 21:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddRecord]
(
@UserID int,
@Term int,
@Gold int,
@Ball int,
@Settled bit
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[BetRecords] ([UserID], [Term], [Gold], [Ball], [Settled]) VALUES (@UserID, @Term, @Gold, @Ball, @Settled);
GO
0 0
- access/sql server笔记(20160818)
- sql server 转 access 笔记
- SQL学习笔记[3] - sql server 直写access数据文件
- 将access导入SQL server (转)
- Access和Sql Server 比较(原)
- sql server與access
- SQL Server & Access
- [原创]批量数据上传(access到sql server。sql server到sql server)
- SQL SERVER DTS(ACCESS,EXCEL)
- 数据库连接字符串(SQL Server Access)
- SQL Server 2005 Remote Access
- 将access导入SQL server
- Access转Sql Server问题
- 将access导入SQL server
- 将access导入SQL server
- 将access导入SQL server
- 将access导入SQL server
- 将access导入SQL server
- 使用RecycleView做网格布局
- Hibernate两种方式进行映射配置
- java中重写equals方法
- Java中的Random函数
- 几种js图片裁剪
- access/sql server笔记(20160818)
- JAVA中读取文件转换为字符串
- ListView的刷新
- JSP笔记 -Servlet技术
- 【嵌入式Linux】LCD应用程序2440
- mysql双机热备
- js判断ie浏览器版本号
- python 学习-使用生成器输出杨辉三角和斐波拉契数列
- 大致看了下,很给了,mark一下。 http://cn.vuejs.org/